import { DatabaseSync } from "node:sqlite";

export type AiRequestStatus = "pending" | "processing" | "done" | "error";

export type AiRequestRow = {
  id: string;
  instructions: string;
  prompt: string;
  status: AiRequestStatus;
  response: string | null;
  error: string | null;
  created_at: number;
  claimed_at: number | null;
  claimed_by: string | null;
  completed_at: number | null;
};

export function ensureAiRequestsSchema(db: DatabaseSync): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS ai_requests (
      id TEXT PRIMARY KEY,
      instructions TEXT NOT NULL DEFAULT '',
      prompt TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','processing','done','error')),
      response TEXT,
      error TEXT,
      created_at INTEGER NOT NULL,
      claimed_at INTEGER,
      claimed_by TEXT,
      completed_at INTEGER
    );

    CREATE INDEX IF NOT EXISTS idx_ai_requests_status_created
      ON ai_requests(status, created_at);
  `);
  migrateAiRequestsTableIfNeeded(db);
}

export function createAiRequest(db: DatabaseSync, input: { instructions?: string | null; prompt: string }): AiRequestRow {
  ensureAiRequestsSchema(db);
  const now = Date.now();
  const id = `air:${now.toString(36)}:${Math.random().toString(36).slice(2, 8)}`;
  db.prepare(`
    INSERT INTO ai_requests(id, instructions, prompt, status, created_at)
    VALUES(?, ?, ?, 'pending', ?);
  `).run(id, input.instructions ?? "", input.prompt, now);
  return getAiRequest(db, id)!;
}

export function getAiRequest(db: DatabaseSync, id: string): AiRequestRow | null {
  ensureAiRequestsSchema(db);
  return (db.prepare(`
    SELECT id, instructions, prompt, status, response, error, created_at, claimed_at, claimed_by, completed_at
    FROM ai_requests WHERE id=? LIMIT 1;
  `).get(id) as AiRequestRow | undefined) ?? null;
}

export function claimAiRequest(
  db: DatabaseSync,
  id: string,
  options: { claimedBy: string; nowMs?: number },
): boolean {
  ensureAiRequestsSchema(db);
  const result = db.prepare(`
    UPDATE ai_requests
    SET status='processing',
        claimed_at=?,
        claimed_by=?,
        error=NULL
    WHERE id=? AND status='pending';
  `).run(options.nowMs ?? Date.now(), options.claimedBy, id);
  return Number(result.changes ?? 0) === 1;
}

export function failStaleAiRequests(
  db: DatabaseSync,
  options: { olderThanMs?: number; nowMs?: number; error?: string } = {},
): number {
  ensureAiRequestsSchema(db);
  const now = options.nowMs ?? Date.now();
  const olderThanMs = options.olderThanMs ?? 120_000;
  const failed = db.prepare(`
    UPDATE ai_requests
    SET status='error',
        error=?,
        completed_at=?
    WHERE status='pending' AND created_at < ?;
  `).run(
    options.error ?? "DewAI request expired before the app could complete it. Retry the request.",
    now,
    now - olderThanMs,
  );
  const rescued = db.prepare(`
    UPDATE ai_requests
    SET status='pending',
        claimed_at=NULL,
        claimed_by=NULL,
        error=NULL
    WHERE status='processing' AND COALESCE(claimed_at, created_at) < ?;
  `).run(now - olderThanMs);
  return Number(failed.changes ?? 0) + Number(rescued.changes ?? 0);
}

export async function waitForAiRequest(
  db: DatabaseSync,
  id: string,
  options: { timeoutMs?: number; pollMs?: number } = {},
): Promise<AiRequestRow> {
  ensureAiRequestsSchema(db);
  const timeoutMs = options.timeoutMs ?? 60_000;
  const pollMs = options.pollMs ?? 1_000;
  const deadline = Date.now() + timeoutMs;

  while (Date.now() <= deadline) {
    const row = getAiRequest(db, id);
    if (!row) throw new Error(`AI request ${id} disappeared.`);
    if (row.status === "done" || row.status === "error") return row;
    await new Promise((resolve) => setTimeout(resolve, pollMs));
  }

  throw new Error(`AI generation timed out after ${timeoutMs}ms.`);
}

function migrateAiRequestsTableIfNeeded(db: DatabaseSync): void {
  const table = db.prepare(`
    SELECT sql FROM sqlite_master WHERE type='table' AND name='ai_requests' LIMIT 1;
  `).get() as { sql?: string } | undefined;
  const sql = table?.sql ?? "";
  const columns = db.prepare("PRAGMA table_info(ai_requests)").all() as Array<{ name: string }>;
  const hasColumn = (name: string) => columns.some((column) => column.name === name);

  if (!sql.includes("'processing'") && sql.includes("CHECK")) {
    db.exec(`
      ALTER TABLE ai_requests RENAME TO ai_requests_legacy_status;
      CREATE TABLE ai_requests (
        id TEXT PRIMARY KEY,
        instructions TEXT NOT NULL DEFAULT '',
        prompt TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','processing','done','error')),
        response TEXT,
        error TEXT,
        created_at INTEGER NOT NULL,
        claimed_at INTEGER,
        claimed_by TEXT,
        completed_at INTEGER
      );
      INSERT INTO ai_requests(id, instructions, prompt, status, response, error, created_at, completed_at)
      SELECT id, instructions, prompt, status, response, error, created_at, completed_at
      FROM ai_requests_legacy_status;
      DROP TABLE ai_requests_legacy_status;
    `);
  } else {
    if (!hasColumn("claimed_at")) db.exec("ALTER TABLE ai_requests ADD COLUMN claimed_at INTEGER;");
    if (!hasColumn("claimed_by")) db.exec("ALTER TABLE ai_requests ADD COLUMN claimed_by TEXT;");
  }

  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_ai_requests_status_created
      ON ai_requests(status, created_at);
  `);
}
