sprint_planer_web/src/Controllers/SprintController.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\SprintWeek;
use App\Domain\User;
use App\Http\Request;
use App\Http\Response;
use App\Http\View;
use InvalidArgumentException;
use App\Repositories\AppSettingsRepository;
use App\Repositories\SprintRepository;
use App\Repositories\SprintWeekRepository;
use App\Repositories\SprintWorkerDayRepository;
use App\Repositories\SprintWorkerRepository;
use App\Repositories\TaskAssignmentRepository;
use App\Repositories\TaskRepository;
use App\Repositories\UserRepository;
use App\Repositories\WorkerRepository;
use App\Services\AuditLogger;
use App\Services\CapacityCalculator;
use DateTimeImmutable;
use PDO;
use PDOException;
use Throwable;

final class SprintController
{
    /**
     * R01-N24: defence-in-depth cap on the batch JSON endpoints. The 1 MiB
     * `Request::MAX_BODY_BYTES` already gates the wire format; this cap
     * stops an attacker (or a buggy client) from packing tens of thousands
     * of small cells inside a still-under-1MiB body and pinning the DB on
     * a long upsert loop. Real workloads are nowhere near this limit:
     * `n_weeks` is capped at 26 in the schema and a sprint never carries
     * tens of admins-on-its-back, so 5000 cells in one PATCH is already
     * comfortably more than any genuine UI flow needs.
     */
    public const MAX_BATCH_ITEMS = 5000;

    public function __construct(
        private readonly PDO                       $pdo,
        private readonly UserRepository            $users,
        private readonly SprintRepository          $sprints,
        private readonly SprintWeekRepository      $weeks,
        private readonly SprintWorkerRepository    $sprintWorkers,
        private readonly SprintWorkerDayRepository $days,
        private readonly TaskRepository            $tasks,
        private readonly TaskAssignmentRepository  $assignments,
        private readonly WorkerRepository          $workers,
        private readonly AuditLogger               $audit,
        private readonly View                      $view,
        private readonly AppSettingsRepository     $appSettings,
    ) {
    }

