/* RAIET v1.40.3 — lightweight platform handoff state for no-chat coordination. */
CREATE TABLE IF NOT EXISTS lobby_platform_handoffs (
    lobby_id BIGINT UNSIGNED NOT NULL,
    platform VARCHAR(32) NOT NULL,
    participant_user_id BIGINT UNSIGNED NOT NULL,
    coordinator_user_id BIGINT UNSIGNED NOT NULL,
    state VARCHAR(40) NOT NULL DEFAULT 'idle',
    state_actor_user_id BIGINT UNSIGNED NULL,
    relationship_status VARCHAR(32) NOT NULL DEFAULT 'unknown',
    relationship_checked_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (lobby_id, platform, participant_user_id),
    KEY idx_platform_handoff_coordinator (lobby_id, platform, coordinator_user_id),
    KEY idx_platform_handoff_updated (updated_at),
    CONSTRAINT fk_platform_handoff_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_platform_handoff_participant FOREIGN KEY (participant_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_platform_handoff_coordinator FOREIGN KEY (coordinator_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_platform_handoff_actor FOREIGN KEY (state_actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
