<?php

namespace App\Libraries\TPAClaimsImportServices;

use App\Models\TicketMasterModel;
use App\Models\ClientPolicyModel;
use App\Models\ClientRMModel;
use App\Models\EmployeeModel;
use App\Models\ClaimDumpFileModel;

class MediAssistClaimImportService extends BaseTpaClaimImportService
{   

    /**
     * MAPPING ARRAYs 
     */

    protected $mapping = [
        ["excel_column" => ["col_name" => "insurance_company", "col_index" => 0], "db_column" => "insurance_company"],
        ["excel_column" => ["col_name" => "insurer_region_name", "col_index" => 1], "db_column" => "insurer_region_name"],
        ["excel_column" => ["col_name" => "insurer_ro_code", "col_index" => 2], "db_column" => "insurer_ro_code"],
        ["excel_column" => ["col_name" => "insurer_do_code", "col_index" => 3], "db_column" => "insurer_do_code"],
        ["excel_column" => ["col_name" => "insurer_bo_code", "col_index" => 4], "db_column" => "insurer_bo_code"],
        ["excel_column" => ["col_name" => "event_id", "col_index" => 5], "db_column" => "event_id"],
        ["excel_column" => ["col_name" => "claim_id", "col_index" => 6], "db_column" => "claim_id"],
        ["excel_column" => ["col_name" => "insurer_claim_ref_no", "col_index" => 7], "db_column" => "insurer_claim_ref_no"],
        ["excel_column" => ["col_name" => "claim_pre_auths", "col_index" => 8], "db_column" => "claim_pre_auths"],
        ["excel_column" => ["col_name" => "ma_policy_id", "col_index" => 9], "db_column" => "ma_policy_id"],
        ["excel_column" => ["col_name" => "policy_no", "col_index" => 10], "db_column" => "policy_no"],
        ["excel_column" => ["col_name" => "policy_holder_name", "col_index" => 11], "db_column" => "policy_holder_name"],
        ["excel_column" => ["col_name" => "policy_type", "col_index" => 12], "db_column" => "policy_type"],
        ["excel_column" => ["col_name" => "policy_subtype_desc", "col_index" => 13], "db_column" => "policy_subtype_desc"],
        ["excel_column" => ["col_name" => "policy_start_date", "col_index" => 14], "db_column" => "policy_start_date"],
        ["excel_column" => ["col_name" => "policy_end_date", "col_index" => 15], "db_column" => "policy_end_date"],
        ["excel_column" => ["col_name" => "devlopment_officer", "col_index" => 16], "db_column" => "devlopment_officer"],
        ["excel_column" => ["col_name" => "agent", "col_index" => 17], "db_column" => "agent"],
        ["excel_column" => ["col_name" => "broker", "col_index" => 18], "db_column" => "broker"],
        ["excel_column" => ["col_name" => "pribenef_employee_code", "col_index" => 19], "db_column" => "pribenef_employee_code"],
        ["excel_column" => ["col_name" => "pribenef_name", "col_index" => 20], "db_column" => "pribenef_name"],
        ["excel_column" => ["col_name" => "pribenef_floater_sum", "col_index" => 21], "db_column" => "pribenef_floater_sum"],
        ["excel_column" => ["col_name" => "benef_maid", "col_index" => 22], "db_column" => "benef_maid"],
        ["excel_column" => ["col_name" => "benef_insurer_id", "col_index" => 23], "db_column" => "benef_insurer_id"],
        ["excel_column" => ["col_name" => "benef_name", "col_index" => 24], "db_column" => "benef_name"],
        ["excel_column" => ["col_name" => "benef_gender", "col_index" => 25], "db_column" => "benef_gender"],
        ["excel_column" => ["col_name" => "benef_relation", "col_index" => 26], "db_column" => "benef_relation"],
        ["excel_column" => ["col_name" => "benef_age", "col_index" => 27], "db_column" => "benef_age"],
        ["excel_column" => ["col_name" => "benef_sum_insured", "col_index" => 28], "db_column" => "benef_sum_insured"],
        ["excel_column" => ["col_name" => "balance_sum_insured", "col_index" => 29], "db_column" => "balance_sum_insured"],
        ["excel_column" => ["col_name" => "intimation_id", "col_index" => 30], "db_column" => "intimation_id"],
        ["excel_column" => ["col_name" => "intimation_date", "col_index" => 31], "db_column" => "intimation_date"],
        ["excel_column" => ["col_name" => "settled_date", "col_index" => 32], "db_column" => "settled_date"],
        ["excel_column" => ["col_name" => "ClaimSource", "col_index" => 33], "db_column" => "ClaimSource"],
        ["excel_column" => ["col_name" => "claim_mode_of_rcpt", "col_index" => 34], "db_column" => "claim_mode_of_rcpt"],
        ["excel_column" => ["col_name" => "claim_type", "col_index" => 35], "db_column" => "claim_type"],
        ["excel_column" => ["col_name" => "claim_sub_type", "col_index" => 36], "db_column" => "claim_sub_type"],
        ["excel_column" => ["col_name" => "claim_stage", "col_index" => 37], "db_column" => "claim_stage"],
        ["excel_column" => ["col_name" => "claim_status", "col_index" => 38], "db_column" => "claim_status"],
        ["excel_column" => ["col_name" => "is_cashlessanywhere", "col_index" => 39], "db_column" => "is_cashlessanywhere"],
        ["excel_column" => ["col_name" => "date_of_admission", "col_index" => 40], "db_column" => "date_of_admission"],
        ["excel_column" => ["col_name" => "date_of_discharge", "col_index" => 41], "db_column" => "date_of_discharge"],
        ["excel_column" => ["col_name" => "claim_amount", "col_index" => 42], "db_column" => "claim_amount"],
        ["excel_column" => ["col_name" => "claim_approved_amount", "col_index" => 43], "db_column" => "claim_approved_amount"],
        ["excel_column" => ["col_name" => "incurred_amount", "col_index" => 44], "db_column" => "incurred_amount"],
        ["excel_column" => ["col_name" => "primary_icd_group", "col_index" => 45], "db_column" => "primary_icd_group"],
        ["excel_column" => ["col_name" => "primary_ailment_name", "col_index" => 46], "db_column" => "primary_ailment_name"],
        ["excel_column" => ["col_name" => "primary_ailment_code", "col_index" => 47], "db_column" => "primary_ailment_code"],
        ["excel_column" => ["col_name" => "treatment_type", "col_index" => 48], "db_column" => "treatment_type"],
        ["excel_column" => ["col_name" => "treatment_name", "col_index" => 49], "db_column" => "treatment_name"],
        ["excel_column" => ["col_name" => "hospital_id", "col_index" => 50], "db_column" => "hospital_id"],
        ["excel_column" => ["col_name" => "hospital_name", "col_index" => 51], "db_column" => "hospital_name"],
        ["excel_column" => ["col_name" => "hospital_city", "col_index" => 52], "db_column" => "hospital_city"],
        ["excel_column" => ["col_name" => "hospital_state", "col_index" => 53], "db_column" => "hospital_state"],
        ["excel_column" => ["col_name" => "hospital_pincode", "col_index" => 54], "db_column" => "hospital_pincode"],
        ["excel_column" => ["col_name" => "hospital_address", "col_index" => 55], "db_column" => "hospital_address"],
        ["excel_column" => ["col_name" => "Clinic_DoctorName_Hospital", "col_index" => 56], "db_column" => "Clinic_DoctorName_Hospital"],
        ["excel_column" => ["col_name" => "OPD_pincode", "col_index" => 57], "db_column" => "OPD_pincode"],
        ["excel_column" => ["col_name" => "payable_amount_OPD_Consultation", "col_index" => 58], "db_column" => "payable_amount_OPD_Consultation"],
        ["excel_column" => ["col_name" => "payable_amount_Dental", "col_index" => 59], "db_column" => "payable_amount_Dental"],
        ["excel_column" => ["col_name" => "payable_amount_Diagnostics", "col_index" => 60], "db_column" => "payable_amount_Diagnostics"],
        ["excel_column" => ["col_name" => "payable_amount_Other", "col_index" => 61], "db_column" => "payable_amount_Other"],
        ["excel_column" => ["col_name" => "payable_amount_Pharmacy", "col_index" => 62], "db_column" => "payable_amount_Pharmacy"],
        ["excel_column" => ["col_name" => "payable_amount_Vaccination", "col_index" => 63], "db_column" => "payable_amount_Vaccination"],
        ["excel_column" => ["col_name" => "payable_amount_Miscellaneous_Charges", "col_index" => 64], "db_column" => "payable_amount_Miscellaneous_Charges"],
        ["excel_column" => ["col_name" => "payable_amount_Health_Checkup", "col_index" => 65], "db_column" => "payable_amount_Health_Checkup"],
        ["excel_column" => ["col_name" => "deduction_amount_copay", "col_index" => 66], "db_column" => "deduction_amount_copay"],
        ["excel_column" => ["col_name" => "deduction_amount_excess_ailment", "col_index" => 67], "db_column" => "deduction_amount_excess_ailment"],
        ["excel_column" => ["col_name" => "deduction_amount_excess_policy", "col_index" => 68], "db_column" => "deduction_amount_excess_policy"],
        ["excel_column" => ["col_name" => "deduction_amount_prorata", "col_index" => 69], "db_column" => "deduction_amount_prorata"],
        ["excel_column" => ["col_name" => "deduction_amount_hospital_discount", "col_index" => 70], "db_column" => "deduction_amount_hospital_discount"],
        ["excel_column" => ["col_name" => "deduction_amount_paid_by_patient", "col_index" => 71], "db_column" => "deduction_amount_paid_by_patient"],
        ["excel_column" => ["col_name" => "deduction_amount_issurer_approved", "col_index" => 72], "db_column" => "deduction_amount_issurer_approved"],
        ["excel_column" => ["col_name" => "deduction_amount_deductible", "col_index" => 73], "db_column" => "deduction_amount_deductible"],
        ["excel_column" => ["col_name" => "deduction_amount_intimation_penalty", "col_index" => 74], "db_column" => "deduction_amount_intimation_penalty"],
        ["excel_column" => ["col_name" => "claim_payable_to_name", "col_index" => 75], "db_column" => "claim_payable_to_name"],
        ["excel_column" => ["col_name" => "utr_no", "col_index" => 76], "db_column" => "utr_no"],
        ["excel_column" => ["col_name" => "utr_date", "col_index" => 77], "db_column" => "utr_date"],
        ["excel_column" => ["col_name" => "denial_short_description", "col_index" => 78], "db_column" => "denial_short_description"],
        ["excel_column" => ["col_name" => "claim_received_date", "col_index" => 79], "db_column" => "claim_received_date"],
        ["excel_column" => ["col_name" => "last_necessary_doc_rec_date", "col_index" => 80], "db_column" => "last_necessary_doc_rec_date"],
        ["excel_column" => ["col_name" => "processed_date", "col_index" => 81], "db_column" => "processed_date"],
        ["excel_column" => ["col_name" => "first_document_attached_date", "col_index" => 82], "db_column" => "first_document_attached_date"],
        ["excel_column" => ["col_name" => "claim_processing_tat_days", "col_index" => 83], "db_column" => "claim_processing_tat_days"],
        ["excel_column" => ["col_name" => "ready_for_payment_date", "col_index" => 84], "db_column" => "ready_for_payment_date"],
        ["excel_column" => ["col_name" => "payment_date", "col_index" => 85], "db_column" => "payment_date"],
        ["excel_column" => ["col_name" => "claim_payment_tat_days", "col_index" => 86], "db_column" => "claim_payment_tat_days"],
        ["excel_column" => ["col_name" => "denial_description", "col_index" => 87], "db_column" => "denial_description"],
        ["excel_column" => ["col_name" => "error_group", "col_index" => 88], "db_column" => "error_group"],
        ["excel_column" => ["col_name" => "tpa_name", "col_index" => 89], "db_column" => "tpa_name"],
        ["excel_column" => ["col_name" => "new_short_error_group", "col_index" => 90], "db_column" => "new_short_error_group"],
        ["excel_column" => ["col_name" => "death_claim", "col_index" => 91], "db_column" => "death_claim"],
        ["excel_column" => ["col_name" => "vip_claim", "col_index" => 92], "db_column" => "vip_claim"],
        ["excel_column" => ["col_name" => "balance_sum_insured_exhausted", "col_index" => 93], "db_column" => "balance_sum_insured_exhausted"],
    ];

