sprint_planer_web/src/Controllers/ImportController.php

<?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\Controllers;

use App\Auth\SessionGuard;
use App\Domain\Import\ImportResult;
use App\Domain\Import\ParsedSheet;
use App\Domain\User;
use App\Http\Request;
use App\Http\Response;
use App\Http\View;
use App\Repositories\SprintRepository;
use App\Repositories\UserRepository;
use App\Services\AuditLogger;
use App\Services\Import\SprintImporter;
use App\Services\Import\XlsxSprintImporter;
use PDO;
use Throwable;

/**
 * Phase 20 — Two-step XLSX → Sprint import wizard.
 *
 *   GET  /sprints/import           upload form
 *   POST /sprints/import           parse, stash in session, redirect to preview
 *   GET  /sprints/import/{token}   preview + per-sheet target picker
 *   POST /sprints/import/{token}   commit selected sheets in one TX each,
 *                                  redirect to the first sprint with a flash.
 *
 * The uploaded XLSX is parsed once and the structured result is stored in
 * the session under a random token; the file itself is not persisted to disk.
 * Tokens expire after 30 minutes.
 */
final class ImportController
{
    public const SESSION_KEY = 'sp_imports';
    private const TTL_SECONDS = 1800;
    private const MAX_FILE_BYTES = 5 * 1024 * 1024;

    /**
     * R01-N14: hard cap on the JSON-encoded preview blob we stash in
     * `$_SESSION` between the upload and the commit step. The 5 MB
     * upload cap (`MAX_FILE_BYTES`) bounds raw bytes coming in, but
     * parsed XLSX expansion is unbounded — a hostile workbook with
     * many tabs could blow the session file size. Two megabytes is
     * roomy for a real planning workbook (the production
     * `Tool_Sprint Planning` one rarely exceeds a few hundred KB
     * serialised) and small enough that on-disk session IO stays
     * snappy.
     */
    public const MAX_SESSION_PAYLOAD_BYTES = 2 * 1024 * 1024;

    public function __construct(
        private readonly PDO              $pdo,
        private readonly UserRepository   $users,
        private readonly SprintRepository $sprints,
        private readonly XlsxSprintImporter $parser,
        private readonly SprintImporter   $committer,
        private readonly View             $view,
        private readonly AuditLogger      $audit,
    ) {
    }

