<?php

class Checklist {

    protected $info = array();
    private $current_date;

    public function __construct() {
        $this->db = DBConnectorFactory::loadDriver();
        $this->current_date = date('Y-m-d H:i:s');
        $this->logged_id = Session::getUserID();
    }

    public function setData(array $data) {
        $this->info = $data;
        return $this;
    }
    public function submitChecklist(){
        $this->db->query('START TRANSACTION');
        $checklist_id = $this->info['checklist_id'];
        if($checklist_id == 0){
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist (
                                            checklist_name,
                                            checklist_description, 
                                            date_created, 
                                            created_by, 
                                            date_modified, 
                                            modified_by
                                            ) 
                                        VALUES (?,?,?,?,?,?)', [&$this->info['checklist_name'],
                                                                &$this->info['checklist_description'],
                                                                &$this->current_date,&$this->logged_id,
                                                                &$this->current_date,&$this->logged_id]);

            $checklist_id = $this->db->id();
        }else{
            $schedule_ids = array();
            $checklist_schedule_query = $this->db->query('select schedule_id from tbl_checklist_schedule where checklist_id= "'.$checklist_id.'"');
            while($row= $this->db->assoc($checklist_schedule_query)){
                $schedule_ids[]= $row['schedule_id']; 
            }
            if(!empty($schedule_ids)){
                $schedule_cheklist_answers_query = $this->db->query('select count(*) as total_used from tbl_checklist_schedule_answer_main where schedule_id IN ('.implode(",",$schedule_ids).')');
                $rows_count = $this->db->assoc($schedule_cheklist_answers_query)['total_used'];
                if($rows_count > 0)
                    return ['unable_edit_checklist'];

            }
            $this->db->prepare_Stmt('Delete from tbl_checklist_questions where checklist_id = ?', [&$checklist_id]);
            $this->db->prepare_Stmt('Update  tbl_checklist set
                                            checklist_name = ?,
                                            checklist_description = ?, 
                                            date_modified =?, 
                                            modified_by =? 
                                    where checklist_id =?
                                            ', [&$this->info['checklist_name'],
                                                &$this->info['checklist_description'],
                                                &$this->current_date,&$this->logged_id,
                                                &$checklist_id]);
        }
        $checklist_questions = json_decode($this->info['checklist_questions'],true);
        foreach($checklist_questions as $question){
            $question['default_answer'] = $question['question_type'] == 6 ? (implode(",",$question['default_answer'])) : $question['default_answer'];
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_questions (
                                            question_name,
                                            answer_type_id, 
                                            answer_type_value, 
                                            default_answer, 
                                            checklist_id
                                            ) 
                                        VALUES (?,?,?,?,?)', [&$question['question_name'],&$question['question_type'],
                                                              &$question['question_value'],&$question['default_answer'],&$checklist_id]);
        }

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);

    }
    public function submitChecklistSchedule(){
        $schedule_id = $this->info['schedule_id'];
        $schedule_end_date = !empty($this->info['schedule_end_date']) ?  $this->info['schedule_end_date'] : NULL;
        $part_id = !empty($this->info['part_id']) ?  $this->info['part_id'] : NULL;
        $schedule_repeat_id = $this->info['schedule_type_id'] == 2 ? $this->info['property_id'] : $this->info['period'];
        $schedule_repeat_value = $this->info['schedule_type_id'] == 2 ? $this->info['property_value'] : ($this->info['period'] == 1 ? $this->info['nb_of_hours'] : ($this->info['period'] == 2 ? $this->info['nb_of_days'] : ($this->info['period'] == 3 ? $this->info['week_days'] : $this->info['nb_of_months'])));
        if($schedule_id == 0){
            $next_schedule_date  = $this->info['schedule_type_id'] == 2 ? NULL : self::getNextScheduleDate($this->info['schedule_start_date'],$this->info['schedule_type_id'],$schedule_repeat_id,$schedule_repeat_value);
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule (
                                            schedule_title,
                                            schedule_start_date, 
                                            schedule_end_date, 
                                            checklist_id, 
                                            line_id, 
                                            Product_ID, 
                                            part_id, 
                                            schedule_type_id, 
                                            schedule_repeat_id, 
                                            schedule_repeat_value,
                                            schedule_next_date
                                            ) 
                                        VALUES (?,?,?,?,?,?,?,?,?,?,?)', [
                                            &$this->info['schedule_title'],&$this->info['schedule_start_date'],
                                            &$schedule_end_date,&$this->info['checklist_id'],&$this->info['line_id'],
                                            &$this->info['Product_ID'],&$part_id,
                                            &$this->info['schedule_type_id'],
                                            &$schedule_repeat_id,&$schedule_repeat_value,
                                            &$next_schedule_date
                                        ]);
        }else{
            $schedule_cheklist_answers_query = $this->db->query('select count(*) as total_used from tbl_checklist_schedule_answer_main where schedule_id = "'.$schedule_id.'"');
            $rows_count = $this->db->assoc($schedule_cheklist_answers_query)['total_used'];
            if($rows_count > 0)
                return ['unable_edit_checklist_schedule'];
            $this->db->prepare_Stmt('Update  tbl_checklist_schedule set
                                            schedule_title = ?,
                                            schedule_start_date = ?, 
                                            schedule_end_date =?, 
                                            checklist_id= ?, 
                                            line_id =?, 
                                            Product_ID =?, 
                                            part_id=? , 
                                            schedule_type_id = ?, 
                                            schedule_repeat_id = ?, 
                                            schedule_repeat_value = ?
                                    where schedule_id =?
                                            ', [&$this->info['schedule_title'],&$this->info['schedule_start_date'],
                                                &$schedule_end_date,&$this->info['checklist_id'],&$this->info['line_id'],
                                                &$this->info['Product_ID'],&$part_id,
                                                &$this->info['schedule_type_id'],
                                                &$schedule_repeat_id,&$schedule_repeat_value,&$schedule_id]);
        }
        $error = $this->db->error();
        return $error;

    }
    public function submitLineChecklistSchedule(){
        $this->db->query('START TRANSACTION');
        $schedule_end_date = !empty($this->info['schedule_end_date']) ?  $this->info['schedule_end_date'] : NULL;

        $schedule_repeat_id = $this->info['schedule_type_id'] == 2 ? $this->info['property_id'] : $this->info['period'];
        $schedule_repeat_value = $this->info['schedule_type_id'] == 2 ? $this->info['property_value'] : ($this->info['period'] == 1 ? $this->info['nb_of_hours'] : ($this->info['period'] == 2 ? $this->info['nb_of_days'] : ($this->info['period'] == 3 ? $this->info['week_days'] : $this->info['nb_of_months'])));


        $next_schedule_date  = $this->info['schedule_type_id'] == 2 ? NULL : self::getNextScheduleDate($this->info['schedule_start_date'],$this->info['schedule_type_id'],$schedule_repeat_id,$schedule_repeat_value);
        $schedule_checklist = json_decode($this->info['line_machine_part'],true);
        foreach($schedule_checklist as $row){
            $part_id = !empty($row['part_id']) ?  $row['part_id'] : NULL;
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule (
                                            schedule_title,
                                            schedule_start_date, 
                                            schedule_end_date, 
                                            checklist_id, 
                                            line_id, 
                                            Product_ID, 
                                            part_id, 
                                            schedule_type_id, 
                                            schedule_repeat_id, 
                                            schedule_repeat_value,
                                            schedule_next_date
                                            ) 
                                        VALUES (?,?,?,?,?,?,?,?,?,?,?)', [
                                            &$this->info['schedule_title'],&$this->info['schedule_start_date'],
                                            &$schedule_end_date,&$this->info['checklist_id'],&$row['line_id'],
                                            &$row['machine_id'],&$part_id,
                                            &$this->info['schedule_type_id'],
                                            &$schedule_repeat_id,&$schedule_repeat_value,
                                            &$next_schedule_date
                                        ]);

        }

        $error = $this->db->error();

        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);

    }
    public function getNextScheduleDate($current_date,$schedule_type,$schedule_repeat_id,$schedule_repeat_value){
        $expected_next_date = NULL;
        if($schedule_type == 1){
            if($schedule_repeat_id <= 2  || $schedule_repeat_id == 4){
                $type = $schedule_repeat_id == 1 ? 'hours' : ($schedule_repeat_id == 2 ? 'day' : 'month');
                $expected_next_date =  date("Y-m-d H:i:s", strtotime('+'.$schedule_repeat_value.' '.$type, strtotime($current_date)));  
            }else{
                $week_days = explode(",",$schedule_repeat_value);
                $current = date('w',strtotime($current_date));
                $nextkey = array_search($current, $week_days) + 1;
                if($nextkey == count($week_days))
                    $nextkey = 0;
                $next = $week_days[$nextkey];
                $week_days_array = ['','monday','tuesday','wednesday','thursday','friday','saturday','sunday'];
                $expected_next_date = date('Y-m-d H:i:s', strtotime('next '.$week_days_array[$next],strtotime($current_date)));
            }
        }
        return $expected_next_date;
    }

    public function deleteChecklistSchedule(){
        $this->db->prepare_Stmt('Delete from tbl_checklist_schedule where schedule_id = ?', [&$this->info['schedule_id']]);
        $error = $this->db->error();
        return $error;
    }
    public function deleteChecklist(){
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_checklist_questions where checklist_id = ?', [&$this->info['checklist_id']]);
        $this->db->prepare_Stmt('Delete from tbl_checklist where checklist_id = ?', [&$this->info['checklist_id']]);
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }
    public function getChecklistQuestions(){
        $data = array();
        $query = $this->db->prepare_Stmt('select questions.*,types.type_name
                                          from tbl_checklist_questions questions
                                          inner join tbl_question_types types
                                          on questions.answer_type_id = types.type_id 
                                          where questions.checklist_id = ?',[&$this->info['checklist_id']]);
        while($row = $this->db->assoc($query)){
            $data[] = $row;
        }
        return $data;
    }
    public function getAnsweredQuestions(){
        $data = array();
        $query = $this->db->prepare_Stmt('select answers.*,
                                                 questions.question_name,
                                                 questions.answer_type_id,
                                                 questions.answer_type_value
                                          from tbl_checklist_schedule_answer_details answers
                                          inner join tbl_checklist_questions questions
                                          on answers.checklist_question_id = questions.checklist_question_id
                                          where answers.answer_main_id = ?',[&$this->info['answer_main_id']]);
        while($row = $this->db->assoc($query)){
            $data[] = $row;
        }
        return $data;
    }
    public function submitChecklistScheduleAnswers(){
        $this->db->query('START TRANSACTION');
        $answer_main_id = $this->info['answer_main_id'];
        if($answer_main_id == 0){
            $schedule_query = $this->db->query('select * from tbl_checklist_schedule  where schedule_id = "'.$this->info['schedule_id'].'"');
            $schedule_row = $this->db->assoc($schedule_query);
            $schedule_repeat_value  = $schedule_row['schedule_type_id'] == 2 ? $schedule_row['schedule_repeat_value'] : 0;
            $property_value  = $schedule_row['schedule_type_id'] == 2 ? $schedule_row['property_value'] : 0;
            $next_date = $schedule_row['schedule_type_id'] == 2 ? NULL : self::getNextDate();
            $this->db->prepare_Stmt('update tbl_checklist_schedule set schedule_next_date = ?,last_run_date = ?,property_value = 0 where schedule_id =?', [&$next_date,&$this->current_date,&$this->info['schedule_id']]);
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule_answer_main (
                                            schedule_id,
                                            schedule_date, 
                                            schedule_repeat_value, 
                                            property_value, 
                                            date_created, 
                                            created_by, 
                                            date_modified,
                                            modified_by
                                            ) 
                                        VALUES (?,?,?,?,?,?,?,?)', [
                                            &$this->info['schedule_id'],&$next_date,
                                            &$schedule_repeat_value,&$property_value,
                                            &$this->current_date,&$this->logged_id,
                                            &$this->current_date,&$this->logged_id
                                        ]);
            $answer_main_id = $this->db->id();
        }else{
            $this->db->prepare_Stmt('Update  tbl_checklist_schedule_answer_main set
                                            modified_by = ?, 
                                            date_modified =?
                                    where answer_main_id =?
                                            ', [&$this->logged_id,&$this->current_date, &$answer_main_id]);
            $this->db->prepare_Stmt('delete from  tbl_checklist_schedule_answer_details where answer_main_id = ? ',[&$answer_main_id]);
        }
        $answers = json_decode($this->info['answers'],true);
        foreach($answers as $answer){
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule_answer_details (
                                            answer_main_id,
                                            checklist_question_id, 
                                            question_solution
                                            ) 
                                        VALUES (?,?,?)', [&$answer_main_id,$answer['checklist_question_id'],&$answer['question_solution']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    } 
    public function submitDefaultScheduleAnswers(){
        $this->db->query('START TRANSACTION');
        $schedule_query = $this->db->query('select * from tbl_checklist_schedule  where schedule_id = "'.$this->info['schedule_id'].'"');
        $schedule_row = $this->db->assoc($schedule_query);
        $checklist_id = $schedule_row['checklist_id'];
        $schedule_repeat_value  = $schedule_row['schedule_type_id'] == 2 ? $schedule_row['schedule_repeat_value'] : 0;
        $property_value  = $schedule_row['schedule_type_id'] == 2 ? $schedule_row['property_value'] : 0;
        $next_date = $schedule_row['schedule_type_id'] == 2 ? NULL : self::getNextDate();
        $this->db->prepare_Stmt('update tbl_checklist_schedule set schedule_next_date = ?,last_run_date = ?,property_value = 0 where schedule_id =?', [&$next_date,&$this->current_date,&$this->info['schedule_id']]);
        $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule_answer_main (
                                            schedule_id,
                                            schedule_date, 
                                            schedule_repeat_value, 
                                            property_value, 
                                            date_created, 
                                            created_by, 
                                            date_modified,
                                            modified_by
                                            ) 
                                        VALUES (?,?,?,?,?,?,?,?)', [
                                            &$this->info['schedule_id'],&$next_date,
                                            &$schedule_repeat_value,&$property_value,
                                            &$this->current_date,&$this->logged_id,
                                            &$this->current_date,&$this->logged_id
                                        ]);
        $answer_main_id = $this->db->id();
        $checklist_questions_query = $this->db->query('select * from tbl_checklist_questions where checklist_id = "'.$checklist_id.'"');
        while($questions = $this->db->assoc($checklist_questions_query)){
            $this->db->prepare_Stmt('INSERT INTO tbl_checklist_schedule_answer_details (
                                            answer_main_id,
                                            checklist_question_id, 
                                            question_solution
                                            ) 
                                        VALUES (?,?,?)', [&$answer_main_id,$questions['checklist_question_id'],&$questions['default_answer']]);
        }
        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }
    public function getNextDate(){
        $schedule_main_query = $this->db->query('select * from tbl_checklist_schedule where schedule_id ="'.$this->info['schedule_id'].'"');
        $schedule_main_row = $this->db->assoc($schedule_main_query);

        $schedule_answer_query = $this->db->query('select count(*) as total_rows from tbl_checklist_schedule_answer_main where schedule_id ="'.$this->info['schedule_id'].'" ');
        $row_count = $this->db->assoc($schedule_answer_query)['total_rows'];
        $current_date = '';
        $expected_next_date = '';
        if($row_count == 0)
            $current_date = $schedule_main_row['schedule_start_date'];
        else
            $current_date= date('Y-m-d H:i:s');

        $expected_next_date = self::getNextScheduleDate($current_date,$schedule_main_row['schedule_type_id'],$schedule_main_row['schedule_repeat_id'],$schedule_main_row['schedule_repeat_value']);
        return $expected_next_date;
    }
    public function deleteAnswers(){
        $this->db->query('START TRANSACTION');
        $this->db->prepare_Stmt('Delete from tbl_checklist_schedule_answer_details where answer_main_id = ?', [&$this->info['answer_main_id']]);
        $this->db->prepare_Stmt('Delete from tbl_checklist_schedule_answer_main where answer_main_id = ?', [&$this->info['answer_main_id']]);

        $data_query = $this->db->query('select * from tbl_checklist_schedule_answer_main  where  answer_main_id = "'.$this->info['answer_main_id'].'"');
        $row = $this->db->assoc($data_query);
        $this->info['schedule_id'] = $row['schedule_id'];

        $expected_date = self::getNextDate();
        $this->db->prepare_Stmt('update tbl_checklist_schedule set  schedule_next_date = ? where schedule_id = ?', [&$expected_date,&$this->info['schedule_id']]);

        $error = $this->db->error();
        if (empty($error)) {
            $this->db->query("COMMIT");
        } else {
            $this->db->query("ROLLBACK");
        }
        return array_unique($error);
    }
    public function getMachineLines($id){
        $data = array();
        $query = $this->db->query('select line_machine.*,products.Product_Name
                                   from tbl_line_machine line_machine
                                   inner join tbl_stock_products products using(Product_ID)
                                   where line_machine.line_id = "'.$id.'"
                                  ');
        while($row = $this->db->assoc($query)){
            $data[] =  $row;
        }
        return $data;
    }
    public function missedChecklistScheduleData(){
        $data = array();
        $condition = !empty($this->info['section_id']) ? ' and section_id = "'.$this->info['section_id'].'"' : '';
        $condition .= !empty($this->info['Product_ID']) ? ' and Product_ID = "'.$this->info['Product_ID'].'"' : '';
        $condition .= !empty($this->info['line_id']) ? ' and line_id = "'.$this->info['line_id'].'"' : '';
        $this->info['current_date_time'] = date('Y-m-d H:i:s');
        $condition .= ' AND (SUBSTR(`schedule_next_date`, 1, 10) between "' . $this->info['start_date'] . '" and "' . $this->info['end_date'] . '" and schedule_type_id = 1) or (isnull(schedule_next_date) and schedule_type_id = 2)';
        $checklist_questions = array();
        $questions_query = $this->db->query('select checklist.checklist_id,
                                                    questions.question_name
                                             from tbl_checklist checklist
                                             inner join tbl_checklist_questions questions using(checklist_id)
                            ');
        while($row= $this->db->assoc($questions_query)){
            $checklist_questions[$row['checklist_id']][] = $row['question_name'];
        }
        $query = $this->db->query('select line_name,product_full_name,product_part_note,schedule_next_date,checklist_id
                                   from checklist_schedule_list check_sched
                                   where 1=1 
                                  '.$condition.'order by line_id desc');
        while($row = $this->db->assoc($query)){
            $row['questions'] = implode("<br>",$checklist_questions[$row['checklist_id']]);
            $row['answers'] ='';
            unset($row['checklist_id']);
            $data[] = array_values($row);
//            $data[$row['line_id']]['line_name'] = $row['line_name'];
//            $data[$row['line_id']]['products'][$row['Product_ID']]['product_name'] = $row['product_full_name'];
//            $data[$row['line_id']]['products'][$row['Product_ID']]['parts'][] =  $row;
        }
        return $data;
    }
}