<?php

namespace App\Console\Commands;

use App\Models\Client\Client;
use App\Models\PensionCase\PensionCase;
use App\Models\PensionCase\PensionCaseComment;
use App\Models\PensionCase\PensionCaseInterim;
use Carbon\Carbon;
use Exception;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use League\Csv\Reader;
use Throwable;

class ImportPensionCases extends Command {
    protected $signature = 'pension-cases:import {path} {--dry-run : Validate without writing to the database}';
    protected $description = 'Import pension cases from a CSV file';

    protected bool $dryRun = false;

    protected array $summary = [
        'created' => 0,
        'updated' => 0,
        'skipped' => 0,
        'failed' => 0,
        'comments_imported' => 0,
        'interims_imported' => 0,
    ];

    /**
     * Date columns that need date normalization
     */
    protected array $dateFields = [
        'issued_tob_date',
        'tob_in_date',
        'ifa_referred_date',
        'application_submitted_date',
        'date_requested_evidence',
        'fos_allocated_date',
        'fos_app_submitted_date',
        'fos_app_written_sent_to_client_date',
        'fos_complaint_letter_sent_to_client_date',
        'fos_initial_complaint_sent_date',
        'fos_under_review_with_investigator_date',
        'fscs_allocated_date',
        'sar_received_date',
        'sar_reviewed_scanned_date',
        'application_out_for_signature_date',
        'allocated_to_investigator_date',
        'opened_at',
        'closed_at',
        'case_last_modified_at',
    ];

    /**
     * Boolean columns to normalise
     */
    protected array $booleanFields = [
        'is_open',
        'is_closed',
        'vulnerable_client_flag',
        'special_contact_requirements_flag',
        'sms_opt_out',
        'do_not_contact',
        'ifa_referred',
        'ifa_refer_opted_out',
        'client_in_dispute',
        'sar_sent',
        'sar_received',
        'fee_capping_flag',
        'atc_received',
    ];

    /**
     * Decimal columns to normalise
     */
    protected array $decimalFields = [
        'settlement_to_date_raw',
        'settlement_to_date_new',
        'projected_future_settlement',
        'quantum',
        'cpfc_fee_percentage',
        'cpfc_fee_total_nett',
        'our_fees_percentage_raw',
    ];

