/**
 * Email template query helpers — email-template-editor (wave-11 leaf-E).
 *
 * Read/write custom email templates stored per-tenant.
 * Falls back to system defaults when no custom row exists.
 */
import { and, eq } from 'drizzle-orm'
import type { Db } from '../client'
import { tenantEmailTemplates } from '../schema/tenant-email-templates'

export type { TenantEmailTemplateRow } from '../schema/tenant-email-templates'

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

export const EMAIL_TEMPLATE_KEYS = [
  'invoice_sent',
  'invoice_reminder',
  'invoice_paid_receipt',
  'proposal_sent',
  'contract_signing_request',
  'portal_invitation',
  'lead_form_thank_you',
] as const

export type EmailTemplateKey = (typeof EMAIL_TEMPLATE_KEYS)[number]

export interface EmailTemplateListItem {
  key: EmailTemplateKey
  label: string
  subject: string
  isCustom: boolean
  updatedAt: string | null
}

const TEMPLATE_LABELS: Record<EmailTemplateKey, string> = {
  invoice_sent: 'Invoice sent',
  invoice_reminder: 'Invoice reminder',
  invoice_paid_receipt: 'Payment receipt',
  proposal_sent: 'Proposal sent',
  contract_signing_request: 'Contract signing request',
  portal_invitation: 'Portal invitation',
  lead_form_thank_you: 'Lead form thank you',
}

const DEFAULT_SUBJECTS: Record<EmailTemplateKey, string> = {
  invoice_sent: 'Invoice {{invoiceNumber}} from {{tenantName}}',
  invoice_reminder: 'Reminder: Invoice {{invoiceNumber}} is due',
  invoice_paid_receipt: 'Payment received — Invoice {{invoiceNumber}}',
  proposal_sent: '{{tenantName}} sent you a proposal',
  contract_signing_request: 'Please sign: {{contractTitle}}',
  portal_invitation: 'You have been invited to {{tenantName}}\'s portal',
  lead_form_thank_you: 'Thank you for contacting {{tenantName}}',
}

// ── getTenantEmailTemplate ─────────────────────────────────────────────────────

export async function getTenantEmailTemplate(
  db: Db,
  tenantId: string,
  key: string,
): Promise<{ subject: string; bodyHtml: string; bodyText: string; isCustom: boolean; updatedAt: Date | null }> {
  const rows = await db
    .select()
    .from(tenantEmailTemplates)
    .where(
      and(
        eq(tenantEmailTemplates.tenantId, tenantId),
        eq(tenantEmailTemplates.templateKey, key),
        eq(tenantEmailTemplates.isActive, true),
      ),
    )
    .limit(1)

  if (rows[0]) {
    return {
      subject: rows[0].subject,
      bodyHtml: rows[0].bodyHtml,
      bodyText: rows[0].bodyText,
      isCustom: true,
      updatedAt: rows[0].updatedAt,
    }
  }

  const defaultSubject = DEFAULT_SUBJECTS[key as EmailTemplateKey] ?? key
  return {
    subject: defaultSubject,
    bodyHtml: `<p>{{body}}</p>`,
    bodyText: '{{body}}',
    isCustom: false,
    updatedAt: null,
  }
}

// ── listTenantEmailTemplates ───────────────────────────────────────────────────

export async function listTenantEmailTemplates(
  db: Db,
  tenantId: string,
): Promise<EmailTemplateListItem[]> {
  const rows = await db
    .select()
    .from(tenantEmailTemplates)
    .where(eq(tenantEmailTemplates.tenantId, tenantId))

  const customByKey = new Map(rows.map((r) => [r.templateKey, r]))

  return EMAIL_TEMPLATE_KEYS.map((key) => {
    const custom = customByKey.get(key)
    return {
      key,
      label: TEMPLATE_LABELS[key],
      subject: custom?.subject ?? DEFAULT_SUBJECTS[key],
      isCustom: !!custom,
      updatedAt: custom?.updatedAt?.toISOString() ?? null,
    }
  })
}

// ── saveTenantEmailTemplate ────────────────────────────────────────────────────

export async function saveTenantEmailTemplate(
  db: Db,
  tenantId: string,
  key: string,
  subject: string,
  bodyHtml: string,
  bodyText: string,
): Promise<{ subject: string; bodyHtml: string; isCustom: boolean; updatedAt: Date }> {
  const now = new Date()

  await db
    .insert(tenantEmailTemplates)
    .values({
      tenantId,
      templateKey: key,
      subject,
      bodyHtml,
      bodyText,
      isActive: true,
      updatedAt: now,
    })
    .onConflictDoUpdate({
      target: [tenantEmailTemplates.tenantId, tenantEmailTemplates.templateKey],
      set: {
        subject,
        bodyHtml,
        bodyText,
        isActive: true,
        updatedAt: now,
      },
    })

  return { subject, bodyHtml, isCustom: true, updatedAt: now }
}

// ── resetTenantEmailTemplate ───────────────────────────────────────────────────

export async function resetTenantEmailTemplate(
  db: Db,
  tenantId: string,
  key: string,
): Promise<void> {
  await db
    .delete(tenantEmailTemplates)
    .where(
      and(
        eq(tenantEmailTemplates.tenantId, tenantId),
        eq(tenantEmailTemplates.templateKey, key),
      ),
    )
}