<?php
session_start();
require 'config.php';
require 'mail_config.php';

header('Content-Type: application/json');
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');

set_exception_handler(function ($e) {
    if (!headers_sent()) {
        http_response_code(500);
        header('Content-Type: application/json');
    }
    error_log("Unhandled admin_bookings_api exception: " . $e->getMessage());
    echo json_encode([
        'success' => false,
        'message' => 'Internal server error. Check server logs for details.'
    ]);
    exit;
});

// Debug: Log session info
error_log("Admin Bookings API - Session admin_id: " . (isset($_SESSION['admin_id']) ? $_SESSION['admin_id'] : 'NOT SET'));

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

// Check if user is admin - check for admin_id which is always set by login
// if (!isset($_SESSION['admin_id']) || empty($_SESSION['admin_id'])) {
//     http_response_code(401);
//     echo json_encode([
//         'success' => false,
//         'message' => 'Unauthorized access - Admin not logged in'
//     ]);
//     exit;
// }

$method = $_SERVER['REQUEST_METHOD'];

// Handle pending count request FIRST (before general GET)
if ($method === 'GET' && isset($_GET['action']) && $_GET['action'] === 'new_count') {
    // Get count of pending bookings only
    try {
        // Use LOWER() to handle case-insensitive matching and TRIM() for whitespace
        $sql = "SELECT COUNT(*) as count FROM bookings WHERE LOWER(TRIM(status)) = 'pending'";
        $result = $conn->query($sql);
        
        if (!$result) {
            echo json_encode([
                'success' => false,
                'message' => 'Database error: ' . $conn->error
            ]);
            exit;
        }
        
        $row = $result->fetch_assoc();
        $count = (int)$row['count'];
        
        // Debug: Log the actual pending bookings for verification
        $debugSql = "SELECT id, status FROM bookings WHERE LOWER(TRIM(status)) = 'pending'";
        $debugResult = $conn->query($debugSql);
        $debugBookings = [];
        while ($debugRow = $debugResult->fetch_assoc()) {
            $debugBookings[] = $debugRow;
        }
        error_log("Pending bookings count: " . $count . " | Details: " . json_encode($debugBookings));
        
        // Also log all bookings with their statuses for comparison
        $allBookingsSql = "SELECT id, status FROM bookings ORDER BY id";
        $allBookingsResult = $conn->query($allBookingsSql);
        $allBookings = [];
        while ($allRow = $allBookingsResult->fetch_assoc()) {
            $allBookings[] = $allRow;
        }
        error_log("All bookings: " . json_encode($allBookings));
        
        echo json_encode([
            'success' => true,
            'count' => $count,
            'debug' => $debugBookings,
            'all_bookings' => $allBookings
        ]);
        exit;
    } catch (Exception $e) {
        echo json_encode([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ]);
        exit;
    }
}

// Get booking seat progress for assignment planning
if ($method === 'GET' && isset($_GET['action']) && $_GET['action'] === 'booking_progress') {
    try {
        if (!isset($_GET['booking_id'])) {
            echo json_encode([
                'success' => false,
                'message' => 'booking_id is required'
            ]);
            exit;
        }

        $booking_id = intval($_GET['booking_id']);
        if ($booking_id <= 0) {
            echo json_encode([
                'success' => false,
                'message' => 'Invalid booking_id'
            ]);
            exit;
        }

        $bookingSql = "SELECT id, guests FROM bookings WHERE id = " . $booking_id . " LIMIT 1";
        $bookingResult = $conn->query($bookingSql);
        if (!$bookingResult || $bookingResult->num_rows === 0) {
            echo json_encode([
                'success' => false,
                'message' => 'Booking not found'
            ]);
            exit;
        }
        $booking = $bookingResult->fetch_assoc();
        $required_guests = intval($booking['guests'] ?? 0);

        $directSql = "SELECT COALESCE(SUM(rt.current_capacity), 0) AS seats
                      FROM table_bookings tb
                      INNER JOIN restaurant_tables rt ON rt.id = tb.table_id
                      WHERE tb.booking_id = " . $booking_id;
        $directResult = $conn->query($directSql);
        $directSeats = $directResult ? intval($directResult->fetch_assoc()['seats'] ?? 0) : 0;

        $onlineSql = "SELECT COALESCE(SUM(ot.current_capacity), 0) AS seats
                      FROM online_table_bookings otb
                      INNER JOIN online_tables ot ON ot.id = otb.online_table_id
                      INNER JOIN bookings b ON b.id = otb.booking_id
                      WHERE otb.booking_id = " . $booking_id . "
                        AND (
                            otb.allocation_status = 'allocated'
                            OR LOWER(TRIM(b.status)) IN ('checked_in', 'checked_out')
                        )";
        $onlineResult = $conn->query($onlineSql);
        $onlineSeats = $onlineResult ? intval($onlineResult->fetch_assoc()['seats'] ?? 0) : 0;

        $assigned_seats = $directSeats + $onlineSeats;
        $remaining_seats = max(0, $required_guests - $assigned_seats);

        echo json_encode([
            'success' => true,
            'data' => [
                'booking_id' => $booking_id,
                'required_guests' => $required_guests,
                'assigned_seats' => $assigned_seats,
                'remaining_seats' => $remaining_seats,
                'is_fully_assigned' => ($remaining_seats === 0)
            ]
        ]);
        exit;
    } catch (Exception $e) {
        echo json_encode([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ]);
        exit;
    }
}

