<?php
// Sales Analytics Page Content
require_once dirname(dirname(__DIR__)) . '/config.php';

// Set page variables for header
$page_title = 'Sales Analytics';
$page_description = 'View detailed sales analytics and performance metrics';

$date = isset($_GET['date']) && $_GET['date'] !== '' ? $_GET['date'] : date('Y-m-d');
$prevDate = date('Y-m-d', strtotime($date . ' -1 day'));
?>


<?php

function getScalar($conn, $sql, $types = '', $params = []) {
    $stmt = $conn->prepare($sql);
    if (!$stmt) { return 0; }
    if ($types !== '' && $params) { $stmt->bind_param($types, ...$params); }
    $stmt->execute();
    $res = $stmt->get_result();
    if (!$res) { return 0; }
    $row = $res->fetch_row();
    return $row ? (float)$row[0] : 0;
}

function detectDateColumn(mysqli $conn, string $table, array $candidates): string {
    foreach ($candidates as $col) {
        $q = $conn->query("SHOW COLUMNS FROM `$table` LIKE '" . $conn->real_escape_string($col) . "'");
        if ($q && $q->num_rows > 0) {
            $q->close();
            return $col;
        }
        if ($q) { $q->close(); }
    }
    // Fallback to first datetime-like column if any
    $q = $conn->query("SHOW COLUMNS FROM `$table`");
    if ($q) {
        while ($row = $q->fetch_assoc()) {
            if (stripos($row['Type'], 'datetime') !== false || stripos($row['Type'], 'timestamp') !== false || stripos($row['Field'], 'date') !== false) {
                $col = $row['Field'];
                $q->close();
                return $col;
            }
        }
        $q->close();
    }
    // Last resort: use 1=1 and ignore date filters
    return '';
}

function parsePackageSizeToGb($label) {
    $label = trim((string)$label);
    
    if ($label === '') {
        return 0;
    }
    
    if (preg_match('/(\d+(?:\.\d+)?)\s*GB/i', $label, $matches)) {
        return (float)$matches[1];
    }
    
    if (preg_match('/(\d+(?:\.\d+)?)\s*MB/i', $label, $matches)) {
        return round(((float)$matches[1]) / 1024, 4);
    }
    
    if (preg_match('/(\d+(?:\.\d+)?)/', $label, $matches)) {
        $value = (float)$matches[1];
        return $value <= 100 ? $value : round($value / 1024, 4);
    }
    
    return 0;
}

// Detect timestamp columns
$ordersTs = detectDateColumn($conn, 'orders', ['created_at','order_date','created','date']);
$agentOrdersTs = detectDateColumn($conn, 'agent_orders', ['created_at','order_date','created','date']);
$paymentsTs = detectDateColumn($conn, 'orders_payments', ['created_at','created','date','paid_at','timestamp','added_at']);
$topupTs = detectDateColumn($conn, 'balance_history', ['created_at','timestamp','date']);

// Daily sales: sum of delivered + pending orders amount (orders and agent_orders) for date
$dailySales = 0;
if ($ordersTs !== '') {
    $salesSqlOrders = "SELECT COALESCE(SUM(amount),0) FROM orders WHERE status IN ('delivered','pending') AND DATE(`$ordersTs`)=?";
    $dailySales += getScalar($conn, $salesSqlOrders, 's', [$date]);
}
if ($agentOrdersTs !== '') {
    $salesSqlAgent = "SELECT COALESCE(SUM(amount),0) FROM agent_orders WHERE status IN ('delivered','pending') AND DATE(`$agentOrdersTs`)=?";
    $dailySales += getScalar($conn, $salesSqlAgent, 's', [$date]);
}

// Previous day sales for change
$prevSales = 0;
if (isset($salesSqlOrders)) { $prevSales += getScalar($conn, $salesSqlOrders, 's', [$prevDate]); }
if (isset($salesSqlAgent)) { $prevSales += getScalar($conn, $salesSqlAgent, 's', [$prevDate]); }
$revChange = $prevSales == 0 ? ($dailySales > 0 ? 100 : 0) : (($dailySales - $prevSales) / $prevSales) * 100;

