<?php

use app\libraries\CommonHelper;
use app\libraries\DateHelper;
use app\libraries\Tenants;
use app\libraries\Userstamp;

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


/**
 * @property Event_makeup_quota_model $Event_makeup_quota_model
 */
class Event_enrollment_item_model extends MY_Model
{

    /* mas_event_evrollment_item - txn_status
		W - 未開始
		M - 調堂 (舊記錄)
		R - 調堂 (新記錄)
		P - 出席
		A - 缺席
		H - 請假

		未完成 - W + R
		已完成 - P + A + H
	*/

    use Tenants;

    const STATUS_STUDENT_PRESENT = 'P';
    const STATUS_STUDENT_LATE = 'L';
    const STATUS_STUDENT_NO_CALL_NO_SHOW = 'A';
    const STATUS_STUDENT_ABSENT = 'A';
    const STATUS_STUDENT_EARLY_LEAVE = 'E';

    /**
     * Student has taken leave
     */
    const STATUS_STUDENT_HOLIDAY_AND_SKIP = 'H';

    const STATUS_COURSE_WAITING_TO_START = 'W';
    const STATUS_STUDENT_NOT_MARKED = 'N';
    /**
     * 已調堂
     */
    const STATUS_OPTIONAL_COURSE_MAKEUP_AND_RESCHEDULED = "M";
    /**
     * 學生重新點堂成功。
     */
    const STATUS_STUDENT_RETAKE_MAKEUP_COURSE = 'R';
    /**
     * 課堂取消成功後先安排調堂，不行要refund。(教師)
     */
    const STATUS_COURSE_CANCELLED = 'C';
    /**
     * 學生沒有交費，教師不要他了，除非有付費。(not in use)
     */
    const STATUS_STUDENT_UNPAID_COURSE_SUSPENDED = 'U';
    /**
     * 系統調堂成功。
     */
    const STATUS_SKIPPED_COURSE = 'S';

    /**
     * 已取消調堂的記號。
     */
    const STATUS_TERMINATED_COURSE_MAKEUP_BY_REVERSAL = 'T';
    /**
     * 學生沒有在一個月內繳交費用而須退學，課堂不能上。
     */
    const STATUS_STUDENT_DROPPED = 'D';

    /**
     * 已調堂
     */
    const STATUS_CANCEL_OPTIONAL_COURSE_MAKEUP_AND_RESCHEDULED = "CM";
    /**
     * 學生重新點堂成功。
     */
    const STATUS_CANCEL_STUDENT_RETAKE_MARKUP_COURSE = 'CR';

    const STATUS_CANCEL_SKIPPED_COURSE = 'CS';
    const STATUS_CANCEL_COURSE_WAITING_TO_START = 'CW';

    public $short = "ei";
    public $table;
    public $tbl;
    public $fullTbl;
    public $fullTable;

    //    protected $table = 'mas_event_enrollment_item';
    public function __construct()
    {
        parent::__construct();
        $this->load->model('Event_model');
        $this->load->model('Room_model');
        $this->load->model('Teacher_model');
        $this->load->model('Course_model');
        $this->load->model('Student_model');
        $this->load->model('Event_timeslot_model');
        $this->load->model('Event_enrollment_model');
        $this->load->model('Invoicing_model');
        $this->load->model('Event_makeup_quota_model');
        $this->database = $this->getTenantNo();
        $this->tbl = 'event_enrollment_item';
        $this->table = $this->tbl . ' ' . $this->short;
        $this->fullTbl = $this->table_prefix . $this->tbl;
        $this->fullTable = $this->table_prefix . $this->table;
    }

    public static function activeStatuses()
    {
        return [
            self::STATUS_STUDENT_PRESENT,
            self::STATUS_STUDENT_LATE,
            self::STATUS_STUDENT_NO_CALL_NO_SHOW,
            self::STATUS_STUDENT_ABSENT,
            self::STATUS_STUDENT_EARLY_LEAVE,
            self::STATUS_COURSE_WAITING_TO_START,
            self::STATUS_STUDENT_NOT_MARKED,
            self::STATUS_STUDENT_RETAKE_MAKEUP_COURSE,
            self::STATUS_STUDENT_UNPAID_COURSE_SUSPENDED
        ];
    }

    public function one($id)
    {
        return $this->getOne($this->database, $this->tbl, [], $id);
    }

    public function listing()
    {
        $this->tenant();
        return $this->getAll($this->database, $this->tbl);
    }

    public function listing_in_event_id($event_id)
    {
        $this->tenant();
        $this->db = $this->load->database($this->database, TRUE);
        return $this->db->from($this->table_prefix . $this->tbl)->where('event_id', $event_id)->get()->result_array();
    }

    /**
     * Proved workable on group_by, sql_mode=only_full_group_by
     */
    public function listing_in_event_attendance($eventId, $timeslotId)
    {
        $this->db = $this->load->database($this->database, TRUE);

        $subquery = $this->db->from($this->fullTable)
            ->where('event_id', $eventId)
            ->where('event_timeslot_id', $timeslotId)
            ->group_by('event_timeslot_id, student_id')
            ->select('MAX(id)', null, false)
            ->get_compiled_select();

        $res = $this->db->from($this->fullTable)
            ->join($this->table_prefix . 'student s', 'ei.student_id = s.id')
            ->select('ei.*, s.chi_name as student_chi_name')
            ->where('ei.id IN (' . $subquery . ')', null, false)
            ->where('ei.status', 1)
            ->get()
            ->result_array();

        return $res;
    }

