<?php
session_start();
if (!isset($_SESSION['username'])) {
    header("Location: login.php");
    exit();
}

require 'db_connect.php';

// Fetch Top 5 Fast-Moving Products
$topProductsQuery = $conn->query("
    SELECT p.name, SUM(s.quantity) AS total_sold, SUM(s.quantity * s.selling_price) AS total_revenue
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY s.product_id
    ORDER BY total_sold DESC
    LIMIT 5
");

// Fetch Bottom 5 Slow-Moving Products
$bottomProductsQuery = $conn->query("
    SELECT p.name, SUM(s.quantity) AS total_sold, SUM(s.quantity * s.selling_price) AS total_revenue
    FROM sales s
    JOIN products p ON s.product_id = p.id
    GROUP BY s.product_id
    ORDER BY total_sold ASC
    LIMIT 5
");
?>

<!DOCTYPE html>
<html>
<head>
    <title>Top & Bottom Products</title>
    <link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap/5.1.3/css/bootstrap.min.css" rel="stylesheet">
    <style>
        body { background: #f4f6f9; font-family: Arial, sans-serif; }
        .container { margin-top: 40px; }
        h2 { margin-bottom: 20px; }
        table { background: #fff; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 10px rgba(0,0,0,0.1); }
        th { background: #007bff; color: #fff; }
    </style>
</head>
<body>

<div class="container">
    <h2>Top 5 Fast-Moving Products</h2>
    <table class="table table-bordered table-striped">
        <thead>
            <tr>
                <th>#</th>
                <th>Product Name</th>
                <th>Quantity Sold</th>
                <th>Total Revenue (KES)</th>
            </tr>
        </thead>
        <tbody>
        <?php $i = 1; while ($row = $topProductsQuery->fetch_assoc()): ?>
            <tr>
                <td><?= $i++ ?></td>
                <td><?= htmlspecialchars($row['name']) ?></td>
                <td><?= $row['total_sold'] ?></td>
                <td><?= number_format($row['total_revenue'], 2) ?></td>
            </tr>
        <?php endwhile; ?>
        </tbody>
    </table>

    <h2 class="mt-5">Bottom 5 Slow-Moving Products</h2>
    <table class="table table-bordered table-striped">
        <thead>
            <tr>
                <th>#</th>
                <th>Product Name</th>
                <th>Quantity Sold</th>
                <th>Total Revenue (KES)</th>
            </tr>
        </thead>
        <tbody>
        <?php $i = 1; while ($row = $bottomProductsQuery->fetch_assoc()): ?>
            <tr>
                <td><?= $i++ ?></td>
                <td><?= htmlspecialchars($row['name']) ?></td>
                <td><?= $row['total_sold'] ?></td>
                <td><?= number_format($row['total_revenue'], 2) ?></td>
            </tr>
        <?php endwhile; ?>
        </tbody>
    </table>

    <a href="admin_dashboard.php" class="btn btn-success mt-4">← Back to Dashboard</a>
</div>

</body>
</html>
