/**
 * VAT rate lookup query — system-i18n.
 *
 * Uses a date-aware "most recent rate on or before date" query.
 * The `rate` column is NUMERIC and Drizzle returns it as a string;
 * coerce to Number before returning.
 *
 * Returns 0 when no row matches (unknown country or date before first entry).
 */
import { and, desc, eq, lte } from 'drizzle-orm'
import type { Db } from '../client'
import { vatRates } from '../schema/vat-rates'

/**
 * Returns the VAT rate (decimal, e.g. 0.18) for `countryCode` in effect on
 * `date`. Returns 0 if no rate history exists for the country/date.
 */
export async function getVatRate(db: Db, countryCode: string, date: Date): Promise<number> {
  // Format date as YYYY-MM-DD for the DATE column comparison.
  const iso = date.toISOString().slice(0, 10)

  const row = await db
    .select({ rate: vatRates.rate })
    .from(vatRates)
    .where(and(eq(vatRates.countryCode, countryCode), lte(vatRates.effectiveFrom, iso)))
    .orderBy(desc(vatRates.effectiveFrom))
    .limit(1)

  return Number(row[0]?.rate ?? 0)
}