<?php
// api.php - JSON endpoints for dashboard, products, pumps, hoses and sales
require __DIR__ . '/init.php';
$pdo = pdo();

// read raw JSON body if present
$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 by file (fs3xp/fss3xp) ---
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'];

    // Ensure fresh stats on Windows
    @clearstatcache(true, $path);

    $content = @file_get_contents($path); // opens+reads+closes each request
    if ($content === false) {
        // Windows services sometimes lock the file; fallback to `type` if allowed
        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;
        // Accept formats: "2, FUELLING" | "2: FUELLING" | "2=FUELLING"
        if (preg_match('/^(\\d+)\\s*[,=:]\\s*([A-Za-z_\\-]+)\\s*$/', $line, $m)) {
            $id = (int)$m[1];
            $st = strtoupper(trim($m[2]));
            // Normalize
            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';

    // Default should be GREEN (IDLE)
    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') return 'imagen/surtidor_rojo.jpg';
    return 'imagen/surtidor_300x300.jpg';
}
// --- end Pump status by file ---



try {
    // 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 (for tile chart)
    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 (labels, data daily totals, top products)
    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 (for multi-series sales chart)
    if ($action === 'get_products_daily') {
        $range = (int)($_GET['range'] ?? 7);
        $range = max(1, min(90, $range));
        // build date labels
        $labels = [];
        for ($i = $range-1; $i >= 0; $i--) $labels[] = date('d M', strtotime("-{$i} days"));
        // get distinct products that had sales in range
        $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,'') as username FROM sales s LEFT JOIN users u ON u.id = s.user_id ORDER BY s.created_at 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;
    }

    // PUMPS
    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;

            // FORCE icon by status (ignore uploaded image)
            $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'],
            'status_debug'=>['ok'=>$st['ok'],'path'=>$st['path'],'count'=>count($st['statuses']),'error'=>$st['error']],
            'updated_at'=>date('Y-m-d H:i:s')
        ]);
        exit;
    }

    // HOSES endpoints
    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
    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';

            // collect hoses for last sale
            $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
                    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,
                // FORCE icon by 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'],
            'status_debug'=>['ok'=>$st['ok'],'path'=>$st['path'],'count'=>count($st['statuses']),'error'=>$st['error']],
            '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 ?
        ";
        $params = array_merge($hoseIds, [$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
        ";
        $itemParams = array_merge($saleIds, $hoseIds);
        $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;
    }

    // NUEVO ENDPOINT: Estado gráfico de tanques para dashboard principal
    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']);
            // $row['sku'] ya viene desde la consulta (la unidad!)
        }
        unset($row);

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

    // CREATE SALE (modificado para devolver tanques e islas en tiempo real)
    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;
        $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]);
                // ACTUALIZAR TANQUES
                $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]);
                // ACTUALIZAR TANQUES
                $upd = $pdo->prepare("UPDATE tanques SET vendido = vendido + ?, disponible = disponible - ? WHERE producto_id = ?");
                $upd->execute([$qty, $qty, $pid]);
            }
        }
        $pdo->commit();

        // Obtener tanques actualizados
        $sqlT = "
            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
        ";
        $stmtT = $pdo->prepare($sqlT);
        $stmtT->execute();
        $rowsT = $stmtT->fetchAll(PDO::FETCH_ASSOC);
        foreach ($rowsT 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);

        // Estado actualizado islas/bombas
        $pumps = $pdo->query("SELECT * FROM pumps ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
        $uploadsDir = __DIR__ . '/uploads/pumps';
        $outPumps = [];
        foreach ($pumps as $p) {
            $imgUrl = site('assets/pump.svg');
            if (!empty($p['image_filename'])) {
                $path = __DIR__ . '/uploads/pumps/' . $p['image_filename'];
                if (file_exists($path)) $imgUrl = site('uploads/pumps/' . $p['image_filename']);
            } else {
                foreach (['png','jpg','jpeg','webp'] as $ext) {
                    $f = $uploadsDir . '/' . $p['id'] . '.' . $ext;
                    if (file_exists($f)) { $imgUrl = site('uploads/pumps/' . $p['id'] . '.' . $ext); break; }
                }
            }
            $hoseStmt = $pdo->prepare("SELECT id FROM hoses WHERE pump_id = ?");
            $hoseStmt->execute([$p['id']]);
            $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
                    ORDER BY s.created_at DESC, s.id DESC
                    LIMIT 1
                ";
                $st = $pdo->prepare($sql);
                foreach ($hoseIds as $k => $hid) {
                    $st->bindValue($k+1, $hid, PDO::PARAM_INT);
                }
                $st->execute();
                $row = $st->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']
                    ];
                }
            }

            $outPumps[] = [
                'id' => (int)$p['id'],
                'name' => $p['name'],
                'location' => $p['location'] ?? '',
                'image_url' => $imgUrl,
                'last_sale' => $last_sale
            ];
        }

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

    echo json_encode(['ok'=>0,'msg'=>'action no valido']);
    exit;

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['ok'=>0,'msg'=>$e->getMessage()]);
    exit;
}