-- =============================================================================
-- init.sql — Script de création du schéma GestHub (MariaDB / InnoDB)
-- =============================================================================
-- Respecte la 3ème forme normale (3NF) : aucune donnée redondante, toutes les
-- dépendances transitives éliminées. La référence aux utilisateurs se fait
-- uniquement via `sub` (identifiant OIDC Keycloak), sans duplication des
-- données du fournisseur d'identité (Bloc 2 - C4).
--
-- Moteur InnoDB : transactions ACID, contraintes de clé étrangère.
-- Exécuté automatiquement au premier démarrage du conteneur MariaDB
-- (monté sur /docker-entrypoint-initdb.d/init.sql, voir docker-compose.yml).
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 1;

-- -----------------------------------------------------------------------------
-- Table : annonces
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS annonces (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titre         VARCHAR(150)      NOT NULL,
    contenu       TEXT              NOT NULL,
    epinglee      TINYINT(1)        NOT NULL DEFAULT 0,
    auteur_sub    VARCHAR(64)       NOT NULL,      -- sub OIDC Keycloak (pas de FK vers Keycloak)
    created_at    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_annonces_auteur (auteur_sub),
    INDEX idx_annonces_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- Table : fichiers
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS fichiers (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom_original   VARCHAR(255)     NOT NULL,
    nom_stocke     VARCHAR(64)      NOT NULL UNIQUE,  -- nom UUID sur disque (anti path traversal)
    mime_type      VARCHAR(127)     NOT NULL,
    taille_octets  BIGINT UNSIGNED  NOT NULL,          -- BIGINT : fichiers volumineux possibles
    uploader_sub   VARCHAR(64)      NOT NULL,
    created_at     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fichiers_uploader (uploader_sub)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- Table : evenements (module planning)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS evenements (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    titre         VARCHAR(150)      NOT NULL,
    description   TEXT              NULL,
    date_debut    DATETIME          NOT NULL,
    date_fin      DATETIME          NOT NULL,
    createur_sub  VARCHAR(64)       NOT NULL,
    created_at    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_evenements_dates CHECK (date_fin >= date_debut),
    INDEX idx_evenements_dates (date_debut, date_fin)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- Table : blocks (widgets du tableau de bord — module historique, conservé)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS blocks (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    block_type    VARCHAR(50)       NOT NULL,   -- 'iframe' | 'buttons' | 'html'
    column_name   VARCHAR(20)       NOT NULL,   -- 'left' | 'center' | 'right'
    position      INT UNSIGNED      NOT NULL DEFAULT 0,
    data          TEXT              NULL,       -- JSON sérialisé (url, titre, liens...)
    created_at    DATETIME          NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- Table : audit_log (traçabilité des actions sensibles — Bloc 1 - C10)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS audit_log (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    action         VARCHAR(50)      NOT NULL,   -- CREATE_ANNONCE, DELETE_FILE, LOGIN, LOGOUT, REFUSE...
    ressource      VARCHAR(150)     NULL,       -- ex. 'annonce:42', 'fichier:uuid'
    statut         VARCHAR(20)      NOT NULL,   -- SUCCESS | REFUSED | ERROR
    utilisateur_sub VARCHAR(64)     NULL,
    ip_address     VARCHAR(45)      NULL,       -- IPv4 ou IPv6
    created_at     DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_action (action),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------------------------
-- Projection méthodologique (non implémentée) : trigger de traçabilité des
-- modifications directes en base, hors application (voir Bloc 1 - C10) :
--
-- DELIMITER $$
-- CREATE TRIGGER trg_annonces_after_update
-- AFTER UPDATE ON annonces
-- FOR EACH ROW
-- BEGIN
--     INSERT INTO audit_log (action, ressource, statut, utilisateur_sub)
--     VALUES ('DIRECT_UPDATE', CONCAT('annonce:', NEW.id), 'SUCCESS', NULL);
-- END$$
-- DELIMITER ;
-- -----------------------------------------------------------------------------