<?php

namespace App\Controllers;

use CodeIgniter\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use DateTime;

class PolicyReportController extends Controller
{
    public function downloadExcel()
    {
        $db = \Config\Database::connect();

        // -----------------------------
        // REQUEST PARAMS
        // -----------------------------
        $managerId = $this->request->getGet('manager_id');
        $fromDate  = $this->request->getGet('from_date');
        $toDate    = $this->request->getGet('to_date');
        $fromDate = DateTime::createFromFormat('d-m-Y', $fromDate)->format('Y-m-d 00:00:00');
        $toDate   = DateTime::createFromFormat('d-m-Y', $toDate)->format('Y-m-d 23:59:59');
        $show_policy_report = $this->request->getGet('show_policy_report') ?? 'All';
        $insurer_id = $this->request->getGet('insurer_id') ?? '';

        if ($show_policy_report === 'Verified') {
            $statusValues = [1];
        } elseif ($show_policy_report === 'Not Verified') {
            $statusValues = [0];
        } else {
            $statusValues = [0, 1];
        }



        if (!$managerId || !$fromDate || !$toDate) {
            return $this->response->setJSON([
                'status'  => 'failed',
                'message' => 'manager_id, from_date and to_date are required'
            ]);
        }

        // -----------------------------
        // QUERY (100% Excel-aligned)  // Old Query Don't Delete It. Any Doubt ask MG Bhai
        // -----------------------------
        // $data = $db->query("
        //     SELECT
        //         pos.pos_code                                   AS pos_code,
        //         pos.name                                       AS pos_name,
        //         pa.agent_code                                  AS ref_code,
        //         pp.created_on                                  AS inward_date,
        //         pa.name                                        AS partner_name,
        //         ins.short_name                                 AS insurer,
        //         pp.policy_number,
        //         pp.insured_name,
        //         pq.insurance_plan_type_id                      AS plan_type,
        //         pp.vehicle_type                                AS product,

        //         pp.od                                          AS od,
        //         pp.tp                                          AS tp,
        //         (IFNULL(pp.od,0) + IFNULL(pp.tp,0))            AS net_amount,

        //         (IFNULL(pp.cgst,0) + IFNULL(pp.sgst,0) + IFNULL(pp.igst,0)) AS service_tax,

        //         (IFNULL(pp.od,0) + IFNULL(pp.tp,0)
        //          + IFNULL(pp.cgst,0) + IFNULL(pp.sgst,0) + IFNULL(pp.igst,0)
        //         ) AS total_amount,

        //         pm.value                                      AS payment_mode,
        //         CONCAT(pp.rto_state_code,'-',pp.rto_city_code) AS rto_loc,
        //         NULL                                          AS ncb_percent,
        //         pp.end_date                                   AS expire_date,

        //         pb.short_name                                 AS broker_code,
        //         ps.name                                       AS assigned_to,
        //         pp.manager_id                                 AS sales_manager,
        //         pa.name                                       AS sales_executive,
        //         pa.mobile                                     AS contact_no,
        //         pos.address,
        //         pos.pincode,

        //         pe.status                                     AS booking_status,
        //         pe.created_on                                 AS booking_date,
        //         pe.id                                         AS booking_ref_no,

        //         pp.agent_retention_rate                       AS grid_percent,
        //         pp.commission_amount                          AS grid_amount,
        //         pe.remarks                                    AS remark
        //     FROM partner_policy pp
        //     LEFT JOIN partner_agent pa ON pa.id = pp.agent_id
        //     LEFT JOIN partner_pos pos ON pos.id = pa.pos_id
        //     LEFT JOIN partner_quotation pq ON pq.id = pp.quotation_id
        //     LEFT JOIN insurers ins ON ins.id = pq.insurer_id
        //     LEFT JOIN partner_payment_mode_master pm ON pm.id = pq.payment_mode_id
        //     LEFT JOIN partner_enquiry pe ON pe.id = pp.enquiry_id
        //     LEFT JOIN partner_staff ps ON ps.id = pe.assigned_to
        //     LEFT JOIN partner_brokers pb ON pb.id = pe.broker_id
        //     WHERE pp.manager_id = ?
        //       AND pp.issued_date BETWEEN ? AND ?
        //     ORDER BY pp.created_on DESC
        // ", [$managerId, $fromDate, $toDate])->getResultArray();

        // New Query 
        // This query is copied from enquiryList() in EnquiryController
        // to  refer this API URI = enquiry/enquiryList?manager_id=1&only_policy_data=true&from_date=18-12-2025&to_date=02-01-2026&staff_id=

        $sql = "SELECT 
                            pos.pos_code                                   AS pos_code,
                            pos.name                                       AS pos_name,
                            pa.agent_code                                  AS ref_code,
                            CASE 
                                WHEN pp.created_on IS NULL 
                                    OR pp.created_on = '0000-00-00 00:00:00'
                                THEN 'N/A'
                                ELSE DATE_FORMAT(pp.created_on, '%d-%m-%Y %h:%i %p')
                            END AS inward_date,
                            CASE 
                                WHEN pp.issued_date IS NULL 
                                    OR pp.issued_date = '0000-00-00 00:00:00'
                                THEN 'N/A'
                                ELSE DATE_FORMAT(pp.issued_date, '%d-%m-%Y %h:%i %p')
                            END AS issued_date,
                            pa.name                                        AS partner_name,
                            ins.short_name                                 AS insurer, 
                            CONCAT('`', pp.policy_number)                 AS policy_number,
                            pp.insured_name,
                            iptm.insurance_plan_type                       AS plan_type,
                            pp.vehicle_type                                AS product,
                            pp.product                                     AS product_type,
                            CASE 
                                WHEN pp.od IS NULL OR pp.od = '' THEN 0
                                ELSE pp.od
                            END AS od,
                            CASE 
                                WHEN pp.tp IS NULL OR pp.tp = '' THEN 0
                                ELSE pp.tp
                            END AS tp,
                            (IFNULL(pp.od,0) + IFNULL(pp.tp,0))            AS net_amount,
                            (IFNULL(pp.cgst,0) + IFNULL(pp.sgst,0) + IFNULL(pp.igst,0)) 
                                                                           AS service_tax,
                            (IFNULL(pp.od,0) + IFNULL(pp.tp,0) + IFNULL(pp.cgst,0) + IFNULL(pp.sgst,0) + IFNULL(pp.igst,0)) 
                                                                           AS total_amount,
                            pm.value                                       AS payment_mode,
                            CONCAT(pp.rto_state_code,'-',pp.rto_city_code) AS rto_loc,
                            CASE 
                                WHEN pp.end_date IS NULL 
                                    OR pp.end_date = '0000-00-00 00:00:00'
                                THEN 'N/A'
                                ELSE DATE_FORMAT(pp.end_date, '%d-%m-%Y %h:%i %p')
                            END AS expire_date,
                            NULL                                           AS ncb_percent,
                            pb.short_name                                  AS broker_code,
                            ps.name                                        AS assigned_to,
                            ps2.name                                       AS sales_manager,
                            pse.name                                       AS sales_executive,
                            pse.mobile                                     AS contact_no,
                            pos.address,
                            pos.pincode,
                            pe.status                                      AS booking_status,
                            pe.id                                          AS enquiry_id,
                            pe.reg_no,
                            CASE 
                                WHEN pe.created_on IS NULL 
                                    OR pe.created_on = '0000-00-00 00:00:00'
                                THEN 'N/A'
                                ELSE DATE_FORMAT(pe.created_on, '%d-%m-%Y %h:%i %p')
                            END AS booking_date,
                            pe.id                                          AS booking_ref_no,
                            pp.agent_retention_rate                        AS grid_percent,
                            pp.commission_amount                           AS grid_amount,
                            pp.year_of_manufacture,
                            pp.weight                                      AS vehicle_weight,
                            pe.remarks                                     AS remark,
                            pp.fuel_type,
                            v.vehicle_no,
                            CASE 
                                WHEN pp.date_of_registration IS NULL 
                                    OR pp.date_of_registration = '0000-00-00'
                                THEN 'N/A'
                                ELSE DATE_FORMAT(pp.date_of_registration, '%d-%m-%Y')
                            END AS date_of_registration,
                            pp.make,
                            pp.model
                FROM partner_enquiry pe
                LEFT JOIN partner_agent pa ON pa.id = pe.agent_id
                LEFT JOIN partner_staff ps ON ps.id = pe.assigned_to
                LEFT JOIN partner_quotation pq ON pq.enquiry_id = pe.id AND pq.status = 'Accepted'
                LEFT JOIN insurers ins ON ins.id = pe.insurer_id
                LEFT JOIN partner_policy pp ON pp.quotation_id = pq.id
                LEFT JOIN partner_payment_mode_master pm ON pm.id = pq.payment_mode_id
                LEFT JOIN partner_pos pos ON pos.id = pa.pos_id
                LEFT JOIN partner_sales_executive pse ON pse.id = pa.sales_executive_id
                LEFT JOIN partner_staff ps2 ON ps2.id = pse.manager_id
                LEFT JOIN partner_brokers pb ON pb.id = pe.broker_id
                LEFT JOIN vehicle v ON v.id = pp.vehicle_id
                LEFT JOIN partner_insurance_plan_type_master iptm ON pq.insurance_plan_type_id = iptm.id
                WHERE pe.is_active = 1
                AND pe.is_quick_quote = 0
                AND pp.id IS NOT NULL
                AND pp.created_on BETWEEN ? AND ?
                AND pe.manager_id = ?";

            $bindings = [$fromDate, $toDate, $managerId ];

            // Add insurer filter only if it's not empty
            if (!empty($insurer_id)) {
                $sql .= " AND ins.id = ?";
                $bindings[] = $insurer_id;
            }

            $whereIn = implode(',', array_fill(0, count($statusValues), '?'));

            if (!empty($statusValues)) {
                $sql .= " AND pp.is_data_accuracy_checked IN ($whereIn)";
                $bindings = array_merge($bindings, $statusValues);
            }

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

            if (!empty($search)) {

                $searchTerm = '%' . strtolower(trim($search)) . '%';

                $sql .= " AND (
                    LOWER(ps.name) LIKE ?
                    OR LOWER(pa.name) LIKE ?
                    OR LOWER(ins.short_name) LIKE ?
                    OR LOWER(pe.reg_no) LIKE ?
                    OR LOWER(pp.insured_name) LIKE ?
                    OR CAST(pp.premium_amount AS CHAR) LIKE ?
                    OR LOWER(pm.value) LIKE ?
                    OR LOWER(pp.policy_number) LIKE ?
                )";

                // Bind search term 8 times
                for ($i = 0; $i < 8; $i++) {
                    $bindings[] = $searchTerm;
                }

                // YES / NO search support
                if (strtolower($search) === 'yes') {
                    $sql .= " OR pp.is_bds_pushed = 1";
                } elseif (strtolower($search) === 'no') {
                    $sql .= " OR pp.is_bds_pushed = 0";
                }
            }

