<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

// Fetch guests with balances
$guests = $conn->query("
SELECT g.*, 
       IFNULL(SUM(i.total_amount),0) as charges, 
       IFNULL(SUM(p.amount),0) as paid,
       IFNULL(SUM(i.total_amount),0) - IFNULL(SUM(p.amount),0) as balance
FROM guests g
LEFT JOIN invoices i ON g.id=i.guest_id
LEFT JOIN payments p ON g.id=p.guest_id
GROUP BY g.id
HAVING balance != 0
");

// Initialize totals
$total_charges = 0;
$total_paid = 0;
$total_balance = 0;
?>

<div class="container mt-4">
     <div class="welcome-msg">
        <i class="fa fa-user-circle"></i> Welcome, <strong><?= htmlspecialchars($_SESSION['username'] ?? 'Guest') ?></strong>!
    </div>
    
<button class="btn btn-secondary btn-custom" onclick="history.back()">
    <i class="fa fa-arrow-left"></i> Back
</button>
<h4>Debtors / Creditors</h4><hr>

<div class="mb-3">
    <input type="text" id="searchInput" class="form-control" placeholder="Search guest name or phone..." onkeyup="filterTable()">
</div>

<table class="table table-bordered table-striped" id="guestTable">
<thead class="table-dark">
<tr>
    <th>#</th>
    <th>Guest</th>
    <th>Total Charges</th>
    <th>Total Paid</th>
    <th>Balance</th>
    <th>Actions</th>
</tr>
</thead>
<tbody>
<?php $i=1; while($g=$guests->fetch_assoc()): 
    $total_charges += $g['charges'];
    $total_paid += $g['paid'];
    $total_balance += $g['balance'];
?>
<tr <?= $g['balance']>0 ? 'class="table-warning"' : '' ?>>
    <td><?= $i ?></td>
    <td><?= htmlspecialchars($g['name']) ?></td>
    <td><?= number_format($g['charges'],2) ?></td>
    <td><?= number_format($g['paid'],2) ?></td>
    <td><?= number_format($g['balance'],2) ?></td>
    <td>
        <a href="payments.php?guest_id=<?= $g['id'] ?>" class="btn btn-sm btn-success mb-1">
            <i class="fa fa-plus"></i>Payments
        </a>
        <a href="payments.php?guest_id=<?= $g['id'] ?>" class="btn btn-sm btn-primary mb-1">
            <i class="fa fa-file-invoice"></i> Invoice
        </a>
        <a href="send_reminder.php?guest_id=<?= $g['id'] ?>" class="btn btn-sm btn-warning mb-1">
            <i class="fa fa-envelope"></i> Reminder
        </a>
    </td>
</tr>
<?php $i++; endwhile; ?>
</tbody>
<tfoot class="table-dark">
<tr>
    <th colspan="2">Totals</th>
    <th><?= number_format($total_charges,2) ?></th>
    <th><?= number_format($total_paid,2) ?></th>
    <th><?= number_format($total_balance,2) ?></th>
    <th></th>
</tr>
</tfoot>
</table>
</div>

<script>
function filterTable() {
    let input = document.getElementById("searchInput").value.toLowerCase();
    let table = document.getElementById("guestTable");
    let tr = table.getElementsByTagName("tr");

    for (let i=1; i<tr.length-1; i++) { // skip header & footer
        let tds = tr[i].getElementsByTagName("td");
        let show = false;
        for(let j=1; j<=1; j++){ // guest name only (column 1)
            if(tds[j] && tds[j].textContent.toLowerCase().includes(input)){
                show = true;
                break;
            }
        }
        tr[i].style.display = show ? "" : "none";
    }
}
</script>

<?php include 'includes/footer.php'; ?>
