<?php

if (!defined('BASEPATH')) {
    exit('No direct script access allowed');
}

class Lesson_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function get_entry(int $id)
    {
        if ($id <= 0) {
            return null;
        }
        $result = $this->db->get_where('course_scheduled_new', ['id' => $id])->result();
        return isset($result[0]) ? $result[0] : null;
    }

    public function update_entry(int $id, array $fields): bool
    {
        if ($id <= 0 || empty($fields)) {
            return false;
        }
        return $this->db->update('course_scheduled_new', $fields, ['id' => $id]);
    }

    public function students_enrolled(int $lesson_id) : array {
        $query = $this->db->query(
            "SELECT u.user_id, u.first_name, u.last_name, lc.comment
             FROM `course_student_new` as cs
             LEFT JOIN user u ON cs.student_id = u.user_id
             WHERE cs.lesson_id = " . (int) $lesson_id . "
             GROUP BY cs.student_id"
        );
        return $query->result_array();
    }

    public function lesson_subjects(DateTime $date, bool $include_other = false) : array
    {
        $year = (int) $date->format('Y');
        $year -= (int) $date->format('m') <= 6 ? 1 : 0;
        $program_year = (($year - 1) % 3) + 1;
        if ($include_other) {
            $this->db->from('lesson_subject');
            $this->db->where_in('program_year', [$program_year, 0]);
            return $this->db->get()->result_array();            
        }
        return $this->db->get_where('lesson_subject', ['program_year' => $program_year])->get()->result_array();        
    }

    public function all_lesson_subjects() : array
    {
        return $this->db->get('lesson_subject')->result_array();        
    }

    public function lesson_teachers(int $lesson_id) : ?array
    {
        $query = "SELECT u.first_name, u.last_name
                  FROM `course_teacher` as ct
                  LEFT JOIN `user` u ON ct.teacher_id = u.user_id
                  WHERE ct.lesson_id = " . (int) $lesson_id;
        $query = $this->db->query($query);
        return $query->result_array();
    }

    public function clear_cache()
    {
        $this->output->set_header('Cache-Control: no-store, no-cache, must-revalidate, post-check=0, pre-check=0');
        $this->output->set_header('Pragma: no-cache');
    }

    public function approve_creation($date)
    {
        $date = new DateTime($date);
        $now = (new \DateTime())->modify('-1 day');
        if($date < $now) {
            return false;//false
        }
        return true;
    }

    public function approve_suppression($date)
    {
        $date = (new \DateTime($date))->modify('+1 month');
        //return $date;
        $now = (new \DateTime())->modify('+1 day');
        if($date < $now) {
            return false;
        }
        return true;
    }

    /**
     * Get lessons of a course with teachers
     *
     * @param integer $course_id
     * @param boolean $include_archived
     * @return array
     */
    public function lessons_with_teachers(int $course_id, bool $include_archived = false) : array
    {
        $query = "SELECT cs.*, c.CourseLanguage, c.CourseName, 
                         GROUP_CONCAT(
                            DISTINCT(
                                IF (ct.id,
                                    JSON_OBJECT(
                                        'teacher_lesson_id', ct.id,
                                        'teacher_id', ct.teacher_id,
                                        'first_name', u.first_name,
                                        'last_name', u.last_name,
                                        'role', ct.teacher_role_id
                                    ),
                                    null)
                            )
                        ) AS teachers
                  FROM `course_scheduled_new` as cs
                  LEFT JOIN `course_new` c ON cs.CourseID = c.ID
                  LEFT JOIN `course_student_new` cst ON cs.ID = cst.lesson_id
                  LEFT JOIN `course_teacher` ct ON cs.ID = ct.lesson_id
                  LEFT JOIN `user` u ON ct.teacher_id = u.user_id
                  WHERE cs.CourseID = " . (int) $course_id . " ";
        if($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }

        $query .= " GROUP BY cs.ID
                    ORDER BY LessonDate ASC";
        $query = $this->db->query($query);

        return $query->result_array();
    }

    /**
     * Get lessons of a course with students
     *
     * @param integer $course_id
     * @param boolean $include_archived
     * @return array
     */
    public function lessons_with_students(int $course_id, bool $include_archived = false) : array
    {
        $query = "SELECT cs.*, c.CourseLanguage, c.CourseName, 
                         GROUP_CONCAT(
                            DISTINCT(
                                IF (u.user_id,
                                    JSON_OBJECT(
                                    'student_id', u.user_id,
                                    'first_name', u.first_name,
                                    'last_name', u.last_name
                                    )
                                    ,
                                    null
                                )
                            )
                        ) AS students
                  FROM `course_scheduled_new` as cs
                  LEFT JOIN `course_new` c ON cs.CourseID = c.ID
                  LEFT JOIN `course_student_new` cst ON cs.ID = cst.lesson_id
                  LEFT JOIN `user` u ON cst.student_id = u.user_id
                  WHERE cs.CourseID = " . (int) $course_id . " ";
        if($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }

        $query .= " GROUP BY cst.lesson_id
                    ORDER BY LessonDate ASC";
        $query = $this->db->query($query);

        return $query->result_array();
    }

    public function lessons_with_presences(int $course_id = null, int $teacher_id = null, array $students_ids = null, bool $include_archived = false) : array
    {
        $query = "SELECT cs.*, ls.name_fr, ls.name_nl, ls.name_en, ls.program_year, c.CourseLanguage, c.CourseName, ct.teacher_role_id,
                    (SUM(CASE WHEN cst.status_id = 0 OR cst.status_id IS NULL THEN 1 END) >= COUNT(cst.ID)) as completed_presence,
                    COUNT(cst.ID) as count_student,
                    GROUP_CONCAT(u.first_name, ' ', u.last_name, ',') as teachers,
                    GROUP_CONCAT(
                            DISTINCT(
                                IF (ct.id,
                                    JSON_OBJECT(
                                        'teacher_lesson_id', ct.id,
                                        'teacher_id', ct.teacher_id,
                                        'first_name', u.first_name,
                                        'last_name', u.last_name,
                                        'role', ct.teacher_role_id
                                    ),
                                    null)
                            )
                        ) AS teachers_json
                  FROM `course_scheduled_new` as cs
                  LEFT JOIN `course_new` c ON cs.CourseID = c.ID
                  LEFT JOIN `course_student_new` cst ON cs.ID = cst.lesson_id
                  LEFT JOIN `lesson_subject` ls ON cs.LessonSubjectId = ls.id
                  LEFT JOIN `course_teacher` ct ON cs.ID = ct.lesson_id
                  LEFT JOIN `user` u ON ct.teacher_id = u.user_id
                  WHERE 1=1 ";
        if (!empty($course_id)) {
            $query .= " AND cs.CourseID = " . (int) $course_id;
        }
        if (!empty($teacher_id)) {
            $query .= " AND ct.teacher_id = " . (int) $teacher_id;
        }
        if (!empty($students_ids)) {
            $query .= " AND cst.student_id IN (" . implode(',', $students_ids) . ")";
        }
        if($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }

        $query .= " GROUP BY cs.ID
                    ORDER BY LessonDate ASC";
        $query = $this->db->query($query);

        return $query->result_array();
    }

    public function check_collision($id, $date, $start, $end)
    {
        $query = $this->db->get_where('course_scheduled_new', array('CourseID' => $id,'LessonDate' => $date));
        $query = $query->result_array();
        if(empty($query)) {
            return false;
        }
        foreach ($query as $row) {
            if($row['LessonStart'] >= $start && $row['LessonEnd'] <= $end) {
                return true;
            } elseif($row['LessonEnd'] > $start) {
                return true;
            } elseif($row['LessonStart'] < $end) {
                return true;
            }
        }
        return false;
    }
}