<?php
/*
 * Copyright 2026 Alessandro Chiapparini <sprint_planer_web@chiapparini.org>
 * SPDX-License-Identifier: Apache-2.0
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * See the LICENSE file in the project root for the full license text.
 */

declare(strict_types=1);

namespace App\Services\Import;

use App\Domain\Import\ParsedAssignment;
use App\Domain\Import\ParsedSheet;
use App\Domain\Import\ParsedTask;
use App\Domain\Import\ParsedWeek;
use App\Domain\Import\ParsedWorker;
use DateTimeImmutable;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception as ReaderException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use RuntimeException;

/**
 * Parse the team's "Tool_Sprint Planning" workbook into ParsedSheet[].
 *
 * Layout assumptions (locked down from doc/Tool_Sprint Planning.xlsx — see
 * SPEC.md §9 Phase 20 for the full coordinate map):
 *
 *   Arbeitstage block (left):
 *     C6 = "Arbeitstage", E6.. = max working days per week
 *     C7 = "Datum",       E7.. = week-start date labels
 *     C8 = "KW",          E8.. = ISO calendar-week numbers
 *     C9..Cn = worker names; E.. days/week per worker; J = RTB; K = Σ
 *     The "Reserven" row sits below the worker block: J{r} = "Reserven", K{r} = fraction.
 *
 *   Tasks block (right, starts at column M):
 *     Q4..  worker-name FORMULAS (=C9, =C10, …) — authoritative task-column
 *           → Arbeitstage worker mapping (skips Arbeitstage gaps).
 *     Row 9 = display header (M="To Do", N="Owner", O="Prio", P="Tot",
 *           Q.. = worker labels, sometimes drifted from row 4's formula
 *           result — we trust row 4).
 *     Row 10..n: M=title, N=owner, O=priority, P=Σ, Q.. days+colour.
 *
 * Year inference: the workbook only stores DD.MM in row 7, so we derive the
 * sprint start date from KW (row 8) + the year whose corresponding ISO-week
 * Monday is closest to today (looking ±1 year). The wizard preview lets the
 * operator override the inferred start date before commit.
 */
final class XlsxSprintImporter
{
    private const MAX_WORKER_ROWS = 30;
    private const MAX_TASK_ROWS   = 200;
    private const MAX_WEEK_COLS   = 12;
    private const MAX_TASK_COLS   = 40;

    private const FIRST_WEEK_COL = 'E';   // E.. holds week values
    private const FIRST_WORKER_ROW = 9;
    private const RTB_COL          = 'J';
    private const RESERVEN_VALUE_COL = 'K';
    private const TASK_TITLE_COL = 'M';
    private const TASK_OWNER_COL = 'N';
    private const TASK_PRIO_COL  = 'O';
    private const FIRST_TASK_COL = 'Q';
    private const FIRST_TASK_ROW = 10;
    private const HEADER_ROW_FOR_TASK_COLS = 4;
    private const HEADER_ROW_LITERAL = 9;

    /**
     * @return list<ParsedSheet>
     * @throws RuntimeException on unreadable file or empty workbook
     */
    public function parse(string $filePath): array
    {
        if (!is_file($filePath) || !is_readable($filePath)) {
            throw new RuntimeException("Cannot read file: {$filePath}");
        }

        try {
            $reader = IOFactory::createReader('Xlsx');
            // We need cell styles to read fill colours; loadAllSheets default.
            $reader->setReadDataOnly(false);
            $reader->setIncludeCharts(false);
            $book = $reader->load($filePath);
        } catch (ReaderException $e) {
            throw new RuntimeException('Not a readable XLSX: ' . $e->getMessage(), 0, $e);
        }

        $out = [];
        foreach ($book->getWorksheetIterator() as $ws) {
            $out[] = $this->parseSheet($ws);
        }

        if ($out === []) {
            throw new RuntimeException('Workbook has no sheets');
        }
        return $out;
    }