            // ORDER BY must be LAST
            $sql .= " ORDER BY pe.created_on DESC";

            $query = $db->query($sql, $bindings);

            $data = $query->getResultArray();

            // dd($whereIn, $bindings,$query,$data);

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

        // enquiry_id,reg_no,year_of_manufacture

        // EXACT HEADER FROM YOUR EXCEL
        // $headers = [
        //     'ENQUIRY ID','POS CODE','POS NAME','REFF CODE','PARTNER NAME','INWARD DATE',
        //     'INSURER','POLICY NO','REG NO','ISSURED NAME','PLAN TYPE','PRODUCT','PRODUCT TYPE','YEAR OF MFG','WEIGHT',
        //     'A (OD)','B (TP)','A+B (NET)','S.TAX','TOTAL',
        //     'PAYMENT MODE','RTO LOC.','NCB %','Expire Date','BROKER CODE',
        //     'ASSIDNED TO','SALES MANAGER','SALES EXECUTIVE','CONTACT NO',
        //     'ADDRESS','PIN CODE','BOOKING STATUS','DATE OF BOOKING',
        //     'BOOKING REF NO','GRID %','GRID AMT','REMARK'
        // ];

        $headers = [
            'PARTNER CODE',
            'PARTNER NAME',
            'BOOKING REF NO',
            'INSURED DATE',
            'INSURER',
            'PLAN TYPE',
            'POLICY NO',
            'INSURED NAME',
            'REG NO',
            'PRODUCT',
            'PRODUCT TYPE',
            'FUEL TYPE',
            'GVW',
            'YEAR OF MFG',
            'RTO LOC',
            'DATE OF REGISTRATION',
            'COMPANY',
            'MAKE & VARIANT',
            'A (OD)',
            'B (TP)',
            'A+B (NET)',
            'S.TAX (CGST/IGST)',
            'TOTAL',
            'PAYMENT MODE',
            'EXPIRY DATE',
            'NCB %',
            'BROKER CODE',
            'ASSIGNED TO',
            // 'SALES MANAGER',
            'SALES EXECUTIVE',
            'CONTACT NO',
            'ADDRESS',
            'PIN CODE',
            'BOOKING STATUS',
            'DATE OF BOOKING',
            'REMARK'
        ];

