<?php

namespace App\Libraries\DigitMotor;

use App\Models\MotorMasterAddonAgeLimitModel;
use App\Models\MotorMasterDocTypeModel;
use App\Models\MotorMasterImportLogModel;
use App\Models\MotorMasterNcbModel;
use App\Models\MotorMasterNomineeRelationModel;
use App\Models\MotorMasterPincodeModel;
use App\Models\MotorMasterPreviousInsurerModel;
use App\Models\MotorMasterPreviousPolicyTypeModel;
use App\Models\MotorMasterProductModel;
use App\Models\MotorMasterRtoModel;
use App\Models\MotorMasterStateModel;
use App\Models\MotorMasterSubProductModel;
use App\Models\MotorMasterVehicleModel;
use App\Models\MotorMasterVoluntaryDeductibleModel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

/**
 * Imports Digit kit Masters Excel files into motor_master_* tables.
 */
class DigitMasterImportService
{
    protected string $mastersPath;
    protected $db;
    protected MotorMasterImportLogModel $logModel;

    /** @var callable|null */
    protected $progressCallback;

    public function __construct(?string $mastersPath = null)
    {
        $this->mastersPath = rtrim($mastersPath ?: '', '/');
        $this->db          = db_connect();
        $this->logModel    = new MotorMasterImportLogModel();
    }

    public function setProgressCallback(?callable $cb): self
    {
        $this->progressCallback = $cb;
        return $this;
    }

    public function createTables(string $sqlFile): void
    {
        if (!is_file($sqlFile)) {
            throw new \RuntimeException('SQL file not found: ' . $sqlFile);
        }
        $sql = file_get_contents($sqlFile);
        foreach (array_filter(array_map('trim', explode(';', $sql))) as $stmt) {
            if ($stmt === '' || str_starts_with($stmt, '--')) {
                continue;
            }
            // skip pure comment blocks
            $clean = preg_replace('/^--.*$/m', '', $stmt);
            $clean = trim($clean ?? '');
            if ($clean === '') {
                continue;
            }
            $this->db->query($clean);
        }
    }

    /**
     * @return array<string, array{rows:int,status:string,message?:string}>
     */
    public function importAll(array $only = []): array
    {
        $map = [
            'products'           => fn () => $this->importProducts(),
            'previous_insurers'  => fn () => $this->importPreviousInsurers(),
            'ncb'                => fn () => $this->importNcb(),
            'voluntary_deductible'=> fn () => $this->importVoluntaryDeductible(),
            'previous_policy_type'=> fn () => $this->importPreviousPolicyType(),
            'doc_types'          => fn () => $this->importDocTypes(),
            'nominee_relations'  => fn () => $this->importNomineeRelations(),
            'states'             => fn () => $this->importStates(),
            'sub_products'       => fn () => $this->importSubProducts(),
            'addon_age_limits'   => fn () => $this->importAddonAgeLimits(),
            'pincodes'           => fn () => $this->importPincodes(),
            'rtos'               => fn () => $this->importRtos(),
            'vehicles'           => fn () => $this->importVehicles(),
        ];

        if ($only) {
            $map = array_intersect_key($map, array_flip($only));
        }

        $results = [];
        foreach ($map as $key => $fn) {
            $this->progress('Importing ' . $key . '...');
            try {
                $rows = $fn();
                $results[$key] = ['rows' => $rows, 'status' => 'OK'];
                $this->logImport($key, $rows, 'OK');
                $this->progress($key . ': ' . $rows . ' rows');
            } catch (\Throwable $e) {
                $results[$key] = ['rows' => 0, 'status' => 'FAILED', 'message' => $e->getMessage()];
                $this->logImport($key, 0, 'FAILED', $e->getMessage());
                $this->progress($key . ' FAILED: ' . $e->getMessage());
            }
        }

        return $results;
    }

    protected function progress(string $message): void
    {
        if ($this->progressCallback) {
            ($this->progressCallback)($message);
        }
    }

