AdvancedTopics: partitioning.sql

-- ddl

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, -- Part of PK, must be NOT NULL
    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 match_teams
(
    match_id           BIGINT NOT NULL DEFAULT 0,
    match_started_at   TIMESTAMP NOT NULL, -- Added to satisfy FK constraint
    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, -- Added to satisfy FK constraint
    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
);


-- views

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;

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;

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;



-- fn, proc, trigg

CREATE OR REPLACE PROCEDURE public.create_match(
    IN p_match_id bigint,
    IN p_game_id bigint,
    IN p_game_mode_id bigint,
    IN p_tournament_id bigint,
    IN p_server_id bigint,
    IN p_participants jsonb,
    OUT p_started_at timestamp -- Added OUT parameter to pass back the chosen partition time
)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_status_id BIGINT;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM servers WHERE id = p_server_id AND is_active = TRUE) THEN
        RAISE EXCEPTION 'Server % is not active or does not exist.', p_server_id;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM tournaments WHERE id = p_tournament_id AND game_id = p_game_id) THEN
        RAISE EXCEPTION 'Tournament % is not connected to game %.', p_tournament_id, p_game_id;
    END IF;

    SELECT id INTO v_status_id FROM match_statuses WHERE name = 'in_progress';
    p_started_at := CURRENT_TIMESTAMP;

    INSERT INTO matches (id, game_id, game_mode_id, tournament_id, server_id, status_id, started_at)
    VALUES (p_match_id, p_game_id, p_game_mode_id, p_tournament_id, p_server_id, v_status_id, p_started_at);

    RAISE NOTICE 'The match % is created in partition window %.', p_match_id, p_started_at;
END;
$procedure$
;

CREATE OR REPLACE PROCEDURE public.add_match_participants(IN p_match_id bigint, IN p_user_ids bigint[], IN p_team_ids bigint[])
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_ongoing_id BIGINT;
    v_started_at TIMESTAMP;
BEGIN
    SELECT id INTO v_ongoing_id FROM match_statuses WHERE name = 'in_progress';

    -- Look up the partition timestamp key along with status validation
    SELECT started_at INTO v_started_at
    FROM matches
    WHERE id = p_match_id AND status_id = v_ongoing_id;

    IF v_started_at IS NULL THEN
        RAISE EXCEPTION 'Match % is not active, does not exist, or status mismatch.', p_match_id;
    END IF;

    IF cardinality(p_user_ids) != cardinality(p_team_ids) THEN
        RAISE EXCEPTION 'The number of user IDs must match the number of team IDs.';
    END IF;

    -- Include composite foreign key tracking column: match_started_at
    INSERT INTO match_teams (match_id, match_started_at, team_id, side_label)
    SELECT DISTINCT p_match_id, v_started_at, t_id, 'Team_' || t_id
    FROM unnest(p_team_ids) AS t_id
    ON CONFLICT DO NOTHING;

    -- Include composite foreign key tracking column: match_started_at
    INSERT INTO match_participants (id, match_id, match_started_at, user_id, team_id)
    SELECT
        coalesce((select max(id) from match_participants), 0) + row_number() OVER (),
        p_match_id,
        v_started_at,
        arr.u_id,
        arr.t_id
    FROM unnest(p_user_ids, p_team_ids) AS arr(u_id, t_id);

    RAISE NOTICE 'Participants successfully added to match %.', p_match_id;
END;
$procedure$
;

CREATE OR REPLACE FUNCTION public.register_match_result(r_match_id bigint, r_participant_ids bigint[], r_results text[], r_scores integer[], r_elo_changes integer[])
 RETURNS text
 LANGUAGE plpgsql
AS $function$
DECLARE
    r_status_name text;
    v_started_at  TIMESTAMP;
    v_comp_id     BIGINT;
BEGIN
    IF cardinality(r_participant_ids) != cardinality(r_results) OR
       cardinality(r_participant_ids) != cardinality(r_scores) OR
       cardinality(r_participant_ids) != cardinality(r_elo_changes) THEN
        RETURN 'Error: Input arrays must have matching dimensions.';
    END IF;

    -- Gather started_at for highly precise partition isolation
    SELECT ms.name, m.started_at INTO r_status_name, v_started_at
    FROM matches m
    JOIN match_statuses ms ON ms.id = m.status_id
    WHERE m.id = r_match_id;

    IF v_started_at IS NULL THEN
        RETURN 'Error: Match does not exist.';
    END IF;

    IF r_status_name NOT IN ('in_progress', 'scheduled') THEN
        RETURN 'Error: The match is over or canceled.';
    END IF;

    -- Primary updates utilize child match indices
    UPDATE match_participants AS mp
    SET result_id  = (SELECT id FROM participant_results WHERE name = arr.res),
        score      = arr.scr,
        elo_change = arr.elo
    FROM unnest(r_participant_ids, r_results, r_scores, r_elo_changes) AS arr(part_id, res, scr, elo)
    WHERE mp.id = arr.part_id
      AND mp.match_id = r_match_id;

    SELECT id INTO v_comp_id FROM match_statuses WHERE name = 'completed';

    -- Included started_at = v_started_at in the WHERE clause for partition pruning
    UPDATE matches
    SET status_id   = v_comp_id,
        ended_at = CURRENT_TIMESTAMP
    WHERE id = r_match_id
      AND started_at = v_started_at;

    RETURN 'OK: Match is over.';
END;
$function$
;

CREATE OR REPLACE FUNCTION public.update_rank()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF OLD.result_id IS NULL AND NEW.result_id IS NOT NULL THEN
        UPDATE profiles
        SET rank_points = GREATEST(0, rank_points + NEW.elo_change)
        WHERE user_id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE TRIGGER update_rank_after_match
AFTER UPDATE OF result_id ON match_participants
FOR EACH ROW
EXECUTE FUNCTION public.update_rank();