    protected $ticketMasterMapping = [

        // Employee / Beneficiary details
        'pribenef_employee_code' => 'emp_code',

        // Policy / Claim identifiers
        'claim_id'               => 'claim_number',
        'event_id'               => 'tpa_claim_id',
        'claim_pre_auths'        => 'tpa_claim_push_reference_no',

        // Claim type & status
        'claim_status'           => 'tpa_claim_status',

        // Dates
        'date_of_admission'      => 'doa',
        'date_of_discharge'      => 'dod',
        'intimation_date'        => 'date_of_intimat',
        'settled_date'           => 'settled_date',
        'claim_received_date'    => 'registration_date',
        'processed_date'         => 'approved_date',

        // Amounts
        'claim_amount'           => 'claim_amount',
        'claim_approved_amount'  => 'approved_amount',

        // Hospital details
        'hospital_name'          => 'hospital_name',
        'hospital_address'       => 'hospital_address',
        'hospital_city'          => 'hospital_city',
        'hospital_state'         => 'hospital_state',
        'hospital_pincode'       => 'hospital_pin_code',
        'hospital_phone_no'      => 'hospital_phone_no',

        // Denial / approval
        'denial_description'     => 'denial_reason',
        'approved_description'   => 'approved_description',

        // Payment
        'utr_no'                 => 'utr_details',
        'claim_type'             => 'tpa_claim_type',
        'primary_ailment_name'   => 'tpa_ailments',
    ];

