import { mkdir } from "node:fs/promises";
import { dirname, join } from "node:path";
import { logger, resolveLogDir } from "./logger";
import type { ApiFormat } from "./schemas";
import type { LogIndexEntry, LogIndexSummary } from "./logIndex";

type SqliteState =
  | { status: "ready"; db: unknown; path: string }
  | { status: "unavailable"; path: string; reason: string };

type SqliteStatus = {
  available: boolean;
  path: string;
  reason: string | null;
};

const SQLITE_INDEX_FILE = "inspector.sqlite";
const SQLITE_PACKAGE_NAME = "better-sqlite3";
const BUN_SQLITE_MODULE_NAME = "bun:sqlite";

const CREATE_SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS log_index (
  id INTEGER PRIMARY KEY,
  file TEXT NOT NULL,
  byte_offset INTEGER NOT NULL,
  byte_length INTEGER NOT NULL,
  session_id TEXT,
  model TEXT,
  timestamp TEXT NOT NULL,
  method TEXT NOT NULL,
  path TEXT NOT NULL,
  response_status INTEGER,
  input_tokens INTEGER,
  output_tokens INTEGER,
  cache_creation_input_tokens INTEGER,
  cache_read_input_tokens INTEGER,
  elapsed_ms INTEGER,
  first_chunk_ms INTEGER,
  total_stream_ms INTEGER,
  tokens_per_second REAL,
  streaming INTEGER NOT NULL,
  user_agent TEXT,
  origin TEXT,
  api_format TEXT NOT NULL,
  is_test INTEGER NOT NULL,
  replay_of_log_id INTEGER,
  provider_name TEXT,
  client_port INTEGER,
  client_pid INTEGER,
  client_cwd TEXT,
  client_project_folder TEXT,
  streaming_chunks_path TEXT,
  raw_request_body_bytes INTEGER,
  response_text_bytes INTEGER,
  warnings_json TEXT,
  error TEXT
);
CREATE INDEX IF NOT EXISTS idx_log_index_session_id_id ON log_index(session_id, id);
CREATE INDEX IF NOT EXISTS idx_log_index_model_id ON log_index(model, id);
CREATE INDEX IF NOT EXISTS idx_log_index_timestamp ON log_index(timestamp);
`;

const UPSERT_SQL = `
INSERT INTO log_index (
  id,
  file,
  byte_offset,
  byte_length,
  session_id,
  model,
  timestamp,
  method,
  path,
  response_status,
  input_tokens,
  output_tokens,
  cache_creation_input_tokens,
  cache_read_input_tokens,
  elapsed_ms,
  first_chunk_ms,
  total_stream_ms,
  tokens_per_second,
  streaming,
  user_agent,
  origin,
  api_format,
  is_test,
  replay_of_log_id,
  provider_name,
  client_port,
  client_pid,
  client_cwd,
  client_project_folder,
  streaming_chunks_path,
  raw_request_body_bytes,
  response_text_bytes,
  warnings_json,
  error
) VALUES (
  ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
ON CONFLICT(id) DO UPDATE SET
  file = excluded.file,
  byte_offset = excluded.byte_offset,
  byte_length = excluded.byte_length,
  session_id = excluded.session_id,
  model = excluded.model,
  timestamp = excluded.timestamp,
  method = excluded.method,
  path = excluded.path,
  response_status = excluded.response_status,
  input_tokens = excluded.input_tokens,
  output_tokens = excluded.output_tokens,
  cache_creation_input_tokens = excluded.cache_creation_input_tokens,
  cache_read_input_tokens = excluded.cache_read_input_tokens,
  elapsed_ms = excluded.elapsed_ms,
  first_chunk_ms = excluded.first_chunk_ms,
  total_stream_ms = excluded.total_stream_ms,
  tokens_per_second = excluded.tokens_per_second,
  streaming = excluded.streaming,
  user_agent = excluded.user_agent,
  origin = excluded.origin,
  api_format = excluded.api_format,
  is_test = excluded.is_test,
  replay_of_log_id = excluded.replay_of_log_id,
  provider_name = excluded.provider_name,
  client_port = excluded.client_port,
  client_pid = excluded.client_pid,
  client_cwd = excluded.client_cwd,
  client_project_folder = excluded.client_project_folder,
  streaming_chunks_path = excluded.streaming_chunks_path,
  raw_request_body_bytes = excluded.raw_request_body_bytes,
  response_text_bytes = excluded.response_text_bytes,
  warnings_json = excluded.warnings_json,
  error = excluded.error
`;

let sqliteStatePromise: Promise<SqliteState> | null = null;
let sqliteStatePath: string | null = null;
let activeDb: unknown | null = null;
let didLogUnavailable = false;

function isSqliteLogIndexEnabled(): boolean {
  const configured = process.env["AGENT_INSPECTOR_SQLITE_INDEX"];
  if (configured === "1") return true;
  if (configured === "0") return false;
  return process.env["NODE_ENV"] !== "test";
}

function getSqlitePath(): string {
  return join(resolveLogDir(), SQLITE_INDEX_FILE);
}

function objectFromUnknown(value: unknown): object | null {
  if (value === null) return null;
  if (typeof value === "object" || typeof value === "function") return value;
  return null;
}

function readProperty(value: unknown, name: string): unknown {
  let current = objectFromUnknown(value);
  while (current !== null) {
    const desc = Object.getOwnPropertyDescriptor(current, name);
    if (desc !== undefined) {
      const propertyValue: unknown = Reflect.get(current, name);
      return propertyValue;
    }
    const nextPrototype: unknown = Object.getPrototypeOf(current);
    current = objectFromUnknown(nextPrototype);
  }
  return undefined;
}

function readFunction(value: unknown, name: string): Function | null {
  const property = readProperty(value, name);
  return typeof property === "function" ? property : null;
}

function callMethod(target: unknown, methodName: string, args: readonly unknown[]): unknown {
  const method = readFunction(target, methodName);
  if (method === null) return undefined;
  try {
    return Reflect.apply(method, target, [...args]);
  } catch (err) {
    logger.warn("[sqliteLogIndex] SQLite method failed:", methodName, String(err));
    return undefined;
  }
}

function prepareStatement(db: unknown, sql: string): unknown {
  return callMethod(db, "prepare", [sql]);
}

function runPrepared(db: unknown, sql: string, params: readonly unknown[] = []): boolean {
  const statement = prepareStatement(db, sql);
  if (statement === undefined) return false;
  const result = callMethod(statement, "run", params);
  callMethod(statement, "finalize", []);
  return result !== undefined;
}

function getPrepared(db: unknown, sql: string, params: readonly unknown[] = []): unknown {
  const statement = prepareStatement(db, sql);
  if (statement === undefined) return undefined;
  const result = callMethod(statement, "get", params);
  callMethod(statement, "finalize", []);
  return result;
}

function allPrepared(db: unknown, sql: string, params: readonly unknown[] = []): unknown[] | null {
  const statement = prepareStatement(db, sql);
  if (statement === undefined) return null;
  const result = callMethod(statement, "all", params);
  callMethod(statement, "finalize", []);
  return Array.isArray(result) ? result : null;
}

function execSql(db: unknown, sql: string): boolean {
  const result = callMethod(db, "exec", [sql]);
  return result !== undefined;
}

function sqliteConstructorFromModule(
  moduleValue: unknown,
  exportName: "default" | "Database",
): ((path: string) => unknown) | null {
  const exportedValue = readProperty(moduleValue, exportName);
  if (typeof exportedValue === "function") {
    return (path: string): unknown => Reflect.construct(exportedValue, [path]);
  }
  return null;
}

async function loadSqliteModuleConstructor(
  moduleName: string,
  exportName: "default" | "Database",
): Promise<{ constructor: ((path: string) => unknown) | null; reason: string | null }> {
  try {
    const importedModule: unknown = await import(/* @vite-ignore */ moduleName);
    const exportedConstructor = sqliteConstructorFromModule(importedModule, exportName);
    if (exportedConstructor !== null) return { constructor: exportedConstructor, reason: null };
    if (exportName === "default" && typeof importedModule === "function") {
      return {
        constructor: (path: string): unknown => Reflect.construct(importedModule, [path]),
        reason: null,
      };
    }
    return { constructor: null, reason: `${moduleName} did not expose ${exportName}` };
  } catch (err) {
    return { constructor: null, reason: String(err) };
  }
}

async function loadSqliteConstructor(): Promise<{
  constructor: ((path: string) => unknown) | null;
  reason: string | null;
}> {
  const bunRuntimeDesc = Object.getOwnPropertyDescriptor(process.versions, "bun");
  if (bunRuntimeDesc !== undefined) {
    const bunSqlite = await loadSqliteModuleConstructor(BUN_SQLITE_MODULE_NAME, "Database");
    if (bunSqlite.constructor !== null) return bunSqlite;

    const betterSqlite = await loadSqliteModuleConstructor(SQLITE_PACKAGE_NAME, "default");
    if (betterSqlite.constructor !== null) return betterSqlite;

    const reason = [bunSqlite.reason, betterSqlite.reason]
      .filter((item): item is string => item !== null)
      .join("; ");
    if (!didLogUnavailable) {
      didLogUnavailable = true;
      logger.warn("[sqliteLogIndex] Optional SQLite index unavailable:", reason);
    }
    return { constructor: null, reason };
  }

  const betterSqlite = await loadSqliteModuleConstructor(SQLITE_PACKAGE_NAME, "default");
  if (betterSqlite.constructor !== null) return betterSqlite;

  const bunSqlite = await loadSqliteModuleConstructor(BUN_SQLITE_MODULE_NAME, "Database");
  if (bunSqlite.constructor !== null) return bunSqlite;

  const reason = [betterSqlite.reason, bunSqlite.reason]
    .filter((item): item is string => item !== null)
    .join("; ");
  if (!didLogUnavailable) {
    didLogUnavailable = true;
    logger.warn("[sqliteLogIndex] Optional SQLite index unavailable:", reason);
  }
  return { constructor: null, reason };
}

async function initializeSqliteState(): Promise<SqliteState> {
  const path = getSqlitePath();
  if (!isSqliteLogIndexEnabled()) {
    return { status: "unavailable", path, reason: "SQLite log index is disabled" };
  }

  const sqliteConstructor = await loadSqliteConstructor();
  if (sqliteConstructor.constructor === null) {
    return {
      status: "unavailable",
      path,
      reason: sqliteConstructor.reason ?? "SQLite runtime is not available",
    };
  }

  try {
    await mkdir(dirname(path), { recursive: true });
    const db = sqliteConstructor.constructor(path);
    execSql(db, "PRAGMA journal_mode = WAL");
    execSql(db, "PRAGMA synchronous = NORMAL");
    if (!execSql(db, CREATE_SCHEMA_SQL)) {
      return { status: "unavailable", path, reason: "SQLite schema initialization failed" };
    }
    activeDb = db;
    return { status: "ready", db, path };
  } catch (err) {
    logger.warn("[sqliteLogIndex] Failed to initialize SQLite index:", String(err));
    return { status: "unavailable", path, reason: String(err) };
  }
}

async function getSqliteState(): Promise<SqliteState> {
  const path = getSqlitePath();
  if (sqliteStatePromise !== null && sqliteStatePath !== path) {
    closeSqliteLogIndex();
  }
  if (sqliteStatePromise === null) {
    sqliteStatePath = path;
    sqliteStatePromise = initializeSqliteState();
  }
  return await sqliteStatePromise;
}

export function closeSqliteLogIndex(): void {
  if (activeDb !== null) {
    callMethod(activeDb, "close", []);
  }
  activeDb = null;
  sqliteStatePromise = null;
  sqliteStatePath = null;
}

function warningJson(warnings: string[] | undefined): string | null {
  return warnings === undefined ? null : JSON.stringify(warnings);
}

function boolToInt(value: boolean): number {
  return value ? 1 : 0;
}

function entryParams(entry: LogIndexEntry): readonly unknown[] {
  const summary = entry.summary;
  return [
    entry.id,
    entry.file,
    entry.byteOffset,
    entry.byteLength,
    entry.sessionId ?? summary?.sessionId ?? null,
    entry.model ?? summary?.model ?? null,
    summary?.timestamp ?? "",
    summary?.method ?? "",
    summary?.path ?? "",
    summary?.responseStatus ?? null,
    summary?.inputTokens ?? null,
    summary?.outputTokens ?? null,
    summary?.cacheCreationInputTokens ?? null,
    summary?.cacheReadInputTokens ?? null,
    summary?.elapsedMs ?? null,
    summary?.firstChunkMs ?? null,
    summary?.totalStreamMs ?? null,
    summary?.tokensPerSecond ?? null,
    boolToInt(summary?.streaming ?? false),
    summary?.userAgent ?? null,
    summary?.origin ?? null,
    summary?.apiFormat ?? "unknown",
    boolToInt(summary?.isTest ?? false),
    summary?.replayOfLogId ?? null,
    summary?.providerName ?? null,
    summary?.clientPort ?? null,
    summary?.clientPid ?? null,
    summary?.clientCwd ?? null,
    summary?.clientProjectFolder ?? null,
    summary?.streamingChunksPath ?? null,
    summary?.rawRequestBodyBytes ?? null,
    summary?.responseTextBytes ?? null,
    warningJson(summary?.warnings),
    summary?.error ?? null,
  ];
}

function readNumber(row: unknown, name: string): number | null {
  const value = readProperty(row, name);
  return typeof value === "number" && Number.isFinite(value) ? value : null;
}

function readString(row: unknown, name: string): string | null {
  const value = readProperty(row, name);
  return typeof value === "string" ? value : null;
}

function readBoolean(row: unknown, name: string): boolean {
  return readNumber(row, name) === 1;
}

function readApiFormat(row: unknown): ApiFormat {
  const value = readString(row, "api_format");
  switch (value) {
    case "anthropic":
      return "anthropic";
    case "openai":
      return "openai";
    case "unknown":
    case null:
      return "unknown";
    default:
      return "unknown";
  }
}

function readWarnings(row: unknown): string[] | undefined {
  const value = readString(row, "warnings_json");
  if (value === null) return undefined;
  try {
    const parsed: unknown = JSON.parse(value);
    if (!Array.isArray(parsed)) return undefined;
    const warnings: string[] = [];
    for (const item of parsed) {
      if (typeof item !== "string") return undefined;
      warnings.push(item);
    }
    return warnings;
  } catch {
    return undefined;
  }
}

function readRequiredNumber(row: unknown, name: string): number | null {
  const value = readNumber(row, name);
  return value === null || !Number.isInteger(value) ? null : value;
}

function summaryFromRow(row: unknown, id: number): LogIndexSummary {
  return {
    id,
    timestamp: readString(row, "timestamp") ?? "",
    method: readString(row, "method") ?? "",
    path: readString(row, "path") ?? "",
    model: readString(row, "model"),
    sessionId: readString(row, "session_id"),
    responseStatus: readNumber(row, "response_status"),
    inputTokens: readNumber(row, "input_tokens"),
    outputTokens: readNumber(row, "output_tokens"),
    cacheCreationInputTokens: readNumber(row, "cache_creation_input_tokens"),
    cacheReadInputTokens: readNumber(row, "cache_read_input_tokens"),
    elapsedMs: readNumber(row, "elapsed_ms"),
    firstChunkMs: readNumber(row, "first_chunk_ms"),
    totalStreamMs: readNumber(row, "total_stream_ms"),
    tokensPerSecond: readNumber(row, "tokens_per_second"),
    streaming: readBoolean(row, "streaming"),
    userAgent: readString(row, "user_agent"),
    origin: readString(row, "origin"),
    apiFormat: readApiFormat(row),
    isTest: readBoolean(row, "is_test"),
    replayOfLogId: readNumber(row, "replay_of_log_id"),
    providerName: readString(row, "provider_name"),
    clientPort: readNumber(row, "client_port"),
    clientPid: readNumber(row, "client_pid"),
    clientCwd: readString(row, "client_cwd"),
    clientProjectFolder: readString(row, "client_project_folder"),
    streamingChunksPath: readString(row, "streaming_chunks_path"),
    rawRequestBodyBytes: readNumber(row, "raw_request_body_bytes"),
    responseTextBytes: readNumber(row, "response_text_bytes"),
    warnings: readWarnings(row),
    error: readString(row, "error"),
  };
}

function entryFromRow(row: unknown): LogIndexEntry | null {
  const id = readRequiredNumber(row, "id");
  const file = readString(row, "file");
  const byteOffset = readRequiredNumber(row, "byte_offset");
  const byteLength = readRequiredNumber(row, "byte_length");
  if (id === null || file === null || byteOffset === null || byteLength === null) return null;
  return {
    id,
    file,
    byteOffset,
    byteLength,
    sessionId: readString(row, "session_id"),
    model: readString(row, "model"),
    summary: summaryFromRow(row, id),
  };
}

export async function getSqliteLogIndexStatus(): Promise<SqliteStatus> {
  const state = await getSqliteState();
  switch (state.status) {
    case "ready":
      return { available: true, path: state.path, reason: null };
    case "unavailable":
      return { available: false, path: state.path, reason: state.reason };
  }
}

export async function getSqliteLogIndexMaxId(): Promise<number | null> {
  const state = await getSqliteState();
  if (state.status !== "ready") return null;
  const row = getPrepared(state.db, "SELECT MAX(id) AS max_id FROM log_index");
  return readNumber(row, "max_id") ?? 0;
}

export async function upsertSqliteLogIndexEntry(entry: LogIndexEntry): Promise<boolean> {
  if (entry.summary === undefined) return false;
  const state = await getSqliteState();
  if (state.status !== "ready") return false;
  return runPrepared(state.db, UPSERT_SQL, entryParams(entry));
}

export async function syncSqliteLogIndexEntries(
  entries: readonly LogIndexEntry[],
): Promise<boolean> {
  const state = await getSqliteState();
  if (state.status !== "ready") return false;
  if (entries.length === 0) return true;

  if (!execSql(state.db, "BEGIN IMMEDIATE")) return false;
  try {
    for (const entry of entries) {
      if (entry.summary === undefined) continue;
      if (!runPrepared(state.db, UPSERT_SQL, entryParams(entry))) {
        execSql(state.db, "ROLLBACK");
        return false;
      }
    }

    return execSql(state.db, "COMMIT");
  } catch (err) {
    execSql(state.db, "ROLLBACK");
    logger.warn("[sqliteLogIndex] Failed to sync SQLite index:", String(err));
    return false;
  }
}

export async function findSqliteLogIndexEntry(id: number): Promise<LogIndexEntry | null> {
  const state = await getSqliteState();
  if (state.status !== "ready") return null;
  const row = getPrepared(state.db, "SELECT * FROM log_index WHERE id = ?", [id]);
  return entryFromRow(row);
}

export async function listSqliteLogIndexEntries({
  sessionId,
  model,
}: {
  sessionId?: string;
  model?: string;
} = {}): Promise<LogIndexEntry[] | null> {
  const state = await getSqliteState();
  if (state.status !== "ready") return null;

  const clauses: string[] = [];
  const params: unknown[] = [];
  if (sessionId !== undefined) {
    clauses.push("session_id = ?");
    params.push(sessionId);
  }
  if (model !== undefined) {
    clauses.push("model = ?");
    params.push(model);
  }

  const where = clauses.length === 0 ? "" : ` WHERE ${clauses.join(" AND ")}`;
  const rows = allPrepared(state.db, `SELECT * FROM log_index${where} ORDER BY id ASC`, params);
  if (rows === null) return null;

  const entries: LogIndexEntry[] = [];
  for (const row of rows) {
    const entry = entryFromRow(row);
    if (entry !== null) entries.push(entry);
  }
  return entries;
}

export async function replaceSqliteLogIndexEntries(
  entries: readonly LogIndexEntry[],
): Promise<boolean> {
  const state = await getSqliteState();
  if (state.status !== "ready") return false;

  if (!execSql(state.db, "BEGIN IMMEDIATE")) return false;
  try {
    if (!runPrepared(state.db, "DELETE FROM log_index")) {
      execSql(state.db, "ROLLBACK");
      return false;
    }

    for (const entry of entries) {
      if (entry.summary === undefined) continue;
      if (!runPrepared(state.db, UPSERT_SQL, entryParams(entry))) {
        execSql(state.db, "ROLLBACK");
        return false;
      }
    }

    return execSql(state.db, "COMMIT");
  } catch (err) {
    execSql(state.db, "ROLLBACK");
    logger.warn("[sqliteLogIndex] Failed to replace SQLite index:", String(err));
    return false;
  }
}