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

$pageTitle = 'Extras / Services 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('extras_report_table_exists')) {
    function extras_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;
    }
}

$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);
$selectedCategoryId = (int)($_GET['category_id'] ?? 0);
$selectedServiceId = (int)($_GET['service_id'] ?? 0);
$groupBy = trim((string)($_GET['group_by'] ?? 'category'));

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

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', 'tblservices', 'tblservicecategory'];
foreach ($requiredTables as $table) {
    if (!extras_report_table_exists($pdo, $table)) {
        $errors[] = 'Required table missing: ' . $table;
    }
}

$properties = [];
$categories = [];
$services = [];

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 {
        $categories = $pdo->query("
            SELECT id, category
            FROM tblservicecategory
            ORDER BY category ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load service categories: ' . $e->getMessage();
    }

    try {
        $services = $pdo->query("
            SELECT
                s.id,
                s.service,
                s.price_per_person,
                s.category AS category_id,
                c.category AS category_name
            FROM tblservices s
            LEFT JOIN tblservicecategory c ON c.id = s.category
            ORDER BY c.category ASC, s.service ASC
        ")->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load services: ' . $e->getMessage();
    }
}

/*
  Important:
  Current extras posting stores service name into folio_transactions.description
  and category/type into folio_transactions.tx_type.
  We therefore link posted extras back to tblservices by matching ft.description = tblservices.service.
*/
$where = "
    DATE(ft.tx_date) BETWEEN :date_from AND :date_to
    AND ft.debit > 0
    AND ft.tx_type <> 'room_charge'
";
$params = [
    ':date_from' => $dateFrom,
    ':date_to'   => $dateTo,
];

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

if ($selectedCategoryId > 0) {
    $where .= " AND s.category = :category_id ";
    $params[':category_id'] = $selectedCategoryId;
}

if ($selectedServiceId > 0) {
    $where .= " AND s.id = :service_id ";
    $params[':service_id'] = $selectedServiceId;
}

$summary = [
    'extras_revenue' => 0,
    'known_service_revenue' => 0,
    'unmatched_revenue' => 0,
    'estimated_qty' => 0,
    'transaction_count' => 0,
    'bookings_count' => 0,
    'avg_charge' => 0,
];

$groupRows = [];
$serviceRows = [];
$categoryRows = [];
$dailyRows = [];
$detailRows = [];
$unmatchedRows = [];

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(SUM(ft.debit), 0) AS extras_revenue,
                COALESCE(SUM(CASE WHEN s.id IS NOT NULL THEN ft.debit ELSE 0 END), 0) AS known_service_revenue,
                COALESCE(SUM(CASE WHEN s.id IS NULL THEN ft.debit ELSE 0 END), 0) AS unmatched_revenue,
                COALESCE(SUM(CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END), 0) AS estimated_qty,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(AVG(ft.debit), 0) AS avg_charge
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            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 extras summary: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        switch ($groupBy) {
            case 'service':
                $groupSelect = "COALESCE(s.service, ft.description, 'Unmatched Service')";
                break;
            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 'category':
            default:
                $groupSelect = "COALESCE(sc.category, ft.tx_type, 'Uncategorised')";
                break;
        }

        $stmt = $pdo->prepare("
            SELECT
                $groupSelect AS group_label,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(SUM(CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END), 0) AS estimated_qty,
                COALESCE(SUM(ft.debit), 0) AS revenue,
                COALESCE(AVG(ft.debit), 0) AS avg_charge
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            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 revenue DESC, group_label ASC
        ");
        $stmt->execute($params);
        $groupRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $errors[] = 'Could not load grouped extras report: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(sc.category, ft.tx_type, 'Uncategorised') AS category_name,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(SUM(CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END), 0) AS estimated_qty,
                COALESCE(SUM(ft.debit), 0) AS revenue
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
            GROUP BY category_name
            ORDER BY revenue DESC, category_name ASC
        ");
        $stmt->execute($params);
        $categoryRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $categoryRows = [];
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(s.id, 0) AS service_id,
                COALESCE(s.service, ft.description, 'Unmatched Service') AS service_name,
                COALESCE(sc.category, ft.tx_type, 'Uncategorised') AS category_name,
                COALESCE(s.price_per_person, 0) AS price_per_person,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(SUM(CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END), 0) AS estimated_qty,
                COALESCE(SUM(ft.debit), 0) AS revenue,
                COALESCE(AVG(ft.debit), 0) AS avg_charge
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
            GROUP BY service_id, service_name, category_name, price_per_person
            ORDER BY revenue DESC, service_name ASC
            LIMIT 100
        ");
        $stmt->execute($params);
        $serviceRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $serviceRows = [];
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                DATE(ft.tx_date) AS charge_date,
                COUNT(*) AS transaction_count,
                COUNT(DISTINCT ft.order_id) AS bookings_count,
                COALESCE(SUM(CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END), 0) AS estimated_qty,
                COALESCE(SUM(ft.debit), 0) AS revenue
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            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 charge_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.order_id,
                ft.folio_id,
                COALESCE(s.id, 0) AS service_id,
                COALESCE(s.service, ft.description) AS service_name,
                COALESCE(s.price_per_person, 0) AS price_per_person,
                COALESCE(sc.category, ft.tx_type, 'Uncategorised') AS category_name,
                CASE WHEN s.price_per_person > 0 THEN ft.debit / s.price_per_person ELSE 0 END AS estimated_qty,
                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 tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN tblservicecategory sc ON sc.id = s.category
            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 extras detail: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                ft.description,
                ft.tx_type,
                COUNT(*) AS transaction_count,
                COALESCE(SUM(ft.debit), 0) AS revenue
            FROM folio_transactions ft
            LEFT JOIN tblservices s ON TRIM(LOWER(s.service)) = TRIM(LOWER(ft.description))
            LEFT JOIN orders o ON o.id = ft.order_id
            LEFT JOIN folios f ON f.id = ft.folio_id
            WHERE $where
              AND s.id IS NULL
            GROUP BY ft.description, ft.tx_type
            ORDER BY revenue DESC
            LIMIT 20
        ");
        $stmt->execute($params);
        $unmatchedRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
        $unmatchedRows = [];
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    $filename = 'extras_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',
        'Property',
        'Booking',
        'Guest',
        'Agent',
        'Folio',
        'Folio Type',
        'Category',
        'Service',
        'Price Per Person',
        'Estimated Quantity',
        'Revenue',
        'Original Type',
        'Original Description'
    ]);

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

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

    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-bag-plus"></i> Services & Extras Analytics
            </div>
            <div class="hero-title mt-2">Extras / Services Report</div>
            <div class="hero-subtitle mt-2">
                Analyse transfers, activities, laundry, minibar and all extras posted to folios using your service catalogue.
            </div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="extras_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-2">
                <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">Category</label>
                <select name="category_id" id="category_id" class="form-select">
                    <option value="0">All categories</option>
                    <?php foreach ($categories as $cat): ?>
                        <option value="<?php echo (int)$cat['id']; ?>" <?php echo $selectedCategoryId === (int)$cat['id'] ? 'selected' : ''; ?>>
                            <?php echo h($cat['category']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-2">
                <label class="form-label">Service</label>
                <select name="service_id" id="service_id" class="form-select">
                    <option value="0">All services</option>
                    <?php foreach ($services as $srv): ?>
                        <option value="<?php echo (int)$srv['id']; ?>"
                                data-category="<?php echo (int)$srv['category_id']; ?>"
                                <?php echo $selectedServiceId === (int)$srv['id'] ? 'selected' : ''; ?>>
                            <?php echo h($srv['service']); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>

            <div class="col-md-1">
                <label class="form-label">Group</label>
                <select name="group_by" class="form-select">
                    <option value="category" <?php echo $groupBy === 'category' ? 'selected' : ''; ?>>Category</option>
                    <option value="service" <?php echo $groupBy === 'service' ? 'selected' : ''; ?>>Service</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</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-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-check2-circle"></i></div>
            <div>
                <div class="metric-label">Matched Services</div>
                <div class="metric-value"><?php echo money_fmt($summary['known_service_revenue']); ?></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-triangle"></i></div>
            <div>
                <div class="metric-label">Unmatched</div>
                <div class="metric-value"><?php echo money_fmt($summary['unmatched_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-123"></i></div>
            <div>
                <div class="metric-label">Est. Qty</div>
                <div class="metric-value"><?php echo money_fmt($summary['estimated_qty']); ?></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-receipt"></i></div>
            <div>
                <div class="metric-label">Transactions</div>
                <div class="metric-value"><?php echo (int)$summary['transaction_count']; ?></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">
    <div class="col-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Grouped Extras 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>Group</th>
                                <th class="text-end">Bookings</th>
                                <th class="text-end">Transactions</th>
                                <th class="text-end">Est. Qty</th>
                                <th class="text-end">Avg Charge</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$groupRows): ?>
                                <tr><td colspan="6" class="text-center text-muted py-4">No extras 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"><?php echo money_fmt($row['estimated_qty']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['avg_charge']); ?></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-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Category Revenue</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Category</th>
                                <th class="text-end">Est. Qty</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$categoryRows): ?>
                                <tr><td colspan="3" class="text-center text-muted py-4">No category data.</td></tr>
                            <?php else: ?>
                                <?php foreach ($categoryRows as $row): ?>
                                    <tr>
                                        <td>
                                            <strong><?php echo h($row['category_name']); ?></strong>
                                            <div class="small text-muted"><?php echo (int)$row['transaction_count']; ?> transactions</div>
                                        </td>
                                        <td class="text-end"><?php echo money_fmt($row['estimated_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">Service 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>Service</th>
                                <th>Category</th>
                                <th class="text-end">Price PP</th>
                                <th class="text-end">Bookings</th>
                                <th class="text-end">Transactions</th>
                                <th class="text-end">Est. Qty</th>
                                <th class="text-end">Avg Charge</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$serviceRows): ?>
                                <tr><td colspan="8" class="text-center text-muted py-4">No service performance found.</td></tr>
                            <?php else: ?>
                                <?php foreach ($serviceRows as $row): ?>
                                    <tr>
                                        <td>
                                            <strong><?php echo h($row['service_name']); ?></strong>
                                            <?php if ((int)$row['service_id'] <= 0): ?>
                                                <div class="small text-danger">Unmatched to tblservices</div>
                                            <?php endif; ?>
                                        </td>
                                        <td><?php echo h($row['category_name']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['price_per_person']); ?></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"><?php echo money_fmt($row['estimated_qty']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['avg_charge']); ?></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-xl-7">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Daily Extras 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">Bookings</th>
                                <th class="text-end">Transactions</th>
                                <th class="text-end">Est. Qty</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$dailyRows): ?>
                                <tr><td colspan="5" class="text-center text-muted py-4">No daily data.</td></tr>
                            <?php else: ?>
                                <?php foreach ($dailyRows as $row): ?>
                                    <tr>
                                        <td><strong><?php echo h($row['charge_date']); ?></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"><?php echo money_fmt($row['estimated_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-xl-5">
        <div class="card panel-card h-100">
            <div class="card-header">
                <h5 class="mb-0">Unmatched Extras</h5>
            </div>
            <div class="card-body p-0">
                <div class="table-responsive">
                    <table class="table table-modern align-middle mb-0">
                        <thead>
                            <tr>
                                <th>Description</th>
                                <th>Type</th>
                                <th class="text-end">Count</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$unmatchedRows): ?>
                                <tr><td colspan="4" class="text-center text-muted py-4">All extras matched to services.</td></tr>
                            <?php else: ?>
                                <?php foreach ($unmatchedRows as $row): ?>
                                    <tr>
                                        <td><?php echo h($row['description']); ?></td>
                                        <td><?php echo h($row['tx_type']); ?></td>
                                        <td class="text-end"><?php echo (int)$row['transaction_count']; ?></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 d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Extras 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>Category</th>
                                <th>Service</th>
                                <th class="text-end">Price PP</th>
                                <th class="text-end">Est. Qty</th>
                                <th class="text-end">Revenue</th>
                            </tr>
                        </thead>
                        <tbody>
                            <?php if (!$detailRows): ?>
                                <tr><td colspan="11" class="text-center text-muted py-4">No extras 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($row['category_name']); ?></td>
                                        <td>
                                            <strong><?php echo h($row['service_name']); ?></strong>
                                            <?php if ((int)$row['service_id'] <= 0): ?>
                                                <div class="small text-danger">Not matched to service setup</div>
                                            <?php endif; ?>
                                        </td>
                                        <td class="text-end"><?php echo money_fmt($row['price_per_person']); ?></td>
                                        <td class="text-end"><?php echo money_fmt($row['estimated_qty']); ?></td>
                                        <td class="text-end fw-bold"><?php echo money_fmt($row['debit']); ?></td>
                                    </tr>
                                <?php endforeach; ?>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>

