/**
 * Invoice payment reminder helpers — invoice-payment-reminders (wave-13).
 *
 * Pure schedule computation (no DB I/O) + DB query helpers for the cron
 * and API routes.
 */
import { and, eq, isNotNull, lte, or, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { tenantSettings } from '../schema/tenants'
import { customers } from '../schema/customers'

// ── Schedule types ─────────────────────────────────────────────────────────────

export interface ReminderStage {
  offset_days: number
  enabled: boolean
}

export const DEFAULT_REMINDER_SCHEDULE: ReminderStage[] = [
  { offset_days: -3, enabled: true },
  { offset_days: 0, enabled: true },
  { offset_days: 7, enabled: true },
  { offset_days: 14, enabled: true },
  { offset_days: 30, enabled: true },
]

export interface NextReminder {
  nextReminderAt: Date
  nextReminderOffset: number
}

/** Parse raw JSONB value into typed ReminderStage[]. Falls back to default. */
export function parseReminderSchedule(raw: unknown): ReminderStage[] {
  if (!Array.isArray(raw)) return DEFAULT_REMINDER_SCHEDULE
  const stages: ReminderStage[] = []
  for (const item of raw) {
    if (
      item !== null &&
      typeof item === 'object' &&
      typeof (item as Record<string, unknown>).offset_days === 'number' &&
      typeof (item as Record<string, unknown>).enabled === 'boolean'
    ) {
      stages.push({
        offset_days: (item as { offset_days: number }).offset_days,
        enabled: (item as { enabled: boolean }).enabled,
      })
    }
  }
  return stages.length > 0 ? stages : DEFAULT_REMINDER_SCHEDULE
}

/**
 * Compute the next reminder for an invoice.
 *
 * Returns the earliest enabled stage strictly after `lastSentOffset`
 * (or the earliest enabled stage when none sent yet). Returns null when
 * no enabled stage remains.
 *
 * Stages are sorted by offset_days ascending before evaluation.
 */
export function computeNextReminder(
  dueDate: Date,
  schedule: ReminderStage[],
  lastSentOffset?: number | null,
): NextReminder | null {
  // Sort stages ascending by offset_days
  const sorted = [...schedule].sort((a, b) => a.offset_days - b.offset_days)
  for (const stage of sorted) {
    if (!stage.enabled) continue
    if (lastSentOffset != null && stage.offset_days <= lastSentOffset) continue
    const nextAt = new Date(dueDate.getTime() + stage.offset_days * 24 * 60 * 60 * 1000)
    return { nextReminderAt: nextAt, nextReminderOffset: stage.offset_days }
  }
  return null
}

// ── DB query helpers ───────────────────────────────────────────────────────────

export interface ReminderSettings {
  enabled: boolean
  schedule: ReminderStage[]
}

/** Fetch tenant reminder settings (enabled + schedule) from tenant_settings. */
export async function getTenantReminderSettings(
  db: Db,
  tenantId: string,
): Promise<ReminderSettings> {
  const row = await db
    .select({
      invoiceRemindersEnabled: tenantSettings.invoiceRemindersEnabled,
      invoiceReminderSchedule: tenantSettings.invoiceReminderSchedule,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
    .then((rows) => rows[0] ?? null)

  if (!row) {
    return { enabled: true, schedule: DEFAULT_REMINDER_SCHEDULE }
  }
  return {
    enabled: row.invoiceRemindersEnabled,
    schedule: parseReminderSchedule(row.invoiceReminderSchedule),
  }
}

/** Persist tenant reminder settings. */
export async function updateTenantReminderSettings(
  db: Db,
  tenantId: string,
  settings: ReminderSettings,
): Promise<ReminderSettings> {
  await db
    .update(tenantSettings)
    .set({
      invoiceRemindersEnabled: settings.enabled,
      invoiceReminderSchedule: settings.schedule,
      updatedAt: new Date(),
    })
    .where(eq(tenantSettings.tenantId, tenantId))
  return settings
}

export interface ReminderDueInvoice {
  id: string
  tenantId: string
  customerId: string | null
  dueDate: string | null
  status: string
  invoiceNumber: string | null
  proformaNumber: string | null
  total: string
  currency: string
  reminderCount: number
  nextReminderOffset: number | null
  paymentLinkSentAt: Date | null
}

/**
 * Select invoices due for reminder: next_reminder_at <= now(), not disabled.
 * Used by the daily cron.
 */
export async function selectReminderDueInvoices(db: Db): Promise<ReminderDueInvoice[]> {
  const now = new Date()
  const rows = await db
    .select({
      id: invoices.id,
      tenantId: invoices.tenantId,
      customerId: invoices.customerId,
      dueDate: invoices.dueDate,
      status: invoices.status,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      total: invoices.total,
      currency: invoices.currency,
      reminderCount: invoices.reminderCount,
      nextReminderOffset: invoices.nextReminderOffset,
      paymentLinkSentAt: invoices.paymentLinkSentAt,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.remindersDisabled, false),
        isNotNull(invoices.nextReminderAt),
        lte(invoices.nextReminderAt, now),
        or(
          eq(invoices.status, 'SENT'),
          eq(invoices.status, 'APPROVED'),
          eq(invoices.status, 'TAX_ISSUED'),
          eq(invoices.status, 'PARTIALLY_PAID'),
        ),
      ),
    )
  return rows as ReminderDueInvoice[]
}

/** Advance reminder state after a send. */
export async function advanceInvoiceReminder(
  db: Db,
  invoiceId: string,
  tenantId: string,
  nextReminder: NextReminder | null,
): Promise<void> {
  await db
    .update(invoices)
    .set({
      reminderLastSentAt: new Date(),
      reminderCount: sql`${invoices.reminderCount} + 1`,
      nextReminderAt: nextReminder?.nextReminderAt ?? null,
      nextReminderOffset: nextReminder?.nextReminderOffset ?? null,
      updatedAt: new Date(),
    })
    .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId)))
}

/** Initialize or clear reminder state when invoice status transitions. */
export async function setInvoiceReminderState(
  db: Db,
  invoiceId: string,
  tenantId: string,
  nextReminder: NextReminder | null,
): Promise<void> {
  await db
    .update(invoices)
    .set({
      nextReminderAt: nextReminder?.nextReminderAt ?? null,
      nextReminderOffset: nextReminder?.nextReminderOffset ?? null,
      updatedAt: new Date(),
    })
    .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId)))
}

/** Disable reminders for a specific invoice. */
export async function disableInvoiceReminders(
  db: Db,
  invoiceId: string,
  tenantId: string,
  disabled: boolean,
): Promise<void> {
  await db
    .update(invoices)
    .set({
      remindersDisabled: disabled,
      nextReminderAt: disabled ? null : undefined,
      nextReminderOffset: disabled ? null : undefined,
      updatedAt: new Date(),
    })
    .where(and(eq(invoices.id, invoiceId), eq(invoices.tenantId, tenantId)))
}

/** Resolve customer primary email for reminder send. */
export async function resolveReminderRecipient(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<string | null> {
  const row = await db
    .select({ email: customers.email })
    .from(customers)
    .where(and(eq(customers.id, customerId), eq(customers.tenantId, tenantId)))
    .limit(1)
    .then((rows) => rows[0] ?? null)
  return row?.email ?? null
}