-- ============================================================
-- MyClan Server Database Schema v2.0
-- Engine: MySQL 8.0+ / MariaDB 10.6+
-- ============================================================

SET foreign_key_checks = 0;


SET foreign_key_checks = 1;

CREATE TABLE users (
    user_id         CHAR(36)     NOT NULL PRIMARY KEY,
    xbox_live_id    VARCHAR(64)  NULL,
    email           VARCHAR(255) NOT NULL UNIQUE,
    display_name    VARCHAR(100) NOT NULL,
    avatar_url      VARCHAR(500) NULL,
    region          VARCHAR(64)  NOT NULL DEFAULT 'North America',
    skill_level     TINYINT      NOT NULL DEFAULT 3,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_email     (email),
    INDEX idx_users_xbox      (xbox_live_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clans (
    clan_id         CHAR(36)     NOT NULL PRIMARY KEY,
    clan_name       VARCHAR(100) NOT NULL,
    clan_tag        VARCHAR(6)   NOT NULL UNIQUE,
    description     TEXT         NULL,
    logo_url        VARCHAR(500) NULL,
    region          VARCHAR(64)  NOT NULL DEFAULT 'North America',
    skill_level     TINYINT      NOT NULL DEFAULT 3,
    owner_id        CHAR(36)     NOT NULL,
    member_count    SMALLINT     NOT NULL DEFAULT 1,
    is_recruiting   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(user_id) ON DELETE RESTRICT,
    INDEX idx_clans_region (region),
    INDEX idx_clans_skill  (skill_level),
    INDEX idx_clans_owner  (owner_id),
    FULLTEXT INDEX ft_clans_search (clan_name, clan_tag, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clan_members (
    clan_member_id  CHAR(36)     NOT NULL PRIMARY KEY,
    clan_id         CHAR(36)     NOT NULL,
    user_id         CHAR(36)     NOT NULL,
    role            ENUM('owner','admin','member') NOT NULL DEFAULT 'member',
    joined_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_clan_user (clan_id, user_id),
    FOREIGN KEY (clan_id) REFERENCES clans(clan_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    INDEX idx_members_clan (clan_id),
    INDEX idx_members_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clan_games (
    clan_game_id    CHAR(36)     NOT NULL PRIMARY KEY,
    clan_id         CHAR(36)     NOT NULL,
    game_id         CHAR(36)     NOT NULL,
    is_primary      TINYINT(1)   NOT NULL DEFAULT 0,
    added_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_clan_game (clan_id, game_id),
    FOREIGN KEY (clan_id) REFERENCES clans(clan_id) ON DELETE CASCADE,
    FOREIGN KEY (game_id) REFERENCES games(game_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE scrim_requests (
    scrim_id            CHAR(36)     NOT NULL PRIMARY KEY,
    requesting_clan_id  CHAR(36)     NOT NULL,
    opponent_clan_id    CHAR(36)     NOT NULL,
    game_id             CHAR(36)     NOT NULL,
    status              ENUM('pending','accepted','declined','completed','cancelled') NOT NULL DEFAULT 'pending',
    scheduled_date      DATETIME     NULL,
    request_message     TEXT         NULL,
    score               VARCHAR(50)  NULL,
    notes               TEXT         NULL,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (requesting_clan_id) REFERENCES clans(clan_id) ON DELETE CASCADE,
    FOREIGN KEY (opponent_clan_id)   REFERENCES clans(clan_id) ON DELETE CASCADE,
    FOREIGN KEY (game_id)            REFERENCES games(game_id) ON DELETE RESTRICT,
    INDEX idx_scrim_requesting (requesting_clan_id),
    INDEX idx_scrim_opponent   (opponent_clan_id),
    INDEX idx_scrim_status     (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
