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

$pageTitle = 'Management Dashboard';

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('pct_fmt')) {
    function pct_fmt($value): string
    {
        return number_format((float)$value, 1) . '%';
    }
}

if (!function_exists('column_exists')) {
    function column_exists(PDO $pdo, string $table, string $column): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
              AND COLUMN_NAME = ?
        ");
        $stmt->execute([$table, $column]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('room_status_expression')) {
    function room_status_expression(PDO $pdo): string
    {
        $hasHousekeeping = column_exists($pdo, 'rooms', 'housekeeping_status');
        $hasStatus = column_exists($pdo, 'rooms', 'status');

        if ($hasHousekeeping && $hasStatus) {
            return "COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '')";
        }
        if ($hasHousekeeping) {
            return "COALESCE(LOWER(r.housekeeping_status), '')";
        }
        if ($hasStatus) {
            return "COALESCE(LOWER(r.status), '')";
        }

        return "''";
    }
}

if (!function_exists('property_filter_sql_raw')) {
    function property_filter_sql_raw(string $alias, int $propertyId): string
    {
        if ($propertyId <= 0) {
            return '';
        }
        return " AND {$alias}.property_id = " . (int)$propertyId . " ";
    }
}

$today = date('Y-m-d');
$propertyId = (int)($_GET['property_id'] ?? 0);
$dateFrom = trim((string)($_GET['date_from'] ?? $today));
$dateTo = trim((string)($_GET['date_to'] ?? date('Y-m-d', strtotime($today . ' +13 days'))));

$errors = [];
$properties = [];

$summary = [
    'room_stock' => 0,
    'occupied_rooms_today' => 0,
    'vacant_rooms_today' => 0,
    'arrivals_today' => 0,
    'departures_today' => 0,
    'in_house_today' => 0,
    'dirty_rooms' => 0,
    'ooo_rooms' => 0,
    'open_balance_bookings' => 0,
    'today_revenue' => 0,
    'today_room_nights' => 0,
    'today_adr' => 0,
    'today_occ_pct' => 0,
    'forecast_revenue' => 0,
    'forecast_room_nights' => 0,
    'forecast_avg_adr' => 0,
    'forecast_avg_occ' => 0,
];

$arrivalsToday = [];
$departuresToday = [];
$openBalanceBookings = [];
$dirtyRooms = [];
$outOfOrderRooms = [];
$forecastRows = [];

if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
    $errors[] = 'Filters: Invalid date range.';
}
if (!$errors && $dateFrom > $dateTo) {
    $errors[] = 'Filters: Date from cannot be after date to.';
}

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) {
    $errors[] = 'Properties: ' . $e->getMessage();
    $properties = [];
}

try {
    $roomStatusExpr = room_status_expression($pdo);
} catch (Throwable $e) {
    $errors[] = 'Room status schema check: ' . $e->getMessage();
    $roomStatusExpr = "''";
}

/* Room stock */
try {
    $roomStockFilterSql = property_filter_sql_raw('r', $propertyId);
    $sql = "SELECT COUNT(*) FROM rooms r WHERE 1=1 {$roomStockFilterSql}";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $summary['room_stock'] = (int)$stmt->fetchColumn();
} catch (Throwable $e) {
    $errors[] = 'Room stock: ' . $e->getMessage();
}

/* Occupied rooms today */
try {
    $occupiedFilterSql = property_filter_sql_raw('r', $propertyId);
    $sql = "
        SELECT COUNT(DISTINCT rmg.room_id)
        FROM order_rooming rmg
        INNER JOIN orders o ON o.id = rmg.order_id
        INNER JOIN rooms r ON r.id = rmg.room_id
        WHERE rmg.check_in <= ?
          AND rmg.check_out > ?
          AND rmg.room_id IS NOT NULL
          {$occupiedFilterSql}
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today, $today]);
    $summary['occupied_rooms_today'] = (int)$stmt->fetchColumn();
    $summary['vacant_rooms_today'] = max(0, $summary['room_stock'] - $summary['occupied_rooms_today']);
    $summary['today_occ_pct'] = $summary['room_stock'] > 0 ? ($summary['occupied_rooms_today'] / $summary['room_stock']) * 100 : 0;
} catch (Throwable $e) {
    $errors[] = 'Occupied today: ' . $e->getMessage();
}

