/* RAIET v1.35 — minimal admin / moderation foundation.
   Run once after 018_account_management.sql. */

CREATE TABLE IF NOT EXISTS security_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    actor_user_id BIGINT UNSIGNED NULL,
    event_type VARCHAR(48) NOT NULL,
    target_user_id BIGINT UNSIGNED NULL,
    target_lobby_id BIGINT UNSIGNED NULL,
    detail VARCHAR(190) NOT NULL DEFAULT '',
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_security_events_created (created_at),
    KEY idx_security_events_actor (actor_user_id, created_at),
    KEY idx_security_events_target_user (target_user_id, created_at),
    CONSTRAINT fk_security_events_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_events_target_user FOREIGN KEY (target_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_security_events_target_lobby FOREIGN KEY (target_lobby_id) REFERENCES lobbies(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE matches
    MODIFY COLUMN end_reason ENUM('host_end','not_enough_players','legacy_closed','moderator_closed') NULL;
