<?php

namespace App\Controllers;

use CodeIgniter\RESTful\ResourceController;

use App\Models\AgentModel;
use App\Models\EnquiryModel;
use App\Models\QuotationModel;
use App\Models\PolicyModel;
use App\Models\FilesModel;
use DateTime;
class EnquiryController extends ResourceController
{
    protected $db;
    protected $enquiryModel;
    protected $AgentModel;
    protected $QuotationModel;
    protected $PolicyModel;
    protected $FilesModel;

    public function __construct()
    {

        $this->db = db_connect();
        $this->enquiryModel = new EnquiryModel();
        $this->AgentModel = new AgentModel();
        $this->QuotationModel = new QuotationModel();
        $this->PolicyModel = new PolicyModel();
        $this->FilesModel = new FilesModel();
    }

    // List all enquiries
    // This query is also copied in PolicyReportController::downloadExcel(). Please apply the same changes there as well.
    // 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=
    public function enquiryList()
    {
        try {

            $agent_id   = $this->request->getGet('agent_id');
            $staff_id = $this->request->getGet('staff_id');
            $enquiry_id = $this->request->getGet('enquiry_id');
            $manager_id   = $this->request->getGet('manager_id');
            $handler_id   = $this->request->getGet('handler_id');

            $from_date = $this->request->getGet('from_date'); 
            $to_date   = $this->request->getGet('to_date');   

            $status   = $this->request->getGet('status'); 
            $enquiry_status   = $this->request->getGet('enquiry_status');   

            $only_policy_data = $this->request->getGet('only_policy_data') ?? false;
            $show_policy_report = $this->request->getGet('show_policy_report') ?? 'All';
            $insurer_id = $this->request->getGet('insurer_id') ?? null;


            // Date filter handling
            if (empty($from_date) || empty($to_date)) {
                // Default last 7 days
                // $from_date = date('Y-m-d 00:00:00', strtotime('-7 days'));
                // $to_date   = date('Y-m-d 23:59:59');
                $from_date = null;
                $to_date   = null;
            } else {
                // Convert d-m-Y → Y-m-d
                $from_date = DateTime::createFromFormat('d-m-Y', $from_date)->format('Y-m-d 00:00:00');
                $to_date   = DateTime::createFromFormat('d-m-Y', $to_date)->format('Y-m-d 23:59:59');
            }


            if (!empty($staff_id) && !empty($manager_id)) {
                $data = $this->enquiryModel->getEnquiry('STAFF&MANAGER', [$staff_id,$manager_id], $only_policy_data, $show_policy_report,$insurer_id , $from_date, $to_date, $status ,$enquiry_status);
            } elseif (!empty($agent_id)) {
                $data = $this->enquiryModel->getEnquiry('AGENT', $agent_id, $only_policy_data, $show_policy_report, $insurer_id , $from_date, $to_date, $status ,$enquiry_status);
            } elseif (!empty($staff_id)) {
                $data = $this->enquiryModel->getEnquiry('STAFF', $staff_id, $only_policy_data, $show_policy_report, $insurer_id , $from_date, $to_date, $status ,$enquiry_status);
            } elseif (!empty($enquiry_id)) {
                $data = $this->enquiryModel->getEnquiry('ENQUIRY',$enquiry_id, $only_policy_data, $show_policy_report,$insurer_id);
            } elseif (!empty($manager_id)) {
                $data = $this->enquiryModel->getEnquiry('MANAGER',$manager_id, $only_policy_data, $show_policy_report, $insurer_id , $from_date, $to_date, $status ,$enquiry_status);
            }elseif (!empty($handler_id)) {

                 $staffData = $this->db->table('partner_staff ps')
                                            ->select("ps.id")
                                            ->groupStart()
                                                ->where("JSON_CONTAINS(ps.handler_id, '\"$handler_id\"')")
                                                ->orWhere('ps.id', $handler_id)
                                            ->groupEnd()
                                            ->groupBy('ps.id')
                                            ->get()->getResultArray();
                $staffIds = array_column($staffData, 'id');       

                $data = $this->enquiryModel->getEnquiry('HANDLER',$staffIds, $only_policy_data, $show_policy_report, $insurer_id , $from_date, $to_date, $status ,$enquiry_status);
            } else {
                $data = $this->enquiryModel->getEnquiry('ALL');
            }

            // echo (string) $db->getLastQuery();
            // echo $this->db->getLastQuery()->getQuery();die;


            foreach ($data as $key => $row) {
                if($row['enquiry_created_on'] !== null)
                {
                    $data[$key]['enquiry_created_on']  = date('d-m-Y', strtotime($row['enquiry_created_on']));
                }
                if($row['created_on'] !== null)
                {
                    $data[$key]['created_on']  = date('d-m-Y h:i A', strtotime($row['created_on']));
                }
                if($row['updated_on'] !== null)
                {
                    $data[$key]['updated_on']  = date('d-m-Y h:i A', strtotime($row['updated_on']));
                }
            }


            if (!empty($this->request->getGet('from_date'))) {
                $from_date = DateTime::createFromFormat('d-m-Y', $this->request->getGet('from_date'))->format('Y-m-d 00:00:00');
                $from_date = DateTime::createFromFormat('Y-m-d 00:00:00', $from_date)->format('d-m-Y');
            }
             if (!empty($this->request->getGet('to_date'))) {
                $to_date   = DateTime::createFromFormat('d-m-Y', $this->request->getGet('to_date'))->format('Y-m-d 23:59:59');
                $to_date   = DateTime::createFromFormat('Y-m-d 23:59:59', $to_date)->format('d-m-Y');
            }
            
            return $this->respond(['status' => 'success', 'code' => 200, 'data' => $data , 'from_date' => $from_date , 'to_date' => $to_date ], 200);
            
        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

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

             // Get enquiry list
            $enquiryData = $this->enquiryModel->select('partner_enquiry.*, partner_enquiry.name as insured_name,A.name as agent_name, A.agent_code,')
                                ->where('partner_enquiry.is_active', 1)
                                ->where('partner_enquiry.is_quick_quote', 1)
                                ->where('partner_enquiry.manager_id', $manager_id)
                                ->where('partner_enquiry.status', 'Proposal Created')
                                ->join('partner_agent A', 'A.id = partner_enquiry.agent_id', 'left')
                                ->orderBy('partner_enquiry.created_on', 'ASC')
                                ->findAll();

            // Loop & attach quotation data
            foreach ($enquiryData as $key => $value) {

                $quoteData = $this->QuotationModel
                    ->select('partner_quotation.*, partner_quotation.id AS quotation_id, 
                            I.name AS insurer_name, I.short_name AS insurer_short_name,
                            ipti.insurance_plan_type')
                    ->join('insurers I', 'I.id = partner_quotation.insurer_id', 'left')
                    ->join('partner_insurance_plan_type_master ipti', 'ipti.id = partner_quotation.insurance_plan_type_id', 'left')
                    ->where('partner_quotation.enquiry_id', $value['id'])
                    ->findAll();

                // Assign properly back into array
                $enquiryData[$key]['quotation_data'] = $quoteData;
            }

            
            return $this->respond(['status' => 'success', 'code' => 200, 'data' => $enquiryData  ], 200);
            
        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

    // List all enquiries
    public function enquiryQuotePolicyView()
    {
        try {

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

            if (!$enquiry_id) {
                return $this->respond(['status' => 'failed','code'   => 400,'data'  => 'enquiry_id is required'], 200);
            }

            //Get enquiry details
            $enquiry = $this->enquiryModel->select('partner_enquiry.* ,  I.name as insurer_name , I.short_name , VT.vehicle_type as vehicle_type ,PB.name as broker_name ,PA.name as agent_name,PA.agent_code,PPMM.value as payment_mode_value')
                                        ->join('insurers I', 'I.id = partner_enquiry.insurer_id', 'left')
                                        ->join('vehicle_type VT', 'VT.id = partner_enquiry.vehicle_type_id', 'left')
                                        ->join('partner_brokers PB', 'PB.id = partner_enquiry.broker_id', 'left')
                                        ->join('partner_agent PA', 'PA.id = partner_enquiry.agent_id', 'left')
                                        ->join('partner_quotation  PQ', 'PQ.enquiry_id = partner_enquiry.id', 'left')
                                        ->join('partner_payment_mode_master PPMM', 'PPMM.id = PQ.payment_mode_id', 'left')
                                        ->where('partner_enquiry.id', $enquiry_id)
                                        ->where('partner_enquiry.is_active', 1)
                                        ->first();

            if (!$enquiry) {
                return $this->respond(['status' => 'failed','code'   => 404,'data'  => 'Enquiry not found'], 200);
            }

            //Get related quotations
            $quotations = $this->QuotationModel->select('partner_quotation.*, pe.reg_no ,  I.name as insurer_name, I.short_name , ipti.insurance_plan_type,pe.broker_id ,PB.name as broker_name,PA.name as agent_name , PP.policy_pdf_file_name , PP.id as policy_id , pm.value as payment_mode_value' )
                                            ->join('partner_enquiry pe', 'pe.id = partner_quotation.enquiry_id', 'left')
                                            ->join('insurers I', 'I.id = partner_quotation.insurer_id', 'left')
                                            ->join('partner_insurance_plan_type_master ipti', 'ipti.id = partner_quotation.insurance_plan_type_id', 'left')
                                            ->join('partner_brokers PB', 'PB.id = pe.broker_id', 'left')
                                            ->join('partner_agent PA', 'PA.id = pe.agent_id', 'left')
                                            ->join('partner_policy PP', 'PP.quotation_id = partner_quotation.id', 'left')
                                            ->join('partner_payment_mode_master pm', 'pm.id = partner_quotation.payment_mode_id', 'left')
                                            ->where('partner_quotation.enquiry_id', $enquiry_id)
                                            ->where('partner_quotation.is_active', 1)
                                            ->orderBy('partner_quotation.id', 'DESC')
                                            ->findAll();

            //Get related policies
            $policies = [];
            if (!empty($quotations)) {
                $quotationId = 0;
                foreach ($quotations as $key => $value) {
                   if($value['status'] == 'Accepted') { $quotationId = $value['id']; }
                }
               
                $policies = $this->PolicyModel->select('partner_policy.*, pe.reg_no, I.name as insurer_name, I.short_name , pq.insured_declared_value,  ipti.insurance_plan_type, pe.broker_id ,  PB.name as broker_name,PA.name as agent_name, pm.id as payment_mode_id , pm.value as payment_mode_value')
                                            ->join('partner_enquiry pe', 'pe.id = partner_policy.enquiry_id', 'left')
                                            ->join('partner_quotation pq', 'pq.id = partner_policy.quotation_id', 'left')
                                            ->join('insurers I', 'I.id = pq.insurer_id', 'left')
                                            ->join('partner_insurance_plan_type_master ipti', 'ipti.id = pq.insurance_plan_type_id', 'left')
                                            ->join('partner_brokers PB', 'PB.id = pe.broker_id', 'left')
                                            ->join('partner_agent PA', 'PA.id = pe.agent_id', 'left')
                                            ->join('partner_payment_mode_master pm', 'pm.id = pq.payment_mode_id', 'left')
                                            ->where('partner_policy.quotation_id', $quotationId)
                                            ->where('partner_policy.is_active', 1)
                                            ->first();
                    if (!empty($policies)) {
                        $policies['issued_date'] = format_date_for_client($policies['issued_date'] ?? null);
                        $policies['start_date']  = format_date_for_client($policies['start_date']  ?? null);
                        $policies['end_date']    = format_date_for_client($policies['end_date']    ?? null);
                    }
                                            

            }

            // Final response
            $data = [
                'enquiry'    => $enquiry,
                'quotations' => $quotations,
                'policies'   => $policies
            ];

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

        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed','code'   => 500,'error'  => $e->getMessage()], 500);
        }
    }

    // CREATE enquiry
    public function createEnquiry()
    {
        try {
            $data = $this->request->getPost();

            log_message('error',json_encode($data));

            // handle file uploads
            $idProofFileName = storage_upload_if_valid(
                $this->request->getFile('id_proof_file_name'),
                'enquiry',
                'id_proof'
            );
            $rcFileName = storage_upload_if_valid(
                $this->request->getFile('rc_file_name'),
                'enquiry',
                'rc'
            );
            $previousPolicyFileName = storage_upload_if_valid(
                $this->request->getFile('previous_policy_file_name'),
                'enquiry',
                'previous_policy'
            );

            //get insurer_branch_id
            if(isset($data['insurer_id']))
            {
                $query = $this->db->query("SELECT id FROM insurer_branch WHERE insurer_id = ? LIMIT 1", [$data['insurer_id']]);
                $insurerBranchresult = $query->getRowArray();
            }


            $assignedTo = $data['assigned_to'] ?? 0;

            $insertData = [
                'agent_id'                      => $data['agent_id'],
                'name'                          => $data['name'],
                'mobile'                        => $data['mobile'],
                'email'                         => $data['email'],
                'reg_no'                        => $data['reg_no'],
                'vehicle_type_id'               => $data['vehicle_type_id'] ?? 0,
                'rc_file_name'                  => $rcFileName,
                'id_proof_file_name'            => $idProofFileName,
                'previous_policy_file_name'     => $previousPolicyFileName,
                'remarks'                       => $data['remarks'],
                'manager_id'                    => $data['manager_id'] ,
                'created_by'                    => $data['created_by'],
                'is_data_created_by_staff'      => $data['is_data_created_by_staff'] ?? 1 ,
                'assigned_to'                   => $assignedTo,
                'enquiry_status'                => !empty($assignedTo) && $assignedTo != "0"
                                                    ? 'Assigned'
                                                    : 'To be assigned',
                'insurer_id'                    => $data['insurer_id'] ?? 0,
                'insurer_branch_id'             => $insurerBranchresult['id'] ?? 0,
                'broker_id'                     => $data['broker_id'] ?? 0,
                'is_quick_quote'                => $data['is_quick_quote'] ?? 0
            ];

            if (!empty($assignedTo) && $assignedTo != "0") {
                $insertData['assigned_to_datetime'] = date('Y-m-d H:i:s');
            }

            $isQuick = $data['is_quick_quote'] ?? 0;
            $isStaff = $data['is_data_created_by_staff'] ?? 0;

            if (isset($data['enquiry_created_on']) &&
                !empty($data['enquiry_created_on']) &&
                $data['enquiry_created_on'] != '0000-00-00' &&
                strtotime($data['enquiry_created_on']) !== false
                ) {
                    $insertData['enquiry_created_on'] =
                        date('Y-m-d H:i:s', strtotime($data['enquiry_created_on']));
                  }


            $enquiryId = $this->enquiryModel->insert($insertData , true);

            // trigger_email('enquiry_created', ['enquiry_id' => $enquiryId]);

            // QUOTATION MULTIPLE INSERT LOGIC
            if ($isStaff == 1 && $isQuick == 1 && !empty($data['quotation']) && is_string($data['quotation']) ) 
            {

                $quotationArray = json_decode($data['quotation'], true);
                foreach ($quotationArray as $q) {

                    //get insurer_branch_id
                    if(isset($q['insurer_id']))
                    {
                        $query = $this->db->query("SELECT id FROM insurer_branch WHERE insurer_id = ? LIMIT 1", [$q['insurer_id']]);
                        $insurerBranchresult = $query->getRowArray();
                    }

                    $quotationData = [
                        'enquiry_id'             => $enquiryId,
                        'insured_declared_value' => $q['insured_declared_value'] ?? 0,
                        'premium_amount'         => $q['premium_amount'] ?? 0,
                        'insurance_plan_type_id' => $q['insurance_plan_type_id'] ?? 0,
                        'insurer_id'             => $q['insurer_id'] ?? 0,
                        'insurer_branch_id'      => $insurerBranchresult['id'] ?? 0,
                        'manager_id'             => $data['manager_id'],
                        'payment_mode_id'        => $q['payment_mode_id'] ?? 0,
                        'created_by'             => $data['created_by'] ,
                        'is_quick_quote'         => $data['is_quick_quote'] ?? 0,
                    ];

                    $this->QuotationModel->insert($quotationData);
                }
                $this->enquiryModel->update($enquiryId, [ 'status' => 'Proposal Created' ]);
            } 
            else if ($isStaff == 1 && $isQuick == 0) 
            {

                $quotationData = [
                    'enquiry_id'             => $enquiryId,
                    'insured_declared_value' => $data['insured_declared_value'] ?? 0,
                    'premium_amount'         => $data['premium_amount'] ?? 0,
                    'insurance_plan_type_id' => $data['insurance_plan_type_id'] ?? 0,
                    'insurer_id'             => $data['insurer_id'] ?? 0,
                    'insurer_branch_id'      => $insurerBranchresult['id'] ?? 0,
                    'manager_id'             => $data['manager_id'],
                    'payment_mode_id'        => $data['payment_mode_id'] ?? 0,
                    'created_by'             => $data['created_by'],
                    'status'                 => 'Accepted'
                ];
                $QuotationId = $this->QuotationModel->insert($quotationData, true);
                $this->enquiryModel->update($enquiryId, [ 'status' => 'Proposal Accepted' ]);

            }
            

            return $this->respond(['status' => 'success', 'code' => 200, 'message' => 'Enquiry created successfully'], 200);

        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

    // UPDATE enquiry
    public function updateEnquiry()
    {
        try {
            $data = $this->request->getPost();

            if (!isset($data['id'])) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'ID Required'], 200);
            }

            $id = $data['id'];
            $enquiry = $this->enquiryModel->find((int)$id);

            if (!$enquiry) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'Data Not Found'], 200);
            }

             //get insurer_branch_id
            if(isset($data['insurer_id']))
            {
                $query = $this->db->query("SELECT id FROM insurer_branch WHERE insurer_id = ? LIMIT 1", [$data['insurer_id']]);
                $insurerBranchresult = $query->getRowArray();
            }

            
            $assignedTo = $data['assigned_to'] ?? $enquiry['assigned_to'];

            $updateData = [
                'agent_id'          => $data['agent_id'] ?? $enquiry['agent_id'],
                'name'              => $data['name'] ?? $enquiry['name'],
                'mobile'            => $data['mobile'] ?? $enquiry['mobile'],
                'email'             => $data['email'] ?? $enquiry['email'],
                'reg_no'            => $data['reg_no'] ?? $enquiry['reg_no'],
                'vehicle_type_id'   => $data['vehicle_type_id'] ?? $enquiry['vehicle_type_id'],
                'remarks'           => $data['remarks'] ?? $enquiry['remarks'],
                'updated_by'        => $data['updated_by'] ?? null,
                'assigned_to'       => $assignedTo,
                'insurer_id'        => $data['insurer_id'] ?? $enquiry['insurer_id'],
                'insurer_branch_id' => $insurerBranchresult['id'] ?? $enquiry['insurer_branch_id'],
                'broker_id'         => $data['broker_id'] ?? $enquiry['broker_id'],

            ];

            if (isset($data['assigned_to']) && !empty($data['assigned_to']) && $data['assigned_to'] != "0") {
                $updateData['assigned_to_datetime'] = date('Y-m-d H:i:s');
            }

            if (array_key_exists('enquiry_created_on', $data) &&
                !empty($data['enquiry_created_on']) &&
                $data['enquiry_created_on'] != '0000-00-00' &&
                strtotime($data['enquiry_created_on']) !== false
                ) {
                    $updateData['enquiry_created_on'] =
                        date('Y-m-d H:i:s', strtotime($data['enquiry_created_on']));
                }


            // File updates
            $idProofFileName = storage_upload_if_valid(
                $this->request->getFile('id_proof_file_name'),
                'enquiry',
                'id_proof'
            );
            if ($idProofFileName !== null) {
                $updateData['id_proof_file_name'] = $idProofFileName;
            }

            $rcFileName = storage_upload_if_valid(
                $this->request->getFile('rc_file_name'),
                'enquiry',
                'rc'
            );
            if ($rcFileName !== null) {
                $updateData['rc_file_name'] = $rcFileName;
            }

            $previousPolicyFileName = storage_upload_if_valid(
                $this->request->getFile('previous_policy_file_name'),
                'enquiry',
                'previous_policy'
            );
            if ($previousPolicyFileName !== null) {
                $updateData['previous_policy_file_name'] = $previousPolicyFileName;
            }

            $this->enquiryModel->update($id, $updateData);

            $enquiryData = $this->enquiryModel->find((int)$id);
            if($enquiryData['assigned_to'] != 0 && $enquiryData['enquiry_status'] == 'To be assigned'){
                $this->enquiryModel->update($id, ['enquiry_status' => 'Assigned']);
            }

            //update insurer data to quotation ( accepted quotation )
            $this->QuotationModel->set([ 'insurer_id' => $data['insurer_id'] ?? $enquiry['insurer_id'],'insurer_branch_id' => $insurerBranchresult['id'] ?? $enquiry['insurer_branch_id']])
                                 ->where('enquiry_id',$id)->where('status','Accepted')
                                 ->update(); 

            return $this->respond(['status' => 'success', 'code' => 200, 'message' => 'Enquiry updated successfully'], 200);

        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

    //Assign to staff
    public function enquiryAssignUpdate()
    {
        try {

            $data = $this->request->getJSON(true);

            if (!isset($data['id'])) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'ID Required'], 200);
            }

