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

$pageTitle = 'Agent Production 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('apr_table_exists')) {
    function apr_table_exists(PDO $pdo, string $table): bool {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = :t");
        $stmt->execute([':t' => $table]);
        return (int)$stmt->fetchColumn() > 0;
    }
}
if (!function_exists('apr_column_exists')) {
    function apr_column_exists(PDO $pdo, string $table, string $column): bool {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE() AND table_name = :t AND column_name = :c");
        $stmt->execute([':t' => $table, ':c' => $column]);
        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);
$selectedAgentId = (int)($_GET['agent_id'] ?? 0);
$includeCancelled = (int)($_GET['include_cancelled'] ?? 0);

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

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

foreach (['orders','agents','properties','folio_transactions','folios','order_rooming'] as $t) {
    if (!apr_table_exists($pdo, $t)) $errors[] = 'Required table missing: ' . $t;
}

$hasOrderCancelled = !$errors && apr_column_exists($pdo, 'orders', 'cancelled');
$hasOrderStatus = !$errors && apr_column_exists($pdo, 'orders', 'status');
$hasRoomingCancelled = !$errors && apr_column_exists($pdo, 'order_rooming', 'cancelled');

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

/* HY093 FIX: dates are validated then safely quoted once, so we do not reuse named placeholders. */
$fromSql = $pdo->quote($dateFrom);
$toSql = $pdo->quote($dateTo);

$orderWhere = "o.check_in <= $toSql AND o.check_out > $fromSql";
$params = [];
if ($selectedPropertyId > 0) {
    $orderWhere .= " AND o.property_id = :property_id";
    $params[':property_id'] = $selectedPropertyId;
}
if ($selectedAgentId > 0) {
    $orderWhere .= " AND o.agent_id = :agent_id";
    $params[':agent_id'] = $selectedAgentId;
}
if (!$includeCancelled) {
    if ($hasOrderCancelled) $orderWhere .= " AND COALESCE(o.cancelled,0)=0";
    if ($hasOrderStatus) $orderWhere .= " AND LOWER(COALESCE(o.status,'')) NOT IN ('cancelled','canceled','void','deleted')";
}

$roomingCancelFilter = (!$includeCancelled && $hasRoomingCancelled) ? " AND COALESCE(orm.cancelled,0)=0" : "";

$summary = [
    'agents_count'=>0,'bookings_count'=>0,'room_revenue'=>0,'extras_revenue'=>0,
    'total_revenue'=>0,'payments_received'=>0,'outstanding_balance'=>0,
    'room_nights'=>0,'avg_booking_value'=>0
];
$rows = [];
$detailRows = [];
$propertyRows = [];

if (!$errors) {
    try {
        $balPropertyFilter = $selectedPropertyId > 0 ? " AND ox.property_id = :bal_property_id" : "";
        $rnPropertyFilter = $selectedPropertyId > 0 ? " AND ox2.property_id = :rn_property_id" : "";

        $sql = "
            SELECT
                COALESCE(a.id,0) AS agent_id,
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''),' ',COALESCE(a.lastname,''))),''),'Direct / No Agent') AS agent_name,
                COUNT(DISTINCT o.id) AS bookings_count,
                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 THEN ft.debit ELSE 0 END),0) AS extras_revenue,
                COALESCE(SUM(CASE WHEN ft.credit>0 THEN ft.credit ELSE 0 END),0) AS payments_received,
                COALESCE((
                    SELECT SUM(f.balance)
                    FROM folios f
                    INNER JOIN orders ox ON ox.id=f.order_id
                    WHERE COALESCE(ox.agent_id,0)=COALESCE(a.id,0)
                      AND ox.check_in <= $toSql
                      AND ox.check_out > $fromSql
                      $balPropertyFilter
                ),0) AS outstanding_balance,
                COALESCE((
                    SELECT SUM(DATEDIFF(LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)), GREATEST(orm.check_in, $fromSql)))
                    FROM order_rooming orm
                    INNER JOIN orders ox2 ON ox2.id=orm.order_id
                    WHERE COALESCE(ox2.agent_id,0)=COALESCE(a.id,0)
                      AND orm.check_in <= $toSql
                      AND orm.check_out > $fromSql
                      $rnPropertyFilter
                      $roomingCancelFilter
                ),0) AS room_nights
            FROM orders o
            LEFT JOIN agents a ON a.id=o.agent_id
            LEFT JOIN folio_transactions ft ON ft.order_id=o.id AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
            WHERE $orderWhere
            GROUP BY agent_id, agent_name
            ORDER BY room_revenue DESC, extras_revenue DESC, agent_name ASC
        ";
        $execParams = $params;
        if ($selectedPropertyId > 0) {
            $execParams[':bal_property_id'] = $selectedPropertyId;
            $execParams[':rn_property_id'] = $selectedPropertyId;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($execParams);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as &$r) {
            $r['total_revenue'] = (float)$r['room_revenue'] + (float)$r['extras_revenue'];
            $r['avg_booking_value'] = (int)$r['bookings_count'] > 0 ? $r['total_revenue'] / (int)$r['bookings_count'] : 0;
            $summary['agents_count']++;
            $summary['bookings_count'] += (int)$r['bookings_count'];
            $summary['room_revenue'] += (float)$r['room_revenue'];
            $summary['extras_revenue'] += (float)$r['extras_revenue'];
            $summary['payments_received'] += (float)$r['payments_received'];
            $summary['outstanding_balance'] += (float)$r['outstanding_balance'];
            $summary['room_nights'] += (int)$r['room_nights'];
        }
        unset($r);
        $summary['total_revenue'] = $summary['room_revenue'] + $summary['extras_revenue'];
        $summary['avg_booking_value'] = $summary['bookings_count'] > 0 ? $summary['total_revenue'] / $summary['bookings_count'] : 0;
    } catch (Throwable $e) {
        $errors[] = 'Could not load agent production summary: ' . $e->getMessage();
    }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(p.name,'No Property') AS property_name,
                COALESCE(p.code,'') AS property_code,
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''),' ',COALESCE(a.lastname,''))),''),'Direct / No Agent') AS agent_name,
                COUNT(DISTINCT o.id) AS bookings_count,
                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 THEN ft.debit ELSE 0 END),0) AS extras_revenue,
                COALESCE(SUM(CASE WHEN ft.credit>0 THEN ft.credit ELSE 0 END),0) AS payments_received
            FROM orders o
            LEFT JOIN agents a ON a.id=o.agent_id
            LEFT JOIN properties p ON p.id=o.property_id
            LEFT JOIN folio_transactions ft ON ft.order_id=o.id AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
            WHERE $orderWhere
            GROUP BY property_name, property_code, agent_name
            ORDER BY property_name ASC, room_revenue DESC
            LIMIT 500
        ");
        $stmt->execute($params);
        $propertyRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($propertyRows as &$r) $r['total_revenue'] = (float)$r['room_revenue'] + (float)$r['extras_revenue'];
        unset($r);
    } catch (Throwable $e) { $propertyRows = []; }
}

