Transaction reconciliation

<?php
$token = getenv('SEPAY_API_TOKEN');
$pdo   = new PDO('mysql:host=localhost;dbname=db_name;charset=utf8mb4', 'db_user', 'db_pass',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
 
$dateFrom = date('Y-m-d H:i:s', strtotime('-24 hours'));
$dateTo   = date('Y-m-d H:i:s');
 
// 1. Fetch last 24h transactions from SePay
$url = 'https://userapi.sepay.vn/v2/transactions?' . http_build_query([
    'transaction_date_from' => $dateFrom,
    'transaction_date_to'   => $dateTo,
    'per_page'              => 100,
]);
 
$ctx = stream_context_create(['http' => [
    'header' => "Authorization: Bearer $token\r\nContent-Type: application/json",
]]);
$result = json_decode(file_get_contents($url, false, $ctx), true);
$transactions = $result['data'] ?? [];
printf("SePay: %d transactions\n", count($transactions));
 
// 2. What's already in DB?
$stmt = $pdo->prepare('SELECT reference_number FROM tb_transactions WHERE created_at >= ?');
$stmt->execute([$dateFrom]);
$existing = array_flip($stmt->fetchAll(PDO::FETCH_COLUMN));
 
// 3. Backfill missing. UNIQUE(sepay_id) prevents duplicates.
$insert = $pdo->prepare('INSERT IGNORE INTO tb_transactions
    (sepay_id, gateway, transaction_date, account_number, sub_account,
     amount_in, amount_out, accumulated, code, transaction_content, reference_number)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
 
$missing = 0;
foreach ($transactions as $tx) {
    if (isset($existing[$tx['reference_number']])) continue;
 
    $insert->execute([
        $tx['id'], $tx['bank_brand_name'], $tx['transaction_date'],
        $tx['account_number'], $tx['va'] ?? '',
        $tx['amount_in'] ?? 0, $tx['amount_out'] ?? 0, $tx['accumulated'] ?? 0,
        $tx['code'], $tx['transaction_content'], $tx['reference_number'],
    ]);
    $missing++;
}
 
printf("Done. Backfilled %d transactions.\n", $missing);