<?php
// Include database connection
include('includes/dbconn.php');
session_start();

// Ensure the session is started and a company ID is set
if (!isset($_SESSION['company_id'])) {
    die("Access denied. Please log in.");
}
$companyId = $_SESSION['company_id'];

// SQL to get revenue and expense accounts from transactions and expenses
$sql = "SELECT a.account_id, a.account_name, a.account_type,
        IFNULL(SUM(CASE WHEN t.type = 'Debit' THEN t.amount ELSE 0 END), 0) as total_debit,
        IFNULL(SUM(CASE WHEN t.type = 'Credit' THEN t.amount ELSE 0 END), 0) as total_credit
        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 = ? AND a.account_type IN ('Revenue', 'Expense')
        GROUP BY a.account_id, a.account_name, a.account_type";
$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $companyId);
$stmt->execute();
$result = $stmt->get_result();

// Initialize variables for total revenue and expenses
$totalRevenue = 0;
$totalExpenses = 0;
?>

<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>Total Debits</th>
                        <th>Total Credits</th>
                        <th>Balance</th>
                    </tr>
                </thead>
                <tbody>
                    <?php while ($row = $result->fetch_assoc()): ?>
                        <?php
                        $balance = 0;
                        // Check if it's a revenue account
                        if ($row['account_type'] == 'Revenue') {
                            $balance = $row['total_credit'] - $row['total_debit'];
                            $totalRevenue += $balance;
                        } else {
                            // It's an expense account
                            $expenseTotal = getExpenseTotal($row['account_name'], $companyId, $conn);
                            $balance = $row['total_debit'] + $expenseTotal - $row['total_credit'];
                            $totalExpenses += $balance;
                        }
                        ?>
                        <tr>
                            <td><?php echo htmlspecialchars($row['account_id']); ?></td>
                            <td><?php echo htmlspecialchars($row['account_name']); ?></td>
                            <td><?php echo number_format($row['total_debit'], 2); ?></td>
                            <td><?php echo number_format($row['total_credit'], 2); ?></td>
                            <td><?php echo number_format($balance, 2); ?></td>
                        </tr>
                    <?php endwhile; ?>
                </tbody>
                <tfoot>
                    <tr>
                        <th colspan="4">Total Revenue</th>
                        <th><?php echo number_format($totalRevenue, 2); ?></th>
                    </tr>
                    <tr>
                        <th colspan="4">Total Expenses</th>
                        <th><?php echo number_format($totalExpenses, 2); ?></th>
                    </tr>
                    <tr>
                        <th colspan="4">Net Profit/Loss</th>
                        <th><?php echo number_format($totalRevenue - $totalExpenses, 2); ?></th>
                    </tr>
                </tfoot>
            </table>
        </div> <!-- end .table-responsive -->
    </div> <!-- end .table-rep-plugin -->
</div> <!-- end .responsive-table-plugin -->

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

/**
 * Function to get the total expense for a given account name and company ID.
 */
function getExpenseTotal($accountName, $companyId, $mysqli) {
    $total = 0;
    $sql = "SELECT SUM(amount) AS total FROM expenses WHERE category = ? AND company_id = ?";
    if ($stmt = $mysqli->prepare($sql)) {
        $stmt->bind_param("si", $accountName, $companyId);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($row = $result->fetch_assoc()) {
            $total = $row['total'];
        }
        $stmt->close();
    }
    return $total;
}
?>
