DatabaseCreation: views.sql

-- 1. friend_list
CREATE OR REPLACE VIEW public.friend_list
AS SELECT f.id AS friendship_id,
    u1.id AS user_id,
    u1.username,
    u2.id AS friend_id,
    u2.username AS friend_username,
    fs.name AS status
   FROM friendships f
     JOIN users u1 ON u1.id = f.user_id_high
     JOIN users u2 ON u2.id = f.user_id_low
     JOIN friendship_statuses fs ON fs.id = f.status_id
  WHERE fs.name = 'accepted'
UNION ALL
 SELECT f.id AS friendship_id,
    u2.id AS user_id,
    u2.username,
    u1.id AS friend_id,
    u1.username AS friend_username,
    fs.name AS status
   FROM friendships f
     JOIN users u1 ON u1.id = f.user_id_high
     JOIN users u2 ON u2.id = f.user_id_low
     JOIN friendship_statuses fs ON fs.id = f.status_id
  WHERE fs.name = 'accepted';

-- 2. game_catalog
CREATE OR REPLACE VIEW public.game_catalog
AS SELECT g.id AS game_id,
    g.title,
    g.release_date,
    string_agg(DISTINCT ge.name::text, ', '::text) AS genres,
    string_agg(DISTINCT d.name::text, ', '::text) AS developers,
    avg(r.rating) AS avg_rating,
    count(DISTINCT r.id) AS review_count
   FROM games g
     LEFT JOIN game_genres gg ON gg.game_id = g.id
     LEFT JOIN genres ge ON ge.id = gg.genre_id
     LEFT JOIN game_developers gd ON gd.game_id = g.id
     LEFT JOIN developers d ON d.id = gd.developer_id
     LEFT JOIN reviews r ON r.game_id = g.id
  GROUP BY g.id, g.title, g.release_date;

-- 3. leaderboard
CREATE OR REPLACE VIEW public.leaderboard
AS SELECT u.id AS user_id,
    u.username,
    p.rank_points,
    l.country,
    p.avatar_url,
    count(DISTINCT mp.id) AS matches_played,
    count(DISTINCT mp.id) FILTER (WHERE pr.name = 'win') AS wins,
        CASE
            WHEN count(DISTINCT mp.id) = 0 THEN 0::numeric
            ELSE round(count(DISTINCT mp.id) FILTER (WHERE pr.name = 'win')::numeric * 100.0 / count(DISTINCT mp.id)::numeric, 2)
        END AS win_rate
   FROM users u
     JOIN profiles p ON p.user_id = u.id
     JOIN locations l ON l.id = u.location_id
     LEFT JOIN match_participants mp ON mp.user_id = u.id
     LEFT JOIN participant_results pr ON pr.id = mp.result_id
  GROUP BY u.id, u.username, p.rank_points, l.country, p.avatar_url
  ORDER BY p.rank_points DESC;

-- 4. match_history
CREATE OR REPLACE VIEW public.match_history
AS SELECT m.id AS match_id,
    g.title AS game,
    gm.mode_name AS game_mode,
    t.name AS tournament_name,
    m.started_at,
    m.ended_at,
    EXTRACT(epoch FROM m.ended_at - m.started_at) / 60::numeric AS duration_minutes,
    l.latitude AS server_lat,
    l.longitude AS server_lon
   FROM matches m
     JOIN games g ON g.id = m.game_id
     JOIN game_modes gm ON gm.id = m.game_mode_id
     LEFT JOIN tournaments t ON t.id = m.tournament_id
     JOIN servers s ON s.id = m.server_id
     JOIN locations l ON l.id = s.location_id
  WHERE m.ended_at IS NOT NULL;

-- 5. player_match_stats
CREATE OR REPLACE VIEW public.player_match_stats
AS SELECT mp.id AS user_match_id,
    u.id AS user_id,
    u.username,
    m.id AS match_id,
    g.title AS game,
    pr.name AS result,
    mp.score,
    mp.elo_change,
    mp.joined_at,
    jsonb_object_agg(st.name, ps.value) FILTER (WHERE st.id IS NOT NULL) AS stats
   FROM match_participants mp
     JOIN users u ON u.id = mp.user_id
     JOIN matches m ON m.id = mp.match_id AND m.started_at = mp.match_started_at
     JOIN games g ON g.id = m.game_id
     LEFT JOIN participant_results pr ON pr.id = mp.result_id
     LEFT JOIN participant_stats ps ON ps.match_participant_id = mp.id
     LEFT JOIN stat_types st ON st.id = ps.stat_type_id
  GROUP BY mp.id, u.id, u.username, m.id, g.title, pr.name, mp.score, mp.elo_change, mp.joined_at;

-- 6. tournament_standings
CREATE OR REPLACE VIEW public.tournament_standings
AS SELECT t.id AS tournament_id,
    t.name AS tournament_name,
    g.title AS game,
    t.starts_at,
    t.prize_pool,
    tm.id AS team_id,
    tm.name AS team_name,
    count(DISTINCT m.id) AS matches_played,
    count(DISTINCT m.id) FILTER (WHERE m.id IS NOT NULL) AS matches_entered
   FROM tournaments t
     JOIN games g ON g.id = t.game_id
     JOIN tournament_teams tt ON tt.tournament_id = t.id
     JOIN teams tm ON tm.id = tt.team_id
     LEFT JOIN matches m ON m.tournament_id = t.id
  GROUP BY t.id, t.name, g.title, t.starts_at, t.prize_pool, tm.id, tm.name
  ORDER BY t.id, (count(DISTINCT m.id)) DESC;

-- 7. unread_notifications
CREATE OR REPLACE VIEW public.unread_notifications
AS SELECT n.id AS notification_id,
    u.id AS user_id,
    u.username,
    nt.name AS type,
    n.message,
    n.is_read,
    g.title AS preferred_game
   FROM notifications n
     JOIN users u ON u.id = n.user_id
     JOIN notification_types nt ON nt.id = n.type_id
     LEFT JOIN preferences pr ON pr.user_id = n.preference_user_id AND pr.game_id = n.preference_game_id
     LEFT JOIN games g ON g.id = pr.game_id
  WHERE n.is_read = false
  ORDER BY n.id DESC;

-- 8. user_all_achievements
CREATE OR REPLACE VIEW public.user_all_achievements
AS SELECT u.id AS user_id,
    u.username,
    a.id AS achievement_id,
    a.title AS achievement_title,
    a.description AS achievement_description,
    g.title AS game,
    ua.unlocked_at
   FROM user_achievements ua
     JOIN users u ON u.id = ua.user_id
     JOIN achievements a ON a.id = ua.achievement_id
     JOIN games g ON g.id = a.game_id
  ORDER BY ua.unlocked_at DESC;

-- 9. user_profile
CREATE OR REPLACE VIEW public.user_profile
AS SELECT u.id AS user_id,
    u.username,
    u.email,
    l.latitude,
    l.longitude,
    u.created_at,
    p.avatar_url,
    p.bio,
    l.country,
    p.rank_points,
    sp.name AS subscription_type,
    s.start_at AS subscription_start,
    s.end_at AS subscription_end
   FROM users u
     JOIN profiles p ON p.user_id = u.id
     JOIN locations l ON l.id = u.location_id
     LEFT JOIN subscriptions s ON s.user_id = u.id AND (s.end_at IS NULL OR s.end_at >= CURRENT_DATE) AND s.start_at <= CURRENT_DATE
     LEFT JOIN subscription_plans sp ON sp.id = s.plan_id;