import type Database from 'better-sqlite3';

export interface ErrorLogEntry {
  id: number;
  created_at: string;
  source: string;
  message: string;
  context: string | null;
  url: string | null;
  user_agent: string | null;
  session_code: string | null;
}

const RETENTION_DAYS = 30;

/**
 * Legt die error_logs-Tabelle bei Bedarf an, analog zum bestehenden Muster
 * in app/api/admin/stats/route.ts (ensureCounters) bzw. lib/restaurant-stats.ts
 * und lib/retention.ts - Tabellen, die erst nach dem ersten Launch dazukamen,
 * werden hier "self-healing" per CREATE TABLE IF NOT EXISTS angelegt, statt
 * eine Migration zu erfordern.
 */
export function ensureErrorLogTable(db: Database.Database) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS error_logs (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      created_at TEXT NOT NULL DEFAULT (datetime('now')),
      source TEXT NOT NULL,
      message TEXT NOT NULL,
      context TEXT,
      url TEXT,
      user_agent TEXT,
      session_code TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_error_logs_created_at ON error_logs(created_at DESC);
  `);
}

export function logError(db: Database.Database, params: {
  source: 'client' | 'server';
  message: string;
  context?: string | null;
  url?: string | null;
  userAgent?: string | null;
  sessionCode?: string | null;
}) {
  ensureErrorLogTable(db);
  db.prepare(`
    INSERT INTO error_logs (source, message, context, url, user_agent, session_code)
    VALUES (?, ?, ?, ?, ?, ?)
  `).run(
    params.source,
    params.message.slice(0, 2000),
    params.context ? params.context.slice(0, 4000) : null,
    params.url ? params.url.slice(0, 500) : null,
    params.userAgent ? params.userAgent.slice(0, 300) : null,
    params.sessionCode ?? null,
  );

  // Automatische Bereinigung nach jedem Insert - haelt die Tabelle bounded,
  // ohne einen separaten Cron-Job/Scheduler zu benoetigen. 30 Tage sind
  // grosszuegig genug fuer "was ist diese Woche schiefgelaufen?", ohne dass
  // die DB unbegrenzt waechst.
  db.prepare(`DELETE FROM error_logs WHERE created_at < datetime('now', '-${RETENTION_DAYS} days')`).run();
}

export function getRecentErrors(
  db: Database.Database,
  opts: { limit?: number; offset?: number; source?: string } = {},
): { errors: ErrorLogEntry[]; total: number } {
  ensureErrorLogTable(db);
  const limit = opts.limit ?? 200;
  const offset = opts.offset ?? 0;
  const source = opts.source;

  const whereClause = source ? 'WHERE source = ?' : '';
  const whereParams = source ? [source] : [];

  const total = (
    db.prepare(`SELECT COUNT(*) AS c FROM error_logs ${whereClause}`).get(...whereParams) as { c: number }
  ).c;

  const errors = db
    .prepare(`SELECT * FROM error_logs ${whereClause} ORDER BY created_at DESC LIMIT ? OFFSET ?`)
    .all(...whereParams, limit, offset) as ErrorLogEntry[];

  return { errors, total };
}

export function getErrorTotals(db: Database.Database): { all: number; client: number; server: number } {
  ensureErrorLogTable(db);
  const rows = db
    .prepare(`SELECT source, COUNT(*) AS c FROM error_logs GROUP BY source`)
    .all() as { source: string; c: number }[];
  const totals = { all: 0, client: 0, server: 0 };
  for (const r of rows) {
    if (r.source === 'client') totals.client = r.c;
    else if (r.source === 'server') totals.server = r.c;
    totals.all += r.c;
  }
  return totals;
}

export function clearErrorLogs(db: Database.Database) {
  ensureErrorLogTable(db);
  db.prepare(`DELETE FROM error_logs`).run();
}