DatabaseCreation: dml.sql

delete from participant_stats;
delete from stat_types;
delete from match_participants;
delete from match_teams;
delete from matches;
delete from tournament_teams;
delete from tournaments;
delete from team_memberships;
delete from teams;
delete from user_achievements;
delete from achievements;
delete from reviews;
delete from notifications;
delete from preferences;
delete from payments;
delete from subscriptions;
delete from direct_messages;
delete from friendships;
delete from linked_accounts;
delete from servers;
delete from game_modes;
delete from game_developers;
delete from game_genres;
delete from genres;
delete from developers;


insert into linked_account_providers (id, name) values
(1, 'steam'),
(2, 'discord'),
(3, 'google'),
(4, 'github')
on conflict (id) do nothing;

insert into friendship_statuses (id, name) values
(1, 'pending'),
(2, 'accepted'),
(3, 'rejected'),
(4, 'blocked')
on conflict (id) do nothing;

insert into subscription_plans (id, name) values
(1, 'free'),
(2, 'basic'),
(3, 'premium'),
(4, 'pro')
on conflict (id) do nothing;

insert into notification_types (id, name) values
(1, 'system'),
(2, 'friend_request'),
(3, 'message'),
(4, 'achievement'),
(5, 'tournament'),
(6, 'subscription')
on conflict (id) do nothing;

insert into team_member_roles (id, name) values
(1, 'member'),
(2, 'captain'),
(3, 'coach'),
(4, 'owner')
on conflict (id) do nothing;

insert into match_statuses (id, name) values
(1, 'scheduled'),
(2, 'in_progress'),
(3, 'completed'),
(4, 'cancelled')
on conflict (id) do nothing;

insert into participant_results (id, name) values
(1, 'win'),
(2, 'loss'),
(3, 'draw')
on conflict (id) do nothing;


insert into locations (id, latitude, longitude, country, city)
values (0, 41.998100, 21.426400, 'Unknown', 'Unknown')
on conflict (id) do nothing;

insert into users (id, username, email, password_hash, location_id, created_at, updated_at)
values (
    0,
    'unknown_user',
    'unknown_user@test.com',
    md5('unknown_user'),
    0,
    current_timestamp,
    current_timestamp
)
on conflict (id) do nothing;

insert into developers (id, name, country)
values (0, 'Unknown Developer', 'Unknown')
on conflict (id) do nothing;

insert into genres (id, name)
values (0, 'Unknown')
on conflict (id) do nothing;

insert into games (id, title, release_date, created_at)
values (0, 'Unknown Game', current_date, current_timestamp)
on conflict (id) do nothing;

insert into game_modes (id, game_id, mode_name)
values (0, 0, 'Unknown')
on conflict (id) do nothing;


insert into developers(id, name, country)
select *
from (
    values
        (1, 'Valve', 'United States'),
        (2, 'Riot Games', 'United States'),
        (3, 'Blizzard Entertainment', 'United States'),
        (4, 'Ubisoft', 'France'),
        (5, 'Epic Games', 'United States'),
        (6, 'Respawn Entertainment', 'United States'),
        (7, 'Mojang Studios', 'Sweden'),
        (8, 'CD Projekt Red', 'Poland'),
        (9, 'FromSoftware', 'Japan'),
        (10, 'Supercell', 'Finland'),
        (11, 'ArenaNet Studios', 'North Macedonia'),
        (12, 'Digital Storm Games', 'Serbia'),
        (13, 'Blue Forge Interactive', 'Germany'),
        (14, 'NovaByte Games', 'Croatia'),
        (15, 'IronFox Studio', 'Bulgaria')
) as d(id, name, country)
on conflict (id) do nothing;


insert into genres(id, name)
select *
from (
    values
        (1, 'FPS'),
        (2, 'MOBA'),
        (3, 'Battle Royale'),
        (4, 'Strategy'),
        (5, 'RPG'),
        (6, 'MMORPG'),
        (7, 'Sports'),
        (8, 'Racing'),
        (9, 'Fighting'),
        (10, 'Survival'),
        (11, 'Tactical Shooter'),
        (12, 'Card Game'),
        (13, 'Simulation'),
        (14, 'Action'),
        (15, 'Adventure')
) as g(id, name)
on conflict (id) do nothing;


