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

$pdo = db();
$q = trim((string)($_GET['q'] ?? ''));

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename="products_export.csv"');

$out = fopen('php://output', 'w');

fputcsv($out, ['SKU','Barcode','Name','Category','Unit','Cost Price','Sell Price','Reorder Level','Active','Current Stock']);

$sql = "
  SELECT
    p.*,
    c.name AS category_name,
    (
      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 product_categories c ON c.id = p.category_id
  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.barcode_value 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);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($out, [
        $row['sku'],
        $row['barcode_value'] ?? '',
        $row['name'],
        $row['category_name'] ?? '',
        $row['unit'] ?? '',
        $row['cost_price'],
        $row['sell_price'],
        $row['reorder_level'],
        ((int)$row['is_active'] === 1 ? 'YES' : 'NO'),
        (int)$row['current_stock'],
    ]);
}

fclose($out);
exit;
