<?php
require_once __DIR__ . '/../config/session.php';
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../config/mailer.php';

// Keep API responses JSON-clean even if PHP notices/warnings occur.
ini_set('display_errors', '0');
ini_set('display_startup_errors', '0');
ini_set('log_errors', '1');

// Check authorization
if (!isLoggedIn()) {
    http_response_code(401);
    echo json_encode(['error' => 'Unauthorized']);
    exit();
}

$currentUser = getCurrentUser();
if (!isset($currentUser['user_type']) || !in_array($currentUser['user_type'], ['admin', 'staff'])) {
    http_response_code(403);
    echo json_encode(['error' => 'Forbidden']);
    exit();
}

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

$action = $_GET['action'] ?? ($_POST['action'] ?? '');

switch ($action) {
    case 'get_guests':
        getGuests();
        break;
    case 'get_reservations':
        getReservations();
        break;
    case 'update_reservation_status':
        updateReservationStatus();
        break;
    case 'update_reservation_payment_status':
        updateReservationPaymentStatus();
        break;
    case 'get_rooms':
        getRooms();
        break;
    case 'add_room':
        addRoom();
        break;
    case 'update_room':
        updateRoom();
        break;
    case 'delete_room':
        deleteRoom();
        break;
    case 'get_gallery':
        getGallery();
        break;
    case 'get_hero_slides':
        getHeroSlides();
        break;
    case 'add_hero_slide':
        addHeroSlide();
        break;
    case 'update_hero_slide':
        updateHeroSlide();
        break;
    case 'delete_hero_slide':
        deleteHeroSlide();
        break;
    case 'add_gallery':
        addGallery();
        break;
    case 'update_gallery':
        updateGallery();
        break;
    case 'delete_gallery':
        deleteGallery();
        break;
    case 'get_news':
        getNews();
        break;
    case 'add_news':
        addNews();
        break;
    case 'update_news':
        updateNews();
        break;
    case 'delete_news':
        deleteNews();
        break;
    case 'get_offers':
        getOffers();
        break;
    case 'add_offer':
        addOffer();
        break;
    case 'update_offer':
        updateOffer();
        break;
    case 'delete_offer':
        deleteOffer();
        break;
    case 'get_services':
        getServices();
        break;
    case 'add_service':
        addService();
        break;
    case 'update_service':
        updateService();
        break;
    case 'delete_service':
        deleteService();
        break;
    case 'get_service_reservations':
        getServiceReservations();
        break;
    case 'update_service_reservation_status':
        updateServiceReservationStatus();
        break;
    case 'update_service_reservation_payment_status':
        updateServiceReservationPaymentStatus();
        break;
    case 'get_staff':
        getStaff();
        break;
    case 'get_stats':
        getStats();
        break;
    case 'get_revenue_invoices':
        getRevenueInvoices();
        break;
    case 'get_newsletter_subscribers':
        getNewsletterSubscribers();
        break;
    case 'send_newsletter':
        sendNewsletter();
        break;
    default:
        http_response_code(400);
        echo json_encode(['error' => 'Invalid action']);
}