</div>

<script>
document.addEventListener('DOMContentLoaded', function () {
    var categorySelect = document.getElementById('category_id');
    var serviceSelect = document.getElementById('service_id');

    if (!categorySelect || !serviceSelect) return;

    var allOptions = Array.prototype.slice.call(serviceSelect.options).map(function (option) {
        return {
            value: option.value,
            text: option.text,
            category: option.getAttribute('data-category') || '0',
            selected: option.selected
        };
    });

    function rebuildServices() {
        var categoryId = categorySelect.value || '0';
        var selectedValue = serviceSelect.value || '0';

        serviceSelect.innerHTML = '';

        allOptions.forEach(function (item) {
            if (item.value !== '0' && categoryId !== '0' && item.category !== categoryId) {
                return;
            }

            var option = document.createElement('option');
            option.value = item.value;
            option.textContent = item.text;
            option.setAttribute('data-category', item.category);

            if (item.value === selectedValue) {
                option.selected = true;
            }

            serviceSelect.appendChild(option);
        });

        if (!serviceSelect.querySelector('option[value="' + selectedValue + '"]')) {
            serviceSelect.value = '0';
        }
    }

    categorySelect.addEventListener('change', function () {
        serviceSelect.value = '0';
        rebuildServices();
    });

    rebuildServices();
});
</script>

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