
import { createClient, type Client } from '@libsql/client';
import NativeDatabase from 'libsql';
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath, pathToFileURL } from 'node:url';
import { drizzle, type LibSQLDatabase } from 'drizzle-orm/libsql';
import { DATA_ROOT } from '../lib/env.js';
import { logger as pinoLogger } from '../lib/logger.js';
import * as schema from './schema.js';

export type ContextDb = LibSQLDatabase<typeof schema>;
export type SqliteTransaction = <Args extends unknown[], Result>(
  fn: (...args: Args) => Result,
) => (...args: Args) => Result;
export type RawSqlite = NativeDatabase.Database & {
  transaction: SqliteTransaction;
  query: NativeDatabase.Database['prepare'];
};

let _sqlite: RawSqlite | null = null;
let _client: Client | null = null;
let _db: ContextDb | null = null;

const transactionStates = new WeakMap<NativeDatabase.Database, { depth: number; nextSavepointId: number }>();

function getTransactionState(sqlite: NativeDatabase.Database): { depth: number; nextSavepointId: number } {
  let state = transactionStates.get(sqlite);
  if (!state) {
    state = { depth: 0, nextSavepointId: 0 };
    transactionStates.set(sqlite, state);
  }
  return state;
}

function sqliteIsInTransaction(sqlite: NativeDatabase.Database): boolean {
  try {
    return sqlite.inTransaction;
  } catch {
    return false;
  }
}

function createTransaction(sqlite: NativeDatabase.Database): SqliteTransaction {
  return function transaction<Args extends unknown[], Result>(fn: (...args: Args) => Result) {
    return (...args: Args): Result => {
      const state = getTransactionState(sqlite);
      const useSavepoint = state.depth > 0 || sqliteIsInTransaction(sqlite);

      if (useSavepoint) {
        const savepoint = `Synax_tx_${state.nextSavepointId++}`;
        sqlite.exec(`SAVEPOINT ${savepoint}`);
        state.depth++;
        try {
          const result = fn(...args);
          sqlite.exec(`RELEASE SAVEPOINT ${savepoint}`);
          return result;
        } catch (err) {
          try {
            sqlite.exec(`ROLLBACK TO SAVEPOINT ${savepoint}`);
          } finally {
            sqlite.exec(`RELEASE SAVEPOINT ${savepoint}`);
          }
          throw err;
        } finally {
          state.depth--;
        }
      }

      sqlite.exec('BEGIN');
      state.depth++;
      try {
        const result = fn(...args);
        sqlite.exec('COMMIT');
        return result;
      } catch (err) {
        sqlite.exec('ROLLBACK');
        throw err;
      } finally {
        state.depth--;
      }
    };
  };
}

function installSqliteCompat(sqlite: NativeDatabase.Database): RawSqlite {
  const compat = sqlite as RawSqlite;
  Object.defineProperty(compat, 'transaction', {
    configurable: true,
    value: createTransaction(sqlite),
  });
  if (typeof compat.query !== 'function') {
    Object.defineProperty(compat, 'query', {
      configurable: true,
      value: sqlite.prepare.bind(sqlite),
    });
  }
  return compat;
}

function resolveDbPath(): string {
  const dir = path.isAbsolute(DATA_ROOT)
    ? DATA_ROOT
    : path.resolve(process.cwd(), DATA_ROOT);
  if (!fs.existsSync(dir)) {
    fs.mkdirSync(dir, { recursive: true });
  }
  return path.join(dir, 'context.db');
}

function resolveMigrationsDir(): string {
  // 兼容直接运行源码、bundled sidecar 与 packaged resources 路径
  try {
    const here = path.dirname(fileURLToPath(import.meta.url));
    const candidate = path.join(here, 'migrations');
    if (fs.existsSync(candidate)) return candidate;
  } catch {
    /* noop */
  }
  const cwdCandidates = [
    path.resolve(process.cwd(), 'migrations'),
    path.resolve(process.cwd(), 'api/db/migrations'),
  ];
  return cwdCandidates.find((candidate) => fs.existsSync(candidate)) ?? cwdCandidates[1];
}

function hasExecutableSql(sql: string): boolean {
  return sql
    .replace(/--.*$/gm, '')
    .trim()
    .length > 0;
}

function configureSqlite(sqlite: NativeDatabase.Database): void {
  sqlite.exec(`
    PRAGMA journal_mode = WAL;
    PRAGMA synchronous = NORMAL;
    PRAGMA foreign_keys = ON;
    PRAGMA busy_timeout = 5000;
  `);
}

function runMigrations(sqlite: NativeDatabase.Database): void {
  const dir = resolveMigrationsDir();
  if (!fs.existsSync(dir)) {
    pinoLogger.warn({ dir }, 'context db: migrations directory missing');
    return;
  }
  const files = fs
    .readdirSync(dir)
    .filter((f) => f.endsWith('.sql'))
    .sort();
  for (const f of files) {
    const sql = fs.readFileSync(path.join(dir, f), 'utf8');
    if (!hasExecutableSql(sql)) {
      pinoLogger.info({ file: f }, 'context db: migration skipped (no-op)');
      continue;
    }
    try {
      sqlite.exec(sql);
      pinoLogger.info({ file: f }, 'context db: migration applied');
    } catch (err) {
      pinoLogger.error({ file: f, err }, 'context db: migration failed');
      throw err;
    }
  }
}

function ensureColumn(sqlite: NativeDatabase.Database, table: string, column: string, ddl: string): void {
  const tableExists = sqlite
    .prepare("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?")
    .get(table) as { name: string } | undefined;
  if (!tableExists) return;
  const columns = sqlite.prepare(`PRAGMA table_info(${table})`).all() as unknown as Array<{ name: string }>;
  if (columns.some((item) => item.name === column)) return;
  sqlite.exec(`ALTER TABLE ${table} ADD COLUMN ${ddl}`);
  pinoLogger.info({ table, column }, 'context db: column added');
}

