-- Migration : remplacement de veriff_* et stripe_identity_* par stripe_id_*
-- Les deux flux Stripe Identity fusionnent en une seule vérification.

ALTER TABLE prestataire_kyc
  ADD COLUMN IF NOT EXISTS stripe_id_session_id    TEXT,
  ADD COLUMN IF NOT EXISTS stripe_id_status         TEXT NOT NULL DEFAULT 'not_started',
  ADD COLUMN IF NOT EXISTS stripe_id_verified_at    TIMESTAMPTZ;

-- Migration des données existantes vers les nouvelles colonnes
UPDATE prestataire_kyc SET
  stripe_id_session_id = COALESCE(
    CASE WHEN stripe_identity_status = 'verified' THEN stripe_identity_session_id ELSE NULL END,
    veriff_session_id,
    stripe_identity_session_id
  ),
  stripe_id_status = CASE
    WHEN veriff_status = 'approved' OR stripe_identity_status = 'verified'        THEN 'verified'
    WHEN veriff_status = 'declined'                                                THEN 'requires_input'
    WHEN veriff_status = 'resubmission_requested'                                 THEN 'requires_input'
    WHEN stripe_identity_status = 'requires_input'                                THEN 'requires_input'
    WHEN veriff_status = 'pending'                                                THEN 'pending'
    WHEN stripe_identity_status IN ('processing', 'pending')                      THEN 'pending'
    WHEN veriff_status = 'canceled' OR stripe_identity_status = 'canceled'        THEN 'canceled'
    ELSE 'not_started'
  END,
  stripe_id_verified_at = COALESCE(veriff_verified_at, stripe_identity_verified_at)
WHERE veriff_status IS NOT NULL OR stripe_identity_status IS NOT NULL;

-- Suppression des anciennes colonnes
ALTER TABLE prestataire_kyc
  DROP COLUMN IF EXISTS veriff_session_id,
  DROP COLUMN IF EXISTS veriff_status,
  DROP COLUMN IF EXISTS veriff_verified_at,
  DROP COLUMN IF EXISTS stripe_identity_session_id,
  DROP COLUMN IF EXISTS stripe_identity_status,
  DROP COLUMN IF EXISTS stripe_identity_verified_at;