// Author: Simon-Pierre Boucher
// Contact: contact@spboucher.ai
// Project: chat.spboucher.ai

// Model catalog: OpenRouter → sync job → SQLite cache → application.
// The catalog is never hand-maintained; removed models are marked unavailable, never deleted.

import { getDb } from "@/lib/db/database";
import { fetchModels } from "@/lib/openrouter";
import type { ModelDefinition } from "@/lib/openrouter";

const SYNC_INTERVAL_MS = 1000 * 60 * 60 * 4; // refresh every 4 hours

export interface CatalogModel extends ModelDefinition {
  available: boolean;
  favorite: boolean;
  pinned: boolean;
  lastUsedAt: number | null;
  useCount: number;
}

interface ModelRow {
  openrouter_model_id: string;
  name: string;
  provider: string | null;
  description: string | null;
  context_length: number | null;
  pricing_prompt: number | null;
  pricing_completion: number | null;
  pricing_image: number | null;
  pricing_request: number | null;
  cap_text: number;
  cap_vision: number;
  cap_reasoning: number;
  cap_tools: number;
  cap_structured: number;
  architecture: string | null;
  tokenizer: string | null;
  available: number;
  or_created_at: number | null;
  favorite: number | null;
  pinned: number | null;
  last_used_at: number | null;
  use_count: number | null;
}

function rowToModel(r: ModelRow): CatalogModel {
  return {
    id: r.openrouter_model_id,
    name: r.name,
    provider: r.provider ?? undefined,
    description: r.description ?? undefined,
    contextLength: r.context_length ?? undefined,
    pricing: {
      prompt: r.pricing_prompt ?? undefined,
      completion: r.pricing_completion ?? undefined,
      image: r.pricing_image ?? undefined,
      request: r.pricing_request ?? undefined,
    },
    capabilities: {
      text: Boolean(r.cap_text),
      vision: Boolean(r.cap_vision),
      reasoning: Boolean(r.cap_reasoning),
      tools: Boolean(r.cap_tools),
      structuredOutput: Boolean(r.cap_structured),
    },
    metadata: {
      architecture: r.architecture ?? undefined,
      tokenizer: r.tokenizer ?? undefined,
      createdAt: r.or_created_at ?? undefined,
    },
    available: Boolean(r.available),
    favorite: Boolean(r.favorite),
    pinned: Boolean(r.pinned),
    lastUsedAt: r.last_used_at,
    useCount: r.use_count ?? 0,
  };
}

export async function syncCatalog(): Promise<{ total: number; added: number; removed: number }> {
  const models = await fetchModels();
  const db = getDb();
  const now = Date.now();

  const upsert = db.prepare(`
    INSERT INTO models (
      openrouter_model_id, name, provider, description, context_length,
      pricing_prompt, pricing_completion, pricing_image, pricing_request,
      cap_text, cap_vision, cap_reasoning, cap_tools, cap_structured,
      architecture, tokenizer, raw_json, available, or_created_at, first_seen_at, updated_at
    ) VALUES (
      @id, @name, @provider, @description, @contextLength,
      @pPrompt, @pCompletion, @pImage, @pRequest,
      @cText, @cVision, @cReasoning, @cTools, @cStructured,
      @architecture, @tokenizer, @raw, 1, @orCreatedAt, @now, @now
    )
    ON CONFLICT(openrouter_model_id) DO UPDATE SET
      name=@name, provider=@provider, description=@description, context_length=@contextLength,
      pricing_prompt=@pPrompt, pricing_completion=@pCompletion, pricing_image=@pImage, pricing_request=@pRequest,
      cap_text=@cText, cap_vision=@cVision, cap_reasoning=@cReasoning, cap_tools=@cTools, cap_structured=@cStructured,
      architecture=@architecture, tokenizer=@tokenizer, raw_json=@raw, available=1, or_created_at=@orCreatedAt, updated_at=@now
  `);

  const existing = new Set(
    (db.prepare("SELECT openrouter_model_id FROM models").all() as { openrouter_model_id: string }[]).map(
      (r) => r.openrouter_model_id
    )
  );
  const seen = new Set<string>();
  let added = 0;

  const tx = db.transaction(() => {
    for (const m of models) {
      seen.add(m.id);
      if (!existing.has(m.id)) added++;
      upsert.run({
        id: m.id,
        name: m.name,
        provider: m.provider ?? null,
        description: m.description ?? null,
        contextLength: m.contextLength ?? null,
        pPrompt: m.pricing?.prompt ?? null,
        pCompletion: m.pricing?.completion ?? null,
        pImage: m.pricing?.image ?? null,
        pRequest: m.pricing?.request ?? null,
        cText: m.capabilities.text ? 1 : 0,
        cVision: m.capabilities.vision ? 1 : 0,
        cReasoning: m.capabilities.reasoning ? 1 : 0,
        cTools: m.capabilities.tools ? 1 : 0,
        cStructured: m.capabilities.structuredOutput ? 1 : 0,
        architecture: m.metadata.architecture ?? null,
        tokenizer: m.metadata.tokenizer ?? null,
        raw: JSON.stringify(m.metadata.raw ?? null),
        orCreatedAt: m.metadata.createdAt ?? null,
        now,
      });
    }
    // Models gone from the catalog stay in the DB (history integrity) but are marked unavailable.
    const markUnavailable = db.prepare(
      "UPDATE models SET available = 0, updated_at = ? WHERE openrouter_model_id = ?"
    );
    for (const id of existing) {
      if (!seen.has(id)) markUnavailable.run(now, id);
    }
    db.prepare("INSERT INTO settings (key, value) VALUES ('catalog_synced_at', ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value").run(String(now));
  });
  tx();

  return { total: models.length, added, removed: existing.size - [...existing].filter((id) => seen.has(id)).length };
}

