<?php

namespace App\Controllers;

use CodeIgniter\RESTful\ResourceController;
use DateTime;

class DashboardController extends ResourceController
{
    protected $db;

    public function __construct()
    {
        $this->db = \Config\Database::connect();
    }

    public function managerDashboard()
    {

        $manager_id   = $this->request->getGet('manager_id');
        $staff_id = $this->request->getGet('staff_id') ?? '';

        $today = date('Y-m-d');
        $month = date('m');
        $year  = date('Y');
    
        // --- Policies Issued ---
        $policiesToday = $this->db->table('partner_policy')
            ->where('created_on >=', $today.' 00:00:00')
            ->where('created_on <=', $today.' 23:59:59')
            ->where('manager_id',$manager_id)
            ->countAllResults();
    
        $policiesMonth = $this->db->table('partner_policy')
            ->where('MONTH(created_on)', $month)
            ->where('YEAR(created_on)', $year)
            ->where('manager_id',$manager_id)
            ->countAllResults();
    
        $policiesYear = $this->db->table('partner_policy')
            ->where('YEAR(created_on)', $year)
            ->where('manager_id',$manager_id)
            ->countAllResults();
    
        // --- Premium Value ---
        $premiumToday = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('created_on >=', $today.' 00:00:00')
            ->where('created_on <=', $today.' 23:59:59')
            ->where('manager_id',$manager_id)
            ->get()->getRow()->total ?? 0);
    