/* Arrivals today */
try {
    $arrivalsFilterSql = property_filter_sql_raw('o', $propertyId);
    $sql = "
        SELECT
            o.*,
            p.name AS property_name,
            p.code AS property_code,
            g.firstname,
            g.lastname,
            CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
            (
                SELECT COUNT(*)
                FROM order_rooming r1
                WHERE r1.order_id = o.id
            ) AS room_slots_total,
            (
                SELECT COUNT(*)
                FROM order_rooming r2
                WHERE r2.order_id = o.id
                  AND COALESCE(r2.checked_in_status,0) = 1
            ) AS room_slots_checked_in
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE DATE(o.check_in) = ?
          {$arrivalsFilterSql}
        ORDER BY p.name ASC, o.id DESC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today]);
    $arrivalsToday = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['arrivals_today'] = count($arrivalsToday);
} catch (Throwable $e) {
    $errors[] = 'Arrivals today: ' . $e->getMessage();
}

/* Departures today */
try {
    $departuresFilterSql = property_filter_sql_raw('o', $propertyId);
    $sql = "
        SELECT
            o.*,
            p.name AS property_name,
            p.code AS property_code,
            g.firstname,
            g.lastname,
            CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
            (
                SELECT COUNT(*)
                FROM order_rooming r1
                WHERE r1.order_id = o.id
            ) AS room_slots_total,
            (
                SELECT COUNT(*)
                FROM order_rooming r2
                WHERE r2.order_id = o.id
                  AND COALESCE(r2.checked_out_status,0) = 1
            ) AS room_slots_checked_out,
            (
                SELECT COALESCE(SUM(f.balance),0)
                FROM folios f
                WHERE f.order_id = o.id
            ) AS booking_balance
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE DATE(o.check_out) = ?
          {$departuresFilterSql}
        ORDER BY p.name ASC, o.id DESC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today]);
    $departuresToday = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['departures_today'] = count($departuresToday);
} catch (Throwable $e) {
    $errors[] = 'Departures today: ' . $e->getMessage();
}

/* In house today */
try {
    $inHouseFilterSql = property_filter_sql_raw('o', $propertyId);
    $sql = "
        SELECT COUNT(*)
        FROM orders o
        WHERE o.check_in <= ?
          AND o.check_out > ?
          {$inHouseFilterSql}
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today, $today]);
    $summary['in_house_today'] = (int)$stmt->fetchColumn();
} catch (Throwable $e) {
    $errors[] = 'In house today: ' . $e->getMessage();
}

/* Open balance bookings */
try {
    $balanceFilterSql = property_filter_sql_raw('o', $propertyId);
    $sql = "
        SELECT
            o.*,
            p.name AS property_name,
            p.code AS property_code,
            g.firstname,
            g.lastname,
            CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,'')) AS agent_name,
            (
                SELECT COALESCE(SUM(f.total_charges),0)
                FROM folios f
                WHERE f.order_id = o.id
            ) AS booking_charges,
            (
                SELECT COALESCE(SUM(f.total_payments),0)
                FROM folios f
                WHERE f.order_id = o.id
            ) AS booking_payments,
            (
                SELECT COALESCE(SUM(f.balance),0)
                FROM folios f
                WHERE f.order_id = o.id
            ) AS booking_balance
        FROM orders o
        LEFT JOIN properties p ON p.id = o.property_id
        LEFT JOIN guests g ON g.id = o.guest_id
        LEFT JOIN agents a ON a.id = o.agent_id
        WHERE o.check_in <= ?
          AND o.check_out > ?
          {$balanceFilterSql}
        ORDER BY booking_balance DESC, o.check_out ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today, $today]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $row) {
        if (abs((float)($row['booking_balance'] ?? 0)) > 0.00001) {
            $openBalanceBookings[] = $row;
        }
    }
    $summary['open_balance_bookings'] = count($openBalanceBookings);
} catch (Throwable $e) {
    $errors[] = 'Open balances: ' . $e->getMessage();
}

/* Dirty rooms */
try {
    $dirtyFilterSql = property_filter_sql_raw('r', $propertyId);
    $sql = "
        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 {$roomStatusExpr} = 'dirty'
        {$dirtyFilterSql}
        ORDER BY p.name ASC, r.room_no ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $dirtyRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['dirty_rooms'] = count($dirtyRooms);
} catch (Throwable $e) {
    $errors[] = 'Dirty rooms: ' . $e->getMessage();
}

