<?php

declare(strict_types=1);

namespace App\Shared\Finance;

use App\Shared\Schema\FinanceSchemaSupport;
use PDO;
use RuntimeException;

final class AccountBalanceSupport
{
    public static function cashboxBalance(PDO $pdo, int $cashboxId, ?string $toDate = null): float
    {
        $toDate = $toDate ?: date('Y-m-d');
        $st = $pdo->prepare("SELECT opening_balance FROM cashboxes WHERE id=?");
        $st->execute([$cashboxId]);
        $opening = (float) ($st->fetchColumn() ?: 0);

        $st2 = $pdo->prepare("SELECT
            COALESCE(SUM(CASE WHEN trx_type='in' THEN amount ELSE 0 END),0) as in_sum,
            COALESCE(SUM(CASE WHEN trx_type='out' THEN amount ELSE 0 END),0) as out_sum
          FROM cashbox_transactions
          WHERE cashbox_id=? AND trx_date < ?");
        $st2->execute([$cashboxId, date('Y-m-d 00:00:00', strtotime($toDate . ' +1 day'))]);
        $row = $st2->fetch() ?: ['in_sum' => 0, 'out_sum' => 0];

        return $opening + (float) $row['in_sum'] - (float) $row['out_sum'];
    }

    public static function addCashboxTransaction(PDO $pdo, int $cashboxId, string $trxDate, string $trxType, float $amount, ?string $refType, ?int $refId, ?string $notes, int $userId): int
    {
        $trxType = strtolower(trim($trxType));
        if (!in_array($trxType, ['in', 'out'], true)) {
            throw new RuntimeException('Invalid transaction type');
        }
        if ($amount <= 0) {
            throw new RuntimeException('Amount must be > 0');
        }

        $sql = "INSERT INTO cashbox_transactions (cashbox_id,trx_date,trx_type,amount,ref_type,ref_id,notes,user_id)
                VALUES (?,?,?,?,?,?,?,?)";
        $st = $pdo->prepare($sql);
        $st->execute([$cashboxId, $trxDate, $trxType, $amount, $refType, $refId, $notes, $userId]);
        return (int) $pdo->lastInsertId();
    }

    public static function nextCashPaymentNo(PDO $pdo): string
    {
        $no = 'CP-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
        $tries = 0;
        while ($tries < 10) {
            $chk = $pdo->prepare("SELECT 1 FROM cash_payments WHERE voucher_no=?");
            $chk->execute([$no]);
            if (!$chk->fetch()) {
                break;
            }
            $no = 'CP-' . date('Y') . '-' . str_pad((string) rand(1, 99999), 5, '0', STR_PAD_LEFT);
            $tries++;
        }

        return $no;
    }

    public static function bankAccountBalance(PDO $pdo, int $bankAccountId, ?string $asOf = null): float
    {
        if ($bankAccountId <= 0) {
            return 0.0;
        }
        FinanceSchemaSupport::ensureBankAccountsSchema($pdo);
        $st = $pdo->prepare("SELECT COALESCE(opening_balance,0) bal FROM bank_accounts WHERE id=? LIMIT 1");
        $st->execute([$bankAccountId]);
        $opening = (float) ($st->fetchColumn() ?: 0);
        $sql = "SELECT COALESCE(SUM(CASE WHEN trx_type='in' THEN amount ELSE -amount END),0) FROM bank_transactions WHERE bank_account_id=?";
        $params = [$bankAccountId];
        if ($asOf) {
            $sql .= " AND trx_date <= ?";
            $params[] = preg_match('/^\d{4}-\d{2}-\d{2}$/', $asOf) ? ($asOf . ' 23:59:59') : $asOf;
        }
        $st = $pdo->prepare($sql);
        $st->execute($params);
        return $opening + (float) ($st->fetchColumn() ?: 0);
    }
}