    /**
     * Column aliases: CSV header → model field
     */
    protected array $fieldMap = [
        'sf_case_id'                            => 'sf_case_id',
        'case_id'                               => 'sf_case_id',
        'salesforce_case_id'                    => 'sf_case_id',
        'case_number'                           => 'case_number',
        'case_owner'                            => 'case_owner',
        'case_owner_name'                       => 'case_owner',
        'created_by'                            => 'created_by',
        'case_last_modified_by'                 => 'case_last_modified_by',
        'claims_writer'                         => 'claims_writer',
        'file_investigator'                     => 'file_investigator',
        'opened_at'                             => 'opened_at',
        'closed_at'                             => 'closed_at',
        'case_last_modified_at'                 => 'case_last_modified_at',
        'is_open'                               => 'is_open',
        'is_closed'                             => 'is_closed',
        'claim_subject'                         => 'claim_subject',
        'claim_basis'                           => 'claim_basis',
        'priority'                              => 'priority',
        'settlement_status'                     => 'settlement_status',
        'bank_claim_status'                     => 'bank_claim_status',
        'fos_claim_status'                      => 'fos_claim_status',
        'fscs_claim_status'                     => 'fscs_claim_status',
        'client_settlement_instruction'         => 'client_settlement_instruction',
        'reasons_for_not_proceeding'            => 'reasons_for_not_proceeding',
        'additional_claims_info'                => 'additional_claims_info',
        'detail_of_outstanding_evidence'        => 'detail_of_outstanding_evidence',
        'underlying_investment'                 => 'underlying_investment',
        'investment_library'                    => 'investment_library',
        'pcp_subject'                           => 'pcp_subject',
        'fscs_contact_name'                     => 'fscs_contact_name',
        'fscs_contact_reference'                => 'fscs_contact_reference',
        'fscs_reference'                        => 'fscs_reference',
        'national_insurance'                    => 'national_insurance',
        'ni_number'                             => 'national_insurance',
        'nino'                                  => 'national_insurance',
        'cmc_client_reference'                  => 'cmc_client_reference',
        'cmc_reference'                         => 'cmc_client_reference',
        'car_reg'                               => 'car_reg',
        'atc'                                   => 'atc',
        'tob_issued_by'                         => 'tob_issued_by',
        'sar_to'                                => 'sar_to',
        'lead_source_cpf'                       => 'lead_source_cpf',
        'fee_structure'                         => 'fee_structure',
        'vulnerable_client_flag'                => 'vulnerable_client_flag',
        'vulnerable_client'                     => 'vulnerable_client_flag',
        'vulnerable_client_details'             => 'vulnerable_client_details',
        'special_contact_requirements_flag'     => 'special_contact_requirements_flag',
        'special_contact_requirements_details'  => 'special_contact_requirements_details',
        'sms_opt_out'                           => 'sms_opt_out',
        'do_not_contact'                        => 'do_not_contact',
        'ifa_referred'                          => 'ifa_referred',
        'ifa_refer_opted_out'                   => 'ifa_refer_opted_out',
        'client_in_dispute'                     => 'client_in_dispute',
        'sar_sent'                              => 'sar_sent',
        'sar_received'                          => 'sar_received',
        'fee_capping_flag'                      => 'fee_capping_flag',
        'settlement_to_date_raw'                => 'settlement_to_date_raw',
        'settlement_to_date_new'                => 'settlement_to_date_new',
        'projected_future_settlement'           => 'projected_future_settlement',
        'quantum'                               => 'quantum',
        'cpfc_fee_percentage'                   => 'cpfc_fee_percentage',
        'cpfc_fee_total_nett'                   => 'cpfc_fee_total_nett',
        'our_fees_percentage_raw'               => 'our_fees_percentage_raw',
        'atc_received'                          => 'atc_received',
        'issued_tob_date'                       => 'issued_tob_date',
        'tob_in_date'                           => 'tob_in_date',
        'ifa_referred_date'                     => 'ifa_referred_date',
        'application_submitted_date'            => 'application_submitted_date',
        'date_requested_evidence'               => 'date_requested_evidence',
        'fos_allocated_date'                    => 'fos_allocated_date',
        'fos_app_submitted_date'                => 'fos_app_submitted_date',
        'fos_app_written_sent_to_client_date'   => 'fos_app_written_sent_to_client_date',
        'fos_complaint_letter_sent_to_client_date' => 'fos_complaint_letter_sent_to_client_date',
        'fos_initial_complaint_sent_date'       => 'fos_initial_complaint_sent_date',
        'fos_under_review_with_investigator_date' => 'fos_under_review_with_investigator_date',
        'fscs_allocated_date'                   => 'fscs_allocated_date',
        'sar_received_date'                     => 'sar_received_date',
        'sar_reviewed_scanned_date'             => 'sar_reviewed_scanned_date',
        'application_out_for_signature_date'    => 'application_out_for_signature_date',
        'allocated_to_investigator_date'        => 'allocated_to_investigator_date',
    ];

    public function handle(): int {
        $path = $this->argument('path');
        $this->dryRun = $this->option('dry-run');

        if (!file_exists($path)) {
            $this->error("File not found: {$path}");
            return self::FAILURE;
        }

        if ($this->dryRun) {
            $this->warn('[DRY RUN] No changes will be written to the database.');
        }

        $this->info("Starting pension case import from: {$path}");

        try {
            $csv = Reader::createFromPath($path, 'r');
            $csv->setHeaderOffset(0);

            foreach ($csv->getRecords() as $index => $row) {
                $rowNum = $index + 2; // 1-based, offset for header

                try {
                    $this->processRow($row, $rowNum);
                } catch (Throwable $e) {
                    $this->summary['failed']++;
                    $this->error("  Row {$rowNum} failed: " . $e->getMessage());
                    Log::error("ImportPensionCases row {$rowNum} failed", [
                        'error' => $e->getMessage(),
                        'row' => $row,
                    ]);
                }
            }
        } catch (Throwable $e) {
            $this->error("Import aborted: " . $e->getMessage());
            return self::FAILURE;
        }

        $this->printSummary();
        $this->logSummary();

        return self::SUCCESS;
    }

    protected function processRow(array $row, int $rowNum): void {
        $cleaned = $this->cleanRow($row);

        // Resolve client
        $client = $this->resolveClient($cleaned);

        if (!$client) {
            $this->warn("  Row {$rowNum}: Could not resolve client - skipping.");
            $this->summary['skipped']++;
            return;
        }

        // Build case data
        $caseData = $this->buildCaseData($cleaned, $client->id);

        if ($this->dryRun) {
            $this->line("  Row {$rowNum}: Would create/update case for client #{$client->id}");
            $this->summary['created']++;
            return;
        }

        DB::beginTransaction();
        try {
            $case = $this->upsertCase($caseData, $cleaned);

            // Import inline comment if present
            $commentText = $cleaned['comment_text'] ?? $cleaned['comments'] ?? $cleaned['comment'] ?? null;
            if ($commentText) {
                PensionCaseComment::create([
                    'pension_case_id' => $case->id,
                    'comment_text' => $commentText,
                    'comment_created_at' => now(),
                ]);
                $this->summary['comments_imported']++;
            }

            DB::commit();
        } catch (Throwable $e) {
            DB::rollBack();
            throw $e;
        }
    }