    protected function logImport(string $key, int $rows, string $status, ?string $message = null): void
    {
        try {
            $this->logModel->insert([
                'master_key'    => $key,
                'source_file'   => $this->mastersPath,
                'rows_upserted' => $rows,
                'status'        => $status,
                'message'       => $message,
                'imported_at'   => date('Y-m-d H:i:s'),
            ]);
        } catch (\Throwable $e) {
            // ignore log failures
        }
    }

    protected function findFile(array $candidates): string
    {
        foreach ($candidates as $name) {
            $path = $this->mastersPath . '/' . $name;
            if (is_file($path)) {
                return $path;
            }
        }
        throw new \RuntimeException('Master file not found. Tried: ' . implode(', ', $candidates));
    }

    protected function loadSheet(string $path, string $sheetName = null, int $maxRows = 0): array
    {
        $reader = IOFactory::createReaderForFile($path);
        $reader->setReadDataOnly(true);
        if (method_exists($reader, 'setReadEmptyCells')) {
            $reader->setReadEmptyCells(false);
        }

        if ($sheetName) {
            $reader->setLoadSheetsOnly([$sheetName]);
        }

        $spreadsheet = $reader->load($path);
        $sheet = $sheetName
            ? $spreadsheet->getSheetByName($sheetName)
            : $spreadsheet->getActiveSheet();

        if (!$sheet) {
            $sheet = $spreadsheet->getSheet(0);
        }

        $rows = $sheet->toArray(null, true, true, false);
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        if ($maxRows > 0 && count($rows) > $maxRows) {
            $rows = array_slice($rows, 0, $maxRows);
        }

        return $rows;
    }

    protected function upsertBatch(string $table, array $rows, array $updateCols): int
    {
        if (!$rows) {
            return 0;
        }

        $count = 0;
        foreach (array_chunk($rows, 500) as $chunk) {
            $this->db->table($table)->upsertBatch($chunk);
            $count += count($chunk);
        }
        return $count;
    }

    protected function cell($row, int $idx): string
    {
        $v = $row[$idx] ?? '';
        if ($v === null) {
            return '';
        }
        return trim((string) $v);
    }

    public function importPreviousInsurers(): int
    {
        $path = $this->findFile(['Motor Prevoius insurer List.xlsx', 'Motor Previous insurer List.xlsx']);
        $rows = $this->loadSheet($path);
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $i => $row) {
            if ($i === 0) {
                continue;
            }
            $code = $this->cell($row, 0);
            $name = $this->cell($row, 1);
            if ($code === '' || !preg_match('/^\d+$/', $code) || $name === '' || strcasecmp($name, 'Name') === 0) {
                continue;
            }
            $out[] = [
                'insurer_code' => str_pad($code, 3, '0', STR_PAD_LEFT),
                'insurer_name' => $name,
                'is_active'    => 1,
                'imported_at'  => $now,
            ];
        }

