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');
    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');
    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','actif','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 : 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, -- NULL avant création via token
    prenom VARCHAR(100),
    nom VARCHAR(100),
    genre genre_type,
    role role_type NOT NULL,
    statut statut_utilisateur_type DEFAULT 'en_attente',
    telephone VARCHAR(20), -- Unifié (mobile privilégié)
    adresse TEXT,
    date_naissance DATE,
    photo_url TEXT, -- Obligatoire pour AM, non utilisé pour parents
    consentement_photo BOOLEAN DEFAULT false,
    date_consentement_photo TIMESTAMPTZ,
    token_creation_mdp VARCHAR(255), -- Token pour créer MDP après validation
    token_creation_mdp_expire_le TIMESTAMPTZ, -- Expiration 7 jours
    changement_mdp_obligatoire BOOLEAN DEFAULT false,
    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
);

-- Index pour recherche par token
CREATE INDEX idx_utilisateurs_token_creation_mdp 
    ON utilisateurs(token_creation_mdp) 
    WHERE token_creation_mdp 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),
    date_agrement DATE NOT NULL, -- Obligatoire selon CDC v1.3
    nir_chiffre CHAR(15),
    nb_max_enfants INT,
    place_disponible INT, 
    biographie TEXT,
    disponible BOOLEAN DEFAULT true
);

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

-- ==========================================================
-- 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, -- Obligatoire selon CDC
    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 : 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),
    type VARCHAR(50),
    statut statut_validation_type DEFAULT 'en_attente',
    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)
);

-- Index pour performance
CREATE INDEX idx_configuration_cle ON configuration(cle);
CREATE INDEX idx_configuration_categorie ON configuration(categorie);

-- Seed initial de configuration
INSERT INTO configuration (cle, valeur, type, categorie, description) VALUES
-- === Configuration Email (SMTP) ===
('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'),

-- === Configuration Application ===
('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'),

-- === Configuration Sécurité ===
('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, -- 'cgu' ou 'privacy'
    version INTEGER NOT NULL, -- Numéro de version (auto-incrémenté)
    fichier_nom VARCHAR(255) NOT NULL, -- Nom original du fichier
    fichier_path VARCHAR(500) NOT NULL, -- Chemin de stockage
    fichier_hash VARCHAR(64) NOT NULL, -- Hash SHA-256 pour intégrité
    actif BOOLEAN DEFAULT false, -- Version actuellement active
    televerse_par UUID REFERENCES utilisateurs(id), -- Qui a uploadé
    televerse_le TIMESTAMPTZ DEFAULT now(), -- Date d'upload
    active_le TIMESTAMPTZ, -- Date d'activation
    UNIQUE(type, version) -- Pas de doublon version
);

-- Index pour performance
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, -- 'cgu' ou 'privacy'
    version_document INTEGER NOT NULL, -- Version acceptée
    accepte_le TIMESTAMPTZ DEFAULT now(), -- Date d'acceptation
    ip_address INET, -- IP de l'utilisateur (RGPD)
    user_agent TEXT -- Navigateur (preuve)
);

-- Index pour performance
CREATE INDEX idx_acceptations_utilisateur ON acceptations_documents(id_utilisateur);
CREATE INDEX idx_acceptations_document ON acceptations_documents(id_document);

-- ==========================================================
-- Modification Table : utilisateurs (ajout colonnes documents)
-- ==========================================================
ALTER TABLE utilisateurs 
    ADD COLUMN IF NOT EXISTS cgu_version_acceptee INTEGER,
    ADD COLUMN IF NOT EXISTS cgu_acceptee_le TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS privacy_version_acceptee INTEGER,
    ADD COLUMN IF NOT EXISTS privacy_acceptee_le TIMESTAMPTZ;

-- ==========================================================
-- 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());