<?php
// cierre_ventas.php
// Corregido: validaciones se ejecutan antes de cualquier modificación en BD.
// Si hay errores (por ejemplo created_by vacío) NO se realiza ningún INSERT ni UPDATE.
// Forzar creación de cierres solapados está activo (hidden input + server-side enforced).
// Soporta .env opcional con APP_TZ para timezone (ej: "America/Santo_Domingo").
//
// Coloca este archivo junto a db.php y opcionalmente .env en la misma carpeta.

session_start();

// --- Leer .env (si existe) ---
function load_dotenv($path) {
    if (!file_exists($path)) return;
    $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#') continue;
        if (strpos($line, '=') === false) continue;
        list($key, $val) = explode('=', $line, 2);
        $key = trim($key);
        $val = trim($val);
        if ((substr($val,0,1) === '"' && substr($val,-1) === '"') || (substr($val,0,1) === "'" && substr($val,-1) === "'")) {
            $val = substr($val,1,-1);
        }
        putenv("$key=$val");
        $_ENV[$key] = $val;
    }
}
load_dotenv(__DIR__ . '/.env');

// timezone
$tz = getenv('APP_TZ') ?: (getenv('TZ') ?: 'America/Santo_Domingo');
if (@date_default_timezone_set($tz) === false) {
    date_default_timezone_set('UTC');
}

// DB
$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()));
}

// CSRF
if (!isset($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(16));
}
$csrf = $_SESSION['csrf_token'];

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

// Load pumps and recent closures (safe read-only queries)
$stmt = $pdo->query("SELECT id, name FROM pumps ORDER BY id");
$pumps = $stmt->fetchAll();

$stmtRecent = $pdo->query("SELECT id, pump_name, start_dt, end_dt, total_amount, transactions, created_at FROM sales_closures ORDER BY created_at DESC LIMIT 50");
$recentClosures = $stmtRecent->fetchAll();

// Defaults
$today = date('Y-m-d');
$default_time_from = '00:00';
$default_time_to = date('H:i');

$errors = [];
$info = null;
$result = null;
$overlaps = [];

