<?php
/**
 * Manager Reservations/Bookings API
 * Handles table reservations and bookings management
 */

session_start();
require 'config.php';

header('Content-Type: application/json');

// Release any expired bookings before processing requests
releaseExpiredBookings($conn);

// Check manager authentication
if (!isset($_SESSION['manager_id'])) {
    http_response_code(401);
    sendResponse(false, 'Unauthorized access', null);
}

$manager_id = $_SESSION['manager_id'];
$action = isset($_GET['action']) ? $_GET['action'] : '';

switch ($action) {
    case 'list':
        listBookings();
        break;
    case 'get':
        getBooking();
        break;
    case 'today':
        getTodayBookings();
        break;
    case 'upcoming':
        getUpcomingBookings();
        break;
    case 'confirm':
        confirmBooking();
        break;
    case 'checkin':
        checkinBooking();
        break;
    case 'cancel':
        cancelBooking();
        break;
    case 'no_show':
        markNoShow();
        break;
    case 'get_available_tables':
        getAvailableTablesForBooking();
        break;
    case 'allocate_table_to_booking':
        allocateTableToBooking();
        break;
    case 'get_booking_stats':
        getBookingStats();
        break;
    default:
        sendResponse(false, 'Invalid action', null);
}

// List all bookings with filters
function listBookings() {
    global $conn;
    
    $status = isset($_GET['status']) ? $_GET['status'] : '';
    $start_date = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d');
    $end_date = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');
    $page = isset($_GET['page']) ? intval($_GET['page']) : 1;
    $limit = isset($_GET['limit']) ? intval($_GET['limit']) : 20;
    $offset = ($page - 1) * $limit;
    
    $sql = "SELECT b.*, 
            (SELECT COUNT(*) FROM table_bookings WHERE booking_id = b.id) as assigned_tables
            FROM bookings b 
            WHERE booking_date BETWEEN '" . $conn->real_escape_string($start_date) . "' 
                   AND '" . $conn->real_escape_string($end_date) . "'";
    
    if ($status) {
        $sql .= " AND b.status = '" . $conn->real_escape_string($status) . "'";
    }
    
    $sql .= " ORDER BY booking_date DESC, booking_time DESC LIMIT " . $offset . ", " . $limit;
    
    $result = $conn->query($sql);
    
    $bookings = [];
    while ($row = $result->fetch_assoc()) {
        $bookings[] = $row;
    }
    
    // Get total count
    $count_sql = "SELECT COUNT(id) as total FROM bookings 
                  WHERE booking_date BETWEEN '" . $conn->real_escape_string($start_date) . "' 
                  AND '" . $conn->real_escape_string($end_date) . "'";
    if ($status) {
        $count_sql .= " AND status = '" . $conn->real_escape_string($status) . "'";
    }
    
    $count_result = $conn->query($count_sql);
    $total = $count_result->fetch_assoc()['total'];
    
    sendResponse(true, 'Bookings retrieved', [
        'bookings' => $bookings,
        'total' => $total,
        'page' => $page,
        'limit' => $limit
    ]);
}

