<?php

/**
* @TODO: Uniformiser la table course_new (rename en course, snake_case des champs, ID => id)
*/

class Course_model extends CI_Model
{
    public const HOLIDAY_COURSE_ID = 1004;

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

    public function get_entries(array $ids): array
    {
        if (empty($ids)) {
            return null;
        }
        $this->db->from('course_new');
        $this->db->where_in('ID', $ids);
        $query =  $this->db->get();
        return $query->result();
    }

    public function get_entries_with_products(array $ids): array
    {
        if (empty($ids)) {
            return [];
        }

        // Only int values and remove empty
        $ids = array_filter(array_filter($ids, 'intval'));

        $query = "SELECT c.*, cp.Course_Title, cp.Course_Sub_Title, cp.Course_Description FROM `course_new` c
                  LEFT JOIN `course_product` cp ON cp.Course_ID = c.ID
                  WHERE c.id IN (" . implode(',', $ids) . ")";
        $courses = $this->db->query($query)->result();

        // Add quantity !
        foreach (array_count_values($ids) as $c_id => $quantity) {
            $course = current(array_filter($courses, fn ($c) => (int) $c->ID === (int) $c_id));
            $c2 = array_filter($courses, fn ($c) => (int) $c->ID === (int) $c_id);
            for ($n = 1; $n < $quantity; $n++) {
                $courses[] = $course;
            }
        }

        return $courses;
    }

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

    public function get_all_entries_with_products(bool $include_archived = false): array
    {
        $query = "SELECT * FROM `course_new` c
                  LEFT JOIN `course_product` cp ON cp.Course_ID = c.ID";
        if ($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }
        $courses = $this->db->query($query)->result_array();

        return $courses;
    }

    public function get_courses_by_students_ids(array $students_ids, $include_archived = false): array
    {
        if (empty($students_ids)) {
            return [];
        }

        $query = "SELECT c.* FROM `course_new` c
                  LEFT JOIN `course_student_new` cs ON cs.course_id = c.ID
                  WHERE cs.student_id IN (" . implode(',', $students_ids) . ")";
        if ($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }
        $query .= " GROUP BY c.ID";

        $courses = $this->db->query($query)->result();
        return $courses;
    }

    public function get_courses_by_student_id(int $student_id, $include_archived = false): array
    {       
        $query = "SELECT c.* FROM `course_new` c
                  LEFT JOIN `course_student_new` cs ON cs.course_id = c.ID
                  WHERE cs.student_id = $student_id";
        if ($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }
        $query .= " GROUP BY c.ID";

        $courses = $this->db->query($query)->result();
        return $courses;
    }

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

    public function course_languages(): array
    {
        $results = $this->db->get('course_language')->result();
        return $results;
    }

    public function listing_all_courses(string $filter = null, int $society = null, bool $include_archived = false, bool $include_masked = true): array
    {
        $list_filters = [
            'no-student' => null, 'student' => null, 'chom-all' => 'all',
            'chom-t' => 1, 'chom-m' => '2', 'chom-v' => 5, 'ext' => 6, 'skype' => 9
        ];
        $query = "SELECT c.*, cl.color
                  FROM `course_new` c
                  LEFT JOIN course_local cl ON c.CourseLocal = cl.course_local_id
                  WHERE 1 = 1";
        if ($include_archived === false) {
            $query .= ' AND c.Archived != 1';
        }
        if ($include_masked === false) {
            $query .= ' AND c.masked != 1';
        }
        if ($society !== null) {
            $query .= " AND CourseSociety = " . (int) $society . " ";
        }
        if ($filter && !empty($list_filters[$filter])) {
            $query .= " AND CourseLocal = \"" . $list_filters[$filter] . "\" ";
        }
        $query .= " GROUP BY c.ID";
        $courses = $this->db->query($query)->result();

        return $courses;
    }

