/* RAIET v1.26 — real match history foundation.
   Run once after 009_presence_heartbeat.sql. */

CREATE TABLE IF NOT EXISTS matches (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    lobby_id BIGINT UNSIGNED NULL,
    owner_user_id BIGINT UNSIGNED NULL,
    game_key VARCHAR(32) NOT NULL,
    region VARCHAR(32) NOT NULL DEFAULT '',
    location_text VARCHAR(120) NOT NULL DEFAULT '',
    owner_display_name VARCHAR(80) NOT NULL DEFAULT '',
    status ENUM('live','completed') NOT NULL DEFAULT 'live',
    started_at DATETIME NOT NULL,
    ended_at DATETIME NULL,
    end_reason ENUM('host_end','not_enough_players','legacy_closed') NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_matches_lobby (lobby_id),
    KEY idx_matches_status_started (status, started_at),
    KEY idx_matches_completed (status, ended_at),
    CONSTRAINT fk_matches_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE SET NULL,
    CONSTRAINT fk_matches_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS match_players (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    match_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    username_snapshot VARCHAR(32) NOT NULL,
    display_name_snapshot VARCHAR(80) NOT NULL,
    role ENUM('owner','member') NOT NULL DEFAULT 'member',
    PRIMARY KEY (id),
    UNIQUE KEY uq_match_player (match_id, user_id),
    KEY idx_match_players_match (match_id),
    CONSTRAINT fk_match_players_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE,
    CONSTRAINT fk_match_players_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/* Preserve any match that happened to be live while this migration is installed. */
INSERT IGNORE INTO matches
    (lobby_id, owner_user_id, game_key, region, location_text, owner_display_name, status, started_at)
SELECT l.id,
       l.owner_user_id,
       l.game_key,
       l.region,
       COALESCE(up.location_text, ''),
       COALESCE(NULLIF(up.display_name, ''), u.username),
       'live',
       COALESCE(l.match_started_at, l.updated_at)
FROM lobbies l
INNER JOIN users u ON u.id = l.owner_user_id
LEFT JOIN user_profiles up ON up.user_id = u.id
WHERE l.status = 'in_match';

INSERT IGNORE INTO match_players
    (match_id, user_id, username_snapshot, display_name_snapshot, role)
SELECT m.id,
       lm.user_id,
       u.username,
       COALESCE(NULLIF(up.display_name, ''), u.username),
       lm.role
FROM matches m
INNER JOIN lobby_members lm ON lm.lobby_id = m.lobby_id
INNER JOIN users u ON u.id = lm.user_id
LEFT JOIN user_profiles up ON up.user_id = u.id
WHERE m.status = 'live';
