<?php

declare(strict_types=1);

namespace App\Shared\Support;

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

final class ChecksSupport
{
    public static function alertCounts(PDO $pdo, int $daysAhead = 7): array
    {
        ChecksSchemaSupport::ensureChecksSchema($pdo);

        $daysAhead = max(1, $daysAhead);
        $today = date('Y-m-d');
        $future = date('Y-m-d', strtotime('+' . $daysAhead . ' days'));

        $returned = (int) $pdo->query("SELECT COUNT(*) FROM checks WHERE current_status='returned'")->fetchColumn();

        $dueTodayStmt = $pdo->prepare("SELECT COUNT(*) FROM checks WHERE current_status IN ('received','endorsed') AND due_date=?");
        $dueTodayStmt->execute([$today]);
        $dueToday = (int) $dueTodayStmt->fetchColumn();

        $dueSoonStmt = $pdo->prepare("SELECT COUNT(*) FROM checks WHERE current_status IN ('received','endorsed') AND due_date IS NOT NULL AND due_date BETWEEN ? AND ?");
        $dueSoonStmt->execute([$today, $future]);
        $dueSoon = (int) $dueSoonStmt->fetchColumn();

        $overdueStmt = $pdo->prepare("SELECT COUNT(*) FROM checks WHERE current_status IN ('received','endorsed') AND due_date IS NOT NULL AND due_date < ?");
        $overdueStmt->execute([$today]);
        $overdue = (int) $overdueStmt->fetchColumn();

        return [
            'returned' => $returned,
            'due_today' => $dueToday,
            'due_soon' => $dueSoon,
            'overdue' => $overdue,
            'total' => $returned + $dueSoon + $overdue,
        ];
    }
}
