-- TRAIL DPS 4.2 - LOT 15 - Fil de course OVH
-- Script idempotent : tables nouvelles uniquement, sans destruction de donnees existantes.
-- Mise a jour stabilisation acces : PIN stable, token admin PC COURSE, sessions terrain invalidables.

CREATE TABLE IF NOT EXISTS course_feed_channels (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_identity_hash CHAR(64) NOT NULL,
    license_id INT UNSIGNED NULL,
    license_key VARCHAR(128) NULL,
    place_instance_id VARCHAR(128) NOT NULL,
    event_remote_id VARCHAR(128) NOT NULL,
    event_local_id BIGINT NULL,
    event_name VARCHAR(255) NOT NULL,
    event_start_date DATETIME NULL,
    event_end_date DATETIME NULL,
    channel_token_hash CHAR(64) NOT NULL,
    channel_token_cipher TEXT NULL,
    admin_token_hash CHAR(64) NULL,
    admin_token_cipher TEXT NULL,
    admin_token_created_at DATETIME NULL,
    pin_hash VARCHAR(255) NOT NULL,
    pin_cipher TEXT NULL,
    pin_version INT UNSIGNED NOT NULL DEFAULT 1,
    pin_updated_at DATETIME NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_course_feed_channel_identity (channel_identity_hash),
    KEY idx_course_feed_channel_token (channel_token_hash),
    KEY idx_course_feed_channel_admin_token (admin_token_hash),
    KEY idx_course_feed_channel_tenant (license_id, place_instance_id, event_remote_id),
    KEY idx_course_feed_channel_expiry (status, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_feed_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id BIGINT UNSIGNED NOT NULL,
    session_token_hash CHAR(64) NOT NULL,
    display_name VARCHAR(120) NOT NULL,
    role VARCHAR(32) NOT NULL,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    pin_version INT UNSIGNED NOT NULL DEFAULT 1,
    status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE',
    banned_at DATETIME NULL,
    last_seen_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_course_feed_session_token (session_token_hash),
    KEY idx_course_feed_session_channel_status (channel_id, status, last_seen_at),
    KEY idx_course_feed_session_admin (channel_id, is_admin, role),
    CONSTRAINT fk_course_feed_sessions_channel
        FOREIGN KEY (channel_id) REFERENCES course_feed_channels(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_feed_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id BIGINT UNSIGNED NOT NULL,
    session_id BIGINT UNSIGNED NULL,
    author_name VARCHAR(120) NOT NULL,
    author_role VARCHAR(32) NOT NULL,
    message_text TEXT NOT NULL,
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    pinned_by_session_id BIGINT UNSIGNED NULL,
    pinned_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NULL,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_course_feed_messages_visible (channel_id, deleted_at, is_pinned, expires_at, created_at),
    KEY idx_course_feed_messages_purge (is_pinned, expires_at, deleted_at),
    KEY idx_course_feed_messages_session (session_id),
    CONSTRAINT fk_course_feed_messages_channel
        FOREIGN KEY (channel_id) REFERENCES course_feed_channels(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_course_feed_messages_session
        FOREIGN KEY (session_id) REFERENCES course_feed_sessions(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_course_feed_messages_pinned_by
        FOREIGN KEY (pinned_by_session_id) REFERENCES course_feed_sessions(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_feed_audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id BIGINT UNSIGNED NOT NULL,
    session_id BIGINT UNSIGNED NULL,
    action VARCHAR(64) NOT NULL,
    context_json JSON NULL,
    ip_hash CHAR(64) NULL,
    user_agent_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_course_feed_audit_expiry (expires_at),
    KEY idx_course_feed_audit_channel (channel_id, created_at),
    CONSTRAINT fk_course_feed_audit_channel
        FOREIGN KEY (channel_id) REFERENCES course_feed_channels(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_course_feed_audit_session
        FOREIGN KEY (session_id) REFERENCES course_feed_sessions(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS course_feed_bans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    channel_id BIGINT UNSIGNED NOT NULL,
    session_id BIGINT UNSIGNED NULL,
    display_name VARCHAR(120) NOT NULL,
    reason VARCHAR(255) NULL,
    banned_by_session_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_course_feed_bans_channel (channel_id, expires_at),
    KEY idx_course_feed_bans_session (session_id),
    CONSTRAINT fk_course_feed_bans_channel
        FOREIGN KEY (channel_id) REFERENCES course_feed_channels(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_course_feed_bans_session
        FOREIGN KEY (session_id) REFERENCES course_feed_sessions(id)
        ON DELETE SET NULL,
    CONSTRAINT fk_course_feed_bans_by_session
        FOREIGN KEY (banned_by_session_id) REFERENCES course_feed_sessions(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alter idempotent pour les bases ou les tables course_feed_* existent deja.
SET @db_name := DATABASE();

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN channel_token_cipher TEXT NULL AFTER channel_token_hash', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'channel_token_cipher');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN admin_token_hash CHAR(64) NULL AFTER channel_token_cipher', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'admin_token_hash');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN admin_token_cipher TEXT NULL AFTER admin_token_hash', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'admin_token_cipher');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN admin_token_created_at DATETIME NULL AFTER admin_token_cipher', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'admin_token_created_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN pin_cipher TEXT NULL AFTER pin_hash', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'pin_cipher');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN pin_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER pin_cipher', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'pin_version');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD COLUMN pin_updated_at DATETIME NULL AFTER pin_version', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND COLUMN_NAME = 'pin_updated_at');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_sessions ADD COLUMN is_admin TINYINT(1) NOT NULL DEFAULT 0 AFTER role', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_sessions' AND COLUMN_NAME = 'is_admin');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_sessions ADD COLUMN pin_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER is_admin', 'SELECT 1') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_sessions' AND COLUMN_NAME = 'pin_version');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_channels ADD INDEX idx_course_feed_channel_admin_token (admin_token_hash)', 'SELECT 1') FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_channels' AND INDEX_NAME = 'idx_course_feed_channel_admin_token');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @sql := (SELECT IF(COUNT(*) = 0, 'ALTER TABLE course_feed_sessions ADD INDEX idx_course_feed_session_admin (channel_id, is_admin, role)', 'SELECT 1') FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'course_feed_sessions' AND INDEX_NAME = 'idx_course_feed_session_admin');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
