-- ================================================================================================
-- Campus Safe API - Database Schema (PostgreSQL) - VERSION CONSOLIDÉE
-- ================================================================================================
-- Génère la base depuis zéro. Inclut toutes les migrations des scripts 02/03/04 précédents.
-- Run with: psql -h localhost -U postgres -d campus_safe -f scripts/01_create_database.sql
--
-- Tables (dans l'ordre de dépendance) :
--   contacts_urgence, faq, roles, utilisateurs, sessions, preferences,
--   calendriers, equipe, guest_sessions, invitations,
--   postes, commentaires, reactions,
--   signalements, signalement_pieces_jointes, signalement_timeline, notes_internes,
--   discussions, messages,
--   inscriptions_evenements, rapports, ressources,
--   guest_merge_audit
-- ================================================================================================

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- ----------------------------------------------------------------
-- Tables sans dépendances externes
-- ----------------------------------------------------------------

CREATE TABLE contacts_urgence (
    id          UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    nom         VARCHAR(255) NOT NULL,
    telephone   VARCHAR(30)  NOT NULL,
    email       VARCHAR(255),
    description TEXT,
    categorie   VARCHAR(100),
    est_actif   BOOLEAN NOT NULL DEFAULT TRUE,
    ordre       INTEGER NOT NULL DEFAULT 0,
    date_creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX ix_contacts_urgence_categorie ON contacts_urgence (categorie);
CREATE INDEX ix_contacts_urgence_ordre     ON contacts_urgence (ordre);
CREATE INDEX ix_contacts_urgence_est_actif ON contacts_urgence (est_actif);


CREATE TABLE faq (
    id           UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    question     VARCHAR(500) NOT NULL,
    reponse      TEXT NOT NULL,
    categorie    VARCHAR(100) NOT NULL,
    ordre        INTEGER NOT NULL DEFAULT 0,
    est_publie   BOOLEAN NOT NULL DEFAULT TRUE,
    date_creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE INDEX ix_faq_categorie ON faq (categorie);


CREATE TABLE roles (
    id           UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    nom          VARCHAR(50)  NOT NULL,
    description  TEXT NOT NULL,
    permissions  JSON NOT NULL DEFAULT '[]',
    date_creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_roles_nom ON roles (nom);


-- ----------------------------------------------------------------
-- utilisateurs (aucune FK vers d'autres tables métier)
-- ----------------------------------------------------------------

CREATE TABLE utilisateurs (
    id                  UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    email               VARCHAR(255) NOT NULL,
    mot_de_passe_hash   VARCHAR(255) NOT NULL,
    nom                 VARCHAR(100) NOT NULL,
    prenom              VARCHAR(100) NOT NULL,
    -- Valeurs: UTILISATEUR | GESTIONNAIRE | ADMIN_SYSTEME
    type_utilisateur    VARCHAR(20)  NOT NULL DEFAULT 'UTILISATEUR',
    bureau_genre_id     VARCHAR(100),
    telephone           VARCHAR(20),
    filiere             VARCHAR(150),
    campus              VARCHAR(150),
    avatar_url          VARCHAR(500),
    est_actif           BOOLEAN NOT NULL DEFAULT TRUE,
    premiere_connexion  BOOLEAN NOT NULL DEFAULT TRUE,
    date_creation       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification   TIMESTAMP WITH TIME ZONE,
    derniere_connexion  TIMESTAMP WITH TIME ZONE
);

CREATE UNIQUE INDEX ix_utilisateurs_email           ON utilisateurs (email);
CREATE INDEX        ix_utilisateurs_type_utilisateur ON utilisateurs (type_utilisateur);


-- ----------------------------------------------------------------
-- Dépendent de utilisateurs uniquement
-- ----------------------------------------------------------------

CREATE TABLE sessions (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id  UUID NOT NULL,
    jti             VARCHAR(100) NOT NULL,
    est_actif       BOOLEAN NOT NULL DEFAULT TRUE,
    date_creation   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_expiration TIMESTAMP WITH TIME ZONE NOT NULL,
    appareil        VARCHAR(100),
    localisation    VARCHAR(200),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_sessions_jti            ON sessions (jti);
CREATE INDEX        ix_sessions_utilisateur_id ON sessions (utilisateur_id);


CREATE TABLE preferences (
    id                      UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id          UUID NOT NULL,
    notifications_activees  BOOLEAN NOT NULL DEFAULT TRUE,
    notifications_email     BOOLEAN NOT NULL DEFAULT TRUE,
    notifications_push      BOOLEAN NOT NULL DEFAULT TRUE,
    -- Valeurs langue: fr | en
    langue                  VARCHAR(10)  NOT NULL DEFAULT 'fr',
    -- Valeurs theme: light | dark
    theme                   VARCHAR(20)  NOT NULL DEFAULT 'light',
    parametres              JSON,
    date_modification       TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ix_preferences_utilisateur_id ON preferences (utilisateur_id);


CREATE TABLE invitations (
    id               UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    email            VARCHAR(255) NOT NULL,
    -- Valeurs: UTILISATEUR | GESTIONNAIRE | ADMIN_SYSTEME
    type_utilisateur VARCHAR(50) NOT NULL,
    -- Valeurs: PENDING | ACCEPTED | EXPIRED | CANCELLED
    statut           VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    invite_par_id    UUID,
    date_creation    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_expiration  TIMESTAMP WITH TIME ZONE NOT NULL,
    date_acceptation TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (invite_par_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_invitations_email         ON invitations (email);
CREATE INDEX ix_invitations_invite_par_id ON invitations (invite_par_id);
CREATE INDEX ix_invitations_statut        ON invitations (statut);


CREATE TABLE equipe (
    id               UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    utilisateur_id   UUID,
    email            VARCHAR(255) NOT NULL,
    nom              VARCHAR(100) NOT NULL,
    prenom           VARCHAR(100) NOT NULL,
    -- Valeurs: GESTIONNAIRE | ADMIN_SYSTEME
    type_utilisateur VARCHAR(20)  NOT NULL,
    est_actif        BOOLEAN NOT NULL DEFAULT TRUE,
    perimetre_assigne VARCHAR(255),
    date_creation    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX ix_equipe_email         ON equipe (email);
CREATE INDEX        ix_equipe_utilisateur_id ON equipe (utilisateur_id);


CREATE TABLE calendriers (
    id                    UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    titre                 VARCHAR(255) NOT NULL,
    description           TEXT NOT NULL,
    -- Valeurs: CONFERENCE | ATELIER | CAMPAGNE | PERMANENCE | FORMATION | AUTRE
    type                  VARCHAR(50)  NOT NULL,
    -- Valeurs: BROUILLON | PUBLIE | ANNULE | TERMINE
    statut                VARCHAR(20)  NOT NULL DEFAULT 'BROUILLON',
    date_debut            TIMESTAMP WITH TIME ZONE NOT NULL,
    date_fin              TIMESTAMP WITH TIME ZONE NOT NULL,
    lieu                  VARCHAR(300) NOT NULL,
    est_en_ligne          BOOLEAN NOT NULL DEFAULT FALSE,
    lien_en_ligne         VARCHAR(500),
    nb_max_participants   INTEGER,
    nb_participants_actuels INTEGER NOT NULL DEFAULT 0,
    image_url             VARCHAR(500),
    tags                  JSON NOT NULL DEFAULT '[]',
    public_cible          JSON NOT NULL DEFAULT '[]',
    organisateur_id       UUID,
    date_creation         TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification     TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (organisateur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_calendriers_organisateur_id ON calendriers (organisateur_id);
CREATE INDEX ix_calendriers_statut          ON calendriers (statut);


-- ----------------------------------------------------------------
-- guest_sessions (dépend de utilisateurs)
-- ----------------------------------------------------------------

CREATE TABLE guest_sessions (
    id                    UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    -- Valeurs: ACTIVE | MERGED | EXPIRED | BLOCKED
    statut                VARCHAR(20)  NOT NULL DEFAULT 'ACTIVE',
    telephone_hash        VARCHAR(255) NOT NULL,
    telephone_chiffre     VARCHAR(255),
    device_fingerprint    VARCHAR(500),
    date_creation         TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification     TIMESTAMP WITH TIME ZONE,
    last_seen_at          TIMESTAMP WITH TIME ZONE,
    merged_into_user_id   UUID,
    merged_at             TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (merged_into_user_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_guest_sessions_telephone_hash      ON guest_sessions (telephone_hash);
CREATE INDEX ix_guest_sessions_statut              ON guest_sessions (statut);
CREATE INDEX ix_guest_sessions_merged_into_user_id ON guest_sessions (merged_into_user_id);


-- ----------------------------------------------------------------
-- postes, commentaires, reactions (forum/communauté)
-- ----------------------------------------------------------------

CREATE TABLE postes (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    titre           VARCHAR(255) NOT NULL,
    contenu         TEXT NOT NULL,
    auteur_id       UUID,
    -- Valeurs: NOUVEAU | APPROUVER_IA | REJETER_IA | APPROUVER | REJETER | ARCHIVER
    statut          VARCHAR(20) NOT NULL DEFAULT 'NOUVEAU',
    approuver_par_id UUID,
    est_epingle     BOOLEAN NOT NULL DEFAULT FALSE,
    -- Valeurs: INFORMATION | RESSOURCE | TEMOIGNAGE | EDUCATION | EVENEMENT | DISCUSSION
    categorie       VARCHAR(100),
    image_url       VARCHAR(500),
    tags            JSON NOT NULL DEFAULT '[]',
    style_metadata  JSON DEFAULT '{}',
    date_creation   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (auteur_id)        REFERENCES utilisateurs (id) ON DELETE SET NULL,
    FOREIGN KEY (approuver_par_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_postes_auteur_id       ON postes (auteur_id);
CREATE INDEX ix_postes_approuver_par_id ON postes (approuver_par_id);
CREATE INDEX ix_postes_statut          ON postes (statut);
CREATE INDEX ix_postes_date_creation   ON postes (date_creation);


CREATE TABLE commentaires (
    id            UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    poste_id      UUID NOT NULL,
    auteur_id     UUID,
    contenu       TEXT NOT NULL,
    date_creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (poste_id)  REFERENCES postes      (id) ON DELETE CASCADE,
    FOREIGN KEY (auteur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_commentaires_poste_id  ON commentaires (poste_id);
CREATE INDEX ix_commentaires_auteur_id ON commentaires (auteur_id);


CREATE TABLE reactions (
    id            UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    poste_id      UUID NOT NULL,
    auteur_id     UUID,
    -- Valeurs: LIKE | HEART | SUPPORT | ANGRY
    type          VARCHAR(30) NOT NULL,
    date_creation TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT uq_reaction_poste_auteur_type UNIQUE (poste_id, auteur_id, type),
    FOREIGN KEY (poste_id)  REFERENCES postes      (id) ON DELETE CASCADE,
    FOREIGN KEY (auteur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_reactions_poste_id  ON reactions (poste_id);
CREATE INDEX ix_reactions_auteur_id ON reactions (auteur_id);


-- ----------------------------------------------------------------
-- signalements et tables associées
-- ----------------------------------------------------------------

CREATE TABLE signalements (
    id                  UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    reference           VARCHAR(50)  NOT NULL,
    titre               VARCHAR(255) NOT NULL,
    description         TEXT NOT NULL,
    -- Valeurs: HARCELEMENT_MORAL | HARCELEMENT_SEXUEL | VBG | DISCRIMINATION | VIOLENCE | AUTRE
    type                VARCHAR(50)  NOT NULL,
    -- Valeurs: NOUVEAU | ASSIGNE | EN_COURS | EN_ATTENTE | TRAITE | CLOTURE
    statut              VARCHAR(20)  NOT NULL DEFAULT 'NOUVEAU',
    est_traite          BOOLEAN NOT NULL DEFAULT FALSE,
    -- Valeurs: ANONYME | NOMINATIF
    mode_identification VARCHAR(20)  NOT NULL DEFAULT 'ANONYME',
    date_incident       TIMESTAMP WITH TIME ZONE,
    lieu_incident       VARCHAR(300),
    auteur_presume      VARCHAR(255),
    a_temoins           BOOLEAN,
    description_temoins TEXT,
    -- Valeurs: BASSE | MOYENNE | HAUTE | CRITIQUE
    priorite            VARCHAR(20),
    -- actor : qui a créé le signalement
    -- Valeurs: AUTH_USER | GUEST
    actor_type          VARCHAR(20)  NOT NULL DEFAULT 'AUTH_USER',
    actor_user_id       UUID,
    actor_guest_id      UUID,
    -- utilisateur_id : référence legacy du soumetteur (AUTH_USER uniquement)
    utilisateur_id      UUID,
    gestionnaire_id     UUID,
    bureau_genre_id     VARCHAR(100),
    date_creation       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification   TIMESTAMP WITH TIME ZONE,
    date_traitement     TIMESTAMP WITH TIME ZONE,
    date_assignation    TIMESTAMP WITH TIME ZONE,
    CONSTRAINT ck_signalements_actor_consistency CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR (actor_type = 'GUEST'  AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    ),
    FOREIGN KEY (actor_user_id)   REFERENCES utilisateurs   (id) ON DELETE SET NULL,
    FOREIGN KEY (actor_guest_id)  REFERENCES guest_sessions  (id) ON DELETE SET NULL,
    FOREIGN KEY (utilisateur_id)  REFERENCES utilisateurs   (id) ON DELETE SET NULL,
    FOREIGN KEY (gestionnaire_id) REFERENCES utilisateurs   (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX ix_signalements_reference     ON signalements (reference);
CREATE INDEX        ix_signalements_statut        ON signalements (statut);
CREATE INDEX        ix_signalements_actor_type    ON signalements (actor_type);
CREATE INDEX        ix_signalements_actor_user_id ON signalements (actor_user_id);
CREATE INDEX        ix_signalements_actor_guest_id ON signalements (actor_guest_id);
CREATE INDEX        ix_signalements_utilisateur_id ON signalements (utilisateur_id);
CREATE INDEX        ix_signalements_gestionnaire_id ON signalements (gestionnaire_id);


CREATE TABLE signalement_pieces_jointes (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    signalement_id  UUID NOT NULL,
    nom_fichier     VARCHAR(255)  NOT NULL,
    type_fichier    VARCHAR(100)  NOT NULL,
    taille_fichier  BIGINT NOT NULL DEFAULT 0,
    url             VARCHAR(1000),
    filepath        VARCHAR(2000),
    file_server_id  VARCHAR(255),
    date_upload     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    FOREIGN KEY (signalement_id) REFERENCES signalements (id) ON DELETE CASCADE
);

CREATE INDEX ix_signalement_pieces_jointes_signalement_id ON signalement_pieces_jointes (signalement_id);


CREATE TABLE signalement_timeline (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    signalement_id  UUID NOT NULL,
    -- Valeurs: CREATION | STATUS_CHANGE | ASSIGNATION | NOTE | PIECE_JOINTE | MESSAGE | CLOTURE
    type_evenement  VARCHAR(100) NOT NULL,
    description     TEXT NOT NULL,
    timestamp       TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    -- Valeurs: AUTH_USER | GUEST
    actor_type      VARCHAR(20)  NOT NULL DEFAULT 'AUTH_USER',
    actor_user_id   UUID,
    actor_guest_id  UUID,
    effectue_par_id UUID,
    effectue_par_nom VARCHAR(200),
    valeur_precedente VARCHAR(100),
    nouvelle_valeur   VARCHAR(100),
    CONSTRAINT ck_signalement_timeline_actor_consistency CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR (actor_type = 'GUEST'  AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    ),
    FOREIGN KEY (signalement_id)  REFERENCES signalements   (id) ON DELETE CASCADE,
    FOREIGN KEY (actor_user_id)   REFERENCES utilisateurs   (id) ON DELETE SET NULL,
    FOREIGN KEY (actor_guest_id)  REFERENCES guest_sessions  (id) ON DELETE SET NULL,
    FOREIGN KEY (effectue_par_id) REFERENCES utilisateurs   (id) ON DELETE SET NULL
);

CREATE INDEX ix_signalement_timeline_signalement_id ON signalement_timeline (signalement_id);
CREATE INDEX ix_signalement_timeline_actor_type     ON signalement_timeline (actor_type);
CREATE INDEX ix_signalement_timeline_actor_user_id  ON signalement_timeline (actor_user_id);
CREATE INDEX ix_signalement_timeline_actor_guest_id ON signalement_timeline (actor_guest_id);


CREATE TABLE notes_internes (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    signalement_id  UUID NOT NULL,
    -- Valeurs: AUTH_USER | GUEST
    actor_type      VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER',
    actor_user_id   UUID,
    actor_guest_id  UUID,
    auteur_id       UUID,
    contenu         TEXT NOT NULL,
    date_creation   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT ck_notes_internes_actor_consistency CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR (actor_type = 'GUEST'  AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    ),
    FOREIGN KEY (signalement_id) REFERENCES signalements  (id) ON DELETE CASCADE,
    FOREIGN KEY (actor_user_id)  REFERENCES utilisateurs  (id) ON DELETE SET NULL,
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions (id) ON DELETE SET NULL,
    FOREIGN KEY (auteur_id)      REFERENCES utilisateurs  (id) ON DELETE SET NULL
);

CREATE INDEX ix_notes_internes_signalement_id ON notes_internes (signalement_id);
CREATE INDEX ix_notes_internes_actor_type     ON notes_internes (actor_type);
CREATE INDEX ix_notes_internes_actor_user_id  ON notes_internes (actor_user_id);
CREATE INDEX ix_notes_internes_actor_guest_id ON notes_internes (actor_guest_id);
CREATE INDEX ix_notes_internes_auteur_id      ON notes_internes (auteur_id);


-- ----------------------------------------------------------------
-- discussions & messages
-- ----------------------------------------------------------------

CREATE TABLE discussions (
    id                   UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    titre                VARCHAR(255),
    sous_titre           VARCHAR(255),
    -- Valeurs: AUTH_USER | GUEST
    actor_type           VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER',
    actor_user_id        UUID,
    actor_guest_id       UUID,
    utilisateur_id       UUID,
    gestionnaire_id      UUID,
    signalement_id       UUID,
    signalement_reference VARCHAR(50),
    dernier_message      TEXT,
    dernier_message_at   TIMESTAMP WITH TIME ZONE,
    non_lus_utilisateur  INTEGER NOT NULL DEFAULT 0,
    non_lus_gestionnaire INTEGER NOT NULL DEFAULT 0,
    est_archivee         BOOLEAN NOT NULL DEFAULT FALSE,
    date_creation        TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT ck_discussions_actor_consistency CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR (actor_type = 'GUEST'  AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    ),
    FOREIGN KEY (actor_user_id)  REFERENCES utilisateurs  (id) ON DELETE SET NULL,
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions (id) ON DELETE SET NULL,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs  (id) ON DELETE CASCADE,
    FOREIGN KEY (gestionnaire_id) REFERENCES utilisateurs (id) ON DELETE SET NULL,
    FOREIGN KEY (signalement_id) REFERENCES signalements  (id) ON DELETE SET NULL
);

CREATE INDEX ix_discussions_actor_type     ON discussions (actor_type);
CREATE INDEX ix_discussions_actor_user_id  ON discussions (actor_user_id);
CREATE INDEX ix_discussions_actor_guest_id ON discussions (actor_guest_id);
CREATE INDEX ix_discussions_utilisateur_id ON discussions (utilisateur_id);
CREATE INDEX ix_discussions_gestionnaire_id ON discussions (gestionnaire_id);
CREATE INDEX ix_discussions_signalement_id ON discussions (signalement_id);


CREATE TABLE messages (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    discussion_id   UUID NOT NULL,
    -- Valeurs: AUTH_USER | GUEST
    actor_type      VARCHAR(20) NOT NULL DEFAULT 'AUTH_USER',
    actor_user_id   UUID,
    actor_guest_id  UUID,
    auteur_id       UUID,
    auteur_nom      VARCHAR(200) NOT NULL,
    contenu         TEXT NOT NULL,
    -- Valeurs: USER | SUPPORT | PRIVATE
    type            VARCHAR(20) NOT NULL DEFAULT 'USER',
    est_lu          BOOLEAN NOT NULL DEFAULT FALSE,
    numero_sequence INTEGER,
    pieces_jointes  JSON NOT NULL DEFAULT '[]',
    date_creation   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT ck_messages_actor_consistency CHECK (
        (actor_type = 'AUTH_USER' AND actor_guest_id IS NULL)
        OR (actor_type = 'GUEST'  AND actor_user_id IS NULL AND actor_guest_id IS NOT NULL)
    ),
    FOREIGN KEY (discussion_id)  REFERENCES discussions   (id) ON DELETE CASCADE,
    FOREIGN KEY (actor_user_id)  REFERENCES utilisateurs  (id) ON DELETE SET NULL,
    FOREIGN KEY (actor_guest_id) REFERENCES guest_sessions (id) ON DELETE SET NULL,
    FOREIGN KEY (auteur_id)      REFERENCES utilisateurs  (id) ON DELETE SET NULL
);

CREATE INDEX ix_messages_discussion_id   ON messages (discussion_id);
CREATE INDEX ix_messages_actor_type      ON messages (actor_type);
CREATE INDEX ix_messages_actor_user_id   ON messages (actor_user_id);
CREATE INDEX ix_messages_actor_guest_id  ON messages (actor_guest_id);
CREATE INDEX ix_messages_auteur_id       ON messages (auteur_id);
CREATE INDEX ix_messages_date_creation   ON messages (date_creation);


-- ----------------------------------------------------------------
-- inscriptions_evenements, rapports, ressources
-- ----------------------------------------------------------------

CREATE TABLE inscriptions_evenements (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    calendrier_id   UUID NOT NULL,
    utilisateur_id  UUID NOT NULL,
    -- Valeurs: INSCRIT | EN_ATTENTE | ANNULE
    statut          VARCHAR(20) NOT NULL DEFAULT 'INSCRIT',
    date_inscription TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT uq_inscription_calendrier_user UNIQUE (calendrier_id, utilisateur_id),
    FOREIGN KEY (calendrier_id)  REFERENCES calendriers  (id) ON DELETE CASCADE,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs (id) ON DELETE CASCADE
);

CREATE INDEX ix_inscriptions_evenements_calendrier_id  ON inscriptions_evenements (calendrier_id);
CREATE INDEX ix_inscriptions_evenements_utilisateur_id ON inscriptions_evenements (utilisateur_id);


CREATE TABLE rapports (
    id              UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    titre           VARCHAR(255) NOT NULL,
    -- Valeurs: SIGNALEMENTS | ACTIVITE | UTILISATEURS | CALENDRIER | PERSONNALISE
    type            VARCHAR(100) NOT NULL,
    -- Valeurs: EN_ATTENTE | EN_COURS | TERMINE | ERREUR
    statut          VARCHAR(20)  NOT NULL DEFAULT 'EN_ATTENTE',
    genere_par_id   UUID,
    parametres      JSON,
    resultats       JSON,
    url_fichier     VARCHAR(500),
    description     TEXT,
    date_creation   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_generation TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (genere_par_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE INDEX ix_rapports_genere_par_id ON rapports (genere_par_id);
CREATE INDEX ix_rapports_statut        ON rapports (statut);


CREATE TABLE ressources (
    id                UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    titre             VARCHAR(255)  NOT NULL,
    description       TEXT,
    -- Valeurs: GUIDE | FORMULAIRE | VIDEO | AUTRE
    categorie         VARCHAR(100),
    bucket_id         VARCHAR(100)  NOT NULL,
    file_id           VARCHAR(100)  NOT NULL,
    file_name         VARCHAR(255)  NOT NULL,
    mime_type         VARCHAR(100)  NOT NULL,
    size_bytes        BIGINT NOT NULL DEFAULT 0,
    public_url        VARCHAR(1000) NOT NULL,
    download_url      VARCHAR(1000),
    est_supprime      BOOLEAN NOT NULL DEFAULT FALSE,
    auteur_id         UUID,
    date_creation     TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    date_modification TIMESTAMP WITH TIME ZONE,
    FOREIGN KEY (auteur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX ix_ressources_file_id     ON ressources (file_id);
CREATE INDEX        ix_ressources_bucket_id   ON ressources (bucket_id);
CREATE INDEX        ix_ressources_categorie   ON ressources (categorie);
CREATE INDEX        ix_ressources_est_supprime ON ressources (est_supprime);
CREATE INDEX        ix_ressources_auteur_id   ON ressources (auteur_id);


-- ----------------------------------------------------------------
-- guest_merge_audit (dépend de guest_sessions + utilisateurs)
-- ----------------------------------------------------------------

CREATE TABLE guest_merge_audit (
    id               UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    guest_id         UUID NOT NULL,
    user_id          UUID NOT NULL,
    merge_request_id VARCHAR(100) NOT NULL,
    summary_json     JSON NOT NULL DEFAULT '[]',
    date_creation    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    CONSTRAINT uq_guest_merge_audit_request UNIQUE (merge_request_id),
    FOREIGN KEY (guest_id) REFERENCES guest_sessions (id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES utilisateurs   (id) ON DELETE CASCADE
);

CREATE INDEX ix_guest_merge_audit_guest_id         ON guest_merge_audit (guest_id);
CREATE INDEX ix_guest_merge_audit_user_id          ON guest_merge_audit (user_id);
CREATE UNIQUE INDEX ix_guest_merge_audit_merge_request_id ON guest_merge_audit (merge_request_id);


-- ------------------------------------------------------------------------------------------------
SELECT 'Database structure created successfully! Tables: ' || COUNT(*)::text AS status
FROM information_schema.tables
WHERE table_schema = current_schema();