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

// ================= FILTERS =================
$today = date('Y-m-d');
$from_date = $_GET['from'] ?? $today;
$to_date   = $_GET['to'] ?? $today;
$status    = $_GET['status'] ?? '';
$guest_id  = $_GET['guest_id'] ?? '';
$search    = $_GET['search'] ?? '';

// ================= BUILD WHERE =================
$where = [];
$where[] = "r.date_in >= '".$conn->real_escape_string($from_date)."'";
$where[] = "r.date_out <= '".$conn->real_escape_string($to_date)."'";

if($status){
    $where[] = "r.status = '".$conn->real_escape_string($status)."'";
}
if($guest_id){
    $where[] = "r.guest_id = '".$conn->real_escape_string($guest_id)."'";
}
if($search){
    $s = $conn->real_escape_string($search);
    $where[] = "(g.name LIKE '%$s%' OR ro.room_number LIKE '%$s%')";
}

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

// ================= FETCH RESERVATIONS =================
$query = "
SELECT r.*, g.name AS guest_name, ro.room_number
FROM reservations r
LEFT JOIN guests g ON g.id = r.guest_id
LEFT JOIN rooms ro ON ro.id = r.room_id
$where_sql
ORDER BY r.date_in DESC
";

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

// ================= EXPORT =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=reservations_{$from_date}_to_{$to_date}.xls");
header("Pragma: no-cache");
header("Expires: 0");

echo "<table border='1'>";
echo "<tr>
        <th>Reservation ID</th>
        <th>Guest</th>
        <th>Room</th>
        <th>Date In</th>
        <th>Date Out</th>
        <th>Days</th>
        <th>Status</th>
        <th>Payment Amount</th>
        <th>Payment Status</th>
        <th>Created At</th>
      </tr>";

if($result && $result->num_rows){
    while($row = $result->fetch_assoc()){
        echo "<tr>
                <td>{$row['id']}</td>
                <td>".htmlspecialchars($row['guest_name'] ?? $row['guest_id'])."</td>
                <td>".htmlspecialchars($row['room_number'])."</td>
                <td>{$row['date_in']}</td>
                <td>{$row['date_out']}</td>
                <td>{$row['days']}</td>
                <td>{$row['status']}</td>
                <td>{$row['payment_amount']}</td>
                <td>{$row['payment_status']}</td>
                <td>{$row['created_at']}</td>
              </tr>";
    }
} else {
    echo "<tr><td colspan='10'>No reservations found</td></tr>";
}

echo "</table>";
exit;
?>