<?php
/**
 * ================================================================
 * ADMIN CARD DRILL-DOWN TRANSACTIONS PAGE
 * ================================================================
 * File: admin/card-transactions.php
 *
 * Shows the exact records that make up each dashboard card figure.
 * Each filter runs the SAME query used in WalletStats, so totals
 * always match what is displayed on the index.php dashboard.
 * ================================================================
 */

require_once '../app/Core/init.php';
require_once '../app/Core/WalletStats.php';

Auth::requireAdmin();

$db = Database::getInstance();

$page_title  = "Card Transactions Detail";
$active_menu = "transactions";

// ================================================================
// FILTER DEFINITIONS
// ================================================================
$filter_map = [
    'current_rent_onhold' => [
        'label'       => '⏳ Current Rent Onhold',
        'description' => 'Subtotals from confirmed/ongoing paid orders not yet released to vendors.',
        'source'      => 'orders',
        'color'       => 'danger',
    ],
    'current_security' => [
        'label'       => '🔒 Current Security Onhold',
        'description' => 'Security deposits held by the platform still in onhold status.',
        'source'      => 'order_payment_breakdown',
        'color'       => 'secondary',
    ],
    'vendor_earnings_onhold' => [
        'label'       => '⏸️ Vendors Earnings Onhold',
        'description' => 'Rental earnings in order_payment_breakdown still onhold — not yet released to vendors.',
        'source'      => 'order_payment_breakdown',
        'color'       => 'primary',
    ],
    'available_commission' => [
        'label'       => '💰 Available Commission',
        'description' => 'All commission credits received by the admin wallet (current balance basis).',
        'source'      => 'wallet_transactions',
        'color'       => 'primary',
    ],
    'total_commission_earned' => [
        'label'       => '📈 Total Commission Earned',
        'description' => 'All platform_commission and penalty credits ever received by the admin wallet.',
        'source'      => 'wallet_transactions',
        'color'       => 'success',
    ],
    'total_customers_spent' => [
        'label'       => '👥 Total Customers Spent',
        'description' => 'All item_rental debit transactions made by customers (lifetime spending).',
        'source'      => 'wallet_transactions',
        'color'       => 'warning',
    ],
    'total_vendors_earned' => [
        'label'       => '🏪 Total Vendors Earned',
        'description' => 'All rental_earning credit transactions paid out to vendors (lifetime).',
        'source'      => 'wallet_transactions',
        'color'       => 'info',
    ],
    'total_withdrawn' => [
        'label'       => '💸 Total Withdrawn',
        'description' => 'All approved/paid withdrawal requests processed on the platform.',
        'source'      => 'withdrawal_requests',
        'color'       => 'info',
    ],
    'pending_withdrawals' => [
        'label'       => '⏳ Pending Withdrawals',
        'description' => 'Withdrawal requests currently awaiting admin approval.',
        'source'      => 'withdrawal_requests',
        'color'       => 'warning',
    ],
    'total_cancellation_refund' => [
        'label'       => '↩️ Total Cancelled (Refunds)',
        'description' => 'All rental cancellation refunds credited back to customers.',
        'source'      => 'wallet_transactions',
        'color'       => 'warning',
    ],
    'total_cancellation_penalty' => [
        'label'       => '↩️ Cancellation Penalty Charged',
        'description' => 'Cancellation penalty amounts received by vendors and admin.',
        'source'      => 'wallet_transactions',
        'color'       => 'dark',
    ],
];

// ================================================================
// VALIDATE FILTER
// ================================================================
$filter = trim($_GET['filter'] ?? '');

if (!array_key_exists($filter, $filter_map)) {
    header('Location: transactions.php');
    exit;
}

$meta = $filter_map[$filter];

// ================================================================
// PAGINATION
// ================================================================
$page     = max(1, (int)($_GET['page'] ?? 1));
$per_page = 50;
$offset   = ($page - 1) * $per_page;

// ================================================================
// QUERIES
// KEY RULE: COUNT query and data query use IDENTICAL WHERE clauses.
// No alias is used in standalone (non-join) COUNT queries to avoid
// "unknown column" errors when the WHERE references a bare column.
// ================================================================
$rows       = [];
$total_rows = 0;
$card_total = 0.00;
$columns    = [];

