<?php

namespace PortalPeople\shippingcosts\components;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * PriceConsolidator
 *
 * Consolidates a pricing spreadsheet by grouping rows that share the same
 * Provider (A), ERPID (B), Service (C) and all Price columns (F–AD).
 * Matching rows have their PostCodes (D) merged as "AB10||AB11||...".
 * All other columns are preserved from the first matching row.
 *
 * Column layout (1-based):
 *   A  = 1  Provider     ─┐
 *   B  = 2  ERPID         │  group key
 *   C  = 3  Service      ─┘
 *   D  = 4  PostCode       ← merged with ||
 *   E  = 5  passthrough   (kept from first row)
 *   F–AD (6–30) prices    ─── group key
 *
 * Usage:
 *   $consolidator = new PriceConsolidator('/path/to/input.xlsx');
 *   $consolidator->load();
 *
 *   // Get as array
 *   $data = $consolidator->toArray();
 *
 *   // Save as Excel
 *   $consolidator->toExcel('/path/to/output.xlsx');
 *
 *   // Download directly to browser
 *   $consolidator->download('consolidated.xlsx');
 */
class PriceConsolidator
{
    // ─── Column map (1-based, all configurable) ───────────────────────────

    /** First column that is part of the group identity key (before postcode) */
    public int $colKeyStart  = 1;   // A – Provider

    /** Postcode column – the one that gets merged with || */
    public int $colPostcode  = 4;   // D

    /** First price column */
    public int $colPriceMin  = 6;   // F

    /** Last price column */
    public int $colPriceMax  = 30;  // AD

    /** Separator used when merging postcodes */
    public string $separator = '||';

    // ─── Internal state ────────────────────────────────────────────────────

    private string  $inputPath;

    /** @var list<string> */
    private array   $order   = [];

    /** @var array<string, array<int, mixed>> */
    private array   $groups  = [];

    /** @var array<int, mixed> */
    private array   $headers = [];

    private int     $inputRows  = 0;
    private int     $skippedRows = 0;

    // ─── Constructor ───────────────────────────────────────────────────────

    public function __construct(string $inputPath)
    {
        $this->inputPath = $inputPath;
    }

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

    /**
     * Load and consolidate the spreadsheet.
     * Must be called before toArray() or toExcel().
     *
     * @throws \RuntimeException if the file cannot be loaded.
     */
    public function load(): static
    {
        if (!file_exists($this->inputPath)) {
            throw new \RuntimeException("PriceConsolidator: file not found: {$this->inputPath}");
        }

        $spreadsheet = IOFactory::load($this->inputPath);
        $sheet       = $spreadsheet->getActiveSheet();
        $lastRow     = $sheet->getHighestDataRow();

        // Read header row
        $this->headers = [];
        for ($c = 1; $c <= $this->colPriceMax; $c++) {
            $this->headers[$c] = $sheet->getCell([$c, 1])->getValue();
        }

        $this->order      = [];
        $this->groups     = [];
        $this->inputRows  = 0;
        $this->skippedRows = 0;

        for ($row = 2; $row <= $lastRow; $row++) {
            $provider = trim((string) $sheet->getCell([$this->colKeyStart, $row])->getValue());
            $postcode = trim((string) $sheet->getCell([$this->colPostcode,  $row])->getValue());

            if ($provider === '' && $postcode === '') {
                $this->skippedRows++;
                continue;
            }

            $this->inputRows++;

            $groupKey = $this->buildGroupKey($sheet, $row);

            if (!isset($this->groups[$groupKey])) {
                $rowData = [];
                for ($c = 1; $c <= $this->colPriceMax; $c++) {
                    $rowData[$c] = $sheet->getCell([$c, $row])->getValue();
                }
                $this->groups[$groupKey] = $rowData;
                $this->order[]           = $groupKey;
            } else {
                $existing  = (string) ($this->groups[$groupKey][$this->colPostcode] ?? '');
                $postcodes = ($existing !== '') ? explode($this->separator, $existing) : [];

                if ($postcode !== '' && !in_array($postcode, $postcodes, true)) {
                    $postcodes[]                                        = $postcode;
                    $this->groups[$groupKey][$this->colPostcode] = implode($this->separator, $postcodes);
                }
            }
        }

        return $this;
    }

