/**
 * pi-db — Local SQLite Database
 * Cross-platform. Stores data in ~/.pi/db/
 * Uses JSON file as a lightweight SQL-like store (no native SQLite dependency)
 *
 * /db tables              → list tables
 * /db create <name> <cols> → create table
 * /db insert <table> <json> → insert row
 * /db query <table> [where] → query rows
 * /db drop <table>        → drop table
 * /db export <table>      → export as CSV
 */
import type { ExtensionAPI } from "@mariozechner/pi-coding-agent";
import { Type } from "@sinclair/typebox";
import { existsSync, readFileSync, writeFileSync, mkdirSync, readdirSync, unlinkSync } from "node:fs";
import { join } from "node:path";
import { homedir } from "node:os";

const DIR = join(homedir(), ".pi", "db");
const RST = "\x1b[0m", B = "\x1b[1m", D = "\x1b[2m";
const G = "\x1b[32m", R = "\x1b[31m", Y = "\x1b[33m", C = "\x1b[36m";

interface Table { name: string; columns: string[]; rows: Record<string, any>[]; created: string; }

function ensureDir() { if (!existsSync(DIR)) mkdirSync(DIR, { recursive: true }); }

function tablePath(name: string) { return join(DIR, `${name}.json`); }

function loadTable(name: string): Table | null {
  const p = tablePath(name);
  if (!existsSync(p)) return null;
  try { return JSON.parse(readFileSync(p, "utf-8")); } catch { return null; }
}

function saveTable(t: Table) { ensureDir(); writeFileSync(tablePath(t.name), JSON.stringify(t, null, 2)); }

function listTables(): string[] {
  ensureDir();
  return readdirSync(DIR).filter(f => f.endsWith(".json")).map(f => f.replace(".json", "")).sort();
}

function matchWhere(row: Record<string, any>, where: string): boolean {
  if (!where) return true;
  // Simple where: "col=val", "col>val", "col<val", "col!=val", "col~val" (contains)
  const conditions = where.split(/\s*&&\s*/);
  for (const cond of conditions) {
    const m = cond.match(/^(\w+)\s*(!=|>=|<=|=|>|<|~)\s*(.+)$/);
    if (!m) continue;
    const [, col, op, val] = m;
    const rv = row[col];
    const numV = Number(val), numR = Number(rv);
    switch (op) {
      case "=": if (String(rv) !== val) return false; break;
      case "!=": if (String(rv) === val) return false; break;
      case ">": if (numR <= numV) return false; break;
      case "<": if (numR >= numV) return false; break;
      case ">=": if (numR < numV) return false; break;
      case "<=": if (numR > numV) return false; break;
      case "~": if (!String(rv).toLowerCase().includes(val.toLowerCase())) return false; break;
    }
  }
  return true;
}

