petitspas/database/BDD.sql

-- ==========================================================
-- P'titsPas — Schéma PostgreSQL (création from scratch)
-- Fichier canonique : toute nouvelle BDD doit partir d'ici.
-- Migrations dans database/migrations/ : BDD existantes uniquement.
-- ==========================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ==========================================================
-- ENUMS
-- ==========================================================
DO $$ BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'role_type') THEN
        CREATE TYPE role_type AS ENUM (
            'parent', 'gestionnaire', 'super_admin', 'administrateur', 'assistante_maternelle'
        );
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'genre_type') THEN
        CREATE TYPE genre_type AS ENUM ('H', 'F', 'Autre');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_utilisateur_type') THEN
        CREATE TYPE statut_utilisateur_type AS ENUM ('en_attente', 'actif', 'suspendu', 'refuse');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_enfant_type') THEN
        CREATE TYPE statut_enfant_type AS ENUM ('a_naitre', 'garde', 'sans_garde', 'scolarise');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_dossier_type') THEN
        CREATE TYPE statut_dossier_type AS ENUM ('envoye', 'accepte', 'refuse');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_contrat_type') THEN
        CREATE TYPE statut_contrat_type AS ENUM (
            'brouillon', 'en_attente_signature', 'valide', 'resilie'
        );
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_avenant_type') THEN
        CREATE TYPE statut_avenant_type AS ENUM ('propose', 'accepte', 'refuse');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'type_evenement_type') THEN
        CREATE TYPE type_evenement_type AS ENUM (
            'absence_enfant', 'conge_am', 'conge_parent', 'arret_maladie_am', 'evenement_rpe'
        );
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_evenement_type') THEN
        CREATE TYPE statut_evenement_type AS ENUM ('propose', 'valide', 'refuse');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'statut_validation_type') THEN
        CREATE TYPE statut_validation_type AS ENUM ('en_attente', 'valide', 'refuse');
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'situation_familiale_type') THEN
        CREATE TYPE situation_familiale_type AS ENUM (
            'celibataire', 'marie', 'concubinage', 'pacse', 'separe', 'divorce', 'veuf', 'parent_isole'
        );
    END IF;
END $$;

-- ==========================================================
-- Table : relais (avant utilisateurs — FK relais_id)
-- ==========================================================
CREATE TABLE relais (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    nom VARCHAR(255) NOT NULL,
    adresse TEXT NOT NULL,
    horaires_ouverture JSONB,
    ligne_fixe VARCHAR(20),
    actif BOOLEAN DEFAULT true,
    notes TEXT,
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : utilisateurs
-- ==========================================================
CREATE TABLE utilisateurs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    password TEXT,
    prenom VARCHAR(100),
    nom VARCHAR(100),
    genre genre_type,
    role role_type NOT NULL,
    statut statut_utilisateur_type DEFAULT 'en_attente',
    telephone VARCHAR(20),
    adresse TEXT,
    date_naissance DATE,
    lieu_naissance_ville VARCHAR(100),
    lieu_naissance_pays VARCHAR(100),
    photo_url TEXT,
    consentement_photo BOOLEAN DEFAULT false,
    date_consentement_photo TIMESTAMPTZ,
    token_creation_mdp VARCHAR(255),
    token_creation_mdp_expire_le TIMESTAMPTZ,
    password_reset_token VARCHAR(255),
    password_reset_expires TIMESTAMPTZ,
    token_reprise VARCHAR(255),
    token_reprise_expire_le TIMESTAMPTZ,
    changement_mdp_obligatoire BOOLEAN DEFAULT false,
    numero_dossier VARCHAR(20),
    cgu_version_acceptee INTEGER,
    cgu_acceptee_le TIMESTAMPTZ,
    privacy_version_acceptee INTEGER,
    privacy_acceptee_le TIMESTAMPTZ,
    relais_id UUID REFERENCES relais(id) ON DELETE SET NULL,
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now(),
    ville VARCHAR(150),
    code_postal VARCHAR(10),
    profession VARCHAR(150),
    situation_familiale situation_familiale_type
);

CREATE INDEX idx_utilisateurs_token_creation_mdp
    ON utilisateurs(token_creation_mdp)
    WHERE token_creation_mdp IS NOT NULL;

CREATE INDEX idx_utilisateurs_password_reset_token
    ON utilisateurs(password_reset_token)
    WHERE password_reset_token IS NOT NULL;

CREATE INDEX idx_utilisateurs_token_reprise
    ON utilisateurs(token_reprise)
    WHERE token_reprise IS NOT NULL;

