<?php
require_once __DIR__ . '/../../includes/bootstrap.php';
require_login();

$pageTitle = 'Room Rack';

if (!function_exists('h')) {
    function h($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('money_fmt')) {
    function money_fmt($amount): string
    {
        return number_format((float)$amount, 2);
    }
}

if (!function_exists('roomrack_column_exists')) {
    function roomrack_column_exists(PDO $pdo, string $table, string $column): bool
    {
        static $cache = [];
        $key = $table . '.' . $column;

        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }

        try {
            $stmt = $pdo->prepare("
                SELECT COUNT(*)
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = :table_name
                  AND COLUMN_NAME = :column_name
            ");
            $stmt->execute([
                ':table_name'  => $table,
                ':column_name' => $column,
            ]);
            $cache[$key] = ((int)$stmt->fetchColumn() > 0);
        } catch (Throwable $e) {
            $cache[$key] = false;
        }

        return $cache[$key];
    }
}


$viewDate = trim((string)($_GET['view_date'] ?? date('Y-m-d')));
$propertyId = (int)($_GET['property_id'] ?? 0);
$search = trim((string)($_GET['q'] ?? ''));

$errors = [];
$properties = [];
$rooms = [];
$rackRows = [];

$hasRoomNameColumn = roomrack_column_exists($pdo, 'rooms', 'room_name');
$hasHousekeepingStatusColumn = roomrack_column_exists($pdo, 'rooms', 'housekeeping_status');
$hasStatusColumn = roomrack_column_exists($pdo, 'rooms', 'status');
$hasRoomingCheckedInColumn = roomrack_column_exists($pdo, 'order_rooming', 'checked_in_status');
$hasRoomingCheckedOutColumn = roomrack_column_exists($pdo, 'order_rooming', 'checked_out_status');

/*
  Auto housekeeping sync after checkout:
  Once a rooming line is checked out, the physical room must be released from the rack
  and marked Dirty for housekeeping. Prefer rooms.housekeeping_status where available.
*/
try {
    if ($hasRoomingCheckedOutColumn && ($hasHousekeepingStatusColumn || $hasStatusColumn)) {
        $setParts = [];

        if ($hasHousekeepingStatusColumn) {
            $setParts[] = "r.housekeeping_status = 'dirty'";
        } elseif ($hasStatusColumn) {
            $setParts[] = "r.status = 'dirty'";
        }

        if ($setParts) {
            $sqlDirty = "
                UPDATE rooms r
                INNER JOIN order_rooming rmg ON rmg.room_id = r.id
                INNER JOIN orders o ON o.id = rmg.order_id
                SET " . implode(', ', $setParts) . "
                WHERE rmg.room_id > 0
                  AND COALESCE(rmg.checked_out_status, 0) = 1
                  AND DATE(COALESCE(rmg.check_out, o.check_out)) <= :view_date
            ";
            $stmtDirty = $pdo->prepare($sqlDirty);
            $stmtDirty->execute([':view_date' => $viewDate]);
        }
    }
} catch (Throwable $e) {
    $errors[] = 'Housekeeping sync: ' . $e->getMessage();
}

try {
    $stmt = $pdo->query("
        SELECT id, name, code
        FROM properties
        WHERE is_active = 1
        ORDER BY name ASC
    ");
    $properties = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $properties = [];
}

/*
  Load rooms.
  Expected fields commonly used:
  - id
  - property_id
  - room_no
  - room_name (optional, only used if column exists)
  - room_type_id (optional)
  - status / housekeeping_status (optional)
*/
try {
    $sqlRooms = "
        SELECT
            r.*,
            p.name AS property_name,
            p.code AS property_code,
            rt.title AS room_type
        FROM rooms r
        LEFT JOIN properties p ON p.id = r.property_id
        LEFT JOIN room_types rt ON rt.id = r.room_type_id
        WHERE 1=1
    ";

    $params = [];

    if ($propertyId > 0) {
        $sqlRooms .= " AND r.property_id = :property_id ";
        $params[':property_id'] = $propertyId;
    }

    if ($search !== '') {
        $searchValue = '%' . $search . '%';

        $searchParts = [
            "r.room_no LIKE :search_room_no",
            "COALESCE(rt.title,'') LIKE :search_room_type",
            "COALESCE(p.name,'') LIKE :search_property_name",
            "COALESCE(p.code,'') LIKE :search_property_code"
        ];

        $params[':search_room_no']        = $searchValue;
        $params[':search_room_type']      = $searchValue;
        $params[':search_property_name']  = $searchValue;
        $params[':search_property_code']  = $searchValue;

        if ($hasRoomNameColumn) {
            $searchParts[] = "COALESCE(r.room_name,'') LIKE :search_room_name";
            $params[':search_room_name'] = $searchValue;
        }

        $sqlRooms .= " AND (" . implode(" OR ", $searchParts) . ") ";
    }

    $sqlRooms .= " ORDER BY p.name ASC, r.room_no ASC, r.id ASC ";

    $stmt = $pdo->prepare($sqlRooms);
    $stmt->execute($params);
    $rooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (Throwable $e) {
    $errors[] = $e->getMessage();
    $rooms = [];
}

/*
  Load active room assignments around selected date.
  A room is relevant if:
    check_in <= viewDate <= check_out
*/
$bookingMap = [];
if ($rooms) {
    try {
        $roomIds = array_map(function ($r) {
            return (int)$r['id'];
        }, $rooms);

        $placeholders = implode(',', array_fill(0, count($roomIds), '?'));

        $sqlAssignments = "
            SELECT
                rmg.*,
                o.order_no,
                o.check_in,
                o.check_out,
                o.adults AS booking_adults,
                o.kids AS booking_kids,
                o.status AS booking_status,
                g.firstname,
                g.lastname,
                CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
                (
                    SELECT COALESCE(SUM(f.balance),0)
                    FROM folios f
                    WHERE f.order_id = o.id
                ) AS booking_balance,
                (
                    SELECT COUNT(*)
                    FROM order_rooming r2
                    WHERE r2.order_id = o.id
                      AND COALESCE(r2.checked_in_status,0) = 1
                ) AS booking_checked_in_rooms,
                (
                    SELECT COUNT(*)
                    FROM order_rooming r2
                    WHERE r2.order_id = o.id
                      AND COALESCE(r2.checked_out_status,0) = 1
                ) AS booking_checked_out_rooms,
                (
                    SELECT COUNT(*)
                    FROM order_rooming r2
                    WHERE r2.order_id = o.id
                ) AS booking_total_rooms
            FROM order_rooming rmg
            INNER JOIN orders o ON o.id = rmg.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            WHERE rmg.room_id IN ($placeholders)
              AND DATE(o.check_in) <= ?
              AND DATE(o.check_out) >= ?
              AND COALESCE(o.status, 1) <> 0
              AND COALESCE(rmg.checked_out_status, 0) <> 1
            ORDER BY o.check_in ASC, o.id DESC
        ";

        $stmt = $pdo->prepare($sqlAssignments);
        $bind = $roomIds;
        $bind[] = $viewDate;
        $bind[] = $viewDate;
        $stmt->execute($bind);
        $assignmentRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($assignmentRows as $ar) {
            $rid = (int)($ar['room_id'] ?? 0);
            if (!isset($bookingMap[$rid])) {
                $bookingMap[$rid] = [];
            }
            $bookingMap[$rid][] = $ar;
        }
    } catch (Throwable $e) {
        $errors[] = $e->getMessage();
    }
}

/*
  Build rack rows: one row per room
*/
$totalVacant = 0;
$totalOccupied = 0;
$totalArrivals = 0;
$totalDepartures = 0;
$totalOOO = 0;

foreach ($rooms as $room) {
    $roomId = (int)$room['id'];
    $assignments = $bookingMap[$roomId] ?? [];
    $current = !empty($assignments) ? $assignments[0] : null;

    $roomNo = trim((string)($room['room_no'] ?? ''));
    $propertyLabel = trim((string)($room['property_name'] ?? ''));
    if (!empty($room['property_code'])) {
        $propertyLabel .= ($propertyLabel !== '' ? ' (' . $room['property_code'] . ')' : $room['property_code']);
    }

    $rawRoomStatus = strtolower(trim((string)($room['status'] ?? $room['housekeeping_status'] ?? '')));
    $displayStatus = 'vacant';
    $statusClass = 'success';

    if (in_array($rawRoomStatus, ['out_of_order', 'ooo', 'maintenance', 'blocked'], true)) {
        $displayStatus = 'out_of_order';
        $statusClass = 'dark';
        $totalOOO++;
    } elseif ($current) {
        $checkInDate = (string)($current['check_in'] ?? '');
        $checkOutDate = (string)($current['check_out'] ?? '');

        if ($checkInDate === $viewDate) {
            $displayStatus = 'arrival';
            $statusClass = 'primary';
            $totalArrivals++;
        } elseif ($checkOutDate === $viewDate) {
            $displayStatus = 'departure';
            $statusClass = 'warning';
            $totalDepartures++;
        } else {
            $displayStatus = 'occupied';
            $statusClass = 'danger';
            $totalOccupied++;
        }
    } else {
        $displayStatus = 'vacant';
        $statusClass = 'success';
        $totalVacant++;
    }

    $rackRows[] = [
        'room'          => $room,
        'property'      => $propertyLabel,
        'assignment'    => $current,
        'display_status'=> $displayStatus,
        'status_class'  => $statusClass,
    ];
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band roomrack-hero-compact">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-grid-3x3-gap"></i> Front Desk</div>
            <div class="hero-title mt-2">Room Rack</div>
            <div class="hero-subtitle mt-2">
                Visual room occupancy board for arrivals, in-house guests, departures, vacant rooms, and operational actions.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="/rooms/modules/payments/frontdesk_dashboard.php" class="btn btn-outline-light">
                <i class="bi bi-arrow-left me-2"></i>Front Desk
            </a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger">
        <div class="fw-bold mb-2">Please fix the following:</div>
        <ul class="mb-0">
            <?php foreach ($errors as $err): ?>
                <li><?php echo h($err); ?></li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

<div class="row g-2 mb-2 roomrack-metrics-compact">
    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success">
                <i class="bi bi-door-open"></i>
            </div>
            <div>
                <div class="metric-label">Vacant</div>
                <div class="metric-value"><?php echo number_format($totalVacant); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger">
                <i class="bi bi-house-door"></i>
            </div>
            <div>
                <div class="metric-label">Occupied</div>
                <div class="metric-value"><?php echo number_format($totalOccupied); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-primary-subtle text-primary">
                <i class="bi bi-box-arrow-in-right"></i>
            </div>
            <div>
                <div class="metric-label">Arrivals</div>
                <div class="metric-value"><?php echo number_format($totalArrivals); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning">
                <i class="bi bi-box-arrow-right"></i>
            </div>
            <div>
                <div class="metric-label">Departures</div>
                <div class="metric-value"><?php echo number_format($totalDepartures); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-dark-subtle text-dark">
                <i class="bi bi-tools"></i>
            </div>
            <div>
                <div class="metric-label">Out of Order</div>
                <div class="metric-value"><?php echo number_format($totalOOO); ?></div>
            </div>
        </div>
    </div>

    <div class="col-sm-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary">
                <i class="bi bi-grid"></i>
            </div>
            <div>
                <div class="metric-label">Rooms</div>
                <div class="metric-value"><?php echo number_format(count($rackRows)); ?></div>
            </div>
        </div>
    </div>
</div>

<div class="card panel-card mb-2 roomrack-filter-compact">
    <div class="card-header py-2">
        <h6 class="mb-0">Search & Filters</h6>
    </div>
    <div class="card-body py-2">
        <form method="get" class="row g-2 align-items-end">
            <div class="col-md-3">
                <label class="form-label">View Date</label>
                <input type="date" name="view_date" class="form-control" value="<?php echo h($viewDate); ?>">
            </div>

            <div class="col-md-3">
                <label class="form-label">Property</label>
                <select name="property_id" class="form-select">
                    <option value="0">All properties</option>
                    <?php foreach ($properties as $property): ?>
                        <option value="<?php echo (int)$property['id']; ?>" <?php echo $propertyId === (int)$property['id'] ? 'selected' : ''; ?>>
                            <?php echo h($property['name']); ?><?php echo !empty($property['code']) ? ' - ' . h($property['code']) : ''; ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-5">
                <label class="form-label">Search</label>
                <input type="text" name="q" class="form-control" value="<?php echo h($search); ?>" placeholder="Room no, room type, property...">
            </div>

            <div class="col-md-1 d-grid">
                <button class="btn btn-primary">Go</button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4">
    <?php if (!$rackRows): ?>
        <div class="col-12">
            <div class="card panel-card">
                <div class="card-body text-center text-muted py-5">
                    No rooms found.
                </div>
            </div>
        </div>
    <?php else: ?>
        <?php foreach ($rackRows as $row): ?>
            <?php
            $room = $row['room'];
            $assignment = $row['assignment'];
            $status = $row['display_status'];
            $statusClass = $row['status_class'];

            $roomNo = trim((string)($room['room_no'] ?? ''));
            $roomType = trim((string)($room['room_type'] ?? ''));
            $roomName = $hasRoomNameColumn ? trim((string)($room['room_name'] ?? '')) : '';
            $bookingId = (int)($assignment['order_id'] ?? 0);
            $bookingNo = trim((string)($assignment['order_no'] ?? ''));
            $guestName = trim((string)($assignment['firstname'] ?? '') . ' ' . (string)($assignment['lastname'] ?? ''));
            if ($guestName === '') {
                $guestName = '—';
            }
            $agentName = trim((string)($assignment['agent_name'] ?? ''));
            if ($agentName === '') {
                $agentName = 'Direct';
            }

            $statusText = 'Vacant';
            if ($status === 'arrival') $statusText = 'Arrival';
            elseif ($status === 'departure') $statusText = 'Departure';
            elseif ($status === 'occupied') $statusText = 'Occupied';
            elseif ($status === 'out_of_order') $statusText = 'Out of Order';

            $balance = (float)($assignment['booking_balance'] ?? 0);

            $roomCheckedIn = (int)($assignment['checked_in_status'] ?? 0);
            $roomCheckedOut = (int)($assignment['checked_out_status'] ?? 0);
            $bookingCheckedInRooms = (int)($assignment['booking_checked_in_rooms'] ?? 0);
            $bookingCheckedOutRooms = (int)($assignment['booking_checked_out_rooms'] ?? 0);
            $bookingTotalRooms = (int)($assignment['booking_total_rooms'] ?? 0);

            $showCheckIn = ($assignment && $roomCheckedOut !== 1 && $roomCheckedIn !== 1);
            $showCheckOut = ($assignment && $roomCheckedOut !== 1 && $roomCheckedIn === 1);
            ?>
            <div class="col-md-6 col-xl-4 col-xxl-3">
                <div class="card rack-card h-100 border-0 shadow-sm">
                    <div class="card-body">
                        <div class="d-flex justify-content-between align-items-start mb-3">
                            <div>
                                <div class="rack-room"><?php echo h($roomNo !== '' ? $roomNo : 'Room'); ?></div>
                                <?php if ($roomName !== ''): ?>
                                    <div class="small text-muted"><?php echo h($roomName); ?></div>
                                <?php endif; ?>
                                <?php if ($roomType !== ''): ?>
                                    <div class="small text-muted"><?php echo h($roomType); ?></div>
                                <?php endif; ?>
                            </div>
                            <span class="badge text-bg-<?php echo h($statusClass); ?>"><?php echo h($statusText); ?></span>
                        </div>

                        <div class="small text-muted mb-2"><?php echo h($row['property']); ?></div>

                        <?php if ($assignment): ?>
                            <div class="rack-line"><span>Booking</span><strong><?php echo h($bookingNo !== '' ? $bookingNo : ('#' . $bookingId)); ?></strong></div>
                            <div class="rack-line"><span>Guest</span><strong><?php echo h($guestName); ?></strong></div>
                            <div class="rack-line"><span>Agent</span><strong><?php echo h($agentName); ?></strong></div>
                            <div class="rack-line"><span>Stay</span><strong><?php echo h($assignment['check_in']); ?> to <?php echo h($assignment['check_out']); ?></strong></div>
                            <div class="rack-line"><span>Folio Balance</span><strong><?php echo money_fmt($balance); ?></strong></div>

                            <div class="d-grid gap-2 mt-3">
                                <?php if ($showCheckIn): ?>
                                    <a href="/rooms/modules/payments/checkin.php?order_id=<?php echo $bookingId; ?>" class="btn btn-sm btn-success">
                                        <i class="bi bi-door-open me-1"></i>Check In
                                    </a>
                                <?php endif; ?>

                                <?php if ($showCheckOut): ?>
                                    <a href="/rooms/modules/payments/check_out.php?order_id=<?php echo $bookingId; ?>" class="btn btn-sm btn-danger">
                                        <i class="bi bi-door-closed me-1"></i>Check Out
                                    </a>
                                <?php endif; ?>

                                <?php if (!$showCheckIn && !$showCheckOut): ?>
                                    <span class="badge text-bg-secondary py-2">Operational action completed</span>
                                <?php endif; ?>

                                <div class="small text-muted text-center">
                                    In <?php echo $bookingCheckedInRooms; ?>/<?php echo $bookingTotalRooms; ?> •
                                    Out <?php echo $bookingCheckedOutRooms; ?>/<?php echo $bookingTotalRooms; ?>
                                </div>

                                <div class="d-flex gap-2">
                                    <a href="/rooms/modules/payments/payments.php?order_id=<?php echo $bookingId; ?>" class="btn btn-sm btn-outline-primary flex-fill">
                                        Payments
                                    </a>
                                    <a href="/rooms/modules/payments/folio.php?q=<?php echo urlencode((string)($bookingNo !== '' ? $bookingNo : $bookingId)); ?>" class="btn btn-sm btn-outline-secondary flex-fill">
                                        Folios
                                    </a>
                                </div>
                                <a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo $bookingId; ?>" class="btn btn-sm btn-light">
                                    <i class="bi bi-eye me-1"></i>View Booking
                                </a>
                            </div>
                        <?php else: ?>
                            <div class="rack-line"><span>Occupancy</span><strong>Vacant</strong></div>
                            <div class="rack-line"><span>Date</span><strong><?php echo h($viewDate); ?></strong></div>

                            <div class="d-grid gap-2 mt-3">
                                <button type="button" class="btn btn-sm btn-outline-secondary" disabled>No Active Booking</button>
                            </div>
                        <?php endif; ?>
                    </div>
                </div>
            </div>
        <?php endforeach; ?>
    <?php endif; ?>
</div>

<style>
.roomrack-hero-compact{
    padding:14px 18px!important;
    margin-bottom:10px!important;
    border-radius:18px!important;
}
.roomrack-hero-compact .hero-title{
    font-size:1.25rem!important;
    line-height:1.15!important;
}
.roomrack-hero-compact .hero-subtitle{
    font-size:.86rem!important;
    margin-top:4px!important;
}
.roomrack-hero-compact .page-eyebrow{
    font-size:.72rem!important;
}
.roomrack-hero-compact .btn{
    padding:.35rem .65rem!important;
    font-size:.82rem!important;
}
.roomrack-metrics-compact .metric-card{
    padding:9px 11px!important;
    border-radius:14px!important;
    min-height:auto!important;
}
.roomrack-metrics-compact .metric-icon{
    width:34px!important;
    height:34px!important;
    border-radius:10px!important;
    font-size:.9rem!important;
}
.roomrack-metrics-compact .metric-label{
    font-size:.72rem!important;
    margin-bottom:0!important;
}
.roomrack-metrics-compact .metric-value{
    font-size:1rem!important;
    line-height:1.05!important;
}
.roomrack-filter-compact .card-header{
    padding:.45rem .8rem!important;
}
.roomrack-filter-compact .card-body{
    padding:.6rem .8rem!important;
}
.roomrack-filter-compact .form-label{
    font-size:.76rem!important;
    margin-bottom:.15rem!important;
    font-weight:700!important;
}
.roomrack-filter-compact .form-control,
.roomrack-filter-compact .form-select{
    min-height:34px!important;
    padding:.35rem .6rem!important;
    font-size:.85rem!important;
}
.roomrack-filter-compact .btn{
    min-height:34px!important;
    padding:.35rem .65rem!important;
}
.rack-card{
    border-radius: 18px;
}
.rack-room{
    font-size: 1.35rem;
    font-weight: 800;
    line-height: 1.1;
    color: #1f2937;
}
.rack-line{
    display:flex;
    justify-content:space-between;
    gap:14px;
    padding:7px 0;
    border-bottom:1px dashed #eef2f7;
}
.rack-line:last-of-type{
    border-bottom:none;
}
.rack-line span{
    color:#6b7280;
}
</style>

<?php require_once __DIR__ . '/../../includes/footer.php'; ?>