        $premiumMonth = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('MONTH(created_on)', $month)
            ->where('YEAR(created_on)', $year)
            ->where('manager_id',$manager_id)
            ->get()->getRow()->total ?? 0);
    
        $premiumYear = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('YEAR(created_on)', $year)
            ->where('manager_id',$manager_id)
            ->get()->getRow()->total ?? 0);
    
        // --- Earnings (same as premium for now) ---
        $earningsToday = $premiumToday;
        $earningsMonth = $premiumMonth;
        $earningsYear  = $premiumYear;
    
        $last30 = date('Y-m-d H:i:s', strtotime('-30 days'));
    
        // --- Performance Agents (last 30 days) ---
        $performanceAgents = $this->db->table('partner_agent pa')
                                ->select('pa.id AS agent_id, pa.name AS agent_name, COUNT(pp.id) AS total_policy_issued, COALESCE(SUM(pp.premium_amount),0) AS total_premium_value, IF(COUNT(pp.id)>0,"Active","Inactive") AS status')
                                ->join('partner_policy pp', 'pp.agent_id = pa.id', 'inner')
                                ->where('pp.created_on >=', $last30)
                                ->where('pa.manager_id',$manager_id)
                                ->get()
                                ->getResultArray();
       
        if(count($performanceAgents) == 1 && $performanceAgents[0]['agent_name'] == null){
            $performanceAgents = [];
        }
            
        // --- Un assigned enquiry ---
        $unassignedEnquiry = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* , pe.name as insured_name , pa.name as agent_name, pa.agent_code')
                                    ->join('partner_agent pa', 'pa.id = pe.agent_id', 'left')
                                    ->where('pe.assigned_to', 0)
                                    ->where('pe.manager_id', $manager_id)
                                    ->get()
                                    ->getResultArray();
    
     
        // // --- Staff level Quotations Pending ---
        // $quotationsPending = $this->db->table('partner_staff ps')
        //                             ->select('ps.name as staff_name, COUNT(pe.id) as total_quotation_pending')
        //                             ->join('partner_enquiry pe', "pe.assigned_to = ps.id AND pe.status = 'Awaiting Proposal'", 'left')
        //                             ->where('ps.role_id', 2)
        //                             ->where('ps.manager_id',$manager_id)
        //                             ->groupBy('ps.id, ps.name') 
        //                             ->get()->getResultArray();

        //  // --- Staff level Quotations Approva Pending ---
        //  $quotationsApprovalPending = $this->db->table('partner_staff ps')
        //                             ->select('ps.name as staff_name, COUNT(pe.id) as total_approval_pending')
        //                             ->join('partner_enquiry pe', "pe.assigned_to = ps.id AND pe.status = 'Proposal Created'", 'left')
        //                             ->where('ps.role_id', 2)
        //                             ->where('ps.manager_id',$manager_id)
        //                             ->groupBy('ps.id, ps.name') 
        //                             ->get()->getResultArray();

    
        // // --- Staff level Policies Pending ---
        // $policiesPending = $this->db->table('partner_staff ps')
        //                         ->select('ps.name as staff_name, COUNT(pe.id) as total_policis_pending, SUM(pq.premium_amount) as total_premium_value')
        //                         ->join('partner_enquiry pe', "pe.assigned_to = ps.id AND pe.status = 'Proposal Accepted'", 'left')
        //                         ->join('partner_quotation pq', "pq.enquiry_id = pe.id AND pq.status = 'Accepted'", 'left')
        //                         ->where('ps.role_id', 2)
        //                         ->where('ps.manager_id',$manager_id)
        //                         ->groupBy('ps.id, ps.name') 
        //                         ->get()->getResultArray();

        $builder = $this->db->table('partner_enquiry pe')
                                ->select("
                                    ps.name AS staff_name,
                                    ps.id AS staff_id,
                                    ps.handler_id,
                                    COUNT(pe.id) AS total_assigned,
                                    SUM(CASE WHEN pe.enquiry_status != 'Completed'  THEN 1 ELSE 0 END) AS total_pending,
                                    SUM(CASE WHEN  DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_assigned,
                                    SUM(CASE WHEN pe.enquiry_status = 'Assigned' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_pending,
                                    SUM(CASE WHEN pe.enquiry_status = 'In progress' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_in_progress,
                                    SUM(CASE WHEN pe.enquiry_status = 'Completed' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_completed,
                                ")
                                ->join('partner_staff ps', 'ps.id = pe.assigned_to AND ps.is_active = 1', 'left')
                                ->where('pe.manager_id', $manager_id)
                                ->where('pe.is_active', 1)
                                ->where('pe.assigned_to IS NOT NULL')
                                ->where('ps.name IS NOT NULL')
                                ->where('pe.assigned_to !=', 0);

                                if (!empty($staff_id)) {
                                    $builder->where('ps.id', $staff_id);
                                }

                                $staffLevelPendingSummary = $builder
                                    ->groupBy('pe.assigned_to')
                                    ->orderBy('ps.name', 'ASC')
                                    ->get()
                                    ->getResultArray();

        foreach ($staffLevelPendingSummary as &$row) {

            $handlerIds = json_decode($row['handler_id'] ?? '[]', true);
        
            if (!empty($handlerIds)) {
                $handlers = $this->db->table('partner_staff')
                            ->select('name')
                            ->whereIn('id', $handlerIds)
                            ->get()
                            ->getResultArray();
        
                $row['handlers'] = array_column($handlers, 'name');
        
            } else {
                $row['handlers'] = [];
            }
        }
                                
     
        $result = [
            'policies_issued' => [
                'today' => $policiesToday,
                'month' => $policiesMonth,
                'year'  => $policiesYear,
            ],
            'premium_value' => [
                'today' => $premiumToday,
                'month' => $premiumMonth,
                'year'  => $premiumYear,
            ],
            'earnings' => [
                'today' => $earningsToday,
                'month' => $earningsMonth,
                'year'  => $earningsYear,
            ],
            'agents_performance'            => $performanceAgents,
            'unassigned_enquiry'            => $unassignedEnquiry,
            // 'quotations_pending'            => $quotationsPending,
            // 'quotations_approval_pending'   => $quotationsApprovalPending,
            // 'policies_pending'              => $policiesPending,
            'staff_level_pending_summary'      => $staffLevelPendingSummary
        ];

        return $this->respond(['status' => 'success', 'code' => 200, 'data' => $result], 200);
    }

    public function handlerDashboard()
    {
        $handler_id = $this->request->getGet('handler_id');
        $manager_id = $this->request->getGet('manager_id');

        if (empty($handler_id)) {
            return $this->response->setJSON([
                'status' => false,
                'message' => 'Handler ID is required.'
            ]);
        }

        $staffLevelPendingSummary = $this->db->table('partner_enquiry pe')
                                    ->select("
                                        ps.name AS staff_name,
                                        ps.id AS staff_id,
                                        COUNT(pe.id) AS total_assigned,
                                        SUM(CASE WHEN pe.enquiry_status != 'Completed'  THEN 1 ELSE 0 END) AS total_pending,
                                        SUM(CASE WHEN  DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_assigned,
                                        SUM(CASE WHEN pe.enquiry_status = 'Assigned' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_pending,
                                        SUM(CASE WHEN pe.enquiry_status = 'In progress' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_in_progress,
                                        SUM(CASE WHEN pe.enquiry_status = 'Completed' AND DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END) AS today_completed,
                                        
                                        ")
                                    ->join('partner_staff ps', 'ps.id = pe.assigned_to', 'left')

                                    ->groupStart()
                                        ->where("JSON_CONTAINS(ps.handler_id, '\"$handler_id\"')")
                                        ->orWhere('ps.id', $handler_id)
                                    ->groupEnd()

                                    // ->where('ps.handler_id', $handler_id)
                                    ->where('pe.is_active', 1)
                                    ->where('pe.assigned_to IS NOT NULL')
                                    ->where('pe.assigned_to !=', 0)
                                    ->groupBy('pe.assigned_to')
                                    ->orderBy('ps.name', 'ASC')
                                    ->get()
                                    ->getResultArray();

                                    
        $last30 = date('Y-m-d H:i:s', strtotime('-30 days'));
        // --- Performance Agents (last 30 days) ---
        $performanceAgents = $this->db->table('partner_agent pa')
                                    ->select('pa.id AS agent_id, pa.name AS agent_name, COUNT(pp.id) AS total_policy_issued, COALESCE(SUM(pp.premium_amount),0) AS total_premium_value, IF(COUNT(pp.id)>0,"Active","Inactive") AS status')
                                    ->join('partner_policy pp', 'pp.agent_id = pa.id', 'inner')
                                    ->where('pp.created_on >=', $last30)
                                    ->where('pa.manager_id',$manager_id)
                                    ->get()
                                    ->getResultArray();

        if(count($performanceAgents) == 1 && $performanceAgents[0]['agent_name'] == null)
        {
            $performanceAgents = [];
        }

        // --- Un assigned enquiry ---
        $unassignedEnquiry = $this->db->table('partner_enquiry pe')
                                ->select('pe.* , pe.name as insured_name , pa.name as agent_name, pa.agent_code')
                                ->join('partner_agent pa', 'pa.id = pe.agent_id', 'left')
                                ->where('pe.assigned_to', 0)
                                ->where('pe.manager_id', $manager_id)
                                ->get()
                                ->getResultArray();

            $result = [
                'staff_level_pending_summary'   => $staffLevelPendingSummary,
                'agents_performance'            => $performanceAgents,
                'unassigned_enquiry'            => $unassignedEnquiry,
            ];
    
            return $this->respond(['status' => 'success', 'code' => 200, 'data' => $result], 200);
    }


    public function staffDashboard()
    {

        $staff_id   = $this->request->getGet('staff_id');


        // --- Proposal pending for staff (last 30 days) ---
        $quotationsPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* ')
                                    ->where('pe.assigned_to',$staff_id)
                                    ->whereIn('pe.status',["Awaiting Proposal","Proposal Rejected"])
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();


        // --- Proposal Approval pending for staff (last 30 days) ---
        $quotationsApprovalPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* ')
                                    ->where('pe.assigned_to',$staff_id)
                                    ->whereIn('pe.status',["Proposal Created"])
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();

        // --- Policy pending for staff (last 30 days) ---
        $policiesPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* , I.name as insurer_name, I.short_name as insurer_short_name')
                                    ->join('partner_quotation Q', 'Q.enquiry_id = pe.id AND Q.status = "Accepted"', 'left')
                                    ->join('insurers I', 'I.id = Q.insurer_id', 'left')
                                    ->where('pe.assigned_to',$staff_id)
                                    ->whereIn('pe.status',["Proposal Accepted"])
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();

        $result = [
                'quotations_pending'   => $quotationsPending,
                'quotations_approval_pending' => $quotationsApprovalPending,
                'quotations_rejected' => $quotationsApprovalPending,
                'policies_pending'     => $policiesPending,
        ];


        return $this->respond(['status' => 'success', 'code' => 200, 'data' => $result], 200);
    }

    public function agentDashboard()
    {

        $agent_id   = $this->request->getGet('agent_id');
        $today = date('Y-m-d');
        $month = date('m');
        $year  = date('Y');
    
        // --- Policies Issued ---
        $policiesToday = $this->db->table('partner_policy')
            ->where('created_on >=', $today.' 00:00:00')
            ->where('created_on <=', $today.' 23:59:59')
            ->where('agent_id',$agent_id)
            ->countAllResults();
    
        $policiesMonth = $this->db->table('partner_policy')
            ->where('MONTH(created_on)', $month)
            ->where('YEAR(created_on)', $year)
            ->where('agent_id',$agent_id)
            ->countAllResults();
    
        $policiesYear = $this->db->table('partner_policy')
            ->where('YEAR(created_on)', $year)
            ->where('agent_id',$agent_id)
            ->countAllResults();
    
        // --- Premium Value ---
        $premiumToday = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('created_on >=', $today.' 00:00:00')
            ->where('created_on <=', $today.' 23:59:59')
            ->where('agent_id',$agent_id)
            ->get()->getRow()->total ?? 0);
    
        $premiumMonth = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('MONTH(created_on)', $month)
            ->where('YEAR(created_on)', $year)
            ->where('agent_id',$agent_id)
            ->get()->getRow()->total ?? 0);
    
        $premiumYear = (float) ($this->db->table('partner_policy')
            ->selectSum('premium_amount', 'total')
            ->where('YEAR(created_on)', $year)
            ->where('agent_id',$agent_id)
            ->get()->getRow()->total ?? 0);
    
        // --- Earnings (same as premium for now) ---
        $earningsToday = $premiumToday;
        $earningsMonth = $premiumMonth;
        $earningsYear  = $premiumYear;
    
        $last30 = date('Y-m-d H:i:s', strtotime('-30 days'));
    
        // --- Proposal pending for agent (last 30 days) ---
        $quotationsPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* ')
                                    ->where('pe.created_on >=', $last30)
                                    ->where('pe.agent_id',$agent_id)
                                    ->whereIn('pe.status',["Awaiting Proposal","Proposal Rejected"])
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();

        // --- Proposal approval pending for agent (last 30 days) ---
        $quotationsApprovalPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* ')
                                    ->where('pe.created_on >=', $last30)
                                    ->where('pe.agent_id',$agent_id)
                                    ->whereIn('pe.status',["Awaiting Created"])
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();

        // --- Policy pending for agent (last 30 days) ---
        $policiesPending = $this->db->table('partner_enquiry pe')
                                    ->select('pe.* , I.name as insurer_name , I.short_name as insurer_short_name')
                                    ->join('partner_quotation Q', 'Q.enquiry_id = pe.id AND Q.status = "Accepted"', 'left')
                                    ->join('insurers I', 'I.id = Q.insurer_id', 'left')
                                    ->where('pe.created_on >=', $last30)
                                    ->where('pe.agent_id',$agent_id)
                                    ->where('pe.status','Proposal Accepted')
                                    ->orderBy('pe.created_on', 'DESC')
                                    ->get()
                                    ->getResultArray();
    
        $result = [
            'policies_issued' => [
                'today' => $policiesToday,
                'month' => $policiesMonth,
                'year'  => $policiesYear,
            ],
            'premium_value' => [
                'today' => $premiumToday,
                'month' => $premiumMonth,
                'year'  => $premiumYear,
            ],
            'earnings' => [
                'today' => $earningsToday,
                'month' => $earningsMonth,
                'year'  => $earningsYear,
            ],
            'quotations_pending'   => $quotationsPending,
            'quotations_approval_pending' => $quotationsApprovalPending,
            'policies_pending'     => $policiesPending,
        ];


        return $this->respond(['status' => 'success', 'code' => 200, 'data' => $result], 200);
    }

    //business dashboard = month-wise insurer,broker and product (PolicyCount , Premium)
    public function businessDashboard()
    {

        $raw = false;$ref = [];
        $manager_id   = $this->request->getGet('manager_id');
        $raw_param = strtolower(trim($this->request->getGet('raw') ?? ''));
        $raw = in_array($raw_param, ['true', '1'], true);
        // Initialize the details array to ensure the final output is always structured.
        $details = [ 'brokerWise' => [], 'productWise' => [], 'insurerWise' => []];
        $message = "Business Dashboard Data Retrieved Successfully";

        try {
            // 1. Fetch all data concurrently (or sequentially, as shown)
            $details['brokerWise'] = $this->brokerWise($manager_id,$raw);
            $details['productWise'] = $this->productWise($manager_id,$raw);
            $details['insurerWise'] = $this->insurerWise($manager_id,$raw);
            
            $ref['message'] = $message;
            $ref['brokerWise_total_records'] = is_array($details['brokerWise']) ? count($details['brokerWise']) : 0;
            $ref['productWise_total_records'] = is_array($details['productWise']) ? count($details['productWise']) : 0 ;
            $ref['insurerWise_total_records'] = is_array($details['insurerWise']) ? count($details['insurerWise']) : 0;

            // 2. Check for overall emptiness (optional, but good practice)
            $is_empty = array_reduce($details, function ($carry, $item) {
                return $carry && empty($item);
            }, true);
        
            if ($is_empty) {
                // Throw a 404 if ALL data sections are empty
                throw new \RuntimeException('No Data Found', 404);
            }

            // 3. Return a successful, combined JSON response    
            return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => $details, 'ref'    => $ref ], 200);

        } catch (\Throwable $e) {
                        
            if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                $ref['message'] = 'No Data Found';
                return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => [], 'ref'    => $ref], 200);
            }
                        
            $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
                || $e instanceof \mysqli_sql_exception
                || $e instanceof \PDOException;

            if ($isDbError) {
                $ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine(); 
                $ref['message'] = 'Database Error Occurred.';
            } else {
                $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
            }

            return $this->respond([ 'status' => 'error', 'code' => $e->getCode(), 'data' => [], 'ref' => $ref], 200);
        }
    }

    //partner dashboard = month-wise PolicyCount , month-wise Premium, No Agent For Last 10 Days and below 50thouands Premium agentwise 
    public function partnerDashboard()
    {

        $raw = false;$ref = [];
        $manager_id   = $this->request->getGet('manager_id');
        $raw_param = strtolower(trim($this->request->getGet('raw') ?? ''));
        $raw = in_array($raw_param, ['true', '1'], true);

        // Initialize the details array to ensure the final output is always structured.
        $message = "Partner Dashboard Data Retrieved Successfully";
        $details = [ 'agentsPerformanceList' => [],'nonAgentsPerformanceList' => [],'nonAgentsPerformanceListPremium' => []];
                    // 'agentNoPolicyLast10Days' => [],// 'agentBelow50tPremiumLast10Days' => [],
                    
        try {
            // 1. Fetch all data concurrently (or sequentially, as shown)
            $from_date = $this->request->getGet('from_date'); 
            $to_date   = $this->request->getGet('to_date');   
            if (empty($from_date) || empty($to_date)) 
            {
              // Default last 10 days
              $fromDate = date('Y-m-d', strtotime('-10 days'));
              $toDate   = date('Y-m-d');
            } else {
              // Convert d-m-Y → Y-m-d
              $fromDate = DateTime::createFromFormat('d-m-Y', $from_date)->format('Y-m-d 00:00:00');
              $toDate   = DateTime::createFromFormat('d-m-Y', $to_date)->format('Y-m-d 23:59:59');
            }


            $data1 = $this->getDataPerformingAgents($fromDate, $toDate, $manager_id, false);

            if (!empty($data1)) {
                
                $lastIndex1 = count(reset($data1)) - 1;
                usort($data1, function($a, $b) use ($lastIndex1) {
                    $rowA = array_values($a);
                    $rowB = array_values($b);
                    return (float)$rowB[$lastIndex1] <=> (float)$rowA[$lastIndex1];
                });
            }

            $details['agentsPerformanceList'] = $data1;
            $details['nonAgentsPerformanceList'] = $this->getDataNonAgentsPerformanceList($fromDate, $toDate,$manager_id,$raw);
            $data2 = $this->getDataLowPremiumAgent($fromDate, $toDate,$manager_id,$raw);
            if (!empty($data2)) {
                
                $lastIndex2 = count(reset($data2)) - 1;
                usort($data2, function($a, $b) use ($lastIndex2) {
                    $rowC = array_values($a);
                    $rowD = array_values($b);
                    return (float)$rowD[$lastIndex2] <=> (float)$rowC[$lastIndex2];
                });
            }
            $details['nonAgentsPerformanceListPremium'] = $data2;


            // $details['agentsPerformanceListPremium'] = $this->agentMonthlyPremiumAmount($date,$manager_id,$raw);

            $ref['agentsPerformanceList_monthlyPolicyCountAndPremiumAmount_totalRecords'] = is_array($details['agentsPerformanceList']) ? count($details['agentsPerformanceList']) : 0;
            $ref['nonAgentsPerformanceList_noPolicyTimeRange_totalRecords'] = is_array($details['nonAgentsPerformanceList']) ? count($details['nonAgentsPerformanceList']) : 0;
            $ref['nonAgentsPerformanceListPremium_below50tPremiumTimeRange_totalRecords'] = is_array($details['nonAgentsPerformanceListPremium']) ? count($details['nonAgentsPerformanceListPremium']) : 0;


            // 2. Check for overall emptiness (optional, but good practice)
            $is_empty = array_reduce($details, function ($carry, $item) {
                return $carry && empty($item);
            }, true);
        
            if ($is_empty) {
                throw new \RuntimeException('No Data Found', 404);
            }

            // 3. Return a successful, combined JSON response
            return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => $details, 'ref'    => $ref ], 200);

        } catch (\Throwable $e) {
                        
            if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                $ref['message'] = 'No Data Found';
                return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => [], 'ref'    => $ref], 200);
            }
                        
            $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
                || $e instanceof \mysqli_sql_exception
                || $e instanceof \PDOException;

            if ($isDbError) {
                $ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine(); 
                $ref['message'] = 'Database Error Occurred.';
            } else {
                $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
            }

            return $this->respond([ 'status' => 'error', 'code' => $e->getCode(), 'data' => [], 'ref' => $ref], 200);
        }

    }

    public function productivityDashboard(){

        $raw = false;$ref = [];
        $manager_id   = $this->request->getGet('manager_id');
        $raw_param = strtolower(trim($this->request->getGet('raw') ?? ''));
        $raw = in_array($raw_param, ['true', '1'], true);

        // Initialize the details array to ensure the final output is always structured.
        $details = [ 'StaffWise' => []];
        $message = "Productivity Dashboard Data Retrieved Successfully";

        try {
            // 1. Fetch all data concurrently (or sequentially, as shown)
            $details['StaffWise'] = $this->staffWiseProductList($manager_id,$raw);
            $ref['StaffWise_total_records'] = is_array($details['StaffWise'])
                                                ? count($details['StaffWise'])
                                                : 0;


            // 2. Check for overall emptiness (optional, but good practice)
            $is_empty = array_reduce($details, function ($carry, $item) {
                return $carry && empty($item);
            }, true);
        
            if ($is_empty) {
                // Throw a 404 if ALL data sections are empty
                throw new \RuntimeException('No Data Found', 404);
            }

            // 3. Return a successful, combined JSON response    
            return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => $details, 'ref'    => $ref ], 200);

        } catch (\Throwable $e) {
                        
            if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                $ref['message'] = 'No Data Found';
                return $this->respond([ 'status' => 'success', 'code'   => 200, 'data'   => [], 'ref'    => $ref], 200);
            }
                        
            $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
                || $e instanceof \mysqli_sql_exception
                || $e instanceof \PDOException;

            if ($isDbError) {
                $ref['debug_info'] = $e->getFile() . " / LN : " . $e->getLine(); 
                $ref['message'] = 'Database Error Occurred.';
            } else {
                $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
            }

            return $this->respond([ 'status' => 'error', 'code' => $e->getCode(), 'data' => [], 'ref' => $ref], 200);
        }
    }

    //Step 1.1
    public function insurerWise($manager_id, $raw){

        $builder = $this->db->table('partner_policy pp');

        $builder->select("
            i.name,
            i.short_name,
            i.id AS insurer_id,
            pp.insured_name,
            COUNT(
                CASE
                    WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                    AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                    THEN pp.policy_number
                    ELSE NULL
                END
            ) AS total_policies_current_month,

            SUM(
                CASE
                    WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                    AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                    THEN pp.premium_amount
                    ELSE 0
                END
            ) AS total_premium_current_month,

            COUNT(
                CASE
                    WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                    AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                    THEN pp.policy_number
                    ELSE NULL
                END
            ) AS total_policies_pre_month,

            SUM(
                CASE
                    WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                    AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                    THEN pp.premium_amount
                    ELSE 0
                END
            ) AS total_premium_pre_month
        ");

        $builder->join('partner_quotation pq', 'pq.id = pp.quotation_id', 'left');
        $builder->join('insurers i', 'i.id = pq.insurer_id', 'left');

        $builder->where("
            pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
            AND pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
        ", null, false);

        $builder->where('pp.manager_id',$manager_id);
        $builder->where('i.is_active',1);

        $builder->where("pq.insurer_id != 0");
        $builder->groupBy('pq.insurer_id');
        $builder->orderBy('pq.insurer_id');

        if ($raw === true) {
            return $builder->getCompiledSelect();
        }

        $query = $builder->get();
        $result = $query->getResultArray();   
        return $result;

    }

    //Step 1.2
    public function brokerWise($manager_id, $raw){

            $builder = $this->db->table('partner_policy pp');
            $builder->select("
                pb.name AS broker_name,
                pb.short_name AS broker_short_name,
                pb.id AS broker_id,
                COUNT(
                    CASE
                        WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                        AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                        THEN pp.policy_number
                        ELSE NULL
                    END
                ) AS total_policies_current_month,

                SUM(
                    CASE
                        WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                        AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                        THEN pp.premium_amount
                        ELSE 0
                    END
                ) AS total_premium_current_month,

                COUNT(
                    CASE
                        WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                        THEN pp.policy_number
                        ELSE NULL
                    END
                ) AS total_policies_pre_month,

                SUM(
                    CASE
                        WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                        THEN pp.premium_amount
                        ELSE 0
                    END
                ) AS total_premium_pre_month
            ");

            $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
            $builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');

            $builder->where("
                pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                AND pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
            ", null, false);

            $builder->where('pp.manager_id',$manager_id);

            $builder->groupBy('pb.id');
            $builder->orderBy('pb.id');

            if ($raw === true) {
                return $builder->getCompiledSelect();
            }

            $query = $builder->get();
            $result = $query->getResultArray();   
            return $result;
    }

    //Step 1.3
    public function productWise($manager_id , $raw){
        $builder = $this->db->table('partner_policy pp');
            $builder->select("
                pp.vehicle_type,
                COUNT(
                    CASE
                        WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                        AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                        THEN pp.policy_number
                        ELSE NULL
                    END
                ) AS total_policies_current_month,

                SUM(
                    CASE
                        WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                        AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                        THEN pp.premium_amount
                        ELSE 0
                    END
                ) AS total_premium_current_month,

                COUNT(
                    CASE
                        WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                        THEN pp.policy_number
                        ELSE NULL
                    END
                ) AS total_policies_pre_month,

                SUM(
                    CASE
                        WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                        AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                        THEN pp.premium_amount
                        ELSE 0
                    END
                ) AS total_premium_pre_month
            ");

            $builder->where("
                pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                AND pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
            ", null, false);

            $builder->where('pp.manager_id',$manager_id);

            $builder->groupBy('pp.vehicle_type');

            if ($raw === true) {
                return $builder->getCompiledSelect();
            }

            $query = $builder->get();

            $result = $query->getResultArray();   
            return $result;

    }

    //Step 2
    public function getDataPerformingAgents($fromDate, $toDate, $manager_id, $raw){


        $builder = $this->db->table('partner_policy pp');

        $builder->select("
        pa.id AS agent_id,
        pa.name AS agent_name,
        pa.agent_code,
        COUNT(pp.policy_number) AS policy_count,
        SUM(pp.premium_amount) AS total_premium_amount
        ");

        $builder->join('partner_agent pa', 'pp.agent_id = pa.id', 'inner');
        $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');

        $builder->where('pp.issued_date >=', $fromDate);
        $builder->where('pp.issued_date <=', $toDate);

        // $builder->where('pp.manager_id',$manager_id);

        $builder->groupBy(['pa.id']);
        $builder->orderBy('policy_count', 'DESC');
        $builder->limit(50);

        if ($raw === true) {
                return $builder->getCompiledSelect();
        }

        $result = $builder->get()->getResultArray();   
        // echo $this->db->getLastQuery()->getQuery();die;
        return $result;

    }

    
    // public function agentMonthlyPremiumAmount($customDate,$manager_id, $raw){
        
    //     $builder = $this->db->table('partner_policy pp');

    //     $builder->select("
    //         pa.id AS agent_id,
    //         pa.name AS agent_name,
    //         pa.agent_code,
    //         SUM(pp.premium_amount) AS total_premium_amount
    //     ");

    //     $builder->join('partner_agent pa', 'pp.agent_id = pa.id', 'inner');
        
    //     $builder->where("MONTH(pp.issued_date) = MONTH('$customDate')", null, false);
    //     $builder->where("YEAR(pp.issued_date) = YEAR('$customDate')", null, false);
    //     $builder->where('pp.manager_id',$manager_id);
        
    //     $builder->groupBy(['pa.id','pa.name','pa.agent_code']);
    //     $builder->orderBy('total_premium_amount', 'DESC');
    //     $builder->limit(50);
    //     if ($raw === true) {
    //             return $builder->getCompiledSelect();
    //     }
    //     $result = $builder->get()->getResultArray();   
    //     return $result;

    // }

    //Step 3 no buiness
    // public function getDataNonAgentsPerformanceList($managerId, $raw){
        
    //     // $builder = $this->db->table('partner_agent pa');
    //     // $builder->select('
    //     //     pa.id AS agent_id,
    //     //     pa.name AS agent_name,
    //     //     pa.email,
    //     //     pa.mobile,
    //     //     pa.agent_code,
    //     //     pa.is_active
    //     //     ');

    //     //     $builder->join(
    //     // 'partner_policy pp',
    //     //         "pa.id = pp.agent_id AND pp.issued_date >= $interval AND pp.manager_id = $manager_id",
    //     //         'left',
    //     //         false   // IMPORTANT → allows raw SQL
    //     //     );

    //     //     $builder->where('pa.manager_id',$manager_id);
    //     //     $builder->where('pp.id', null);
    //     //     $builder->orderBy('pa.name');

    //     // 1. Select the required columns
    //     $builder = $this->db->table('partner_agent pa');
    //     $builder->select('
    //         pa.id AS agent_id,
    //         pa.agent_code,
    //         pa.name AS agent_name,
    //         pa.email,
    //         pa.mobile,
    //         pa.is_active,
    //         MAX(hist.issued_date) AS last_issued_date
    //     ');

    //     // 2. INNER JOIN ensures they have at least one policy in history (skips new/empty agents)
    //     $builder->join('partner_policy hist', 'pa.id = hist.agent_id', 'inner');

    //     // 3. LEFT JOIN checks for policies in the last 10 days
    //     $builder->join('partner_policy recent', 
    //         "pa.id = recent.agent_id AND recent.issued_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 10 DAY)", 
    //         'left'
    //     );

    //     // 4. Filters
    //     $builder->where('pa.manager_id', $managerId);
    //     $builder->where('pa.is_active', 1);
    //     $builder->where('recent.id', NULL); // Keeps only those with NO activity in the 10-day window

    //     // 5. Grouping and Ordering
    //     $builder->groupBy('pa.id');
    //     $builder->orderBy('pa.name', 'ASC');

    //     // 6. Get Raw Query
    //     if ($raw === true) {
    //         return $builder->getCompiledSelect();
    //     }
    //     // 7. Execute
    //     $query = $builder->get();
    //     $result = $query->getResult();

    //     // 8. To verify the exact SQL for debugging:
    //     // echo (string) $db->getLastQuery();
    //     // echo $this->db->getLastQuery()->getQuery();die;
    //     return $result;

    // }

    public function getDataNonAgentsPerformanceList($fromDate, $toDate, $managerId, $raw)
    {
        $builder = $this->db->table('partner_agent pa');

        $builder->select('
            pa.id AS agent_id,
            pa.agent_code,
            pa.name AS agent_name,
            pa.email,
            pa.mobile,
            pa.is_active,
            MAX(hist.issued_date) AS last_issued_date
        ');

        // Must have at least one policy ever
        $builder->join(
            'partner_policy hist',
            'pa.id = hist.agent_id',
            'inner'
        );

        // Check activity inside the date range
        $builder->join(
            'partner_policy recent',
            "pa.id = recent.agent_id 
            AND recent.issued_date >= '{$fromDate}'
            AND recent.issued_date <= '{$toDate}'",
            'left'
        );

        // Filters
        $builder->where('pa.manager_id', $managerId);
        $builder->where('pa.is_active', 1);

        // Key condition → NO activity in date range
        $builder->where('recent.agent_id IS NULL', null, false);

        // Group & Order
        $builder->groupBy('pa.id');
        $builder->orderBy('pa.name', 'ASC');

        if ($raw === true) {
            return $builder->getCompiledSelect();
        }

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

    
    //Step 4
    //any changed in this query also change in do the same in  ExcelExportController - getDataLowPremiumAgent 
    public function getDataLowPremiumAgent($fromDate, $toDate,$manager_id, $raw){
            
        $builder = $this->db->table('partner_policy pp');

        $builder->select("
             pa.id AS agent_id,
            pa.name AS agent_name,
            pa.agent_code,
            CASE
                WHEN pa.is_active = 1 THEN 'Active'
                ELSE 'Inactive'
            END AS agent_status,
            MAX(pp.issued_date) AS last_business_date,
            CONCAT('₹ ', FORMAT(SUM(pp.premium_amount), 2, 'en_IN')) AS display_premium,
            SUM(pp.premium_amount) AS total_premium_amount
        ");

        $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');

        $builder->where('pp.manager_id', $manager_id);
        
        $builder->having('total_premium_amount <', 50000);

        $builder->where('pp.issued_date >=', $fromDate);
        $builder->where('pp.issued_date <=', $toDate);

        // groupBy must be array of separate fields
        $builder->groupBy([
            'pa.id',
            'pa.name',
            'pa.agent_code',
            'pa.is_active'
        ]);

        // orderBy must not contain semicolon + must be separated
        $builder->orderBy('total_premium_amount', 'DESC');
        $builder->orderBy('last_business_date', 'ASC');

        if ($raw === true) {
                return $builder->getCompiledSelect();
        }

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

    // Step 5
    public function staffWiseProductList($manager_id, $raw){

        $builder = $this->db->table('partner_policy pp');

        $builder->select("
            pa.sales_executive_id,
            pse.name as sales_executive_name,
            pp.vehicle_type AS vechile_type,
            COUNT(
                CASE
                    WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                    AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                    THEN pp.policy_number
                END
            ) AS total_policies_current_month,

            SUM(
                CASE
                    WHEN MONTH(pp.issued_date) = MONTH(CURRENT_DATE())
                    AND YEAR(pp.issued_date) = YEAR(CURRENT_DATE())
                    THEN pp.premium_amount ELSE 0
                END
            ) AS total_premium_current_month,

            COUNT(
                CASE
                    WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                    AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                    THEN pp.policy_number
                END
            ) AS total_policies_pre_month,

            SUM(
                CASE
                    WHEN pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
                    AND pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')
                    THEN pp.premium_amount ELSE 0
                END
            ) AS total_premium_pre_month
        ");

        $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'left');
        $builder->join('partner_sales_executive pse', 'pse.id = pa.sales_executive_id', 'left');

        // Date filters
        $builder->where("
            pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
            AND pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')
        ", null, false);

        $builder->where('pp.manager_id', $manager_id);
        $builder->where('pa.sales_executive_id IS NOT NULL', null, false);
        $builder->where('pse.is_active', 1);

        // Group by sales exec + vehicle type
        $builder->groupBy("pa.sales_executive_id, pp.vehicle_type");
        $builder->orderBy("pa.sales_executive_id");
        if ($raw === true) {
                return $builder->getCompiledSelect();
        }


        $rows = $builder->get()->getResultArray();

        // Final compilation
        $final = [];

        foreach ($rows as $row) {
            $se_id = $row['sales_executive_id'];

            if (!isset($final[$se_id])) {
                $final[$se_id] = [
                    "sales_executive_id"   => $row["sales_executive_id"],
                    "sales_executive_name" => $row["sales_executive_name"],
                    "products_list"        => [],
                ];
            }

            // Add each product entry
            $final[$se_id]["products_list"][] = [
                "vechile_type"                 => $row["vechile_type"],
                "total_policies_current_month" => $row["total_policies_current_month"],
                "total_premium_current_month"  => $row["total_premium_current_month"],
                "total_policies_pre_month"     => $row["total_policies_pre_month"],
                "total_premium_pre_month"      => $row["total_premium_pre_month"]
            ];
        }

        // Add total_products count
        // foreach ($final as $key => $value) {
        //     $final[$key]["total_products"] = count($value["products_list"]);
        // }

        return array_values($final);
    }
// ─────────────────────────────────────────────────────────────────────────────
//  DashboardController.php  — Partner Portal API methods
//  Routes:
//    GET partner/(:num)/details   → partnerDetails($id)
//    GET partner/(:num)/policies  → partnerPolicies($id)
//    GET partner/(:num)/renewals  → partnerRenewals($id)   ?days=20
//    GET partner/(:num)/earnings  → partnerEarnings($id)
// ─────────────────────────────────────────────────────────────────────────────


// ══════════════════════════════════════════════════════════════════════════════
//  GET partner/{id}/details
//  agent_id = partner_agent.id
//  Joins: partner_policy (agent_id), partner_enquiry (agent_id),
//         partner_endorsement_request (agent_id)
// ══════════════════════════════════════════════════════════════════════════════
public function partnerDetails($id)
{
    $ref = [];

    try {
        if (empty($id)) {
            return $this->respond([
                'status'  => 'error',
                'code'    => 400,
                'data'    => [],
                'message' => 'Missing required parameter: id',
            ], 200);
        }

        // ── 1. Agent profile  (partner_agent.id = $id)
        $agent = $this->db->table('partner_agent pa')
            ->select('
                pa.id,
                pa.name           AS agent_name,
                pa.agent_code,
                pa.mobile,
                pa.email,
                pa.is_active,
                ps.name           AS manager_name,
                ps.mobile          AS manager_mobile,
                ps.email           AS manager_email
            ')
            ->join('partner_staff ps', 'ps.id = pa.manager_id', 'left')
            ->where('pa.id', $id)
            ->get()->getRowArray();

        if (empty($agent)) {
            throw new \RuntimeException('Partner not found.', 404);
        }

        // -- 2. Overview = current Indian financial year (Apr–Mar, Asia/Kolkata)
        $fy          = $this->indianFinancialYearBounds(null);
        $fyStart     = $fy['start'];
        $fyEnd       = $fy['end'];
        $overviewFy  = $fy['label'];

        $policyStats = $this->db->table('partner_policy pp')
            ->select("
                SUM(CASE WHEN pp.policy_number IS NOT NULL AND pp.issued_date >= '{$fyStart}' AND pp.issued_date <= '{$fyEnd}' THEN 1 ELSE 0 END) AS issued_policies,
                SUM(CASE WHEN pp.policy_number IS NULL AND DATE(pp.created_on) >= '{$fyStart}' AND DATE(pp.created_on) <= '{$fyEnd}' THEN 1 ELSE 0 END) AS pending_policies,
                COALESCE(SUM(CASE WHEN pp.policy_number IS NOT NULL AND pp.issued_date >= '{$fyStart}' AND pp.issued_date <= '{$fyEnd}' THEN pp.premium_amount ELSE 0 END), 0) AS total_premium,
                COALESCE(SUM(CASE WHEN pp.policy_number IS NOT NULL AND pp.issued_date >= '{$fyStart}' AND pp.issued_date <= '{$fyEnd}' THEN COALESCE(pp.commission_amount, 0) ELSE 0 END), 0) AS commission_earned
            ", false)
            ->where('pp.agent_id', $id)
            ->get()->getRowArray();

        $policyStats['mapped_policies'] = (int) ($policyStats['issued_policies'] ?? 0) + (int) ($policyStats['pending_policies'] ?? 0);

        $agentId = (int) $id;

        $paidRow = $this->db->table('partner_invoice_items pii')
            ->select('COALESCE(SUM(COALESCE(pii.commission_amount, 0)), 0) AS commission_paid', false)
            ->join('partner_policy pp', 'pp.id = pii.policy_id', 'inner')
            ->join('partner_invoice pi', 'pi.id = pii.invoice_id AND pi.is_active = 1', 'inner')
            ->where('pp.agent_id', $agentId)
            ->where('pii.is_active', 1)
            ->where('pp.is_active', 1)
            ->where('pp.policy_number IS NOT NULL')
            ->where('pp.issued_date >=', $fyStart)
            ->where('pp.issued_date <=', $fyEnd)
            ->where(
                'JSON_SEARCH(pi.agent_id, \'one\', CAST(' . $agentId . ' AS CHAR)) IS NOT NULL',
                null,
                false
            )
            ->where(
                'EXISTS (SELECT 1 FROM partner_invoice_utr piu WHERE piu.invoice_id = pi.id AND piu.is_active = 1 AND piu.amount > 0)',
                null,
                false
            )
            ->get()->getRowArray();

        $commissionEarned = (float) ($policyStats['commission_earned'] ?? 0);
        $commissionPaidRaw = (float) ($paidRow['commission_paid'] ?? 0);
        $commissionPaid   = min($commissionPaidRaw, $commissionEarned);
        $commissionUnpaid = max(0, $commissionEarned - $commissionPaid);

        $policyStats['commission_paid']   = $commissionPaid;
        $policyStats['commission_unpaid'] = $commissionUnpaid;

        // ── 3. Enquiry counts (created in current FY)
        $enquiryStats = $this->db->table('partner_enquiry pe')
            ->select('
                COUNT(pe.id)                                                                        AS enquiry_total,
                SUM(CASE WHEN pe.enquiry_status = "Completed" THEN 1 ELSE 0 END)                   AS enquiry_completed,
                SUM(CASE WHEN pe.enquiry_status != "Completed" THEN 1 ELSE 0 END)                  AS enquiry_pending
            ')
            ->where('pe.agent_id', $id)
            ->where('pe.is_active', 1)
            ->where('pe.created_on >=', $fyStart . ' 00:00:00')
            ->where('pe.created_on <=', $fyEnd . ' 23:59:59')
            ->get()->getRowArray();

        // ── 4. Endorsement counts (created in current FY)
        $endorseStats = $this->db->table('partner_endorsement_request per')
            ->select('
                COUNT(per.id)                                                                       AS endorsement_total,
                SUM(CASE WHEN per.status = "Completed" THEN 1 ELSE 0 END)                          AS endorsement_done,
                SUM(CASE WHEN per.status != "Completed" THEN 1 ELSE 0 END)                         AS endorsement_pending
            ')
            ->where('per.agent_id', $id)
            ->where('per.is_active', 1)
            ->where('per.created_at >=', $fyStart . ' 00:00:00')
            ->where('per.created_at <=', $fyEnd . ' 23:59:59')
            ->get()->getRowArray();

        // ── Merge everything
        $data = array_merge(
            $agent,
            [
                'status'                  => $agent['is_active'] ? 'Active' : 'Inactive',
                'overview_financial_year' => $overviewFy,
            ],
            $policyStats  ?? [],
            $enquiryStats ?? [],
            $endorseStats ?? [],
        );

        $ref['message'] = 'Partner details retrieved successfully.';

        return $this->respond([
            'status' => 'success',
            'code'   => 200,
            'data'   => $data,
            'ref'    => $ref,
        ], 200);

    } catch (\Throwable $e) {

        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
            $ref['message'] = 'No Data Found';
            return $this->respond([
                'status'  => 'success',
                'code'    => 200,
                'data'    => [],
                'message' => 'No Data Found',
                'ref'     => $ref,
            ], 200);
        }

        $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
            || $e instanceof \mysqli_sql_exception
            || $e instanceof \PDOException;

        if ($isDbError) {
            $ref['debug_info'] = $e->getFile() . ' / LN : ' . $e->getLine();
            $ref['message']    = 'Database Error Occurred.';
        } else {
            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
        }

        return $this->respond([
            'status'  => 'error',
            'code'    => 500,
            'data'    => [],
            'ref'     => $ref,
        ], 200);
    }
}


// ══════════════════════════════════════════════════════════════════════════════
//  GET partner/{id}/policies
//  partner_policy.agent_id = $id
//  holder_name  → pp.insured_name   (the actual insured person, NOT agent)
//  product      → pp.product (varchar 50), falls back to pp.vehicle_type
//  policy_no    → pp.policy_number
// ══════════════════════════════════════════════════════════════════════════════
public function partnerPolicies($id)
{
    $ref = [];

    try {
        if (empty($id)) {
            return $this->respond([
                'status'  => 'error',
                'code'    => 400,
                'data'    => [],
                'message' => 'Missing required parameter: id',
            ], 200);
        }

        $results = $this->db->table('partner_policy pp')
            ->select('
                pp.policy_number                                        AS policy_no,
                pp.insured_name                                         AS holder_name,
                COALESCE(NULLIF(pp.product, ""), pp.vehicle_type)       AS product,
                pp.premium_amount                                       AS premium,
                DATE_FORMAT(pp.issued_date, "%d-%m-%Y")                 AS issued_date,
                DATE_FORMAT(pp.end_date,    "%d-%m-%Y")                 AS expiry_date
            ')
            ->where('pp.agent_id', $id)
            ->where('pp.policy_number IS NOT NULL')
            ->where('pp.premium_amount IS NOT NULL')
            ->where('pp.is_active', 1)
            ->orderBy('pp.issued_date', 'DESC')
            ->get()->getResultArray();

        $ref['total_records'] = count($results);

        if (empty($results)) {
            throw new \RuntimeException('No policies found for this partner.', 404);
        }

        return $this->respond([
            'status' => 'success',
            'code'   => 200,
            'data'   => $results,
            'ref'    => $ref,
        ], 200);

    } catch (\Throwable $e) {

        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
            $ref['message'] = 'No Data Found';
            return $this->respond([
                'status'  => 'success',
                'code'    => 200,
                'data'    => [],
                'message' => 'No Data Found',
                'ref'     => $ref,
            ], 200);
        }

        $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
            || $e instanceof \mysqli_sql_exception
            || $e instanceof \PDOException;

        if ($isDbError) {
            $ref['debug_info'] = $e->getFile() . ' / LN : ' . $e->getLine();
            $ref['message']    = 'Database Error Occurred.';
        } else {
            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
        }

        return $this->respond([
            'status'  => 'error',
            'code'    => 500,
            'data'    => [],
            'ref'     => $ref,
        ], 200);
    }
}


// ══════════════════════════════════════════════════════════════════════════════
//  GET partner/{id}/renewals?days=20
//  partner_policy.agent_id = $id
//  holder_name → pp.insured_name
//  premium     → pp.premium_amount
// ══════════════════════════════════════════════════════════════════════════════
public function partnerRenewals($id)
{
    $ref  = [];
    $days = (int) ($this->request->getGet('days') ?? 20);

    // Calculate range boundaries
    // 10 => 0–10 | 20 => 11–20 | 30 => 21–30 | 40 => 31–40 | 50 => 41–50
    $endDay   = $days;
    $startDay = ($days <= 10) ? 0 : ($days - 9);

    try {
        if (empty($id)) {
            return $this->respond([
                'status'  => 'error',
                'code'    => 400,
                'data'    => [],
                'message' => 'Missing required parameter: id',
            ], 200);
        }

        $results = $this->db->table('partner_policy pp')
            ->select('
                pp.policy_number                                        AS policy_no,
                pp.insured_name                                         AS holder_name,
                pp.premium_amount                                       AS premium,
                pp.vehicle_type                                         AS vehicle_type,
                DATE_FORMAT(pp.end_date, "%d-%m-%Y")                    AS end_date,
                DATEDIFF(pp.end_date, CURDATE())                        AS days_left
            ')
            ->where('pp.agent_id', $id)
            ->where('pp.is_active', 1)
            ->where('pp.policy_number IS NOT NULL')
            ->where('pp.premium_amount IS NOT NULL')
            ->where("pp.end_date >= DATE_ADD(CURDATE(), INTERVAL {$startDay} DAY)", null, false)
            ->where("pp.end_date <= DATE_ADD(CURDATE(), INTERVAL {$endDay} DAY)", null, false)
            ->orderBy('pp.end_date', 'ASC')
            ->get()->getResultArray();

        $ref['days_filter']   = $days;
        $ref['range']         = "day {$startDay} to day {$endDay}";
        $ref['total_records'] = count($results);

        if (empty($results)) {
            throw new \RuntimeException('No renewals due within this range.', 404);
        }

        return $this->respond([
            'status' => 'success',
            'code'   => 200,
            'data'   => $results,
            'ref'    => $ref,
        ], 200);

    } catch (\Throwable $e) {

        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
            $ref['message'] = 'No Data Found';
            return $this->respond([
                'status'  => 'success',
                'code'    => 200,
                'data'    => [],
                'message' => 'No Data Found',
                'ref'     => $ref,
            ], 200);
        }

        $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
            || $e instanceof \mysqli_sql_exception
            || $e instanceof \PDOException;

        if ($isDbError) {
            $ref['debug_info'] = $e->getFile() . ' / LN : ' . $e->getLine();
            $ref['message']    = 'Database Error Occurred.';
        } else {
            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
        }

        return $this->respond([
            'status'  => 'error',
            'code'    => 500,
            'data'    => [],
            'ref'     => $ref,
        ], 200);
    }
}


// ══════════════════════════════════════════════════════════════════════════════
//  GET partner/{id}/earnings
//  partner_policy.agent_id = $id
//  Groups by issued_date month → month_key (YYYY-MM), month_label (Month YYYY)
//  payout      = SUM(partner_policy.commission_amount) — total commission for the month
//  payout_paid = SUM(partner_invoice_items.commission_amount) where policy belongs to agent,
//                invoice lists this agent in agent_id JSON, and invoice has UTR payment (amount > 0)
//  payout_unpaid = payout − payout_paid (floored at 0)
//  FY filter is client-side
// ══════════════════════════════════════════════════════════════════════════════
public function partnerEarnings($id)
{
    $ref      = [];
    $monthKey = $this->request->getGet('month_key') ?? null;
    $fyParam  = $this->request->getGet('financial_year') ?? null;

    try {
        if (empty($id)) {
            return $this->respond([
                'status'  => 'error',
                'code'    => 400,
                'data'    => [],
                'message' => 'Missing required parameter: id',
            ], 200);
        }

        $agentId = (int) $id;

        $fyBounds = null;
        if (!empty($fyParam)) {
            $fyBounds = $this->indianFinancialYearBounds($fyParam);
        }

        $builder = $this->db->table('partner_policy pp');

        $builder->select("
            DATE_FORMAT(pp.issued_date, '%Y-%m')                        AS month_key,
            DATE_FORMAT(pp.issued_date, '%M %Y')                        AS month_label,
            COUNT(pp.id)                                                AS policies,
            COALESCE(SUM(pp.premium_amount), 0)                         AS premium,
            COALESCE(SUM(COALESCE(pp.commission_amount, 0)), 0)         AS payout
        ");

        $builder->where('pp.agent_id', $agentId);
        $builder->where('pp.is_active', 1);
        $builder->where('pp.policy_number IS NOT NULL');
        $builder->where('pp.premium_amount IS NOT NULL');

        if ($fyBounds !== null) {
            $builder->where('pp.issued_date >=', $fyBounds['start']);
            $builder->where('pp.issued_date <=', $fyBounds['end']);
        }

        if (!empty($monthKey)) {
            $builder->where("DATE_FORMAT(pp.issued_date, '%Y-%m')", $monthKey);
        }

        $builder->groupBy("DATE_FORMAT(pp.issued_date, '%Y-%m')");
        $builder->orderBy('month_key', 'ASC');

        $results = $builder->get()->getResultArray();

        // Paid commission: invoice line items tied to this agent’s policies, invoice includes agent, UTR settled
        $paidBuilder = $this->db->table('partner_invoice_items pii');
        $paidBuilder->select("
            DATE_FORMAT(pp.issued_date, '%Y-%m') AS month_key,
            COALESCE(SUM(COALESCE(pii.commission_amount, 0)), 0) AS payout_paid
        ", false);
        $paidBuilder->join('partner_policy pp', 'pp.id = pii.policy_id', 'inner');
        $paidBuilder->join('partner_invoice pi', 'pi.id = pii.invoice_id AND pi.is_active = 1', 'inner');
        $paidBuilder->where('pp.agent_id', $agentId);
        $paidBuilder->where('pii.is_active', 1);
        $paidBuilder->where('pp.is_active', 1);
        $paidBuilder->where('pp.policy_number IS NOT NULL');
        $paidBuilder->where('pp.premium_amount IS NOT NULL');
        $paidBuilder->where(
            'JSON_SEARCH(pi.agent_id, \'one\', CAST(' . $agentId . ' AS CHAR)) IS NOT NULL',
            null,
            false
        );
        $paidBuilder->where(
            'EXISTS (SELECT 1 FROM partner_invoice_utr piu WHERE piu.invoice_id = pi.id AND piu.is_active = 1 AND piu.amount > 0)',
            null,
            false
        );

        if ($fyBounds !== null) {
            $paidBuilder->where('pp.issued_date >=', $fyBounds['start']);
            $paidBuilder->where('pp.issued_date <=', $fyBounds['end']);
        }

        if (!empty($monthKey)) {
            $paidBuilder->where("DATE_FORMAT(pp.issued_date, '%Y-%m')", $monthKey);
        }

        $paidBuilder->groupBy("DATE_FORMAT(pp.issued_date, '%Y-%m')");
        $paidRows = $paidBuilder->get()->getResultArray();

        $paidMap = [];
        foreach ($paidRows as $pr) {
            $paidMap[$pr['month_key']] = (float) $pr['payout_paid'];
        }

        foreach ($results as &$row) {
            $mk            = $row['month_key'];
            $payout        = (float) $row['payout'];
            $payoutPaid    = min((float) ($paidMap[$mk] ?? 0), $payout);
            $row['payout'] = $payout;
            $row['payout_paid'] = $payoutPaid;
            $row['payout_unpaid'] = max(0, $payout - $payoutPaid);
            $row['premium'] = (float) $row['premium'];
            $row['policies'] = (int) $row['policies'];
        }
        unset($row);

        $ref['month_filter']     = $monthKey ?? 'all';
        $ref['financial_year']   = ($fyBounds !== null) ? $fyBounds['label'] : 'all';
        $ref['total_records']    = count($results);

        return $this->respond([
            'status' => 'success',
            'code'   => 200,
            'data'   => $results,
            'ref'    => $ref,
        ], 200);

    } catch (\Throwable $e) {

        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
            $ref['message'] = 'No Data Found';
            return $this->respond([
                'status'  => 'success',
                'code'    => 200,
                'data'    => [],
                'message' => 'No Data Found',
                'ref'     => $ref,
            ], 200);
        }

        $isDbError = $e instanceof \CodeIgniter\Database\Exceptions\DatabaseException
            || $e instanceof \mysqli_sql_exception
            || $e instanceof \PDOException;

        if ($isDbError) {
            $ref['debug_info'] = $e->getFile() . ' / LN : ' . $e->getLine();
            $ref['message']    = 'Database Error Occurred.';
        } else {
            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
        }

        return $this->respond([
            'status'  => 'error',
            'code'    => 500,
            'data'    => [],
            'ref'     => $ref,
        ], 200);
    }
 }

    /**
     * Indian financial year Apr 1 (Y) → Mar 31 (Y+1).
     *
     * @param string|null $fyStr e.g. "2025-2026" (first year is the April year)
     * @return array{start: string, end: string, label: string} Y-m-d bounds + label "2025-2026"
     */
    private function indianFinancialYearBounds(?string $fyStr = null): array
    {
        if ($fyStr !== null && preg_match('/^(\d{4})-(\d{4})$/', trim($fyStr), $m)) {
            $y1 = (int) $m[1];
            $y2 = (int) $m[2];
            if ($y2 !== $y1 + 1) {
                $y1 = $y1 - 1;
                $y2 = $y1 + 1;
            }
            $start = sprintf('%04d-04-01', $y1);
            $end   = sprintf('%04d-03-31', $y2);

            return [
                'start' => $start,
                'end'   => $end,
                'label' => $y1 . '-' . $y2,
            ];
        }

        $tz  = new \DateTimeZone('Asia/Kolkata');
        $now = new \DateTime('now', $tz);
        $y   = (int) $now->format('Y');
        $mo  = (int) $now->format('n');
        $y1  = $mo >= 4 ? $y : $y - 1;
        $y2  = $y1 + 1;
        $start = sprintf('%04d-04-01', $y1);
        $end   = sprintf('%04d-03-31', $y2);

        return [
            'start' => $start,
            'end'   => $end,
            'label' => $y1 . '-' . $y2,
        ];
    }
}