if (!$errors) {
    try {
        $stmt = $pdo->prepare("
            SELECT
                o.id AS order_id, o.order_no, o.check_in, o.check_out,
                COALESCE(p.name,'No Property') AS property_name,
                COALESCE(p.code,'') AS property_code,
                COALESCE(NULLIF(TRIM(CONCAT(COALESCE(a.firstname,''),' ',COALESCE(a.lastname,''))),''),'Direct / No Agent') AS agent_name,
                TRIM(CONCAT(COALESCE(g.firstname,''),' ',COALESCE(g.lastname,''))) AS guest_name,
                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 THEN ft.debit ELSE 0 END),0) AS extras_revenue,
                COALESCE(SUM(CASE WHEN ft.credit>0 THEN ft.credit ELSE 0 END),0) AS payments_received,
                COALESCE((SELECT SUM(f.balance) FROM folios f WHERE f.order_id=o.id),0) AS outstanding_balance,
                COALESCE((
                    SELECT SUM(DATEDIFF(LEAST(orm.check_out, DATE_ADD($toSql, INTERVAL 1 DAY)), GREATEST(orm.check_in, $fromSql)))
                    FROM order_rooming orm
                    WHERE orm.order_id=o.id AND orm.check_in <= $toSql AND orm.check_out > $fromSql $roomingCancelFilter
                ),0) AS room_nights
            FROM orders o
            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=o.property_id
            LEFT JOIN folio_transactions ft ON ft.order_id=o.id AND DATE(ft.tx_date) BETWEEN $fromSql AND $toSql
            WHERE $orderWhere
            GROUP BY o.id,o.order_no,o.check_in,o.check_out,property_name,property_code,agent_name,guest_name
            ORDER BY room_revenue DESC, extras_revenue DESC, o.check_in ASC
            LIMIT 1500
        ");
        $stmt->execute($params);
        $detailRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($detailRows as &$r) $r['total_revenue'] = (float)$r['room_revenue'] + (float)$r['extras_revenue'];
        unset($r);
    } catch (Throwable $e) {
        $errors[] = 'Could not load booking detail: ' . $e->getMessage();
    }
}

