<?php

namespace App\Models;

use CodeIgniter\Model;

class EmployeePolicyModel extends Model
{
    protected $table            = 'employee_polices';
    protected $primaryKey       = 'id';
    protected $allowedFields    = [
        "id",
        "employee_id",
        "client_policy_id",
        "tpa_id",
        "uhid",
        "batch_id",
        "status",
        "pre_existing_alignments",
        "date_of_exit",
        "reason_for_exit",
        "basic_cover_si",
        "date_coverage",
        "policy_end_date",
        "days",
        "premium",
        "rata_premimum",
        "si_enhancement_date",
        "gst",
        "created_by",
        "updated_by",
        "updated_at",
        "rand_string",
        "is_active",
        "claim_status",
        'ecard_sent_status',
        'payable_employee',
        'file_id',
        "enrollment_open_date",
        "enrollment_close_date",
    ];

    // Callbacks
    protected $allowCallbacks       = true;
    protected $beforeInsert         = ["checkAndADDCreatedByValue", "addRandomNumberBeforeInsert"];
    protected $afterInsert          = [];
    protected $beforeUpdate         = ["checkAndUpdateUpdatedByValue"];
    protected $afterUpdate          = [];
    protected $beforeFind           = [];
    protected $afterFind            = [];
    protected $beforeDelete         = [];
    protected $afterDelete          = [];

    protected function checkAndADDCreatedByValue(array $data)
    {
        // Check if 'updated_by' value is null or empty
        if (empty($data['data']['created_by'])) {           
            // Set 'updated_by' value to the current session user ID
            $data['data']['created_by'] = get_session_userid();
        }

        return $data;
    }

    protected function checkAndUpdateUpdatedByValue(array $data)
    {
        // Check if 'updated_by' value is null or empty
        if (empty($data['data']['updated_by'])) {           
            // Set 'updated_by' value to the current session user ID
            $data['data']['updated_by'] = get_session_userid();
        }

        return $data;
    }

    protected function addRandomNumberBeforeInsert(array $data)
    {   
        $random_number = generate_random_alphanumeric();
        $data['data']['rand_string'] = $random_number;

        return $data;
    }

    // ----------------------------------------------------------------------------------------------------------
    public function getEmployeePolicy($client_id = 0, $policy_id = 0, $status = [], $branch_id = 0, $emp_code = "", $emp_name = "", $status_type = "", $search = "")
    {
        // dd($status);

        $status_query = "employee_polices.status"; // Default fallback

        if ($status_type == "hr") {
            $status_query = "CASE 
                                WHEN employee_polices.status = 'enrolled' THEN 'submitted' 
                                ELSE employee_polices.status 
                            END as status";
        }

        $selectColumns = [
            'employee_polices.*',
            'policy_type.policy_type as policy_name',
            'im.short_name as insurer_short_name',
            'ib.branch_name as insurer_branch_name',
            'ib.branch_code as insurer_branch_code',
            'tpam.name as tpa_name',
            'tpam.short_name as tpa_short_name',
            'tpab.branch_code as tpa_branch_code',
            'cm.client_name',
            'cm.short_name as client_short_name',
            // 'emp.id as employee_primary_id',
            'emp.relationship',
            'emp.relationship_code',
            'emp.change_event',
            'emp.emp_code',
            'emp.name',
            'emp.email_corporate',
            'emp.dob',
            'DATE_FORMAT(emp.dob, "%d/%m/%Y") AS formatted_dob',
            'emp.gender',
            'emp.emp_status',
            'emp.is_active as emp_is_active',
            'emp.mobile as mobile',
            'emp.doj',
            'emp.basic_pay',
            'emp.band as grade',
            'policy_type.policy_type',
            'client_branch.branch_name as client_branch_name',
            'client_branch.branch_code as client_branch_code',
            'cp.policy_no',
            'cp.policy_type_id',

            // Name audit trail
            '(SELECT old_value FROM auditing_history WHERE pk = emp.id AND field_name = "name" AND table_name = "employees"  ORDER BY id ASC LIMIT 1) AS name_first_old',
            '(SELECT new_value FROM auditing_history WHERE pk = emp.id AND field_name = "name" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS name_last_new',
            '(SELECT DATE_FORMAT(COALESCE(created_at, emp.updated_at), "%d/%m/%Y %h:%i %p") FROM auditing_history WHERE pk = emp.id AND field_name = "name" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS name_created_at',

            // DOB audit trail
            '(SELECT old_value FROM auditing_history WHERE pk = emp.id AND field_name = "dob" AND table_name = "employees"  ORDER BY id ASC LIMIT 1) AS dob_first_old',
            '(SELECT new_value FROM auditing_history WHERE pk = emp.id AND field_name = "dob" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS dob_last_new',
            '(SELECT DATE_FORMAT(COALESCE(created_at, emp.updated_at), "%d/%m/%Y %h:%i %p") FROM auditing_history WHERE pk = emp.id AND field_name = "dob" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS dob_created_at',

            // Gender audit trail
            '(SELECT old_value FROM auditing_history WHERE pk = emp.id AND field_name = "gender" AND table_name = "employees"  ORDER BY id ASC LIMIT 1) AS gender_first_old',
            '(SELECT new_value FROM auditing_history WHERE pk = emp.id AND field_name = "gender" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS gender_last_new',
            '(SELECT DATE_FORMAT(COALESCE(created_at, emp.updated_at), "%d/%m/%Y %h:%i %p") FROM auditing_history WHERE pk = emp.id AND field_name = "gender" AND table_name = "employees"  ORDER BY id DESC LIMIT 1) AS gender_created_at',

            "(CASE 
                WHEN emp.relationship = 'Self' 
                THEN (
                    SELECT IF(COUNT(e.id) > 0, 
                        CONCAT(
                            COUNT(e.id), 
                            ' people removed ( ', 
                            GROUP_CONCAT(CONCAT(e.name, ' - ', e.relationship) SEPARATOR ', '), 
                            ' )'
                        ), 
                        NULL)
                    FROM employees e
                    JOIN employee_polices ep ON e.id = ep.employee_id
                    WHERE e.emp_code = emp.emp_code 
                    AND (e.is_dependent_modified = 0 OR (e.is_active = 0 AND e.emp_status != 'truncated'))
                    AND ep.client_policy_id = employee_polices.client_policy_id
                ) 
                ELSE NULL 
            END) AS removed_summary",

            "(CASE 
                WHEN emp.relationship = 'Self' 
                THEN (
                    SELECT DATE_FORMAT(MAX(e.updated_at), '%d/%m/%Y %h:%i %p')
                    FROM employees e
                    JOIN employee_polices ep ON e.id = ep.employee_id
                    WHERE e.emp_code = emp.emp_code 
                    AND (e.is_dependent_modified = 0 OR (e.is_active = 0 AND e.emp_status != 'truncated'))
                    AND ep.client_policy_id = employee_polices.client_policy_id
                ) 
                ELSE NULL 
            END) AS removed_summary_updated_at",

            "(IF(COALESCE(emp.is_dependent_modified, 0) = 1, 'Newly Added, ', 
                CASE 
                    WHEN emp.relationship != 'Self' 
                        AND emp.file_id IS NULL
                        AND emp.created_by = (
                            SELECT e_sub.id
                            FROM employees e_sub
                            JOIN employee_polices ep_sub ON e_sub.id = ep_sub.employee_id
                            WHERE e_sub.emp_code = emp.emp_code 
                            AND e_sub.relationship = 'Self' 
                            AND e_sub.is_active = 1
                            AND ep_sub.client_policy_id = employee_polices.client_policy_id
                            LIMIT 1
                        )
                        