// Total agent balances (store owners only)
$totalAgentBalances = getScalar($conn, "SELECT COALESCE(SUM(balance),0) FROM agents WHERE is_store_owner = 1");

// Daily topups for store owners only (admin credits - balance_history records with reason 'payment' or manual adjustments)
$dailyTopups = 0;
if ($topupTs !== '') {
    $topupSql = "SELECT COALESCE(SUM(bh.change_amount),0) 
                 FROM balance_history bh
                 INNER JOIN agents a ON bh.agent_id = a.id
                 WHERE a.is_store_owner = 1 
                 AND bh.change_amount > 0 
                 AND bh.reason IN ('payment','manual_adjustment') 
                 AND DATE(bh.`$topupTs`)=?";
    $dailyTopups = getScalar($conn, $topupSql, 's', [$date]);
}

// Total agents
$totalAgents = (int)getScalar($conn, "SELECT COUNT(*) FROM agents");

// Data served (sum of delivered packages in GB)
$dailyDataServedGb = 0;
// Pending packages (sum of package sizes for pending orders)
$pendingPackagesGb = 0;
if ($agentOrdersTs !== '') {
    $stmt = $conn->prepare("SELECT package_size FROM agent_orders WHERE status='pending' AND DATE(`$agentOrdersTs`)=?");
    if ($stmt) {
        $stmt->bind_param('s', $date);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $pendingPackagesGb += parsePackageSizeToGb($row['package_size']);
            }
        }
        $stmt->close();
    }
}
if ($agentOrdersTs !== '') {
    $stmt = $conn->prepare("SELECT package_size FROM agent_orders WHERE status='delivered' AND DATE(`$agentOrdersTs`)=?");
    if ($stmt) {
        $stmt->bind_param('s', $date);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $dailyDataServedGb += parsePackageSizeToGb($row['package_size']);
            }
        }
        $stmt->close();
    }
}

// NEW METRICS FOR SALES ANALYTICS

// 1. All credits added by admin to store owners within a particular day
$adminCreditsToStoreOwners = 0;
if ($topupTs !== '') {
    $creditsSql = "SELECT COALESCE(SUM(bh.change_amount), 0) 
                   FROM balance_history bh
                   INNER JOIN agents a ON bh.agent_id = a.id
                   INNER JOIN stores s ON s.owner_id = a.id
                   WHERE bh.change_amount > 0 
                   AND bh.reason IN ('payment', 'manual_adjustment')
                   AND DATE(bh.`$topupTs`) = ?";
    $adminCreditsToStoreOwners = getScalar($conn, $creditsSql, 's', [$date]);
}

// 2. Total package size in gigabytes placed on every store for a particular day (per store breakdown)
$storePackageSizes = [];
// Get orders package sizes
$ordersGbStmt = $conn->prepare("
    SELECT 
        o.store_id,
        s.store_name,
        o.package_size
    FROM orders o
    INNER JOIN stores s ON o.store_id = s.id
    WHERE DATE(o.created_at) = ?
");
$ordersGbData = [];
if ($ordersGbStmt) {
    $ordersGbStmt->bind_param('s', $date);
    if ($ordersGbStmt->execute()) {
        $result = $ordersGbStmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $storeId = $row['store_id'];
            if (!isset($ordersGbData[$storeId])) {
                $ordersGbData[$storeId] = ['store_name' => $row['store_name'], 'total_gb' => 0];
            }
            $ordersGbData[$storeId]['total_gb'] += parsePackageSizeToGb($row['package_size']);
        }
    }
    $ordersGbStmt->close();
}

// Get agent_orders package sizes
$agentOrdersGbStmt = $conn->prepare("
    SELECT 
        ao.store_id,
        s.store_name,
        ao.package_size
    FROM agent_orders ao
    INNER JOIN stores s ON ao.store_id = s.id
    WHERE DATE(ao.created_at) = ?
");
if ($agentOrdersGbStmt) {
    $agentOrdersGbStmt->bind_param('s', $date);
    if ($agentOrdersGbStmt->execute()) {
        $result = $agentOrdersGbStmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $storeId = $row['store_id'];
            if (!isset($ordersGbData[$storeId])) {
                $ordersGbData[$storeId] = ['store_name' => $row['store_name'], 'total_gb' => 0];
            }
            $ordersGbData[$storeId]['total_gb'] += parsePackageSizeToGb($row['package_size']);
        }
    }
    $agentOrdersGbStmt->close();
}

