<?php

declare(strict_types=1);

function benchmarkAccount(PDO $pdo, int $accountId): array
{
    $statement = $pdo->prepare(<<<'SQL'
        SELECT a.id, a.region, a.plan, a.status, a.balance_cents,
               e.event_type AS latest_event_type, e.created_at AS latest_event_at
        FROM benchmark_accounts AS a
        LEFT JOIN LATERAL (
            SELECT event_type, created_at
            FROM benchmark_events
            WHERE account_id = a.id
            ORDER BY created_at DESC
            LIMIT 1
        ) AS e ON true
        WHERE a.id = :id
        SQL);
    $statement->execute(['id' => $accountId]);

    return $statement->fetch() ?: [];
}

function benchmarkActivity(PDO $pdo, string $region): array
{
    $statement = $pdo->prepare(<<<'SQL'
        SELECT plan, count(*) AS account_count, round(avg(balance_cents), 2) AS average_balance_cents
        FROM benchmark_accounts
        WHERE region = :region
        GROUP BY plan
        ORDER BY plan
        SQL);
    $statement->execute(['region' => $region]);

    return $statement->fetchAll();
}

function benchmarkTouch(PDO $pdo, int $accountId): array
{
    $pdo->beginTransaction();

    try {
        $update = $pdo->prepare(<<<'SQL'
            UPDATE benchmark_accounts
            SET balance_cents = balance_cents + 1, updated_at = clock_timestamp()
            WHERE id = :id
            RETURNING balance_cents
            SQL);
        $update->execute(['id' => $accountId]);
        $balance = (int) $update->fetchColumn();

        $insert = $pdo->prepare(<<<'SQL'
            INSERT INTO benchmark_events (account_id, event_type, amount_cents, created_at)
            VALUES (:id, 'benchmark_touch', 1, clock_timestamp())
            RETURNING id
            SQL);
        $insert->execute(['id' => $accountId]);
        $eventId = (int) $insert->fetchColumn();

        $pdo->commit();

        return ['balance_cents' => $balance, 'event_id' => $eventId];
    } catch (Throwable $exception) {
        $pdo->rollBack();
        throw $exception;
    }
}