<?php

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

class Slip_signed_model extends MY_Model
{
    public $short = "ss";
    public $table;
    public $tbl;
    public $database;
    public $table_prefix = '';

    public function __construct()
    {
        parent::__construct();
        $this->database = $this->getTenantNo();
        $this->tbl = 'replyslip_signed';
        $this->table = $this->tbl . ' ' . $this->short;

        $this->db = $this->load->database($this->database, true);
    }

    public function datatable()
    {
        $id = $this->input->get('id');
        $this->load->model('Slip_model');
        $slip = $this->Slip_model->get_slip($id);


        $this->db->from('student')
            ->group_start()
            ->or_where('motherphone IS NOT NULL', null, false)
            ->or_where('fatherphone IS NOT NULL', null, false)
            ->or_where('guardianphone IS NOT NULL', null, false)
            ->group_end();

        if (!empty($slip['student_ids'])) {
            $this->db->where_in('id', explode(',', $slip['student_ids']));
        }
        $count = $this->db->count_all_results();
        $this->db->reset_query();

        $this->db
            ->from($this->table)
            ->join('replyslip', 'replyslip_signed.replyslip_id = replyslip.id')
            ->join('student', 'replyslip_signed.student_id = student.student_id', 'right')
            ->group_start()
            ->where('replyslip_signed.replyslip_id', $id)
            ->or_where('replyslip_signed.replyslip_id IS NULL', NULL, FALSE)
            ->group_end()
            ->group_start()
            ->or_where('motherphone IS NOT NULL', null, false)
            ->or_where('fatherphone IS NOT NULL', null, false)
            ->or_where('guardianphone IS NOT NULL', null, false)
            ->group_end();

        if (!empty($slip['student_ids'])) {
            $this->db->where_in('student.id', explode(',', $slip['student_ids']));
        }

        /**
         * @var CI_DB_query_builder
         */
        $query = $this->db
            ->select('
                student.id,
                student.eng_name,
                student.chi_name,
                student.student_no,
                student.motherphone,
                student.fatherphone,
                student.guardianphone,
                CASE 
                    WHEN replyslip_signed.signed_datetime IS NOT NULL AND FROM_UNIXTIME(replyslip_signed.signed_datetime, "%Y-%m-%d %H:%i:%s") > replyslip.due_datetime THEN "逾期簽署"
                    WHEN replyslip_signed.signed_datetime IS NOT NULL THEN "已簽"
                    ELSE "未簽" 
                END AS signed_status,
                replyslip_signed.signed_datetime,
                replyslip_signed.attended,
                replyslip_signed.agreed,
                replyslip_signed.opted
            ', false);

        return [
            'query' => $query,
            'count' => $count,
            'column' => [
                'student.eng_name',
                'student.chi_name',
                'student.student_no',
                'student.motherphone',
                'student.fatherphone',
                'student.guardianphone',
            ]
        ];
    }

    public function datatable_map($items)
    {
        foreach ($items as &$item) {
            $item['signed_datetime'] = $item['signed_datetime'] ? date('Y-m-d H:i', $item['signed_datetime']) : null;
            $item['attended'] = $item['attended'] == 1 ? '<i class="fa fa-check"></i>' : '-';
            $item['opted'] = $item['opted'] == 1 ? '<i class="fa fa-check"></i>' : '-';
            $item['agreed'] = $item['agreed'] == 1 ? '<i class="fa fa-check"></i>' : '-';
        }
        return $items;
    }

    public function findAll($id)
    {
        return $this->db
            ->from('replyslip_signed')
            ->join('student', 'replyslip_signed.student_id = student.student_id', 'right')
            ->group_start()
            ->where('replyslip_signed.replyslip_id', $id)
            ->or_where('replyslip_signed.replyslip_id IS NULL', NULL, FALSE)
            ->group_end()
            ->get()
            ->result_array();
    }

    // subquery join clause
    // subquery in clause
    public function getUnsignedRecords()
    {
        $student = $this->db
            ->from('student')
            ->join('user', 'student.student_id = user.student_id')
            ->select('student_id, fcm_token')
            ->get_compiled_select();

        return $this->db
            ->from('replyslip_signed')
            ->join('(' . $student . ') s1', 'replyslip_signed.student_id = s1.student_id', 'right')
            ->where('replyslip_signed.replyslip_id NOT IN (
                SELECT `id` FROM `replyslip` 
                WHERE due_datetime IS NOT NULL 
                AND due_datetime != "0000-00-00 00:00:00"
                AND UNIX_TIMESTAMP(due_datetime) > NOW()
                AND UNIX_TIMESTAMP(due_datetime) <= NOW() + INTERVAL 48 HOUR
            ) ', NULL, FALSE)
            ->get()
            ->result_array();

        // echo $this->db
        //     ->from('replyslip_signed')
        //     ->join('(' . $student . ') s1', 'replyslip_signed.student_id = s1.student_id', 'right')
        //     ->where('replyslip_signed.replyslip_id NOT IN (
        //         SELECT `id` FROM `replyslip` 
        //         WHERE due_datetime IS NOT NULL 
        //         AND due_datetime != "0000-00-00 00:00:00"
        //         AND UNIX_TIMESTAMP(due_datetime) > NOW()
        //         AND UNIX_TIMESTAMP(due_datetime) <= NOW() + INTERVAL 48 HOUR
        //     ) ', NULL, FALSE)
        //     ->get_compiled_select();
        // die();
    }

    public function get_signed_slips($where = [])
    {
        return $this->getAll($this->database, $this->tbl, [], $where);
    }

    public function create_slip_signed($params)
    {
        $parent_id = $params['parent_id'];
        $signed_password = $params['signed_password'];
        $this->validate_signed_password($parent_id, $signed_password);

        $signed_datetime = time();
        $id = $this->insertDB($this->database, $this->tbl, [
            'replyslip_id' => $params['replyslip_id'],
            'student_id' => $params['student_id'],
            'attended' => (int) $params['attended'] ?? 0,
            'opted' => (int) $params['opted'] ?? 0,
            'agreed' => (int) $params['agreed'] ?? 0,
        ], false, [
            'signed_datetime' => $signed_datetime
        ]);

        $params['id'] = $id;
        $params['signed_datetime'] = $signed_datetime;
        $params['datetime'] = date('Y-m-d H:i', $signed_datetime);

        return $params;
    }

    protected function validate_signed_password($parent_id, $signed_password)
    {
        $this->load->model('Parent_model');
        $res = $this->Parent_model->get_parent($parent_id, [], ['signed_password' => $this->Parent_model->encrpyt_password($signed_password)]);

        if (empty($res)) throw new Exception('密碼錯誤');
    }
}
