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

$department_id = 5;

// ================= FILTER =================
$product_filter = $_GET['product_id'] ?? '';
$period = $_GET['period'] ?? 'daily';

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

// ================= PRODUCTS =================
$products = $conn->query("
SELECT DISTINCT p.id, p.name
FROM hk_requisition_items hki
JOIN products p ON p.id = hki.product_id
ORDER BY p.name ASC
");

// ================= QUERY =================
$where = "ps.department_id = $department_id";

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

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

$sql = "
SELECT 
    $group_by AS period_label,
    p.name,
    SUM(hki.quantity_used) AS total_used

FROM hk_requisition_items hki
JOIN products p ON p.id = hki.product_id
JOIN product_stock ps ON ps.product_id = p.id

WHERE $where
AND hki.used_at IS NOT NULL

GROUP BY period_label, p.name
ORDER BY hki.used_at ASC
";

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

// ================= PREPARE CHART DATA =================
$labels = [];
$product_data = [];

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;
    }

    if(!isset($product_data[$product_name])){
        $product_data[$product_name] = [];
    }

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

// ================= DATASETS =================
$datasets = [];
$colors = [
    '#0d6efd',
    '#198754',
    '#dc3545',
    '#fd7e14',
    '#6f42c1',
    '#20c997',
    '#6610f2',
    '#d63384',
    '#198754'
];

$color_index = 0;

foreach($product_data as $product_name => $values){

    $data = [];

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

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

    $color_index++;
}
?>

<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>📈 Housekeeping Usage Analytics Report</h3>

<div class="card shadow-sm mb-4">
<div class="card-body">

<form method="GET" class="row g-3">

<div class="col-md-4">
<label class="form-label">Select 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_filter == $p['id']) ? 'selected' : '' ?>>
            <?= htmlspecialchars($p['name']) ?>
        </option>

    <?php endwhile; ?>
</select>
</div>

<div class="col-md-4">
<label class="form-label">Report Type</label>

<select name="period" class="form-control">

<option value="daily" <?= ($period=='daily') ? 'selected' : '' ?>>
Daily Report
</option>

<option value="monthly" <?= ($period=='monthly') ? 'selected' : '' ?>>
Monthly Report
</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">
    <i class="fa fa-chart-line"></i> Generate Report
</button>
</div>

</form>

</div>
</div>

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

<h5 class="mb-3">
<?= $label ?> Usage Trend
</h5>

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

</div>
</div>

</div>

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

<script>

const ctx = document.getElementById('usageChart');

new Chart(ctx, {
    type: 'line',

    data: {
        labels: <?= json_encode($labels) ?>,

        datasets: <?= json_encode($datasets) ?>
    },

    options: {
        responsive: true,

        interaction: {
            mode: 'index',
            intersect: false
        },

        stacked: false,

        plugins: {
            legend: {
                position: 'top'
            }
        },

        scales: {

            y: {
                beginAtZero: true,
                title: {
                    display: true,
                    text: 'Quantity Used'
                }
            },

            x: {
                title: {
                    display: true,
                    text: '<?= $label ?>'
                }
            }

        }

    }
});

</script>

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