sprint_planer_web/tests/Services/Import/SprintImporterCommitTest.php

<?php

declare(strict_types=1);

namespace App\Tests\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 App\Domain\TaskAssignment;
use App\Http\Request;
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\Services\Import\SprintImporter;
use App\Tests\TestCase;
use PDO;
use RuntimeException;

/**
 * Phase 20 — commit-side test. Builds a ParsedSheet by hand (no XLSX, no
 * PhpSpreadsheet) and asserts that committing it produces the expected
 * sprint, weeks, workers, sprint_workers, sprint_worker_days, tasks,
 * task_assignments, and audit rows.
 */
final class SprintImporterCommitTest extends TestCase
{
    /**
     * @return array{0:PDO,1:SprintImporter,2:Request}
     */
    private function build(): array
    {
        $pdo = $this->makeDb();
        $importer = new SprintImporter(
            pdo:          $pdo,
            sprints:      new SprintRepository($pdo),
            weeks:        new SprintWeekRepository($pdo),
            sprintWorkers:new SprintWorkerRepository($pdo),
            days:         new SprintWorkerDayRepository($pdo),
            tasks:        new TaskRepository($pdo),
            assignments:  new TaskAssignmentRepository($pdo),
            workers:      new WorkerRepository($pdo),
            audit:        new AuditLogger($pdo),
        );
        $req = new Request('POST', '/sprints/import/x', [], [], '', [], []);
        return [$pdo, $importer, $req];
    }

    private function sampleSheet(): ParsedSheet
    {
        return new ParsedSheet(
            sheetName: 'Sprint 1',
            weeks: [
                new ParsedWeek(1, 13, '23.03', 2, '2026-03-23'),
                new ParsedWeek(2, 14, '30.03', 4, '2026-03-30'),
            ],
            workers: [
                new ParsedWorker('Alice',    [1 => 2.0, 2 => 4.0], 0.5),
                new ParsedWorker('Bob',      [1 => 1.0, 2 => 0.0], 0.7),
            ],
            tasks: [
                new ParsedTask('Build thing',  'Alice', 1, [
                    new ParsedAssignment('Alice', 2.0, TaskAssignment::STATUS_ABGESCHLOSSEN, 'FF00B050'),
                    new ParsedAssignment('Bob',   1.0, TaskAssignment::STATUS_GESTARTET,    'FFFFFF00'),
                ]),
                new ParsedTask('Review thing', 'Carol', 2, [
                    new ParsedAssignment('Alice', 0.5, TaskAssignment::STATUS_ZUGEWIESEN,   null),
                ]),
            ],
            reserveFraction:   0.2,
            inferredStartDate: '2026-03-23',
            inferredEndDate:   '2026-04-05',
            warnings:          [],
        );
    }