function getGuests() {
    global $conn;
    
    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    $search = $_GET['search'] ?? '';
    
    $sql = "SELECT 
                u.id,
                u.first_name,
                u.last_name,
                u.email,
                u.phone,
                u.created_at,
                u.is_active,
                COUNT(r.id) AS reservation_count
            FROM users u
            LEFT JOIN reservations r ON r.user_id = u.id
            WHERE u.user_type = 'guest'";
    
    if ($search) {
        $search = '%' . $conn->real_escape_string($search) . '%';
        $sql .= " AND (u.first_name LIKE '$search' OR u.last_name LIKE '$search' OR u.email LIKE '$search')";
    }
    
    $sql .= " GROUP BY u.id ORDER BY u.created_at DESC LIMIT $limit OFFSET $offset";
    
    $result = $conn->query($sql);
    $guests = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    
    // Get count
    $countSql = "SELECT COUNT(*) as count FROM users WHERE user_type = 'guest'";
    if ($search) {
        $countSql .= " AND (first_name LIKE '$search' OR last_name LIKE '$search' OR email LIKE '$search')";
    }
    $countResult = $conn->query($countSql);
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;
    
    echo json_encode([
        'success' => true,
        'data' => $guests,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function getReservations() {
    global $conn;
    
    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    $status = trim((string)($_GET['status'] ?? ''));
    $guest = trim((string)($_GET['guest'] ?? ''));
    $room = trim((string)($_GET['room'] ?? ''));
    
    $sql = "SELECT rr.*, u.first_name, u.last_name, u.email, r.room_number, r.room_type
            FROM reservations rr
            LEFT JOIN users u ON rr.user_id = u.id
            LEFT JOIN rooms r ON rr.room_id = r.id";

    $where = [];
    if ($status !== '') {
        $statusEscaped = $conn->real_escape_string($status);
        $where[] = "rr.status = '$statusEscaped'";
    }
    if ($guest !== '') {
        $guestLike = '%' . $conn->real_escape_string($guest) . '%';
        $where[] = "(u.first_name LIKE '$guestLike' OR u.last_name LIKE '$guestLike' OR CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')) LIKE '$guestLike' OR u.email LIKE '$guestLike')";
    }
    if ($room !== '') {
        $roomLike = '%' . $conn->real_escape_string($room) . '%';
        $where[] = "(CAST(r.room_number AS CHAR) LIKE '$roomLike' OR r.room_type LIKE '$roomLike')";
    }
    if (!empty($where)) {
        $sql .= " WHERE " . implode(' AND ', $where);
    }
    
    $sql .= " ORDER BY rr.created_at DESC LIMIT $limit OFFSET $offset";
    
    $result = $conn->query($sql);
    $reservations = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    
    // Get count
    $countSql = "SELECT COUNT(*) as count
                 FROM reservations rr
                 LEFT JOIN users u ON rr.user_id = u.id
                 LEFT JOIN rooms r ON rr.room_id = r.id";
    if (!empty($where)) {
        $countSql .= " WHERE " . implode(' AND ', $where);
    }
    $countResult = $conn->query($countSql);
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;
    
    echo json_encode([
        'success' => true,
        'data' => $reservations,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function getRooms() {
    global $conn;
    
    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    
    $sql = "SELECT * FROM rooms ORDER BY room_number ASC LIMIT $limit OFFSET $offset";
    
    $result = $conn->query($sql);
    $rooms = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    
    // Get count
    $countResult = $conn->query("SELECT COUNT(*) as count FROM rooms");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;
    
    echo json_encode([
        'success' => true,
        'data' => $rooms,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function reservationStatusLabel(string $status): string {
    $map = [
        'pending' => 'Pending',
        'confirmed' => 'Confirmed',
        'checked_in' => 'Checked In',
        'checked_out' => 'Checked Out',
        'cancelled' => 'Cancelled',
    ];
    return $map[$status] ?? ucfirst(str_replace('_', ' ', $status));
}

function buildReservationStatusEmailBody(array $data): string {
    $guestName = htmlspecialchars((string)($data['guest_name'] ?? 'Guest'), ENT_QUOTES, 'UTF-8');
    $bookingId = htmlspecialchars((string)($data['booking_id'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $statusLabel = htmlspecialchars((string)($data['status_label'] ?? 'Updated'), ENT_QUOTES, 'UTF-8');
    $roomType = htmlspecialchars((string)($data['room_type'] ?? 'Room'), ENT_QUOTES, 'UTF-8');
    $checkIn = htmlspecialchars((string)($data['check_in'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $checkOut = htmlspecialchars((string)($data['check_out'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $guests = htmlspecialchars((string)($data['guests'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $totalPrice = htmlspecialchars((string)($data['total_price'] ?? '0.00'), ENT_QUOTES, 'UTF-8');

    return <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family: Arial, sans-serif; color: #1f2937; background:#f5f7fb; margin:0; padding:24px;">
    <div style="max-width:680px; margin:0 auto; background:#fff; border:1px solid #dbe5ef; border-radius:12px; overflow:hidden;">
        <div style="padding:20px 24px; background:#0f2942; color:#fff;">
            <h2 style="margin:0;">Zemaraim Reservation Status Update</h2>
        </div>
        <div style="padding:22px 24px;">
            <p>Hello {$guestName}, your reservation status has been updated.</p>
            <table style="width:100%; border-collapse:collapse;">
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Booking ID</td><td style="padding:8px 0;">#{$bookingId}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">New Status</td><td style="padding:8px 0;">{$statusLabel}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Room</td><td style="padding:8px 0;">{$roomType}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Check In</td><td style="padding:8px 0;">{$checkIn}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Check Out</td><td style="padding:8px 0;">{$checkOut}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Guests</td><td style="padding:8px 0;">{$guests}</td></tr>
            </table>
            <p style="margin-top:12px; padding:12px 14px; border:1px solid #cfe0f3; border-radius:10px; background:#f3f8fe;">
                <strong>Total Cost:</strong> \${$totalPrice}
            </p>
        </div>
    </div>
</body>
</html>
HTML;
}

function serviceReservationStatusLabel(string $status): string {
    $map = [
        'pending' => 'Pending',
        'confirmed' => 'Confirmed',
        'completed' => 'Completed',
        'cancelled' => 'Cancelled',
    ];
    return $map[$status] ?? ucfirst(str_replace('_', ' ', $status));
}

function buildServiceReservationStatusEmailBody(array $data): string {
    $guestName = htmlspecialchars((string)($data['guest_name'] ?? 'Guest'), ENT_QUOTES, 'UTF-8');
    $bookingId = htmlspecialchars((string)($data['booking_id'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $statusLabel = htmlspecialchars((string)($data['status_label'] ?? 'Updated'), ENT_QUOTES, 'UTF-8');
    $serviceName = htmlspecialchars((string)($data['service_name'] ?? 'Service'), ENT_QUOTES, 'UTF-8');
    $startDate = htmlspecialchars((string)($data['start_date'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $endDate = htmlspecialchars((string)($data['end_date'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $guests = htmlspecialchars((string)($data['guests'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $totalPrice = htmlspecialchars((string)($data['total_price'] ?? '0.00'), ENT_QUOTES, 'UTF-8');

    return <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family: Arial, sans-serif; color: #1f2937; background:#f5f7fb; margin:0; padding:24px;">
    <div style="max-width:680px; margin:0 auto; background:#fff; border:1px solid #dbe5ef; border-radius:12px; overflow:hidden;">
        <div style="padding:20px 24px; background:#0f2942; color:#fff;">
            <h2 style="margin:0;">Zemaraim Service Reservation Status Update</h2>
        </div>
        <div style="padding:22px 24px;">
            <p>Hello {$guestName}, your service reservation status has been updated.</p>
            <table style="width:100%; border-collapse:collapse;">
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Booking ID</td><td style="padding:8px 0;">#{$bookingId}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">New Status</td><td style="padding:8px 0;">{$statusLabel}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Service</td><td style="padding:8px 0;">{$serviceName}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Start Date</td><td style="padding:8px 0;">{$startDate}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">End Date</td><td style="padding:8px 0;">{$endDate}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Guests</td><td style="padding:8px 0;">{$guests}</td></tr>
            </table>
            <p style="margin-top:12px; padding:12px 14px; border:1px solid #cfe0f3; border-radius:10px; background:#f3f8fe;">
                <strong>Total Cost:</strong> \${$totalPrice}
            </p>
        </div>
    </div>
</body>
</html>
HTML;
}

function buildReservationPaymentEmailBody(array $data): string {
    $guestName = htmlspecialchars((string)($data['guest_name'] ?? 'Guest'), ENT_QUOTES, 'UTF-8');
    $bookingId = htmlspecialchars((string)($data['booking_id'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $roomType = htmlspecialchars((string)($data['room_type'] ?? 'Room'), ENT_QUOTES, 'UTF-8');
    $checkIn = htmlspecialchars((string)($data['check_in'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $checkOut = htmlspecialchars((string)($data['check_out'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $paymentStatus = htmlspecialchars((string)($data['payment_status'] ?? 'Paid'), ENT_QUOTES, 'UTF-8');
    $amount = htmlspecialchars((string)($data['amount'] ?? '0.00'), ENT_QUOTES, 'UTF-8');
    $updatedAt = htmlspecialchars((string)($data['updated_at'] ?? date('Y-m-d H:i:s')), ENT_QUOTES, 'UTF-8');

    return <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family: Arial, sans-serif; color:#1f2937; background:#f5f7fb; margin:0; padding:20px;">
    <div style="max-width:680px; width:100%; margin:0 auto; background:#fff; border:1px solid #dbe5ef; border-radius:12px; overflow:hidden;">
        <div style="padding:18px 20px; background:#0f2942; color:#fff;">
            <h2 style="margin:0; font-size:20px;">Zemaraim Payment Confirmation</h2>
        </div>
        <div style="padding:20px;">
            <p style="margin-top:0;">Hello {$guestName}, your room reservation payment has been updated.</p>
            <table style="width:100%; border-collapse:collapse;">
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Booking ID</td><td style="padding:8px 0;">#{$bookingId}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Room</td><td style="padding:8px 0;">{$roomType}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Check In</td><td style="padding:8px 0;">{$checkIn}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Check Out</td><td style="padding:8px 0;">{$checkOut}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Payment Status</td><td style="padding:8px 0;">{$paymentStatus}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Updated At</td><td style="padding:8px 0;">{$updatedAt}</td></tr>
            </table>
            <p style="margin:12px 0 0; padding:12px 14px; border:1px solid #cfe0f3; border-radius:10px; background:#f3f8fe;">
                <strong>Paid Amount:</strong> \${$amount}
            </p>
        </div>
    </div>
</body>
</html>
HTML;
}

function buildServiceReservationPaymentEmailBody(array $data): string {
    $guestName = htmlspecialchars((string)($data['guest_name'] ?? 'Guest'), ENT_QUOTES, 'UTF-8');
    $bookingId = htmlspecialchars((string)($data['booking_id'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $serviceName = htmlspecialchars((string)($data['service_name'] ?? 'Service'), ENT_QUOTES, 'UTF-8');
    $startDate = htmlspecialchars((string)($data['start_date'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $endDate = htmlspecialchars((string)($data['end_date'] ?? '-'), ENT_QUOTES, 'UTF-8');
    $paymentStatus = htmlspecialchars((string)($data['payment_status'] ?? 'Paid'), ENT_QUOTES, 'UTF-8');
    $amount = htmlspecialchars((string)($data['amount'] ?? '0.00'), ENT_QUOTES, 'UTF-8');
    $updatedAt = htmlspecialchars((string)($data['updated_at'] ?? date('Y-m-d H:i:s')), ENT_QUOTES, 'UTF-8');

    return <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family: Arial, sans-serif; color:#1f2937; background:#f5f7fb; margin:0; padding:20px;">
    <div style="max-width:680px; width:100%; margin:0 auto; background:#fff; border:1px solid #dbe5ef; border-radius:12px; overflow:hidden;">
        <div style="padding:18px 20px; background:#0f2942; color:#fff;">
            <h2 style="margin:0; font-size:20px;">Zemaraim Service Payment Confirmation</h2>
        </div>
        <div style="padding:20px;">
            <p style="margin-top:0;">Hello {$guestName}, your service reservation payment has been updated.</p>
            <table style="width:100%; border-collapse:collapse;">
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Booking ID</td><td style="padding:8px 0;">#{$bookingId}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Service</td><td style="padding:8px 0;">{$serviceName}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Start Date</td><td style="padding:8px 0;">{$startDate}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">End Date</td><td style="padding:8px 0;">{$endDate}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Payment Status</td><td style="padding:8px 0;">{$paymentStatus}</td></tr>
                <tr><td style="padding:8px 0; color:#607286; font-weight:600;">Updated At</td><td style="padding:8px 0;">{$updatedAt}</td></tr>
            </table>
            <p style="margin:12px 0 0; padding:12px 14px; border:1px solid #cfe0f3; border-radius:10px; background:#f3f8fe;">
                <strong>Paid Amount:</strong> \${$amount}
            </p>
        </div>
    </div>
</body>
</html>
HTML;
}

function addRoom() {
    global $conn;

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $roomNumber = trim((string)($_POST['room_number'] ?? ''));
    $roomType = trim((string)($_POST['room_type'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $maxGuests = intval($_POST['max_guests'] ?? 0);
    $price = floatval($_POST['price_per_night'] ?? 0);
    $floor = isset($_POST['floor_number']) && $_POST['floor_number'] !== '' ? intval($_POST['floor_number']) : null;
    $viewType = trim((string)($_POST['view_type'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $isAvailable = isset($_POST['is_available']) ? intval($_POST['is_available']) : 1;
    $isAvailable = $isAvailable === 1 ? 1 : 0;

    if ($roomNumber === '' || $roomType === '' || $maxGuests <= 0 || $price < 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Room number, type, guests and price are required']);
        return;
    }

    $uploadResult = handleRoomImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }

    if ($imageUrl === '') {
        $imageUrl = 'images/room/1.webp';
    }

    $stmt = $conn->prepare("INSERT INTO rooms (room_number, room_type, description, max_guests, price_per_night, floor_number, view_type, image_url, is_available) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare add room query']);
        return;
    }

    $stmt->bind_param('sssidissi', $roomNumber, $roomType, $description, $maxGuests, $price, $floor, $viewType, $imageUrl, $isAvailable);
    $ok = $stmt->execute();
    $errno = $stmt->errno;
    $insertId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        if ($errno === 1062) {
            http_response_code(422);
            echo json_encode(['success' => false, 'error' => 'Room number already exists']);
            return;
        }
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add room']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Room added successfully',
        'data' => ['id' => $insertId]
    ]);
}

function updateRoom() {
    global $conn;

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $roomNumber = trim((string)($_POST['room_number'] ?? ''));
    $roomType = trim((string)($_POST['room_type'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $maxGuests = intval($_POST['max_guests'] ?? 0);
    $price = floatval($_POST['price_per_night'] ?? 0);
    $floor = isset($_POST['floor_number']) && $_POST['floor_number'] !== '' ? intval($_POST['floor_number']) : null;
    $viewType = trim((string)($_POST['view_type'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $isAvailable = isset($_POST['is_available']) ? intval($_POST['is_available']) : 1;
    $isAvailable = $isAvailable === 1 ? 1 : 0;

    if ($id <= 0 || $roomNumber === '' || $roomType === '' || $maxGuests <= 0 || $price < 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid room data']);
        return;
    }

    $uploadResult = handleRoomImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }

    if ($imageUrl === '') {
        $imageUrl = 'images/room/1.webp';
    }

    $stmt = $conn->prepare("UPDATE rooms SET room_number = ?, room_type = ?, description = ?, max_guests = ?, price_per_night = ?, floor_number = ?, view_type = ?, image_url = ?, is_available = ? WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update room query']);
        return;
    }

    $stmt->bind_param('sssidissii', $roomNumber, $roomType, $description, $maxGuests, $price, $floor, $viewType, $imageUrl, $isAvailable, $id);
    $ok = $stmt->execute();
    $errno = $stmt->errno;
    $stmt->close();

    if (!$ok) {
        if ($errno === 1062) {
            http_response_code(422);
            echo json_encode(['success' => false, 'error' => 'Room number already exists']);
            return;
        }
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update room']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Room updated successfully',
        'data' => ['id' => $id]
    ]);
}

function deleteRoom() {
    global $conn;

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid room id']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM rooms WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete room query']);
        return;
    }

    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $errno = $stmt->errno;
    $stmt->close();

    if ($ok) {
        echo json_encode([
            'success' => true,
            'message' => 'Room deleted successfully',
            'data' => ['id' => $id, 'deleted' => true]
        ]);
        return;
    }

    if ($errno === 1451) {
        $softStmt = $conn->prepare("UPDATE rooms SET is_available = 0 WHERE id = ?");
        if (!$softStmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to deactivate linked room']);
            return;
        }

        $softStmt->bind_param('i', $id);
        $softOk = $softStmt->execute();
        $softStmt->close();

        if ($softOk) {
            echo json_encode([
                'success' => true,
                'message' => 'Room has bookings and was set to unavailable',
                'data' => ['id' => $id, 'deleted' => false, 'soft_deleted' => true]
            ]);
            return;
        }
    }

    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Failed to delete room']);
}

function getGallery() {
    global $conn;
    ensureGalleryTable();

    $limit = intval($_GET['limit'] ?? 100);
    $offset = intval($_GET['offset'] ?? 0);

    $sql = "SELECT * FROM gallery ORDER BY sort_order ASC, id DESC LIMIT $limit OFFSET $offset";
    $result = $conn->query($sql);
    $items = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

    $countResult = $conn->query("SELECT COUNT(*) as count FROM gallery");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;

    echo json_encode([
        'success' => true,
        'data' => $items,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function addGallery() {
    global $conn;
    ensureGalleryTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $category = trim((string)($_POST['category'] ?? ''));
    $sortOrder = intval($_POST['sort_order'] ?? 0);
    $isFeatured = isset($_POST['is_featured']) ? intval($_POST['is_featured']) : 0;
    $isFeatured = $isFeatured === 1 ? 1 : 0;

    if ($title === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Gallery title is required']);
        return;
    }

    $uploadResult = handleGalleryImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }
    if ($imageUrl === '') {
        $imageUrl = 'images/gallery/1.webp';
    }

    $stmt = $conn->prepare("INSERT INTO gallery (title, description, image_url, category, sort_order, is_featured) VALUES (?, ?, ?, ?, ?, ?)");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare add gallery query']);
        return;
    }

    $stmt->bind_param('ssssii', $title, $description, $imageUrl, $category, $sortOrder, $isFeatured);
    $ok = $stmt->execute();
    $insertId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add gallery image']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Gallery image added successfully',
        'data' => ['id' => $insertId]
    ]);
}

function updateGallery() {
    global $conn;
    ensureGalleryTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $category = trim((string)($_POST['category'] ?? ''));
    $sortOrder = intval($_POST['sort_order'] ?? 0);
    $isFeatured = isset($_POST['is_featured']) ? intval($_POST['is_featured']) : 0;
    $isFeatured = $isFeatured === 1 ? 1 : 0;

    if ($id <= 0 || $title === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid gallery data']);
        return;
    }

    $uploadResult = handleGalleryImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }
    if ($imageUrl === '') {
        $imageUrl = 'images/gallery/1.webp';
    }

    $stmt = $conn->prepare("UPDATE gallery SET title = ?, description = ?, image_url = ?, category = ?, sort_order = ?, is_featured = ? WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update gallery query']);
        return;
    }

    $stmt->bind_param('ssssiii', $title, $description, $imageUrl, $category, $sortOrder, $isFeatured, $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update gallery image']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Gallery image updated successfully',
        'data' => ['id' => $id]
    ]);
}

function deleteGallery() {
    global $conn;
    ensureGalleryTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid gallery id']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM gallery WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete gallery query']);
        return;
    }

    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to delete gallery image']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Gallery image deleted successfully',
        'data' => ['id' => $id]
    ]);
}

function getNews() {
    global $conn;
    ensureNewsTable();

    $limit = intval($_GET['limit'] ?? 100);
    $offset = intval($_GET['offset'] ?? 0);

    $sql = "SELECT id, title, slug, content, summary, author_id, featured_image, status, created_at, updated_at, published_at
            FROM news
            ORDER BY created_at DESC
            LIMIT $limit OFFSET $offset";
    $result = $conn->query($sql);
    $items = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

    $countResult = $conn->query("SELECT COUNT(*) as count FROM news");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;

    echo json_encode([
        'success' => true,
        'data' => $items,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function addNews() {
    global $conn;
    ensureNewsTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $title = trim((string)($_POST['title'] ?? ''));
    $summary = trim((string)($_POST['summary'] ?? ''));
    $content = trim((string)($_POST['content'] ?? ''));
    $status = trim((string)($_POST['status'] ?? 'draft'));
    $featuredImage = trim((string)($_POST['featured_image'] ?? ''));
    $authorId = intval(getCurrentUserId() ?? 0);

    if ($title === '' || $content === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Title and content are required']);
        return;
    }

    $allowed = ['draft', 'published', 'archived'];
    if (!in_array($status, $allowed, true)) {
        $status = 'draft';
    }

    $uploadResult = handleNewsImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $featuredImage = $uploadResult['path'];
    }
    if ($featuredImage === '') {
        $featuredImage = 'images/news/1.webp';
    }

    $slug = generateUniqueNewsSlug($title);
    $publishedAt = $status === 'published' ? date('Y-m-d H:i:s') : null;

    $stmt = $conn->prepare("INSERT INTO news (title, slug, content, summary, author_id, featured_image, status, published_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare add news query']);
        return;
    }

    $stmt->bind_param('ssssisss', $title, $slug, $content, $summary, $authorId, $featuredImage, $status, $publishedAt);
    $ok = $stmt->execute();
    $insertId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add news']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'News added successfully',
        'data' => ['id' => $insertId]
    ]);
}

function updateNews() {
    global $conn;
    ensureNewsTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $title = trim((string)($_POST['title'] ?? ''));
    $summary = trim((string)($_POST['summary'] ?? ''));
    $content = trim((string)($_POST['content'] ?? ''));
    $status = trim((string)($_POST['status'] ?? 'draft'));
    $featuredImage = trim((string)($_POST['featured_image'] ?? ''));

    if ($id <= 0 || $title === '' || $content === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid news data']);
        return;
    }

    $allowed = ['draft', 'published', 'archived'];
    if (!in_array($status, $allowed, true)) {
        $status = 'draft';
    }

    $uploadResult = handleNewsImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $featuredImage = $uploadResult['path'];
    }
    if ($featuredImage === '') {
        $featuredImage = 'images/news/1.webp';
    }

    $slug = generateUniqueNewsSlug($title, $id);
    $publishedAt = $status === 'published' ? date('Y-m-d H:i:s') : null;

    $stmt = $conn->prepare("UPDATE news SET title = ?, slug = ?, content = ?, summary = ?, featured_image = ?, status = ?, published_at = ? WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update news query']);
        return;
    }

    $stmt->bind_param('sssssssi', $title, $slug, $content, $summary, $featuredImage, $status, $publishedAt, $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update news']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'News updated successfully',
        'data' => ['id' => $id]
    ]);
}

function deleteNews() {
    global $conn;
    ensureNewsTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid news id']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM news WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete news query']);
        return;
    }

    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to delete news']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'News deleted successfully',
        'data' => ['id' => $id]
    ]);
}

function getOffers() {
    global $conn;
    ensureSpecialOffersTable();

    $limit = intval($_GET['limit'] ?? 100);
    $offset = intval($_GET['offset'] ?? 0);

    $sql = "SELECT id, title, description, discount_type, discount_value, start_date, end_date, is_active, max_redemptions, redemptions_count, created_at
            FROM special_offers
            ORDER BY created_at DESC
            LIMIT $limit OFFSET $offset";
    $result = $conn->query($sql);
    $items = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

    $countResult = $conn->query("SELECT COUNT(*) as count FROM special_offers");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;

    echo json_encode([
        'success' => true,
        'data' => $items,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function addOffer() {
    global $conn;
    ensureSpecialOffersTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $discountType = trim((string)($_POST['discount_type'] ?? 'percentage'));
    $discountValue = floatval($_POST['discount_value'] ?? 0);
    $startDate = trim((string)($_POST['start_date'] ?? ''));
    $endDate = trim((string)($_POST['end_date'] ?? ''));
    $isActive = isset($_POST['is_active']) ? intval($_POST['is_active']) : 1;
    $isActive = $isActive === 1 ? 1 : 0;
    $maxRedemptions = isset($_POST['max_redemptions']) && $_POST['max_redemptions'] !== '' ? intval($_POST['max_redemptions']) : null;

    if ($title === '' || $startDate === '' || $endDate === '' || $discountValue < 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid offer data']);
        return;
    }

    if (!in_array($discountType, ['percentage', 'fixed_amount'], true)) {
        $discountType = 'percentage';
    }

    if ($startDate > $endDate) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'End date must be on or after start date']);
        return;
    }

    $applicableRooms = null;
    if ($maxRedemptions === null) {
        $stmt = $conn->prepare("INSERT INTO special_offers (title, description, discount_type, discount_value, applicable_rooms, start_date, end_date, is_active, max_redemptions) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NULL)");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare add offer query']);
            return;
        }
        $stmt->bind_param('sssdsssi', $title, $description, $discountType, $discountValue, $applicableRooms, $startDate, $endDate, $isActive);
    } else {
        $stmt = $conn->prepare("INSERT INTO special_offers (title, description, discount_type, discount_value, applicable_rooms, start_date, end_date, is_active, max_redemptions) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare add offer query']);
            return;
        }
        $stmt->bind_param('sssdsssii', $title, $description, $discountType, $discountValue, $applicableRooms, $startDate, $endDate, $isActive, $maxRedemptions);
    }
    $ok = $stmt->execute();
    $insertId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add offer']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Offer added successfully',
        'data' => ['id' => $insertId]
    ]);
}

function updateOffer() {
    global $conn;
    ensureSpecialOffersTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $discountType = trim((string)($_POST['discount_type'] ?? 'percentage'));
    $discountValue = floatval($_POST['discount_value'] ?? 0);
    $startDate = trim((string)($_POST['start_date'] ?? ''));
    $endDate = trim((string)($_POST['end_date'] ?? ''));
    $isActive = isset($_POST['is_active']) ? intval($_POST['is_active']) : 1;
    $isActive = $isActive === 1 ? 1 : 0;
    $maxRedemptions = isset($_POST['max_redemptions']) && $_POST['max_redemptions'] !== '' ? intval($_POST['max_redemptions']) : null;

    if ($id <= 0 || $title === '' || $startDate === '' || $endDate === '' || $discountValue < 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid offer data']);
        return;
    }

    if (!in_array($discountType, ['percentage', 'fixed_amount'], true)) {
        $discountType = 'percentage';
    }

    if ($startDate > $endDate) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'End date must be on or after start date']);
        return;
    }

    $applicableRooms = null;
    if ($maxRedemptions === null) {
        $stmt = $conn->prepare("UPDATE special_offers SET title = ?, description = ?, discount_type = ?, discount_value = ?, applicable_rooms = ?, start_date = ?, end_date = ?, is_active = ?, max_redemptions = NULL WHERE id = ?");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare update offer query']);
            return;
        }
        $stmt->bind_param('sssdsssii', $title, $description, $discountType, $discountValue, $applicableRooms, $startDate, $endDate, $isActive, $id);
    } else {
        $stmt = $conn->prepare("UPDATE special_offers SET title = ?, description = ?, discount_type = ?, discount_value = ?, applicable_rooms = ?, start_date = ?, end_date = ?, is_active = ?, max_redemptions = ? WHERE id = ?");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare update offer query']);
            return;
        }
        $stmt->bind_param('sssdsssiii', $title, $description, $discountType, $discountValue, $applicableRooms, $startDate, $endDate, $isActive, $maxRedemptions, $id);
    }
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update offer']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Offer updated successfully',
        'data' => ['id' => $id]
    ]);
}

function deleteOffer() {
    global $conn;
    ensureSpecialOffersTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid offer id']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM special_offers WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete offer query']);
        return;
    }

    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to delete offer']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Offer deleted successfully',
        'data' => ['id' => $id]
    ]);
}

function getServices() {
    global $conn;
    ensureFacilitiesTable();
    
    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    
    $sql = "SELECT * FROM facilities ORDER BY name ASC LIMIT $limit OFFSET $offset";
    
    $result = $conn->query($sql);
    $services = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    
    // Get count
    $countResult = $conn->query("SELECT COUNT(*) as count FROM facilities");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;
    
    echo json_encode([
        'success' => true,
        'data' => $services,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function addService() {
    global $conn;
    ensureFacilitiesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $name = trim((string)($_POST['name'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $icon = trim((string)($_POST['icon'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    [$icon, $imageUrl] = normalizeFacilityMedia($icon, $imageUrl);
    $isActive = isset($_POST['is_active']) ? intval($_POST['is_active']) : 1;
    $isActive = $isActive === 1 ? 1 : 0;

    if ($name === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Service name is required']);
        return;
    }

    $uploadResult = handleServiceImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }

    $hasIconColumn = facilitiesHasColumn('icon');
    if ($hasIconColumn) {
        $stmt = $conn->prepare("INSERT INTO facilities (name, description, icon, image_url, is_active) VALUES (?, ?, ?, ?, ?)");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare add service query']);
            return;
        }
        $stmt->bind_param('ssssi', $name, $description, $icon, $imageUrl, $isActive);
    } else {
        $stmt = $conn->prepare("INSERT INTO facilities (name, description, image_url, is_active) VALUES (?, ?, ?, ?)");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare add service query']);
            return;
        }
        $stmt->bind_param('sssi', $name, $description, $imageUrl, $isActive);
    }
    $ok = $stmt->execute();
    $insertId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add service']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Service added successfully',
        'data' => ['id' => $insertId]
    ]);
}

function updateService() {
    global $conn;
    ensureFacilitiesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $name = trim((string)($_POST['name'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $icon = trim((string)($_POST['icon'] ?? ''));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    [$icon, $imageUrl] = normalizeFacilityMedia($icon, $imageUrl);
    $isActive = isset($_POST['is_active']) ? intval($_POST['is_active']) : 1;
    $isActive = $isActive === 1 ? 1 : 0;

    if ($id <= 0 || $name === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid service data']);
        return;
    }

    $uploadResult = handleServiceImageUpload('image_file');
    if ($uploadResult['ok'] === false) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => $uploadResult['error']]);
        return;
    }
    if (!empty($uploadResult['path'])) {
        $imageUrl = $uploadResult['path'];
    }

    $hasIconColumn = facilitiesHasColumn('icon');
    if ($hasIconColumn) {
        $stmt = $conn->prepare("UPDATE facilities SET name = ?, description = ?, icon = ?, image_url = ?, is_active = ? WHERE id = ?");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare update service query']);
            return;
        }
        $stmt->bind_param('ssssii', $name, $description, $icon, $imageUrl, $isActive, $id);
    } else {
        $stmt = $conn->prepare("UPDATE facilities SET name = ?, description = ?, image_url = ?, is_active = ? WHERE id = ?");
        if (!$stmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to prepare update service query']);
            return;
        }
        $stmt->bind_param('sssii', $name, $description, $imageUrl, $isActive, $id);
    }
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update service']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Service updated successfully',
        'data' => ['id' => $id]
    ]);
}

function deleteService() {
    global $conn;
    ensureFacilitiesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid service id']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM facilities WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete service query']);
        return;
    }

    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $errno = $stmt->errno;
    $stmt->close();

    if ($ok) {
        echo json_encode([
            'success' => true,
            'message' => 'Service deleted successfully',
            'data' => ['id' => $id, 'deleted' => true]
        ]);
        return;
    }

    // If service is referenced by reservations, fallback to soft delete (inactive)
    if ($errno === 1451) {
        $softStmt = $conn->prepare("UPDATE facilities SET is_active = 0 WHERE id = ?");
        if (!$softStmt) {
            http_response_code(500);
            echo json_encode(['success' => false, 'error' => 'Failed to deactivate linked service']);
            return;
        }

        $softStmt->bind_param('i', $id);
        $softOk = $softStmt->execute();
        $softStmt->close();

        if ($softOk) {
            echo json_encode([
                'success' => true,
                'message' => 'Service has existing bookings and was set to inactive',
                'data' => ['id' => $id, 'deleted' => false, 'soft_deleted' => true]
            ]);
            return;
        }
    }

    http_response_code(500);
    echo json_encode(['success' => false, 'error' => 'Failed to delete service']);
}

function handleServiceImageUpload($fileField) {
    if (!isset($_FILES[$fileField]) || !is_array($_FILES[$fileField])) {
        return ['ok' => true, 'path' => null];
    }

    $file = $_FILES[$fileField];
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return ['ok' => true, 'path' => null];
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        return ['ok' => false, 'error' => 'Image upload failed. Please try again.'];
    }

    $tmp = (string)($file['tmp_name'] ?? '');
    if ($tmp === '' || !is_uploaded_file($tmp)) {
        return ['ok' => false, 'error' => 'Invalid uploaded image.'];
    }

    $originalName = (string)($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = ['jpg', 'jpeg', 'png', 'webp', 'gif', 'svg'];
    if (!in_array($extension, $allowed, true)) {
        return ['ok' => false, 'error' => 'Only JPG, PNG, WEBP, GIF, or SVG images are allowed.'];
    }

    $uploadRelativeDir = 'images/services';
    $uploadAbsoluteDir = realpath(__DIR__ . '/..');
    if ($uploadAbsoluteDir === false) {
        return ['ok' => false, 'error' => 'Upload directory resolution failed.'];
    }
    $uploadAbsoluteDir .= DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR . 'services';

    if (!is_dir($uploadAbsoluteDir) && !mkdir($uploadAbsoluteDir, 0775, true) && !is_dir($uploadAbsoluteDir)) {
        return ['ok' => false, 'error' => 'Unable to create service image directory.'];
    }

    $fileName = 'service-' . date('YmdHis') . '-' . uniqid('', true) . '.' . $extension;
    $destination = $uploadAbsoluteDir . DIRECTORY_SEPARATOR . $fileName;

    if (!move_uploaded_file($tmp, $destination)) {
        return ['ok' => false, 'error' => 'Unable to save uploaded image.'];
    }

    $relativePath = $uploadRelativeDir . '/' . $fileName;
    return ['ok' => true, 'path' => str_replace('\\', '/', $relativePath)];
}

function handleRoomImageUpload($fileField) {
    if (!isset($_FILES[$fileField]) || !is_array($_FILES[$fileField])) {
        return ['ok' => true, 'path' => null];
    }

    $file = $_FILES[$fileField];
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return ['ok' => true, 'path' => null];
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        return ['ok' => false, 'error' => 'Room image upload failed. Please try again.'];
    }

    $tmp = (string)($file['tmp_name'] ?? '');
    if ($tmp === '' || !is_uploaded_file($tmp)) {
        return ['ok' => false, 'error' => 'Invalid uploaded room image.'];
    }

    $originalName = (string)($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = ['jpg', 'jpeg', 'png', 'webp', 'gif', 'svg'];
    if (!in_array($extension, $allowed, true)) {
        return ['ok' => false, 'error' => 'Only JPG, PNG, WEBP, GIF, or SVG images are allowed.'];
    }

    $uploadRelativeDir = 'images/room';
    $uploadAbsoluteDir = realpath(__DIR__ . '/..');
    if ($uploadAbsoluteDir === false) {
        return ['ok' => false, 'error' => 'Upload directory resolution failed.'];
    }
    $uploadAbsoluteDir .= DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR . 'room';

    if (!is_dir($uploadAbsoluteDir) && !mkdir($uploadAbsoluteDir, 0775, true) && !is_dir($uploadAbsoluteDir)) {
        return ['ok' => false, 'error' => 'Unable to create room image directory.'];
    }

    $fileName = 'room-' . date('YmdHis') . '-' . uniqid('', true) . '.' . $extension;
    $destination = $uploadAbsoluteDir . DIRECTORY_SEPARATOR . $fileName;

    if (!move_uploaded_file($tmp, $destination)) {
        return ['ok' => false, 'error' => 'Unable to save uploaded room image.'];
    }

    $relativePath = $uploadRelativeDir . '/' . $fileName;
    return ['ok' => true, 'path' => str_replace('\\', '/', $relativePath)];
}

function handleGalleryImageUpload($fileField) {
    if (!isset($_FILES[$fileField]) || !is_array($_FILES[$fileField])) {
        return ['ok' => true, 'path' => null];
    }

    $file = $_FILES[$fileField];
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return ['ok' => true, 'path' => null];
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        return ['ok' => false, 'error' => 'Gallery image upload failed. Please try again.'];
    }

    $tmp = (string)($file['tmp_name'] ?? '');
    if ($tmp === '' || !is_uploaded_file($tmp)) {
        return ['ok' => false, 'error' => 'Invalid uploaded gallery image.'];
    }

    $originalName = (string)($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = ['jpg', 'jpeg', 'png', 'webp', 'gif', 'svg'];
    if (!in_array($extension, $allowed, true)) {
        return ['ok' => false, 'error' => 'Only JPG, PNG, WEBP, GIF, or SVG images are allowed.'];
    }

    $uploadRelativeDir = 'images/gallery';
    $uploadAbsoluteDir = realpath(__DIR__ . '/..');
    if ($uploadAbsoluteDir === false) {
        return ['ok' => false, 'error' => 'Upload directory resolution failed.'];
    }
    $uploadAbsoluteDir .= DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR . 'gallery';

    if (!is_dir($uploadAbsoluteDir) && !mkdir($uploadAbsoluteDir, 0775, true) && !is_dir($uploadAbsoluteDir)) {
        return ['ok' => false, 'error' => 'Unable to create gallery image directory.'];
    }

    $fileName = 'gallery-' . date('YmdHis') . '-' . uniqid('', true) . '.' . $extension;
    $destination = $uploadAbsoluteDir . DIRECTORY_SEPARATOR . $fileName;

    if (!move_uploaded_file($tmp, $destination)) {
        return ['ok' => false, 'error' => 'Unable to save uploaded gallery image.'];
    }

    $relativePath = $uploadRelativeDir . '/' . $fileName;
    return ['ok' => true, 'path' => str_replace('\\', '/', $relativePath)];
}

function ensureGalleryTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS gallery (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        image_url VARCHAR(255) NOT NULL,
        category VARCHAR(100),
        sort_order INT DEFAULT 0,
        is_featured BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_category (category)
    )";

    $conn->query($sql);
}

function ensureFacilitiesTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS facilities (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        icon VARCHAR(255),
        image_url VARCHAR(255),
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_name (name)
    )";

    $conn->query($sql);

    $requiredColumns = [
        'description' => "TEXT NULL",
        'icon' => "VARCHAR(255) NULL",
        'image_url' => "VARCHAR(255) NULL",
        'is_active' => "BOOLEAN DEFAULT TRUE",
        'created_at' => "TIMESTAMP DEFAULT CURRENT_TIMESTAMP"
    ];

    foreach ($requiredColumns as $column => $definition) {
        $check = $conn->query("SHOW COLUMNS FROM facilities LIKE '" . $conn->real_escape_string($column) . "'");
        if ($check && $check->num_rows > 0) {
            continue;
        }
        $conn->query("ALTER TABLE facilities ADD COLUMN {$column} {$definition}");
    }
}

function facilitiesHasColumn($columnName) {
    global $conn;

    $column = $conn->real_escape_string((string)$columnName);
    $result = $conn->query("SHOW COLUMNS FROM facilities LIKE '{$column}'");
    return $result && $result->num_rows > 0;
}

function normalizeFacilityMedia($icon, $imageUrl) {
    $icon = trim((string)$icon);
    $imageUrl = trim((string)$imageUrl);

    $isIconClass = static function ($value) {
        return preg_match('/^(icofont-|fa[srlbd]?\s|fa[srlbd]?-)/i', (string)$value) === 1;
    };
    $isIconImage = static function ($value) {
        return preg_match('/(\.svg(\?.*)?$|\/svg\/|icon)/i', (string)$value) === 1;
    };
    $isPhoto = static function ($value) {
        return preg_match('/\.(webp|png|jpe?g|gif)(\?.*)?$/i', (string)$value) === 1
            && preg_match('/\/svg\//i', (string)$value) !== 1;
    };

    // Auto-fix old swapped values to protect admin + website rendering.
    if (($isIconClass($imageUrl) || $isIconImage($imageUrl)) && $isPhoto($icon)) {
        $tmp = $icon;
        $icon = $imageUrl;
        $imageUrl = $tmp;
    }

    return [$icon, $imageUrl];
}

function ensureNewsTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS news (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        slug VARCHAR(255) UNIQUE NOT NULL,
        content LONGTEXT NOT NULL,
        summary VARCHAR(500),
        author_id INT,
        featured_image VARCHAR(255),
        status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
        views INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        published_at DATETIME,
        INDEX idx_slug (slug),
        INDEX idx_status (status)
    )";

    $conn->query($sql);
}

function ensureSpecialOffersTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS special_offers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        discount_type ENUM('percentage', 'fixed_amount') NOT NULL,
        discount_value DECIMAL(10, 2) NOT NULL,
        applicable_rooms JSON,
        start_date DATE NOT NULL,
        end_date DATE NOT NULL,
        is_active BOOLEAN DEFAULT TRUE,
        max_redemptions INT,
        redemptions_count INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_active (is_active),
        INDEX idx_dates (start_date, end_date)
    )";

    $conn->query($sql);
}

function ensureServiceReservationPaymentColumn() {
    global $conn;

    $check = $conn->query("SHOW COLUMNS FROM service_reservations LIKE 'payment_status'");
    if ($check && $check->num_rows > 0) {
        return;
    }

    $conn->query("ALTER TABLE service_reservations ADD COLUMN payment_status ENUM('unpaid', 'partial', 'paid') DEFAULT 'unpaid' AFTER total_price");
}

function ensureNewsletterSubscribersTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS newsletter_subscribers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) NOT NULL UNIQUE,
        full_name VARCHAR(255) DEFAULT NULL,
        status ENUM('active', 'inactive') DEFAULT 'active',
        source VARCHAR(60) DEFAULT 'website',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";

    $conn->query($sql);
}

function normalizeHexColor($value, $fallback) {
    $value = trim((string)$value);
    if (preg_match('/^#([a-fA-F0-9]{6}|[a-fA-F0-9]{3})$/', $value) === 1) {
        if (strlen($value) === 4) {
            return '#' . $value[1] . $value[1] . $value[2] . $value[2] . $value[3] . $value[3];
        }
        return strtoupper($value);
    }
    return $fallback;
}

function ensureHeroSlidesTable() {
    global $conn;

    $sql = "CREATE TABLE IF NOT EXISTS hero_slides (
        id INT AUTO_INCREMENT PRIMARY KEY,
        label VARCHAR(120) NOT NULL,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        button_text VARCHAR(120) NOT NULL DEFAULT 'Explore',
        button_url VARCHAR(255) NOT NULL DEFAULT 'rooms.php',
        image_url VARCHAR(255) NOT NULL,
        label_color CHAR(7) NOT NULL DEFAULT '#FCBF49',
        title_color CHAR(7) NOT NULL DEFAULT '#FFFFFF',
        description_color CHAR(7) NOT NULL DEFAULT '#E5E7EB',
        button_text_color CHAR(7) NOT NULL DEFAULT '#10243D',
        button_bg_color CHAR(7) NOT NULL DEFAULT '#FCBF49',
        sort_order INT NOT NULL DEFAULT 0,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_active_sort (is_active, sort_order, id)
    )";
    $conn->query($sql);

    $requiredColumns = [
        'label_color' => "ALTER TABLE hero_slides ADD COLUMN label_color CHAR(7) NOT NULL DEFAULT '#FCBF49' AFTER image_url",
        'title_color' => "ALTER TABLE hero_slides ADD COLUMN title_color CHAR(7) NOT NULL DEFAULT '#FFFFFF' AFTER label_color",
        'description_color' => "ALTER TABLE hero_slides ADD COLUMN description_color CHAR(7) NOT NULL DEFAULT '#E5E7EB' AFTER title_color",
        'button_text_color' => "ALTER TABLE hero_slides ADD COLUMN button_text_color CHAR(7) NOT NULL DEFAULT '#10243D' AFTER description_color",
        'button_bg_color' => "ALTER TABLE hero_slides ADD COLUMN button_bg_color CHAR(7) NOT NULL DEFAULT '#FCBF49' AFTER button_text_color",
    ];

    foreach ($requiredColumns as $columnName => $alterSql) {
        $check = $conn->query("SHOW COLUMNS FROM hero_slides LIKE '{$columnName}'");
        if (!($check instanceof mysqli_result) || $check->num_rows === 0) {
            $conn->query($alterSql);
        }
        if ($check instanceof mysqli_result) {
            $check->free();
        }
    }
}

function seedHeroSlidesIfEmpty() {
    global $conn;
    ensureHeroSlidesTable();

    $countResult = $conn->query("SELECT COUNT(*) AS total FROM hero_slides");
    $countRow = $countResult ? $countResult->fetch_assoc() : null;
    $totalSlides = (int)($countRow['total'] ?? 0);
    if ($totalSlides > 0) {
        return;
    }

    $defaultSlides = [
        [
            'label' => 'Luxury Accommodation',
            'title' => 'Experience Premium Comfort',
            'description' => 'Discover spacious suites with modern amenities, premium bedding, and stunning city views.',
            'button_text' => 'Explore Rooms',
            'button_url' => 'rooms.php',
            'image_url' => 'images/background/1.webp',
            'sort_order' => 1,
        ],
        [
            'label' => 'Culinary Delight',
            'title' => 'Savor Exquisite Flavors',
            'description' => 'Indulge in world-class dining experiences prepared by our renowned chefs.',
            'button_text' => 'See Dining Options',
            'button_url' => 'facilities.php',
            'image_url' => 'images/background/8.webp',
            'sort_order' => 2,
        ],
        [
            'label' => 'Wellness Retreat',
            'title' => 'Relax & Rejuvenate',
            'description' => 'Unwind with our premium spa services and wellness facilities designed for your well-being.',
            'button_text' => 'Explore Spa',
            'button_url' => 'facilities.php',
            'image_url' => 'images/background/10.webp',
            'sort_order' => 3,
        ],
        [
            'label' => 'Exclusive Deals',
            'title' => 'Save on Your Getaway',
            'description' => 'Enjoy special rates and flexible booking options for your perfect stay.',
            'button_text' => 'View Offers',
            'button_url' => 'offer.php',
            'image_url' => 'images/misc/4.webp',
            'sort_order' => 4,
        ],
    ];

    $insertStmt = $conn->prepare(
        "INSERT INTO hero_slides
            (label, title, description, button_text, button_url, image_url, label_color, title_color, description_color, button_text_color, button_bg_color, sort_order, is_active)
         VALUES
            (?, ?, ?, ?, ?, ?, '#FCBF49', '#FFFFFF', '#E5E7EB', '#10243D', '#FCBF49', ?, 1)"
    );
    if (!$insertStmt) {
        return;
    }

    foreach ($defaultSlides as $slide) {
        $insertStmt->bind_param(
            'ssssssi',
            $slide['label'],
            $slide['title'],
            $slide['description'],
            $slide['button_text'],
            $slide['button_url'],
            $slide['image_url'],
            $slide['sort_order']
        );
        $insertStmt->execute();
    }
    $insertStmt->close();
}

function getHeroSlides() {
    global $conn;
    ensureHeroSlidesTable();
    seedHeroSlidesIfEmpty();

    $limit = intval($_GET['limit'] ?? 60);
    $offset = intval($_GET['offset'] ?? 0);
    if ($limit < 1) {
        $limit = 1;
    }
    if ($limit > 200) {
        $limit = 200;
    }

    $sql = "SELECT id, label, title, description, button_text, button_url, image_url,
                   label_color, title_color, description_color, button_text_color, button_bg_color,
                   sort_order, is_active, created_at
            FROM hero_slides
            ORDER BY sort_order ASC, id ASC
            LIMIT {$limit} OFFSET {$offset}";
    $result = $conn->query($sql);
    $rows = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

    $countResult = $conn->query("SELECT COUNT(*) AS count FROM hero_slides");
    $total = $countResult ? (int)($countResult->fetch_assoc()['count'] ?? 0) : 0;

    echo json_encode([
        'success' => true,
        'data' => $rows,
        'total' => $total,
        'limit' => $limit,
        'offset' => $offset,
    ]);
}

function getHeroSlideByIdForResponse($id) {
    global $conn;
    $id = intval($id);
    if ($id <= 0) {
        return null;
    }

    $stmt = $conn->prepare(
        "SELECT id, label, title, description, button_text, button_url, image_url,
                label_color, title_color, description_color, button_text_color, button_bg_color,
                sort_order, is_active, created_at
         FROM hero_slides
         WHERE id = ?
         LIMIT 1"
    );
    if (!$stmt) {
        return null;
    }
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();
    return $row ?: null;
}

function addHeroSlide() {
    global $conn;
    ensureHeroSlidesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $label = trim((string)($_POST['label'] ?? 'Featured'));
    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $buttonText = trim((string)($_POST['button_text'] ?? 'Explore'));
    $buttonUrl = trim((string)($_POST['button_url'] ?? 'rooms.php'));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $sortOrder = intval($_POST['sort_order'] ?? 0);
    $isActive = (int)(($_POST['is_active'] ?? '1') === '1');

    if ($title === '' || $imageUrl === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Title and image URL are required']);
        return;
    }

    $labelColor = normalizeHexColor($_POST['label_color'] ?? '#FCBF49', '#FCBF49');
    $titleColor = normalizeHexColor($_POST['title_color'] ?? '#FFFFFF', '#FFFFFF');
    $descriptionColor = normalizeHexColor($_POST['description_color'] ?? '#E5E7EB', '#E5E7EB');
    $buttonTextColor = normalizeHexColor($_POST['button_text_color'] ?? '#10243D', '#10243D');
    $buttonBgColor = normalizeHexColor($_POST['button_bg_color'] ?? '#FCBF49', '#FCBF49');

    $stmt = $conn->prepare(
        "INSERT INTO hero_slides
            (label, title, description, button_text, button_url, image_url, label_color, title_color, description_color, button_text_color, button_bg_color, sort_order, is_active)
         VALUES
            (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
    );
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare add hero slide query']);
        return;
    }

    $stmt->bind_param(
        'sssssssssssii',
        $label,
        $title,
        $description,
        $buttonText,
        $buttonUrl,
        $imageUrl,
        $labelColor,
        $titleColor,
        $descriptionColor,
        $buttonTextColor,
        $buttonBgColor,
        $sortOrder,
        $isActive
    );
    $ok = $stmt->execute();
    $newId = $stmt->insert_id;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to add hero slide']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Hero slide added successfully',
        'data' => getHeroSlideByIdForResponse($newId),
    ]);
}

function updateHeroSlide() {
    global $conn;
    ensureHeroSlidesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $label = trim((string)($_POST['label'] ?? 'Featured'));
    $title = trim((string)($_POST['title'] ?? ''));
    $description = trim((string)($_POST['description'] ?? ''));
    $buttonText = trim((string)($_POST['button_text'] ?? 'Explore'));
    $buttonUrl = trim((string)($_POST['button_url'] ?? 'rooms.php'));
    $imageUrl = trim((string)($_POST['image_url'] ?? ''));
    $sortOrder = intval($_POST['sort_order'] ?? 0);
    $isActive = (int)(($_POST['is_active'] ?? '1') === '1');

    if ($id <= 0 || $title === '' || $imageUrl === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid hero slide data']);
        return;
    }

    $labelColor = normalizeHexColor($_POST['label_color'] ?? '#FCBF49', '#FCBF49');
    $titleColor = normalizeHexColor($_POST['title_color'] ?? '#FFFFFF', '#FFFFFF');
    $descriptionColor = normalizeHexColor($_POST['description_color'] ?? '#E5E7EB', '#E5E7EB');
    $buttonTextColor = normalizeHexColor($_POST['button_text_color'] ?? '#10243D', '#10243D');
    $buttonBgColor = normalizeHexColor($_POST['button_bg_color'] ?? '#FCBF49', '#FCBF49');

    $stmt = $conn->prepare(
        "UPDATE hero_slides
         SET label = ?, title = ?, description = ?, button_text = ?, button_url = ?, image_url = ?,
             label_color = ?, title_color = ?, description_color = ?, button_text_color = ?, button_bg_color = ?,
             sort_order = ?, is_active = ?, updated_at = NOW()
         WHERE id = ?"
    );
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update hero slide query']);
        return;
    }

    $stmt->bind_param(
        'sssssssssssiii',
        $label,
        $title,
        $description,
        $buttonText,
        $buttonUrl,
        $imageUrl,
        $labelColor,
        $titleColor,
        $descriptionColor,
        $buttonTextColor,
        $buttonBgColor,
        $sortOrder,
        $isActive,
        $id
    );
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update hero slide']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Hero slide updated successfully',
        'data' => getHeroSlideByIdForResponse($id),
    ]);
}

function deleteHeroSlide() {
    global $conn;
    ensureHeroSlidesTable();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    if ($id <= 0) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid hero slide id']);
        return;
    }

    $countResult = $conn->query("SELECT COUNT(*) AS total FROM hero_slides");
    $totalSlides = $countResult ? (int)($countResult->fetch_assoc()['total'] ?? 0) : 0;
    if ($totalSlides <= 1) {
        http_response_code(409);
        echo json_encode(['success' => false, 'error' => 'At least one hero slide is required']);
        return;
    }

    $stmt = $conn->prepare("DELETE FROM hero_slides WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare delete hero slide query']);
        return;
    }
    $stmt->bind_param('i', $id);
    $ok = $stmt->execute();
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to delete hero slide']);
        return;
    }

    echo json_encode([
        'success' => true,
        'message' => 'Hero slide deleted successfully',
    ]);
}

function collectNewsletterSubscribers(): array {
    global $conn;
    ensureNewsletterSubscribersTable();

    $subscribers = [];
    $seenEmails = [];

    $manualSql = "SELECT email, full_name, source FROM newsletter_subscribers WHERE status = 'active' ORDER BY created_at DESC";
    $manualResult = $conn->query($manualSql);
    if ($manualResult instanceof mysqli_result) {
        while ($row = $manualResult->fetch_assoc()) {
            $email = strtolower(trim((string)($row['email'] ?? '')));
            if ($email === '' || isset($seenEmails[$email])) {
                continue;
            }
            $seenEmails[$email] = true;
            $subscribers[] = [
                'email' => $email,
                'name' => trim((string)($row['full_name'] ?? '')),
                'source' => trim((string)($row['source'] ?? 'website')),
            ];
        }
        $manualResult->free();
    }

    $userSql = "SELECT first_name, last_name, email
                FROM users
                WHERE user_type = 'guest' AND is_active = 1 AND email IS NOT NULL AND email <> ''";
    $userResult = $conn->query($userSql);
    if ($userResult instanceof mysqli_result) {
        while ($row = $userResult->fetch_assoc()) {
            $email = strtolower(trim((string)($row['email'] ?? '')));
            if ($email === '' || isset($seenEmails[$email])) {
                continue;
            }
            $seenEmails[$email] = true;
            $name = trim((string)($row['first_name'] ?? '') . ' ' . (string)($row['last_name'] ?? ''));
            $subscribers[] = [
                'email' => $email,
                'name' => $name,
                'source' => 'guest_account',
            ];
        }
        $userResult->free();
    }

    return $subscribers;
}

function getNewsletterSubscribers() {
    $subscribers = collectNewsletterSubscribers();

    echo json_encode([
        'success' => true,
        'data' => $subscribers,
        'total' => count($subscribers),
    ]);
}

function sendNewsletter() {
    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $subject = trim((string)($_POST['subject'] ?? ''));
    $message = trim((string)($_POST['message'] ?? ''));

    if ($subject === '' || $message === '') {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Subject and message are required']);
        return;
    }

    $subscribers = collectNewsletterSubscribers();
    if (empty($subscribers)) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'No active subscribers found']);
        return;
    }

    $safeSubject = htmlspecialchars($subject, ENT_QUOTES, 'UTF-8');
    $safeMessage = nl2br(htmlspecialchars($message, ENT_QUOTES, 'UTF-8'));
    $sentCount = 0;
    $failedCount = 0;

    foreach ($subscribers as $subscriber) {
        $email = trim((string)($subscriber['email'] ?? ''));
        if ($email === '') {
            continue;
        }
        $name = trim((string)($subscriber['name'] ?? ''));
        if ($name === '') {
            $name = 'Subscriber';
        }

        $emailBody = <<<HTML
<!DOCTYPE html>
<html>
<head><meta charset="UTF-8"></head>
<body style="font-family: Arial, sans-serif; color:#1f2937; background:#f5f7fb; margin:0; padding:20px;">
    <div style="max-width:680px; width:100%; margin:0 auto; background:#fff; border:1px solid #dbe5ef; border-radius:12px; overflow:hidden;">
        <div style="padding:18px 20px; background:#0f2942; color:#fff;">
            <h2 style="margin:0; font-size:20px;">Zemaraim Newsletter</h2>
        </div>
        <div style="padding:20px;">
            <p style="margin-top:0;">Hello {$name},</p>
            <h3 style="margin:0 0 12px; font-size:18px; color:#10243d;">{$safeSubject}</h3>
            <div style="font-size:14px; line-height:1.7; color:#31465c;">{$safeMessage}</div>
            <p style="margin:16px 0 0; font-size:12px; color:#607286;">You are receiving this email because you are subscribed to Zemaraim updates.</p>
        </div>
    </div>
</body>
</html>
HTML;

        $isSent = sendEmail($email, $name, $subject, $emailBody);
        if ($isSent) {
            $sentCount++;
        } else {
            $failedCount++;
        }
    }

    echo json_encode([
        'success' => $sentCount > 0,
        'message' => $sentCount > 0
            ? "Newsletter sent to {$sentCount} subscriber(s)." . ($failedCount > 0 ? " Failed: {$failedCount}." : '')
            : 'Unable to send newsletter at the moment',
        'sent_count' => $sentCount,
        'failed_count' => $failedCount,
        'total_subscribers' => count($subscribers),
    ]);
}

function handleNewsImageUpload($fileField) {
    if (!isset($_FILES[$fileField]) || !is_array($_FILES[$fileField])) {
        return ['ok' => true, 'path' => null];
    }

    $file = $_FILES[$fileField];
    if (($file['error'] ?? UPLOAD_ERR_NO_FILE) === UPLOAD_ERR_NO_FILE) {
        return ['ok' => true, 'path' => null];
    }

    if (($file['error'] ?? UPLOAD_ERR_OK) !== UPLOAD_ERR_OK) {
        return ['ok' => false, 'error' => 'News image upload failed. Please try again.'];
    }

    $tmp = (string)($file['tmp_name'] ?? '');
    if ($tmp === '' || !is_uploaded_file($tmp)) {
        return ['ok' => false, 'error' => 'Invalid uploaded news image.'];
    }

    $originalName = (string)($file['name'] ?? '');
    $extension = strtolower(pathinfo($originalName, PATHINFO_EXTENSION));
    $allowed = ['jpg', 'jpeg', 'png', 'webp', 'gif', 'svg'];
    if (!in_array($extension, $allowed, true)) {
        return ['ok' => false, 'error' => 'Only JPG, PNG, WEBP, GIF, or SVG images are allowed.'];
    }

    $uploadRelativeDir = 'images/news';
    $uploadAbsoluteDir = realpath(__DIR__ . '/..');
    if ($uploadAbsoluteDir === false) {
        return ['ok' => false, 'error' => 'Upload directory resolution failed.'];
    }
    $uploadAbsoluteDir .= DIRECTORY_SEPARATOR . 'images' . DIRECTORY_SEPARATOR . 'news';

    if (!is_dir($uploadAbsoluteDir) && !mkdir($uploadAbsoluteDir, 0775, true) && !is_dir($uploadAbsoluteDir)) {
        return ['ok' => false, 'error' => 'Unable to create news image directory.'];
    }

    $fileName = 'news-' . date('YmdHis') . '-' . uniqid('', true) . '.' . $extension;
    $destination = $uploadAbsoluteDir . DIRECTORY_SEPARATOR . $fileName;

    if (!move_uploaded_file($tmp, $destination)) {
        return ['ok' => false, 'error' => 'Unable to save uploaded news image.'];
    }

    $relativePath = $uploadRelativeDir . '/' . $fileName;
    return ['ok' => true, 'path' => str_replace('\\', '/', $relativePath)];
}

function generateUniqueNewsSlug($title, $excludeId = 0) {
    global $conn;

    $base = strtolower(trim($title));
    $base = preg_replace('/[^a-z0-9]+/', '-', $base);
    $base = trim((string)$base, '-');
    if ($base === '') {
        $base = 'news';
    }

    $slug = $base;
    $counter = 1;
    while (newsSlugExists($slug, $excludeId)) {
        $slug = $base . '-' . $counter;
        $counter++;
    }
    return $slug;
}

function newsSlugExists($slug, $excludeId = 0) {
    global $conn;

    if ($excludeId > 0) {
        $stmt = $conn->prepare("SELECT id FROM news WHERE slug = ? AND id != ? LIMIT 1");
        if (!$stmt) {
            return true;
        }
        $stmt->bind_param('si', $slug, $excludeId);
    } else {
        $stmt = $conn->prepare("SELECT id FROM news WHERE slug = ? LIMIT 1");
        if (!$stmt) {
            return true;
        }
        $stmt->bind_param('s', $slug);
    }

    $stmt->execute();
    $result = $stmt->get_result();
    $exists = $result && $result->num_rows > 0;
    $stmt->close();
    return $exists;
}

function getServiceReservations() {
    global $conn;
    ensureServiceReservationPaymentColumn();

    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    $status = trim((string)($_GET['status'] ?? ''));
    $guest = trim((string)($_GET['guest'] ?? ''));
    $service = trim((string)($_GET['service'] ?? ''));
    $date = trim((string)($_GET['date'] ?? ''));

    $sql = "SELECT sr.*, u.first_name, u.last_name, u.email, f.name AS service_name
            FROM service_reservations sr
            LEFT JOIN users u ON sr.user_id = u.id
            LEFT JOIN facilities f ON sr.facility_id = f.id";

    $where = [];
    if ($status !== '') {
        $statusEscaped = $conn->real_escape_string($status);
        $where[] = "sr.status = '$statusEscaped'";
    }
    if ($guest !== '') {
        $guestLike = '%' . $conn->real_escape_string($guest) . '%';
        $where[] = "(u.first_name LIKE '$guestLike' OR u.last_name LIKE '$guestLike' OR CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')) LIKE '$guestLike' OR u.email LIKE '$guestLike')";
    }
    if ($service !== '') {
        $serviceLike = '%' . $conn->real_escape_string($service) . '%';
        $where[] = "f.name LIKE '$serviceLike'";
    }
    if ($date !== '') {
        $dateEscaped = $conn->real_escape_string($date);
        $where[] = "(sr.service_start_date <= '$dateEscaped' AND COALESCE(sr.service_end_date, sr.service_start_date) >= '$dateEscaped')";
    }
    if (!empty($where)) {
        $sql .= " WHERE " . implode(' AND ', $where);
    }

    $sql .= " ORDER BY sr.created_at DESC LIMIT $limit OFFSET $offset";

    $result = $conn->query($sql);
    $serviceReservations = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];

    $countSql = "SELECT COUNT(*) as count
                 FROM service_reservations sr
                 LEFT JOIN users u ON sr.user_id = u.id
                 LEFT JOIN facilities f ON sr.facility_id = f.id";
    if (!empty($where)) {
        $countSql .= " WHERE " . implode(' AND ', $where);
    }

    $countResult = $conn->query($countSql);
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;

    echo json_encode([
        'success' => true,
        'data' => $serviceReservations,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function updateReservationStatus() {
    global $conn;

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $status = trim((string)($_POST['status'] ?? ''));
    $allowedStatuses = ['pending', 'confirmed', 'checked_in', 'checked_out', 'cancelled'];

    if ($id <= 0 || !in_array($status, $allowedStatuses, true)) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid reservation data']);
        return;
    }

    $allowCheckoutChange = isset($_POST['allow_checkout_change']) && (string)$_POST['allow_checkout_change'] === '1';
    $currentStatus = null;
    $statusStmt = $conn->prepare("SELECT status FROM reservations WHERE id = ? LIMIT 1");
    if (!$statusStmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to validate current reservation status']);
        return;
    }
    $statusStmt->bind_param('i', $id);
    $statusStmt->execute();
    $statusResult = $statusStmt->get_result();
    $statusRow = $statusResult ? $statusResult->fetch_assoc() : null;
    $statusStmt->close();

    if (!$statusRow) {
        http_response_code(404);
        echo json_encode(['success' => false, 'error' => 'Reservation not found']);
        return;
    }
    $currentStatus = strtolower((string)($statusRow['status'] ?? ''));

    if ($currentStatus === 'checked_out' && $status !== 'checked_out' && !$allowCheckoutChange) {
        http_response_code(409);
        echo json_encode(['success' => false, 'error' => 'Checked Out reservations are locked. Click Change first.']);
        return;
    }

    $stmt = $conn->prepare("UPDATE reservations SET status = ?, updated_at = NOW() WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update query']);
        return;
    }

    $stmt->bind_param('si', $status, $id);
    $ok = $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update reservation status']);
        return;
    }

    $emailSent = null;
    if ($affected > 0 && in_array($status, ['confirmed', 'checked_in', 'checked_out', 'cancelled'], true)) {
        $detailsStmt = $conn->prepare(
            "SELECT r.id, r.status, r.check_in_date, r.check_out_date, r.number_of_guests, r.total_price,
                    u.first_name, u.last_name, u.email,
                    rm.room_type
             FROM reservations r
             LEFT JOIN users u ON r.user_id = u.id
             LEFT JOIN rooms rm ON r.room_id = rm.id
             WHERE r.id = ?
             LIMIT 1"
        );
        if ($detailsStmt) {
            $detailsStmt->bind_param('i', $id);
            $detailsStmt->execute();
            $detailsResult = $detailsStmt->get_result();
            $row = $detailsResult ? $detailsResult->fetch_assoc() : null;
            $detailsStmt->close();

            if ($row && !empty($row['email'])) {
                $customerName = trim((string)($row['first_name'] ?? '') . ' ' . (string)($row['last_name'] ?? ''));
                if ($customerName === '') {
                    $customerName = 'Guest';
                }
                $statusLabel = reservationStatusLabel((string)$status);
                $subject = 'Your Zemaraim Reservation Status is now ' . $statusLabel;
                $body = buildReservationStatusEmailBody([
                    'guest_name' => $customerName,
                    'booking_id' => (string)$id,
                    'status_label' => $statusLabel,
                    'room_type' => (string)($row['room_type'] ?? 'Room'),
                    'check_in' => (string)($row['check_in_date'] ?? '-'),
                    'check_out' => (string)($row['check_out_date'] ?? '-'),
                    'guests' => (string)($row['number_of_guests'] ?? '-'),
                    'total_price' => number_format((float)($row['total_price'] ?? 0), 2),
                ]);
                $emailSent = sendEmail((string)$row['email'], $customerName, $subject, $body);
            }
        }
    }

    echo json_encode([
        'success' => true,
        'message' => $affected >= 0 ? 'Status updated successfully' : 'No changes applied',
        'data' => [
            'id' => $id,
            'status' => $status
        ],
        'email_sent' => $emailSent
    ]);
}

function updateReservationPaymentStatus() {
    global $conn;

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $paymentStatus = trim((string)($_POST['payment_status'] ?? ''));
    $allowedStatuses = ['unpaid', 'partial', 'paid'];

    if ($id <= 0 || !in_array($paymentStatus, $allowedStatuses, true)) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid payment status data']);
        return;
    }

    $allowPaidChange = isset($_POST['allow_paid_change']) && (string)$_POST['allow_paid_change'] === '1';
    $currentPaymentStatus = null;
    $paymentStatusStmt = $conn->prepare("SELECT payment_status FROM reservations WHERE id = ? LIMIT 1");
    if (!$paymentStatusStmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to validate current payment status']);
        return;
    }
    $paymentStatusStmt->bind_param('i', $id);
    $paymentStatusStmt->execute();
    $paymentStatusResult = $paymentStatusStmt->get_result();
    $paymentStatusRow = $paymentStatusResult ? $paymentStatusResult->fetch_assoc() : null;
    $paymentStatusStmt->close();

    if (!$paymentStatusRow) {
        http_response_code(404);
        echo json_encode(['success' => false, 'error' => 'Reservation not found']);
        return;
    }
    $currentPaymentStatus = strtolower((string)($paymentStatusRow['payment_status'] ?? ''));

    if ($currentPaymentStatus === 'paid' && $paymentStatus !== 'paid' && !$allowPaidChange) {
        http_response_code(409);
        echo json_encode(['success' => false, 'error' => 'Paid payment status is locked. Click Change first.']);
        return;
    }

    $stmt = $conn->prepare("UPDATE reservations SET payment_status = ?, updated_at = NOW() WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare payment update query']);
        return;
    }

    $stmt->bind_param('si', $paymentStatus, $id);
    $ok = $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update payment status']);
        return;
    }

    $emailSent = null;
    if ($affected > 0 && $paymentStatus === 'paid') {
        $detailsStmt = $conn->prepare(
            "SELECT r.id, r.check_in_date, r.check_out_date, r.total_price, r.payment_status, r.updated_at,
                    u.first_name, u.last_name, u.email,
                    rm.room_type
             FROM reservations r
             LEFT JOIN users u ON r.user_id = u.id
             LEFT JOIN rooms rm ON r.room_id = rm.id
             WHERE r.id = ?
             LIMIT 1"
        );
        if ($detailsStmt) {
            $detailsStmt->bind_param('i', $id);
            $detailsStmt->execute();
            $detailsResult = $detailsStmt->get_result();
            $row = $detailsResult ? $detailsResult->fetch_assoc() : null;
            $detailsStmt->close();

            if ($row && !empty($row['email'])) {
                $customerName = trim((string)($row['first_name'] ?? '') . ' ' . (string)($row['last_name'] ?? ''));
                if ($customerName === '') {
                    $customerName = 'Guest';
                }

                $subject = 'Payment Received for Your Zemaraim Room Reservation';
                $body = buildReservationPaymentEmailBody([
                    'guest_name' => $customerName,
                    'booking_id' => (string)$id,
                    'room_type' => (string)($row['room_type'] ?? 'Room'),
                    'check_in' => (string)($row['check_in_date'] ?? '-'),
                    'check_out' => (string)($row['check_out_date'] ?? '-'),
                    'payment_status' => ucfirst((string)($row['payment_status'] ?? 'paid')),
                    'amount' => number_format((float)($row['total_price'] ?? 0), 2),
                    'updated_at' => (string)($row['updated_at'] ?? date('Y-m-d H:i:s')),
                ]);
                $emailSent = sendEmail((string)$row['email'], $customerName, $subject, $body);
            }
        }
    }

    echo json_encode([
        'success' => true,
        'message' => $affected >= 0 ? 'Payment status updated successfully' : 'No changes applied',
        'data' => [
            'id' => $id,
            'payment_status' => $paymentStatus
        ],
        'email_sent' => $emailSent
    ]);
}

