<?php

class Student_model extends CI_Model
{
    public function get_entry(int $id, bool $add_user = false): stdClass
    {
        if ($id <= 0) {
            return null;
        }
        $result = $this->db->get_where('student', ['student_id' => $id])->result();
        if (!empty($result) && !empty($result[0])) {
            if ($add_user) {
                $this->load->model('users_model');
                $result[0]->user = $this->users_model->get_entry($id);
            }
            return $result[0];
        }

        return null;
    }

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

    public function get_students_with_courses()
    {
        $query = "SELECT u.user_id, u.email, u.first_name, u.last_name, u.LegitCreationDate,
                    s.parent_id,s.dob, p.first_name as parent_first_name, p.last_name as parent_last_name,
                    GROUP_CONCAT(DISTINCT course_id SEPARATOR ',') as courses_ids
                FROM user u
                INNER JOIN student s ON u.user_id = s.student_id
                LEFT JOIN user p ON s.parent_id = p.user_id
                LEFT JOIN course_student_new AS cs ON cs.student_id = u.user_id
                WHERE (u.Archived = 0 or u.Archived = 2) AND u.role_id = 4
                GROUP BY u.user_id";

        $students = $this->db->query($query)->result();

        $query = "SELECT CourseName, ID FROM course_new";
        $courses = [];
        foreach ($this->db->query($query)->result() as $c) {
            $courses[(int) $c->ID] = $c;
        }

        foreach ($students as $s) {
            $courses_ids = explode(',', $s->courses_ids);
            $s->courses = [];
            foreach ($courses_ids as $c_id) {
                if (!empty($courses[(int) $c_id])) {
                    $s->courses[] = $courses[(int) $c_id];
                }
            }
        }

        return $students;
    }