export default function (pi: ExtensionAPI) {
  ensureDir();

  pi.registerCommand("db", {
    description: "Database: /db tables|create|insert|query|drop|export|count",
    handler: async (args, ctx) => {
      const parts = (args || "").trim().match(/(?:[^\s"]+|"[^"]*")+/g) || [];
      const cmd = (parts[0] || "tables").toLowerCase();

      if (cmd === "tables") {
        const tables = listTables();
        if (!tables.length) return `${Y}No tables.${RST} Use /db create <name> col1,col2,...`;
        let out = `${B}${C}📋 Tables${RST}\n\n`;
        for (const name of tables) {
          const t = loadTable(name);
          if (!t) continue;
          out += `  ${B}${name}${RST} — ${t.rows.length} rows, ${t.columns.length} columns\n`;
          out += `    ${D}(${t.columns.join(", ")})${RST}\n`;
        }
        return out;
      }

      if (cmd === "create") {
        const name = parts[1];
        const cols = (parts[2] || "").replace(/"/g, "");
        if (!name || !cols) return `${Y}Usage:${RST} /db create <name> col1,col2,col3`;
        if (loadTable(name)) return `${R}Table already exists:${RST} ${name}`;
        const table: Table = { name, columns: cols.split(",").map(c => c.trim()), rows: [], created: new Date().toISOString() };
        saveTable(table);
        return `${G}✅ Created table ${name}${RST} (${table.columns.join(", ")})`;
      }

      if (cmd === "insert") {
        const name = parts[1];
        const jsonStr = parts.slice(2).join(" ").replace(/^"|"$/g, "");
        if (!name || !jsonStr) return `${Y}Usage:${RST} /db insert <table> {"col":"val",...}`;
        const t = loadTable(name);
        if (!t) return `${R}Table not found:${RST} ${name}`;
        try {
          const row = JSON.parse(jsonStr);
          row._id = t.rows.length + 1;
          t.rows.push(row);
          saveTable(t);
          return `${G}✅ Inserted row ${row._id} into ${name}${RST}`;
        } catch (e: any) { return `${R}Invalid JSON:${RST} ${e.message}`; }
      }

      if (cmd === "query") {
        const name = parts[1];
        const where = parts.slice(2).join(" ").replace(/"/g, "");
        if (!name) return `${Y}Usage:${RST} /db query <table> [where]\n  Where: col=val, col>10, col~text, col!=x, combine with &&`;
        const t = loadTable(name);
        if (!t) return `${R}Table not found:${RST} ${name}`;
        const results = t.rows.filter(r => matchWhere(r, where));
        if (!results.length) return `${Y}No matching rows.${RST}`;
        const cols = t.columns;
        const widths = cols.map(c => Math.min(25, Math.max(c.length, ...results.slice(0, 50).map(r => String(r[c] ?? "").length))));
        let out = `${B}${C}${name}${RST} — ${results.length} rows\n\n`;
        out += `  ${cols.map((c, i) => c.padEnd(widths[i])).join(" │ ")}\n`;
        out += `  ${widths.map(w => "─".repeat(w)).join("─┼─")}\n`;
        for (const r of results.slice(0, 100)) {
          out += `  ${cols.map((c, i) => String(r[c] ?? "").slice(0, widths[i]).padEnd(widths[i])).join(" │ ")}\n`;
        }
        if (results.length > 100) out += `  ${D}... ${results.length - 100} more rows${RST}\n`;
        return out;
      }

      if (cmd === "count") {
        const name = parts[1];
        if (!name) return `${Y}Usage:${RST} /db count <table>`;
        const t = loadTable(name);
        if (!t) return `${R}Table not found:${RST} ${name}`;
        return `${B}${name}:${RST} ${t.rows.length} rows`;
      }

      if (cmd === "drop") {
        const name = parts[1];
        if (!name) return `${Y}Usage:${RST} /db drop <table>`;
        const p = tablePath(name);
        if (!existsSync(p)) return `${R}Table not found:${RST} ${name}`;
        unlinkSync(p);
        return `${G}Dropped table ${name}.${RST}`;
      }

      if (cmd === "export") {
        const name = parts[1];
        if (!name) return `${Y}Usage:${RST} /db export <table>`;
        const t = loadTable(name);
        if (!t) return `${R}Table not found:${RST} ${name}`;
        let csv = t.columns.join(",") + "\n";
        for (const r of t.rows) {
          csv += t.columns.map(c => `"${String(r[c] ?? "").replace(/"/g, '""')}"`).join(",") + "\n";
        }
        return csv;
      }

      return `${B}${C}📋 Database${RST}\n  /db tables | create <n> cols | insert <n> {json}\n  /db query <n> [where] | count <n> | drop <n> | export <n>`;
    }
  });

  pi.registerTool({
    name: "db_query",
    description: "Query a local database table. Where syntax: col=val, col>10, col~text, combine with &&.",
    parameters: Type.Object({
      table: Type.String({ description: "Table name" }),
      where: Type.Optional(Type.String({ description: "Where clause: col=val && col2>10" })),
      limit: Type.Optional(Type.Number({ description: "Max rows (default: 100)" })),
    }),
    execute: async (p) => {
      const t = loadTable(p.table);
      if (!t) return `Table not found: ${p.table}`;
      const results = t.rows.filter(r => matchWhere(r, p.where || "")).slice(0, p.limit || 100);
      return JSON.stringify({ table: p.table, count: results.length, rows: results }, null, 2);
    }
  });

  pi.registerTool({
    name: "db_schema",
    description: "Get table schema: columns, row count, sample data.",
    parameters: Type.Object({ table: Type.Optional(Type.String({ description: "Table name (omit for all)" })) }),
    execute: async (p) => {
      if (p.table) {
        const t = loadTable(p.table);
        if (!t) return `Table not found: ${p.table}`;
        return JSON.stringify({ name: t.name, columns: t.columns, rows: t.rows.length, sample: t.rows.slice(0, 3) }, null, 2);
      }
      return JSON.stringify(listTables().map(name => {
        const t = loadTable(name);
        return t ? { name, columns: t.columns, rows: t.rows.length } : { name, error: "corrupt" };
      }), null, 2);
    }
  });

  pi.registerTool({
    name: "db_insert",
    description: "Insert a row into a table.",
    parameters: Type.Object({
      table: Type.String({ description: "Table name" }),
      data: Type.String({ description: "JSON object with column values" }),
    }),
    execute: async (p) => {
      const t = loadTable(p.table);
      if (!t) return `Table not found: ${p.table}`;
      const row = JSON.parse(p.data);
      row._id = t.rows.length + 1;
      t.rows.push(row);
      saveTable(t);
      return `Inserted row ${row._id} into ${p.table}`;
    }
  });
}