<?php
require_auth();
require_permission('bookings.view');
$pdo = db();
$d = input();
$today = date('Y-m-d');
$pid = !empty($d['property_id']) ? (int)$d['property_id'] : null;

$propFilter = $pid ? ' AND r.property_id = ' . (int)$pid : '';
$bkFilter   = $pid ? ' AND b.property_id = ' . (int)$pid : '';

// KPIs
$totalRooms = (int)$pdo->query("SELECT COUNT(*) FROM rooms r WHERE r.deleted_at IS NULL $propFilter")->fetchColumn();
$occupiedRooms = (int)$pdo->query("SELECT COUNT(*) FROM rooms r WHERE r.deleted_at IS NULL AND r.occupancy_status='occupied' $propFilter")->fetchColumn();
$dirtyRooms = (int)$pdo->query("SELECT COUNT(*) FROM rooms r WHERE r.deleted_at IS NULL AND r.housekeeping_status='dirty' $propFilter")->fetchColumn();
$oooRooms = (int)$pdo->query("SELECT COUNT(*) FROM rooms r WHERE r.deleted_at IS NULL AND r.housekeeping_status IN ('maintenance','out_of_order') $propFilter")->fetchColumn();
$availableRooms = $totalRooms - $occupiedRooms - $oooRooms;
$occupancyRate = $totalRooms > 0 ? round(($occupiedRooms / $totalRooms) * 100, 1) : 0;

// Arrivals today
$arrSql = "SELECT b.id, b.reference, b.guest_name, b.check_in, b.check_out, b.guests_count,
                  b.total, b.payment_status, b.status,
                  r.room_number, rt.name AS room_type_name
           FROM bookings b
           LEFT JOIN rooms r ON r.id = b.room_id
           LEFT JOIN room_types rt ON rt.id = b.room_type_id
           WHERE b.check_in = ? AND b.status IN ('pending','confirmed') $bkFilter
           ORDER BY b.created_at";
$s = $pdo->prepare($arrSql); $s->execute([$today]);
$arrivals = $s->fetchAll();

// Departures today
$depSql = "SELECT b.id, b.reference, b.guest_name, b.check_out, b.total, b.amount_paid,
                  (b.total + COALESCE(b.extras_total,0) - b.amount_paid) AS balance,
                  r.room_number, rt.name AS room_type_name
           FROM bookings b
           LEFT JOIN rooms r ON r.id = b.room_id
           LEFT JOIN room_types rt ON rt.id = b.room_type_id
           WHERE b.check_out = ? AND b.status IN ('confirmed','checked_in') $bkFilter
           ORDER BY b.created_at";
$s = $pdo->prepare($depSql); $s->execute([$today]);
$departures = $s->fetchAll();

// Currently in-house
$inhouseSql = "SELECT b.id, b.reference, b.guest_name, b.check_in, b.check_out,
                      r.room_number, rt.name AS room_type_name
               FROM bookings b
               LEFT JOIN rooms r ON r.id = b.room_id
               LEFT JOIN room_types rt ON rt.id = b.room_type_id
               WHERE b.status = 'checked_in' AND b.check_out > ? $bkFilter
               ORDER BY b.check_out, r.room_number";
$s = $pdo->prepare($inhouseSql); $s->execute([$today]);
$inhouse = $s->fetchAll();

// Room grid (all rooms with current booking if any)
$gridSql = "SELECT r.id, r.room_number, r.floor, r.building,
                   r.housekeeping_status, r.occupancy_status,
                   rt.name AS room_type_name, rt.code AS room_type_code,
                   (SELECT b.guest_name FROM bookings b WHERE b.room_id = r.id
                       AND b.status='checked_in' LIMIT 1) AS current_guest,
                   (SELECT b.check_out FROM bookings b WHERE b.room_id = r.id
                       AND b.status='checked_in' LIMIT 1) AS current_checkout
            FROM rooms r
            JOIN room_types rt ON rt.id = r.room_type_id
            WHERE r.deleted_at IS NULL $propFilter
            ORDER BY r.floor, LENGTH(r.room_number), r.room_number";
$rooms = $pdo->query($gridSql)->fetchAll();

// Revenue today (room charges + folio)
$revToday = (float)$pdo->query("
    SELECT COALESCE(SUM(b.total),0)
    FROM bookings b
    WHERE b.check_in = '$today' AND b.status NOT IN ('cancelled','no_show') $bkFilter
")->fetchColumn();

// RevPAR / ADR for the month
$monthStart = date('Y-m-01');
$mtdRevenue = (float)$pdo->query("
    SELECT COALESCE(SUM(b.total),0)
    FROM bookings b
    WHERE b.status NOT IN ('cancelled','no_show')
      AND b.check_in >= '$monthStart' $bkFilter
")->fetchColumn();
$mtdRoomNights = (int)$pdo->query("
    SELECT COALESCE(SUM(b.nights),0)
    FROM bookings b
    WHERE b.status NOT IN ('cancelled','no_show')
      AND b.check_in >= '$monthStart' $bkFilter
")->fetchColumn();
$daysInMonth = (int)date('j');
$availableRoomNights = $totalRooms * $daysInMonth;
$adr = $mtdRoomNights > 0 ? round($mtdRevenue / $mtdRoomNights, 2) : 0;
$revpar = $availableRoomNights > 0 ? round($mtdRevenue / $availableRoomNights, 2) : 0;

json_ok([
    'kpi' => [
        'total_rooms'      => $totalRooms,
        'occupied_rooms'   => $occupiedRooms,
        'available_rooms'  => max(0, $availableRooms),
        'dirty_rooms'      => $dirtyRooms,
        'ooo_rooms'        => $oooRooms,
        'occupancy_rate'   => $occupancyRate,
        'arrivals_today'   => count($arrivals),
        'departures_today' => count($departures),
        'in_house'         => count($inhouse),
        'revenue_today'    => $revToday,
        'mtd_revenue'      => $mtdRevenue,
        'adr'              => $adr,
        'revpar'           => $revpar,
    ],
    'arrivals'   => $arrivals,
    'departures' => $departures,
    'inhouse'    => $inhouse,
    'rooms'      => $rooms,
]);
