-- KYC prestataires : Veriff (identité), INSEE (SIRET), casier judiciaire, Stripe Identity (lien de parenté)

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE prestataire_kyc (
  id                              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  prestataire_id                  UUID        NOT NULL REFERENCES prestataires(id) ON DELETE CASCADE,

  -- Vérification identité via Veriff (pièce d'identité)
  veriff_session_id               TEXT,
  veriff_status                   TEXT        NOT NULL DEFAULT 'not_started',
  -- not_started | pending | approved | declined | resubmission_requested
  veriff_verified_at              TIMESTAMPTZ,

  -- Vérification SIRET via INSEE Sirene
  siret                           TEXT,
  siret_status                    TEXT        NOT NULL DEFAULT 'not_started',
  -- not_started | verified | invalid
  siret_verified_at               TIMESTAMPTZ,
  siret_raison_sociale            TEXT,
  siret_code_naf                  TEXT,
  siret_activite                  TEXT,

  -- Casier judiciaire (bucket privé Supabase, jamais exposé publiquement)
  casier_path                     TEXT,
  casier_status                   TEXT        NOT NULL DEFAULT 'not_started',
  -- not_started | pending | validated | rejected
  casier_submitted_at             TIMESTAMPTZ,
  casier_reviewed_at              TIMESTAMPTZ,
  casier_reviewed_by              UUID        REFERENCES utilisateurs(id) ON DELETE SET NULL,

  -- Lien de parenté : Stripe Identity (ID du parent) + upload livret de famille
  stripe_identity_session_id      TEXT,
  stripe_identity_status          TEXT        NOT NULL DEFAULT 'not_started',
  -- not_started | processing | verified | requires_input | canceled
  stripe_identity_verified_at     TIMESTAMPTZ,
  livret_famille_path             TEXT,
  lien_parente_admin_status       TEXT        NOT NULL DEFAULT 'not_started',
  -- not_started | validated | rejected
  lien_parente_reviewed_at        TIMESTAMPTZ,
  lien_parente_reviewed_by        UUID        REFERENCES utilisateurs(id) ON DELETE SET NULL,

  created_at                      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at                      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT prestataire_kyc_prestataire_unique UNIQUE (prestataire_id)
);

CREATE TRIGGER set_prestataire_kyc_updated_at
  BEFORE UPDATE ON prestataire_kyc
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();