-- VAT-inclusive calculation and two-source customer-location evidence.
-- Billing address and Stripe payment country are the primary evidence.
-- IP country is optional additional evidence and must come from a trusted proxy.
ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS net_amount_pence INTEGER,
  ADD COLUMN IF NOT EXISTS tax_amount_pence INTEGER,
  ADD COLUMN IF NOT EXISTS tax_rate_bps INTEGER,
  ADD COLUMN IF NOT EXISTS tax_jurisdiction CHAR(2),
  ADD COLUMN IF NOT EXISTS tax_calculation_method TEXT,
  ADD COLUMN IF NOT EXISTS billing_address JSONB,
  ADD COLUMN IF NOT EXISTS billing_country CHAR(2),
  ADD COLUMN IF NOT EXISTS payment_country CHAR(2),
  ADD COLUMN IF NOT EXISTS ip_country CHAR(2),
  ADD COLUMN IF NOT EXISTS tax_location_status TEXT,
  ADD COLUMN IF NOT EXISTS tax_review_required BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS tax_evidence_checked_at TIMESTAMPTZ;

ALTER TABLE payments DROP CONSTRAINT IF EXISTS payments_tax_amounts_check;
ALTER TABLE payments ADD CONSTRAINT payments_tax_amounts_check CHECK (
  (net_amount_pence IS NULL AND tax_amount_pence IS NULL)
  OR (net_amount_pence >= 0 AND tax_amount_pence >= 0 AND net_amount_pence + tax_amount_pence = amount_pence)
);

ALTER TABLE payments DROP CONSTRAINT IF EXISTS payments_tax_location_status_check;
ALTER TABLE payments ADD CONSTRAINT payments_tax_location_status_check CHECK (
  tax_location_status IS NULL OR tax_location_status IN (
    'match_billing_ip',
    'match_billing_payment',
    'match_ip_payment',
    'unresolved_conflict',
    'incomplete',
    'physical_uk_delivery',
    'physical_non_uk_delivery',
    'match',
    'conflict'
  )
);

CREATE INDEX IF NOT EXISTS idx_payments_tax_review
  ON payments (tax_review_required, paid_at DESC)
  WHERE tax_review_required = TRUE;