    protected function resolveClient(array $cleaned): ?Client {
        // Try CMC reference first
        if (!empty($cleaned['cmc_client_reference'])) {
            $client = Client::where('reference', $cleaned['cmc_client_reference'])->first();
            if ($client) {
                return $client;
            }
        }

        // Try by NI number via existing pension case
        if (!empty($cleaned['national_insurance'])) {
            $existingCase = PensionCase::where('national_insurance', $cleaned['national_insurance'])->first();
            if ($existingCase) {
                return $existingCase->client;
            }
        }

        return null;
    }

    protected function upsertCase(array $caseData, array $cleaned): PensionCase {
        $case = null;

        // Match by sf_case_id
        if (!empty($caseData['sf_case_id'])) {
            $case = PensionCase::where('sf_case_id', $caseData['sf_case_id'])->first();
        }

        // Match by case_number
        if (!$case && !empty($caseData['case_number'])) {
            $case = PensionCase::where('case_number', $caseData['case_number'])->first();
        }

        if ($case) {
            $case->update($caseData);
            $this->summary['updated']++;
        } else {
            $case = PensionCase::create($caseData);
            $this->summary['created']++;
        }

        return $case;
    }

    protected function buildCaseData(array $cleaned, int $clientId): array {
        $data = ['client_id' => $clientId];

        foreach ($this->fieldMap as $csvKey => $modelKey) {
            if (!array_key_exists($csvKey, $cleaned)) {
                continue;
            }

            $value = $cleaned[$csvKey];

            if (in_array($modelKey, $this->booleanFields)) {
                $value = $this->cleanBoolean($value);
            } elseif (in_array($modelKey, $this->dateFields)) {
                $value = $this->cleanDate($value);
            } elseif (in_array($modelKey, $this->decimalFields)) {
                $value = $this->cleanDecimal($value);
            }

            // Only set if not already set from a higher-priority alias
            if (!isset($data[$modelKey])) {
                $data[$modelKey] = $value;
            }
        }

        return $data;
    }

    // -----------------------------------------------------------------------
    // Row & value cleaning
    // -----------------------------------------------------------------------

    protected function cleanRow(array $row): array {
        return collect($row)->mapWithKeys(function ($value, $key) {
            return [
                $this->cleanHeader($key) => $this->cleanValue($value)
            ];
        })->toArray();
    }

    protected function cleanHeader(string $header): string {
        return strtolower(trim(str_replace([' ', '-'], '_', $header)));
    }

    protected function cleanValue($value): mixed {
        if (is_null($value)) {
            return null;
        }

        $value = trim((string)$value);

        return $value === '' ? null : $value;
    }

    protected function cleanBoolean($value): ?bool {
        if (is_null($value)) {
            return null;
        }

        return match (strtolower(trim((string)$value))) {
            'yes', '1', 'true', 'y' => true,
            'no', '0', 'false', 'n' => false,
            default => null,
        };
    }

    protected function cleanDate($value): ?string {
        if (!$value) {
            return null;
        }

        $value = trim((string)$value);

        if ($value === '') {
            return null;
        }

        $formats = ['d/m/Y', 'd-m-Y', 'Y-m-d', 'd/n/Y', 'm/d/Y'];

        foreach ($formats as $format) {
            try {
                return Carbon::createFromFormat($format, $value)->format('Y-m-d');
            } catch (Exception) {
                // try next format
            }
        }

        try {
            return Carbon::parse($value)->format('Y-m-d');
        } catch (Exception) {
            return null;
        }
    }

    protected function cleanDecimal($value): ?string {
        if (!$value) {
            return null;
        }

        // Strip currency symbols and thousands separators
        $value = preg_replace('/[£$€,]/', '', trim((string)$value));
        $value = trim($value);

        return is_numeric($value) ? $value : null;
    }

    // -----------------------------------------------------------------------
    // Reporting
    // -----------------------------------------------------------------------

    protected function printSummary(): void {
        $this->newLine();
        $this->info('Import complete.');
        $this->table(
            ['Metric', 'Count'],
            collect($this->summary)->map(fn($v, $k) => [ucwords(str_replace('_', ' ', $k)), $v])->values()->toArray()
        );
    }

    protected function logSummary(): void {
        $logPath = storage_path('logs/pension-cases-import-' . now()->format('Y-m-d') . '.log');
        $lines = ["Pension Case Import - " . now()->toDateTimeString()];
        foreach ($this->summary as $key => $val) {
            $lines[] = "  {$key}: {$val}";
        }
        file_put_contents($logPath, implode(PHP_EOL, $lines) . PHP_EOL, FILE_APPEND);
    }
}