    /**
     * Extra dump → claim_report fields beyond ticketMasterMapping.
    protected $statusMapping = [
        'Settled' => 11,
        'Rejected' => 8,
        'Paid' => 11,
        'Denied' => 8,
        'Cancelled' => 13,
        'Processed' => 61,
        'Information Awaited' => 4,
        'Denied Letter Sent' => 66,
        'Cashless Document Awaited' => 3,
        'Approved' => 9,
        'Closed' => 12,
    ];

    /**
     * ABSTRACT FUNCTIONs 
     */
    public function bulkInsertTPATable(array $data): bool
    {
        if (empty($data)) {
            return false;
        }
        
        $builder = $this->db->table('claims_dump_medi_assist');
        return $builder->insertBatch($data);

    }


    public function importClaimMaster(array $data): bool
    {
        if (empty($data)) {
            return false;
        }
        
        $ticketMasterModel = new TicketMasterModel();
        return $ticketMasterModel->insertBatch($data);
    }


    public function updateTicketIdInTPATable(int $fileId): bool
    {
        $rows = $this->db->table('claims_dump_medi_assist cd')
            ->select('cd.id, tm.id AS ticket_id')
            ->join('ticket_master tm', 'tm.claim_dump_ref_id = cd.id AND tm.file_id = cd.file_id', 'inner')
            ->where('cd.is_active', 1)
            ->where('cd.file_id', $fileId)
            ->where('cd.ticket_id IS NULL')
            ->get()
            ->getResultArray();

        if (empty($rows)) {
            return true;
        }

        $updateData = [];
        foreach ($rows as $row) {
            $updateData[] = [
                'id'        => $row['id'],
                'ticket_id' => $row['ticket_id'],
            ];
        }

        return $this->db->table('claims_dump_medi_assist')->updateBatch($updateData, 'id') !== false;
    }


