-- 0062_stripe_cutover.sql
-- Hard cutover SUMIT → Stripe. No backcompat. Rewritten against real schema.

-- vendors: drop SUMIT (9 cols), add Stripe (6 cols + unique index)
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_company_id;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_api_key_enc;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_terminal_active;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_bank_code;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_branch_code;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_account_last4;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_onboarded_at;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_billing_identifier;
ALTER TABLE vendors DROP COLUMN IF EXISTS sumit_api_public_key;

ALTER TABLE vendors ADD COLUMN stripe_account_id TEXT;
ALTER TABLE vendors ADD COLUMN stripe_charges_enabled BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE vendors ADD COLUMN stripe_payouts_enabled BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE vendors ADD COLUMN stripe_onboarding_state TEXT NOT NULL DEFAULT 'not_started';
ALTER TABLE vendors ADD COLUMN stripe_details_submitted BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE vendors ADD COLUMN stripe_requirements_currently_due JSONB;
CREATE UNIQUE INDEX vendors_stripe_account_id_uniq ON vendors(stripe_account_id) WHERE stripe_account_id IS NOT NULL;

-- purchases: drop SUMIT (7 cols + 1 index), add provider-neutral (6 cols + unique index)
DROP INDEX IF EXISTS purchases_sumit_payment_id_idx;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_payment_id;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_vendor_doc_id;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_vendor_doc_number;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_vendor_receipt_url;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_platform_doc_id;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_platform_doc_number;
ALTER TABLE purchases DROP COLUMN IF EXISTS sumit_platform_receipt_url;

ALTER TABLE purchases ADD COLUMN provider_payment_id TEXT;
ALTER TABLE purchases ADD COLUMN provider_refund_id TEXT;
ALTER TABLE purchases ADD COLUMN vendor_tax_doc_id TEXT;
ALTER TABLE purchases ADD COLUMN platform_tax_doc_id TEXT;
ALTER TABLE purchases ADD COLUMN vendor_tax_doc_pdf_url TEXT;
ALTER TABLE purchases ADD COLUMN platform_tax_doc_pdf_url TEXT;
CREATE UNIQUE INDEX purchases_provider_payment_id_uniq ON purchases(provider_payment_id) WHERE provider_payment_id IS NOT NULL;

-- group_reservations: drop SUMIT j4/j5/vault hold cols (11 total).
-- Existing provider_authorization_id / provider_transaction_id / hold_expires_at / hold_refreshed_at stay.
-- Stripe PaymentIntent.id (manual-capture hold) -> provider_authorization_id; final captured PI/charge -> provider_transaction_id.
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j5_auth_number;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j5_placed_at;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j5_attempt_count;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j5_unique_identifier;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j4_captured_at;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j4_document_id;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS j4_sumit_payment_id;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS vault_token_enc;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS vault_card_exp_month;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS vault_card_exp_year;
ALTER TABLE group_reservations DROP COLUMN IF EXISTS vault_card_pattern;

-- case_resolutions: rename sumit_refund_id -> provider_refund_id
ALTER TABLE case_resolutions DROP COLUMN IF EXISTS sumit_refund_id;
ALTER TABLE case_resolutions ADD COLUMN provider_refund_id TEXT;

-- users: saved-PM bootstrap (2 cols + unique index)
ALTER TABLE users ADD COLUMN stripe_customer_id TEXT;
ALTER TABLE users ADD COLUMN default_payment_method_id TEXT;
CREATE UNIQUE INDEX users_stripe_customer_id_uniq ON users(stripe_customer_id) WHERE stripe_customer_id IS NOT NULL;

-- webhook event dedup
CREATE TABLE stripe_webhook_events (
  event_id TEXT PRIMARY KEY,
  event_type TEXT NOT NULL,
  received_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  processed_at TIMESTAMPTZ
);