insert into games(id, title, release_date, created_at)
select *
from (
    values
        (1, 'Counter-Strike 2', date '2023-09-27', current_timestamp),
        (2, 'Dota 2', date '2013-07-09', current_timestamp),
        (3, 'Valorant', date '2020-06-02', current_timestamp),
        (4, 'League of Legends', date '2009-10-27', current_timestamp),
        (5, 'Overwatch 2', date '2022-10-04', current_timestamp),
        (6, 'Rainbow Six Siege', date '2015-12-01', current_timestamp),
        (7, 'Fortnite', date '2017-07-21', current_timestamp),
        (8, 'Apex Legends', date '2019-02-04', current_timestamp),
        (9, 'Minecraft', date '2011-11-18', current_timestamp),
        (10, 'The Witcher 3', date '2015-05-19', current_timestamp),
        (11, 'Elden Ring', date '2022-02-25', current_timestamp),
        (12, 'Clash Royale', date '2016-03-02', current_timestamp),
        (13, 'FIFA 23', date '2022-09-30', current_timestamp),
        (14, 'Rocket League', date '2015-07-07', current_timestamp),
        (15, 'Guild Wars 2', date '2012-08-28', current_timestamp)
) as g(id, title, release_date, created_at)
on conflict (id) do nothing;


insert into game_genres(game_id, genre_id)
select g.id, ((g.id - 1) % 15) + 1
from games g
where g.id between 1 and 15
on conflict do nothing;

insert into game_genres(game_id, genre_id)
select g.id, ((g.id + 5) % 15) + 1
from games g
where g.id between 1 and 15
on conflict do nothing;


insert into game_developers(game_id, developer_id)
select g.id, ((g.id - 1) % 15) + 1
from games g
where g.id between 1 and 15
on conflict do nothing;


insert into game_modes(id, game_id, mode_name)
select
    ((g.id - 1) * 5) + m.mode_id as id,
    g.id as game_id,
    m.mode_name
from games g
cross join (
    values
        (1, '1v1'),
        (2, '2v2'),
        (3, '3v3'),
        (4, '5v5'),
        (5, 'Practice')
) as m(mode_id, mode_name)
where g.id between 1 and 15
on conflict (id) do nothing;

insert into game_modes(id, game_id, mode_name)
values (0, 0, 'Unknown')
on conflict (id) do nothing;


insert into servers(id, game_id, location_id, server_name, is_active)
select
    ((g.id - 1) * 5) + s.server_no as id,
    g.id as game_id,
    l.id as location_id,
    g.title || ' Server ' || s.server_no as server_name,
    true as is_active
from games g
cross join generate_series(1, 5) s(server_no)
join lateral (
    select id
    from locations
    where id <> 0
    order by random()
    limit 1
) l on true
where g.id between 1 and 15
on conflict (id) do nothing;

insert into servers(id, game_id, location_id, server_name, is_active)
values (0, 0, 0, 'Unknown Server', false)
on conflict (id) do nothing;


insert into linked_accounts(id, user_id, provider_id, external_user_id, nickname, avatar_url, linked_at)
select
    gs as id,
    gs as user_id,
    1 as provider_id,
    'external_' || gs as external_user_id,
    'player_' || gs as nickname,
    'https://cdn.arenanet.local/avatar/' || gs || '.png' as avatar_url,
    now() - (random() * interval '3 years') as linked_at
from generate_series(1, 300000) gs
where exists (select 1 from users u where u.id = gs)
on conflict (id) do nothing;


insert into friendships(id, user_id_low, user_id_high, requested_by_user_id, status_id, created_at, updated_at)
select
    gs as id,
    gs as user_id_low,
    gs + 1 as user_id_high,
    gs as requested_by_user_id,
    2 as status_id,
    t.created_at,
    t.created_at + (random() * interval '2 years') as updated_at
from generate_series(1, 500000) gs
cross join lateral (
    select now() - (random() * interval '4 years') as created_at
) t
where exists (select 1 from users u where u.id = gs)
  and exists (select 1 from users u where u.id = gs + 1)
on conflict (id) do nothing;


insert into direct_messages(id, sender_id, receiver_id, content, sent_at, read_at)
select
    gs as id,
    gs as sender_id,
    gs + 1 as receiver_id,
    case
        when gs % 5 = 0 then 'Want to queue ranked later?'
        when gs % 5 = 1 then 'Good game, well played.'
        when gs % 5 = 2 then 'Join our team for the tournament.'
        when gs % 5 = 3 then 'Can you review the match stats?'
        else 'Let us practice tonight.'
    end as content,
    msg.sent_at,
    case
        when gs % 4 = 0 then null
        else msg.sent_at + (random() * interval '30 days')
    end as read_at