    public function updateTicketMasterRejectedReasonInTPATable($data): bool
    {   
        if (empty($data)) {
            return false;
        }
        
        $builder = $this->db->table('claims_dump_medi_assist');
        return $builder->updateBatch($data, 'id') !== false;
    }


    /**
     * MAPPING FUNCTIONs 
     */

    public function mapTPAData(array $rows, $file_id): array
    {   
        $ClientPolicyModel = new ClaimDumpFileModel();
        $file_data = $ClientPolicyModel->where('id', $file_id)->first();

        $mapped = [];

        foreach ($rows as $row) {

            $item = [];

            foreach ($this->mapping as $map) {
                $excelColumn = $map['excel_column']['col_name'];
                $dbColumn    = $map['db_column'];

                $value = $row[$excelColumn] ?? null;
                $item[$dbColumn] = $value;
            }

            $item['file_id'] = $file_id ?? null;
            $item['client_id'] = $file_data['client_id'] ?? null;
            $item['client_policy_id'] = $file_data['client_policy_id'] ?? null;
            $item['created_by'] = $file_data['created_by'] ?? null;

            $mapped[] = $item;
        }

        return $mapped;
    }

    public function mapClaimMasterData($file_id): array
    {

        $ClientPolicyModel = new ClaimDumpFileModel();
        $file_data = $ClientPolicyModel->where('id', $file_id)->first();

        $tpaClaimDumpData = $this->getTpaClaimDumpData('claims_dump_medi_assist', ['file_id' => $file_id]);

        if (empty($tpaClaimDumpData)) {
            return $this->emptyClaimMasterMappingResponse('claims_dump_medi_assist', (int) $file_id);
        }


        $ClientPolicyModel = new ClientPolicyModel();
        $client_policy_data = $ClientPolicyModel
            ->select("
                client_policy.*,
                (
                    SELECT id
                    FROM client_rm
                    WHERE is_active = 1
                    AND level = 3
                    AND client_id = client_policy.client_id
                    ORDER BY id ASC
                    LIMIT 1
                ) AS acm_id
            ")
            ->where('client_policy.id', $file_data['client_policy_id'])
            ->where('client_policy.is_active', 1)
            ->first();

        try {

            $mapped = [];
            $rejecetd_reason = [];
            $status_update_array = [];

            foreach ($tpaClaimDumpData as $row) {

                $tpaNo = trim((string) ($row['benef_insurer_id'] ?? ''));
                if ($tpaNo === '') {
                    $tpaNo = trim((string) ($row['benef_maid'] ?? ''));
                }
                if ($tpaNo === '') {
                    $tpaNo = trim((string) ($row['event_id'] ?? ''));
                }
                $tpaNo = $tpaNo !== '' ? $tpaNo : null;

                $params = [
                    'doa' => change_date_format($row['date_of_admission'] ?? '') ?? null,
                    'emp_code' => $row['pribenef_employee_code'] ?? null,
                    'claim_amount' => $row['claim_amount'] ?? null,
                    'tpa_no' => $tpaNo,
                    'client_id' => $file_data['client_id'] ?? null,
                    'client_policy_id' => $file_data['client_policy_id'] ?? null,
                ];

                $newStatusId = $this->resolveClaimStatusId(
                    $this->statusMapping,
                    $row['claim_status'] ?? '',
                    (int) $row['id'],
                    $rejecetd_reason
                );
                if ($newStatusId === null) {
                    continue;
                }
                $existingTicket = $this->getExistingTicketMasterClaim($params);

                if ($this->handleExistingTicketStatusUpdate(
                    $existingTicket,
                    $newStatusId,
                    (int) $row['id'],
                    $status_update_array,
                    $rejecetd_reason
                )) {
                    continue;
                }

                $item = [];

                $item['client_id']    = $client_policy_data['client_id'] ?? null;
                $item['client_policy_id'] = $client_policy_data['id'] ?? null;
                $item['insurer_id']  = $client_policy_data['insurer_id'] ?? null;
                $item['tpa_id']  = $client_policy_data['tpa_id'] ?? null;
                $item['acm_id']  = $client_policy_data['acm_id'] ?? null;
                $item['policy_no']  = $client_policy_data['policy_no'] ?? null;
                $item['relationship']  = $this->convertRelation($row['benef_relation'] ?? null);

                $employee_data = $this->getEmployeeDetails($file_data['client_id'], $file_data['client_policy_id'], $row['pribenef_employee_code'], $item['relationship']);

                if (!empty($employee_data)) {
                    $item['emp_id']    = $employee_data['id'] ?? null;
                    $item['emp_name'] = $employee_data['name'] ?? null;
                    $item['emp_code'] = $employee_data['emp_code'] ?? null;
                    $item['emp_mail']  = $employee_data['emp_mail'] ?? null;
                    $item['emp_mobile']  = $employee_data['emp_mobile'] ?? null;
                    if (!empty($employee_data['insured_emp_id'])) {
                        $item['insured_emp_id']  = $employee_data['insured_emp_id'] ?? null;
                        $item['insured_name']  = $employee_data['insured_name'] ?? null;
                    }else{
                        $reason = 'This Dependent employee not exist in our system.';
                        $rejecetd_reason[] = ["id" => $row['id'], "master_reject_reason" => $reason];
                        continue;
                    }                
                } else {

                    $reason = 'This employee not exist in our system.';
                    $rejecetd_reason[] = ["id" => $row['id'], "master_reject_reason" => $reason];
                    continue;
                }

                foreach ($this->ticketMasterMapping as $tpaKey => $ticketMasterKey) {
                    $item[$ticketMasterKey] = array_key_exists($tpaKey, $row) ? $row[$tpaKey] : null;
                }

                // Meta fields
                $item['claim_dump_ref_id']    = $row['id'];
                $item['file_id']              = $file_id;
                $item['claim_status_id']      = $newStatusId;
                $item['claim_dump_date'] = $file_data['claim_dump_date'] ?? null;
                $item['created_by'] = $file_data['created_by'] ?? null;
                $item['tpa_no'] = $tpaNo;
                $item['claim_type'] = 1;
                $item['priority']           = 1;
                $item['mode_of_intimation'] = 5;
                $item['ticket_type_id']     = 1;
                $item['claim_created_by']   = 'DUMP_TPA';

                $mapped[] = $item;
            }

            return [
                'status' => true,
                'mapped_array' => $mapped,
                'rejected_reason_array' => $rejecetd_reason,
                'status_update_array' => $status_update_array,
            ];
            
        } catch (\Throwable $th) {

            $errorData = [
                'message' => $th->getMessage(),
                'file'    => $th->getFile(),
                'line'    => $th->getLine(),
                'code'    => $th->getCode(),
                'trace'   => $th->getTraceAsString(),
                'trace_array' => $th->getTrace(), // full array version (optional)
                'function' => $th->getTrace()[0]['function'] ?? null,
                'class' => $th->getTrace()[0]['class'] ?? null,
            ];

            return ['status' => false, "message" => $errorData['message'], 'error_data' => $errorData];
        }
    }


    /**
     * HELPER FUNCTIONs 
     */

    public function convertRelation(?string $relation): ?string
    {
        if (empty($relation)) {
            return null;
        }

        $relation = strtolower($relation);

        if (str_contains($relation, 'self')) {
            return 'self';
        }

        if (str_contains($relation, 'spouse') || str_contains($relation, 'wife') || str_contains($relation, 'husband')) {
            return 'spouse';
        }

        if (str_contains($relation, 'daughter')) {
            return 'daughter';
        }

        if (str_contains($relation, 'son')) {
            return 'son';
        }

        if (str_contains($relation, 'father in law') || str_contains($relation, 'father-in-law')) {
            return 'father-in-law';
        }

        if (str_contains($relation, 'mother in law') || str_contains($relation, 'mother-in-law')) {
            return 'mother-in-law';
        }

        if (str_contains($relation, 'father')) {
            return 'father';
        }

        if (str_contains($relation, 'mother')) {
            return 'mother';
        }

        return null; // unmatched case
    }


}