if (isset($_GET['export']) && $_GET['export'] === 'csv' && !$errors) {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="agent_production_report_' . date('Ymd_His') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Agent','Bookings','Room Revenue','Extras Revenue','Total Revenue','Payments Received','Outstanding Balance','Room Nights','Average Booking Value']);
    foreach ($rows as $r) {
        fputcsv($out, [$r['agent_name'], (int)$r['bookings_count'], number_format((float)$r['room_revenue'],2,'.',''), number_format((float)$r['extras_revenue'],2,'.',''), number_format((float)$r['total_revenue'],2,'.',''), number_format((float)$r['payments_received'],2,'.',''), number_format((float)$r['outstanding_balance'],2,'.',''), (int)$r['room_nights'], number_format((float)$r['avg_booking_value'],2,'.','')]);
    }
    fclose($out);
    exit;
}

require_once __DIR__ . '/../../includes/header.php';
?>

<div class="hero-band">
    <div class="row g-4 align-items-center">
        <div class="col-lg-8">
            <div class="page-eyebrow text-white-50"><i class="bi bi-people"></i> Sales & Agent Analytics</div>
            <div class="hero-title mt-2">Agent Production Report</div>
            <div class="hero-subtitle mt-2">Track bookings, room revenue, extras, payments, outstanding balances and room nights by agent.</div>
        </div>
        <div class="col-lg-4 text-lg-end">
            <a href="agent_production_report.php?<?php echo h(http_build_query(array_merge($_GET, ['export' => 'csv']))); ?>" class="btn btn-light">
                <i class="bi bi-download me-2"></i>Export CSV
            </a>
        </div>
    </div>
</div>

<?php if ($errors): ?>
    <div class="alert alert-danger"><div class="fw-bold mb-2">Please fix the following:</div><ul class="mb-0"><?php foreach ($errors as $err): ?><li><?php echo h($err); ?></li><?php endforeach; ?></ul></div>
<?php endif; ?>

<div class="card panel-card mb-4">
    <div class="card-body">
        <form method="get" class="row g-3 align-items-end">
            <div class="col-md-2"><label class="form-label">Date From</label><input type="date" name="date_from" class="form-control" value="<?php echo h($dateFrom); ?>"></div>
            <div class="col-md-2"><label class="form-label">Date To</label><input type="date" name="date_to" class="form-control" value="<?php echo h($dateTo); ?>"></div>
            <div class="col-md-3"><label class="form-label">Property</label><select name="property_id" class="form-select"><option value="0">All properties</option><?php foreach ($properties as $p): ?><option value="<?php echo (int)$p['id']; ?>" <?php echo $selectedPropertyId === (int)$p['id'] ? 'selected' : ''; ?>><?php echo h($p['name']); ?> - <?php echo h($p['code']); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-3"><label class="form-label">Agent</label><select name="agent_id" class="form-select"><option value="0">All agents / direct</option><?php foreach ($agents as $a): ?><?php $an = trim(($a['firstname'] ?? '') . ' ' . ($a['lastname'] ?? '')); ?><option value="<?php echo (int)$a['id']; ?>" <?php echo $selectedAgentId === (int)$a['id'] ? 'selected' : ''; ?>><?php echo h($an !== '' ? $an : ('Agent #' . (int)$a['id'])); ?></option><?php endforeach; ?></select></div>
            <div class="col-md-1"><label class="form-label">Cancelled</label><select name="include_cancelled" class="form-select"><option value="0" <?php echo $includeCancelled === 0 ? 'selected' : ''; ?>>No</option><option value="1" <?php echo $includeCancelled === 1 ? 'selected' : ''; ?>>Yes</option></select></div>
            <div class="col-md-1 d-grid"><button class="btn btn-primary"><i class="bi bi-funnel"></i></button></div>
        </form>
    </div>
</div>

<div class="row g-4 mb-4">
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Agents</div><div class="metric-value"><?php echo (int)$summary['agents_count']; ?></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Bookings</div><div class="metric-value"><?php echo (int)$summary['bookings_count']; ?></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Total Revenue</div><div class="metric-value"><?php echo money_fmt($summary['total_revenue']); ?></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Room Nights</div><div class="metric-value"><?php echo (int)$summary['room_nights']; ?></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Outstanding</div><div class="metric-value"><?php echo money_fmt($summary['outstanding_balance']); ?></div></div></div>
    <div class="col-md-6 col-xl-2"><div class="metric-card"><div class="metric-label">Avg Booking</div><div class="metric-value"><?php echo money_fmt($summary['avg_booking_value']); ?></div></div></div>
</div>