from generate_series(1, 700000) gs
cross join lateral (
    select now() - (random() * interval '2 years') as sent_at
) msg
where exists (select 1 from users u where u.id = gs)
  and exists (select 1 from users u where u.id = gs + 1)
on conflict (id) do nothing;


insert into subscriptions(id, user_id, plan_id, start_at, end_at, is_active)
select
    gs as id,
    gs as user_id,
    ((gs - 1) % 4) + 1 as plan_id,
    now() - (random() * interval '2 years') as start_at,
    null as end_at,
    true as is_active
from generate_series(1, 500000) gs
where exists (select 1 from users u where u.id = gs)
on conflict (id) do nothing;


insert into payments(id, user_id, subscription_id, amount, currency_code, paid_at, provider_reference)
select
    s.id as id,
    s.user_id,
    s.id as subscription_id,
    case
        when s.id % 3 = 0 then 4.99
        when s.id % 3 = 1 then 9.99
        else 14.99
    end as amount,
    'EUR' as currency_code,
    s.start_at + interval '5 minutes' as paid_at,
    'PAY-' || s.id as provider_reference
from subscriptions s
where s.id between 1 and 500000
on conflict (id) do nothing;


insert into preferences(user_id, game_id, is_favorite, created_at)
select
    ((gs - 1) / 5) + 1 as user_id,
    ((gs - 1) % 5) + 1 as game_id,
    true as is_favorite,
    now() - (random() * interval '3 years') as created_at
from generate_series(1, 500000) gs
where exists (select 1 from users u where u.id = ((gs - 1) / 5) + 1)
  and exists (select 1 from games g where g.id = ((gs - 1) % 5) + 1)
on conflict do nothing;


insert into notifications
(
    id,
    user_id,
    type_id,
    title,
    message,
    is_read,
    created_at,
    preference_user_id,
    preference_game_id
)
select
    gs as id,
    ((gs - 1) / 5) + 1 as user_id,
    1 as type_id,
    case
        when gs % 3 = 0 then 'Tournament reminder'
        when gs % 3 = 1 then 'New match available'
        else 'Game recommendation'
    end as title,
    case
        when gs % 3 = 0 then 'A tournament for one of your favorite games starts soon.'
        when gs % 3 = 1 then 'A new match has been scheduled.'
        else 'You may like this game based on your preferences.'
    end as message,
    gs % 4 = 0 as is_read,
    now() - (random() * interval '1 year') as created_at,
    ((gs - 1) / 5) + 1 as preference_user_id,
    ((gs - 1) % 5) + 1 as preference_game_id
from generate_series(1, 100000) gs
where exists (
    select 1
    from preferences p
    where p.user_id = ((gs - 1) / 5) + 1
      and p.game_id = ((gs - 1) % 5) + 1
)
on conflict (id) do nothing;


insert into reviews(id, user_id, game_id, rating, comment, created_at)
select
    gs as id,
    ((gs - 1) / 15) + 1 as user_id,
    ((gs - 1) % 15) + 1 as game_id,
    1 + floor(random() * 10)::int as rating,
    case
        when gs % 4 = 0 then 'Very competitive and fun.'
        when gs % 4 = 1 then 'Good matchmaking and active community.'
        when gs % 4 = 2 then 'Needs better servers, but gameplay is solid.'
        else 'Great for tournaments and team play.'
    end as comment,
    now() - (random() * interval '3 years') as created_at
from generate_series(1, 20000) gs
where exists (select 1 from users u where u.id = ((gs - 1) / 15) + 1)
on conflict (id) do nothing;


insert into achievements(id, game_id, title, description)
select
    ((g.id - 1) * 5) + a.achievement_no as id,
    g.id as game_id,
    case a.achievement_no
        when 1 then 'First Victory'
        when 2 then 'Sharp Shooter'
        when 3 then 'Team Player'
        when 4 then 'Tournament Debut'
        else 'Elite Competitor'
    end as title,
    case a.achievement_no
        when 1 then 'Win your first match.'
        when 2 then 'Achieve a high accuracy score.'
        when 3 then 'Play multiple matches with a team.'
        when 4 then 'Join your first tournament.'
        else 'Reach a high competitive ranking.'
    end as description
