/**
 * tax_rates — admin-dashboard.
 *
 * Stores statutory non-VAT tax rates for each country (corporate income,
 * personal income brackets, withholding defaults). VAT rates remain in
 * `vat_rates` (system-i18n).
 *
 * The composite UNIQUE (country_code, tax_type, effective_from) ensures
 * exactly one rate per country/type/date. Immutability: past rates are never
 * deleted — new future-dated entries supersede them.
 *
 * `rate` is NUMERIC(6,4) — decimal fraction (e.g. 0.2300 = 23%).
 * `threshold_ils` is the income ceiling (ILS/yr) for bracket types; NULL for
 * the highest bracket and non-bracket types.
 */
import { pgTable, uuid, text, numeric, date, timestamp, index, unique, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { adminUsers } from './admin'

export const taxRates = pgTable(
  'tax_rates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    countryCode: text('country_code').notNull(),
    // tax_type CHECK enum
    taxType: text('tax_type').notNull(),
    rate: numeric('rate', { precision: 6, scale: 4 }).notNull(),
    effectiveFrom: date('effective_from').notNull(),
    // ILS/yr ceiling for bracket types; NULL = highest bracket or non-bracket
    thresholdIls: numeric('threshold_ils'),
    notes: text('notes'),
    createdBy: uuid('created_by')
      .notNull()
      .references(() => adminUsers.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    countryTypeFromUnique: unique('tax_rates_country_type_from_unique').on(
      t.countryCode,
      t.taxType,
      t.effectiveFrom,
    ),
    countryCodeIdx: index('tax_rates_country_code_idx').on(t.countryCode),
    taxTypeCheck: check(
      'tax_rates_tax_type_check',
      sql`${t.taxType} IN (
        'corporate_income',
        'personal_bracket_1','personal_bracket_2','personal_bracket_3',
        'personal_bracket_4','personal_bracket_5','personal_bracket_6',
        'withholding_default'
      )`,
    ),
  }),
)

export type TaxRateRow = typeof taxRates.$inferSelect
export type NewTaxRate = typeof taxRates.$inferInsert