// Handle general GET for all bookings
if ($method === 'GET') {
    try {
        // Fetch all bookings with assigned table details
        // Filter out tables with status = 'merged' (secondary merged tables)
        // Include both restaurant tables (from table_bookings) and online tables (from online_table_bookings)
        $sql = "SELECT b.id, b.name, b.email, b.phone, b.booking_date, b.slot_time, b.guests, 
                b.meal_type, b.table_type, b.dietary_needs, b.special_requests, b.status, 
                b.created_at, 
                CONCAT_WS(', ', 
                    GROUP_CONCAT(DISTINCT COALESCE(tb.assigned_table_name, rt.table_number)),
                    GROUP_CONCAT(DISTINCT CASE
                        WHEN ot.id IS NOT NULL
                             AND (
                                 otb.allocation_status IN ('allocated', 'released')
                                 OR LOWER(TRIM(b.status)) IN ('checked_in', 'checked_out')
                             )
                        THEN ot.table_number
                        ELSE NULL
                    END)
                ) AS assigned_tables
                FROM bookings b
                LEFT JOIN table_bookings tb ON b.id = tb.booking_id
                LEFT JOIN restaurant_tables rt ON tb.table_id = rt.id AND rt.status != 'merged'
                LEFT JOIN online_table_bookings otb ON b.id = otb.booking_id
                LEFT JOIN online_tables ot ON otb.online_table_id = ot.id
                GROUP BY b.id
                ORDER BY b.created_at DESC, b.booking_date DESC";
        
        $result = $conn->query($sql);
        
        if ($result) {
            $bookings = [];
            while ($row = $result->fetch_assoc()) {
                $bookings[] = $row;
            }
            
            echo json_encode([
                'success' => true,
                'data' => $bookings,
                'count' => count($bookings)
            ]);
        } else {
            echo json_encode([
                'success' => false,
                'message' => 'Database error: ' . $conn->error
            ]);

        }
    } catch (Exception $e) {
        echo json_encode([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ]);
    }
} elseif ($method === 'POST') {
    // Handle different POST actions
    try {
        $data = json_decode(file_get_contents("php://input"), true);
        
        // Check if this is a table assignment request
        if (isset($data['action']) && $data['action'] === 'assign_table') {
            if (!isset($data['booking_id']) || !isset($data['table_id'])) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Missing booking_id or table_id'
                ]);
                exit;
            }

            $booking_id = (int)$data['booking_id'];
            $table_id = (int)$data['table_id'];

            // Verify booking exists and get booking date/slot/meal
            $checkBooking = "SELECT id, booking_date, slot_time, meal_type FROM bookings WHERE id = ?";
            $stmt = $conn->prepare($checkBooking);
            $stmt->bind_param("i", $booking_id);
            $stmt->execute();
            $bookingResult = $stmt->get_result();
            if ($bookingResult->num_rows === 0) {
                $stmt->close();
                echo json_encode([
                    'success' => false,
                    'message' => 'Booking not found'
                ]);
                exit;
            }
            $booking = $bookingResult->fetch_assoc();
            $booking_date = $booking['booking_date'];
            $booking_slot_time = $booking['slot_time'];
            $stmt->close();

            // First check if table is from online_tables (for online bookings)
            $checkOnlineTable = "SELECT id, table_number, status FROM online_tables WHERE id = ?";
            $stmt = $conn->prepare($checkOnlineTable);
            $stmt->bind_param("i", $table_id);
            $stmt->execute();
            $onlineTableResult = $stmt->get_result();
            $isOnlineTable = $onlineTableResult->num_rows > 0;
            
            if ($isOnlineTable) {
                $table = $onlineTableResult->fetch_assoc();
                $stmt->close();
                
                // Check if online table is in maintenance
                if ($table['status'] === 'maintenance') {
                    echo json_encode([
                        'success' => false,
                        'message' => 'Table is in maintenance and cannot be assigned'
                    ]);
                    exit;
                }

                // Check if table is already assigned to another booking on the SAME date and slot
                $checkConflict = "SELECT COUNT(*) as conflict_count FROM online_table_bookings otb
                                 INNER JOIN bookings b ON otb.booking_id = b.id
                                 WHERE otb.online_table_id = ? 
                                 AND b.booking_date = ?
                                 AND TIME(b.slot_time) = TIME(?)
                                 AND b.id != ?
                                 AND b.status IN ('pending', 'confirmed', 'checked_in')
                                 AND otb.allocation_status = 'allocated'";
                $stmt = $conn->prepare($checkConflict);
                $stmt->bind_param("issi", $table_id, $booking_date, $booking_slot_time, $booking_id);
                $stmt->execute();
                $conflictResult = $stmt->get_result();
                $conflict = $conflictResult->fetch_assoc();
                $stmt->close();

                if ($conflict['conflict_count'] > 0) {
                    echo json_encode([
                        'success' => false,
                        'message' => 'Online table is already assigned for this slot on ' . date('F j, Y', strtotime($booking_date))
                    ]);
                    exit;
                }

                // Insert into online_table_bookings junction table
                $insertSql = "INSERT INTO online_table_bookings (booking_id, online_table_id, allocation_status) VALUES (?, ?, 'allocated')";
                $insertStmt = $conn->prepare($insertSql);
                $insertStmt->bind_param("ii", $booking_id, $table_id);

                if ($insertStmt->execute()) {
                    $insertStmt->close();
                    
                    echo json_encode([
                        'success' => true,
                        'message' => 'Online table assigned to booking successfully',
                        'table_name' => $table['table_number']
                    ]);
                } else {
                    $insertStmt->close();
                    echo json_encode([
                        'success' => false,
                        'message' => 'Failed to assign online table: ' . $conn->error
                    ]);
                }
            } else {
                // Fall back to restaurant_tables (for direct dining)
                $checkTable = "SELECT id, table_number, status FROM restaurant_tables WHERE id = ?";
                $stmt = $conn->prepare($checkTable);
                $stmt->bind_param("i", $table_id);
                $stmt->execute();
                $tableResult = $stmt->get_result();
                if ($tableResult->num_rows === 0) {
                    $stmt->close();
                    echo json_encode([
                        'success' => false,
                        'message' => 'Table not found'
                    ]);
                    exit;
                }
                $table = $tableResult->fetch_assoc();
                $stmt->close();

                // Check if table is merged or in maintenance
                if (in_array($table['status'], ['merged', 'maintenance'])) {
                    echo json_encode([
                        'success' => false,
                        'message' => 'Table is ' . $table['status'] . ' and cannot be assigned'
                    ]);
                    exit;
                }

                // Check if table is already assigned to another booking on the SAME date and slot
                $checkConflict = "SELECT COUNT(*) as conflict_count FROM table_bookings tb
                                 INNER JOIN bookings b ON tb.booking_id = b.id
                                 WHERE tb.table_id = ? 
                                 AND b.booking_date = ?
                                 AND TIME(b.slot_time) = TIME(?)
                                 AND b.id != ?
                                 AND b.status IN ('pending', 'confirmed', 'checked_in')";
                $stmt = $conn->prepare($checkConflict);
                $stmt->bind_param("issi", $table_id, $booking_date, $booking_slot_time, $booking_id);
                $stmt->execute();
                $conflictResult = $stmt->get_result();
                $conflict = $conflictResult->fetch_assoc();
                $stmt->close();

                if ($conflict['conflict_count'] > 0) {
                    echo json_encode([
                        'success' => false,
                        'message' => 'Table is already assigned for this slot on ' . date('F j, Y', strtotime($booking_date))
                    ]);
                    exit;
                }

                // Insert into table_bookings junction table with table name snapshot
                $insertSql = "INSERT INTO table_bookings (booking_id, table_id, assigned_table_name) VALUES (?, ?, ?)";
                $insertStmt = $conn->prepare($insertSql);
                $table_name = $table['table_number'];
                $insertStmt->bind_param("iis", $booking_id, $table_id, $table_name);

                if ($insertStmt->execute()) {
                    $insertStmt->close();
                    
                    // Update table status to booked only if it has any active bookings
                    $checkActiveBookings = "SELECT COUNT(*) as active_count FROM table_bookings tb
                                           INNER JOIN bookings b ON tb.booking_id = b.id
                                           WHERE tb.table_id = ?
                                           AND b.status IN ('pending', 'confirmed', 'checked_in')";
                    $stmt = $conn->prepare($checkActiveBookings);
                    $stmt->bind_param("i", $table_id);
                    $stmt->execute();
                    $activeResult = $stmt->get_result();
                    $activeCount = $activeResult->fetch_assoc()['active_count'];
                    $stmt->close();

                    // Set table status to booked if it has any active bookings
                    if ($activeCount > 0) {
                        $updateTableSql = "UPDATE restaurant_tables SET status = 'booked' WHERE id = ?";
                        $updateTableStmt = $conn->prepare($updateTableSql);
                        $updateTableStmt->bind_param("i", $table_id);
                        $updateTableStmt->execute();
                        $updateTableStmt->close();
                    }
                    
                    echo json_encode([
                        'success' => true,
                        'message' => 'Table assigned to booking successfully',
                        'table_name' => $table['table_number']
                    ]);
                } else {
                    $insertStmt->close();
                    echo json_encode([
                        'success' => false,
                        'message' => 'Failed to assign table: ' . $conn->error
                    ]);
                }
            }
            exit;
        }

        // Admin-created booking (supports large groups)
        if (isset($data['action']) && $data['action'] === 'create_booking') {
            $requiredFields = ['name', 'email', 'phone', 'booking_date', 'slot_time', 'guests', 'meal_type'];
            foreach ($requiredFields as $field) {
                if (!isset($data[$field]) || trim((string)$data[$field]) === '') {
                    echo json_encode([
                        'success' => false,
                        'message' => 'Missing required field: ' . $field
                    ]);
                    exit;
                }
            }

            $name = trim((string)$data['name']);
            $email = trim((string)$data['email']);
            $phone = trim((string)$data['phone']);
            $booking_date = trim((string)$data['booking_date']);
            $slot_time = trim((string)$data['slot_time']);
            $guests = intval($data['guests']);
            $meal_type = trim((string)$data['meal_type']);
            $table_type = isset($data['table_type']) ? trim((string)$data['table_type']) : 'online';
            $dietary_needs = isset($data['dietary_needs']) ? trim((string)$data['dietary_needs']) : '';
            $special_requests = isset($data['special_requests']) ? trim((string)$data['special_requests']) : '';
            $status = isset($data['status']) ? strtolower(trim((string)$data['status'])) : 'confirmed';

            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Invalid email format'
                ]);
                exit;
            }
            if ($guests <= 0) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Guests must be greater than 0'
                ]);
                exit;
            }
            $allowedMealTypes = ['breakfast', 'lunch', 'dinner'];
            if (!in_array(strtolower($meal_type), $allowedMealTypes)) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Invalid meal type'
                ]);
                exit;
            }
            $allowedTableTypes = ['online', 'dine-in', 'dine_in', 'direct'];
            if (!in_array(strtolower($table_type), $allowedTableTypes)) {
                $table_type = 'online';
            }
            $allowedStatuses = ['pending', 'confirmed', 'checked_in'];
            if (!in_array($status, $allowedStatuses)) {
                $status = 'confirmed';
            }

            $dateObj = DateTime::createFromFormat('Y-m-d', $booking_date);
            if (!$dateObj || $dateObj->format('Y-m-d') !== $booking_date) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Invalid booking date format'
                ]);
                exit;
            }

            // Accept HH:MM or HH:MM:SS
            if (preg_match('/^\d{2}:\d{2}$/', $slot_time)) {
                $slot_time .= ':00';
            }
            $timeObj = DateTime::createFromFormat('H:i:s', $slot_time);
            if (!$timeObj || $timeObj->format('H:i:s') !== $slot_time) {
                echo json_encode([
                    'success' => false,
                    'message' => 'Invalid slot time format'
                ]);
                exit;
            }

            $insertSql = "INSERT INTO bookings
                        (user_id, name, email, phone, booking_date, slot_time, guests, meal_type, table_type, dietary_needs, special_requests, status)
                        VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            $stmt = $conn->prepare($insertSql);
            if ($stmt === false) {
                throw new Exception('Failed to prepare booking insert query: ' . $conn->error);
            }

            $stmt->bind_param(
                "sssssisssss",
                $name,
                $email,
                $phone,
                $booking_date,
                $slot_time,
                $guests,
                $meal_type,
                $table_type,
                $dietary_needs,
                $special_requests,
                $status
            );

            if (!$stmt->execute()) {
                $stmt->close();
                echo json_encode([
                    'success' => false,
                    'message' => 'Failed to create booking: ' . $conn->error
                ]);
                exit;
            }

            $newBookingId = $stmt->insert_id;
            $stmt->close();

            echo json_encode([
                'success' => true,
                'message' => 'Booking created successfully',
                'booking_id' => $newBookingId
            ]);
            exit;
        }
        
        // Handle status update (existing functionality)
        if (!isset($data['id']) || !isset($data['status'])) {
            echo json_encode([
                'success' => false,
                'message' => 'Missing required fields'
            ]);
            exit;
        }
        
        $id = $data['id'];
        $status = $data['status'];
        $reason = isset($data['reason']) ? $data['reason'] : null;
        
        // Validate status
        $validStatuses = ['pending', 'confirmed', 'cancelled'];
        if (!in_array($status, $validStatuses)) {
            echo json_encode([
                'success' => false,
                'message' => 'Invalid status'
            ]);
            exit;
        }
        
        // Get booking details first (including table info)
        // Filter out tables with status = 'merged' (secondary merged tables)
        $selectSql = "SELECT b.id, b.name, b.email, b.phone, b.booking_date, b.slot_time, b.guests, b.meal_type, b.special_requests, 
                             CONCAT_WS(', ', 
                                GROUP_CONCAT(DISTINCT COALESCE(tb.assigned_table_name, rt.table_number)),
                                GROUP_CONCAT(DISTINCT CASE
                                    WHEN ot.id IS NOT NULL
                                         AND (
                                             otb.allocation_status IN ('allocated', 'released')
                                             OR LOWER(TRIM(b.status)) IN ('checked_in', 'checked_out')
                                         )
                                    THEN ot.table_number
                                    ELSE NULL
                                END)
                             ) AS assigned_tables, GROUP_CONCAT(DISTINCT tb.table_id) AS table_ids
                      FROM bookings b
                      LEFT JOIN table_bookings tb ON b.id = tb.booking_id
                      LEFT JOIN restaurant_tables rt ON tb.table_id = rt.id AND rt.status != 'merged'
                      LEFT JOIN online_table_bookings otb ON b.id = otb.booking_id
                      LEFT JOIN online_tables ot ON otb.online_table_id = ot.id
                      WHERE b.id = ?
                      GROUP BY b.id";
        $stmt = $conn->prepare($selectSql);
        if ($stmt === false) {
            throw new Exception('Failed to prepare booking lookup query: ' . $conn->error);
        }
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $booking = $result->fetch_assoc();
        $stmt->close();
        
        // Extract the first table_id and table_number if multiple tables are assigned
        if (!empty($booking['table_ids'])) {
            $tableIds = explode(',', $booking['table_ids']);
            $booking['table_id'] = (int)$tableIds[0];
        }
        
        // Extract the first table number from assigned_tables
        if (!empty($booking['assigned_tables'])) {
            $tableNumbers = explode(',', $booking['assigned_tables']);
            $booking['table_number'] = trim($tableNumbers[0]);
        } else {
            $booking['table_number'] = null;
        }
        
        if (!$booking) {
            echo json_encode([
                'success' => false,
                'message' => 'Booking not found'
            ]);
            exit;
        }
        
        // Update booking status
        $updateSql = "UPDATE bookings SET status = ? WHERE id = ?";
        $updateStmt = $conn->prepare($updateSql);
        if ($updateStmt === false) {
            throw new Exception('Failed to prepare booking status update query: ' . $conn->error);
        }
        $updateStmt->bind_param("si", $status, $id);
        
        if ($updateStmt->execute()) {
            // Send email based on status
            $emailSent = false;
            $tableUpdated = false;
            
            if ($status === 'confirmed') {
                $emailSent = sendBookingApprovedEmail($conn, $booking);
                // Check if table was updated
                if (!empty($booking['table_id'])) {
                    $checkTableSql = "SELECT status FROM restaurant_tables WHERE id = ?";
                    $checkStmt = $conn->prepare($checkTableSql);
                    $checkStmt->bind_param("i", $booking['table_id']);
                    $checkStmt->execute();
                    $checkResult = $checkStmt->get_result();
                    $tableStatus = $checkResult->fetch_assoc();
                    $checkStmt->close();
                    $tableUpdated = ($tableStatus['status'] === 'booked');
                }
            } elseif ($status === 'cancelled') {
                $emailSent = sendBookingRejectedEmail($conn, $booking, $reason);
                
                // Get table IDs before deleting
                $tableIds = [];
                if (!empty($booking['table_ids'])) {
                    $tableIds = array_map('intval', explode(',', $booking['table_ids']));
                }
                
                // Delete table bookings for this booking
                if (!empty($booking['table_ids'])) {
                    $deleteTableBookingSql = "DELETE FROM table_bookings WHERE booking_id = ?";
                    $deleteStmt = $conn->prepare($deleteTableBookingSql);
                    $deleteStmt->bind_param("i", $id);
                    $deleteStmt->execute();
                    $deleteStmt->close();
                }
                
                // Update table statuses: they should only remain 'booked' if they have other active bookings
                foreach ($tableIds as $tableId) {
                    // Check if this table has any remaining active bookings
                    $checkActiveBookings = "SELECT COUNT(*) as active_count FROM table_bookings tb
                                           INNER JOIN bookings b ON tb.booking_id = b.id
                                           WHERE tb.table_id = ?
                                           AND b.status IN ('pending', 'confirmed', 'checked_in')";
                    $stmt = $conn->prepare($checkActiveBookings);
                    $stmt->bind_param("i", $tableId);
                    $stmt->execute();
                    $activeResult = $stmt->get_result();
                    $activeCount = $activeResult->fetch_assoc()['active_count'];
                    $stmt->close();
                    
                    // If no active bookings, mark table as available
                    if ($activeCount == 0) {
                        $updateTableSql = "UPDATE restaurant_tables SET status = 'available' WHERE id = ?";
                        $tableStmt = $conn->prepare($updateTableSql);
                        $tableStmt->bind_param("i", $tableId);
                        $tableStmt->execute();
                        $tableStmt->close();
                    }
                }
                
                // Check if table was updated back to available
                if (!empty($booking['table_id'])) {
                    $checkTableSql = "SELECT status FROM restaurant_tables WHERE id = ?";
                    $checkStmt = $conn->prepare($checkTableSql);
                    $checkStmt->bind_param("i", $booking['table_id']);
                    $checkStmt->execute();
                    $checkResult = $checkStmt->get_result();
                    $tableStatus = $checkResult->fetch_assoc();
                    $checkStmt->close();
                    $tableUpdated = ($tableStatus['status'] === 'available');
                }
            }
            
            // Get updated pending count using case-insensitive matching
            $countSql = "SELECT COUNT(*) as pending_count FROM bookings WHERE LOWER(TRIM(status)) = 'pending'";
            $countResult = $conn->query($countSql);
            $countRow = $countResult->fetch_assoc();
            $pendingCount = (int)$countRow['pending_count'];
            
            echo json_encode([
                'success' => true,
                'message' => 'Booking status updated successfully and email sent',
                'email_sent' => $emailSent,
                'table_id' => $booking['table_id'] ?? null,
                'table_updated' => $tableUpdated,
                'table_number' => $booking['table_number'] ?? null,
                'booking_status' => $status,
                'pending_count' => $pendingCount
            ]);
        } else {
            echo json_encode([
                'success' => false,
                'message' => 'Database error: ' . $conn->error
            ]);
        }
        $updateStmt->close();
    } catch (Throwable $e) {
        echo json_encode([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ]);
    }
} else {
    echo json_encode([
        'success' => false,
        'message' => 'Invalid request method'
    ]);
}

