<?php

namespace App\Services\Courier;

use App\Models\Courier;
use App\Models\CourierReconciliationImport;
use App\Models\CourierReconciliationRow;
use App\Models\OrderCourierShipment;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Facades\DB;

class CourierReconciliationService
{
    public function importCsv(Courier $courier, UploadedFile $file, array $data, ?int $adminId = null): CourierReconciliationImport
    {
        return DB::transaction(function () use ($courier, $file, $data, $adminId) {
            $import = CourierReconciliationImport::create([
                'courier_id' => $courier->id,
                'import_date' => $data['import_date'] ?? now()->toDateString(),
                'source' => 'csv',
                'reference_no' => $data['reference_no'] ?? null,
                'file_name' => $file->getClientOriginalName(),
                'created_by_admin_id' => $adminId,
            ]);

            $handle = fopen($file->getRealPath(), 'r');
            $headers = [];
            $rowNo = 0;
            $summary = [
                'total_rows' => 0,
                'matched_rows' => 0,
                'mismatch_rows' => 0,
                'missing_rows' => 0,
                'duplicate_rows' => 0,
            ];
            $seenParcels = [];

            while (($row = fgetcsv($handle)) !== false) {
                $rowNo++;

                if ($rowNo === 1) {
                    $headers = array_map(fn ($header) => $this->normalizeHeader($header), $row);
                    continue;
                }

                if (count(array_filter($row, fn ($value) => $value !== null && $value !== '')) === 0) {
                    continue;
                }

                $summary['total_rows']++;
                $record = array_combine($headers, array_slice(array_pad($row, count($headers), null), 0, count($headers))) ?: [];
                $parcelId = $this->first($record, ['parcel_id', 'consignment_id', 'tracking_id', 'tracking_code']);
                $invoiceNo = $this->first($record, ['invoice_no', 'invoice', 'order_id']);
                $courierCod = $this->amount($this->first($record, ['cod_amount', 'collectable_amount', 'collected_amount']));
                $courierCharge = $this->amount($this->first($record, ['courier_charge', 'delivery_charge', 'charge']));
                $courierSettlement = $this->amount($this->first($record, ['settled_amount', 'settlement_amount', 'paid_amount', 'net_received']));
                $duplicate = $parcelId && in_array($parcelId, $seenParcels, true);

                if ($parcelId) {
                    $seenParcels[] = $parcelId;
                }

                $shipment = OrderCourierShipment::query()
                    ->where('courier_id', $courier->id)
                    ->where(function ($query) use ($parcelId, $invoiceNo) {
                        if ($parcelId) {
                            $query->orWhere('courier_consignment_id', $parcelId)
                                ->orWhere('courier_parcel_id', $parcelId)
                                ->orWhere('courier_tracking_id', $parcelId);
                        }

                        if ($invoiceNo && is_numeric($invoiceNo)) {
                            $query->orWhere('order_id', (int) $invoiceNo);
                        }
                    })
                    ->latest('id')
                    ->first();

                $status = 'matched';
                $reason = null;
                $systemCod = (float) ($shipment?->cod_collected_amount ?: $shipment?->collectable_amount ?: 0);
                $systemCharge = (float) ($shipment?->actual_delivery_cost ?: 0) + (float) ($shipment?->actual_cod_charge ?: 0) + (float) ($shipment?->actual_return_cost ?: 0);
                $systemSettlement = (float) ($shipment?->settled_amount ?: 0);

                if ($duplicate) {
                    $status = 'duplicate';
                    $reason = 'Duplicate parcel found in courier statement.';
                    $summary['duplicate_rows']++;
                } elseif (! $shipment) {
                    $status = 'missing_in_system';
                    $reason = 'Courier parcel not found in system.';
                    $summary['missing_rows']++;
                } elseif (abs($courierCod - $systemCod) > 0.01 || abs($courierCharge - $systemCharge) > 0.01 || ($courierSettlement > 0 && abs($courierSettlement - $systemSettlement) > 0.01)) {
                    $status = 'mismatch';
                    $reason = 'COD, charge, or settlement amount mismatch.';
                    $summary['mismatch_rows']++;
                } else {
                    $summary['matched_rows']++;
                }

                CourierReconciliationRow::create([
                    'courier_reconciliation_import_id' => $import->id,
                    'courier_id' => $courier->id,
                    'order_courier_shipment_id' => $shipment?->id,
                    'row_no' => $rowNo,
                    'parcel_id' => $parcelId,
                    'invoice_no' => $invoiceNo,
                    'courier_cod_amount' => $courierCod,
                    'system_cod_amount' => $systemCod,
                    'courier_charge_amount' => $courierCharge,
                    'system_charge_amount' => $systemCharge,
                    'courier_settlement_amount' => $courierSettlement,
                    'system_settlement_amount' => $systemSettlement,
                    'status' => $status,
                    'mismatch_reason' => $reason,
                    'raw_payload' => $record,
                ]);
            }

            if (is_resource($handle)) {
                fclose($handle);
            }

            $import->forceFill($summary + ['summary' => $summary])->save();

            return $import->fresh(['courier', 'rows.shipment']);
        });
    }

    private function normalizeHeader(?string $header): string
    {
        return strtolower(trim(preg_replace('/[^a-zA-Z0-9]+/', '_', (string) $header), '_'));
    }

    private function first(array $record, array $keys): mixed
    {
        foreach ($keys as $key) {
            if (isset($record[$key]) && $record[$key] !== '') {
                return $record[$key];
            }
        }

        return null;
    }

    private function amount(mixed $value): float
    {
        if (is_numeric($value)) {
            return (float) $value;
        }

        $normalized = preg_replace('/[^0-9.\-]/', '', (string) $value);

        return is_numeric($normalized) ? (float) $normalized : 0;
    }
}
