CREATE TABLE linked_account_providers
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE friendship_statuses
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE subscription_plans
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE notification_types
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE team_member_roles
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE match_statuses
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE participant_results
(
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);


INSERT INTO linked_account_providers (id, name) VALUES
(1, 'steam'),
(2, 'discord'),
(3, 'google'),
(4, 'github');

INSERT INTO friendship_statuses (id, name) VALUES
(1, 'pending'),
(2, 'accepted'),
(3, 'rejected'),
(4, 'blocked');

INSERT INTO subscription_plans (id, name) VALUES
(1, 'free'),
(2, 'basic'),
(3, 'premium'),
(4, 'pro');

INSERT INTO notification_types (id, name) VALUES
(1, 'system'),
(2, 'friend_request'),
(3, 'message'),
(4, 'achievement'),
(5, 'tournament'),
(6, 'subscription');

INSERT INTO team_member_roles (id, name) VALUES
(1, 'member'),
(2, 'captain'),
(3, 'coach'),
(4, 'owner');

INSERT INTO match_statuses (id, name) VALUES
(1, 'scheduled'),
(2, 'in_progress'),
(3, 'completed'),
(4, 'cancelled');

INSERT INTO participant_results (id, name) VALUES
(1, 'win'),
(2, 'loss'),
(3, 'draw');



CREATE TABLE locations
(
    id           BIGINT PRIMARY KEY,
    latitude     NUMERIC(9,6) NOT NULL CHECK (latitude BETWEEN -90 AND 90),
    longitude    NUMERIC(9,6) NOT NULL CHECK (longitude BETWEEN -180 AND 180),
    country      VARCHAR(100),
    city         VARCHAR(100),
    UNIQUE (latitude, longitude)
);

