<?php
require_once __DIR__ . '/../config/config.php';
require_permission('reports.view');

$from = $_GET['from'] ?? date('Y-m-01');
$to = $_GET['to'] ?? date('Y-m-d');

$byReason = $pdo->prepare("SELECT wr.name reason_name, COUNT(*) line_count, SUM(wi.quantity * wi.unit_cost) total_value
                            FROM waste_items wi
                            JOIN waste_reasons wr ON wr.id = wi.reason_id
                            JOIN waste_records w ON w.id = wi.waste_id
                            WHERE DATE(w.created_at) BETWEEN ? AND ?
                            GROUP BY wr.id, wr.name ORDER BY total_value DESC");
$byReason->execute([$from, $to]);
$reasonRows = $byReason->fetchAll();

$byItem = $pdo->prepare("SELECT i.name item_name, i.code item_code, un.abbreviation unit_abbr,
                          SUM(wi.quantity) total_qty, SUM(wi.quantity * wi.unit_cost) total_value
                          FROM waste_items wi
                          JOIN items i ON i.id = wi.item_id
                          LEFT JOIN units un ON un.id = i.unit_id
                          JOIN waste_records w ON w.id = wi.waste_id
                          WHERE DATE(w.created_at) BETWEEN ? AND ?
                          GROUP BY i.id, i.name, i.code, un.abbreviation ORDER BY total_value DESC LIMIT 20");
$byItem->execute([$from, $to]);
$itemRows = $byItem->fetchAll();

$grandTotal = array_sum(array_column($reasonRows, 'total_value'));

$pageTitle = 'Waste Report';
include __DIR__ . '/../includes/header.php';
include __DIR__ . '/../includes/sidebar.php';
?>
<h1 class="text-2xl font-bold mb-6">Waste & Breakage Report</h1>

<form method="GET" class="flex flex-wrap items-end gap-3 mb-6 bg-white rounded-xl shadow-sm border border-slate-200 p-4">
  <div>
    <label class="text-xs text-slate-500 block mb-1">From</label>
    <input type="date" name="from" value="<?= e($from) ?>" class="border border-slate-300 rounded-lg px-3 py-2 text-sm">
  </div>
  <div>
    <label class="text-xs text-slate-500 block mb-1">To</label>
    <input type="date" name="to" value="<?= e($to) ?>" class="border border-slate-300 rounded-lg px-3 py-2 text-sm">
  </div>
  <button class="bg-blue-700 hover:bg-blue-800 text-white text-sm font-medium px-4 py-2 rounded-lg">Filter</button>
</form>

<div class="bg-orange-50 border border-orange-200 rounded-xl p-5 mb-6">
  <p class="text-xs text-orange-700">Total Waste Value (<?= date('M j, Y', strtotime($from)) ?> to <?= date('M j, Y', strtotime($to)) ?>)</p>
  <p class="text-3xl font-bold text-orange-700 mt-1">₦<?= number_format($grandTotal,2) ?></p>
</div>

<div class="grid grid-cols-1 lg:grid-cols-2 gap-6">
  <div class="overflow-x-auto">
  <div class="bg-white rounded-xl shadow-sm border border-slate-200">
    <div class="px-5 py-4 border-b border-slate-200 font-semibold">By Reason</div>
    <table class="w-full text-sm">
      <thead class="bg-slate-50 text-slate-500 text-xs uppercase"><tr>
        <th class="text-left px-5 py-2">Reason</th><th class="text-left px-5 py-2">Entries</th>
        <th class="text-left px-5 py-2">Value</th></tr></thead>
      <tbody>
      <?php foreach ($reasonRows as $r): ?>
        <tr class="border-t border-slate-100">
          <td class="px-5 py-2.5"><?= e($r['reason_name']) ?></td>
          <td class="px-5 py-2.5"><?= (int)$r['line_count'] ?></td>
          <td class="px-5 py-2.5">₦<?= number_format($r['total_value'],2) ?></td>
        </tr>
      <?php endforeach; ?>
      <?php if (empty($reasonRows)): ?><tr><td colspan="3" class="px-5 py-6 text-center text-slate-400">No waste in this period.</td></tr><?php endif; ?>
      </tbody>
    </table>
  </div>
  </div>

  <div class="overflow-x-auto">
  <div class="bg-white rounded-xl shadow-sm border border-slate-200">
    <div class="px-5 py-4 border-b border-slate-200 font-semibold">Top Items by Waste Value</div>
    <table class="w-full text-sm">
      <thead class="bg-slate-50 text-slate-500 text-xs uppercase"><tr>
        <th class="text-left px-5 py-2">Item</th><th class="text-left px-5 py-2">Qty</th>
        <th class="text-left px-5 py-2">Value</th></tr></thead>
      <tbody>
      <?php foreach ($itemRows as $r): ?>
        <tr class="border-t border-slate-100">
          <td class="px-5 py-2.5"><?= e($r['item_name']) ?></td>
          <td class="px-5 py-2.5"><?= number_format($r['total_qty'],2) ?> <?= e($r['unit_abbr']) ?></td>
          <td class="px-5 py-2.5">₦<?= number_format($r['total_value'],2) ?></td>
        </tr>
      <?php endforeach; ?>
      <?php if (empty($itemRows)): ?><tr><td colspan="3" class="px-5 py-6 text-center text-slate-400">No waste in this period.</td></tr><?php endif; ?>
      </tbody>
    </table>
  </div>
  </div>
</div>
<?php include __DIR__ . '/../includes/footer.php'; ?>
