<?php
// report_cierres.php (formato: montos 2 decimales, volumen 3 decimales)
$config = require __DIR__ . '/db.php';
try {
    $dsn = "mysql:host={$config['host']};port={$config['port']};dbname={$config['dbname']};charset={$config['charset']}";
    $pdo = new PDO($dsn, $config['user'], $config['pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (Exception $e) {
    die("Error de conexión: " . htmlspecialchars($e->getMessage()));
}

function h($s){ return htmlspecialchars((string)$s, ENT_QUOTES, 'UTF-8'); }

$stmt = $pdo->query("SELECT id, name FROM pumps ORDER BY id");
$pumps = $stmt->fetchAll();
$stmtC = $pdo->query("SELECT id, pump_name, start_dt, end_dt, created_at FROM sales_closures ORDER BY created_at DESC LIMIT 50");
$closures = $stmtC->fetchAll();

$mode = $_GET['mode'] ?? 'range';
$date_from = $_GET['date_from'] ?? date('Y-m-d');
$time_from = $_GET['time_from'] ?? '00:00';
$date_to = $_GET['date_to'] ?? date('Y-m-d');
$time_to = $_GET['time_to'] ?? date('H:i');
$closure_id = isset($_GET['closure_id']) ? (int)$_GET['closure_id'] : null;

$errors = [];
$report = null;
$exportCsv = isset($_GET['export']) && $_GET['export'] === '1';

if ($mode === 'closure' && $closure_id) {
    $stmt = $pdo->prepare("SELECT * FROM sales_closures WHERE id = :id");
    $stmt->execute([':id' => $closure_id]);
    $row = $stmt->fetch();
    if (!$row) {
        $errors[] = "Cierre no encontrado.";
    } else {
        $details = null;
        if (!empty($row['details'])) {
            $decoded = json_decode($row['details'], true);
            if ($decoded !== null) $details = $decoded;
        }

        if ($details && isset($details['per_pump'])) {
            // Ensure numeric types and rounding
            foreach ($details['per_pump'] as &$pp) {
                $pp['total_amount'] = round((float)$pp['total_amount'], 2);
                $pp['total_volume'] = round((float)($pp['total_volume'] ?? 0), 3);
                if (!empty($pp['products'])) {
                    foreach ($pp['products'] as &$pd) {
                        $pd['qty'] = round((float)$pd['qty'], 3);
                        $pd['total'] = round((float)$pd['total'], 2);
                    }
                }
            }
            $report = [
                'mode' => 'closure',
                'closure' => $row,
                'per_pump' => $details['per_pump'],
                'totals' => [
                    'total_amount' => round((float)$row['total_amount'],2),
                    'total_volume' => round((float)$row['total_volume'],3),
                    'transactions' => (int)$row['transactions']
                ]
            ];
        } else {
            // fall through to range calculation using closure period
            $mode = 'range';
            $date_from = (new DateTime($row['start_dt']))->format('Y-m-d');
            $time_from = (new DateTime($row['start_dt']))->format('H:i');
            $date_to = (new DateTime($row['end_dt']))->format('Y-m-d');
            $time_to = (new DateTime($row['end_dt']))->format('H:i');
        }
    }
}

if ($mode === 'range' && $report === null) {
    try {
        $start = new DateTime($date_from . ' ' . $time_from);
        $end = new DateTime($date_to . ' ' . $time_to);
        if ($end < $start) $end->modify('+1 day');
        $start_dt = $start->format('Y-m-d H:i:s');
        $end_dt = $end->format('Y-m-d H:i:s');
    } catch (Exception $e) {
        $errors[] = "Formato de fecha/hora inválido.";
    }

    if (empty($errors)) {
        $sqlPerPump = "
            SELECT p.id AS pump_id, p.name AS pump_name,
              IFNULL(SUM(si.total),0) AS total_amount,
              IFNULL(SUM(si.qty),0) AS total_volume,
              COUNT(DISTINCT si.sale_id) AS transactions
            FROM pumps p
            LEFT JOIN hoses h ON h.pump_id = p.id
            LEFT JOIN sale_items si ON si.hose_id = h.id
            LEFT JOIN sales s ON s.id = si.sale_id
              AND s.created_at BETWEEN :start_dt AND :end_dt
            GROUP BY p.id, p.name
            ORDER BY p.id
        ";
        $stmtPP = $pdo->prepare($sqlPerPump);
        $stmtPP->execute([':start_dt' => $start_dt, ':end_dt' => $end_dt]);
        $perPumpRows = $stmtPP->fetchAll();

        $per_pump = [];
        $grand_total_amount = 0.0;
        $grand_total_volume = 0.0;
        $grand_tx = 0;

        foreach ($perPumpRows as $pr) {
            $pid = (int)$pr['pump_id'];
            $stmtPS = $pdo->prepare("
                SELECT DISTINCT si.sale_id
                FROM sale_items si
                JOIN sales s ON s.id = si.sale_id
                JOIN hoses h ON h.id = si.hose_id
                WHERE h.pump_id = :pid AND s.created_at BETWEEN :start_dt AND :end_dt
            ");
            $stmtPS->execute([':pid' => $pid, ':start_dt' => $start_dt, ':end_dt' => $end_dt]);
            $pumpSaleIds = array_map('intval', $stmtPS->fetchAll(PDO::FETCH_COLUMN));

            $stmtPD = $pdo->prepare("
                SELECT p.id AS product_id, p.name AS product_name,
                       IFNULL(SUM(si.qty),0) AS qty, IFNULL(SUM(si.total),0) AS total
                FROM sale_items si
                JOIN sales s ON s.id = si.sale_id
                JOIN products p ON p.id = si.product_id
                JOIN hoses h ON h.id = si.hose_id
                WHERE h.pump_id = :pid AND s.created_at BETWEEN :start_dt AND :end_dt
                GROUP BY p.id, p.name
                ORDER BY p.name
            ");
            $stmtPD->execute([':pid' => $pid, ':start_dt' => $start_dt, ':end_dt' => $end_dt]);
            $pumpProducts = $stmtPD->fetchAll();

            $amount = round((float)$pr['total_amount'], 2);
            $vol = round((float)$pr['total_volume'], 3);
            $tx = (int)$pr['transactions'];

            // round product rows
            foreach ($pumpProducts as &$pd) {
                $pd['qty'] = round((float)$pd['qty'], 3);
                $pd['total'] = round((float)$pd['total'], 2);
            }

            $per_pump[] = [
                'pump_id' => $pid,
                'pump_name' => $pr['pump_name'],
                'total_amount' => $amount,
                'total_volume' => $vol,
                'transactions' => $tx,
                'sale_ids' => $pumpSaleIds,
                'products' => $pumpProducts
            ];

            $grand_total_amount += $amount;
            $grand_total_volume += $vol;
            $grand_tx += $tx;
        }

        $report = [
            'mode' => 'range',
            'start_dt' => $start_dt,
            'end_dt' => $end_dt,
            'per_pump' => $per_pump,
            'totals' => [
                'total_amount' => round($grand_total_amount, 2),
                'total_volume' => round($grand_total_volume, 3),
                'transactions' => $grand_tx
            ]
        ];
    }
}

// CSV export
if ($report && $exportCsv) {
    header('Content-Type: text/csv; charset=utf-8');
    $fn = 'report_cierres_' . date('Ymd_His') . '.csv';
    header('Content-Disposition: attachment; filename=' . $fn);
    $out = fopen('php://output', 'w');
    if ($report['mode'] === 'range') {
        fputcsv($out, ["Reporte por rango", $report['start_dt'], $report['end_dt']]);
    } else {
        $c = $report['closure'];
        fputcsv($out, ["Reporte por cierre", "Closure ID", $c['id'], "Periodo", $c['start_dt'], $c['end_dt']]);
    }
    fputcsv($out, []);
    fputcsv($out, ['pump_id', 'pump_name', 'total_amount', 'total_volume', 'transactions', 'sale_ids']);
    foreach ($report['per_pump'] as $pp) {
        fputcsv($out, [
            $pp['pump_id'],
            $pp['pump_name'],
            number_format($pp['total_amount'], 2, '.', ''),
            number_format($pp['total_volume'], 3, '.', ''),
            $pp['transactions'],
            json_encode($pp['sale_ids'])
        ]);
        if (!empty($pp['products'])) {
            fputcsv($out, ['PRODUCTOS PARA PUMP ' . $pp['pump_id']]);
            fputcsv($out, ['product_id', 'product_name', 'qty', 'total']);
            foreach ($pp['products'] as $pd) {
                fputcsv($out, [$pd['product_id'], $pd['product_name'], number_format($pd['qty'],3,'.',''), number_format($pd['total'],2,'.','')]);
            }
            fputcsv($out, []);
        }
    }
    fputcsv($out, []);
    fputcsv($out, ['GRAND_TOTAL', number_format($report['totals']['total_amount'],2,'.',''), number_format($report['totals']['total_volume'],3,'.',''), $report['totals']['transactions']]);
    fclose($out);
    exit;
}
?>
<!doctype html>
<html lang="es">
<head>
  <meta charset="utf-8">
  <title>Reporte de cierres / periodos</title>
  <style>
    body{font-family:Arial, sans-serif;margin:16px}
    label{display:block;margin-top:8px}
    .row{margin-bottom:8px}
    table{border-collapse:collapse;width:100%;margin-top:12px}
    table td, table th{border:1px solid #ddd;padding:6px}
    .right{text-align:right}
    .actions{margin-top:8px}
    @media print { .no-print { display:none; } }
  </style>
  <script> function doPrint(){ window.print(); } </script>
</head>
<body>
  <h2>Reporte de ventas por bomba (por periodo)</h2>

  <div class="no-print">
    <form method="get" action="">
      <div class="row">
        <label>Modo:</label>
        <select name="mode" onchange="this.form.submit();" id="modeSel">
          <option value="range" <?= $mode==='range' ? 'selected':'' ?>>Rango manual</option>
          <option value="closure" <?= $mode==='closure' ? 'selected':'' ?>>Por cierre guardado</option>
        </select>
      </div>

      <div id="modeForm">
        <?php if ($mode === 'range'): ?>
          <div class="row">
            <label>Fecha/Hora inicio:</label>
            <input type="date" name="date_from" value="<?=h($date_from)?>">
            <input type="time" name="time_from" value="<?=h($time_from)?>">
          </div>
          <div class="row">
            <label>Fecha/Hora fin:</label>
            <input type="date" name="date_to" value="<?=h($date_to)?>">
            <input type="time" name="time_to" value="<?=h($time_to)?>">
            <div style="font-size:0.9em;color:#555">Si la hora fin es menor que la inicio se interpreta como turno que cruzó la medianoche.</div>
          </div>
        <?php else: ?>
          <div class="row">
            <label>Selecciona cierre guardado:</label>
            <select name="closure_id">
              <option value="">-- elija --</option>
              <?php foreach ($closures as $c): ?>
                <option value="<?=h($c['id'])?>" <?= $closure_id == $c['id'] ? 'selected' : '' ?>>ID <?=h($c['id'])?> — <?=h($c['pump_name'] ?: 'TODOS')?> — <?=h($c['start_dt'])?> → <?=h($c['end_dt'])?></option>
              <?php endforeach; ?>
            </select>
          </div>
        <?php endif; ?>
        <div class="actions">
          <button type="submit">Generar reporte</button>
          <?php if ($report): ?>
            <a class="no-print" href="?<?= http_build_query(array_merge($_GET, ['export'=>'1'])) ?>">Exportar CSV</a>
            <button type="button" onclick="doPrint();" class="no-print">Imprimir</button>
          <?php endif; ?>
        </div>
      </div>
    </form>
  </div>

  <?php if ($report): ?>
    <h3>Resumen del periodo: <?= h($report['mode'] === 'range' ? ($report['start_dt'].' → '.$report['end_dt']) : ('Closure ID '.$report['closure']['id'].' ('.$report['closure']['start_dt'].' → '.$report['closure']['end_dt'].')')) ?></h3>
    <p><strong>Total general:</strong> Monto <?= number_format($report['totals']['total_amount'],2) ?> — Volumen <?= number_format($report['totals']['total_volume'],3) ?> — Transacciones <?= h($report['totals']['transactions']) ?></p>

    <h4>Ventas por bomba</h4>
    <table>
      <tr><th>Bomba ID</th><th>Nombre</th><th class="right">Monto</th><th class="right">Volumen</th><th class="right">Transacciones</th><th>Sales IDs</th></tr>
      <?php foreach ($report['per_pump'] as $pp): ?>
        <tr>
          <td><?=h($pp['pump_id'])?></td>
          <td><?=h($pp['pump_name'])?></td>
          <td class="right"><?=number_format($pp['total_amount'],2)?></td>
          <td class="right"><?=number_format($pp['total_volume'],3)?></td>
          <td class="right"><?=h($pp['transactions'])?></td>
          <td><?=h(implode(',', $pp['sale_ids'] ?? []))?></td>
        </tr>
      <?php endforeach; ?>
    </table>

    <h4>Desglose por bomba y producto</h4>
    <?php foreach ($report['per_pump'] as $pp): ?>
      <h5>Bomba <?=h($pp['pump_id'])?> — <?=h($pp['pump_name'])?> (Monto <?=number_format($pp['total_amount'],2)?>)</h5>
      <?php if (!empty($pp['products'])): ?>
        <table>
          <tr><th>Producto</th><th class="right">Volumen</th><th class="right">Total</th></tr>
          <?php foreach ($pp['products'] as $pd): ?>
            <tr>
              <td><?=h($pd['product_name'])?></td>
              <td class="right"><?=number_format($pd['qty'],3)?></td>
              <td class="right"><?=number_format($pd['total'],2)?></td>
            </tr>
          <?php endforeach; ?>
        </table>
      <?php else: ?>
        <div>No hay ventas registradas para esta bomba en el periodo.</div>
      <?php endif; ?>
    <?php endforeach; ?>
  <?php endif; ?>

</body>
</html>