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

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

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

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

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

// ================= FETCH BOOKINGS =================
$query = "
SELECT cb.*, g.name, cr.name AS conference_name
FROM conference_bookings cb
LEFT JOIN conference_rooms cr ON cr.id = cb.conference_room_id
LEFT JOIN guests g ON g.id = cb.guest_id
$where_sql
ORDER BY cb.booking_date DESC
";

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

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

echo "<table border='1'>";
echo "<tr>
        <th>ID</th>
        <th>Guest</th>
        <th>Conference Room</th>
        <th>Booking Date</th>
        <th>Status</th>
        <th>Total Amount</th>
        <th>Attendees</th>
        <th>Room Price</th>
        <th>Cost/Participant</th>
        <th>Started At</th>
        <th>End Date</th>
      </tr>";

if($result && $result->num_rows){
    while($row = $result->fetch_assoc()){
        echo "<tr>
                <td>{$row['id']}</td>
                <td>".$row['client_name'] ?? $row['guest_id']."</td>
                <td>".$row['conference_name']."</td>
                <td>{$row['booking_date']}</td>
                <td>{$row['status']}</td>
                <td>".number_format($row['total_amount'],2)."</td>
                <td>{$row['attendees']}</td>
                <td>".number_format($row['room_price'],2)."</td>
                <td>".number_format($row['cost_per_participant'],2)."</td>
                <td>{$row['started_at']}</td>
                <td>{$row['end_date']}</td>
              </tr>";
    }
} else {
    echo "<tr><td colspan='11' style='text-align:center'>No conference bookings found</td></tr>";
}

echo "</table>";
exit;