// Function to send booking approved email
function sendBookingApprovedEmail($conn, $booking) {
    try {
        // Check if PHPMailer is installed
        $phpmailerFile = __DIR__ . '/phpmailer/src/PHPMailer.php';
        if (!file_exists($phpmailerFile)) {
            error_log("CRITICAL: PHPMailer library not found at {$phpmailerFile}");
            error_log("Email NOT sent for booking ID: {$booking['id']}");
            return false;
        }

        require_once __DIR__ . '/phpmailer/src/Exception.php';
        require_once __DIR__ . '/phpmailer/src/PHPMailer.php';
        require_once __DIR__ . '/phpmailer/src/SMTP.php';

        $mail = new PHPMailer\PHPMailer\PHPMailer(true);

        // Server settings
        $mail->isSMTP();
        $mail->Host = MAIL_HOST;
        $mail->SMTPAuth = true;
        $mail->Username = MAIL_USERNAME;
        $mail->Password = MAIL_PASSWORD;
        $mail->SMTPSecure = PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port = MAIL_PORT;
        $mail->SMTPDebug = 0;
        $mail->Debugoutput = 'error_log';
        
        // Allow self-signed certificates
        $mail->SMTPOptions = array(
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true
            )
        );

        // Recipients
        $mail->setFrom(MAIL_FROM_EMAIL, MAIL_FROM_NAME);
        $mail->addAddress($booking['email'], $booking['name']);

        // Content
        $mail->isHTML(true);
        $mail->Subject = 'Booking Confirmation - Granny Restaurant';
        $mail->Body = getBookingApprovedEmailTemplate($booking);
        $mail->AltBody = "Your table booking has been confirmed at Granny Restaurant.";

        $mailResult = $mail->send();
        
        if ($mailResult) {
            error_log("✓ [SUCCESS] Booking confirmation email sent successfully for booking ID: {$booking['id']} to {$booking['email']}");
        } else {
            error_log("✗ [FAILED] Booking confirmation email failed for booking ID: {$booking['id']} - Error: {$mail->ErrorInfo}");
            return false;
        }
        
        // Update table status to "booked" if a table is assigned
        if (!empty($booking['table_id'])) {
            error_log("Attempting to update table status for table_id: {$booking['table_id']}");
            $updateTableSql = "UPDATE restaurant_tables SET status = 'booked' WHERE id = ?";
            $tableStmt = $conn->prepare($updateTableSql);
            
            if ($tableStmt === false) {
                error_log("Table status update FAILED - Prepare error: " . $conn->error);
                return true; // Still return true since email was sent
            }
            
            if (!$tableStmt->bind_param("i", $booking['table_id'])) {
                error_log("Table status update FAILED - Bind error: " . $tableStmt->error);
                $tableStmt->close();
                return true; // Still return true since email was sent
            }
            
            if (!$tableStmt->execute()) {
                error_log("Table status update FAILED - Execute error: " . $tableStmt->error);
                $tableStmt->close();
                return true; // Still return true since email was sent
            }
            
            $tableStmt->close();
            error_log("✓ Table {$booking['table_id']} status successfully updated to 'booked' for booking {$booking['id']}");
        } else {
            error_log("No table assigned to booking {$booking['id']}, skipping table status update");
        }
        
        return true;
    } catch (PHPMailer\PHPMailer\Exception $e) {
        error_log("✗ [PHPMailer Exception] Email Error (Approved) for booking ID {$booking['id']}: " . $e->getMessage());
        return false;
    } catch (Exception $e) {
        error_log("✗ [General Exception] Email Error (Approved) for booking ID {$booking['id']}: " . $e->getMessage());
        return false;
    }
}

