<?php
// api.php - JSON endpoints completo con sincronización desde sale.txt
require __DIR__ . '/init.php';
$pdo = pdo();

$rawInput = file_get_contents('php://input');
$jsonInput = json_decode($rawInput, true);
$action = $_GET['action'] ?? $_POST['action'] ?? ($jsonInput['action'] ?? '');

header('Content-Type: application/json; charset=utf-8');

// --- Pump status functions ---
function fp_status_file_path() {
    $candidates = [
        'C:/opscode/fs3xp.txt',
        'C:/opscode/fss3xp.txt',
        'C:\\opscode\\fs3xp.txt',
        'C:\\opscode\\fss3xp.txt',
    ];
    foreach ($candidates as $p) {
        if (@file_exists($p)) return $p;
    }
    return null;
}

function fp_read_pump_statuses() {
    $path = fp_status_file_path();
    if (!$path) return ['ok'=>false,'path'=>null,'statuses'=>[],'error'=>'status file not found'];
    @clearstatcache(true, $path);
    $content = @file_get_contents($path);
    if ($content === false) {
        if (function_exists('shell_exec')) {
            $cmd = 'type "' . str_replace('"', '\"', $path) . '"';
            $alt = @shell_exec($cmd);
            if (is_string($alt) && trim($alt) !== '') {
                $content = $alt;
            }
        }
    }
    if ($content === false) {
        return ['ok'=>false,'path'=>$path,'statuses'=>[],'error'=>'cannot read status file'];
    }
    
    $statuses = [];
    foreach (preg_split("/\\r\\n|\\n|\\r/", trim($content)) as $line) {
        $line = trim($line);
        if ($line === '') continue;
        if (preg_match('/^(\\d+)\\s*[,=:]\\s*([A-Za-z_\\-]+)\\s*$/', $line, $m)) {
            $id = (int)$m[1];
            $st = strtoupper(trim($m[2]));
            if ($st === 'IDELL') $st = 'IDLE';
            if ($st === 'FUELING' || $st === 'FUEL') $st = 'FUELLING';
            $statuses[$id] = $st;
        }
    }
    return ['ok'=>true,'path'=>$path,'statuses'=>$statuses,'error'=>null];
}

function fp_status_to_icon($status) {
    $status = strtoupper(trim((string)$status));
    if ($status === 'IDELL') $status = 'IDLE';
    if ($status === 'FUELING' || $status === 'FUEL') $status = 'FUELLING';
    if ($status === '' || $status === 'IDLE') return 'imagen/surtidor_300x300.jpg';
    if ($status === 'CALLING' || $status === 'STARTING') return 'imagen/surtidor_naranja.jpg';
    if ($status === 'FUELLING') return 'imagen/surtidor_azul.jpg';
    if ($status === 'CLOSED' || $status === 'ERROR') return 'imagen/surtidor_rojo.jpg';
    return 'imagen/surtidor_300x300.jpg';
}

