/* RAIET v1.43 — lobby-scoped ephemeral text + WebRTC voice signaling.
   Run once after 029_cs2_community_public_mode.sql. */

ALTER TABLE lobbies
    ADD COLUMN text_chat_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER note,
    ADD COLUMN voice_chat_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER text_chat_enabled;

CREATE TABLE IF NOT EXISTS lobby_chat_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lobby_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    message_text VARCHAR(600) NOT NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_lobby_chat_lobby_id (lobby_id, id),
    KEY idx_lobby_chat_expires (expires_at),
    KEY idx_lobby_chat_user (user_id, created_at),
    CONSTRAINT fk_lobby_chat_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_chat_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_chat_reports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lobby_id BIGINT UNSIGNED NOT NULL,
    message_id BIGINT UNSIGNED NULL,
    reporter_user_id BIGINT UNSIGNED NULL,
    reported_user_id BIGINT UNSIGNED NULL,
    reporter_username VARCHAR(64) NOT NULL,
    reported_username VARCHAR(64) NOT NULL,
    message_text VARCHAR(600) NOT NULL,
    message_created_at DATETIME NOT NULL,
    status ENUM('pending','reviewed','dismissed','actioned') NOT NULL DEFAULT 'pending',
    reviewed_by_user_id BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_lobby_chat_report_once (message_id, reporter_user_id),
    KEY idx_lobby_chat_reports_status (status, created_at),
    KEY idx_lobby_chat_reports_expires (expires_at),
    KEY idx_lobby_chat_reports_reported (reported_user_id, created_at),
    CONSTRAINT fk_lobby_chat_reports_message FOREIGN KEY (message_id) REFERENCES lobby_chat_messages(id) ON DELETE SET NULL,
    CONSTRAINT fk_lobby_chat_reports_reporter FOREIGN KEY (reporter_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_lobby_chat_reports_reported FOREIGN KEY (reported_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_lobby_chat_reports_reviewer FOREIGN KEY (reviewed_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_voice_presence (
    lobby_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    mic_muted TINYINT(1) NOT NULL DEFAULT 0,
    joined_at DATETIME NOT NULL,
    last_seen_at DATETIME NOT NULL,
    PRIMARY KEY (lobby_id, user_id),
    KEY idx_lobby_voice_presence_seen (last_seen_at),
    KEY idx_lobby_voice_presence_user (user_id, last_seen_at),
    CONSTRAINT fk_lobby_voice_presence_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_voice_presence_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_voice_signals (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lobby_id BIGINT UNSIGNED NOT NULL,
    from_user_id BIGINT UNSIGNED NOT NULL,
    to_user_id BIGINT UNSIGNED NOT NULL,
    signal_type ENUM('offer','answer','ice','bye') NOT NULL,
    payload_json MEDIUMTEXT NOT NULL,
    created_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_lobby_voice_signal_target (lobby_id, to_user_id, id),
    KEY idx_lobby_voice_signal_expires (expires_at),
    KEY idx_lobby_voice_signal_sender (lobby_id, from_user_id, created_at),
    CONSTRAINT fk_lobby_voice_signal_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_voice_signal_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_voice_signal_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
