<?php
/**
 * Room Availability Helper Functions
 * This file provides functions to replace the vw_room_availability view
 * for cloud hosting compatibility (without using database views)
 */

/**
 * Get room availability query without using views
 * This replaces the vw_room_availability view functionality
 * 
 * @param mysqli $con Database connection
 * @param int $branchId Optional branch filter
 * @param string $roomStatus Optional status filter ('Available', 'Occupied', 'Maintenance')
 * @return string SQL query for room availability
 */
function getRoomAvailabilityQuery($branchId = null, $roomStatus = null) {
    $query = "
    SELECT 
        r.ID AS RoomID,
        r.BranchID AS BranchID,
        r.RoomName AS RoomName,
        r.RoomType AS RoomType,
        r.Status AS RoomStatus,
        r.Capacity,
        r.WeekdayFare,
        r.WeekendFare,
        r.Amenities,
        r.Description,
        b.BranchName,
        b.BranchCode,
        CASE 
            WHEN EXISTS(
                SELECT 1 FROM tblbookings bk 
                WHERE bk.RoomID = r.ID 
                    AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
                    AND CURDATE() >= bk.CheckInDate 
                    AND CURDATE() < bk.CheckOutDate 
                    AND bk.IsActive = 1
            ) THEN 'Occupied'
            WHEN r.Status = 'Available' THEN 'Available'
            ELSE r.Status
        END AS CurrentStatus,
        (SELECT bk.ID FROM tblbookings bk 
         WHERE bk.RoomID = r.ID 
             AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
             AND CURDATE() >= bk.CheckInDate 
             AND CURDATE() < bk.CheckOutDate 
             AND bk.IsActive = 1 
         LIMIT 1) AS CurrentBookingID,
        (SELECT bk.BookingNumber FROM tblbookings bk 
         WHERE bk.RoomID = r.ID 
             AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
             AND CURDATE() >= bk.CheckInDate 
             AND CURDATE() < bk.CheckOutDate 
             AND bk.IsActive = 1 
         LIMIT 1) AS CurrentBookingNumber,
        (SELECT bk.CustomerName FROM tblbookings bk 
         WHERE bk.RoomID = r.ID 
             AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
             AND CURDATE() >= bk.CheckInDate 
             AND CURDATE() < bk.CheckOutDate 
             AND bk.IsActive = 1 
         LIMIT 1) AS CurrentCustomerName,
        (SELECT bk.CheckOutDate FROM tblbookings bk 
         WHERE bk.RoomID = r.ID 
             AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
             AND CURDATE() >= bk.CheckInDate 
             AND CURDATE() < bk.CheckOutDate 
             AND bk.IsActive = 1 
         LIMIT 1) AS CurrentCheckOutDate
    FROM tblrooms r 
    LEFT JOIN tblbranches b ON r.BranchID = b.ID
    WHERE r.IsActive = 1 
        AND b.IsActive = 1";
    
    if ($branchId !== null) {
        $query .= " AND r.BranchID = " . intval($branchId);
    }
    
    if ($roomStatus !== null) {
        if ($roomStatus === 'Available') {
            $query .= " HAVING CurrentStatus = 'Available'";
        } elseif ($roomStatus === 'Occupied') {
            $query .= " HAVING CurrentStatus = 'Occupied'";
        } else {
            $query .= " AND r.Status = '" . mysqli_real_escape_string($GLOBALS['con'], $roomStatus) . "'";
        }
    }
    
    $query .= " ORDER BY b.BranchName, r.RoomType, r.RoomName";
    
    return $query;
}

/**
 * Check if a room is available for specific dates
 * 
 * @param mysqli $con Database connection
 * @param int $roomId Room ID to check
 * @param string $checkInDate Check-in date (YYYY-MM-DD)
 * @param string $checkOutDate Check-out date (YYYY-MM-DD)
 * @param int $excludeBookingId Optional booking ID to exclude from conflict check
 * @return bool True if room is available, false otherwise
 */
