<?php
include 'includes/auth.php';
include 'db.php';
include 'includes/header.php';

$department_id = 5;

// ================= FILTERS =================
$product_id = $_GET['product_id'] ?? '';
$period = $_GET['period'] ?? 'monthly';

// ================= GROUPING =================
if($period == 'daily'){
    $group_by = "DATE(bri.used_at)";
    $label_title = "Daily Usage";
}
elseif($period == '6months'){
    $group_by = "DATE_FORMAT(bri.used_at, '%Y-%m')";
    $label_title = "Last 6 Months Usage";
}
else{
    $group_by = "DATE_FORMAT(bri.used_at, '%Y-%m')";
    $label_title = "Monthly Usage";
}

// ================= PRODUCTS LIST =================
$products = $conn->query("
    SELECT DISTINCT p.id, p.name
    FROM k_requisition_items bri
    JOIN products p ON p.id = bri.product_id
    JOIN product_stock ps ON ps.product_id = p.id
    WHERE ps.department_id = $department_id
    ORDER BY p.name
");

// ================= QUERY =================
$where = "ps.department_id = $department_id AND bri.used_at IS NOT NULL";

if($product_id){
    $where .= " AND bri.product_id = ".intval($product_id);
}

if($period == '6months'){
    $where .= " AND bri.used_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)";
}

$sql = "
SELECT 
    $group_by AS period_label,
    p.name,
    SUM(bri.quantity_used) AS total_used
FROM k_requisition_items bri
JOIN products p ON p.id = bri.product_id
JOIN product_stock ps ON ps.product_id = p.id
WHERE $where
GROUP BY period_label, p.name
ORDER BY period_label ASC
";

$result = $conn->query($sql);

// ================= FORMAT DATA =================
$labels = [];
$data_set = [];

while($row = $result->fetch_assoc()){

    $period_label = $row['period_label'];
    $product_name = $row['name'];
    $total_used = (int)$row['total_used'];

    if(!in_array($period_label, $labels)){
        $labels[] = $period_label;
    }

    $data_set[$product_name][$period_label] = $total_used;
}

// ================= BUILD CHART DATA =================
$colors = ['#0d6efd','#198754','#dc3545','#fd7e14','#6f42c1','#20c997'];

$datasets = [];
$i = 0;

foreach($data_set as $product => $values){

    $data = [];

    foreach($labels as $lbl){
        $data[] = $values[$lbl] ?? 0;
    }

    $datasets[] = [
        'label' => $product,
        'data' => $data,
        'borderColor' => $colors[$i % count($colors)],
        'tension' => 0.3,
        'fill' => false
    ];

    $i++;
}
?>

<div class="container mt-4">
     <button class="btn btn-secondary mb-3" onclick="history.back()">
        <i class="fa fa-arrow-left"></i> Back
    </button>

<h3>📊 Kitchen Analytics Report</h3>
<p><?= $label_title ?></p>

<!-- FILTERS -->
<form method="GET" class="row g-3 mb-4">

    <div class="col-md-4">
        <label>Product</label>
        <select name="product_id" class="form-control">
            <option value="">All Products</option>
            <?php while($p = $products->fetch_assoc()): ?>
                <option value="<?= $p['id'] ?>" <?= ($product_id==$p['id'])?'selected':'' ?>>
                    <?= htmlspecialchars($p['name']) ?>
                </option>
            <?php endwhile; ?>
        </select>
    </div>

    <div class="col-md-4">
        <label>Period</label>
        <select name="period" class="form-control">
            <option value="daily" <?= ($period=='daily')?'selected':'' ?>>Daily</option>
            <option value="monthly" <?= ($period=='monthly')?'selected':'' ?>>Monthly</option>
            <option value="6months" <?= ($period=='6months')?'selected':'' ?>>Last 6 Months</option>
        </select>
    </div>

    <div class="col-md-4 d-flex align-items-end">
        <button class="btn btn-primary w-100">
            📈 Generate
        </button>
    </div>

</form>

<!-- CHART -->
<div class="card shadow-sm">
    <div class="card-body">

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

    </div>
</div>

</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>

<script>
const ctx = document.getElementById('kitchenChart');

new Chart(ctx, {
    type: 'line',
    data: {
        labels: <?= json_encode($labels) ?>,
        datasets: <?= json_encode($datasets) ?>
    },
    options: {
        responsive: true,
        plugins: {
            legend: { position: 'top' }
        },
        scales: {
            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: 'Quantity Used'
                }
            },
            x: {
                title: {
                    display: true,
                    text: 'Period'
                }
            }
        }
    }
});
</script>

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