// Convert to array format
foreach ($ordersGbData as $storeId => $data) {
    if ($data['total_gb'] > 0) {
        $storePackageSizes[] = [
            'store_id' => $storeId,
            'store_name' => $data['store_name'],
            'total_gb' => $data['total_gb']
        ];
    }
}
// Sort by total_gb descending
usort($storePackageSizes, function($a, $b) {
    return $b['total_gb'] <=> $a['total_gb'];
});

// 3. Sum of all amount of orders placed in a store for a particular day (per store breakdown)
$storeOrderAmounts = [];
$storeAmountStmt = $conn->prepare("
    SELECT 
        s.id as store_id,
        s.store_name,
        COALESCE(SUM(o.amount), 0) as total_amount_orders,
        COALESCE(SUM(ao.amount), 0) as total_amount_agent_orders
    FROM stores s
    LEFT JOIN orders o ON s.id = o.store_id AND DATE(o.created_at) = ?
    LEFT JOIN agent_orders ao ON s.id = ao.store_id AND DATE(ao.created_at) = ?
    GROUP BY s.id, s.store_name
    ORDER BY (total_amount_orders + total_amount_agent_orders) DESC
");
if ($storeAmountStmt) {
    $storeAmountStmt->bind_param('ss', $date, $date);
    if ($storeAmountStmt->execute()) {
        $result = $storeAmountStmt->get_result();
        while ($row = $result->fetch_assoc()) {
            $totalAmount = (float)$row['total_amount_orders'] + (float)$row['total_amount_agent_orders'];
            if ($totalAmount > 0) {
                $storeOrderAmounts[] = [
                    'store_id' => $row['store_id'],
                    'store_name' => $row['store_name'],
                    'total_amount' => $totalAmount
                ];
            }
        }
    }
    $storeAmountStmt->close();
}

// 4. Total number of stores and total number of sub agents
$totalStores = (int)getScalar($conn, "SELECT COUNT(*) FROM stores WHERE status = 'active'");
$totalSubAgents = (int)getScalar($conn, "SELECT COUNT(*) FROM agents WHERE is_store_owner = 0 AND store_id IS NOT NULL");

// 5. Cost - Total cost of all orders (sum of amounts from orders and agent_orders for the day)
// This is the same as dailySales, but we'll keep it separate for clarity
$totalOrderCost = $dailySales;

// 6. Sum of all orders in gigabytes for a day (including both delivered and pending)
$totalOrdersGb = $dailyDataServedGb + $pendingPackagesGb;
// Also include orders from 'orders' table
if ($ordersTs !== '') {
    $stmt = $conn->prepare("SELECT package_size FROM orders WHERE DATE(`$ordersTs`)=?");
    if ($stmt) {
        $stmt->bind_param('s', $date);
        if ($stmt->execute()) {
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $totalOrdersGb += parsePackageSizeToGb($row['package_size']);
            }
        }
        $stmt->close();
    }
}

// 7. Total sum of all amount of orders placed in a day (already calculated as dailySales)
$totalOrdersAmount = $dailySales;
?>

<!-- Professional Page Header -->
<div class="bg-gray-800 border border-gray-700 text-white p-8 rounded-lg mb-8 shadow-lg">
    <div class="flex items-center justify-between">
        <div>
            <h1 class="text-4xl font-bold mb-2 flex items-center text-white">
                <i class="fas fa-chart-line mr-4 text-gray-300"></i>
                Sales Analytics
            </h1>
            <p class="text-gray-400 text-lg">Comprehensive sales performance and revenue insights</p>
            <p class="text-gray-500 text-sm mt-2">
                <i class="fas fa-calendar-alt mr-2"></i>
                Last updated: <?= date('M j, Y \a\t g:i A') ?>
            </p>
        </div>
        <div class="text-right">
            <div class="bg-gray-700 border border-gray-600 rounded-lg p-4">
                <p class="text-gray-400 text-sm">Total Revenue Today</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($dailySales, 2) ?></p>
            </div>
        </div>
    </div>