export function catalogSyncedAt(): number | null {
  const row = getDb().prepare("SELECT value FROM settings WHERE key = 'catalog_synced_at'").get() as
    | { value: string }
    | undefined;
  return row ? Number(row.value) : null;
}

/** Sync if the cache is empty or stale. Safe to call on any request path. */
export async function ensureCatalogFresh(): Promise<void> {
  const syncedAt = catalogSyncedAt();
  if (syncedAt && Date.now() - syncedAt < SYNC_INTERVAL_MS) return;
  try {
    await syncCatalog();
  } catch (e) {
    if (!syncedAt) throw e; // no cache at all — surface the failure
    // stale cache is still usable; sync will retry on a later request
    console.error("catalog refresh failed:", e);
  }
}

export function listCatalog(): CatalogModel[] {
  const rows = getDb()
    .prepare(
      `SELECT m.*, p.favorite, p.pinned, p.last_used_at, p.use_count
       FROM models m LEFT JOIN model_prefs p ON p.model_id = m.openrouter_model_id
       ORDER BY m.name COLLATE NOCASE`
    )
    .all() as ModelRow[];
  return rows.map(rowToModel);
}

export function getModel(id: string): CatalogModel | null {
  const row = getDb()
    .prepare(
      `SELECT m.*, p.favorite, p.pinned, p.last_used_at, p.use_count
       FROM models m LEFT JOIN model_prefs p ON p.model_id = m.openrouter_model_id
       WHERE m.openrouter_model_id = ?`
    )
    .get(id) as ModelRow | undefined;
  return row ? rowToModel(row) : null;
}

export function touchModelUsage(id: string): void {
  getDb()
    .prepare(
      `INSERT INTO model_prefs (model_id, last_used_at, use_count) VALUES (?, ?, 1)
       ON CONFLICT(model_id) DO UPDATE SET last_used_at = excluded.last_used_at, use_count = use_count + 1`
    )
    .run(id, Date.now());
}

export function setModelPref(id: string, pref: { favorite?: boolean; pinned?: boolean }): void {
  const db = getDb();
  db.prepare("INSERT OR IGNORE INTO model_prefs (model_id) VALUES (?)").run(id);
  if (pref.favorite !== undefined) {
    db.prepare("UPDATE model_prefs SET favorite = ? WHERE model_id = ?").run(pref.favorite ? 1 : 0, id);
  }
  if (pref.pinned !== undefined) {
    db.prepare("UPDATE model_prefs SET pinned = ? WHERE model_id = ?").run(pref.pinned ? 1 : 0, id);
  }
}