-- 032_tax_location_v2.sql
-- Two-evidence VAT/location model + retained review audit.

ALTER TABLE pending_orders
  ADD COLUMN IF NOT EXISTS tax_snapshot JSONB;

ALTER TABLE payments
  ADD COLUMN IF NOT EXISTS tax_evidence JSONB,
  ADD COLUMN IF NOT EXISTS tax_reviewed_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS tax_reviewed_by UUID REFERENCES staff_users(staff_id),
  ADD COLUMN IF NOT EXISTS tax_review_decision TEXT,
  ADD COLUMN IF NOT EXISTS tax_review_reason TEXT;

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'
  )
);

ALTER TABLE payments DROP CONSTRAINT IF EXISTS payments_tax_review_decision_check;
ALTER TABLE payments ADD CONSTRAINT payments_tax_review_decision_check CHECK (
  tax_review_decision IS NULL OR tax_review_decision IN (
    'confirm_gb',
    'confirm_non_gb',
    'leave_unresolved'
  )
);

CREATE TABLE IF NOT EXISTS tax_review_audit (
  audit_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payment_id UUID NOT NULL REFERENCES payments(payment_id),
  customer_id UUID REFERENCES customers(customer_id),
  order_id TEXT,
  staff_id UUID REFERENCES staff_users(staff_id),
  staff_email TEXT,
  decision TEXT NOT NULL CHECK (decision IN ('confirm_gb','confirm_non_gb','leave_unresolved')),
  previous_tax_jurisdiction CHAR(2),
  new_tax_jurisdiction CHAR(2),
  previous_tax_review_required BOOLEAN,
  new_tax_review_required BOOLEAN,
  reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tax_review_audit_payment
  ON tax_review_audit (payment_id, created_at DESC);

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