/* RAIET v1.29 — friends + lobby invites foundation.
   Run once after 012_match_feedback.sql. */

CREATE TABLE IF NOT EXISTS friendships (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_one_id BIGINT UNSIGNED NOT NULL,
    user_two_id BIGINT UNSIGNED NOT NULL,
    requested_by BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','accepted') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    accepted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_friendship_pair (user_one_id, user_two_id),
    KEY idx_friendships_requested_by (requested_by, status),
    CONSTRAINT fk_friendships_one FOREIGN KEY (user_one_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_friendships_two FOREIGN KEY (user_two_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_friendships_requester FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT chk_friendship_order CHECK (user_one_id < user_two_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lobby_invites (
    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,
    status ENUM('pending','accepted','declined','expired') NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL,
    responded_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_lobby_invite_target (lobby_id, to_user_id),
    KEY idx_lobby_invites_target_status (to_user_id, status, created_at),
    CONSTRAINT fk_lobby_invites_lobby FOREIGN KEY (lobby_id) REFERENCES lobbies(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_invites_from FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_lobby_invites_to FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT chk_lobby_invite_not_self CHECK (from_user_id <> to_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
