<?php
require __DIR__ . '/../init.php';
require_login();
$pdo = pdo();

// filtros por defecto
$from_date = $_GET['from_date'] ?? date('Y-m-d', strtotime('-7 days'));
$from_time = $_GET['from_time'] ?? '00:00';
$to_date   = $_GET['to_date']   ?? date('Y-m-d');
$to_time   = $_GET['to_time']   ?? '23:59';
$pump_id   = isset($_GET['pump_id']) ? (int)$_GET['pump_id'] : 0;
$product_id= isset($_GET['product_id']) ? (int)$_GET['product_id'] : 0;
$export    = isset($_GET['export']) ? $_GET['export'] : '';

// construir datetimes
$from_dt = $from_date . ' ' . $from_time . ':00';
$to_dt   = $to_date . ' ' . $to_time . ':59';

// obtener listas para selects
$products = $pdo->query("SELECT id,name FROM products ORDER BY name")->fetchAll();
$pumps = $pdo->query("SELECT id,name FROM pumps ORDER BY name")->fetchAll();

// Si el usuario filtró por bomba, obtener product_ids de sus mangueras (mantener compatibilidad)
$pumpProductIds = [];
if ($pump_id) {
    $stmt = $pdo->prepare("SELECT DISTINCT product_id FROM hoses WHERE pump_id = ?");
    $stmt->execute([$pump_id]);
    $pumpProductIds = array_column($stmt->fetchAll(), 'product_id');
}

// construir condiciones y parámetros
$conditions = ["s.created_at BETWEEN ? AND ?"];
$params = [$from_dt, $to_dt];

if ($product_id) {
    $conditions[] = "si.product_id = ?";
    $params[] = $product_id;
} elseif ($pump_id && count($pumpProductIds)) {
    // filtrar por products asociados a la bomba (fallback)
    $placeholders = implode(',', array_fill(0, count($pumpProductIds), '?'));
    $conditions[] = "si.product_id IN ($placeholders)";
    foreach ($pumpProductIds as $pid) $params[] = $pid;
}

// Query principal: detalle por item, incluyendo hose_id y pump (si existe)
$sql = "SELECT 
    s.id as sale_id,
    s.sale_code,
    s.created_at,
    s.payment_method,
    u.username,
    si.product_id,
    p.name as product_name,
    si.qty,
    si.unit_price,
    si.total,
    si.hose_id,
    h.name as hose_name,
    pm.id as pump_id,
    pm.name as pump_name
    FROM sale_items si
    JOIN sales s ON s.id = si.sale_id
    JOIN products p ON p.id = si.product_id
    LEFT JOIN users u ON u.id = s.user_id
    LEFT JOIN hoses h ON h.id = si.hose_id
    LEFT JOIN pumps pm ON pm.id = h.pump_id
    WHERE " . implode(' AND ', $conditions) . "
    ORDER BY s.created_at DESC
    LIMIT 5000"; // límite

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

// Totales agregados
$total_sum = 0;
foreach ($rows as $r) $total_sum += (float)$r['total'];

// Si export CSV solicitado
if ($export === 'csv') {
    header('Content-Type: text/csv; charset=utf-8');
    header('Content-Disposition: attachment; filename="sales_report_' . date('Ymd_His') . '.csv"');
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Sale ID','Sale Code','Fecha','Usuario','Producto','Qty','Precio Unit','Total','Metodo Pago','Hose ID','Hose Name','Pump ID','Pump Name']);
    foreach ($rows as $r) {
        fputcsv($out, [
            $r['sale_id'],
            $r['sale_code'],
            $r['created_at'],
            $r['username'],
            $r['product_name'],
            $r['qty'],
            number_format($r['unit_price'],4,'.',''),
            number_format($r['total'],2,'.',''),
            $r['payment_method'],
            $r['hose_id'] ?? '',
            $r['hose_name'] ?? '',
            $r['pump_id'] ?? '',
            $r['pump_name'] ?? ''
        ]);
    }
    fclose($out);
    exit;
}
?>
<!doctype html>
<html>
<head><meta charset="utf-8"><title>Reportes - Ventas</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body class="p-3">
<a href="<?= site('/index.php') ?>">&laquo; Volver</a>
<h3>Reportes de Ventas</h3>

