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

$pageTitle = 'Revenue 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('report_table_exists')) {
    function report_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('report_column_exists')) {
    function report_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;
    }
}

$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);
$selectedType = trim((string)($_GET['tx_type'] ?? ''));
$groupBy = trim((string)($_GET['group_by'] ?? 'tx_type'));

$allowedGroupBy = ['tx_type', 'property', 'agent', 'date', 'folio_type'];
if (!in_array($groupBy, $allowedGroupBy, true)) {
    $groupBy = 'tx_type';
}

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 = ['folio_transactions', 'folios', 'orders', 'properties'];
foreach ($requiredTables as $table) {
    if (!report_table_exists($pdo, $table)) {
        $errors[] = 'Required table missing: ' . $table;
    }
}

$properties = [];
$txTypes = [];

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 {
        $txTypes = $pdo->query("
            SELECT DISTINCT tx_type
            FROM folio_transactions
            WHERE tx_type IS NOT NULL AND tx_type <> ''
            ORDER BY tx_type ASC
        ")->fetchAll(PDO::FETCH_COLUMN);
    } catch (Throwable $e) {
        $txTypes = [];
    }
}

/*
 * Porterage classification requested:
 * - tx_type contains "portrage"
 * - OR description contains "potrage"
 * - OR description contains "rage"
 *
 * Note: "Uses tblservices service 4358 or explicit porterage spellings to avoid false matches.
 */
$where = " DATE(ft.tx_date) BETWEEN :date_from AND :date_to "
       . " AND COALESCE(ft.is_voided, 0) = 0 ";
$params = [
    ':date_from' => $dateFrom,
    ':date_to'   => $dateTo,
];

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

if ($selectedType !== '') {
    $where .= " AND ft.tx_type = :tx_type ";
    $params[':tx_type'] = $selectedType;
}

// Full extras audit intentionally ignores the transaction-type filter and includes voided rows,
// so every non-room positive charge in the selected date/property range can be reviewed.
$extrasAuditWhere = " DATE(ft.tx_date) BETWEEN :audit_date_from AND :audit_date_to ";
$extrasAuditParams = [
    ':audit_date_from' => $dateFrom,
    ':audit_date_to'   => $dateTo,
];
if ($selectedPropertyId > 0) {
    $extrasAuditWhere .= " AND ft.property_id = :audit_property_id ";
    $extrasAuditParams[':audit_property_id'] = $selectedPropertyId;
}

$summary = [
    'total_charges' => 0,
    'room_revenue' => 0,
    'extras_revenue' => 0,
    'gratuities' => 0,
    'front_office_gratuities' => 0,
    'restaurant_gratuities' => 0,
    'curio_sales' => 0,
    'porterage' => 0,
    'payments' => 0,
    'net_balance' => 0,
    'transaction_count' => 0,
    'bookings_count' => 0,
];

$groupRows = [];
$topExtras = [];
$dailyRows = [];
$detailRows = [];
$extrasAuditRows = [];
$restaurantGratuityRows = [];
$restaurantGratuityTotal = 0.0;
$roomChargedRestaurantGratuity = 0.0;
$frontOfficeGratuityRows = [];
$frontOfficeGratuityTotal = 0.0;
$curioSaleRows = [];
$curioSalesTotal = 0.0;

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(ft.debit), 0) AS total_charges,
                COALESCE(SUM(CASE WHEN ft.tx_type = 'room_charge' THEN ft.debit ELSE 0 END), 0) AS room_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.tx_type <> 'room_charge'
                         AND ft.debit > 0
                         AND NOT (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                         AND NOT (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS extras_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS gratuities,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS porterage,
                COALESCE(SUM(ft.credit), 0) AS payments,
                COALESCE(SUM(ft.debit - ft.credit), 0) AS net_balance,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
        ");
        $stmt->execute($params);
        $summary = array_merge($summary, $stmt->fetch(PDO::FETCH_ASSOC) ?: []);
    } catch (Throwable $e) {
        $errors[] = 'Could not load report summary: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        switch ($groupBy) {
            case 'property':
                $groupSelect = "COALESCE(p.name, 'No Property')";
                break;
            case 'agent':
                $groupSelect = "COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))), ''), 'Direct / No Agent')";
                break;
            case 'date':
                $groupSelect = "DATE(ft.tx_date)";
                break;
            case 'folio_type':
                $groupSelect = "COALESCE(f.folio_type, 'unknown')";
                break;
            case 'tx_type':
            default:
                $groupSelect = "COALESCE(ft.tx_type, 'unknown')";
                break;
        }

        $stmt = $pdo->prepare("
            SELECT
                $groupSelect AS group_label,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(SUM(ft.debit), 0) AS total_charges,
                COALESCE(SUM(CASE WHEN ft.tx_type = 'room_charge' THEN ft.debit ELSE 0 END), 0) AS room_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.tx_type <> 'room_charge'
                         AND ft.debit > 0
                         AND NOT (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                         AND NOT (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS extras_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS gratuities,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS porterage,
                COALESCE(SUM(ft.credit), 0) AS payments,
                COALESCE(SUM(ft.debit - ft.credit), 0) AS net_balance
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = ft.property_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
            GROUP BY group_label
            ORDER BY group_label ASC
        ");
        $stmt->execute($params);
        $groupRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load grouped report: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                ft.tx_type,
                ft.description,
                COUNT(*) AS qty,
                COALESCE(SUM(ft.debit), 0) AS revenue
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
              AND ft.debit > 0
              AND ft.tx_type <> 'room_charge'
              AND NOT (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
              AND NOT (
                    LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                    AND (
                           LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                        OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                    )
                    AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
              )
            GROUP BY ft.tx_type, ft.description
            ORDER BY revenue DESC
            LIMIT 20
        ");
        $stmt->execute($params);
        $topExtras = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $topExtras = [];
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                DATE(ft.tx_date) AS revenue_date,
                COALESCE(SUM(ft.debit), 0) AS total_charges,
                COALESCE(SUM(CASE WHEN ft.tx_type = 'room_charge' THEN ft.debit ELSE 0 END), 0) AS room_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.tx_type <> 'room_charge'
                         AND ft.debit > 0
                         AND NOT (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                         AND NOT (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS extras_revenue,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
                                LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                                AND (
                                       LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                                    OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                                )
                                AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         )
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS gratuities,
                COALESCE(SUM(
                    CASE
                        WHEN ft.debit > 0
                         AND (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
)
                        THEN ft.debit
                        ELSE 0
                    END
                ), 0) AS porterage,
                COALESCE(SUM(ft.credit), 0) AS payments,
                COALESCE(SUM(ft.debit - ft.credit), 0) AS net_balance
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
            GROUP BY DATE(ft.tx_date)
            ORDER BY revenue_date ASC
        ");
        $stmt->execute($params);
        $dailyRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $dailyRows = [];
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                ft.id,
                ft.tx_date,
                ft.tx_type,
                ft.description,
                ft.debit,
                ft.credit,
                ft.order_id,
                ft.folio_id,
                o.order_no,
                o.check_in,
                o.check_out,
                p.name AS property_name,
                p.code AS property_code,
                f.folio_name,
                f.folio_type,
                TRIM(CONCAT(COALESCE(g.firstname,''), ' ', COALESCE(g.lastname,''))) AS guest_name,
                TRIM(CONCAT(COALESCE(a.firstname,''), ' ', COALESCE(a.lastname,''))) AS agent_name
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN guests g ON g.id = o.guest_id
            LEFT JOIN agents a ON a.id = o.agent_id
            LEFT JOIN properties p ON p.id = ft.property_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
            ORDER BY ft.tx_date DESC, ft.id DESC
            LIMIT 1000
        ");
        $stmt->execute($params);
        $detailRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load transaction detail: ' . $e->getMessage();
    }
}


if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                ft.id,
                ft.tx_date,
                ft.tx_type,
                ft.description,
                ft.source_table,
                ft.source_id,
                ft.debit,
                ft.credit,
                ft.is_voided,
                ft.order_id,
                ft.folio_id,
                o.order_no,
                p.name AS property_name,
                p.code AS property_code,
                f.folio_name,
                f.folio_type,
                CASE
                    WHEN COALESCE(ft.is_voided, 0) = 1 THEN 'Voided'
                    WHEN (
    (
        ft.source_table = 'tblservices'
        AND ft.source_id = 4358
    )
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.tx_type, '')) LIKE '%potrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%porterage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%portrage%'
    OR LOWER(COALESCE(ft.description, '')) LIKE '%potrage%'
) THEN 'Porterage'
                    WHEN LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra', 'extras', 'gratuity', 'gratuities')
                         AND (
                                LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                             OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                         )
                         AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%'
                         THEN 'Gratuity'
                    ELSE 'Extras'
                END AS assigned_category
            FROM folio_transactions ft
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN properties p ON p.id = ft.property_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $extrasAuditWhere
              AND ft.debit > 0
              AND LOWER(TRIM(COALESCE(ft.tx_type, ''))) <> 'room_charge'
            ORDER BY ft.tx_date DESC, ft.id DESC
        ");
        $stmt->execute($extrasAuditParams);
        $extrasAuditRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load full extras detail: ' . $e->getMessage();
    }
}