    public function listing_in_student_class_record($studentId, $future)
    {
        $this->db = $this->load->database($this->database, TRUE);

        $subquery = $this->db->from($this->fullTable)
            // ->where('event_id', $eventId)
            // ->where('event_timeslot_id', $timeslotId)
            ->where('student_id', $studentId)
            ->group_by('event_timeslot_id, student_id')
            ->select('MAX(id)', null, false)
            ->get_compiled_select();

        $this->db->from($this->fullTable)
            ->join($this->table_prefix . 'event_timeslot et', 'ei.event_timeslot_id = et.id')
            // ->where('mas_event_timeslot.startdate > ', $startdate, false)
            ->where('et.holiday_skip IS NULL', null, false)
            // Get event - course relationship
            ->join($this->table_prefix . 'event_course ec', 'ei.event_id = ec.event_id')
            // Get course name
            ->join($this->table_prefix . 'course c', 'ec.course_id = c.id')
            ->join($this->table_prefix . 'event e', 'e.id = ei.event_id')
            ->join($this->table_prefix . 'room r', 'r.id = e.room_id')
            ->join('rbac_users ru', 'e.pic_1 = ru.id')
            ->join($this->table_prefix . 'student s', 'ei.student_id = s.id')
            ->select('
                ei.*, 
                et.startdate as date, 
                et.starttime, 
                et.endtime, 
                et.id as event_timeslot_id, 
                c.name,
                ru.chi_name as teacher_name,
                s.chi_name as student_name,
                r.name as room_name,
                c.name
            ')
            ->where('ei.id IN (' . $subquery . ')', null, false);

        if ($future) {
            $this->db
                ->where_in('ei.txn_status', self::activeStatuses())
                ->where('et.startdate > ', (new DateTime(date('Y-m-d', time())))->getTimestamp(), false);
        }

        $result = $this->db->get()->result_array();

        $res = [];
        foreach ($result as $item) {
            $checkinDatetime = !empty($item['in_datetime']) ? date('H:i', $item['in_datetime']) : '';
            $date = date('Y-m-d', $item['date']);

            // $dates = explode(' ', $date);
            // use date to generate app calendar compatible key
            $key = $date;
            // ZuluString
            // $keys = $dates;
            $calendarDatetime = sprintf('%sT00:00:00Z', $date);
            $key = $calendarDatetime;

            $values = [
                'id' => $item['id'],
                'date' => $date,
                'type' => '上課記錄',
                'time' => sprintf(
                    '%s - %s',
                    DateTime::createFromFormat('H:i', $item['starttime'])->format('H:i'),
                    DateTime::createFromFormat('H:i', $item['endtime'])->format('H:i')
                ),
                'title' => $item['name'] ?? '',
                'checkin' => $checkinDatetime ?? '',
                'teacher_comment' => '',
                'taken_leave_datetime' => $item['taken_leave_datetime'],
                'absent' => $item['absent'],
                'makeup' => $item['txn_status'] == 'M',
                'calendar_datetime' => $calendarDatetime,
            ];

            $this->array_put($res, $key, $values);
        }

        krsort($res);
        return $res;
    }

    public function inactive_enrollment_items()
    {
        $this->db = $this->load->database($this->database, TRUE);

        $items = $this->db->from($this->fullTable)
            ->join('mas_event_timeslot', $this->short . '.event_timeslot_id = mas_event_timeslot.id')
            ->where_not_in('txn_status', self::activeStatuses())
            ->select('event_timeslot_id')
            ->get()
            ->result_array();

        $res = [];

        foreach ($items as $item) {
            $i = $item['event_timeslot_id'];
            $res[$i] = $i;
        }

        return $res;
    }

    public function makeup_listing($studentId, $tenantNo)
    {
        $this->db = $this->load->database($this->database, TRUE);

        $subquery = $this->db->from($this->fullTable)
            ->join('mas_event_timeslot et', $this->short . '.event_timeslot_id = et.id')
            ->where('student_id', $studentId)
            ->where_in('txn_status', ['W', 'R'])
            ->where('et.startdate > ' . (new DateTime(date('Y-m-d')))->getTimestamp(), null, false);

        $subquery2 = clone $subquery;

        if (empty($subquery->get()->row_array())) {
            log_message('debug', $this->db->last_query());
            return [];
        }
        $subquery2 = $subquery2
            ->select($this->short . '.*')
            ->get_compiled_select();

        $this->load->model('Event_makeup_quota_model');
        $this->Event_makeup_quota_model->setTenantNo($tenantNo, true);
        $subquery3 = $this->Event_makeup_quota_model->detail_student_query($studentId);

        $courses = $this->db
            ->from('mas_course')
            ->where('mas_course.status', 1)
            ->join('mas_event_course', 'mas_course.id = mas_event_course.course_id')
            ->join('mas_event_timeslot', 'mas_event_course.event_id = mas_event_timeslot.event_id')
            // show all timeslots
            ->join('(' . $subquery2 . ') item', 'mas_event_timeslot.id = item.event_timeslot_id', 'left')
            ->join('(' . $subquery3 . ') quota', 'item.event_enrollment_id = quota.event_enrollment_id', 'left')
            ->group_start()
            ->group_start()
            ->where('mas_event_timeslot.holiday_skip IS NULL', null, false)
            ->where('mas_course.mode != "period"', null, false)
            ->group_end()
            ->or_where('mas_course.mode = "period"', null, false)
            ->group_end()
            ->where('mas_event_timeslot.startdate > ', time(), false)
            ->where_in('YEAR(FROM_UNIXTIME(mas_event_timeslot.startdate))', $this->threeYearsInterval(), false)
            ->select('
                mas_course.name, 
                mas_course.id as course_id, 
                mas_event_timeslot.event_id as event_id,
                mas_event_timeslot.starttime,
                mas_event_timeslot.endtime,
                item.*,
                item.id as item_id,
                mas_event_timeslot.startdate as date,
                quota.quota,
                mas_event_timeslot.id as event_timeslot_id
            ')
            ->order_by('mas_event_timeslot.id', 'asc')
            ->get()
            ->result_array();


        $res = [];
        $inactiveEnrollmentItems = $this->inactive_enrollment_items();
        $hk = new DateTimeZone('Asia/Hong_Kong');

        $eventTimeslotIds = [];


        $makeupQuotas = [];
        $this->load->model('Event_makeup_quota_model');
        $this->Event_makeup_quota_model->setTenantNo($tenantNo);
        $_makeupQuotas = $this->Event_makeup_quota_model->getAll($this->Event_makeup_quota_model->database, $this->Event_makeup_quota_model->tbl, [], [
            'student_id' => $studentId
        ]);
        foreach ($_makeupQuotas as $_makeupQuota) {
            $makeupQuotas[$_makeupQuota['event_enrollment_id']] = $_makeupQuota;
        }

        foreach ($courses as $item) {
            $timeslotId = $item['event_timeslot_id'];
            if (!empty($eventTimeslotIds[$timeslotId])) continue;
            else $eventTimeslotIds[$timeslotId] = 1;

            if (!empty($inactiveEnrollmentItems[$timeslotId])) {
                continue;
            }

            if ($item['student_id'] != null && $studentId != $item['student_id']) {
                continue;
            }

            // $date = Carbon::createFromTimestamp($item['date'])->timezone('Asia/Hong_Kong')->add(13, 'hour')->format('Y-m-d H:i:s');
            // 'Y-m-d\TH:i:s\Z'
            $checkinDatetime = !empty($item['in_datetime']) ? date('H:i', $item['in_datetime']) : '';

            $date = date('Y-m-d', $item['date']);
            // use date to generate app calendar compatible key
            $key = $date;
            // ZuluString
            // $keys = $dates;
            $calendarDatetime = sprintf('%sT00:00:00Z', $date);
            $key = $calendarDatetime;

            $makeupQuota = $makeupQuotas[$item['event_enrollment_id']] ?? false;
            $makeupDiff = $makeupQuota ? $makeupQuota['initial_quota'] - $makeupQuota['quota'] : 0;
            $makeupRemaining = $makeupQuota ? $makeupQuota['quota'] : 0;
            // EnrollmentItemId
            $itemId = $item['item_id'];
            $values = [
                'item_id' => $itemId,
                'course_id' => $item['course_id'],
                'student_id' => $item['student_id'],
                'timeslot_id' => $timeslotId,
                'date' => $date,
                'type' => '上課記錄',
                'time' => sprintf(
                    '%s - %s',
                    DateTime::createFromFormat('H:i', $item['starttime'])->format('H:i'),
                    DateTime::createFromFormat('H:i', $item['endtime'])->format('H:i')
                ),
                'title' => $item['name'] ?? '',
                'checkin' => $checkinDatetime,
                'teacher_comment' => '',
                'taken_leave_datetime' => $item['taken_leave_datetime'] ?? "",
                'absent' => $item['absent'] ? 1 : 0,
                'makeup' => $item['txn_status'] == 'M' ? 1 : 0,
                'calendar_datetime' => $calendarDatetime,
                'enrolled' => $item['txn_status'] != null ? 1 : 0,
                'quota' => intval($item['quota']) ?? 0,
                'makeup_quota_stats' => sprintf('已使用%s的調堂限額，尚餘%s的調堂限額', $makeupDiff, $makeupRemaining)
            ];

            $this->array_put($res, $key, $values);
        }

        return $res;
    }

    public function listing_in_event_calendar()
    {
        $this->db = $this->load->database($this->database, TRUE);

        $enrollmentItemSubquery = $this->db->from($this->fullTable)
            ->group_by('event_timeslot_id, student_id')
            ->select('MAX(id)', null, false)
            ->get_compiled_select();

        $res = $this->db->from($this->fullTable)
            ->join($this->table_prefix . 'student s', 'ei.student_id = s.id')
            ->select('ei.*, s.chi_name as student_chi_name')
            ->where('ei.id IN (' . $enrollmentItemSubquery . ')', null, false)
            ->where('ei.status', 1) //please don't remove this condition! (this status will become 0 upon void invoices)
            ->get()
            ->result_array();
        //        log_message('debug','sql:'.$this->db->last_query());
        /*
         * SELECT `ei`.*, `mas_student`.`chi_name` as `student_chi_name`
FROM `mas_event_enrollment_item` `ei`
JOIN `mas_student` ON `ei`.`student_id` = `mas_student`.`id`
WHERE ei.id IN (SELECT MAX(id)
FROM `mas_event_enrollment_item` `ei`
GROUP BY `event_timeslot_id`, `student_id`)
         */

        return $res;
    }

    public function detail_in_qrcode_scanned($eventId, $timeslotId, $studentId)
    {
        $this->tenant();
        $db = $this->load->database($this->database, TRUE);
        return $db->from($this->fullTable)
            ->where('event_id', $eventId)
            ->where('event_timeslot_id', $timeslotId)
            ->where('student_id', $studentId)
            ->where('skip', 0)
            ->get()
            ->row_array();
    }

    public function detail_in_event_enrollment($eventId, $timeslotId, $studentId)
    {
        return $this->detail_in_qrcode_scanned($eventId, $timeslotId, $studentId);
    }

    public function detail_in_nextTimeslot($nextTxnId)
    {
        $this->tenant();
        $db = $this->load->database($this->database, TRUE);
        return $db->from($this->fullTable)
            ->join($this->table_prefix . 'event_timeslot et', 'ei.event_timeslot_id = et.id')
            ->where('ei.id', $nextTxnId)
            ->select('et.startdate')
            ->get()
            ->row_array();
    }

    public function save($params, $mode, $id = 0)
    {
        if ($mode === 'c') {
            $id = $this->create($params);
        } else {
            $this->update($params, $id);
        }
        return $id;
    }

    public function create($params)
    {
        $this->tenant();
        // if ($res = $this->detail_in_event_enrollment($params['event_id'], $params['event_timeslot_id'], $params['student_id'])) {
        //     return $res;
        // } else {

        // }
        Userstamp::$timestamp = true;
        $this->db->insert($this->fullTbl, Userstamp::create($params, 'created_by', 'create_time'));
        $params['id'] = $this->db->insert_id();
        return $params;
    }

    public function create_batch($params)
    {
        $this->db = $this->load->database($this->database, true);

        $this->db->insert_batch($this->fullTbl, $params, null);
    }

    /**
     *
     * @param array $params
     * @param array $enrollment
     * @param bool $enrollOrPay         true = event_enroll, false = event_pay
     * @return void
     */
    public function enrollOrPay($params, $enrollment, $enrollOrPay)
    {
        $event = $this->Event_model->detail($enrollment['event_id']);
        $course = $this->Course_model->detail($params['course_id']);
        // $enrollmentId = $enrollment['id'];
        // $studentId = $enrollment['student_id'];
        // $isTrial = $params['is_trial'];
        // $price = $params['price'];
        // $startdate = $enrollment['startdate'];
        $weekdays = [];
        if (!empty($params['ids'])) {
            $enrolledTimeslotIds = explode(',', $params['ids']);
        } else {
            if ($course['mode'] == 'month') {
                if (!empty($params['weekday-sun']))
                    array_push($weekdays, 0);
                if (!empty($params['weekday-mon']))
                    array_push($weekdays, 1);
                if (!empty($params['weekday-tue']))
                    array_push($weekdays, 2);
                if (!empty($params['weekday-wed']))
                    array_push($weekdays, 3);
                if (!empty($params['weekday-thu']))
                    array_push($weekdays, 4);
                if (!empty($params['weekday-fri']))
                    array_push($weekdays, 5);
                if (!empty($params['weekday-sat']))
                    array_push($weekdays, 6);
            }
            $__timeslots = $this->Event_timeslot_model->listing_by_event_id($enrollment['event_id']);
            $enrolledTimeslotIds = [];
            foreach ($__timeslots as $__timeslot) {
                array_push($enrolledTimeslotIds, $__timeslot['id']);
            }
        }

        $skippedTimeslotIds = !empty($params['skipIds']) ? explode(',', $params['skipIds']) : [];
        $timeslots = $this->Event_timeslot_model->listing_in_ids($enrolledTimeslotIds);
        if ($this->trial_enrollment_item_invoicing($params, $course, $enrollment, $timeslots, $weekdays))
            return;
        // $ym = null;
        $max = count($timeslots);
        $counter = 0;
        $firstDateIterated = false;
        $passedSelectedMonth = false;
        $itemDates = [];
        $itemTimeslots = [];
        $selectedMonthTimestamp = !empty($params['selected_month']) ? DateTime::createFromFormat('Y-m-d', $params['selected_month'] . '-01', new DateTimeZone('Asia/Hong_Kong'))->getTimestamp() : null;
        $selectedMonthDate = date('Y-m-d', $selectedMonthTimestamp);

        foreach ($timeslots as $timeslot) {
            $week = intval(date('w', $timeslot['startdate']));
            $timeslotTimestamp = intval($timeslot['startdate']);
            $timeslotDate = date('Y-m-d', $timeslotTimestamp);
            $enrollmentDate = date('Y-m-d H:i:s', $enrollment['first_date']);

            if (!empty($weekdays) && !in_array($week, $weekdays)) {
                continue;
            }
            if ($timeslot['holiday_skip']) {
                continue;
            }

            if (!$firstDateIterated) {
                if (date('Y-m-d', $enrollment['first_date']) <= date('Y-m-d', $timeslotTimestamp)) {
                    $firstDateIterated = true;
                } else {
                    continue;
                }
            }
            // Event_enroll
            if ($enrollOrPay) {
                // Enrollment Item
                $enrollmentItem = $this->create_enrollment_item($enrollment, $timeslot);
                if ($course['mode'] == 'month') {
                    switch (!$passedSelectedMonth) {
                        case $selectedMonthTimestamp > $timeslotTimestamp:
                            break;
                        case $selectedMonthTimestamp <= $timeslotTimestamp:
                            if (date('m', strtotime('+1 month', $selectedMonthTimestamp)) == date('m', $timeslotTimestamp)) {
                                $passedSelectedMonth = true;
                            } else {
                                $counter++;
                                array_push($itemDates, date('Y-m-d', $timeslotTimestamp));
                                $this->push_timeslot($itemTimeslots, $timeslot);
                            }
                            break;
                    }
                } else {
                    $enrollmentItem = $this->create_enrollment_item($enrollment, $timeslot);
                    $counter++;
                    array_push($itemDates, date('Y-m-d', $timeslotTimestamp));
                    $this->push_timeslot($itemTimeslots, $timeslot);
                }
                // Event_pay
            } else {
                if ($course['mode'] == 'month') {
                    switch (true) {
                        case $selectedMonthTimestamp > $timeslotTimestamp:
                            break;
                        case $selectedMonthTimestamp <= $timeslotTimestamp:
                            if (date('m', strtotime('+1 month', $selectedMonthTimestamp)) == date('m', $timeslotTimestamp)) {
                                break 2;
                            } else {
                                $enrollmentItem = $this->create_enrollment_item($enrollment, $timeslot);
                                $counter++;
                                array_push($itemDates, date('Y-m-d', $timeslotTimestamp));
                                $this->push_timeslot($itemTimeslots, $timeslot);
                            }
                            break;
                    }
                } else {
                    $enrollmentItem = $this->create_enrollment_item($enrollment, $timeslot);
                    $counter++;
                    array_push($itemDates, date('Y-m-d', $timeslotTimestamp));
                    $this->push_timeslot($itemTimeslots, $timeslot);
                }
            }
        }
        //invoicing
        $first = $itemDates[0];
        $last = $itemDates[count($itemDates) - 1];
        $firstTimestamp = DateTime::createFromFormat('Y-m-d', $first, new DateTimeZone('Asia/Hong_Kong'))->getTimestamp();
        $lastTimestamp = DateTime::createFromFormat('Y-m-d', $last, new DateTimeZone('Asia/Hong_Kong'))->getTimestamp();
        $range = sprintf('%s - %s', $first, $last);
        $this->create_invoicing($params, $enrollment, $range, $firstTimestamp, $lastTimestamp, $counter, $course['name'], implode('||', $itemTimeslots), 1, $params['price'], $params['discount'], $params['net_amount']);
        $this->Event_makeup_quota_model->create([
            'student_id' => $enrollment['student_id'],
            'event_enrollment_id' => $enrollment['id'],
            'quota' => $counter
        ]);

        if (!empty($skippedTimeslotIds)) {
            foreach ($skippedTimeslotIds as $timeslotId) {
                $this->create_skipped_enrollment_item($enrollment['event_id'], $enrollment['id'], $timeslotId, $enrollment['student_id']);
            }
        }
    }

    protected function push_timeslot(&$array, $timeslot)
    {
        array_push($array, sprintf(
            '%s(%s) %s-%s',
            date('Y-m-d', $timeslot['startdate']),
            DateHelper::getDefaultWeekdays($timeslot['startdate']),
            DateHelper::getAmpm($timeslot['starttime'], false),
            DateHelper::getAmpm($timeslot['endtime'], true, false)
        ));
    }

    protected function create_enrollment_item($enrollment, $timeslot)
    {
        if ($res = $this->detail_in_event_enrollment($enrollment['event_id'], $timeslot['id'], $enrollment['student_id'])) {
            return $res;
        } else {
            return $this->create([
                'event_id' => $enrollment['event_id'],
                'event_enrollment_id' => $enrollment['id'],
                'event_timeslot_id' => $timeslot['id'],
                'student_id' => $enrollment['student_id'],
                'skip' => 0,
                'txn_status' => 'W',
            ]);
        }
    }

    protected function create_skipped_enrollment_item($eventId, $enrollmentId, $timeslotId, $studentId)
    {
        return $this->create([
            'event_id' => $eventId,
            'event_enrollment_id' => $enrollmentId,
            'event_timeslot_id' => $timeslotId,
            'student_id' => $studentId,
            'skip' => 1,
            'txn_status' => 'W'
        ]);
    }

    /*
     * $this->create_invoicing($params, $enrollment, $range, $firstTimestamp, $lastTimestamp, $counter, $course['name'], implode('||', $itemTimeslots), 1, $params['price'], $params['discount'], $params['net_amount']);
     */

    protected function create_invoicing($params, $enrollment, $dateRange, $periodStartdate, $periodEnddate, $itemCount, $itemName, $itemTimeslots, $qty = 1, $price = 0, $discount = 0, $amount = 0)
    {
        $this->Invoicing_model->create([
            'selected_month' => $params['selected_month'] ?? null,
            'user_id' => $enrollment['student_id'],
            'event_id' => $enrollment['event_id'],
            'event_enrollment_id' => $enrollment['id'],
            'attend_startdate' => $params['first_date'],
            'date_range' => $dateRange,
            'period_startdate' => $periodStartdate,
            'period_enddate' => $periodEnddate,
            'is_trial' => $params['is_trial'],
            'course_count' => $itemCount,
            'item_name' => $itemName,
            'item_timeslot' => $itemTimeslots,
            'unit_price' => $price,
            'qty' => $qty,
            'discount' => $discount,
            'discount_amount' => $params['discount_amount'],
            'amount' => $amount,
            'original_amount' => floatval($price) * intval($qty),
            'L' => $params['L'],
        ]);
    }

    /**
     * Invoicing Trial
     *
     * @param array $params
     * @param array $course
     * @param array $enrollment
     * @param array $timeslots
     * @param array $weekdays
     * @return bool
     */
    protected function trial_enrollment_item_invoicing($params, $course, $enrollment, $timeslots, $weekdays)
    {
        $trial = $params['is_trial'];
        // $firstDateIterated = false;
        if ($trial) {
            foreach ($timeslots as $timeslot) {
                $week = date('w', $timeslot['startdate']);
                if (!empty($weekdays) && !in_array($week, $weekdays))
                    continue;
                // if (!$firstDateIterated) {
                //     if ($enrollment['first_date'] != $timeslot['startdate'])  continue;
                // }
                if ($enrollment['first_date'] < $timeslot['startdate'])
                    continue;
                $date = date('Y-m-d', $timeslot['startdate']);
                // Enrollment Item
                $enrollmentItem = $this->create_enrollment_item($enrollment, $timeslot);
                $this->create_invoicing($params, $enrollment, $date, $date, $date, 1, $course['name'], sprintf(
                    '%s(%s) %s-%s',
                    $date,
                    DateHelper::getDefaultWeekdays($date),
                    DateHelper::getAmpm($timeslot['starttime'], false),
                    DateHelper::getAmpm($timeslot['endtime'], true, false)
                ), $params['price'], $params['discount'], $params['net_amount']);
                break;
            }
        }
        return $trial;
    }

    public function update($params, $id)
    {
        $this->saveDB("default", $this->tbl, $params, $id);
        $params['id'] = $id;
        return $params;
    }

    public function updateWhere($params, $where)
    {
        $this->saveWhereDB($this->database, $this->tbl, $params, $where);
        return $params;
    }

    public function listing_in_event_attendance2($ids)
    {
        $this->tenant();
        $db = $this->load->database($this->database, TRUE);
        $this->load->model("Student_model");
        
        return $db->select("ei.id as enrollment_item_id,ei.*,mas_student.*")
            ->from($this->fullTable)
             ->where_in('ei.event_timeslot_id', $ids)
            ->join("mas_student","ei.student_id = mas_student.id")
            ->get()
            ->result();
    }



    public function update_in_event_attendance($params)
    {
        $items = $params['item'];
        $itemIds = array_keys($items);
        $_eeis = $this->listing_in_event_attendance2($itemIds);
        foreach ($_eeis as $eei) {
            $id = $eei['id'];
            if (!empty($items[$id]['in_datetime'])) {
                $this->update([
                    'in_datetime' => DateTime::createFromFormat('Y-m-d H:i', $items[$id]['in_datetime'])->getTimestamp(),
                    'txn_status' => 'P'
                ], $id);
            }
            if (!empty($items[$id]['out_datetime'])) {
                $this->update([
                    'out_datetime' => DateTime::createFromFormat('Y-m-d H:i', $items[$id]['out_datetime'])->getTimestamp(),
                    'txn_status' => 'P'
                ], $id);
            }
            if (!empty($items[$id]['absent'])) {
                $this->update([
                    'absent' => 1,
                    'txn_status' => 'A'
                ], $id);
            }
            if (!empty($items[$id]['taken_leave_datetime'])) {
                $this->update([
                    'taken_leave_datetime' => DateTime::createFromFormat('Y-m-d H:i', $items[$id]['taken_leave_datetime'])->getTimestamp(),
                    'txn_status' => 'H'
                ], $id);
            }
        }
    }

    public function makeup($params)
    {
        $itemIds = array_keys($params['ids']);
        $timeslotId = $params['event_timeslot_id'];
        $_eeis = $this->listing_in_event_attendance_3($itemIds);
       
        $timeslot = $this->Event_timeslot_model->detail($timeslotId);
    
        foreach ($_eeis as $eei) {
            $id = $eei['id'];
            $new = $this->create([
                'event_id' => $timeslot['event_id'],
                'event_timeslot_id' => $timeslot['id'],
                'event_enrollment_id' => $eei['event_enrollment_id'],
                'student_id' => $eei['student_id'],
                'prev_txn_id' => $eei['id'],
                'txn_status' => 'R',
            ]);
            
            $this->update([
                'next_txn_id' => $new['id'],
                'txn_status' => 'M'
            ], $eei['id']); //cant update 
        }
    }

    public function listing_attendance($ids)
    {
        $this->db = $this->load->database($this->database, true);
        return $this->db
            ->from($this->fullTable)
            ->join($this->table_prefix . 'event_timeslot et', $this->short . '.event_timeslot_id = et.id')
            ->where_in('ei.id', $ids)
            ->select('ei.*, et.startdate, et.starttime, et.endtime')
            ->get()
            ->result_array();
    }

    public function listing_prev_item($id, $datetime, $starttime)
    {
        $startdate = date('Y-m-d', $datetime);
        $this->db = $this->load->database($this->database, true);
        $eeis = $this->db
            ->from($this->fullTable)
            ->join($this->table_prefix . 'event_timeslot et', 'ei.event_timeslot_id = et.id')
            ->where('ei.id !=' . $id, null, false)
            ->where('et.starttime < "' . $starttime . '"', null, false)
            ->where('FROM_UNIXTIME(startdate, "%Y-%m-%d") = "' . $startdate . '"', null, false)
            ->select('ei.*, et.startdate, et.starttime, et.endtime')
            ->get()
            ->result_array();

        $existingStarttime = [];
        $res = [];

        foreach ($eeis as $eei) {
            $starttime = $eei['starttime'];
            if (!empty($existingStarttime[$starttime])) continue;
            else $existingStarttime[$starttime] = 1;

            array_push($res, $eei);
        }

        return $res;
    }

    public function in($ids, $autoAttendOut = false)
    {
        $items = $this->listing_attendance($ids);

        $res = [];
        foreach ($items as $item) {
            if ($autoAttendOut) {
                array_push($res, $this->update([
                    'in_datetime' => (new DateTime(date('Y-m-d', $item['startdate']) . $item['starttime']))->getTimestamp(),
                    'out_datetime' => (new DateTime(date('Y-m-d', $item['startdate']) . $item['endtime']))->getTimestamp(),
                    'txn_status' => in_array($item['txn_status'], ['W', 'N', 'R']) ? 'P' : $item['txn_status'],
                ], $item['id']));
            } else {
                array_push($res, $this->update([
                    'in_datetime' => time(),
                    'txn_status' => in_array($item['txn_status'], ['W', 'N', 'R']) ? 'P' : $item['txn_status'],
                ], $item['id']));
            }
        }

        return $res;
    }

    public function out($ids, $autoAttendOut = false)
    {
        $items = $this->listing_attendance($ids);

        $res = [];
        foreach ($items as $item) {
            $inDatetime = (new DateTime(date('Y-m-d', $item['startdate']) . $item['starttime']))->getTimestamp();
            $outDatetime = (new DateTime(date('Y-m-d', $item['startdate']) . $item['endtime']))->getTimestamp();
            array_push($res, $this->update([
                // 'in_datetime' => (string) $inDatetime,
                'out_datetime' => (string) $autoAttendOut ?  $outDatetime : time(),
                'txn_status' => in_array($item['txn_status'], ['W', 'N', 'R']) ? 'P' : $item['txn_status'],
            ], $item['id']));

            $_items = $this->listing_prev_item($item['id'], $inDatetime, $item['starttime']);

            $found = false;
            foreach ($_items as $_item) {
                if ($_item['in_datetime'] || $found) {
                    $found = true;
                    $canUpdate = in_array($_item['txn_status'], ['P', 'L', 'W', 'N', 'R']);
                    if ($canUpdate) {
                        array_push($res, $this->update([
                            'in_datetime' => (string) $_item['in_datetime'] ?? (new DateTime(date('Y-m-d', $_item['startdate']) . $_item['starttime']))->getTimestamp(),
                            'out_datetime' => (string) $_item['out_datetime'] ?? (new DateTime(date('Y-m-d', $_item['startdate']) . $_item['endtime']))->getTimestamp(),
                            'txn_status' => in_array($_item['txn_status'], ['W', 'N', 'R']) ? 'P' : $_item['txn_status'],
                        ], $_item['id']));
                    }
                } else {
                    break;
                }
            }
        }

        return $res;
    }

    public function absent($ids)
    {
        $items = $this->listing_attendance($ids);

        $res = [];

        foreach ($items as $item) {
            array_push($res, $this->update(['absent' => 1, 'txn_status' => 'A'], $item['id']));
        }

        return $res;
    }

    public function erase($ids)
    {
        $items = $this->listing_attendance($ids);

        $res = [];

        foreach ($items as $item) {
            array_push($res, $this->update([
                'in_datetime' => null,
                'out_datetime' => null,
                'taken_leave_datetime' => null,
                'absent' => 0,
                'txn_status' => $item['txn_status'] == 'M' ? 'M' : ($item['txn_status'] == 'R' ? 'R' : 'W'),
            ], $item['id']));
        }

        return $res;
    }

    public function approved_makeup($studentId, $fromItemId, $toTimeslotId)
    {
        $this->db = $this->load->database($this->database, true);

        $existing = $this->db->from($this->fullTbl)->where('id', $fromItemId)->get()->row_array();

        $this->load->model('Event_timeslot_model');
        $timeslot = $this->Event_timeslot_model->detail($toTimeslotId);

        $new = $this->create([
            'event_id' => $timeslot['event_id'],
            'event_timeslot_id' => $toTimeslotId,
            'event_enrollment_id' => $existing['event_enrollment_id'],
            'student_id' => $studentId,
            'txn_status' => 'R',
            'prev_txn_id' => $existing['id']
        ]);

        // From item (OLD)
        $this->update([
            'txn_status' => 'M',
            'prev_txn_status' => $existing['txn_status'],
            'next_txn_id' => $new['id']
        ], $existing['id']);
    }

    public function activeQuery($tenantNo, $withMakeup = false)
    {
        $this->db = $this->load->database($tenantNo, true);

        $query = $this->db->from($this->fullTbl)
            ->where_not_in('txn_status', $withMakeup ? ['H', 'C', 'U', 'S', 'D'] : ['H', 'M', 'C', 'U', 'S', 'D']);

        $res = [];
        $res['compiled_select'] = $query->get_compiled_select();
        $this->db->reset_query();

        $this->db = $this->load->database($tenantNo, true);
        $query2 = $this->db->from($this->fullTbl)
            ->where_not_in('txn_status', ['H', 'M', 'C', 'U', 'S', 'D']);

        $_temp = $query2->get()->row_array();

        $res['has_record'] = $_temp != null && count($_temp) > 0;

        return $res;
    }

    public function getLatestQueryOnTimeslot($timeslotId)
    {
        $this->tenant();

        return $this->db->from($this->fullTable)
            ->where('event_timeslot_id', $timeslotId)
            ->group_by('event_timeslot_id, student_id')
            ->select('MAX(id)', null, false)
            ->get_compiled_select();
    }

    public function detail_studentId_timeslotIds_count($studentId, $timeslotIds)
    {
        $this->tenant();

        return $this->db->from($this->fullTable)
            ->where('student_id', $studentId)
            ->where_in('event_timeslot_id', $timeslotIds)
            ->count_all_results();
    }

    public function delete_in_studentId_timeslotIds($studentId, $timeslotIds)
    {
        $this->tenant();

        return $this->db->from($this->fullTable)
            ->where('student_id', $studentId)
            ->where_in('event_timeslot_id', $timeslotIds)
            ->delete($this->table_prefix . 'event_enrollment_item');
    }

    public function makeup_next_timeslot($eventId, $timeslotId, $studentIds, $enrollmentItemIds, $selectedDate, $courseLock = true)
    {
        $this->tenant();

        $this->load->model('Event_timeslot_model');
        $selectedTimeslots = $this->Event_timeslot_model->detail_selected_date($selectedDate);

        if (empty($selectedTimeslots)) return [];
        $selectedTimeslotIds = [];
        foreach ($selectedTimeslots as $_) {
            array_push($selectedTimeslotIds, $_['id']);
        }

        // Get the course_id of the current event_id
        $this->load->model('Event_course_model');
        $relatedEventCourse = $this->Event_course_model->getOne("default", "event_course", ['course_id'], $eventId, 'event_id');
        
      
        // The event_id of next timeslot shall fall into these event_id range
        $_relatedEventCourses = $courseLock
            ? $this->Event_course_model->getAll("default", $this->Event_course_model->tbl, ['event_id', 'course_id'], ['course_id' => $relatedEventCourse['course_id']])
            : $this->Event_course_model->getAll("default", $this->Event_course_model->tbl, ['event_id', 'course_id']);
        
        $_relatedCourseIds = [];
        foreach ($_relatedEventCourses as $_relatedEventCourse) {
            array_push($_relatedCourseIds, $_relatedEventCourse['course_id']);
        }
        $this->load->model('Course_model');
        $_relatedCourses = $this->Course_model->getAllIn("default", $this->Course_model->tbl, ['id', 'name'], 'id', $_relatedCourseIds);
        // Courses with course_id valued array key 
        $relatedCourses = [];
        foreach ($_relatedCourses as $_relatedCourse) {
            $relatedCourses[$_relatedCourse['id']] = $_relatedCourse;
        }
       
        // EventCourses with event_id valued array key plus course name
        $relatedEventCourses = [];
        foreach ($_relatedEventCourses as &$_relatedEventCourse) {
            $_relatedEventCourse['name'] = $relatedCourses[$_relatedEventCourse['course_id']]['name'];
            $relatedEventCourses[$_relatedEventCourse['event_id']] = $_relatedEventCourse;
        }

        // Chosen enrollment items for the makeup
        $unavailableEnrollmentItems = $this->db->from($this->fullTable)
            ->where_in('event_enrollment_id', $enrollmentItemIds)
            ->or_group_start()
            ->where_in('student_id', $studentIds)
            ->where_in('event_timeslot_id', $selectedTimeslotIds)
            ->where_in('txn_status', self::activeStatuses())
            ->group_end()
            ->select('event_timeslot_id')
            ->get()
            ->result_array();
        $unavailableTimeslotIds = [];
        foreach ($unavailableEnrollmentItems as $_item) {
            array_push($unavailableTimeslotIds, $_item['event_timeslot_id']);
        }

        $res = [];

        foreach ($selectedTimeslots as $selectedTimeslot) {
            $tid = $selectedTimeslot['id'];
            $eid = $selectedTimeslot['event_id'];

            if (in_array($tid, $unavailableTimeslotIds)) continue;
            if (empty($relatedEventCourses[$eid])) continue;

            array_push($res, [
                'id' => $tid,
                'name' => $relatedEventCourses[$eid]['name'],
                'event_id' => $selectedTimeslot['event_id'],
                'startdate' => $selectedTimeslot['startdate'],
                'starttime' => $selectedTimeslot['starttime'],
                'endtime' => $selectedTimeslot['endtime'],
            ]);
        }

        return $res;
    }


    public function listing_timeslot_student($event_id, $timeslot_id)
    {
        return $this->db->from('mas_event_enrollment_item')
            ->where('event_id', $event_id)
            ->where("event_timeslot_id", $timeslot_id)
            ->get()
            ->result();
    }

    public function update_attendance($params)
    {

        $items = $params['item'];
        $itemIds = array_keys($items);

        $students = json_decode(json_encode($this->listing_timeslot_student($params['event_id'], $params['timeslot_id'])), true);

        foreach ($itemIds as $id) {

            if ($items[$id]['in_datetime'] != "") {
                $items[$id]['in_datetime'] = DateTime::createFromFormat('d-m-Y H:i', $items[$id]['in_datetime'])->getTimestamp();
                $items[$id]['txn_status'] = "P";
            }

            if ($items[$id]['out_datetime'] != "") {
                $items[$id]['out_datetime'] = DateTime::createFromFormat('d-m-Y H:i', $items[$id]['out_datetime'])->getTimestamp();
                $items[$id]['txn_status'] = "P";
            }

            if (!empty($items[$id]['absent'])) {
                $items[$id]['absent'] = 1;
                $items[$id]['txn_status'] = "A";
            }

            if ($items[$id]['taken_leave_datetime'] != '') {
                $items[$id]['taken_leave_datetime'] = DateTime::createFromFormat('d-m-Y H:i', $items[$id]['taken_leave_datetime'])->getTimestamp();
                $items[$id]['txn_status'] = "H";
            }


            $this->db->set($items[$id])
                ->where("event_id", $params['event_id'])
                ->where("event_timeslot_id", $params['timeslot_id'])
                ->where('student_id', $id)
                ->update('mas_event_enrollment_item');
        }
    }

    public function listing_in_event_attendance_3($ids)
    {
        $this->tenant();

        $db = $this->load->database("default", TRUE);

        return $db->from($this->fullTable)

            ->where_in('id', $ids)

            ->get()

            ->result_array();
    }


    public function student_makeup($params)
    {
        $itemIds = array_keys($params['ids']);
        $timeslotId = $params['event_timeslot_id'];
        $_eeis = $this->listing_in_event_attendance_3($itemIds);
        $timeslot = $this->Event_timeslot_model->detail($timeslotId);
        foreach ($_eeis as $eei) {
            $id = $eei['id'];
            $new = $this->create([
                'event_id' => $timeslot['event_id'],
                'event_timeslot_id' => $timeslot['id'],
                'event_enrollment_id' => $eei['event_enrollment_id'],
                'student_id' => $eei['student_id'],
                'prev_txn_id' => $eei['id'],
                'txn_status' => 'R',
            ]);
            $this->update([
                'next_txn_id' => $new['id'],
                'txn_status' => 'M'
            ], $eei['id']);
        }
    }
}
