import { and, desc, eq, inArray, isNull, sql } from 'drizzle-orm';
import type { Database } from './client.js';
import { systemAlerts } from './schema/ext-ops.js';

export type AlertSeverity = 'info' | 'warn' | 'critical';
export type AlertStatus = 'open' | 'acknowledged' | 'resolved';

export interface AlertInput {
  kind: string;
  severity: AlertSeverity;
  connectorId?: string | null;
  message: string;
  detail?: Record<string, unknown>;
}

export interface SystemAlert {
  id: number;
  kind: string;
  severity: string;
  connectorId: string | null;
  message: string;
  detail: Record<string, unknown>;
  firstSeenAt: Date;
  lastSeenAt: Date;
  count: number;
  status: string;
  resolvedAt: Date | null;
}

const ACTIVE: AlertStatus[] = ['open', 'acknowledged'];

/**
 * Raise (or bump) an internal alert (CLAUDE.md §170). Deduplicated on kind + connector + message
 * among open/acknowledged rows: a repeat increments `count`, refreshes `last_seen_at`, `severity`
 * and `detail`. Returns the alert id. Inside failure handlers use `raiseAlertSafe`: an alert
 * failure must never mask the original error.
 */
export async function raiseAlert(db: Database, input: AlertInput): Promise<number> {
  const connectorId = input.connectorId ?? null;
  const detail = input.detail ?? {};
  const where = and(eq(systemAlerts.kind, input.kind), eq(systemAlerts.message, input.message), connectorId === null ? isNull(systemAlerts.connectorId) : eq(systemAlerts.connectorId, connectorId), inArray(systemAlerts.status, ACTIVE));
  const [existing] = await db.select({ id: systemAlerts.id }).from(systemAlerts).where(where).orderBy(desc(systemAlerts.lastSeenAt)).limit(1);
  if (existing) {
    await db
      .update(systemAlerts)
      .set({ count: sql`${systemAlerts.count} + 1`, lastSeenAt: new Date(), severity: input.severity, detail })
      .where(eq(systemAlerts.id, existing.id));
    return existing.id;
  }
  const [row] = await db.insert(systemAlerts).values({ kind: input.kind, severity: input.severity, connectorId, message: input.message, detail }).returning({ id: systemAlerts.id });
  return row!.id;
}

/** Like `raiseAlert` but swallows (and returns) the error — for use inside failure handlers. */
export async function raiseAlertSafe(db: Database, input: AlertInput): Promise<number | Error> {
  try {
    return await raiseAlert(db, input);
  } catch (e) {
    return e as Error;
  }
}

/**
 * Resolve open/acknowledged alerts of one kind (or several) for a connector — or for all
 * connectors when `connectorId` is undefined; `null` targets alerts without a connector.
 * Returns the number of rows resolved.
 */
export async function resolveAlerts(db: Database, kind: string | string[], connectorId?: string | null): Promise<number> {
  const kinds = Array.isArray(kind) ? kind : [kind];
  if (!kinds.length) return 0;
  const conds = [inArray(systemAlerts.kind, kinds), inArray(systemAlerts.status, ACTIVE)];
  if (connectorId === null) conds.push(isNull(systemAlerts.connectorId));
  else if (connectorId !== undefined) conds.push(eq(systemAlerts.connectorId, connectorId));
  const rows = await db
    .update(systemAlerts)
    .set({ status: 'resolved', resolvedAt: new Date() })
    .where(and(...conds))
    .returning({ id: systemAlerts.id });
  return rows.length;
}

/** Open (and optionally acknowledged) alerts, most severe and most recent first. */
export async function listAlerts(db: Database, opts: { status?: AlertStatus | 'active'; connectorId?: string; limit?: number } = {}): Promise<SystemAlert[]> {
  const status = opts.status ?? 'active';
  const conds = status === 'active' ? [inArray(systemAlerts.status, ACTIVE)] : [eq(systemAlerts.status, status)];
  if (opts.connectorId) conds.push(eq(systemAlerts.connectorId, opts.connectorId));
  const rows = await db
    .select()
    .from(systemAlerts)
    .where(and(...conds))
    .orderBy(sql`CASE ${systemAlerts.severity} WHEN 'critical' THEN 0 WHEN 'warn' THEN 1 ELSE 2 END`, desc(systemAlerts.lastSeenAt))
    .limit(opts.limit ?? 200);
  return rows as SystemAlert[];
}