// --- SYNC SALES FROM sale.txt ---
function sync_sales_from_file_action($pdo) {
    $salesFile = 'C:/opscode/sale.txt';
    
    if (!file_exists($salesFile)) {
        return ['ok'=>0,'registered'=>0,'skipped'=>0,'message'=>'Archivo sale.txt no encontrado en C:/opscode/'];
    }

    @clearstatcache(true, $salesFile);
    $content = @file_get_contents($salesFile);
    
    if ($content === false) {
        return ['ok'=>0,'registered'=>0,'skipped'=>0,'message'=>'No se pudo leer sale.txt'];
    }

    $lines = preg_split("/\\r\\n|\\n|\\r/", trim($content));
    
    if (count($lines) < 2) {
        return ['ok'=>1,'registered'=>0,'skipped'=>0,'message'=>'Archivo vacío o sin datos'];
    }

    array_shift($lines);
    
    $user_id = null;
    if (isset($_SESSION['user_id']) && $_SESSION['user_id'] > 0) {
        $user_id = (int)$_SESSION['user_id'];
    } elseif (isset($_SESSION['username'])) {
        $stmtUser = $pdo->prepare("SELECT id FROM users WHERE username = ? LIMIT 1");
        $stmtUser->execute([$_SESSION['username']]);
        $userRow = $stmtUser->fetch();
        if ($userRow) {
            $user_id = (int)$userRow['id'];
        }
    }
    
    if (!$user_id) {
        $stmtDefaultUser = $pdo->query("SELECT id FROM users WHERE active = 1 ORDER BY id LIMIT 1");
        $defaultUser = $stmtDefaultUser->fetch();
        if ($defaultUser) {
            $user_id = (int)$defaultUser['id'];
        }
    }
    
    $registered = 0;
    $skipped = 0;

    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '') continue;

        $cols = array_map('trim', explode(",", $line));
        
        if (count($cols) < 19) {
            $skipped++;
            continue;
        }

        $idVenta = $cols[0];
        $numVenta = $cols[1];
        $fechaFin = $cols[2];
        $horaFin = $cols[3];
        $surtidor = (int)$cols[4];
        $manguera = (int)$cols[5];
        $idProducto2 = (int)$cols[6];
        
        $volumenStr = str_replace([',', ' Gal', ' ', '$'], ['', '', '', ''], $cols[8]);
        $precioStr = str_replace(['$', ',', ' '], ['', '', ''], $cols[10]);
        $montoStr = str_replace(['$', ',', ' '], ['', '', ''], $cols[11]);
        
        $volumen = (float)$volumenStr;
        $precio = (float)$precioStr;
        $monto = (float)$montoStr;

        if ($volumen <= 0 || $monto <= 0) {
            $skipped++;
            continue;
        }

        $saleCode = 'EXT-' . $numVenta;
        
        $checkStmt = $pdo->prepare("SELECT id FROM sales WHERE sale_code = ? LIMIT 1");
        $checkStmt->execute([$saleCode]);
        
        if ($checkStmt->fetch()) {
            $skipped++;
            continue;
        }

        $dateParts = explode('/', $fechaFin);
        if (count($dateParts) === 3) {
            $fechaSQL = $dateParts[2] . '-' . str_pad($dateParts[1], 2, '0', STR_PAD_LEFT) . '-' . str_pad($dateParts[0], 2, '0', STR_PAD_LEFT) . ' ' . $horaFin;
        } else {
            $fechaSQL = date('Y-m-d H:i:s');
        }

        $stmtProd = $pdo->prepare("SELECT id FROM products WHERE id = ? LIMIT 1");
        $stmtProd->execute([$idProducto2]);
        $product = $stmtProd->fetch();
        
        if (!$product) {
            $skipped++;
            continue;
        }
        
        $productId = (int)$product['id'];

        $stmtHose = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = ? AND name = ? LIMIT 1");
        $stmtHose->execute([$surtidor, (string)$manguera]);
        $hose = $stmtHose->fetch();
        $hoseId = $hose ? (int)$hose['id'] : null;

        $pdo->beginTransaction();
        try {
            $stmtSale = $pdo->prepare("INSERT INTO sales (sale_code, user_id, total, payment_method, created_at) VALUES (?, ?, ?, 'Efectivo', ?)");
            $stmtSale->execute([$saleCode, $user_id, $monto, $fechaSQL]);
            $saleId = $pdo->lastInsertId();

            $unitPrice = $precio;

            $stmtItem = $pdo->prepare("INSERT INTO sale_items (sale_id, product_id, hose_id, qty, unit_price, total) VALUES (?, ?, ?, ?, ?, ?)");
            $stmtItem->execute([$saleId, $productId, $hoseId, $volumen, $unitPrice, $monto]);

            $stmtTank = $pdo->prepare("UPDATE tanques SET vendido = vendido + ?, disponible = disponible - ? WHERE producto_id = ?");
            $stmtTank->execute([$volumen, $volumen, $productId]);

            $pdo->commit();
            $registered++;
            
        } catch (Exception $e) {
            $pdo->rollBack();
            $skipped++;
        }
    }

    return [
        'ok' => 1,
        'registered' => $registered,
        'skipped' => $skipped,
        'message' => "Sincronización completada: $registered nuevas, $skipped omitidas"
    ];
}