// Prepare form values (use POST if present, otherwise defaults)
$form_date_from = $_POST['date_from'] ?? $today;
$form_time_from = $_POST['time_from'] ?? $default_time_from;
$form_date_to = $_POST['date_to'] ?? $today;
$form_time_to = $_POST['time_to'] ?? $default_time_to;
$form_pump_id = $_POST['pump_id'] ?? 'all';
$form_created_by = $_POST['created_by'] ?? ($_SESSION['username'] ?? '');
$form_notes = $_POST['notes'] ?? '';
// force is always true (hidden input). In UI checkbox shown disabled and checked.
$force = true;

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // reassign sanitized posted values (so we keep user input shown in form on error)
    $form_pump_id = $_POST['pump_id'] ?? 'all';
    $form_date_from = $_POST['date_from'] ?? $today;
    $form_time_from = $_POST['time_from'] ?? $default_time_from;
    $form_date_to = $_POST['date_to'] ?? $today;
    $form_time_to = $_POST['time_to'] ?? $default_time_to;
    $form_created_by = trim($_POST['created_by'] ?? ($_SESSION['username'] ?? ''));
    $form_notes = trim($_POST['notes'] ?? '');
    // enforce force true on server
    $force = true;

    // Basic CSRF check
    if (!isset($_POST['csrf']) || $_POST['csrf'] !== $csrf) {
        $errors[] = "Token inválido. Refresca la página e intenta de nuevo.";
    }

    // Basic validation BEFORE any DB modifications
    if ($form_date_from === '' || $form_date_to === '' || $form_time_from === '' || $form_time_to === '') {
        $errors[] = "Fecha y hora inicial y final son obligatorias.";
    }
    if ($form_pump_id !== 'all' && !ctype_digit((string)$form_pump_id)) {
        $errors[] = "Surtidor inválido.";
    }
    if ($form_created_by === '') {
        $errors[] = "El campo 'Usuario que realiza el cierre' es obligatorio.";
    }

    // If there are validation errors -> DO NOT proceed with queries that modify DB
    if (empty($errors)) {
        // Parse DateTimes safely (in configured tz)
        try {
            $start = new DateTime($form_date_from . ' ' . $form_time_from);
            $end   = new DateTime($form_date_to . ' ' . $form_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.";
        }
    }

    // still only proceed if no errors
    if (empty($errors)) {
        // Get hoses if pump selected
        $hoseIds = [];
        if ($form_pump_id !== 'all') {
            $stmtH = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = :pid");
            $stmtH->execute([':pid' => $form_pump_id]);
            $hoseIds = $stmtH->fetchAll(PDO::FETCH_COLUMN);
        }

        // Check overlaps for information (we won't abort because force===true by requirement)
        try {
            if ($form_pump_id === 'all') {
                $sqlOverlap = "SELECT id, pump_id, start_dt, end_dt FROM sales_closures WHERE NOT (end_dt < :start_dt OR start_dt > :end_dt) ORDER BY start_dt DESC LIMIT 10";
                $stmtO = $pdo->prepare($sqlOverlap);
                $stmtO->execute([':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $overlaps = $stmtO->fetchAll();
            } else {
                $sqlOverlap = "SELECT id, pump_id, start_dt, end_dt FROM sales_closures WHERE (pump_id IS NULL OR pump_id = :pump_id) AND NOT (end_dt < :start_dt OR start_dt > :end_dt) ORDER BY start_dt DESC LIMIT 10";
                $stmtO = $pdo->prepare($sqlOverlap);
                $stmtO->execute([':pump_id'=>$form_pump_id, ':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $overlaps = $stmtO->fetchAll();
            }
        } catch (Exception $e) {
            // non-fatal: report but continue
            $errors[] = "Error comprobando cierres existentes: " . $e->getMessage();
        }
    }

    // proceed to compute totals and insert only if still no errors
    if (empty($errors)) {
        try {
            if ($form_pump_id === 'all') {
                // overall totals
                $stmtT = $pdo->prepare("
                    SELECT IFNULL(SUM(si.total),0) AS total_amount, IFNULL(SUM(si.qty),0) AS total_volume, COUNT(DISTINCT si.sale_id) AS transactions
                    FROM sale_items si
                    JOIN sales s ON s.id = si.sale_id
                    WHERE si.hose_id IS NOT NULL AND s.created_at BETWEEN :start_dt AND :end_dt
                ");
                $stmtT->execute([':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $totals = $stmtT->fetch();

                // overall product breakdown
                $stmtD = $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
                    WHERE si.hose_id IS NOT NULL AND s.created_at BETWEEN :start_dt AND :end_dt
                    GROUP BY p.id, p.name ORDER BY p.name
                ");
                $stmtD->execute([':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $detailsRows = $stmtD->fetchAll();

                // sale ids overall
                $stmtS = $pdo->prepare("
                    SELECT DISTINCT si.sale_id
                    FROM sale_items si
                    JOIN sales s ON s.id = si.sale_id
                    WHERE si.hose_id IS NOT NULL AND s.created_at BETWEEN :start_dt AND :end_dt
                ");
                $stmtS->execute([':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $saleIds = array_map('intval', $stmtS->fetchAll(PDO::FETCH_COLUMN));

                // per pump breakdown
                $stmtPP = $pdo->prepare("
                    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->execute([':start_dt'=>$start_dt, ':end_dt'=>$end_dt]);
                $perPumpRows = $stmtPP->fetchAll();

                $per_pump = [];
                foreach ($perPumpRows as $pr) {
                    $pid = (int)$pr['pump_id'];
                    // sale ids per pump
                    $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));

                    // product breakdown per pump
                    $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();

                    $per_pump[] = [
                        'pump_id' => $pid,
                        'pump_name' => $pr['pump_name'],
                        'total_amount' => round((float)$pr['total_amount'], 2),
                        'total_volume' => round((float)$pr['total_volume'], 3),
                        'transactions' => (int)$pr['transactions'],
                        'sale_ids' => $pumpSaleIds,
                        'products' => array_map(function($r){
                            return [
                                'product_id' => (int)$r['product_id'],
                                'product_name' => $r['product_name'],
                                'qty' => round((float)$r['qty'], 3),
                                'total' => round((float)$r['total'], 2)
                            ];
                        }, $pumpProducts)
                    ];
                }

                // Round totals
                $totals['total_amount'] = round((float)$totals['total_amount'], 2);
                $totals['total_volume'] = round((float)$totals['total_volume'], 3);

                $pumpName = 'TODOS';
                $pumpForInsert = null;

            } else {
                // specific pump totals
                if (empty($hoseIds)) {
                    $totals = ['total_amount'=>0.00, 'total_volume'=>0.000, 'transactions'=>0];
                    $detailsRows = [];
                    $saleIds = [];
                } else {
                    $placeholders = implode(',', array_fill(0, count($hoseIds), '?'));
                    $params = array_merge($hoseIds, [$start_dt, $end_dt]);

                    $stmtT = $pdo->prepare("
                        SELECT IFNULL(SUM(si.total),0) AS total_amount,
                               IFNULL(SUM(si.qty),0) AS total_volume,
                               COUNT(DISTINCT si.sale_id) AS transactions
                        FROM sale_items si
                        JOIN sales s ON s.id = si.sale_id
                        WHERE si.hose_id IN ($placeholders) AND s.created_at BETWEEN ? AND ?
                    ");
                    $stmtT->execute($params);
                    $totals = $stmtT->fetch();

                    $stmtD = $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
                        WHERE si.hose_id IN ($placeholders) AND s.created_at BETWEEN ? AND ?
                        GROUP BY p.id, p.name ORDER BY p.name
                    ");
                    $stmtD->execute($params);
                    $detailsRows = $stmtD->fetchAll();

                    $stmtS = $pdo->prepare("SELECT DISTINCT si.sale_id FROM sale_items si JOIN sales s ON s.id = si.sale_id WHERE si.hose_id IN ($placeholders) AND s.created_at BETWEEN ? AND ?");
                    $stmtS->execute($params);
                    $saleIds = array_map('intval', $stmtS->fetchAll(PDO::FETCH_COLUMN));
                }

                $stmtPn = $pdo->prepare("SELECT name FROM pumps WHERE id = :id");
                $stmtPn->execute([':id'=>$form_pump_id]);
                $pn = $stmtPn->fetch();
                $pumpName = $pn ? $pn['name'] : null;
                $pumpForInsert = $form_pump_id;

                $totals['total_amount'] = round((float)($totals['total_amount'] ?? 0), 2);
                $totals['total_volume'] = round((float)($totals['total_volume'] ?? 0), 3);
                $per_pump = [
                    [
                        'pump_id' => $pumpForInsert,
                        'pump_name' => $pumpName,
                        'total_amount' => $totals['total_amount'],
                        'total_volume' => $totals['total_volume'],
                        'transactions' => (int)($totals['transactions'] ?? 0),
                        'sale_ids' => $saleIds,
                        'products' => array_map(function($r){ return ['product_id'=>(int)$r['product_id'],'product_name'=>$r['product_name'],'qty'=>round((float)$r['qty'],3),'total'=>round((float)$r['total'],2)]; }, $detailsRows)
                    ]
                ];
            }

            // Insert closure (force true: we proceed)
            $pdo->beginTransaction();
            $insertSql = "INSERT INTO sales_closures
                (pump_id, pump_name, start_dt, end_dt, total_amount, total_volume, transactions, created_by, notes, details, sale_ids)
                VALUES
                (:pump_id, :pump_name, :start_dt, :end_dt, :total_amount, :total_volume, :transactions, :created_by, :notes, :details, :sale_ids)";
            $stmtIns = $pdo->prepare($insertSql);

            $detailsPayload = [
                'overall_products' => array_map(function($r){ return ['product_id'=>(int)$r['product_id'],'product_name'=>$r['product_name'],'qty'=>round((float)$r['qty'],3),'total'=>round((float)$r['total'],2)]; }, $detailsRows ?? []),
                'per_pump' => $per_pump
            ];
            $detailsJson = json_encode($detailsPayload, JSON_UNESCAPED_UNICODE);
            $saleIdsJson = json_encode(array_values($saleIds ?? []));

            $stmtIns->execute([
                ':pump_id' => $pumpForInsert,
                ':pump_name' => $pumpName,
                ':start_dt' => $start_dt,
                ':end_dt' => $end_dt,
                ':total_amount' => $totals['total_amount'] ?? 0.00,
                ':total_volume' => $totals['total_volume'] ?? 0.000,
                ':transactions' => $totals['transactions'] ?? 0,
                ':created_by' => $form_created_by ?: null,
                ':notes' => $form_notes ?: null,
                ':details' => $detailsJson ?: null,
                ':sale_ids' => $saleIdsJson ?: null
            ]);
            $closureId = $pdo->lastInsertId();
            $pdo->commit();

            $result = [
                'closure_id' => $closureId,
                'start_dt' => $start_dt,
                'end_dt' => $end_dt,
                'pump' => $pumpName,
                'total_amount' => $totals['total_amount'] ?? 0.00,
                'total_volume' => $totals['total_volume'] ?? 0.000,
                'transactions' => $totals['transactions'] ?? 0,
                'details_payload' => $detailsPayload,
                'sale_ids' => $saleIds ?? []
            ];

            $info = "Cierre guardado correctamente (ID: $closureId).";

            // After success, reset form to today defaults
            $form_date_from = $today;
            $form_time_from = $default_time_from;
            $form_date_to = $today;
            $form_time_to = $default_time_to;
            $form_created_by = $_SESSION['username'] ?? '';
            $form_notes = '';

            // refresh recent closures list
            $stmtRecent = $pdo->query("SELECT id, pump_name, start_dt, end_dt, total_amount, transactions, created_at FROM sales_closures ORDER BY created_at DESC LIMIT 50");
            $recentClosures = $stmtRecent->fetchAll();

        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            $errors[] = "Error al calcular/guardar el cierre: " . $e->getMessage();
        }
    }
}
?>
<!doctype html>
<html lang="es">
<head>
  <meta charset="utf-8">
  <title>Cierre de Ventas</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%}
    table td, table th{border:1px solid #ddd;padding:6px}
    .right{text-align:right}
    .btn-home { display:inline-block; background: linear-gradient(180deg,#2b8aef,#1166d6); color:#fff; padding:10px 16px; border-radius:6px; text-decoration:none; font-weight:600; box-shadow: 0 3px 0 rgba(0,0,0,0.12); border: 1px solid rgba(0,0,0,0.08); transition: transform 0.08s ease, box-shadow 0.08s ease; }
    .btn-home:hover { transform: translateY(-1px); box-shadow:0 6px 18px rgba(0,0,0,0.12); }
    .title { font-size:18px; font-weight:700; }
    .required { color: #b00; margin-left:4px; font-weight:700; }
    .btn-submit { background: linear-gradient(180deg,#28a745,#218838); color:#fff; padding:8px 14px; border-radius:6px; border: none; font-weight:700; cursor:pointer; }
    .btn-submit:hover { opacity:0.95; }
    .force-wrap { display:flex; align-items:center; gap:8px; font-size:14px; color:#333; }
    .force-note { font-size:12px; color:#666; margin-top:4px; }
    .errors { color:#b00; margin-bottom:12px; }
    .info { color:green; margin-bottom:12px; }
  </style>
</head>
<body>
  <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:12px">
    <div class="title">Cierre de ventas</div>
    <div><a class="btn-home" href="index.php" title="Ir al inicio">← Ir a inicio</a></div>
  </div>

  <?php if (!empty($errors)): ?>
    <div class="errors"><strong>Errores:</strong><ul><?php foreach($errors as $e) echo '<li>'.h($e).'</li>'; ?></ul></div>
  <?php endif; ?>

  <?php if ($info): ?>
    <div class="info"><?=h($info)?> — <a href="closure_print.php?id=<?=h($result['closure_id'] ?? $closureId)?>" target="_blank">Ver/Imprimir cierre</a></div>
  <?php endif; ?>

  <form method="post" action="">
    <input type="hidden" name="csrf" value="<?=h($csrf)?>">
    <div class="row">
      <label for="pump_id">Surtidor:</label>
      <select name="pump_id" id="pump_id">
        <option value="all" <?= $form_pump_id==='all' ? 'selected' : '' ?>>-- Todos los surtidores --</option>
        <?php foreach ($pumps as $p): ?>
          <option value="<?=h($p['id'])?>" <?= $form_pump_id == $p['id'] ? 'selected' : '' ?>><?=h($p['name'])?></option>
        <?php endforeach; ?>
      </select>
    </div>

    <div class="row">
      <label>Fecha/Hora inicial:</label>
      <input type="date" name="date_from" value="<?=h($form_date_from)?>"> 
      <input type="time" name="time_from" value="<?=h($form_time_from)?>">
    </div>

    <div class="row">
      <label>Fecha/Hora final:</label>
      <input type="date" name="date_to" value="<?=h($form_date_to)?>"> 
      <input type="time" name="time_to" value="<?=h($form_time_to)?>">
      <div style="font-size:0.9em;color:#555">Si la hora final es menor que la inicial se interpretará que el turno cruzó a la madrugada siguiente.</div>
    </div>

    <div class="row">
      <label>Usuario que realiza el cierre: <span class="required">*</span></label>
      <input type="text" name="created_by" required value="<?=h($form_created_by)?>">
    </div>

    <div class="row">
      <label>Notas (opcional):</label>
      <textarea name="notes" rows="3" cols="60"><?=h($form_notes)?></textarea>
    </div>

    <div class="row">
      <div class="force-wrap">
        <input type="checkbox" checked disabled aria-disabled="true">
        <div>
          <div><strong>Forzar creación aun con cierres solapados</strong></div>
          <div class="force-note">Esta opción está activada por defecto y no se puede desmarcar. El sistema insertará el cierre incluso si el periodo se solapa con cierres previos.</div>
        </div>
      </div>
      <input type="hidden" name="force" value="1">
    </div>

    <div class="row">
      <button type="submit" class="btn-submit">Generar cierre</button>
    </div>
  </form>

  <hr>
  <h3>Últimos cierres</h3>
  <table>
    <tr><th>ID</th><th>Surtidor</th><th>Periodo</th><th class="right">Total</th><th>Tx</th><th>Fecha</th><th>Acciones</th></tr>
    <?php foreach ($recentClosures as $c): ?>
      <tr>
        <td><?=h($c['id'])?></td>
        <td><?=h($c['pump_name'] ?: 'TODOS')?></td>
        <td><?=h($c['start_dt'])?> → <?=h($c['end_dt'])?></td>
        <td class="right"><?=number_format((float)$c['total_amount'],2)?></td>
        <td><?=h($c['transactions'])?></td>
        <td><?=h($c['created_at'])?></td>
        <td>
          <a href="?export=closure&id=<?=h($c['id'])?>">Export CSV</a> |
          <a href="closure_print.php?id=<?=h($c['id'])?>" target="_blank">Ver / Imprimir</a>
        </td>
      </tr>
    <?php endforeach; ?>
  </table>

  <?php if (!empty($overlaps)): ?>
    <hr>
    <h4>Cierres solapados detectados (informativo)</h4>
    <ul>
      <?php foreach ($overlaps as $o): ?>
        <li>ID <?=h($o['id'])?> — Surtidor <?=h($o['pump_id'] ?? 'TODOS')?> — <?=h($o['start_dt'])?> → <?=h($o['end_dt'])?></li>
      <?php endforeach; ?>
    </ul>
  <?php endif; ?>

</body>
</html>