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

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

// ================= BUILD WHERE =================
$where = [];
$where[] = "DATE(s.checkout_date) >= '".$conn->real_escape_string($from_date)."'";
$where[] = "DATE(s.checkout_date) <= '".$conn->real_escape_string($to_date)."'";

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

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

// ================= FETCH STAYS =================
$query = "
SELECT s.id, g.name AS guest_name, r.room_number, s.locked_room_price, 
       s.checkin_date, s.checkout_date, s.days, s.status, s.created_by
FROM stays s
LEFT JOIN guests g ON g.id = s.guest_id
LEFT JOIN rooms r ON r.id = s.room_id
$where_sql
ORDER BY s.checkout_date DESC
";

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

// ================= EXPORT =================
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=stays_report_".date('Ymd_His').".xls");
header("Pragma: no-cache");
header("Expires: 0");

// Table header
echo "<table border='1'>";
echo "<tr>
        <th>Stay ID</th>
        <th>Guest</th>
        <th>Room</th>
        <th>Locked Price (MK)</th>
        <th>Checkin Date</th>
        <th>Checkout Date</th>
        <th>Days</th>
        <th>Status</th>
        <th>Created By</th>
      </tr>";

// Table body
if($result && $result->num_rows){
    while($row = $result->fetch_assoc()){
        echo "<tr>";
        echo "<td>{$row['id']}</td>";
        echo "<td>".$row['guest_name']."</td>";
        echo "<td>".$row['room_number']."</td>";
        echo "<td>".number_format($row['locked_room_price'],2)."</td>";
        echo "<td>{$row['checkin_date']}</td>";
        echo "<td>{$row['checkout_date']}</td>";
        echo "<td>{$row['days']}</td>";
        echo "<td>".($row['status']==7?'Completed':'Active')."</td>";
        echo "<td>".$row['created_by']."</td>";
        echo "</tr>";
    }
}else{
    echo "<tr><td colspan='9'>No stays found</td></tr>";
}

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