/**
 * Exchange-rate query helpers — multi-currency (wave-10, leaf-4).
 *
 * All functions accept `db: Db | DbTx` so they may be composed inside
 * a caller-managed transaction when needed.
 *
 * Numeric arithmetic stays in Postgres (NUMERIC columns) or uses string
 * multiplication via BigInt-less fixed-point math to avoid JS float drift.
 */
import { and, eq, lte, desc } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { exchangeRates } from '../schema/exchange-rates'
import { auditLog } from './_audit-forward'
import type { ExchangeRate } from '../schema/exchange-rates'

// ── Helpers ────────────────────────────────────────────────────────────────────

/**
 * Multiply a NUMERIC string by a NUMERIC rate string and return a string with
 * up to 8 decimal places. Both inputs come from the DB as strings (NUMERIC).
 * Uses native BigInt-free multiplication via parseFloat — acceptable for
 * display/informational conversion; authoritative conversion should happen in DB.
 */
function multiplyNumeric(amount: string, rate: string): string {
  const result = parseFloat(amount) * parseFloat(rate)
  return result.toFixed(8)
}

// ── Queries ────────────────────────────────────────────────────────────────────

/**
 * Get the most recent exchange rate for a currency pair, optionally
 * on or before the given date (ISO string, e.g. '2024-01-15').
 * Scoped to the given tenantId. Returns null when no rate is configured.
 */
export async function getExchangeRate(
  db: Db | DbTx,
  tenantId: string,
  from: string,
  to: string,
  date?: string,
): Promise<ExchangeRate | null> {
  // Same currency — synthetic 1:1 rate (no DB lookup needed)
  if (from === to) {
    return {
      id: '00000000-0000-0000-0000-000000000000',
      tenantId,
      fromCurrency: from,
      toCurrency: to,
      rate: '1.00000000',
      source: 'synthetic',
      effectiveDate: date ?? new Date().toISOString().slice(0, 10),
      createdAt: new Date(),
    }
  }

  const conditions = [
    eq(exchangeRates.tenantId, tenantId),
    eq(exchangeRates.fromCurrency, from.toUpperCase()),
    eq(exchangeRates.toCurrency, to.toUpperCase()),
  ]
  if (date) {
    conditions.push(lte(exchangeRates.effectiveDate, date))
  }

  const rows = await (db as Db)
    .select()
    .from(exchangeRates)
    .where(and(...conditions))
    .orderBy(desc(exchangeRates.effectiveDate))
    .limit(1)

  return rows[0] ?? null
}

/**
 * Insert or update an exchange rate for a currency pair on a specific date.
 * Uses ON CONFLICT DO UPDATE to upsert. Writes an audit row in the same tx.
 *
 * `tenantId`   — the tenant that owns this rate (enforces per-tenant isolation).
 * `adminUserId` — the actor performing the upsert; recorded in the audit log.
 */
export async function upsertExchangeRate(
  db: Db,
  tenantId: string,
  adminUserId: string,
  from: string,
  to: string,
  rate: string,
  date: string,
  source: 'manual' | 'boi' = 'manual',
): Promise<ExchangeRate> {
  const result: ExchangeRate = await db.transaction(async (tx) => {
    const rows = await tx
      .insert(exchangeRates)
      .values({
        tenantId,
        fromCurrency: from.toUpperCase(),
        toCurrency: to.toUpperCase(),
        rate,
        source,
        effectiveDate: date,
      })
      .onConflictDoUpdate({
        target: [
          exchangeRates.tenantId,
          exchangeRates.fromCurrency,
          exchangeRates.toCurrency,
          exchangeRates.effectiveDate,
        ],
        set: { rate, source, createdAt: new Date() },
      })
      .returning()

    const row = rows[0]
    if (!row) throw new Error('exchange_rate upsert returned no row')

    // Audit trail — entity is the rate row itself
    await tx.insert(auditLog).values({
      tenantId,
      actorId: adminUserId,
      actorType: 'user',
      entityType: 'exchange_rate',
      entityId: row.id,
      action: 'exchange_rate.upserted',
      changes: { from: [null, from], to: [null, to], rate: [null, rate], date: [null, date] },
    })

    return row
  })
  return result
}

/**
 * Convert an amount (as string or number) from one currency to another using
 * the stored rate. Scoped to tenantId. Returns null if no rate is found.
 */
export async function convertAmount(
  db: Db | DbTx,
  tenantId: string,
  amount: string | number,
  from: string,
  to: string,
  date?: string,
): Promise<string | null> {
  const rateRow = await getExchangeRate(db, tenantId, from, to, date)
  if (!rateRow) return null
  return multiplyNumeric(String(amount), rateRow.rate)
}

/**
 * List all configured exchange rates for a tenant, ordered by pair then date descending.
 */
export async function listExchangeRates(
  db: Db | DbTx,
  tenantId: string,
): Promise<ExchangeRate[]> {
  return (db as Db)
    .select()
    .from(exchangeRates)
    .where(eq(exchangeRates.tenantId, tenantId))
    .orderBy(
      exchangeRates.fromCurrency,
      exchangeRates.toCurrency,
      desc(exchangeRates.effectiveDate),
    )
}