CREATE TABLE IF NOT EXISTS prestataire_demandes (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  first_name          TEXT NOT NULL,
  last_name           TEXT NOT NULL,
  email               TEXT NOT NULL,
  password_hash       TEXT NOT NULL,
  numero_tel          TEXT NOT NULL,
  adresse             TEXT NOT NULL,
  date_naissance      DATE,
  sector              TEXT NOT NULL,
  lieu                TEXT NOT NULL,
  intervient_mineurs  BOOLEAN NOT NULL DEFAULT false,
  statut_juridique    TEXT NOT NULL,
  entreprise_nom      TEXT NOT NULL,
  siret               TEXT NOT NULL,
  assurance_confirmed BOOLEAN NOT NULL DEFAULT false,
  experience          TEXT NOT NULL,
  certif_1            BOOLEAN NOT NULL DEFAULT false,
  certif_2            BOOLEAN NOT NULL DEFAULT false,
  certif_3            BOOLEAN NOT NULL DEFAULT false,
  signature           TEXT NOT NULL,
  documents           JSONB NOT NULL DEFAULT '{}'::jsonb,
  status              TEXT NOT NULL DEFAULT 'pending',
  review_note         TEXT,
  reviewed_at         TIMESTAMPTZ,
  reviewed_by         UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  approved_user_id    UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  approved_provider_id UUID REFERENCES prestataires(id) ON DELETE SET NULL,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT prestataire_demandes_email_status_check CHECK (status IN ('pending', 'approved', 'rejected'))
);

CREATE INDEX IF NOT EXISTS prestataire_demandes_status_idx ON prestataire_demandes (status, created_at DESC);
CREATE INDEX IF NOT EXISTS prestataire_demandes_email_idx ON prestataire_demandes (email);