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

$pdo = db();

$product_id = (int)($_GET['product_id'] ?? 0);
$from = trim((string)($_GET['from'] ?? ''));
$to = trim((string)($_GET['to'] ?? ''));

if ($product_id <= 0) {
    http_response_code(400);
    die("Invalid product_id.");
}

// Get product for filename
$stmt = $pdo->prepare("SELECT sku, name FROM products WHERE id = :id LIMIT 1");
$stmt->execute([':id' => $product_id]);
$p = $stmt->fetch();
if (!$p) {
    http_response_code(404);
    die("Product not found.");
}

$filenameSku = preg_replace('/[^A-Za-z0-9_\-]/', '_', (string)$p['sku']);
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="product_history_' . $filenameSku . '.csv"');

$out = fopen('php://output', 'w');
fputcsv($out, ['Date','Type','Qty','Unit Cost','Reference Type','Reference ID','Notes','By','Running Balance']);

// Fetch movements oldest->newest to compute balance
$sql = "
  SELECT
    m.movement_type, m.quantity, m.unit_cost,
    m.reference_type, m.reference_id, m.notes,
    m.created_at,
    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 ";

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

$balance = 0;
while ($m = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $qty = (int)$m['quantity'];
    $type = $m['movement_type'];

    if ($type === 'IN') $balance += $qty;
    if ($type === 'OUT') $balance -= $qty;
    if ($type === 'ADJUST') $balance += $qty;

    fputcsv($out, [
        $m['created_at'],
        $type,
        $qty,
        ($m['unit_cost'] !== null ? $m['unit_cost'] : ''),
        ($m['reference_type'] ?? ''),
        ($m['reference_id'] ?? ''),
        ($m['notes'] ?? ''),
        ($m['created_by_name'] ?? ''),
        $balance
    ]);
}

fclose($out);
exit;
