<?php
require_once __DIR__ . '/../functions.php';
requireAdmin();
$page_title = 'Reports';
$db = db();

// Revenue today
$today = $db->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE DATE(created_at) = CURDATE() AND status != 'cancelled'")->fetchColumn();
$thisMonth = $db->query("SELECT COALESCE(SUM(total), 0) FROM orders WHERE MONTH(created_at) = MONTH(CURDATE()) AND YEAR(created_at) = YEAR(CURDATE()) AND status != 'cancelled'")->fetchColumn();
$avgOrderValue = $db->query("SELECT COALESCE(AVG(total), 0) FROM orders WHERE status != 'cancelled'")->fetchColumn();
$totalOrders = $db->query("SELECT COUNT(*) FROM orders")->fetchColumn();

// Orders by status
$ordersByStatus = $db->query("SELECT status, COUNT(*) as count FROM orders GROUP BY status")->fetchAll();

// Top products
$topProducts = $db->query("
    SELECT oi.product_name, SUM(oi.quantity) as total_qty, SUM(oi.total) as total_revenue
    FROM order_items oi JOIN orders o ON oi.order_id = o.id
    WHERE o.status != 'cancelled'
    GROUP BY oi.product_id ORDER BY total_qty DESC LIMIT 10
")->fetchAll();

// Monthly revenue (last 6 months)
$monthlyRevenue = $db->query("
    SELECT DATE_FORMAT(created_at, '%Y-%m') as month, COALESCE(SUM(total), 0) as revenue
    FROM orders WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND status != 'cancelled'
    GROUP BY month ORDER BY month
")->fetchAll();

require_once __DIR__ . '/../includes/admin-header.php';
?>
<div class="grid grid-cols-1 md:grid-cols-4 gap-4 mb-8">
    <div class="bg-white rounded-xl border border-gray-200 p-5"><p class="text-sm text-gray-500">Today's Revenue</p><p class="text-2xl font-bold text-gray-800 mt-1"><?= formatPrice($today) ?></p></div>
    <div class="bg-white rounded-xl border border-gray-200 p-5"><p class="text-sm text-gray-500">This Month</p><p class="text-2xl font-bold text-gray-800 mt-1"><?= formatPrice($thisMonth) ?></p></div>
    <div class="bg-white rounded-xl border border-gray-200 p-5"><p class="text-sm text-gray-500">Avg Order Value</p><p class="text-2xl font-bold text-gray-800 mt-1"><?= formatPrice($avgOrderValue) ?></p></div>
    <div class="bg-white rounded-xl border border-gray-200 p-5"><p class="text-sm text-gray-500">Total Orders</p><p class="text-2xl font-bold text-gray-800 mt-1"><?= $totalOrders ?></p></div>
</div>

<div class="grid grid-cols-1 lg:grid-cols-2 gap-6 mb-6">
    <div class="bg-white rounded-xl border border-gray-200 p-6">
        <h3 class="font-semibold text-gray-800 mb-4">Monthly Revenue (Last 6 Months)</h3>
        <div class="space-y-2">
            <?php foreach ($monthlyRevenue as $mr): ?>
            <div class="flex items-center gap-4"><span class="text-sm text-gray-600 w-20"><?= h($mr['month']) ?></span><div class="flex-1 bg-gray-100 rounded-full h-4"><div class="bg-primary-500 rounded-full h-4" style="width: <?= $thisMonth > 0 ? ($mr['revenue'] / $thisMonth * 100) : 0 ?>%"></div></div><span class="text-sm font-medium w-24 text-right"><?= formatPrice($mr['revenue']) ?></span></div>
            <?php endforeach; ?>
        </div>
    </div>
    <div class="bg-white rounded-xl border border-gray-200 p-6">
        <h3 class="font-semibold text-gray-800 mb-4">Orders by Status</h3>
        <div class="space-y-2">
            <?php foreach ($ordersByStatus as $os): ?>
            <div class="flex items-center justify-between py-1">
                <span><?= getStatusBadge($os['status']) ?></span>
                <span class="font-medium"><?= $os['count'] ?></span>
            </div>
            <?php endforeach; ?>
        </div>
    </div>
</div>

<div class="bg-white rounded-xl border border-gray-200 p-6">
    <h3 class="font-semibold text-gray-800 mb-4">Top Selling Products</h3>
    <table class="w-full text-sm">
        <thead><tr class="text-left text-gray-500 border-b"><th class="pb-2 font-medium">Product</th><th class="pb-2 font-medium">Units Sold</th><th class="pb-2 font-medium">Revenue</th></tr></thead>
        <tbody>
            <?php foreach ($topProducts as $tp): ?>
            <tr class="border-b last:border-0">
                <td class="py-2 font-medium text-gray-800"><?= h($tp['product_name']) ?></td>
                <td class="py-2"><?= $tp['total_qty'] ?></td>
                <td class="py-2 font-medium"><?= formatPrice($tp['total_revenue']) ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php require_once __DIR__ . '/../includes/admin-footer.php'; ?>