                    THEN 'Newly Added ' 
                    ELSE NULL 
                END
            )) AS newly_added",

            "(IF(COALESCE(emp.is_dependent_modified, 0) = 1, DATE_FORMAT(emp.updated_at, '%d/%m/%Y %h:%i %p'), 
                CASE 
                    WHEN emp.relationship != 'Self' 
                        AND emp.file_id IS NULL
                        AND emp.created_by = (
                            SELECT e_sub.id
                            FROM employees e_sub
                            JOIN employee_polices ep_sub ON e_sub.id = ep_sub.employee_id
                            WHERE e_sub.emp_code = emp.emp_code 
                            AND e_sub.relationship = 'Self' 
                            AND e_sub.is_active = 1
                            AND ep_sub.client_policy_id = employee_polices.client_policy_id
                            LIMIT 1
                        )
                    THEN DATE_FORMAT(emp.updated_at, '%d/%m/%Y %h:%i %p')
                    ELSE NULL 
                END
            )) AS newly_added_updated_at",
        ];

        if ($status_type === 'hr') {
            $selectColumns[] = "(CASE 
                WHEN emp.relationship = 'Self' 
                THEN IF(auth_history.user_id IS NOT NULL, 'Yes', 'No')
                ELSE NULL 
            END) AS logged_in";
        }

        $selectColumns[] = $status_query;

        $result = $this->select($selectColumns, false)
            ->join('employees emp', 'employee_polices.employee_id = emp.id')
            ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id') //cp - client policy
            ->join('policies pm', 'cp.policy_id = pm.id', 'left') //pm - policy master
            ->join('policy_type', 'policy_type.id = cp.policy_type_id')
            ->join('insurers im', 'cp.insurer_id = im.id', 'left') //im - insurer master
            ->join('insurer_branch ib', 'cp.insurer_branch_id = ib.id', 'left') //ib - insurer branch
            ->join('tpa tpam', 'cp.tpa_id = tpam.id', 'left') //tpam - tpa master
            ->join('tpa_branch tpab', 'cp.tpa_branch_id = tpab.id', 'left') //tpab - tpa branch
            ->join('clients cm', 'cp.client_id = cm.id') //cm - client master
            ->join('client_branch', 'emp.client_branch_id = client_branch.id'); //cm - client master

        if ($status_type === 'hr') {
            $result->join(
                "(SELECT user_id FROM auth_history WHERE user_type = 'employee' GROUP BY user_id) auth_history",
                'emp.id = auth_history.user_id',
                'left',
                false
            );
        }

        $result->orderBy('emp.emp_code', 'ASC')
            ->orderBy('employee_polices.employee_id', 'ASC');

       
        // Conditionally add where clauses
        if (is_string($client_id) && preg_match('/^[a-f0-9]{32}$/i', $client_id)) {
            $result->where('MD5(emp.client_id)', $client_id);
        } else if ($client_id != 0 && !empty($client_id)) {
            $result->where('emp.client_id', $client_id);
        }
        if ($branch_id != 0 && !empty($branch_id)) {
            $result->where('emp.client_branch_id', $branch_id);
        }
        if ($policy_id != 0 && !empty($policy_id)) {
            $result->where('employee_polices.client_policy_id', $policy_id);
        }

        if (is_array($status) && count($status) > 0) {

            $result->where('employee_polices.status !=', 'expired');

            if (in_array("active", $status)) {

                $result->where('employee_polices.tpa_id IS NOT NULL');
                $result->where('employee_polices.uhid IS NOT NULL');
                $result->whereIn('employee_polices.status', $status);
            } elseif (in_array("pending", $status)) {

                $result->where('employee_polices.tpa_id IS NULL');
                $result->where('employee_polices.uhid IS NULL');
                $result->whereIn('employee_polices.status', array_merge($status, ['active']));
            } else {

                $result->whereIn('employee_polices.status', $status);
            }

            // if($status == 'active'){
            //     $result->where('employee_polices.tpa_id IS NOT NULL');
            //     $result->where('employee_polices.uhid IS NOT NULL');          
            // }else if($status == 'pending'){
            //     $status = 'active';
            //     $result->where('employee_polices.status', $status);
            // }else{
            //     $result->where('employee_polices.status', $status);
            // }
        }

        if (!empty($emp_code)) {
            $result->where('emp.emp_code', $emp_code);
        }
        if (!empty($emp_name)) {
            $result->like('emp.name', $emp_name);
        }

        if (!empty($search)) {
            $this->applyEmployeeGlobalSearch($result, $search);
        }

        // Always check these conditions
        $result->where('employee_polices.is_active', 1)
        ->where('emp.is_active', 1);

        $res = $result->findAll();

        // dd($this->db->getLastQuery());

        return $res;
    }

    /**
     * Global search across emp_code, name, mobile, email, tpa_id, dob, relationship, status.
     */
    protected function applyEmployeeGlobalSearch($builder, string $search)
    {
        $search = trim($search);
        if ($search === '') {
            return $builder;
        }

        return $builder->groupStart()
            ->like('emp.emp_code', $search)
            ->orLike('emp.name', $search)
            ->orLike('emp.mobile', $search)
            ->orLike('emp.email_corporate', $search)
            ->orLike('employee_polices.tpa_id', $search)
            ->orLike('emp.dob', $search)
            ->orWhere("DATE_FORMAT(emp.dob, '%d/%m/%Y') LIKE " . $this->db->escape('%' . $search . '%'), null, false)
            ->orLike('emp.relationship', $search)
            ->orLike('employee_polices.status', $search)
            ->orLike('emp.emp_status', $search)
            ->groupEnd();
    }
   
    // public function getEmployeePolicy($client_id = 0, $policy_id = 0, $status = [], $branch_id = 0, $emp_code = "", $emp_name = "")
    // {
    //     // dd($status);

    //     $result = $this->select([
    //         'employee_polices.*',
    //         'policy_type.policy_type as policy_name',
    //         'im.short_name as insurer_short_name',
    //         'ib.branch_name as insurer_branch_name',
    //         'ib.branch_code as insurer_branch_code',
    //         'tpam.name as tpa_name',
    //         'tpam.short_name as tpa_short_name',
    //         'tpab.branch_code as tpa_branch_code',
    //         'cm.client_name',
    //         'cm.short_name as client_short_name',
    //         // 'emp.id as employee_primary_id',
    //         'emp.relationship',
    //         'emp.relationship_code',
    //         'emp.change_event',
    //         'emp.emp_code',
    //         'emp.name',
    //         'emp.email_corporate',
    //         'emp.dob',
    //         'DATE_FORMAT(emp.dob, "%d/%m/%Y") AS formatted_dob',
    //         'emp.gender',
    //         'emp.emp_status',
    //         'emp.is_active as emp_is_active',
    //         'emp.mobile as mobile',
    //         'emp.doj',
    //         'emp.basic_pay',
    //         'emp.band as grade',
    //         'policy_type.policy_type',
    //         'policy_type.id as policy_type_id',
    //         'client_branch.branch_name as client_branch_name',
    //         'client_branch.branch_code as client_branch_code',
    //         'cp.policy_no',
    //     ])
    //         ->join('employees emp', 'employee_polices.employee_id = emp.id')
    //         ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id') //cp - client policy
    //         ->join('policies pm', 'cp.policy_id = pm.id', 'left') //pm - policy master
    //         ->join('policy_type', 'policy_type.id = cp.policy_type_id')
    //         ->join('insurers im', 'cp.insurer_id = im.id') //im - insurer master
    //         ->join('insurer_branch ib', 'cp.insurer_branch_id = ib.id') //ib - insurer branch
    //         ->join('tpa tpam', 'cp.tpa_id = tpam.id', 'left') //tpam - tpa master
    //         ->join('tpa_branch tpab', 'cp.tpa_branch_id = tpab.id', 'left') //tpab - tpa branch
    //         ->join('clients cm', 'cp.client_id = cm.id') //cm - client master
    //         ->join('client_branch', 'emp.client_branch_id = client_branch.id') //cm - client master
    //         ->orderBy('emp.emp_code', 'ASC')
    //         ->orderBy('employee_polices.employee_id', 'ASC');
    //     // Conditionally add where clauses
    //     if ($client_id != 0 && !empty($client_id)) {
    //         $result->where('emp.client_id', $client_id);
    //     }
    //     if ($branch_id != 0 && !empty($branch_id)) {
    //         $result->where('emp.client_branch_id', $branch_id);
    //     }
    //     if ($policy_id != 0 && !empty($policy_id)) {
    //         $result->where('employee_polices.client_policy_id', $policy_id);
    //     }

    //     if (is_array($status) && count($status) > 0) {

    //         $result->where('employee_polices.status !=', 'expired');

    //         if (in_array("active", $status)) {

    //             $result->where('employee_polices.tpa_id IS NOT NULL');
    //             $result->where('employee_polices.uhid IS NOT NULL');
    //             $result->whereIn('employee_polices.status', $status);
    //         } elseif (in_array("pending", $status)) {

    //             $result->where('employee_polices.tpa_id IS NULL');
    //             $result->where('employee_polices.uhid IS NULL');
    //             $result->whereIn('employee_polices.status', array_merge($status, ['active']));
    //         } else {

    //             $result->whereIn('employee_polices.status', $status);
    //         }

    //         // if($status == 'active'){
    //         //     $result->where('employee_polices.tpa_id IS NOT NULL');
    //         //     $result->where('employee_polices.uhid IS NOT NULL');          
    //         // }else if($status == 'pending'){
    //         //     $status = 'active';
    //         //     $result->where('employee_polices.status', $status);
    //         // }else{
    //         //     $result->where('employee_polices.status', $status);
    //         // }
    //     }

    //     if (!empty($emp_code)) {
    //         $result->where('emp.emp_code', $emp_code);
    //     }
    //     if (!empty($emp_name)) {
    //         $result->like('emp.name', $emp_name);
    //     }

    //     // Always check these conditions
    //     $result->where('employee_polices.is_active', 1)
    //     ->where('emp.is_active', 1);

    //     $res = $result->findAll();

    //     // dd($this->db->getLastQuery());

    //     return $res;
    // }

   public function getEmployeePolicyForEcard($policy_id = 0)
   {
        $result = $this->select([
            'employee_polices.id'
        ])
        ->join('employees emp', 'employee_polices.employee_id = emp.id AND emp.relationship = "Self" ');
        if ($policy_id !=0 && !empty($policy_id)) {
            $result->where('employee_polices.client_policy_id', $policy_id);
        }
        // Always check these conditions
        $result->where('employee_polices.is_active', 1)
               ->where('employee_polices.status', 'active')
               ->where('employee_polices.tpa_id is not null')
               ->where('employee_polices.ecard_sent_status',0)
               ->where('emp.relationship','Self');
        
         return $result->findAll();
        // $result->findAll();
        // print_r($this->db->getLastQuery());die();
   }


   //for emp onboard do not change
   public function checkExistingEmpPolicy($arr)
   {
         return $this->where('employee_id',$arr['employee_id'])
                       ->where('client_policy_id',$arr['client_policy_id'])
                       ->where('is_active',1)
                       ->find();
   }