    public function get_students_without_invoices(int $limit): array
    {
        $students_raw = $this->db
                             ->query("SELECT u.*, s.parent_id as parent_id, s.admin_comment as admin_comment FROM user u
                                     INNER JOIN student s ON u.user_id = s.student_id
                                     LEFT JOIN invoices_students ist ON ist.student_id = u.user_id                                     
                                     WHERE (u.Archived = 0 or u.Archived = 2) AND u.role_id = 4 AND ist.student_id IS NULL")
                             ->result();
        $students_invoices = [];
        foreach ($this->db->query("SELECT * from invoices_students")->result() as $si) {
            $students_invoices[(int) $si->student_id] = (int) $si->invoice_id;
        }
        $siblings = $this->get_siblings(array_unique(array_map(function ($s) { return $s->parent_id; }, $students_raw)));
        $students = [];
        foreach ($students_raw as $student) {
            if (count($students) >= $limit) {
                continue;
            }
            $no_invoice = true;

            $brothers = $get_siblings[(int) $student->parent_id];
            if (!empty($brothers)) {
                foreach ($brothers as $b) {
                    if ($students_invoices[(int) key($b)]) {
                        $no_invoice[] &= false;
                    }
                }
            }

            // Dirty: We need to create an wallet.balance field for scalability
            $balance = $this->wallet_model->wallet_balance((int) $student->user_id);
            if ($balance < 0) {
                if ($no_invoice) {
                    $students[] = $student;
                }
            }
        }

        if (count($students) > 0) {
            // Add Courses to students
            $students_ids = array_map(function ($s) { return (int) $s->user_id; }, $students);
            $query = "SELECT *, 
                      MIN(csh.LessonDate) as date_start
                      FROM `course_student_new` cs
                      LEFT JOIN `course_new` c ON c.ID = cs.course_id
                      LEFT JOIN course_scheduled_new csh ON csh.CourseID = cs.course_id
                      WHERE cs.student_id IN (" . implode(',', $students_ids) . ")
                      GROUP BY cs.course_id";
            $courses = [];
            foreach ($this->db->query($query)->result() as $c) {
                $courses[(int) $c->student_id] = $c;
            }
            foreach ($students as $k => $s) {
                $students[$k]->course = $courses[(int) $s->user_id];
            }
        }
        return $students;
    }

    public function get_siblings(array $parents_ids): array
    {
        $students_siblings = $this->db->query("SELECT u.*, s.parent_id FROM user u
                                               INNER JOIN student s ON u.user_id = s.student_id
                                               WHERE u.role_id = 4 AND s.parent_id IN (". implode(',', $parents_ids) .")")
                                      ->result();
        $brothers = [];
        foreach ($students_siblings as $s) {
            if ((int) $s->parent_id == 0) {
                continue;
            }
            if (!isset($brothers[(int) $s->parent_id])) {
                $brothers[(int) $s->parent_id] = [];
            }
            $brothers[(int) $s->parent_id][] = $s;
        }

        return $brothers;
    }

    public function get_from_parent_id(int $parent_id): array
    {
        $childrens = $this->db->query("SELECT u.*, s.parent_id FROM user u
                                      INNER JOIN student s ON u.user_id = s.student_id
                                      WHERE u.role_id = 4 AND s.parent_id = $parent_id")
                              ->result();
        return $childrens;
    }

    public function get_truant_students(DateTime $course_begin_before, int $min_percent_missing, int $limit): array
    {
        $query = "SELECT cst.student_id, cst.course_id, cst.lesson_id, su.first_name, su.last_name,
                         su.email AS student_email, pu.email AS parent_email,
                  (SUM(CASE WHEN cst.status_id = 3 THEN 1 END) / SUM(CASE WHEN cst.status_id = 1 OR cst.status_id = 3 THEN 1 END) ) as percentage_missing_presence
                  FROM `course_student_new` cst
                  LEFT JOIN `course_scheduled_new` cs ON cs.ID = cst.lesson_id
                  LEFT JOIN `course_new` c ON cs.CourseID = c.ID
                  LEFT JOIN `student` st ON st.student_id = cst.student_id
                  LEFT JOIN `parent` p ON p.parent_id = st.parent_id
                  INNER JOIN user su ON su.user_id = cst.student_id
                  INNER JOIN user pu ON pu.user_id = st.parent_id
                  WHERE c.Archived != 1 AND cst.status_id IN (1,3)
                  GROUP BY cst.student_id
                  HAVING percentage_missing_presence IS NOT NULL AND percentage_missing_presence > 0.5
                  ORDER BY cs.LessonDate DESC";

        $students = [];
        $course_ids = [];

        foreach ($this->db->query($query)->result() as $student) {
            $students[(int) $student->student_id] = $student;
            $course_ids[] = (int) $student->course_id;
        }

        $query = "SELECT *, COUNT(cst.ID) as students_count, MIN(LessonDate) as date_start
                  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.CourseID IN (". implode(',', array_unique($course_ids)) .")
                  GROUP BY cs.ID
                  HAVING date_start < CAST('{$course_begin_before->format('Y-m-d')}' AS DATE)";

        $course_students = [];
        foreach ($this->db->query($query)->result() as $course_scheduled) {
            $course_students[(int) $course_scheduled->ID] = $course_scheduled;
        }

        foreach ($students as $k => $student) {
            if (empty($course_students[(int) $student->course_id]) || (int) $course_students[(int) $student->course_id]->students_count < 2) {
                unset($students[$k]);
            } else {
                if (empty($students[$k]->courses)) {
                    $students[$k]->courses = [];
                }
                $students[$k]->courses[] = $course_students[(int) $student->course_id];
            }
        }

        return array_slice($students, 0, $limit);
    }

    public function is_assigned_to_lesson(int $student_id, int $course_id, int $lesson_id) : bool
    {
        $this->db->select('id');
        $this->db->where([
            'student_id' => $student_id,
            'course_id'  => $course_id,
            'lesson_id'  => $lesson_id
        ]);
        $lessons_assigned = $this->db->get('course_student_new')->result();

        return count($lessons_assigned) > 0;
    }
}