<?php

declare(strict_types=1);

namespace App\Services;

use App\Core\SchemaRuntime;
use App\Shared\Schema\ChecksSchemaSupport;
use PDO;

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

    public function data(int $daysAhead): array
    {
        SchemaRuntime::ensure($this->pdo, 'checks_schema', function (): void {
            ChecksSchemaSupport::ensureChecksSchema($this->pdo);
        }, 'sql/migrate_v1_32_checks.sql');
        $daysAhead = max(1, min($daysAhead, 30));
        $today = date('Y-m-d');
        $future = date('Y-m-d', strtotime('+' . $daysAhead . ' days'));
        $summary = checks_alert_counts($this->pdo, $daysAhead);
        $baseSelect = "SELECT c.*, p.name drawer_name, ep.name endorsed_party_name
               FROM checks c
               JOIN parties p ON p.id=c.drawer_party_id
               LEFT JOIN parties ep ON ep.id=c.last_endorsed_party_id";
        $st = $this->pdo->prepare($baseSelect . " WHERE c.current_status='returned' ORDER BY c.updated_at DESC, c.id DESC");
        $st->execute();
        $returnedRows = $st->fetchAll() ?: [];
        $st = $this->pdo->prepare($baseSelect . " WHERE c.current_status IN ('received','endorsed') AND c.due_date IS NOT NULL AND c.due_date BETWEEN ? AND ? ORDER BY c.due_date ASC, c.id DESC");
        $st->execute([$today, $future]);
        $dueSoonRows = $st->fetchAll() ?: [];
        $st = $this->pdo->prepare($baseSelect . " WHERE c.current_status IN ('received','endorsed') AND c.due_date IS NOT NULL AND c.due_date < ? ORDER BY c.due_date ASC, c.id DESC");
        $st->execute([$today]);
        $overdueRows = $st->fetchAll() ?: [];
        return compact('summary', 'returnedRows', 'dueSoonRows', 'overdueRows', 'daysAhead');
    }
}