try {
    // ============================================
    // SYNC SALES FROM FILE
    // ============================================
    if ($action === 'sync_sales_from_file') {
        $result = sync_sales_from_file_action($pdo);
        echo json_encode($result);
        exit;
    }

    // ============================================
    // GET PRODUCTS
    // ============================================
    if ($action === 'get_products') {
        $rows = $pdo->query("SELECT id,name,sku,price,unit,COALESCE(bg_color,'') as bg_color,active FROM products WHERE (active IS NULL OR active=1) ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['ok'=>1,'products'=>$rows]);
        exit;
    }

    // ============================================
    // PRODUCT SALES TOTALS
    // ============================================
    if ($action === 'get_products_sales') {
        $range = (int)($_GET['range'] ?? 7);
        $range = max(1, min(365, $range));
        $start = date('Y-m-d H:i:s', strtotime("-{$range} days"));
        $stmt = $pdo->prepare("
            SELECT p.id, p.name, COALESCE(SUM(CASE WHEN s.created_at >= ? THEN si.total ELSE 0 END),0) as total
            FROM products p
            LEFT JOIN sale_items si ON si.product_id = p.id
            LEFT JOIN sales s ON s.id = si.sale_id
            GROUP BY p.id
            ORDER BY total DESC
        ");
        $stmt->execute([$start]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['ok'=>1,'products'=>$rows,'updated_at'=>date('Y-m-d H:i:s')]);
        exit;
    }

    // ============================================
    // DASHBOARD SUMMARY
    // ============================================
    if ($action === 'get_dashboard') {
        $range = (int)($_GET['range'] ?? 7);
        $range = max(1, min(90, $range));
        $labels = []; 
        $data = [];
        
        for ($i = $range-1; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-{$i} days"));
            $labels[] = date('d M', strtotime($d));
            $stmt = $pdo->prepare("SELECT COALESCE(SUM(total),0) FROM sales WHERE DATE(created_at)=?");
            $stmt->execute([$d]);
            $data[] = (float)$stmt->fetchColumn();
        }
        
        $today = date('Y-m-d');
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(total),0) FROM sales WHERE DATE(created_at)=?");
        $stmt->execute([$today]); 
        $total_today = (float)$stmt->fetchColumn();
        
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM sales WHERE DATE(created_at)=?");
        $stmt->execute([$today]); 
        $count_today = (int)$stmt->fetchColumn();

        $stmt = $pdo->prepare("
            SELECT p.id, p.name, SUM(si.qty) as qty, SUM(si.total) as total
            FROM sale_items si
            JOIN products p ON p.id = si.product_id
            JOIN sales s ON s.id = si.sale_id
            WHERE s.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
            GROUP BY p.id
            ORDER BY total DESC
            LIMIT 5
        ");
        $stmt->execute([$range]);
        $top = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo json_encode([
            'ok'=>1,
            'labels'=>$labels,
            'data'=>$data,
            'total_today'=>$total_today,
            'count_today'=>$count_today,
            'top_products'=>$top,
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // ============================================
    // PRODUCTS DAILY SERIES
    // ============================================
    if ($action === 'get_products_daily') {
        $range = (int)($_GET['range'] ?? 7);
        $range = max(1, min(90, $range));
        $labels = [];
        
        for ($i = $range-1; $i >= 0; $i--) {
            $labels[] = date('d M', strtotime("-{$i} days"));
        }
        
        $stmt = $pdo->prepare("
            SELECT DISTINCT p.id, p.name 
            FROM products p 
            JOIN sale_items si ON si.product_id = p.id 
            JOIN sales s ON s.id = si.sale_id 
            WHERE s.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY) 
            ORDER BY p.id
        ");
        $stmt->execute([$range]);
        $prods = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        $series = [];
        foreach ($prods as $p) {
            $data = [];
            for ($i = $range-1; $i >= 0; $i--) {
                $d = date('Y-m-d', strtotime("-{$i} days"));
                $st = $pdo->prepare("
                    SELECT COALESCE(SUM(si.total),0) 
                    FROM sale_items si 
                    JOIN sales s ON s.id = si.sale_id 
                    WHERE si.product_id = ? AND DATE(s.created_at) = ?
                ");
                $st->execute([$p['id'], $d]);
                $data[] = (float)$st->fetchColumn();
            }
            $series[] = [
                'product_id' => (int)$p['id'],
                'name' => $p['name'],
                'data' => $data
            ];
        }
        
        echo json_encode([
            'ok'=>1,
            'labels'=>$labels,
            'series'=>$series,
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // ============================================
    // RECENT SALES
    // ============================================
    if ($action === 'get_recent_sales') {
        $limit = isset($_GET['limit']) ? max(1, min(100, (int)$_GET['limit'])) : 10;
        $stmt = $pdo->prepare("
            SELECT s.id, s.sale_code, s.total, s.created_at, COALESCE(u.username,'Sistema') as username 
            FROM sales s 
            LEFT JOIN users u ON u.id = s.user_id 
            ORDER BY s.created_at DESC, s.id DESC
            LIMIT ?
        ");
        $stmt->bindValue(1, (int)$limit, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        echo json_encode(['ok'=>1,'sales'=>$rows]);
        exit;
    }

    // ============================================
    // GET PUMPS WITH STATUS AND LAST SALE (UNIFIED)
    // ============================================
    if ($action === 'get_pumps_unified') {
        $pumps = $pdo->query("SELECT * FROM pumps ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        $st = fp_read_pump_statuses();
        $statuses = $st['statuses'];

        $out = [];
        foreach ($pumps as $p) {
            $pid = (int)$p['id'];
            $status = $statuses[$pid] ?? 'IDLE';

            $hoseStmt = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = ?");
            $hoseStmt->execute([$pid]);
            $hoseIds = $hoseStmt->fetchAll(PDO::FETCH_COLUMN, 0);

            $last_sale = null;
            if (count($hoseIds) > 0) {
                $placeholders = implode(',', array_fill(0, count($hoseIds), '?'));
                $sql = "
                    SELECT s.id, s.sale_code, s.created_at, SUM(si.total) as total
                    FROM sales s
                    JOIN sale_items si ON si.sale_id = s.id
                    WHERE si.hose_id IN ($placeholders)
                    GROUP BY s.id, s.sale_code, s.created_at
                    ORDER BY s.created_at DESC, s.id DESC
                    LIMIT 1
                ";
                $stSale = $pdo->prepare($sql);
                foreach ($hoseIds as $k => $hid) {
                    $stSale->bindValue($k+1, (int)$hid, PDO::PARAM_INT);
                }
                $stSale->execute();
                $row = $stSale->fetch(PDO::FETCH_ASSOC);
                
                if ($row) {
                    $last_sale = [
                        'id' => (int)$row['id'],
                        'sale_code' => $row['sale_code'],
                        'created_at' => $row['created_at'],
                        'total' => (float)$row['total']
                    ];
                }
            }

            $out[] = [
                'id' => $pid,
                'name' => $p['name'],
                'location' => $p['location'] ?? '',
                'status' => $status,
                'image_url' => site(fp_status_to_icon($status)) . '?v=' . time(),
                'last_sale' => $last_sale
            ];
        }

        echo json_encode([
            'ok'=>1,
            'pumps'=>$out,
            'status_ok'=>$st['ok'],
            'status_file'=>$st['path'],
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // ============================================
    // GET PUMPS (SOLO ESTADOS - LEGACY)
    // ============================================
    if ($action === 'get_pumps') {
        $rows = $pdo->query("SELECT * FROM pumps ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        $st = fp_read_pump_statuses();
        $statuses = $st['statuses'];

        foreach ($rows as &$p) {
            $pid = (int)$p['id'];
            $status = $statuses[$pid] ?? 'IDLE';
            $p['status'] = $status;
            $p['image_url'] = site(fp_status_to_icon($status)) . '?v=' . time();
        }

        echo json_encode([
            'ok'=>1,
            'pumps'=>$rows,
            'status_ok'=>$st['ok'],
            'status_file'=>$st['path'],
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // ============================================
    // GET HOSES
    // ============================================
    if ($action === 'get_hoses_all' || $action === 'get_hoses') {
        $pump_id = isset($_GET['pump_id']) ? (int)$_GET['pump_id'] : 0;
        
        if ($pump_id > 0) {
            $stmt = $pdo->prepare("
                SELECT h.id, h.name, h.pump_id, h.product_id, COALESCE(p.name,'') AS product_name 
                FROM hoses h 
                LEFT JOIN products p ON p.id = h.product_id 
                WHERE h.pump_id = ? 
                ORDER BY h.id
            ");
            $stmt->execute([$pump_id]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } else {
            $rows = $pdo->query("
                SELECT h.id, h.name, h.pump_id, h.product_id, COALESCE(p.name,'') AS product_name 
                FROM hoses h 
                LEFT JOIN products p ON p.id = h.product_id 
                ORDER BY h.pump_id, h.id
            ")->fetchAll(PDO::FETCH_ASSOC);
        }
        
        echo json_encode(['ok'=>1,'hoses'=>$rows]);
        exit;
    }

    // ============================================
    // GET PUMPS WITH LAST SALE (LEGACY)
    // ============================================
    if ($action === 'get_pumps_with_last_sale') {
        $pumps = $pdo->query("SELECT * FROM pumps ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        $st = fp_read_pump_statuses();
        $statuses = $st['statuses'];

        $out = [];
        foreach ($pumps as $p) {
            $pid = (int)$p['id'];
            $status = $statuses[$pid] ?? 'IDLE';

            $hoseStmt = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = ?");
            $hoseStmt->execute([$pid]);
            $hoseIds = $hoseStmt->fetchAll(PDO::FETCH_COLUMN, 0);

            $last_sale = null;
            if (count($hoseIds) > 0) {
                $placeholders = implode(',', array_fill(0, count($hoseIds), '?'));
                $sql = "
                    SELECT s.id, s.sale_code, s.created_at, SUM(si.total) as total
                    FROM sales s
                    JOIN sale_items si ON si.sale_id = s.id
                    WHERE si.hose_id IN ($placeholders)
                    GROUP BY s.id, s.sale_code, s.created_at
                    ORDER BY s.created_at DESC, s.id DESC
                    LIMIT 1
                ";
                $stSale = $pdo->prepare($sql);
                foreach ($hoseIds as $k => $hid) {
                    $stSale->bindValue($k+1, (int)$hid, PDO::PARAM_INT);
                }
                $stSale->execute();
                $row = $stSale->fetch(PDO::FETCH_ASSOC);
                
                if ($row) {
                    $last_sale = [
                        'id' => (int)$row['id'],
                        'sale_code' => $row['sale_code'],
                        'created_at' => $row['created_at'],
                        'total' => (float)$row['total']
                    ];
                }
            }

            $out[] = [
                'id' => $pid,
                'name' => $p['name'],
                'location' => $p['location'] ?? '',
                'status' => $status,
                'image_url' => site(fp_status_to_icon($status)) . '?v=' . time(),
                'last_sale' => $last_sale
            ];
        }

        echo json_encode([
            'ok'=>1,
            'pumps'=>$out,
            'status_ok'=>$st['ok'],
            'status_file'=>$st['path'],
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // ============================================
    // GET PUMP SALES DETAILED
    // ============================================
    if ($action === 'get_pump_sales_detailed') {
        $pump_id = isset($_GET['pump_id']) ? (int)$_GET['pump_id'] : 0;
        $limit = isset($_GET['limit']) ? max(1, min(2000, (int)$_GET['limit'])) : 200;
        
        if ($pump_id <= 0) {
            echo json_encode(['ok'=>0,'error'=>'pump_id requerido']);
            exit;
        }

        $hoseStmt = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = ?");
        $hoseStmt->execute([$pump_id]);
        $hoseIds = $hoseStmt->fetchAll(PDO::FETCH_COLUMN, 0);

        if (!$hoseIds) {
            echo json_encode(['ok'=>1,'sales'=>[]]);
            exit;
        }

        $placeholders = implode(',', array_fill(0, count($hoseIds), '?'));
        $sqlSales = "
            SELECT DISTINCT s.id AS sale_id, s.sale_code, s.created_at
            FROM sales s
            JOIN sale_items si ON si.sale_id = s.id
            WHERE si.hose_id IN ($placeholders)
            ORDER BY s.created_at DESC, s.id DESC
            LIMIT ?
        ";
        $stmt = $pdo->prepare($sqlSales);
        foreach ($hoseIds as $k => $hid) {
            $stmt->bindValue($k+1, $hid, PDO::PARAM_INT);
        }
        $stmt->bindValue(count($hoseIds)+1, $limit, PDO::PARAM_INT);
        $stmt->execute();
        $saleRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$saleRows) {
            echo json_encode(['ok'=>1,'sales'=>[]]);
            exit;
        }

        $saleIds = array_column($saleRows, 'sale_id');
        $placeholders_sale = implode(',', array_fill(0, count($saleIds), '?'));

        $sqlItems = "
            SELECT si.sale_id, si.qty, si.unit_price, si.total AS item_total, si.hose_id,
                   COALESCE(p.name, '') AS product_name, COALESCE(p.sku, '') AS product_sku
            FROM sale_items si
            JOIN products p ON p.id = si.product_id
            WHERE si.sale_id IN ($placeholders_sale)
              AND si.hose_id IN ($placeholders)
            ORDER BY si.sale_id, si.id
        ";
        $stmt2 = $pdo->prepare($sqlItems);
        $i = 1;
        foreach ($saleIds as $sid) {
            $stmt2->bindValue($i++, $sid, PDO::PARAM_INT);
        }
        foreach ($hoseIds as $hid) {
            $stmt2->bindValue($i++, $hid, PDO::PARAM_INT);
        }
        $stmt2->execute();
        $itemsRows = $stmt2->fetchAll(PDO::FETCH_ASSOC);

        $itemsBySale = [];
        foreach ($itemsRows as $it) {
            $sid = (int)$it['sale_id'];
            if (!isset($itemsBySale[$sid])) $itemsBySale[$sid] = [];
            $itemsBySale[$sid][] = [
                'qty' => (float)$it['qty'],
                'unit_price' => (float)$it['unit_price'],
                'total' => (float)$it['item_total'],
                'product_name' => $it['product_name'],
                'product_sku' => $it['product_sku'],
                'hose_id' => isset($it['hose_id']) ? (int)$it['hose_id'] : null
            ];
        }

        $out = [];
        foreach ($saleRows as $sr) {
            $sid = (int)$sr['sale_id'];
            $out[] = [
                'sale_id' => $sid,
                'sale_code' => $sr['sale_code'],
                'created_at' => $sr['created_at'],
                'items' => $itemsBySale[$sid] ?? []
            ];
        }

        echo json_encode(['ok'=>1,'sales'=>$out]);
        exit;
    }

    // ============================================
    // GET TANQUES STATUS
    // ============================================
    if ($action === 'get_tanques_status') {
        $sql = "
            SELECT
                t.id,
                t.capacidad,
                t.stock_inicial,
                t.vendido,
                p.name AS producto_nombre,
                p.sku AS sku,
                IFNULL(SUM(c.cantidad),0) AS total_compras,
                (t.stock_inicial + IFNULL(SUM(c.cantidad),0) - t.vendido) AS disponible_real
            FROM tanques t
            LEFT JOIN products p ON p.id = t.producto_id
            LEFT JOIN compras_tanques c ON c.tanque_id = t.id
            GROUP BY t.id, t.capacidad, t.stock_inicial, t.vendido, p.name, p.sku
            ORDER BY t.id ASC
        ";
        $stmt = $pdo->prepare($sql);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($rows as &$row) {
            $row['disponible_real'] = floatval($row['disponible_real']);
            $row['capacidad'] = floatval($row['capacidad']);
            $row['stock_inicial'] = floatval($row['stock_inicial']);
            $row['vendido'] = floatval($row['vendido']);
            $row['total_compras'] = floatval($row['total_compras']);
        }
        unset($row);

        echo json_encode(['ok'=>1, 'tanques'=>$rows]);
        exit;
    }

    // ============================================
    // CREATE SALE
    // ============================================
    if ($action === 'create_sale') {
        $data = $jsonInput ?: $_POST;
        if (!$data || empty($data['items'])) {
            throw new Exception('items requeridos');
        }
        
        $items = $data['items'];
        $payment = $data['payment_method'] ?? 'cash';
        
        $user_id = isset($data['user_id']) ? (int)$data['user_id'] : null;
        
        if (!$user_id && isset($_SESSION['user_id'])) {
            $user_id = (int)$_SESSION['user_id'];
        }
        
        $total = 0;
        
        foreach ($items as $it) {
            $total += (float)$it['qty'] * (float)$it['unit_price'];
        }
        
        $code = 'S' . date('YmdHis') . rand(100,999);
        
        $pdo->beginTransaction();
        $stmt = $pdo->prepare("INSERT INTO sales (sale_code,user_id,total,payment_method) VALUES (?,?,?,?)");
        $stmt->execute([$code,$user_id,$total,$payment]);
        $sale_id = $pdo->lastInsertId();

        $include_hose = isset($items[0]['hose_id']);
        
        if ($include_hose) {
            $si = $pdo->prepare("INSERT INTO sale_items (sale_id,product_id,qty,unit_price,total,hose_id) VALUES (?,?,?,?,?,?)");
            foreach ($items as $it) {
                $qty = (float)$it['qty']; 
                $up = (float)$it['unit_price'];
                $pid = (int)$it['product_id'];
                $hid = isset($it['hose_id']) ? (int)$it['hose_id'] : null;
                $si->execute([$sale_id, $pid, $qty, $up, $qty*$up, $hid]);
                
                $upd = $pdo->prepare("UPDATE tanques SET vendido = vendido + ?, disponible = disponible - ? WHERE producto_id = ?");
                $upd->execute([$qty, $qty, $pid]);
            }
        } else {
            $si = $pdo->prepare("INSERT INTO sale_items (sale_id,product_id,qty,unit_price,total) VALUES (?,?,?,?,?)");
            foreach ($items as $it) {
                $qty = (float)$it['qty']; 
                $up = (float)$it['unit_price'];
                $pid = (int)$it['product_id'];
                $si->execute([$sale_id, $pid, $qty, $up, $qty*$up]);
                
                $upd = $pdo->prepare("UPDATE tanques SET vendido = vendido + ?, disponible = disponible - ? WHERE producto_id = ?");
                $upd->execute([$qty, $qty, $pid]);
            }
        }
        
        $pdo->commit();

        echo json_encode(['ok'=>1,'sale_id'=>$sale_id,'sale_code'=>$code,'total'=>$total]);
        exit;
    }

    // ============================================
    // ✅ GET COMPANIES (NUEVO)
    // ============================================
    if ($action === 'get_companies') {
        try {
            $stmt = $pdo->query("
                SELECT id, codigo, nombre, direccion, telefono, contacto, centro_costo 
                FROM empresa 
                ORDER BY nombre
            ");
            $companies = $stmt->fetchAll(PDO::FETCH_ASSOC);
            echo json_encode(['ok' => 1, 'companies' => $companies]);
        } catch (Exception $e) {
            echo json_encode(['ok' => 0, 'message' => $e->getMessage()]);
        }
        exit;
    }

    // ============================================
    // ✅ ASSIGN EMPLOYEE CREDIT (NUEVO)
    // ============================================
    if ($action === 'assign_employee_credit') {
        try {
            $data = json_decode(file_get_contents('php://input'), true) ?: $_POST;
            
            $sale_id = (int)($data['sale_id'] ?? 0);
            $sale_code = $data['sale_code'] ?? '';
            $total = (float)($data['total'] ?? 0);
            $codigo_empresa = $data['codigo_empresa'] ?? '';
            $nombre_empresa = $data['nombre_empresa'] ?? '';
            $codigo_tarjeta = $data['codigo_tarjeta'] ?? '';
            $user_id = (int)($data['user_id'] ?? 0);
            
            if (!$codigo_tarjeta || !$codigo_empresa || !$sale_id) {
                throw new Exception('Faltan datos requeridos');
            }
            
            // Buscar empleado por código de tarjeta
            $stmt = $pdo->prepare("
                SELECT e.*, emp.nombre as nombre_empresa 
                FROM empleados e 
                JOIN empresa emp ON emp.codigo = e.codigo_empresa 
                WHERE e.codigo_tarjeta = ? AND e.codigo_empresa = ?
            ");
            $stmt->execute([$codigo_tarjeta, $codigo_empresa]);
            $empleado = $stmt->fetch(PDO::FETCH_ASSOC);
            
            if (!$empleado) {
                throw new Exception('Empleado no encontrado con ese código de tarjeta');
            }
            
            // Obtener balance actual
            $stmt = $pdo->prepare("
                SELECT balance 
                FROM trans_creditos 
                WHERE codigo_empleado = ? 
                ORDER BY created_at DESC 
                LIMIT 1
            ");
            $stmt->execute([$empleado['codigo_empleado']]);
            $lastTrans = $stmt->fetch(PDO::FETCH_ASSOC);
            $currentBalance = $lastTrans ? (float)$lastTrans['balance'] : (float)$empleado['limite_credito'];
            
            // Verificar límite de crédito
            if ($currentBalance < $total) {
                throw new Exception('Saldo insuficiente. Disponible: $' . number_format($currentBalance, 2));
            }
            
            $newBalance = $currentBalance - $total;
            
            // Insertar transacción
            $pdo->beginTransaction();
            
            $stmt = $pdo->prepare("
                INSERT INTO trans_creditos (
                    codigo_empresa, nombre_empresa, 
                    codigo_empleado, nombre_empleado,
                    codigo_tarjeta, sale_id, sale_code,
                    debito, credito, balance,
                    tipo_transaccion, descripcion, user_id
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0.00, ?, 'debito', ?, ?)
            ");
            
            $stmt->execute([
                $codigo_empresa,
                $nombre_empresa,
                $empleado['codigo_empleado'],
                $empleado['nombre'],
                $codigo_tarjeta,
                $sale_id,
                $sale_code,
                $total,
                $newBalance,
                'Compra de combustible - ' . $sale_code,
                $user_id
            ]);
            
            // Actualizar método de pago de la venta
            $stmt = $pdo->prepare("UPDATE sales SET payment_method = 'credito_empleado' WHERE id = ?");
            $stmt->execute([$sale_id]);
            
            $pdo->commit();
            
            echo json_encode([
                'ok' => 1, 
                'message' => 'Crédito asignado correctamente a ' . $empleado['nombre'],
                'balance' => $newBalance,
                'empleado' => $empleado['nombre'],
                'empresa' => $nombre_empresa
            ]);
            
        } catch (Exception $e) {
            if ($pdo->inTransaction()) $pdo->rollBack();
            echo json_encode(['ok' => 0, 'message' => $e->getMessage()]);
        }
        exit;
    }

    // ============================================
    // Action no válida
    // ============================================
    echo json_encode(['ok'=>0,'msg'=>'action no valido: ' . $action]);
    exit;

} catch (Exception $e) {
    if (isset($pdo) && $pdo->inTransaction()) {
        $pdo->rollBack();
    }
    http_response_code(500);
    echo json_encode(['ok'=>0,'msg'=>$e->getMessage(),'trace'=>$e->getTraceAsString()]);
    exit;
}