//-------------------------------------------------------------------------------------------------------


    public function getInceptionEmployeeDataForExportExcel($ref_data, $return_type = 0)
    {   
        $client_policy_id = $ref_data['client_policy_id'];
        $client_branch_id = $ref_data['client_branch_id'];
        $insurer_or_tpa = $ref_data['insurer_or_tpa'];
        $event = $ref_data['event_type'];

        if($insurer_or_tpa == 'tpa'){

            $id = 'tpa_id';

        }else if($insurer_or_tpa == 'insurer'){

            $id = 'uhid';
        }

        $datas = '';
        if($event === 'inception'){

            $datas =  'I';

        }else if($event === 'dependent_addition'){

            $datas =  'DA';

        }else if($event === 'addition'){

            $datas =  'A';

        }

        $sql = "
                SELECT 
                employees.name AS emp_name,
                employees.emp_code AS emp_code,
                employees.dob AS emp_dob,
                employees.gender AS emp_gender,
                employees.relationship AS emp_relationship,
                employees.relationship_code AS emp_relationship_code,
                '$datas' as event_type_data,
                employees.change_event AS change_event,


                employees.doj AS emp_doj,
                employees.mobile AS emp_mobile,
                employees.email_corporate AS emp_email_c,
                employees.email_personal AS emp_email_p,
                employees.band AS emp_grade,
                employees.designation AS emp_designation,
                employees.basic_pay AS emp_basic_pay,

                TIMESTAMPDIFF(YEAR, employees.dob, CURDATE()) AS emp_age,
                employees.emp_type as emp_type,
                

                employee_polices.id as primaryKey, 
                employee_polices.tpa_id, 
                employee_polices.uhid, 
                employee_polices.pre_existing_alignments, 
                employee_polices.basic_cover_si, 
                employee_polices.date_coverage as date_of_coverage, 
                employee_polices.policy_end_date, 
                employee_polices.days as no_of_days,
                employee_polices.premium,
                employee_polices.rata_premimum as pro_rata_premium,
                employee_polices.gst,
                (employee_polices.rata_premimum + employee_polices.gst) AS total, 
                batch_data.emp_policy_id, 
                batch_data.bl AS batch_list_batch_code, 
                batch_data.bf AS batch_files_batch_code,

            CASE
                WHEN client_branch.sez = 0 THEN 'NON-SEZ'
                WHEN client_branch.sez = 1 THEN 'SEZ'
            END as sez

            FROM employee_polices
            LEFT JOIN employees ON employees.id = employee_polices.employee_id
            LEFT JOIN client_branch ON client_branch.id = employees.client_branch_id
            LEFT JOIN (
                SELECT 
                    batch_list.emp_policy_id, 
                    batch_list.batch_code as bl, 
                    batch_files.batch_code as bf
                FROM batch_files 
                LEFT JOIN batch_list ON batch_files.batch_code = batch_list.batch_code 
                WHERE batch_files.event_type = '{$event}' 
                    AND batch_files.actions = 'export' 
                    AND batch_files.insurer_or_tpa = '{$insurer_or_tpa}'
            ) as batch_data ON employee_polices.id = batch_data.emp_policy_id
            WHERE employee_polices.client_policy_id = '{$client_policy_id}'
                AND (employee_polices.{$id} IS NULL OR employee_polices.{$id} = '')
                AND employees.client_branch_id = '{$client_branch_id}'
                AND employee_polices.is_active = 1
                AND employee_polices.status = 'active'
                AND employees.is_active = 1
                AND employees.emp_status = 'active'
        ";

            // Get the result set
            $query = $this->db->query($sql);
            
            if($return_type == 1){
                $results = $query->getResultArray();
            }else{
                $results = $query->getResult();
            }

            // dd($this->db->getLastQuery());

            return $results;
    }


    public function getCorrectionEmployeesDataForExportExcel($ref_data)
    {

            $client_id = $ref_data['client_id'];
            $client_policy_id = $ref_data['client_policy_id'];
            $client_branch_id = $ref_data['client_branch_id'];
            $insurer_or_tpa = $ref_data['insurer_or_tpa'];

            $endorsement_condition = "{$insurer_or_tpa}" === 'tpa' ? "AND (emp_endorsement.endorsement_id IS NOT NULL OR emp_endorsement.endorsement_id != '')" : "AND (emp_endorsement.endorsement_id IS NULL OR emp_endorsement.endorsement_id = '')";

            $sql = "
                    SELECT DISTINCT
                        emp_endorsement.id, 
                        emp_endorsement.pk, 
                        emp_endorsement.emp_code, 
                        emp_endorsement.endorsement_id, 
                        emp_endorsement.old_value, 
                        emp_endorsement.new_value, 
                        emp_endorsement.field_name, 
                        emp_endorsement.remarks, 
                        emp_endorsement.actions, 
                        employees.id AS primaryKey, 
                        employees.name AS emp_name, 
                        employees.dob AS emp_dob, 
                        employees.gender AS emp_gender, 
                        employees.client_id AS emp_client_id, 
                        employees.emp_type as emp_type,
                        employee_polices.uhid,
                        employee_polices.tpa_id,
                        employees.relationship_code as emp_relationship_code,
                        employees.relationship as emp_relationship,
                        'C' as event_type_data,

                        employees.doj AS emp_doj,
                        employees.mobile AS emp_mobile,
                        employees.email_corporate AS emp_email_c,
                        employees.email_personal AS emp_email_p,
                        employees.band AS emp_grade,
                        employees.designation AS emp_designation,
                        employees.basic_pay AS emp_basic_pay,

                        batch_data.emp_policy_id, 
                        batch_data.bl AS batch_list_batch_code, 
                        batch_data.bf AS batch_files_batch_code

                    FROM 
                        emp_endorsement
                    LEFT JOIN
                        employees ON employees.id = emp_endorsement.pk
                    LEFT JOIN 
                        employee_polices ON employees.id = employee_polices.employee_id
                    LEFT JOIN (
                        SELECT batch_list.emp_policy_id, 
                            batch_list.batch_code AS bl, 
                            batch_files.batch_code AS bf 
                        FROM 
                            batch_files 
                        LEFT JOIN 
                            batch_list ON batch_files.batch_code = batch_list.batch_code 
                        WHERE batch_files.event_type = 'correction' 
                        AND batch_files.actions = 'export' 
                        AND batch_files.insurer_or_tpa = '{$insurer_or_tpa}'
                    ) AS batch_data ON emp_endorsement.pk = batch_data.emp_policy_id

                    WHERE employees.client_id = '{$client_id}'
                    AND employee_polices.client_policy_id = '{$client_policy_id}'
                    AND employees.client_branch_id = '{$client_branch_id}'
                    AND emp_endorsement.actions = 'c'
                    AND emp_endorsement.status != 'truncated'
                    AND employee_polices.is_active = 1
                    AND employee_polices.status = 'active'
                    AND employees.is_active = 1
                    AND employees.emp_status = 'active'
                    AND (employee_polices.tpa_id IS NOT NULL AND employee_polices.tpa_id != '')
                    AND (employee_polices.uhid IS NOT NULL AND employee_polices.uhid != '')                    
                    $endorsement_condition";

                // Execute the raw query
                $query = $this->db->query($sql);

                // Get the result set
                $result = $query->getResult();

                // dd($this->db->getLastQuery(), $result);

                return $result;

    }


    public function getSIEnhancementEmployeesDataForExportExcel($ref_data)
    {

            $client_id = $ref_data['client_id'];
            $client_policy_id = $ref_data['client_policy_id'];
            $client_branch_id = $ref_data['client_branch_id'];
            $insurer_or_tpa = $ref_data['insurer_or_tpa'];

            $endorsement_condition = "{$insurer_or_tpa}" === 'tpa' ? "AND (a.endorsement_id IS NOT NULL OR a.endorsement_id != '')" : "AND (a.endorsement_id IS NULL OR a.endorsement_id = '')";


            $query = $this->db->query("
                    SELECT 
                        a.id as endorsement_primarykey,
                        a.group_key,
                        employee_polices.id AS primaryKey,
                        employees.name AS emp_name,
                        employees.emp_code AS emp_code,
                        employees.dob AS emp_dob,
                        employees.gender AS emp_gender,
                        employees.relationship_code AS emp_relationship_code,
                        employees.relationship AS emp_relationship,
                        employees.emp_type as emp_type,
                        'SI' as event_type_data,


                        employees.doj AS emp_doj,
                        employees.mobile AS emp_mobile,
                        employees.email_corporate AS emp_email_c,
                        employees.email_personal AS emp_email_p,
                        employees.band AS emp_grade,
                        employees.designation AS emp_designation,
                        employees.basic_pay AS emp_basic_pay,

                        employee_polices.uhid AS uhid,
                        employee_polices.pre_existing_alignments,
                        employee_polices.policy_end_date,
                        employee_polices.basic_cover_si as old_basic_cover_si,
                        employee_polices.rata_premimum as old_si_premium,                
                        batch_data.emp_policy_id,
                        batch_data.bl AS batch_list_batch_code,
                        batch_data.bf AS batch_files_batch_code,
                        sidata.new_basic_cover_si, 
                        sidata.new_si_premium,
                        sidata.date_of_coverage,
                        
                        DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1 AS no_of_days,

                        sidata.new_si_premium - employee_polices.rata_premimum AS difference_premium,

                        ROUND((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365, 2) AS pro_rata_premium,

                        ROUND(((sidata.new_si_premium - employee_polices.rata_premimum) * (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) * 0.18, 2) AS gst,

                        ROUND(
                            ((sidata.new_si_premium - employee_polices.rata_premimum) * 
                            (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) + 
                            ROUND(
                                ((sidata.new_si_premium - employee_polices.rata_premimum) * 
                                (DATEDIFF(employee_polices.policy_end_date, sidata.date_of_coverage) + 1) / 365) * 0.18, 2
                            ), 2
                        ) AS total

                    FROM 
                        emp_endorsement a
                    LEFT JOIN 
                        employees ON employees.emp_code = a.emp_code
                    LEFT JOIN 
                        employee_polices ON employees.id = employee_polices.employee_id 
                    LEFT JOIN (
                        SELECT 
                            aa.emp_code, 
                            aa.new_value as 'new_basic_cover_si', 
                            bb.new_value as 'new_si_premium', 
                            cc.new_value as 'date_of_coverage' 
                        FROM (
                            SELECT  
                                a1.emp_code, 
                                a1.field_name, 
                                a1.new_value 
                            FROM 
                                emp_endorsement as a1 
                            WHERE 
                                a1.field_name = 'basic_cover_si'
                        ) aa 
                        LEFT JOIN (
                            SELECT  
                                b1.emp_code, 
                                b1.field_name, 
                                b1.new_value 
                            FROM 
                                emp_endorsement as b1 
                            WHERE 
                                b1.field_name = 'premium'
                        ) bb ON aa.emp_code = bb.emp_code 
                        LEFT JOIN (
                            SELECT  
                                c1.emp_code, 
                                c1.field_name, 
                                c1.new_value 
                            FROM 
                                emp_endorsement as c1  
                            WHERE 
                                c1.field_name = 'si_enhancement_date'
                        ) cc ON aa.emp_code = cc.emp_code
                    ) as sidata ON a.emp_code = sidata.emp_code 
                    LEFT JOIN (
                        SELECT DISTINCT 
                            batch_list.emp_policy_id, 
                            batch_list.batch_code AS bl, 
                            batch_files.batch_code AS bf 
                        FROM 
                            batch_files 
                        LEFT JOIN 
                            batch_list ON batch_files.batch_code = batch_list.batch_code 
                        WHERE 
                            batch_files.event_type = 'si_enhancement' 
                            AND batch_files.actions = 'export' 
                            AND batch_files.insurer_or_tpa = '{$insurer_or_tpa}'
                    ) AS batch_data ON employee_polices.id = batch_data.emp_policy_id

                    WHERE employee_polices.client_policy_id = '{$client_policy_id}'
                        AND employees.client_branch_id = '{$client_branch_id}'
                        $endorsement_condition 
                        AND a.actions = 'si'
                        AND a.status != 'truncated'
                        AND employee_polices.is_active = 1
                        AND employee_polices.status = 'active'
                        AND employees.is_active = 1
                        AND employees.emp_status = 'active'
                        group by group_key
                ");

                // Get the result set
                $results = $query->getResult();
                // dd($this->db->getLastQuery(), $results);
                return $results;

    }

    // DO NOT DELETE this DELETION QUERY FUNCTION

    // public function getDeletionEmployeeDataForExportExcel($ref_data, $return_type = 0)
    // {

    //         $client_id = $ref_data['client_id'];
    //         $client_policy_id = $ref_data['client_policy_id'];
    //         $client_branch_id = $ref_data['client_branch_id'];
    //         $insurer_or_tpa = $ref_data['insurer_or_tpa'];

    //         $get_insurer_id_from_client_policy = $this->db->table('client_policy')
    //             ->select('insurer_id')
    //             ->where('id', $client_policy_id)
    //             ->get()
    //             ->getRowArray();

    //         $add_one_day = 0;

    //         if (!empty($get_insurer_id_from_client_policy)) {

    //             $get_the_insurer_add_one_for_delete = $this->db->table('insurers')
    //                 ->select('deletion_add_day')
    //                 ->where('id', $get_insurer_id_from_client_policy['insurer_id'])
    //                 ->get()
    //                 ->getRowArray();

    //             if (!empty($get_the_insurer_add_one_for_delete) && $get_the_insurer_add_one_for_delete['deletion_add_day'] == 1) {
    //                 $add_one_day = 1;
    //             }
    //         }

    //         $status_condition = "{$insurer_or_tpa}" === 'tpa' 
    //             ? "employee_polices.status = 'inactive' AND employees.emp_status = 'inactive'" 
    //             : "employee_polices.status = 'active' AND employees.emp_status = 'active'";

    //         $endorsement_condition = "{$insurer_or_tpa}" === 'tpa' ? "AND (a.endorsement_id IS NOT NULL OR a.endorsement_id != '')" : "AND (a.endorsement_id IS NULL OR a.endorsement_id = '')";

    //         $query = $this->db->query("
    //         SELECT DISTINCT
    //             a.id as endorsement_primarykey,
    //             a.group_key,
    //             employee_polices.id as primaryKey, 
    //             employees.name AS emp_name,
    //             employees.emp_code AS emp_code,
    //             employees.dob AS emp_dob,
    //             employees.gender AS emp_gender,
    //             employees.relationship AS emp_relationship,
    //             employees.relationship_code AS emp_relationship_code,
    //             employees.emp_type as emp_type,
    //             'D' as event_type_data,
    //             TIMESTAMPDIFF(YEAR, employees.dob, CURDATE()) AS emp_age,

    //             employees.doj AS emp_doj,
    //             employees.mobile AS emp_mobile,
    //             employees.email_corporate AS emp_email_c,
    //             employees.email_personal AS emp_email_p,
    //             employees.band AS emp_grade,
    //             employees.designation AS emp_designation,
    //             employees.basic_pay AS emp_basic_pay,
                
    //             employee_polices.basic_cover_si, 
    //             employee_polices.uhid as uhid, 
    //             employee_polices.policy_end_date, 
    //             employee_polices.rata_premimum as premium, 
    //             employee_polices.claim_status,
                
    //             batch_data.emp_policy_id AS emp_policy_id,
    //             batch_data.bl AS batch_list_batch_code,
    //             batch_data.bf AS batch_files_batch_code,
                
    //             deletiondata.empstatus, 
    //             deletiondata.changeevent,
    //             deletiondata.dateofexit,
    //             deletiondata.reasonforexit,
    //             deletiondata.status,
                
    //             DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + '$add_one_day' AS no_of_days,
                
                
    //             CASE 
    //                 WHEN employee_polices.claim_status = 0 THEN 
    //                     ROUND((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365, 2)
    //                 ELSE 
    //                     0
    //             END AS pro_rata_premium,
                
    //             CASE 
    //                 WHEN employee_polices.claim_status = 0 THEN 
    //                     ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18, 2)
    //                 ELSE 
    //                     0
    //             END AS gst,
                
    //             CASE 
    //                 WHEN employee_polices.claim_status = 0 THEN 
    //                     ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) + 
    //                         (((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18), 2)
    //                 ELSE 
    //                     0
    //             END AS total,
                
    //             CASE 
    //                 WHEN employee_polices.claim_status = 0 THEN 
    //                     'No claim'
    //                 ELSE 
    //                     'Claim'
    //             END AS claim_status            
            
    //         FROM 
    //             emp_endorsement a
    //         LEFT JOIN 
    //             employees ON a.emp_code = employees.emp_code
    //         LEFT JOIN 
    //             employee_polices ON employees.id = employee_polices.employee_id 
            
    //         LEFT JOIN( 
        
    //          select aa.emp_code, aa.new_value as 'empstatus', bb.new_value as 'changeevent', cc.new_value as 'dateofexit', dd.new_value as 'reasonforexit', ee.new_value as 'status' from
                
    //             ( SELECT  a1.emp_code, a1.field_name, a1.new_value from emp_endorsement as a1 where a1.field_name = 'emp_status' and a1.status != 'truncated') aa 
    //             left join 
    //             ( SELECT  b1.emp_code, b1.field_name, b1.new_value from emp_endorsement as b1 where b1.field_name = 'change_event' and b1.status != 'truncated') bb on aa.emp_code = bb.emp_code 
    //             left join
    //             ( SELECT  c1.emp_code, c1.field_name, c1.new_value from emp_endorsement as c1  where c1.field_name = 'date_of_exit' and c1.status != 'truncated') cc on aa.emp_code = cc.emp_code
    //             left join
    //             ( SELECT  d1.emp_code, d1.field_name, d1.new_value from emp_endorsement as d1  where d1.field_name = 'reason_for_exit' and d1.status != 'truncated') dd on aa.emp_code = dd.emp_code 
    //             left JOIN
    //             ( SELECT  e1.emp_code, e1.field_name, e1.new_value from emp_endorsement as e1  where e1.field_name = 'status' and e1.status != 'truncated') ee on aa.emp_code = ee.emp_code
                
    //         ) as deletiondata on a.emp_code = deletiondata.emp_code and a.status != 'truncated'
        
    //         LEFT JOIN 
    //             (
    //             SELECT  
    //                 batch_list.emp_policy_id,
    //                 batch_list.batch_code AS bl,
    //                 batch_files.batch_code AS bf
    //             FROM 
    //                 batch_files
    //             LEFT JOIN 
    //                 batch_list ON batch_files.batch_code = batch_list.batch_code 
    //             WHERE 
    //                 batch_files.event_type = 'deletion'
    //                 AND batch_files.actions = 'export'
    //                 AND batch_files.insurer_or_tpa = '{$insurer_or_tpa}'
    //             ) AS batch_data ON employee_polices.id = batch_data.emp_policy_id 

    //         WHERE employee_polices.client_policy_id = {$client_policy_id}
    //             AND employees.client_branch_id = {$client_branch_id}
    //             $endorsement_condition
    //             AND a.actions = 'd'
    //             AND a.status != 'truncated'
    //             AND employee_polices.is_active = 1
    //             AND employees.is_active = 1
    //             AND $status_condition
    //             group by group_key
    //     ");
    
    //     if($return_type == 1){
    //         $result = $query->getResultArray();
    //     }else{
    //         $result = $query->getResult();
    //     }

    //     // dd($this->db->getLastQuery(), $result);

    //     return $result;
        
    // }

    public function getDeletionEmployeeDataForExportExcel($ref_data, $return_type = 0)
    {

            $client_id = $ref_data['client_id'];
            $client_policy_id = $ref_data['client_policy_id'];
            $client_branch_id = $ref_data['client_branch_id'];
            $insurer_or_tpa = $ref_data['insurer_or_tpa'];

            $get_insurer_id_from_client_policy = $this->db->table('client_policy')
                ->select('insurer_id')
                ->where('id', $client_policy_id)
                ->get()
                ->getRowArray();

            $add_one_day = 0;

            if (!empty($get_insurer_id_from_client_policy)) {

                $get_the_insurer_add_one_for_delete = $this->db->table('insurers')
                    ->select('deletion_add_day')
                    ->where('id', $get_insurer_id_from_client_policy['insurer_id'])
                    ->get()
                    ->getRowArray();

                if (!empty($get_the_insurer_add_one_for_delete) && $get_the_insurer_add_one_for_delete['deletion_add_day'] == 1) {
                    $add_one_day = 1;
                }
            }

            $status_condition = "{$insurer_or_tpa}" === 'tpa' 
                ? "employee_polices.status = 'inactive' AND employees.emp_status = 'inactive'" 
                : "employee_polices.status = 'active' AND employees.emp_status = 'active'";

            $endorsement_condition = "{$insurer_or_tpa}" === 'tpa' ? "AND (a.endorsement_id IS NOT NULL OR a.endorsement_id != '')" : "AND (a.endorsement_id IS NULL OR a.endorsement_id = '')";

            $query = $this->db->query("
            SELECT DISTINCT
                a.id as endorsement_primarykey,
                a.group_key,
                employee_polices.id as primaryKey, 
                employees.name AS emp_name,
                employees.emp_code AS emp_code,
                employees.dob AS emp_dob,
                employees.gender AS emp_gender,
                employees.relationship AS emp_relationship,
                employees.relationship_code AS emp_relationship_code,
                employees.emp_type as emp_type,
                'D' as event_type_data,
                TIMESTAMPDIFF(YEAR, employees.dob, CURDATE()) AS emp_age,

                employees.doj AS emp_doj,
                employees.mobile AS emp_mobile,
                employees.email_corporate AS emp_email_c,
                employees.email_personal AS emp_email_p,
                employees.band AS emp_grade,
                employees.designation AS emp_designation,
                employees.basic_pay AS emp_basic_pay,
                
                employee_polices.basic_cover_si, 
                employee_polices.uhid as uhid, 
                employee_polices.policy_end_date, 
                employee_polices.rata_premimum as premium, 
                employee_polices.claim_status,
                
                batch_data.emp_policy_id AS emp_policy_id,
                batch_data.bl AS batch_list_batch_code,
                batch_data.bf AS batch_files_batch_code,
                
                deletiondata.empstatus, 
                deletiondata.changeevent,
                deletiondata.dateofexit,
                deletiondata.reasonforexit,
                deletiondata.status,
                deletiondata.claimstatus,
                
                DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + '$add_one_day' AS no_of_days,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365, 2)
                    ELSE 
                        0
                END AS pro_rata_premium,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18, 2)
                    ELSE 
                        0
                END AS gst,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) + 
                            (((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18), 2)
                    ELSE 
                        0
                END AS total,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        'No claim'
                    ELSE 
                        'Claim'
                END AS claim_status            
            
            FROM 
                emp_endorsement a
            LEFT JOIN 
                employee_polices ON a.pk = employee_polices.id
            LEFT JOIN 
                employees ON employee_polices.employee_id = employees.id

            LEFT JOIN (
                SELECT 
                    emp_code,
                    group_key,
                    MAX(CASE WHEN field_name = 'emp_status' THEN new_value END) AS empstatus,
                    MAX(CASE WHEN field_name = 'change_event' THEN new_value END) AS changeevent,
                    MAX(CASE WHEN field_name = 'date_of_exit' THEN new_value END) AS dateofexit,
                    MAX(CASE WHEN field_name = 'reason_for_exit' THEN new_value END) AS reasonforexit,
                    MAX(CASE WHEN field_name = 'status' THEN new_value END) AS status,
                    MAX(CASE WHEN field_name = 'claim_status' THEN new_value END) AS claimstatus
                FROM 
                    emp_endorsement
                WHERE 
                    status != 'truncated' 
                GROUP BY 
                    group_key
                    
            ) AS deletiondata 
            ON a.emp_code = deletiondata.emp_code AND a.status != 'truncated'
        
            LEFT JOIN 
            (
                SELECT  
                    batch_list.emp_policy_id,
                    batch_list.batch_code AS bl,
                    batch_files.batch_code AS bf
                FROM 
                    batch_files
                LEFT JOIN 
                    batch_list ON batch_files.batch_code = batch_list.batch_code 
                WHERE 
                    batch_files.event_type = 'deletion'
                    AND batch_files.actions = 'export'
                    AND batch_files.insurer_or_tpa = '{$insurer_or_tpa}'
            ) AS batch_data ON employee_polices.id = batch_data.emp_policy_id 

            WHERE employee_polices.client_policy_id = {$client_policy_id}
                AND employees.client_branch_id = {$client_branch_id}
                $endorsement_condition
                AND a.actions = 'd'
                AND a.status != 'truncated'
                AND employee_polices.is_active = 1
                AND employees.is_active = 1
                AND $status_condition
                group by group_key
        ");
    
        if($return_type == 1){
            $result = $query->getResultArray();
        }else{
            $result = $query->getResult();
        }

        // dd($this->db->getLastQuery(), $result);
        // dd($result);

        return $result;
        
    }


    public function fetchEmpEndorsementData($fetch_data)
    {
        // dd($fetch_data);

        $client_policy_id = $fetch_data['client_policy_id'];
        $client_branch_id = $fetch_data['client_branch_id'];
        $client_id = $fetch_data['client_id'];
        $emp_name = $fetch_data['emp_name'];
        $emp_code = $fetch_data['emp_code'];

        // Your raw SQL query
        $sql = "
                SELECT
                    ee.id as emp_endorsement_primarykey,
                    ep.id as emp_policy_primarykey,
                    e.id as employees_primarykey,
                    ee.file_id,
                    ee.group_key,
                    (
                        SELECT employees.id
                        FROM employees
                        WHERE client_id = '$client_id'
                            AND client_branch_id = '$client_branch_id'
                            AND is_active = 1
                            AND emp_status = 'active'
                            AND emp_code = '$emp_code'
                            AND name = '$emp_name'
                        LIMIT 1
                    ) AS employees_id,

                    MAX(
                    CASE
                        WHEN ee.field_name = 'date_of_exit' THEN ee.new_value
                    END
                    ) AS date_of_exit,
                    MAX(
                    CASE
                        WHEN ee.field_name = 'reason_for_exit' THEN ee.new_value
                    END
                    ) AS reason_for_exit,
                    MAX(
                    CASE
                        WHEN ee.field_name = 'status' THEN ee.new_value
                    END
                    ) AS status,
                    MAX(
                    CASE
                        WHEN ee.field_name = 'claim_status' THEN ee.new_value
                    END
                    ) AS claim_status
                FROM
                    emp_endorsement AS ee
                    JOIN employee_polices AS ep ON ep.id = ee.pk
                    JOIN employees AS e ON e.emp_code = ee.emp_code
                WHERE
                    ee.emp_code = '$emp_code'
                    AND ep.client_policy_id = '$client_policy_id'
                    AND e.client_branch_id = '$client_branch_id'
                    AND ee.name = '$emp_name'
                    AND ee.field_name IN ('date_of_exit', 'reason_for_exit', 'status', 'claim_status')
                    AND ep.is_active = 1
                    AND ep.status = 'active'
                    AND e.is_active = 1
                    AND e.emp_status = 'active'
                GROUP BY
                    ee.group_key
        ";

        // dd($sql);

        // Execute the raw SQL query
        $query = $this->db->query($sql);

        // Fetch and return results
         $row = $query->getRowArray();

         return $row;
    }
    

    //for emp onboard do not change

    // public function checkExistingEmpPolicy($arr)
    // {
    //       return $this->where('employee_id',$arr['employee_id'])
    //                     ->where('client_policy_id',$arr['client_policy_id'])
    //                     ->find();
    // }


    public function updateSiAndPremium( $client_policy_id, $employee_id,$basic_cover_si,$premium = 0)
    {

      
        $query = "UPDATE employee_polices
                    SET employee_polices.basic_cover_si = '{$basic_cover_si}'
                    , employee_polices.premium = '{$premium}'
                    WHERE employee_polices.employee_id = '{$employee_id}'
                    AND employee_polices.client_policy_id = '{$client_policy_id}'";

        $this->query($query);
    }


    public function getEmployeeEndorsementList($client_id, $policy_id, $status, $branch_id)
    {   
        $query1 = $this->db->table('emp_endorsement e');
        $query1->select('
                    e.id, 
                    e.pk, 
                    e.emp_code, 
                    e.name, 
                    e.actions, 
                    e.status, 
                    e.remarks, 
                    e.endorsement_id, 
                    ep.client_policy_id, 
                    insurers.short_name as insurer_short_name,
                    client_policy.policy_no,
                    policy_type.policy_type
                ');
                $query1->distinct();
                $query1->join('employee_polices ep', 'ep.id = e.pk');
                $query1->join('employees', 'employees.id = ep.employee_id');
                $query1->join('client_policy', 'client_policy.id = ep.client_policy_id');
                $query1->join('client_branch', 'client_branch.id = employees.client_branch_id');
                // $query1->join('policies', 'policies.id = client_policy.policy_id');
                $query1->join('policy_type', 'policy_type.id = client_policy.policy_type_id');
                $query1->join('insurers', 'insurers.id = client_policy.insurer_id');
                $query1->whereIn('e.actions', ['c']);
                $query1->where('ep.client_policy_id', $policy_id);
                $query1->where('employees.client_id', $client_id);
                $query1->where('employees.client_branch_id', $branch_id);
                if($status != 0 && !empty($status)){

                    $query1->where('e.status', $status);
                }
                $query1->orderBy('e.id', 'desc'); 
    
        $results1 = $query1->get()->getResultArray();
        
        $query2 = $this->db->table('emp_endorsement e');

            $query2->select('
                    e.id, 
                    e.pk, 
                    e.emp_code, 
                    e.name, 
                    e.actions, 
                    e.status, 
                    e.endorsement_id, 
                    e.remarks, 
                    ep.client_policy_id, 
                    insurers.short_name as insurer_short_name,
                    client_policy.policy_no,
                    policy_type.policy_type
                ');
                $query2->join('employee_polices ep', 'ep.id = e.pk');
                $query2->join('employees', 'employees.id = ep.employee_id');
                $query2->join('client_policy', 'client_policy.id = ep.client_policy_id');
                $query2->join('client_branch', 'client_branch.id = employees.client_branch_id');
                // $query2->join('policies', 'policies.id = client_policy.policy_id');
                $query2->join('policy_type', 'policy_type.id = client_policy.policy_type_id');
                $query2->join('insurers', 'insurers.id = client_policy.insurer_id');
                $query2->whereIn('e.actions', ['si', 'd', 'a']);
                $query2->where('ep.client_policy_id', $policy_id);
                $query2->where('employees.client_id', $client_id);
                $query2->where('employees.client_branch_id', $branch_id);
                if($status != 0 && !empty($status)){

                    $query2->where('e.status', $status);
                }
                $query2->groupBy('e.emp_code, e.name, e.actions');
                $query2->orderBy('e.id', 'desc'); 
        
        $results2 = $query2->get()->getResultArray();
        
        $results = array_merge($results1, $results2);

        // dd($this->db->getLastQuery());
        
        return $results;
    
        
    }
    
    //----------------------------------------------------------------------------------------------


    public function getEmployeePolicyForFileList($client_id, $policy_id)
    {
         $result =  $this->select(['employee_polices.*','pt.policy_type','im.short_name as insurer_short_name','ib.branch_name as insurer_branch_name','ib.branch_code as insurer_branch_code','tpam.name as tpa_name','tpam.short_name as tpa_short_name','tpab.branch_code as tpa_branch_code','cm.client_name','cm.short_name as client_short_name','emp.relationship','emp.relationship_code','emp.change_event','emp.emp_code','emp.name','emp.email_corporate','emp.dob','emp.gender','emp.emp_status','emp.is_active as emp_is_active','emp.mobile as mobile'])
                         ->join('employees emp', 'employee_polices.employee_id = emp.id') 
                         ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id') //cp - client policy
                         ->join('policy_type pt', 'cp.policy_type_id = pt.id') //pm - policy master
                         ->join('insurers im', 'cp.insurer_id = im.id') //im - insurar master
                         ->join('insurer_branch ib', 'cp.insurer_branch_id = ib.id') //ib - insurar branch
                         ->join('tpa tpam', 'cp.tpa_id = tpam.id') //tpam - tpa master
                         ->join('tpa_branch tpab', 'cp.tpa_branch_id = tpab.id') //tpab - tpa brach
                         ->join('clients cm', 'cp.client_id = cm.id') //cm - client master
                         ->where('emp.client_id',$client_id)
                         ->where('employee_polices.client_policy_id',$policy_id)
                         ->where('employee_polices.is_active',1)
                         ->where('emp.is_active',1);

         $result->whereIn('employee_polices.status', ['enrolled']);
         $result->whereIn('emp.emp_status', ['enrolled']);
         $result = $result->findAll();  
         // print_r($this->db->getLastQuery());
         return ($result);
    }


    public function getViewEmpSuccessList($file_id)
    {
        return   $this->db->table('employees emp')
            ->select(['employee_polices.*','pm.name as policy_name','im.short_name as insurer_short_name','ib.branch_name as insurer_branch_name','ib.branch_code as insurer_branch_code','tpam.name as tpa_name','tpam.short_name as tpa_short_name','tpab.branch_code as tpa_branch_code','cm.client_name','cm.short_name as client_short_name','emp.relationship','emp.relationship_code','emp.change_event','emp.emp_code','emp.name','emp.email_corporate','emp.dob','emp.gender','emp.emp_status','emp.is_active as emp_is_active','emp.mobile as mobile'])
            ->join('employee_polices', 'employee_polices.employee_id = emp.id') 
            ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id') //cp - client policy
            ->join('policies pm', 'cp.policy_id = pm.id') //pm - policy master
            ->join('insurers im', 'cp.insurer_id = im.id') //im - insurar master
            ->join('insurer_branch ib', 'cp.insurer_branch_id = ib.id') //ib - insurar branch
            ->join('tpa tpam', 'cp.tpa_id = tpam.id') //tpam - tpa master
            ->join('tpa_branch tpab', 'cp.tpa_branch_id = tpab.id') //tpab - tpa brach
            ->join('clients cm', 'cp.client_id = cm.id') //cm - client master
            ->where('emp.file_id',$file_id)
            ->whereIn('employee_polices.status', ['draft', 'enrolled'])
            ->get()->getResultArray();
    }

    public function getECardDataUsingMd5($client_policy_id, $emp_code,$client_id){

        $result = $this->db->table('employees e')
        ->select('e.id, 
                    e.name, e.mobile, 
                    e.relationship, 
                    e.relationship_code, 
                    e.emp_code, 
                    e.email_personal, 
                    e.email_corporate, 
                    e.gender, 
                    e.dob, 
                    e.doj, 
                    e.band,
                    TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) AS emp_age,
                    (SELECT name FROM employees WHERE relationship = "Self" and emp_code = ' . $this->db->escape($emp_code) . ' and client_id = '.$client_id.' LIMIT 1) AS self,


                    ep.tpa_id, 
                    ep.uhid,
                    ep.policy_end_date,
                    ep.basic_cover_si,

                    clients.client_name, 
                    clients.short_name AS client_short_name,

                    cp.policy_start_date,
                    cp.policy_no,

                    insurers.name AS insurer_name, 
                    insurers.short_name AS insurer_short_name,
                    insurers.insurer_logo,

                    insurer_branch.branch_name,                    
                    insurer_branch.branch_code,                    
                    insurer_branch.city as insurer_branch_city,                    

                    tpa.name AS tpa_name, 
                    tpa.tpa_logo AS tpa_logo, 
                    tpa.front_card, 
                    tpa.back_card, 
                    tpa.short_name AS tpa_short_name'
                )

        ->join('employee_polices ep', 'ep.employee_id = e.id')
        ->join('client_policy cp', 'cp.id = ep.client_policy_id')
        ->join('clients', 'clients.id = cp.client_id')
        ->join('insurers', 'insurers.id = cp.insurer_id')
        ->join('insurer_branch', 'insurer_branch.id = cp.insurer_branch_id')
        ->join('tpa', 'tpa.id = cp.tpa_id')
        ->where("ep.tpa_id IS NOT NULL AND ep.tpa_id <> ''")
        ->where('e.emp_status', 'active')
        ->where('e.is_active', '1')
        ->where('ep.status', 'active')
        ->where('ep.is_active', '1')
        ->where('e.emp_code', $emp_code)
        ->where('ep.client_policy_id', $client_policy_id)
        ->get()
        ->getResultArray();
    
       return $result;

    }    
    
    
    public function getECardSingleData($rand_string, $emp_code,$client_id){

        $result = $this->db->table('employees e')
        ->select('e.id, 
                    e.name, e.mobile, 
                    e.relationship, 
                    e.relationship_code, 
                    e.emp_code, 
                    e.email_personal, 
                    e.email_corporate, 
                    e.gender, 
                    e.dob, 
                    e.doj, 
                    e.band,
                    TIMESTAMPDIFF(YEAR, e.dob, CURDATE()) AS emp_age,
                    (SELECT name FROM employees WHERE relationship = "Self" and emp_code = ' . $this->db->escape($emp_code) . ' and client_id = '.$client_id.' LIMIT 1) AS self,


                    ep.tpa_id, 
                    ep.uhid,
                    ep.policy_end_date,
                    ep.basic_cover_si,

                    clients.client_name, 
                    clients.short_name AS client_short_name,

                    cp.policy_start_date,
                    cp.policy_no,

                    insurers.name AS insurer_name, 
                    insurers.short_name AS insurer_short_name,
                    insurers.insurer_logo,

                    insurer_branch.branch_name,                    
                    insurer_branch.branch_code,                    
                    insurer_branch.city as insurer_branch_city,                    

                    tpa.name AS tpa_name, 
                    tpa.tpa_logo AS tpa_logo, 
                    tpa.front_card, 
                    tpa.back_card, 
                    tpa.short_name AS tpa_short_name'
                )

        ->join('employee_polices ep', 'ep.employee_id = e.id')
        ->join('client_policy cp', 'cp.id = ep.client_policy_id')
        ->join('clients', 'clients.id = cp.client_id')
        ->join('insurers', 'insurers.id = cp.insurer_id')
        ->join('insurer_branch', 'insurer_branch.id = cp.insurer_branch_id')
        ->join('tpa', 'tpa.id = cp.tpa_id')
        ->where("ep.tpa_id IS NOT NULL AND ep.tpa_id <> ''")
        ->where('e.emp_status', 'active')
        ->where('e.is_active', '1')
        ->where('ep.status', 'active')
        ->where('ep.is_active', '1')
        ->where('ep.rand_string', $rand_string)
        ->get()
        ->getResultArray();
    
       return $result;

    }
    

    public function getExisitingMobileNos(string $client_policy_id)
    {
        $result =  $this->select(['employee_polices.id','emp.id as emp_id','emp.relationship','emp.emp_code','emp.name','emp.email_corporate','emp.mobile as mobile'])
                         ->join('employees emp', 'employee_polices.employee_id = emp.id') 
                         ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id')
                         ->where('employee_polices.client_policy_id',$client_policy_id)
                         ->whereIn('employee_polices.status', ['draft', 'enrolled'])
                         ->whereIn('emp.emp_status', ['draft', 'enrolled'])
                         ->where('emp.is_active', 1)
                         ->where('emp.relationship', 'Self')
                         ->where('emp.mobile is not null')
                         ->where('employee_polices.is_active', 1)
                         ->findAll();        
         
         return ($result);
    }


    public function bulkUpdate($emp_details)
    {
        // Extract IDs, tpa_ids, and uhids
        $ids = array_column($emp_details, 'id');
        $tpa_ids = array_column($emp_details, 'tpa_id');
        $uhids = array_column($emp_details, 'uhid');

        // Escape values for SQL
        $escapedIds = array_map([$this->db, 'escape'], $ids);
        $escapedTpaIds = array_map([$this->db, 'escape'], $tpa_ids);
        $escapedUhids = array_map([$this->db, 'escape'], $uhids);

        // Construct the CASE statements
        $caseTpaId = array_map(function($id, $tpa_id) {
            return "WHEN id = $id THEN $tpa_id";
        }, $escapedIds, $escapedTpaIds);

        $caseUhid = array_map(function($id, $uhid) {
            return "WHEN id = $id THEN $uhid";
        }, $escapedIds, $escapedUhids);

        // Convert cases to a string
        $caseTpaIdString = implode(' ', $caseTpaId);
        $caseUhidString = implode(' ', $caseUhid);

        // Convert ids to a string
        $idsString = implode(', ', $escapedIds);

        // Construct the SQL query
        $sql = "
            UPDATE {$this->table}
            SET 
                tpa_id = CASE {$caseTpaIdString} END,
                uhid = CASE {$caseUhidString} END
            WHERE id IN ({$idsString})
        ";

        // Begin a transaction
        $this->db->transBegin();

        try {
            // Execute the query
            $this->db->query($sql);
            

            // Commit the transaction
            if ($this->db->transStatus() === FALSE) {
                // If something went wrong, rollback
                $this->db->transRollback();
                throw new \Exception('Bulk update failed.');
            } else {
                // Otherwise, commit
                $this->db->transCommit();
            }

            return $this->db->getLastQuery();
            
        } catch (\Exception $e) {
            // Rollback the transaction on error
            $this->db->transRollback();
            throw $e;
        }
    }


    public function bulkUpdateForEndorsement($endorsement_details)
    {

        // Extract IDs, endorsement_ids, and statuses
        $ids = array_column($endorsement_details, 'group_key');
        $endorsement_ids = array_column($endorsement_details, 'endorsement_id');
        $statuses = array_column($endorsement_details, 'status');

        // Escape values for SQL
        $escapedIds = array_map([$this->db, 'escape'], $ids);
        $escapedEndorsementIds = array_map([$this->db, 'escape'], $endorsement_ids);
        $escapedStatuses = array_map([$this->db, 'escape'], $statuses);

        // Construct the CASE statements
        $caseEndorsementId = array_map(function ($id, $endorsement_id) {
            return "WHEN group_key = $id THEN $endorsement_id";
        }, $escapedIds, $escapedEndorsementIds);

        // $caseStatus = array_map(function ($id, $status) {
        //     return "WHEN status = $id THEN $status";
        // }, $escapedIds, $escapedStatuses);

        // Convert cases to a string
        $caseEndorsementIdString = implode(' ', $caseEndorsementId);
        // $caseStatusString = implode(' ', $caseStatus);

        // Convert ids to a string
        $idsString = implode(', ', $escapedIds);

        // Construct the SQL query
        $sql = "
            UPDATE emp_endorsement
            SET 
                endorsement_id = CASE {$caseEndorsementIdString} END,
                status = 'complete'
            WHERE group_key IN ({$idsString})
        ";


        // dd($sql);
        // Begin a transaction
        $this->db->transBegin();

        try {
            // Execute the query
            $this->db->query($sql);

            // Commit the transaction
            if ($this->db->transStatus() === FALSE) {
                // If something went wrong, rollback
                $this->db->transRollback();
                throw new \Exception('Bulk update failed.');
            } else {
                // Otherwise, commit
                $this->db->transCommit();
            }
            // $this->storeEndorsementNumber($file_id, $endorsement_id, $enrollment_file_id);

            return $this->db->getLastQuery();
        } catch (\Exception $e) {
            // Rollback the transaction on error
            $this->db->transRollback();
            throw $e;
        }
    }
    

    public function bulkUpdateForCorrection($emp_details){

        foreach ($emp_details as $employee) {
            $id = $this->db->escape($employee['id']);
            $ids[] = $id;
        
            foreach ($employee as $field => $value) {
                if ($field === 'id') continue;
                $escapedValue = $this->db->escape($value);
        
                if (!isset($caseStatements[$field])) {
                    $caseStatements[$field] = [];
                }
        
                $caseStatements[$field][] = "WHEN id = $id THEN $escapedValue";
            }
        }
        
        // Construct the CASE strings
        $caseStrings = [];
        foreach ($caseStatements as $field => $cases) {
            $caseStrings[] = "$field = CASE " . implode(' ', $cases) . " END";
        }
        
        // Convert ids to a string
        $idsString = implode(', ', $ids);
        
        // Construct the SQL query
        $sql = "
            UPDATE employees
            SET " . implode(', ', $caseStrings) . "
            WHERE id IN ($idsString)
        ";
        
        // Begin a transaction
        $this->db->transBegin();
        
        try {
            // Execute the query
            $this->db->query($sql);
        
            // Commit the transaction
            if ($this->db->transStatus() === FALSE) {
                // If something went wrong, rollback
                $this->db->transRollback();
                throw new \Exception('Bulk update failed.');
            } else {
                // Otherwise, commit
                $this->db->transCommit();
            }
        
            return $this->db->getLastQuery();
            
        } catch (\Exception $e) {
            // Rollback the transaction on error
            $this->db->transRollback();
            throw $e;
        }

    }

    //for truncate get deletion data
    public function getDeletionDataForTruncated($file_id)
    {   

        // Fetch the necessary details in a single query using JOINs
        $result = $this->db->table('files f')
            ->select('i.deletion_add_day')
            ->join('client_policy cp', 'cp.id = f.policy_id', 'left')
            ->join('insurers i', 'i.id = cp.insurer_id', 'left')
            ->where('f.id', $file_id)
            ->get()
            ->getRowArray();

        // Determine if one day should be added for deletion
        $add_one_day = (!empty($result) && $result['deletion_add_day'] == 1) ? 1 : 0;


        $query = $this->db->query("

            SELECT DISTINCT

                a.id as endorsement_primarykey,
                a.group_key,
                employee_polices.id as primaryKey, 
                employees.name AS emp_name,
                employees.emp_code AS emp_code,
                employees.dob AS emp_dob,
                employees.gender AS emp_gender,
                employees.relationship AS emp_relationship,
                employees.relationship_code AS emp_relationship_code,
                employees.emp_type as emp_type,
                'D' as event_type_data,
                TIMESTAMPDIFF(YEAR, employees.dob, CURDATE()) AS emp_age,

                employees.doj AS emp_doj,
                employees.mobile AS emp_mobile,
                employees.email_corporate AS emp_email_c,
                employees.email_personal AS emp_email_p,
                employees.band AS emp_grade,
                employees.designation AS emp_designation,
                employees.basic_pay AS emp_basic_pay,
                
                employee_polices.basic_cover_si, 
                employee_polices.uhid as uhid, 
                employee_polices.policy_end_date, 
                employee_polices.rata_premimum as premium, 
                employee_polices.claim_status,
                                
                deletiondata.empstatus, 
                deletiondata.changeevent,
                deletiondata.dateofexit,
                deletiondata.reasonforexit,
                deletiondata.status,
                deletiondata.claimstatus,
                
                DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + '$add_one_day' AS no_of_days,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365, 2)
                    ELSE 
                        0
                END AS pro_rata_premium,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18, 2)
                    ELSE 
                        0
                END AS gst,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        ROUND(((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) + 
                            (((employee_polices.rata_premimum * (DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) + $add_one_day)) / 365) * 0.18), 2)
                    ELSE 
                        0
                END AS total,
                
                CASE 
                    WHEN deletiondata.claimstatus = 0 THEN 
                        'No claim'
                    ELSE 
                        'Claim'
                END AS claim_status            
            
            FROM 
                emp_endorsement a
            LEFT JOIN 
                employee_polices ON a.pk = employee_polices.id
            LEFT JOIN 
                employees ON employee_polices.employee_id = employees.id

            LEFT JOIN (
                SELECT 
                    emp_code,
                    group_key,
                    MAX(CASE WHEN field_name = 'emp_status' THEN new_value END) AS empstatus,
                    MAX(CASE WHEN field_name = 'change_event' THEN new_value END) AS changeevent,
                    MAX(CASE WHEN field_name = 'date_of_exit' THEN new_value END) AS dateofexit,
                    MAX(CASE WHEN field_name = 'reason_for_exit' THEN new_value END) AS reasonforexit,
                    MAX(CASE WHEN field_name = 'status' THEN new_value END) AS status,
                    MAX(CASE WHEN field_name = 'claim_status' THEN new_value END) AS claimstatus
                FROM 
                    emp_endorsement
                WHERE 
                    status != 'truncated' 
                GROUP BY 
                    group_key
                    
            ) AS deletiondata 

            ON a.emp_code = deletiondata.emp_code AND a.status != 'truncated'

            WHERE a.file_id = {$file_id}
                AND a.actions = 'd'
                AND a.status != 'truncated'
                AND employee_polices.is_active = 1
                AND employees.is_active = 1
                group by group_key
        ");
    

        $result = $query->getResultArray();
        // dd($this->db->getLastQuery(), $result);
        // dd($result);

        return $result;        

    }

    //for truncate get addition data
    public function getAdditionDataForTruncated($file_id, $event_type)
    {   
        $action = "a";
        if($event_type == "dependent_addition"){
            $action = "da";
        }

        $query = $this->db->query("

           SELECT 
                ROUND(SUM(ep.rata_premimum + ep.gst), 2) AS total
            FROM 
                emp_endorsement ee
            JOIN 
                employee_polices ep ON ee.pk = ep.id
            WHERE 
                ee.file_id = $file_id
                AND ee.actions = '$action'
                AND ep.status != 'truncated'
                AND ee.status != 'truncated'
                AND ee.is_active = 1
                AND ep.is_active = 1;
        ");
    

        $result = $query->getResultArray();
        // dd($this->db->getLastQuery(), $result);
        // dd($result);

        return $result[0];        

    }

    // reverse the employee policy table data for the truncated the deletion file
    public function updateEmployeePolicyTruncateReverse($file_id)
    {
        $this->db->query("
            UPDATE employee_polices
            SET 
                is_active = 1,
                status = 'active',
                date_of_exit = NULL,
                reason_for_exit = NULL,
                claim_status = 0,
            WHERE 
                id IN (
                    SELECT pk 
                    FROM emp_endorsement 
                    WHERE file_id = $file_id 
                    GROUP BY group_key
                )
            AND status = 'inactive'
        ");

    }

    //update employee policy table status truncated and is_active 0 for truncated to the addition file
    public function updateEmpEndorsementAddition($array)
    {
        $caseStatus = "CASE ";
        $caseEndorsementId = "CASE ";
        $ids = [];
        foreach ($array as $item) {
            $caseStatus .= "WHEN id = {$item['id']} THEN '{$item['status']}' ";
            $caseEndorsementId .= "WHEN id = {$item['id']} THEN '{$item['endorsement_id']}' ";
            $ids[] = $item['id'];
        }
        $caseStatus .= "END";
        $caseEndorsementId .= "END";
        $ids = implode(',', $ids);
        $query = "UPDATE emp_endorsement SET status = $caseStatus, endorsement_id = $caseEndorsementId WHERE id IN ($ids);"; // Execute the query 
        $this->db->query($query);
    }

    //calculate the CD Transaction sum of the amount for the inception, addition, dependent addition, missed inception
    public function calculateCdTranctionAmount($file_id)
    {
        $query = $this->db->query("

            SELECT 
                ROUND(SUM(ep.rata_premimum + ep.gst), 2) AS total
            FROM 
                employee_polices ep
            WHERE 
                ep.file_id = $file_id
                AND ep.uhid IS NOT NULL'
                AND ep.status != 'truncated'
                AND ep.status != 'truncated'
                AND ep.is_active = 1;
        ");


        $result = $query->getResultArray();
        // dd($this->db->getLastQuery(), $result);
        // dd($result);

        return $result[0];
    }

    public function download_inception($client_id = 0, $branch_id = 0,  $policy_id = 0, $status = [], $emp_code = "", $emp_name = ""){
            
        $result = $this->select([

            'emp.emp_code AS `Emp ID`',
            'emp.name AS `Name of Emp/Dep`',
            "DATE_FORMAT(emp.dob, '%d-%b-%Y') AS `DOB`",
            'emp.gender As `Gender`',
            'emp.relationship As `Relationship`',

            'employee_polices.basic_cover_si As `Basic cover SI`',

            "DATE_FORMAT(employee_polices.date_coverage, '%d-%b-%Y') AS `Date of Coverage`",
            "DATE_FORMAT(emp.doj, '%d-%b-%Y') AS `DOJ`",
            'emp.basic_pay As `Basic Pay`',
            'emp.band  As `Band/Grade`',
            'emp.designation  As `Designation`',

            'emp.mobile as Phone',
            'emp.email_corporate As Email',

            'COALESCE(employee_polices.pre_existing_alignments, 0) as `PRE EXISTING AILMENTS`',

            'emp.change_event',
            'employee_polices.date_of_exit',
            'employee_polices.reason_for_exit',

            'emp.unit'
             
        ])
            ->join('employees emp', 'employee_polices.employee_id = emp.id')
            ->join('client_policy cp', 'employee_polices.client_policy_id = cp.id') //cp - client policy
            ->join('policies pm', 'cp.policy_id = pm.id', 'left') //pm - policy master
            ->join('policy_type', 'policy_type.id = cp.policy_type_id')
            ->join('insurers im', 'cp.insurer_id = im.id', 'left') //im - insurer master
            ->join('insurer_branch ib', 'cp.insurer_branch_id = ib.id', 'left') //ib - insurer branch
            ->join('tpa tpam', 'cp.tpa_id = tpam.id', 'left') //tpam - tpa master
            ->join('tpa_branch tpab', 'cp.tpa_branch_id = tpab.id', 'left') //tpab - tpa branch
            ->join('clients cm', 'cp.client_id = cm.id') //cm - client master
            ->join('client_branch', 'emp.client_branch_id = client_branch.id') //cm - client master
            ->orderBy('emp.emp_code', 'ASC')
            ->orderBy('employee_polices.employee_id', 'ASC');

       
            // Conditionally add where clauses
        if (is_string($client_id) && preg_match('/^[a-f0-9]{32}$/i', $client_id)) {
            $result->where('MD5(emp.client_id)', $client_id);
        } elseif ($client_id != 0 && !empty($client_id)) {
            $result->where('emp.client_id', $client_id);
        }
        if ($branch_id != 0 && !empty(trim($branch_id))) {
            $result->where('emp.client_branch_id', $branch_id);
        }
        if ($policy_id != 0 && !empty(trim($policy_id))) {
            $result->where('employee_polices.client_policy_id', $policy_id);
        }

        // if (is_array($status) && count($status) > 0) {

        //     $result->where('employee_polices.status !=', 'expired');

        //     if (in_array("active", $status)) {

        //         $result->where('employee_polices.tpa_id IS NOT NULL');
        //         $result->where('employee_polices.uhid IS NOT NULL');
        //         $result->whereIn('employee_polices.status', $status);
        //     } elseif (in_array("pending", $status)) {

        //         $result->where('employee_polices.tpa_id IS NULL');
        //         $result->where('employee_polices.uhid IS NULL');
        //         $result->whereIn('employee_polices.status', array_merge($status, ['active']));
        //     } else {

        //         $result->whereIn('employee_polices.status', $status);
        //     }
        // }

        $result->where('employee_polices.status =', 'enrolled');

        if (!empty(trim($emp_code))) {
            $result->where('emp.emp_code', $emp_code);
        }
        if (!empty(trim($emp_name))) {
            $result->like('emp.name', $emp_name);
        }

        // Always check these conditions
        $result->where('employee_polices.is_active', 1)
        ->where('emp.is_active', 1);

        $res = $result->findAll();

        return $res;
    }

    /**
     * Inception files whose enrollment window closed yesterday (send HR summary the next day).
     */
    public function getEnrollmentClosedFilesForHrMail()
    {
        $subQuery = '(SELECT MAX(id) as id
                      FROM files
                      WHERE status = "success"
                      AND enrollment_close_date IS NOT NULL
                      AND DATE(enrollment_close_date) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
                      GROUP BY policy_id) closed_files';

        $builder = $this->db->table('files')
            ->select([
                'files.id as file_id',
                'files.client_id',
                'files.client_branch_id',
                'files.policy_id',
                'files.enrollment_open_date',
                'files.enrollment_close_date',
                'cp.policy_type_id',
                'cp.is_addon',
            ])
            ->join($subQuery, 'closed_files.id = files.id')
            ->join('client_policy cp', 'cp.id = files.policy_id')
            ->where('cp.is_active', 1)
            ->where('cp.policy_status', 1);

        return $builder->get()->getResultArray();
    }

    /**
     * All active HR contacts for a client (across branches).
     */
    public function getClientHrContactsForMail(int $client_id): array
    {
        $rows = $this->db->table('level_contacts lc')
            ->select('lc.id, lc.name, lc.email')
            ->join('client_branch cb', 'lc.ref_id = cb.id')
            ->where('lc.contact_type', 'client')
            ->where('lc.is_active', 1)
            ->where('cb.client_id', $client_id)
            ->where('cb.is_active', 1)
            ->where('lc.email IS NOT NULL', null, false)
            ->where("lc.email != ''", null, false)
            ->orderBy('lc.name', 'ASC')
            ->get()
            ->getResultArray();

        $unique = [];
        foreach ($rows as $row) {
            $email = strtolower(trim($row['email'] ?? ''));
            if ($email === '' || isset($unique[$email])) {
                continue;
            }
            $unique[$email] = [
                'id' => $row['id'],
                'name' => trim($row['name'] ?? '') ?: 'HR Team',
                'email' => trim($row['email']),
            ];
        }

        return array_values($unique);
    }

    /**
     * Fetch a single inception file for HR enrollment-closed mail (test/manual use).
     */
    public function getEnrollmentClosedFileForHrMailById(int $file_id)
    {
        return $this->db->table('files')
            ->select([
                'files.id as file_id',
                'files.client_id',
                'files.client_branch_id',
                'files.policy_id',
                'files.enrollment_open_date',
                'files.enrollment_close_date',
                'cp.policy_type_id',
                'cp.is_addon',
            ])
            ->join('client_policy cp', 'cp.id = files.policy_id')
            ->where('files.id', $file_id)
            ->where('files.status', 'success')
            ->where('cp.is_active', 1)
            ->get()
            ->getRowArray();
    }

    /**
     * Enrollment status tile counts (same logic as enrollment_list.php).
     */
    public function getEnrollmentStatusTileCounts(int $client_id, int $branch_id, int $client_policy_id): array
    {
        $authSubQuery = '(SELECT user_id FROM auth_history WHERE user_type = "employee" GROUP BY user_id) auth_history';

        $rows = $this->db->table('employees')
            ->select('employees.id, employees.relationship, employees.emp_status, auth_history.user_id as logged_in_user_id')
            ->join('employee_polices', 'employees.id = employee_polices.employee_id')
            ->join($authSubQuery, 'employees.id = auth_history.user_id', 'left', false)
            ->where('employees.client_id', $client_id)
            ->where('employees.client_branch_id', $branch_id)
            ->where('employees.is_active', 1)
            ->where('employees.emp_status !=', 'truncated')
            ->where('employee_polices.client_policy_id', $client_policy_id)
            ->where('employee_polices.is_active', 1)
            ->where('employee_polices.status !=', 'truncated')
            ->groupBy('employees.id, employees.relationship, employees.emp_status, auth_history.user_id')
            ->get()
            ->getResultArray();

        $counts = [
            'emp_count' => 0,
            'enrolled' => 0,
            'not_enrolled' => 0,
            'logged_in' => 0,
            'not_logged_in' => 0,
            'draft' => 0,
        ];

        foreach ($rows as $row) {
            if (strtolower(trim($row['relationship'] ?? '')) !== 'self') {
                continue;
            }

            $counts['emp_count']++;

            $isEnrolled = ($row['emp_status'] ?? '') === 'enrolled';
            if ($isEnrolled) {
                $counts['enrolled']++;
            } else {
                $counts['not_enrolled']++;
            }

            $isLoggedIn = !empty($row['logged_in_user_id']);
            if ($isLoggedIn) {
                $counts['logged_in']++;
            } else {
                $counts['not_logged_in']++;
            }

            if (!$isEnrolled && $isLoggedIn) {
                $counts['draft']++;
            }
        }

        return $counts;
    }


}