import type { Database } from 'bun:sqlite'
import { readFileSync, existsSync, unlinkSync } from 'node:fs'
import { resolve, dirname } from 'node:path'
import { fileURLToPath } from 'node:url'
import { tmpdir } from 'node:os'

const __dirname = dirname(fileURLToPath(import.meta.url))

// Cross-runtime SQLite resolution: bun:sqlite → node:sqlite → libsql.
// Static `import { Database } from 'bun:sqlite'` is Bun-only and throws
// ReferenceError under Node.js (slz bin → dist/cli-node.js). RCS requires
// a working DB — throws a clear error if no backend is available.
type DatabaseCtor = new (path: string) => Database

const ResolvedDatabase: DatabaseCtor = (() => {
  // Tier 1: bun:sqlite (Bun runtime)
  try {
    const mod = require('bun:sqlite')
    if (mod?.Database) return mod.Database as DatabaseCtor
  } catch {
    /* not Bun */
  }

  // Tier 2: node:sqlite (Node >= 22.5; --experimental-sqlite on 22.5-22.x)
  try {
    const { DatabaseSync } = require('node:sqlite')
    if (DatabaseSync) {
      // node:sqlite has prepare()/exec() but not query() or transaction() —
      // add bun:sqlite compat for both. transaction() is implemented via
      // manual BEGIN/COMMIT/ROLLBACK because DatabaseSync has no native
      // transaction support. Without this, /web/auth/join (the only caller
      // of db.transaction()) throws TypeError under Node.js runtime.
      return class NodeSqliteCompat extends DatabaseSync {
        query(sql: string) {
          const stmt = this.prepare(sql)
          return {
            get: (...params: unknown[]) => stmt.get(...params),
            all: (...params: unknown[]) => stmt.all(...params),
            run: (...params: unknown[]) => stmt.run(...params),
          }
        }
        transaction<TArgs extends unknown[], TResult>(
          fn: (...args: TArgs) => TResult,
        ): (...args: TArgs) => TResult {
          return (...args: TArgs) => {
            this.exec('BEGIN')
            try {
              const result = fn(...args)
              this.exec('COMMIT')
              return result
            } catch (err) {
              try {
                this.exec('ROLLBACK')
              } catch {
                // ROLLBACK may fail if the connection is already broken;
                // the original error is more important to surface.
              }
              throw err
            }
          }
        }
      } as unknown as DatabaseCtor
    }
  } catch {
    /* Node < 22.5 or flag not set */
  }

  // Tier 3: libsql (optionalDependency — covers Node 20/21)
  // libsql 0.5.x exports the Database constructor directly (better-sqlite3
  // compatible API): `require('libsql')` returns a function, not an object
  // with a `.Database` property. Check `typeof === 'function'` and call
  // `new libsql(path)` directly.
  //
  // Parameter format compatibility: bun:sqlite and node:sqlite both accept
  // `$name` named params, but libsql (better-sqlite3 lineage) only accepts
  // `:name` and `@name` — `$name` silently fails to bind, causing
  // "NOT NULL constraint failed" errors. Convert `$name` → `:name` in SQL
  // and `{$name: v}` → `{name: v}` in param objects so RCS queries work
  // unchanged across all three backends.
  try {
    const libsql = require('libsql')
    if (typeof libsql === 'function') {
      const convertSql = (sql: string): string => sql.replace(/\$(\w+)/g, ':$1')
      const convertParams = (params: unknown[]): unknown[] =>
        params.map(p => {
          if (p && typeof p === 'object' && !Array.isArray(p)) {
            const obj = p as Record<string, unknown>
            const out: Record<string, unknown> = {}
            for (const k of Object.keys(obj)) {
              out[k.startsWith('$') ? k.slice(1) : k] = obj[k]
            }
            return out
          }
          return p
        })
      return class LibsqlCompat {
        private _db: {
          prepare(sql: string): {
            get(...p: unknown[]): unknown
            all(...p: unknown[]): unknown[]
            run(...p: unknown[]): { changes: number }
          }
          exec(sql: string): void
          close(): void
          transaction<TArgs extends unknown[], TResult>(
            fn: (...args: TArgs) => TResult,
          ): (...args: TArgs) => TResult
        }
        constructor(path: string) {
          this._db = new libsql(path)
        }
        exec(sql: string): void {
          this._db.exec(sql)
        }
        query(sql: string) {
          const stmt = this._db.prepare(convertSql(sql))
          return {
            get: (...params: unknown[]) => stmt.get(...convertParams(params)),
            all: (...params: unknown[]) => stmt.all(...convertParams(params)),
            run: (...params: unknown[]) => stmt.run(...convertParams(params)),
          }
        }
        // libsql (better-sqlite3 lineage) has transaction() natively with
        // the same API as bun:sqlite — delegate to it. Without this, the
        // /web/auth/join flow throws TypeError under Node + libsql backend.
        transaction<TArgs extends unknown[], TResult>(
          fn: (...args: TArgs) => TResult,
        ): (...args: TArgs) => TResult {
          return this._db.transaction(fn)
        }
        close(): void {
          this._db.close()
        }
      } as unknown as DatabaseCtor
    }
  } catch {
    /* libsql not installed */
  }

  throw new Error(
    '[RCS] No SQLite backend available. ' +
      'Install libsql (npm i libsql), use Node >= 22.5, or run under Bun.',
  )
})()