        // Header row
        $col = 'A';
        foreach ($headers as $header) {
            $sheet->setCellValue($col.'1', $header);
            $sheet->getColumnDimension($col)->setAutoSize(true);
            $col++;
        }

        // Data rows
        $row = 2;
        foreach ($data as $d) {
            $sheet->fromArray([
                $d['ref_code'],
                $d['partner_name'],
                $d['booking_ref_no'],
                $d['issued_date'],
                $d['insurer'],
                $d['plan_type'],
                $d['policy_number'],
                $d['insured_name'],
                $d['reg_no'],
                $d['product'],
                $d['product_type'],
                $d['fuel_type'], // 
                $d['vehicle_weight'],
                $d['year_of_manufacture'], 
                $d['rto_loc'],
                $d['date_of_registration'],
                $d['make'], 
                $d['model'], 
                $d['od'],
                $d['tp'],
                $d['net_amount'],
                $d['service_tax'],
                $d['total_amount'],
                $d['payment_mode'],
                $d['expire_date'],
                $d['ncb_percent'],
                $d['broker_code'],
                $d['assigned_to'],
                // $d['sales_manager'],
                $d['sales_executive'],
                $d['contact_no'],
                $d['address'],
                $d['pincode'],
                $d['booking_status'],
                $d['booking_date'],
                $d['remark']
            ], null, "A$row");

            $row++;
        }
        
