sprint_planer_web/tests/Cascade/CascadeAuditTest.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\Tests\Cascade;

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\WorkerRepository;
use App\Services\AuditLogger;
use App\Tests\TestCase;
use PDO;

/**
 * End-to-end tests for Phase 8: every FK cascade that used to silently
 * lose audit rows now emits one DELETE row per cascaded child.
 *
 * We don't spin up the full Controller (that needs a Request + Session);
 * instead we exercise the exact "snapshot children then delete parent"
 * flow each controller method now follows. A regression in the controller
 * would have to skip those snapshots to break this test.
 */
final class CascadeAuditTest extends TestCase
{
    // -------------------------------------------------------------------
    // Helpers: seed a tiny fully-populated sprint.
    // -------------------------------------------------------------------

    /**
     * @return array{
     *   pdo:                     PDO,
     *   sprintId:                int,
     *   swAliceId:               int,
     *   swBobId:                 int,
     *   weekIds:                 list<int>,
     *   taskId:                  int,
     *   audit:                   AuditLogger,
     *   days:                    SprintWorkerDayRepository,
     *   assignments:             TaskAssignmentRepository,
     *   sprintWorkers:           SprintWorkerRepository,
     *   weeks:                   SprintWeekRepository,
     * }
     */
    private function seed(): array
    {
        $pdo = $this->makeDb();

        $workers = new WorkerRepository($pdo);
        $sprints = new SprintRepository($pdo);
        $weeks   = new SprintWeekRepository($pdo);
        $sw      = new SprintWorkerRepository($pdo);
        $days    = new SprintWorkerDayRepository($pdo);
        $tasks   = new TaskRepository($pdo);
        $asg     = new TaskAssignmentRepository($pdo);
        $audit   = new AuditLogger($pdo);

        $wAlice = $workers->create('Alice', true, 0.0);
        $wBob   = $workers->create('Bob',   true, 0.0);

        $sprint = $sprints->create('S', '2026-01-05', '2026-01-30', 0.2);
        $wks    = $sprints->materializeWeeks($sprint->id, '2026-01-05', '2026-01-30', 4);
        $weekIds = array_map(fn($w) => (int) $w['id'], $wks);

        $swAlice = $sw->add($sprint->id, $wAlice->id, 0.0);
        $swBob   = $sw->add($sprint->id, $wBob->id,   0.0);

        // Fill day cells for both workers across all 4 weeks.
        foreach ($weekIds as $weekId) {
            $days->upsert($swAlice->id, $weekId, 4.0);
            $days->upsert($swBob->id,   $weekId, 3.0);
        }

        // A task + two assignments (one per sprint worker).
        $task = $tasks->create($sprint->id, 'T', null, 1);
        $asg->upsert($task->id, $swAlice->id, 2.0);
        $asg->upsert($task->id, $swBob->id,   1.5);

        return [
            'pdo'           => $pdo,
            'sprintId'      => $sprint->id,
            'swAliceId'     => $swAlice->id,
            'swBobId'       => $swBob->id,
            'weekIds'       => $weekIds,
            'taskId'        => $task->id,
            'audit'         => $audit,
            'days'          => $days,
            'assignments'   => $asg,
            'sprintWorkers' => $sw,
            'weeks'         => $weeks,
        ];
    }

    // -------------------------------------------------------------------
    // Path 1: removing a sprint_worker cascades to sprint_worker_days
    // -------------------------------------------------------------------