CREATE INDEX idx_utilisateurs_numero_dossier
    ON utilisateurs(numero_dossier)
    WHERE numero_dossier IS NOT NULL;

-- ==========================================================
-- Table : assistantes_maternelles
-- ==========================================================
CREATE TABLE assistantes_maternelles (
    id_utilisateur UUID PRIMARY KEY REFERENCES utilisateurs(id) ON DELETE CASCADE,
    numero_agrement VARCHAR(50),
    nir_chiffre CHAR(15) NOT NULL,
    nb_max_enfants INT,
    biographie TEXT,
    disponible BOOLEAN DEFAULT true,
    ville_residence VARCHAR(100),
    date_agrement DATE,
    annee_experience SMALLINT,
    specialite VARCHAR(100),
    place_disponible INT,
    numero_dossier VARCHAR(20)
);

CREATE INDEX idx_assistantes_maternelles_numero_dossier
    ON assistantes_maternelles(numero_dossier)
    WHERE numero_dossier IS NOT NULL;

-- ==========================================================
-- Table : parents
-- ==========================================================
CREATE TABLE parents (
    id_utilisateur UUID PRIMARY KEY REFERENCES utilisateurs(id) ON DELETE CASCADE,
    id_co_parent UUID REFERENCES utilisateurs(id),
    numero_dossier VARCHAR(20)
);

CREATE INDEX idx_parents_numero_dossier
    ON parents(numero_dossier)
    WHERE numero_dossier IS NOT NULL;

-- ==========================================================
-- Table : enfants
-- ==========================================================
CREATE TABLE enfants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    statut statut_enfant_type,
    prenom VARCHAR(100),
    nom VARCHAR(100),
    genre genre_type NOT NULL,
    date_naissance DATE,
    date_prevue_naissance DATE,
    photo_url TEXT,
    consentement_photo BOOLEAN DEFAULT false,
    date_consentement_photo TIMESTAMPTZ,
    est_multiple BOOLEAN DEFAULT false
);

-- ==========================================================
-- Table : enfants_parents
-- ==========================================================
CREATE TABLE enfants_parents (
    id_parent UUID REFERENCES parents(id_utilisateur) ON DELETE CASCADE,
    id_enfant UUID REFERENCES enfants(id) ON DELETE CASCADE,
    PRIMARY KEY (id_parent, id_enfant)
);

-- ==========================================================
-- Table : enfants_assistantes_maternelles (placement AM ↔ enfant — #131)
-- ==========================================================
CREATE TABLE enfants_assistantes_maternelles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_am UUID NOT NULL REFERENCES assistantes_maternelles(id_utilisateur) ON DELETE CASCADE,
    id_enfant UUID NOT NULL REFERENCES enfants(id) ON DELETE CASCADE,
    date_debut DATE NOT NULL DEFAULT CURRENT_DATE,
    date_fin DATE NULL,
    cree_le TIMESTAMPTZ NOT NULL DEFAULT now(),
    cree_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL
);

CREATE INDEX idx_eam_am ON enfants_assistantes_maternelles(id_am);
CREATE INDEX idx_eam_enfant ON enfants_assistantes_maternelles(id_enfant);

CREATE UNIQUE INDEX uq_enfant_garde_active
    ON enfants_assistantes_maternelles (id_enfant)
    WHERE date_fin IS NULL;

