agent-v1

import { tool } from "@lmstudio/sdk";
import { z } from "zod";
import { getOrCreateDb, queryAll } from "./buildExcelTools";
import { runExcelSubAgent } from "./excelSubAgent";
import { lookupExcelFile } from "./excelFileRegistry";
import { adaptFromTool } from "../../utils/shared/pluginCtl";
import { configSchematics } from "../../config";

export function buildExcelQueryTool(ctl: any) {
    return tool({
        name: "query_excel_data",
        // description:
        //     `Use this tool when the user's question requires looking up data, numbers, rows, or aggregates ` +
        //     `from an uploaded spreadsheet (e.g. "how many X", "what's the fastest Y", "compare A and B", ` +
        //     `"is there missing data"). Requires the exact fileName of the spreadsheet — check the ` +
        //     `conversation for an attached-files note if you're unsure of the name. ` +
        //     `If the user's question refers back to something ambiguous — "that model," "those results," ` +
        //     `"it," etc. — resolve it using the actual conversation history before calling this tool. If you ` +
        //     `are unsure of what they're referring to from prior context, ask the user to clarify rather ` +
        //     `than guessing or defaulting to "all models." ` +
        //     `The 'question' you write is handled by a separate agent that CANNOT see this conversation — ` +
        //     `it only sees the exact text you provide plus the spreadsheet's schema. Rewrite the user's ` +
        //     `question into a fully self-contained one before calling this tool: spell out any relevant ` +
        //     `criteria, goals, or context from the conversation, rather than passing along references or ` +
        //     `implicit context the sub-agent has no way to resolve.`,
        description:
            `Look up data, numbers, rows, or aggregates from an uploaded spreadsheet by exact fileName. ` +
            `If the user references something ambiguous ("that model," "it"), resolve it from conversation ` +
            `history first — ask the user if still unclear, never guess or default to "all". This tool's ` +
            `question is sent to a sub-agent with no view of this conversation: write it fully ` +
            `self-contained, including any relevant criteria or context, not implicit references.`,
        parameters: {
            question: z.string().describe("The user's data question, verbatim or lightly cleaned up."),
            fileName: z.string().describe("The exact filename of the spreadsheet to query, e.g. 'npu_vs_cpu_test.xlsx'."),
        },
        implementation: async ({ question, fileName }: { question: string; fileName: string }, toolCtx) => {
            const file = lookupExcelFile(ctl.workingDirectoryPath, fileName);
            if (!file) {
                return (
                    `Error: no file named "${fileName}" is currently registered for this conversation. ` +
                    `It may not have been processed yet, or the name may be slightly off — ask the user ` +
                    `to confirm the exact filename.`
                );
            }

            const adapted = adaptFromTool(ctl, toolCtx, configSchematics);

            let db;
            try {
                db = await getOrCreateDb(file);
            } catch (e) {
                return `Error loading "${fileName}": ${(e as Error).message}`;
            }

            const schemaSummaries: string[] = [];
            try {
                const tables = await queryAll(db, `SELECT table_name FROM information_schema.tables WHERE table_schema='main'`);
                for (const t of tables) {
                    const cols = await queryAll(db, `PRAGMA table_info("${t.table_name}")`);
                    const colDescriptions = cols.map((c: any) => `${c.name} (${c.type})`).join(", ");

                    const textColsWithNumericTwin = cols
                        .filter((c: any) => cols.some((c2: any) => c2.name === `${c.name}_numeric`))
                        .map((c: any) => c.name);

                    let summary = `table "${t.table_name}": columns [${colDescriptions}]`;
                    if (textColsWithNumericTwin.length > 0) {
                        summary +=
                            `\nWARNING: columns [${textColsWithNumericTwin.join(", ")}] are TEXT containing units ` +
                            `(e.g. "18.42 tokens/s"). Sorting/comparing them as strings gives WRONG results ` +
                            `(e.g. "9.50" sorts above "18.42" alphabetically). ` +
                            `ALWAYS use the corresponding "<column>_numeric" column for ORDER BY, MIN, MAX, or any numeric comparison.`;
                    }
                    schemaSummaries.push(summary);
                }
            } catch (e) {
                return `Error reading schema for "${fileName}": ${(e as Error).message}`;
            }

            console.log(schemaSummaries.join("\n"))
            const { trace, answer } = await runExcelSubAgent(adapted, {
                question,
                targetFiles: [file],
                schemaSummary: schemaSummaries.join("\n"),
            });

            // Visible to the user only — per docs, status is UI-only unless echoed in the return value.
            if (typeof toolCtx?.status === "function") {
                toolCtx.status(trace);
            }

            // This is what the model actually sees.
            return answer;
        },
    });
}