</div>

<!-- Enhanced Date Filter -->
<div class="bg-gray-800/50 backdrop-blur-sm rounded-2xl p-6 mb-8 border border-gray-700/50 shadow-xl">
    <div class="flex items-center justify-between mb-4">
        <h3 class="text-xl font-semibold text-white flex items-center">
            <i class="fas fa-filter mr-3 text-blue-400"></i>
            Date Filter
        </h3>
        <div class="flex space-x-2">
            <button onclick="setDateFilter('today')" class="px-3 py-1 bg-blue-600 hover:bg-blue-700 text-white rounded-lg text-sm transition-all">
                Today
            </button>
            <button onclick="setDateFilter('yesterday')" class="px-3 py-1 bg-gray-600 hover:bg-gray-700 text-white rounded-lg text-sm transition-all">
                Yesterday
            </button>
            <button onclick="setDateFilter('week')" class="px-3 py-1 bg-gray-600 hover:bg-gray-700 text-white rounded-lg text-sm transition-all">
                This Week
            </button>
        </div>
    </div>
    <form method="GET" action="index.php" class="flex items-center space-x-4">
        <input type="hidden" name="page" value="sales_analytics">
        <div class="flex-1">
            <label class="block text-sm font-medium text-gray-300 mb-2">Select Date</label>
            <input type="date" name="date" value="<?= htmlspecialchars($date) ?>" 
                   class="w-full bg-gray-700 border-2 border-gray-600 rounded-xl p-3 text-white focus:border-blue-500 focus:ring-2 focus:ring-blue-500/20 transition-all" />
        </div>
        <div class="flex items-end">
            <button type="submit" class="bg-gradient-to-r from-blue-600 to-purple-600 hover:from-blue-700 hover:to-purple-700 text-white px-6 py-3 rounded-xl font-semibold transition-all transform hover:scale-105 shadow-lg">
                <i class="fas fa-search mr-2"></i>
                Apply Filter
            </button>
        </div>
    </form>
</div>

<!-- Enhanced Statistics Cards -->
<div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-8">
    <!-- Daily Sales Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-money-bill-wave text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Daily Sales</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($dailySales, 2) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-calendar-day mr-1"></i>
                Delivered + pending orders on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">orders</code> & <code class="text-gray-500">agent_orders</code> status in (<code class="text-gray-500">delivered</code>, <code class="text-gray-500">pending</code>).
            </p>
        </div>
    </div>

    <!-- Total Agent Balances Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-wallet text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Store Owner Balances</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($totalAgentBalances, 2) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-store mr-1"></i>
                Sum of all store owner account balances
            </p>
        </div>
    </div>

    <!-- Daily Topups Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-credit-card text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Topups Today</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($dailyTopups, 2) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-arrow-up mr-1"></i>
                Admin credits to store owners on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">balance_history</code> for store owners only, reason in (<code class="text-gray-500">payment</code>, <code class="text-gray-500">manual_adjustment</code>).
            </p>
        </div>
    </div>

    <!-- Data Served Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-signal text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Data Served</p>
                <p class="text-3xl font-bold text-white"><?= number_format($dailyDataServedGb, 2) ?> GB</p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-check-double mr-1"></i>
                Delivered agent orders on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">agent_orders</code> status <code class="text-gray-500">delivered</code>; package sizes converted to GB.
            </p>
        </div>
    </div>
</div>

<!-- Secondary Statistics Cards -->
<div class="grid grid-cols-1 md:grid-cols-2 gap-6 mb-8">
    <!-- Total Agents Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-user-friends text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Total Agents</p>
                <p class="text-3xl font-bold text-white"><?= number_format($totalAgents) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-check-circle mr-1"></i>
                Active agents in system
            </p>
        </div>
    </div>

    <!-- Pending Packages Card -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-hourglass-half text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Pending Packages</p>
                <p class="text-3xl font-bold text-white"><?= number_format($pendingPackagesGb, 2) ?> GB</p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-clock mr-1"></i>
                Pending agent orders on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">agent_orders</code> status <code class="text-gray-500">pending</code>, package sizes converted to GB.
            </p>
        </div>
    </div>
