petitspas/database/migrations/02_indexes.sql

-- =============================================
-- 02_indexes.sql : Index FK + colonnes critiques
-- =============================================

-- Recommandation : exécuter après 01_init.sql

-- ===========
-- UTILISATEURS
-- ===========
-- Recherche par email (insensibilité à la casse pour lookup)
CREATE INDEX IF NOT EXISTS idx_utilisateurs_lower_courriel
  ON utilisateurs (LOWER(courriel));

-- ===========
-- ASSISTANTES_MATERNELLES
-- ===========
-- FK -> utilisateurs(id)
CREATE INDEX IF NOT EXISTS idx_am_id_utilisateur
  ON assistantes_maternelles (id_utilisateur);

-- =======
-- PARENTS
-- =======
-- FK -> utilisateurs(id)
CREATE INDEX IF NOT EXISTS idx_parents_id_utilisateur
  ON parents (id_utilisateur);

-- Co-parent (nullable)
CREATE INDEX IF NOT EXISTS idx_parents_id_co_parent
  ON parents (id_co_parent);

-- =======
-- ENFANTS
-- =======
-- (souvent filtrés par statut / date_naissance ? à activer si besoin)
-- CREATE INDEX IF NOT EXISTS idx_enfants_statut ON enfants (statut);
-- CREATE INDEX IF NOT EXISTS idx_enfants_date_naissance ON enfants (date_naissance);

-- ================
-- ENFANTS_PARENTS (N:N)
-- ================
-- PK composite déjà en place (id_parent, id_enfant), ajouter index individuels si jointures unilatérales fréquentes
CREATE INDEX IF NOT EXISTS idx_enfants_parents_id_parent
  ON enfants_parents (id_parent);

CREATE INDEX IF NOT EXISTS idx_enfants_parents_id_enfant
  ON enfants_parents (id_enfant);

-- ========
-- DOSSIERS
-- ========
-- FK -> parent / enfant
CREATE INDEX IF NOT EXISTS idx_dossiers_id_parent
  ON dossiers (id_parent);

CREATE INDEX IF NOT EXISTS idx_dossiers_id_enfant
  ON dossiers (id_enfant);

-- Statut (filtrages "à traiter", "envoyé", etc.)
CREATE INDEX IF NOT EXISTS idx_dossiers_statut
  ON dossiers (statut);

-- JSONB : si on fait des requêtes @> sur le planning souhaité
-- CREATE INDEX IF NOT EXISTS idx_dossiers_planning_souhaite_gin
--   ON dossiers USING GIN (planning_souhaite);

-- ========
-- MESSAGES
-- ========
-- Filtrage par dossier + tri chronologique
CREATE INDEX IF NOT EXISTS idx_messages_id_dossier_cree_le
  ON messages (id_dossier, cree_le);

-- Recherche par expéditeur
CREATE INDEX IF NOT EXISTS idx_messages_id_expediteur_cree_le
  ON messages (id_expediteur, cree_le);

-- =========
-- CONTRATS
-- =========
-- UNIQUE(id_dossier) existe déjà -> index implicite
-- Tri / filtres fréquents
CREATE INDEX IF NOT EXISTS idx_contrats_statut
  ON contrats (statut);

-- JSONB planning : activer si on requête par clé
-- CREATE INDEX IF NOT EXISTS idx_contrats_planning_gin
--   ON contrats USING GIN (planning);

-- ==================
-- AVENANTS_CONTRATS
-- ==================
CREATE INDEX IF NOT EXISTS idx_avenants_contrats_id_contrat_cree_le
  ON avenants_contrats (id_contrat, cree_le);

CREATE INDEX IF NOT EXISTS idx_avenants_contrats_initie_par
  ON avenants_contrats (initie_par);

CREATE INDEX IF NOT EXISTS idx_avenants_contrats_statut
  ON avenants_contrats (statut);

-- =========
-- EVENEMENTS
-- =========
-- Accès par enfant + période
CREATE INDEX IF NOT EXISTS idx_evenements_id_enfant_date_debut
  ON evenements (id_enfant, date_debut);

-- Filtrage par auteur / AM / parent
CREATE INDEX IF NOT EXISTS idx_evenements_cree_par
  ON evenements (cree_par);

CREATE INDEX IF NOT EXISTS idx_evenements_id_am
  ON evenements (id_am);

CREATE INDEX IF NOT EXISTS idx_evenements_id_parent
  ON evenements (id_parent);

CREATE INDEX IF NOT EXISTS idx_evenements_type
  ON evenements (type);

CREATE INDEX IF NOT EXISTS idx_evenements_statut
  ON evenements (statut);

-- =================
-- SIGNALEMENTS_BUGS
-- =================
CREATE INDEX IF NOT EXISTS idx_signalements_bugs_id_utilisateur_cree_le
  ON signalements_bugs (id_utilisateur, cree_le);

-- =======
-- UPLOADS
-- =======
CREATE INDEX IF NOT EXISTS idx_uploads_id_utilisateur_cree_le
  ON uploads (id_utilisateur, cree_le);

-- =============
-- NOTIFICATIONS
-- =============
-- Requêtes fréquentes : non lues + ordre chrono
CREATE INDEX IF NOT EXISTS idx_notifications_user_lu_cree_le
  ON notifications (id_utilisateur, lu, cree_le);

-- Option : index partiel pour "non lues"
-- CREATE INDEX IF NOT EXISTS idx_notifications_non_lues
--   ON notifications (id_utilisateur, cree_le)
--   WHERE lu = false;

-- ===========
-- VALIDATIONS
-- ===========
-- Requêtes par utilisateur validé, par statut et par date
CREATE INDEX IF NOT EXISTS idx_validations_id_utilisateur_cree_le
  ON validations (id_utilisateur, cree_le);

CREATE INDEX IF NOT EXISTS idx_validations_statut
  ON validations (statut);