function updateServiceReservationStatus() {
    global $conn;
    ensureServiceReservationPaymentColumn();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $status = trim((string)($_POST['status'] ?? ''));
    $allowedStatuses = ['pending', 'confirmed', 'completed', 'cancelled'];

    if ($id <= 0 || !in_array($status, $allowedStatuses, true)) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid service reservation data']);
        return;
    }

    $allowCompletedChange = isset($_POST['allow_completed_change']) && (string)$_POST['allow_completed_change'] === '1';
    $currentStatus = null;
    $statusStmt = $conn->prepare("SELECT status FROM service_reservations WHERE id = ? LIMIT 1");
    if (!$statusStmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to validate current reservation status']);
        return;
    }
    $statusStmt->bind_param('i', $id);
    $statusStmt->execute();
    $statusResult = $statusStmt->get_result();
    $statusRow = $statusResult ? $statusResult->fetch_assoc() : null;
    $statusStmt->close();

    if (!$statusRow) {
        http_response_code(404);
        echo json_encode(['success' => false, 'error' => 'Service reservation not found']);
        return;
    }
    $currentStatus = strtolower((string)($statusRow['status'] ?? ''));

    if ($currentStatus === 'completed' && $status !== 'completed' && !$allowCompletedChange) {
        http_response_code(409);
        echo json_encode(['success' => false, 'error' => 'Completed reservations are locked. Click Change first.']);
        return;
    }

    $stmt = $conn->prepare("UPDATE service_reservations SET status = ?, updated_at = NOW() WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare update query']);
        return;
    }

    $stmt->bind_param('si', $status, $id);
    $ok = $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update service reservation status']);
        return;
    }

    $emailSent = null;
    if ($affected > 0 && in_array($status, ['confirmed', 'completed', 'cancelled'], true)) {
        $hasTotalPriceColumn = false;
        $totalPriceCol = $conn->query("SHOW COLUMNS FROM service_reservations LIKE 'total_price'");
        if ($totalPriceCol instanceof mysqli_result) {
            $hasTotalPriceColumn = $totalPriceCol->num_rows > 0;
            $totalPriceCol->free();
        }

        $totalPriceSelect = $hasTotalPriceColumn ? "sr.total_price" : "0 AS total_price";
        $detailsSql = "SELECT sr.id, sr.service_start_date, sr.service_end_date, sr.number_of_guests, {$totalPriceSelect},
                              u.first_name, u.last_name, u.email,
                              f.name AS service_name
                       FROM service_reservations sr
                       LEFT JOIN users u ON sr.user_id = u.id
                       LEFT JOIN facilities f ON sr.facility_id = f.id
                       WHERE sr.id = ?
                       LIMIT 1";
        $detailsStmt = $conn->prepare($detailsSql);
        if ($detailsStmt) {
            $detailsStmt->bind_param('i', $id);
            $detailsStmt->execute();
            $detailsResult = $detailsStmt->get_result();
            $row = $detailsResult ? $detailsResult->fetch_assoc() : null;
            $detailsStmt->close();

            if ($row && !empty($row['email'])) {
                $customerName = trim((string)($row['first_name'] ?? '') . ' ' . (string)($row['last_name'] ?? ''));
                if ($customerName === '') {
                    $customerName = 'Guest';
                }
                $statusLabel = serviceReservationStatusLabel((string)$status);
                $subject = 'Your Zemaraim Service Reservation Status is now ' . $statusLabel;
                $body = buildServiceReservationStatusEmailBody([
                    'guest_name' => $customerName,
                    'booking_id' => (string)$id,
                    'status_label' => $statusLabel,
                    'service_name' => (string)($row['service_name'] ?? 'Service'),
                    'start_date' => (string)($row['service_start_date'] ?? '-'),
                    'end_date' => (string)($row['service_end_date'] ?? '-'),
                    'guests' => (string)($row['number_of_guests'] ?? '-'),
                    'total_price' => number_format((float)($row['total_price'] ?? 0), 2),
                ]);
                $emailSent = sendEmail((string)$row['email'], $customerName, $subject, $body);
            }
        }
    }

    echo json_encode([
        'success' => true,
        'message' => $affected >= 0 ? 'Status updated successfully' : 'No changes applied',
        'data' => [
            'id' => $id,
            'status' => $status
        ],
        'email_sent' => $emailSent
    ]);
}