function isRoomAvailable($con, $roomId, $checkInDate, $checkOutDate, $excludeBookingId = null) {
    $roomId = intval($roomId);
    $checkInDate = mysqli_real_escape_string($con, $checkInDate);
    $checkOutDate = mysqli_real_escape_string($con, $checkOutDate);
    
    $query = "
    SELECT COUNT(*) as conflict_count 
    FROM tblbookings b 
    WHERE b.RoomID = $roomId 
        AND b.BookingStatus IN ('Confirmed', 'CheckedIn') 
        AND b.IsActive = 1
        AND (
            (b.CheckInDate <= '$checkInDate' AND b.CheckOutDate > '$checkInDate')
            OR (b.CheckInDate < '$checkOutDate' AND b.CheckOutDate >= '$checkOutDate')
            OR (b.CheckInDate >= '$checkInDate' AND b.CheckOutDate <= '$checkOutDate')
        )";
    
    if ($excludeBookingId !== null) {
        $query .= " AND b.ID != " . intval($excludeBookingId);
    }
    
    $result = mysqli_query($con, $query);
    $row = mysqli_fetch_assoc($result);
    
    return $row['conflict_count'] == 0;
}

/**
 * Get available rooms for specific dates and branch
 * 
 * @param mysqli $con Database connection
 * @param string $checkInDate Check-in date (YYYY-MM-DD)
 * @param string $checkOutDate Check-out date (YYYY-MM-DD)
 * @param int $branchId Optional branch ID filter
 * @param string $roomType Optional room type filter
 * @return mysqli_result Query result with available rooms
 */
function getAvailableRooms($con, $checkInDate, $checkOutDate, $branchId = null, $roomType = null) {
    $checkInDate = mysqli_real_escape_string($con, $checkInDate);
    $checkOutDate = mysqli_real_escape_string($con, $checkOutDate);
    
    $query = "
    SELECT 
        r.ID as RoomID,
        r.BranchID,
        r.RoomName,
        r.RoomType,
        r.Capacity,
        r.WeekdayFare,
        r.WeekendFare,
        r.Amenities,
        r.Description,
        r.Status as RoomStatus,
        b.BranchName,
        b.BranchCode,
        b.Location as BranchLocation
    FROM tblrooms r
    LEFT JOIN tblbranches b ON r.BranchID = b.ID
    WHERE r.IsActive = 1 
        AND b.IsActive = 1
        AND r.Status = 'Available'
        AND r.ID NOT IN (
            SELECT bk.RoomID 
            FROM tblbookings bk 
            WHERE bk.BookingStatus IN ('Confirmed', 'CheckedIn') 
                AND bk.IsActive = 1
                AND (
                    (bk.CheckInDate <= '$checkInDate' AND bk.CheckOutDate > '$checkInDate')
                    OR (bk.CheckInDate < '$checkOutDate' AND bk.CheckOutDate >= '$checkOutDate')
                    OR (bk.CheckInDate >= '$checkInDate' AND bk.CheckOutDate <= '$checkOutDate')
                )
        )";
    
    if ($branchId !== null) {
        $query .= " AND r.BranchID = " . intval($branchId);
    }
    
    if ($roomType !== null) {
        $query .= " AND r.RoomType = '" . mysqli_real_escape_string($con, $roomType) . "'";
    }
    
    $query .= " ORDER BY b.BranchName, r.RoomType, r.WeekdayFare, r.RoomName";
    
    return mysqli_query($con, $query);
}

/**
 * Get room occupancy status with current booking details
 * 
 * @param mysqli $con Database connection
 * @param int $roomId Room ID to check
 * @return array Room status information including current booking details
 */
