<?php

namespace App\Controllers;

use App\Libraries\PartnerPayoutGridRetention;
use CodeIgniter\RESTful\ResourceController;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate; // <--- THIS MUST BE HERE
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class ExcelExportController extends ResourceController
{
    protected $db;

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

    
        /**
         * Centralized function to create the spreadsheet, apply styling, 
         * set headers, and stream the file output.
         *
         * @param array $header Column headers (1D array)
         * @param array $data Policy data including the total row (2D array)
         * @param string $title Spreadsheet title
         * @param string $fileName Output file name
         * @param string $totalLabelCol Column letter for the 'TOTAL:' label (e.g., 'C' or 'E')
         * @param string $totalValueCol Column letter for the total value (e.g., 'D' or 'F')
         * @return void
         */
        private function streamExcelFile(array $header, array $data, string $title, string $fileName, bool $showTotal = true): void 
        {
            while (ob_get_level() > 0) {
                ob_end_clean();
            }

            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $columnCount = count($header);
            $lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount);
            $rowCount = count($data);

            // 1. Title
            $sheet->mergeCells("A1:{$lastCol}1");
            $sheet->setCellValue('A1', $title);
            $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
            $sheet->getStyle('A1')->getAlignment()->setHorizontal('center');

            // 2. Headers
            $sheet->fromArray($header, NULL, 'A2');
            $sheet->getStyle("A2:{$lastCol}2")->getFont()->setBold(true);
            $sheet->getStyle("A2:{$lastCol}2")->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('F2F2F2');

            // 3. Data
            $sheet->fromArray($data, NULL, 'A3');

            // 4. Dynamic Total Row Styling
            if ($rowCount > 0 && $showTotal === true) {
                $totalRowNumber = $rowCount + 2; 
                
                // Bold the entire last row
                $sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getFont()->setBold(true);
                
                // Find the second to last column letter for "Total:" alignment
                $labelColLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount - 1);

                // Right-align the "Total:" label (usually second to last column)
                $sheet->getStyle($labelColLetter . $totalRowNumber)->getAlignment()->setHorizontal('right');
                
                // Format the last column (the sum) as a number
                $sheet->getStyle($lastCol . $totalRowNumber)->getNumberFormat()->setFormatCode('#,##0.00');
                /**
                 * ACCOUNTING FORMAT: 
                 * The "_-₹*" part tells Excel to put the ₹ on the left and 
                 * the "*" creates a gap that pushes the numbers to the right.
                 */
                $accountingFormat = '_-₹* #,##0.00_-;_-₹* -#,##0.00_-;_-₹* "-"??_-;_-@_-';
    
                $sheet->getStyle("{$lastCol}3:{$lastCol}{$totalRowNumber}")
                    ->getNumberFormat()
                    ->setFormatCode($accountingFormat);

                // 4. Force Right Alignment for the numeric part
                $sheet->getStyle("{$lastCol}3:{$lastCol}{$totalRowNumber}")
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

            }


            // 5. Auto-size
            for ($i = 1; $i <= $columnCount; $i++) {
                $col = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
                $sheet->getColumnDimension($col)->setAutoSize(true);
            }

            header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            header("Content-Disposition: attachment; filename=\"{$fileName}\"");
            header("Cache-Control: max-age=0");

            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            $writer->save('php://output');
            exit();
        }

        // 1) Broker generate the Excel file 
        public function downloadExcelBroker()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $brokerId = $this->request->getGet('broker_id');
                $month = $this->request->getGet('month');

                if (empty($managerId) || empty($brokerId) || empty($month)) {

                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $msg .= empty($brokerId) ? "broker_id " : "";
                    $msg .= empty($month) ? "month " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
            }

                $data = $this->getDataBroker($managerId, $brokerId, $month);
                if (empty($data)) {
                    throw new \RuntimeException('No policies found...', 404);
                }
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }
                
                $header = ['S.No', 
                'Received Date', 'Assigned To', 
                'Partner Name' , 'Partner Code',
                'Insurer Name' ,'Insurer Short Name', 
                'Insured Name', 'Vehicle Number', 
                'Payment Mode', 'Plan Type',
                'Broker Name', 'Issue Date', 'Policy Number', 'Premium Amount'];
                

                
                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
            
                $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month')); 
                $brokerName = $finalData[0][11] ?? ''; 

                $fileName = "Broker_Policies_Details_{$monthName}.xlsx";
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);


                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $titleName,
                    $fileName,
                    true // Pass 'true' to show/bold the total row
                );
                

                return; 

            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                            $ref['debug_msg'] = 'No Data Found';
                            return $this->respond([
                                'status' => 'success',
                                'code'   => 200,
                                'data'   => [],
                                'message' => 'No Data Found',
                                'ref'    => $ref
                            ], 200);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
                            $message = 'Database Error Occurred.';
                        } else {
                            $ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
                            $message = 'Something went wrong. Please try again later.';
                        }

                        return $this->respond([
                            'status' => 'error',
                            'code'   => 500,
                            'data'   => [],
                            'message'=> $message,
                            'ref'    => $ref
                        ], 200);
                    }
        }
    
        // 2) Product generate the Excel file
        public function downloadExcelProduct()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $vehicleType = $this->request->getGet('vehicle_type');
                $month = $this->request->getGet('month');

                if (empty($managerId) || empty($vehicleType) || empty($month)) {
            

                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $msg .= empty($brokerId) ? "broker_id " : "";
                    $msg .= empty($month) ? "month " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
            
                }

                $data = $this->getDataProduct($managerId, $vehicleType, $month);
                if (empty($data)) {
                    throw new \RuntimeException('No policies found for this vehicle type...', 404);
                }
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }
                // $header = ['Vehicle Type', 'Issue Date', 'Policy Number', 'Premium Amount'];
                $header = ['S.No', 
                    'Received Date', 'Assigned To',
                    'Partner Name' , 'Partner Code',
                    'Insurer Name' ,'Insurer Short Name',
                    'Insured Name', 'Vehicle Number',
                    'Payment Mode', 'Plan Type',
                    'Vehicle Type', 'Issue Date', 'Policy Number', 'Premium Amount'];
                
                

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));
                $fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
                $formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month')); 
                $vehicleType = $finalData[0][11] ?? ''; 

                
                $fileName = "Product_Policies_Details_{$monthName}.xlsx";
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $titleName,
                    $fileName,
                    true // This will ignore the bold/alignment logic for the last row
                );
                return; 
            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                            $ref['debug_msg'] = 'No Data Found';
                            return $this->respond([
                                'status' => 'success',
                                'code'   => 200,
                                'data'   => [],
                                'message' => 'No Data Found',
                                'ref'    => $ref
                            ], 200);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
                            $message = 'Database Error Occurred.';
                        } else {
                            $ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
                            $message = 'Something went wrong. Please try again later.';
                        }

                        return $this->respond([
                            'status' => 'error',
                            'code'   => 500,
                            'data'   => [],
                            'message'=> $message,
                            'ref'    => $ref
                        ], 200);
                    }
        }
    
        // 3) Insurer generate the Excel file
        public function downloadExcelInsurer()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $insurerId = $this->request->getGet('insurer_id');
                $month = $this->request->getGet('month');

                // --- ADDED VALIDATION (Simplified logic for clarity) ---
                if (empty($managerId) || empty($insurerId) || empty($month)) {
                    $msg = "Missing: ";
                    $msg .= empty($managerId)  ? "manager_id "  : "";
                    $msg .= empty($insurerId)  ? "insurer_id "  : "";
                    $msg .= empty($month)      ? "month "       : "";
                    $ref = ['debug_msg' => $msg];
                    // Status code remains 200, but JSON code is 400 as per your preference
                    return $this->response->setJSON([
                        'status' => 'error',
                        'code'   => 400,
                        'data'   => [],
                        'message' => 'Missing required parameters',
                        'ref'    => $ref
                    ])->setStatusCode(200); 
                }

                $data = $this->getDataInsurer($managerId, $insurerId, $month);

                // --- 1. MODIFIED LOGIC: Throw RuntimeException if no data ---
                if (empty($data)) {
                    throw new \RuntimeException('No policies Details found for this insurer.', 404);
                }
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }


                // Define column headers
                $header = ['S.No', 
                    'Received Date', 'Assigned To',
                    'Partner Name' , 'Partner Code',
                    'Vehicle Number',
                    'Payment Mode', 'Plan Type',
                    'Insurer Name', 'Insurer Short Name','Insured Name', 'Policy Issued Date','Policy Number', 'Premium Amount'];
                

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));
                $fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
                $formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month')); 
                $InsurerName = $finalData[0][8] ?? "" ; 
    
                
                $fileName = "Insurer_Policies_Details_{$monthName}.xlsx";
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $titleName,
                    $fileName,
                    true // This will ignore the bold/alignment logic for the last row
                );

                return; // STOP CI4 rendering anything else
        
            } catch (\Throwable $e) {

                // ... (Your error handling remains the same) ...
                $ref = [];
                // Case 1: No Data Found (Soft Success JSON)
                if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                    $ref['debug_msg'] = 'No Data Found';
                    return $this->respond([
                        'status' => 'success',
                        'code'   => 200,
                        'data'   => [],
                        'message' => 'No Data Found',
                        'ref'    => $ref
                    ], 200);
                }

                // Case 2: Database or Other Error (Error JSON)
                $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['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
                    $message = 'Database Error Occurred.';
                } else {
                    $ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
                    $message = 'Something went wrong. Please try again later.';
                }

                return $this->respond([
                    'status' => 'error',
                    'code'   => 500,
                    'data'   => [],
                    'message'=> $message,
                    'ref'    => $ref
                ], 200);
            }
        }
        
        // 4) LowPremium generate the Excel file (Below 5OK)
        public function downloadExcelLowPremium()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $from_date = $this->request->getGet('from_date');
                $to_date = $this->request->getGet('to_date');
                
                if (empty($managerId)) {
                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
                }
                
                if (empty($from_date) || empty($to_date)) {

                    // Default: last 10 days
                    $fromObj = new \DateTime('-10 days');
                    $toObj   = new \DateTime();

                } else {

                    // Convert d-m-Y → DateTime
                    $fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
                    $toObj   = \DateTime::createFromFormat('d-m-Y', $to_date);

                    if (!$fromObj || !$toObj) {
                        return $this->response->setJSON([
                            'status' => 'error',
                            'message' => 'Invalid date format (expected d-m-Y)',
                        ]);
                    }
                }

                // For DB query
                $fromDate = $fromObj->format('Y-m-d 00:00:00');
                $toDate   = $toObj->format('Y-m-d 23:59:59');

                $data = $this->getDataLowPremiumAgent($managerId,$fromDate,$toDate);
                if (empty($data)) {
                    throw new \RuntimeException('No low premium agents found...', 404);
                }

                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;
 
                foreach ($data as $row) {
                    // 1. Convert associative row to indexed array
                    $indexedRow = array_values($row);

                    // 2. Add to total sum using the last index (total_premium_amount)
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];

                    // 3. Remove columns you don't want in the Excel
                    unset($indexedRow[$lastIndex]); // Remove total_premium_amount (raw number)
                    unset($indexedRow[4]);          // Remove last_business_date
                    unset($indexedRow[0]);          // Remove agent_id

                    // 4. Re-index the array so it's clean (0, 1, 2...)
                    $cleanedRow = array_values($indexedRow);

                    // 5. Merge with Serial Number
                    $finalData[] = array_merge([$serialNo++], $cleanedRow);
                }

                $header = ['S.No', 'Partner Code','Partner Name','Partner Status' , 'Premium Amount'];                
                
                

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
            
                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                // Note: $month variable is undefined, assuming you want current month for filename
                $monthName = date('M_Y');
                $PartnerName = $finalData[0][2] ?? ''; 
                $fileName = 'Non_Performing_Partner_Business_Less_than_50000_Policy_Details.xlsx';
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $titleName,
                    $fileName,
                    true // This will ignore the bold/alignment logic for the last row
                );

                return; 
            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                            $ref['debug_msg'] = 'No Data Found';
                            return $this->respond([
                                'status' => 'success',
                                'code'   => 200,
                                'data'   => [],
                                'message' => 'No Data Found',
                                'ref'    => $ref
                            ], 200);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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['debug_msg'] = 'Database Error Occurred: ' . $e->getMessage();
                            $message = 'Database Error Occurred.';
                        } else {
                            $ref['debug_msg'] = 'An unexpected error occurred: ' . $e->getMessage();
                            $message = 'Something went wrong. Please try again later.';
                        }

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

        // 5.1) AgentMonthly generate the Excel file
        public function downloadExcelPerformingAgentsByID()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $agentId = $this->request->getGet('agent_id');
                $from_date = $this->request->getGet('from_date');
                $to_date = $this->request->getGet('to_date');
                // $month = $this->request->getGet('month');

                if (empty($managerId) || empty($agentId)) {

                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $msg .= empty($agentId) ? "agent_id " : "";
                    // $msg .= empty($month) ? "month " : "";
                    $ref = ['debug_msg' => $msg];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
                }

                if (empty($from_date) || empty($to_date)) {

                    // Default: last 10 days
                    $fromObj = new \DateTime('-10 days');
                    $toObj   = new \DateTime();

                } else {

                    // Convert d-m-Y → DateTime
                    $fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
                    $toObj   = \DateTime::createFromFormat('d-m-Y', $to_date);

                    if (!$fromObj || !$toObj) {
                        return $this->response->setJSON([
                            'status' => 'error',
                            'message' => 'Invalid date format (expected d-m-Y)',
                        ]);
                    }
                }

                // For DB query
                $fromDate = $fromObj->format('Y-m-d 00:00:00');
                $toDate   = $toObj->format('Y-m-d 23:59:59');


                // Renamed helper function for consistency: getAgentMonthlyPoliciesData
                $data = $this->getDataPerformingAgentsByID($managerId, $agentId,$fromDate,$toDate); // , $month);
                
                if (empty($data)) {
                    throw new \RuntimeException('No policies found for this agent for the specified month.', 404);
                }
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });                

                $header = ['S.No', 
                    'Received Date', 'Assigned To',
                    'Insurer Name', 'Insurer Short Name',
                    'Insured Name', 'Vehicle Number',
                    'Payment Mode', 'Plan Type', 'Issue Date',
                    'Partner Code', 'Partner Name', 
                    'Policy Number', 'Premium Amount'];
                

                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));
                $fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
                $formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                // $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month')); 
                
                $PartnerName = $finalData[0][11] ?? ''; 
                
                $fileName = 'Performing_Partner_Policy_Details.xlsx';
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $titleName,
                    $fileName,
                    true // This will ignore the bold/alignment logic for the last row
                );


                return; 
            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        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);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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.';
                            $message = 'Database Error Occurred.';
                        } else {
                            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                            $message = 'An unexpected error occurred: ' . $e->getMessage();
                        }

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

        // 5.2) Top 50 Agent the Excel file
        public function downloadExcelPerformingAgentsTop50()
        {
      
            try{
                $managerId = $this->request->getGet('manager_id');
                $from_date = $this->request->getGet('from_date');
                $to_date = $this->request->getGet('to_date');
        
                if (empty($managerId)) {
                    $msg = "Missing: manager_id";
                    $ref = ['debug_msg' => $msg];
                    return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
                }

                if (empty($from_date) || empty($to_date)) {

                    // Default: last 10 days
                    $fromObj = new \DateTime('-10 days');
                    $toObj   = new \DateTime();

                } else {

                    // Convert d-m-Y → DateTime
                    $fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
                    $toObj   = \DateTime::createFromFormat('d-m-Y', $to_date);

                    if (!$fromObj || !$toObj) {
                        return $this->response->setJSON([
                            'status' => 'error',
                            'message' => 'Invalid date format (expected d-m-Y)',
                        ]);
                    }
                }

                // For DB query
                $fromDate = $fromObj->format('Y-m-d 00:00:00');
                $toDate   = $toObj->format('Y-m-d 23:59:59');

                // For filename
                $startDateFile = $fromObj->format('d_m_Y');
                $endDateFile   = $toObj->format('d_m_Y');

                // For title
                $startDateTitle = $fromObj->format('d-m-Y');
                $endDateTitle   = $toObj->format('d-m-Y');

                // Renamed helper function for consistency: getAgentMonthlyPoliciesData
                $data = $this->getDataPerformingAgentsTop50($managerId,$fromDate,$toDate);
                $header = ['S.No', 'Partner Code', 'Partner Name', 'Policy Count', 'Premium Amount'];
                
                if (empty($data)) {
                    throw new \RuntimeException('No policies found.', 404);
                }

                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }


                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));
                $fmt = new \NumberFormatter('en_IN', \NumberFormatter::CURRENCY);
                $formattedGrandTotal = $fmt->formatCurrency($totalSum, "INR");

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                $fileName = 'Performing_Partner_Top_50_Policies_Details_Monthly_Report.xlsx';

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    "Performing Partner ( Top 50 ) Policies Details - From ".$startDateTitle." to ".$endDateTitle." Report",
                    $fileName,
                    true // This will ignore the bold/alignment logic for the last row
                );                

                return; 
            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        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);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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.';
                            $message = 'Database Error Occurred.';
                        } else {
                            $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                            $message = 'An unexpected error occurred: ' . $e->getMessage();
                        }

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

        // 6) Agents who all are Without Policies generate the Excel file (last 10 days no Policies created by the agents)
        public function downloadExcelWithoutPolicies()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $from_date = $this->request->getGet('from_date');
                $to_date = $this->request->getGet('to_date');
                
                if (empty($managerId)) {
                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
                }

                if (empty($from_date) || empty($to_date)) {

                    // Default: last 10 days
                    $fromObj = new \DateTime('-10 days');
                    $toObj   = new \DateTime();

                } else {

                    // Convert d-m-Y → DateTime
                    $fromObj = \DateTime::createFromFormat('d-m-Y', $from_date);
                    $toObj   = \DateTime::createFromFormat('d-m-Y', $to_date);

                    if (!$fromObj || !$toObj) {
                        return $this->response->setJSON([
                            'status' => 'error',
                            'message' => 'Invalid date format (expected d-m-Y)',
                        ]);
                    }
                }

                // For DB query
                $fromDate = $fromObj->format('Y-m-d 00:00:00');
                $toDate   = $toObj->format('Y-m-d 23:59:59');

                // For filename
                $startDateFile = $fromObj->format('d_m_Y');
                $endDateFile   = $toObj->format('d_m_Y');

                // For title
                $startDateTitle = $fromObj->format('d-m-Y');
                $endDateTitle   = $toObj->format('d-m-Y');


                $data = $this->getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate);

                if (empty($data)) {
                    throw new \RuntimeException('No Partners without policies found.', 404);
                }

                $header = ['S.No','Partner Code','Partner Name', 'Email', 'Mobile'];

                $finalData = [];
                $serialNo = 1; // Start counter at 1

                // 2. Loop through and prepend the serial number
                foreach ($data as $row) {
                    // We create a new array for each row
                    $finalData[] = [
                        $serialNo++, 
                        $row[0], 
                        $row[1], 
                        $row[2],      
                        $row[3]      
                    ];
                }
                
                

                // --- Filename Generation ---

                // $fileName = 'Non_Performing_Partners_No_Business_For_Last_10_Days_' . $startDateFile . '_to_' . $endDateFile . '.xlsx';
                $fileName = 'Non_Performing_Partners_No_Business_From_' . $startDateFile . '_To_' . $endDateFile . '.xlsx';


                // Example output: Agents_Without_Policies_20251206_to_20251215.xlsx 

                // --- CENTRALIZED CALL ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    "Non Performing Partners ( No Business From " . $startDateTitle . " to " . $endDateTitle . " )",
                    $fileName,
                    false // This will ignore the bold/alignment logic for the last row
                );

                return; 
            } catch (\Throwable $e) {

                        // ... (Your error handling remains the same) ...
                        $ref = [];
                        // Case 1: No Data Found (Soft Success JSON)
                        if ($e instanceof \RuntimeException && $e->getCode() === 404) {
                            $ref['message'] = 'No Data Found';
                            return $this->respond([
                                'status' => 'success',
                                'code'   => 200,
                                'data'   => [],
                                'ref'    => $ref
                            ], 200);
                        }

                        // Case 2: Database or Other Error (Error JSON)
                        $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() ?: 500,
                            'data'   => [],
                            'ref'    => $ref
                        ], 200);
                    }
        }

        // 7 Function to generate the Excel file
        public function downloadExcelStaffAndProduct()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $executiveId = $this->request->getGet('sales_executive_id');
                $vehicleType = $this->request->getGet('vehicle_type');
                $month = $this->request->getGet('month');

                // --- ADDED VALIDATION (Simplified logic for clarity) ---
                if (empty($managerId) || empty($executiveId) || empty($vehicleType) || empty($month)) {
                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $msg .= empty($executiveId) ? "sales_executive_id " : "";
                    $msg .= empty($vehicleType) ? "vehicle_type " : "";
                    $msg .= empty($month) ? "month " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
            
                }

                $data = $this->getDataStaffAndProduct($managerId, $executiveId,$vehicleType, $month);
                if (empty($data)) {
                    throw new \RuntimeException('No policies found for the specified month.', 404);
                }
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                foreach ($data as $row) {
                    $indexedRow = array_values($row);
                    $lastIndex = count($indexedRow) - 1;
                    $totalSum += (float) $indexedRow[$lastIndex];
                    unset($indexedRow[$lastIndex]);
                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }
                // foreach ($data as $row) {
                //     $finalData[] = array_merge([$serialNo++], $row);
                // }
                $executiveName = $finalData[0][11] ?? '';
                $vehicleType = $finalData[0][12] ?? '';
                // Define column headers

                $header = ['S.No', 
                    'Received Date', 'Assigned To',
                    'Partner Name' , 'Partner Code',
                    'Insurer Name' ,'Insurer Short Name',
                    'Insured Name', 'Vehicle Number',
                    'Payment Mode', 'Plan Type',
                    'Sales Executive Name', 'Vehicle Type', 'Policy Number','Issued Date', 'Premium Amount'];
                
                // --- 1. MODIFIED LOGIC: Throw RuntimeException if no data ---
                

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));   

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;
                
                
                $monthName = ($month === 'current') ? date('M_Y') : date('M_Y', strtotime('-1 month')); 
                $fileName  = "Sales_Executive_Product_Wise_" . $monthName . ".xlsx";
                $titleName = str_replace(['_', '.xlsx'], [' ', ''], $fileName);

                $this->streamExcelFile($header,$finalData,$titleName,$fileName,true);

                return; // STOP CI4 rendering anything else
        
            } catch (\Throwable $e) {

                // ... (Your error handling remains the same) ...
                $ref = [];
                // Case 1: No Data Found (Soft Success JSON)
                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);
                }

                // Case 2: Database or Other Error (Error JSON)
                $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.';
                    $message = 'Database Error Occurred.';
                } else {
                    $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                    $message = 'An unexpected error occurred: ' . $e->getMessage();
                }

                return $this->respond([
                    'status' => 'error',
                    'code'   => 500,
                    'data'   => [],
                    'message'=> $message,
                    'ref'    => $ref
                ], 200);
            }
        }
        
        // 8 ZIP inside 3 Excel 
        public function downloadExcelAsZIP($managerId = null)
        {
            // Ensure managerId is retrieved correctly if not passed as argument
            $managerId = $managerId ?? $this->request->getGet('manager_id');

            if (empty($managerId)) {
                return $this->response->setJSON([
                    'status' => 'error',
                    'code' => 400,
                    'message' => 'Missing required parameter: manager_id'
                ]);
            }

            $fromDate = date('Y-m-d', strtotime('-10 days'));
            $toDate   = date('Y-m-d');
            // 1. Fetch Data First
            $monthlyData = $this->getDataPerformingAgentsByID($managerId,null,$fromDate,$toDate);
            $lowPremiumData = $this->getDataLowPremiumAgent($managerId,$fromDate,$toDate);
            $inactiveData = $this->getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate);

            // 2. Add Total Rows where needed (Premium is usually the last index)
            if (!empty($monthlyData)) {
                $totalMonthly = array_sum(array_column($monthlyData, 12)); // Index 12 is 'premium_amount'
                $monthlyData[] = ['', '', '', '', '', '', '', '', '', '', '', 'Total:', $totalMonthly];
            }

            if (!empty($lowPremiumData)) {
                $totalLow = array_sum(array_column($lowPremiumData, 13)); // Index 13 is 'premium_amount'
                $lowPremiumData[] = ['', '', '', '', '', '', '', '', '', '', '', '', 'Total:', $totalLow];
            }

            // 3. Prepare Report Configuration
            $reports = [
                'Monthly_Policies_Details.xlsx'   => ['data' => $monthlyData, 'header' => ['Received Date', 'Assigned To', 'Insurer', 'Short Name', 'Insured', 'Reg No', 'Mode', 'Plan', 'Agent', 'Code', 'Policy #', 'Issued Date', 'Premium'], 'title' => "Monthly Report",'showTotal' => true],
                'Low_Premium_Policies_Details.xlsx' => ['data' => $lowPremiumData, 'header' => ['Received Date', 'Assigned To', 'Insurer', 'Short Name', 'Insured', 'Reg No', 'Mode', 'Plan', 'Agent', 'Code', 'Status', 'Policy #', 'Issued Date', 'Premium'], 'title' => "Low Premium Report",'showTotal' => true],
                'Partners_Without_Policies_Details.xlsx'    => ['data' => $inactiveData, 'header' => ['Partner Name', 'Partner Code', 'Email', 'Mobile'], 'title' => "Inactive Partners",'showTotal' => false]
            ];

            // 4. Initialize Zip
            $zipFileName = 'Agent_Reports_' . date('Y-m-d_H-i') . '.zip';
            $zipFilePath = tempnam(sys_get_temp_dir(), 'zip');

            $zip = new \ZipArchive();
            if ($zip->open($zipFilePath, \ZipArchive::CREATE | \ZipArchive::OVERWRITE) === TRUE) {
                foreach ($reports as $name => $config) {
                    $excelContent = $this->generateExcelBinary($config['header'], $config['data'], $config['title'],$config['showTotal']);
                    $zip->addFromString($name, $excelContent);
                }
                $zip->close();
            }

            // 5. Direct Download
            // Instead of creating a file on disk, we can send the ZIP data directly.
            // This avoids the "deleteFileAfterSend" error entirely.

            $zipData = file_get_contents($zipFilePath); // Read the zip file into a variable
            unlink($zipFilePath); // Delete the temp file immediately

            return $this->response->download($zipFileName, $zipData);
        }


        // GENERATE ZIP
        private function generateExcelBinary(array $header, array $data, string $title, bool $showTotal = true): string
        {
            $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // 1. Calculate dimensions
            $columnCount = count($header);
            $lastCol = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($columnCount);
            $rowCount = count($data);

            // 2. Title Styling
            $sheet->mergeCells("A1:{$lastCol}1");
            $sheet->setCellValue('A1', $title);
            $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(14);
            $sheet->getStyle('A1')->getAlignment()->setHorizontal('center');

            // 3. Headers (Row 2)
            $sheet->fromArray($header, NULL, 'A2');
            $sheet->getStyle("A2:{$lastCol}2")->getFont()->setBold(true);
            $sheet->getStyle("A2:{$lastCol}2")->getFill()
                ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setARGB('F2F2F2'); // Light gray background

            // 4. Data (Starting Row 3)
            $sheet->fromArray($data, NULL, 'A3');

            // 5. Bold the Total Row (The last row of data)
            if ($rowCount > 0 && $showTotal === true) {
                $totalRowNumber = $rowCount + 2; // Row 1 (Title) + Row 2 (Header) + Data Rows
                $sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getFont()->setBold(true);
        
                // Optional: Add a top border to the total row to make it look professional
                // $sheet->getStyle("A{$totalRowNumber}:{$lastCol}{$totalRowNumber}")->getBorders()->getTop()->setBorderStyle(\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN);
            }

            // 6. Auto-size Columns
            // Using column index to avoid issues with double-letter columns (e.g., AA, AB)
            for ($i = 1; $i <= $columnCount; $i++) {
                $colLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
                $sheet->getColumnDimension($colLetter)->setAutoSize(true);
            }

            // 7. Capture Binary Output
            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
            
            // Use Output Buffering to grab the file content as a string
            ob_start();
            $writer->save('php://output');
            $excelBinary = ob_get_clean();
    
            return $excelBinary;
        }

        public function downloadExcelStaffPendingSummary()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                

                // --- ADDED VALIDATION (Simplified logic for clarity) ---
                if (empty($managerId)) {
                    $msg = "Missing: manager_id";
                    $ref = ['debug_msg' => $msg];
                    return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
                }

                $data = $this->getDataStaffPendingSummary($managerId);
                
                if (empty($data)) {
                    throw new \RuntimeException('No summary found.', 404);
                }
                
                // Define column headers
                $header = ['S.No', 'Staff Name', 'Today Assigned', 'Pending', 'In Progress', 'Completed', 'Previous Pending'];

                // Initialize totals
                $sums = array_fill(2, 5, 0); // Start from index 2, cover 5 columns
                $finalData = [];
                $serialNo = 1;

                // Process data and calculate sums
                foreach ($data as $row) {
                    // Convert row to indexed array values to ensure merge works correctly
                    $rowData = array_values($row); 
                    
                    // Add Serial Number
                    $finalData[] = array_merge([$serialNo++], $rowData);

                    // Summing columns index 2 to 6
                    for ($i = 2; $i <= 6; $i++) {
                        // Use index $i-1 because $row doesn't have S.No yet, but $rowData does
                        $sums[$i] += (int)($rowData[$i - 1] ?? 0);
                    }
                }

                //  Create the Total Row
                $totalRow = array_fill(0, count($header), ''); // Create empty row
                $totalRow[1] = 'Total:'; // Label under "Staff Name"                

                foreach ($sums as $index => $sumValue) {
                    // This ensures that even if the sum is 0, it puts the digit '0' in the cell
                    $totalRow[$index] = $sumValue; 
                }

                //  Fill sums into the total row
                foreach ($sums as $index => $sumValue) {
                    $totalRow[$index] = $sumValue;
                }

                //  Add to final data
                $finalData[] = $totalRow;

                $fileName = 'Staff_Level_Pending_Summary.xlsx';


                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    "Staff Level Pending Summary",
                    $fileName,
                    false
                );

                return; // STOP CI4 rendering anything else
        
            } catch (\Throwable $e) {

                // ... (Your error handling remains the same) ...
                $ref = [];
                // Case 1: No Data Found (Soft Success JSON)
                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);
                }

                // Case 2: Database or Other Error (Error JSON)
                $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.';
                    $message = 'Database Error Occurred.';
                } else {
                    $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                    $message = 'An unexpected error occurred: ' . $e->getMessage();
                }

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


        public function downloadExcelStaffPendingSummaryByID()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $staffId = $this->request->getGet('staff_id');
                $status = $this->request->getGet('status');
                

                // --- ADDED VALIDATION (Simplified logic for clarity) ---
                if (empty($managerId) || empty($staffId) || empty($status)) {
                    $msg = "Missing: ";
                    $msg .= empty($managerId) ? "manager_id " : "";
                    $msg .= empty($staffId) ? "staff_id " : "";
                    $msg .= empty($status) ? "status " : "";
                    $ref = ['debug_msg' => $msg];
                    // $ref = ['debug_msg' => 'Missing required parameters: manager_id, broker_id, and month must be provided.'];
                    return $this->response->setJSON(['status' => 'error','code' => 400,'data' => [],'message' => 'Missing required parameters','ref' => $ref])->setStatusCode(200);
            
                }

                $data = $this->getDataStaffPendingSummaryByID($managerId,$staffId,$status);
                
                if (empty($data)) {
                    throw new \RuntimeException('No summary found.', 404);
                }
                
                // Define column headers
                
                
                $header = ['S.No', 'Received Date', 
                    'Assigned To(Staff Name)',
                    'Partner Name', 'Partner Code',
                    'Insurer Name', 'Insurer Short Name',
                    'Insured Name', 'Vehicle Number', 
                    'Payment Mode', 'Plan Type'];
                // Initialize totals
                $lastIndex = count(reset($data)) - 1;
                // usort($data, function($a, $b) use ($lastIndex) {
                //         return (float)$a[$lastIndex] <=> (float)$b[$lastIndex];
                // });
                usort($data, function($a, $b) use ($lastIndex) {
                    return (float)$b[$lastIndex] <=> (float)$a[$lastIndex];
                });
                $finalData = [];
                $serialNo = 1;
                
                foreach ($data as $row) {
                    $finalData[] = array_merge([$serialNo++], $row);
                }

                $staffName = $finalData[0][2] ?? 'Staff';
            
                $cleanStaffName = str_replace(' ', '_', strtolower($staffName)); 
                $cleanStaffName2 = ucwords($staffName); 

                $cleanStatus = str_replace('_', ' ', strtolower($status));
                $cleanStatus2 = ucwords(str_replace('_', ' ', strtolower($status)));
                
                $fileName = $cleanStaffName . '_' . $cleanStatus . '_Summary.xlsx';

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    $cleanStaffName2 ." ". $cleanStatus2." Summary",
                    $fileName,
                    false
                );

                return; // STOP CI4 rendering anything else
        
            } catch (\Throwable $e) {

                // ... (Your error handling remains the same) ...
                $ref = [];
                // Case 1: No Data Found (Soft Success JSON)
                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);
                }

                // Case 2: Database or Other Error (Error JSON)
                $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.';
                    $message = 'Database Error Occurred.';
                } else {
                    $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                    $message = 'An unexpected error occurred: ' . $e->getMessage();
                }

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


        public function downloadExcelEndorsement()
        {
            try{
                $managerId = $this->request->getGet('manager_id');
                $search = $this->request->getGet('search');
                $agent_id           = $this->request->getGet('agent_id');
                $policy_number      = $this->request->getGet('policy_number');
                $from_date          = $this->request->getGet('from_date');
                $to_date            = $this->request->getGet('to_date');
                $endorsement_type   = $this->request->getGet('endorsement_type');
                $insurer_id         = $this->request->getGet('insurer_id');
                $status             = $this->request->getGet('status');
                $verification       = $this->request->getGet('verification');
                

                // --- ADDED VALIDATION (Simplified logic for clarity) ---
                if (empty($managerId)) {
                    $msg = "Missing: manager_id";
                    $ref = ['debug_msg' => $msg];
                    return $this->response->setJSON(['status' => 'error', 'code' => 400, 'data' => [], 'message' => 'Missing required parameters', 'ref' => $ref])->setStatusCode(200);
                }

                // $data = $this->getDataEndorsement($managerId,$search);
                $data = $this->getDataEndorsement($managerId,$search,$agent_id,$policy_number,$from_date,$to_date,$endorsement_type,$insurer_id,$status,$verification);
                
                
                if (empty($data)) {
                    throw new \RuntimeException('No Endorsement Details found.', 404);
                }
                
                $header = [
                    'S.No',
                    'Created Date',
                    'Internal/External', 
                    'Created By',
                    'Policy Number',
                    'Start Date', 'End Date',
                    'Vehicle No',
                    'Type Of Endorsement',
                    'Endorsement Number',
                    'Insured Name',
                    'Insurer',
                    'Broker',
                    'Partner Code And Name',
                    'Contact Person',
                    'Remarks',
                    'Is Financial',
                    'Status','Verification','Pending Days',
                    'Endorsement Premium Amount',
                    'Commission Amount',
                ];
                                
                $finalData = [];
                $serialNo = 1;
                $totalSum = 0;

                // foreach ($data as $row) {
                //     $indexedRow = array_values($row);
                //     $lastIndex = count($indexedRow) - 1;
                //     $totalSum += (float) $indexedRow[$lastIndex];
                //     unset($indexedRow[$lastIndex]);
                //     $finalData[] = array_merge([$serialNo++], $indexedRow);
                // }

                foreach ($data as $row) {

                    $indexedRow = array_values($row);

                    // Commission Amount is last column
                    $commissionIndex = count($indexedRow) - 1;

                    $totalSum += (float) $indexedRow[$commissionIndex];

                    $finalData[] = array_merge([$serialNo++], $indexedRow);
                }

                // Calculate and append total
                $lastColIndex = count($header) - 1; // Get index of the last column (Premium)
                // $grandTotal   = array_sum(array_column($finalData, $lastColIndex));   

                $totalRow = array_fill(0, count($header), ''); // Create empty row of same width
                $totalRow[$lastColIndex - 1] = 'Total:';       // Put label in 2nd to last
                $totalRow[$lastColIndex] = $totalSum; 
                $finalData[] = $totalRow;

                $fileName = 'Endorsement_Report_' . date('YmdHis') . '.xlsx';

                // --- CALL CENTRALIZED FUNCTION ---
                $this->streamExcelFile(
                    $header,
                    $finalData,
                    "Endorsement Report",
                    $fileName,
                    true
                );

                return; // STOP CI4 rendering anything else
        
            } catch (\Throwable $e) {

                // ... (Your error handling remains the same) ...
                $ref = [];
                // Case 1: No Data Found (Soft Success JSON)
                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);
                }

                // Case 2: Database or Other Error (Error JSON)
                $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.';
                    $message = 'Database Error Occurred.';
                } else {
                    $ref['message'] = 'An unexpected error occurred: ' . $e->getMessage();
                    $message = 'An unexpected error occurred: ' . $e->getMessage();
                }

                return $this->respond([
                    'status' => 'error',
                    'code'   => 500,
                    'data'   => [],
                    'message'=> $message,
                    'ref'    => $ref
                ], 200);
            }
        }
        
 
        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @param int $brokerId
         * @param string $month ('current' or 'previous')
         * @return array
         */
        private function getDataBroker($managerId, $brokerId, $month): array
        {
            // --- Correction 1: Initialize Database Connection ---
            
            $data = [];

            try {
                $builder = $this->db->table('partner_policy pp');
                // Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
                
                
                $builder->select('
                DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                S.name as assigned_to_name,
                A.name as agent_name, A.agent_code,
                I.name as insurer_name,I.short_name as insurer_short_name,
                pe.name as insured_name,
                pe.reg_no,
                pm.value as payment_mode_value,
                ipti.insurance_plan_type,
                pb.name AS broker_name, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number)                  AS policy_number, 
                CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
                pp.premium_amount AS raw_premium');
                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
                $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                $builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
                $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
                $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
                
                // --- Correction 2: Use Robust Date Range Filtering ---
                if ($month === "current") {
                    // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                } 
                else if ($month === "previous") {
                    // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                }
                
                // Broker and Manager Filter
                $builder->where('pp.manager_id', $managerId);
                $builder->where('pb.id', $brokerId);
                $builder->orderBy('pp.issued_date', 'DESC');

                $results = $builder->get()->getResultArray();
                
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;
        }

        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @param string $vehicleType
         * @param string $month ('current' or 'previous')
         * @return array
         */
        private function getDataProduct($managerId, $vehicleType, $month): array
        {
            // --- Correction 1: Initialize Database Connection ---
            
            $data = [];

            try {
                $builder = $this->db->table('partner_policy pp');
                // Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
                $builder->select('
                DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                S.name as assigned_to_name,
                A.name as agent_name, A.agent_code,
                I.name as insurer_name,I.short_name as insurer_short_name,
                pe.name as insured_name,
                pe.reg_no,
                pm.value as payment_mode_value,
                ipti.insurance_plan_type,
                pp.vehicle_type, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number)                  AS policy_number,
                CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
                pp.premium_amount AS raw_premium');

                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                $builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
                $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
                $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
                
                
                // --- Correction 2: Use Robust Date Range Filtering ---
                if ($month === "current") {
                    // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                } 
                else if ($month === "previous") {
                    // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                }
                
                // Broker and Manager Filter
                $builder->where('pp.manager_id', $managerId);
                $builder->where('pp.vehicle_type', $vehicleType);
                

                $results = $builder->get()->getResultArray();
                
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;
        }
    
        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @param int $insurerId
         * @param string $month ('current' or 'previous')
         * @return array
         */
        private function getDataInsurer($managerId, $insurerId, $month): array
        {
            // --- Correction 1: Initialize Database Connection ---
            
            $data = [];

            try {
                $builder = $this->db->table('partner_policy pp');
                // Note: I removed 'broker_id' from SELECT as it's redundant in the Excel sheet details
                $builder->select('
                DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                S.name as assigned_to_name,
                A.name as agent_name, A.agent_code,
                pe.reg_no,
                pm.value as payment_mode_value,
                ipti.insurance_plan_type,
                i.name AS insurer_name,i.short_name,pp.insured_name, DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,CONCAT("`", pp.policy_number)                  AS policy_number,
                CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
                pp.premium_amount AS raw_premium');

                $builder->join('partner_quotation pq', 'pq.id = pp.quotation_id', 'left');
                $builder->join('insurers i', 'i.id = pq.insurer_id', 'left');
                
                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = pq.payment_mode_id', 'left');
                $builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = pq.insurance_plan_type_id', 'left');
                $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');
                
                // --- Correction 2: Use Robust Date Range Filtering ---
                if ($month === "current") {
                    // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                } 
                else if ($month === "previous") {
                    // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                }
                
                // Broker and Manager Filter
                $builder->where('pp.manager_id', $managerId);
                $builder->where('i.id', $insurerId);
                $builder->where('i.is_active',1);
                $builder->orderBy('pp.issued_date', 'DESC');
                // print_r($builder->getCompiledSelect());die;
                $results = $builder->get()->getResultArray();
                
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;
        }
    
        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @param int $executiveId
         * @param string $vehicleType 
         * @param string $month ('current' or 'previous')
         * @return array
         */
        private function getDataStaffAndProduct($managerId, $executiveId,$vehicleType, $month): array
        {
            // --- Correction 1: Initialize Database Connection ---
            
            $data = [];

            try {
                $builder = $this->db->table('partner_policy pp');
                
                $builder->select('
                DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                S.name as assigned_to_name,
                pa.name as agent_name, pa.agent_code,
                I.name as insurer_name,I.short_name as insurer_short_name,
                pe.name as insured_name,
                pe.reg_no,
                pm.value as payment_mode_value,
                ipti.insurance_plan_type,
                pse.name as sales_executive_name, pp.vehicle_type AS vechile_type,CONCAT("`", pp.policy_number)                  AS policy_number,DATE_FORMAT(pp.issued_date, "%d-%m-%Y") AS issued_date,
                CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
                pp.premium_amount AS raw_premium');
                
                $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'left');
                $builder->join('partner_sales_executive pse', 'pse.id = pa.sales_executive_id', 'left');

                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
                $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
                $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
                $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');

                
                // --- Correction 2: Use Robust Date Range Filtering ---
                if ($month === "current") {
                    // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                } 
                else if ($month === "previous") {
                    // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
                    $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                    $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                }
                
                // Broker and Manager Filter
                $builder->where('pp.manager_id', $managerId);
                $builder->where('pa.sales_executive_id', $executiveId);
                $builder->where('pa.sales_executive_id IS NOT NULL');
                $builder->where('pse.is_active', 1);
                $builder->where('pp.vehicle_type', $vehicleType);
                $builder->orderBy('pp.issued_date', 'DESC');
                $results = $builder->get()->getResultArray();
                
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;
        }

        
    
        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @param int $AgentId
         * @param string $month ('current' or 'previous')
         * @return array
         */
        private function getDataPerformingAgentsByID($managerId, $AgentId,$fromDate,$toDate): array
        {
            $data = [];

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

                // 1. Properly aligned Select Statement
                $builder->select('
                    DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                    S.name                                          AS assigned_to_name,
                    I.name                                          AS insurer_name,
                    I.short_name                                    AS insurer_short_name,
                    pe.name                                         AS insured_name,
                    pe.reg_no,
                    pm.value                                        AS payment_mode_value,
                    ipti.insurance_plan_type,
                    DATE_FORMAT(pp.issued_date, "%d-%m-%Y")         AS issued_date,
                    pa.agent_code                                   AS agent_code,
                    pa.name                                         AS agent_name,
                    CONCAT("`", pp.policy_number)                  AS policy_number,
                    CONCAT("₹ ", FORMAT(pp.premium_amount, 2, "en_IN")) AS premium_amount,
                    pp.premium_amount AS raw_premium');

                // 2. Joins
                $builder->join('partner_agent pa', 'pp.agent_id = pa.id', 'inner');
                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
                $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
                $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
                $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');

                // 3. Where Conditions
                // $builder->where('pp.manager_id', $managerId);
                $builder->where('pp.policy_number IS NOT NULL');
                $builder->where('pa.id', $AgentId);

                // $fromDate = date('Y-m-d', strtotime('-10 days'));
                // $toDate   = date('Y-m-d');
                
                

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

                // Current Month Date Range Filter
                // if ($month === "current") {
                //  // WHERE issued_date >= start_of_current_month AND issued_date < start_of_next_month
                    // $builder->where("pp.issued_date >= DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                    // $builder->where("pp.issued_date < DATE_FORMAT(DATE_ADD(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                // } 
                // else if ($month === "previous") {
                //     // WHERE issued_date >= start_of_previous_month AND issued_date < start_of_current_month
                //     $builder->where("pp.issued_date >= DATE_FORMAT(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), '%Y-%m-01')", NULL, FALSE);
                //     $builder->where("pp.issued_date < DATE_FORMAT(CURRENT_DATE(), '%Y-%m-01')", NULL, FALSE);
                // }

                // 4. Order and Limit
                $builder->orderBy('pp.issued_date', 'DESC');

                // 5. Fetch and Restructure
                $results = $builder->get()->getResultArray();

                foreach ($results as $row) {
                    // Using array_values ensures we return a indexed 2D array for PhpSpreadsheet
                    $data[] = array_values($row);
                }

            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                throw $e; 
            }

            return $data;
        }


        public function getDataPerformingAgentsTop50($manager_id,$fromDate,$toDate){

            $data = [];

            try {                
                $builder = $this->db->table('partner_policy pp');
                // $fromDate = date('Y-m-d', strtotime('-10 days'));
                // $toDate   = date('Y-m-d');

                $builder->select("
                    pa.agent_code,
                    pa.name AS agent_name,
                    COUNT(pp.policy_number) AS policy_count,
                    CONCAT('₹ ', FORMAT(SUM(pp.premium_amount), 2, 'en_IN')) AS premium_amount,
                    SUM(pp.premium_amount) AS raw_premium");

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

                // $builder->where("MONTH(pp.issued_date) = MONTH('$customDate')", null, false);
                // $builder->where("YEAR(pp.issued_date) = YEAR('$customDate')", null, false);
                $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);

                $results = $builder->get()->getResultArray();
                
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;

        }
       
        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @return array
         */
        private function getDataAgentsWithoutPolicies($managerId,$fromDate,$toDate): array
        {
            $data = [];

            try {
                
                    // 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'
                    // );
                    $builder->join('partner_policy recent',"pa.id = recent.agent_id 
                        AND recent.issued_date >= '{$fromDate}'
                        AND recent.issued_date <= '{$toDate}'",
                        '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. Execute
                    $query = $builder->get();
                    $results = $query->getResultArray(); 

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

                

                foreach ($results as $row) { 
                    // Now you can use unset because $row is an array
                    unset($row['agent_id']);
                    unset($row['is_active']);
                    // If you want to show last_issued_date in the Excel, don't unset it here.
                    // If you want to skip it, keep the unset.
                    unset($row['last_issued_date']); 

                    $data[] = array_values($row);
                }

            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                throw $e;
            }

            return $data;
        }

        /**
         * Executes the detailed policy query and extracts the data for the spreadsheet.
         * @param string $managerId
         * @return array
         */
        //any changed in this query also change in do the same in  DashboardController - getDataLowPremiumAgent 
        public function getDataLowPremiumAgent($manager_id,$fromDate,$toDate){
            
            $builder = $this->db->table('partner_policy pp');

            $builder->select("
                pa.id AS agent_id,
                pa.agent_code,
                pa.name AS agent_name,
                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);

            // RAW condition must be inside where() with FALSE
            // $builder->where("
            //     pp.issued_date >= DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)
            // ", null, false);

            // 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');
            return $builder->get()->getResultArray();
        }

        // Second Comment
        // private function getDataLowPremiumAgent($managerId): array
        // {
        //     $data = [];

        //     try {
        //         // 1. Get the list of Agent IDs who have TOTAL premium < 50,000
        //         $agentIdList = $this->db->table('partner_policy')
        //             ->select('agent_id')
        //             ->where('manager_id', $managerId)
        //             ->groupBy('agent_id')
        //             ->having('SUM(premium_amount) <', 50000)
        //             ->get()
        //             ->getResultArray();

        //         // Extract IDs into a simple array: [1, 5, 10...]
        //         $agentIds = array_column($agentIdList, 'agent_id');

        //         // 2. If no agents found, return empty
        //         if (empty($agentIds)) {
        //             return [];
        //         }

        //         // 3. Fetch detailed records for ONLY these agents
        //         $builder = $this->db->table('partner_policy pp');

        //         $builder->select("
        //             DATE_FORMAT(pe.created_on, '%d-%m-%Y %h:%i %p') AS received_date,
        //             S.name as assigned_to_name,
        //             I.name as insurer_name,
        //             I.short_name as insurer_short_name,
        //             pe.name as insured_name,
        //             pe.reg_no,
        //             pm.value as payment_mode_value,
        //             ipti.insurance_plan_type,
        //             pa.name AS agent_name,
        //             pa.agent_code,
        //             CASE 
        //                 WHEN pa.is_active = 1 THEN 'Active Partner'
        //                 ELSE 'Inactive Partner'
        //             END AS agent_status,
        //             CONCAT('\t', pp.policy_number) AS policy_number,
        //             DATE_FORMAT(pp.issued_date, '%d-%m-%Y') AS issued_date,
        //             /* Indian Rupee Formatting for individual policy premium */
        //             CONCAT('₹ ', FORMAT(pp.premium_amount, 2, 'en_IN')) AS premium_amount,
        //             pp.premium_amount AS raw_premium
        //         ");

        //         $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');
        //         $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
        //         $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
        //         $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
        //         $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
        //         $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
        //         $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');

        //         // WHERE filters
        //         $builder->where('pp.manager_id', $managerId);
        //         $builder->whereIn('pp.agent_id', $agentIds); // <--- This filters by the 104 agents found
        //         $builder->where('pp.premium_amount IS NOT NULL');

        //         $builder->orderBy('pp.issued_date', 'DESC');

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

        //         // Restructure for Excel (array_values removes the string keys)
        //         foreach ($results as $row) {
        //             $data[] = array_values($row);
        //         }
                
        //     } catch (\Exception $e) {
        //         log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
        //         return [];
        //     }

        //     return $data;
        // }

        // First Comment
        // private function getDataLowPremiumAgent($managerId): array
        // {
        //     // --- Correction 1: Initialize Database Connection ---
            
        //     $data = [];

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

        //         $builder->select("
        //             DATE_FORMAT(pe.created_on, '%d-%m-%Y %h:%i %p') AS received_date,
        //             S.name as assigned_to_name,
        //             I.name as insurer_name,I.short_name as insurer_short_name,
        //             pe.name as insured_name,
        //             pe.reg_no,
        //             pm.value as payment_mode_value,
        //             ipti.insurance_plan_type,
        //             pa.name AS agent_name,
        //             pa.agent_code,
        //             CASE 
        //                 WHEN pa.is_active = 1 THEN 'Active Partner'
        //                 ELSE 'Inactive Partner'
        //             END AS agent_status,
        //             CONCAT('\t', pp.policy_number)                  AS policy_number,
        //             DATE_FORMAT(pp.issued_date, '%d-%m-%Y') AS issued_date,
        //             CONCAT('₹ ', FORMAT(pp.premium_amount, 2, 'en_IN')) AS premium_amount,
        //             pp.premium_amount AS raw_premium
        //         ");

        //         $builder->join('partner_agent pa', 'pa.id = pp.agent_id', 'inner');
                                
        //         $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
        //         $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
        //         $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                
        //         $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
        //         $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');
        //         $builder->join('partner_staff S', 'S.id = pe.assigned_to', 'left');

        //         // WHERE conditions
        //         $builder->where('pp.manager_id', $managerId);
        //         // $builder->where('pa.id', $agentId);
        //         $builder->having('raw_premium <', 50000);
        //         $builder->orderBy('pp.issued_date', 'DESC');

        //         $results = $builder->get()->getResultArray();
                
        //         // Restructure data for PhpSpreadsheet (2D array of values)
        //         foreach ($results as $row) {
        //             $data[] = array_values($row);
        //         }
                
        //     } catch (DatabaseException $e) {
        //         log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
        //         // Re-throw the exception so it is caught by the main function's catch block
        //         throw $e; 
        //     }

        //     return $data;
        // }

        private function getDataStaffPendingSummary($managerId): array
        {

            $data = [];

            try {

           $results = $this->db->table('partner_enquiry pe')
                                ->select("
                                    ps.name AS staff_name,
                                    COUNT(pe.id) AS total_assigned,
                                    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,
                                    /* SUM(CASE WHEN pe.enquiry_status != 'Completed'  THEN 1 ELSE 0 END) AS previous_total_pending,
                                    Logic: (All Pending) - (All Assigned Today) */
                                    GREATEST(0, 
                                        SUM(CASE WHEN pe.enquiry_status != 'Completed' THEN 1 ELSE 0 END) - 
                                        SUM(CASE WHEN DATE(pe.created_on) = CURDATE() THEN 1 ELSE 0 END)
                                    ) AS previous_total_pending
                                ")
                                ->join('partner_staff ps', 'ps.id = pe.assigned_to', 'left')
                                ->where('pe.manager_id', $managerId)
                                ->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();

            
                // Restructure data for PhpSpreadsheet (2D array of values)
                foreach ($results as $row) {
                    unset($row['total_assigned']);
                    $data[] = array_values($row);
                }
                
            } catch (\CodeIgniter\Database\Exceptions\DatabaseException $e) {
                log_message('error', 'Database Error during Excel fetch: ' . $e->getMessage());
                // Re-throw the exception so it is caught by the main function's catch block
                throw $e; 
            }

            return $data;
        }

        private function getDataStaffPendingSummaryByID($managerId, $staffId, $status): array
        {
            try {
                $builder = $this->db->table('partner_enquiry pe');
                

                $builder->select('
                    DATE_FORMAT(pe.created_on, "%d-%m-%Y %h:%i %p") AS received_date,
                    ps.name AS staff_name,
                    A.name as agent_name, 
                    A.agent_code,
                    I.name as insurer_name,
                    I.short_name as insurer_short_name,
                    pe.name as insured_name,
                    CONCAT("\t",  pe.reg_no) AS reg_no,
                    pm.value as payment_mode_value,
                    ipti.insurance_plan_type
                ', false); // 'false' prevents CI from automatically escaping quotes in functions like DATE_FORMAT

                $builder->join('partner_policy pp', 'pp.enquiry_id = pe.id', 'left');
                $builder->join('partner_staff ps', 'ps.id = pe.assigned_to', 'left');
                $builder->join('partner_brokers pb', 'pb.id = pe.broker_id', 'left');
                $builder->join('partner_quotation Q', 'Q.enquiry_id = pe.id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = Q.payment_mode_id', 'left');
                $builder->join('partner_agent A', 'A.id = pe.agent_id', 'left');
                $builder->join('partner_insurance_plan_type_master ipti', 'ipti.id = Q.insurance_plan_type_id', 'left');
                $builder->join('insurers I', 'I.id = Q.insurer_id', 'left');

                $builder->where('pe.manager_id', $managerId);
                $builder->where('pe.assigned_to', $staffId);
                $builder->where('pe.is_active', 1);

                // Date boundaries
                $todayStart = date('Y-m-d 00:00:00');
                $todayEnd   = date('Y-m-d 23:59:59');

                switch ($status) {
                    case 'TodayAssigned':
                        $builder->where('pe.created_on >=', $todayStart);
                        $builder->where('pe.created_on <=', $todayEnd);
                        break;
                    case 'Assigned':
                        $builder->where('pe.enquiry_status', 'Assigned');
                        $builder->where('pe.created_on >=', $todayStart);
                        $builder->where('pe.created_on <=', $todayEnd);
                        break;
                    case 'InProgress':
                        $builder->where('pe.enquiry_status', 'In progress');
                        $builder->where('pe.created_on >=', $todayStart);
                        $builder->where('pe.created_on <=', $todayEnd);
                        break;
                    case 'Completed':
                        $builder->where('pe.enquiry_status', 'Completed');
                        $builder->where('pe.created_on >=', $todayStart);
                        $builder->where('pe.created_on <=', $todayEnd);
                        break;

                    case 'PrevPending':
                        /** * To match your Summary Logic (SUM CASE WHEN status != 'Completed'):
                         * We remove the date restriction. 
                         * This shows ALL records that are not completed, regardless of date.
                         */
                        $builder->where('pe.enquiry_status !=', 'Completed');
                        $builder->where('pe.created_on <', $todayStart);
                        break;
                }

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

                $data = [];
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                
                return $data;

            } catch (\Exception $e) {
                log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
                throw $e; 
            }
        }


        private function getDataEndorsement($managerId, $search = '',
                    $agent_id = null,
                    $policy_number = null,
                    $from_date = null,
                    $to_date = null,
                    $endorsement_type = null,
                    $insurer_id = null,
                    $status = null,
                    $verification = null
                ): array
        {
            try {
                $builder = $this->db->table('partner_endorsement_request per');
                $builder->select("
                    DATE_FORMAT(per.created_at, '%d-%m-%Y %h:%i %p') AS received_date,
                    per.policy_from,
                    ps.name AS staff_name,
                    per.policy_number,
                    CASE 
                        WHEN per.policy_start_date = '0000-00-00'
                        OR YEAR(per.policy_start_date) < 2000 
                        THEN NULL 
                        ELSE DATE_FORMAT(per.policy_start_date, '%Y-%m-%d') 
                    END AS policy_start_date,
                    CASE 
                        WHEN per.policy_end_date = '0000-00-00'
                        OR YEAR(per.policy_end_date) < 2000 
                        THEN NULL 
                        ELSE DATE_FORMAT(per.policy_end_date, '%Y-%m-%d') 
                    END AS policy_end_date,
                    per.reg_no,
                    petm.endorsement_type,
                    per.endorsement_no,
                    per.insured_name,
                    CONCAT(I.name, ' - ', I.short_name) AS insurer_name,
                    pb.name AS broker_name,
                    CONCAT(pa.agent_code, ' - ', pa.name) AS agent_details,
                    per.contact_person,
                    per.endorsement_description,
                    per.financia_or_non_financial,
                    per.status,
                    CASE 
                        WHEN per.is_data_accuracy_checked = 0 THEN 'To Verify'
                        ELSE 'Verified'
                    END AS verification_status,
                    CASE 
                        WHEN per.status = 'Closed' THEN '-' 
                        ELSE CONCAT(DATEDIFF(CURDATE(), per.created_at),' days')
                    END AS pending_days,
                    per.endorsement_premium,
                    per.commission_amount,
                ");

                // ✅ Correct joins (NO duplicates)
                $builder->join('partner_policy pp', 'pp.policy_number = per.policy_number', 'left');
                $builder->join('partner_enquiry pe', 'pe.id = pp.enquiry_id', 'left');
                $builder->join('partner_agent pa', 'pa.id = per.agent_id', 'left');
                $builder->join('partner_staff ps', 'ps.id = per.created_by', 'left');
                $builder->join('insurers I', 'I.id = per.insurer_id', 'left');
                $builder->join('partner_endorsement_type_master petm', 'petm.id = per.endorsement_type', 'left');
                $builder->join('partner_brokers pb', 'pb.id = per.broker_id', 'left');
                $builder->join('clients c', 'c.id = per.client_id', 'left');
                $builder->join('partner_payment_mode_master pm', 'pm.id = per.payment_mode_id', 'left');
                $builder->where('per.manager_id', $managerId);
                $builder->where('per.is_active', 1);

                // Filters
                if (!empty($agent_id)) {
                    $builder->where('per.agent_id', $agent_id);
                }

                if (!empty($policy_number)) {
                    $builder->where('per.policy_number', $policy_number);
                }

                if (!empty($from_date) && !empty($to_date)) {
                    $builder->where('per.created_at >=', $from_date . ' 00:00:00');
                    $builder->where('per.created_at <=', $to_date . ' 23:59:59');
                }

                if (!empty($endorsement_type) && $endorsement_type != 'All') {
                    $builder->where('per.endorsement_type', $endorsement_type);
                }

                if (!empty($insurer_id)) {
                    $builder->where('per.insurer_id', $insurer_id);
                }

                if (!empty($status) && $status != 'All') {
                    $builder->where('per.status', $status);
                }

                if (!empty($verification) && $verification != 'All') {
                    if ($verification == 'Verified') {
                        $builder->where('per.is_data_accuracy_checked', 1);
                    } elseif ($verification == 'To Verify') {
                        $builder->where('per.is_data_accuracy_checked', 0);
                    }
                }

                // Search filter
                // if (!empty($search)) {
                //     $search = strtolower(trim($search));

                //     $builder->groupStart()
                //         ->like('LOWER(per.policy_from)', $search)
                //         ->orLike('LOWER(per.status)', $search)
                //         ->orLike('LOWER(per.reg_no)', $search)
                //         ->orLike('LOWER(per.policy_start_date)', $search)
                //         ->orLike('LOWER(per.policy_end_date)', $search)
                //         ->orLike('LOWER(I.name)', $search)
                //         ->orLike('LOWER(I.insurer_short_name)', $search)
                //         ->orLike('LOWER(per.policy_number)', $search)
                //         ->orLike('LOWER(petm.endorsement_type)', $search)
                //         ->orLike('LOWER(per.endorsement_no)', $search)
                //     ->groupEnd();
                // }
                if (!empty($search)) {
                        $search = strtolower(trim($search));

                        $builder->groupStart()

                            // Normal columns
                            ->like('LOWER(per.policy_from)', $search)
                            ->orLike('LOWER(per.status)', $search)
                            ->orLike('LOWER(per.reg_no)', $search)
                            ->orLike('LOWER(per.policy_number)', $search)
                            ->orLike('LOWER(per.endorsement_no)', $search)

                            // Insurer
                            ->orLike('LOWER(I.name)', $search)
                            ->orLike('LOWER(I.short_name)', $search)

                            // Endorsement type
                            ->orLike('LOWER(petm.endorsement_type)', $search)

                            // ✅ Broker name
                            ->orLike('LOWER(pb.name)', $search)

                            // ✅ Agent code + name (CONCAT)
                            ->orLike("LOWER(CONCAT(pa.agent_code, ' - ', pa.name))", $search)
                            ->orLike("LOWER(pa.agent_code)", $search)
                            ->orLike("LOWER(pa.name)", $search)

                            // ✅ Verification (CASE condition)
                            ->orLike("
                                LOWER(
                                    CASE 
                                        WHEN per.is_data_accuracy_checked = 0 THEN 'to verify'
                                        ELSE 'verified'
                                    END
                                )", $search)

                            // ✅ Received date (formatted)
                            ->orLike("DATE_FORMAT(per.created_at, '%d-%m-%Y %h:%i %p')", $search)

                            // ✅ Policy dates (formatted)
                            ->orLike("DATE_FORMAT(per.policy_start_date, '%Y-%m-%d')", $search)
                            ->orLike("DATE_FORMAT(per.policy_end_date, '%Y-%m-%d')", $search)

                        ->groupEnd();
                    }

               
                        $builder->groupBy('per.id');

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

                // Convert associative rows to indexed rows (Excel-friendly)
                $data = [];
                foreach ($results as $row) {
                    $data[] = array_values($row);
                }
                // foreach ($results as $row) {
                //     foreach ($row as $key => $value) {
                //         if ($value === null || $value === '') {
                //             $row[$key] = '-';
                //         }
                //     }
                //     $data[] = array_values($row);
                // }


                return $data;

            } catch (\Exception $e) {
                log_message('error', 'Excel Fetch Error: ' . $e->getMessage());
                throw $e;
            }
        }

    // grid/download?role=Manager means all list (optional insurer, rto, segment, vehicle_type, search, logged_id)
    public function downloadExcelGrid()
    {
        try {
            $request = $this->request;

            // ✅ Inputs
            $role        = trim((string) ($request->getGet('role') ?? ''));
            $fileId      = trim((string) ($request->getGet('file_id') ?? ''));
            $insurer     = trim((string) ($request->getGet('insurer') ?? ''));
            $rto         = trim((string) ($request->getGet('rto') ?? ''));
            $segment     = trim((string) ($request->getGet('segment') ?? ''));
            $vehicleType = trim((string) ($request->getGet('vehicle_type') ?? ''));
            $search      = trim((string) ($request->getGet('search') ?? ''));
            $loggedId    = trim((string) ($request->getGet('logged_id') ?? ''));

            if ($role === '') {
                return $this->response->setJSON([
                    'status' => 'failed',
                    'code'   => 400,
                    'data'   => 'Role is required to export payout grid.',
                ]);
            }

            $isAgent = strtolower($role) === 'agent';

            // ✅ partner_VT / partner_RR + Agent comp/tp/od = partner_RR − original (Agent only)
            $metaMap   = [];
            $masterVt  = [];
            if ($isAgent && $loggedId !== '') {
                $agentPk  = (int) $loggedId;
                $metaMap  = PartnerPayoutGridRetention::buildPartnerMetaMap($this->db, $agentPk);
                $masterVt = PartnerPayoutGridRetention::buildMasterNormLabelToVehicleTypeIdMap($this->db);
            }

            // ✅ Main query
            $builder = $this->db->table('partner_insurance_payout_grid');

            if ($fileId !== '') {
                $builder->where('partner_agent_incentive_file_id', $fileId);
            } else {
                $maxFileId = $this->db->table('partner_insurance_payout_grid')
                    ->selectMax('partner_agent_incentive_file_id')
                    ->get()
                    ->getRow()
                    ->partner_agent_incentive_file_id ?? null;

                if (!empty($maxFileId)) {
                    $builder->where('partner_agent_incentive_file_id', $maxFileId);
                }
            }

            // ✅ Filters
            if ($insurer !== '')     $builder->where('insurer', $insurer);
            if ($rto !== '')         $builder->where('rto', $rto);
            if ($segment !== '')     $builder->where('segment', $segment);
            if ($vehicleType !== '') $builder->where('vehicle_type', $vehicleType);

            // ✅ Search
            if ($search !== '') {
                $builder->groupStart()
                    ->like('insurer', $search)
                    ->orLike('vehicle_type', $search)
                    ->orLike('segment', $search)
                    ->orLike('rto', $search)
                    ->orLike('remarks', $search)
                    ->groupEnd();
            }

            // ✅ Fetch rows
            $rows = $builder
                ->select('id, insurer, vehicle_type, segment, rto, comp, tp, od, remarks')
                ->orderBy('id', 'DESC')
                ->get()
                ->getResultArray();

            // =========================
            // ✅ HEADERS
            // =========================
            $headers = ['S.No', 'Insurer', 'Vehicle Type', 'Segment' , 'RTO', 'Comp', 'TP' , 'OD', 'Remarks'];

            // =========================
            // ✅ DATA
            // =========================
            $data = [];
            $sNo = 1;

            foreach ($rows as $row) {

                $compRaw = $row['comp'] ?? null;
                $tpRaw   = $row['tp'] ?? null;
                $odRaw   = $row['od'] ?? null;

                $compOut = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($compRaw);
                $tpOut   = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($tpRaw);
                $odOut   = PartnerPayoutGridRetention::gridPayoutDisplayManagerAccounts($odRaw);

                $pm = null;
                // ✅ Agent + logged_id: PRR match + per-column calc only when original value is positive
                if ($isAgent && $loggedId !== '') {
                    $pm = PartnerPayoutGridRetention::resolvePartnerMetaForGridRow($row, $metaMap, $masterVt);
                    if ($pm === null) {
                        $compOut = '-';
                        $tpOut   = '-';
                        $odOut   = '-';
                    } else {
                        $rr      = (float) $pm['partner_RR'];
                        $compOut = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($compRaw, $rr);
                        $tpOut   = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($tpRaw, $rr);
                        $odOut   = PartnerPayoutGridRetention::agentPayoutColumnFromPartnerRr($odRaw, $rr);
                    }
                }

                $line = [
                    $sNo++,
                    $row['insurer'] ?? '-',
                    $row['vehicle_type'] ?? '-',
                    $row['segment'] ?? '-',
                    $row['rto'] ?? '-',
                ];

                $line[] = $compOut;
                $line[] = $tpOut;
                $line[] = $odOut;
                $line[] = $row['remarks'] ?? '-';

                $data[] = $line;
            }

            // ✅ Export
            $fileName = "Payout_" . date('Ymd_His') . ".xlsx";

            return $this->streamExcelFile($headers, $data, 'Payout', $fileName, false);

        } catch (\Throwable $e) {
            return $this->response->setJSON([
                'status' => 'failed',
                'code'   => 500,
                'data'   => $e->getMessage(),
            ]);
        }
    }

    public function exportAgentRetentionRateExcel()
    {
        try {
            $vehicleTypes = $this->db->table('vehicle_type')
                ->select('id, vehicle_type')
                ->where('is_active', 1)
                ->orderBy('vehicle_type', 'ASC')
                ->get()
                ->getResultArray();

            $segments = $this->db->table('partner_segment ps')
                ->select('ps.id, ps.segment, ps.vehicle_type_id, vt.vehicle_type')
                ->join('vehicle_type vt', 'vt.id = ps.vehicle_type_id', 'inner')
                ->where('ps.is_active', 1)
                ->where('vt.is_active', 1)
                ->orderBy('vt.vehicle_type', 'ASC')
                ->orderBy('ps.vehicle_type_id', 'ASC')
                ->orderBy('ps.id', 'ASC')
                ->orderBy('ps.segment', 'ASC')
                ->get()
                ->getResultArray();

            $agents = $this->db->table('partner_agent')
                ->select('id, agent_code')
                ->where('is_active', 1)
                ->where('agent_code IS NOT NULL', null, false)
                ->where('agent_code !=', '')
                ->orderBy('agent_code', 'ASC')
                ->get()
                ->getResultArray();

            $rates = $this->db->table('partner_retention_rate')
                ->select('agent_id, vehicle_type_id, segment_id, retention_rate')
                ->where('is_active', 1)
                ->get()
                ->getResultArray();

            $rateMap = [];
            foreach ($rates as $r) {
                $aId = (int) ($r['agent_id'] ?? 0);
                $vId = (int) ($r['vehicle_type_id'] ?? 0);
                $sId = (int) ($r['segment_id'] ?? 0);
                if ($aId <= 0 || $vId <= 0 || $sId <= 0) {
                    continue;
                }
                $rateMap[$aId . '_' . $vId . '_' . $sId] = (float) ($r['retention_rate'] ?? 0);
            }

            $segmentRows = [];
            foreach ($segments as $seg) {
                $vId = (int) ($seg['vehicle_type_id'] ?? 0);
                $sId = (int) ($seg['id'] ?? 0);
                $vehicleTypeName = (string) ($seg['vehicle_type'] ?? '');
                if ($vId <= 0 || $sId <= 0 || $vehicleTypeName === '') {
                    continue;
                }
                $segmentRows[] = [
                    'vehicle_type_id' => $vId,
                    'vehicle_type'    => $vehicleTypeName,
                    'segment_id'      => $sId,
                    'segment'         => (string) ($seg['segment'] ?? ''),
                ];
            }

            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            $sheet->setCellValue('A1', 'Vehicle Type');
            $sheet->setCellValue('B1', 'Segment');

            $colIndex = 3;
            foreach ($agents as $agent) {
                $cell = Coordinate::stringFromColumnIndex($colIndex++) . '1';
                $sheet->setCellValue($cell, (string) ($agent['agent_code'] ?? ''));
            }

            $rowIndex = 2;
            foreach ($segmentRows as $sr) {
                $vehicleTypeId = (int) $sr['vehicle_type_id'];
                $segmentId = (int) $sr['segment_id'];
                $sheet->setCellValue('A' . $rowIndex, (string) $sr['vehicle_type']);
                $sheet->setCellValue('B' . $rowIndex, (string) $sr['segment']);

                $colIndex = 3;
                foreach ($agents as $agent) {
                    $agentId = (int) ($agent['id'] ?? 0);
                    $key = $agentId . '_' . $vehicleTypeId . '_' . $segmentId;
                    $cell = Coordinate::stringFromColumnIndex($colIndex++) . $rowIndex;
                    $sheet->setCellValue($cell, (float) ($rateMap[$key] ?? 0));
                }
                $rowIndex++;
            }

            $lastCol = Coordinate::stringFromColumnIndex(max(2, count($agents) + 2));
            $lastRow = max(1, $rowIndex - 1);
            $sheet->getStyle("A1:{$lastCol}1")->getFont()->setBold(true);
            $sheet->getStyle("A1:{$lastCol}{$lastRow}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);

            // Input validation for retention cells:
            // - Applies to C2:lastCol(lastRow)
            // - Allows decimal value between 0 and 100 only
            // - Blocks negatives, values > 100, and text/special characters
            if (count($agents) > 0 && $lastRow >= 2) {
                $validation = $sheet->getCell('C2')->getDataValidation();
                $validation->setType(DataValidation::TYPE_DECIMAL);
                $validation->setErrorStyle(DataValidation::STYLE_STOP);
                $validation->setAllowBlank(true);
                $validation->setShowInputMessage(true);
                $validation->setShowErrorMessage(true);
                $validation->setOperator(DataValidation::OPERATOR_BETWEEN);
                $validation->setFormula1('0');
                $validation->setFormula2('100');
                $validation->setPromptTitle('Valid retention rate');
                $validation->setPrompt('Enter a number between 0 and 100');
                $validation->setErrorTitle('Invalid value');
                $validation->setError('Only numeric values from 0 to 100 are allowed.');
                for ($row = 2; $row <= $lastRow; $row++) {
                    for ($col = 3; $col <= (count($agents) + 2); $col++) {
                        $cell = Coordinate::stringFromColumnIndex($col) . $row;
                        $sheet->getCell($cell)->setDataValidation(clone $validation);
                    }
                }
            }

            for ($i = 1; $i <= count($agents) + 2; $i++) {
                $col = Coordinate::stringFromColumnIndex($i);
                $sheet->getColumnDimension($col)->setAutoSize(true);
            }

            $fileName = 'RentationRate_' . date('Ymd_His') . '.xlsx';
            while (ob_get_level() > 0) {
                ob_end_clean();
            }
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . $fileName . '"');
            header('Cache-Control: max-age=0');
            $writer = new Xlsx($spreadsheet);
            $writer->save('php://output');
            exit();
        } catch (\Throwable $e) {
            return $this->response->setJSON([
                'status' => 'failed',
                'code'   => 500,
                'data'   => $e->getMessage(),
            ]);
        }
    }

    public function importAgentRetentionRateExcel()
    {
        try {
            $file = $this->request->getFile('retention_excel');
            if (!$file || !$file->isValid()) {
                $file = $this->request->getFile('retention_rate_excel');
            }
            if (!$file || !$file->isValid()) {
                return $this->respond([
                    'status' => 'failed',
                    'code'   => 200,
                    'data'   => 'Valid Excel file is required in retention_excel / retention_rate_excel',
                ], 200);
            }

            $spreadsheet = IOFactory::load($file->getTempName());
            $sheet = $spreadsheet->getActiveSheet();
            $rows = $sheet->toArray(null, true, true, false);

            if (empty($rows) || empty($rows[0])) {
                return $this->respond([
                    'status' => 'failed',
                    'code'   => 200,
                    'data'   => 'Excel file is empty',
                ], 200);
            }

            $headerRow = $rows[0];
            $headerAgents = [];
            for ($col = 2; $col < count($headerRow); $col++) {
                $name = trim((string) ($headerRow[$col] ?? ''));
                if ($name === '') {
                    continue;
                }
                $headerAgents[$col] = strtolower($name);
            }

            if (empty($headerAgents)) {
                return $this->respond([
                    'status' => 'failed',
                    'code'   => 200,
                    'data'   => 'Agent code headers are missing in row 1',
                ], 200);
            }

            $vehicleTypeRows = $this->db->table('vehicle_type')
                ->select('id, vehicle_type')
                ->where('is_active', 1)
                ->get()
                ->getResultArray();
            $vehicleTypeMap = [];
            foreach ($vehicleTypeRows as $vt) {
                $key = strtolower(trim((string) ($vt['vehicle_type'] ?? '')));
                if ($key !== '') {
                    $vehicleTypeMap[$key] = (int) $vt['id'];
                }
            }

            $segmentRows = $this->db->table('partner_segment')
                ->select('id, segment, vehicle_type_id')
                ->where('is_active', 1)
                ->get()
                ->getResultArray();
            $segmentMap = [];
            foreach ($segmentRows as $seg) {
                $vId = (int) ($seg['vehicle_type_id'] ?? 0);
                $segName = strtolower(trim((string) ($seg['segment'] ?? '')));
                if ($vId > 0 && $segName !== '') {
                    $segmentMap[$vId . '_' . $segName] = (int) $seg['id'];
                }
            }

            $agentRows = $this->db->table('partner_agent')
                ->select('id, agent_code')
                ->where('is_active', 1)
                ->where('agent_code IS NOT NULL', null, false)
                ->where('agent_code !=', '')
                ->get()
                ->getResultArray();
            $agentMap = [];
            foreach ($agentRows as $agent) {
                $key = strtolower(trim((string) ($agent['agent_code'] ?? '')));
                if ($key !== '') {
                    $agentMap[$key] = (int) $agent['id'];
                }
            }

            $unknownAgentCodes = [];
            $validHeaderAgents = [];
            foreach ($headerAgents as $colIndex => $agentCode) {
                if (isset($agentMap[$agentCode])) {
                    $validHeaderAgents[$colIndex] = $agentMap[$agentCode];
                } else {
                    $unknownAgentCodes[] = trim((string) ($headerRow[$colIndex] ?? ''));
                }
            }

            if (empty($validHeaderAgents)) {
                return $this->respond([
                    'status' => 'failed',
                    'code'   => 200,
                    'data'   => 'No valid agent code headers found in DB',
                    'report' => [
                        'unknown_agent_codes' => array_values(array_unique($unknownAgentCodes)),
                    ],
                ], 200);
            }

            $existingRows = $this->db->table('partner_retention_rate')
                ->select('id, agent_id, vehicle_type_id, segment_id, retention_rate')
                ->get()
                ->getResultArray();

            $existingMap = [];
            foreach ($existingRows as $er) {
                $key = ((int) $er['agent_id']) . '_' . ((int) $er['vehicle_type_id']) . '_' . ((int) ($er['segment_id'] ?? 0));
                $existingMap[$key] = [
                    'id'   => (int) $er['id'],
                    'rate' => (float) ($er['retention_rate'] ?? 0),
                ];
            }

            $now = date('Y-m-d H:i:s');
            $inserted = 0;
            $updated = 0;
            $skippedZero = 0;
            $skippedSame = 0;
            $skippedEmptyRow = 0;
            $skippedEmptyCell = 0;
            $skippedUnknownVehicleType = 0;
            $skippedUnknownSegment = 0;
            $invalidRange = 0;
            $invalidNumber = 0;
            $unknownVehicleTypes = [];
            $unknownSegments = [];
            $invalidCells = [];
            $agentWiseSkippedCounts = []; // keyed by row reference "vehicle_type | segment"

            $this->db->transStart();

            for ($r = 1; $r < count($rows); $r++) {
                $row = $rows[$r];
                $vehicleTypeNameRaw = trim((string) ($row[0] ?? ''));
                $segmentNameRaw = trim((string) ($row[1] ?? ''));
                if ($vehicleTypeNameRaw === '' && $segmentNameRaw === '') {
                    $skippedEmptyRow++;
                    continue;
                }

                $vehicleTypeKey = strtolower($vehicleTypeNameRaw);
                if (!isset($vehicleTypeMap[$vehicleTypeKey])) {
                    $skippedUnknownVehicleType++;
                    if ($vehicleTypeNameRaw !== '') {
                        $unknownVehicleTypes[] = $vehicleTypeNameRaw;
                    }
                    continue;
                }

                $vehicleTypeId = (int) $vehicleTypeMap[$vehicleTypeKey];
                $segmentKey = strtolower($segmentNameRaw);
                $segmentMapKey = $vehicleTypeId . '_' . $segmentKey;
                if ($segmentNameRaw === '' || !isset($segmentMap[$segmentMapKey])) {
                    $skippedUnknownSegment++;
                    if ($segmentNameRaw !== '') {
                        $unknownSegments[] = $segmentNameRaw;
                    }
                    continue;
                }
                $segmentId = (int) $segmentMap[$segmentMapKey];

                $rowRef = trim($vehicleTypeNameRaw . ' | ' . $segmentNameRaw);
                if ($rowRef !== '' && !isset($agentWiseSkippedCounts[$rowRef])) {
                    $agentWiseSkippedCounts[$rowRef] = 0;
                }

                foreach ($validHeaderAgents as $colIndex => $agentId) {
                    $rawRate = $row[$colIndex] ?? null;
                    $rawText = trim((string) $rawRate);

                    if ($rawText === '') {
                        $skippedEmptyCell++;
                        if ($rowRef !== '') {
                            $agentWiseSkippedCounts[$rowRef]++;
                        }
                        continue;
                    }

                    if (!is_numeric($rawText)) {
                        $invalidNumber++;
                        if ($rowRef !== '') {
                            $agentWiseSkippedCounts[$rowRef]++;
                        }
                        if (count($invalidCells) < 20) {
                            $invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " invalid number";
                        }
                        continue;
                    }

                    $rate = (float) $rawText;
                    if (!is_finite($rate)) {
                        $invalidNumber++;
                        if ($rowRef !== '') {
                            $agentWiseSkippedCounts[$rowRef]++;
                        }
                        if (count($invalidCells) < 20) {
                            $invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " invalid number";
                        }
                        continue;
                    }

                    if ($rate < 0 || $rate > 100) {
                        $invalidRange++;
                        if ($rowRef !== '') {
                            $agentWiseSkippedCounts[$rowRef]++;
                        }
                        if (count($invalidCells) < 20) {
                            $invalidCells[] = "R" . ($r + 1) . "C" . ($colIndex + 1) . " out of range (0-100)";
                        }
                        continue;
                    }

                    // Performance optimization:
                    // Skip zero values to avoid unnecessary DB lookup/write.
                    if (round($rate, 2) == 0.00) {
                        $skippedZero++;
                        if ($rowRef !== '') {
                            $agentWiseSkippedCounts[$rowRef]++;
                        }
                        continue;
                    }

                    $key = $agentId . '_' . $vehicleTypeId . '_' . $segmentId;
                    $existing = $existingMap[$key] ?? null;

                    if ($existing) {
                        if (round((float) $existing['rate'], 2) === round((float) $rate, 2)) {
                            $skippedSame++;
                            if ($rowRef !== '') {
                                $agentWiseSkippedCounts[$rowRef]++;
                            }
                            continue;
                        }
                        $this->db->table('partner_retention_rate')
                            ->where('id', (int) $existing['id'])
                            ->update([
                                'retention_rate' => round($rate, 2),
                                'is_active'      => 1,
                                'updated_on'     => $now,
                            ]);
                        $existingMap[$key]['rate'] = round($rate, 2);
                        $updated++;
                    } else {
                        $this->db->table('partner_retention_rate')
                            ->insert([
                                'agent_id'        => $agentId,
                                'vehicle_type_id' => $vehicleTypeId,
                                'segment_id'      => $segmentId,
                                'retention_rate'  => round($rate, 2),
                                'is_active'       => 1,
                                'created_on'      => $now,
                            ]);
                        $existingMap[$key] = [
                            'id'   => (int) $this->db->insertID(),
                            'rate' => round($rate, 2),
                        ];
                        $inserted++;
                    }
                }
            }

            $this->db->transComplete();
            if ($this->db->transStatus() === false) {
                throw new \RuntimeException('Import transaction failed');
            }

            return $this->respond([
                'status' => 'success',
                'code'   => 200,
                'data'   => [
                    'inserted_rows'         => $inserted,
                    'updated_rows'          => $updated,
                    'inserted_cells'        => $inserted,
                    'updated_cells'         => $updated,
                    'skipped_zero'          => $skippedZero,
                    'skipped_same'          => $skippedSame,
                    'skipped_empty_row'     => $skippedEmptyRow,
                    'skipped_empty_cell'    => $skippedEmptyCell,
                    'skipped_unknown_vehicle_type' => $skippedUnknownVehicleType,
                    'skipped_unknown_segment'      => $skippedUnknownSegment,
                    'invalid_range'         => $invalidRange,
                    'invalid_number'        => $invalidNumber,
                ],
                'report' => [
                    'unknown_agent_codes'  => array_values(array_unique($unknownAgentCodes)),
                    'unknown_vehicle_types'=> array_values(array_unique($unknownVehicleTypes)),
                    'unknown_segments'     => array_values(array_unique($unknownSegments)),
                    'invalid_cells'        => $invalidCells,
                    'agent_wise_skipped_counts' => $agentWiseSkippedCounts,
                ],
                'message' => 'Retention rate import completed',
            ], 200);
        } catch (\Throwable $e) {
            return $this->respond([
                'status' => 'failed',
                'code'   => 500,
                'data'   => $e->getMessage(),
            ], 500);
        }
    }

    

}
