<?php

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

class Parents_model extends MY_Model
{

    public $short = "pr";
    public $table;
    public $tbl;
    public $database = 'default';

    public function __construct()
    {
        parent::__construct();
        $this->tbl = 'parent';
        $this->table = $this->tbl . ' ' . $this->short;
        $this->fields = [
            'id',
            'tenant_no',
            'mobile',
            'username',
            'chi_name',
            'eng_name',
            'email',
            'remark',
            'status',
        ];
    }

    public function totalCount()
    {
        $db = $this->load->database($this->database, TRUE);
        $db->from($this->table_prefix . $this->table);
        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 . '.username',
            $this->short . '.chi_name',
            $this->short . '.eng_name',
            $this->short . '.mobile',
            $this->short . '.email',
            $this->short . '.create_time',
            $this->short . '.update_time',
            $this->short . '.status',
        ];
        $orders = [];
        foreach ($param['order'] as $order) {
            $colIdx = $order['column'];
            $colName = $param['columns'][$colIdx]['data'];
            $dir = strtoupper($order['dir']);
            $orders[] = $this->short . "." . $colName . ' ' . $dir;
        }
        $db = $this->load->database($this->database, TRUE);
        //        $searchVal = trim($param['search']['value']);
        $search = [];
        foreach ($param['columns'] as $column) {
            if ($column['searchable'] === "true") {
                $search[$this->short . "." . $column['data']] = $column['search']['value'];
            }
        }

        $db->group_start();
        if (!empty($search)) {
            $db->or_like($search);
        }
        $db->group_end();

