agent-v1

import * as XLSX from "xlsx";
import * as fs from "fs";
import * as os from "os";
import * as path from "path";
import * as crypto from "crypto";
import { Database } from "duckdb";
import type { FileHandle } from "@lmstudio/sdk";

export function sanitizeTableName(name: string): string {
    let clean = name.replace(/[^a-zA-Z0-9_]/g, "_");
    if (/^[0-9]/.test(clean)) clean = "t_" + clean; // table names can't start with a digit
    return clean;
}


function sanitizeColumnName(name: string): string {
    const trimmed = name.trim();
    let clean = trimmed.replace(/[^a-zA-Z0-9_]/g, "_");
    if (/^[0-9]/.test(clean)) clean = "c_" + clean;
    return clean || "column";
}

function run(db: Database, sql: string): Promise<void> {
    return new Promise((resolve, reject) => {
        db.run(sql, (err) => (err ? reject(err) : resolve()));
    });
}

function queryAll(db: Database, sql: string): Promise<Record<string, unknown>[]> {
    return new Promise((resolve, reject) => {
        db.all(sql, (err, rows) => (err ? reject(err) : resolve(rows)));
    });
}

/** Trims a string value; leaves non-strings untouched. */
function trimIfString(v: unknown): unknown {
    return typeof v === "string" ? v.trim() : v;
}

/** True if every non-null value in the sample looks like "<number><optional unit>",
 *  e.g. "4583.14 ms", "9.50 tokens/s", "12". Empty/all-null columns return false. */
function looksNumericWithUnit(values: unknown[]): boolean {
    const numWithUnit = /^-?\d+(\.\d+)?\s*[a-zA-Z/%]*$/;
    let sawValue = false;
    for (const v of values) {
        if (v === null || v === undefined || v === "") continue;
        if (typeof v !== "string") return false;
        sawValue = true;
        if (!numWithUnit.test(v.trim())) return false;
    }
    return sawValue;
}

function extractNumber(v: unknown): number | null {
    if (typeof v !== "string") return null;
    const match = v.trim().match(/-?\d+(\.\d+)?/);
    return match ? parseFloat(match[0]) : null;
}

export async function loadExcelIntoDuckDb(db: Database, file: FileHandle): Promise<void> {
    const filePath = await file.getFilePath();
    const workbook = XLSX.readFile(filePath);

    const tmpDir = fs.mkdtempSync(path.join(os.tmpdir(), "lms-excel-"));

    try {
        for (const sheetName of workbook.SheetNames) {
            const worksheet = workbook.Sheets[sheetName];
            const rawRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(worksheet, {
                defval: null,
                raw: true,
            });

            if (rawRows.length === 0) continue; // skip empty sheets, read_json_auto errors on empty input

            // Drop fully-blank rows (common as visual separators in spreadsheets) and
            // trim whitespace on every string cell / header.
            const cleanedRows = rawRows
                .map((row) => {
                    const cleaned: Record<string, unknown> = {};
                    for (const key of Object.keys(row)) {
                        cleaned[sanitizeColumnName(key)] = trimIfString(row[key]);
                    }
                    return cleaned;
                })
                .filter((row) => Object.values(row).some((v) => v !== null && v !== ""));

            if (cleanedRows.length === 0) continue;

            const jsonlPath = path.join(tmpDir, `${crypto.randomUUID()}.jsonl`);
            const lines = cleanedRows.map((r) => JSON.stringify(r)).join("\n");
            fs.writeFileSync(jsonlPath, lines);

            const tableName = sanitizeTableName(sheetName);
            const escapedPath = jsonlPath.replace(/'/g, "''");
            await run(db, `CREATE TABLE "${tableName}" AS SELECT * FROM read_json_auto('${escapedPath}')`);

            // For any text column that's uniformly "<number><unit>" (e.g. "4583.14 ms",
            // "9.50 tokens/s"), add a derived DOUBLE column so the model can sort/aggregate
            // numerically instead of doing string comparisons on unit-suffixed text.
            const columnNames = Object.keys(cleanedRows[0]);
            for (const col of columnNames) {
                const sampleValues = cleanedRows.map((r) => r[col]);
                if (!looksNumericWithUnit(sampleValues)) continue;

                const numericCol = `${col}_numeric`;
                await run(db, `ALTER TABLE "${tableName}" ADD COLUMN "${numericCol}" DOUBLE`);

                // Update row by row using rowid-free approach: rebuild via a CASE-free
                // UPDATE using regexp_extract, which DuckDB supports natively and is
                // both correct and fast (no need to loop in JS).
                await run(
                    db,
                    `UPDATE "${tableName}"
                     SET "${numericCol}" = TRY_CAST(regexp_extract("${col}", '-?[0-9]+(\\.[0-9]+)?') AS DOUBLE)`
                );
            }
        }
    } finally {
        fs.rmSync(tmpDir, { recursive: true, force: true });
    }
}

/** Small helper reused by the tools file to show the model real example rows. */
export async function getSampleRows(
    db: Database,
    tableName: string,
    limit = 3
): Promise<Record<string, unknown>[]> {
    return queryAll(db, `SELECT * FROM "${tableName}" LIMIT ${limit}`);
}