/* OOO rooms */
try {
    $oooFilterSql = property_filter_sql_raw('r', $propertyId);
    $sql = "
        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 {$roomStatusExpr} IN ('out_of_order','ooo','maintenance','blocked')
        {$oooFilterSql}
        ORDER BY p.name ASC, r.room_no ASC
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $outOfOrderRooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $summary['ooo_rooms'] = count($outOfOrderRooms);
} catch (Throwable $e) {
    $errors[] = 'Out of order rooms: ' . $e->getMessage();
}

/* Today revenue */
try {
    $todayRevenueFilterSql = property_filter_sql_raw('o', $propertyId);
    $sql = "
        SELECT
            COALESCE(SUM(
                CASE
                    WHEN COALESCE(rmg.adults,0) <= 0 THEN 0
                    WHEN COALESCE(rmg.adults,0) = 1 THEN
                        CASE
                            WHEN COALESCE(rp.single_rate,0) > 0 THEN COALESCE(rp.single_rate,0)
                            ELSE COALESCE(rp.sharing_rate,0) + COALESCE(rp.single_supplement,0)
                        END
                    ELSE
                        (COALESCE(rmg.adults,0) * COALESCE(rp.sharing_rate,0))
                END
                +
                (COALESCE(rmg.children,0) * (COALESCE(rp.sharing_rate,0) * (COALESCE(rp.child_rate_percent,0) / 100)))
            ),0) AS projected_revenue,
            COUNT(*) AS room_nights
        FROM rel_orders_prices rp
        INNER JOIN orders o ON o.id = rp.order_id
        INNER JOIN order_rooming rmg ON rmg.order_id = rp.order_id
        WHERE rp.date = ?
        {$todayRevenueFilterSql}
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$today]);
    $rev = $stmt->fetch(PDO::FETCH_ASSOC);

    $summary['today_revenue'] = (float)($rev['projected_revenue'] ?? 0);
    $summary['today_room_nights'] = (int)($rev['room_nights'] ?? 0);
    $summary['today_adr'] = $summary['today_room_nights'] > 0 ? $summary['today_revenue'] / $summary['today_room_nights'] : 0;
} catch (Throwable $e) {
    $errors[] = 'Today revenue: ' . $e->getMessage();
}

/* Forecast */
if (!$errors) {
    try {
        $forecastRoomFilterSql = property_filter_sql_raw('r', $propertyId);
        $forecastRevenueFilterSql = property_filter_sql_raw('o', $propertyId);

        $start = new DateTime($dateFrom);
        $end = new DateTime($dateTo);
        $endInclusive = clone $end;
        $endInclusive->modify('+1 day');
        $period = new DatePeriod($start, new DateInterval('P1D'), $endInclusive);

        $occSum = 0;
        $occCount = 0;

        foreach ($period as $dt) {
            $day = $dt->format('Y-m-d');

            $sql = "
                SELECT COUNT(DISTINCT rmg.room_id)
                FROM order_rooming rmg
                INNER JOIN orders o ON o.id = rmg.order_id
                INNER JOIN rooms r ON r.id = rmg.room_id
                WHERE rmg.check_in <= ?
                  AND rmg.check_out > ?
                  AND rmg.room_id IS NOT NULL
                  {$forecastRoomFilterSql}
            ";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$day, $day]);
            $occupied = (int)$stmt->fetchColumn();
            $occPct = $summary['room_stock'] > 0 ? ($occupied / $summary['room_stock']) * 100 : 0;

            $sql = "
                SELECT
                    COALESCE(SUM(
                        CASE
                            WHEN COALESCE(rmg.adults,0) <= 0 THEN 0
                            WHEN COALESCE(rmg.adults,0) = 1 THEN
                                CASE
                                    WHEN COALESCE(rp.single_rate,0) > 0 THEN COALESCE(rp.single_rate,0)
                                    ELSE COALESCE(rp.sharing_rate,0) + COALESCE(rp.single_supplement,0)
                                END
                            ELSE
                                (COALESCE(rmg.adults,0) * COALESCE(rp.sharing_rate,0))
                        END
                        +
                        (COALESCE(rmg.children,0) * (COALESCE(rp.sharing_rate,0) * (COALESCE(rp.child_rate_percent,0) / 100)))
                    ),0) AS projected_revenue,
                    COUNT(*) AS room_nights
                FROM rel_orders_prices rp
                INNER JOIN orders o ON o.id = rp.order_id
                INNER JOIN order_rooming rmg ON rmg.order_id = rp.order_id
                WHERE rp.date = ?
                {$forecastRevenueFilterSql}
            ";
            $stmt = $pdo->prepare($sql);
            $stmt->execute([$day]);
            $rev = $stmt->fetch(PDO::FETCH_ASSOC);

            $revenue = (float)($rev['projected_revenue'] ?? 0);
            $roomNights = (int)($rev['room_nights'] ?? 0);
            $adr = $roomNights > 0 ? ($revenue / $roomNights) : 0;

            $forecastRows[] = [
                'day' => $day,
                'occupied' => $occupied,
                'occ_pct' => $occPct,
                'room_nights' => $roomNights,
                'adr' => $adr,
                'revenue' => $revenue,
            ];

            $summary['forecast_revenue'] += $revenue;
            $summary['forecast_room_nights'] += $roomNights;
            $occSum += $occPct;
            $occCount++;
        }

        $summary['forecast_avg_adr'] = $summary['forecast_room_nights'] > 0 ? ($summary['forecast_revenue'] / $summary['forecast_room_nights']) : 0;
        $summary['forecast_avg_occ'] = $occCount > 0 ? ($occSum / $occCount) : 0;
    } catch (Throwable $e) {
        $errors[] = 'Forecast: ' . $e->getMessage();
    }
}