<form method="get" class="row g-2 align-items-end mb-3">
  <div class="col-md-3">
    <label class="form-label">Desde (fecha)</label>
    <input type="date" name="from_date" class="form-control" value="<?= htmlspecialchars($from_date) ?>">
  </div>
  <div class="col-md-2">
    <label class="form-label">Desde (hora)</label>
    <input type="time" name="from_time" class="form-control" value="<?= htmlspecialchars($from_time) ?>">
  </div>
  <div class="col-md-3">
    <label class="form-label">Hasta (fecha)</label>
    <input type="date" name="to_date" class="form-control" value="<?= htmlspecialchars($to_date) ?>">
  </div>
  <div class="col-md-2">
    <label class="form-label">Hasta (hora)</label>
    <input type="time" name="to_time" class="form-control" value="<?= htmlspecialchars($to_time) ?>">
  </div>

  <div class="col-md-3">
    <label class="form-label">Bomba</label>
    <select name="pump_id" class="form-select">
      <option value="0">-- Todas --</option>
      <?php foreach($pumps as $p): ?>
        <option value="<?= $p['id'] ?>" <?= $pump_id==$p['id']?'selected':'' ?>><?= htmlspecialchars($p['name']) ?></option>
      <?php endforeach; ?>
    </select>
  </div>
  <div class="col-md-3">
    <label class="form-label">Tipo Combustible</label>
    <select name="product_id" class="form-select">
      <option value="0">-- Todos --</option>
      <?php foreach($products as $p): ?>
        <option value="<?= $p['id'] ?>" <?= $product_id==$p['id']?'selected':'' ?>><?= htmlspecialchars($p['name']) ?></option>
      <?php endforeach; ?>
    </select>
  </div>

  <div class="col-md-3">
    <button class="btn btn-primary" type="submit">Filtrar</button>
    <a class="btn btn-outline-secondary" href="<?= site('admin/reports.php') ?>">Limpiar</a>
    <?php
      $qs = $_GET;
      $qs['export'] = 'csv';
      $exportUrl = site('admin/reports.php') . '?' . http_build_query($qs);
    ?>
    <a class="btn btn-success" href="<?= $exportUrl ?>">Exportar CSV</a>
  </div>
</form>

<hr>

<div class="mb-2">
  <strong>Resultados:</strong> <?= count($rows) ?> filas &nbsp;|&nbsp; <strong>Total:</strong> $<?= number_format($total_sum,2) ?>
</div>

<table class="table table-sm table-striped">
<thead>
<tr>
  <th>Fecha</th><th>Venta</th><th>Usuario</th><th>Producto</th><th>Qty</th><th>Precio</th><th>Total</th><th>Método</th><th>Isla</th>
</tr>
</thead>
<tbody>
<?php foreach($rows as $r): ?>
<tr>
  <td><?= $r['created_at'] ?></td>
  <td><?= htmlspecialchars($r['sale_code']) ?></td>
  <td><?= htmlspecialchars($r['username']) ?></td>
  <td><?= htmlspecialchars($r['product_name']) ?></td>
  <td><?= $r['qty'] ?></td>
  <td>$<?= number_format($r['unit_price'],4) ?></td>
  <td>$<?= number_format($r['total'],2) ?></td>
  <td><?= htmlspecialchars($r['payment_method']) ?></td>
  <td><?= htmlspecialchars($r['pump_name'] ?? ($r['hose_name'] ? ('Hose: '.$r['hose_name']) : '—')) ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>

</body>
</html>