function getRoomOccupancyStatus($con, $roomId) {
    $roomId = intval($roomId);
    
    $query = "
    SELECT 
        r.ID as RoomID,
        r.RoomName,
        r.RoomType,
        r.Status as RoomStatus,
        r.BranchID,
        b.BranchName,
        CASE 
            WHEN EXISTS(
                SELECT 1 FROM tblbookings bk 
                WHERE bk.RoomID = r.ID 
                    AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
                    AND CURDATE() >= bk.CheckInDate 
                    AND CURDATE() < bk.CheckOutDate 
                    AND bk.IsActive = 1
            ) THEN 'Occupied'
            WHEN r.Status = 'Available' THEN 'Available'
            ELSE r.Status
        END AS CurrentStatus,
        bk.ID as CurrentBookingID,
        bk.BookingNumber as CurrentBookingNumber,
        bk.CustomerName as CurrentCustomerName,
        bk.CustomerPhone as CurrentCustomerPhone,
        bk.CheckInDate as CurrentCheckInDate,
        bk.CheckOutDate as CurrentCheckOutDate,
        bk.BookingStatus as CurrentBookingStatus
    FROM tblrooms r
    LEFT JOIN tblbranches b ON r.BranchID = b.ID
    LEFT JOIN tblbookings bk ON (
        bk.RoomID = r.ID 
        AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
        AND CURDATE() >= bk.CheckInDate 
        AND CURDATE() < bk.CheckOutDate 
        AND bk.IsActive = 1
    )
    WHERE r.ID = $roomId AND r.IsActive = 1";
    
    $result = mysqli_query($con, $query);
    return mysqli_fetch_assoc($result);
}

/**
 * Get room statistics for dashboard
 * 
 * @param mysqli $con Database connection
 * @param int $branchId Optional branch ID filter
 * @return array Array with room statistics
 */
function getRoomStatistics($con, $branchId = null) {
    $branchFilter = '';
    if ($branchId !== null) {
        $branchFilter = " AND r.BranchID = " . intval($branchId);
    }
    
    $query = "
    SELECT 
        COUNT(*) as total_rooms,
        SUM(CASE WHEN r.Status = 'Available' 
            AND NOT EXISTS(
                SELECT 1 FROM tblbookings bk 
                WHERE bk.RoomID = r.ID 
                    AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
                    AND CURDATE() >= bk.CheckInDate 
                    AND CURDATE() < bk.CheckOutDate 
                    AND bk.IsActive = 1
            ) THEN 1 ELSE 0 END) as available_rooms,
        SUM(CASE WHEN EXISTS(
                SELECT 1 FROM tblbookings bk 
                WHERE bk.RoomID = r.ID 
                    AND bk.BookingStatus IN ('Confirmed','CheckedIn') 
                    AND CURDATE() >= bk.CheckInDate 
                    AND CURDATE() < bk.CheckOutDate 
                    AND bk.IsActive = 1
            ) THEN 1 ELSE 0 END) as occupied_rooms,
        SUM(CASE WHEN r.Status = 'Maintenance' THEN 1 ELSE 0 END) as maintenance_rooms
    FROM tblrooms r 
    WHERE r.IsActive = 1 $branchFilter";
    
    $result = mysqli_query($con, $query);
    return mysqli_fetch_assoc($result);
}

/**
 * Helper function to calculate room fare based on date
 * 
 * @param array $room Room data with WeekdayFare and WeekendFare
 * @param string $date Date to check (YYYY-MM-DD)
 * @return float Room fare for the given date
 */
function getRoomFareForDate($room, $date) {
    $dayOfWeek = date('N', strtotime($date)); // 1 = Monday, 7 = Sunday
    
    // Weekend is Friday (5), Saturday (6), Sunday (7)
    if ($dayOfWeek >= 5) {
        return floatval($room['WeekendFare']);
    } else {
        return floatval($room['WeekdayFare']);
    }
}

/**
 * Calculate total booking amount for date range
 * 
 * @param array $room Room data with fare information
 * @param string $checkInDate Check-in date (YYYY-MM-DD)
 * @param string $checkOutDate Check-out date (YYYY-MM-DD)
 * @return float Total booking amount
 */
function calculateBookingAmount($room, $checkInDate, $checkOutDate) {
    $total = 0;
    $currentDate = new DateTime($checkInDate);
    $endDate = new DateTime($checkOutDate);
    
    while ($currentDate < $endDate) {
        $fare = getRoomFareForDate($room, $currentDate->format('Y-m-d'));
        $total += $fare;
        $currentDate->add(new DateInterval('P1D'));
    }
    
    return $total;
}

?>