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

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

$manager_store_id = $_SESSION['store_id'] ?? null;
$user_id = $_SESSION['user_id'];
$role_id = $_SESSION['role_id'];

$store_id = $manager_store_id;
$category_id = $_GET['category_id'] ?? '';
$date_from = $_GET['date_from'] ?? date('Y-m-01');
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$page = max(1, intval($_GET['page'] ?? 1));
$limit = 10;
$offset = ($page - 1) * $limit;
$export = $_GET['export'] ?? '';

$where = ['DATE(st.created_at) >= ?', 'DATE(st.created_at) <= ?', 'st.store_id = ?'];
$params = [$date_from, $date_to, $store_id];
$types = 'ssi';

if ($category_id !== '') {
    $where[] = 'p.category_id = ?';
    $params[] = $category_id;
    $types .= 'i';
}

// Count total rows
$count_sql = "SELECT COUNT(DISTINCT si.product_id) AS total 
              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);
$count_stmt = $conn->prepare($count_sql);
$count_stmt->bind_param($types, ...$params);
$count_stmt->execute();
$count_result = $count_stmt->get_result()->fetch_assoc();
$total_rows = $count_result['total'] ?? 0;
$count_stmt->close();
$total_pages = ceil($total_rows / $limit);

// Main data query
$sql = "SELECT p.product_name, SUM(si.quantity) AS qty_sold, 
               SUM(si.quantity * si.unit_price) AS total_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 si.product_id
        ORDER BY qty_sold DESC
        LIMIT ? OFFSET ?";
$params[] = $limit;
$params[] = $offset;
$types .= 'ii';

$stmt = $conn->prepare($sql);
$stmt->bind_param($types, ...$params);
$stmt->execute();
$data = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
$stmt->close();

// Audit log
$action = $export === 'excel' ? 'EXPORT' : 'VIEW';
$details = $export === 'excel' ? 'Exported product sales summary' : 'Viewed product sales summary';
$device = $_SERVER['HTTP_USER_AGENT'];
$module = 'product_sales_summary';
$audit = $conn->prepare("INSERT INTO audit_log (user_id, role_id, action, details, module, device_info) VALUES (?, ?, ?, ?, ?, ?)");
$audit->bind_param("iissss", $user_id, $role_id, $action, $details, $module, $device);
$audit->execute();
$audit->close();

if ($export === 'excel') {
    header("Content-Type: application/vnd.ms-excel");
    header("Content-Disposition: attachment; filename=product_sales_summary.xls");
    echo "<table border=1><tr><th>Product</th><th>Qty Sold</th><th>Revenue</th></tr>";
    foreach ($data as $row) {
        echo "<tr><td>{$row['product_name']}</td><td>{$row['qty_sold']}</td><td>{$row['total_revenue']}</td></tr>";
    }
    echo "</table>";
    exit();
}

// Fetch categories
$categories = $conn->query("SELECT id, category_name FROM categories ORDER BY category_name");
?>
<!DOCTYPE html>
<html>
<head>
    <title>Product Sales Summary</title>
    <style>
        body {
            font-family: 'Segoe UI', sans-serif;
            background: url('../../images/profile.jpg') no-repeat center center fixed;
            background-size: cover;
            color: white;
            margin: 0;
        }
        .overlay {
            position: fixed;
            inset: 0;
            background: rgba(0,0,0,0.6);
            z-index: -1;
        }
        .content {
            margin: 90px auto 40px;
            max-width: 1000px;
            padding: 30px;
            background: rgba(255,255,255,0.08);
            border-radius: 15px;
            backdrop-filter: blur(15px);
        }
        h2 {
            text-align: center;
            color: #ffa500;
            margin-bottom: 20px;
        }
        .filters {
            display: flex;
            flex-wrap: wrap;
            gap: 10px;
            margin-bottom: 20px;
            justify-content: center;
        }
        .filters select, .filters input, .filters button {
            padding: 8px 12px;
            border: none;
            border-radius: 6px;
            background: rgba(255,255,255,0.15);
            color: white;
        }
        .filters button {
            background: #ff6f00;
            cursor: pointer;
        }
        table {
            width: 100%;
            border-collapse: collapse;
            background: rgba(0,0,0,0.3);
        }
        th, td {
            padding: 10px;
            text-align: center;
            border-bottom: 1px solid rgba(255,255,255,0.1);
        }
        th {
            background: #ff6f00;
        }
        .pagination {
            text-align: center;
            margin-top: 20px;
        }
        .pagination a {
            color: white;
            margin: 0 5px;
            padding: 6px 12px;
            background: rgba(255,255,255,0.1);
            border-radius: 5px;
            text-decoration: none;
        }
        .pagination a.active {
            background: #ffa500;
            color: black;
            font-weight: bold;
        }
    </style>
</head>
<body>
<div class="overlay"></div>
<div class="content">
    <h2>📊 Product Sales Summary</h2>
    <form class="filters" method="GET">
        <select name="category_id">
            <option value="">All Categories</option>
            <?php while($cat = $categories->fetch_assoc()): ?>
                <option value="<?= $cat['id'] ?>" <?= ($category_id == $cat['id']) ? 'selected' : '' ?>>
                    <?= htmlspecialchars($cat['category_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="submit" name="export" value="excel">Export Excel</button>
    </form>

    <table>
        <thead>
            <tr>
                <th>Product</th>
                <th>Qty Sold</th>
                <th>Total Revenue</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach($data as $row): ?>
                <tr>
                    <td><?= htmlspecialchars($row['product_name']) ?></td>
                    <td><?= number_format($row['qty_sold']) ?></td>
                    <td><?= number_format($row['total_revenue'], 2) ?></td>
                </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

    <?php if ($total_pages > 1): ?>
    <div class="pagination">
        <?php for($p = 1; $p <= $total_pages; $p++): ?>
            <a href="?<?= http_build_query(array_merge($_GET, ['page' => $p])) ?>" class="<?= $p == $page ? 'active' : '' ?>">
                <?= $p ?>
            </a>
        <?php endfor; ?>
    </div>
    <?php endif; ?>
</div>
</body>
</html>
