sprint_planer_web/src/Repositories/SprintWorkerRepository.php

<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Domain\SprintWorker;
use PDO;
use RuntimeException;

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

    /** @return list<SprintWorker> ordered by sort_order ASC, with worker name joined. */
    public function allForSprint(int $sprintId): array
    {
        $stmt = $this->pdo->prepare(
            'SELECT sw.*, w.name AS worker_name
             FROM sprint_workers sw
             JOIN workers w ON w.id = sw.worker_id
             WHERE sw.sprint_id = ?
             ORDER BY sw.sort_order ASC'
        );
        $stmt->execute([$sprintId]);
        $out = [];
        foreach ($stmt as $row) {
            $out[] = self::hydrate($row);
        }
        return $out;
    }

    public function find(int $id): ?SprintWorker
    {
        $stmt = $this->pdo->prepare(
            'SELECT sw.*, w.name AS worker_name
             FROM sprint_workers sw
             JOIN workers w ON w.id = sw.worker_id
             WHERE sw.id = ?'
        );
        $stmt->execute([$id]);
        $row = $stmt->fetch();
        return is_array($row) ? self::hydrate($row) : null;
    }

    /**
     * Add a worker to a sprint at the end of the order. Returns the created
     * SprintWorker. UNIQUE(sprint_id, worker_id) enforces that a worker
     * can't be added twice; PDOException propagates.
     */
    public function add(int $sprintId, int $workerId, float $rtb): SprintWorker
    {
        $maxOrder = (int) $this->pdo
            ->query('SELECT COALESCE(MAX(sort_order), 0) FROM sprint_workers WHERE sprint_id = ' . $sprintId)
            ->fetchColumn();
        $newOrder = $maxOrder + 1;

        $stmt = $this->pdo->prepare(
            'INSERT INTO sprint_workers (sprint_id, worker_id, rtb, sort_order) VALUES (?, ?, ?, ?)'
        );
        $stmt->execute([$sprintId, $workerId, $rtb, $newOrder]);

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

    /**
     * Remove a sprint_worker row. Returns the removed row (before) for
     * auditing, or null if it didn't exist.
     */
    public function remove(int $id): ?SprintWorker
    {
        $before = $this->find($id);
        if ($before === null) {
            return null;
        }
        $this->pdo
            ->prepare('DELETE FROM sprint_workers WHERE id = ?')
            ->execute([$id]);
        return $before;
    }

    /**
     * Apply an ordering of sprint_workers within a single sprint. The
     * $ordering payload is a list of {sprint_worker_id, sort_order} pairs,
     * assumed to be self-consistent (no duplicate orders, all IDs belong to
     * the same sprint).
     *
     * Returns per-row before/after for auditing. Unchanged rows are omitted
     * (the audit logger's no-op rule would drop them anyway, but this also
     * avoids unnecessary UPDATE statements).
     *
     * @param list<array{sprint_worker_id:int, sort_order:int}> $ordering
     * @return list<array{before: SprintWorker, after: SprintWorker}>
     */
    public function reorder(int $sprintId, array $ordering): array
    {
        if ($ordering === []) {
            return [];
        }

        // Pre-fetch current state for the sprint so we can diff before/after.
        $current = [];
        foreach ($this->allForSprint($sprintId) as $sw) {
            $current[$sw->id] = $sw;
        }

        // Stage new sort orders into negative space first so the updates don't
        // violate any hypothetical unique constraint (the schema doesn't have
        // one on sort_order today, but this keeps us future-proof).
        $stage = $this->pdo->prepare(
            'UPDATE sprint_workers SET sort_order = -? WHERE id = ? AND sprint_id = ?'
        );
        foreach ($ordering as $row) {
            $stage->execute([$row['sort_order'], $row['sprint_worker_id'], $sprintId]);
        }

        $apply = $this->pdo->prepare(
            'UPDATE sprint_workers SET sort_order = ? WHERE id = ? AND sprint_id = ?'
        );
        foreach ($ordering as $row) {
            $apply->execute([$row['sort_order'], $row['sprint_worker_id'], $sprintId]);
        }

        $out = [];
        foreach ($ordering as $row) {
            $swId = (int) $row['sprint_worker_id'];
            $before = $current[$swId] ?? null;
            if ($before === null) {
                continue;
            }
            if ($before->sortOrder === (int) $row['sort_order']) {
                continue;
            }
            $after = $this->find($swId);
            if ($after !== null) {
                $out[] = ['before' => $before, 'after' => $after];
            }
        }
        return $out;
    }

    /**
     * Edit the RTB for a single sprint_worker. Returns before/after.
     *
     * @return array{before: SprintWorker, after: SprintWorker}
     */
    public function setRtb(int $id, float $rtb): array
    {
        $before = $this->find($id);
        if ($before === null) {
            throw new RuntimeException("sprint_worker {$id} not found");
        }
        $this->pdo
            ->prepare('UPDATE sprint_workers SET rtb = ? WHERE id = ?')
            ->execute([$rtb, $id]);
        $after = $this->find($id) ?? $before;
        return ['before' => $before, 'after' => $after];
    }

    /**
     * @param array<string,mixed> $row
     */
    private static function hydrate(array $row): SprintWorker
    {
        return new SprintWorker(
            id:         (int)    $row['id'],
            sprintId:   (int)    $row['sprint_id'],
            workerId:   (int)    $row['worker_id'],
            workerName: (string) $row['worker_name'],
            rtb:        (float)  $row['rtb'],
            sortOrder:  (int)    $row['sort_order'],
        );
    }
}