// Get booking details
function getBooking() {
    global $conn;
    
    if (!isset($_GET['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($_GET['booking_id']);
    
    $sql = "SELECT * FROM bookings WHERE id = " . $booking_id;
    $result = $conn->query($sql);
    
    if ($result->num_rows === 0) {
        sendResponse(false, 'Booking not found', null);
    }
    
    $booking = $result->fetch_assoc();
    
    // Get assigned tables with their snapshot names
    // Filter out tables with status = 'merged' (secondary merged tables)
    $tables_sql = "SELECT rt.*, COALESCE(tb.assigned_table_name, rt.table_number) as display_name FROM restaurant_tables rt
                   INNER JOIN table_bookings tb ON rt.id = tb.table_id
                   WHERE tb.booking_id = " . $booking_id . " AND rt.status != 'merged'";
    $tables_result = $conn->query($tables_sql);
    $booking['assigned_tables'] = [];
    while ($row = $tables_result->fetch_assoc()) {
        $booking['assigned_tables'][] = $row;
    }
    
    sendResponse(true, 'Booking retrieved', $booking);
}

// Get today's bookings
function getTodayBookings() {
    global $conn;
    
    $date = date('Y-m-d');
    
    // Filter out tables with status = 'merged' (secondary merged tables)
    $sql = "SELECT b.*, 
            (SELECT GROUP_CONCAT(COALESCE(tb.assigned_table_name, rt.table_number)) FROM restaurant_tables rt
             INNER JOIN table_bookings tb ON rt.id = tb.table_id
             WHERE tb.booking_id = b.id AND rt.status != 'merged') as table_numbers
            FROM bookings b 
            WHERE booking_date = '" . $date . "'
            ORDER BY booking_time ASC";
    
    $result = $conn->query($sql);
    
    $bookings = [];
    while ($row = $result->fetch_assoc()) {
        $bookings[] = $row;
    }
    
    sendResponse(true, 'Today bookings retrieved', $bookings);
}

// Get upcoming bookings
function getUpcomingBookings() {
    global $conn;
    
    $days = isset($_GET['days']) ? intval($_GET['days']) : 7;
    
    // Filter out tables with status = 'merged' (secondary merged tables)
    $sql = "SELECT b.*, 
            (SELECT GROUP_CONCAT(COALESCE(tb.assigned_table_name, rt.table_number)) FROM restaurant_tables rt
             INNER JOIN table_bookings tb ON rt.id = tb.table_id
             WHERE tb.booking_id = b.id AND rt.status != 'merged') as table_numbers
            FROM bookings b 
            WHERE booking_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL " . $days . " DAY)
            AND b.status IN ('pending', 'confirmed')
            ORDER BY booking_date ASC, booking_time ASC";
    
    $result = $conn->query($sql);
    
    $bookings = [];
    while ($row = $result->fetch_assoc()) {
        $bookings[] = $row;
    }
    
    sendResponse(true, 'Upcoming bookings retrieved', $bookings);
}

// Confirm a booking
function confirmBooking() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($data['booking_id']);
    $notes = isset($data['notes']) ? $data['notes'] : '';
    
    $sql = "UPDATE bookings SET status = 'confirmed' WHERE id = " . $booking_id;
    
    if ($conn->query($sql)) {
        sendResponse(true, 'Booking confirmed', null);
    } else {
        sendResponse(false, 'Error confirming booking', null);
    }
}

// Check in a booking (customer arrives)
function checkinBooking() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($data['booking_id']);
    
    $sql = "UPDATE bookings SET status = 'checked_in' WHERE id = " . $booking_id;
    
    if ($conn->query($sql)) {
        sendResponse(true, 'Booking checked in', null);
    } else {
        sendResponse(false, 'Error checking in booking', null);
    }
}

// Cancel a booking
function cancelBooking() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($data['booking_id']);
    $reason = isset($data['reason']) ? $conn->real_escape_string($data['reason']) : '';
    
    $sql = "UPDATE bookings SET status = 'cancelled' WHERE id = " . $booking_id;
    
    if ($conn->query($sql)) {
        // Release assigned tables
        $release_sql = "UPDATE restaurant_tables rt
                        SET status = 'available'
                        WHERE id IN (
                            SELECT table_id FROM table_bookings 
                            WHERE booking_id = " . $booking_id . "
                        )";
        $conn->query($release_sql);
        
        sendResponse(true, 'Booking cancelled', null);
    } else {
        sendResponse(false, 'Error cancelling booking', null);
    }
}

// Mark as no-show
function markNoShow() {
    global $conn;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($data['booking_id']);
    
    $sql = "UPDATE bookings SET status = 'no_show' WHERE id = " . $booking_id;
    
    if ($conn->query($sql)) {
        // Release assigned tables
        $release_sql = "UPDATE restaurant_tables rt
                        SET status = 'available'
                        WHERE id IN (
                            SELECT table_id FROM table_bookings 
                            WHERE booking_id = " . $booking_id . "
                        )";
        $conn->query($release_sql);
        
        sendResponse(true, 'Marked as no-show', null);
    } else {
        sendResponse(false, 'Error marking no-show', null);
    }
}

// Get available tables for booking
function getAvailableTablesForBooking() {
    global $conn;
    
    if (!isset($_GET['booking_id'])) {
        sendResponse(false, 'Booking ID is required', null);
    }
    
    $booking_id = intval($_GET['booking_id']);
    
    // Get booking details
    $booking_sql = "SELECT guests FROM bookings WHERE id = " . $booking_id;
    $booking_result = $conn->query($booking_sql);
    
    if ($booking_result->num_rows === 0) {
        sendResponse(false, 'Booking not found', null);
    }
    
    $booking = $booking_result->fetch_assoc();
    $guests = $booking['guests'];
    
    // Get available tables that can accommodate the guests
    $tables_sql = "SELECT rt.* FROM restaurant_tables rt
                   WHERE rt.status = 'available'
                   AND rt.base_capacity >= " . $guests . "
                   AND rt.id NOT IN (
                       SELECT table_id FROM table_bookings 
                       WHERE booking_id = " . $booking_id . "
                   )
                   ORDER BY rt.base_capacity ASC, rt.floor_level, rt.section";
    
    $tables_result = $conn->query($tables_sql);
    
    $tables = [];
    while ($row = $tables_result->fetch_assoc()) {
        $tables[] = $row;
    }
    
    sendResponse(true, 'Available tables retrieved', $tables);
}

