/**
 * Gap-free invoice sequence helpers — invoices-core.
 * Prefix sync + atomic next-number assignment live here to avoid import cycles
 * between invoices.ts and settings-invoices.ts.
 */
import { eq, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { tenantSettings } from '../schema/tenants'

export const DEFAULT_SEQUENCE_PREFIXES = {
  invoice: 'INV-',
  proforma: 'PROFORMA-',
  credit_note: 'CN-',
} as const

export type InvoiceSequenceType = 'invoice' | 'proforma' | 'credit_note'

export interface InvoiceSequencePrefixes {
  invoice: string
  proforma: string
  credit_note: string
}

/** Read invoice/proforma prefixes from tenant_settings (invoice-settings-page). */
export async function resolveInvoiceSequencePrefixes(
  db: Db | DbTx,
  tenantId: string,
): Promise<InvoiceSequencePrefixes> {
  const [row] = await db
    .select({
      invoiceNumberPrefix: tenantSettings.invoiceNumberPrefix,
      proformaNumberPrefix: tenantSettings.proformaNumberPrefix,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  return {
    invoice: row?.invoiceNumberPrefix ?? DEFAULT_SEQUENCE_PREFIXES.invoice,
    proforma: row?.proformaNumberPrefix ?? DEFAULT_SEQUENCE_PREFIXES.proforma,
    credit_note: DEFAULT_SEQUENCE_PREFIXES.credit_note,
  }
}

/**
 * Upsert invoice_sequences.prefix for all sequence types.
 * ON CONFLICT updates prefix only — last_number is preserved.
 */
export async function syncInvoiceSequencePrefixes(
  tx: DbTx,
  tenantId: string,
  prefixes: InvoiceSequencePrefixes,
): Promise<void> {
  const rows: Array<[InvoiceSequenceType, string]> = [
    ['invoice', prefixes.invoice],
    ['proforma', prefixes.proforma],
    ['credit_note', prefixes.credit_note],
  ]

  for (const [type, prefix] of rows) {
    await tx.execute(
      sql`INSERT INTO invoice_sequences (tenant_id, type, last_number, prefix)
          VALUES (${tenantId}, ${type}, 0, ${prefix})
          ON CONFLICT (tenant_id, type)
          DO UPDATE SET prefix = EXCLUDED.prefix`,
    )
  }
}

/**
 * Atomically assign the next sequential number for (tenantId, type).
 * Uses INSERT … ON CONFLICT DO UPDATE so it handles the first invoice per tenant.
 * Returns prefix + zero-padded number (5 digits min).
 */
export async function nextInvoiceNumber(
  tx: DbTx,
  tenantId: string,
  type: InvoiceSequenceType,
): Promise<string> {
  const prefixes = await resolveInvoiceSequencePrefixes(tx, tenantId)
  const prefix =
    type === 'invoice'
      ? prefixes.invoice
      : type === 'proforma'
        ? prefixes.proforma
        : prefixes.credit_note

  const result = await tx.execute(
    sql`INSERT INTO invoice_sequences (tenant_id, type, last_number, prefix)
        VALUES (${tenantId}, ${type}, 1, ${prefix})
        ON CONFLICT (tenant_id, type)
        DO UPDATE SET last_number = invoice_sequences.last_number + 1
        RETURNING prefix, last_number`,
  )
  const row = result[0] as { prefix: string; last_number: number }
  const padded = String(row.last_number).padStart(5, '0')
  return `${row.prefix}${padded}`
}