switch ($filter) {

    // ──────────────────────────────────────────────────────────────
    // CURRENT RENT ONHOLD
    // ──────────────────────────────────────────────────────────────
    case 'current_rent_onhold':
        $columns = ['Order #', 'Customer', 'Vendor', 'Item', 'Rental Period', 'Subtotal', 'Security Deposit', 'Total Amount', 'Status', 'Payment', 'Created'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM orders
            WHERE status IN ('confirmed', 'ongoing')
              AND payment_status = 'paid'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                o.id, o.order_number, o.start_date, o.end_date,
                o.subtotal, o.security_deposit, o.total_amount,
                o.status, o.payment_status, o.created_at,
                CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
                v.business_name                         AS vendor_name,
                i.title                                 AS item_title
            FROM orders o
            LEFT JOIN customers c ON o.customer_id = c.id
            LEFT JOIN vendors   v ON o.vendor_id   = v.id
            LEFT JOIN items     i ON o.item_id     = i.id
            WHERE o.status IN ('confirmed', 'ongoing')
              AND o.payment_status = 'paid'
            ORDER BY o.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) AS total
            FROM orders
            WHERE status IN ('confirmed', 'ongoing')
              AND payment_status = 'paid'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // CURRENT SECURITY ONHOLD
    // ──────────────────────────────────────────────────────────────
    case 'current_security':
        $columns = ['ID', 'Order #', 'Customer', 'Vendor / Item', 'Amount', 'Order Total', 'Source', 'Created'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM order_payment_breakdown
            WHERE type   = 'security_deposit'
              AND status = 'onhold'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                opb.id, opb.order_id, opb.amount, opb.total_amount,
                opb.source, opb.created_at, o.order_number,
                CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
                v.business_name                         AS vendor_name,
                i.title                                 AS item_title
            FROM order_payment_breakdown opb
            LEFT JOIN orders    o ON opb.order_id    = o.id
            LEFT JOIN customers c ON opb.customer_id = c.id
            LEFT JOIN vendors   v ON o.vendor_id     = v.id
            LEFT JOIN items     i ON o.item_id       = i.id
            WHERE opb.type   = 'security_deposit'
              AND opb.status = 'onhold'
            ORDER BY opb.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM order_payment_breakdown
            WHERE type   = 'security_deposit'
              AND status = 'onhold'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // VENDOR EARNINGS ONHOLD
    // ──────────────────────────────────────────────────────────────
    case 'vendor_earnings_onhold':
        $columns = ['ID', 'Dates', 'Order #/Item', 'Vendor', 'Customer', 'Order Total', 'Source', 'Earning (Onhold)'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM order_payment_breakdown
            WHERE type   = 'rental'
              AND status = 'onhold'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                opb.id, opb.order_id, opb.amount, opb.total_amount,
                opb.source, opb.created_at, o.order_number, o.start_date, o.end_date,
                CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
                v.business_name                         AS vendor_name,
                i.title                                 AS item_title
            FROM order_payment_breakdown opb
            LEFT JOIN orders    o ON opb.order_id    = o.id
            LEFT JOIN customers c ON opb.customer_id = c.id
            LEFT JOIN vendors   v ON o.vendor_id     = v.id
            LEFT JOIN items     i ON o.item_id       = i.id
            WHERE opb.type   = 'rental'
              AND opb.status = 'onhold'
            ORDER BY opb.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM order_payment_breakdown
            WHERE type   = 'rental'
              AND status = 'onhold'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // AVAILABLE COMMISSION (admin wallet credit transactions)
    // ──────────────────────────────────────────────────────────────
    case 'available_commission':
        $columns = ['Txn ID', 'Date', 'Type', 'Order #', 'Description', 'Amount', 'Balance After', 'Status'];

        $admin         = $db->fetch("SELECT id FROM users WHERE user_type = 'admin' ORDER BY id ASC LIMIT 1");
        $admin_user_id = (int)($admin['id'] ?? 1);

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM wallet_transactions
            WHERE user_id  = {$admin_user_id}
              AND category = 'credit'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                wt.id, wt.transaction_type, wt.amount, wt.balance_after,
                wt.description, wt.status, wt.created_at, wt.order_id,
                o.order_number
            FROM wallet_transactions wt
            LEFT JOIN orders o ON wt.order_id = o.id
            WHERE wt.user_id  = {$admin_user_id}
              AND wt.category = 'credit'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM wallet_transactions
            WHERE user_id  = {$admin_user_id}
              AND category = 'credit'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL COMMISSION EARNED
    // ──────────────────────────────────────────────────────────────
    case 'total_commission_earned':
        $columns = ['Txn ID', 'Date', 'Type', 'Order #', 'Description', 'Amount', 'Balance After', 'Status'];

        $admin         = $db->fetch("SELECT id FROM users WHERE user_type = 'admin' ORDER BY id ASC LIMIT 1");
        $admin_user_id = (int)($admin['id'] ?? 1);

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM wallet_transactions
            WHERE user_id          = {$admin_user_id}
              AND transaction_type IN ('platform_commission', 'admin_cancellation_penalty_received')
              AND category         = 'credit'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                wt.id, wt.transaction_type, wt.amount, wt.balance_after,
                wt.description, wt.status, wt.created_at, wt.order_id,
                o.order_number
            FROM wallet_transactions wt
            LEFT JOIN orders o ON wt.order_id = o.id
            WHERE wt.user_id          = {$admin_user_id}
              AND wt.transaction_type IN ('platform_commission', 'admin_cancellation_penalty_received')
              AND wt.category         = 'credit'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM wallet_transactions
            WHERE user_id          = {$admin_user_id}
              AND transaction_type IN ('platform_commission', 'admin_cancellation_penalty_received')
              AND category         = 'credit'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL CUSTOMERS SPENT
    // WalletStats source: SUM(total_spent) FROM wallets WHERE user_type='customer'
    // Drill-down: item_rental debits by customer wallets
    // ──────────────────────────────────────────────────────────────
    case 'total_customers_spent':
        $columns = ['Txn ID', 'Date', 'Customer', 'Order #', 'Item', 'Description', 'Amount Paid', 'Status'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM wallet_transactions wt
            INNER JOIN wallets w ON wt.wallet_id = w.id
            WHERE wt.transaction_type = 'item_rental'
              AND wt.category         = 'debit'
              AND w.user_type         = 'customer'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                wt.id, wt.amount, wt.description,
                wt.status, wt.created_at, wt.order_id,
                o.order_number,
                u.name     AS user_name,
                i.title    AS item_title
            FROM wallet_transactions wt
            INNER JOIN wallets w  ON wt.wallet_id  = w.id
            LEFT JOIN  orders  o  ON wt.order_id   = o.id
            LEFT JOIN  users   u  ON wt.user_id    = u.id
            LEFT JOIN  items   i  ON o.item_id     = i.id
            WHERE wt.transaction_type = 'item_rental'
              AND wt.category         = 'debit'
              AND w.user_type         = 'customer'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(wt.amount), 0) AS total
            FROM wallet_transactions wt
            INNER JOIN wallets w ON wt.wallet_id = w.id
            WHERE wt.transaction_type = 'item_rental'
              AND wt.category         = 'debit'
              AND w.user_type         = 'customer'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL VENDORS EARNED
    // WalletStats source: SUM(total_earned) FROM wallets WHERE user_type='vendor'
    // Drill-down: rental_earning credits to vendor wallets
    // ──────────────────────────────────────────────────────────────
    case 'total_vendors_earned':
        $columns = ['Txn ID', 'Date', 'Vendor', 'Order #', 'Item', 'Description', 'Amount Earned', 'Balance After', 'Status'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM wallet_transactions wt
            INNER JOIN wallets w ON wt.wallet_id = w.id
            WHERE wt.transaction_type IN ('rental_earning', 'vendor_cancellation_penalty_received')
              AND wt.category         = 'credit'
              AND w.user_type         = 'vendor'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                wt.id, wt.amount, wt.balance_after,
                wt.description, wt.status, wt.created_at, wt.order_id,
                o.order_number,
                v.business_name     AS user_name,
                i.title    AS item_title
            FROM wallet_transactions wt
            INNER JOIN wallets w  ON wt.wallet_id  = w.id
            LEFT JOIN  orders  o  ON wt.order_id   = o.id
            LEFT JOIN  users   u  ON wt.user_id    = u.id
            LEFT JOIN  vendors v  ON wt.user_id    = v.id
            LEFT JOIN  items   i  ON o.item_id     = i.id
            WHERE wt.transaction_type IN ('rental_earning', 'vendor_cancellation_penalty_received')
              AND wt.category         = 'credit'
              AND w.user_type         = 'vendor'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(wt.amount), 0) AS total
            FROM wallet_transactions wt
            INNER JOIN wallets w ON wt.wallet_id = w.id
            WHERE wt.transaction_type IN ('rental_earning', 'vendor_cancellation_penalty_received')
              AND wt.category         = 'credit'
              AND w.user_type         = 'vendor'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL WITHDRAWN (approved + paid)
    // ──────────────────────────────────────────────────────────────
    case 'total_withdrawn':
        $columns = ['ID', 'Date', 'Reference', 'User', 'User Type', 'Bank', 'Account Number', 'Status', 'Amount'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM withdrawal_requests
            WHERE status IN ('approved', 'paid')
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT wr.*, 
            u.email as user_email,
            u.user_type,
            CASE 
                WHEN u.user_type = 'vendor' THEN v.business_name
                WHEN u.user_type = 'customer' THEN CONCAT(c.first_name, ' ', c.last_name)
                ELSE 'Admin'
            END as display_name
            FROM withdrawal_requests wr
            LEFT JOIN users u ON wr.user_id = u.id
            LEFT JOIN vendors v ON u.id = v.user_id
            LEFT JOIN customers c ON u.id = c.user_id
            WHERE wr.status IN ('approved', 'paid')
            ORDER BY wr.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM withdrawal_requests
            WHERE status IN ('approved', 'paid')
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // PENDING WITHDRAWALS
    // ──────────────────────────────────────────────────────────────
    case 'pending_withdrawals':
        $columns = ['ID', 'Date', 'Reference', 'User', 'User Type', 'Bank', 'Account Number', 'Status', 'Amount'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM withdrawal_requests
            WHERE status = 'pending'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT wr.*, 
            u.email as user_email,
            u.user_type,
            CASE 
                WHEN u.user_type = 'vendor' THEN v.business_name
                WHEN u.user_type = 'customer' THEN CONCAT(c.first_name, ' ', c.last_name)
                ELSE 'Admin'
            END as display_name
            FROM withdrawal_requests wr
            LEFT JOIN users u ON wr.user_id = u.id
            LEFT JOIN vendors v ON u.id = v.user_id
            LEFT JOIN customers c ON u.id = c.user_id
            WHERE wr.status = 'pending'
            ORDER BY wr.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM withdrawal_requests
            WHERE status = 'pending'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL CANCELLATION REFUND
    // ──────────────────────────────────────────────────────────────
    case 'total_cancellation_refund':
        $columns = ['Txn ID', 'Created', 'Cancelled', 'Order #', 'Customer', 'Vendor', 'Status', 'Amount Refunded'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM orders
            WHERE status         = 'cancelled'
        ")['cnt'];

        $rows = $db->fetchAll("
            SELECT
                wt.id, wt.order_number, wt.subtotal, wt.status, wt.created_at, wt.updated_at,
                v.business_name, c.first_name, c.last_name, i.title
            FROM orders wt
            LEFT JOIN vendors v  ON wt.vendor_id    = v.id
            LEFT JOIN customers c  ON wt.customer_id    = c.id
            LEFT JOIN items i  ON wt.item_id    = i.id
            WHERE wt.status         = 'cancelled'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(subtotal), 0) AS total
            FROM orders
            WHERE status         = 'cancelled'
        ")['total'];
        break;

    // ──────────────────────────────────────────────────────────────
    // TOTAL CANCELLATION PENALTY
    // ──────────────────────────────────────────────────────────────
    case 'total_cancellation_penalty':
        $columns = ['Txn ID', 'Date', 'Customer', 'Vendor', 'Order #', 'Description', 'Penalty Amount', 'Status'];

        $total_rows = (int)$db->fetch("
            SELECT COUNT(*) AS cnt
            FROM wallet_transactions
            WHERE transaction_type IN ('admin_cancellation_penalty_received', 'vendor_cancellation_penalty_received')
              AND category = 'credit'
        ")['cnt'];

        $rows = $db->fetchAll("
             SELECT
                wt.id, wt.transaction_type, wt.amount, wt.description,
                wt.status, wt.created_at, wt.order_id, i.title,
                o.order_number, v.business_name,
                c.first_name AS first_name,
                c.last_name AS last_name
            FROM wallet_transactions wt
            LEFT JOIN orders o ON wt.order_id = o.id
            LEFT JOIN items i ON o.item_id = i.id
            LEFT JOIN  vendors v  ON o.vendor_id    = v.id
            LEFT JOIN customers  c ON o.customer_id  = c.id
            WHERE wt.transaction_type IN ('admin_cancellation_penalty_received', 'vendor_cancellation_penalty_received')
              AND wt.category = 'credit'
            ORDER BY wt.created_at DESC
            LIMIT {$per_page} OFFSET {$offset}
        ");

        $card_total = (float)$db->fetch("
            SELECT COALESCE(SUM(amount), 0) AS total
            FROM wallet_transactions
            WHERE transaction_type IN ('admin_cancellation_penalty_received', 'vendor_cancellation_penalty_received')
              AND category = 'credit'
        ")['total'];
        break;

} // end switch

$total_pages = $total_rows > 0 ? (int)ceil($total_rows / $per_page) : 1;

// ================================================================
// HELPERS
// ================================================================
function fmt($amount) {
    return '&#8358;' . number_format((float)$amount, 2);
}

function badge_status($status) {
    $map = [
        'completed' => 'success',  'onhold'   => 'warning',
        'released'  => 'success',  'cancelled'=> 'danger',
        'refunded'  => 'info',     'pending'  => 'secondary',
        'approved'  => 'success',  'paid'     => 'success',
        'failed'    => 'danger',   'reversed' => 'dark',
    ];
    $cls = $map[strtolower($status ?? '')] ?? 'secondary';
    return '<span class="badge bg-' . $cls . '">' . ucfirst(htmlspecialchars($status ?? '-')) . '</span>';
}

function txn_type_label($type) {
    $labels = [
        'item_rental'                              => 'Rental Payment',
        'security_deposit_held'                    => 'Security Held',
        'security_deposit_refund'                  => 'Security Refund',
        'rental_earning'                           => 'Rental Earning',
        'platform_commission'                      => 'Platform Commission',
        'withdrawal'                               => 'Withdrawal',
        'top_up'                                   => 'Wallet Top-up',
        'rental_refund'                            => 'Rental Refund',
        'rental_cancellation_refund'               => 'Cancellation Refund',
        'security_deposit_cancellation_refund'     => 'Security Cancel Refund',
        'vendor_cancellation_penalty_received'     => 'Vendor Penalty',
        'admin_cancellation_penalty_received'      => 'Admin Penalty Commission',
    ];
    return $labels[$type] ?? ucwords(str_replace('_', ' ', $type ?? ''));
}

include '../templates/layouts/admin/head.php';
?>

<div class="main-content">
    <?php include '../templates/layouts/admin/header.php'; ?>

<style>
.table-summary-row td { font-weight: bold; border-top: 2px solid #dee2e6; }
.hover-row:hover td  { background-color: rgba(0,0,0,.03); }
</style>

<div class="container-fluid py-4">

    <!-- Breadcrumb + Title -->
    <div class="row mb-3">
        <div class="col-12">
            <nav aria-label="breadcrumb">
                <ol class="breadcrumb mb-1">
                    <li class="breadcrumb-item"><a href="index.php">Dashboard</a></li>
                    <li class="breadcrumb-item active"><?= htmlspecialchars($meta['label']) ?></li>
                </ol>
            </nav>
            <h1 class="h3 mb-0"><?= htmlspecialchars($meta['label']) ?></h1>
            <p class="text-muted mt-1 mb-0"><?= htmlspecialchars($meta['description']) ?></p>
        </div>
    </div>

    <!-- Summary Banner -->
    <div class="row mb-4">
        <div class="col-md-4 mb-3">
            <div class="card shadow-sm h-100" style="border-left:5px solid #0d6efd;">
                <div class="card-body">
                    <h6 class="text-muted mb-1">Card Total Amount</h6>
                    <h2 class="mb-0 fw-bold text-<?= $meta['color'] ?>"><?= fmt($card_total) ?></h2>
                    <small class="text-muted">Matches dashboard card figure</small>
                </div>
            </div>
        </div>
        <div class="col-md-4 mb-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <h6 class="text-muted mb-1">Records Found</h6>
                    <h2 class="mb-0 fw-bold"><?= number_format($total_rows) ?></h2>
                    <small class="text-muted">Page <?= $page ?> of <?= $total_pages ?></small>
                </div>
            </div>
        </div>
        <div class="col-md-4 mb-3">
            <div class="card shadow-sm h-100">
                <div class="card-body">
                    <h6 class="text-muted mb-1">Source Table</h6>
                    <h5 class="mb-0 fw-bold text-secondary"><?= htmlspecialchars($meta['source']) ?></h5>
                    <small class="text-muted">Where this card's data comes from</small>
                </div>
            </div>
        </div>
    </div>

    <!-- Main Table Card -->
    <div class="card shadow-sm">
        <div class="card-header d-flex justify-content-between align-items-center">
            <h5 class="mb-0">Transaction Records</h5>
            <a href="index.php" class="btn btn-sm btn-outline-secondary">← Back to Dashboard</a>
        </div>

        <div class="card-body p-0">
            <?php if (empty($rows)): ?>
                <div class="text-center text-muted py-5">
                    <i class="fas fa-inbox fa-3x mb-3 d-block"></i>
                    <p class="mb-0">No records found for this filter.</p>
                </div>
            <?php else: ?>
            <div class="table-responsive">
            <table class="table table-hover table-striped table-sm align-middle mb-0">
                <thead class="table-light">
                    <tr>
                        <?php foreach ($columns as $col): ?>
                            <th class="px-3 py-2 text-nowrap"><?= htmlspecialchars($col) ?></th>
                        <?php endforeach; ?>
                    </tr>
                </thead>
                <tbody>

                    <?php // ── orders ──────────────────────────────────────────── ?>
                    <?php if ($filter === 'current_rent_onhold'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><a href="order-details.php?id=<?= $r['id'] ?>"><?= htmlspecialchars($r['order_number']) ?></a></td>
                            <td><?= htmlspecialchars($r['customer_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['vendor_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['item_title'] ?? '-') ?></td>
                            <td class="text-nowrap"><small><?= htmlspecialchars($r['start_date']) ?> → <?= htmlspecialchars($r['end_date']) ?></small></td>
                            <td class="text-end fw-bold"><?= fmt($r['subtotal']) ?></td>
                            <td class="text-end"><?= fmt($r['security_deposit']) ?></td>
                            <td class="text-end"><?= fmt($r['total_amount']) ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                            <td><?= badge_status($r['payment_status']) ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y', strtotime($r['created_at'])) ?></small></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── order_payment_breakdown ──────────────────────────── ?>
                    <?php if ($filter === 'current_security'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td><a href="order-details.php?id=<?= $r['order_id'] ?>"><?= htmlspecialchars($r['order_number'] ?? '-') ?></a></td>
                            <td><?= htmlspecialchars($r['customer_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars(trim(($r['vendor_name'] ?? '') . ' — ' . ($r['item_title'] ?? ''), ' — ')) ?></td>
                            <td class="text-end fw-bold text-danger"><?= fmt($r['amount']) ?></td>
                            <td class="text-end"><?= fmt($r['total_amount']) ?></td>
                            <td><?= htmlspecialchars(ucfirst($r['source'] ?? '-')) ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y', strtotime($r['created_at'])) ?></small></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php if ($filter === 'vendor_earnings_onhold'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap">
                                <small><b><?= date('M d, Y', strtotime($r['created_at'])) ?></b><br>
                                
                                    <span class="badge bg-info"><?= date('M d', strtotime($r['start_date']))." - ".date('M d, Y', strtotime($r['end_date'])) ?></span><br>
                                    <span class="badge bg-success"><?php echo ((strtotime($r['end_date']) - strtotime($r['start_date']))/86400) + 1 . ' days'; ?></span>
                                </small>                            
                            </td>
                            <td><a href="order-details.php?id=<?= $r['order_id'] ?>"><?= htmlspecialchars($r['order_number'] ?? '-') ?></a><br><?= htmlspecialchars($r['item_title'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['vendor_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['customer_name'] ?? '-') ?></td>
                            <td class="text-end"><?= fmt($r['total_amount']) ?></td>
                            <td align='center'><?= htmlspecialchars(ucfirst($r['source'] ?? '-')) ?></td>
                            <td class="text-end fw-bold text-success"><?= fmt($r['amount']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── wallet_transactions (commission / admin) ──────────── ?>
                    <?php if (in_array($filter, ['available_commission', 'total_commission_earned'])):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td><span class="badge bg-success"><?= txn_type_label($r['transaction_type']) ?></span></td>
                            <td><?= !empty($r['order_number']) ? '<a href="order-details.php?id=' . $r['order_id'] . '">' . htmlspecialchars($r['order_number']) . '</a>' : '-' ?></td>
                            <td><small><?= htmlspecialchars(substr($r['description'] ?? '', 0, 65)) ?></small></td>
                            <td class="text-end fw-bold text-success">+<?= fmt($r['amount']) ?></td>
                            <td class="text-end"><?= fmt($r['balance_after']) ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── wallet_transactions (customers spent) ────────────── ?>
                    <?php if ($filter === 'total_customers_spent'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td><?= htmlspecialchars($r['user_name'] ?? '-') ?></td>
                            <td><?= !empty($r['order_number']) ? '<a href="order-details.php?id=' . $r['order_id'] . '">' . htmlspecialchars($r['order_number']) . '</a>' : '-' ?></td>
                            <td><?= htmlspecialchars($r['item_title'] ?? '-') ?></td>
                            <td><small><?= htmlspecialchars(substr($r['description'] ?? '', 0, 55)) ?></small></td>
                            <td class="text-end fw-bold text-danger">-<?= fmt($r['amount']) ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── wallet_transactions (vendors earned) ─────────────── ?>
                    <?php if ($filter === 'total_vendors_earned'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td><?= htmlspecialchars($r['user_name'] ?? '-') ?></td>
                            <td><?= !empty($r['order_number']) ? '<a href="order-details.php?id=' . $r['order_id'] . '">' . htmlspecialchars($r['order_number']) . '</a>' : '-' ?></td>
                            <td><?= htmlspecialchars($r['item_title'] ?? '-') ?></td>
                            <td><small><?= htmlspecialchars(substr($r['description'] ?? '', 0, 55)) ?></small></td>
                            <td class="text-end fw-bold text-success">+<?= fmt($r['amount']) ?></td>
                            <td class="text-end"><?= fmt($r['balance_after']) ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── wallet_transactions (cancellation refund) ─────────── ?>
                    <?php if ($filter === 'total_cancellation_refund'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['updated_at'])) ?></small></td>
                            <td><?= '<a href="order-details.php?id=' . $r['order_number'] . '">' . htmlspecialchars($r['order_number']) . '</a><br>' . htmlspecialchars($r['title']) ?></td>
                            <td><?= htmlspecialchars($r['first_name'] ?? '')." ".htmlspecialchars($r['last_name'] ?? '') ?></td>
                            <td><?= htmlspecialchars($r['business_name'] ?? '') ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                            <td class="text-end fw-bold text-success">+<?= fmt($r['subtotal']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── wallet_transactions (cancellation penalty) ────────── ?>
                    <?php if ($filter === 'total_cancellation_penalty'):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td><?= htmlspecialchars($r['first_name'] ?? '-')." ".htmlspecialchars($r['last_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['business_name'] ?? '-') ?></td>
                            <td><?= !empty($r['order_number']) ? '<a href="order-details.php?id=' . $r['order_id'] . '">' . htmlspecialchars($r['order_number']) . '</a>' : '-' ?><br><?= htmlspecialchars($r['title']) ?></td>
                            <td><small><?= htmlspecialchars(substr($r['description'] ?? '', 0, 65)) ?></small></td>
                            <td><?= badge_status($r['status']) ?></td>
                            <td class="text-end fw-bold text-success">+<?= fmt($r['amount']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                    <?php // ── withdrawal_requests ──────────────────────────────── ?>
                    <?php if (in_array($filter, ['total_withdrawn', 'pending_withdrawals'])):
                        foreach ($rows as $r): ?>
                        <tr class="hover-row">
                            <td class="px-3"><?= $r['id'] ?></td>
                            <td class="text-nowrap"><small><?= date('M d, Y H:i', strtotime($r['created_at'])) ?></small></td>
                            <td><code class="small"><?= htmlspecialchars($r['reference'] ?? '-') ?></code></td>
                            <td><?= htmlspecialchars($r['display_name'] ?? '-') ?></td>
                            <td><span class="badge bg-secondary"><?= ucfirst($r['user_type'] ?? '-') ?></span></td>
                            <td><?= htmlspecialchars($r['bank_name'] ?? '-') ?></td>
                            <td><?= htmlspecialchars($r['account_number'] ?? '-') ?></td>
                            <td><?= badge_status($r['status']) ?></td>
                            <td class="text-end fw-bold"><?= fmt($r['amount']) ?></td>
                        </tr>
                        <?php endforeach; endif; ?>

                </tbody>
                <tfoot>
                    <tr class="table-summary-row" style="background:#fff3cd;">
                        <td colspan="<?= count($columns) - 1 ?>" class="text-end px-3 py-2">
                            Page <?= $page ?> subtotal:
                        </td>
                        <td class="text-end px-3 py-2">
                            <?php
                            $page_total = 0;
                            foreach ($rows as $r) {
                                $page_total += isset($r['subtotal'])
                                    ? floatval($r['subtotal'])
                                    : floatval($r['amount'] ?? 0);
                            }
                            echo fmt($page_total);
                            ?>
                        </td>
                    </tr>
                    <tr class="table-summary-row" style="background:#d4edda;">
                        <td colspan="<?= count($columns) - 1 ?>" class="text-end px-3 py-2 fw-bold">
                            Grand total (all pages):
                        </td>
                        <td class="text-end px-3 py-2 fw-bold text-success"><?= fmt($card_total) ?></td>
                    </tr>
                </tfoot>
            </table>
            </div><!-- /table-responsive -->
            <?php endif; ?>
        </div><!-- /card-body -->

        <?php if ($total_pages > 1): ?>
        <div class="card-footer bg-white">
            <nav>
                <ul class="pagination pagination-sm justify-content-center mb-0">
                    <?php if ($page > 1): ?>
                        <li class="page-item">
                            <a class="page-link" href="?filter=<?= urlencode($filter) ?>&page=<?= $page - 1 ?>">« Prev</a>
                        </li>
                    <?php endif; ?>
                    <?php
                    $pstart = max(1, $page - 2);
                    $pend   = min($total_pages, $page + 2);
                    for ($i = $pstart; $i <= $pend; $i++): ?>
                        <li class="page-item <?= $i === $page ? 'active' : '' ?>">
                            <a class="page-link" href="?filter=<?= urlencode($filter) ?>&page=<?= $i ?>"><?= $i ?></a>
                        </li>
                    <?php endfor; ?>
                    <?php if ($page < $total_pages): ?>
                        <li class="page-item">
                            <a class="page-link" href="?filter=<?= urlencode($filter) ?>&page=<?= $page + 1 ?>">Next »</a>
                        </li>
                    <?php endif; ?>
                </ul>
            </nav>
        </div>
        <?php endif; ?>

    </div><!-- /card -->
</div><!-- /container -->

<?php include '../templates/layouts/admin/footer.php'; ?>
</div>