// Allocate table to booking
function allocateTableToBooking() {
    global $conn, $manager_id;
    
    $data = json_decode(file_get_contents("php://input"), true);
    
    if (!isset($data['booking_id']) || !isset($data['table_id'])) {
        sendResponse(false, 'Booking ID and table ID are required', null);
    }
    
    $booking_id = intval($data['booking_id']);
    $table_id = intval($data['table_id']);
    
    // Get booking date
    $booking_sql = "SELECT booking_date FROM bookings WHERE id = " . $booking_id;
    $booking_result = $conn->query($booking_sql);
    if ($booking_result->num_rows === 0) {
        sendResponse(false, 'Booking not found', null);
    }
    $booking = $booking_result->fetch_assoc();
    $booking_date = $booking['booking_date'];
    
    // Check if already allocated
    $check_sql = "SELECT id FROM table_bookings 
                  WHERE booking_id = " . $booking_id . " AND table_id = " . $table_id;
    $check_result = $conn->query($check_sql);
    
    if ($check_result->num_rows > 0) {
        sendResponse(false, 'Table already allocated to this booking', null);
    }
    
    // Check if table is already allocated for this specific date
    $date_conflict_sql = "SELECT COUNT(*) as conflict_count FROM table_bookings tb
                         INNER JOIN bookings b ON tb.booking_id = b.id
                         WHERE tb.table_id = " . $table_id . "
                         AND b.booking_date = '" . $conn->real_escape_string($booking_date) . "'
                         AND b.id != " . $booking_id . "
                         AND b.status IN ('pending', 'confirmed', 'checked_in')";
    $conflict_result = $conn->query($date_conflict_sql);
    $conflict_row = $conflict_result->fetch_assoc();
    
    if ($conflict_row['conflict_count'] > 0) {
        sendResponse(false, 'Table is already allocated for ' . date('F j, Y', strtotime($booking_date)), null);
    }
    
    // Allocate table
    $insert_sql = "INSERT INTO table_bookings (booking_id, table_id) 
                   VALUES (" . $booking_id . ", " . $table_id . ")";
    
    if ($conn->query($insert_sql)) {
        // Check if table has any active bookings and update status accordingly
        $check_active_sql = "SELECT COUNT(*) as active_count FROM table_bookings tb
                            INNER JOIN bookings b ON tb.booking_id = b.id
                            WHERE tb.table_id = " . $table_id . "
                            AND b.status IN ('pending', 'confirmed', 'checked_in')";
        $active_result = $conn->query($check_active_sql);
        $active_row = $active_result->fetch_assoc();
        
        // Update table status to booked only if it has active bookings
        if ($active_row['active_count'] > 0) {
            $update_sql = "UPDATE restaurant_tables SET status = 'booked' WHERE id = " . $table_id;
            $conn->query($update_sql);
        }
        
        sendResponse(true, 'Table allocated to booking', null);
    } else {
        sendResponse(false, 'Error allocating table', null);
    }
}

// Get booking statistics
function getBookingStats() {
    global $conn;
    
    $date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
    
    $stats = [];
    
    // Total bookings for the day
    $total_sql = "SELECT COUNT(id) as total FROM bookings WHERE booking_date = '" . 
                 $conn->real_escape_string($date) . "'";
    $stats['total_bookings'] = $conn->query($total_sql)->fetch_assoc()['total'] ?? 0;
    
    // By status
    $status_sql = "SELECT status, COUNT(id) as count FROM bookings 
                   WHERE booking_date = '" . $conn->real_escape_string($date) . "'
                   GROUP BY status";
    $status_result = $conn->query($status_sql);
    $stats['by_status'] = [];
    while ($row = $status_result->fetch_assoc()) {
        $stats['by_status'][] = $row;
    }
    
    // Total guests
    $guests_sql = "SELECT SUM(guests) as total_guests FROM bookings 
                   WHERE booking_date = '" . $conn->real_escape_string($date) . "'";
    $stats['total_guests'] = $conn->query($guests_sql)->fetch_assoc()['total_guests'] ?? 0;
    
    // Peak time
    $peak_sql = "SELECT booking_time, COUNT(id) as count FROM bookings 
                 WHERE booking_date = '" . $conn->real_escape_string($date) . "'
                 GROUP BY booking_time
                 ORDER BY count DESC LIMIT 1";
    $peak_result = $conn->query($peak_sql);
    $stats['peak_time'] = $peak_result->num_rows > 0 ? 
                          $peak_result->fetch_assoc() : null;
    
    sendResponse(true, 'Booking statistics retrieved', $stats);
}
?>