    public function get_stats_courses_scheduled(array $course_ids): array
    {
        if (empty($course_ids)) {
            return [];
        }
        $course_list_ids = implode(',', $course_ids);
        $query = $this->db->query(
            "SELECT 
                CourseId,
                MIN(LessonDate) as date_start,
                MAX(LessonDate) as date_end,
                SUM(CASE WHEN LessonDate < NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as given_seconds,
                SUM(CASE WHEN LessonDate > NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as remain_seconds
            FROM `course_scheduled_new`
            WHERE CourseId IN ($course_list_ids)
            GROUP BY CourseId"
        );
        $course_scheduled = $query->result();
        $course_tree = [];

        foreach ($course_scheduled as $cs) {
            $course_tree[(int) $cs->CourseId] = [
                'date_start' => $cs->date_start,
                'date_end' => $cs->date_end,
                'given_hours' => (int)($cs->given_seconds / 3600),
                'remain_hours' => (int)($cs->remain_seconds / 3600),
            ];
        }
        return $course_tree;
    }

    /**
     * Get all lessons between interval for planning pages
     * For administrators
     *
     * @param DateTime $from
     * @param DateTime $to
     * @param array $course_local_ids
     * @param string|null $search
     * @return array
     */
    public function get_planning_courses(DateTime $from, DateTime $to, array $course_local_ids = [], string $search = null): array
    {
        $sql = "SELECT *, clang.code as lang_code, cs.ID as lesson_id, 
                COUNT(DISTINCT(cst.student_id)) as students_count
                FROM `course_scheduled_new` cs
                LEFT JOIN `course_new` c ON c.ID = cs.CourseID
                LEFT JOIN `course_local` cl ON cl.course_local_id = c.CourseLocal
                LEFT JOIN `course_language` clang ON clang.course_language_id = c.CourseLanguage
                LEFT JOIN `course_student_new` cst ON cst.course_id = c.ID
                WHERE LessonDate BETWEEN CAST('{$from->format('Y-m-d')}' AS DATE) AND CAST('{$to->format('Y-m-d')}' AS DATE) ";

        if (!empty($course_local_ids)) {
            $sql .= "AND c.CourseLocal IN (" . implode(',', $course_local_ids) . ") ";
        }
        if (!empty($search)) {
            $sql .= "AND c.CourseName LIKE '%{$this->db->escape_like_str($search)}%' ";
        }
        $sql .= "GROUP BY cs.ID";

        $courses = $this->db->query($sql)->result();
        $courses_planning = [];

        $courses_ids = array_unique(array_map(fn ($c): int => (int) $c->CourseID, $courses));
        $teachers_courses = $this->get_courses_teachers($courses_ids);

        foreach ($courses as $course) {
            $teachers_names = "";
            if ($teachers_courses[(int) $course->CourseID]) {
                $teachers_names = implode(', ', array_map(fn ($t) => $t['last_name'] . ' ' . $t['first_name'], $teachers_courses[(int) $course->CourseID]));
            }

            $duration = (strtotime($course->LessonEnd) - strtotime($course->LessonStart)) / 3600;
            $description = "[$course->CourseName]\nTeacher(s): $teachers_names\nClassroom: $course->code\n"
                         . "Student nb: $course->students_count\nDuration: $duration H\n[$c->lang_code]";

            $courses_planning[] = [
                'id' => $course->CourseID,
                'groupeId' => $course->lesson_id,
                'title' => $course->CourseName,
                'description' => $description,
                'courseId' => $course->CourseID,
                'color' => $course->color,
                'teachers' => $teachers_courses[(int) $course->CourseID],
                'start' => $course->LessonDate . "T" . $course->LessonStart,
                'end' => $course->LessonDate . "T" . $course->LessonStart,
                'done' => $course->LessonDone,
                'comment' => $course->LessonComment,
            ];
        }

        return $courses_planning;
    }

    public function get_courses_students(array $course_ids): array
    {
        if (empty($course_ids)) {
            return [];
        }
        $course_list_ids = implode(',', $course_ids);
        $query = $this->db->query(
            "SELECT cs.course_id, cs.student_id, u.last_name, u.first_name, u.dob, u.email
            FROM `course_student_new` cs
            LEFT JOIN user u ON cs.student_id = u.user_id
            WHERE cs.course_id IN ($course_list_ids)
            GROUP BY cs.course_id, cs.student_id"
        );
        $courses_students = $query->result();
        $courses_students_tree = [];

        $this->load->model('wallet_model');
        $students_ids = array_unique(array_map(fn ($c) => $c->student_id, $courses_students));
        $students_wallets = $this->wallet_model->students_wallets($students_ids);

        foreach ($courses_students as $cs) {
            $courses_students_tree[(int) $cs->course_id][] = [
                'id' => $cs->student_id,
                'last_name' => $cs->last_name,
                'first_name' => $cs->first_name,
                'birthdate' => $cs->dob,
                'email' => $cs->email,
                'total_payment' => $students_wallets[(int) $cs->student_id]->total_payment,
                'discount' => $students_wallets[(int) $cs->student_id]->total_discount,
                'total_courses_purchased' => 0.0
            ];
        }
        return $courses_students_tree;
    }

    public function get_courses_teachers(array $course_ids): array
    {        
        if (empty($course_ids)) {
            return [];
        }
        $this->load->model('teacher_model');

        $course_list_ids = implode(',', $course_ids);
        $query = $this->db->query(
            "SELECT ct.course_id, ct.teacher_id, ct.teacher_role_id, u.last_name, u.first_name, u.dob, u.email,
                GROUP_CONCAT(DISTINCT ct.teacher_role_id ORDER BY ct.teacher_role_id SEPARATOR ',') AS role_ids,
                IF (MAX(cs.lessonDate) >= NOW(), TRUE, FALSE) as have_future_lesson
            FROM `course_teacher` ct
            LEFT JOIN course_scheduled_new cs ON ct.lesson_id = cs.ID
            LEFT JOIN user u ON ct.teacher_id = u.user_id
            WHERE ct.course_id IN ($course_list_ids)
            GROUP BY ct.course_id, ct.teacher_id"
        );

        $courses_teachers = $query->result();
        $courses_teachers_tree = [];

        foreach ($courses_teachers as $ct) {
            $role_id = $this->compute_main_role(explode(',', $ct->role_ids));
            $courses_teachers_tree[(int) $ct->course_id][] = [
                'id' => $ct->teacher_id,
                'last_name' => $ct->last_name,
                'first_name' => $ct->first_name,
                'role_id' => (int) $ct->teacher_role_id,
                'role' => Teacher_model::TEACHER_ROLES[(int) $role_id],
                'have_future_lessons' => (bool) $ct->have_future_lesson,
                'birthdate' => $ct->dob,
                'email' => $ct->email
            ];
        }
        return $courses_teachers_tree;
    }

    public function get_incoming_courses(DateTime $from, DateTime $to, int $limit): array
    {
        if ($from >= $to) {
            return [];
        }

        $query = $this->db->query(
            "SELECT CourseID, MIN(LessonDate) as date_start, LessonDate, LessonStart FROM `course_scheduled_new`
                GROUP BY CourseID
                HAVING date_start BETWEEN CAST('{$from->format('Y-m-d')}' AS DATE) AND CAST('{$to->format('Y-m-d')}' AS DATE)
                ORDER BY `date_start` DESC
                LIMIT $limit"
        );
        $lessons_start = [];
        foreach ($query->result() as $lesson) {
            $lessons_start[(int) $lesson->CourseID] = $lesson;
        }
        if (empty($lessons_start)) {
            return [];
        }

        $query = $this->db->query(
            "SELECT CourseName, c.ID,
             COUNT(DISTINCT(cst.student_id)) as students_count,
             COUNT(DISTINCT(ct.teacher_id)) as teachers_count
             FROM `course_new` as c
            LEFT JOIN course_student_new cst ON cst.course_id = c.ID
            LEFT JOIN course_teacher ct ON ct.course_id = c.ID
            WHERE c.ID IS NOT NULL AND c.ID > 0 AND c.ID != " . Course_model::HOLIDAY_COURSE_ID . " AND
                c.ID IN (" . implode(',', array_keys($lessons_start)) . ")
            GROUP BY c.ID"
        );
        $courses_scheduled = $query->result();
        foreach ($courses_scheduled as &$c) {
            if (!empty($lessons_start[(int) $c->ID])) {
                $c->lesson_begin = new DateTime($lessons_start[(int) $c->ID]->LessonDate . $lessons_start[(int) $c->ID]->LessonStart);
            }
        }

        usort($courses_scheduled, function ($a, $b) {
            return $a->lesson_begin <=> $b->lesson_begin;
        });

        return $courses_scheduled;
    }

    public function get_incoming_courses_without_teachers(DateTime $from, DateTime $to, int $limit): array
    {
        if ($from >= $to) {
            return [];
        }
        $query = $this->db->query(
            "SELECT c.CourseName, c.ID, cs.`LessonDate`, cs.`LessonStart`,
                COUNT(DISTINCT(cst.student_id)) as students_count
             FROM `course_scheduled_new` as cs
             LEFT JOIN course_new c ON cs.CourseID = c.ID
             LEFT JOIN course_teacher ct ON ct.lesson_id = cs.ID
             LEFT JOIN course_student_new cst ON cst.lesson_id = cs.ID
             LEFT JOIN teacher t ON t.teacher_id = ct.teacher_id
             WHERE t.teacher_id IS NULL
             AND cst.id IS NOT NULL
             AND cs.`LessonDate` BETWEEN CAST('{$from->format('Y-m-d')}' AS DATE) AND CAST('{$to->format('Y-m-d')}' AS DATE)
             AND c.ID != " . Course_model::HOLIDAY_COURSE_ID . "
             GROUP BY cs.CourseID
             ORDER BY cs.`LessonDate`, cs.`LessonStart`
             LIMIT $limit"
        );

        $courses_without_teacher = $query->result();
        return $courses_without_teacher;
    }

    public function get_courses_ending_soon(int $max_hours_remaining): array
    {
        $query = $this->db->query(
            "SELECT c.*,
                MIN(cs.LessonDate) as date_start,
                MAX(cs.LessonDate) as date_end,
                SUM(CASE WHEN LessonDate < NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as given_seconds
            FROM `course_scheduled_new` cs
            LEFT JOIN course_new as c ON c.ID = cs.CourseId
            WHERE cs.CourseId != " . Course_model::HOLIDAY_COURSE_ID . " AND c.Archived = 0
            GROUP BY cs.CourseId  
            ORDER BY given_seconds ASC"
        );
        $courses_raw = $query->result();
        $courses = [];

        foreach ($courses_raw as $c) {
            if ($c->ID === null) {
                continue;
            }

            $c->remain_hours = (float) $c->CourseContract - (float)($c->given_seconds / 3600);
            if ($c->remain_hours <= 0 || $c->remain_hours > $max_hours_remaining) {
                continue;
            }
            $courses[] = $c;
        }
        // Sort by remaining hours
        usort($courses, function ($a, $b) {
            return $a->remain_hours <=> $b->remain_hours;
        });

        return $courses;
    }

    public function get_courses_available_to_archive(int $limit): array
    {
        $expire_date = (new DateTime('now'))->modify('-6 months');
        $query = $this->db->query(
            "SELECT c.*,
                MIN(cs.LessonDate) as date_start,
                MAX(cs.LessonDate) as date_end,
                SUM(CASE WHEN LessonDate < NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as given_seconds
            FROM `course_scheduled_new` cs
            LEFT JOIN course_new as c ON c.ID = cs.CourseId
            WHERE cs.CourseId != " . Course_model::HOLIDAY_COURSE_ID . " AND c.Archived = 0
            GROUP BY cs.CourseId
            HAVING date_end < CAST('{$expire_date->format('Y-m-d')}' AS DATE) AND (given_seconds / 3600) >= c.CourseContract
            ORDER BY date_end ASC
            LIMIT $limit"
        );
        $courses = $query->result();

        return $courses;
    }

    public function get_courses_overtime(int $limit): array
    {
        $expire_date = (new DateTime('now'))->modify('-1 year');
        $query = $this->db->query(
            "SELECT c.*,
                SUM(CASE WHEN LessonDate < NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as given_seconds,
                SUM(CASE WHEN LessonDate > NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END) as planned_seconds
            FROM `course_new` c
            LEFT JOIN course_scheduled_new as cs ON cs.CourseID = c.ID
            WHERE c.CourseContract > 0 AND c.Archived = 0
            GROUP BY cs.CourseId"
        );
        $courses_raw = $query->result();
        $courses_overtime = [];

        foreach ($courses_raw as $course) {
            $total_hours = ((float) $course->given_seconds / 3600) + ((float) $course->planned_seconds / 3600);
            if ($limit <= 0 || (float) $course->CourseContract >= $total_hours) {
                continue;
            }
            $limit--;
            $courses_overtime[] = $course;
        }

        return $courses_overtime;
    }

    public function get_courses_stopped(DateTime $before_at, int $limit): array
    {
        $query = $this->db->query(
            "SELECT c.*,
                SUM(CASE WHEN cs.LessonDate < NOW() THEN TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart)) ELSE 0 END)/3600 as given_hours,
                MAX(cs.LessonDate) as date_end
             FROM `course_new` c
             LEFT JOIN course_scheduled_new as cs ON cs.CourseID = c.ID
             WHERE cs.CourseId != " . Course_model::HOLIDAY_COURSE_ID . " AND c.CourseContract > 0 AND c.Archived = 0
             GROUP BY cs.CourseId
             HAVING MAX(cs.LessonDate) < CAST('{$before_at->format('Y-m-d')}'  AS DATE) AND (SUM(TIME_TO_SEC(TIMEDIFF(LessonEnd, LessonStart))) / 3600) < c.CourseContract
             LIMIT $limit"
        );
        $courses_stopped = $query->result();

        return $courses_stopped;
    }

    public function get_courses_missing_presences(DateTime $after_at, int $max_percent_missing, int $limit): array
    {
        $yesterday = (new DateTime('now'))->modify('-1 day');
        $query = $this->db->query(
            "SELECT *,
            (SUM(CASE WHEN cst.status_id = 0 OR cst.status_id IS NULL THEN 1 END) / COUNT(cst.ID)) as percentage_missing_presence,
            COUNT(cst.ID) as students_count
            FROM `course_scheduled_new` cs
            LEFT JOIN `course_new` c ON cs.CourseID = c.ID
            LEFT JOIN `course_student_new` cst ON cs.ID = cst.lesson_id
            WHERE c.Archived != 1 AND cs.LessonDate BETWEEN CAST('{$after_at->format('Y-m-d')}' AS DATE) AND CAST('{$yesterday->format('Y-m-d')}' AS DATE)
            GROUP BY cs.ID
            HAVING students_count > 1 AND percentage_missing_presence IS NOT NULL
            ORDER BY cs.LessonDate DESC"
        );

        $courses_temp = [];
        $courses = [];
        foreach ($query->result() as $lesson) {
            if (count($courses_temp) > $limit) {
                break;
            }
            if (empty($courses_temp[(int) $lesson->CourseID])) {
                $courses_temp[(int) $lesson->CourseID] = [];
            }
            $courses_temp[(int) $lesson->CourseID][] = $lesson;
        }
        // Calculate presences
        foreach ($courses_temp as $course) {
            $lessons_presence = array_map(function ($l): float {
                return (float) $l->percentage_missing_presence;
            }, $course);

            if (!empty($lessons_presence) && (array_sum($lessons_presence) / count($lessons_presence)) >= 0.50) {
                $courses[(int) $course[0]->CourseID] = end($course);
                $courses[(int) $course[0]->CourseID]->percentage_missing_presence_total = array_sum($lessons_presence) / count($lessons_presence);
            }
        }

        $teachers_courses = $this->get_courses_teachers(array_keys($courses));
        foreach ($courses as &$course) {
            $course->teachers = $teachers_courses[$course->CourseID];
        }

        return $courses;
    }

    public function count_courses_this_week(): int
    {
        $date_start = (new DateTime('now'))->modify('-7 days');
        $query = $this->db->query(
            "SELECT COUNT(ID) as num FROM `course_scheduled_new`
            WHERE LessonDate BETWEEN CAST('{$date_start->format('Y-m-d')}' AS DATE) AND NOW()
            AND CourseID != " . Course_model::HOLIDAY_COURSE_ID . ""
        );
        $result = $query->result();
        return (int) $result[0]->num;
    }

    public function count_courses_this_month(): int
    {
        $date_start = (new DateTime('now'))->modify('-31 days');
        $query = $this->db->query(
            "SELECT COUNT(ID) as num FROM `course_scheduled_new`
            WHERE LessonDate BETWEEN CAST('{$date_start->format('Y-m-d')}' AS DATE) AND NOW()
            AND CourseID != " . Course_model::HOLIDAY_COURSE_ID
        );
        $result = $query->result();
        return (int) $result[0]->num;
    }

    private function compute_main_role(array $role_ids) : int
    {
        if (in_array('2', $role_ids)) {
            return 2;
        } else if (in_array('0', $role_ids)) {
            return 0;
        } else if (in_array('3', $role_ids)) {
            return 3;
        } else if (in_array('1', $role_ids)) {
            return 1;
        }

        return 0;
    }
}