<?php

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

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

class Course_model extends MY_Model
{

    use Tenants;

    public $short = "c";
    public $table;
    public $tbl;
    public $fullTbl;
    public $fullTable;
    public $database;
    public $filteredcount = 0;

    public function __construct()
    {
        parent::__construct();
        //$this->database = $this->session->userdata('tenant_no');
        $this->database = "default";
        $this->tbl = 'course';
        $this->table = $this->tbl . ' ' . $this->short;
        $this->fullTbl = $this->table_prefix . $this->tbl;
        $this->fullTable = $this->table_prefix . $this->table;
    }

    public function allfortenants()
    {
        $mode = $this->input->get('mode');
        $_fields = [$this->short . '.id', $this->short . '.name', $this->short . '.code'];
        $where = [$this->short . '.status' => 1, 'course_category_id' => $this->input->get('course_category_id'), 'course_category_sub_id' => $this->input->get('course_subcategory_id')];
        if ($mode === 'add') {
            return $this->getAll($this->database, $this->table, $_fields, $where);
        } else {
            return $this->getAll($this->input->get('tenantno'), $this->table, $_fields, $where);
        }
    }

    public function allforlist()
    {
        $fields = [$this->short . '.id', $this->short . '.name', $this->short . '.code'];
        return $this->getAll($this->database, $this->table, $fields, []);
    }

    public function all($categoryId = '', $subcategoryId = '', $fields = [])
    {
        $_fields = empty($fields) ? [$this->short . '.id', $this->short . '.name', $this->short . '.code'] : $fields;
        $where = [$this->short . '.status' => 1];
        if (!empty($categoryId) && !empty($subcategoryId)) {
            $where = array_merge(['course_category_id' => $categoryId, 'course_category_sub_id' => $subcategoryId], $where);
        }
        return $this->getAll($this->database, $this->table, $_fields, $where);
    }

    public function allforfilter($tenant)
    {
        $_fields = [$this->short . '.id', $this->short . '.name', $this->short . '.code'];
        $where = [$this->short . '.status' => 1];
        if ($tenant === 'main') {
            return $this->getAll($this->database, $this->table, $_fields, $where);
        } else {
            $data = array_merge($this->getAll($this->database, $this->table, $_fields, $where), $this->getAll('main', $this->table, $_fields, $where));
            return $data;
        }
    }

    public function listing_selection()
    {


        return $this->db->from($this->fullTbl)->get()->result_array();
    }

    public function listing_by_course_subcategory($courseCategoryId, $courseSubcategoryId)
    {


        return $this->db->from($this->fullTbl)
            ->where('course_category_id', $courseCategoryId)
            ->where('course_category_sub_id', $courseSubcategoryId)
            ->get()
            ->result_array();
    }