    public function testCreateNewSprintWritesEverythingTransactionally(): void
    {
        [$pdo, $importer, $req] = $this->build();
        $sheet = $this->sampleSheet();

        $result = $importer->commit(
            sheet:            $sheet,
            sprintName:       'Sprint 1',
            startDate:        '2026-03-23',
            endDate:          '2026-04-05',
            target:           'new',
            existingSprintId: null,
            req:              $req,
            actor:            null,
        );

        $this->assertSame('Sprint 1', $result->sprintName);
        $this->assertSame(2, $result->weekCount);
        $this->assertSame(2, $result->workerCount);
        $this->assertSame(2, $result->taskCount);

        // The two parsed workers were both auto-created.
        $this->assertSame(['Alice', 'Bob'], $result->createdWorkers);
        // Carol (the second task's owner) doesn't exist as a worker — recorded as missing.
        $this->assertSame(['Carol'], $result->missingOwners);

        // Sprint row + reserve.
        $sprintRow = $pdo->query('SELECT * FROM sprints')->fetch();
        $this->assertSame('Sprint 1', $sprintRow['name']);
        $this->assertSame('2026-03-23', $sprintRow['start_date']);
        $this->assertEqualsWithDelta(0.2, (float) $sprintRow['reserve_fraction'], 1e-9);

        // 2 weeks with active_days_mask matching maxDays.
        $weeks = $pdo->query('SELECT * FROM sprint_weeks ORDER BY sort_order')->fetchAll();
        $this->assertCount(2, $weeks);
        $this->assertSame(0b00011, (int) $weeks[0]['active_days_mask'], '2 days → Mo+Di');
        $this->assertSame(0b01111, (int) $weeks[1]['active_days_mask'], '4 days → Mo..Do');
        $this->assertEqualsWithDelta(2.0, (float) $weeks[0]['max_working_days'], 1e-9);
        $this->assertEqualsWithDelta(4.0, (float) $weeks[1]['max_working_days'], 1e-9);

        // 2 sprint_workers with RTBs.
        $sw = $pdo->query('SELECT sw.*, w.name FROM sprint_workers sw JOIN workers w ON w.id=sw.worker_id ORDER BY sw.sort_order')->fetchAll();
        $this->assertCount(2, $sw);
        $this->assertSame('Alice', $sw[0]['name']);
        $this->assertEqualsWithDelta(0.5, (float) $sw[0]['rtb'], 1e-9);
        $this->assertSame('Bob',   $sw[1]['name']);
        $this->assertEqualsWithDelta(0.7, (float) $sw[1]['rtb'], 1e-9);

        // sprint_worker_days: 3 non-zero cells (Alice w1+w2, Bob w1; Bob w2 was 0).
        $dayCells = (int) $pdo->query('SELECT COUNT(*) FROM sprint_worker_days')->fetchColumn();
        $this->assertSame(3, $dayCells);

        // 2 tasks; first owned by Alice, second has no owner (Carol unknown).
        $tasks = $pdo->query('SELECT * FROM tasks ORDER BY sort_order')->fetchAll();
        $this->assertCount(2, $tasks);
        $this->assertSame('Build thing',  $tasks[0]['title']);
        $this->assertNotNull($tasks[0]['owner_worker_id']);
        $this->assertSame('Review thing', $tasks[1]['title']);
        $this->assertNull($tasks[1]['owner_worker_id']);
        $this->assertSame(1, (int) $tasks[0]['priority']);
        $this->assertSame(2, (int) $tasks[1]['priority']);

        // Task assignments: 2 days+colour for task 1, none for task 2 (the
        // single ZUGEWIESEN cell with 0.5 days writes a row but no status row).
        $aRows = $pdo->query('SELECT ta.*, t.title FROM task_assignments ta JOIN tasks t ON t.id=ta.task_id ORDER BY t.sort_order, ta.sprint_worker_id')->fetchAll();
        $this->assertCount(3, $aRows, 'three assignment rows total');

        // Status: the green cell → abgeschlossen, the yellow cell → gestartet,
        // the default zugewiesen cell stays at default.
        $statuses = array_map(fn($r) => $r['status'], $aRows);
        sort($statuses);
        $this->assertSame(['abgeschlossen', 'gestartet', 'zugewiesen'], $statuses);

        // Audit log: sprint CREATE + IMPORTED_FROM_XLSX + 2× sprint_week CREATE
        // + 2× worker CREATE + 2× sprint_worker CREATE + 3× sprint_worker_day CREATE
        // + 2× task CREATE + 3× task_assignment CREATE + 2× task_assignment status UPDATE/CREATE.
        $auditCount = (int) $pdo->query('SELECT COUNT(*) FROM audit_log')->fetchColumn();
        $this->assertGreaterThan(15, $auditCount, 'every write is audited');

        $importedRows = (int) $pdo->query("SELECT COUNT(*) FROM audit_log WHERE action='IMPORTED_FROM_XLSX'")->fetchColumn();
        $this->assertSame(1, $importedRows);
    }

    public function testRefusesToImportIntoNonEmptyExistingSprint(): void
    {
        [$pdo, $importer, $req] = $this->build();
        $sprints = new SprintRepository($pdo);
        $sprint = $sprints->create('Existing', '2026-03-23', '2026-04-05', 0.2);
        $sprints->materializeWeeks($sprint->id, '2026-03-23', 2); // populates sprint_weeks

        $this->expectException(RuntimeException::class);
        $this->expectExceptionMessage('not empty');

        $importer->commit(
            sheet:            $this->sampleSheet(),
            sprintName:       'Existing',
            startDate:        '2026-03-23',
            endDate:          '2026-04-05',
            target:           'existing',
            existingSprintId: $sprint->id,
            req:              $req,
            actor:            null,
        );
    }

    public function testReusesExistingWorkerByCaseFoldedName(): void
    {
        [$pdo, $importer, $req] = $this->build();
        // Pre-create Alice with non-matching case + extra spaces.
        (new WorkerRepository($pdo))->create('  alice  ', true, 0.0);

        $result = $importer->commit(
            sheet:            $this->sampleSheet(),
            sprintName:       'S',
            startDate:        '2026-03-23',
            endDate:          '2026-04-05',
            target:           'new',
            existingSprintId: null,
            req:              $req,
            actor:            null,
        );

        // Bob is created; Alice already existed (matched by case-folded name).
        $this->assertSame(['Bob'], $result->createdWorkers);
        $this->assertSame(2, (int) $pdo->query('SELECT COUNT(*) FROM workers')->fetchColumn());
    }

    public function testMaxDaysToMaskSemantics(): void
    {
        $this->assertSame(0,       SprintImporter::maxDaysToMask(0));
        $this->assertSame(0b00001, SprintImporter::maxDaysToMask(1));
        $this->assertSame(0b00011, SprintImporter::maxDaysToMask(2));
        $this->assertSame(0b00111, SprintImporter::maxDaysToMask(3));
        $this->assertSame(0b01111, SprintImporter::maxDaysToMask(4));
        $this->assertSame(0b11111, SprintImporter::maxDaysToMask(5));
        $this->assertSame(0b11111, SprintImporter::maxDaysToMask(7), 'caps at 5');
        $this->assertSame(0,       SprintImporter::maxDaysToMask(-1));
    }

    public function testFoldNormalisation(): void
    {
        $this->assertSame('alice', SprintImporter::fold('Alice'));
        $this->assertSame('alice', SprintImporter::fold('  ALICE  '));
        $this->assertSame('michael br', SprintImporter::fold('Michael  Br'));
        $this->assertSame('jürg', SprintImporter::fold('Jürg'));
    }
}