<?php

namespace PortalPeople\gforce\components;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PortalPeople\shippingcosts\models\ColumnMapForm;
use PortalPeople\shippingcosts\models\Shippingcosts;
use PortalPeople\shippingcosts\services\PostcodeExpansionService;
use backend\models\Accounts;
use Yii;

/**
 * RateSheetImporter
 *
 * Reads a GForce pricing spreadsheet (xlsx) and imports each row as an
 * individual Shippingcosts record — one record per postcode, with no
 * consolidation step.
 *
 * Expected column layout (detected from the header row):
 *   Provider  – haulier name → resolved to Accounts.intid
 *   ERPID     – ignored (informational)
 *   Service   – shipping type (ECON / PREM / etc.)
 *   PostCode  – individual postcode or range; expanded if needed
 *   Concat    – formula column, ignored
 *   <prices>  – any remaining columns whose headers look like price bands
 *               (e.g. "Qtr", "1 x Half", "2 x Full", "Micro", "Mini QP")
 *
 * Rows where PostCode is empty or null are skipped.
 * Price cells containing "N/A", null, or "" are omitted from the price list.
 *
 * Upsert strategy (per haulier + service + postcode):
 *   - If a matching record already exists → update its prices in place.
 *   - If no matching record is found     → create a new one.
 */
class RateSheetImporter
{
    /** Status applied to every new Shippingcosts record. */
    public string $defaultStatus = 'active';

    /** How many save retries before giving up on a single row. */
    public int $maxRetries = 2;

    /** Maximum number of preview rows rendered on the sample screen. */
    public int $defaultPreviewLimit = 200;

    /** @var array<string, int|null>  provider name → Accounts.intid */
    private array $haulierCache = [];

    // ─── Public API ────────────────────────────────────────────────────────

    /**
     * Import a GForce xlsx file.
     *
     * @param  string   $filePath  Absolute path to the uploaded xlsx file.
     * @return array{
     *     saved:   int,
     *     updated: int,
     *     skipped: int,
     *     errors:  list<array{row: int, postcode: string, errors: array}>
     * }
     *
     * @throws \RuntimeException if the file cannot be loaded.
     */
    public function import(string $filePath): array
    {
        $result = $this->processSheet($filePath, true, 0);

        return [
            'saved' => $result['saved'],
            'updated' => $result['updated'],
            'skipped' => $result['skipped'],
            'errors' => $result['errors'],
        ];
    }

    /**
     * Build a preview of what will be created/updated without saving changes.
     *
     * @param string $filePath Absolute path to the uploaded xlsx file.
     * @param int    $limit    Maximum number of sample rows in preview output.
     *
     * @return array{
     *     sourceRows: int,
     *     expandedRows: int,
     *     toCreate: int,
     *     toUpdate: int,
     *     skipped: int,
    *     sample: list<array{
    *         row: int,
    *         postcode: string,
    *         service: string,
    *         haulierid: int|null,
    *         priceCount: int,
    *         action: string
    *     }>
     * }
     */
    public function preview(string $filePath, int $limit = 0): array
    {
        $sampleLimit = $limit > 0 ? $limit : $this->defaultPreviewLimit;

        $result = $this->processSheet($filePath, false, $sampleLimit);

        return [
            'sourceRows' => $result['sourceRows'],
            'expandedRows' => $result['expandedRows'],
            'toCreate' => $result['toCreate'],
            'toUpdate' => $result['toUpdate'],
            'skipped' => $result['skipped'],
            'sample' => $result['sample'],
        ];
    }