/*
 * Authoritative restaurant gratuity source
 * ---------------------------------------
 * Gratuity is entered as an active restaurant_order_items line and is part of
 * the final restaurant bill/payment.  Folio transaction descriptions are not
 * reliable enough to identify the gratuity portion of bundled room postings.
 *
 * Therefore:
 *   1. Gratuity totals come from closed restaurant orders.
 *   2. Room-charged gratuity is removed from Extras Revenue because that amount
 *      is already embedded in the restaurant folio posting.
 *   3. Cash/Card payments are left unchanged; they represent money collected.
 */
if (!$errors
    && report_table_exists($pdo, 'restaurant_orders')
    && report_table_exists($pdo, 'restaurant_order_items')) {
    try {
        $restaurantHasProperty = report_column_exists($pdo, 'restaurant_orders', 'property_id');

        $gratuityWhere = " ro.status = 'closed'
                           AND roi.status = 'active'
                           AND DATE(ro.closed_at) BETWEEN :gratuity_date_from AND :gratuity_date_to
                           AND LOWER(TRIM(COALESCE(roi.item_name, ''))) LIKE '%gratuity%'
                           AND UPPER(COALESCE(ro.order_no, '')) NOT LIKE 'FO%' ";
        $gratuityParams = [
            ':gratuity_date_from' => $dateFrom,
            ':gratuity_date_to' => $dateTo,
        ];

        if ($selectedPropertyId > 0 && $restaurantHasProperty) {
            $gratuityWhere .= " AND ro.property_id = :gratuity_property_id ";
            $gratuityParams[':gratuity_property_id'] = $selectedPropertyId;
        }

        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(roi.line_total), 0) AS gratuity_total,
                COALESCE(SUM(
                    CASE WHEN LOWER(TRIM(COALESCE(ro.payment_status, ''))) = 'room_charged'
                         THEN roi.line_total ELSE 0 END
                ), 0) AS room_charged_gratuity
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro
                ON ro.id = roi.restaurant_order_id
            WHERE $gratuityWhere
        ");
        $stmt->execute($gratuityParams);
        $restaurantGratuitySummary = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        $restaurantGratuityTotal = (float)($restaurantGratuitySummary['gratuity_total'] ?? 0);
        $roomChargedRestaurantGratuity = (float)($restaurantGratuitySummary['room_charged_gratuity'] ?? 0);

        // The folio query already identifies non-restaurant/front-office tips.
        // Keep that amount separate, then combine it with restaurant gratuities.
        $frontOfficeGratuityTotal = (float)($summary['gratuities'] ?? 0);
        $summary['front_office_gratuities'] = $frontOfficeGratuityTotal;
        $summary['restaurant_gratuities'] = $restaurantGratuityTotal;
        $summary['gratuities'] = $frontOfficeGratuityTotal + $restaurantGratuityTotal;

        // Room-posted restaurant gratuity is embedded in restaurant extras.
        $summary['extras_revenue'] = max(
            0,
            (float)$summary['extras_revenue'] - $roomChargedRestaurantGratuity
        );

        $propertySelect = $restaurantHasProperty
            ? "COALESCE(p.name, CONCAT('Property #', ro.property_id), 'No Property') AS property_name"
            : "'Restaurant' AS property_name";
        $propertyJoin = $restaurantHasProperty
            ? "LEFT JOIN properties p ON p.id = ro.property_id"
            : "";

        $stmt = $pdo->prepare("
            SELECT
                roi.id AS gratuity_item_id,
                roi.restaurant_order_id,
                ro.order_no,
                ro.closed_at,
                ro.payment_status,
                roi.item_name,
                roi.line_total,
                $propertySelect
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro
                ON ro.id = roi.restaurant_order_id
            $propertyJoin
            WHERE $gratuityWhere
            ORDER BY ro.closed_at DESC, roi.id DESC
        ");
        $stmt->execute($gratuityParams);
        $restaurantGratuityRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Correct daily gratuities and remove room-posted gratuity from daily extras.
        $stmt = $pdo->prepare("
            SELECT
                DATE(ro.closed_at) AS gratuity_date,
                COALESCE(SUM(roi.line_total), 0) AS gratuity_total,
                COALESCE(SUM(
                    CASE WHEN LOWER(TRIM(COALESCE(ro.payment_status, ''))) = 'room_charged'
                         THEN roi.line_total ELSE 0 END
                ), 0) AS room_charged_gratuity
            FROM restaurant_order_items roi
            INNER JOIN restaurant_orders ro
                ON ro.id = roi.restaurant_order_id
            WHERE $gratuityWhere
            GROUP BY DATE(ro.closed_at)
        ");
        $stmt->execute($gratuityParams);
        $dailyGratuities = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $gratuityDay) {
            $dailyGratuities[(string)$gratuityDay['gratuity_date']] = $gratuityDay;
        }

        foreach ($dailyRows as &$dailyRow) {
            $dayKey = (string)($dailyRow['revenue_date'] ?? '');
            $dayData = $dailyGratuities[$dayKey] ?? null;
            $dailyRow['gratuities'] = $dayData ? (float)$dayData['gratuity_total'] : 0.0;
            if ($dayData) {
                $dailyRow['extras_revenue'] = max(
                    0,
                    (float)$dailyRow['extras_revenue'] - (float)$dayData['room_charged_gratuity']
                );
            }
        }
        unset($dailyRow);

        // The folio grouping cannot reliably split bundled restaurant postings.
        // Clear its description-based gratuity values and add one authoritative row.
        foreach ($groupRows as &$groupRow) {
            $groupRow['gratuities'] = 0.0;
            if ($groupBy === 'tx_type'
                && strtolower(trim((string)($groupRow['group_label'] ?? ''))) === 'restaurant') {
                $groupRow['extras_revenue'] = max(
                    0,
                    (float)$groupRow['extras_revenue'] - $roomChargedRestaurantGratuity
                );
            }
        }
        unset($groupRow);

        if ($restaurantGratuityTotal > 0) {
            $groupRows[] = [
                'group_label' => 'Restaurant Gratuities',
                'transaction_count' => count($restaurantGratuityRows),
                'bookings_count' => count(array_unique(array_column($restaurantGratuityRows, 'restaurant_order_id'))),
                'total_charges' => 0,
                'room_revenue' => 0,
                'extras_revenue' => 0,
                'gratuities' => $restaurantGratuityTotal,
                'porterage' => 0,
                'payments' => 0,
                'net_balance' => 0,
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load restaurant gratuities: ' . $e->getMessage();
    }
}


/*
 * Front-office gratuities and curio sales
 * ---------------------------------------
 * Front-office gratuities remain sourced from explicit folio gratuity/tip
 * transactions that are not restaurant bundle descriptions.
 * Curio sales are restaurant-module orders whose order number starts with FO.
 */
if (!$errors) {
    try {
        $frontTipWhere = " DATE(ft.tx_date) BETWEEN :fo_tip_from AND :fo_tip_to
                           AND COALESCE(ft.is_voided, 0) = 0
                           AND ft.debit > 0
                           AND LOWER(TRIM(COALESCE(ft.tx_type, ''))) IN ('extra','extras','gratuity','gratuities')
                           AND (
                                  LOWER(COALESCE(ft.description, '')) LIKE '%tip%'
                               OR LOWER(COALESCE(ft.description, '')) LIKE '%gratuity%'
                           )
                           AND LOWER(TRIM(COALESCE(ft.description, ''))) NOT LIKE 'restaurant%' ";
        $frontTipParams = [':fo_tip_from' => $dateFrom, ':fo_tip_to' => $dateTo];
        if ($selectedPropertyId > 0) {
            $frontTipWhere .= " AND ft.property_id = :fo_tip_property_id ";
            $frontTipParams[':fo_tip_property_id'] = $selectedPropertyId;
        }
        $stmt = $pdo->prepare("SELECT ft.id, ft.tx_date, ft.tx_type, ft.description, ft.debit,
                                      ft.order_id, o.order_no, p.name AS property_name,
                                      f.folio_name
                               FROM folio_transactions ft
                               LEFT JOIN orders o ON o.id = ft.order_id
                               LEFT JOIN properties p ON p.id = ft.property_id
                               LEFT JOIN folios f ON f.id = ft.folio_id
                               WHERE $frontTipWhere
                               ORDER BY ft.tx_date DESC, ft.id DESC");
        $stmt->execute($frontTipParams);
        $frontOfficeGratuityRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $frontOfficeGratuityTotal = array_sum(array_map(static function ($r) {
            return (float)($r['debit'] ?? 0);
        }, $frontOfficeGratuityRows));
        $summary['front_office_gratuities'] = $frontOfficeGratuityTotal;
        $summary['gratuities'] = $frontOfficeGratuityTotal + (float)$summary['restaurant_gratuities'];
    } catch (Throwable $e) {
        $errors[] = 'Could not load front-office gratuities: ' . $e->getMessage();
    }
}

if (!$errors
    && report_table_exists($pdo, 'restaurant_orders')
    && report_table_exists($pdo, 'restaurant_order_items')) {
    try {
        $restaurantHasProperty = report_column_exists($pdo, 'restaurant_orders', 'property_id');
        $curioWhere = " ro.status = 'closed'
                        AND roi.status = 'active'
                        AND UPPER(COALESCE(ro.order_no, '')) LIKE 'FO%'
                        AND DATE(ro.closed_at) BETWEEN :curio_from AND :curio_to
                        AND LOWER(TRIM(COALESCE(roi.item_name, ''))) NOT LIKE '%gratuity%' ";
        $curioParams = [':curio_from' => $dateFrom, ':curio_to' => $dateTo];
        if ($selectedPropertyId > 0 && $restaurantHasProperty) {
            $curioWhere .= " AND ro.property_id = :curio_property_id ";
            $curioParams[':curio_property_id'] = $selectedPropertyId;
        }
        $propertySelect = $restaurantHasProperty
            ? "COALESCE(p.name, CONCAT('Property #', ro.property_id), 'No Property') AS property_name"
            : "'Front Office' AS property_name";
        $propertyJoin = $restaurantHasProperty ? "LEFT JOIN properties p ON p.id = ro.property_id" : "";
        $stmt = $pdo->prepare("SELECT roi.id AS order_item_id, roi.restaurant_order_id,
                                      ro.order_no, ro.closed_at, ro.payment_status,
                                      roi.item_name, roi.qty, roi.line_total, $propertySelect
                               FROM restaurant_order_items roi
                               INNER JOIN restaurant_orders ro ON ro.id = roi.restaurant_order_id
                               $propertyJoin
                               WHERE $curioWhere
                               ORDER BY ro.closed_at DESC, roi.id DESC");
        $stmt->execute($curioParams);
        $curioSaleRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $curioSalesTotal = array_sum(array_map(static function ($r) {
            return (float)($r['line_total'] ?? 0);
        }, $curioSaleRows));
        $summary['curio_sales'] = $curioSalesTotal;
        if ($curioSalesTotal > 0) {
            $groupRows[] = [
                'group_label' => 'Front Office Curio Sales',
                'transaction_count' => count($curioSaleRows),
                'bookings_count' => count(array_unique(array_column($curioSaleRows, 'restaurant_order_id'))),
                'total_charges' => 0,
                'room_revenue' => 0,
                'extras_revenue' => $curioSalesTotal,
                'gratuities' => 0,
                'porterage' => 0,
                'payments' => 0,
                'net_balance' => 0,
            ];
        }
    } catch (Throwable $e) {
        $errors[] = 'Could not load front-office curio sales: ' . $e->getMessage();
    }
}

$summary['charge_collection_percent'] = ((float)$summary['total_charges'] > 0)
    ? (((float)$summary['payments'] / (float)$summary['total_charges']) * 100)
    : 0;
$summary['room_revenue_percent'] = ((float)$summary['total_charges'] > 0)
    ? (((float)$summary['room_revenue'] / (float)$summary['total_charges']) * 100)
    : 0;
$summary['extras_revenue_percent'] = ((float)$summary['total_charges'] > 0)
    ? (((float)$summary['extras_revenue'] / (float)$summary['total_charges']) * 100)
    : 0;
$summary['gratuities_percent'] = ((float)$summary['total_charges'] > 0)
    ? (((float)$summary['gratuities'] / (float)$summary['total_charges']) * 100)
    : 0;
$summary['porterage_percent'] = ((float)$summary['total_charges'] > 0)
    ? (((float)$summary['porterage'] / (float)$summary['total_charges']) * 100)
    : 0;
$summary['average_transaction'] = ((int)$summary['transaction_count'] > 0)
    ? ((float)$summary['total_charges'] / (int)$summary['transaction_count'])
    : 0;
$summary['average_booking_revenue'] = ((int)$summary['bookings_count'] > 0)
    ? ((float)$summary['total_charges'] / (int)$summary['bookings_count'])
    : 0;

$maxDailyTotal = 0.0;
$bestRevenueDay = '';
foreach ($dailyRows as $dr) {
    $dailyTotal = (float)($dr['total_charges'] ?? ((float)($dr['room_revenue'] ?? 0) + (float)($dr['extras_revenue'] ?? 0) + (float)($dr['gratuities'] ?? 0) + (float)($dr['porterage'] ?? 0)));
    if ($dailyTotal > $maxDailyTotal) {
        $maxDailyTotal = $dailyTotal;
        $bestRevenueDay = (string)($dr['revenue_date'] ?? '');
    }
}
$summary['best_revenue_day'] = $bestRevenueDay;
$summary['best_revenue_day_total'] = $maxDailyTotal;

$revenueMixRows = [
    [
        'label' => 'Room Revenue',
        'amount' => (float)$summary['room_revenue'],
        'percent' => (float)$summary['room_revenue_percent'],
        'icon' => 'bi-building',
        'class' => 'primary',
    ],
    [
        'label' => 'Extras Revenue',
        'amount' => (float)$summary['extras_revenue'],
        'percent' => (float)$summary['extras_revenue_percent'],
        'icon' => 'bi-bag-plus',
        'class' => 'warning',
    ],
    [
        'label' => 'Gratuities',
        'amount' => (float)$summary['gratuities'],
        'percent' => (float)$summary['gratuities_percent'],
        'icon' => 'bi-heart',
        'class' => 'success',
    ],
    [
        'label' => 'Front Office Tips',
        'amount' => (float)$summary['front_office_gratuities'],
        'percent' => (float)$summary['total_charges'] > 0 ? ((float)$summary['front_office_gratuities'] / (float)$summary['total_charges']) * 100 : 0,
        'icon' => 'bi-person-heart',
        'class' => 'success',
    ],
    [
        'label' => 'Restaurant Gratuities',
        'amount' => (float)$summary['restaurant_gratuities'],
        'percent' => (float)$summary['total_charges'] > 0 ? ((float)$summary['restaurant_gratuities'] / (float)$summary['total_charges']) * 100 : 0,
        'icon' => 'bi-cup-hot',
        'class' => 'success',
    ],
    [
        'label' => 'Curio Sales (FO)',
        'amount' => (float)$summary['curio_sales'],
        'percent' => (float)$summary['total_charges'] > 0 ? ((float)$summary['curio_sales'] / (float)$summary['total_charges']) * 100 : 0,
        'icon' => 'bi-shop',
        'class' => 'warning',
    ],
    [
        'label' => 'Porterage',
        'amount' => (float)$summary['porterage'],
        'percent' => (float)$summary['porterage_percent'],
        'icon' => 'bi-luggage',
        'class' => 'info',
    ],
    [
        'label' => 'Payments Collected',
        'amount' => (float)$summary['payments'],
        'percent' => (float)$summary['charge_collection_percent'],
        'icon' => 'bi-cash-stack',
        'class' => 'success',
    ],
    [
        'label' => 'Net Balance Impact',
        'amount' => (float)$summary['net_balance'],
        'percent' => (float)$summary['total_charges'] > 0 ? ((float)$summary['net_balance'] / (float)$summary['total_charges']) * 100 : 0,
        'icon' => 'bi-exclamation-circle',
        'class' => 'danger',
    ],
];



if (isset($_GET['export']) && in_array((string)$_GET['export'], ['xls','excel'], true) && !$errors) {
    $filename = 'revenue_report_full_' . date('Ymd_His') . '.xls';

    header('Content-Type: application/vnd.ms-excel; charset=utf-8');
    header('Content-Disposition: attachment; filename="' . $filename . '"');
    header('Pragma: no-cache');
    header('Expires: 0');

    $xlsText = static function ($value): string {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    };
    $xlsNum = static function ($value, int $decimals = 2): string {
        return number_format((float)$value, $decimals, '.', '');
    };

    echo "<!DOCTYPE html>\n<html>\n<head>\n<meta charset=\"utf-8\">\n";
    echo "<style>\n";
    echo "body{font-family:Arial,Helvetica,sans-serif;color:#111827;}\n";
    echo "h1{font-size:22px;margin:0 0 4px 0;color:#0f172a;}\n";
    echo "h2{font-size:15px;margin:20px 0 8px 0;color:#1f2937;background:#e5e7eb;padding:6px 8px;}\n";
    echo "table{border-collapse:collapse;margin-bottom:14px;}\n";
    echo "th{background:#1f2937;color:#ffffff;font-weight:bold;border:1px solid #9ca3af;padding:6px;}\n";
    echo "td{border:1px solid #d1d5db;padding:5px;vertical-align:top;}\n";
    echo ".title{background:#0f172a;color:#ffffff;padding:12px;}\n";
    echo ".subtitle{color:#e5e7eb;font-size:11px;}\n";
    echo ".metric th{background:#334155;} .num{text-align:right;} .good{background:#dcfce7;} .warn{background:#fef3c7;} .bad{background:#fee2e2;}\n";
    echo ".barwrap{width:180px;background:#e5e7eb;height:13px;} .bar{background:#2563eb;height:13px;}\n";
    echo "</style>\n</head>\n<body>\n";

    echo '<table style="width:100%;"><tr><td class="title" colspan="12">';
    echo '<h1>Revenue Report</h1>';
    echo '<div class="subtitle">Period: ' . $xlsText($dateFrom) . ' to ' . $xlsText($dateTo) . ' | Grouped by: ' . $xlsText(ucwords(str_replace('_', ' ', $groupBy))) . ' | Exported: ' . date('Y-m-d H:i:s') . '</div>';
    echo '</td></tr></table>';

    echo '<h2>Executive Summary</h2>';
    echo '<table class="metric"><tr>';
    echo '<th>Room Revenue</th><th>Extras Revenue</th><th>Gratuities</th><th>Porterage</th><th>Total Charges</th><th>Payments</th><th>Net Balance</th><th>Collection %</th><th>Bookings</th><th>Transactions</th><th>Avg Transaction</th><th>Avg Booking Revenue</th><th>Best Revenue Day</th><th>Best Day Revenue</th>';
    echo '</tr><tr>';
    echo '<td class="num">' . $xlsNum($summary['room_revenue']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['extras_revenue']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['gratuities']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['porterage']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['total_charges']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['payments']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['net_balance']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['charge_collection_percent']) . '%</td>';
    echo '<td class="num">' . (int)$summary['bookings_count'] . '</td>';
    echo '<td class="num">' . (int)$summary['transaction_count'] . '</td>';
    echo '<td class="num">' . $xlsNum($summary['average_transaction']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['average_booking_revenue']) . '</td>';
    echo '<td>' . $xlsText($summary['best_revenue_day']) . '</td>';
    echo '<td class="num">' . $xlsNum($summary['best_revenue_day_total']) . '</td>';
    echo '</tr></table>';

    echo '<h2>Revenue Mix Analysis</h2>';
    echo '<table><tr><th>Metric</th><th>Amount</th><th>% of Charges</th><th>Visual Bar</th></tr>';
    foreach ($revenueMixRows as $mix) {
        $pct = max(0, min(100, abs((float)$mix['percent'])));
        echo '<tr><td>' . $xlsText($mix['label']) . '</td><td class="num">' . $xlsNum($mix['amount']) . '</td><td class="num">' . $xlsNum($mix['percent']) . '%</td><td><div class="barwrap"><div class="bar" style="width:' . $xlsNum($pct) . '%"></div></div></td></tr>';
    }
    echo '</table>';

    echo '<h2>Grouped Revenue Summary</h2>';
    echo '<table><tr><th>Group</th><th>Bookings</th><th>Transactions</th><th>Room Revenue</th><th>Extras Revenue</th><th>Gratuities</th><th>Porterage</th><th>Total Charges</th><th>Payments</th><th>Net Balance</th></tr>';
    foreach ($groupRows as $row) {
        echo '<tr><td>' . $xlsText(ucwords(str_replace('_', ' ', (string)$row['group_label']))) . '</td><td class="num">' . (int)$row['bookings_count'] . '</td><td class="num">' . (int)$row['transaction_count'] . '</td><td class="num">' . $xlsNum($row['room_revenue']) . '</td><td class="num">' . $xlsNum($row['extras_revenue']) . '</td><td class="num">' . $xlsNum($row['gratuities']) . '</td><td class="num">' . $xlsNum($row['porterage']) . '</td><td class="num">' . $xlsNum($row['total_charges']) . '</td><td class="num">' . $xlsNum($row['payments']) . '</td><td class="num">' . $xlsNum($row['net_balance']) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Daily Revenue Movement</h2>';
    echo '<table><tr><th>Date</th><th>Total Charges</th><th>Room Revenue</th><th>Extras Revenue</th><th>Gratuities</th><th>Porterage</th><th>Payments</th><th>Net Balance</th><th>Revenue Bar</th></tr>';
    $maxTotal = max(1, (float)$summary['best_revenue_day_total']);
    foreach ($dailyRows as $row) {
        $total = (float)($row['total_charges'] ?? 0);
        $pct = min(100, ($total / $maxTotal) * 100);
        echo '<tr><td>' . $xlsText($row['revenue_date']) . '</td><td class="num">' . $xlsNum($total) . '</td><td class="num">' . $xlsNum($row['room_revenue']) . '</td><td class="num">' . $xlsNum($row['extras_revenue']) . '</td><td class="num">' . $xlsNum($row['gratuities']) . '</td><td class="num">' . $xlsNum($row['porterage']) . '</td><td class="num">' . $xlsNum($row['payments']) . '</td><td class="num">' . $xlsNum($row['net_balance']) . '</td><td><div class="barwrap"><div class="bar" style="width:' . $xlsNum($pct) . '%"></div></div></td></tr>';
    }
    echo '</table>';

    echo '<h2>Top Extras / Service Charges</h2>';
    echo '<table><tr><th>Type</th><th>Description</th><th>Qty</th><th>Revenue</th></tr>';
    foreach ($topExtras as $row) {
        echo '<tr><td>' . $xlsText(ucwords(str_replace('_', ' ', (string)$row['tx_type']))) . '</td><td>' . $xlsText($row['description']) . '</td><td class="num">' . (int)$row['qty'] . '</td><td class="num">' . $xlsNum($row['revenue']) . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Full Extras Classification Detail</h2>';
    echo '<table><tr><th>Date</th><th>Assigned Category</th><th>Raw Type</th><th>Description</th><th>Source Table</th><th>Source ID</th><th>Property</th><th>Booking</th><th>Folio</th><th>Debit</th><th>Voided</th></tr>';
    foreach ($extrasAuditRows as $row) {
        $property = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) {
            $property .= ' (' . $row['property_code'] . ')';
        }
        echo '<tr><td>' . $xlsText($row['tx_date']) . '</td><td>' . $xlsText($row['assigned_category']) . '</td><td>' . $xlsText($row['tx_type']) . '</td><td>' . $xlsText($row['description']) . '</td><td>' . $xlsText($row['source_table']) . '</td><td>' . $xlsText($row['source_id']) . '</td><td>' . $xlsText($property) . '</td><td>' . $xlsText($row['order_no'] ?: ('#' . $row['order_id'])) . '</td><td>' . $xlsText($row['folio_name']) . '</td><td class="num">' . $xlsNum($row['debit']) . '</td><td>' . ((int)$row['is_voided'] === 1 ? 'Yes' : 'No') . '</td></tr>';
    }
    echo '</table>';

    echo '<h2>Transaction Detail</h2>';
    echo '<table><tr><th>Date</th><th>Type</th><th>Description</th><th>Property</th><th>Booking</th><th>Guest</th><th>Agent</th><th>Folio</th><th>Folio Type</th><th>Debit</th><th>Credit</th><th>Balance Impact</th></tr>';
    foreach ($detailRows as $row) {
        $property = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) {
            $property .= ' (' . $row['property_code'] . ')';
        }
        echo '<tr><td>' . $xlsText($row['tx_date']) . '</td><td>' . $xlsText($row['tx_type']) . '</td><td>' . $xlsText($row['description']) . '</td><td>' . $xlsText($property) . '</td><td>' . $xlsText($row['order_no'] ?: ('#' . $row['order_id'])) . '</td><td>' . $xlsText(trim((string)$row['guest_name'])) . '</td><td>' . $xlsText(trim((string)$row['agent_name'])) . '</td><td>' . $xlsText($row['folio_name']) . '</td><td>' . $xlsText($row['folio_type']) . '</td><td class="num">' . $xlsNum($row['debit']) . '</td><td class="num">' . $xlsNum($row['credit']) . '</td><td class="num">' . $xlsNum((float)$row['debit'] - (float)$row['credit']) . '</td></tr>';
    }
    echo '</table>';

    echo "</body></html>";
    exit;
}


if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'revenue_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, ['Revenue Report', $dateFrom . ' to ' . $dateTo]);
    fputcsv($out, []);
    fputcsv($out, ['Executive Summary']);
    fputcsv($out, ['Metric', 'Value']);
    foreach ([
        'Room Revenue' => $summary['room_revenue'],
        'Extras Revenue' => $summary['extras_revenue'],
        'Gratuities' => $summary['gratuities'],
        'Porterage' => $summary['porterage'],
        'Total Charges' => $summary['total_charges'],
        'Payments' => $summary['payments'],
        'Net Balance' => $summary['net_balance'],
        'Collection %' => number_format((float)$summary['charge_collection_percent'], 2, '.', '') . '%',
        'Bookings' => $summary['bookings_count'],
        'Transactions' => $summary['transaction_count'],
        'Average Transaction' => $summary['average_transaction'],
        'Average Booking Revenue' => $summary['average_booking_revenue'],
        'Best Revenue Day' => $summary['best_revenue_day'],
        'Best Revenue Day Total' => $summary['best_revenue_day_total'],
    ] as $label => $value) {
        fputcsv($out, [$label, $value]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Revenue Mix']);
    fputcsv($out, ['Metric', 'Amount', 'Percent']);
    foreach ($revenueMixRows as $mix) {
        fputcsv($out, [$mix['label'], number_format((float)$mix['amount'], 2, '.', ''), number_format((float)$mix['percent'], 2, '.', '')]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Grouped Revenue Summary']);
    fputcsv($out, ['Group', 'Bookings', 'Transactions', 'Room Revenue', 'Extras Revenue', 'Gratuities', 'Porterage', 'Total Charges', 'Payments', 'Net Balance']);
    foreach ($groupRows as $row) {
        fputcsv($out, [
            $row['group_label'],
            $row['bookings_count'],
            $row['transaction_count'],
            number_format((float)$row['room_revenue'], 2, '.', ''),
            number_format((float)$row['extras_revenue'], 2, '.', ''),
            number_format((float)$row['gratuities'], 2, '.', ''),
            number_format((float)$row['porterage'], 2, '.', ''),
            number_format((float)$row['total_charges'], 2, '.', ''),
            number_format((float)$row['payments'], 2, '.', ''),
            number_format((float)$row['net_balance'], 2, '.', ''),
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Daily Revenue Movement']);
    fputcsv($out, ['Date', 'Total Charges', 'Room Revenue', 'Extras Revenue', 'Gratuities', 'Porterage', 'Payments', 'Balance Impact']);
    foreach ($dailyRows as $row) {
        fputcsv($out, [
            $row['revenue_date'],
            number_format((float)($row['total_charges'] ?? 0), 2, '.', ''),
            number_format((float)$row['room_revenue'], 2, '.', ''),
            number_format((float)$row['extras_revenue'], 2, '.', ''),
            number_format((float)$row['gratuities'], 2, '.', ''),
            number_format((float)$row['porterage'], 2, '.', ''),
            number_format((float)$row['payments'], 2, '.', ''),
            number_format((float)$row['net_balance'], 2, '.', ''),
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Top Extras / Service Charges']);
    fputcsv($out, ['Type', 'Description', 'Qty', 'Revenue']);
    foreach ($topExtras as $row) {
        fputcsv($out, [
            $row['tx_type'],
            $row['description'],
            $row['qty'],
            number_format((float)$row['revenue'], 2, '.', ''),
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Full Extras Classification Detail']);
    fputcsv($out, ['Date', 'Assigned Category', 'Raw Type', 'Description', 'Source Table', 'Source ID', 'Property', 'Booking', 'Folio', 'Debit', 'Voided']);
    foreach ($extrasAuditRows as $row) {
        $property = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) {
            $property .= ' (' . $row['property_code'] . ')';
        }
        fputcsv($out, [
            $row['tx_date'],
            $row['assigned_category'],
            $row['tx_type'],
            $row['description'],
            $row['source_table'],
            $row['source_id'],
            $property,
            $row['order_no'] ?: ('#' . $row['order_id']),
            $row['folio_name'],
            number_format((float)$row['debit'], 2, '.', ''),
            (int)$row['is_voided'] === 1 ? 'Yes' : 'No',
        ]);
    }

    fputcsv($out, []);
    fputcsv($out, ['Transaction Detail']);
    fputcsv($out, [
        'Date',
        'Type',
        'Description',
        'Property',
        'Booking',
        'Guest',
        'Agent',
        'Folio',
        'Folio Type',
        'Debit',
        'Credit',
        'Balance Impact'
    ]);

    foreach ($detailRows as $row) {
        $property = trim((string)($row['property_name'] ?? ''));
        if (!empty($row['property_code'])) {
            $property .= ' (' . $row['property_code'] . ')';
        }

        fputcsv($out, [
            $row['tx_date'],
            $row['tx_type'],
            $row['description'],
            $property,
            $row['order_no'] ?: ('#' . $row['order_id']),
            trim((string)$row['guest_name']),
            trim((string)$row['agent_name']),
            $row['folio_name'],
            $row['folio_type'],
            number_format((float)$row['debit'], 2, '.', ''),
            number_format((float)$row['credit'], 2, '.', ''),
            number_format(((float)$row['debit'] - (float)$row['credit']), 2, '.', ''),
        ]);
    }

    fclose($out);
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>
<style>
    .hero-band.rev-compact-hero {
        padding: 14px 18px !important;
        margin-bottom: 14px !important;
        border-radius: 16px !important;
    }
    .rev-compact-hero .hero-title {
        font-size: 1.35rem !important;
        line-height: 1.15 !important;
        margin-top: .15rem !important;
    }
    .rev-compact-hero .hero-subtitle {
        font-size: .86rem !important;
        margin-top: .25rem !important;
        max-width: 980px;
    }
    .rev-compact-hero .page-eyebrow {
        font-size: .72rem !important;
        letter-spacing: .08em;
        text-transform: uppercase;
    }
    .rev-compact-hero .btn { padding: .42rem .75rem; }
    .analysis-card {
        border: 1px solid rgba(148, 163, 184, .25);
        border-radius: 16px;
        padding: 14px;
        background: linear-gradient(180deg, #fff, #f8fafc);
        height: 100%;
    }
    .analysis-label {
        font-size: .76rem;
        color: #64748b;
        text-transform: uppercase;
        letter-spacing: .06em;
        font-weight: 700;
    }
    .analysis-value {
        font-size: 1.25rem;
        font-weight: 800;
        color: #0f172a;
        margin-top: 2px;
    }
    .analysis-bar-wrap {
        height: 9px;
        background: #e5e7eb;
        border-radius: 999px;
        overflow: hidden;
        margin-top: 10px;
    }
    .analysis-bar {
        height: 9px;
        border-radius: 999px;
        background: #2563eb;
    }
    .daily-visual-bar {
        min-width: 120px;
        height: 8px;
        background: #e5e7eb;
        border-radius: 999px;
        overflow: hidden;
    }
    .daily-visual-bar span {
        display: block;
        height: 8px;
        border-radius: 999px;
        background: #2563eb;
    }
    .detail-scroll {
        max-height: 620px;
        overflow: auto;
    }
    .detail-scroll thead th {
        position: sticky;
        top: 0;
        z-index: 4;
        background: #f8fafc;
        box-shadow: inset 0 -1px 0 rgba(15, 23, 42, .12), 0 2px 4px rgba(15, 23, 42, .06);
    }
</style>


<div class="hero-band rev-compact-hero">
    <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-arrow"></i> Management Reports</div>
            <div class="hero-title mt-2">Revenue Report</div>
            <div class="hero-subtitle mt-2">
                Track accommodation revenue, extras, gratuities, porterage, payments, balances, and charge performance from folio transactions.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <div class="d-flex flex-wrap gap-2 justify-content-lg-end">
                <a href="revenue_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'xls']))); ?>" class="btn btn-light">
                    <i class="bi bi-file-earmark-excel me-2"></i>Export Full Excel
                </a>
                <a href="revenue_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-outline-light">
                    <i class="bi bi-download me-2"></i>CSV
                </a>
            </div>
        </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-2">
                <label class="form-label">Transaction Type</label>
                <select name="tx_type" class="form-select">
                    <option value="">All types</option>
                    <?php foreach ($txTypes as $type): ?>
                        <option value="<?php echo h($type); ?>" <?php echo $selectedType === (string)$type ? 'selected' : ''; ?>>
                            <?php echo h(ucwords(str_replace('_', ' ', (string)$type))); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-2">
                <label class="form-label">Group By</label>
                <select name="group_by" class="form-select">
                    <option value="tx_type" <?php echo $groupBy === 'tx_type' ? 'selected' : ''; ?>>Transaction Type</option>
                    <option value="property" <?php echo $groupBy === 'property' ? 'selected' : ''; ?>>Property</option>
                    <option value="agent" <?php echo $groupBy === 'agent' ? 'selected' : ''; ?>>Agent</option>
                    <option value="date" <?php echo $groupBy === 'date' ? 'selected' : ''; ?>>Date</option>
                    <option value="folio_type" <?php echo $groupBy === 'folio_type' ? 'selected' : ''; ?>>Folio Type</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-primary-subtle text-primary"><i class="bi bi-building"></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-warning-subtle text-warning"><i class="bi bi-bag-plus"></i></div>
            <div>
                <div class="metric-label">Extras Revenue</div>
                <div class="metric-value"><?php echo money_fmt($summary['extras_revenue']); ?></div>
            </div>
        </div>
    </div>

    <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-heart"></i></div>
            <div>
                <div class="metric-label">Gratuities</div>
                <div class="metric-value"><?php echo money_fmt($summary['gratuities']); ?></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-luggage"></i></div>
            <div>
                <div class="metric-label">Porterage</div>
                <div class="metric-value"><?php echo money_fmt($summary['porterage']); ?></div>
            </div>
        </div>
    </div>

    <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-stack"></i></div>
            <div>
                <div class="metric-label">Payments</div>
                <div class="metric-value"><?php echo money_fmt($summary['payments']); ?></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-calculator"></i></div>
            <div>
                <div class="metric-label">Total Charges</div>
                <div class="metric-value"><?php echo money_fmt($summary['total_charges']); ?></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-exclamation-circle"></i></div>
            <div>
                <div class="metric-label">Net Balance</div>
                <div class="metric-value"><?php echo money_fmt($summary['net_balance']); ?></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-journal-check"></i></div>
            <div>
                <div class="metric-label">Bookings</div>
                <div class="metric-value"><?php echo (int)$summary['bookings_count']; ?></div>
            </div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-3">
        <div class="analysis-card">
            <div class="analysis-label">Collection Ratio</div>
            <div class="analysis-value"><?php echo money_fmt($summary['charge_collection_percent']); ?>%</div>
            <div class="small text-muted">Payments collected vs total charges</div>
            <div class="analysis-bar-wrap"><div class="analysis-bar" style="width:<?php echo h((string)min(100, max(0, (float)$summary['charge_collection_percent']))); ?>%"></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="analysis-card">
            <div class="analysis-label">Room Revenue Mix</div>
            <div class="analysis-value"><?php echo money_fmt($summary['room_revenue_percent']); ?>%</div>
            <div class="small text-muted">Accommodation share of all charges</div>
            <div class="analysis-bar-wrap"><div class="analysis-bar" style="width:<?php echo h((string)min(100, max(0, (float)$summary['room_revenue_percent']))); ?>%"></div></div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="analysis-card">
            <div class="analysis-label">Average Transaction</div>
            <div class="analysis-value"><?php echo money_fmt($summary['average_transaction']); ?></div>
            <div class="small text-muted"><?php echo (int)$summary['transaction_count']; ?> transactions in range</div>
        </div>
    </div>
    <div class="col-md-6 col-xl-3">
        <div class="analysis-card">
            <div class="analysis-label">Best Revenue Day</div>
            <div class="analysis-value"><?php echo h($summary['best_revenue_day'] ?: '—'); ?></div>
            <div class="small text-muted"><?php echo money_fmt($summary['best_revenue_day_total']); ?></div>
        </div>
    </div>
</div>

<div class="row g-4 mb-4">
    <?php foreach ($revenueMixRows as $mix): ?>
        <?php $mixPct = min(100, max(0, abs((float)$mix['percent']))); ?>
        <div class="col-md-6 col-xl-3">
            <div class="analysis-card">
                <div class="d-flex align-items-center justify-content-between">
                    <div>
                        <div class="analysis-label"><?php echo h($mix['label']); ?></div>
                        <div class="analysis-value"><?php echo money_fmt($mix['amount']); ?></div>
                    </div>
                    <div class="metric-icon bg-<?php echo h($mix['class']); ?>-subtle text-<?php echo h($mix['class']); ?>">
                        <i class="bi <?php echo h($mix['icon']); ?>"></i>
                    </div>
                </div>
                <div class="small text-muted mt-1"><?php echo money_fmt($mix['percent']); ?>% of total charges</div>
                <div class="analysis-bar-wrap"><div class="analysis-bar" style="width:<?php echo h((string)$mixPct); ?>%"></div></div>
            </div>
        </div>
    <?php endforeach; ?>
</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">Grouped Revenue Summary</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive detail-scroll">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Group</th>
                                <th class="text-end">Bookings</th>
                                <th class="text-end">Transactions</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">Extras</th>
                                <th class="text-end">Gratuities</th>
                                <th class="text-end">Porterage</th>
                                <th class="text-end">Total Charges</th>
                                <th class="text-end">Payments</th>
                                <th class="text-end">Balance</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$groupRows): ?>
                                <tr><td colspan="10" class="text-center text-muted py-4">No data found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($groupRows as $row): ?>
                                    <tr>
                                        <td><strong><?php echo h(ucwords(str_replace('_', ' ', (string)$row['group_label']))); ?></strong></td>
                                        <td class="text-end"><?php echo (int)$row['bookings_count']; ?></td>
                                        <td class="text-end"><?php echo (int)$row['transaction_count']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['room_revenue']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['extras_revenue']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['gratuities']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['porterage']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['total_charges']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['payments']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['net_balance']); ?></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">Top Extras / Service Charges (Excludes Gratuities &amp; Porterage)</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Type</th>
                                <th>Description</th>
                                <th class="text-end">Qty</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$topExtras): ?>
                                <tr><td colspan="4" class="text-center text-muted py-4">No extras found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($topExtras as $row): ?>
                                    <tr>
                                        <td><?php echo h(ucwords(str_replace('_', ' ', (string)$row['tx_type']))); ?></td>
                                        <td><?php echo h($row['description']); ?></td>
                                        <td class="text-end"><?php echo (int)$row['qty']; ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['revenue']); ?></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">Daily Revenue Movement</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">Total Charges</th>
                                <th>Visual</th>
                                <th class="text-end">Room Revenue</th>
                                <th class="text-end">Extras Revenue</th>
                                <th class="text-end">Gratuities</th>
                                <th class="text-end">Porterage</th>
                                <th class="text-end">Payments</th>
                                <th class="text-end">Balance Impact</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$dailyRows): ?>
                                <tr><td colspan="9" class="text-center text-muted py-4">No daily movement found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($dailyRows as $row): ?>
                                    <tr>
                                        <td><strong><?php echo h($row['revenue_date']); ?></strong></td>
                                        <?php
                                        $dailyTotal = (float)($row['total_charges'] ?? 0);
                                        $dailyPct = (float)$summary['best_revenue_day_total'] > 0 ? min(100, ($dailyTotal / (float)$summary['best_revenue_day_total']) * 100) : 0;
                                        ?>
                                        <td class="text-end fw-bold"><?php echo money_fmt($dailyTotal); ?></td>
                                        <td><div class="daily-visual-bar"><span style="width:<?php echo h((string)$dailyPct); ?>%"></span></div></td>
                                        <td class="text-end"><?php echo money_fmt($row['room_revenue']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['extras_revenue']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['gratuities']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['porterage']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['payments']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['net_balance']); ?></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">
                <div>
                    <h5 class="mb-0">Front Office Gratuity / Tip Detail</h5>
                    <div class="small text-muted">Guest tips entered at reception or front office, separate from restaurant gratuities.</div>
                </div>
                <span class="badge text-bg-success"><?php echo count($frontOfficeGratuityRows); ?> lines · <?php echo money_fmt($frontOfficeGratuityTotal); ?></span>
            </div>
            <div class="card-body p-0"><div class="table-responsive detail-scroll">
                <table class="table table-modern align-middle mb-0">
                    <thead><tr><th>Date</th><th>Property</th><th>Booking</th><th>Folio</th><th>Description</th><th class="text-end">Tip</th></tr></thead>
                    <tbody>
                    <?php if (!$frontOfficeGratuityRows): ?>
                        <tr><td colspan="6" class="text-center text-muted py-4">No front-office gratuities found.</td></tr>
                    <?php else: foreach ($frontOfficeGratuityRows as $tip): ?>
                        <tr><td><?php echo h($tip['tx_date']); ?></td><td><?php echo h($tip['property_name'] ?? 'Not set'); ?></td><td><?php echo h($tip['order_no'] ?: ('#' . $tip['order_id'])); ?></td><td><?php echo h($tip['folio_name']); ?></td><td><?php echo h($tip['description']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($tip['debit']); ?></td></tr>
                    <?php endforeach; 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">
                <div>
                    <h5 class="mb-0">Front Office Curio Sales</h5>
                    <div class="small text-muted">Active non-gratuity items from closed restaurant-module orders whose order number starts with FO.</div>
                </div>
                <span class="badge text-bg-warning"><?php echo count($curioSaleRows); ?> lines · <?php echo money_fmt($curioSalesTotal); ?></span>
            </div>
            <div class="card-body p-0"><div class="table-responsive detail-scroll">
                <table class="table table-modern align-middle mb-0">
                    <thead><tr><th>Closed</th><th>FO Order</th><th>Property</th><th>Payment Status</th><th>Item</th><th class="text-end">Qty</th><th class="text-end">Sales</th></tr></thead>
                    <tbody>
                    <?php if (!$curioSaleRows): ?>
                        <tr><td colspan="7" class="text-center text-muted py-4">No FO curio sales found.</td></tr>
                    <?php else: foreach ($curioSaleRows as $curio): ?>
                        <tr><td><?php echo h($curio['closed_at']); ?></td><td><strong><?php echo h($curio['order_no']); ?></strong></td><td><?php echo h($curio['property_name'] ?? 'Front Office'); ?></td><td><?php echo h(ucwords(str_replace('_', ' ', (string)$curio['payment_status']))); ?></td><td><?php echo h($curio['item_name']); ?></td><td class="text-end"><?php echo h($curio['qty']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($curio['line_total']); ?></td></tr>
                    <?php endforeach; 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">
                <div>
                    <h5 class="mb-0">Restaurant Gratuity Detail</h5>
                    <div class="small text-muted">Restaurant-only gratuities from closed non-FO restaurant orders. Front-office tips and FO curio orders are shown separately.</div>
                </div>
                <span class="badge text-bg-success"><?php echo count($restaurantGratuityRows); ?> lines · <?php echo money_fmt($restaurantGratuityTotal); ?></span>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive detail-scroll">
                    <table class="table table-modern align-middle mb-0">
                        <thead><tr><th>Closed</th><th>Restaurant Order</th><th>Property</th><th>Payment Status</th><th>Item</th><th class="text-end">Gratuity</th></tr></thead>
                        <tbody>
                        <?php if (!$restaurantGratuityRows): ?>
                            <tr><td colspan="6" class="text-center text-muted py-4">No restaurant gratuities found.</td></tr>
                        <?php else: ?>
                            <?php foreach ($restaurantGratuityRows as $gr): ?>
                                <tr>
                                    <td><?php echo h($gr['closed_at']); ?></td>
                                    <td><strong><?php echo h($gr['order_no'] ?: ('#' . $gr['restaurant_order_id'])); ?></strong></td>
                                    <td><?php echo h($gr['property_name'] ?? 'Restaurant'); ?></td>
                                    <td><?php echo h(ucwords(str_replace('_', ' ', (string)$gr['payment_status']))); ?></td>
                                    <td><?php echo h($gr['item_name']); ?></td>
                                    <td class="text-end fw-bold"><?php echo money_fmt($gr['line_total']); ?></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">
                <div>
                    <h5 class="mb-0">Full Extras Classification Detail</h5>
                    <div class="small text-muted">Shows every positive non-room charge in the date/property range, including voided rows. “Assigned Category” shows how the current rules classify each line.</div>
                </div>
                <span class="badge text-bg-secondary"><?php echo count($extrasAuditRows); ?> rows</span>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive detail-scroll">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Date</th>
                                <th>Assigned Category</th>
                                <th>Raw Type</th>
                                <th>Description</th>
                                <th>Source</th>
                                <th>Property</th>
                                <th>Booking</th>
                                <th>Folio</th>
                                <th class="text-end">Debit</th>
                                <th>Voided</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$extrasAuditRows): ?>
                                <tr><td colspan="10" class="text-center text-muted py-4">No non-room positive charges found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($extrasAuditRows as $row): ?>
                                    <?php
                                    $propertyLabel = trim((string)($row['property_name'] ?? ''));
                                    if (!empty($row['property_code'])) {
                                        $propertyLabel .= ' (' . $row['property_code'] . ')';
                                    }
                                    $cat = (string)$row['assigned_category'];
                                    $badgeClass = $cat === 'Gratuity' ? 'success' : ($cat === 'Porterage' ? 'info' : ($cat === 'Voided' ? 'danger' : 'warning'));
                                    ?>
                                    <tr<?php echo (int)$row['is_voided'] === 1 ? ' class="table-danger"' : ''; ?>>
                                        <td><?php echo h($row['tx_date']); ?></td>
                                        <td><span class="badge text-bg-<?php echo h($badgeClass); ?>"><?php echo h($cat); ?></span></td>
                                        <td><code><?php echo h($row['tx_type']); ?></code></td>
                                        <td><?php echo h($row['description']); ?></td>
                                        <td><?php echo h(trim((string)$row['source_table']) . ($row['source_id'] !== null ? ' #' . $row['source_id'] : '')); ?></td>
                                        <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                        <td><?php echo h($row['order_no'] ?: ('#' . $row['order_id'])); ?></td>
                                        <td><?php echo h($row['folio_name']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['debit']); ?></td>
                                        <td><?php echo (int)$row['is_voided'] === 1 ? 'Yes' : 'No'; ?></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">Transaction Detail</h5>
                <span class="text-muted small">Showing latest 1,000 rows</span>
            </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>Property</th>
                                <th>Booking</th>
                                <th>Guest</th>
                                <th>Agent</th>
                                <th>Folio</th>
                                <th>Type</th>
                                <th>Description</th>
                                <th class="text-end">Debit</th>
                                <th class="text-end">Credit</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$detailRows): ?>
                                <tr><td colspan="10" class="text-center text-muted py-4">No transactions 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($row['tx_date']); ?></td>
                                        <td><?php echo h($propertyLabel ?: 'Not set'); ?></td>
                                        <td>
                                            <a href="/rooms/modules/payments/extras.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>
                                            <div class="data-stack">
                                                <strong><?php echo h($row['folio_name']); ?></strong>
                                                <small><?php echo h($row['folio_type']); ?></small>
                                            </div>
                                        </td>
                                        <td><?php echo h(ucwords(str_replace('_', ' ', (string)$row['tx_type']))); ?></td>
                                        <td><?php echo h($row['description']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['debit']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['credit']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

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