/**
 * Database client singleton for Agent storage.
 *
 * Design principles:
 * - Lazy initialization - only connect when first accessed
 * - Singleton pattern - single connection throughout the app lifecycle
 * - Auto-create tables on first run (no migration tool needed)
 * - Configurable path via environment variable
 */
import { drizzle, BetterSQLite3Database } from 'drizzle-orm/better-sqlite3';
import { sql } from 'drizzle-orm';
import * as schema from './schema';
import { getAgentDataDir } from '../storage';
import path from 'node:path';
import { existsSync, mkdirSync } from 'node:fs';
import { createRequire } from 'node:module';
import type BetterSqlite3 from 'better-sqlite3';

// ============================================================
// Types
// ============================================================

export type DrizzleDB = BetterSQLite3Database<typeof schema>;

type BetterSqlite3Ctor = typeof import('better-sqlite3');
const runtimeRequire =
  typeof require === 'function' ? require : createRequire(typeof __filename !== 'undefined' ? __filename : process.cwd());

function loadBetterSqlite3(): BetterSqlite3Ctor {
  try {
    const mod = runtimeRequire('better-sqlite3') as { default?: BetterSqlite3Ctor } | BetterSqlite3Ctor;
    return ((mod as { default?: BetterSqlite3Ctor }).default || mod) as BetterSqlite3Ctor;
  } catch (error) {
    const platform = `${process.platform}-${process.arch}`;
    const raw = error instanceof Error ? error.message : String(error);
    throw new Error(
      `Failed to load better-sqlite3 native bindings on ${platform} with Node ${process.version}. ` +
        `Run: pnpm --filter webpage-mcp rebuild better-sqlite3 ` +
        `or reinstall dependencies with: pnpm install --force. Original error: ${raw}`,
    );
  }
}

// ============================================================
// Singleton State
// ============================================================

let dbInstance: DrizzleDB | null = null;
let sqliteInstance: BetterSqlite3.Database | null = null;

// ============================================================
// Database Path Resolution
// ============================================================

/**
 * Get the database file path.
 * Environment: WEBPAGE_MCP_AGENT_DB_FILE overrides the default path.
 */
export function getDatabasePath(): string {
  const envPath = process.env.WEBPAGE_MCP_AGENT_DB_FILE;
  if (envPath && envPath.trim()) {
    return path.resolve(envPath.trim());
  }
  return path.join(getAgentDataDir(), 'agent.db');
}

// ============================================================
// Schema Initialization SQL
// ============================================================

const CREATE_TABLES_SQL = `
-- Projects table
CREATE TABLE IF NOT EXISTS projects (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  root_path TEXT NOT NULL,
  preferred_cli TEXT,
  selected_model TEXT,
  active_claude_session_id TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  last_active_at TEXT
);

CREATE INDEX IF NOT EXISTS projects_last_active_idx ON projects(last_active_at);

-- Sessions table
CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  engine_name TEXT NOT NULL,
  engine_session_id TEXT,
  name TEXT,
  model TEXT,
  permission_mode TEXT NOT NULL DEFAULT 'bypassPermissions',
  allow_dangerously_skip_permissions TEXT,
  system_prompt_config TEXT,
  options_config TEXT,
  management_info TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS sessions_project_id_idx ON sessions(project_id);
CREATE INDEX IF NOT EXISTS sessions_engine_name_idx ON sessions(engine_name);

-- Messages table
CREATE TABLE IF NOT EXISTS messages (
  id TEXT PRIMARY KEY,
  project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  session_id TEXT NOT NULL,
  conversation_id TEXT,
  role TEXT NOT NULL,
  content TEXT NOT NULL,
  message_type TEXT NOT NULL,
  metadata TEXT,
  cli_source TEXT,
  request_id TEXT,
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS messages_project_id_idx ON messages(project_id);
CREATE INDEX IF NOT EXISTS messages_session_id_idx ON messages(session_id);
CREATE INDEX IF NOT EXISTS messages_created_at_idx ON messages(created_at);
CREATE INDEX IF NOT EXISTS messages_request_id_idx ON messages(request_id);

-- Enable foreign key enforcement
PRAGMA foreign_keys = ON;
`;

/**
 * Migration SQL to add new columns to existing databases.
 * Each migration is idempotent - safe to run multiple times.
 */
const MIGRATION_SQL = `
-- Add active_claude_session_id column if it doesn't exist (for existing databases)
-- SQLite doesn't support IF NOT EXISTS for columns, so we use a workaround
`;

// ============================================================
// Database Initialization
// ============================================================

/**
 * Check if a column exists in a table.
 */
function columnExists(sqlite: BetterSqlite3.Database, tableName: string, columnName: string): boolean {
  const result = sqlite.prepare(`PRAGMA table_info(${tableName})`).all() as Array<{ name: string }>;
  return result.some((col) => col.name === columnName);
}

/**
 * Run migrations for existing databases.
 * Adds new columns that may be missing in older database versions.
 */
function runMigrations(sqlite: BetterSqlite3.Database): void {
  // Migration 1: Add active_claude_session_id column to projects table
  if (!columnExists(sqlite, 'projects', 'active_claude_session_id')) {
    sqlite.exec('ALTER TABLE projects ADD COLUMN active_claude_session_id TEXT');
  }

  // Migration 2: Add enable_chrome_mcp column to projects table (default enabled)
  if (!columnExists(sqlite, 'projects', 'enable_chrome_mcp')) {
    sqlite.exec("ALTER TABLE projects ADD COLUMN enable_chrome_mcp TEXT NOT NULL DEFAULT '1'");
  }
}

/**
 * Initialize the database schema.
 * Safe to call multiple times - uses IF NOT EXISTS.
 * Also runs migrations for existing databases.
 */
function initializeSchema(sqlite: BetterSqlite3.Database): void {
  sqlite.exec(CREATE_TABLES_SQL);
  runMigrations(sqlite);
}

/**
 * Ensure the data directory exists.
 */
function ensureDataDir(): void {
  const dataDir = getAgentDataDir();
  if (!existsSync(dataDir)) {
    mkdirSync(dataDir, { recursive: true });
  }
}

// ============================================================
// Public API
// ============================================================

/**
 * Get the Drizzle database instance.
 * Lazily initializes the connection and schema on first call.
 */
export function getDb(): DrizzleDB {
  if (dbInstance) {
    return dbInstance;
  }

  ensureDataDir();
  const dbPath = getDatabasePath();

  // Create SQLite connection
  const BetterSqlite3 = loadBetterSqlite3();
  const sqlite = new BetterSqlite3(dbPath);
  sqliteInstance = sqlite;

  // Enable WAL mode for better concurrent read performance
  sqlite.pragma('journal_mode = WAL');

  // Initialize schema
  initializeSchema(sqlite);

  // Create Drizzle instance
  dbInstance = drizzle(sqlite, { schema });

  return dbInstance;
}

/**
 * Close the database connection.
 * Should be called on graceful shutdown.
 */
export function closeDb(): void {
  if (sqliteInstance) {
    sqliteInstance.close();
    sqliteInstance = null;
    dbInstance = null;
  }
}

/**
 * Check if database is initialized.
 */
export function isDbInitialized(): boolean {
  return dbInstance !== null;
}

/**
 * Execute raw SQL (for advanced use cases).
 */
export function execRawSql(sqlStr: string): void {
  if (!sqliteInstance) {
    getDb(); // Initialize if not already
  }
  sqliteInstance!.exec(sqlStr);
}