</div>

<!-- NEW METRICS SECTION -->
<div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-4 gap-6 mb-8">
    <!-- 1. Admin Credits to Store Owners -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-hand-holding-usd text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Admin Credits</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($adminCreditsToStoreOwners, 2) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-user-shield mr-1"></i>
                Credits to store owners on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">balance_history</code> with reason <code class="text-gray-500">payment</code> or <code class="text-gray-500">manual_adjustment</code>
            </p>
        </div>
    </div>

    <!-- 4. Total Stores and Sub Agents -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-store text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Total Stores</p>
                <p class="text-3xl font-bold text-white"><?= number_format($totalStores) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-users-cog mr-1"></i>
                Sub Agents: <?= number_format($totalSubAgents) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Active stores and their sub-agents
            </p>
        </div>
    </div>

    <!-- 5. Total Order Cost -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-receipt text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Total Cost</p>
                <p class="text-3xl font-bold text-white">GHS <?= number_format($totalOrderCost, 2) ?></p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-calculator mr-1"></i>
                Sum of all order amounts on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Source: <code class="text-gray-500">orders</code> & <code class="text-gray-500">agent_orders</code> tables
            </p>
        </div>
    </div>

    <!-- 6. Total Orders in GB -->
    <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg hover:shadow-xl transition-all duration-300">
        <div class="flex items-center justify-between mb-4">
            <div class="bg-gray-700 rounded-lg p-3">
                <i class="fas fa-database text-2xl text-gray-300"></i>
            </div>
            <div class="text-right">
                <p class="text-gray-400 text-sm font-medium">Total Orders (GB)</p>
                <p class="text-3xl font-bold text-white"><?= number_format($totalOrdersGb, 2) ?> GB</p>
            </div>
        </div>
        <div class="bg-gray-700/50 rounded-lg p-3 border border-gray-600">
            <p class="text-gray-300 text-xs">
                <i class="fas fa-chart-bar mr-1"></i>
                Sum of all orders in GB on <?= htmlspecialchars($date) ?>
            </p>
            <p class="text-gray-400 text-[11px] mt-1">
                Includes delivered and pending orders
            </p>
        </div>
    </div>
</div>

<!-- Summary Card for Total Orders Amount (Metric 7) -->
<div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white shadow-lg mb-8">
    <div class="flex items-center justify-between">
        <div>
            <h3 class="text-xl font-bold mb-2 flex items-center text-white">
                <i class="fas fa-coins mr-3 text-gray-300"></i>
                Total Orders Amount - <?= htmlspecialchars($date) ?>
            </h3>
            <p class="text-gray-400 text-sm">Sum of all order amounts placed on this day</p>
        </div>
        <div class="text-right">
            <p class="text-gray-400 text-sm font-medium mb-1">Total Amount</p>
            <p class="text-4xl font-bold text-white">GHS <?= number_format($totalOrdersAmount, 2) ?></p>
        </div>
    </div>
</div>