// Function to send booking rejected email
function sendBookingRejectedEmail($conn, $booking, $reason) {
    try {
        // Check if PHPMailer is installed
        $phpmailerFile = __DIR__ . '/phpmailer/src/PHPMailer.php';
        if (!file_exists($phpmailerFile)) {
            error_log("CRITICAL: PHPMailer library not found at {$phpmailerFile}");
            error_log("Email NOT sent for booking ID: {$booking['id']}");
            return false;
        }

        require_once __DIR__ . '/phpmailer/src/Exception.php';
        require_once __DIR__ . '/phpmailer/src/PHPMailer.php';
        require_once __DIR__ . '/phpmailer/src/SMTP.php';

        $mail = new PHPMailer\PHPMailer\PHPMailer(true);

        // Server settings
        $mail->isSMTP();
        $mail->Host = MAIL_HOST;
        $mail->SMTPAuth = true;
        $mail->Username = MAIL_USERNAME;
        $mail->Password = MAIL_PASSWORD;
        $mail->SMTPSecure = PHPMailer\PHPMailer\PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port = MAIL_PORT;
        $mail->SMTPDebug = 0;
        $mail->Debugoutput = 'error_log';
        
        // Allow self-signed certificates
        $mail->SMTPOptions = array(
            'ssl' => array(
                'verify_peer' => false,
                'verify_peer_name' => false,
                'allow_self_signed' => true
            )
        );

        // Recipients
        $mail->setFrom(MAIL_FROM_EMAIL, MAIL_FROM_NAME);
        $mail->addAddress($booking['email'], $booking['name']);

        // Content
        $mail->isHTML(true);
        $mail->Subject = 'Booking Status Update - Granny Restaurant';
        $mail->Body = getBookingRejectedEmailTemplate($booking, $reason);
        $mail->AltBody = "Your table booking has been declined. Reason: " . $reason;

        $mailResult = $mail->send();
        
        if ($mailResult) {
            error_log("✓ [SUCCESS] Booking cancellation email sent successfully for booking ID: {$booking['id']} to {$booking['email']}");
        } else {
            error_log("✗ [FAILED] Booking cancellation email failed for booking ID: {$booking['id']} - Error: {$mail->ErrorInfo}");
            return false;
        }
        
        // Update table status back to "available" if a table was assigned
        if (!empty($booking['table_id'])) {
            error_log("Attempting to update table status back to available for table_id: {$booking['table_id']}");
            $updateTableSql = "UPDATE restaurant_tables SET status = 'available' WHERE id = ?";
            $tableStmt = $conn->prepare($updateTableSql);
            
            if ($tableStmt === false) {
                error_log("Table status update FAILED - Prepare error: " . $conn->error);
                return true; // Still return true since email was sent
            }
            
            if (!$tableStmt->bind_param("i", $booking['table_id'])) {
                error_log("Table status update FAILED - Bind error: " . $tableStmt->error);
                $tableStmt->close();
                return true; // Still return true since email was sent
            }
            
            if (!$tableStmt->execute()) {
                error_log("Table status update FAILED - Execute error: " . $tableStmt->error);
                $tableStmt->close();
                return true; // Still return true since email was sent
            }
            
            $tableStmt->close();
            error_log("✓ Table {$booking['table_id']} status successfully updated to 'available' for booking {$booking['id']}");
        } else {
            error_log("No table assigned to booking {$booking['id']}, skipping table status update");
        }
        
        return true;
    } catch (PHPMailer\PHPMailer\Exception $e) {
        error_log("✗ [PHPMailer Exception] Email Error (Rejected) for booking ID {$booking['id']}: " . $e->getMessage());
        return false;
    } catch (Exception $e) {
        error_log("✗ [General Exception] Email Error (Rejected) for booking ID {$booking['id']}: " . $e->getMessage());
        return false;
    }
}

