<?php

require_once 'includes/dbconn.php';

// Initialize an array to hold each account's debit and credit totals
$trialBalanceData = [];

// Fetch all accounts
$sqlAccounts = "SELECT account_id, account_name FROM chart_of_accounts";
$resultAccounts = $conn->query($sqlAccounts);

while ($rowAccount = $resultAccounts->fetch_assoc()) {
    $accountId = $rowAccount['account_id'];
    $accountName = $rowAccount['account_name'];

    // Fetch debits for this account
    $sqlDebit = "SELECT COALESCE(SUM(amount), 0) AS total_debit FROM transactions WHERE account_id = ? AND type = 'Debit'";
    $stmtDebit = $conn->prepare($sqlDebit);
    $stmtDebit->bind_param("i", $accountId);
    $stmtDebit->execute();
    $resultDebit = $stmtDebit->get_result();
    $rowDebit = $resultDebit->fetch_assoc();

    // Fetch credits for this account
    $sqlCredit = "SELECT COALESCE(SUM(amount), 0) AS total_credit FROM transactions WHERE account_id = ? AND type = 'Credit'";
    $stmtCredit = $conn->prepare($sqlCredit);
    $stmtCredit->bind_param("i", $accountId);
    $stmtCredit->execute();
    $resultCredit = $stmtCredit->get_result();
    $rowCredit = $resultCredit->fetch_assoc();

    // Add the account with its debits and credits to the trial balance data
    $trialBalanceData[] = [
        'account_name' => $accountName,
        'total_debit' => $rowDebit['total_debit'],
        'total_credit' => $rowCredit['total_credit']
    ];
}

// Output JSON response
header('Content-Type: application/json');
echo json_encode($trialBalanceData);

$conn->close();

?>