/**
 * Invoice email event query helpers — invoice-email-history (wave-13).
 *
 * All helpers are tenant-scoped; every statement carries a tenant_id WHERE.
 * Route files MUST NOT import raw Drizzle tables; they import from this module.
 */
import { and, eq, gt, inArray, desc, asc } from 'drizzle-orm'
import type { Db } from '../client'
import { invoiceEmailEvents } from '../schema/invoice-email-events'
import { users } from '../schema/users'

// ── Types ──────────────────────────────────────────────────────────────────────

export type InvoiceEmailEventType =
  | 'sent'
  | 'delivered'
  | 'opened'
  | 'clicked'
  | 'bounced'
  | 'failed'

export interface InvoiceEmailEventRow {
  id: string
  tenantId: string
  invoiceId: string
  sentBy: string
  toAddress: string
  eventType: InvoiceEmailEventType
  metadata: Record<string, unknown> | null
  occurredAt: string // ISO TIMESTAMPTZ
}

export interface InvoiceEmailEventWithSender extends InvoiceEmailEventRow {
  sentByName: string | null
}

// ── insertEmailEvent ──────────────────────────────────────────────────────────

export async function insertEmailEvent(
  db: Db,
  input: {
    tenantId: string
    invoiceId: string
    sentBy: string
    toAddress: string
    eventType: InvoiceEmailEventType
    metadata?: Record<string, unknown> | null
  },
): Promise<InvoiceEmailEventRow> {
  const [row] = await db
    .insert(invoiceEmailEvents)
    .values({
      tenantId: input.tenantId,
      invoiceId: input.invoiceId,
      sentBy: input.sentBy,
      toAddress: input.toAddress,
      eventType: input.eventType,
      metadata: input.metadata ?? null,
    })
    .returning()

  return mapRow(row!)
}

// ── listEmailEventsByInvoice ──────────────────────────────────────────────────

export async function listEmailEventsByInvoice(
  db: Db,
  input: { tenantId: string; invoiceId: string },
): Promise<InvoiceEmailEventWithSender[]> {
  const rows = await db
    .select({
      id: invoiceEmailEvents.id,
      tenantId: invoiceEmailEvents.tenantId,
      invoiceId: invoiceEmailEvents.invoiceId,
      sentBy: invoiceEmailEvents.sentBy,
      toAddress: invoiceEmailEvents.toAddress,
      eventType: invoiceEmailEvents.eventType,
      metadata: invoiceEmailEvents.metadata,
      occurredAt: invoiceEmailEvents.occurredAt,
      sentByName: users.name,
    })
    .from(invoiceEmailEvents)
    .leftJoin(users, eq(invoiceEmailEvents.sentBy, users.id))
    .where(
      and(
        eq(invoiceEmailEvents.tenantId, input.tenantId),
        eq(invoiceEmailEvents.invoiceId, input.invoiceId),
      ),
    )
    .orderBy(asc(invoiceEmailEvents.occurredAt))

  return rows.map((r) => ({
    ...mapRow({
      id: r.id,
      tenantId: r.tenantId,
      invoiceId: r.invoiceId,
      sentBy: r.sentBy,
      toAddress: r.toAddress,
      eventType: r.eventType,
      metadata: r.metadata,
      occurredAt: r.occurredAt,
    }),
    sentByName: r.sentByName ?? null,
  }))
}

// ── hasRecentOpenEvent ────────────────────────────────────────────────────────

export async function hasRecentOpenEvent(
  db: Db,
  input: {
    tenantId: string
    invoiceId: string
    toAddress: string
    withinMs?: number
  },
): Promise<boolean> {
  const withinMs = input.withinMs ?? 3_600_000
  const cutoff = new Date(Date.now() - withinMs)

  const [row] = await db
    .select({ id: invoiceEmailEvents.id })
    .from(invoiceEmailEvents)
    .where(
      and(
        eq(invoiceEmailEvents.tenantId, input.tenantId),
        eq(invoiceEmailEvents.invoiceId, input.invoiceId),
        eq(invoiceEmailEvents.toAddress, input.toAddress),
        eq(invoiceEmailEvents.eventType, 'opened'),
        gt(invoiceEmailEvents.occurredAt, cutoff),
      ),
    )
    .limit(1)

  return row !== undefined
}

// ── getLatestRecipientStatus ──────────────────────────────────────────────────

/**
 * For a batch of invoice ids, return the best/most-recent delivery status
 * per invoice's most recent `sent` occurrence.
 * Priority order (best→worst): opened > delivered > sent > bounced > failed
 */
