<?php

declare(strict_types=1);

namespace App\Infrastructure\Jobs;

use App\Domain\Jobs\JobStatus;
use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\Connection;

/**
 * Append + update for `job_runs`. The runner writes a `running` row at the
 * start (so a crashed process leaves visible debris) and updates it to a
 * final status when the body completes — successful, failed, or
 * `skipped_locked` if the lock was already held.
 */
final class JobRunRepository
{
    public function __construct(private readonly Connection $connection)
    {
    }

    public function startRun(
        string $jobName,
        DateTimeImmutable $startedAt,
        string $triggeredBy,
    ): int {
        $this->connection->insert('job_runs', [
            'job_name' => $jobName,
            'status' => JobStatus::Running->value,
            'items_processed' => 0,
            'error_message' => null,
            'triggered_by' => $triggeredBy,
            'started_at' => $startedAt->format('Y-m-d H:i:s'),
            'finished_at' => null,
        ]);

        return (int) $this->connection->lastInsertId();
    }

    public function finishRun(
        int $runId,
        JobStatus $status,
        int $itemsProcessed,
        DateTimeImmutable $finishedAt,
        ?string $errorMessage = null,
    ): void {
        $this->connection->update(
            'job_runs',
            [
                'status' => $status->value,
                'items_processed' => $itemsProcessed,
                'finished_at' => $finishedAt->format('Y-m-d H:i:s'),
                'error_message' => $errorMessage,
            ],
            ['id' => $runId],
        );
    }

    /**
     * Append a single finalised row in one statement — used for the
     * `skipped_locked` path where there's nothing to update because no
     * `running` row was written.
     */
    public function recordCompleted(
        string $jobName,
        JobStatus $status,
        int $itemsProcessed,
        DateTimeImmutable $startedAt,
        DateTimeImmutable $finishedAt,
        string $triggeredBy,
        ?string $errorMessage = null,
    ): int {
        $this->connection->insert('job_runs', [
            'job_name' => $jobName,
            'status' => $status->value,
            'items_processed' => $itemsProcessed,
            'error_message' => $errorMessage,
            'triggered_by' => $triggeredBy,
            'started_at' => $startedAt->format('Y-m-d H:i:s'),
            'finished_at' => $finishedAt->format('Y-m-d H:i:s'),
        ]);

        return (int) $this->connection->lastInsertId();
    }

    /**
     * Latest row per job name. Used by /internal/jobs/status and by the
     * tick dispatcher to decide which jobs are due.
     *
     * @return array<string, array{
     *     id: int,
     *     job_name: string,
     *     status: string,
     *     items_processed: int,
     *     error_message: ?string,
     *     triggered_by: string,
     *     started_at: ?DateTimeImmutable,
     *     finished_at: ?DateTimeImmutable
     * }>
     */
    public function latestPerJob(): array
    {
        // Two-step: the inner query finds the latest started_at per
        // job_name; the outer joins back to grab the row. Works on both
        // SQLite and MySQL without window functions.
        /** @var list<array<string, mixed>> $rows */
        $rows = $this->connection->fetchAllAssociative(
            'SELECT r.id, r.job_name, r.status, r.items_processed, r.error_message, '
            . 'r.triggered_by, r.started_at, r.finished_at '
            . 'FROM job_runs r '
            . 'INNER JOIN ('
            . '  SELECT job_name, MAX(started_at) AS m FROM job_runs GROUP BY job_name'
            . ') x ON x.job_name = r.job_name AND x.m = r.started_at',
        );

        $out = [];
        foreach ($rows as $row) {
            $name = (string) $row['job_name'];
            $out[$name] = [
                'id' => (int) $row['id'],
                'job_name' => $name,
                'status' => (string) $row['status'],
                'items_processed' => (int) $row['items_processed'],
                'error_message' => $row['error_message'] !== null ? (string) $row['error_message'] : null,
                'triggered_by' => (string) $row['triggered_by'],
                'started_at' => self::parseTs($row['started_at']),
                'finished_at' => self::parseTs($row['finished_at']),
            ];
        }

        return $out;
    }

    /**
     * @return array{
     *     id: int,
     *     job_name: string,
     *     status: string,
     *     items_processed: int,
     *     error_message: ?string,
     *     triggered_by: string,
     *     started_at: ?DateTimeImmutable,
     *     finished_at: ?DateTimeImmutable
     * }|null
     */
    public function latestForJob(string $jobName): ?array
    {
        $latest = $this->latestPerJob();

        return $latest[$jobName] ?? null;
    }

    private static function parseTs(mixed $value): ?DateTimeImmutable
    {
        if ($value === null || $value === '') {
            return null;
        }

        return new DateTimeImmutable((string) $value, new DateTimeZone('UTC'));
    }
}
