/**
 * exchange_rates — multi-currency (wave-10, leaf-4).
 *
 * Stores historical exchange rates between ISO 4217 currency pairs.
 * Used by invoices, expenses, and payouts for multi-currency support.
 *
 * The unique constraint on (from_currency, to_currency, effective_date)
 * ensures exactly one rate per pair per date. Consumers call
 * getExchangeRate(db, from, to, date?) which does a lte(effectiveDate, date)
 * + DESC + LIMIT 1 to find the rate in effect on any given date.
 *
 * `rate` is NUMERIC(18,8) — never a floating-point column.
 * `source` distinguishes manual entries from automated Bank of Israel pulls.
 * `tenantId` scopes rates per-tenant so one tenant cannot read or mutate
 * another tenant's configured rates.
 */
import { pgTable, uuid, text, numeric, date, timestamp, uniqueIndex } from 'drizzle-orm/pg-core'

export const exchangeRates = pgTable(
  'exchange_rates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id').notNull(),
    fromCurrency: text('from_currency').notNull(),
    toCurrency: text('to_currency').notNull(),
    rate: numeric('rate', { precision: 18, scale: 8 }).notNull(),
    source: text('source').default('manual'), // 'manual' | 'boi' (Bank of Israel)
    effectiveDate: date('effective_date').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    uniqPairDate: uniqueIndex('exchange_rates_pair_date_uniq').on(
      t.tenantId,
      t.fromCurrency,
      t.toCurrency,
      t.effectiveDate,
    ),
  }),
)

export type ExchangeRate = typeof exchangeRates.$inferSelect
export type NewExchangeRate = typeof exchangeRates.$inferInsert