ALTER TABLE prestataire_demandes
  ADD COLUMN IF NOT EXISTS plan_id UUID REFERENCES abonnements(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS stripe_customer_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_payment_method_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_setup_intent_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_setup_session_id TEXT,
  ADD COLUMN IF NOT EXISTS stripe_subscription_id TEXT,
  ADD COLUMN IF NOT EXISTS payment_setup_status TEXT NOT NULL DEFAULT 'not_required',
  ADD COLUMN IF NOT EXISTS payment_setup_confirmed_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS payment_charge_status TEXT NOT NULL DEFAULT 'not_required';

ALTER TABLE prestataire_demandes
  DROP CONSTRAINT IF EXISTS prestataire_demandes_payment_setup_status_check;

ALTER TABLE prestataire_demandes
  ADD CONSTRAINT prestataire_demandes_payment_setup_status_check
  CHECK (payment_setup_status IN ('not_required', 'pending', 'authorized', 'cancelled', 'failed'));

ALTER TABLE prestataire_demandes
  DROP CONSTRAINT IF EXISTS prestataire_demandes_payment_charge_status_check;

ALTER TABLE prestataire_demandes
  ADD CONSTRAINT prestataire_demandes_payment_charge_status_check
  CHECK (payment_charge_status IN ('not_required', 'pending_approval', 'succeeded', 'failed'));

CREATE INDEX IF NOT EXISTS prestataire_demandes_plan_id_idx
  ON prestataire_demandes (plan_id);