<?php
class Authority_model extends CI_Model {

    private $table = 'authority';

    /**
     * Get all authorities for a specific company
     */
    public function get_all_by_company($company_id) {
        $this->db->where('company_id', $company_id);
        $this->db->where('is_delete', '0');
        $this->db->where('status', '1');
        $this->db->order_by('authority_name', 'ASC');
        return $this->db->get($this->table)->result();
    }

    /**
     * Get all authorities (for Super Admin without company filter)
     * @deprecated Use get_all_by_company instead
     */
    public function get_all() {
        $this->db->order_by('authority_name', 'ASC');
        return $this->db->get_where($this->table, [
            'is_delete' => '0',
            'status'    => '1'
        ])->result();
    }
    
    /**
     * Get authorities by IDs - for filtering based on user assignments
     */
    public function get_by_ids($ids) {
        if (empty($ids)) {
            return [];
        }
        $this->db->where_in('id', $ids);
        $this->db->where('is_delete', '0');
        $this->db->where('status', '1');
        $this->db->order_by('authority_name', 'ASC');
        return $this->db->get($this->table)->result();
    }
    
    /**
     * Get authorities by IDs for a specific company
     */
    public function get_by_ids_and_company($ids, $company_id) {
        if (empty($ids)) {
            return [];
        }
        $this->db->where_in('id', $ids);
        $this->db->where('company_id', $company_id);
        $this->db->where('is_delete', '0');
        $this->db->where('status', '1');
        $this->db->order_by('authority_name', 'ASC');
        return $this->db->get($this->table)->result();
    }

    public function get($id) {
        return $this->db->get_where($this->table, ['id' => $id])->row();
    }
    
    /**
     * Get authority by ID and company
     */
    public function get_by_company($id, $company_id) {
        return $this->db->get_where($this->table, [
            'id' => $id,
            'company_id' => $company_id
        ])->row();
    }

    public function insert($data) {
        return $this->db->insert($this->table, $data);
    }

    public function update($id, $data) {
        $this->db->where('id', $id);
        return $this->db->update($this->table, $data);
    }

    public function delete($id) {
        $this->db->where('id', $id);
        return $this->db->update($this->table, ['is_delete' => 1]);
    }

    public function update_status($id, $status, $deactivation_date = null, $deactivation_remarks = null) {
        $update_data = ['status' => $status];
        
        // If deactivating (status = 0), save deactivation details
        if ($status == '0' && !empty($deactivation_date)) {
            $update_data['deactivation_date'] = $deactivation_date;
            $update_data['deactivation_remarks'] = $deactivation_remarks;
        }
        
        // If activating (status = 1), clear deactivation details
        if ($status == '1') {
            $update_data['deactivation_date'] = null;
            $update_data['deactivation_remarks'] = null;
        }
        
        $this->db->where('id', $id)
                 ->update($this->table, $update_data);
    }

    /**
     * Check if authority name exists within the same company
     */
    public function isAuthorityExistsInCompany($name, $company_id, $exclude_id = null)
    {
        $this->db->where('company_id', $company_id);
        $this->db->where('is_delete', '0');
        $this->db->where('status', '1');
        $this->db->where('LOWER(authority_name)', strtolower(trim($name)));
        
        if ($exclude_id) {
            $this->db->where('id !=', $exclude_id);
        }
        
        return $this->db->count_all_results($this->table) > 0;
    }
    
    /**
     * Check if authority name exists (normalized) within the same company
     */
    public function isAuthorityExistsNormalizedInCompany($normalized_name, $company_id, $exclude_id = null)
    {
        $sql = "
            SELECT 1
            FROM authority
            WHERE LOWER(
                REGEXP_REPLACE(authority_name, '[\\s\\-\\/\\'']+', '', 'g')
            ) = ?
            AND company_id = ?
            AND is_delete = '0'
        ";
        
        $params = [$normalized_name, $company_id];
        
        if ($exclude_id) {
            $sql .= " AND id != ?";
            $params[] = $exclude_id;
        }
        
        $sql .= " LIMIT 1";

        return $this->db->query($sql, $params)->num_rows() > 0;
    }
    
    /**
     * Check if alias exists within the same company
     */
    public function isAliasExistsInCompany($alias, $company_id, $exclude_id = null)
    {
        if (empty($alias)) {
            return false;
        }
        
        $this->db->where('company_id', $company_id);
        $this->db->where('is_delete', '0');
        $this->db->where('status', '1');
        $this->db->where('LOWER(alias_name)', strtolower(trim($alias)));
        
        if ($exclude_id) {
            $this->db->where('id !=', $exclude_id);
        }
        
        return $this->db->count_all_results($this->table) > 0;
    }

    /**
     * @deprecated Use isAuthorityExistsInCompany instead
     */
    public function isAuthorityExists($name, $exclude_id = null)
    {
        return check_duplicate_name(
            $this->table,
            'authority_name',
            $name,
            $exclude_id,
            ['is_delete' => '0', 'status' => '1']
        );
    }
    
    /**
     * @deprecated Use isAuthorityExistsNormalizedInCompany instead
     */
    public function isAuthorityExistsNormalized($normalized_name)
    {
        $sql = "
            SELECT 1
            FROM authority
            WHERE LOWER(
                REGEXP_REPLACE(authority_name, '[\\s\\-\\/\\'']+', '', 'g')
            ) = ?
            AND is_delete = '0'
            LIMIT 1
        ";

        return $this->db->query($sql, [$normalized_name])->num_rows() > 0;
    }
    
    /**
     * Check if authority is in use (has document heads)
     */
    public function isInUse($id) {
        return $this->db->where('authority_id', $id)
                        ->where('status', '1')
                        ->count_all_results('sub_type') > 0;
    }

}
