<?php
require_once 'db.php';

// Get current date/time values
$today = date('Y-m-d');
$weekStart = date('Y-m-d', strtotime('monday this week'));
$monthStart = date('Y-m-01');

// 1. Available Rooms
$stmt = $pdo->query("SELECT COUNT(*) FROM rooms WHERE status = 'available'");
$availableRooms = $stmt->fetchColumn();

// 2. Total Bookings Today
$stmt = $pdo->prepare("SELECT COUNT(*) FROM bookings WHERE DATE(created_at) = ?");
$stmt->execute([$today]);
$bookedToday = $stmt->fetchColumn();

// 3. Total Bookings This Week
$stmt = $pdo->prepare("SELECT COUNT(*) FROM bookings WHERE DATE(created_at) BETWEEN ? AND ?");
$stmt->execute([$weekStart, $today]);
$bookedThisWeek = $stmt->fetchColumn();

// 4. Total Bookings This Month
$stmt = $pdo->prepare("SELECT COUNT(*) FROM bookings WHERE DATE(created_at) BETWEEN ? AND ?");
$stmt->execute([$monthStart, $today]);
$bookedThisMonth = $stmt->fetchColumn();

// 5. Rooms Soon to Be Available (checking out within 24hrs)
$stmt = $pdo->prepare("SELECT COUNT(*) FROM bookings WHERE check_out BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 1 DAY)");
$stmt->execute();
$soonAvailable = $stmt->fetchColumn();

// 6. Revenue Today
$stmt = $pdo->prepare("SELECT SUM(total_amount) FROM bookings WHERE DATE(created_at) = ?");
$stmt->execute([$today]);
$revenueToday = $stmt->fetchColumn() ?? 0;

// 7. Revenue This Week
$stmt = $pdo->prepare("SELECT SUM(total_amount) FROM bookings WHERE DATE(created_at) BETWEEN ? AND ?");
$stmt->execute([$weekStart, $today]);
$revenueWeek = $stmt->fetchColumn() ?? 0;

// 8. Revenue This Month
$stmt = $pdo->prepare("SELECT SUM(total_amount) FROM bookings WHERE DATE(created_at) BETWEEN ? AND ?");
$stmt->execute([$monthStart, $today]);
$revenueMonth = $stmt->fetchColumn() ?? 0;

// 9. Most Booked Room Category
$stmt = $pdo->query("SELECT categories.name, COUNT(*) as total FROM bookings 
    JOIN rooms ON bookings.room_id = rooms.id
    JOIN categories ON rooms.category_id = categories.id
    GROUP BY categories.name ORDER BY total DESC LIMIT 1");
$mostBookedCategory = $stmt->fetch();

// 10. Peak Booking Hour
$stmt = $pdo->query("SELECT HOUR(created_at) as hour, COUNT(*) as total FROM bookings
    GROUP BY hour ORDER BY total DESC LIMIT 1");
$peakHour = $stmt->fetch();

?>