            $id = $data['id'];
            $enquiry = $this->enquiryModel->find((int)$id);

            if (!$enquiry) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'Data Not Found'], 200);
            }


            $updateData['assigned_to'] = $data['assigned_to'];
            $updateData['insurer_id'] = $data['insurer_id']; 
            $updateData['broker_id'] = $data['broker_id']; 
            if (isset($data['from_dashboard_enquiry_status']) && $data['from_dashboard_enquiry_status'] === 'Assigned') {
                $updateData['enquiry_status'] = 'Assigned';
            }

            if($enquiry['assigned_to'] != 0 && $enquiry['enquiry_status'] == 'To be assigned'){
                $updateData['enquiry_status'] = 'Assigned';
            }

            if (!empty($data['assigned_to']) && $data['assigned_to'] != "0") {
                $updateData['assigned_to_datetime'] = date('Y-m-d H:i:s');
            }

            $this->enquiryModel->update($id, $updateData);

            // trigger_email('enquiry_assigned', ['enquiry_id' => $id]);

            return $this->respond(['status' => 'success', 'code' => 200, 'message' => 'Enquiry updated successfully'], 200);
            
        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

    // Download enquiry file
    public function downloadEnquiryFile()
    {
        try {
            $enquiryId = $this->request->getGet('enquiry_id');
            $fileType  = $this->request->getGet('file_type'); // rc | id_proof | previous_policy

            if (!$enquiryId || !$fileType) {
                return $this->respond(['status' => 'failed','code'   => 400,'data'   => 'enquiry_id and file_type are required'], 200);
            }

            // Map file_type to DB column and folder
            $fileMap = [
                'rc'              => ['column' => 'rc_file_name', 'folder' => 'rc'],
                'id_proof'        => ['column' => 'id_proof_file_name', 'folder' => 'id_proof'],
                'previous_policy' => ['column' => 'previous_policy_file_name', 'folder' => 'previous_policy'],
            ];

            if (!array_key_exists($fileType, $fileMap)) {
                return $this->respond(['status' => 'failed','code'   => 400,'data'   => 'Invalid file_type'], 200);
            }

            $fileColumn = $fileMap[$fileType]['column'];
            $folder     = $fileMap[$fileType]['folder'];

            // Fetch record from DB
            $fileRecord = $this->enquiryModel->where('is_active', 1)->find((int)$enquiryId);

            if (!$fileRecord || empty($fileRecord[$fileColumn])) {
                return $this->respond([ 'status' => 'failed','code'   => 404,'data'   => 'File not found in database'], 200);
            }

            $fileName = $fileRecord[$fileColumn];

            if (!storage_exists('enquiry', $folder, $fileName)) {
                return $this->respond(['status' => 'failed','code'   => 404,'data'   => 'File missing on server'], 200);
            }

            return storage_download('enquiry', $folder, $fileName);

        } catch (\Exception $e) {
            return $this->respond(['status'  => 'failed','code'    => 500,'message' => $e->getMessage() ], 500);
        }
    }

    public function uploadEnquiryFiles()
    {
        try {
            $enquiryId = $this->request->getPost('enquiry_id');

            if (!$enquiryId) {
                return $this->respond(['status' => 'failed', 'message' => 'enquiry_id required'], 404);
            }

            $files = $this->request->getFiles();

            if (!$files) {
                return $this->respond(['status' => 'failed', 'message' => 'No files uploaded'], 404);
            }

            
            $savedFiles = [];

            foreach ($files['files'] as $file) {
                if ($file->isValid()) {
                    $newName = storage_upload_if_valid($file, 'enquiry', '');
                    if ($newName === null) {
                        continue;
                    }

                    // Save DB record
                    $this->FilesModel->insert([
                        'enquiry_id' => $enquiryId,
                        'file_name'  => $newName,
                        'is_active'  => 1,
                        'created_by' => $this->request->getPost('created_by') ?? 0,
                    ]);

                    $savedFiles[] = $newName;
                }
            }

            return $this->respond(['status' => 'success','uploaded_files' => $savedFiles ], 200);

        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'message' => $e->getMessage()], 500);
        }
    }

    public function downloadEnquiryFiles()
    {
       

        $fileId = $this->request->getGet('file_id');

        $file = $this->FilesModel->find((int)$fileId);

        if (!$file || empty($file['file_name'])) {
            return $this->respond(['status' => 'failed','message' => 'Invalid file id'], 404);
        }

        if (!storage_exists('enquiry', '', $file['file_name'])) {
            return $this->respond([
                'status' => 'failed',
                'message' => 'File missing on server'
            ], 404);
        }

        return storage_download('enquiry', '', $file['file_name']);
    }

    public function deleteEnquiryFile()
    {
        try {
            $fileId = $this->request->getGet('file_id');

            if (!$fileId) {
                return $this->respond(['status'  => 'failed','message' => 'file_id is required'], 400);
            }

            $file = $this->FilesModel->find((int)$fileId);

            if (!$file) {
                return $this->respond(['status'  => 'failed','message' => 'Invalid file id'], 404);
            }

            if (!empty($file['file_name'])) {
                storage_delete('enquiry', '', $file['file_name']);
            }

            // Delete DB record
            $this->FilesModel->delete($fileId);

            return $this->respond(['status'  => 'success','message' => 'File deleted successfully'], 200);

        } catch (\Exception $e) {
            return $this->respond(['status'  => 'failed','error'   => $e->getMessage()], 500);
        }
    }


    public function enquiryFiles()
    {
        $enquiryId = $this->request->getGet('enquiry_id');

        if (!$enquiryId) {
            return $this->respond(['status' => 'failed', 'message' => 'enquiry_id required'], 400);
        }


        $files = $this->FilesModel
            ->where('enquiry_id', $enquiryId)
            ->where('is_active', 1)
            ->findAll();

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

    public function updateEnquiryInProgress()
    {
        $enquiryId = $this->request->getGet('enquiry_id');
        $this->enquiryModel->update($enquiryId, [  'enquiry_status' => 'In progress']);
        return $this->respond(['status' => 'success','data'  => $enquiryId ], 200);
    }

    public function updateEnquiryStatus()
    {
        $enquiryId = $this->request->getGet('enquiry_id');
        $isActive = $this->request->getGet('is_active');
        $this->enquiryModel->update($enquiryId, [  'is_active' => $isActive]);
        return $this->respond(['status' => 'success','data'  => $enquiryId ], 200);
    }

    public function checkVehicleDuplicate()
    {
        // --- 1. Input Validation ---
        $reg_no = $this->request->getGet("reg_no");

        if (empty($reg_no)) {
            return $this->response->setJSON([
                'status' => 'error',
                'message' => 'Please provide a valid vehicle registration number.'
            ])->setStatusCode(400);
        }

        $normalized_input = strtoupper(preg_replace('/[^A-Za-z0-9]/', '', $reg_no));

        if ($normalized_input === 'NEW') {
            return $this->response->setJSON([
                "status"  => "not exists",
                "message" => "No active record found for this vehicle number in enquiries."
            ]);
        }

        // --- 2. Step 1: Check in partner_enquiry (Active/Latest Record) ---
        // Get the LATEST ACTIVE enquiry record for the registration number
        $enquiry = $this->db->table("partner_enquiry")
            ->select("*")
            ->where("is_active", 1)
            ->groupStart()
                // This replaces common separators in the DB column with empty strings
                ->where("REPLACE(REPLACE(REPLACE(reg_no, ' ', ''), '-', ''), '.', '') =", $normalized_input)
            ->groupEnd()
            ->orderBy("id", "DESC")
            ->get()
            ->getRowArray();

        if (!$enquiry) {
            return $this->response->setJSON([
                "status"  => "not exists",
                "message" => "No active record found for $reg_no"
            ]);
        }

        // NOTE: The previous check for $enquiry["is_active"] == 0 is now redundant 
        // because the query already filters for where("is_active", 1).
        // --- 3. Step 2: Check in partner_policy (Active Policy for Enquiry) ---
        // Get the LATEST ACTIVE policy for the found enquiry
        $policy = $this->db->table("partner_policy")
            ->where("enquiry_id", $enquiry["id"])
            ->where("is_active", 1)
            ->orderBy("id", "DESC")
            ->get()
            ->getRowArray();

        if (!$policy) {
            return $this->response->setJSON([
                "status"  => "exists",
                "message" => "Vehicle enquiry found but no active policy issued."
            ]);
        }

        $endDate = $policy["end_date"] ?? '';
        $currentDate = date("Y-m-d");

        // --- 4. Step 3  Check if date is missing or invalid format (0000-00-00)
        if (empty($endDate) || $endDate === "0000-00-00") {
            return $this->response->setJSON([
                "status"  => "exists",
                "message" => "Policy exists but end date is invalid for $reg_no"
            ]);
        }

        // --- 5. Step 4 Compare Dates
        // $msg = $enquiry["name"]." with policy ".$policy["policy_number"]." for vehicle ".$reg_no." already exists and is active.";
        $msg = "An active policy already exists for vehicle number ".$reg_no.".";
        if ($endDate >= $currentDate) {
            return $this->response->setJSON([
                "status"  => "exists",
                "message" => $msg,
                "ref"    => [
                    "enquiry" => $enquiry,
                    "policy"  => $policy
                ]
            ]);
        }

        // --- 6. POLICY expired (end_date is in the past)
        return $this->response->setJSON([
            "status"  => "not exists",
            "message" => "Policy exists but expired on $endDate for vehicle $reg_no"
        ]);
    }


    public function setVehicleTypeToEnquiry()
    {
        try {
            $data = $this->request->getJSON(true);

            // print_r($data);die;

            if (!isset($data['id'])) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'ID Required'], 200);
            }

            $id = $data['id'];
            $enquiry = $this->enquiryModel->find((int)$id);

            if (!$enquiry) {
                return $this->respond(['status' => 'failed', 'code' => 200, 'error' => 'Data Not Found'], 200);
            }

            $updateData = [
                'vehicle_type_id'   => $data['vehicle_type_id'] ?? $enquiry['vehicle_type_id'],
                'updated_by'        => $data['updated_by'] ?? null,
            ];

            $this->enquiryModel->update($id, $updateData);

            return $this->respond(['status' => 'success', 'code' => 200, 'message' => 'Enquiry updated successfully'], 200);

        } catch (\Exception $e) {
            return $this->respond(['status' => 'failed', 'code' => 500, 'error' => $e->getMessage()], 500);
        }
    }

}