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

// ================= GET FILTERS =================
$from_date = $_GET['from'] ?? '';
$to_date   = $_GET['to'] ?? '';
$status    = $_GET['status'] ?? '';
$search    = $_GET['search'] ?? '';

// ================= BUILD WHERE =================
$where = [];

if($from_date){
    $where[] = "DATE(i.issue_date) >= '".$conn->real_escape_string($from_date)."'";
}
if($to_date){
    $where[] = "DATE(i.issue_date) <= '".$conn->real_escape_string($to_date)."'";
}
if($status){
    $where[] = "i.status = '".$conn->real_escape_string($status)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(i.invoice_number LIKE '%$s%' OR i.description LIKE '%$s%')";
}

$where_sql = $where ? "WHERE ".implode(" AND ", $where) : "";

// ================= FETCH INVOICES =================
$query = "
SELECT i.*, g.name AS guest_name
FROM invoices i
LEFT JOIN guests g ON g.id = i.guest_id
$where_sql
ORDER BY i.issue_date DESC
";

$result = $conn->query($query);

// ================= SEND HEADERS FOR EXCEL =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=invoices_report_".date('Y-m-d').".xls");
header("Pragma: no-cache");
header("Expires: 0");
?>

<table border="1">
    <thead>
        <tr>
            <th>Invoice Number</th>
            <th>Guest</th>
            <th>Total Amount</th>
            <th>Status</th>
            <th>Issue Date</th>
            <th>Due Date</th>
            <th>Description</th>
            <th>Created By</th>
        </tr>
    </thead>
    <tbody>
        <?php if($result && $result->num_rows): ?>
            <?php while($row = $result->fetch_assoc()): ?>
                <tr>
                    <td><?= $row['invoice_number'] ?></td>
                    <td><?= $row['guest_name'] ?? $row['guest_id'] ?></td>
                    <td><?= number_format($row['total_amount'],2) ?></td>
                    <td><?= $row['status'] ?></td>
                    <td><?= $row['issue_date'] ?></td>
                    <td><?= $row['due_date'] ?></td>
                    <td><?= $row['description'] ?></td>
                    <td><?= $row['created_by'] ?></td>
                </tr>
            <?php endwhile; ?>
        <?php else: ?>
            <tr>
                <td colspan="8" style="text-align:center;">No records found</td>
            </tr>
        <?php endif; ?>
    </tbody>
</table>