ALTER TABLE votes
  ADD COLUMN IF NOT EXISTS filter_flagged BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS filter_matches TEXT[] DEFAULT '{}'::text[],
  ADD COLUMN IF NOT EXISTS provider_response TEXT,
  ADD COLUMN IF NOT EXISTS provider_flag_reason TEXT,
  ADD COLUMN IF NOT EXISTS provider_flagged_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS moderation_note TEXT,
  ADD COLUMN IF NOT EXISTS moderated_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS moderated_by UUID REFERENCES utilisateurs(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS votes_filter_flagged_idx ON votes (filter_flagged, date_creation DESC);
CREATE INDEX IF NOT EXISTS votes_provider_flagged_idx ON votes (provider_flagged_at DESC);

CREATE TABLE IF NOT EXISTS contrats (
  id                     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  utilisateur_id         UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  prestataire_id         UUID REFERENCES prestataires(id) ON DELETE SET NULL,
  prestataire_demande_id UUID REFERENCES prestataire_demandes(id) ON DELETE SET NULL,
  titre                  TEXT NOT NULL,
  type_contrat           TEXT NOT NULL DEFAULT 'partenariat',
  statut                 TEXT NOT NULL DEFAULT 'draft',
  fichier_url            TEXT,
  notes                  TEXT,
  signature_name         TEXT,
  signed_at              TIMESTAMPTZ,
  created_by             UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  updated_by             UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT contrats_statut_check CHECK (statut IN ('draft', 'sent', 'signed', 'archived'))
);

CREATE INDEX IF NOT EXISTS contrats_prestataire_idx ON contrats (prestataire_id, created_at DESC);
CREATE INDEX IF NOT EXISTS contrats_utilisateur_idx ON contrats (utilisateur_id, created_at DESC);
CREATE INDEX IF NOT EXISTS contrats_statut_idx ON contrats (statut, created_at DESC);