/**
 * Dunning query helpers — payment-retry-dunning (wave-11).
 *
 * All helpers are tenant-filtered.
 * Provides CRUD for dunning_schedules and dunning_log.
 */
import { and, eq, desc, asc } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import { dunningSchedules, dunningLog } from '../schema/dunning'
import { tenantSettings } from '../schema/tenants'

// ── Zod schemas ───────────────────────────────────────────────────────────────

export const upsertDunningScheduleSchema = z.object({
  offsetDays: z.number().int().min(1),
  action: z.enum(['email_reminder', 'suspend_access', 'flag_for_review']),
  emailTemplateId: z.string().uuid().optional().nullable(),
})

export const deleteDunningScheduleSchema = z.object({
  offsetDays: z.number().int().min(1),
})

export type UpsertDunningScheduleInput = z.infer<typeof upsertDunningScheduleSchema>

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

export interface DunningScheduleObject {
  id: string
  tenantId: string
  offsetDays: number
  action: string
  emailTemplateId: string | null
  createdAt: string
}

export interface DunningLogObject {
  id: string
  tenantId: string
  invoiceId: string
  offsetDays: number
  action: string
  sentAt: string
  result: string
  errorMsg: string | null
}

// ── Serializers ───────────────────────────────────────────────────────────────

function mapSchedule(row: typeof dunningSchedules.$inferSelect): DunningScheduleObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    offsetDays: row.offsetDays,
    action: row.action,
    emailTemplateId: row.emailTemplateId ?? null,
    createdAt: row.createdAt instanceof Date ? row.createdAt.toISOString() : String(row.createdAt),
  }
}

function mapLog(row: typeof dunningLog.$inferSelect): DunningLogObject {
  return {
    id: row.id,
    tenantId: row.tenantId,
    invoiceId: row.invoiceId,
    offsetDays: row.offsetDays,
    action: row.action,
    sentAt: row.sentAt instanceof Date ? row.sentAt.toISOString() : String(row.sentAt),
    result: row.result,
    errorMsg: row.errorMsg ?? null,
  }
}

// ── Query helpers ─────────────────────────────────────────────────────────────

export async function listDunningSchedules(
  db: Db,
  tenantId: string,
): Promise<DunningScheduleObject[]> {
  const rows = await db
    .select()
    .from(dunningSchedules)
    .where(eq(dunningSchedules.tenantId, tenantId))
    .orderBy(asc(dunningSchedules.offsetDays))
  return rows.map(mapSchedule)
}

export async function upsertDunningSchedule(
  db: Db,
  tenantId: string,
  input: UpsertDunningScheduleInput,
): Promise<DunningScheduleObject> {
  const [row] = await db
    .insert(dunningSchedules)
    .values({
      tenantId,
      offsetDays: input.offsetDays,
      action: input.action,
      emailTemplateId: input.emailTemplateId ?? null,
    })
    .onConflictDoUpdate({
      target: [dunningSchedules.tenantId, dunningSchedules.offsetDays],
      set: {
        action: input.action,
        emailTemplateId: input.emailTemplateId ?? null,
      },
    })
    .returning()
  return mapSchedule(row!)
}

export async function deleteDunningSchedule(
  db: Db,
  tenantId: string,
  offsetDays: number,
): Promise<void> {
  await db
    .delete(dunningSchedules)
    .where(
      and(
        eq(dunningSchedules.tenantId, tenantId),
        eq(dunningSchedules.offsetDays, offsetDays),
      ),
    )
}

export async function appendDunningLog(
  db: Db,
  tenantId: string,
  entry: {
    invoiceId: string
    offsetDays: number
    action: string
    result: 'sent' | 'skipped' | 'error'
    errorMsg?: string | null
  },
): Promise<DunningLogObject> {
  const [row] = await db
    .insert(dunningLog)
    .values({
      tenantId,
      invoiceId: entry.invoiceId,
      offsetDays: entry.offsetDays,
      action: entry.action,
      result: entry.result,
      errorMsg: entry.errorMsg ?? null,
    })
    .returning()
  return mapLog(row!)
}

export async function listDunningLog(
  db: Db,
  tenantId: string,
  invoiceId: string,
): Promise<DunningLogObject[]> {
  const rows = await db
    .select()
    .from(dunningLog)
    .where(
      and(
        eq(dunningLog.tenantId, tenantId),
        eq(dunningLog.invoiceId, invoiceId),
      ),
    )
    .orderBy(desc(dunningLog.sentAt))
  return rows.map(mapLog)
}

export async function getDunningSuspendAccess(db: Db, tenantId: string): Promise<boolean> {
  const [row] = await db
    .select({ flag: tenantSettings.dunningSuspendAccess })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
  return row?.flag ?? false
}

/**
 * Check if a dunning step has already been sent for (invoice, offsetDays) — idempotency guard.
 */
export async function isDunningStepAlreadySent(
  db: Db,
  tenantId: string,
  invoiceId: string,
  offsetDays: number,
): Promise<boolean> {
  const rows = await db
    .select({ id: dunningLog.id })
    .from(dunningLog)
    .where(
      and(
        eq(dunningLog.tenantId, tenantId),
        eq(dunningLog.invoiceId, invoiceId),
        eq(dunningLog.offsetDays, offsetDays),
      ),
    )
    .limit(1)
  return rows.length > 0
}