CREATE TABLE users
(
    id             BIGINT PRIMARY KEY,
    username       VARCHAR(30) NOT NULL UNIQUE,
    email          VARCHAR(100) NOT NULL UNIQUE,
    password_hash  TEXT NOT NULL,
    location_id    BIGINT NOT NULL DEFAULT 0
        REFERENCES locations(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (char_length(username) BETWEEN 3 AND 30)
);

CREATE TABLE profiles
(
    user_id        BIGINT PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    avatar_url     TEXT,
    bio            TEXT,
    rank_points    INT NOT NULL DEFAULT 1000 CHECK (rank_points >= 0)
);

CREATE TABLE developers
(
    id             BIGINT PRIMARY KEY,
    name           VARCHAR(100) NOT NULL UNIQUE,
    country        VARCHAR(100)
);

CREATE TABLE genres
(
    id             BIGINT PRIMARY KEY,
    name           VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE games
(
    id             BIGINT PRIMARY KEY,
    title          VARCHAR(100) NOT NULL UNIQUE,
    release_date   DATE NOT NULL CHECK (release_date <= CURRENT_DATE),
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE game_genres
(
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    genre_id       BIGINT NOT NULL DEFAULT 0
        REFERENCES genres(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    PRIMARY KEY (game_id, genre_id)
);

CREATE TABLE game_developers
(
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    developer_id   BIGINT NOT NULL DEFAULT 0
        REFERENCES developers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    PRIMARY KEY (game_id, developer_id)
);

CREATE TABLE game_modes
(
    id             BIGINT PRIMARY KEY,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    mode_name      VARCHAR(50) NOT NULL,
    UNIQUE (game_id, mode_name)
);

CREATE TABLE servers
(
    id             BIGINT PRIMARY KEY,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    location_id    BIGINT NOT NULL DEFAULT 0
        REFERENCES locations(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    server_name    VARCHAR(100) NOT NULL,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    UNIQUE (game_id, server_name)
);

CREATE TABLE linked_accounts
(
    id                  BIGINT PRIMARY KEY,
    user_id             BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    provider_id         BIGINT NOT NULL DEFAULT 1
        REFERENCES linked_account_providers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    external_user_id    VARCHAR(100) NOT NULL,
    nickname            VARCHAR(50),
    avatar_url          TEXT,
    linked_at           TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (provider_id, external_user_id),
    UNIQUE (user_id, provider_id)
);

CREATE TABLE friendships
(
    id                    BIGINT PRIMARY KEY,
    user_id_low           BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    user_id_high          BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    requested_by_user_id  BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    status_id             BIGINT NOT NULL DEFAULT 1
        REFERENCES friendship_statuses(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    created_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (user_id_low < user_id_high),
    CHECK (requested_by_user_id = user_id_low OR requested_by_user_id = user_id_high)
);

CREATE TABLE direct_messages
(
    id             BIGINT PRIMARY KEY,
    sender_id      BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    receiver_id    BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    content        TEXT NOT NULL,
    sent_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at        TIMESTAMP,
    CHECK (sender_id <> receiver_id),
    CHECK (read_at IS NULL OR read_at >= sent_at)
);

CREATE TABLE subscriptions
(
    id             BIGINT PRIMARY KEY,
    user_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    plan_id        BIGINT NOT NULL DEFAULT 1
        REFERENCES subscription_plans(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    start_at       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_at         TIMESTAMP,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    CHECK (end_at IS NULL OR end_at > start_at)
);

CREATE TABLE payments
(
    id                  BIGINT PRIMARY KEY,
    user_id             BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    subscription_id     BIGINT NOT NULL DEFAULT 0
        REFERENCES subscriptions(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    amount              NUMERIC(12,2) NOT NULL CHECK (amount > 0),
    currency_code       CHAR(3) NOT NULL DEFAULT 'EUR',
    paid_at             TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    provider_reference  VARCHAR(100) UNIQUE
);

CREATE TABLE preferences
(
    user_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    is_favorite    BOOLEAN NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, game_id)
);

CREATE TABLE notifications
(
    id                  BIGINT PRIMARY KEY,
    user_id             BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    type_id             BIGINT NOT NULL DEFAULT 1
        REFERENCES notification_types(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    title               VARCHAR(120) NOT NULL,
    message             TEXT NOT NULL,
    is_read             BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    preference_user_id  BIGINT DEFAULT NULL,
    preference_game_id  BIGINT DEFAULT NULL,
    FOREIGN KEY (preference_user_id, preference_game_id)
        REFERENCES preferences(user_id, game_id) ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE reviews
(
    id             BIGINT PRIMARY KEY,
    user_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    rating         INT NOT NULL CHECK (rating BETWEEN 1 AND 10),
    comment        TEXT,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, game_id)
);

CREATE TABLE achievements
(
    id             BIGINT PRIMARY KEY,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    title          VARCHAR(100) NOT NULL,
    description    TEXT,
    UNIQUE (game_id, title)
);

CREATE TABLE user_achievements
(
    id              BIGINT PRIMARY KEY,
    user_id         BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    achievement_id  BIGINT NOT NULL DEFAULT 0
        REFERENCES achievements(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    unlocked_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE teams
(
    id             BIGINT PRIMARY KEY,
    name           VARCHAR(50) NOT NULL UNIQUE,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE team_memberships
(
    team_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    user_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    role_id        BIGINT NOT NULL DEFAULT 1
        REFERENCES team_member_roles(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    joined_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_active      BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (team_id, user_id)
);

CREATE TABLE tournaments
(
    id                 BIGINT PRIMARY KEY,
    game_id            BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    name               VARCHAR(100) NOT NULL,
    organizer_team_id  BIGINT NOT NULL DEFAULT 0
        REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    prize_pool         NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (prize_pool >= 0),
    starts_at          TIMESTAMP NOT NULL,
    ends_at            TIMESTAMP,
    created_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (ends_at IS NULL OR ends_at >= starts_at),
    UNIQUE (game_id, name, starts_at)
);

CREATE TABLE tournament_teams
(
    tournament_id    BIGINT NOT NULL DEFAULT 0
        REFERENCES tournaments(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    team_id          BIGINT NOT NULL DEFAULT 0
        REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    joined_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tournament_id, team_id)
);


CREATE TABLE matches
(
    id             BIGINT,
    game_id        BIGINT NOT NULL DEFAULT 0 REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    game_mode_id   BIGINT NOT NULL DEFAULT 0 REFERENCES game_modes(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    tournament_id  BIGINT NOT NULL DEFAULT 0 REFERENCES tournaments(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    server_id      BIGINT NOT NULL DEFAULT 0 REFERENCES servers(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    status_id      BIGINT NOT NULL DEFAULT 1 REFERENCES match_statuses(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    started_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at       TIMESTAMP,
    created_at     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (ended_at IS NULL OR started_at IS NULL OR ended_at >= started_at),
    PRIMARY KEY (id, started_at)
) PARTITION BY RANGE (started_at);

CREATE TABLE matches_y2025 PARTITION OF matches
    FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2026-01-01 00:00:00');

CREATE TABLE matches_y2026 PARTITION OF matches
    FOR VALUES FROM ('2026-01-01 00:00:00') TO ('2027-01-01 00:00:00');

CREATE TABLE matches_default PARTITION OF matches DEFAULT;


CREATE TABLE match_teams
(
    match_id           BIGINT NOT NULL DEFAULT 0,
    match_started_at   TIMESTAMP NOT NULL,
    team_id            BIGINT NOT NULL DEFAULT 0 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    side_label         VARCHAR(20),
    PRIMARY KEY (match_id, team_id),
    FOREIGN KEY (match_id, match_started_at) REFERENCES matches(id, started_at) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE match_participants
(
    id                 BIGINT PRIMARY KEY,
    match_id           BIGINT NOT NULL DEFAULT 0,
    match_started_at   TIMESTAMP NOT NULL,
    user_id            BIGINT NOT NULL DEFAULT 0 REFERENCES users(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    team_id            BIGINT NOT NULL DEFAULT 0 REFERENCES teams(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    result_id          BIGINT DEFAULT NULL REFERENCES participant_results(id) ON DELETE SET NULL ON UPDATE CASCADE,
    score              INT CHECK (score >= 0),
    elo_change         INT NOT NULL DEFAULT 0,
    joined_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (match_id, user_id),
    FOREIGN KEY (match_id, team_id) REFERENCES match_teams(match_id, team_id),
    FOREIGN KEY (match_id, match_started_at) REFERENCES matches(id, started_at) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE stat_types
(
    id             BIGINT PRIMARY KEY,
    game_id        BIGINT NOT NULL DEFAULT 0
        REFERENCES games(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    name           VARCHAR(50) NOT NULL,
    unit           VARCHAR(20),
    UNIQUE (game_id, name)
);

CREATE TABLE participant_stats
(
    match_participant_id  BIGINT NOT NULL DEFAULT 0
        REFERENCES match_participants(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    stat_type_id          BIGINT NOT NULL DEFAULT 0
        REFERENCES stat_types(id) ON DELETE SET DEFAULT ON UPDATE CASCADE,
    value                 NUMERIC(12,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (match_participant_id, stat_type_id)
);