// Email template for booking approved
function getBookingApprovedEmailTemplate($booking) {
    $bookingDate = date('F j, Y', strtotime($booking['booking_date']));
    $bookingTime = date('g:i A', strtotime($booking['slot_time']));
    $occasion = !empty($booking['meal_type']) ? $booking['meal_type'] : (!empty($booking['special_requests']) ? $booking['special_requests'] : 'N/A');
    $tableInfo = !empty($booking['assigned_tables']) ? 
        "<div class='detail-row'>
            <span class='detail-label'>Assigned Table(s):</span>
            <span class='detail-value' style='color: #8B4513; font-weight: bold; font-size: 16px;'>{$booking['assigned_tables']}</span>
        </div>" : '';
    
    return "
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                body {
                    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
                    background-color: #f5f5f5;
                    margin: 0;
                    padding: 0;
                }
                .container {
                    max-width: 600px;
                    margin: 20px auto;
                    background-color: #ffffff;
                    border-radius: 8px;
                    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                    overflow: hidden;
                }
                .header {
                    background: linear-gradient(135deg, #8B4513 0%, #A0522D 100%);
                    color: #ffffff;
                    padding: 30px;
                    text-align: center;
                }
                .header h1 {
                    margin: 0;
                    font-size: 28px;
                    font-weight: bold;
                }
                .content {
                    padding: 30px;
                }
                .booking-details {
                    background-color: #f8f9fa;
                    border-left: 4px solid #8B4513;
                    padding: 20px;
                    margin: 20px 0;
                    border-radius: 4px;
                }
                .detail-row {
                    display: flex;
                    justify-content: space-between;
                    margin: 10px 0;
                    font-size: 14px;
                }
                .detail-label {
                    font-weight: bold;
                    color: #333;
                }
                .detail-value {
                    color: #666;
                }
                .success-message {
                    color: #28a745;
                    font-size: 16px;
                    font-weight: bold;
                    margin: 20px 0;
                }
                .footer {
                    background-color: #f8f9fa;
                    padding: 20px;
                    text-align: center;
                    font-size: 12px;
                    color: #666;
                    border-top: 1px solid #eee;
                }
            </style>
        </head>
        <body>
            <div class='container'>
                <div class='header'>
                    <h1>✓ Booking Confirmed</h1>
                </div>
                <div class='content'>
                    <p>Dear <strong>{$booking['name']}</strong>,</p>
                    
                    <p>We are delighted to confirm your table booking at Granny Restaurant!</p>
                    
                    <div class='success-message'>
                        Your reservation has been approved and confirmed.
                    </div>
                    
                    <h3>Booking Details:</h3>
                    <div class='booking-details'>
                        <div class='detail-row'>
                            <span class='detail-label'>Date:</span>
                            <span class='detail-value'>{$bookingDate}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Time:</span>
                            <span class='detail-value'>{$bookingTime}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Number of Guests:</span>
                            <span class='detail-value'>{$booking['guests']}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Occasion:</span>
                            <span class='detail-value'>{$occasion}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Contact:</span>
                            <span class='detail-value'>{$booking['phone']}</span>
                        </div>
                        {$tableInfo}
                    </div>
                    
                    <p>Please arrive 10 minutes before your reservation time. If you need to make any changes or have any questions, please contact us at your earliest convenience.</p>
                    
                    <p>Thank you for choosing Granny Restaurant!</p>
                </div>
                <div class='footer'>
                    <p>&copy; 2026 Granny Restaurant. All rights reserved.</p>
                    <p>This is an automated email. Please do not reply directly to this email.</p>
                </div>
            </div>
        </body>
        </html>
    ";
}

// Email template for booking rejected
function getBookingRejectedEmailTemplate($booking, $reason) {
    $bookingDate = date('F j, Y', strtotime($booking['booking_date']));
    $bookingTime = date('g:i A', strtotime($booking['slot_time']));
    $occasion = !empty($booking['meal_type']) ? $booking['meal_type'] : (!empty($booking['special_requests']) ? $booking['special_requests'] : 'N/A');
    
    return "
        <!DOCTYPE html>
        <html>
        <head>
            <style>
                body {
                    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
                    background-color: #f5f5f5;
                    margin: 0;
                    padding: 0;
                }
                .container {
                    max-width: 600px;
                    margin: 20px auto;
                    background-color: #ffffff;
                    border-radius: 8px;
                    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
                    overflow: hidden;
                }
                .header {
                    background: linear-gradient(135deg, #dc3545 0%, #c82333 100%);
                    color: #ffffff;
                    padding: 30px;
                    text-align: center;
                }
                .header h1 {
                    margin: 0;
                    font-size: 28px;
                    font-weight: bold;
                }
                .content {
                    padding: 30px;
                }
                .booking-details {
                    background-color: #f8f9fa;
                    border-left: 4px solid #dc3545;
                    padding: 20px;
                    margin: 20px 0;
                    border-radius: 4px;
                }
                .detail-row {
                    display: flex;
                    justify-content: space-between;
                    margin: 10px 0;
                    font-size: 14px;
                }
                .detail-label {
                    font-weight: bold;
                    color: #333;
                }
                .detail-value {
                    color: #666;
                }
                .reason-box {
                    background-color: #fff3cd;
                    border: 1px solid #ffc107;
                    border-radius: 4px;
                    padding: 15px;
                    margin: 20px 0;
                }
                .reason-label {
                    font-weight: bold;
                    color: #856404;
                    margin-bottom: 10px;
                }
                .reason-text {
                    color: #856404;
                    line-height: 1.6;
                }
                .footer {
                    background-color: #f8f9fa;
                    padding: 20px;
                    text-align: center;
                    font-size: 12px;
                    color: #666;
                    border-top: 1px solid #eee;
                }
            </style>
        </head>
        <body>
            <div class='container'>
                <div class='header'>
                    <h1>Booking Status Update</h1>
                </div>
                <div class='content'>
                    <p>Dear <strong>{$booking['name']}</strong>,</p>
                    
                    <p>Thank you for your interest in booking a table at Granny Restaurant. Unfortunately, we are unable to accommodate your reservation at this time.</p>
                    
                    <h3>Original Booking Details:</h3>
                    <div class='booking-details'>
                        <div class='detail-row'>
                            <span class='detail-label'>Date:</span>
                            <span class='detail-value'>{$bookingDate}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Time:</span>
                            <span class='detail-value'>{$bookingTime}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Number of Guests:</span>
                            <span class='detail-value'>{$booking['guests']}</span>
                        </div>
                        <div class='detail-row'>
                            <span class='detail-label'>Occasion:</span>
                            <span class='detail-value'>{$occasion}</span>
                        </div>
                    </div>
                    
                    <div class='reason-box'>
                        <div class='reason-label'>Reason for Cancellation:</div>
                        <div class='reason-text'>" . nl2br(htmlspecialchars($reason)) . "</div>
                    </div>
                    
                    <p>We apologize for any inconvenience this may cause. We would be happy to help you find an alternative date and time that works better for you. Please feel free to contact us to discuss other options.</p>
                    
                    <p>We appreciate your understanding and hope to serve you in the future!</p>
                </div>
                <div class='footer'>
                    <p>&copy; 2026 Granny Restaurant. All rights reserved.</p>
                    <p>This is an automated email. Please do not reply directly to this email.</p>
                </div>
            </div>
        </body>
        </html>
    ";
}

$conn->close();
?>


