<?php
session_start();
if (!isset($_SESSION['role_id']) || $_SESSION['role_id'] != 1) {
    header("Location: ../../index.php");
    exit();
}

include('../../includes/config.php');
include('../../includes/header.php');

$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$store_id = $_GET['store_id'] ?? '';
$export = $_GET['export'] ?? '';

$where = ['DATE(si.created_at) >= ?', 'DATE(si.created_at) <= ?'];
$params = [$date_from, $date_to];
$types = 'ss';
if ($store_id !== '') {
    $where[] = 'st.store_id = ?';
    $params[] = $store_id;
    $types .= 'i';
}

$sql = "SELECT p.id, p.product_name, SUM(si.quantity) AS qty, SUM(si.quantity * si.unit_price) AS revenue
        FROM sales_items si
        JOIN sales_transactions st ON si.transaction_id = st.id
        JOIN products p ON si.product_id = p.id
        WHERE " . implode(' AND ', $where) . "
        GROUP BY p.id
        ORDER BY qty DESC
        LIMIT 10";
$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

$stores = $conn->query("SELECT id, store_name FROM stores");

if ($export === 'excel') {
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=bestsellers.xls");
    echo "<table border=1><tr><th>Product</th><th>Qty Sold</th><th>Revenue</th></tr>";
    foreach ($data as $r) {
        echo "<tr><td>" . htmlspecialchars($r['product_name']) . "</td><td>{$r['qty']}</td><td>{$r['revenue']}</td></tr>";
    }
    echo "</table>";
    exit();
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Top 10 Best-Selling Products</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <style>
        body, html {
            margin: 0;
            padding: 0;
            font-family: 'Segoe UI', sans-serif;
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            color: white;
        }

        .overlay {
            position: fixed;
            top: 0; left: 0;
            width: 100%; height: 100%;
            background: rgba(0, 0, 0, 0.6);
            z-index: -1;
        }

        .content {
            margin: 100px auto 40px auto;
            max-width: 1200px;
            padding: 40px;
            background: rgba(255, 255, 255, 0.08);
            border-radius: 20px;
            backdrop-filter: blur(15px);
            box-shadow: 0 8px 32px rgba(0, 0, 0, 0.5);
        }

        h2 {
            text-align: center;
            margin-bottom: 20px;
            color: #ffa500;
        }

        .back-link {
            display: inline-block;
            margin-bottom: 20px;
            padding: 10px 20px;
            background:rgba(255, 111, 0, 0);
            color: white;
            font-weight: 600;
            border-radius: 8px;
            text-decoration: none;
            transition: background-color 0.3s ease;
        }

        .back-link:hover {
            background: #e65c00;
        }

        .filters {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            justify-content: center;
            margin-bottom: 20px;
        }

        .filters select,
        .filters input[type="date"],
        .filters button {
            padding: 10px 15px;
            border-radius: 8px;
            border: none;
            font-size: 14px;
        }

        .filters select,
        .filters input[type="date"] {
            background: rgba(255, 255, 255, 0.2);
            color: white;
        }

        .filters button {
            background: #ff6f00;
            color: white;
            cursor: pointer;
        }

        .filters button:hover {
            background: #ffa500;
        }

        canvas {
            background: white;
            border-radius: 10px;
            padding: 10px;
            margin-bottom: 30px;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            background: rgba(255, 255, 255, 0.05);
        }

        th, td {
            padding: 12px;
            text-align: center;
            border-bottom: 1px solid rgba(255, 255, 255, 0.1);
        }

        th {
            background: rgba(0, 0, 0, 0.3);
            color: #ffa500;
        }

        tr:hover {
            background: rgba(255, 255, 255, 0.1);
        }

        @media(max-width: 768px) {
            .filters {
                flex-direction: column;
                align-items: center;
            }

            canvas {
                width: 100% !important;
            }
        }
    </style>
</head>
<body>
<div class="overlay"></div>

<div class="content">
    <a href="reports.php" class="back-link">&larr; Back to Reports</a>
    <h2>📦 Top 10 Best-Selling Products</h2>

    <form class="filters" method="GET">
        <select name="store_id">
            <option value="">All Stores</option>
            <?php while ($s = $stores->fetch_assoc()): ?>
                <option value="<?= $s['id'] ?>" <?= ($store_id == $s['id'] ? 'selected' : '') ?>>
                    <?= htmlspecialchars($s['store_name']) ?>
                </option>
            <?php endwhile; ?>
        </select>

        <input type="date" name="date_from" value="<?= $date_from ?>">
        <input type="date" name="date_to" value="<?= $date_to ?>">
        <button type="submit">Filter</button>
        <button type="button" onclick="window.location.href='?date_from=<?= $date_from ?>&date_to=<?= $date_to ?>&store_id=<?= $store_id ?>&export=excel'">Export Excel</button>
        <button type="button" onclick="window.print()">Export PDF</button>
    </form>

    <canvas id="productChart" height="100"></canvas>

    <table>
        <thead>
            <tr>
                <th>Product</th>
                <th>Qty Sold</th>
                <th>Revenue (MWK)</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($data as $r): ?>
                <tr>
                    <td><?= htmlspecialchars($r['product_name']) ?></td>
                    <td><?= $r['qty'] ?></td>
                    <td><?= number_format($r['revenue'], 2) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<script>
const ctx3 = document.getElementById('productChart').getContext('2d');
new Chart(ctx3, {
    type: 'bar',
    data: {
        labels: <?= json_encode(array_column($data, 'product_name')) ?>,
        datasets: [
            {
                label: 'Qty Sold',
                data: <?= json_encode(array_column($data, 'qty')) ?>,
                backgroundColor: 'rgba(0,150,136,0.6)'
            },
            {
                label: 'Revenue',
                data: <?= json_encode(array_column($data, 'revenue')) ?>,
                backgroundColor: 'rgba(255,111,0,0.6)'
            }
        ]
    },
    options: {
        responsive: true,
        scales: {
            y: {
                beginAtZero: true,
                ticks: {
                    color: '#fff'
                }
            },
            x: {
                ticks: {
                    color: '#fff'
                }
            }
        },
        plugins: {
            legend: {
                labels: {
                    color: '#fff'
                }
            }
        }
    }
});
</script>
</body>
</html>