        return $this->upsertBatch('motor_master_previous_insurer', $out, ['insurer_name', 'is_active', 'imported_at']);
    }

    public function importProducts(): int
    {
        // Seed from known Digit product matrix (also present in Product Code Description workbook headers)
        $now = date('Y-m-d H:i:s');
        $products = [
            ['20201', '2W Comprehensive', '2W'],
            ['20202', '2W TP only', '2W'],
            ['20203', '2W SAOD', '2W'],
            ['20101', '4W Comprehensive', '4W'],
            ['20102', '4W TP only', '4W'],
            ['20103', '4W SAOD', '4W'],
            ['20301', 'CV Comprehensive', 'CV'],
            ['20302', 'CV TP only', 'CV'],
        ];

        $out = [];
        foreach ($products as [$code, $name, $class]) {
            $out[] = [
                'product_code'  => $code,
                'product_name'  => $name,
                'vehicle_class' => $class,
                'is_active'     => 1,
                'imported_at'   => $now,
            ];
        }

        return $this->upsertBatch('motor_master_product', $out, ['product_name', 'vehicle_class', 'is_active', 'imported_at']);
    }

    public function importNcb(): int
    {
        $path = $this->findFile(['NCB Master.xlsx']);
        $rows = $this->loadSheet($path);
        $now  = date('Y-m-d H:i:s');
        $out  = [];
        $order = 0;

        foreach ($rows as $i => $row) {
            $code = strtoupper($this->cell($row, 0));
            if ($code === '' || $code === 'NCB') {
                continue;
            }
            $out[] = [
                'ncb_code'    => $code,
                'sort_order'  => $order++,
                'is_active'   => 1,
                'imported_at' => $now,
            ];
        }

        return $this->upsertBatch('motor_master_ncb', $out, ['sort_order', 'is_active', 'imported_at']);
    }

    public function importVoluntaryDeductible(): int
    {
        $path = $this->findFile(['Volunatry Deductible Master.xlsx', 'Voluntary Deductible Master.xlsx']);
        $rows = $this->loadSheet($path);
        $now  = date('Y-m-d H:i:s');
        $out  = [];
        $order = 0;

        foreach ($rows as $row) {
            $code = strtoupper($this->cell($row, 0));
            if ($code === '' || str_contains($code, 'VOLUNTARY')) {
                continue;
            }
            $out[] = [
                'deductible_code' => $code,
                'sort_order'      => $order++,
                'is_active'       => 1,
                'imported_at'     => $now,
            ];
        }

        return $this->upsertBatch('motor_master_voluntary_deductible', $out, ['sort_order', 'is_active', 'imported_at']);
    }

    public function importPreviousPolicyType(): int
    {
        $path = $this->findFile(['Previous_Policy_Type.xlsx']);
        $rows = $this->loadSheet($path, 'Description');
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $i => $row) {
            $code = strtoupper(trim($this->cell($row, 0)));
            $desc = $this->cell($row, 1);
            if ($i === 0 || $code === '' || $code === 'DOMNAME') {
                continue;
            }
            $out[] = [
                'policy_type_code' => $code,
                'description'      => $desc,
                'is_active'        => 1,
                'imported_at'      => $now,
            ];
        }

        return $this->upsertBatch('motor_master_previous_policy_type', $out, ['description', 'is_active', 'imported_at']);
    }

    public function importDocTypes(): int
    {
        $path = $this->findFile(['Doc Type Master.xlsx', 'Doc Type Master1.xlsx']);
        $rows = $this->loadSheet($path, 'KYC doc Type');
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $i => $row) {
            $code = strtoupper($this->cell($row, 0));
            $type = strtoupper($this->cell($row, 1));
            if ($i === 0 || $code === '' || $code === 'DOCUMENT_NUMBER') {
                continue;
            }
            $out[] = [
                'doc_code'    => $code,
                'doc_type'    => $type,
                'is_active'   => 1,
                'imported_at' => $now,
            ];
        }

        return $this->upsertBatch('motor_master_doc_type', $out, ['doc_type', 'is_active', 'imported_at']);
    }

    public function importNomineeRelations(): int
    {
        $path = $this->findFile(['Nominee Master.xlsx']);
        $rows = $this->loadSheet($path);
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $row) {
            $code = strtoupper(trim($this->cell($row, 0)));
            if ($code === '' || str_contains($code, 'NOMINEE')) {
                continue;
            }
            $out[] = [
                'relation_code' => $code,
                'is_active'     => 1,
                'imported_at'   => $now,
            ];
        }

        return $this->upsertBatch('motor_master_nominee_relation', $out, ['is_active', 'imported_at']);
    }

    public function importStates(): int
    {
        $path = $this->findFile(['State Code Master (1).xlsx', 'State Code Master.xlsx']);
        $rows = $this->loadSheet($path);
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $i => $row) {
            $code = preg_replace('/\s+/', '', $this->cell($row, 0));
            $name = trim($this->cell($row, 1));
            if ($i === 0 || $code === '' || stripos($code, 'State') !== false) {
                continue;
            }
            $out[] = [
                'state_code'  => $code,
                'state_name'  => $name,
                'is_active'   => 1,
                'imported_at' => $now,
            ];
        }

        return $this->upsertBatch('motor_master_state', $out, ['state_name', 'is_active', 'imported_at']);
    }

    public function importSubProducts(): int
    {
        $path = $this->findFile(['Motor Product Code Description (1).xlsx', 'Motor Product Code Description.xlsx']);
        $rows = $this->loadSheet($path, 'SubinsuranceProductcode');
        $now  = date('Y-m-d H:i:s');
        $out  = [];
        $businessType = 'NEW';

        foreach ($rows as $i => $row) {
            $col0 = $this->cell($row, 0);
            $label = $this->cell($row, 1);
            $code  = $this->cell($row, 2);

            if ($col0 !== '') {
                if (stripos($col0, 'rollover') !== false || stripos($col0, 'renewal') !== false) {
                    $businessType = 'ROLLOVER';
                } elseif (stripos($col0, 'new') !== false) {
                    $businessType = 'NEW';
                }
            }

            if ($label === '' || $code === '' || stripos($label, 'Product') === 0) {
                continue;
            }

            // code cell may contain notes like "51 (51- 5 yr...)"
            if (preg_match('/^([A-Za-z0-9]+)/', $code, $m)) {
                $code = $m[1];
            }

            $out[] = [
                'business_type'    => $businessType,
                'product_label'    => $label,
                'sub_product_code' => $code,
                'is_active'        => 1,
                'imported_at'      => $now,
            ];
        }

        // unique by business+label+code
        $uniq = [];
        foreach ($out as $row) {
            $k = $row['business_type'] . '|' . $row['product_label'] . '|' . $row['sub_product_code'];
            $uniq[$k] = $row;
        }

        if (!$uniq) {
            return 0;
        }

        $this->db->table('motor_master_sub_product')->truncate();
        $this->db->table('motor_master_sub_product')->insertBatch(array_values($uniq));
        return count($uniq);
    }

    public function importAddonAgeLimits(): int
    {
        $path = $this->findFile(['Motor Product Code Description (1).xlsx', 'Motor Product Code Description.xlsx']);
        $rows = $this->loadSheet($path, 'Addons age limit');
        $now  = date('Y-m-d H:i:s');
        $out  = [];

        foreach ($rows as $i => $row) {
            $name = $this->cell($row, 0);
            if ($i === 0 || $name === '' || stripos($name, 'Add On') === 0) {
                continue;
            }
            $out[] = [
                'addon_name'   => $name,
                'age_limit_4w' => $this->cell($row, 1) ?: null,
                'age_limit_2w' => $this->cell($row, 2) ?: null,
                'is_active'    => 1,
                'imported_at'  => $now,
            ];
        }

        if (!$out) {
            return 0;
        }

        $this->db->table('motor_master_addon_age_limit')->truncate();
        $this->db->table('motor_master_addon_age_limit')->insertBatch($out);
        return count($out);
    }

    public function importPincodes(): int
    {
        $path = $this->findFile(['Pin Code and RTO Master_2023.xlsx']);
        $rows = $this->loadSheet($path, 'PIN CODE');
        $now  = date('Y-m-d H:i:s');
        $out  = [];
        $seen = [];

        foreach ($rows as $i => $row) {
            $pin = preg_replace('/\D+/', '', $this->cell($row, 0));
            if ($i === 0 || strlen($pin) !== 6 || isset($seen[$pin])) {
                continue;
            }
            $seen[$pin] = true;
            $out[] = [
                'pincode'     => $pin,
                'city'        => $this->cell($row, 1) ?: null,
                'district'    => $this->cell($row, 2) ?: null,
                'street'      => $this->cell($row, 3) ?: null,
                'taluk'       => $this->cell($row, 4) ?: null,
                'segment'     => $this->cell($row, 5) ?: null,
                'state_code'  => null,
                'is_active'   => 1,
                'imported_at' => $now,
            ];
        }

        return $this->upsertBatch('motor_master_pincode', $out, [
            'city', 'district', 'street', 'taluk', 'segment', 'is_active', 'imported_at',
        ]);
    }

    public function importRtos(): int
    {
        $path = $this->findFile(['Pin Code and RTO Master_2023.xlsx']);
        $rows = $this->loadSheet($path, 'RTO MASTER');
        $now  = date('Y-m-d H:i:s');
        $out  = [];
        $seen = [];

        foreach ($rows as $i => $row) {
            $city = $this->cell($row, 0);
            $rto  = strtoupper(preg_replace('/\s+/', '', $this->cell($row, 1)));
            if ($i === 0 || $rto === '' || $rto === 'RTO' || isset($seen[$rto])) {
                continue;
            }
            $seen[$rto] = true;
            $out[] = [
                'rto_code'    => $rto,
                'city_state'  => $city ?: null,
                'is_active'   => 1,
                'imported_at' => $now,
            ];
        }

        return $this->upsertBatch('motor_master_rto', $out, ['city_state', 'is_active', 'imported_at']);
    }

    public function importVehicles(): int
    {
        $path = $this->findFile(['Vehicle Master_New (2).xlsx', 'Vehicle Master_New.xlsx', 'Vehicle Master.xlsx']);
        $this->progress('Loading vehicle master workbook (large file)...');

        $reader = IOFactory::createReaderForFile($path);
        $reader->setReadDataOnly(true);
        if (method_exists($reader, 'setReadEmptyCells')) {
            $reader->setReadEmptyCells(false);
        }

        $spreadsheet = $reader->load($path);
        $sheet = $spreadsheet->getSheet(0);
        $highestRow = (int) $sheet->getHighestDataRow();
        $this->progress('Vehicle master rows to scan: ' . $highestRow);

        $now = date('Y-m-d H:i:s');
        $batch = [];
        $count = 0;
        $chunkSize = 500;

        for ($r = 2; $r <= $highestRow; $r++) {
            $code = trim((string) $sheet->getCell('A' . $r)->getValue());
            if ($code === '') {
                continue;
            }

            $batch[] = [
                'vehicle_code'         => $code,
                'make'                 => trim((string) $sheet->getCell('B' . $r)->getValue()),
                'model'                => trim((string) $sheet->getCell('C' . $r)->getValue()),
                'variant'              => trim((string) $sheet->getCell('D' . $r)->getValue()) ?: null,
                'body_type'            => trim((string) $sheet->getCell('E' . $r)->getValue()) ?: null,
                'seating_capacity'     => $this->toInt($sheet->getCell('F' . $r)->getValue()),
                'power'                => $this->toFloat($sheet->getCell('G' . $r)->getValue()),
                'cubic_capacity'       => $this->toFloat($sheet->getCell('H' . $r)->getValue()),
                'gross_vehicle_weight' => $this->toFloat($sheet->getCell('I' . $r)->getValue()),
                'fuel_type'            => trim((string) $sheet->getCell('J' . $r)->getValue()) ?: null,
                'no_of_wheels'         => $this->toInt($sheet->getCell('K' . $r)->getValue()),
                'abs'                  => substr(trim((string) $sheet->getCell('L' . $r)->getValue()), 0, 1) ?: null,
                'air_bags'             => $this->toInt($sheet->getCell('M' . $r)->getValue()),
                'length_m'             => $this->toFloat($sheet->getCell('N' . $r)->getValue()),
                'ex_showroom_price'    => $this->toFloat($sheet->getCell('O' . $r)->getValue()),
                'price_year'           => $this->toInt($sheet->getCell('P' . $r)->getValue()),
                'production_status'    => trim((string) $sheet->getCell('Q' . $r)->getValue()) ?: null,
                'manufacturing'        => trim((string) $sheet->getCell('R' . $r)->getValue()) ?: null,
                'vehicle_type'         => trim((string) $sheet->getCell('S' . $r)->getValue()) ?: null,
                'is_active'            => 1,
                'imported_at'          => $now,
            ];

            if (count($batch) >= $chunkSize) {
                $this->db->table('motor_master_vehicle')->upsertBatch($batch);
                $count += count($batch);
                $batch = [];
                if ($count % 5000 === 0) {
                    $this->progress('Vehicles upserted: ' . $count);
                }
            }
        }

        if ($batch) {
            $this->db->table('motor_master_vehicle')->upsertBatch($batch);
            $count += count($batch);
        }

        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        return $count;
    }

    protected function toInt($v): ?int
    {
        if ($v === null || $v === '') {
            return null;
        }
        if (!is_numeric($v)) {
            return null;
        }
        return (int) $v;
    }

    protected function toFloat($v): ?float
    {
        if ($v === null || $v === '') {
            return null;
        }
        if (!is_numeric($v)) {
            return null;
        }
        return (float) $v;
    }
}