<?php
class Authority_model extends CI_Model {

    private $table = 'authority';

    public function get_all() {
        $this->db->order_by('authority_name', 'ASC');
            return $this->db->get_where($this->table, [
                'is_delete' => '0',
                'status'    => '1'   // add your active check here
            ])->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();
    }

    public function get($id) {
        return $this->db->get_where($this->table, ['id' => $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) {
        $this->db->where('id', $id)
                 ->update($this->table, ['status' => $status]);
    }

    public function isAuthorityExists($name, $exclude_id = null)
    {
        // Use the helper function that ignores special characters
        // Compares only alphanumeric characters (case-insensitive)
        return check_duplicate_name(
            $this->table,
            'authority_name',
            $name,
            $exclude_id,
            ['is_delete' => '0', 'status' => '1']
        );
    }
    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;
    }

}