    public function newForm(Request $req): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }

        return Response::html($this->view->render('sprints/import_upload', [
            'title'       => 'Import sprints',
            'currentUser' => $actor,
            'csrfToken'   => SessionGuard::csrfToken(),
            'error'       => $req->queryString('error'),
        ]));
    }

    public function upload(Request $req): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }
        if (!SessionGuard::verifyCsrf($req)) {
            return Response::text('CSRF token invalid', 403);
        }

        // Validate the upload up-front; fail-closed.
        if (!isset($_FILES['xlsx']) || !is_array($_FILES['xlsx'])) {
            return Response::redirect('/sprints/import?error=no_file');
        }
        $file = $_FILES['xlsx'];
        if (($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
            $code = self::uploadErrorCode((int) ($file['error'] ?? UPLOAD_ERR_NO_FILE));
            return Response::redirect('/sprints/import?error=' . $code);
        }
        $size = (int) ($file['size'] ?? 0);
        if ($size <= 0 || $size > self::MAX_FILE_BYTES) {
            return Response::redirect('/sprints/import?error=size');
        }
        $tmp = (string) ($file['tmp_name'] ?? '');
        if ($tmp === '' || !is_uploaded_file($tmp)) {
            return Response::redirect('/sprints/import?error=upload_invalid');
        }
        $orig = (string) ($file['name'] ?? '');
        if (!self::looksLikeXlsx($orig, $tmp)) {
            return Response::redirect('/sprints/import?error=not_xlsx');
        }

        try {
            $sheets = $this->parser->parse($tmp);
        } catch (Throwable) {
            return Response::redirect('/sprints/import?error=parse_failed');
        }

        // R01-N14: enforce the per-token serialised cap. We encode once
        // for the size check and stash the array form (cheap to read,
        // no decode hop on preview/commit). A parse that explodes past
        // the cap is rejected outright; nothing lands in the session.
        $sheetsArr     = array_map(fn(ParsedSheet $s) => $s->toArray(), $sheets);
        $payloadBytes  = self::encodedPayloadBytes($sheetsArr);
        if ($payloadBytes > self::MAX_SESSION_PAYLOAD_BYTES) {
            return Response::redirect('/sprints/import?error=too_large_payload');
        }

        $token = bin2hex(random_bytes(16));
        SessionGuard::start();
        if (!isset($_SESSION[self::SESSION_KEY]) || !is_array($_SESSION[self::SESSION_KEY])) {
            $_SESSION[self::SESSION_KEY] = [];
        }
        $_SESSION[self::SESSION_KEY][$token] = [
            'created_at'    => time(),
            'sheets'        => $sheetsArr,
            'file_name'     => basename($orig),
            'payload_bytes' => $payloadBytes,
        ];
        $this->pruneSessionImports($req, $actor);

        return Response::redirect('/sprints/import/' . $token);
    }

    public function preview(Request $req, array $params): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }
        $token = (string) ($params['token'] ?? '');
        $entry = $this->loadSessionEntry($token, $req, $actor);
        if ($entry === null) {
            return Response::redirect('/sprints/import?error=expired');
        }

        $sheets = array_map(
            static fn(array $arr) => ParsedSheet::fromArray($arr),
            $entry['sheets'],
        );

        // Existing-sprint candidates for the per-sheet target picker: only
        // empty sprints (no weeks/workers/tasks) qualify. Spec §plan §2.
        $emptySprints = $this->emptySprintCandidates();

        // Pre-compute summary stats for the diff panel.
        $summaries = [];
        $existingWorkerFolds = $this->existingWorkerFolds();
        foreach ($sheets as $sheet) {
            $summaries[] = $this->summarise($sheet, $existingWorkerFolds);
        }

        return Response::html($this->view->render('sprints/import_preview', [
            'title'        => 'Import preview',
            'currentUser'  => $actor,
            'csrfToken'    => SessionGuard::csrfToken(),
            'token'        => $token,
            'fileName'     => (string) ($entry['file_name'] ?? ''),
            'sheets'       => $sheets,
            'summaries'    => $summaries,
            'emptySprints' => $emptySprints,
            'error'        => $req->queryString('error'),
        ]));
    }

    public function commit(Request $req, array $params): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }
        if (!SessionGuard::verifyCsrf($req)) {
            return Response::text('CSRF token invalid', 403);
        }
        $token = (string) ($params['token'] ?? '');
        $entry = $this->loadSessionEntry($token, $req, $actor);
        if ($entry === null) {
            return Response::redirect('/sprints/import?error=expired');
        }
        $sheets = array_map(
            static fn(array $arr) => ParsedSheet::fromArray($arr),
            $entry['sheets'],
        );

        $results = [];
        foreach ($sheets as $idx => $sheet) {
            $skip = $req->postString("skip_{$idx}");
            if ($skip === '1') {
                continue;
            }
            $name      = $req->postString("name_{$idx}");
            $startDate = $req->postString("start_{$idx}");
            $endDate   = $req->postString("end_{$idx}");
            $target    = $req->postString("target_{$idx}");
            $existing  = $req->postString("existing_{$idx}");
            $existingId = ($target === 'existing' && $existing !== '' && ctype_digit($existing))
                ? (int) $existing
                : null;
            if ($name === '') {
                $name = $sheet->sheetName;
            }
            if ($startDate === '') {
                $startDate = $sheet->inferredStartDate ?? '';
            }
            if ($endDate === '') {
                $endDate = $sheet->inferredEndDate ?? '';
            }

            try {
                $results[] = $this->committer->commit(
                    sheet:             $sheet,
                    sprintName:        $name,
                    startDate:         $startDate,
                    endDate:           $endDate,
                    target:            $target === 'existing' ? 'existing' : 'new',
                    existingSprintId:  $existingId,
                    req:               $req,
                    actor:             $actor,
                );
            } catch (Throwable $e) {
                // Bail on first failure: prior sheets in this loop already
                // committed, but each is a self-contained transaction so the
                // user keeps the partial progress and can re-try the rest.
                SessionGuard::start();
                $_SESSION['flash_import_error'] = sprintf(
                    'Sheet "%s" failed: %s',
                    $sheet->sheetName,
                    $e->getMessage(),
                );
                return Response::redirect('/sprints/import/' . $token . '?error=commit');
            }
        }

        // Drop the session entry; the wizard is done.
        if (isset($_SESSION[self::SESSION_KEY][$token])) {
            unset($_SESSION[self::SESSION_KEY][$token]);
        }

        if ($results === []) {
            return Response::redirect('/sprints/import?error=nothing_selected');
        }

        // Land on the first imported sprint with an audible flash.
        SessionGuard::start();
        $_SESSION['flash_import_summary'] = self::summariseResults($results);
        return Response::redirect('/sprints/' . $results[0]->sprintId);
    }

    // ------------------------------------------------------------------ utils

    /** @return array{sheets: list<array<string,mixed>>, file_name: string, created_at: int}|null */
    private function loadSessionEntry(string $token, Request $req, User $actor): ?array
    {
        if (!preg_match('/^[0-9a-f]{32}$/', $token)) {
            return null;
        }
        SessionGuard::start();
        $bag = $_SESSION[self::SESSION_KEY] ?? [];
        if (!is_array($bag) || !isset($bag[$token]) || !is_array($bag[$token])) {
            return null;
        }
        $entry = $bag[$token];
        $createdAt = (int) ($entry['created_at'] ?? 0);
        if ($createdAt + self::TTL_SECONDS < time()) {
            // R01-N14: emit IMPORT_PREVIEW_ABANDONED for the token the
            // user just tried to use. They'll be redirected to the
            // upload form with `?error=expired`; the audit row makes the
            // expiry visible in `/audit` instead of disappearing
            // silently.
            $this->recordAbandonedImport($entry, $req, $actor);
            unset($_SESSION[self::SESSION_KEY][$token]);
            return null;
        }
        return $entry;
    }

    private function pruneSessionImports(Request $req, User $actor): void
    {
        SessionGuard::start();
        $bag = $_SESSION[self::SESSION_KEY] ?? [];
        if (!is_array($bag)) {
            return;
        }
        $cutoff = time() - self::TTL_SECONDS;
        foreach ($bag as $tok => $row) {
            if (!is_array($row)) {
                unset($_SESSION[self::SESSION_KEY][$tok]);
                continue;
            }
            if ((int) ($row['created_at'] ?? 0) < $cutoff) {
                // R01-N14: token aged out without a commit. Same audit
                // row as in `loadSessionEntry`.
                $this->recordAbandonedImport($row, $req, $actor);
                unset($_SESSION[self::SESSION_KEY][$tok]);
            }
        }
    }

    /**
     * R01-N14: write an IMPORT_PREVIEW_ABANDONED audit row for a
     * preview token that expired before being committed. `entity_type`
     * is `import_token` because no DB row backs the preview blob; the
     * row carries enough metadata (file name, age, sheet count,
     * payload size) for an admin reviewing `/audit` to reconstruct
     * what was abandoned.
     *
     * @param array<string,mixed> $entry
     */
    private function recordAbandonedImport(array $entry, Request $req, User $actor): void
    {
        $this->audit->recordForRequest(
            action:     'IMPORT_PREVIEW_ABANDONED',
            entityType: 'import_token',
            entityId:   null,
            before:     null,
            after:      self::abandonedAuditPayload($entry, time()),
            req:        $req,
            actor:      $actor,
        );
    }

    /** @return list<array{id:int,name:string,startDate:string,endDate:string}> */
    private function emptySprintCandidates(): array
    {
        $stmt = $this->pdo->query(
            'SELECT s.id, s.name, s.start_date, s.end_date
             FROM sprints s
             WHERE NOT EXISTS (SELECT 1 FROM sprint_weeks   WHERE sprint_id = s.id)
               AND NOT EXISTS (SELECT 1 FROM sprint_workers WHERE sprint_id = s.id)
               AND NOT EXISTS (SELECT 1 FROM tasks          WHERE sprint_id = s.id)
             ORDER BY s.start_date DESC, s.id DESC'
        );
        $out = [];
        foreach ($stmt as $row) {
            $out[] = [
                'id'        => (int) $row['id'],
                'name'      => (string) $row['name'],
                'startDate' => (string) $row['start_date'],
                'endDate'   => (string) $row['end_date'],
            ];
        }
        return $out;
    }

    /** @return array<string,bool> case-folded name => true */
    private function existingWorkerFolds(): array
    {
        $stmt = $this->pdo->query('SELECT name FROM workers');
        $out = [];
        foreach ($stmt as $row) {
            $out[SprintImporter::fold((string) $row['name'])] = true;
        }
        return $out;
    }

    /**
     * @param array<string,bool> $existingFolds
     * @return array<string,mixed>
     */
    private function summarise(ParsedSheet $sheet, array $existingFolds): array
    {
        $newWorkers = [];
        foreach ($sheet->workers as $w) {
            if (!isset($existingFolds[SprintImporter::fold($w->name)])) {
                $newWorkers[] = $w->name;
            }
        }
        $cells = 0;
        $byStatus = [
            'zugewiesen'    => 0,
            'gestartet'     => 0,
            'abgeschlossen' => 0,
            'abgebrochen'   => 0,
        ];
        foreach ($sheet->tasks as $t) {
            foreach ($t->assignments as $a) {
                if ($a->days > 0) {
                    $cells++;
                }
                $byStatus[$a->status] = ($byStatus[$a->status] ?? 0) + 1;
            }
        }
        return [
            'newWorkers'    => $newWorkers,
            'workerCount'   => count($sheet->workers),
            'taskCount'     => count($sheet->tasks),
            'weekCount'     => count($sheet->weeks),
            'cellCount'     => $cells,
            'statusCounts'  => $byStatus,
        ];
    }

    /** @param list<ImportResult> $results */
    private static function summariseResults(array $results): string
    {
        $parts = [];
        foreach ($results as $r) {
            $bits = ["{$r->workerCount} workers", "{$r->taskCount} tasks", "{$r->assignmentCellCount} cells"];
            if ($r->createdWorkers !== []) {
                $bits[] = count($r->createdWorkers) . ' new workers';
            }
            $parts[] = "“{$r->sprintName}” imported (" . implode(', ', $bits) . ').';
        }
        return implode(' ', $parts);
    }

    /**
     * R01-N14: serialised-byte estimate for a `ParsedSheet[]::toArray()`
     * payload. Pure, so the cap can be unit-tested without a real
     * upload + session round-trip.
     *
     * Encoding mirrors `AuditLogger::encodeJson`: UTF-8 plain, no
     * escaping of slashes — what the session bag *would* serialise to
     * if we were JSON-encoding it. PHP's session serialiser is
     * different (PHP-serialized format) but the JSON byte length is a
     * reliable proxy and matches the contract recorded in REVIEW_01.
     *
     * @param array<int,array<string,mixed>> $sheetsArr
     */
    public static function encodedPayloadBytes(array $sheetsArr): int
    {
        $json = json_encode(
            $sheetsArr,
            JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES,
        );
        return $json === false ? 0 : strlen($json);
    }

    /**
     * R01-N14: payload for the IMPORT_PREVIEW_ABANDONED audit row.
     * Keeps just enough metadata to reconstruct what was lost without
     * persisting any user task content.
     *
     * @param  array<string,mixed> $entry  the session entry as stashed
     *                                     by `upload()`
     * @return array<string,mixed>
     */
    public static function abandonedAuditPayload(array $entry, int $now): array
    {
        $createdAt = (int) ($entry['created_at'] ?? 0);
        $sheets    = (array) ($entry['sheets'] ?? []);
        // Missing `created_at` reads as 0; without this guard the
        // computed age would be the full unix-epoch offset (~50 yr),
        // which is alarming noise in `/audit` for what is just a
        // malformed entry. Same shape clamps clock-skew "future" rows
        // to 0 instead of negative.
        $age       = $createdAt > 0 ? max(0, $now - $createdAt) : 0;
        return [
            'file_name'     => (string) ($entry['file_name'] ?? ''),
            'sheet_count'   => count($sheets),
            'payload_bytes' => (int) ($entry['payload_bytes'] ?? 0),
            'age_seconds'   => $age,
            'created_at'    => $createdAt > 0 ? gmdate('Y-m-d\TH:i:s\Z', $createdAt) : '',
        ];
    }

    private static function looksLikeXlsx(string $origName, string $tmpPath): bool
    {
        if (!preg_match('/\.xlsx$/i', $origName)) {
            return false;
        }
        // XLSX is a ZIP container; the file's first 4 bytes start with PK\x03\x04 or PK\x05\x06.
        $fh = @fopen($tmpPath, 'rb');
        if ($fh === false) {
            return false;
        }
        $head = (string) fread($fh, 4);
        fclose($fh);
        if (strlen($head) !== 4) {
            return false;
        }
        return $head[0] === 'P' && $head[1] === 'K'
            && (($head[2] === "\x03" && $head[3] === "\x04")
             || ($head[2] === "\x05" && $head[3] === "\x06"));
    }

    private static function uploadErrorCode(int $code): string
    {
        return match ($code) {
            UPLOAD_ERR_INI_SIZE,
            UPLOAD_ERR_FORM_SIZE => 'too_big',
            UPLOAD_ERR_PARTIAL   => 'partial',
            UPLOAD_ERR_NO_FILE   => 'no_file',
            UPLOAD_ERR_NO_TMP_DIR,
            UPLOAD_ERR_CANT_WRITE,
            UPLOAD_ERR_EXTENSION => 'server',
            default              => 'unknown',
        };
    }
}