-- ==========================================================
-- Table : dossier_famille (inscription parent — ticket #119)
-- ==========================================================
CREATE TABLE dossier_famille (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    numero_dossier VARCHAR(20) NOT NULL,
    id_parent UUID NOT NULL REFERENCES parents(id_utilisateur) ON DELETE CASCADE,
    presentation TEXT,
    statut statut_dossier_type NOT NULL DEFAULT 'envoye',
    cree_le TIMESTAMPTZ NOT NULL DEFAULT now(),
    modifie_le TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_dossier_famille_numero ON dossier_famille(numero_dossier);
CREATE INDEX idx_dossier_famille_id_parent ON dossier_famille(id_parent);

-- ==========================================================
-- Table : dossier_famille_enfants
-- ==========================================================
CREATE TABLE dossier_famille_enfants (
    id_dossier_famille UUID NOT NULL REFERENCES dossier_famille(id) ON DELETE CASCADE,
    id_enfant UUID NOT NULL REFERENCES enfants(id) ON DELETE CASCADE,
    PRIMARY KEY (id_dossier_famille, id_enfant)
);

CREATE INDEX idx_dossier_famille_enfants_enfant ON dossier_famille_enfants(id_enfant);

-- ==========================================================
-- Table : dossiers
-- ==========================================================
CREATE TABLE dossiers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_parent UUID REFERENCES parents(id_utilisateur) ON DELETE CASCADE,
    id_enfant UUID REFERENCES enfants(id) ON DELETE CASCADE,
    presentation TEXT,
    type_contrat VARCHAR(50),
    repas BOOLEAN DEFAULT false,
    budget NUMERIC(10,2),
    planning_souhaite JSONB,
    statut statut_dossier_type DEFAULT 'envoye',
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : messages
-- ==========================================================
CREATE TABLE messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_dossier UUID REFERENCES dossiers(id) ON DELETE CASCADE,
    id_expediteur UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    contenu TEXT,
    re_redige_par_ia BOOLEAN DEFAULT false,
    cree_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : contrats
-- ==========================================================
CREATE TABLE contrats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_dossier UUID UNIQUE REFERENCES dossiers(id) ON DELETE CASCADE,
    planning JSONB,
    tarif_horaire NUMERIC(6,2),
    indemnites_repas NUMERIC(6,2),
    date_debut DATE,
    statut statut_contrat_type DEFAULT 'brouillon',
    signe_parent BOOLEAN DEFAULT false,
    signe_am BOOLEAN DEFAULT false,
    finalise_le TIMESTAMPTZ,
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : avenants_contrats
-- ==========================================================
CREATE TABLE avenants_contrats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_contrat UUID REFERENCES contrats(id) ON DELETE CASCADE,
    modifications JSONB,
    initie_par UUID REFERENCES utilisateurs(id),
    statut statut_avenant_type DEFAULT 'propose',
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : evenements
-- ==========================================================
CREATE TABLE evenements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type type_evenement_type,
    id_enfant UUID REFERENCES enfants(id) ON DELETE CASCADE,
    id_am UUID REFERENCES utilisateurs(id),
    id_parent UUID REFERENCES parents(id_utilisateur),
    cree_par UUID REFERENCES utilisateurs(id),
    date_debut TIMESTAMPTZ,
    date_fin TIMESTAMPTZ,
    commentaires TEXT,
    statut statut_evenement_type DEFAULT 'propose',
    delai_grace TIMESTAMPTZ,
    urgent BOOLEAN DEFAULT false,
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : signalements_bugs
-- ==========================================================
CREATE TABLE signalements_bugs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_utilisateur UUID REFERENCES utilisateurs(id),
    description TEXT,
    cree_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : uploads
-- ==========================================================
CREATE TABLE uploads (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_utilisateur UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    fichier_url TEXT NOT NULL,
    type VARCHAR(50),
    cree_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : notifications
-- ==========================================================
CREATE TABLE notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_utilisateur UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    contenu TEXT,
    lu BOOLEAN DEFAULT false,
    cree_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : validations
-- ==========================================================
CREATE TABLE validations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_utilisateur UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    type VARCHAR(50),
    statut statut_validation_type DEFAULT 'en_attente',
    commentaire TEXT,
    valide_par UUID REFERENCES utilisateurs(id) ON DELETE SET NULL,
    cree_le TIMESTAMPTZ DEFAULT now(),
    modifie_le TIMESTAMPTZ DEFAULT now()
);

-- ==========================================================
-- Table : configuration
-- ==========================================================
CREATE TABLE configuration (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cle VARCHAR(100) UNIQUE NOT NULL,
    valeur TEXT,
    type VARCHAR(50) NOT NULL,
    categorie VARCHAR(50),
    description TEXT,
    modifie_le TIMESTAMPTZ DEFAULT now(),
    modifie_par UUID REFERENCES utilisateurs(id)
);

CREATE INDEX idx_configuration_cle ON configuration(cle);
CREATE INDEX idx_configuration_categorie ON configuration(categorie);

INSERT INTO configuration (cle, valeur, type, categorie, description) VALUES
('smtp_host', 'localhost', 'string', 'email', 'Serveur SMTP (ex: mail.mairie-bezons.fr, smtp.gmail.com)'),
('smtp_port', '25', 'number', 'email', 'Port SMTP (25, 465, 587)'),
('smtp_secure', 'false', 'boolean', 'email', 'Utiliser SSL/TLS (true pour port 465)'),
('smtp_auth_required', 'false', 'boolean', 'email', 'Authentification SMTP requise'),
('smtp_user', '', 'string', 'email', 'Utilisateur SMTP (si authentification requise)'),
('smtp_password', '', 'encrypted', 'email', 'Mot de passe SMTP (chiffré en AES-256)'),
('email_from_name', 'P''titsPas', 'string', 'email', 'Nom de l''expéditeur affiché dans les emails'),
('email_from_address', 'no-reply@ptits-pas.fr', 'string', 'email', 'Adresse email de l''expéditeur'),
('app_name', 'P''titsPas', 'string', 'app', 'Nom de l''application (affiché dans l''interface)'),
('app_url', 'https://app.ptits-pas.fr', 'string', 'app', 'URL publique de l''application (pour les liens dans emails)'),
('app_logo_url', '/assets/logo.png', 'string', 'app', 'URL du logo de l''application'),
('setup_completed', 'false', 'boolean', 'app', 'Configuration initiale terminée'),
('password_reset_token_expiry_days', '7', 'number', 'security', 'Durée de validité des tokens de création/réinitialisation de mot de passe (en jours)'),
('jwt_expiry_hours', '24', 'number', 'security', 'Durée de validité des sessions JWT (en heures)'),
('max_upload_size_mb', '5', 'number', 'security', 'Taille maximale des fichiers uploadés (en MB)'),
('bcrypt_rounds', '12', 'number', 'security', 'Nombre de rounds bcrypt pour le hachage des mots de passe');

-- ==========================================================
-- Table : documents_legaux
-- ==========================================================
CREATE TABLE documents_legaux (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type VARCHAR(50) NOT NULL,
    version INTEGER NOT NULL,
    fichier_nom VARCHAR(255) NOT NULL,
    fichier_path VARCHAR(500) NOT NULL,
    fichier_hash VARCHAR(64) NOT NULL,
    actif BOOLEAN DEFAULT false,
    televerse_par UUID REFERENCES utilisateurs(id),
    televerse_le TIMESTAMPTZ DEFAULT now(),
    active_le TIMESTAMPTZ,
    UNIQUE(type, version)
);

CREATE INDEX idx_documents_legaux_type_actif ON documents_legaux(type, actif);
CREATE INDEX idx_documents_legaux_version ON documents_legaux(type, version DESC);

-- ==========================================================
-- Table : acceptations_documents
-- ==========================================================
CREATE TABLE acceptations_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    id_utilisateur UUID REFERENCES utilisateurs(id) ON DELETE CASCADE,
    id_document UUID REFERENCES documents_legaux(id),
    type_document VARCHAR(50) NOT NULL,
    version_document INTEGER NOT NULL,
    accepte_le TIMESTAMPTZ DEFAULT now(),
    ip_address INET,
    user_agent TEXT
);

CREATE INDEX idx_acceptations_utilisateur ON acceptations_documents(id_utilisateur);
CREATE INDEX idx_acceptations_document ON acceptations_documents(id_document);

-- ==========================================================
-- Ticket #103 : Numéro de dossier (AAAA-NNNNNN, séquence par année)
-- ==========================================================
CREATE TABLE numero_dossier_sequence (
    annee INT PRIMARY KEY,
    prochain INT NOT NULL DEFAULT 1
);

-- ==========================================================
-- Seed : Documents légaux génériques v1
-- ==========================================================
INSERT INTO documents_legaux (type, version, fichier_nom, fichier_path, fichier_hash, actif, televerse_le, active_le) VALUES
('cgu', 1, 'cgu_v1_default.pdf', '/documents/legaux/cgu_v1_default.pdf', 'a3f8b2c4d5e6f7a8b9c0d1e2f3a4b5c6d7e8f9a0b1c2d3e4f5a6b7c8d9e0f1a2', true, now(), now()),
('privacy', 1, 'privacy_v1_default.pdf', '/documents/legaux/privacy_v1_default.pdf', 'b4f9c3d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a1b2c3d4e5f6a7b8c9d0e1f2a3b4', true, now(), now());

-- ==========================================================
-- Seed : Super Administrateur par défaut
-- Email: admin@ptits-pas.fr | Mot de passe: 4dm1n1strateur
-- ==========================================================
INSERT INTO utilisateurs (
    email,
    password,
    prenom,
    nom,
    role,
    statut,
    changement_mdp_obligatoire
) VALUES (
    'admin@ptits-pas.fr',
    '$2b$12$plOZCW7lzLFkWgDPcE6p6u10EA4yErQt6Xcp5nyH3Sp/2.6EpNW.6',
    'Super',
    'Administrateur',
    'super_admin',
    'actif',
    true
);