from games g
cross join generate_series(1, 5) a(achievement_no)
where g.id between 1 and 15
on conflict (id) do nothing;

insert into achievements(id, game_id, title, description)
values (0, 0, 'Unknown Achievement', 'Default achievement.')
on conflict (id) do nothing;


insert into user_achievements(id, user_id, achievement_id, unlocked_at)
select
    gs as id,
    gs as user_id,
    ((gs - 1) % 24) + 1 as achievement_id,
    now() - (random() * interval '3 years') as unlocked_at
from generate_series(1, 500000) gs
where exists (select 1 from users u where u.id = gs)
on conflict (id) do nothing;


insert into teams(id, name, created_at)
select
    gs as id,
    'Team_' || gs as name,
    now() - (random() * interval '4 years') as created_at
from generate_series(1, 100000) gs
on conflict (id) do nothing;


insert into team_memberships(team_id, user_id, role_id, joined_at, is_active)
select
    ((gs - 1) / 5) + 1 as team_id,
    gs as user_id,
    case
        when gs % 5 = 1 then 4
        else 1
    end as role_id,
    now() - (random() * interval '3 years') as joined_at,
    true as is_active
from generate_series(1, 500000) gs
where exists (select 1 from users u where u.id = gs)
on conflict do nothing;


delete from participant_stats;
delete from stat_types;
delete from match_participants;
delete from match_teams;
delete from matches;
delete from tournament_teams;
delete from tournaments;


insert into teams(id, name, created_at)
values (0, 'Unknown Team', now())
on conflict (id) do nothing;


insert into tournaments
(
    id,
    game_id,
    name,
    organizer_team_id,
    prize_pool,
    starts_at,
    ends_at,
    created_at
)
select
    gs as id,
    g.id as game_id,
    'Tournament_' || gs as name,
    ((gs - 1) % 100000) + 1 as organizer_team_id,
    case
        when gs % 5 = 0 then 10000
        when gs % 5 = 1 then 5000
        when gs % 5 = 2 then 2500
        when gs % 5 = 3 then 1000
        else 500
    end as prize_pool,
    now() - interval '1 year' + (gs * interval '3 hours') as starts_at,
    now() - interval '1 year' + (gs * interval '3 hours') + interval '6 hours' as ends_at,
    now() - interval '1 year' + (gs * interval '2 hours') as created_at
from generate_series(1, 2000) gs
join lateral (
    select id
    from games
    where id <> 0
    order by id
    offset ((gs - 1) % (select count(*) from games where id <> 0))
    limit 1
) g on true
on conflict (id) do nothing;


insert into tournament_teams(tournament_id, team_id, joined_at)
select
    t.id as tournament_id,
    ((t.id - 1) * 8) + slot.slot_no as team_id,
    t.created_at + interval '1 hour' as joined_at
from tournaments t
cross join generate_series(1, 8) slot(slot_no)
where t.id between 1 and 2000
on conflict do nothing;


insert into matches
(
    id,
    game_id,
    game_mode_id,
    tournament_id,
    server_id,
    status_id,
    started_at,
    ended_at,
    created_at
)
select
    gs as id,
    t.game_id,
    gm.id as game_mode_id,
    t.id as tournament_id,
    coalesce(
        (
            select s.id
            from servers s
            where s.game_id = t.game_id
            order by s.id
            limit 1
        ),
        0
    ) as server_id,
    3 as status_id,
    t.starts_at + ((gs % 20) * interval '30 minutes') as started_at,
    t.starts_at + ((gs % 20) * interval '30 minutes') + interval '45 minutes' as ended_at,
    t.created_at as created_at
from generate_series(1, 1000000) gs
join tournaments t
    on t.id = ((gs - 1) % 2000) + 1
join game_modes gm
    on gm.game_id = t.game_id
   and lower(gm.mode_name) =
        case
            when gs <= 300000 then '5v5'
            when gs <= 600000 then '3v3'
            when gs <= 800000 then '2v2'
            else '1v1'
        end
on conflict (id) do nothing;


insert into match_teams(match_id, team_id, side_label)
select
    m.id as match_id,
    ((m.tournament_id - 1) * 8) + 1 as team_id,
    'A' as side_label
from matches m
where m.id between 1 and 1000000
on conflict do nothing;

insert into match_teams(match_id, team_id, side_label)
select
    m.id as match_id,
    ((m.tournament_id - 1) * 8) + 2 as team_id,
    'B' as side_label