    public function listing_in_event_selection()
    {


        return $this->db
            ->from($this->fullTable)
            ->join($this->table_prefix . 'course_category cc', 'c.course_category_id = cc.id')
            ->join($this->table_prefix . 'course_category_sub ccs', 'c.course_category_sub_id = ccs.id')
            ->select('
                c.id,  
                CONCAT(cc.name, " | ", ccs.name, " | ", c.name, " | ", 
                CASE
                    WHEN c.mode = "month" THEN "月費"
                    WHEN c.mode = "lesson" THEN "堂費"
                    ELSE "非延續性收費" 
                END,               
                c.duration, " 分鐘") AS name,
            ', false)
            ->get()->result_array();
    }

    public function detail($id, $fields = [])
    {

        return $this->getOne($this->database, $this->tbl, $fields, $id);
    }

    public function detailfortenants($data, $fields = [])
    {

        return $this->getOne($data['tenant_no'], $this->tbl, $fields, $data['course_id']);
    }

    public function detail_in_event_enroll($id)
    {


        return $this->db
            ->from($this->fullTable)
            ->join($this->table_prefix . 'course_category cc', 'c.course_category_id = cc.id')
            ->join($this->table_prefix . 'course_category_sub ccs', 'c.course_category_sub_id = ccs.id')
            ->where('c.id', $id)
            ->select('
                c.id,
                cc.name as course_category_name,
                ccs.name as course_category_sub_name,
                CONCAT(cc.name, " | ", ccs.name, " | ", c.name, " | ", 
                CASE
                    WHEN c.mode = "month" THEN "月費"
                    WHEN c.mode = "lesson" THEN "堂費"
                    ELSE "非延續性收費" 
                END,
                c.duration, " 分鐘") AS name,
            ', false)
            ->get()->row_array();
    }

    public function detail_in_event($id)
    {


        return $this->db
            ->from($this->fullTable)
            ->join($this->table_prefix . 'course_category cc', 'c.course_category_id = cc.id')
            ->join($this->table_prefix . 'course_category_sub ccs', 'c.course_category_sub_id = ccs.id')
            ->where('c.id', $id)
            ->select('
                c.id,  
                CONCAT(cc.name, " | ", ccs.name, " | ", c.name, " | ", 
                CASE
                    WHEN c.mode = "month" THEN "月費"
                    WHEN c.mode = "lesson" THEN "堂費"
                    ELSE "非延續性收費" 
                END,              
                c.duration, " 分鐘") AS name
            ', false)
            ->get()->row_array();
    }

    // public function detail_by_event_id($eventId) {
    //     $this->tenant();

    //     return $this->db
    //         ->from('mas_event')
    //         ->join('mas_event_course', 'mas_event_course.event_id = mas_event.id')
    //         ->join('mas_course_category', 'mas_course.course_category_id = mas_course_category.id')
    //         ->join('mas_course_category_sub', 'mas_course.course_category_sub_id = mas_course_category_sub.id')
    //         ->where('mas_event.id', $eventId)
    //         ->select('mas_event.*', false)
    //         ->get()
    //         ->row_array();
    // }

    public function isRegular($mode)
    {
        return in_array($mode, ['month', 'lesson']);
    }

    public function fullTitle($name, $weekdays)
    {
        if (!empty($weekdays)) {
            $tcWeekdays = [];

            $default = DateHelper::getDefaultTcWeekdays();
            if (!empty($weekdays)) {
                foreach (explode('/', $weekdays) as $weekdayIndex) {
                    array_push($tcWeekdays, $default[$weekdayIndex]);
                }
                return sprintf('%s (%s)', $name, implode('/', $tcWeekdays));
            } else {
                return sprintf('%s', $name);
            }
        } else {
            return $name;
        }
    }


    /*
    public function update($params, $id)
    {
        if (isset($params['updated_by'])) {
           // $params['updated_at'] = $params['update_time'];
            unset($params['id']);
           
          //  unset($params['update_time']);
            $this->db->update('mas_course', $params, ['id' => $id]);
        } else {
            Userstamp::$timestamp = true;
            $this->db->update("mas_course", Userstamp::update($params), ['id' => $id]);
        }

        $params['id'] = $id;
        return $params;
    }*/

    public function update($params, $id)
    {
        $this->db->from('mas_course')
            ->where("id", $id)
            ->set($params)
            ->update();
    }

    public function totalCount()
    {
        $db = $this->load->database($this->database, TRUE);
        $db->from($this->fullTable);
        if ($this->database == 'default') {
            // $db->where(['tenant_no' => $this->session->userdata('tenant_no')]);
        }
        return $db->count_all_results();
    }

    public function filteredCount($param)
    {
        $fields = [$this->short . '.id', $this->short . '.code', 'cc.name AS category', /*'ccs.name AS subcategory',*/ $this->short . '.name', $this->short . '.duration', $this->short . '.mode'];
        $start = $param['start'];
        $length = $param['length'];
        $joins = [];
        $joins[$this->table_prefix . 'course_category cc'] = "cc.id = " . $this->short . ".course_category_id";
        //$joins[$this->table_prefix . 'course_category_sub ccs'] = "ccs.id = " . $this->short . ".course_category_sub_id";
        $db = $this->load->database($this->database, TRUE);
        $db->select(implode(', ', $fields));
        $db->from($this->fullTable);
        $tenant_no = $this->session->userdata('tenant_no');
        if ($this->database == 'default') {
            // $db->where(['tenant_no' => $tenant_no]);
        }
        foreach ($joins as $joinTbl => $condition) {
            $db->join($joinTbl, $condition);
        }
        $search = [];
        foreach ($param['columns'] as $column) {
            if ($column['searchable'] === "true" && !empty($column['search']['value'])) {
                $colName = str_replace(array('category', 'subname', 'subcc.name'), array('cc.name', 'ccs.name', 'ccs.name'), $column['data']);
                $searchfield = ($colName === 'cc.name' || $colName === 'ccs.name' || $colName === 'subcc.name') ? $colName : $this->short . "." . $colName;
                $search[$searchfield] = $column['search']['value'];
            }
        }
        if (!empty($search)) {
            foreach ($search as $k => $v) {
                if ($v !== '') {
                    if ($k === 'name') {
                        $db->like($search);
                    }
                    $db->like($search, 'none');
                }
            }
        }
        return count($db->get()->result_array());
    }

    /* public function listing($param)
    {
        $fields = [$this->short . '.id', $this->short . '.code', 'cc.name AS category', 'cc.status as categorystatus', 'ccs.name AS subcategory', 'ccs.status as substatus', $this->short . '.status', $this->short . '.name', $this->short . '.duration', $this->short . '.mode'];
        $orders = [];
        foreach ($param['order'] as $order) {
            $colIdx = $order['column'];
            $colName = str_replace(array('category', 'subname', 'subcc.name'), array('cc.name', 'ccs.name', 'ccs.name'), $param['columns'][$colIdx]['data']);
            $dir = strtoupper($order['dir']);
            $orders[] = ($colName === 'cc.name' || $colName === 'ccs.name' || $colName === 'subcc.name') ? $colName . ' ' . $dir : $this->short . "." . $colName . ' ' . $dir;
            //            log_message('debug','colname:'.$colName.';dir:'.$dir);
        }
        $start = $param['start'];
        $length = $param['length'];
        $joins = [];
        $joins[$this->table_prefix . 'course_category cc'] = "cc.id = " . $this->short . ".course_category_id";
        $joins[$this->table_prefix . 'course_category_sub ccs'] = "ccs.id = " . $this->short . ".course_category_sub_id";
      
        //        $searchVal = trim($param['search']['value']);
        $search = [];
        //        if (trim($searchVal) !== "") {
        foreach ($param['columns'] as $column) {
            if ($column['searchable'] === "true" && !empty($column['search']['value'])) {
                $colName = str_replace(array('category', 'subname', 'subcc.name'), array('cc.name', 'ccs.name', 'ccs.name'), $column['data']);
                $searchfield = ($colName === 'cc.name' || $colName === 'ccs.name' || $colName === 'subcc.name') ? $colName : $this->short . "." . $colName;
                $search[$searchfield] = $column['search']['value'];
            }
        }
        $result = $this->getFromDBFilter($this->database, $this->table, $fields, $start, $length, $orders, $search, $joins, 'c.name');
        //        log_message('debug','count result:'.count($result));
        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'id' => $row['id'],
                'code' => $row['code'],
                'category' => $row['categorystatus'] == 0 ? '<span class="notinuse">' . $row['category'] . '</span>' : $row['category'],
                'subcategory' => $row['substatus'] == 0 ? '<span class="notinuse">' . $row['subcategory'] . '</span>' : $row['subcategory'],
                'name' => $row['name'],
                'duration' => $row['duration'] . '分鐘',
                'mode' => $this->paid_mode[$row['mode']],
                'details' => '<button id="details-' . $row['id'] . '">詳情</button>',
                'status' => is_null($row['status']) ? '' : $this->status[$row['status']],
                'delete' => '<input id="' . $row['id'] . '" type="checkbox" class="checkbox-delete" />',
            ];
        }
        return $data;
    }*/

    public function listing($param)
    {   //var_dump($param["columns"][2]);
        //exit;
        $fields = ['id', 'course_category_id', 'code', 'name', 'status', 'duration'];
        $orders = [];
        $start = $param['start'];
        $length = $param['length'];
        $joins = [];
        $search = [];
    
        if ($param["columns"][1]["search"]['value'] !== "") {
            $searchVal = trim($param["columns"][1]['search']['value']);
            $search = [];
            if (trim($searchVal) !== "") {
                $search[$this->short . "." . $param["columns"][1]['data']] = $searchVal;
            }
        }

        if ($param["columns"][2]["search"]['value'] !== "") {
            $searchVal = trim($param['columns'][2]['search']['value']);
            $search = [];
            if (trim($searchVal) !== "") {
                $search[$this->short . "." . $param["columns"][2]['data']] = $searchVal;
            }
        }

        $result = $this->getFromDB($this->database, $this->table, $fields, $start, $length, $orders, $search, $joins, 'c.name');

        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'id' => $row['id'],
                'code' => $row['code'],
                'category' => $row['course_category_id'] == 1 ? "恆常課程" : '暑期課程',
                //'subcategory' => $row['substatus'] == 0 ? '<span class="notinuse">' . $row['subcategory'] . '</span>' : $row['subcategory'],
                'name' => $row['name'],
                'duration' => $row['duration'] . '分鐘',
                //'mode' => $this->paid_mode[$row['mode']],
                //'details' => '<button id="details-' . $row['id'] . '">詳情</button>',
                'details' => '<a class="btn btn-secondary" href="' . base_url('backend/course/edit?id=' . $row['id']) . '">詳細</a>',
                'status' => is_null($row['status']) ? '' : $this->status[$row['status']],
                'delete' => '<input id="' . $row['id'] . '" type="checkbox" class="checkbox-delete" />',
            ];
        }
        return $data;
    }

    public function insert($param)
    {
        $this->insertDB($this->database, $this->tbl, $param);
    }

    public function one($course_id)
    {
        $this->load->model("Unit_model");
        $fields = [

            $this->short . '.id',
            $this->short . '.code',
            $this->short . '.course_category_id',
            $this->short . '.course_category_sub_id',
            $this->short . '.name',
            $this->short . '.description',
            $this->short . '.duration',
            $this->short . '.qty',
            $this->short . '.mode',
            $this->short . '.status',
            $this->short . '.remark', "(select count(id) from " . $this->table_prefix . "event_course where course_id=$course_id group by id union select count(id) from " . $this->database_prefix . "system." . $this->Unit_model->table_prefix . "unit where course_id=$course_id group by id) as inuse"
        ];
        return $this->getOne($this->database, $this->table, $fields, $course_id);
    }

    public function save($param, $id)
    {
        //        log_message('debug','code:'.$param['code'].';id:'.$id);
        $this->saveDB($this->database, $this->tbl, $param, $id);
    }

    public function deleteMultiple($ids)
    {
        $this->deleteDB($this->database, $this->tbl, $ids, true);
    }

    public function deleteAll()
    {
        $this->deleteAllDB($this->database, $this->tbl);
    }

    public function getAllIds()
    {
        $ids = [];
        $all = $this->all();
        foreach ($all as $k => $v) {
            $ids[] = $v['id'];
        }
        return $ids;
    }

    public function teacher_calendar()
    {
        $this->load->model('Tenant_model');
        $this->load->model('Event_enrollment_item_model');

        $tenantNo = $this->getTenantNo();
        $tenantName = $this->Tenant_model->detail_no($tenantNo)['name'];

        $baseQuery = $this->Event_enrollment_item_model->activeQuery($tenantNo);

        if (!$baseQuery['has_record']) {
            return [];
        }

        $this->db = $this->load->database($tenantNo, true);
        $courses = $this->db
            ->from($this->fullTable)
            ->where('c.status', 1)
            ->join($this->table_prefix . 'event_course ec', 'c.id = ec.course_id')
            ->join($this->table_prefix . 'event e', 'ec.event_id = e.id')
            ->join($this->table_prefix . 'event_timeslot et', 'ec.event_id = et.event_id')
            // show all timeslots
            ->join('(' . $baseQuery['compiled_select'] . ') item', 'et.id = item.event_timeslot_id', 'left')
            ->join($this->table_prefix . 'room r', 'e.room_id = r.id')
            ->group_start()
            ->group_start()
            ->where('et.holiday_skip IS NULL', null, false)
            ->where('c.mode != "period"', null, false)
            ->group_end()
            ->or_where('c.mode = "period"', null, false)
            ->group_end()
            ->where_in('YEAR(FROM_UNIXTIME(et.startdate))', $this->threeYearsInterval(), false)
            ->select('
                r.name as room_name,
                c.code as course_code, 
                c.name, 
                c.id as course_id, 
                et.event_id as event_id,
                et.starttime,
                et.endtime,
                item.*,
                item.id as item_id,
                et.startdate as date,
                et.id as event_timeslot_id
            ', false)
            ->order_by('et.id', 'asc')
            ->get()
            ->result_array();

        //        log_message('debug', $this->db->last_query());

        $res = [];

        $existingTimeslotIds = [];
        foreach ($courses as $item) {
            $timeslotId = $item['event_timeslot_id'];

            if (!empty($existingTimeslotIds[$timeslotId])) continue;
            else $existingTimeslotIds[$timeslotId] = 1;

            // $txnStatus = $item['txn_status'];
            // $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'
            $date = date('Y-m-d H:i:s', $item['date']);
            $checkinDatetime = !empty($item['in_datetime']) ? date('H:i', $item['in_datetime']) : '';

            $date = explode(' ', $date)[0];
            // use date to generate app calendar compatible key
            $key = date('Y-m-d H:i:s', (new DateTime($date, new DateTimeZone('Asia/Hong_Kong')))->getTimestamp());
            // ZuluString
            $keys = explode(' ', $key);

            $calendarDatetime = sprintf('%sT%sZ', $keys[0], $keys[1]);
            $key = sprintf('%sT%sZ', $keys[0], '00:00:00');

            // EnrollmentItemId
            $itemId = $item['item_id'];
            $values = [
                'item_id' => $itemId,
                'room_name' => sprintf('%s %s', $tenantName, $item['room_name'] ?? ''),
                'course_id' => $item['course_id'],
                'course_code' => $item['course_code'] ?? '',
                'title' => $item['name'] ?? '',
                'student_id' => $item['student_id'],
                'timeslot_id' => $timeslotId,
                'date' => $date,
                'type' => '上課記錄',
                'time' => sprintf('%s - %s', $item['starttime'], $item['endtime']),
                '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' => 0
            ];

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

        return $res;
    }

    public function listing_students($timeslotId)
    {
        $tenantNo = $this->getTenantNo();
        $this->load->model('Event_enrollment_item_model');

        $baseQuery = $this->Event_enrollment_item_model->activeQuery($tenantNo, true);

        if (!$baseQuery['has_record']) {
            return [];
        }

        $this->db = $this->load->database($tenantNo, true);
        $courses = $this->db
            ->from($this->fullTable)
            ->where('c.status', 1)
            ->join($this->table_prefix . 'event_course ec', 'c.id = ec.course_id')
            ->join($this->table_prefix . 'event_timeslot et', 'ec.event_id = et.event_id')
            // show all timeslots
            ->join('(' . $baseQuery['compiled_select'] . ') item', 'et.id = item.event_timeslot_id')
            ->join($this->table_prefix . 'student s', 'item.student_id = s.id')
            ->group_start()
            ->group_start()
            ->where('et.holiday_skip IS NULL', null, false)
            ->where('c.mode != "period"', null, false)
            ->group_end()
            ->or_where('c.mode = "period"', null, false)
            ->group_end()
            ->where('item.event_timeslot_id', $timeslotId)
            ->where('item.id IN (' . $this->Event_enrollment_item_model->getLatestQueryOnTimeslot($timeslotId) . ')', null, false)
            ->where_in('YEAR(FROM_UNIXTIME(et.startdate))', $this->threeYearsInterval(), false)
            ->select('
                c.name, 
                c.id as course_id, 
                et.event_id as event_id,
                et.starttime,
                et.endtime,
                item.*,
                item.id as item_id,
                s.chi_name as student_name,
                et.startdate as date,
                et.id as event_timeslot_id
            ', false)
            ->order_by('et.id', 'asc')
            ->get()
            ->result_array();

        //        log_message('debug', $this->db->last_query());

        $res = [];

        foreach ($courses as $item) {
            $timeslotId = $item['event_timeslot_id'];

            // $txnStatus = $item['txn_status'];
            // $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'
            $date = date('Y-m-d H:i:s', $item['date']);
            $checkinDatetime = !empty($item['in_datetime']) ? date('H:i', $item['in_datetime']) : '';

            $date = explode(' ', $date)[0];
            // use date to generate app calendar compatible key
            $key = date('Y-m-d H:i:s', (new DateTime($date, new DateTimeZone('Asia/Hong_Kong')))->getTimestamp());
            // ZuluString
            $keys = explode(' ', $key);

            $calendarDatetime = sprintf('%sT%sZ', $keys[0], $keys[1]);
            $key = sprintf('%sT%sZ', $keys[0], '00:00:00');

            // EnrollmentItemId
            $itemId = $item['item_id'];
            $values = [
                'item_id' => $itemId,
                'course_id' => $item['course_id'],
                'student_id' => $item['student_id'],
                'student_name' => $item['student_name'],
                'timeslot_id' => $timeslotId,
                'date' => $date,
                'type' => '上課記錄',
                'time' => sprintf('%s - %s', $item['starttime'], $item['endtime']),
                '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' => 0
            ];

            if ($values['absent'] == 0 && $values['makeup'] == 0 && $values['taken_leave_datetime'] == '') {
                $values['in_datetime'] = !empty($item['in_datetime']) ? date('H:i', $item['in_datetime']) : "";
                $values['out_datetime'] = !empty($item['out_datetime']) ? date('H:i', $item['out_datetime']) : "";
            } else {
                $values['in_datetime'] = "";
                $values['out_datetime'] = "";
            }
            if ($values['makeup'] == 1) {
                $this->Event_enrollment_item_model->setTenantNo($tenantNo);
                $nextItem = $this->Event_enrollment_item_model->detail_in_nextTimeslot($item['next_txn_id']);
                $values['makeup_date'] = date('Y-m-d', $nextItem['startdate']);
            } else {
                $values['makeup_date'] = "";
            }

            if ($values['absent'] == 0 && $values['makeup'] == 0 && $values['taken_leave_datetime'] == '' && $values['in_datetime'] == '' && $values['out_datetime'] == '') {
                $values['not_processed'] = 1;
            } else {
                $values['not_processed'] = 0;
            }
            $this->array_put($res, $key, $values);
        }

        return $res;
    }

    public function get_id($id)
    {
        return $this->db->from('mas_course')
            ->select("*")
            ->where("id", $id)
            ->get()
            ->row_array();
    }

    public function delete($id)
    {
        $this->db->from('mas_course')
            ->where('id', $id)
            ->delete();
    }
}
