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

$pdo = db();
$product_id = (int)($_GET['product_id'] ?? 0);
if ($product_id <= 0) {
    http_response_code(400);
    die("Invalid product_id.");
}

$title = "Product Stock History - IMS";

function get_str(string $k): string { return trim((string)($_GET[$k] ?? '')); }

// Filters (optional)
$from = get_str('from'); // YYYY-MM-DD
$to = get_str('to');     // YYYY-MM-DD

// Load product
$stmt = $pdo->prepare("
  SELECT p.*, c.name AS category_name
  FROM products p
  LEFT JOIN product_categories c ON c.id = p.category_id
  WHERE p.id = :id
  LIMIT 1
");
$stmt->execute([':id' => $product_id]);
$product = $stmt->fetch();

if (!$product) {
    http_response_code(404);
    die("Product not found.");
}

// Current stock
$stockStmt = $pdo->prepare("
  SELECT
    COALESCE(SUM(CASE WHEN movement_type='IN' THEN quantity ELSE 0 END),0) -
    COALESCE(SUM(CASE WHEN movement_type='OUT' THEN quantity ELSE 0 END),0) +
    COALESCE(SUM(CASE WHEN movement_type='ADJUST' THEN quantity ELSE 0 END),0) AS stock
  FROM inventory_movements
  WHERE product_id = :pid
");
$stockStmt->execute([':pid' => $product_id]);
$current_stock = (int)($stockStmt->fetch()['stock'] ?? 0);

// Fetch movements (apply filters)
$sql = "
  SELECT
    m.id, m.movement_type, m.quantity, m.unit_cost,
    m.reference_type, m.reference_id, m.notes,
    m.created_at, m.created_by,
    u.full_name AS created_by_name
  FROM inventory_movements m
  LEFT JOIN users u ON u.id = m.created_by
  WHERE m.product_id = :pid
";
$params = [':pid' => $product_id];

if ($from !== '') {
    $sql .= " AND DATE(m.created_at) >= :from ";
    $params[':from'] = $from;
}
if ($to !== '') {
    $sql .= " AND DATE(m.created_at) <= :to ";
    $params[':to'] = $to;
}

$sql .= " ORDER BY m.created_at ASC, m.id ASC "; // oldest->newest for running balance

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

// Running balance (oldest -> newest)
$balance = 0;
$with_balance = [];
foreach ($movements_oldest as $m) {
    $qty = (int)$m['quantity'];

    if ($m['movement_type'] === 'IN') {
        $balance += $qty;
    } elseif ($m['movement_type'] === 'OUT') {
        $balance -= $qty;
    } elseif ($m['movement_type'] === 'ADJUST') {
        $balance += $qty; // ADJUST can be + or -
    }

    $m['running_balance'] = $balance;
    $with_balance[] = $m;
}

// Display newest first for UI
$movements = array_reverse($with_balance);

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">
    <div>
      <h4 class="mb-0">Stock History</h4>
      <div class="text-muted small">
        <?= htmlspecialchars($product['name']) ?> (<?= htmlspecialchars($product['sku']) ?>)
        • Category: <?= htmlspecialchars($product['category_name'] ?? '-') ?>
        • Current Stock: <b><?= $current_stock ?></b> <?= htmlspecialchars($product['unit'] ?? '') ?>
      </div>
    </div>

    <div class="d-flex gap-2">
      <a class="btn btn-outline-secondary btn-sm" href="products.php">Back to Products</a>
    </div>
  </div>

  <div class="card shadow-sm mb-3">
    <div class="card-body">
      <form class="row g-2 align-items-end" method="GET">
        <input type="hidden" name="product_id" value="<?= (int)$product_id ?>">

        <div class="col-12 col-md-3">
          <label class="form-label">From</label>
          <input class="form-control form-control-sm" type="date" name="from" value="<?= htmlspecialchars($from) ?>">
        </div>

        <div class="col-12 col-md-3">
          <label class="form-label">To</label>
          <input class="form-control form-control-sm" type="date" name="to" value="<?= htmlspecialchars($to) ?>">
        </div>

        <div class="col-12 col-md-3">
          <button class="btn btn-sm btn-outline-primary" type="submit">Filter</button>
<a class="btn btn-sm btn-outline-secondary" href="product_stock_history.php?product_id=<?= (int)$product_id ?>">Reset</a>

<a class="btn btn-sm btn-outline-success"
   href="export_product_history_csv.php?product_id=<?= (int)$product_id ?>&from=<?= urlencode($from) ?>&to=<?= urlencode($to) ?>">
   Export CSV
</a>

        </div>

        <div class="col-12 col-md-3 text-md-end text-muted small">
          Total movements: <?= count($movements) ?>
        </div>
      </form>
    </div>
  </div>

  <div class="card shadow-sm">
    <div class="card-body">
      <div class="table-responsive">
        <table class="table table-sm align-middle">
          <thead>
            <tr>
              <th>Date</th>
              <th>Type</th>
              <th class="text-end">Qty</th>
              <th class="text-end">Unit Cost</th>
              <th>Reference</th>
              <th>Notes</th>
              <th>By</th>
              <th class="text-end">Balance</th>
            </tr>
          </thead>
          <tbody>
            <?php if (!$movements): ?>
              <tr><td colspan="8" class="text-muted">No movements found.</td></tr>
            <?php else: ?>
              <?php foreach ($movements as $m): ?>
                <?php
                  $type = $m['movement_type'];
                  $qty = (int)$m['quantity'];

                  // Display qty with sign style
                  $qtyLabel = ($type === 'OUT') ? "-$qty" : (string)$qty;
                  if ($type === 'ADJUST' && $qty > 0) $qtyLabel = "+$qty";
                  if ($type === 'ADJUST' && $qty < 0) $qtyLabel = (string)$qty;

                  $ref = $m['reference_type'] ?? '-';
                  $refId = $m['reference_id'];
                  $refText = $ref;
                  if ($refId !== null) $refText .= " #" . (int)$refId;

                  $rowClass = '';
                  if ($type === 'OUT') $rowClass = 'table-danger';
                  if ($type === 'IN') $rowClass = 'table-success';
                  if ($type === 'ADJUST') $rowClass = 'table-warning';
                ?>
                <tr class="<?= $rowClass ?>">
                  <td class="small text-muted"><?= htmlspecialchars($m['created_at']) ?></td>
                  <td class="fw-semibold"><?= htmlspecialchars($type) ?></td>
                  <td class="text-end fw-semibold"><?= htmlspecialchars($qtyLabel) ?></td>
                  <td class="text-end">
                    <?= $m['unit_cost'] !== null ? number_format((float)$m['unit_cost'], 2) : '-' ?>
                  </td>
                  <td><?= htmlspecialchars($refText) ?></td>
                  <td><?= htmlspecialchars($m['notes'] ?? '-') ?></td>
                  <td class="small"><?= htmlspecialchars($m['created_by_name'] ?? '-') ?></td>
                  <td class="text-end fw-semibold"><?= (int)$m['running_balance'] ?></td>
                </tr>
              <?php endforeach; ?>
            <?php endif; ?>
          </tbody>
        </table>
      </div>

      <div class="text-muted small">
        Running balance is calculated from oldest to newest, then displayed newest-first.
      </div>
    </div>
  </div>
</main>

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