<?php require_once '../includes/header.php';

// ---------- Key Metrics (all on-the-fly calculations) ----------
$totalActiveProducts = $pdo->query("SELECT COUNT(*) FROM products WHERE is_active = 1")->fetchColumn();
$totalOrders         = $pdo->query("SELECT COUNT(*) FROM orders")->fetchColumn();
$pendingOrders       = $pdo->query("SELECT COUNT(*) FROM orders WHERE status = 'pending'")->fetchColumn();
$deliveredOrders     = $pdo->query("SELECT COUNT(*) FROM orders WHERE status = 'delivered'")->fetchColumn();
$totalResellers      = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
$activeSubscriptions = $pdo->query("SELECT COUNT(*) FROM user_subscriptions WHERE status = 'active' AND expires_at > NOW()")->fetchColumn();

// 🔥 Correct net profit = selling - buying - packaging_fee (only delivered)
$totalProfitStmt = $pdo->query("
    SELECT COALESCE(SUM(total_selling - total_buying - COALESCE(packaging_fee, 0)), 0)
    FROM orders
    WHERE status = 'delivered'
");
$totalProfit = (float) $totalProfitStmt->fetchColumn();

$walletBalance = $pdo->query("SELECT COALESCE(SUM(CASE WHEN type='credit' THEN amount ELSE -amount END), 0) FROM wallet_transactions")->fetchColumn();

// ---------- Monthly Sales Chart (using collect amount) ----------
$salesStmt = $pdo->query("
    SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
           COALESCE(SUM(total_selling + courier_fee), 0) AS total
    FROM orders
    WHERE status = 'delivered'
      AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
    GROUP BY month
    ORDER BY month ASC
");
$salesData = $salesStmt->fetchAll();
$months = array_column($salesData, 'month');
$salesValues = array_map('floatval', array_column($salesData, 'total'));

// ---------- Order Status Distribution ----------
$statusStmt = $pdo->query("SELECT status, COUNT(*) AS count FROM orders GROUP BY status");
$statusCounts = $statusStmt->fetchAll();
$statusLabels = array_map('ucfirst', array_column($statusCounts, 'status'));
$statusData   = array_map('intval', array_column($statusCounts, 'count'));

// ---------- Top 5 Products ----------
$topProductsStmt = $pdo->query("
    SELECT oi.product_name, SUM(oi.quantity) AS total_qty
    FROM order_items oi
    JOIN orders o ON oi.order_id = o.id
    WHERE o.status = 'delivered'
    GROUP BY oi.product_name
    ORDER BY total_qty DESC
    LIMIT 5
");
$topProducts = $topProductsStmt->fetchAll();

// ---------- Recent 5 Orders (show collect amount) ----------
$recentOrdersStmt = $pdo->query("
    SELECT o.id, o.order_number, o.customer_name,
           o.total_selling, o.courier_fee, o.status, o.created_at
    FROM orders o
    ORDER BY o.created_at DESC
    LIMIT 5
");
$recentOrders = $recentOrdersStmt->fetchAll();
?>

<h1 class="h2 mb-4">Dashboard</h1>

<!-- Key Metrics Row -->
<div class="row g-3 mb-4">
    <div class="col-xl-3 col-md-6">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted">Active Products</h6>
                <h3><?= number_format($totalActiveProducts) ?></h3>
            </div>
        </div>
    </div>
    <div class="col-xl-3 col-md-6">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted">Total Orders</h6>
                <h3><?= number_format($totalOrders) ?></h3>
                <small class="text-warning"><?= $pendingOrders ?> pending</small>
            </div>
        </div>
    </div>
    <div class="col-xl-3 col-md-6">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted">Resellers</h6>
                <h3><?= number_format($totalResellers) ?></h3>
                <small class="text-info"><?= $activeSubscriptions ?> active subs</small>
            </div>
        </div>
    </div>
    <div class="col-xl-3 col-md-6">
        <div class="card border-0 shadow-sm bg-dark text-white">
            <div class="card-body">
                <h6 class="text-white-50">Total Net Profit</h6>
                <h3>৳<?= number_format($totalProfit, 2) ?></h3>
                <small>Delivered orders</small>
            </div>
        </div>
    </div>
</div>

<!-- Second Metrics Row -->
<div class="row g-3 mb-4">
    <div class="col-md-4">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted">Delivered Orders</h6>
                <h3><?= number_format($deliveredOrders) ?></h3>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card border-0 shadow-sm">
            <div class="card-body">
                <h6 class="text-muted">Pending Orders</h6>
                <h3><?= number_format($pendingOrders) ?></h3>
            </div>
        </div>
    </div>
    <div class="col-md-4">
        <div class="card border-0 shadow-sm bg-primary text-white">
            <div class="card-body">
                <h6 class="text-white-50">Total Wallet Balance</h6>
                <h3>৳<?= number_format($walletBalance, 2) ?></h3>
            </div>
        </div>
    </div>
</div>

<!-- Charts Row -->
<div class="row g-4 mb-4">
    <div class="col-lg-8">
        <div class="card shadow-sm">
            <div class="card-header bg-white"><h5 class="mb-0">Monthly Sales (Collected)</h5></div>
            <div class="card-body"><canvas id="salesChart" height="100"></canvas></div>
        </div>
    </div>
    <div class="col-lg-4">
        <div class="card shadow-sm">
            <div class="card-header bg-white"><h5 class="mb-0">Order Status</h5></div>
            <div class="card-body"><canvas id="statusChart" height="200"></canvas></div>
        </div>
    </div>
</div>

<!-- Top Products & Recent Orders -->
<div class="row g-4 mb-4">
    <div class="col-md-6">
        <div class="card shadow-sm">
            <div class="card-header bg-white"><h5 class="mb-0">Top Selling Products</h5></div>
            <div class="card-body p-0">
                <?php if (empty($topProducts)): ?>
                    <p class="p-3 text-muted">No data.</p>
                <?php else: ?>
                    <ul class="list-group list-group-flush">
                        <?php foreach ($topProducts as $tp): ?>
                            <li class="list-group-item d-flex justify-content-between">
                                <?= h($tp['product_name']) ?>
                                <span class="badge bg-primary"><?= $tp['total_qty'] ?> sold</span>
                            </li>
                        <?php endforeach; ?>
                    </ul>
                <?php endif; ?>
            </div>
        </div>
    </div>
    <div class="col-md-6">
        <div class="card shadow-sm">
            <div class="card-header bg-white d-flex justify-content-between align-items-center">
                <h5 class="mb-0">Recent Orders</h5>
                <a href="orders.php" class="btn btn-sm btn-outline-primary">View All</a>
            </div>
            <div class="card-body p-0">
                <?php if (empty($recentOrders)): ?>
                    <p class="p-3 text-muted">No orders yet.</p>
                <?php else: ?>
                    <div class="table-responsive">
                        <table class="table table-striped mb-0">
                            <thead>
                                <tr>
                                    <th>Order #</th>
                                    <th>Customer</th>
                                    <th>Collect</th>
                                    <th>Status</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php foreach ($recentOrders as $ord):
                                    $collect = (float)$ord['total_selling'] + (float)$ord['courier_fee'];
                                ?>
                                    <tr>
                                        <td><a href="order_details.php?id=<?= $ord['id'] ?>">#<?= h($ord['order_number']) ?></a></td>
                                        <td><?= h($ord['customer_name'] ?? 'N/A') ?></td>
                                        <td>৳<?= number_format($collect, 2) ?></td>
                                        <td>
                                            <?php
                                            $statusClass = match($ord['status']) {
                                                'pending' => 'warning',
                                                'confirmed' => 'info',
                                                'packaging' => 'secondary',
                                                'shipping' => 'primary',
                                                'delivered' => 'success',
                                                'cancelled' => 'danger',
                                                default => 'dark'
                                            };
                                            ?>
                                            <span class="badge bg-<?= $statusClass ?>"><?= h($ord['status']) ?></span>
                                        </td>
                                    </tr>
                                <?php endforeach; ?>
                            </tbody>
                        </table>
                    </div>
                <?php endif; ?>
            </div>
        </div>
    </div>
</div>

<!-- Chart.js Initialization -->
<script>
document.addEventListener('DOMContentLoaded', function () {
    new Chart(document.getElementById('salesChart'), {
        type: 'bar',
        data: {
            labels: <?= json_encode($months) ?>,
            datasets: [{
                label: 'Revenue Collected (৳)',
                data: <?= json_encode($salesValues) ?>,
                backgroundColor: 'rgba(102,126,234,0.7)',
                borderColor: 'rgba(102,126,234,1)',
                borderWidth: 1
            }]
        },
        options: {
            responsive: true,
            scales: {
                y: { beginAtZero: true }
            }
        }
    });

    new Chart(document.getElementById('statusChart'), {
        type: 'doughnut',
        data: {
            labels: <?= json_encode($statusLabels) ?>,
            datasets: [{
                data: <?= json_encode($statusData) ?>,
                backgroundColor: [
                    '#ffc107','#17a2b8','#6c757d','#007bff','#28a745',
                    '#dc3545','#343a40','#fd7e14','#20c997','#e83e8c'
                ]
            }]
        },
        options: {
            responsive: true,
            plugins: {
                legend: { position: 'bottom' }
            }
        }
    });
});
</script>

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