/**
 * Tax rate query helpers — admin-dashboard.
 *
 * getTaxRate: public lookup (date-aware, most-recent effective rate).
 * listTaxRates: admin list with added-by email join.
 * addTaxRate: insert with uniqueness / threshold validation.
 * assertTaxRateMutable: immutability guard for past rates.
 * listVatRatesForAdmin: read-only VAT projection for admin UI.
 */
import { and, desc, eq, lte } from 'drizzle-orm'
import type { Db } from '../client'
import { taxRates } from '../schema/tax'
import { vatRates } from '../schema/vat-rates'
import { adminUsers } from '../schema/admin'

// ── Public lookup ─────────────────────────────────────────────────────────────

/**
 * Returns the statutory tax rate (decimal, e.g. 0.23) for `countryCode` /
 * `taxType` in effect on `date`. Returns 0 if no matching row exists.
 */
export async function getTaxRate(
  db: Db,
  countryCode: string,
  taxType: 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: taxRates.rate })
    .from(taxRates)
    .where(
      and(
        eq(taxRates.countryCode, countryCode),
        eq(taxRates.taxType, taxType),
        lte(taxRates.effectiveFrom, iso),
      ),
    )
    .orderBy(desc(taxRates.effectiveFrom))
    .limit(1)

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

// ── Admin list ────────────────────────────────────────────────────────────────

export interface TaxRateListRow {
  id: string
  countryCode: string
  taxType: string
  rate: string
  effectiveFrom: string
  thresholdIls: string | null
  notes: string | null
  createdAt: Date
  addedByEmail: string
}

/**
 * Returns all tax_rates rows for a country, ordered by tax_type then
 * effective_from DESC. Joins admin_users.email as addedByEmail.
 */
export async function listTaxRates(db: Db, countryCode: string): Promise<TaxRateListRow[]> {
  const rows = await db
    .select({
      id: taxRates.id,
      countryCode: taxRates.countryCode,
      taxType: taxRates.taxType,
      rate: taxRates.rate,
      effectiveFrom: taxRates.effectiveFrom,
      thresholdIls: taxRates.thresholdIls,
      notes: taxRates.notes,
      createdAt: taxRates.createdAt,
      addedByEmail: adminUsers.email,
    })
    .from(taxRates)
    .innerJoin(adminUsers, eq(taxRates.createdBy, adminUsers.id))
    .where(eq(taxRates.countryCode, countryCode))
    .orderBy(taxRates.taxType, desc(taxRates.effectiveFrom))

  return rows
}

// ── Add tax rate ──────────────────────────────────────────────────────────────

export class TaxRateConflictError extends Error {
  constructor(message: string) {
    super(message)
    this.name = 'TaxRateConflictError'
  }
}

export class TaxRateValidationError extends Error {
  constructor(message: string) {
    super(message)
    this.name = 'TaxRateValidationError'
  }
}

const BRACKET_TYPES = [
  'personal_bracket_1',
  'personal_bracket_2',
  'personal_bracket_3',
  'personal_bracket_4',
  'personal_bracket_5',
] as const

const HIGHEST_BRACKET = 'personal_bracket_6'

export interface AddTaxRateInput {
  countryCode: string
  taxType: string
  rate: string          // NUMERIC string, e.g. '0.2300'
  effectiveFrom: string // ISO date string YYYY-MM-DD
  thresholdIls?: string | null
  notes?: string | null
  createdBy: string     // admin_users.id
}

/**
 * Inserts a new tax rate. Validates:
 *  (a) threshold_ils is required for bracket_1..5; must be null for bracket_6
 *      and non-bracket types.
 *  (b) The UNIQUE constraint on (country_code, tax_type, effective_from) —
 *      surfaces as TaxRateConflictError (409).
 */
export async function addTaxRate(db: Db, input: AddTaxRateInput): Promise<{ id: string }> {
  const isBracket15 = (BRACKET_TYPES as readonly string[]).includes(input.taxType)
  const isHighestBracket = input.taxType === HIGHEST_BRACKET

  // threshold validation
  if (isBracket15 && (input.thresholdIls === undefined || input.thresholdIls === null || input.thresholdIls === '')) {
    throw new TaxRateValidationError(`threshold_ils is required for ${input.taxType}`)
  }
  if (!isBracket15 && !isHighestBracket && input.thresholdIls != null) {
    throw new TaxRateValidationError(`threshold_ils must be null for non-bracket type ${input.taxType}`)
  }

  try {
    const [row] = await db
      .insert(taxRates)
      .values({
        countryCode: input.countryCode,
        taxType: input.taxType,
        rate: input.rate,
        effectiveFrom: input.effectiveFrom,
        thresholdIls: input.thresholdIls ?? null,
        notes: input.notes ?? null,
        createdBy: input.createdBy,
      })
      .returning({ id: taxRates.id })

    return { id: row!.id }
  } catch (err: unknown) {
    // Drizzle surfaces Postgres unique violation as error with code '23505'
    if (
      err &&
      typeof err === 'object' &&
      'code' in err &&
      (err as { code: string }).code === '23505'
    ) {
      throw new TaxRateConflictError(
        `A rate for (${input.countryCode}, ${input.taxType}, ${input.effectiveFrom}) already exists`,
      )
    }
    throw err
  }
}

// ── Immutability guard ────────────────────────────────────────────────────────

export class TaxRateImmutableError extends Error {
  constructor() {
    super('Past or current tax rates cannot be modified; add a new future-dated entry instead')
    this.name = 'TaxRateImmutableError'
  }
}

/**
 * Throws TaxRateImmutableError if effective_from <= today (past rates are
 * immutable; corrections are new future entries).
 */
export function assertTaxRateMutable(effectiveFrom: string): void {
  const today = new Date().toISOString().slice(0, 10)
  if (effectiveFrom <= today) {
    throw new TaxRateImmutableError()
  }
}

// ── VAT read-only projection ──────────────────────────────────────────────────

export interface VatRateAdminRow {
  countryCode: string
  effectiveFrom: string
  rate: string
}

/**
 * Returns all VAT rate rows for a country, reverse-chronological.
 * Read-only — VAT is owned by system-i18n's `vat_rates` table.
 */
export async function listVatRatesForAdmin(
  db: Db,
  countryCode: string,
): Promise<VatRateAdminRow[]> {
  return db
    .select({
      countryCode: vatRates.countryCode,
      effectiveFrom: vatRates.effectiveFrom,
      rate: vatRates.rate,
    })
    .from(vatRates)
    .where(eq(vatRates.countryCode, countryCode))
    .orderBy(desc(vatRates.effectiveFrom))
}