<?php
// Database Configuration
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'rest_table');

// Create database connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
    die(json_encode(['success' => false, 'message' => 'Database connection failed: ' . $conn->connect_error]));
}

// Set charset
$conn->set_charset("utf8");

// Enable error reporting for development
error_reporting(E_ALL);
ini_set('display_errors', 0); // Don't display errors in response

// Function to send JSON response
function sendResponse($success, $message, $data = null) {
    header('Content-Type: application/json');
    $response = [
        'success' => $success,
        'message' => $message
    ];
    if ($data !== null) {
        $response['data'] = $data;
    }
    echo json_encode($response);
    exit;
}

// Function to hash password
function hashPassword($password) {
    return password_hash($password, PASSWORD_BCRYPT);
}

// Function to verify password
function verifyPassword($password, $hash) {
    return password_verify($password, $hash);
}

// Function to automatically release expired bookings
function releaseExpiredBookings($conn) {
    try {
        // Get current local date/time
        $today = date('Y-m-d');
        $nowTime = date('H:i:s');
        $expired_ids = [];
        
        // 1) Expire bookings from previous dates.
        $sql = "SELECT b.id FROM bookings b
                WHERE b.booking_date < ? 
                AND LOWER(TRIM(b.status)) NOT IN ('expired', 'cancelled', 'checked_in', 'no_show')";
        
        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            error_log("Error preparing statement in releaseExpiredBookings: " . $conn->error);
            return false;
        }
        
        $stmt->bind_param("s", $today);
        if (!$stmt->execute()) {
            error_log("Error executing statement in releaseExpiredBookings: " . $stmt->error);
            $stmt->close();
            return false;
        }
        
        $result = $stmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $expired_ids[] = $row['id'];
        }
        $stmt->close();

        // 2) Expire today's bookings whose slot end time has passed.
        $todaySql = "SELECT b.id, b.meal_type, b.slot_time
                     FROM bookings b
                     WHERE b.booking_date = ?
                     AND LOWER(TRIM(b.status)) NOT IN ('expired', 'cancelled', 'checked_in', 'no_show')";
        $todayStmt = $conn->prepare($todaySql);
        if (!$todayStmt) {
            error_log("Error preparing today's bookings statement in releaseExpiredBookings: " . $conn->error);
            return false;
        }

        $todayStmt->bind_param("s", $today);
        if (!$todayStmt->execute()) {
            error_log("Error executing today's bookings statement in releaseExpiredBookings: " . $todayStmt->error);
            $todayStmt->close();
            return false;
        }

        $todayResult = $todayStmt->get_result();
        while ($booking = $todayResult->fetch_assoc()) {
            $bookingId = (int)$booking['id'];
            $slotStart = !empty($booking['slot_time']) ? date('H:i:s', strtotime($booking['slot_time'])) : null;
            $slotEnd = null;

            if (!empty($booking['meal_type']) && !empty($slotStart)) {
                $slotSql = "SELECT ts.end_time
                            FROM time_slots ts
                            INNER JOIN meal_types mt ON mt.id = ts.meal_type_id
                            WHERE LOWER(TRIM(mt.name)) = LOWER(TRIM(?))
                            AND TIME(ts.start_time) = TIME(?)
                            AND ts.status = 'active'
                            ORDER BY ts.display_order ASC
                            LIMIT 1";
                $slotStmt = $conn->prepare($slotSql);
                if ($slotStmt) {
                    $mealType = $booking['meal_type'];
                    $slotStmt->bind_param("ss", $mealType, $slotStart);
                    if ($slotStmt->execute()) {
                        $slotResult = $slotStmt->get_result();
                        $slotRow = $slotResult ? $slotResult->fetch_assoc() : null;
                        if (!empty($slotRow['end_time'])) {
                            $slotEnd = date('H:i:s', strtotime($slotRow['end_time']));
                        }
                    }
                    $slotStmt->close();
                }
            }

            // Fallback: if slot mapping is missing, assume a 1-hour slot.
            if (!$slotEnd && !empty($slotStart)) {
                $slotEnd = date('H:i:s', strtotime($slotStart . ' +1 hour'));
            }

            if ($slotEnd && strtotime($nowTime) > strtotime($slotEnd)) {
                $expired_ids[] = $bookingId;
            }
        }
        $todayStmt->close();

        $expired_ids = array_values(array_unique(array_map('intval', $expired_ids)));
        
        if (empty($expired_ids)) {
            return true; // No expired bookings
        }
        
        // Update all expired bookings to 'expired' status
        $ids_placeholder = implode(',', array_fill(0, count($expired_ids), '?'));
        $update_sql = "UPDATE bookings SET status = 'expired' 
                       WHERE id IN ($ids_placeholder)";
        
        $update_stmt = $conn->prepare($update_sql);
        if (!$update_stmt) {
            error_log("Error preparing update statement in releaseExpiredBookings: " . $conn->error);
            return false;
        }
        
        // Bind parameters dynamically
        $types = str_repeat('i', count($expired_ids));
        $update_stmt->bind_param($types, ...$expired_ids);
        
        if (!$update_stmt->execute()) {
            error_log("Error updating expired bookings: " . $update_stmt->error);
            $update_stmt->close();
            return false;
        }
        $update_stmt->close();

        // Release online table allocations for these expired bookings.
        $releaseOnlineSql = "UPDATE online_table_bookings
                             SET allocation_status = 'released', released_at = NOW()
                             WHERE booking_id IN ($ids_placeholder)
                             AND allocation_status = 'allocated'";
        $releaseOnlineStmt = $conn->prepare($releaseOnlineSql);
        if ($releaseOnlineStmt) {
            $releaseOnlineStmt->bind_param($types, ...$expired_ids);
            $releaseOnlineStmt->execute();
            $releaseOnlineStmt->close();
        }
        
        // Release the tables associated with these expired bookings
        $table_sql = "SELECT DISTINCT tb.table_id FROM table_bookings tb
                      WHERE tb.booking_id IN ($ids_placeholder)";
        
        $table_stmt = $conn->prepare($table_sql);
        if (!$table_stmt) {
            error_log("Error preparing table select statement in releaseExpiredBookings: " . $conn->error);
            return false;
        }
        
        $table_stmt->bind_param($types, ...$expired_ids);
        if (!$table_stmt->execute()) {
            error_log("Error fetching tables for expired bookings: " . $table_stmt->error);
            $table_stmt->close();
            return false;
        }
        
        $table_result = $table_stmt->get_result();
        $table_ids = [];
        
        while ($table_row = $table_result->fetch_assoc()) {
            $table_ids[] = $table_row['table_id'];
        }
        $table_stmt->close();
        
        // Update table statuses to 'available' only if no other non-expired bookings exist for them
        if (!empty($table_ids)) {
            foreach ($table_ids as $table_id) {
                // Check if there are any non-expired bookings for this table
                $check_sql = "SELECT COUNT(*) as booking_count FROM table_bookings tb
                              INNER JOIN bookings b ON tb.booking_id = b.id
                              WHERE tb.table_id = ? 
                              AND LOWER(TRIM(b.status)) NOT IN ('expired', 'cancelled')";
                
                $check_stmt = $conn->prepare($check_sql);
                if ($check_stmt) {
                    $check_stmt->bind_param("i", $table_id);
                    $check_stmt->execute();
                    $check_result = $check_stmt->get_result();
                    $check_row = $check_result->fetch_assoc();
                    
                    // If no active bookings, mark table as available
                    if ($check_row['booking_count'] == 0) {
                        $release_sql = "UPDATE restaurant_tables SET status = 'available' WHERE id = ?";
                        $release_stmt = $conn->prepare($release_sql);
                        if ($release_stmt) {
                            $release_stmt->bind_param("i", $table_id);
                            $release_stmt->execute();
                            $release_stmt->close();
                        }
                    }
                    $check_stmt->close();
                }
            }
        }
        
        error_log("Released " . count($expired_ids) . " expired bookings");
        return true;
        
    } catch (Exception $e) {
        error_log("Exception in releaseExpiredBookings: " . $e->getMessage());
        return false;
    }
}

// Enable CORS
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');

if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    http_response_code(200);
    exit;
}
?>