    private function parseSheet(Worksheet $ws): ParsedSheet
    {
        $warnings = [];

        // 1. Discover the week columns: E6.., stop at first empty cell.
        $weekCols = $this->scanRowColumns($ws, 6, self::FIRST_WEEK_COL, self::MAX_WEEK_COLS);
        // 2. Build ParsedWeek list, pulling max_working_days from row 6, KW from row 8, label from row 7.
        $weeks = [];
        $weekColIndex = [];
        $sortOrder = 1;
        foreach ($weekCols as $col) {
            $maxDaysRaw = self::numericOrNull($ws->getCell($col . '6')->getValue());
            $kwRaw      = self::numericOrNull($ws->getCell($col . '8')->getValue());
            $labelRaw   = $ws->getCell($col . '7')->getValue();
            $maxDays    = $maxDaysRaw === null ? 0 : (int) round($maxDaysRaw);
            $kw         = $kwRaw === null ? 0 : (int) round($kwRaw);
            $label      = is_scalar($labelRaw) ? (string) $labelRaw : '';
            if ($maxDays > 5) {
                $maxDays = 5;
                $warnings[] = "Week {$sortOrder}: max_working_days clamped to 5.";
            }
            if ($maxDays < 0) {
                $maxDays = 0;
            }
            $weeks[] = new ParsedWeek(
                sortOrder:         $sortOrder,
                kw:                $kw,
                dateLabel:         $label,
                maxWorkingDays:    $maxDays,
                inferredStartDate: $kw > 0 ? $this->isoWeekToDate($kw) : null,
            );
            $weekColIndex[$sortOrder] = $col;
            $sortOrder++;
        }

        // Sprint-level start/end inferred from the first/last weeks.
        [$startDate, $endDate] = $this->inferSprintRange($weeks);

        // 3. Walk worker rows starting at row 9. Stop at the Reserven row, or
        //    when col C has been empty for more than GAP_TOLERANCE consecutive
        //    rows (the sample workbook leaves a blank row between Titus and
        //    Suzan in Sprint 2 — we tolerate that without truncating).
        $reserveFraction = 0.2;
        $reserveSeen     = false;
        $workers         = [];
        $consecutiveEmpty = 0;
        $gapTolerance     = 2;
        for ($i = 0; $i < self::MAX_WORKER_ROWS; $i++) {
            $r = self::FIRST_WORKER_ROW + $i;
            $cName = $ws->getCell('C' . $r)->getValue();
            $jVal  = $ws->getCell(self::RTB_COL . $r)->getValue();

            if (is_string($jVal) && trim($jVal) !== '' && self::ciEquals($jVal, 'Reserven')) {
                $reserveSeen = true;
                $kVal = self::numericOrNull($ws->getCell(self::RESERVEN_VALUE_COL . $r)->getValue());
                if ($kVal !== null) {
                    $reserveFraction = max(0.0, min(1.0, (float) $kVal));
                }
                break; // worker block ends at Reserven
            }

            $name = is_scalar($cName) ? trim((string) $cName) : '';
            if ($name === '') {
                $consecutiveEmpty++;
                if ($consecutiveEmpty > $gapTolerance && $workers !== []) {
                    break;
                }
                continue;
            }
            $consecutiveEmpty = 0;

            $daysPerWeek = [];
            foreach ($weekColIndex as $sortIdx => $col) {
                $v = self::numericOrNull($ws->getCell($col . $r)->getValue());
                $daysPerWeek[$sortIdx] = $v === null ? 0.0 : (float) $v;
            }
            $rtb = self::numericOrNull($ws->getCell(self::RTB_COL . $r)->getValue());
            $workers[] = new ParsedWorker(
                name:        $name,
                daysPerWeek: $daysPerWeek,
                rtb:         $rtb === null ? 0.0 : max(0.0, min(1.0, (float) $rtb)),
            );
        }

        // 4. Build task-column → worker-name map from row 4 formulas (preferring
        //    the cached calculated value; fall back to the row-9 literal).
        $taskCols = $this->scanRowColumns($ws, self::HEADER_ROW_FOR_TASK_COLS, self::FIRST_TASK_COL, self::MAX_TASK_COLS);
        $taskColToWorker = [];
        foreach ($taskCols as $col) {
            $name = $this->resolveTaskColumnWorker(
                $ws,
                $col,
                self::HEADER_ROW_FOR_TASK_COLS,
                self::HEADER_ROW_LITERAL,
            );
            if ($name !== null && $name !== '') {
                $taskColToWorker[$col] = $name;
            }
        }
        if ($taskColToWorker === []) {
            $warnings[] = 'No task-block worker columns detected; the Tasks header at row 4 looks empty.';
        }

        // 5. Walk task rows from row 10 down. Tolerate up to GAP_TOLERANCE
        //    consecutive empty title cells before declaring the block done —
        //    Sprint 2's row 13 is a deliberate visual gap between two task
        //    groups in the source workbook.
        $tasks = [];
        $consecutiveEmptyTaskRows = 0;
        $taskGapTolerance = 2;
        for ($i = 0; $i < self::MAX_TASK_ROWS; $i++) {
            $r = self::FIRST_TASK_ROW + $i;
            $title = $ws->getCell(self::TASK_TITLE_COL . $r)->getValue();
            $title = is_scalar($title) ? trim((string) $title) : '';
            if ($title === '') {
                $consecutiveEmptyTaskRows++;
                if ($consecutiveEmptyTaskRows > $taskGapTolerance) {
                    break;
                }
                continue;
            }
            $consecutiveEmptyTaskRows = 0;

            $owner = $ws->getCell(self::TASK_OWNER_COL . $r)->getValue();
            $owner = is_scalar($owner) && trim((string) $owner) !== '' ? trim((string) $owner) : null;
            $prioRaw = self::numericOrNull($ws->getCell(self::TASK_PRIO_COL . $r)->getValue());
            $prio = $prioRaw === null ? 2 : (int) round($prioRaw);
            if ($prio !== 1 && $prio !== 2) {
                // Default missing or unrecognised priorities to "nice to have" (2).
                $warnings[] = "Task '{$title}': priority '{$prio}' coerced to 2.";
                $prio = 2;
            }

            $assignments = [];
            foreach ($taskColToWorker as $col => $workerName) {
                $cell = $ws->getCell($col . $r);
                $val  = self::numericOrNull($cell->getValue());
                $argb = $this->cellFillArgb($cell);
                $status = XlsxColorClassifier::classify($argb);
                $days = $val === null ? 0.0 : (float) $val;
                if ($days > 0 || $status !== \App\Domain\TaskAssignment::STATUS_ZUGEWIESEN) {
                    $assignments[] = new ParsedAssignment(
                        workerName: $workerName,
                        days:       max(0.0, $days),
                        status:     $status,
                        argbHex:    $argb,
                    );
                }
            }
            $tasks[] = new ParsedTask(
                title:       $title,
                ownerName:   $owner,
                priority:    $prio,
                assignments: $assignments,
            );
        }

        return new ParsedSheet(
            sheetName:         $ws->getTitle(),
            weeks:             $weeks,
            workers:           $workers,
            tasks:             $tasks,
            reserveFraction:   $reserveFraction,
            inferredStartDate: $startDate,
            inferredEndDate:   $endDate,
            warnings:          $warnings,
        );
    }