require_once __DIR__ . '/../../includes/header.php';
?>
<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-graph-up"></i> Executive View</div>
            <div class="hero-title mt-2">Management Dashboard</div>
            <div class="hero-subtitle mt-2">
                Executive summary of occupancy, ADR, projected revenue, front desk activity, housekeeping issues, and open folio balances.
            </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="card panel-card mb-4">
    <div class="card-header"><h5 class="mb-0">Dashboard Filters</h5></div>
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <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-3">
                <label class="form-label">Forecast From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>
            <div class="col-md-3">
                <label class="form-label">Forecast To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </div>
            <div class="col-md-3 d-grid">
                <button class="btn btn-primary">Refresh Dashboard</button>
            </div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-grid"></i></div><div><div class="metric-label">Room Stock</div><div class="metric-value"><?php echo number_format($summary['room_stock']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><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 Today</div><div class="metric-value"><?php echo number_format($summary['occupied_rooms_today']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-success-subtle text-success"><i class="bi bi-percent"></i></div><div><div class="metric-label">Occ. Today</div><div class="metric-value"><?php echo pct_fmt($summary['today_occ_pct']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-currency-dollar"></i></div><div><div class="metric-label">Revenue Today</div><div class="metric-value"><?php echo money_fmt($summary['today_revenue']); ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-info-subtle text-info"><i class="bi bi-box-arrow-in-right"></i></div><div><div class="metric-label">Arrivals Today</div><div class="metric-value"><?php echo number_format($summary['arrivals_today']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-box-arrow-right"></i></div><div><div class="metric-label">Departures Today</div><div class="metric-value"><?php echo number_format($summary['departures_today']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-dark-subtle text-dark"><i class="bi bi-cash-stack"></i></div><div><div class="metric-label">Open Balances</div><div class="metric-value"><?php echo number_format($summary['open_balance_bookings']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-primary-subtle text-primary"><i class="bi bi-house-check"></i></div><div><div class="metric-label">In House</div><div class="metric-value"><?php echo number_format($summary['in_house_today']); ?></div></div></div></div>
</div>

<div class="row g-4 mb-4">
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-brush"></i></div><div><div class="metric-label">Dirty Rooms</div><div class="metric-value"><?php echo number_format($summary['dirty_rooms']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><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($summary['ooo_rooms']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-success-subtle text-success"><i class="bi bi-bar-chart-line"></i></div><div><div class="metric-label">Forecast Avg Occ.</div><div class="metric-value"><?php echo pct_fmt($summary['forecast_avg_occ']); ?></div></div></div></div>
    <div class="col-sm-6 col-xl-3"><div class="metric-card"><div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-graph-up-arrow"></i></div><div><div class="metric-label">Forecast Revenue</div><div class="metric-value"><?php echo money_fmt($summary['forecast_revenue']); ?></div></div></div></div>
</div>

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