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

if (!function_exists('money_val')) {
    function money_val($amount): float
    {
        return round((float)$amount, 2);
    }
}

$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'))));

if (strtotime($dateFrom) === false || strtotime($dateTo) === false || $dateFrom > $dateTo) {
    exit('Invalid date range.');
}

$paramsBase = [];
$orderPropertySql = '';
$roomPropertySql  = '';
$ratePropertySql  = '';

if ($propertyId > 0) {
    $paramsBase[':property_id'] = $propertyId;
    $orderPropertySql = " AND o.property_id = :property_id ";
    $roomPropertySql  = " AND r.property_id = :property_id ";
    $ratePropertySql  = " AND rp.property_id = :property_id ";
}

/* property label */
$propertyLabel = 'All Properties';
if ($propertyId > 0) {
    $stmt = $pdo->prepare("SELECT name, code FROM properties WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $propertyId]);
    $prop = $stmt->fetch(PDO::FETCH_ASSOC);
    if ($prop) {
        $propertyLabel = trim((string)($prop['name'] ?? ''));
        if (!empty($prop['code'])) {
            $propertyLabel .= ' (' . $prop['code'] . ')';
        }
    }
}

$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,
];

/* Room stock */
$sql = "SELECT COUNT(*) FROM rooms r WHERE 1=1 $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['room_stock'] = (int)$stmt->fetchColumn();

/* Occupied rooms today */
$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 DATE(o.check_in) <= :today
      AND DATE(o.check_out) >= :today
      AND rmg.room_id IS NOT NULL
      $roomPropertySql
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$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;

/* Arrivals today */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_in) = :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['arrivals_today'] = (int)$stmt->fetchColumn();

/* Departures today */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_out) = :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['departures_today'] = (int)$stmt->fetchColumn();

/* In house */
$sql = "SELECT COUNT(*) FROM orders o WHERE DATE(o.check_in) <= :today AND DATE(o.check_out) >= :today $orderPropertySql";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$summary['in_house_today'] = (int)$stmt->fetchColumn();

/* Dirty rooms */
$sql = "SELECT COUNT(*) FROM rooms r WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') = 'dirty' $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['dirty_rooms'] = (int)$stmt->fetchColumn();

/* OOO rooms */
$sql = "SELECT COUNT(*) FROM rooms r WHERE COALESCE(LOWER(r.housekeeping_status), LOWER(r.status), '') IN ('out_of_order','ooo','maintenance','blocked') $roomPropertySql";
$stmt = $pdo->prepare($sql);
$stmt->execute($paramsBase);
$summary['ooo_rooms'] = (int)$stmt->fetchColumn();

/* Open balances */
$sql = "
    SELECT o.id,
           (
             SELECT COALESCE(SUM(f.balance),0)
             FROM folios f
             WHERE f.order_id = o.id
           ) AS booking_balance
    FROM orders o
    WHERE DATE(o.check_in) <= :today
      AND DATE(o.check_out) >= :today
      $orderPropertySql
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$openBalance = 0;
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
    if (abs((float)$row['booking_balance']) > 0.00001) {
        $openBalance++;
    }
}
$summary['open_balance_bookings'] = $openBalance;

/* Today revenue */
$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 order_rooming rmg ON rmg.order_id = rp.order_id
    WHERE rp.date = :today
    $ratePropertySql
";
$params = $paramsBase;
$params[':today'] = $today;
$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rev = $stmt->fetch(PDO::FETCH_ASSOC);
$summary['today_revenue'] = money_val($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;

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

$forecastRows = [];
$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 DATE(o.check_in) <= :day
          AND DATE(o.check_out) >= :day
          AND rmg.room_id IS NOT NULL
          $roomPropertySql
    ";
    $params = $paramsBase;
    $params[':day'] = $day;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $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 order_rooming rmg ON rmg.order_id = rp.order_id
        WHERE rp.date = :day
        $ratePropertySql
    ";
    $params = $paramsBase;
    $params[':day'] = $day;
    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $rev = $stmt->fetch(PDO::FETCH_ASSOC);

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

    $forecastRows[] = [
        'Section' => 'Forecast',
        'Property' => $propertyLabel,
        'Date From' => $dateFrom,
        'Date To' => $dateTo,
        'Day' => $day,
        'Room Stock' => $summary['room_stock'],
        'Occupied Rooms' => $occupied,
        'Vacant Rooms' => max(0, $summary['room_stock'] - $occupied),
        'Occupancy %' => round($occPct, 2),
        'Room Nights' => $roomNights,
        'ADR' => money_val($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;

/* Build export rows */
$rows = [];

$rows[] = [
    'Section' => 'Summary',
    'Property' => $propertyLabel,
    'Date From' => $dateFrom,
    'Date To' => $dateTo,
    'Day' => $today,
    'Room Stock' => $summary['room_stock'],
    'Occupied Rooms' => $summary['occupied_rooms_today'],
    'Vacant Rooms' => $summary['vacant_rooms_today'],
    'Occupancy %' => round($summary['today_occ_pct'], 2),
    'Room Nights' => $summary['today_room_nights'],
    'ADR' => money_val($summary['today_adr']),
    'Revenue' => money_val($summary['today_revenue']),
];

$rows[] = [
    'Section' => 'KPI',
    'Property' => $propertyLabel,
    'Date From' => $dateFrom,
    'Date To' => $dateTo,
    'Day' => 'Today',
    'Room Stock' => $summary['arrivals_today'],
    'Occupied Rooms' => $summary['departures_today'],
    'Vacant Rooms' => $summary['in_house_today'],
    'Occupancy %' => $summary['open_balance_bookings'],
    'Room Nights' => $summary['dirty_rooms'],
    'ADR' => $summary['ooo_rooms'],
    'Revenue' => '',
];

$rows[] = [
    'Section' => 'Forecast Summary',
    'Property' => $propertyLabel,
    'Date From' => $dateFrom,
    'Date To' => $dateTo,
    'Day' => 'Range',
    'Room Stock' => $summary['room_stock'],
    'Occupied Rooms' => '',
    'Vacant Rooms' => '',
    'Occupancy %' => round($summary['forecast_avg_occ'], 2),
    'Room Nights' => $summary['forecast_room_nights'],
    'ADR' => money_val($summary['forecast_avg_adr']),
    'Revenue' => money_val($summary['forecast_revenue']),
];

$rows = array_merge($rows, $forecastRows);

$headers = [
    'Section',
    'Property',
    'Date From',
    'Date To',
    'Day',
    'Room Stock',
    'Occupied Rooms',
    'Vacant Rooms',
    'Occupancy %',
    'Room Nights',
    'ADR',
    'Revenue',
];

$filename = 'management_dashboard_' . $today . '.csv';
csv_out($headers, $rows, $filename);