        // foreach ($data as $d) {
        //     $sheet->fromArray([
        //         $d['enquiry_id'],
        //         $d['pos_code'],
        //         $d['pos_name'],
        //         $d['ref_code'],
        //         $d['partner_name'],
        //         $d['inward_date'],
        //         $d['insurer'],
        //         $d['policy_number'],
        //         $d['reg_no'],
        //         $d['insured_name'],
        //         $d['plan_type'],
        //         $d['product'],
        //         $d['product_type'],
        //         $d['year_of_manufacture'],
        //         $d['vehicle_weight'],
        //         $d['od'],
        //         $d['tp'],
        //         $d['net_amount'],
        //         $d['service_tax'],
        //         $d['total_amount'],
        //         $d['payment_mode'],
        //         $d['rto_loc'],
        //         $d['ncb_percent'],
        //         $d['expire_date'],
        //         $d['broker_code'],
        //         $d['assigned_to'],
        //         $d['sales_manager'],
        //         $d['sales_executive'],
        //         $d['contact_no'],
        //         $d['address'],
        //         $d['pincode'],
        //         $d['booking_status'],
        //         $d['booking_date'],
        //         $d['booking_ref_no'],
        //         $d['grid_percent'],
        //         $d['grid_amount'],
        //         $d['remark']
        //     ], null, "A$row");

        //     $row++;
        // }

        // -----------------------------
        // DOWNLOAD RESPONSE
        // -----------------------------
        $fileName = 'Policy_Report_' . date('YmdHis') . '.xlsx';

        $response = $this->response;
        $response->setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $response->setHeader('Content-Disposition', 'attachment; filename="'.$fileName.'"');
        $response->setHeader('Cache-Control', 'max-age=0');

        $writer = new Xlsx($spreadsheet);
        ob_start();
        $writer->save('php://output');
        $excelOutput = ob_get_clean();

        return $response->setBody($excelOutput);
    }
}