function ensureRuntimeSchema(sqlite: NativeDatabase.Database): void {
  ensureColumn(sqlite, 'agent_runtime_sessions', 'active_run_id', 'active_run_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_sessions', 'pending_resume_token', 'pending_resume_token TEXT');
  ensureColumn(sqlite, 'agent_runtime_sessions', 'title', 'title TEXT');
  ensureColumn(sqlite, 'agent_runtime_sessions', 'session_metadata_json', 'session_metadata_json TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'project_id', "project_id TEXT NOT NULL DEFAULT ''");
  ensureColumn(sqlite, 'agent_runtime_messages', 'sequence', 'sequence INTEGER NOT NULL DEFAULT 0');
  ensureColumn(sqlite, 'agent_runtime_messages', 'turn_id', 'turn_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'run_id', 'run_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'step_id', 'step_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'provider_id', 'provider_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'model_id', 'model_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'tool_call_id', 'tool_call_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_messages', 'usage_json', "usage_json TEXT NOT NULL DEFAULT '{}'");
  ensureColumn(sqlite, 'agent_runtime_messages', 'metadata_json', "metadata_json TEXT NOT NULL DEFAULT '{}'");
  ensureColumn(sqlite, 'agent_runtime_events', 'payload_json', "payload_json TEXT NOT NULL DEFAULT '{}'");
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'run_id', 'run_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'step_id', 'step_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'model_tool_call_id', 'model_tool_call_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'mutability', "mutability TEXT NOT NULL DEFAULT 'read'");
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'args_hash', "args_hash TEXT NOT NULL DEFAULT ''");
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'input_ref_json', 'input_ref_json TEXT');
  ensureColumn(sqlite, 'agent_runtime_tool_calls', 'output_ref_json', 'output_ref_json TEXT');
  ensureColumn(sqlite, 'agent_runtime_permissions', 'run_id', 'run_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_permissions', 'step_id', 'step_id TEXT');
  ensureColumn(sqlite, 'agent_runtime_permissions', 'patterns_json', "patterns_json TEXT NOT NULL DEFAULT '[]'");
  ensureColumn(sqlite, 'agent_runtime_permissions', 'user_reply', 'user_reply TEXT');
  ensureColumn(sqlite, 'agent_runtime_permissions', 'resume_token', 'resume_token TEXT');
  ensureColumn(sqlite, 'agent_runtime_permissions', 'metadata_json', "metadata_json TEXT NOT NULL DEFAULT '{}'");
  ensureColumn(sqlite, 'agent_runtime_artifacts', 'metadata_json', "metadata_json TEXT NOT NULL DEFAULT '{}'");
  ensureColumn(sqlite, 'agent_runtime_context_bundles', 'blocks_json', "blocks_json TEXT NOT NULL DEFAULT '[]'");
  ensureColumn(sqlite, 'agent_runtime_thinking_summaries', 'evidence_used_json', "evidence_used_json TEXT NOT NULL DEFAULT '[]'");
  ensureColumn(sqlite, 'wiki_source_bindings', 'file_path', 'file_path TEXT');
  ensureColumn(sqlite, 'wiki_source_bindings', 'start_line', 'start_line INTEGER');
  ensureColumn(sqlite, 'wiki_source_bindings', 'end_line', 'end_line INTEGER');
  ensureColumn(sqlite, 'wiki_source_bindings', 'qualified_name', 'qualified_name TEXT');
  ensureColumn(sqlite, 'wiki_refresh_tasks', 'draft_ids_json', "draft_ids_json TEXT NOT NULL DEFAULT '[]'");
  ensureColumn(sqlite, 'wiki_refresh_tasks', 'affected_document_ids_json', "affected_document_ids_json TEXT NOT NULL DEFAULT '[]'");
  ensureColumn(sqlite, 'wiki_block_revisions', 'draft_id', 'draft_id TEXT');
  ensureColumn(sqlite, 'wiki_documents', 'pipeline_stage', "pipeline_stage TEXT NOT NULL DEFAULT 'pending'");
  ensureColumn(sqlite, 'wiki_blocks', 'search_text', "search_text TEXT NOT NULL DEFAULT ''");
}

function getOrCreateRawSqlite(dbPath = resolveDbPath()): RawSqlite {
  if (_sqlite) return _sqlite;
  const sqlite = installSqliteCompat(new NativeDatabase(dbPath));
  configureSqlite(sqlite);

  runMigrations(sqlite);
  ensureRuntimeSchema(sqlite);

  _sqlite = sqlite;
  pinoLogger.info({ dbPath }, 'context db: ready');
  return _sqlite;
}

function getOrCreateClient(dbPath = resolveDbPath()): Client {
  if (_client) return _client;
  _client = createClient({ url: pathToFileURL(dbPath).href });
  return _client;
}

export function getDb(): ContextDb {
  if (_db) return _db;

  const dbPath = resolveDbPath();
  getOrCreateRawSqlite(dbPath);
  const client = getOrCreateClient(dbPath);
  _db = drizzle({ client, schema });
  return _db;
}

export function getRawSqlite(): RawSqlite {
  return getOrCreateRawSqlite();
}

export function closeDb(): void {
  if (_client) {
    _client.close();
  }
  if (_sqlite) {
    try {
      _sqlite.close();
    } catch {
      /* noop */
    }
  }
  _client = null;
  _sqlite = null;
  _db = null;
}

export { schema };
