sprint_planer_web/src/Repositories/SprintRepository.php

<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Domain\Sprint;
use App\Domain\SprintWeek;
use DateTimeImmutable;
use PDO;
use RuntimeException;

final class SprintRepository
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    /**
     * List sprints with worker + task counts. Newest start first.
     *
     * @return list<array{sprint: Sprint, nWorkers: int, nTasks: int}>
     */
    public function allWithCounts(): array
    {
        $stmt = $this->pdo->query(
            'SELECT s.*,
                    (SELECT COUNT(*) FROM sprint_workers WHERE sprint_id = s.id) AS n_workers,
                    (SELECT COUNT(*) FROM tasks          WHERE sprint_id = s.id) AS n_tasks
             FROM sprints s
             ORDER BY s.start_date DESC, s.id DESC'
        );
        $out = [];
        foreach ($stmt as $row) {
            $out[] = [
                'sprint'   => self::hydrate($row),
                'nWorkers' => (int) $row['n_workers'],
                'nTasks'   => (int) $row['n_tasks'],
            ];
        }
        return $out;
    }

    public function find(int $id): ?Sprint
    {
        $stmt = $this->pdo->prepare('SELECT * FROM sprints WHERE id = ?');
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return is_array($row) ? self::hydrate($row) : null;
    }

    /**
     * Create a sprint row. Caller is responsible for the enclosing transaction
     * (e.g. to also audit the change and materialise week rows atomically).
     */
    public function create(
        string $name,
        string $startDate,
        string $endDate,
        float $reserveFraction,
    ): Sprint {
        $now = gmdate('Y-m-d\TH:i:s\Z');
        $stmt = $this->pdo->prepare(
            'INSERT INTO sprints (name, start_date, end_date, reserve_fraction, is_archived, created_at, updated_at)
             VALUES (?, ?, ?, ?, 0, ?, ?)'
        );
        $stmt->execute([$name, $startDate, $endDate, $reserveFraction, $now, $now]);

        $id = (int) $this->pdo->lastInsertId();
        $sprint = $this->find($id);
        if ($sprint === null) {
            throw new RuntimeException('Inserted sprint not found');
        }
        return $sprint;
    }

    /** Whitelisted updatable columns on `sprints` for admin edits. */
    private const UPDATABLE = ['name', 'start_date', 'end_date', 'reserve_fraction', 'is_archived'];

    /**
     * Apply the given field changes. Returns before/after for auditing.
     *
     * @param array<string,mixed> $changes
     * @return array{before: Sprint, after: Sprint}
     */
    public function update(int $id, array $changes): array
    {
        $before = $this->find($id);
        if ($before === null) {
            throw new RuntimeException("Sprint {$id} not found");
        }

        $changes = array_intersect_key($changes, array_flip(self::UPDATABLE));
        if ($changes === []) {
            return ['before' => $before, 'after' => $before];
        }

        $sets = [];
        $vals = [];
        foreach ($changes as $col => $v) {
            $sets[] = "{$col} = ?";
            $vals[] = match ($col) {
                'is_archived'       => ((bool) $v) ? 1 : 0,
                'reserve_fraction'  => (float) $v,
                default             => (string) $v,
            };
        }
        $sets[] = 'updated_at = ?';
        $vals[] = gmdate('Y-m-d\TH:i:s\Z');
        $vals[] = $id;

        $stmt = $this->pdo->prepare(
            'UPDATE sprints SET ' . implode(', ', $sets) . ' WHERE id = ?'
        );
        $stmt->execute($vals);

        $after = $this->find($id) ?? $before;
        return ['before' => $before, 'after' => $after];
    }

    /**
     * Delete a sprint. Does NOT read cascaded child rows; the controller is
     * responsible for auditing those (sprint_weeks / sprint_workers /
     * sprint_worker_days / tasks / task_assignments) BEFORE calling this
     * method, per spec §7. Returns the pre-deletion row for the parent
     * audit entry.
     */
    public function delete(int $id): ?Sprint
    {
        $before = $this->find($id);
        if ($before === null) {
            return null;
        }
        $this->pdo
            ->prepare('DELETE FROM sprints WHERE id = ?')
            ->execute([$id]);
        return $before;
    }

    /**
     * Materialise N week rows for a sprint with sensible defaults.
     *
     * Returns the inserted rows (before=null, after=row-snapshot) so the caller
     * can audit each CREATE.
     *
     * @return list<array{id:int, sort_order:int, iso_week:int, start_date:string, max_working_days:float, active_days_mask:int}>
     */
    public function materializeWeeks(int $sprintId, string $startDate, int $nWeeks): array
    {
        if ($nWeeks < 1) {
            return [];
        }

        $d0 = DateTimeImmutable::createFromFormat('Y-m-d', $startDate);
        if ($d0 === false) {
            throw new RuntimeException("Invalid start_date: {$startDate}");
        }

        $insert = $this->pdo->prepare(
            'INSERT INTO sprint_weeks
             (sprint_id, sort_order, iso_week, start_date, max_working_days, active_days_mask)
             VALUES (?, ?, ?, ?, ?, ?)'
        );

        $out = [];
        for ($i = 1; $i <= $nWeeks; $i++) {
            $weekStart = $d0->modify('+' . ($i - 1) . ' weeks');
            $iso       = (int) $weekStart->format('W');
            $ymd       = $weekStart->format('Y-m-d');
            $insert->execute([$sprintId, $i, $iso, $ymd, 5.0, SprintWeek::MASK_ALL]);

            $out[] = [
                'id'               => (int) $this->pdo->lastInsertId(),
                'sort_order'       => $i,
                'iso_week'         => $iso,
                'start_date'       => $ymd,
                'max_working_days' => 5.0,
                'active_days_mask' => SprintWeek::MASK_ALL,
            ];
        }
        return $out;
    }

    /**
     * @param array<string,mixed> $row
     */
    private static function hydrate(array $row): Sprint
    {
        return new Sprint(
            id:              (int) $row['id'],
            name:            (string) $row['name'],
            startDate:       (string) $row['start_date'],
            endDate:         (string) $row['end_date'],
            reserveFraction: (float) $row['reserve_fraction'],
            isArchived:      ((int) $row['is_archived']) === 1,
            createdAt:       (string) $row['created_at'],
            updatedAt:       (string) $row['updated_at'],
        );
    }
}