function updateServiceReservationPaymentStatus() {
    global $conn;
    ensureServiceReservationPaymentColumn();

    if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
        http_response_code(405);
        echo json_encode(['success' => false, 'error' => 'Method not allowed']);
        return;
    }

    $id = intval($_POST['id'] ?? 0);
    $paymentStatus = trim((string)($_POST['payment_status'] ?? ''));
    $allowedStatuses = ['unpaid', 'partial', 'paid'];

    if ($id <= 0 || !in_array($paymentStatus, $allowedStatuses, true)) {
        http_response_code(422);
        echo json_encode(['success' => false, 'error' => 'Invalid service payment status data']);
        return;
    }

    $allowPaidChange = isset($_POST['allow_paid_change']) && (string)$_POST['allow_paid_change'] === '1';
    $currentPaymentStatus = null;
    $paymentStatusStmt = $conn->prepare("SELECT payment_status FROM service_reservations WHERE id = ? LIMIT 1");
    if (!$paymentStatusStmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to validate current payment status']);
        return;
    }
    $paymentStatusStmt->bind_param('i', $id);
    $paymentStatusStmt->execute();
    $paymentStatusResult = $paymentStatusStmt->get_result();
    $paymentStatusRow = $paymentStatusResult ? $paymentStatusResult->fetch_assoc() : null;
    $paymentStatusStmt->close();

    if (!$paymentStatusRow) {
        http_response_code(404);
        echo json_encode(['success' => false, 'error' => 'Service reservation not found']);
        return;
    }
    $currentPaymentStatus = strtolower((string)($paymentStatusRow['payment_status'] ?? ''));

    if ($currentPaymentStatus === 'paid' && $paymentStatus !== 'paid' && !$allowPaidChange) {
        http_response_code(409);
        echo json_encode(['success' => false, 'error' => 'Paid payment status is locked. Click Change first.']);
        return;
    }

    $stmt = $conn->prepare("UPDATE service_reservations SET payment_status = ?, updated_at = NOW() WHERE id = ?");
    if (!$stmt) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to prepare service payment update query']);
        return;
    }

    $stmt->bind_param('si', $paymentStatus, $id);
    $ok = $stmt->execute();
    $affected = $stmt->affected_rows;
    $stmt->close();

    if (!$ok) {
        http_response_code(500);
        echo json_encode(['success' => false, 'error' => 'Failed to update service payment status']);
        return;
    }

    $emailSent = null;
    if ($affected > 0 && $paymentStatus === 'paid') {
        $hasTotalPriceColumn = false;
        $totalPriceCol = $conn->query("SHOW COLUMNS FROM service_reservations LIKE 'total_price'");
        if ($totalPriceCol instanceof mysqli_result) {
            $hasTotalPriceColumn = $totalPriceCol->num_rows > 0;
            $totalPriceCol->free();
        }

        $totalPriceSelect = $hasTotalPriceColumn ? "sr.total_price" : "0 AS total_price";
        $detailsSql = "SELECT sr.id, sr.service_start_date, sr.service_end_date, sr.payment_status, sr.updated_at, {$totalPriceSelect},
                              u.first_name, u.last_name, u.email,
                              f.name AS service_name
                       FROM service_reservations sr
                       LEFT JOIN users u ON sr.user_id = u.id
                       LEFT JOIN facilities f ON sr.facility_id = f.id
                       WHERE sr.id = ?
                       LIMIT 1";
        $detailsStmt = $conn->prepare($detailsSql);
        if ($detailsStmt) {
            $detailsStmt->bind_param('i', $id);
            $detailsStmt->execute();
            $detailsResult = $detailsStmt->get_result();
            $row = $detailsResult ? $detailsResult->fetch_assoc() : null;
            $detailsStmt->close();

            if ($row && !empty($row['email'])) {
                $customerName = trim((string)($row['first_name'] ?? '') . ' ' . (string)($row['last_name'] ?? ''));
                if ($customerName === '') {
                    $customerName = 'Guest';
                }

                $subject = 'Payment Received for Your Zemaraim Service Reservation';
                $body = buildServiceReservationPaymentEmailBody([
                    'guest_name' => $customerName,
                    'booking_id' => (string)$id,
                    'service_name' => (string)($row['service_name'] ?? 'Service'),
                    'start_date' => (string)($row['service_start_date'] ?? '-'),
                    'end_date' => (string)($row['service_end_date'] ?? '-'),
                    'payment_status' => ucfirst((string)($row['payment_status'] ?? 'paid')),
                    'amount' => number_format((float)($row['total_price'] ?? 0), 2),
                    'updated_at' => (string)($row['updated_at'] ?? date('Y-m-d H:i:s')),
                ]);
                $emailSent = sendEmail((string)$row['email'], $customerName, $subject, $body);
            }
        }
    }

    echo json_encode([
        'success' => true,
        'message' => $affected >= 0 ? 'Service payment status updated successfully' : 'No changes applied',
        'data' => [
            'id' => $id,
            'payment_status' => $paymentStatus
        ],
        'email_sent' => $emailSent
    ]);
}