<!-- Store Breakdown Section -->
<div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-8">
    <!-- Store Package Sizes Breakdown -->
    <div class="bg-gray-800/50 backdrop-blur-sm rounded-2xl p-6 border border-gray-700/50 shadow-xl">
        <h3 class="text-xl font-bold text-white mb-4 flex items-center">
            <i class="fas fa-chart-pie mr-3 text-blue-400"></i>
            Package Size by Store (GB) - <?= htmlspecialchars($date) ?>
        </h3>
        <div class="overflow-x-auto">
            <table class="w-full text-sm">
                <thead>
                    <tr class="border-b border-gray-700">
                        <th class="text-left py-2 px-3 text-gray-300">Store Name</th>
                        <th class="text-right py-2 px-3 text-gray-300">Total GB</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($storePackageSizes)): ?>
                        <tr>
                            <td colspan="2" class="text-center py-4 text-gray-400">No orders found for this date</td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($storePackageSizes as $store): ?>
                        <tr class="border-b border-gray-700/50 hover:bg-gray-700/30">
                            <td class="py-2 px-3 text-white"><?= htmlspecialchars($store['store_name']) ?></td>
                            <td class="py-2 px-3 text-right text-green-400 font-semibold"><?= number_format($store['total_gb'], 2) ?> GB</td>
                        </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr class="border-t-2 border-gray-600 font-bold">
                        <td class="py-2 px-3 text-white">Total</td>
                        <td class="py-2 px-3 text-right text-green-400">
                            <?= number_format(array_sum(array_column($storePackageSizes, 'total_gb')), 2) ?> GB
                        </td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>

    <!-- Store Order Amounts Breakdown -->
    <div class="bg-gray-800/50 backdrop-blur-sm rounded-2xl p-6 border border-gray-700/50 shadow-xl">
        <h3 class="text-xl font-bold text-white mb-4 flex items-center">
            <i class="fas fa-money-bill-wave mr-3 text-green-400"></i>
            Order Amount by Store - <?= htmlspecialchars($date) ?>
        </h3>
        <div class="overflow-x-auto">
            <table class="w-full text-sm">
                <thead>
                    <tr class="border-b border-gray-700">
                        <th class="text-left py-2 px-3 text-gray-300">Store Name</th>
                        <th class="text-right py-2 px-3 text-gray-300">Total Amount</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if (empty($storeOrderAmounts)): ?>
                        <tr>
                            <td colspan="2" class="text-center py-4 text-gray-400">No orders found for this date</td>
                        </tr>
                    <?php else: ?>
                        <?php foreach ($storeOrderAmounts as $store): ?>
                        <tr class="border-b border-gray-700/50 hover:bg-gray-700/30">
                            <td class="py-2 px-3 text-white"><?= htmlspecialchars($store['store_name']) ?></td>
                            <td class="py-2 px-3 text-right text-green-400 font-semibold">GHS <?= number_format($store['total_amount'], 2) ?></td>
                        </tr>
                        <?php endforeach; ?>
                    <?php endif; ?>
                </tbody>
                <tfoot>
                    <tr class="border-t-2 border-gray-600 font-bold">
                        <td class="py-2 px-3 text-white">Total</td>
                        <td class="py-2 px-3 text-right text-green-400">
                            GHS <?= number_format(array_sum(array_column($storeOrderAmounts, 'total_amount')), 2) ?>
                        </td>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>
</div>