export async function getLatestRecipientStatus(
  db: Db,
  input: { tenantId: string; invoiceIds: string[] },
): Promise<Map<string, InvoiceEmailEventType>> {
  if (input.invoiceIds.length === 0) return new Map()

  // Get the most recent event per invoice using a window function via raw sql
  const rows = await db
    .select({
      invoiceId: invoiceEmailEvents.invoiceId,
      eventType: invoiceEmailEvents.eventType,
      occurredAt: invoiceEmailEvents.occurredAt,
    })
    .from(invoiceEmailEvents)
    .where(
      and(
        eq(invoiceEmailEvents.tenantId, input.tenantId),
        inArray(invoiceEmailEvents.invoiceId, input.invoiceIds),
      ),
    )
    .orderBy(desc(invoiceEmailEvents.occurredAt))

  // Priority: opened > clicked > delivered > bounced > failed > sent
  const priority: Record<InvoiceEmailEventType, number> = {
    opened: 5,
    clicked: 4,
    delivered: 3,
    bounced: 2,
    failed: 1,
    sent: 0,
  }

  const result = new Map<string, InvoiceEmailEventType>()

  for (const row of rows) {
    const current = result.get(row.invoiceId)
    const incoming = row.eventType as InvoiceEmailEventType
    if (!current || (priority[incoming] ?? -1) > (priority[current] ?? -1)) {
      result.set(row.invoiceId, incoming)
    }
  }

  return result
}

// ── getLastSendRecipients ─────────────────────────────────────────────────────

/**
 * Returns distinct to_address values from the most recent `sent` event
 * batch for a given invoice. Used to pre-fill the Send-again dialog.
 */
export async function getLastSendRecipients(
  db: Db,
  input: { tenantId: string; invoiceId: string },
): Promise<string[]> {
  // Find the occurred_at of the most recent 'sent' event
  const [latestSent] = await db
    .select({ occurredAt: invoiceEmailEvents.occurredAt })
    .from(invoiceEmailEvents)
    .where(
      and(
        eq(invoiceEmailEvents.tenantId, input.tenantId),
        eq(invoiceEmailEvents.invoiceId, input.invoiceId),
        eq(invoiceEmailEvents.eventType, 'sent'),
      ),
    )
    .orderBy(desc(invoiceEmailEvents.occurredAt))
    .limit(1)

  if (!latestSent) return []

  // All sent events within 1 second of the latest (same batch)
  const batchStart = new Date(latestSent.occurredAt.getTime() - 1000)

  const rows = await db
    .selectDistinct({ toAddress: invoiceEmailEvents.toAddress })
    .from(invoiceEmailEvents)
    .where(
      and(
        eq(invoiceEmailEvents.tenantId, input.tenantId),
        eq(invoiceEmailEvents.invoiceId, input.invoiceId),
        eq(invoiceEmailEvents.eventType, 'sent'),
        gt(invoiceEmailEvents.occurredAt, batchStart),
      ),
    )

  return rows.map((r) => r.toAddress)
}

// ── getLastSentEventForInvoice ────────────────────────────────────────────────

/**
 * Find the most recent 'sent' event for a given invoice (no tenant filter).
 * Used by the Resend webhook handler to resolve tenant_id + sent_by without
 * a user session. The webhook carries no tenant context; this resolves it via
 * the invoice_id tag echoed back by Resend.
 */
export async function getLastSentEventForInvoice(
  db: Db,
  invoiceId: string,
): Promise<{ tenantId: string; sentBy: string } | null> {
  const [row] = await db
    .select({
      tenantId: invoiceEmailEvents.tenantId,
      sentBy: invoiceEmailEvents.sentBy,
    })
    .from(invoiceEmailEvents)
    .where(
      and(
        eq(invoiceEmailEvents.invoiceId, invoiceId),
        eq(invoiceEmailEvents.eventType, 'sent'),
      ),
    )
    .orderBy(desc(invoiceEmailEvents.occurredAt))
    .limit(1)

  return row ?? null
}

// ── helpers ────────────────────────────────────────────────────────────────────

function mapRow(row: {
  id: string
  tenantId: string
  invoiceId: string
  sentBy: string
  toAddress: string
  eventType: string
  metadata: unknown
  occurredAt: Date
}): InvoiceEmailEventRow {
  return {
    id: row.id,
    tenantId: row.tenantId,
    invoiceId: row.invoiceId,
    sentBy: row.sentBy,
    toAddress: row.toAddress,
    eventType: row.eventType as InvoiceEmailEventType,
    metadata: row.metadata as Record<string, unknown> | null,
    occurredAt: row.occurredAt.toISOString(),
  }
}