import * as fs from 'fs';
import * as path from 'path';
import { tmpdir } from 'os';
import Database from 'better-sqlite3';
import { afterEach, describe, expect, it } from 'vitest';
import { executeTool, TOOL_DEFS } from './data.js';
import type { ToolContext } from './core.js';

function makeWorkspace(): string {
  return fs.mkdtempSync(path.join(tmpdir(), 'maestro-data-'));
}

function makeContext(workspacePath: string, editAllowed = false): ToolContext {
  return { workspacePath, editAllowed };
}

/** Create a fixture DB with a users table inside the workspace. */
function createFixtureDb(workspacePath: string, fileName = 'fixture.db'): string {
  const dbPath = path.join(workspacePath, fileName);
  const db = new Database(dbPath);
  db.exec(`
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER);
    INSERT INTO users (name, age) VALUES ('alice', 30), ('bob', 25), ('carol', 35);
  `);
  db.close();
  return fileName;
}

describe('SQLite tool', () => {
  let workspacePath = '';

  afterEach(() => {
    if (workspacePath) {
      fs.rmSync(workspacePath, { recursive: true, force: true });
      workspacePath = '';
    }
  });

  it('exposes only the SQLite tool def', () => {
    expect(Object.keys(TOOL_DEFS)).toEqual(['SQLite']);
    expect(TOOL_DEFS['SQLite']!.function.name).toBe('SQLite');
  });

  it('returns null for unknown tool names', async () => {
    workspacePath = makeWorkspace();
    const result = await executeTool('NotATool', { query: 'SELECT 1' }, makeContext(workspacePath));
    expect(result).toBeNull();
  });

  describe('SELECT happy path', () => {
    it('returns a formatted table for a SELECT in read-only mode', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT name, age FROM users ORDER BY age', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('name');
      expect(result?.output).toContain('age');
      expect(result?.output).toContain('bob');
      expect(result?.output).toContain('alice');
      expect(result?.output).toContain('carol');
      expect(result?.output).toContain('(3 rows)');
    });

    it('formats an empty result set as (0 rows)', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: "SELECT * FROM users WHERE name = 'nobody'", db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toBe('(0 rows)');
    });

    it('uses singular "row" for a single-row result', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: "SELECT name FROM users WHERE name = 'alice'", db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('(1 row)');
    });

    it('renders NULL values as NULL', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);
      const db = new Database(path.join(workspacePath, dbFile));
      db.exec("INSERT INTO users (name, age) VALUES ('dave', NULL)");
      db.close();

      const result = await executeTool(
        'SQLite',
        { query: "SELECT age FROM users WHERE name = 'dave'", db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('NULL');
    });

    it('executes multiple SELECT statements and joins their outputs', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        {
          query: "SELECT name FROM users WHERE name = 'alice'; SELECT name FROM users WHERE name = 'bob';",
          db_path: dbFile,
        },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('alice');
      expect(result?.output).toContain('bob');
    });
  });

  describe('write guards (editAllowed=false)', () => {
    it.each(['INSERT', 'UPDATE', 'DELETE', 'REPLACE'])('rejects %s in read-only mode', async (kw) => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);
      const queries: Record<string, string> = {
        INSERT: "INSERT INTO users (name, age) VALUES ('mallory', 1)",
        UPDATE: "UPDATE users SET age = 99 WHERE name = 'alice'",
        DELETE: 'DELETE FROM users',
        REPLACE: "REPLACE INTO users (id, name, age) VALUES (1, 'evil', 0)",
      };

      const result = await executeTool('SQLite', { query: queries[kw]!, db_path: dbFile }, makeContext(workspacePath, false));

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Only SELECT queries are allowed');
    });

    it('rejects a write smuggled after a SELECT in a multi-statement query', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT * FROM users; DELETE FROM users', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      // Guard fires before anything executes; data must be intact
      const db = new Database(path.join(workspacePath, dbFile), { readonly: true });
      const count = db.prepare('SELECT COUNT(*) AS c FROM users').get() as { c: number };
      db.close();
      expect(count.c).toBe(3);
    });

    it('rejects CREATE TABLE in read-only mode', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'CREATE TABLE extra (id INTEGER)', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Only SELECT queries are allowed');
    });
  });

  describe('writes allowed (editAllowed=true)', () => {
    it('supports CREATE TABLE + INSERT + SELECT in one call', async () => {
      workspacePath = makeWorkspace();

      const result = await executeTool(
        'SQLite',
        {
          query: "CREATE TABLE t (v TEXT); INSERT INTO t (v) VALUES ('x'), ('y'); SELECT COUNT(*) AS c FROM t",
          db_path: 'new.db',
        },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('OK');
      expect(result?.output).toContain('2 row(s) affected');
      expect(result?.output).toContain('2');
      expect(fs.existsSync(path.join(workspacePath, 'new.db'))).toBe(true);
    });

    it('defaults db_path to temp.db inside the workspace', async () => {
      workspacePath = makeWorkspace();

      const result = await executeTool(
        'SQLite',
        { query: 'CREATE TABLE t (v TEXT)' },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(false);
      expect(fs.existsSync(path.join(workspacePath, 'temp.db'))).toBe(true);
    });

    it('reports affected row count for UPDATE', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'UPDATE users SET age = age + 1', db_path: dbFile },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('3 row(s) affected');
    });
  });

  describe('always-blocked DDL', () => {
    it.each([
      ['DROP TABLE users', 'DROP'],
      ['ALTER TABLE users ADD COLUMN extra TEXT', 'ALTER'],
      ["ATTACH DATABASE '/etc/passwd' AS evil", 'ATTACH'],
      ['DETACH DATABASE evil', 'DETACH'],
      ['REINDEX', 'REINDEX'],
      ['VACUUM', 'VACUUM'],
    ])('blocks "%s" even with editAllowed=true', async (query, kw) => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool('SQLite', { query, db_path: dbFile }, makeContext(workspacePath, true));

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Forbidden SQL');
      expect(result?.output).toContain(kw);
    });

    it.each([
      'CREATE INDEX idx ON users (name)',
      'CREATE UNIQUE INDEX idx ON users (name)',
      'CREATE TRIGGER trg AFTER INSERT ON users BEGIN SELECT 1; END',
    ])('blocks "%s"', async (query) => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool('SQLite', { query, db_path: dbFile }, makeContext(workspacePath, true));

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Forbidden SQL');
    });

    it('is case-insensitive for blocked keywords', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'drop table users', db_path: dbFile },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Forbidden SQL');
    });

    it('blocks DDL hidden behind a SELECT in a multi-statement query', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT 1; DROP TABLE users', db_path: dbFile },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Forbidden SQL');
    });
  });

  describe('PRAGMA handling', () => {
    it('allows PRAGMA table_info when edit is enabled', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'PRAGMA table_info(users)', db_path: dbFile },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('name');
      expect(result?.output).toContain('age');
    });

    it('blocks PRAGMA other than table_info/table_list', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'PRAGMA journal_mode = WAL', db_path: dbFile },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('PRAGMA');
      expect(result?.output).toContain('not allowed');
    });

    // Documents current behavior: PRAGMA table_info survives the DDL block list
    // but the read-only gate only whitelists SELECT, so it is rejected when
    // editAllowed=false. Schema inspection therefore requires edit mode.
    it('rejects PRAGMA table_info in read-only mode (current behavior)', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'PRAGMA table_info(users)', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Only SELECT queries are allowed');
    });
  });

  describe('errors', () => {
    it('rejects an empty query', async () => {
      workspacePath = makeWorkspace();

      const result = await executeTool('SQLite', { query: '  ;;  ' }, makeContext(workspacePath, true));

      expect(result?.isError).toBe(true);
      expect(result?.output).toBe('Empty query');
    });

    it('returns a Query error for SQL referencing a missing table', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT * FROM no_such_table', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Query error');
      expect(result?.output).toContain('no_such_table');
    });

    it('returns a syntax error as Query error', async () => {
      workspacePath = makeWorkspace();
      const dbFile = createFixtureDb(workspacePath);

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT FROM WHERE', db_path: dbFile },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Query error');
    });

    it('fails to open a missing DB file in read-only mode', async () => {
      workspacePath = makeWorkspace();

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT 1', db_path: 'does-not-exist.db' },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Failed to open database');
    });

    it('returns a Query error when the file is not a SQLite database', async () => {
      workspacePath = makeWorkspace();
      fs.writeFileSync(path.join(workspacePath, 'not-a-db.db'), 'this is plain text, not sqlite');

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT 1', db_path: 'not-a-db.db' },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(true);
    });
  });

  describe('path traversal guard', () => {
    it.each([
      '../outside.db',
      '../../etc/evil.db',
      'sub/../../escape.db',
    ])('rejects db_path "%s" outside the workspace', async (dbPath) => {
      workspacePath = makeWorkspace();

      const result = await executeTool('SQLite', { query: 'SELECT 1', db_path: dbPath }, makeContext(workspacePath, true));

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Path traversal');
    });

    it('rejects an absolute db_path outside the workspace', async () => {
      workspacePath = makeWorkspace();

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT 1', db_path: '/tmp/evil-absolute.db' },
        makeContext(workspacePath, true),
      );

      expect(result?.isError).toBe(true);
      expect(result?.output).toContain('Path traversal');
    });

    it('allows a relative path inside a workspace subdirectory', async () => {
      workspacePath = makeWorkspace();
      fs.mkdirSync(path.join(workspacePath, 'output'), { recursive: true });
      createFixtureDb(workspacePath, path.join('output', 'nested.db'));

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT COUNT(*) AS c FROM users', db_path: 'output/nested.db' },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('3');
    });
  });

  describe('large result sets', () => {
    // Documents current behavior: the tool has no output truncation, so a
    // large SELECT returns every row verbatim.
    it('returns all rows of a large SELECT (no truncation in the tool itself)', async () => {
      workspacePath = makeWorkspace();
      const dbPath = path.join(workspacePath, 'big.db');
      const db = new Database(dbPath);
      db.exec('CREATE TABLE big (id INTEGER PRIMARY KEY, payload TEXT)');
      const insert = db.prepare('INSERT INTO big (payload) VALUES (?)');
      const tx = db.transaction(() => {
        for (let i = 0; i < 2000; i++) insert.run(`row-payload-${i}-${'x'.repeat(50)}`);
      });
      tx();
      db.close();

      const result = await executeTool(
        'SQLite',
        { query: 'SELECT * FROM big', db_path: 'big.db' },
        makeContext(workspacePath, false),
      );

      expect(result?.isError).toBe(false);
      expect(result?.output).toContain('(2000 rows)');
      expect(result?.output).toContain('row-payload-0-');
      expect(result?.output).toContain('row-payload-1999-');
    });
  });
});
