<?php
// public/stock_summary.php
require_once __DIR__ . '/../helpers/auth.php';
require_role(['admin', 'staff']);

$pdo = db();
$title = "Stock Summary - IMS";

// Search filter (optional)
$q = trim((string)($_GET['q'] ?? ''));

// Query: current stock = IN - OUT + ADJUST
$sql = "
  SELECT
    p.id,
    p.sku,
    p.name,
    p.unit,
    p.reorder_level,
    p.is_active,

    COALESCE(SUM(CASE WHEN m.movement_type='IN' THEN m.quantity ELSE 0 END),0) AS total_in,
    COALESCE(SUM(CASE WHEN m.movement_type='OUT' THEN m.quantity ELSE 0 END),0) AS total_out,
    COALESCE(SUM(CASE WHEN m.movement_type='ADJUST' THEN m.quantity ELSE 0 END),0) AS total_adjust,

    (
      COALESCE(SUM(CASE WHEN m.movement_type='IN' THEN m.quantity ELSE 0 END),0)
      - COALESCE(SUM(CASE WHEN m.movement_type='OUT' THEN m.quantity ELSE 0 END),0)
      + COALESCE(SUM(CASE WHEN m.movement_type='ADJUST' THEN m.quantity ELSE 0 END),0)
    ) AS current_stock

  FROM products p
  LEFT JOIN inventory_movements m ON m.product_id = p.id
  WHERE 1=1
";

$params = [];

if ($q !== '') {
    $sql .= " AND (p.sku LIKE :q OR p.name LIKE :q) ";
    $params[':q'] = '%' . $q . '%';
}

$sql .= "
  GROUP BY p.id
  ORDER BY p.name ASC
";

$stmt = $pdo->prepare($sql);
$stmt->execute($params);
$rows = $stmt->fetchAll();

require_once __DIR__ . '/../templates/header.php';
require_once __DIR__ . '/../templates/sidebar.php';
?>

<main class="col-12 col-md-9 col-lg-10 p-3 p-md-4">
  <div class="d-flex flex-wrap gap-2 align-items-center justify-content-between mb-3">
    <h4 class="mb-0">Stock Summary</h4>

    <form class="d-flex gap-2" method="GET">
      <input class="form-control form-control-sm" name="q" placeholder="Search SKU or name..." value="<?= htmlspecialchars($q) ?>">
      <button class="btn btn-sm btn-outline-primary" type="submit">Search</button>
      <a class="btn btn-sm btn-outline-secondary" href="stock_summary.php">Reset</a>
    </form>
  </div>

  <div class="card shadow-sm">
    <div class="card-body">
      <div class="table-responsive">
        <table class="table table-sm align-middle">
          <thead>
            <tr>
              <th>SKU</th>
              <th>Product</th>
              <th>Unit</th>
              <th class="text-end">IN</th>
              <th class="text-end">OUT</th>
              <th class="text-end">ADJ</th>
              <th class="text-end">Current</th>
              <th class="text-center">Reorder</th>
              <th class="text-center">Active</th>
            </tr>
          </thead>
          <tbody>
            <?php if (!$rows): ?>
              <tr><td colspan="9" class="text-muted">No products found.</td></tr>
            <?php else: ?>
              <?php foreach ($rows as $r): ?>
                <?php
                  $current = (int)$r['current_stock'];
                  $reorder_level = (int)$r['reorder_level'];
                  $needs_reorder = ($reorder_level > 0 && $current <= $reorder_level);
                ?>
                <tr class="<?= $needs_reorder ? 'table-warning' : '' ?>">
                  <td><?= htmlspecialchars($r['sku']) ?></td>
                  <td><?= htmlspecialchars($r['name']) ?></td>
                  <td><?= htmlspecialchars($r['unit']) ?></td>

                  <td class="text-end"><?= (int)$r['total_in'] ?></td>
                  <td class="text-end"><?= (int)$r['total_out'] ?></td>
                  <td class="text-end"><?= (int)$r['total_adjust'] ?></td>

                  <td class="text-end fw-semibold"><?= $current ?></td>

                  <td class="text-center">
                    <?php if ($needs_reorder): ?>
                      <span class="badge text-bg-warning">LOW</span>
                      <div class="small text-muted">Level: <?= $reorder_level ?></div>
                    <?php else: ?>
                      <span class="text-muted">-</span>
                      <div class="small text-muted">Level: <?= $reorder_level ?></div>
                    <?php endif; ?>
                  </td>

                  <td class="text-center">
                    <?= ((int)$r['is_active'] === 1) ? '✅' : '❌' ?>
                  </td>
                </tr>
              <?php endforeach; ?>
            <?php endif; ?>
          </tbody>
        </table>
      </div>

      <div class="text-muted small">
        Highlighted rows mean current stock is at/under reorder level.
      
      </div>
    </div>
  </div>
</main>

<?php require_once __DIR__ . '/../templates/footer.php'; ?>