        if ($this->database == 'default') {
            $db->where([$this->short . '.tenant_no' => $this->session->userdata('tenant_no')]);
        }
        $db->from($this->table_prefix . $this->table);
        return $db->count_all_results();
    }

    public function listing($param)
    {
        $fields = [
            $this->short . '.id',
            $this->short . '.username',
            $this->short . '.chi_name',
            $this->short . '.eng_name',
            $this->short . '.mobile',
            $this->short . '.email',
            $this->short . '.create_time',
            $this->short . '.update_time',
            $this->short . '.status',
        ];
        $orders = [];
        foreach ($param['order'] as $order) {
            $colIdx = $order['column'];
            $colName = $param['columns'][$colIdx]['data'];
            $dir = strtoupper($order['dir']);
            $orders[] = $this->short . "." . $colName . ' ' . $dir;
        }
        $start = $param['start'];
        $length = $param['length'];
        $joins = [];
        //        $joins['course_category cc'] = "cc.id = " . $this->short . ".course_category_id";
        //        $joins['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") {
                $search[$this->short . "." . $column['data']] = $column['search']['value'];
            }
        }
        //        }
        $result = $this->getFromDBFilter($this->database, $this->table, $fields, $start, $length, $orders, $search, $joins);
        $data = [];
        foreach ($result as $row) {
            $data[] = [
                'id' => $row['id'],
                'username' => $row['username'],
                'chi_name' => $row['chi_name'],
                'eng_name' => $row['eng_name'],
                'mobile' => $row['mobile'],
                'email' => $row['email'],
                'create_time' => date("Y-m-d H:i:s", $row['create_time']),
                'update_time' => date("Y-m-d H:i:s", $row['update_time']),
                'details' => '<button id="details-' . $row['id'] . '">詳情</button>',
                'status' => $this->status[$row['status']],
                //                'delete' => '<input type="checkbox" class="checkbox-delete" />',
            ];
        }
        return $data;
    }

    //overwrite
    public function getFromDBFilter($database, $table, $fields, $start, $length, $orders, $search, $joins = [], $keywordfield = '')
    {
        $db = $this->load->database($database, TRUE);
        $db->distinct();
        $db->select(implode(', ', $fields));
        $db->from($this->table_prefix . $table);

        if (count($joins) > 0) {
            foreach ($joins as $joinTbl => $condition) {
                $db->join($joinTbl, $condition);
            }
        }

        $db->group_start();
        if (!empty($search)) {
            $db->or_like($search);
        }
        $db->group_end();

        if ($database == 'default') {
            $db->where([$this->short . '.tenant_no' => $this->session->userdata('tenant_no')]);
        }

        $db->order_by(implode(', ', $orders));
        if ($start > -1) {
            $db->limit($length, $start);
        }
        $data = $db->get()->result_array();
        //        log_message('debug', 'sql:' . $db->last_query());
        return $data;
    }

    public function getParentsFromStudentID($student_id, $tenant_no)
    {
        $this->load->model('Student_parent_model');
        $parents = $this->Student_parent_model->parents($student_id, $tenant_no);
        if (empty($parents)) {
            return [];
        }
        $parent_ids = [];
        foreach ($parents as $parent) {
            $parent_ids[] = $parent['parent_id'];
        }
        return $this->getAllIn($this->database, $this->table, $this->addPrefixToFields($this->fields, $this->short), 'id', $parent_ids);
    }

    private function _generateParentUsername($student_no)
    {
        $tableName = $this->table_prefix . $this->tbl;
        return sprintf("CONCAT('%s', " .
            "		CHAR(" .
            "			ASCII(" .
            "				SUBSTRING(" .
            "					IFNULL((SELECT * FROM (SELECT MAX(`username`) FROM `%s` WHERE `username` LIKE '%s%%') AS `temp`), '@')," .
            "				-1)" . // SUBSTRING
            "			) + 1" . // ASCII
            "		)" . // CHAR
            "   )", $student_no, $tableName, $student_no);
    }

    public function insert($param)
    {
        $db = $this->load->database($this->database, TRUE);
        $db->set('create_time', 'UNIX_TIMESTAMP()', FALSE);
        $db->set('created_by', $this->session->userdata('userid'));
        $db->set('update_time', 'UNIX_TIMESTAMP()', FALSE);
        $db->set('updated_by', $this->session->userdata('userid'));
        $db->insert($this->table_prefix . $this->tbl, $param);
        return $db->insert_id();
    }

    private function students($parent_id)
    {
        $this->load->model('Student_model');
        return $this->Student_model->getStudentsFromParentID($parent_id);
    }

    public function one($parent_id)
    {
        $result = $this->getOne($this->database, $this->table, $this->addPrefixToFields($this->fields, $this->short), $parent_id);
        $result['students'] = $this->students($parent_id);
        return $result;
    }

    public function getParentFromUsername($username)
    {
        return $this->getOneRow($this->database, $this->table, $this->addPrefixToFields($this->fields, $this->short), ['username' => $username]);
    }

    public function attachStudent($username, $student_no)
    {
        $this->load->model('Student_model');
        $student = $this->Student_model->getStudentFromStudentNo($student_no);
        if (empty($student)) {
            return false;
        }
        $parent = $this->getParentFromUsername($username);
        if (empty($parent)) {
            return false;
        }
        $this->load->model('Student_parent_model');
        $this->Student_parent_model->insert([
            'tenant_no' => $this->session->userdata('tenant_no'),
            'student_id' => $student['id'],
            'parent_id' => $parent['id'],
        ]);
        return true;
    }

    public function detachStudent($username, $student_no)
    {
        $this->load->model('Student_model');
        $student = $this->Student_model->getStudentFromStudentNo($student_no);
        if (empty($student)) {
            return false;
        }
        $parent = $this->getParentFromUsername($username);
        if (empty($parent)) {
            return false;
        }
        $this->load->model('Student_parent_model');
        $this->Student_parent_model->remove([
            'tenant_no' => $this->session->userdata('tenant_no'),
            'student_id' => $student['id'],
            'parent_id' => $parent['id'],
        ]);
        return true;
    }

    public function createParentBatch($data, $student_no)
    {
        $this->load->model('Student_parent_model');
        $db = $this->load->database($this->database, TRUE);
        foreach ($data as $idx => $row) {
            $db->set('tenant_no', $row['tenant_no']);
            $db->set('mobile', $row['mobile']);
            $db->set('username', $this->_generateParentUsername($student_no), FALSE);
            $db->set('password', sha1($row['mobile']));
            $db->set('signed_password', sha1($row['mobile']));
            $db->set('chi_name', $row['chi_name']);
            $db->set('eng_name', $row['eng_name']);
            $db->set('email', $row['email']);
            $db->set('remark', $row['remark'] ?? null);
            $db->set('create_time', 'UNIX_TIMESTAMP()', FALSE);
            $db->set('created_by', $this->session->userdata('userid'));
            $db->set('update_time', 'UNIX_TIMESTAMP()', FALSE);
            $db->set('updated_by', $this->session->userdata('userid'));
            $db->insert($this->table_prefix . $this->tbl, []);
            $parent_id = $db->insert_id();
            $this->Student_parent_model->insert([
                'tenant_no' => $row['tenant_no'],
                'student_id' => $row['student_id'],
                'parent_id' => $parent_id,
            ]);
        }
        return $parent_id;
    }

    public function searchParent($param)
    {
        $fields = ['username', 'chi_name', 'eng_name', 'mobile'];
        $db = $this->load->database($this->database, TRUE);
        $db->select(implode(", ", $this->fields));
        $db->from($this->table_prefix . $this->tbl);
        foreach ($fields as $field) {
            if (trim($param[$field]) != "") {
                $db->like($field, $param[$field]);
            }
        }
        $row = $db->get()->result_array();
        return $row;
    }

    public function resetPassword($username)
    {
        $parent = $this->getParentFromUsername($username);
        $data = [
            'password' => sha1($parent['mobile']),
            'signed_password' => sha1($parent['mobile']),
            'status' => $parent['status'],
        ];
        $this->save($data, $parent['id']);
    }

    public function updateInformation($param, $username)
    {
        $parent = $this->getParentFromUsername($username);
        $this->save($param, $parent['id']);
    }

    public function save($param, $id)
    {
        $this->saveDB($this->database, $this->tbl, $param, $id);
    }

    public function listing_phone($phone, $tenantNo)
    {
        $this->db = $this->load->database($this->database, TRUE);
        return $this->db->from($this->table_prefix . $this->tbl)
            ->like('mobile', $phone)
            ->where('tenant_no', $tenantNo)
            ->get()
            ->result_array();
    }
}