    /**
     * Scan a row leftward from $startCol; return the column letters that hold
     * a non-null value, stopping at the first empty cell.
     *
     * @return list<string>
     */
    private function scanRowColumns(Worksheet $ws, int $row, string $startCol, int $maxCols): array
    {
        $out = [];
        $colIdx = Coordinate::columnIndexFromString($startCol);
        for ($i = 0; $i < $maxCols; $i++) {
            $col = Coordinate::stringFromColumnIndex($colIdx + $i);
            $v = $ws->getCell($col . $row)->getValue();
            // Treat both null and empty-string as a sentinel for "block ends here",
            // but allow a tiny gap of 1 (tolerant): if next col has a value, keep going.
            if ($v === null || (is_string($v) && trim($v) === '')) {
                // peek one ahead — gap tolerance of 1
                $nextCol = Coordinate::stringFromColumnIndex($colIdx + $i + 1);
                $next = $ws->getCell($nextCol . $row)->getValue();
                if ($next === null || (is_string($next) && trim($next) === '')) {
                    break;
                }
                continue;
            }
            $out[] = $col;
        }
        return $out;
    }

    /**
     * Resolve a task-block worker column header. Row 4 carries =C{n} formulas
     * whose cached value is the canonical worker name; if that is unavailable
     * for any reason, fall back to the row-9 typed literal.
     */
    private function resolveTaskColumnWorker(
        Worksheet $ws,
        string $col,
        int $formulaRow,
        int $literalRow,
    ): ?string {
        $cellF = $ws->getCell($col . $formulaRow);
        $cached = $cellF->getOldCalculatedValue();
        if (is_scalar($cached) && trim((string) $cached) !== '') {
            return trim((string) $cached);
        }
        $rawF = $cellF->getValue();
        if (is_scalar($rawF) && !is_string($rawF) || (is_string($rawF) && !str_starts_with($rawF, '='))) {
            // Plain literal in row 4.
            $s = trim((string) $rawF);
            if ($s !== '') {
                return $s;
            }
        }
        $litRaw = $ws->getCell($col . $literalRow)->getValue();
        $lit = is_scalar($litRaw) ? trim((string) $litRaw) : '';
        return $lit === '' ? null : $lit;
    }