from matches m
where m.id between 1 and 1000000
on conflict do nothing;


insert into match_participants
(
    id,
    match_id,
    user_id,
    team_id,
    result_id,
    score,
    elo_change,
    joined_at
)
select
    row_number() over (order by m.id, mt.side_label, member.slot_no) as id,
    m.id as match_id,
    member.user_id,
    mt.team_id,
    case
        when mt.side_label = 'A' then 1
        else 2
    end as result_id,
    case
        when mt.side_label = 'A' then 16 + floor(random() * 10)::int
        else 5 + floor(random() * 11)::int
    end as score,
    case
        when mt.side_label = 'A' then 10 + floor(random() * 20)::int
        else -20 + floor(random() * 10)::int
    end as elo_change,
    m.started_at - interval '5 minutes' as joined_at
from matches m
join game_modes gm on gm.id = m.game_mode_id
join match_teams mt on mt.match_id = m.id
join lateral (
    select
        tm.user_id,
        row_number() over (order by tm.user_id) as slot_no
    from team_memberships tm
    where tm.team_id = mt.team_id
      and tm.is_active = true
    order by tm.user_id
    limit
        case lower(gm.mode_name)
            when '5v5' then 5
            when '3v3' then 3
            when '2v2' then 2
            when '1v1' then 1
            else 0
        end
) member on true
where m.id between 1 and 1000000
on conflict (id) do nothing;


insert into stat_types(id, game_id, name, unit)
select
    ((g.id - 1) * 5) + s.stat_no as id,
    g.id as game_id,
    case s.stat_no
        when 1 then 'kills'
        when 2 then 'deaths'
        when 3 then 'assists'
        when 4 then 'damage'
        else 'accuracy'
    end as name,
    case s.stat_no
        when 4 then 'points'
        when 5 then '%'
        else 'count'
    end as unit
from games g
cross join generate_series(1, 5) s(stat_no)
where g.id <> 0
on conflict (id) do nothing;

insert into stat_types(id, game_id, name, unit)
values (0, 0, 'unknown', null)
on conflict (id) do nothing;


insert into participant_stats(match_participant_id, stat_type_id, value)
select
    mp.id as match_participant_id,
    ((m.game_id - 1) * 5) + st.stat_no as stat_type_id,
    case st.stat_no
        when 1 then floor(random() * 35)
        when 2 then floor(random() * 25)
        when 3 then floor(random() * 20)
        when 4 then floor(random() * 4000)
        else round((40 + random() * 60)::numeric, 2)
    end as value
from match_participants mp
join matches m on m.id = mp.match_id
cross join generate_series(1, 5) st(stat_no)
where mp.id between 1 and 6000000
on conflict do nothing;


select 'tournaments' as table_name, count(*) from tournaments
union all select 'tournament_teams', count(*) from tournament_teams
union all select 'matches', count(*) from matches
union all select 'match_teams', count(*) from match_teams
union all select 'match_participants', count(*) from match_participants
union all select 'stat_types', count(*) from stat_types
union all select 'participant_stats', count(*) from participant_stats;


select
    gm.mode_name,
    count(*) as matches
from matches m
join game_modes gm on gm.id = m.game_mode_id
group by gm.mode_name
order by gm.mode_name;


select 'developers' as table_name, count(*) from developers
union all select 'genres', count(*) from genres
union all select 'games', count(*) from games
union all select 'game_modes', count(*) from game_modes
union all select 'servers', count(*) from servers
union all select 'linked_accounts', count(*) from linked_accounts
union all select 'friendships', count(*) from friendships
union all select 'direct_messages', count(*) from direct_messages
union all select 'subscriptions', count(*) from subscriptions
union all select 'payments', count(*) from payments
union all select 'preferences', count(*) from preferences
union all select 'notifications', count(*) from notifications
union all select 'reviews', count(*) from reviews
union all select 'achievements', count(*) from achievements
union all select 'user_achievements', count(*) from user_achievements
union all select 'teams', count(*) from teams
union all select 'team_memberships', count(*) from team_memberships
union all select 'tournaments', count(*) from tournaments
union all select 'tournament_teams', count(*) from tournament_teams
union all select 'matches', count(*) from matches
union all select 'match_teams', count(*) from match_teams
union all select 'match_participants', count(*) from match_participants
union all select 'stat_types', count(*) from stat_types
union all select 'participant_stats', count(*) from participant_stats;