/**
 * Outbox query helpers - used by the process-outbox cron handler.
 *
 * Implements at-least-once delivery with bounded retries (max OUTBOX_MAX_RETRIES attempts).
 * `FOR UPDATE SKIP LOCKED` prevents duplicate processing when multiple
 * Workers instances run concurrently.
 */

import { and, desc, eq, isNotNull, isNull, lt, or, sql } from 'drizzle-orm';
import type { DrizzleClient, TxDrizzleClient } from '../client.js';
import { outbox } from '../schema.js';

export type OutboxEvent = typeof outbox.$inferSelect;

export const OUTBOX_MAX_RETRIES = 3;
export const OUTBOX_MAX_RETRYABLE_RETRIES = 12;

export function outboxRetryLimitForError(error: unknown): number {
  return typeof error === 'object' &&
    error !== null &&
    'retryable' in error &&
    error.retryable === true
    ? OUTBOX_MAX_RETRYABLE_RETRIES
    : OUTBOX_MAX_RETRIES;
}

/**
 * Fetch up to `limit` unprocessed outbox events eligible for processing.
 *
 * Eligible = one of:
 *   1. Never attempted: processed_at IS NULL AND failed_at IS NULL
 *   2. Previously failed but retryable: processed_at IS NULL AND retry_count < OUTBOX_MAX_RETRIES
 *      AND failed_at < now() - 5 minutes
 *
 * Uses `FOR UPDATE SKIP LOCKED` to prevent double-processing across concurrent workers.
 */
export async function getPendingOutboxEvents(
  db: DrizzleClient,
  limit = 50,
): Promise<OutboxEvent[]> {
  const fiveMinutesAgo = new Date(Date.now() - 5 * 60 * 1000);

  return db
    .select()
    .from(outbox)
    .where(
      and(
        isNull(outbox.processedAt),
        isNull(outbox.deadAt),
        or(
          // Never attempted
          isNull(outbox.failedAt),
          // Previously failed, retryable: retry_count < OUTBOX_MAX_RETRIES AND failed_at < now() - 5min
          and(lt(outbox.retryCount, outbox.retryLimit), lt(outbox.failedAt, fiveMinutesAgo)),
        ),
      ),
    )
    .orderBy(outbox.createdAt)
    .limit(limit)
    .for('update', { skipLocked: true });
}

/**
 * Mark an outbox event as successfully processed.
 */
export async function markOutboxEventProcessed(db: DrizzleClient, id: string): Promise<void> {
  await db
    .update(outbox)
    .set({ processedAt: new Date() })
    .where(sql`${outbox.id} = ${id}`);
}

/**
 * Insert a new outbox row and return its id.
 *
 * Centralises the insert-and-return-id pattern used across all workflow files
 * so workflow code never calls db.insert(outbox) directly (query-layer exclusivity).
 *
 * Inserts one row per domain effect. Callers own effect idempotency.
 */
export async function insertOutboxRow(
  db: DrizzleClient | TxDrizzleClient,
  values: typeof outbox.$inferInsert,
): Promise<{ id: string }> {
  const [inserted] = await db.insert(outbox).values(values).returning({ id: outbox.id });
  if (!inserted) throw new Error('outbox insert returned no row');
  return inserted;
}

export async function insertOutboxRowOnce(
  db: DrizzleClient,
  values: typeof outbox.$inferInsert & { dedupeKey: string },
): Promise<{ id: string; inserted: boolean }> {
  const [inserted] = await db
    .insert(outbox)
    .values(values)
    .onConflictDoNothing({ target: outbox.dedupeKey })
    .returning({ id: outbox.id });
  if (inserted) return { id: inserted.id, inserted: true };

  const [existing] = await db
    .select({ id: outbox.id })
    .from(outbox)
    .where(eq(outbox.dedupeKey, values.dedupeKey))
    .limit(1);
  if (!existing) throw new Error('outbox dedupe conflict returned no row');
  return { id: existing.id, inserted: false };
}

/**
 * Mark an outbox event as failed. Increments retry_count and stores the error message.
 * Events are retried after 5 minutes if retry_count < OUTBOX_MAX_RETRIES.
 */
export async function markOutboxEventFailed(
  db: DrizzleClient,
  id: string,
  error: string,
  retryLimit = OUTBOX_MAX_RETRIES,
): Promise<void> {
  await db
    .update(outbox)
    .set({
      failedAt: new Date(),
      retryCount: sql`${outbox.retryCount} + 1`,
      ...(retryLimit > OUTBOX_MAX_RETRIES
        ? { retryLimit: sql`GREATEST(${outbox.retryLimit}, ${retryLimit})` }
        : {}),
      lastError: error,
      deadAt: sql`CASE WHEN ${outbox.retryCount} + 1 >= GREATEST(${outbox.retryLimit}, ${retryLimit}) THEN now() ELSE ${outbox.deadAt} END`,
    })
    .where(sql`${outbox.id} = ${id}`);
}

export async function listDeadOutbox(db: DrizzleClient, limit = 100): Promise<OutboxEvent[]> {
  return db
    .select()
    .from(outbox)
    .where(and(isNull(outbox.processedAt), isNotNull(outbox.deadAt)))
    .orderBy(desc(outbox.deadAt))
    .limit(limit);
}

export async function countDeadOutbox(db: DrizzleClient): Promise<number> {
  const [r] = await db
    .select({ n: sql<number>`count(*)` })
    .from(outbox)
    .where(and(isNull(outbox.processedAt), isNotNull(outbox.deadAt)));
  return Number(r?.n ?? 0);
}

export async function redriveOutboxEvent(db: DrizzleClient, id: string): Promise<void> {
  await db
    .update(outbox)
    .set({
      deadAt: null,
      failedAt: null,
      retryCount: 0,
      retryLimit: OUTBOX_MAX_RETRIES,
      lastError: null,
    })
    .where(sql`${outbox.id} = ${id}`);
}

export async function resetOutboxEventForRetry(db: DrizzleClient, id: string): Promise<void> {
  await db
    .update(outbox)
    .set({
      retryCount: 0,
      retryLimit: OUTBOX_MAX_RETRIES,
      failedAt: null,
    })
    .where(sql`${outbox.id} = ${id}`);
}

/**
 * Convenience wrapper: insert an outbox row for a domain event.
 *
 * Workflow code calls this instead of `insertOutboxRow` directly to avoid
 * constructing the full `outbox.$inferInsert` shape at every call-site.
 * The queue send (OUTBOX_QUEUE.send) must be done by the caller after the
 * transaction commits — this helper only inserts the DB row.
 */
export async function enqueueOutbox(
  db: DrizzleClient,
  event: {
    aggregateType: string;
    aggregateId: string;
    eventType: string;
    payload: Record<string, unknown>;
  },
): Promise<{ id: string }> {
  return insertOutboxRow(db, {
    aggregateType: event.aggregateType,
    aggregateId: event.aggregateId,
    eventType: event.eventType,
    payload: event.payload,
  });
}

export async function insertOutboxEvent(
  db: DrizzleClient,
  values: typeof outbox.$inferInsert,
): Promise<void> {
  await db.insert(outbox).values(values);
}