let _db: Database | null = null
let _dbPath: string | null = null

/**
 * Run all schema migrations on the given database.
 * Idempotent — uses CREATE TABLE IF NOT EXISTS for new tables, plus
 * ALTER TABLE ADD COLUMN for columns added to existing tables (gated by
 * PRAGMA table_info so re-running on an already-migrated DB is a no-op).
 * Reads rcs-schema.sql and executes as a single db.exec() call,
 * which correctly handles semicolons inside CHECK constraints.
 *
 * File is named rcs-schema.sql (not schema.sql) to avoid collision with
 * CodeGraph's schema.sql at the same dist/ path — both modules resolve
 * their schema via __dirname, and the bundler places them in the same
 * chunk directory.
 */
export function migrateDatabase(db: Database): void {
  const schemaPath = resolve(__dirname, 'rcs-schema.sql')
  const schema = readFileSync(schemaPath, 'utf8')

  db.exec('PRAGMA foreign_keys = ON')
  // Run ALTER TABLE migrations BEFORE db.exec(schema) — rcs-schema.sql's
  // CREATE INDEX statements reference the new columns, so the columns
  // must exist before the indexes can be created. CREATE TABLE IF NOT
  // EXISTS skips existing tables (so the ALTER adds the missing columns
  // to pre-existing tables); on a fresh DB the ALTER is a no-op because
  // the table doesn't exist yet, then db.exec(schema) creates it with
  // all columns.
  migrateEnvironmentsColumns(db)
  db.exec(schema)
}

/**
 * Add columns to pre-existing `environments` tables that were created
 * before owner_user_id / claim_token / claim_expires_at existed.
 * CREATE TABLE IF NOT EXISTS skips existing tables entirely, so without
 * this the new columns would never appear on upgraded DBs. Must run
 * BEFORE db.exec(schema) because rcs-schema.sql's CREATE INDEX statements
 * reference the new columns. On a fresh DB (table doesn't exist yet),
 * skip entirely — db.exec(schema) will create the table with all columns.
 */
function migrateEnvironmentsColumns(db: Database): void {
  // Check if environments table exists at all. On a fresh DB it won't,
  // and ALTER TABLE on a non-existent table throws. PRAGMA table_info
  // returns empty for non-existent tables, but ALTER still errors — so
  // gate on table existence via sqlite_master.
  const tableExists = db
    .query(
      "SELECT 1 FROM sqlite_master WHERE type='table' AND name='environments'",
    )
    .get() as { 1: number } | undefined
  if (!tableExists) return

  const cols = db.query('PRAGMA table_info(environments)').all() as Array<{
    name: string
  }>
  const names = new Set(cols.map(c => c.name))
  const addIfMissing = (name: string, defn: string): void => {
    if (!names.has(name)) {
      db.exec(`ALTER TABLE environments ADD COLUMN ${name} ${defn}`)
    }
  }
  addIfMissing('owner_user_id', 'TEXT')
  addIfMissing('claim_token', 'TEXT')
  addIfMissing('claim_expires_at', 'TEXT')
}

/**
 * Resolve the default database path.
 * - Uses RCS_DB_PATH env var if set
 * - Uses /app/data/rcs.db if the directory exists (production)
 * - Falls back to a temp file otherwise (test/development)
 */
function resolveDefaultPath(): string {
  const configured = process.env.RCS_DB_PATH
  if (configured) return configured

  const defaultDir = '/app/data'
  if (existsSync(defaultDir)) {
    return resolve(defaultDir, 'rcs.db')
  }

  // Use a temp file for test/development environments
  // (supports WAL mode, unlike :memory:)
  return resolve(tmpdir(), 'rcs-dev.db')
}

/**
 * Initialize a database connection at the given path.
 * Enables WAL mode and runs migrations.
 */
export function initDatabase(path?: string): Database {
  const dbPath = path || resolveDefaultPath()
  const db = new ResolvedDatabase(dbPath)
  db.exec('PRAGMA journal_mode = WAL')
  db.exec('PRAGMA foreign_keys = ON')
  migrateDatabase(db)
  _db = db
  _dbPath = dbPath
  return db
}

/**
 * Get the singleton database connection.
 * Initializes on first call.
 */
export function getDb(): Database {
  if (!_db) {
    initDatabase()
  }
  return _db!
}

/**
 * Reset the singleton — for testing only.
 * Closes the connection and removes the file if it was
 * a temp/development file (not :memory:).
 */
export function resetDbSingleton(): void {
  if (_db) {
    try {
      _db.close()
    } catch {
      // already closed
    }
  }
  // Clean up temp file and WAL/SHM companion files
  if (_dbPath && _dbPath !== ':memory:') {
    for (const suffix of ['', '-wal', '-shm']) {
      try {
        if (existsSync(_dbPath + suffix)) {
          unlinkSync(_dbPath + suffix)
        }
      } catch {
        // ignore cleanup errors
      }
    }
  }
  _db = null
  _dbPath = null
}