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

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

// Fetch unique expense categories for the company from the chart_of_accounts table
$categorySql = "SELECT account_name FROM chart_of_accounts 
                WHERE company_id = ? AND account_type = 'Expense' 
                ORDER BY account_id ASC";
$categoryStmt = $conn->prepare($categorySql);
$categoryStmt->bind_param("i", $companyId);
$categoryStmt->execute();
$categoryResult = $categoryStmt->get_result();

$categories = [];
while ($categoryRow = $categoryResult->fetch_assoc()) {
    $categories[] = $categoryRow['account_name'];
}

// Fetch expenses data for the company, joined with the chart of accounts
$expenseSql = "SELECT e.expense_id, e.user_id, e.expense_name, e.expense_date, e.capture_date, 
                      e.description, e.amount, a.account_name AS category, e.company_id 
               FROM expenses e
               INNER JOIN chart_of_accounts a ON e.category = a.account_id
               WHERE e.company_id = ? AND a.account_type = 'Expense'
               ORDER BY e.expense_date DESC";
$expenseStmt = $conn->prepare($expenseSql);
$expenseStmt->bind_param("i", $companyId);
$expenseStmt->execute();
$expenseResult = $expenseStmt->get_result();

// Initialize totals for each category
$categoryTotals = array_fill_keys($categories, 0);

// Here you can fetch user information and manager information from the database or session

?>

<table>
        <thead>
            <tr>
                <th>Date</th>
                <th>Description</th>
                <?php foreach ($categories as $category): ?>
                    <th><?php echo htmlspecialchars($category); ?></th>
                <?php endforeach; ?>
                <th>Total</th>
            </tr>
        </thead>
        <tbody>
            <?php while ($row = $expenseResult->fetch_assoc()): ?>
                <tr>
                    <td><?php echo htmlspecialchars($row['expense_date']); ?></td>
                    <td><?php echo htmlspecialchars($row['description']); ?></td>
                    <?php
                    // Initialize row total
                    $rowTotal = 0;
                    // Display columns for each category
                    foreach ($categories as $category):
                        $amount = $category == $row['category'] ? $row['amount'] : 0;
                        $rowTotal += $amount;
                        $categoryTotals[$category] += $amount;
                        echo '<td>' . number_format($amount, 2) . '</td>';
                    endforeach;
                    ?>
                    <td><?php echo number_format($rowTotal, 2); ?></td>
                </tr>
            <?php endwhile; ?>
        </tbody>
        <tfoot>
            <tr>
                <th colspan="2">Category Totals</th>
                <?php foreach ($categoryTotals as $total): ?>
                    <th><?php echo number_format($total, 2); ?></th>
                <?php endforeach; ?>
                <th><?php echo number_format(array_sum($categoryTotals), 2); ?></th>
            </tr>
        </tfoot>
    </table>
    
    <?php
$categoryStmt->close();
$expenseStmt->close();
?>