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

// 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 today's date
        $today = date('Y-m-d');
        
        // Find all bookings where booking_date is in the past and status is not expired/cancelled/checked_in
        $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();
        $expired_ids = [];
        
        while ($row = $result->fetch_assoc()) {
            $expired_ids[] = $row['id'];
        }
        $stmt->close();
        
        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 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;
}
?>
