-- Jetons d'activation et de reinitialisation.
--
-- Seul le SHA-256 est stocke : le jeton en clair ne vit que dans l'URL remise
-- au titulaire. Une fuite de cette table ne permet pas de prendre un compte.

CREATE TABLE jetons_acces (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    utilisateur_id BIGINT UNSIGNED NOT NULL,
    jeton_hash     CHAR(64)        NOT NULL,
    type           ENUM('activation','reinitialisation') NOT NULL,
    expire_le      DATETIME        NOT NULL,
    utilise_le     DATETIME        NULL,
    cree_le        DATETIME        NOT NULL,

    PRIMARY KEY (id),
    UNIQUE KEY uq_jetons_hash (jeton_hash),
    KEY idx_jetons_utilisateur (utilisateur_id, type),

    CONSTRAINT fk_jetons_utilisateur
        FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Tentatives de connexion : substitut de Redis pour la limitation de debit,
-- puisque le mutualise n'offre aucun magasin en memoire. A purger par le cron.

CREATE TABLE tentatives_connexion (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    email      VARCHAR(190)    NOT NULL,
    ip         VARCHAR(45)     NOT NULL,
    reussie    TINYINT(1)      NOT NULL DEFAULT 0,
    created_at DATETIME        NOT NULL,

    PRIMARY KEY (id),
    KEY idx_tentatives_email (email, created_at),
    KEY idx_tentatives_ip (ip, created_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Journal d'audit. acteur_id passe a NULL si le compte disparait, mais la
-- ligne de journal ne bouge jamais.
--
-- details est en TEXT et non JSON : MariaDB alias JSON vers LONGTEXT de toute
-- facon, et cela reste lisible dans phpMyAdmin.

CREATE TABLE journal_audit (
    id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    acteur_id  BIGINT UNSIGNED NULL,
    action     VARCHAR(60)     NOT NULL,
    cible_type VARCHAR(60)     NULL,
    cible_id   BIGINT UNSIGNED NULL,
    details    TEXT            NULL,
    ip         VARCHAR(45)     NULL,
    created_at DATETIME        NOT NULL,

    PRIMARY KEY (id),
    KEY idx_audit_cible (cible_type, cible_id),
    KEY idx_audit_date (created_at),

    CONSTRAINT fk_audit_acteur
        FOREIGN KEY (acteur_id) REFERENCES utilisateurs (id) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
