sprint_planer_web/tests/Db/MigrationBackfillTest.php

<?php

declare(strict_types=1);

namespace App\Tests\Db;

use PDO;
use PHPUnit\Framework\TestCase;

/**
 * Phase 12: verify migration 002 backfills active_days_mask from the legacy
 * free-form max_working_days values, and clamps half-day values to whole
 * integers in 0..5 without dropping rows or widening beyond Mo–Fr.
 *
 * We start from a 001-only schema (no active_days_mask column), hand-insert
 * a row per legacy value we care about, then apply 002 and read back.
 */
final class MigrationBackfillTest extends TestCase
{
    private function makeLegacyDb(): PDO
    {
        $pdo = new PDO('sqlite::memory:', null, null, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
        $pdo->exec('PRAGMA foreign_keys = ON');

        $sql = file_get_contents(__DIR__ . '/../../migrations/001_init.sql');
        $this->assertNotFalse($sql, 'read 001_init.sql');
        $pdo->exec($sql);

        return $pdo;
    }

    /** @return list<array{legacyDays:float, expectedMask:int, expectedDays:float}> */
    public static function cases(): array
    {
        return [
            // whole numbers 0..5 stay as themselves
            ['legacyDays' => 0.0, 'expectedMask' => 0,     'expectedDays' => 0.0],
            ['legacyDays' => 1.0, 'expectedMask' => 0b1,   'expectedDays' => 1.0],
            ['legacyDays' => 3.0, 'expectedMask' => 0b111, 'expectedDays' => 3.0],
            ['legacyDays' => 5.0, 'expectedMask' => 31,    'expectedDays' => 5.0],
            // half-days round up (2.5 → 3, 4.5 → 5)
            ['legacyDays' => 2.5, 'expectedMask' => 0b111, 'expectedDays' => 3.0],
            ['legacyDays' => 4.5, 'expectedMask' => 31,    'expectedDays' => 5.0],
            // 0.5 rounds up to 1
            ['legacyDays' => 0.5, 'expectedMask' => 0b1,   'expectedDays' => 1.0],
        ];
    }

    public function testBackfillMapsLegacyValuesToMask(): void
    {
        $pdo = $this->makeLegacyDb();

        // Seed a sprint + one week per case.
        $pdo->exec("INSERT INTO sprints (id, name, start_date, end_date, reserve_fraction, is_archived, created_at, updated_at)
                    VALUES (1, 'S', '2026-01-05', '2026-03-30', 0.2, 0, '2026-01-01T00:00:00Z', '2026-01-01T00:00:00Z')");

        $insertWeek = $pdo->prepare(
            'INSERT INTO sprint_weeks (id, sprint_id, sort_order, iso_week, start_date, max_working_days)
             VALUES (?, 1, ?, ?, ?, ?)'
        );
        $cases = self::cases();
        foreach ($cases as $i => $c) {
            $insertWeek->execute([
                $i + 1, $i + 1, 1, '2026-01-05', $c['legacyDays'],
            ]);
        }

        // Apply migration 002.
        $sql = file_get_contents(__DIR__ . '/../../migrations/002_sprint_week_active_days.sql');
        $this->assertNotFalse($sql, 'read 002');
        $pdo->exec($sql);

        // The column now exists; legacy rows were rewritten.
        $rows = $pdo->query('SELECT id, max_working_days, active_days_mask FROM sprint_weeks ORDER BY id')->fetchAll();
        $this->assertCount(count($cases), $rows);

        foreach ($cases as $i => $c) {
            $row = $rows[$i];
            $this->assertSame(
                $c['expectedMask'],
                (int) $row['active_days_mask'],
                "mask for legacy={$c['legacyDays']} (row id={$row['id']})",
            );
            $this->assertEqualsWithDelta(
                $c['expectedDays'],
                (float) $row['max_working_days'],
                1e-9,
                "days for legacy={$c['legacyDays']}",
            );
        }
    }
}