<?php
ob_start();
include 'db.php';
include 'layout.php';
include 'financial_helpers.php';

$store_id = $_SESSION['store_id'] ?? 1;

// Get filter parameters
$as_of_date = $_GET['as_of_date'] ?? date('Y-m-d');
$account_type = $_GET['account_type'] ?? '';
$account_category = $_GET['account_category'] ?? '';

// Build the query for trial balance
$where_conditions = ["coa.store_id = ?"];
$params = [$store_id];
$param_types = "i";

if ($account_type) {
    $where_conditions[] = "coa.account_type = ?";
    $params[] = $account_type;
    $param_types .= "s";
}

if ($account_category) {
    $where_conditions[] = "coa.account_category = ?";
    $params[] = $account_category;
    $param_types .= "s";
}

$where_clause = implode(" AND ", $where_conditions);

$query = "
    SELECT 
        coa.id,
        coa.account_code,
        coa.account_name,
        coa.account_type,
        coa.account_category,
        coa.opening_balance,
        COALESCE(SUM(CASE WHEN jel.debit_amount > 0 THEN jel.debit_amount ELSE 0 END), 0) as total_debits,
        COALESCE(SUM(CASE WHEN jel.credit_amount > 0 THEN jel.credit_amount ELSE 0 END), 0) as total_credits
    FROM chart_of_accounts coa
    LEFT JOIN journal_entry_lines jel ON coa.id = jel.account_id
    LEFT JOIN journal_entries je ON jel.journal_entry_id = je.id
    WHERE $where_clause
    AND (je.status = 'posted' OR je.status IS NULL)
    AND (je.status != 'void' OR je.status IS NULL)
    AND (je.entry_date <= ? OR je.entry_date IS NULL)
    GROUP BY coa.id, coa.account_code, coa.account_name, coa.account_type, coa.account_category, coa.opening_balance
    ORDER BY coa.account_code
";

$params[] = $as_of_date;
$param_types .= "s";

$stmt = $conn->prepare($query);
$stmt->bind_param($param_types, ...$params);
$stmt->execute();
$trial_balance = $stmt->get_result();

// Calculate totals
$total_debits = 0;
$total_credits = 0;
$accounts_data = [];

while ($row = $trial_balance->fetch_assoc()) {
    // Calculate balance based on account type
    $balance = $row['opening_balance'] + $row['total_debits'] - $row['total_credits'];
    
    // For liability, equity, and revenue accounts, reverse the balance
    if (in_array($row['account_type'], ['liability', 'equity', 'revenue'])) {
        $balance = -$balance;
    }
    
    $row['balance'] = $balance;
    $accounts_data[] = $row;
    
    if ($balance > 0) {
        $total_debits += $balance;
    } else {
        $total_credits += abs($balance);
    }
}

// Get account types and categories for filters
$account_types = $conn->query("SELECT DISTINCT account_type FROM chart_of_accounts WHERE store_id = $store_id ORDER BY account_type");
$account_categories = $conn->query("SELECT DISTINCT account_category FROM chart_of_accounts WHERE store_id = $store_id ORDER BY account_category");
?>