function getStaff() {
    global $conn;
    
    $limit = intval($_GET['limit'] ?? 50);
    $offset = intval($_GET['offset'] ?? 0);
    
    $sql = "SELECT id, first_name, last_name, email, user_type, created_at, is_active 
            FROM users 
            WHERE user_type IN ('admin', 'staff')
            ORDER BY created_at DESC
            LIMIT $limit OFFSET $offset";
    
    $result = $conn->query($sql);
    $staff = $result ? $result->fetch_all(MYSQLI_ASSOC) : [];
    
    // Get count
    $countResult = $conn->query("SELECT COUNT(*) as count FROM users WHERE user_type IN ('admin', 'staff')");
    $totalCount = $countResult ? $countResult->fetch_assoc()['count'] : 0;
    
    echo json_encode([
        'success' => true,
        'data' => $staff,
        'total' => $totalCount,
        'limit' => $limit,
        'offset' => $offset
    ]);
}

function getRevenueInvoices() {
    global $conn;

    $limit = intval($_GET['limit'] ?? 120);
    if ($limit < 1) {
        $limit = 1;
    }
    if ($limit > 300) {
        $limit = 300;
    }

    $roomSql = "SELECT
                    CONCAT('INV-R-', r.id) AS invoice_no,
                    'Room Booking Invoice' AS invoice_type,
                    r.id AS source_id,
                    CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')) AS guest_name,
                    CONCAT(COALESCE(rm.room_number, 'N/A'), ' (', UPPER(COALESCE(rm.room_type, 'ROOM')), ')') AS item_name,
                    CONCAT(COALESCE(r.check_in_date, '-'), ' to ', COALESCE(r.check_out_date, '-')) AS booking_dates,
                    r.total_price AS amount,
                    COALESCE(r.payment_status, 'unpaid') AS payment_status,
                    COALESCE(r.status, 'pending') AS status,
                    r.created_at
                FROM reservations r
                LEFT JOIN users u ON u.id = r.user_id
                LEFT JOIN rooms rm ON rm.id = r.room_id
                ORDER BY r.created_at DESC
                LIMIT {$limit}";

    $serviceSql = "SELECT
                    CONCAT('INV-S-', sr.id) AS invoice_no,
                    'Service Booking Invoice' AS invoice_type,
                    sr.id AS source_id,
                    CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, '')) AS guest_name,
                    COALESCE(f.name, 'Service') AS item_name,
                    CONCAT(COALESCE(sr.service_start_date, '-'), ' to ', COALESCE(sr.service_end_date, '-')) AS booking_dates,
                    sr.total_price AS amount,
                    COALESCE(sr.payment_status, 'unpaid') AS payment_status,
                    COALESCE(sr.status, 'pending') AS status,
                    sr.created_at
                FROM service_reservations sr
                LEFT JOIN users u ON u.id = sr.user_id
                LEFT JOIN facilities f ON f.id = sr.facility_id
                ORDER BY sr.created_at DESC
                LIMIT {$limit}";

    $invoiceRows = [];

    $roomResult = $conn->query($roomSql);
    if ($roomResult) {
        $roomRows = $roomResult->fetch_all(MYSQLI_ASSOC);
        foreach ($roomRows as $row) {
            $row['invoice_category'] = 'room';
            $invoiceRows[] = $row;
        }
    }

    $serviceResult = $conn->query($serviceSql);
    if ($serviceResult) {
        $serviceRows = $serviceResult->fetch_all(MYSQLI_ASSOC);
        foreach ($serviceRows as $row) {
            $row['invoice_category'] = 'service';
            $invoiceRows[] = $row;
        }
    }

    usort($invoiceRows, static function ($a, $b) {
        $aTime = strtotime((string)($a['created_at'] ?? ''));
        $bTime = strtotime((string)($b['created_at'] ?? ''));
        return $bTime <=> $aTime;
    });

    $invoiceRows = array_slice($invoiceRows, 0, $limit);

    echo json_encode([
        'success' => true,
        'data' => $invoiceRows,
        'total' => count($invoiceRows),
        'limit' => $limit
    ]);
}

