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

// Fetch guests
$guests = $conn->query("SELECT * FROM guests WHERE status = 2 ORDER BY name");

$guest_id   = $_GET['guest_id'] ?? null;
$start_date = $_GET['start_date'] ?? null;
$end_date   = $_GET['end_date'] ?? null;
?>

<div class="container mt-4">
    <div class="welcome-msg mb-3">
        <i class="fa fa-user-circle fa-lg"></i> Welcome, <strong><?= htmlspecialchars($_SESSION['username'] ?? 'Guest') ?></strong>!
    </div>

    <button class="btn btn-secondary btn-custom mb-3" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>

    <h4 class="mb-3"><i class="fa fa-file-invoice-dollar"></i> Customer Statement</h4>
    <hr>

    <!-- Guest Selection & Date Filter -->
    <form method="GET" class="row g-3 mb-4">
        <div class="col-md-3">
            <select name="guest_id" class="form-control" required>
                <option value="">Select Guest</option>
                <?php while($g=$guests->fetch_assoc()): ?>
                    <option value="<?= $g['id'] ?>" <?= ($guest_id==$g['id'])?'selected':'' ?>>
                        <?= htmlspecialchars($g['name']) ?>
                    </option>
                <?php endwhile; ?>
            </select>
        </div>
        <div class="col-md-3">
            <input type="date" name="start_date" class="form-control" value="<?= $start_date ?>" placeholder="Start Date">
        </div>
        <div class="col-md-3">
            <input type="date" name="end_date" class="form-control" value="<?= $end_date ?>" placeholder="End Date">
        </div>
        <div class="col-md-3">
            <button type="submit" class="btn btn-primary w-100"><i class="fa fa-eye"></i> View Statement</button>
        </div>
    </form>

<?php
if($guest_id){

    $guest_id = intval($guest_id);

    // ================= FETCH TRANSACTIONS =================
    $payments_q = "SELECT 'Payment' AS type, amount, payment_date AS date, 'green' AS color 
                   FROM payments WHERE guest_id=$guest_id";
    if($start_date) $payments_q .= " AND DATE(payment_date) >= '$start_date'";
    if($end_date) $payments_q .= " AND DATE(payment_date) <= '$end_date'";

    $invoices_q = "SELECT 'Invoice' AS type, total_amount AS amount, created_at AS date, 'red' AS color
                   FROM invoices WHERE guest_id=$guest_id";
    if($start_date) $invoices_q .= " AND DATE(created_at) >= '$start_date'";
    if($end_date) $invoices_q .= " AND DATE(created_at) <= '$end_date'";

    $conf_q = "SELECT 'Conference' AS type, total_amount AS amount, booking_date AS date, 'blue' AS color
               FROM conference_bookings cb 
               JOIN guests g ON g.name = cb.client_name
               WHERE g.id=$guest_id AND cb.status<>'CANCELLED'";
    if($start_date) $conf_q .= " AND DATE(booking_date) >= '$start_date'";
    if($end_date) $conf_q .= " AND DATE(booking_date) <= '$end_date'";

    $services_q = "SELECT 'Service' AS type, SUM(amount*quantity) AS amount, created_at AS date, 'orange' AS color
                    FROM charges 
                    WHERE guest_id=$guest_id";
    if($start_date) $services_q .= " AND DATE(created_at) >= '$start_date'";
    if($end_date) $services_q .= " AND DATE(created_at) <= '$end_date'";
    $services_q .= " GROUP BY DATE(created_at)";

    $transactions_q = "$payments_q UNION ALL $invoices_q UNION ALL $conf_q UNION ALL $services_q ORDER BY date ASC";
    $transactions = $conn->query($transactions_q);

    // ================= DISPLAY STATEMENT =================
    $balance = 0;
    $total_payments = $total_invoices = $total_conference = $total_services = 0;
?>

<!-- ================= CUSTOMER STATEMENT TABLE ================= -->
<div class="card shadow-sm mb-4">
    <div class="card-header bg-primary text-white">
        <i class="fa fa-receipt"></i> Statement for <?= htmlspecialchars($guests->fetch_assoc()['name'] ?? 'Guest') ?>
    </div>
    <div class="card-body p-0">
        <table class="table table-striped mb-0">
            <thead class="table-dark text-center">
                <tr>
                    <th>Date</th>
                    <th>Type</th>
                    <th class="text-end">Credit (Payments)</th>
                    <th class="text-end">Debit (Charges)</th>
                    <th class="text-end">Balance</th>
                </tr>
            </thead>
            <tbody>
            <?php while($t = $transactions->fetch_assoc()):
                $amount = floatval($t['amount']);
                $credit = $debit = 0;

                // Payments reduce balance (credit)
                if($t['type']=='Payment'){
                    $credit = $amount;
                    $balance -= $credit;
                    $total_payments += $credit;
                } else {
                    $debit = $amount;
                    $balance += $debit;
                    switch($t['type']){
                        case 'Invoice': $total_invoices += $debit; break;
                        case 'Conference': $total_conference += $debit; break;
                        case 'Service': $total_services += $debit; break;
                    }
                }
            ?>
                <tr class="text-center">
                    <td><?= date('d-M-Y', strtotime($t['date'])) ?></td>
                    <td class="text-start">
                        <?php 
                        switch($t['type']){
                            case 'Payment': echo '<i class="fa fa-money-bill-wave text-success"></i> Payment'; break;
                            case 'Invoice': echo '<i class="fa fa-file-invoice text-danger"></i> Invoice'; break;
                            case 'Conference': echo '<i class="fa fa-users text-primary"></i> Conference'; break;
                            case 'Service': echo '<i class="fa fa-concierge-bell text-warning"></i> Service'; break;
                        }
                        ?>
                    </td>
                    <td class="text-end text-success"><?= $credit ? number_format($credit,2) : '-' ?></td>
                    <td class="text-end text-danger"><?= $debit ? number_format($debit,2) : '-' ?></td>
                    <td class="text-end"><?= number_format($balance,2) ?></td>
                </tr>
            <?php endwhile; ?>
            </tbody>
            <tfoot class="table-light text-center fw-bold">
                <tr>
                    <th colspan="2" class="text-start">Totals</th>
                    <th class="text-end text-success"><?= number_format($total_payments,2) ?></th>
                    <th class="text-end text-danger">
                        <?= number_format($total_invoices + $total_conference + $total_services,2) ?>
                    </th>
                    <th class="text-end"><?= number_format($balance,2) ?></th>
                </tr>
            </tfoot>
        </table>
    </div>
</div>

<?php } ?>

</div>

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