    /** GET /sprints/new — admin-only form. */
    public function newForm(Request $req): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }

        return Response::html($this->view->render('sprints/new', [
            'title'       => 'New sprint',
            'currentUser' => $actor,
            'csrfToken'   => SessionGuard::csrfToken(),
            'error'       => $req->queryString('error'),
            'form'        => [
                'name'             => '',
                'start_date'       => '',
                'end_date'         => '',
                'reserve_fraction' => '20',
            ],
        ]));
    }

    /** POST /sprints — create sprint + materialise weeks in one tx. */
    public function create(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);
        }

        $name     = trim($req->postString('name'));
        $start    = $req->postString('start_date');
        $end      = $req->postString('end_date');
        // reserve_fraction submitted as a percentage (0..100) from the form.
        $reservePct = $req->postString('reserve_fraction');

        if ($name === '') {
            return Response::redirect('/sprints/new?error=name_required');
        }
        $startD = DateTimeImmutable::createFromFormat('Y-m-d', $start);
        $endD   = DateTimeImmutable::createFromFormat('Y-m-d', $end);
        if ($startD === false || $endD === false) {
            return Response::redirect('/sprints/new?error=dates_invalid');
        }
        if ($endD < $startD) {
            return Response::redirect('/sprints/new?error=dates_order');
        }
        if (!is_numeric($reservePct)) {
            return Response::redirect('/sprints/new?error=reserve_invalid');
        }
        $reserve = ((float) $reservePct) / 100.0;
        if ($reserve < 0.0 || $reserve > 1.0) {
            return Response::redirect('/sprints/new?error=reserve_out_of_range');
        }
        // Week count derives from the date range — same rule as PATCH /sprints/{id}.
        $nWeeks = self::weeksBetween($startD->format('Y-m-d'), $endD->format('Y-m-d'));
        if ($nWeeks > 26) {
            return Response::redirect('/sprints/new?error=dates_too_long');
        }

        $this->pdo->beginTransaction();
        try {
            $sprint = $this->sprints->create(
                name:            $name,
                startDate:       $startD->format('Y-m-d'),
                endDate:         $endD->format('Y-m-d'),
                reserveFraction: $reserve,
            );

            $this->audit->recordForRequest(
                action:     'CREATE',
                entityType: 'sprint',
                entityId:   $sprint->id,
                before:     null,
                after:      $sprint->toAuditSnapshot(),
                req:        $req,
                actor:      $actor,
            );

            $weeks = $this->sprints->materializeWeeks(
                $sprint->id,
                $startD->format('Y-m-d'),
                $nWeeks,
            );
            foreach ($weeks as $w) {
                $this->audit->recordForRequest(
                    action:     'CREATE',
                    entityType: 'sprint_week',
                    entityId:   $w['id'],
                    before:     null,
                    after:      ['sprint_id' => $sprint->id] + $w,
                    req:        $req,
                    actor:      $actor,
                );
            }

            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::redirect('/sprints/new?error=db_error');
        }

        return Response::redirect('/sprints/' . $sprint->id);
    }

    /** GET /sprints/{id} — main planning view (Section A Arbeitstage; tasks land in Phase 6). */
    public function show(Request $req, array $params): Response
    {
        $actor = SessionGuard::requireAuth($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }

        $id   = (int) $params['id'];
        $data = $this->loadSprintPage($id);
        if ($data === null) {
            return Response::text('Not Found', 404);
        }

        return Response::html($this->view->render('sprints/show', [
            'title'       => $data['sprint']->name,
            'currentUser' => $actor,
            'csrfToken'   => SessionGuard::csrfToken(),
        ] + $data));
    }

    /**
     * GET /sprints/{id}/present — big-screen / beamer presentation view.
     * Strips chrome + Arbeitstage + capacity summary; renders only the
     * task-list toolbar + table full-viewport.
     */
    public function present(Request $req, array $params): Response
    {
        $actor = SessionGuard::requireAuth($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }

        $id   = (int) $params['id'];
        $data = $this->loadSprintPage($id);
        if ($data === null) {
            return Response::text('Not Found', 404);
        }

        // Present view extends layout-bare.twig instead of the shared layout.twig.
        return Response::html($this->view->render('sprints/present', [
            'title'       => $data['sprint']->name . ' — present',
            'currentUser' => $actor,
            'csrfToken'   => SessionGuard::csrfToken(),
        ] + $data));
    }

    /**
     * Shared data fan-out for show() and present(). Returns null if the
     * sprint is missing so each caller can render its own 404.
     *
     * @return array{
     *   sprint: \App\Domain\Sprint,
     *   weeks: list<\App\Domain\SprintWeek>,
     *   sprintWorkers: list<\App\Domain\SprintWorker>,
     *   grid: array<int, array<int, float>>,
     *   capacity: array<int, array{ressourcen:float, after_reserves:float, committed_prio1:float, available:float}>,
     *   tasks: list<\App\Domain\Task>,
     *   taskGrid: array<int, array<int, float>>,
     *   statusGrid: array<int, array<int, string>>,
     *   ownerChoices: list<\App\Domain\Worker>,
     *   taskStatusEnabled: bool,
     *   assignmentSliderMax: int,
     * }|null
     */
    private function loadSprintPage(int $id): ?array
    {
        $sprint = $this->sprints->find($id);
        if ($sprint === null) {
            return null;
        }

        $weeks         = $this->weeks->allForSprint($id);
        $sprintWorkers = $this->sprintWorkers->allForSprint($id);
        $grid          = $this->days->grid($id);
        $tasks         = $this->tasks->allForSprint($id);
        $taskGrid      = $this->assignments->gridForSprint($id);
        $statusGrid    = $this->assignments->statusGridForSprint($id);
        $committedP1   = $this->assignments->committedPrio1BySprint($id);

        // Seed initial capacity server-side so the page is meaningful without JS
        // and the JS has the same numbers to compare against.
        $capacity = [];
        foreach ($sprintWorkers as $sw) {
            $wkDays     = $grid[$sw->id] ?? [];
            $ressourcen = array_sum($wkDays);
            $capacity[$sw->id] = CapacityCalculator::forWorker(
                $ressourcen,
                $sprint->reserveFraction,
                $committedP1[$sw->id] ?? 0.0,
            );
        }

        // Owner dropdown source: all active workers (not just sprint members,
        // since the Excel allows the owner to be any worker — but typically
        // they are one of the sprint workers. Keep it restrictive for the UI).
        $ownerChoices = $this->workers->all();

        // Phase 22: candidate destination sprints for Move/Copy (everything
        // except this one), plus bidirectional linked-task summaries for the
        // tasks on this sprint.
        $sprintChoices = [];
        foreach ($this->sprints->allWithCounts() as $row) {
            $s = $row['sprint'];
            if ($s->id === $id) {
                continue;
            }
            $sprintChoices[] = ['id' => $s->id, 'name' => $s->name];
        }
        $linkedMap = $this->tasks->linkedSummariesForTasks($tasks);

        return [
            'sprint'            => $sprint,
            'weeks'             => $weeks,
            'sprintWorkers'     => $sprintWorkers,
            'grid'              => $grid,
            'capacity'          => $capacity,
            'tasks'             => $tasks,
            'taskGrid'          => $taskGrid,
            'statusGrid'        => $statusGrid,
            'ownerChoices'      => $ownerChoices,
            'sprintChoices'     => $sprintChoices,
            'linkedMap'         => $linkedMap,
            'taskStatusEnabled'   => $this->appSettings->getBool('task_status_enabled', false),
            'assignmentSliderMax' => max(
                1,
                min(100, $this->appSettings->getInt('assignment_slider_max', 10)),
            ),
        ];
    }

    // -----------------------------------------------------------------------
    // Phase 4 — settings page + JSON mutation endpoints.
    // -----------------------------------------------------------------------

    /** GET /sprints/{id}/settings — admin-only. */
    public function settings(Request $req, array $params): Response
    {
        $actor = SessionGuard::requireAdmin($this->users);
        if ($actor instanceof Response) {
            return $actor;
        }

        $id = (int) $params['id'];
        $sprint = $this->sprints->find($id);
        if ($sprint === null) {
            return Response::text('Not Found', 404);
        }

        return Response::html($this->view->render('sprints/settings', [
            'title'            => "Settings — {$sprint->name}",
            'currentUser'      => $actor,
            'csrfToken'        => SessionGuard::csrfToken(),
            'sprint'           => $sprint,
            'weeks'            => $this->weeks->allForSprint($id),
            'sprintWorkers'    => $this->sprintWorkers->allForSprint($id),
            'availableWorkers' => $this->workers->activeNotInSprint($id),
            'error'            => $req->queryString('error'),
        ]));
    }

    /** PATCH /sprints/{id} — JSON — update name / dates / reserve_fraction. */
    public function updateMeta(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $id = (int) $params['id'];
        $sprint = $this->sprints->find($id);
        if ($sprint === null) {
            return Response::err('not_found', 'Sprint not found', 404);
        }

        $body = $req->json() ?? [];
        $changes = [];

        if (array_key_exists('name', $body)) {
            $name = is_string($body['name']) ? trim($body['name']) : '';
            if ($name === '') {
                return Response::err('validation', 'Name cannot be empty', 422, ['field' => 'name']);
            }
            $changes['name'] = $name;
        }

        if (array_key_exists('start_date', $body)) {
            if (!is_string($body['start_date']) || !self::isIsoDate($body['start_date'])) {
                return Response::err('validation', 'Invalid start_date', 422, ['field' => 'start_date']);
            }
            $changes['start_date'] = $body['start_date'];
        }

        if (array_key_exists('end_date', $body)) {
            if (!is_string($body['end_date']) || !self::isIsoDate($body['end_date'])) {
                return Response::err('validation', 'Invalid end_date', 422, ['field' => 'end_date']);
            }
            $changes['end_date'] = $body['end_date'];
        }

        if (array_key_exists('reserve_fraction', $body)) {
            if (!is_numeric($body['reserve_fraction'])) {
                return Response::err('validation', 'reserve_fraction must be numeric', 422);
            }
            $rf = (float) $body['reserve_fraction'];
            if ($rf < 0.0 || $rf > 1.0) {
                return Response::err('validation', 'reserve_fraction must be 0..1', 422);
            }
            $changes['reserve_fraction'] = $rf;
        }

        $effectiveStart = $changes['start_date'] ?? $sprint->startDate;
        $effectiveEnd   = $changes['end_date']   ?? $sprint->endDate;
        if ($effectiveEnd < $effectiveStart) {
            return Response::err('validation', 'end_date must be on or after start_date', 422);
        }

        // When the date range moves, the week set is fully derived from it:
        // count = floor((end - start)/7) + 1, capped at 26.
        $datesChanged = isset($changes['start_date']) || isset($changes['end_date']);
        $targetWeeks  = null;
        $cascadedDays = [];
        if ($datesChanged) {
            $targetWeeks = self::weeksBetween($effectiveStart, $effectiveEnd);
            if ($targetWeeks > 26) {
                return Response::err(
                    'validation',
                    'Date range spans more than 26 weeks',
                    422,
                );
            }
            // Snapshot cells that would be cascade-deleted by a shrink BEFORE
            // we open the transaction, so the audit has the rows it needs.
            $existing = $this->weeks->allForSprint($id);
            if ($targetWeeks < count($existing)) {
                foreach (array_slice($existing, $targetWeeks) as $w) {
                    foreach ($this->days->allForSprintWeek($w->id) as $d) {
                        $cascadedDays[] = $d;
                    }
                }
            }
        }

        if ($changes === []) {
            return Response::ok(['sprint' => $sprint->toAuditSnapshot()]);
        }

        $this->pdo->beginTransaction();
        try {
            $result = $this->sprints->update($id, $changes);
            $this->audit->recordForRequest(
                'UPDATE', 'sprint', $id,
                $result['before']->toAuditSnapshot(),
                $result['after']->toAuditSnapshot(),
                $req, $actor,
            );

            if ($datesChanged && $targetWeeks !== null) {
                // 1) Realign existing rows' start_date/iso_week to the new offset.
                foreach ($this->weeks->realignDates($id, $effectiveStart) as $d) {
                    $this->audit->recordForRequest(
                        'UPDATE', 'sprint_week', $d['after']->id,
                        $d['before']->toAuditSnapshot(),
                        $d['after']->toAuditSnapshot(),
                        $req, $actor,
                    );
                }
                // 2) Audit cells that the upcoming shrink will cascade.
                foreach ($cascadedDays as $cell) {
                    $this->audit->recordForRequest(
                        'DELETE', 'sprint_worker_days', $cell->id,
                        $cell->toAuditSnapshot(), null,
                        $req, $actor,
                    );
                }
                // 3) Resize the week set.
                $diff = $this->weeks->syncCount($id, $effectiveStart, $targetWeeks);
                foreach ($diff['added'] as $w) {
                    $this->audit->recordForRequest(
                        'CREATE', 'sprint_week', $w->id,
                        null, $w->toAuditSnapshot(),
                        $req, $actor,
                    );
                }
                foreach ($diff['removed'] as $w) {
                    $this->audit->recordForRequest(
                        'DELETE', 'sprint_week', $w->id,
                        $w->toAuditSnapshot(), null,
                        $req, $actor,
                    );
                }
            }

            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not save sprint', 500);
        }

        return Response::ok([
            'sprint'       => $result['after']->toAuditSnapshot(),
            'weeks_synced' => $datesChanged,
        ]);
    }

    /**
     * Number of calendar weeks the inclusive range [start..end] spans.
     * Matches the "every 7-day block since start" model used by
     * {@see SprintWeekRepository::syncCount()}.
     */
    private static function weeksBetween(string $startYmd, string $endYmd): int
    {
        $start = DateTimeImmutable::createFromFormat('Y-m-d', $startYmd);
        $end   = DateTimeImmutable::createFromFormat('Y-m-d', $endYmd);
        if ($start === false || $end === false) {
            return 1;
        }
        $days = (int) $start->diff($end)->format('%r%a');
        return max(1, intdiv($days, 7) + 1);
    }

    /** POST /sprints/{id}/weeks — JSON — resize the week set. */
    public function replaceWeeks(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $id = (int) $params['id'];
        $sprint = $this->sprints->find($id);
        if ($sprint === null) {
            return Response::err('not_found', 'Sprint not found', 404);
        }

        $body = $req->json() ?? [];
        if (!isset($body['n_weeks']) || !is_int($body['n_weeks']) || $body['n_weeks'] < 1 || $body['n_weeks'] > 26) {
            return Response::err('validation', 'n_weeks must be an integer in 1..26', 422);
        }

        // Identify which weeks will be dropped so we can snapshot their
        // sprint_worker_days BEFORE syncCount runs the DELETE (cascade
        // would otherwise wipe them without audit).
        $targetCount = (int) $body['n_weeks'];
        $existing    = $this->weeks->allForSprint($id);
        $toRemove    = $targetCount < count($existing)
            ? array_slice($existing, $targetCount)
            : [];

        $cascadedDays = [];
        foreach ($toRemove as $w) {
            foreach ($this->days->allForSprintWeek($w->id) as $d) {
                $cascadedDays[] = $d;
            }
        }

        $this->pdo->beginTransaction();
        try {
            foreach ($cascadedDays as $d) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_worker_days', $d->id,
                    $d->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }

            $diff = $this->weeks->syncCount($id, $sprint->startDate, $targetCount);

            foreach ($diff['added'] as $w) {
                $this->audit->recordForRequest(
                    'CREATE', 'sprint_week', $w->id,
                    null, $w->toAuditSnapshot(),
                    $req, $actor,
                );
            }
            foreach ($diff['removed'] as $w) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_week', $w->id,
                    $w->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }

            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not update weeks', 500);
        }

        return Response::ok([
            'weeks' => array_map(
                fn($w) => $w->toAuditSnapshot(),
                $this->weeks->allForSprint($id)
            ),
            'added'   => count($diff['added']),
            'removed' => count($diff['removed']),
        ]);
    }

    /** POST /sprints/{id}/workers — JSON — add a worker to the sprint. */
    public function addWorker(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        if ($this->sprints->find($sprintId) === null) {
            return Response::err('not_found', 'Sprint not found', 404);
        }

        $body = $req->json() ?? [];
        if (!isset($body['worker_id']) || !is_int($body['worker_id'])) {
            return Response::err('validation', 'worker_id required', 422);
        }
        $workerId = (int) $body['worker_id'];
        $worker = $this->workers->find($workerId);
        if ($worker === null) {
            return Response::err('validation', 'Unknown worker', 422, ['field' => 'worker_id']);
        }
        if (!$worker->isActive) {
            return Response::err('validation', 'Worker is inactive', 422, ['field' => 'worker_id']);
        }

        $rtb = $worker->defaultRtb;
        if (isset($body['rtb'])) {
            if (!is_numeric($body['rtb']) || (float) $body['rtb'] < 0.0 || (float) $body['rtb'] > 1.0) {
                return Response::err('validation', 'rtb must be 0..1', 422);
            }
            $rtb = (float) $body['rtb'];
        }

        $this->pdo->beginTransaction();
        try {
            $sw = $this->sprintWorkers->add($sprintId, $workerId, $rtb);
            $this->audit->recordForRequest(
                'CREATE', 'sprint_worker', $sw->id,
                null, $sw->toAuditSnapshot(),
                $req, $actor,
            );
            $this->pdo->commit();
        } catch (PDOException $e) {
            $this->pdo->rollBack();
            if (str_contains(strtolower($e->getMessage()), 'unique')) {
                return Response::err('conflict', 'Worker already in sprint', 409);
            }
            return Response::err('db_error', 'Could not add worker', 500);
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not add worker', 500);
        }

        return Response::ok([
            'sprint_worker' => $sw->toAuditSnapshot() + ['worker_name' => $sw->workerName],
        ]);
    }

    /** DELETE /sprints/{id}/workers/{sw_id} — JSON — remove a worker from the sprint. */
    public function removeWorker(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        $swId     = (int) $params['sw_id'];

        $existing = $this->sprintWorkers->find($swId);
        if ($existing === null || $existing->sprintId !== $sprintId) {
            return Response::err('not_found', 'sprint_worker not found in this sprint', 404);
        }

        // Snapshot everything the FK cascade will wipe BEFORE deleting, so
        // each cascaded row gets its own DELETE audit entry (spec §5).
        $cascadedDays        = $this->days->allForSprintWorker($swId);
        $cascadedAssignments = $this->assignments->allForSprintWorker($swId);

        $this->pdo->beginTransaction();
        try {
            foreach ($cascadedDays as $d) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_worker_days', $d->id,
                    $d->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            foreach ($cascadedAssignments as $a) {
                $this->audit->recordForRequest(
                    'DELETE', 'task_assignment', $a->id,
                    $a->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }

            $removed = $this->sprintWorkers->remove($swId);
            if ($removed !== null) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_worker', $removed->id,
                    $removed->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not remove worker', 500);
        }

        return Response::ok(['removed_id' => $swId]);
    }

    /** POST /sprints/{id}/workers/reorder — JSON — apply an ordering. */
    public function reorderWorkers(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        if ($this->sprints->find($sprintId) === null) {
            return Response::err('not_found', 'Sprint not found', 404);
        }

        $body = $req->json();
        if (!is_array($body) || !array_is_list($body)) {
            return Response::err('validation', 'body must be a list of {sprint_worker_id, sort_order}', 422);
        }

        $ordering = [];
        $seenOrder = [];
        foreach ($body as $row) {
            if (!is_array($row) || !isset($row['sprint_worker_id'], $row['sort_order'])) {
                return Response::err('validation', 'each entry needs sprint_worker_id and sort_order', 422);
            }
            $sw    = (int) $row['sprint_worker_id'];
            $order = (int) $row['sort_order'];
            if ($sw <= 0 || $order < 1) {
                return Response::err('validation', 'ids/orders must be positive', 422);
            }
            if (isset($seenOrder[$order])) {
                return Response::err('validation', 'duplicate sort_order', 422);
            }
            $seenOrder[$order] = true;
            $ordering[] = ['sprint_worker_id' => $sw, 'sort_order' => $order];
        }

        $this->pdo->beginTransaction();
        try {
            $diffs = $this->sprintWorkers->reorder($sprintId, $ordering);
            foreach ($diffs as $d) {
                $this->audit->recordForRequest(
                    'UPDATE', 'sprint_worker', $d['after']->id,
                    $d['before']->toAuditSnapshot(),
                    $d['after']->toAuditSnapshot(),
                    $req, $actor,
                );
            }
            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not reorder', 500);
        }

        return Response::ok(['moved' => count($diffs)]);
    }

    /** PATCH /sprints/{id}/workers/{sw_id} — JSON — edit RTB. */
    public function updateWorker(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        $swId     = (int) $params['sw_id'];

        $existing = $this->sprintWorkers->find($swId);
        if ($existing === null || $existing->sprintId !== $sprintId) {
            return Response::err('not_found', 'sprint_worker not found in this sprint', 404);
        }

        $body = $req->json() ?? [];
        if (!isset($body['rtb']) || !is_numeric($body['rtb'])) {
            return Response::err('validation', 'rtb required', 422);
        }
        $rtb = (float) $body['rtb'];
        if ($rtb < 0.0 || $rtb > 1.0) {
            return Response::err('validation', 'rtb must be 0..1', 422);
        }

        $this->pdo->beginTransaction();
        try {
            $result = $this->sprintWorkers->setRtb($swId, $rtb);
            $this->audit->recordForRequest(
                'UPDATE', 'sprint_worker', $swId,
                $result['before']->toAuditSnapshot(),
                $result['after']->toAuditSnapshot(),
                $req, $actor,
            );
            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not update worker', 500);
        }

        return Response::ok(['sprint_worker' => $result['after']->toAuditSnapshot()]);
    }

    /** PATCH /sprints/{id}/week-cells — JSON — batch upsert of sprint_worker_days. */
    public function updateWeekCells(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        $sprint   = $this->sprints->find($sprintId);
        if ($sprint === null) {
            return Response::err('not_found', 'Sprint not found', 404);
        }

        $body = $req->json();
        if (!is_array($body) || !array_is_list($body)) {
            return Response::err('validation', 'body must be a list of {sprint_worker_id, sprint_week_id, days}', 422);
        }
        if ($body === []) {
            return Response::ok(['applied' => 0, 'noop' => 0, 'per_worker' => new \stdClass()]);
        }
        if (count($body) > self::MAX_BATCH_ITEMS) {
            return Response::err(
                'too_many_items',
                'cell list exceeds ' . self::MAX_BATCH_ITEMS . '-item cap',
                413,
            );
        }

        // Cross-check every cell belongs to this sprint.
        $validSw = array_column(
            array_map(fn($sw) => ['id' => $sw->id], $this->sprintWorkers->allForSprint($sprintId)),
            'id',
        );
        $validSw = array_flip($validSw);

        $validWk = array_column(
            array_map(fn($w) => ['id' => $w->id], $this->weeks->allForSprint($sprintId)),
            'id',
        );
        $validWk = array_flip($validWk);

        $cells = [];
        foreach ($body as $i => $row) {
            if (!is_array($row) || !isset($row['sprint_worker_id'], $row['sprint_week_id'], $row['days'])) {
                return Response::err('validation', "cell[{$i}] needs sprint_worker_id, sprint_week_id, days", 422);
            }
            $swId   = (int) $row['sprint_worker_id'];
            $wkId   = (int) $row['sprint_week_id'];
            $daysN  = $row['days'];
            if (!is_numeric($daysN)) {
                return Response::err('validation', "cell[{$i}] days must be numeric", 422);
            }
            $days = (float) $daysN;
            if (!isset($validSw[$swId])) {
                return Response::err('validation', "cell[{$i}] sprint_worker {$swId} not in sprint", 422);
            }
            if (!isset($validWk[$wkId])) {
                return Response::err('validation', "cell[{$i}] sprint_week {$wkId} not in sprint", 422);
            }
            if (!CapacityCalculator::isHalfStep($days, 0.0, 5.0)) {
                return Response::err('validation', "cell[{$i}] days must be 0..5 in 0.5 steps", 422);
            }
            $cells[] = ['sw_id' => $swId, 'week_id' => $wkId, 'days' => $days];
        }

        $applied = 0;
        $noop    = 0;
        $touchedWorkers = [];

        $this->pdo->beginTransaction();
        try {
            foreach ($cells as $c) {
                $result = $this->days->upsert($c['sw_id'], $c['week_id'], $c['days']);
                if ($result['action'] === 'NOOP') {
                    $noop++;
                    continue;
                }
                $applied++;
                $touchedWorkers[$c['sw_id']] = true;

                $this->audit->recordForRequest(
                    action:     $result['action'],
                    entityType: 'sprint_worker_days',
                    entityId:   $result['after']?->id ?? $result['before']?->id,
                    before:     $result['before']?->toAuditSnapshot(),
                    after:      $result['after']?->toAuditSnapshot(),
                    req:        $req,
                    actor:      $actor,
                );
            }
            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not save cells', 500);
        }

        // Recompute capacity for every worker whose row changed.
        $grid = $this->days->grid($sprintId);
        $perWorker = [];
        foreach (array_keys($touchedWorkers) as $swId) {
            $ressourcen = array_sum($grid[$swId] ?? []);
            $perWorker[(string) $swId] = CapacityCalculator::forWorker(
                $ressourcen,
                $sprint->reserveFraction,
                0.0,
            );
        }

        return Response::ok([
            'applied'    => $applied,
            'noop'       => $noop,
            'per_worker' => $perWorker === [] ? new \stdClass() : $perWorker,
        ]);
    }

    /**
     * PATCH /sprints/{id}/week/{week_id} — JSON — set the active weekdays for
     * one week. Accepts either `{"active_days_mask": 15}` or
     * `{"active_days": ["Mo", "Di", "Mi", "Do"]}`; max_working_days is
     * derived server-side as popcount(mask).
     */
    public function updateWeekDays(Request $req, array $params): Response
    {
        $gate = $this->gateJsonAdmin($req);
        if ($gate instanceof Response) {
            return $gate;
        }
        $actor = $gate;

        $sprintId = (int) $params['id'];
        $weekId   = (int) $params['week_id'];

        $week = $this->weeks->find($weekId);
        if ($week === null || $week->sprintId !== $sprintId) {
            return Response::err('not_found', 'sprint_week not found in this sprint', 404);
        }

        $body = $req->json() ?? [];
        $mask = null;

        if (array_key_exists('active_days_mask', $body)) {
            $raw = $body['active_days_mask'];
            if (!is_int($raw) || $raw < 0 || $raw > SprintWeek::MASK_ALL) {
                return Response::err(
                    'validation',
                    'active_days_mask must be an integer 0..31',
                    422,
                    ['field' => 'active_days_mask'],
                );
            }
            $mask = $raw;
        } elseif (array_key_exists('active_days', $body)) {
            if (!is_array($body['active_days']) || !array_is_list($body['active_days'])) {
                return Response::err(
                    'validation',
                    'active_days must be a list of Mo/Di/Mi/Do/Fr',
                    422,
                    ['field' => 'active_days'],
                );
            }
            try {
                $mask = SprintWeek::daysToMask($body['active_days']);
            } catch (InvalidArgumentException $e) {
                return Response::err(
                    'validation',
                    $e->getMessage(),
                    422,
                    ['field' => 'active_days'],
                );
            }
        } else {
            return Response::err(
                'validation',
                'one of active_days_mask or active_days required',
                422,
            );
        }

        if ($week->activeDaysMask === $mask) {
            return Response::ok(['sprint_week' => $week->toAuditSnapshot()]);
        }

        $this->pdo->beginTransaction();
        try {
            $result = $this->weeks->updateActiveDays($weekId, $mask);
            $this->audit->recordForRequest(
                'UPDATE', 'sprint_week', $weekId,
                $result['before']->toAuditSnapshot(),
                $result['after']->toAuditSnapshot(),
                $req, $actor,
            );
            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::err('db_error', 'Could not update week', 500);
        }

        return Response::ok(['sprint_week' => $result['after']->toAuditSnapshot()]);
    }

    /**
     * POST /sprints/{id}/delete — destructive: removes the sprint and every
     * row attached to it.
     *
     * Form-post (not JSON): admin + CSRF token from the form, plus a
     * `confirm_name` field that must match the sprint's name verbatim. Each
     * cascaded child (task_assignments, sprint_worker_days, tasks,
     * sprint_workers, sprint_weeks) is snapshotted and audited DELETE
     * before the parent delete fires, per spec §7. Tasks in *other*
     * sprints whose `linked_task_id` points at one of this sprint's tasks
     * are silently SET NULL by the FK; we audit those as UPDATE rows so
     * the chain is reconstructable from the audit log.
     */
    public function delete(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);
        }

        $sprintId = (int) $params['id'];
        $sprint   = $this->sprints->find($sprintId);
        if ($sprint === null) {
            return Response::text('Not Found', 404);
        }

        // Type-the-name guard: defence in depth — the JS keeps the submit
        // button disabled until this matches, but a JS-bypass attempt still
        // hits this check.
        $confirm = trim($req->postString('confirm_name'));
        if ($confirm !== $sprint->name) {
            return Response::redirect('/sprints/' . $sprintId . '/settings?error=name_mismatch');
        }

        // Snapshot every cascaded child BEFORE the parent delete. Order
        // mirrors the FK dependency tree: leaves first.
        $sprintWorkers       = $this->sprintWorkers->allForSprint($sprintId);
        $cascadedDays        = [];
        $cascadedAssignments = [];
        foreach ($sprintWorkers as $sw) {
            foreach ($this->days->allForSprintWorker($sw->id) as $d) {
                $cascadedDays[] = $d;
            }
            foreach ($this->assignments->allForSprintWorker($sw->id) as $a) {
                $cascadedAssignments[] = $a;
            }
        }
        $tasksInSprint = $this->tasks->allForSprint($sprintId);
        $weeks         = $this->weeks->allForSprint($sprintId);

        // Phase 22 SET NULL audit: tasks in OTHER sprints whose
        // linked_task_id points at any task in this sprint will be
        // silently nulled by the cascade. Capture them so the audit log
        // doesn't lose the link.
        $linkUpdates = [];
        if ($tasksInSprint !== []) {
            $taskIds = array_map(fn($t) => $t->id, $tasksInSprint);
            $place   = implode(',', array_fill(0, count($taskIds), '?'));
            $stmt    = $this->pdo->prepare(
                'SELECT * FROM tasks WHERE linked_task_id IN (' . $place . ')'
            );
            $stmt->execute($taskIds);
            foreach ($stmt as $row) {
                $tid = (int) $row['id'];
                // Skip rows that are themselves in this sprint — they're
                // about to be deleted, no SET NULL audit needed.
                if ((int) $row['sprint_id'] === $sprintId) {
                    continue;
                }
                $linkUpdates[] = $tid;
            }
        }

        $this->pdo->beginTransaction();
        try {
            // Audit cascaded leaves first.
            foreach ($cascadedAssignments as $a) {
                $this->audit->recordForRequest(
                    'DELETE', 'task_assignment', $a->id,
                    $a->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            foreach ($cascadedDays as $d) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_worker_days', $d->id,
                    $d->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            foreach ($tasksInSprint as $t) {
                $this->audit->recordForRequest(
                    'DELETE', 'task', $t->id,
                    $t->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            foreach ($sprintWorkers as $sw) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_worker', $sw->id,
                    $sw->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }
            foreach ($weeks as $w) {
                $this->audit->recordForRequest(
                    'DELETE', 'sprint_week', $w->id,
                    $w->toAuditSnapshot(), null,
                    $req, $actor,
                );
            }

            // Audit the SET NULL on cross-sprint linked tasks. We refetch
            // each row inside the transaction so the snapshot is current.
            foreach ($linkUpdates as $tid) {
                $linked = $this->tasks->find($tid);
                if ($linked === null) {
                    continue;
                }
                $beforeSnap = $linked->toAuditSnapshot();
                $afterSnap  = $beforeSnap;
                $afterSnap['linked_task_id'] = null;
                $this->audit->recordForRequest(
                    'UPDATE', 'task', $tid,
                    $beforeSnap, $afterSnap,
                    $req, $actor,
                );
            }

            $this->sprints->delete($sprintId);
            $this->audit->recordForRequest(
                'DELETE', 'sprint', $sprintId,
                $sprint->toAuditSnapshot(), null,
                $req, $actor,
            );

            $this->pdo->commit();
        } catch (Throwable) {
            $this->pdo->rollBack();
            return Response::redirect('/sprints/' . $sprintId . '/settings?error=db_error');
        }

        // R01-N26: stash the deleted sprint's name in a one-shot session
        // flash instead of leaking it via `?deleted=<name>`. The query-string
        // form let anyone craft `/?deleted=foo` and see a green "Sprint foo
        // was deleted" chip without an actual delete having happened.
        // The home route reads this key and unsets it on render; expiry on
        // first read makes it impossible to re-trigger with a refresh.
        SessionGuard::start();
        $_SESSION['flash_deleted_sprint_name'] = $sprint->name;
        return Response::redirect('/');
    }

    // ------------------------------------------------------------------
    // Shared helpers
    // ------------------------------------------------------------------

    /**
     * Admin gate for JSON endpoints. Returns the signed-in User on success,
     * or an `Response::err(...)` JSON envelope on failure. Also enforces CSRF.
     */
    private function gateJsonAdmin(Request $req): User|Response
    {
        $user = SessionGuard::currentUser($this->users);
        if ($user === null) {
            return Response::err('unauthenticated', 'Sign in required', 401);
        }
        if (!$user->isAdmin) {
            return Response::err('forbidden', 'Admin access required', 403);
        }
        if (!SessionGuard::verifyCsrf($req)) {
            return Response::err('csrf', 'CSRF token invalid', 403);
        }
        return $user;
    }

    private static function isIsoDate(string $s): bool
    {
        $d = DateTimeImmutable::createFromFormat('Y-m-d', $s);
        return $d !== false && $d->format('Y-m-d') === $s;
    }
}