    public function testRemovingSprintWorkerAuditsEveryCascadedDay(): void
    {
        $s = $this->seed();

        // Simulate SprintController::removeWorker for Alice.
        $cascadedDays = $s['days']->allForSprintWorker($s['swAliceId']);
        $cascadedAsgs = $s['assignments']->allForSprintWorker($s['swAliceId']);

        $this->assertCount(4, $cascadedDays, 'Alice has one cell per week');
        $this->assertCount(1, $cascadedAsgs, 'Alice has one assignment');

        $s['pdo']->beginTransaction();
        foreach ($cascadedDays as $d) {
            $s['audit']->record('DELETE', 'sprint_worker_days', $d->id, $d->toAuditSnapshot(), null);
        }
        foreach ($cascadedAsgs as $a) {
            $s['audit']->record('DELETE', 'task_assignment', $a->id, $a->toAuditSnapshot(), null);
        }
        $removed = $s['sprintWorkers']->remove($s['swAliceId']);
        $s['audit']->record('DELETE', 'sprint_worker', $removed->id, $removed->toAuditSnapshot(), null);
        $s['pdo']->commit();

        // FK cascade should have wiped the child tables for Alice.
        $this->assertSame(0, (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM sprint_worker_days WHERE sprint_worker_id = {$s['swAliceId']}"
        )->fetchColumn());
        $this->assertSame(0, (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM task_assignments WHERE sprint_worker_id = {$s['swAliceId']}"
        )->fetchColumn());

        // Audit counts.
        $dayAuditCount = (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM audit_log
             WHERE action = 'DELETE' AND entity_type = 'sprint_worker_days'"
        )->fetchColumn();
        $this->assertSame(4, $dayAuditCount, 'one DELETE audit per cell that cascaded');

        $asgAuditCount = (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM audit_log
             WHERE action = 'DELETE' AND entity_type = 'task_assignment'"
        )->fetchColumn();
        $this->assertSame(1, $asgAuditCount);

        $swAuditCount = (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM audit_log
             WHERE action = 'DELETE' AND entity_type = 'sprint_worker'"
        )->fetchColumn();
        $this->assertSame(1, $swAuditCount);

        // Bob is untouched.
        $this->assertCount(4, $s['days']->allForSprintWorker($s['swBobId']));
    }

    // -------------------------------------------------------------------
    // Path 2: shrinking sprint_weeks cascades to sprint_worker_days
    // -------------------------------------------------------------------

    public function testShrinkingWeeksAuditsCascadedDaysInDroppedWeeks(): void
    {
        $s = $this->seed();

        // Simulate SprintController::replaceWeeks shrinking 4 → 2.
        $targetCount = 2;
        $existing    = $s['weeks']->allForSprint($s['sprintId']);
        $toRemove    = array_slice($existing, $targetCount);
        $this->assertCount(2, $toRemove);

        $cascadedDays = [];
        foreach ($toRemove as $w) {
            foreach ($s['days']->allForSprintWeek($w->id) as $d) {
                $cascadedDays[] = $d;
            }
        }
        // Two dropped weeks × 2 workers = 4 cells that will cascade.
        $this->assertCount(4, $cascadedDays);

        $s['pdo']->beginTransaction();
        foreach ($cascadedDays as $d) {
            $s['audit']->record('DELETE', 'sprint_worker_days', $d->id, $d->toAuditSnapshot(), null);
        }
        $diff = $s['weeks']->syncCount($s['sprintId'], '2026-01-05', $targetCount);
        foreach ($diff['removed'] as $w) {
            $s['audit']->record('DELETE', 'sprint_week', $w->id, $w->toAuditSnapshot(), null);
        }
        $s['pdo']->commit();

        // Weeks 3 and 4 are gone; their day cells are gone too.
        $remainingWeeks = $s['weeks']->allForSprint($s['sprintId']);
        $this->assertCount(2, $remainingWeeks);
        foreach ($remainingWeeks as $w) {
            // Each remaining week still has 2 cells (Alice + Bob).
            $this->assertCount(2, $s['days']->allForSprintWeek($w->id));
        }
        // Dropped week IDs have zero cells.
        foreach ($toRemove as $w) {
            $this->assertCount(0, $s['days']->allForSprintWeek($w->id));
        }

        $dayAudits = (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM audit_log
             WHERE action = 'DELETE' AND entity_type = 'sprint_worker_days'"
        )->fetchColumn();
        $this->assertSame(4, $dayAudits, 'audits every cell in the dropped weeks');

        $weekAudits = (int) $s['pdo']->query(
            "SELECT COUNT(*) FROM audit_log
             WHERE action = 'DELETE' AND entity_type = 'sprint_week'"
        )->fetchColumn();
        $this->assertSame(2, $weekAudits);
    }

    // -------------------------------------------------------------------
    // Repo-level lookups used by the controller
    // -------------------------------------------------------------------

    public function testSprintWorkerDayRepoByParentLookups(): void
    {
        $s = $this->seed();

        $this->assertCount(4, $s['days']->allForSprintWorker($s['swAliceId']));
        $this->assertCount(4, $s['days']->allForSprintWorker($s['swBobId']));

        foreach ($s['weekIds'] as $weekId) {
            // Each week has two cells (Alice + Bob).
            $this->assertCount(2, $s['days']->allForSprintWeek($weekId));
        }

        // Unknown parent returns empty, not null.
        $this->assertSame([], $s['days']->allForSprintWorker(999_999));
        $this->assertSame([], $s['days']->allForSprintWeek(999_999));
    }

