import { firstExecuteRow } from '../execute-rows.js';
import { asc, eq } from 'drizzle-orm';
import type { DrizzleClient } from '@/server/db/client.js';
import { encrypt } from '@/server/db/crypto.js';
import { alertChannels } from '@/server/db/schema.js';
import type { AlertChannelRecord } from '@/server/db/schema.js';

export type ChannelPublic = Omit<AlertChannelRecord, 'secretEnc'> & { hasSecret: boolean };

export function toPublicChannel(row: AlertChannelRecord): ChannelPublic {
  const { secretEnc, ...rest } = row;
  return { ...rest, hasSecret: secretEnc != null };
}

export async function listAlertChannels(db: DrizzleClient): Promise<AlertChannelRecord[]> {
  return db.select().from(alertChannels).orderBy(asc(alertChannels.id));
}

/** Channels eligible to receive a given severity (enabled + min_severity floor satisfied). */
export async function listEnabledChannelsForSeverity(
  db: DrizzleClient,
  severity: 'warn' | 'page',
): Promise<AlertChannelRecord[]> {
  const rows = await db.select().from(alertChannels).where(eq(alertChannels.enabled, true));
  const rank = { warn: 1, page: 2 } as const;
  return rows.filter((r) => rank[severity] >= rank[r.minSeverity as 'warn' | 'page']);
}

export async function getAlertChannel(
  db: DrizzleClient,
  id: string,
): Promise<AlertChannelRecord | null> {
  const rows = await db.select().from(alertChannels).where(eq(alertChannels.id, id)).limit(1);
  return rows[0] ?? null;
}

export async function insertAlertChannel(
  db: DrizzleClient,
  input: {
    id: string;
    kind: string;
    label: string;
    enabled: boolean;
    minSeverity: string;
    config: Record<string, unknown>;
    secret?: string;
    piiKey?: string;
  },
): Promise<AlertChannelRecord> {
  const values: Record<string, unknown> = {
    id: input.id,
    kind: input.kind,
    label: input.label,
    enabled: input.enabled,
    minSeverity: input.minSeverity,
    config: input.config,
  };
  if (input.secret !== undefined && input.piiKey !== undefined) {
    values.secretEnc = encrypt(input.secret, input.piiKey);
  }
  const [row] = await db
    .insert(alertChannels)
    .values(values as never)
    .returning();
  if (!row) throw new Error('insertAlertChannel: insert failed');
  return row;
}

export async function updateAlertChannel(
  db: DrizzleClient,
  id: string,
  fields: {
    label?: string;
    enabled?: boolean;
    minSeverity?: string;
    config?: Record<string, unknown>;
    secret?: string;
    piiKey?: string;
  },
): Promise<AlertChannelRecord | null> {
  const patch: Record<string, unknown> = { updatedAt: new Date() };
  if (fields.label !== undefined) patch.label = fields.label;
  if (fields.enabled !== undefined) patch.enabled = fields.enabled;
  if (fields.minSeverity !== undefined) patch.minSeverity = fields.minSeverity;
  if (fields.config !== undefined) patch.config = fields.config;
  if (fields.secret !== undefined && fields.piiKey !== undefined) {
    patch.secretEnc = encrypt(fields.secret, fields.piiKey);
  }
  const [row] = await db
    .update(alertChannels)
    .set(patch)
    .where(eq(alertChannels.id, id))
    .returning();
  return row ?? null;
}

export async function deleteAlertChannel(db: DrizzleClient, id: string): Promise<boolean> {
  const res = await db
    .delete(alertChannels)
    .where(eq(alertChannels.id, id))
    .returning({ id: alertChannels.id });
  return res.length > 0;
}

/** Decrypt a channel's secret for sending. Returns null if no secret set. Never log the result. */
export async function decryptChannelSecret(
  db: DrizzleClient,
  id: string,
  piiKey: string,
): Promise<string | null> {
  const { sql } = await import('drizzle-orm');
  const res = await db.execute<{ secret: string | null }>(sql`
    SELECT pgp_sym_decrypt(secret_enc, ${piiKey})::text AS secret
    FROM alert_channels WHERE id = ${id}
  `);
  return firstExecuteRow<{ secret: string | null }>(res)?.secret ?? null;
}