    /**
     * Return consolidated data as a plain PHP array.
     *
     * The first element is the header row (keyed by column index 1–colPriceMax).
     * Subsequent elements are data rows in the same format.
     *
     * @return list<array<int, mixed>>
     */
    public function toArray(): array
    {
        $this->assertLoaded();

        $rows = [$this->headers];

        foreach ($this->order as $key) {
            $rows[] = $this->groups[$key];
        }

        return $rows;
    }

    /**
     * Return consolidated data as an array keyed by the header names.
     * Useful for passing directly to GridView or similar.
     *
     * @return list<array<string, mixed>>
     */
    public function toNamedArray(): array
    {
        $this->assertLoaded();

        $out = [];
        foreach ($this->order as $key) {
            $row   = $this->groups[$key];
            $named = [];
            for ($c = 1; $c <= $this->colPriceMax; $c++) {
                $header        = (string) ($this->headers[$c] ?? "col{$c}");
                $named[$header] = $row[$c] ?? null;
            }
            $out[] = $named;
        }

        return $out;
    }

    /**
     * Write consolidated data to an Excel (.xlsx) file.
     *
     * @param string $outputPath  Absolute path for the output file.
     *                            Defaults to inputPath with _consolidated suffix.
     * @return string  The resolved output path.
     */
    public function toExcel(?string $outputPath = null): string
    {
        $this->assertLoaded();

        $outputPath ??= preg_replace('/(\.\w+)$/', '_consolidated$1', $this->inputPath);

        $out      = new Spreadsheet();
        $outSheet = $out->getActiveSheet();

        // Header row
        for ($c = 1; $c <= $this->colPriceMax; $c++) {
            $outSheet->setCellValue([$c, 1], $this->headers[$c] ?? '');
        }

        // Data rows
        $outRow = 2;
        foreach ($this->order as $key) {
            $rowData = $this->groups[$key];
            for ($c = 1; $c <= $this->colPriceMax; $c++) {
                $outSheet->setCellValue([$c, $outRow], $rowData[$c] ?? '');
            }
            $outRow++;
        }

        (new Xlsx($out))->save($outputPath);

        return $outputPath;
    }

    /**
     * Stream the consolidated Excel file directly to the browser.
     * Sends appropriate headers and exits.
     *
     * @param string $filename  Suggested download filename (no path).
     */
    public function download(string $filename = 'consolidated.xlsx'): void
    {
        $this->assertLoaded();

        // Write to a temp file then stream it
        $tmp = tempnam(sys_get_temp_dir(), 'pp_consolidator_') . '.xlsx';

        try {
            $this->toExcel($tmp);

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . rawurlencode($filename) . '"');
            header('Cache-Control: max-age=0');
            header('Content-Length: ' . filesize($tmp));

            readfile($tmp);
        } finally {
            if (file_exists($tmp)) {
                unlink($tmp);
            }
        }

        exit;
    }

    // ─── Stats ─────────────────────────────────────────────────────────────

    /** Total input data rows processed (excludes header and skipped blanks). */
    public function getInputRowCount(): int
    {
        return $this->inputRows;
    }

    /** Number of output (consolidated) rows. */
    public function getOutputRowCount(): int
    {
        return count($this->order);
    }

    /** Number of blank/skipped rows from the input. */
    public function getSkippedRowCount(): int
    {
        return $this->skippedRows;
    }

    /** Number of rows removed by consolidation. */
    public function getMergedRowCount(): int
    {
        return $this->inputRows - count($this->order);
    }

    // ─── Private helpers ───────────────────────────────────────────────────

    private function buildGroupKey(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet, int $row): string
    {
        $parts = [];

        // Identity columns before the postcode (A, B, C …)
        for ($c = $this->colKeyStart; $c < $this->colPostcode; $c++) {
            $parts[] = (string) $sheet->getCell([$c, $row])->getValue();
        }

        // Price columns (F–AD) – they must all match for a merge to happen
        for ($c = $this->colPriceMin; $c <= $this->colPriceMax; $c++) {
            $parts[] = (string) $sheet->getCell([$c, $row])->getValue();
        }

        return implode("\x00", $parts);
    }

    private function assertLoaded(): void
    {
        if (empty($this->order) && $this->inputRows === 0) {
            throw new \RuntimeException('PriceConsolidator: call load() before reading results.');
        }
    }
}
