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

$pageTitle = 'ADR / RevPAR Report';

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('perf_table_exists')) {
    function perf_table_exists(PDO $pdo, string $tableName): bool
    {
        $stmt = $pdo->prepare("
            SELECT COUNT(*)
            FROM information_schema.tables
            WHERE table_schema = DATABASE()
              AND table_name = :table_name
        ");
        $stmt->execute([':table_name' => $tableName]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('perf_column_exists')) {
    function perf_column_exists(PDO $pdo, string $tableName, string $columnName): bool
    {
        $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' => $tableName,
            ':column_name' => $columnName,
        ]);
        return (int)$stmt->fetchColumn() > 0;
    }
}

if (!function_exists('build_perf_date_range')) {
    function build_perf_date_range(string $from, string $to): array
    {
        $dates = [];
        $start = new DateTime($from);
        $end = new DateTime($to);
        $endExclusive = (clone $end)->modify('+1 day');

        while ($start < $endExclusive) {
            $dates[] = $start->format('Y-m-d');
            $start->modify('+1 day');
        }

        return $dates;
    }
}

$errors = [];

$today = date('Y-m-d');
$monthStart = date('Y-m-01');

$dateFrom = trim((string)($_GET['date_from'] ?? $monthStart));
$dateTo = trim((string)($_GET['date_to'] ?? $today));
$selectedPropertyId = (int)($_GET['property_id'] ?? 0);
$selectedRoomTypeId = (int)($_GET['room_type_id'] ?? 0);
$includeCancelled = (int)($_GET['include_cancelled'] ?? 0);

if ($dateFrom === '') {
    $dateFrom = $monthStart;
}
if ($dateTo === '') {
    $dateTo = $today;
}

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

$requiredTables = ['orders', 'order_rooming', 'rooms', 'room_types', 'properties', 'folio_transactions'];
foreach ($requiredTables as $table) {
    if (!perf_table_exists($pdo, $table)) {
        $errors[] = 'Required table missing: ' . $table;
    }
}

$hasRoomActive = !$errors && perf_column_exists($pdo, 'rooms', 'is_active');
$hasRoomStatus = !$errors && perf_column_exists($pdo, 'rooms', 'status');
$hasOrderStatus = !$errors && perf_column_exists($pdo, 'orders', 'status');
$hasOrderCancelled = !$errors && perf_column_exists($pdo, 'orders', 'cancelled');
$hasRoomingCancelled = !$errors && perf_column_exists($pdo, 'order_rooming', 'cancelled');
$hasRoomingProperty = !$errors && perf_column_exists($pdo, 'order_rooming', 'property_id');

$properties = [];
$roomTypes = [];

if (!$errors) {
    try {
        $properties = $pdo->query("
            SELECT id, name, code
            FROM properties
            WHERE is_active = 1
            ORDER BY name ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load properties: ' . $e->getMessage();
    }

    try {
        $roomTypes = $pdo->query("
            SELECT id, title, property_id
            FROM room_types
            ORDER BY title ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load room types: ' . $e->getMessage();
    }
}

$dateList = [];
if (!$errors) {
    $dateList = build_perf_date_range($dateFrom, $dateTo);
}

$fromSql = $pdo->quote($dateFrom);
$toSql = $pdo->quote($dateTo);

$summary = [
    'room_revenue' => 0,
    'available_room_nights' => 0,
    'occupied_room_nights' => 0,
    'vacant_room_nights' => 0,
    'occupancy_percent' => 0,
    'adr' => 0,
    'revpar' => 0,
    'bookings_count' => 0,
];

$dailyRows = [];
$roomTypeRows = [];
$propertyRows = [];
$detailRows = [];

function performance_cancel_filter(bool $includeCancelled, bool $hasOrderCancelled, bool $hasRoomingCancelled, bool $hasOrderStatus): string
{
    if ($includeCancelled) {
        return "";
    }

    $sql = "";

    if ($hasOrderCancelled) {
        $sql .= " AND COALESCE(o.cancelled, 0) = 0 ";
    }

    if ($hasRoomingCancelled) {
        $sql .= " AND COALESCE(orm.cancelled, 0) = 0 ";
    }

    if ($hasOrderStatus) {
        $sql .= " AND LOWER(COALESCE(o.status, '')) NOT IN ('cancelled','canceled','void','deleted') ";
    }

    return $sql;
}

/* ===========================
   DAILY PERFORMANCE
=========================== */
if (!$errors) {
    try {
        $roomWhere = " 1 = 1 ";
        $roomParams = [];

        if ($selectedPropertyId > 0) {
            $roomWhere .= " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $roomParams[':property_id'] = $selectedPropertyId;
        }

        if ($selectedRoomTypeId > 0) {
            $roomWhere .= " AND r.room_type_id = :room_type_id ";
            $roomParams[':room_type_id'] = $selectedRoomTypeId;
        }

        if ($hasRoomActive) {
            $roomWhere .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        if ($hasRoomStatus) {
            $roomWhere .= " AND (r.status IS NULL OR r.status NOT IN ('inactive','out_of_order','blocked')) ";
        }

        $stmt = $pdo->prepare("
            SELECT COUNT(*) AS rooms_count
            FROM rooms r
            LEFT JOIN room_types rt ON rt.id = r.room_type_id
            WHERE $roomWhere
        ");
        $stmt->execute($roomParams);
        $roomsCount = (int)($stmt->fetchColumn() ?: 0);

        foreach ($dateList as $day) {
            $daySql = $pdo->quote($day);

            $occWhere = "
                orm.check_in <= $daySql
                AND orm.check_out > $daySql
            ";
            $occParams = [];

            if ($selectedPropertyId > 0) {
                if ($hasRoomingProperty) {
                    $occWhere .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
                } else {
                    $occWhere .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
                }
                $occParams[':property_id'] = $selectedPropertyId;
            }

            if ($selectedRoomTypeId > 0) {
                $occWhere .= " AND orm.room_type_id = :room_type_id ";
                $occParams[':room_type_id'] = $selectedRoomTypeId;
            }

            $occWhere .= performance_cancel_filter((bool)$includeCancelled, $hasOrderCancelled, $hasRoomingCancelled, $hasOrderStatus);

            $stmtOcc = $pdo->prepare("
                SELECT
                    COUNT(DISTINCT orm.id) AS occupied_rooms,
                    COUNT(DISTINCT o.id) AS bookings_count
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN rooms r ON r.id = orm.room_id
                LEFT JOIN room_types rt ON rt.id = orm.room_type_id
                WHERE $occWhere
            ");
            $stmtOcc->execute($occParams);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $occupied = (int)($occ['occupied_rooms'] ?? 0);
            $available = $roomsCount;

            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $revenueWhere = "
                DATE(ft.tx_date) = $daySql
                AND ft.tx_type = 'room_charge'
            ";
            $revenueParams = [];

            if ($selectedPropertyId > 0) {
                $revenueWhere .= " AND ft.property_id = :r_property_id ";
                $revenueParams[':r_property_id'] = $selectedPropertyId;
            }

            if ($selectedRoomTypeId > 0) {
                $revenueWhere .= "
                    AND EXISTS (
                        SELECT 1
                        FROM order_rooming orm2
                        WHERE orm2.order_id = ft.order_id
                          AND orm2.room_type_id = :r_room_type_id
                          AND orm2.check_in <= $daySql
                          AND orm2.check_out > $daySql
                    )
                ";
                $revenueParams[':r_room_type_id'] = $selectedRoomTypeId;
            }

            $stmtRev = $pdo->prepare("
                SELECT COALESCE(SUM(ft.debit), 0)
                FROM folio_transactions ft
                WHERE $revenueWhere
            ");
            $stmtRev->execute($revenueParams);
            $roomRevenue = (float)($stmtRev->fetchColumn() ?: 0);

            $occupancyPercent = $available > 0 ? ($occupied / $available) * 100 : 0;
            $adr = $occupied > 0 ? ($roomRevenue / $occupied) : 0;
            $revpar = $available > 0 ? ($roomRevenue / $available) : 0;
            $vacant = max(0, $available - $occupied);

            $dailyRows[] = [
                'perf_date' => $day,
                'room_revenue' => $roomRevenue,
                'available_room_nights' => $available,
                'occupied_room_nights' => $occupied,
                'vacant_room_nights' => $vacant,
                'occupancy_percent' => $occupancyPercent,
                'adr' => $adr,
                'revpar' => $revpar,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
            ];

            $summary['room_revenue'] += $roomRevenue;
            $summary['available_room_nights'] += $available;
            $summary['occupied_room_nights'] += $occupied;
            $summary['vacant_room_nights'] += $vacant;
            $summary['bookings_count'] += (int)($occ['bookings_count'] ?? 0);
        }

        $summary['occupancy_percent'] = $summary['available_room_nights'] > 0
            ? ($summary['occupied_room_nights'] / $summary['available_room_nights']) * 100
            : 0;

        $summary['adr'] = $summary['occupied_room_nights'] > 0
            ? ($summary['room_revenue'] / $summary['occupied_room_nights'])
            : 0;

        $summary['revpar'] = $summary['available_room_nights'] > 0
            ? ($summary['room_revenue'] / $summary['available_room_nights'])
            : 0;
    } catch (Throwable $e) {
        $errors[] = 'Could not load daily ADR / RevPAR: ' . $e->getMessage();
    }
}

/* ===========================
   ROOM TYPE PERFORMANCE
=========================== */
if (!$errors) {
    try {
        $typeFilter = " 1 = 1 ";
        $typeParams = [];

        if ($selectedPropertyId > 0) {
            $typeFilter .= " AND COALESCE(r.property_id, rt.property_id) = :property_id ";
            $typeParams[':property_id'] = $selectedPropertyId;
        }

        if ($selectedRoomTypeId > 0) {
            $typeFilter .= " AND rt.id = :room_type_id ";
            $typeParams[':room_type_id'] = $selectedRoomTypeId;
        }

        if ($hasRoomActive) {
            $typeFilter .= " AND COALESCE(r.is_active, 1) = 1 ";
        }

        $stmt = $pdo->prepare("
            SELECT
                rt.id AS room_type_id,
                rt.title AS room_type,
                COALESCE(p.name, 'No Property') AS property_name,
                COALESCE(p.id, 0) AS property_id,
                COUNT(r.id) AS rooms_count
            FROM room_types rt
            LEFT JOIN rooms r ON r.room_type_id = rt.id
            LEFT JOIN properties p ON p.id = COALESCE(r.property_id, rt.property_id)
            WHERE $typeFilter
            GROUP BY rt.id, rt.title, property_name, property_id
            HAVING rooms_count > 0
            ORDER BY property_name ASC, rt.title ASC
        ");
        $stmt->execute($typeParams);
        $types = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($types as $type) {
            $available = (int)$type['rooms_count'] * count($dateList);

            $occWhere = "
                orm.room_type_id = :room_type_id
                AND orm.check_in <= $toSql
                AND orm.check_out > $fromSql
            ";
            $occParams = [
                ':room_type_id' => (int)$type['room_type_id'],
            ];

            if ((int)$type['property_id'] > 0) {
                if ($hasRoomingProperty) {
                    $occWhere .= " AND COALESCE(orm.property_id, o.property_id) = :property_id ";
                } else {
                    $occWhere .= " AND o.property_id = :property_id ";
                }
                $occParams[':property_id'] = (int)$type['property_id'];
            }

            $occWhere .= performance_cancel_filter((bool)$includeCancelled, $hasOrderCancelled, $hasRoomingCancelled, $hasOrderStatus);

            $stmtOcc = $pdo->prepare("
                SELECT
                    COALESCE(SUM(
                        DATEDIFF(
                            LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                            GREATEST(orm.check_in, $fromSql)
                        )
                    ), 0) AS occupied_nights,
                    COUNT(DISTINCT o.id) AS bookings_count
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                WHERE $occWhere
            ");
            $stmtOcc->execute($occParams);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $occupied = (int)($occ['occupied_nights'] ?? 0);
            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $revWhere = "
                ft.tx_type = 'room_charge'
                AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
                AND EXISTS (
                    SELECT 1
                    FROM order_rooming orm2
                    LEFT JOIN orders o2 ON o2.id = orm2.order_id
                    WHERE orm2.order_id = ft.order_id
                      AND orm2.room_type_id = :rev_room_type_id
                      AND orm2.check_in <= $toSql
                      AND orm2.check_out > $fromSql
                )
            ";

            $revParams = [
                ':rev_room_type_id' => (int)$type['room_type_id'],
            ];

            if ((int)$type['property_id'] > 0) {
                $revWhere .= " AND ft.property_id = :rev_property_id ";
                $revParams[':rev_property_id'] = (int)$type['property_id'];
            }

            $stmtRev = $pdo->prepare("
                SELECT COALESCE(SUM(ft.debit), 0)
                FROM folio_transactions ft
                WHERE $revWhere
            ");
            $stmtRev->execute($revParams);
            $roomRevenue = (float)($stmtRev->fetchColumn() ?: 0);

            $roomTypeRows[] = [
                'property_name' => $type['property_name'],
                'room_type' => $type['room_type'],
                'rooms_count' => (int)$type['rooms_count'],
                'room_revenue' => $roomRevenue,
                'available_room_nights' => $available,
                'occupied_room_nights' => $occupied,
                'occupancy_percent' => $available > 0 ? ($occupied / $available) * 100 : 0,
                'adr' => $occupied > 0 ? ($roomRevenue / $occupied) : 0,
                'revpar' => $available > 0 ? ($roomRevenue / $available) : 0,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load room type performance: ' . $e->getMessage();
    }
}

/* ===========================
   PROPERTY PERFORMANCE
=========================== */
if (!$errors) {
    try {
        $propFilter = " 1 = 1 ";
        $propParams = [];

        if ($selectedPropertyId > 0) {
            $propFilter .= " AND p.id = :property_id ";
            $propParams[':property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT
                p.id AS property_id,
                p.name AS property_name,
                p.code AS property_code,
                COUNT(r.id) AS rooms_count
            FROM properties p
            LEFT JOIN rooms r ON r.property_id = p.id
            WHERE $propFilter
            GROUP BY p.id, p.name, p.code
            HAVING rooms_count > 0
            ORDER BY p.name ASC
        ");
        $stmt->execute($propParams);
        $props = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($props as $prop) {
            $available = (int)$prop['rooms_count'] * count($dateList);

            if ($hasRoomingProperty) {
                $occWhere = "COALESCE(orm.property_id, o.property_id, r.property_id) = :property_id";
            } else {
                $occWhere = "COALESCE(o.property_id, r.property_id) = :property_id";
            }

            $occWhere .= "
                AND orm.check_in <= $toSql
                AND orm.check_out > $fromSql
            ";

            $occParams = [
                ':property_id' => (int)$prop['property_id'],
            ];

            if ($selectedRoomTypeId > 0) {
                $occWhere .= " AND orm.room_type_id = :room_type_id ";
                $occParams[':room_type_id'] = $selectedRoomTypeId;
            }

            $occWhere .= performance_cancel_filter((bool)$includeCancelled, $hasOrderCancelled, $hasRoomingCancelled, $hasOrderStatus);

            $stmtOcc = $pdo->prepare("
                SELECT
                    COALESCE(SUM(
                        DATEDIFF(
                            LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                            GREATEST(orm.check_in, $fromSql)
                        )
                    ), 0) AS occupied_nights,
                    COUNT(DISTINCT o.id) AS bookings_count
                FROM order_rooming orm
                LEFT JOIN orders o ON o.id = orm.order_id
                LEFT JOIN rooms r ON r.id = orm.room_id
                WHERE $occWhere
            ");
            $stmtOcc->execute($occParams);
            $occ = $stmtOcc->fetch(PDO::FETCH_ASSOC) ?: [];

            $occupied = (int)($occ['occupied_nights'] ?? 0);
            if ($occupied > $available && $available > 0) {
                $occupied = $available;
            }

            $revWhere = "
                ft.tx_type = 'room_charge'
                AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
                AND ft.property_id = :property_id
            ";

            $revParams = [
                ':property_id' => (int)$prop['property_id'],
            ];

            if ($selectedRoomTypeId > 0) {
                $revWhere .= "
                    AND EXISTS (
                        SELECT 1
                        FROM order_rooming orm2
                        WHERE orm2.order_id = ft.order_id
                          AND orm2.room_type_id = :room_type_id
                          AND orm2.check_in <= $toSql
                          AND orm2.check_out > $fromSql
                    )
                ";
                $revParams[':room_type_id'] = $selectedRoomTypeId;
            }

            $stmtRev = $pdo->prepare("
                SELECT COALESCE(SUM(ft.debit), 0)
                FROM folio_transactions ft
                WHERE $revWhere
            ");
            $stmtRev->execute($revParams);
            $roomRevenue = (float)($stmtRev->fetchColumn() ?: 0);

            $propertyRows[] = [
                'property_name' => $prop['property_name'],
                'property_code' => $prop['property_code'],
                'rooms_count' => (int)$prop['rooms_count'],
                'room_revenue' => $roomRevenue,
                'available_room_nights' => $available,
                'occupied_room_nights' => $occupied,
                'occupancy_percent' => $available > 0 ? ($occupied / $available) * 100 : 0,
                'adr' => $occupied > 0 ? ($roomRevenue / $occupied) : 0,
                'revpar' => $available > 0 ? ($roomRevenue / $available) : 0,
                'bookings_count' => (int)($occ['bookings_count'] ?? 0),
            ];
        }
    } catch (Throwable $e) {
        $propertyRows = [];
    }
}

/* ===========================
   BOOKING DETAIL
=========================== */
if (!$errors) {
    try {
        $detailWhere = "
            orm.check_in <= $toSql
            AND orm.check_out > $fromSql
        ";

        $detailParams = [];

        if ($selectedPropertyId > 0) {
            if ($hasRoomingProperty) {
                $detailWhere .= " AND COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id) = :property_id ";
            } else {
                $detailWhere .= " AND COALESCE(o.property_id, r.property_id, rt.property_id) = :property_id ";
            }
            $detailParams[':property_id'] = $selectedPropertyId;
        }

        if ($selectedRoomTypeId > 0) {
            $detailWhere .= " AND orm.room_type_id = :room_type_id ";
            $detailParams[':room_type_id'] = $selectedRoomTypeId;
        }

        $detailWhere .= performance_cancel_filter((bool)$includeCancelled, $hasOrderCancelled, $hasRoomingCancelled, $hasOrderStatus);

        $propertyJoinExpr = $hasRoomingProperty
            ? "COALESCE(orm.property_id, o.property_id, r.property_id, rt.property_id)"
            : "COALESCE(o.property_id, r.property_id, rt.property_id)";

        $stmt = $pdo->prepare("
            SELECT
                orm.id AS rooming_id,
                orm.order_id,
                o.order_no,
                orm.check_in,
                orm.check_out,
                r.room_no,
                rt.title AS room_type,
                p.name AS property_name,
                p.code AS property_code,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name,
                DATEDIFF(
                    LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)),
                    GREATEST(orm.check_in, $fromSql)
                ) AS nights_in_range,
                (
                    SELECT COALESCE(SUM(ft.debit), 0)
                    FROM folio_transactions ft
                    WHERE ft.order_id = orm.order_id
                      AND ft.tx_type = 'room_charge'
                      AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
                ) AS room_revenue
            FROM order_rooming orm
            LEFT JOIN orders o ON o.id = orm.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN rooms r ON r.id = orm.room_id
            LEFT JOIN room_types rt ON rt.id = orm.room_type_id
            LEFT JOIN properties p ON p.id = $propertyJoinExpr
            WHERE $detailWhere
            ORDER BY orm.check_in ASC, p.name ASC, rt.title ASC, r.room_no ASC
            LIMIT 1500
        ");
        $stmt->execute($detailParams);
        $detailRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load booking performance detail: ' . $e->getMessage();
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'adr_revpar_report_' . date('Ymd_His') . '.csv';

    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');

    $out = fopen('php://output', 'w');

    fputcsv($out, [
        'Date',
        'Room Revenue',
        'Available Room Nights',
        'Occupied Room Nights',
        'Vacant Room Nights',
        'Occupancy %',
        'ADR',
        'RevPAR',
        'Bookings'
    ]);

    foreach ($dailyRows as $row) {
        fputcsv($out, [
            $row['perf_date'],
            number_format((float)$row['room_revenue'], 2, '.', ''),
            $row['available_room_nights'],
            $row['occupied_room_nights'],
            $row['vacant_room_nights'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            number_format((float)$row['adr'], 2, '.', ''),
            number_format((float)$row['revpar'], 2, '.', ''),
            $row['bookings_count'],
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Room Type Performance']);
    fputcsv($out, ['Property', 'Room Type', 'Rooms', 'Room Revenue', 'Available Nights', 'Occupied Nights', 'Occupancy %', 'ADR', 'RevPAR']);

    foreach ($roomTypeRows as $row) {
        fputcsv($out, [
            $row['property_name'],
            $row['room_type'],
            $row['rooms_count'],
            number_format((float)$row['room_revenue'], 2, '.', ''),
            $row['available_room_nights'],
            $row['occupied_room_nights'],
            number_format((float)$row['occupancy_percent'], 2, '.', ''),
            number_format((float)$row['adr'], 2, '.', ''),
            number_format((float)$row['revpar'], 2, '.', ''),
        ]);
    }

    fclose($out);
    exit;
}

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-speedometer2"></i> Revenue Performance
            </div>
            <div class="hero-title mt-2">ADR / RevPAR Report</div>
            <div class="hero-subtitle mt-2">
                Analyse room revenue, occupancy, ADR and RevPAR by day, property and room type.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="adr_revpar_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-light">
                <i class="bi bi-download me-2"></i>Export CSV
            </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-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2">
                <label class="form-label">Date From</label>
                <input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>">
            </div>

            <div class="col-md-2">
                <label class="form-label">Date To</label>
                <input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>">
            </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 $selectedPropertyId === (int)$property['id'] ? 'selected' : ''; ?>>
                            <?php echo h($property['name']); ?> - <?php echo h($property['code']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-3">
                <label class="form-label">Room Type</label>
                <select name="room_type_id" class="form-select">
                    <option value="0">All room types</option>
                    <?php foreach ($roomTypes as $rt): ?>
                        <option value="<?php echo (int)$rt['id']; ?>" <?php echo $selectedRoomTypeId === (int)$rt['id'] ? 'selected' : ''; ?>>
                            <?php echo h($rt['title']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-1">
                <label class="form-label">Cancelled</label>
                <select name="include_cancelled" class="form-select">
                    <option value="0" <?php echo $includeCancelled === 0 ? 'selected' : ''; ?>>No</option>
                    <option value="1" <?php echo $includeCancelled === 1 ? 'selected' : ''; ?>>Yes</option>
                </select>
            </div>

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

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-success-subtle text-success"><i class="bi bi-cash-coin"></i></div>
            <div>
                <div class="metric-label">Room Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['room_revenue']); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <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">Occupied Nights</div>
                <div class="metric-value"><?php echo (int)$summary['occupied_room_nights']; ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-info-subtle text-info"><i class="bi bi-calendar-check"></i></div>
            <div>
                <div class="metric-label">Available Nights</div>
                <div class="metric-value"><?php echo (int)$summary['available_room_nights']; ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-warning-subtle text-warning"><i class="bi bi-percent"></i></div>
            <div>
                <div class="metric-label">Occupancy</div>
                <div class="metric-value"><?php echo money_fmt($summary['occupancy_percent']); ?>%</div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-danger-subtle text-danger"><i class="bi bi-graph-up"></i></div>
            <div>
                <div class="metric-label">ADR</div>
                <div class="metric-value"><?php echo money_fmt($summary['adr']); ?></div>
            </div>
        </div>
    </div>

    <div class="col-md-6 col-xl-2">
        <div class="metric-card">
            <div class="metric-icon bg-secondary-subtle text-secondary"><i class="bi bi-speedometer"></i></div>
            <div>
                <div class="metric-label">RevPAR</div>
                <div class="metric-value"><?php echo money_fmt($summary['revpar']); ?></div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4">
    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Daily ADR / RevPAR</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">Available</th>
                                <th class="text-end">Occupied</th>
                                <th class="text-end">Occ %</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$dailyRows): ?>
                                <tr><td colspan="7" class="text-center text-muted py-4">No performance data found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($dailyRows as $row): ?>
                                    <tr>
                                        <td><strong><?php echo h($row['perf_date']); ?></strong></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue']); ?></td>
                                        <td class="text-end"><?php echo (int)$row['available_room_nights']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['occupied_room_nights']; ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['adr']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['revpar']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Property Performance</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Property</th>
                                <th class="text-end">Revenue</th>
                                <th class="text-end">Occ %</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$propertyRows): ?>
                                <tr><td colspan="5" class="text-center text-muted py-4">No property performance found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($propertyRows as $row): ?>
                                    <tr>
                                        <td>
                                            <strong><?php echo h($row['property_name']); ?></strong>
                                            <?php if (!empty($row['property_code'])): ?>
                                                <div class="small text-muted"><?php echo h($row['property_code']); ?></div>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end"><?php echo money_fmt($row['adr']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['revpar']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header">
                <h5 class="mb-0">Room Type Performance</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Property</th>
                                <th>Room Type</th>
                                <th class="text-end">Rooms</th>
                                <th class="text-end">Revenue</th>
                                <th class="text-end">Available</th>
                                <th class="text-end">Occupied</th>
                                <th class="text-end">Occ %</th>
                                <th class="text-end">ADR</th>
                                <th class="text-end">RevPAR</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$roomTypeRows): ?>
                                <tr><td colspan="9" class="text-center text-muted py-4">No room type performance found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($roomTypeRows as $row): ?>
                                    <tr>
                                        <td><?php echo h($row['property_name']); ?></td>
                                        <td><strong><?php echo h($row['room_type']); ?></strong></td>
                                        <td class="text-end"><?php echo (int)$row['rooms_count']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue']); ?></td>
                                        <td class="text-end"><?php echo (int)$row['available_room_nights']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['occupied_room_nights']; ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['occupancy_percent']); ?>%</td>
                                        <td class="text-end"><?php echo money_fmt($row['adr']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['revpar']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

    <div class="col-12">
        <div class="card panel-card">
            <div class="card-header d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Booking / Rooming Performance Detail</h5>
                <span class="text-muted small">Showing latest 1,500 rooming records</span>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Property</th>
                                <th>Booking</th>
                                <th>Guest</th>
                                <th>Agent</th>
                                <th>Room</th>
                                <th>Room Type</th>
                                <th>Stay</th>
                                <th class="text-end">Nights</th>
                                <th class="text-end">Room Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$detailRows): ?>
                                <tr><td colspan="9" class="text-center text-muted py-4">No rooming performance records found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($detailRows as $row): ?>
                                    <?php
                                    $propertyLabel = trim((string)($row['property_name'] ?? ''));
                                    if (!empty($row['property_code'])) {
                                        $propertyLabel .= ' (' . $row['property_code'] . ')';
                                    }
                                    $guestName = trim((string)($row['guest_name'] ?? ''));
                                    $agentName = trim((string)($row['agent_name'] ?? ''));
                                    ?>
                                    <tr>
                                        <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                        <td>
                                            <a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo (int)$row['order_id']; ?>" class="booking-ref">
                                                <?php echo h($row['order_no'] ?: ('#' . $row['order_id'])); ?>
                                            </a>
                                        </td>
                                        <td><?php echo h($guestName ?: 'Guest'); ?></td>
                                        <td><?php echo h($agentName ?: 'Direct'); ?></td>
                                        <td><?php echo h($row['room_no'] ?: 'Unassigned'); ?></td>
                                        <td><strong><?php echo h($row['room_type'] ?: 'Not set'); ?></strong></td>
                                        <td>
                                            <div class="data-stack">
                                                <strong><?php echo h($row['check_in']); ?></strong>
                                                <small>to <?php echo h($row['check_out']); ?></small>
                                            </div>
                                        </td>
                                        <td class="text-end"><?php echo max(0, (int)$row['nights_in_range']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

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