function getStats() {
    global $conn;
    
    $stats = [
        'total_guests' => 0,
        'total_reservations' => 0,
        'active_reservations' => 0,
        'total_revenue' => 0,
        'total_rooms' => 0,
        'available_rooms' => 0,
        'staff_count' => 0
    ];
    
    // Total guests
    $result = $conn->query("SELECT COUNT(*) as count FROM users WHERE user_type = 'guest' AND is_active = 1");
    if ($result) {
        $stats['total_guests'] = $result->fetch_assoc()['count'] ?? 0;
    }

    // Total reservations
    $result = $conn->query("SELECT COUNT(*) as count FROM reservations");
    if ($result) {
        $stats['total_reservations'] = $result->fetch_assoc()['count'] ?? 0;
    }

    // Active reservations
    $result = $conn->query("SELECT COUNT(*) as count FROM reservations WHERE status IN ('pending', 'confirmed', 'checked_in')");
    if ($result) {
        $stats['active_reservations'] = $result->fetch_assoc()['count'] ?? 0;
    }

    // Total revenue
    $result = $conn->query("SELECT SUM(total_price) as total FROM reservations WHERE status != 'cancelled'");
    if ($result) {
        $stats['total_revenue'] = $result->fetch_assoc()['total'] ?? 0;
    }

    // Total rooms
    $result = $conn->query("SELECT COUNT(*) as count FROM rooms");
    if ($result) {
        $stats['total_rooms'] = $result->fetch_assoc()['count'] ?? 0;
    }

    // Available rooms
    $result = $conn->query("SELECT COUNT(*) as count FROM rooms WHERE status = 'available'");
    if ($result) {
        $stats['available_rooms'] = $result->fetch_assoc()['count'] ?? 0;
    }

    // Staff count
    $result = $conn->query("SELECT COUNT(*) as count FROM users WHERE user_type IN ('admin', 'staff') AND is_active = 1");
    if ($result) {
        $stats['staff_count'] = $result->fetch_assoc()['count'] ?? 0;
    }

    echo json_encode([
        'success' => true,
        'data' => $stats
    ]);
}
?>
