<?php

namespace App\Models;

use CodeIgniter\Model;

class EmpEndorsementModel extends Model
{
    protected $table            = 'emp_endorsement';
    protected $primaryKey       = 'id';
    protected $allowedFields    = [
        'id',
        'pk',
        'group_key',
        'endorsement_id',
        'emp_code',
        'table_name',
        'actions',
        'name',
        'field_name',
        'old_value',
        'new_value',
        'date_of_correction',
        'status',
        "created_by",
        "updated_by",
        "is_active",
        "remarks",
        "file_id"
    ];

     // Callbacks
     protected $allowCallbacks       = true;
     protected $beforeInsert         = [];
     protected $afterInsert          = [];
     protected $beforeUpdate         = [];
     protected $afterUpdate          = [];
     protected $beforeFind           = [];
     protected $afterFind            = [];
     protected $beforeDelete         = [];
     protected $afterDelete          = [];


    //  protected function afterFind(array $data)
    //  {
    //      foreach ($data as &$row) {
    //          // Format date_of_coverage field to Indian date format
    //          if (isset($row['field_name'])) {
    //              $row['field_name'] = remove_underscore_capitalize_first_letter($row['field_name']);
    //          }
    //      }
 
    //      return $data;
    //  }


    public function getDataForEnodrsementListinSIEnhancement($group_key){
        $query = "
            SELECT 
            employee_polices.days as old_days,
            employee_polices.rata_premimum as old_rata,
            employee_polices.gst as old_gst,
            client_policy.policy_start_date as old_date,

            MAX(CASE WHEN emp_endorsement.field_name = 'basic_cover_si' THEN emp_endorsement.new_value ELSE NULL END) AS new_basic_cover_si,
            MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) AS new_si_premium,
            MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END) AS date_of_coverage,
            MAX(CASE WHEN emp_endorsement.field_name = 'basic_cover_si' THEN emp_endorsement.old_value ELSE NULL END) AS old_basic_cover_si,
            MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END) AS old_si_premium,

            (MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) AS difference_premium,

            DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value END)) + 1 AS no_of_days,

            ROUND(
                (
                    (
                        MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value END) - 
                        MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value END)
                    ) * 
                    DATEDIFF(
                        employee_polices.policy_end_date, 
                        MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value END)
                    ) / 365
                ),
                2
            ) AS pro_rata_premimum,

            ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365) * 0.18, 2) AS gst,
            ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365), 2) + 
            ROUND(((MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.new_value ELSE NULL END) - MAX(CASE WHEN emp_endorsement.field_name = 'premium' THEN emp_endorsement.old_value ELSE NULL END)) * DATEDIFF(employee_polices.policy_end_date, MAX(CASE WHEN emp_endorsement.field_name = 'si_enhancement_date' THEN emp_endorsement.new_value ELSE NULL END)) / 365) * 0.18, 2) AS total
       
            FROM emp_endorsement
        JOIN employee_polices ON employee_polices.id = emp_endorsement.pk
        JOIN client_policy ON client_policy.id = employee_polices.client_policy_id
        WHERE group_key = '{$group_key}'
        ";

        $results = $this->db->query($query)->getResult();
        return $results;

    }


    public function getDataForEnodrsementListinDeletion($group_key){
        $query = "
                SELECT 
            
                employee_polices.days as old_days,
                employee_polices.rata_premimum as old_rata,
                employee_polices.gst as old_gst,
                employee_polices.policy_end_date as end_date,
                client_policy.policy_start_date as start_date,
            
                MAX(CASE WHEN a.field_name = 'date_of_exit' THEN a.new_value ELSE NULL END) AS date_of_leaving,
                MAX(CASE WHEN a.field_name = 'reason_for_exit' THEN a.new_value ELSE NULL END) AS exist_reason,
                DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit) AS no_of_days,
                ROUND((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365, 2) AS pro_rata_premium,
                ROUND(((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) * 0.18, 2) AS gst,
                ROUND(((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) + (((employee_polices.rata_premimum * DATEDIFF(employee_polices.policy_end_date, deletiondata.dateofexit)) / 365) * 0.18), 2) AS total
            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 
                client_policy ON client_policy.id = employee_polices.client_policy_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'
                    ) aa 
                LEFT JOIN 
                    (
                        SELECT  
                            b1.emp_code, 
                            b1.field_name, 
                            b1.new_value 
                        FROM 
                            emp_endorsement AS b1 
                        WHERE 
                            b1.field_name = 'change_event'
                    ) 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'
                    ) 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'
                    ) 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'
                    ) ee ON aa.emp_code = ee.emp_code
            ) AS deletiondata ON a.emp_code = deletiondata.emp_code 
            WHERE group_key = '{$group_key}';
            ";

        $results = $this->db->query($query)->getResult();
        return $results;

    }

}