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

// ================= DATE RANGE FILTER =================
$default_from = date('Y-m-01');
$default_to   = date('Y-m-d');
$from_date = $_GET['from'] ?? $default_from;
$to_date   = $_GET['to'] ?? $default_to;

// Escape for safety
$from_date_safe = $conn->real_escape_string($from_date);
$to_date_safe   = $conn->real_escape_string($to_date);

// ================= FETCH REQUISITIONS =================
$requisitions = $conn->query("
    SELECT r.*, p.name AS product_name
    FROM stock_dispatch_requests r
    LEFT JOIN products p ON r.product_id = p.id
    WHERE DATE(r.created_at) BETWEEN '$from_date_safe' AND '$to_date_safe'
    ORDER BY r.created_at DESC
");

// ================= FETCH DISPATCHES =================
$dispatches = $conn->query("
    SELECT r.id AS requisition_id, r.dispatch_date, r.product_id, p.name AS product_name, r.quantity, r.status
    FROM stock_dispatch_requests r
    LEFT JOIN products p ON r.product_id = p.id
    WHERE r.status='Approved' AND DATE(r.dispatch_date) BETWEEN '$from_date_safe' AND '$to_date_safe'
    ORDER BY r.dispatch_date DESC
");

// ================= PRODUCT STOCK =================
$products_res = $conn->query("SELECT * FROM products ORDER BY name ASC");
$products = [];
while($p = $products_res->fetch_assoc()){
    $products[$p['id']] = $p;
}

// ================= ANALYTICS =================
$total_reqs = $requisitions->num_rows;
$total_disp  = $dispatches->num_rows;
$total_products = count($products);
$total_stock = array_sum(array_column($products, 'quantity'));

// Pending and approved counts
$pending = $conn->query("SELECT COUNT(*) as cnt FROM stock_dispatch_requests WHERE status='Pending'")->fetch_assoc()['cnt'];
$approved = $conn->query("SELECT COUNT(*) as cnt FROM stock_dispatch_requests WHERE status='Approved'")->fetch_assoc()['cnt'];
$rejected = $conn->query("SELECT COUNT(*) as cnt FROM stock_dispatch_requests WHERE status='Rejected'")->fetch_assoc()['cnt'];

// ================= PRODUCT STATS =================
$product_stats = [];
foreach($products as $pid => $p){
    // Total dispatched
    $disp_res = $conn->query("SELECT SUM(quantity) as total_disp FROM stock_dispatch_requests WHERE product_id=$pid AND status='Approved'");
    $total_disp = $disp_res->fetch_assoc()['total_disp'] ?? 0;

    // Total pending
    $pend_res = $conn->query("SELECT SUM(quantity) as total_pend FROM stock_dispatch_requests WHERE product_id=$pid AND status='Pending'");
    $total_pend = $pend_res->fetch_assoc()['total_pend'] ?? 0;

    // Total stock value
    $stock_value = isset($p['unit_price']) ? $p['quantity'] * $p['unit_price'] : null;

    $product_stats[$pid] = [
        'dispatched' => $total_disp,
        'pending' => $total_pend,
        'stock_value' => $stock_value
    ];
}
?>

<div class="container mt-4">
    <div class="row mb-3">

<div class="col-md-2">
<a href="export_requisition_dispatch_excel.php?from=<?= $from_date ?>&to=<?= $to_date ?>" 
class="btn btn-success w-100">
<i class="fa fa-file-excel"></i> Excel
</a>
</div>

<div class="col-md-2">
<a href="export_requisition_dispatch_pdf.php?from=<?= $from_date ?>&to=<?= $to_date ?>" 
class="btn btn-danger w-100">
<i class="fa fa-file-pdf"></i> PDF
</a>
</div>

</div>
<h3 class="text-center">📦 Requisition & Dispatch Analytics</h3>
<hr>

<!-- Date Filter Form -->
<form class="row g-2 mb-3" method="get">
    <div class="col-md-3">
        <input type="date" class="form-control" name="from" value="<?= $from_date ?>">
    </div>
    <div class="col-md-3">
        <input type="date" class="form-control" name="to" value="<?= $to_date ?>">
    </div>
    <div class="col-md-2">
        <button type="submit" class="btn btn-primary w-100">Filter</button>
    </div>
</form>

<!-- Summary Cards -->
<div class="row mb-4">
    <div class="col-md-2"><div class="alert alert-success text-center"><strong>Total Requisitions:</strong><br><?= $total_reqs ?></div></div>
    <div class="col-md-2"><div class="alert alert-primary text-center"><strong>Total Dispatched:</strong><br><?= $total_disp ?></div></div>
    <div class="col-md-2"><div class="alert alert-warning text-center"><strong>Products:</strong><br><?= $total_products ?></div></div>
    <div class="col-md-2"><div class="alert alert-info text-center"><strong>Total Stock:</strong><br><?= $total_stock ?></div></div>
    <div class="col-md-2"><div class="alert alert-secondary text-center"><strong>Pending:</strong><br><?= $pending ?></div></div>
    <div class="col-md-2"><div class="alert alert-dark text-center"><strong>Rejected:</strong><br><?= $rejected ?></div></div>
</div>

<!-- Requisitions Table -->
<h5>Requisitions</h5>
<div class="table-responsive">
<table class="table table-bordered table-striped table-sm">
<thead class="bg-dark text-white">
<tr>
    <th>#</th>
    <th>Requisition ID</th>
    <th>Product</th>
    <th>Quantity</th>
    <th>Status</th>
    <th>Requested By</th>
    <th>Approved By</th>
    <th>Dispatched By</th>
    <th>Created At</th>
    <th>Dispatch Date</th>
</tr>
</thead>
<tbody>
<?php $i=1; while($r = $requisitions->fetch_assoc()): ?>
<tr class="<?= $r['status']=='Approved'?'table-success':($r['status']=='Pending'?'table-warning':'table-danger') ?>">
    <td><?= $i ?></td>
    <td><?= $r['id'] ?></td>
    <td><?= $r['product_name'] ?></td>
    <td><?= $r['quantity'] ?></td>
    <td><?= $r['status'] ?></td>
    <td><?= $r['requested_by'] ?></td>
    <td><?= $r['approved_by'] ?? '-' ?></td>
    <td><?= $r['dispatched_by'] ?? '-' ?></td>
    <td><?= date('Y-m-d H:i', strtotime($r['created_at'])) ?></td>
    <td><?= $r['dispatch_date'] ? date('Y-m-d H:i', strtotime($r['dispatch_date'])) : '-' ?></td>
</tr>
<?php $i++; endwhile; ?>
</tbody>
</table>
</div>

<!-- Product Stock Table -->
<h5>Product Stock & Summary</h5>
<div class="table-responsive">
<table class="table table-bordered table-striped table-sm">
<thead class="bg-dark text-white">
<tr>
    <th>#</th>
    <th>Product</th>
    <th>SKU</th>
    <th>Quantity</th>
    <th>Min Level</th>
    <th>Dispatched</th>
    <th>Pending</th>
    <?php if(isset($products[array_key_first($products)]['unit_price'])): ?>
    <th>Stock Value (MK)</th>
    <?php endif; ?>
    <th>Statement</th>
</tr>
</thead>
<tbody>
<?php $i=1; foreach($products as $p): 
    $stats = $product_stats[$p['id']];
    $statement = "Product {$p['name']} has {$p['quantity']} units in stock, {$stats['dispatched']} dispatched, {$stats['pending']} pending.";
?>
<tr class="<?= $p['quantity']<=$p['min_level']?'table-danger':'' ?>">
    <td><?= $i ?></td>
    <td><?= $p['name'] ?></td>
    <td><?= $p['sku'] ?></td>
    <td><?= $p['quantity'] ?></td>
    <td><?= $p['min_level'] ?></td>
    <td><?= $stats['dispatched'] ?></td>
    <td><?= $stats['pending'] ?></td>
    <?php if($stats['stock_value'] !== null): ?>
    <td><?= number_format($stats['stock_value'],2) ?></td>
    <?php endif; ?>
    <td><?= $statement ?></td>
</tr>
<?php $i++; endforeach; ?>
</tbody>
</table>
</div>

<!-- Stock vs Min Level Chart -->
<h5>Stock vs Minimum Level</h5>
<canvas id="stockChart" height="100"></canvas>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const ctx = document.getElementById('stockChart').getContext('2d');
const labels = <?= json_encode(array_column($products, 'name')) ?>;
const stockData = <?= json_encode(array_column($products, 'quantity')) ?>;
const minLevelData = <?= json_encode(array_column($products, 'min_level')) ?>;

new Chart(ctx, {
    type: 'bar',
    data: {
        labels: labels,
        datasets: [
            { label: 'Stock', data: stockData, backgroundColor: 'rgba(54, 162, 235, 0.7)' },
            { label: 'Min Level', data: minLevelData, backgroundColor: 'rgba(255, 99, 132, 0.7)' }
        ]
    },
    options: {
        responsive: true,
        plugins: { legend: { position: 'top' } }
    }
});
</script>

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