<div class="card panel-card mb-4">
    <div class="card-header"><h5 class="mb-0">Agent Production Summary</h5></div>
    <div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0">
        <thead><tr><th>Agent</th><th class="text-end">Bookings</th><th class="text-end">Room Revenue</th><th class="text-end">Extras</th><th class="text-end">Total Revenue</th><th class="text-end">Payments</th><th class="text-end">Outstanding</th><th class="text-end">Room Nights</th><th class="text-end">Avg Booking</th></tr></thead>
        <tbody><?php if (!$rows): ?><tr><td colspan="9" class="text-center text-muted py-4">No agent production found.</td></tr><?php else: ?><?php foreach ($rows as $r): ?><tr><td><strong><?php echo h($r['agent_name']); ?></strong></td><td class="text-end"><?php echo (int)$r['bookings_count']; ?></td><td class="text-end"><?php echo money_fmt($r['room_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['extras_revenue']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['total_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['payments_received']); ?></td><td class="text-end fw-bold text-danger"><?php echo money_fmt($r['outstanding_balance']); ?></td><td class="text-end"><?php echo (int)$r['room_nights']; ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['avg_booking_value']); ?></td></tr><?php endforeach; ?><?php endif; ?></tbody>
    </table></div></div>
</div>

<div class="card panel-card mb-4">
    <div class="card-header"><h5 class="mb-0">Agent Production by Property</h5></div>
    <div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0">
        <thead><tr><th>Property</th><th>Agent</th><th class="text-end">Bookings</th><th class="text-end">Room Revenue</th><th class="text-end">Extras</th><th class="text-end">Total</th><th class="text-end">Payments</th></tr></thead>
        <tbody><?php if (!$propertyRows): ?><tr><td colspan="7" class="text-center text-muted py-4">No property breakdown found.</td></tr><?php else: ?><?php foreach ($propertyRows as $r): ?><tr><td><strong><?php echo h($r['property_name']); ?></strong><?php if (!empty($r['property_code'])): ?><div class="small text-muted"><?php echo h($r['property_code']); ?></div><?php endif; ?></td><td><?php echo h($r['agent_name']); ?></td><td class="text-end"><?php echo (int)$r['bookings_count']; ?></td><td class="text-end"><?php echo money_fmt($r['room_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['extras_revenue']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['total_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['payments_received']); ?></td></tr><?php endforeach; ?><?php endif; ?></tbody>
    </table></div></div>
</div>

<div class="card panel-card">
    <div class="card-header d-flex justify-content-between align-items-center"><h5 class="mb-0">Booking Detail by Agent</h5><span class="text-muted small">Showing latest 1,500 bookings</span></div>
    <div class="card-body p-0"><div class="table-responsive"><table class="table table-modern align-middle mb-0">
        <thead><tr><th>Property</th><th>Booking</th><th>Guest</th><th>Agent</th><th>Stay</th><th class="text-end">Room Nights</th><th class="text-end">Room Revenue</th><th class="text-end">Extras</th><th class="text-end">Total</th><th class="text-end">Payments</th><th class="text-end">Outstanding</th></tr></thead>
        <tbody><?php if (!$detailRows): ?><tr><td colspan="11" class="text-center text-muted py-4">No booking details found.</td></tr><?php else: ?><?php foreach ($detailRows as $r): ?><?php $propertyLabel = trim((string)($r['property_name'] ?? '')); if (!empty($r['property_code'])) $propertyLabel .= ' (' . $r['property_code'] . ')'; $guestName = trim((string)($r['guest_name'] ?? '')); ?><tr><td><?php echo h($propertyLabel ?: 'Not set'); ?></td><td><a href="/rooms/modules/reservations/reservation_view.php?order_id=<?php echo (int)$r['order_id']; ?>" class="booking-ref"><?php echo h($r['order_no'] ?: ('#' . $r['order_id'])); ?></a></td><td><?php echo h($guestName ?: 'Guest'); ?></td><td><?php echo h($r['agent_name']); ?></td><td><div class="data-stack"><strong><?php echo h($r['check_in']); ?></strong><small>to <?php echo h($r['check_out']); ?></small></div></td><td class="text-end"><?php echo (int)$r['room_nights']; ?></td><td class="text-end"><?php echo money_fmt($r['room_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['extras_revenue']); ?></td><td class="text-end fw-bold"><?php echo money_fmt($r['total_revenue']); ?></td><td class="text-end"><?php echo money_fmt($r['payments_received']); ?></td><td class="text-end fw-bold text-danger"><?php echo money_fmt($r['outstanding_balance']); ?></td></tr><?php endforeach; ?><?php endif; ?></tbody>
    </table></div></div>
</div>

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