<div class="page-wrapper">
    <div class="content">
        <div class="page-header">
            <div class="page-title">
                <h4>Trial Balance</h4>
                <h6>Account balances as of <?php echo date('F j, Y', strtotime($as_of_date)); ?></h6>
            </div>
            <div class="page-btn">
                <button class="btn btn-added" onclick="window.print()">
                    <i class="fa fa-print"></i> Print
                </button>
            </div>
        </div>

        <!-- Filters -->
        <div class="card">
            <div class="card-body">
                <form method="GET" class="row g-3">
                    <div class="col-md-3">
                        <label for="as_of_date" class="form-label">As of Date</label>
                        <input type="date" class="form-control" id="as_of_date" name="as_of_date" value="<?php echo htmlspecialchars($as_of_date); ?>">
                    </div>
                    <div class="col-md-3">
                        <label for="account_type" class="form-label">Account Type</label>
                        <select class="form-select" id="account_type" name="account_type">
                            <option value="">All Types</option>
                            <?php while ($type = $account_types->fetch_assoc()): ?>
                                <option value="<?php echo htmlspecialchars($type['account_type']); ?>" <?php echo $account_type === $type['account_type'] ? 'selected' : ''; ?>>
                                    <?php echo ucfirst($type['account_type']); ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label for="account_category" class="form-label">Account Category</label>
                        <select class="form-select" id="account_category" name="account_category">
                            <option value="">All Categories</option>
                            <?php while ($category = $account_categories->fetch_assoc()): ?>
                                <option value="<?php echo htmlspecialchars($category['account_category']); ?>" <?php echo $account_category === $category['account_category'] ? 'selected' : ''; ?>>
                                    <?php echo ucwords(str_replace('_', ' ', $category['account_category'])); ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label">&nbsp;</label>
                        <div>
                            <button type="submit" class="btn btn-primary">
                                <i class="fa fa-search"></i> Filter
                            </button>
                            <a href="trial_balance.php" class="btn btn-secondary">
                                <i class="fa fa-refresh"></i> Reset
                            </a>
                        </div>
                    </div>
                </form>
            </div>
        </div>

        <!-- Trial Balance Table -->
        <div class="card">
            <div class="card-body">
                <div class="table-responsive">
                    <table class="table datatable">
                        <thead>
                            <tr>
                                <th>Account Code</th>
                                <th>Account Name</th>
                                <th>Type</th>
                                <th>Category</th>
                                <th>Opening Balance</th>
                                <th>Debits</th>
                                <th>Credits</th>
                                <th>Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php foreach ($accounts_data as $account): ?>
                                <tr>
                                    <td>
                                        <strong><?php echo htmlspecialchars($account['account_code']); ?></strong>
                                    </td>
                                    <td><?php echo htmlspecialchars($account['account_name']); ?></td>
                                    <td>
                                        <span class="badge bg-<?php 
                                            echo $account['account_type'] === 'asset' ? 'success' : 
                                                ($account['account_type'] === 'liability' ? 'warning' : 
                                                ($account['account_type'] === 'equity' ? 'info' : 
                                                ($account['account_type'] === 'revenue' ? 'primary' : 'danger'))); 
                                        ?>">
                                            <?php echo ucfirst($account['account_type']); ?>
                                        </span>
                                    </td>
                                    <td><?php echo ucwords(str_replace('_', ' ', $account['account_category'])); ?></td>
                                    <td class="text-end">
                                        <?php echo number_format($account['opening_balance'], 2); ?>
                                    </td>
                                    <td class="text-end">
                                        <?php echo number_format($account['total_debits'], 2); ?>
                                    </td>
                                    <td class="text-end">
                                        <?php echo number_format($account['total_credits'], 2); ?>
                                    </td>
                                    <td class="text-end">
                                        <span class="text-<?php echo $account['balance'] >= 0 ? 'success' : 'danger'; ?>">
                                            <strong><?php echo number_format($account['balance'], 2); ?></strong>
                                        </span>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        </tbody>
                        <tfoot>
                            <tr class="table-dark">
                                <td colspan="4"><strong>TOTALS</strong></td>
                                <td class="text-end"><strong><?php echo number_format(array_sum(array_column($accounts_data, 'opening_balance')), 2); ?></strong></td>
                                <td class="text-end"><strong><?php echo number_format(array_sum(array_column($accounts_data, 'total_debits')), 2); ?></strong></td>
                                <td class="text-end"><strong><?php echo number_format(array_sum(array_column($accounts_data, 'total_credits')), 2); ?></strong></td>
                                <td class="text-end">
                                    <strong><?php echo number_format($total_debits - $total_credits, 2); ?></strong>
                                </td>
                            </tr>
                        </tfoot>
                    </table>
                </div>
                
                <!-- Balance Check -->
                <div class="mt-3">
                    <?php if (abs($total_debits - $total_credits) < 0.01): ?>
                        <div class="alert alert-success">
                            <i class="fa fa-check-circle"></i> <strong>Trial Balance is in balance!</strong> 
                            Total Debits: <?php echo number_format($total_debits, 2); ?> = 
                            Total Credits: <?php echo number_format($total_credits, 2); ?>
                        </div>
                    <?php else: ?>
                        <div class="alert alert-danger">
                            <i class="fa fa-exclamation-triangle"></i> <strong>Trial Balance is out of balance!</strong> 
                            Difference: <?php echo number_format($total_debits - $total_credits, 2); ?>
                        </div>
                    <?php endif; ?>
                </div>
            </div>
        </div>
    </div>
</div>

<script>
// Auto-submit form when date changes
document.getElementById('as_of_date').addEventListener('change', function() {
    this.form.submit();
});
</script>

</body>
</html>
