-- ============================================================================
-- Live distant (lot 2, synthèse §49) : tickets et sessions d'accès au wasm d'un bateau via b<N>.ship2net.fr.
--
-- Parcours : le portail crée un TICKET (aléatoire, 60 s, usage unique, lié à un ShipMember et un bateau) et
-- redirige vers https://b<N>.ship2net.fr/_auth?ticket=... ; le vérificateur (VPS, port interne) consomme le
-- ticket et crée une SESSION (aléatoire, Duree_session_live heures), portée par un cookie HttpOnly propre au
-- sous-domaine du bateau ; Traefik (ForwardAuth) fait vérifier cette session à chaque requête, avec contrôle de
-- l'accès en base à ce moment-là (un accès suspendu ou révoqué coupe le Live immédiatement).
--
-- Ni le ticket ni la session ne sont stockés en clair : seule leur empreinte SHA-256 l'est (une fuite de la
-- table ne permet pas d'ouvrir de session).
-- ============================================================================

CREATE TABLE TB_Tickets_live (
    ID                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Empreinte           CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'SHA-256 (hexadécimal) du ticket - jamais le ticket lui-même',
    ID_Shipmember       INT(10) UNSIGNED NOT NULL COMMENT 'ShipMember pour qui le ticket a été émis',
    ID_Bateau           INT(10) UNSIGNED NOT NULL COMMENT 'Bateau visé : le ticket n''est valable que sur b<ID_Bateau>.ship2net.fr',
    Expiration          DATETIME NOT NULL COMMENT 'Création + 60 s',
    Date_utilisation    DATETIME NULL DEFAULT NULL COMMENT 'Renseignée à la consommation : un ticket ne sert qu''une fois',
    Date_creation       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ID),
    UNIQUE KEY uk_ticket_empreinte (Empreinte),
    KEY idx_ticket_expiration (Expiration),
    CONSTRAINT fk_ticket_live_shipmember FOREIGN KEY (ID_Shipmember) REFERENCES TB_Shipmembers (ID),
    CONSTRAINT fk_ticket_live_bateau FOREIGN KEY (ID_Bateau) REFERENCES TB_Bateaux (Cle_primaire)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
  COMMENT='Tickets d''ouverture d''une session Live (60 s, usage unique) - synthèse §49';

CREATE TABLE TB_Sessions_live (
    ID                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Empreinte           CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'SHA-256 (hexadécimal) de la valeur du cookie - jamais la valeur elle-même',
    ID_Shipmember       INT(10) UNSIGNED NOT NULL,
    ID_Bateau           INT(10) UNSIGNED NOT NULL COMMENT 'La session n''est valable que sur b<ID_Bateau>.ship2net.fr',
    Expiration          DATETIME NOT NULL COMMENT 'Création + Duree_session_live heures (TB_Configuration)',
    Date_creation       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (ID),
    UNIQUE KEY uk_session_empreinte (Empreinte),
    KEY idx_session_expiration (Expiration),
    CONSTRAINT fk_session_live_shipmember FOREIGN KEY (ID_Shipmember) REFERENCES TB_Shipmembers (ID),
    CONSTRAINT fk_session_live_bateau FOREIGN KEY (ID_Bateau) REFERENCES TB_Bateaux (Cle_primaire)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_uca1400_ai_ci
  COMMENT='Sessions Live ouvertes (cookie HttpOnly par sous-domaine de bateau) - synthèse §49';

INSERT INTO TB_Configuration (Validite, Nom, Description, Valeur)
VALUES (1, 'Duree_session_live',
        'Durée (heures) d''une session Live distante, ouverte depuis le bouton Live du portail', '4');