    public function testTaskAssignmentRepoByParentLookup(): void
    {
        $s = $this->seed();

        $this->assertCount(1, $s['assignments']->allForSprintWorker($s['swAliceId']));
        $this->assertCount(1, $s['assignments']->allForSprintWorker($s['swBobId']));
        $this->assertSame([], $s['assignments']->allForSprintWorker(999_999));
    }

    // -------------------------------------------------------------------
    // Path 4 (Phase 22.1): deleting a sprint cascades through the entire
    // child tree — every leaf gets a DELETE audit before the parent goes.
    // -------------------------------------------------------------------

    public function testDeletingSprintAuditsEntireCascade(): void
    {
        $s = $this->seed();
        /** @var SprintRepository $sprints */
        $sprints = new SprintRepository($s['pdo']);

        // Mirror SprintController::delete: snapshot every leaf, then audit
        // each one, then drop the sprint and audit the parent.
        $sprintWorkers = $s['sprintWorkers']->allForSprint($s['sprintId']);
        $cascadedDays  = [];
        $cascadedAsgs  = [];
        foreach ($sprintWorkers as $sw) {
            foreach ($s['days']->allForSprintWorker($sw->id) as $d) {
                $cascadedDays[] = $d;
            }
            foreach ($s['assignments']->allForSprintWorker($sw->id) as $a) {
                $cascadedAsgs[] = $a;
            }
        }
        $tasksRepo = new TaskRepository($s['pdo']);
        $tasks     = $tasksRepo->allForSprint($s['sprintId']);
        $weeks     = $s['weeks']->allForSprint($s['sprintId']);

        $this->assertCount(2, $sprintWorkers);
        $this->assertCount(8, $cascadedDays,  'Alice + Bob × 4 weeks');
        $this->assertCount(2, $cascadedAsgs,  'one assignment per worker');
        $this->assertCount(1, $tasks);
        $this->assertCount(4, $weeks);

        $s['pdo']->beginTransaction();
        foreach ($cascadedAsgs as $a) {
            $s['audit']->record('DELETE', 'task_assignment',    $a->id, $a->toAuditSnapshot(), null);
        }
        foreach ($cascadedDays as $d) {
            $s['audit']->record('DELETE', 'sprint_worker_days', $d->id, $d->toAuditSnapshot(), null);
        }
        foreach ($tasks as $t) {
            $s['audit']->record('DELETE', 'task',               $t->id, $t->toAuditSnapshot(), null);
        }
        foreach ($sprintWorkers as $sw) {
            $s['audit']->record('DELETE', 'sprint_worker',      $sw->id, $sw->toAuditSnapshot(), null);
        }
        foreach ($weeks as $w) {
            $s['audit']->record('DELETE', 'sprint_week',        $w->id, $w->toAuditSnapshot(), null);
        }
        $deleted = $sprints->delete($s['sprintId']);
        $this->assertNotNull($deleted);
        $s['audit']->record('DELETE', 'sprint', $deleted->id, $deleted->toAuditSnapshot(), null);
        $s['pdo']->commit();

        // Sprint and every cascaded child are gone.
        $this->assertNull($sprints->find($s['sprintId']));
        $this->assertCount(0, $s['weeks']->allForSprint($s['sprintId']));
        $this->assertCount(0, $s['sprintWorkers']->allForSprint($s['sprintId']));
        $this->assertCount(0, $tasksRepo->allForSprint($s['sprintId']));
        $this->assertSame([], $s['days']->allForSprintWorker($s['swAliceId']));
        $this->assertSame([], $s['assignments']->allForSprintWorker($s['swAliceId']));

        // Audit count per entity_type matches the cascade size.
        $countByType = [];
        $stmt = $s['pdo']->query(
            "SELECT entity_type, COUNT(*) AS n
             FROM audit_log
             WHERE action = 'DELETE'
             GROUP BY entity_type"
        );
        foreach ($stmt as $row) {
            $countByType[(string) $row['entity_type']] = (int) $row['n'];
        }
        $this->assertSame(2, $countByType['task_assignment']    ?? 0);
        $this->assertSame(8, $countByType['sprint_worker_days'] ?? 0);
        $this->assertSame(1, $countByType['task']               ?? 0);
        $this->assertSame(2, $countByType['sprint_worker']      ?? 0);
        $this->assertSame(4, $countByType['sprint_week']        ?? 0);
        $this->assertSame(1, $countByType['sprint']             ?? 0);
    }
}