<!-- Enhanced Revenue Change Section -->
<div class="bg-gray-800/50 backdrop-blur-sm rounded-2xl p-8 mb-8 border border-gray-700/50 shadow-xl">
    <div class="flex items-center justify-between mb-6">
        <h2 class="text-2xl font-bold text-white flex items-center">
            <i class="fas fa-chart-line mr-3 text-blue-400"></i>
            Revenue Performance
        </h2>
        <div class="flex items-center space-x-4">
            <div class="text-right">
                <p class="text-gray-300 text-sm">Previous Day</p>
                <p class="text-white font-semibold"><?= htmlspecialchars($prevDate) ?></p>
            </div>
            <div class="text-right">
                <p class="text-gray-300 text-sm">Current Day</p>
                <p class="text-white font-semibold"><?= htmlspecialchars($date) ?></p>
            </div>
        </div>
    </div>
    
    <div class="grid grid-cols-1 md:grid-cols-3 gap-6">
        <!-- Revenue Change Card -->
        <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white">
            <div class="flex items-center justify-between mb-4">
                <div class="bg-gray-700 rounded-lg p-3">
                    <i class="fas <?= $revChange >= 0 ? 'fa-arrow-up' : 'fa-arrow-down' ?> text-2xl <?= $revChange >= 0 ? 'text-green-400' : 'text-red-400' ?>"></i>
                </div>
                <div class="text-right">
                    <p class="text-gray-400 text-sm font-medium">Revenue Change</p>
                    <p class="text-4xl font-bold <?= $revChange >= 0 ? 'text-green-400' : 'text-red-400' ?>">
                        <?= ($revChange >= 0 ? '+' : '') . number_format($revChange, 2) ?>%
                    </p>
                </div>
            </div>
            <p class="text-gray-400 text-sm">
                Compared to previous day
            </p>
        </div>

        <!-- Previous Day Sales -->
        <div class="bg-gray-700/50 rounded-xl p-6 border border-gray-600">
            <div class="flex items-center justify-between mb-4">
                <div class="bg-gray-600/50 rounded-lg p-3">
                    <i class="fas fa-calendar-minus text-2xl text-gray-300"></i>
                </div>
                <div class="text-right">
                    <p class="text-gray-300 text-sm font-medium">Previous Day</p>
                    <p class="text-2xl font-bold text-white">GHS <?= number_format($prevSales, 2) ?></p>
                </div>
            </div>
            <p class="text-gray-400 text-sm">
                Sales on <?= htmlspecialchars($prevDate) ?>
            </p>
        </div>

        <!-- Current Day Sales -->
        <div class="bg-gray-700/50 rounded-xl p-6 border border-gray-600">
            <div class="flex items-center justify-between mb-4">
                <div class="bg-gray-600/50 rounded-lg p-3">
                    <i class="fas fa-calendar-day text-2xl text-gray-300"></i>
                </div>
                <div class="text-right">
                    <p class="text-gray-300 text-sm font-medium">Current Day</p>
                    <p class="text-2xl font-bold text-white">GHS <?= number_format($dailySales, 2) ?></p>
                </div>
            </div>
            <p class="text-gray-400 text-sm">
                Sales on <?= htmlspecialchars($date) ?>
            </p>
        </div>
    </div>
</div>

<!-- Additional Analytics Section -->
<div class="bg-gray-800/50 backdrop-blur-sm rounded-2xl p-8 border border-gray-700/50 shadow-xl">
    <h3 class="text-2xl font-bold text-white mb-6 flex items-center">
        <i class="fas fa-chart-bar mr-3 text-blue-400"></i>
        Performance Insights
    </h3>
    
    <div class="grid grid-cols-1 md:grid-cols-2 gap-6">
        <!-- Sales Efficiency -->
        <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white">
            <div class="flex items-center justify-between mb-4">
                <div>
                    <h4 class="text-lg font-semibold text-white">Sales Efficiency</h4>
                    <p class="text-gray-400 text-sm">Revenue per agent</p>
                </div>
                <div class="bg-gray-700 rounded-lg p-3">
                    <i class="fas fa-percentage text-2xl text-gray-300"></i>
                </div>
            </div>
            <p class="text-3xl font-bold text-white">
                GHS <?= $totalAgents > 0 ? number_format($dailySales / $totalAgents, 2) : '0.00' ?>
            </p>
        </div>

        <!-- Topup Ratio -->
        <div class="bg-gray-800 border border-gray-700 rounded-lg p-6 text-white">
            <div class="flex items-center justify-between mb-4">
                <div>
                    <h4 class="text-lg font-semibold text-white">Topup Ratio</h4>
                    <p class="text-gray-400 text-sm">Topups vs Sales</p>
                </div>
                <div class="bg-gray-700 rounded-lg p-3">
                    <i class="fas fa-balance-scale text-2xl text-gray-300"></i>
                </div>
            </div>
            <p class="text-3xl font-bold text-white">
                <?= $dailySales > 0 ? number_format(($dailyTopups / $dailySales) * 100, 1) : '0.0' ?>%
            </p>
        </div>
    </div>
</div>

<script>
function setDateFilter(type) {
    const dateInput = document.querySelector('input[name="date"]');
    const today = new Date();
    let targetDate;
    
    switch(type) {
        case 'today':
            targetDate = today;
            break;
        case 'yesterday':
            targetDate = new Date(today);
            targetDate.setDate(today.getDate() - 1);
            break;
        case 'week':
            targetDate = new Date(today);
            targetDate.setDate(today.getDate() - 7);
            break;
    }
    
    dateInput.value = targetDate.toISOString().split('T')[0];
}
</script>