    /**
     * Read a cell's fill colour as ARGB (e.g. "FF00B050"). Returns null when
     * the cell has no user-applied solid fill (theme defaults, no fill, or
     * non-solid pattern) so the colour classifier treats it as "no status".
     */
    private function cellFillArgb($cell): ?string
    {
        $style = $cell->getStyle();
        $fill  = $style->getFill();
        if ($fill->getFillType() !== Fill::FILL_SOLID) {
            return null;
        }
        $color = $fill->getStartColor();
        $argb  = $color->getARGB();
        if (!is_string($argb) || $argb === '' || $argb === '00000000') {
            return null;
        }
        // Theme-derived fills round-trip through getARGB() as the resolved RGB,
        // but workbook-chrome theme colours (banner / header bands) end up
        // saturation-low or near-white, which the classifier already filters.
        return strtoupper($argb);
    }

    /**
     * Pick the year whose ISO-week Monday for $kw is closest to today.
     * Returns Y-m-d.
     */
    private function isoWeekToDate(int $kw, ?DateTimeImmutable $today = null): string
    {
        $today ??= new DateTimeImmutable('today');
        $cy = (int) $today->format('o'); // ISO week-numbering year
        $best = null;
        $bestDist = PHP_INT_MAX;
        foreach ([$cy - 1, $cy, $cy + 1] as $y) {
            try {
                $d = (new DateTimeImmutable())->setISODate($y, $kw, 1)->setTime(0, 0);
            } catch (\Throwable) {
                continue;
            }
            $dist = abs((int) $today->diff($d)->format('%r%a'));
            if ($dist < $bestDist) {
                $bestDist = $dist;
                $best = $d;
            }
        }
        return ($best ?? $today)->format('Y-m-d');
    }

    /**
     * Sprint-level start (Monday of first week) and end (Sunday of last week).
     *
     * @param list<ParsedWeek> $weeks
     * @return array{?string,?string}
     */
    private function inferSprintRange(array $weeks): array
    {
        if ($weeks === []) {
            return [null, null];
        }
        $first = $weeks[0];
        $last  = $weeks[count($weeks) - 1];
        if ($first->inferredStartDate === null || $last->inferredStartDate === null) {
            return [null, null];
        }
        $startD = DateTimeImmutable::createFromFormat('Y-m-d', $first->inferredStartDate);
        $lastD  = DateTimeImmutable::createFromFormat('Y-m-d', $last->inferredStartDate);
        if ($startD === false || $lastD === false) {
            return [null, null];
        }
        $endD = $lastD->modify('+6 days');
        return [$startD->format('Y-m-d'), $endD->format('Y-m-d')];
    }

    private static function numericOrNull(mixed $v): ?float
    {
        if (is_int($v) || is_float($v)) {
            return (float) $v;
        }
        if (is_string($v) && $v !== '') {
            $t = trim($v);
            if (is_numeric($t)) {
                return (float) $t;
            }
        }
        return null;
    }

    private static function ciEquals(string $a, string $b): bool
    {
        return strcasecmp(trim($a), trim($b)) === 0;
    }
}
