<?php
// Start the session if not already started
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

// Ensure the user is logged in and has a company_id set
if (!isset($_SESSION['company_id'])) {
    // Redirect to login page or display an error message
    exit("User not logged in. Please login to view the balance sheet.");
}

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

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

// Prepare the SQL statement to get the balance for each type of account
$sql = "SELECT a.account_id, a.account_name, a.account_type, 
        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, a.account_type 
        ORDER BY a.account_type"; // Ordering by account_type for better presentation

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $companyId);
$stmt->execute();
$result = $stmt->get_result();
?>

<div class="responsive-table-plugin">
    <div class="table-rep-plugin">
        <div class="table-responsive" data-pattern="priority-columns">
            <table id="tech-companies-1" class="table table-striped">
                <thead>
                    <tr>
                        <th>Account ID</th>
                        <th>Account Name</th>
                        <th>Account Type</th>
                        <th>Balance</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    while ($row = $result->fetch_assoc()) {
                        echo "<tr>";
                        echo "<td>" . htmlspecialchars($row['account_id']) . "</td>";
                        echo "<td>" . htmlspecialchars($row['account_name']) . "</td>";
                        echo "<td>" . htmlspecialchars($row['account_type']) . "</td>";
                        echo "<td>" . number_format($row['balance'], 2) . "</td>";
                        echo "</tr>";
                    }
                    ?>
                </tbody>
            </table>
        </div> <!-- end .table-responsive -->
    </div> <!-- end .table-rep-plugin -->
</div> <!-- end .responsive-table-plugin -->

<?php
$stmt->close();
?>
