<?php
// display_coa.php
include('includes/dbconn.php'); // Database connection script

$companyId = $_SESSION['company_id']; // From session

$sql = "SELECT a.account_id, a.account_name, IFNULL(SUM(CASE WHEN t.type = 'Debit' THEN t.amount ELSE -t.amount END), 0) as balance 
        FROM chart_of_accounts a 
        LEFT JOIN transactions t ON a.account_id = t.account_id AND a.company_id = t.company_id 
        WHERE a.company_id = ? 
        GROUP BY a.account_id, a.account_name";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $companyId);
$stmt->execute();
$result = $stmt->get_result();

echo "<table border='1'><tr><th>Account ID</th><th>Account Name</th><th>Balance</th></tr>";
while ($row = $result->fetch_assoc()) {
    echo "<tr><td>" . htmlspecialchars($row['account_id']) . "</td><td>" . htmlspecialchars($row['account_name']) . "</td><td>" . number_format($row['balance'], 2) . "</td></tr>";
}
echo "</table>";

$stmt->close();
?>