    /**
     * Shared parser/processor used for both import and preview.
     *
     * @return array{
     *     saved: int,
     *     updated: int,
     *     skipped: int,
     *     errors: list<array{row: int, postcode: string, errors: array}>,
     *     sourceRows: int,
     *     expandedRows: int,
     *     toCreate: int,
     *     toUpdate: int,
    *     sample: list<array{
    *         row: int,
    *         postcode: string,
    *         service: string,
    *         haulierid: int|null,
    *         priceCount: int,
    *         action: string
    *     }>
     * }
     */
    private function processSheet(string $filePath, bool $persist, int $sampleLimit): array
    {
        if (!file_exists($filePath)) {
            throw new \RuntimeException("RateSheetImporter: file not found: {$filePath}");
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet       = $spreadsheet->getActiveSheet();
        $lastRow     = $sheet->getHighestDataRow();
        $lastCol     = $sheet->getHighestDataColumn();

        // ── Read header row ──────────────────────────────────────────────
        $headers     = [];
        $colIterator = $sheet->getRowIterator(1, 1);
        foreach ($colIterator as $row) {
            foreach ($row->getCellIterator('A', $lastCol) as $cell) {
                $headers[$cell->getColumn()] = trim((string) ($cell->getValue() ?? ''));
            }
        }

        // ── Identify column roles ───────────────────────────────────────
        $colProvider  = null;
        $colService   = null;
        $colPostcode  = null;
        $priceColumns = []; // col letter => header label

        foreach ($headers as $col => $header) {
            $lower = strtolower($header);

            if (in_array($lower, ['provider', 'haulier', 'carrier'], true)) {
                $colProvider = $col;
                continue;
            }

            if (in_array($lower, ['service', 'type', 'service type'], true)) {
                $colService = $col;
                continue;
            }

            if (in_array($lower, ['postcode', 'post code', 'post_code', 'postal code'], true)) {
                $colPostcode = $col;
                continue;
            }

            // Anything left that looks like a price band
            if ($header !== '' && $this->looksLikePriceHeader($header)) {
                $priceColumns[$col] = $header;
            }
        }

        if ($colPostcode === null) {
            throw new \RuntimeException(
                'RateSheetImporter: could not find a PostCode column in the header row. '
                . 'Expected a header named "PostCode", "Post Code", or similar.'
            );
        }

        // ── Process data rows ────────────────────────────────────────────
        $results = [
            'saved' => 0,
            'updated' => 0,
            'skipped' => 0,
            'errors' => [],
            'sourceRows' => 0,
            'expandedRows' => 0,
            'toCreate' => 0,
            'toUpdate' => 0,
            'sample' => [],
        ];

        for ($rowNum = 2; $rowNum <= $lastRow; $rowNum++) {
            $rawPostcode = trim((string) ($sheet->getCell($colPostcode . $rowNum)->getValue() ?? ''));

            // Skip blank postcode rows
            if ($rawPostcode === '' || strtolower($rawPostcode) === 'n/a') {
                $results['skipped']++;
                continue;
            }

            $results['sourceRows']++;

            // Resolve provider → haulier ID
            $haulierid = null;
            if ($colProvider !== null) {
                $providerVal = trim((string) ($sheet->getCell($colProvider . $rowNum)->getValue() ?? ''));
                $haulierid   = $this->resolveHaulier($providerVal);
            }

            // Resolve service type
            $serviceType = '';
            if ($colService !== null) {
                $serviceType = trim((string) ($sheet->getCell($colService . $rowNum)->getValue() ?? ''));
            }

            // Collect prices (skip N/A / empty cells)
            $prices = [];
            foreach ($priceColumns as $col => $label) {
                $val = $sheet->getCell($col . $rowNum)->getValue();

                if ($val === null || $val === '' || strtolower(trim((string) $val)) === 'n/a') {
                    continue;
                }

                $meta     = ColumnMapForm::parsePriceHeader($label);
                $prices[] = [
                    'label' => $meta['label'],
                    'size'  => $meta['size'],
                    'qty'   => $meta['qty'] ?? 1,
                    'price' => (float) $val,
                ];
            }

            // Expand postcode ranges (e.g. "AB1-5") into individual codes
            $expandedCodes = PostcodeExpansionService::expandPostcodes($rawPostcode);

            if (empty($expandedCodes)) {
                $expandedCodes = [$rawPostcode];
            }

            // Create / update one record per expanded postcode
            foreach ($expandedCodes as $postcode) {
                $postcode = trim($postcode);
                if ($postcode === '') {
                    continue;
                }

                $results['expandedRows']++;

                if ($persist) {
                    $outcome = $this->upsert($haulierid, $serviceType, $postcode, $prices, $rowNum);

                    if ($outcome === 'saved') {
                        $results['saved']++;
                    } elseif ($outcome === 'updated') {
                        $results['updated']++;
                    } else {
                        $results['skipped']++;
                        $results['errors'][] = $outcome;
                    }
                } else {
                    $exists = $this->findExisting($haulierid, $serviceType, $postcode) !== null;
                    $action = $exists ? 'update' : 'create';

                    if ($exists) {
                        $results['toUpdate']++;
                    } else {
                        $results['toCreate']++;
                    }

                    if ($sampleLimit > 0 && count($results['sample']) < $sampleLimit) {
                        $results['sample'][] = [
                            'row' => $rowNum,
                            'postcode' => $postcode,
                            'service' => $serviceType,
                            'haulierid' => $haulierid,
                            'priceCount' => count($prices),
                            'action' => $action,
                        ];
                    }
                }
            }
        }

        return $results;
    }

    // ─── Upsert logic ──────────────────────────────────────────────────────

    /**
     * Find an existing record matching haulier + service + postcode, or create
     * a fresh one.  Returns 'saved', 'updated', or an error array.
     *
     * @return 'saved'|'updated'|array{row: int, postcode: string, errors: array}
     */
    private function upsert(
        ?int $haulierid,
        string $serviceType,
        string $postcode,
        array $prices,
        int $rowNum
    ): mixed {
        $model = $this->findExisting($haulierid, $serviceType, $postcode);
        $isNew  = $model === null;

        if ($isNew) {
            $model = new Shippingcosts();
            $model->status = $this->defaultStatus;
        }

        $model->haulierid    = $haulierid;
        $model->type         = $serviceType;
        $model->postcodes    = $postcode;
        $model->prices       = $prices;
        $model->no_of_prices = count($prices);

        $attempts   = 0;
        $lastErrors = [];

        do {
            $attempts++;
            try {
                if ($model->save()) {
                    return $isNew ? 'saved' : 'updated';
                }
                $lastErrors = $model->getErrors();
            } catch (\Throwable $e) {
                $lastErrors = ['exception' => [$e->getMessage()]];
                Yii::warning(
                    "RateSheetImporter: row {$rowNum} postcode {$postcode} attempt {$attempts} threw – "
                    . $e->getMessage(),
                    __CLASS__
                );
            }
            $model->clearErrors();
        } while ($attempts <= $this->maxRetries);

        return ['row' => $rowNum, 'postcode' => $postcode, 'errors' => $lastErrors];
    }

    /**
     * Find an existing Shippingcosts record by haulier + type + exact postcode.
     * The postcode column may contain a single value or a ||‑separated list that
     * starts/ends with the target postcode.
     */
    private function findExisting(?int $haulierid, string $serviceType, string $postcode): ?Shippingcosts
    {
        $query = Shippingcosts::find()
            ->where(['type' => $serviceType])
            ->andWhere(
                $haulierid !== null
                    ? ['haulierid' => $haulierid]
                    : ['haulierid' => null]
            );

        // Exact match first
        $exact = (clone $query)->andWhere(['postcodes' => $postcode])->one();
        if ($exact !== null) {
            return $exact;
        }

        // Also try rows where this postcode appears inside a ||‑separated list
        $like = (clone $query)
            ->andWhere(['like', 'postcodes', $postcode])
            ->one();

        return $like ?: null;
    }

    // ─── Haulier resolution ────────────────────────────────────────────────

    private function resolveHaulier(string $value): ?int
    {
        if ($value === '') {
            return null;
        }

        if (is_numeric($value)) {
            return (int) $value;
        }

        if (array_key_exists($value, $this->haulierCache)) {
            return $this->haulierCache[$value];
        }

        $account = Accounts::find()
            ->where(['name' => $value, 'type' => 'haulier'])
            ->one();

        $intid = $account ? (int) $account->intid : null;
        $this->haulierCache[$value] = $intid;

        return $intid;
    }

    // ─── Header detection ──────────────────────────────────────────────────

    private function looksLikePriceHeader(string $header): bool
    {
        $lower = strtolower($header);

        // Known size keywords
        $sizeWords = array_keys(ColumnMapForm::SIZE_MAP);
        foreach ($sizeWords as $word) {
            if (str_contains($lower, $word)) {
                return true;
            }
        }

        // "Micro", "Mini", "Euro" variants
        if (preg_match('/\b(micro|mini|euro)\b/i', $header)) {
            return true;
        }

        // Patterns like "1 X", "2x", "3 X"
        return (bool) preg_match('/\b\d+\s*[xX]\b/', $header);
    }
}
