\restrict ZKFwz32JCQnwq2gwoaBzlgpvHoJbPzKHpeaag8wdVrNs8lCtlV7bq2OU8S8bfv6
-- Dumped from database version 16.10 -- Dumped by pg_dump version 16.10
SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off;
CREATE SCHEMA analytics;
ALTER SCHEMA analytics OWNER TO postgres;
CREATE SCHEMA core;
ALTER SCHEMA core OWNER TO postgres;
CREATE SCHEMA game_content;
ALTER SCHEMA game_content OWNER TO postgres;
CREATE SCHEMA gameplay;
ALTER SCHEMA gameplay OWNER TO postgres;
-- -- Name: get_dynamic_stats_report(integer, character varying, integer); Type: FUNCTION; Schema: analytics; Owner: postgres
CREATE FUNCTION analytics.get_dynamic_stats_report(p_tournament_id integer, p_metric character varying, p_min_value integer) RETURNS TABLE(player_name character varying, team_name character varying, metric_value integer)
LANGUAGE plpgsql
AS
-- ╨д╨╛╤А╨╝╨╕╤А╨╛╨▓╨░╨╜╨╕╨╡ ╨┤╨╕╨╜╨░╨╝╨╕╤З╨╡╤Б╨║╨╛╨│╨╛ ╨╖╨░╨┐╤А╨╛╤Б╨░
query_text := format(
'SELECT
p.name AS player_name,
t.name AS team_name,
SUM(ps.%I)::INTEGER AS metric_value
FROM analytics.player_stats ps
JOIN gameplay.games g ON ps.game_id = g.game_id
JOIN gameplay.matches m ON g.match_id = m.match_id
JOIN core.players p ON ps.player_id = p.player_id
JOIN core.teams t ON p.team_id = t.team_id
WHERE m.tournament_id = $1
GROUP BY p.name, t.name
HAVING SUM(ps.%I) >= $2
ORDER BY metric_value DESC',
p_metric, p_metric
);
-- ╨Т╤Л╨┐╨╛╨╗╨╜╨╡╨╜╨╕╨╡ ╨╖╨░╨┐╤А╨╛╤Б╨░
RETURN QUERY EXECUTE query_text USING p_tournament_id, p_min_value;
END;
ALTER FUNCTION analytics.get_dynamic_stats_report(p_tournament_id integer, p_metric character varying, p_min_value integer) OWNER TO postgres;
CREATE FUNCTION analytics.get_player_total_kda(p_player_id integer) RETURNS TABLE(total_kills integer, total_deaths integer, total_assists integer, kda_ratio double precision) LANGUAGE plpgsql AS $$ BEGIN RETURN QUERY SELECT SUM(ps.kills)::INTEGER AS total_kills, SUM(ps.deaths)::INTEGER AS total_deaths, SUM(ps.assists)::INTEGER AS total_assists, CASE WHEN SUM(ps.deaths) = 0 THEN NULL ELSE (SUM(ps.kills) + SUM(ps.assists))::FLOAT / SUM(ps.deaths) END AS kda_ratio FROM analytics.player_stats ps WHERE ps.player_id = p_player_id; END; $$;
ALTER FUNCTION analytics.get_player_total_kda(p_player_id integer) OWNER TO postgres;
CREATE FUNCTION analytics.log_player_stats_changes() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'UPDATE' THEN INSERT INTO analytics.player_stats_history ( stat_id, old_kills, new_kills, old_deaths, new_deaths, old_assists, new_assists, change_type, changed_by ) VALUES ( OLD.stat_id, OLD.kills, NEW.kills, OLD.deaths, NEW.deaths, OLD.assists, NEW.assists, 'UPDATE', CURRENT_USER ); ELSIF TG_OP = 'INSERT' THEN INSERT INTO analytics.player_stats_history ( stat_id, new_kills, new_deaths, new_assists, change_type, changed_by ) VALUES ( NEW.stat_id, NEW.kills, NEW.deaths, NEW.assists, 'INSERT', CURRENT_USER ); ELSIF TG_OP = 'DELETE' THEN INSERT INTO analytics.player_stats_history ( stat_id, old_kills, old_deaths, old_assists, change_type, changed_by ) VALUES ( OLD.stat_id, OLD.kills, OLD.deaths, OLD.assists, 'DELETE', CURRENT_USER ); END IF; RETURN NULL; END; $$;
ALTER FUNCTION analytics.log_player_stats_changes() OWNER TO postgres;
-- -- Name: add_match(integer, integer, integer, character varying, character varying, integer, timestamp without time zone); Type: PROCEDURE; Schema: gameplay; Owner: postgres
CREATE PROCEDURE gameplay.add_match(IN p_tournament_id integer, IN p_team1_id integer, IN p_team2_id integer, IN p_stage character varying, IN p_round character varying, IN p_parent_match_id integer, IN p_start_time timestamp without time zone) LANGUAGE plpgsql AS $$ BEGIN -- ╨Я╤А╨╛╨▓╨╡╤А╨║╨░, ╤З╤В╨╛ ╨║╨╛╨╝╨░╨╜╨┤╤Л ╤Б╤Г╤Й╨╡╤Б╤В╨▓╤Г╤О╤В IF NOT EXISTS (SELECT 1 FROM core.teams WHERE team_id = p_team1_id) THEN RAISE EXCEPTION 'Team1 with ID % does not exist', p_team1_id; END IF; IF NOT EXISTS (SELECT 1 FROM core.teams WHERE team_id = p_team2_id) THEN RAISE EXCEPTION 'Team2 with ID % does not exist', p_team2_id; END IF; -- ╨Я╤А╨╛╨▓╨╡╤А╨║╨░ ╤В╤Г╤А╨╜╨╕╤А╨░ IF NOT EXISTS (SELECT 1 FROM core.tournaments WHERE tournament_id = p_tournament_id) THEN RAISE EXCEPTION 'Tournament with ID % does not exist', p_tournament_id; END IF; -- ╨Я╤А╨╛╨▓╨╡╤А╨║╨░ ╤Н╤В╨░╨┐╨░ IF p_stage NOT IN ('group', 'playoff') THEN RAISE EXCEPTION 'Invalid stage: %', p_stage; END IF; -- ╨Т╤Б╤В╨░╨▓╨║╨░ ╨╝╨░╤В╤З╨░ INSERT INTO gameplay.matches ( tournament_id, team1_id, team2_id, stage, round, parent_match_id, start_time ) VALUES ( p_tournament_id, p_team1_id, p_team2_id, p_stage, p_round, p_parent_match_id, p_start_time ); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END; $$;
ALTER PROCEDURE gameplay.add_match(IN p_tournament_id integer, IN p_team1_id integer, IN p_team2_id integer, IN p_stage character varying, IN p_round character varying, IN p_parent_match_id integer, IN p_start_time timestamp without time zone) OWNER TO postgres;
CREATE FUNCTION gameplay.log_match_changes() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF TG_OP = 'UPDATE' THEN INSERT INTO gameplay.match_history ( match_id, old_winner_id, new_winner_id, change_type, changed_by ) VALUES ( OLD.match_id, OLD.winner_id, NEW.winner_id, 'UPDATE', CURRENT_USER ); ELSIF TG_OP = 'INSERT' THEN INSERT INTO gameplay.match_history ( match_id, new_winner_id, change_type, changed_by ) VALUES ( NEW.match_id, NEW.winner_id, 'INSERT', CURRENT_USER ); ELSIF TG_OP = 'DELETE' THEN INSERT INTO gameplay.match_history ( match_id, old_winner_id, change_type, changed_by ) VALUES ( OLD.match_id, OLD.winner_id, 'DELETE', CURRENT_USER ); END IF; RETURN NULL; END; $$;
ALTER FUNCTION gameplay.log_match_changes() OWNER TO postgres;
-- -- Name: update_match_winner(integer, integer); Type: PROCEDURE; Schema: gameplay; Owner: postgres
CREATE PROCEDURE gameplay.update_match_winner(IN p_match_id integer, IN p_winner_id integer) LANGUAGE plpgsql AS $$ BEGIN -- ╨Я╤А╨╛╨▓╨╡╤А╨║╨░, ╤З╤В╨╛ ╨╝╨░╤В╤З ╤Б╤Г╤Й╨╡╤Б╤В╨▓╤Г╨╡╤В IF NOT EXISTS (SELECT 1 FROM gameplay.matches WHERE match_id = p_match_id) THEN RAISE EXCEPTION 'Match with ID % does not exist', p_match_id; END IF; -- ╨Я╤А╨╛╨▓╨╡╤А╨║╨░, ╤З╤В╨╛ ╨┐╨╛╨▒╨╡╨┤╨╕╤В╨╡╨╗╤М тАФ ╨╛╨┤╨╜╨░ ╨╕╨╖ ╨║╨╛╨╝╨░╨╜╨┤ ╨╝╨░╤В╤З╨░ IF NOT EXISTS ( SELECT 1 FROM gameplay.matches WHERE match_id = p_match_id AND (team1_id = p_winner_id OR team2_id = p_winner_id) ) THEN RAISE EXCEPTION 'Winner ID % is not a participant of match %', p_winner_id, p_match_id; END IF; -- ╨Ю╨▒╨╜╨╛╨▓╨╗╨╡╨╜╨╕╨╡ UPDATE gameplay.matches SET winner_id = p_winner_id WHERE match_id = p_match_id; EXCEPTION WHEN OTHERS THEN RAISE; -- ╨Я╤А╨╛╤Б╤В╨╛ ╨┐╤А╨╛╨▒╤А╨░╤Б╤Л╨▓╨░╨╡╨╝ ╨╕╤Б╨║╨╗╤О╤З╨╡╨╜╨╕╨╡, ╨╛╤В╨║╨░╤В ╨╛╨▒╤А╨░╨▒╨╛╤В╨░╨╡╤В ╨▓╨╜╨╡╤И╨╜╤П╤П ╤В╤А╨░╨╜╨╖╨░╨║╤Ж╨╕╤П END; $$;
ALTER PROCEDURE gameplay.update_match_winner(IN p_match_id integer, IN p_winner_id integer) OWNER TO postgres;
SET default_tablespace = '';
SET default_table_access_method = heap;
CREATE TABLE core.teams ( team_id integer NOT NULL, name character varying(100) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE core.teams OWNER TO postgres;
CREATE TABLE core.tournaments ( tournament_id integer NOT NULL, name character varying(200) NOT NULL, game_type_id integer, start_date date NOT NULL, end_date date, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT tournaments_check CHECK ((end_date >= start_date)) );
ALTER TABLE core.tournaments OWNER TO postgres;
CREATE TABLE gameplay.matches ( match_id integer NOT NULL, tournament_id integer, team1_id integer, team2_id integer, winner_id integer, stage character varying(50) NOT NULL, round character varying(50), parent_match_id integer, start_time timestamp without time zone, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT matches_stage_check CHECK (((stage)::text = ANY ((ARRAY['group'::character varying, 'playoff'::character varying])::text[]))) );
ALTER TABLE gameplay.matches OWNER TO postgres;
CREATE VIEW analytics.match_results_report AS SELECT t.tournament_id, t.name AS tournament_name, m.match_id, m.stage, m.round, t1.name AS team1_name, t2.name AS team2_name, tw.name AS winner_name, m.start_time FROM ((((core.tournaments t JOIN gameplay.matches m ON ((t.tournament_id = m.tournament_id))) JOIN core.teams t1 ON ((m.team1_id = t1.team_id))) JOIN core.teams t2 ON ((m.team2_id = t2.team_id))) LEFT JOIN core.teams tw ON ((m.winner_id = tw.team_id))) ORDER BY m.start_time;
ALTER VIEW analytics.match_results_report OWNER TO postgres;
CREATE TABLE analytics.player_stats ( stat_id integer NOT NULL, game_id integer, player_id integer, hero_id integer, kills integer DEFAULT 0, deaths integer DEFAULT 0, assists integer DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE analytics.player_stats OWNER TO postgres;
CREATE TABLE core.players ( player_id integer NOT NULL, name character varying(100) NOT NULL, team_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE core.players OWNER TO postgres;
CREATE TABLE gameplay.games ( game_id integer NOT NULL, match_id integer, map_id integer, winner_team_id integer, duration integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE gameplay.games OWNER TO postgres;
CREATE VIEW analytics.player_kda_report AS SELECT t.tournament_id, t.name AS tournament_name, p.player_id, p.name AS player_name, tm.name AS team_name, avg(ps.kills) AS avg_kills, avg(ps.deaths) AS avg_deaths, avg(ps.assists) AS avg_assists, CASE WHEN (avg(ps.deaths) = (0)::numeric) THEN NULL::numeric ELSE ((avg(ps.kills) + avg(ps.assists)) / avg(ps.deaths)) END AS kda_ratio FROM (((((core.tournaments t JOIN gameplay.matches m ON ((t.tournament_id = m.tournament_id))) JOIN gameplay.games g ON ((m.match_id = g.match_id))) JOIN analytics.player_stats ps ON ((g.game_id = ps.game_id))) JOIN core.players p ON ((ps.player_id = p.player_id))) JOIN core.teams tm ON ((p.team_id = tm.team_id))) GROUP BY t.tournament_id, t.name, p.player_id, p.name, tm.name ORDER BY CASE WHEN (avg(ps.deaths) = (0)::numeric) THEN NULL::numeric ELSE ((avg(ps.kills) + avg(ps.assists)) / avg(ps.deaths)) END DESC;
ALTER VIEW analytics.player_kda_report OWNER TO postgres;
CREATE TABLE analytics.player_stats_history ( history_id integer NOT NULL, stat_id integer, old_kills integer, new_kills integer, old_deaths integer, new_deaths integer, old_assists integer, new_assists integer, change_type character varying(50), changed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, changed_by character varying(100) );
ALTER TABLE analytics.player_stats_history OWNER TO postgres;
CREATE SEQUENCE analytics.player_stats_history_history_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE analytics.player_stats_history_history_id_seq OWNER TO postgres;
-- -- Name: player_stats_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: analytics; Owner: postgres
ALTER SEQUENCE analytics.player_stats_history_history_id_seq OWNED BY analytics.player_stats_history.history_id;
CREATE SEQUENCE analytics.player_stats_stat_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE analytics.player_stats_stat_id_seq OWNER TO postgres;
ALTER SEQUENCE analytics.player_stats_stat_id_seq OWNED BY analytics.player_stats.stat_id;
CREATE TABLE core.game_types ( game_type_id integer NOT NULL, name character varying(100) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE core.game_types OWNER TO postgres;
CREATE SEQUENCE core.game_types_game_type_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE core.game_types_game_type_id_seq OWNER TO postgres;
ALTER SEQUENCE core.game_types_game_type_id_seq OWNED BY core.game_types.game_type_id;
CREATE SEQUENCE core.players_player_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE core.players_player_id_seq OWNER TO postgres;
ALTER SEQUENCE core.players_player_id_seq OWNED BY core.players.player_id;
CREATE SEQUENCE core.teams_team_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE core.teams_team_id_seq OWNER TO postgres;
ALTER SEQUENCE core.teams_team_id_seq OWNED BY core.teams.team_id;
CREATE SEQUENCE core.tournaments_tournament_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE core.tournaments_tournament_id_seq OWNER TO postgres;
ALTER SEQUENCE core.tournaments_tournament_id_seq OWNED BY core.tournaments.tournament_id;
CREATE TABLE game_content.heroes ( hero_id integer NOT NULL, name character varying(100) NOT NULL, game_type_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE game_content.heroes OWNER TO postgres;
CREATE SEQUENCE game_content.heroes_hero_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE game_content.heroes_hero_id_seq OWNER TO postgres;
ALTER SEQUENCE game_content.heroes_hero_id_seq OWNED BY game_content.heroes.hero_id;
CREATE TABLE game_content.maps ( map_id integer NOT NULL, name character varying(100) NOT NULL, game_type_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE game_content.maps OWNER TO postgres;
CREATE SEQUENCE game_content.maps_map_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE game_content.maps_map_id_seq OWNER TO postgres;
ALTER SEQUENCE game_content.maps_map_id_seq OWNED BY game_content.maps.map_id;
CREATE SEQUENCE gameplay.games_game_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE gameplay.games_game_id_seq OWNER TO postgres;
ALTER SEQUENCE gameplay.games_game_id_seq OWNED BY gameplay.games.game_id;
CREATE TABLE gameplay.match_history ( history_id integer NOT NULL, match_id integer, old_winner_id integer, new_winner_id integer, change_type character varying(50), changed_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, changed_by character varying(100) );
ALTER TABLE gameplay.match_history OWNER TO postgres;
CREATE SEQUENCE gameplay.match_history_history_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE gameplay.match_history_history_id_seq OWNER TO postgres;
-- -- Name: match_history_history_id_seq; Type: SEQUENCE OWNED BY; Schema: gameplay; Owner: postgres
ALTER SEQUENCE gameplay.match_history_history_id_seq OWNED BY gameplay.match_history.history_id;
CREATE SEQUENCE gameplay.matches_match_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE gameplay.matches_match_id_seq OWNER TO postgres;
ALTER SEQUENCE gameplay.matches_match_id_seq OWNED BY gameplay.matches.match_id;
CREATE TABLE public.games ( game_id integer NOT NULL, match_id integer, map_id integer, winner_team_id integer, duration integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.games OWNER TO postgres;
CREATE SEQUENCE public.games_game_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.games_game_id_seq OWNER TO postgres;
ALTER SEQUENCE public.games_game_id_seq OWNED BY public.games.game_id;
CREATE TABLE public.heroes ( hero_id integer NOT NULL, name character varying(100) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.heroes OWNER TO postgres;
CREATE SEQUENCE public.heroes_hero_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.heroes_hero_id_seq OWNER TO postgres;
ALTER SEQUENCE public.heroes_hero_id_seq OWNED BY public.heroes.hero_id;
CREATE TABLE public.maps ( map_id integer NOT NULL, name character varying(100) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.maps OWNER TO postgres;
CREATE SEQUENCE public.maps_map_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.maps_map_id_seq OWNER TO postgres;
ALTER SEQUENCE public.maps_map_id_seq OWNED BY public.maps.map_id;
CREATE TABLE public.matches ( match_id integer NOT NULL, tournament_id integer, team1_id integer, team2_id integer, winner_id integer, stage character varying(50) NOT NULL, round character varying(50), parent_match_id integer, start_time timestamp without time zone, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT matches_stage_check CHECK (((stage)::text = ANY ((ARRAY['group'::character varying, 'playoff'::character varying])::text[]))) );
ALTER TABLE public.matches OWNER TO postgres;
CREATE SEQUENCE public.matches_match_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.matches_match_id_seq OWNER TO postgres;
ALTER SEQUENCE public.matches_match_id_seq OWNED BY public.matches.match_id;
CREATE TABLE public.player_stats ( stat_id integer NOT NULL, game_id integer, player_id integer, hero_id integer, kills integer DEFAULT 0, deaths integer DEFAULT 0, assists integer DEFAULT 0, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.player_stats OWNER TO postgres;
CREATE SEQUENCE public.player_stats_stat_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.player_stats_stat_id_seq OWNER TO postgres;
ALTER SEQUENCE public.player_stats_stat_id_seq OWNED BY public.player_stats.stat_id;
CREATE TABLE public.players ( player_id integer NOT NULL, name character varying(100) NOT NULL, team_id integer, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.players OWNER TO postgres;
CREATE SEQUENCE public.players_player_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.players_player_id_seq OWNER TO postgres;
ALTER SEQUENCE public.players_player_id_seq OWNED BY public.players.player_id;
CREATE TABLE public.teams ( team_id integer NOT NULL, name character varying(100) NOT NULL, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.teams OWNER TO postgres;
CREATE SEQUENCE public.teams_team_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.teams_team_id_seq OWNER TO postgres;
ALTER SEQUENCE public.teams_team_id_seq OWNED BY public.teams.team_id;
CREATE TABLE public.tournaments ( tournament_id integer NOT NULL, name character varying(200) NOT NULL, start_date date NOT NULL, end_date date, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP );
ALTER TABLE public.tournaments OWNER TO postgres;
CREATE SEQUENCE public.tournaments_tournament_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER SEQUENCE public.tournaments_tournament_id_seq OWNER TO postgres;
ALTER SEQUENCE public.tournaments_tournament_id_seq OWNED BY public.tournaments.tournament_id;
ALTER TABLE ONLY analytics.player_stats ALTER COLUMN stat_id SET DEFAULT nextval('analytics.player_stats_stat_id_seq'::regclass);
ALTER TABLE ONLY analytics.player_stats_history ALTER COLUMN history_id SET DEFAULT nextval('analytics.player_stats_history_history_id_seq'::regclass);
ALTER TABLE ONLY core.game_types ALTER COLUMN game_type_id SET DEFAULT nextval('core.game_types_game_type_id_seq'::regclass);
ALTER TABLE ONLY core.players ALTER COLUMN player_id SET DEFAULT nextval('core.players_player_id_seq'::regclass);
ALTER TABLE ONLY core.teams ALTER COLUMN team_id SET DEFAULT nextval('core.teams_team_id_seq'::regclass);
ALTER TABLE ONLY core.tournaments ALTER COLUMN tournament_id SET DEFAULT nextval('core.tournaments_tournament_id_seq'::regclass);
ALTER TABLE ONLY game_content.heroes ALTER COLUMN hero_id SET DEFAULT nextval('game_content.heroes_hero_id_seq'::regclass);
ALTER TABLE ONLY game_content.maps ALTER COLUMN map_id SET DEFAULT nextval('game_content.maps_map_id_seq'::regclass);
ALTER TABLE ONLY gameplay.games ALTER COLUMN game_id SET DEFAULT nextval('gameplay.games_game_id_seq'::regclass);
ALTER TABLE ONLY gameplay.match_history ALTER COLUMN history_id SET DEFAULT nextval('gameplay.match_history_history_id_seq'::regclass);
ALTER TABLE ONLY gameplay.matches ALTER COLUMN match_id SET DEFAULT nextval('gameplay.matches_match_id_seq'::regclass);
ALTER TABLE ONLY public.games ALTER COLUMN game_id SET DEFAULT nextval('public.games_game_id_seq'::regclass);
ALTER TABLE ONLY public.heroes ALTER COLUMN hero_id SET DEFAULT nextval('public.heroes_hero_id_seq'::regclass);
ALTER TABLE ONLY public.maps ALTER COLUMN map_id SET DEFAULT nextval('public.maps_map_id_seq'::regclass);
ALTER TABLE ONLY public.matches ALTER COLUMN match_id SET DEFAULT nextval('public.matches_match_id_seq'::regclass);
ALTER TABLE ONLY public.player_stats ALTER COLUMN stat_id SET DEFAULT nextval('public.player_stats_stat_id_seq'::regclass);
ALTER TABLE ONLY public.players ALTER COLUMN player_id SET DEFAULT nextval('public.players_player_id_seq'::regclass);
ALTER TABLE ONLY public.teams ALTER COLUMN team_id SET DEFAULT nextval('public.teams_team_id_seq'::regclass);
ALTER TABLE ONLY public.tournaments ALTER COLUMN tournament_id SET DEFAULT nextval('public.tournaments_tournament_id_seq'::regclass);
-- -- Name: player_stats_history player_stats_history_pkey; Type: CONSTRAINT; Schema: analytics; Owner: postgres
ALTER TABLE ONLY analytics.player_stats_history ADD CONSTRAINT player_stats_history_pkey PRIMARY KEY (history_id);
ALTER TABLE ONLY analytics.player_stats ADD CONSTRAINT player_stats_pkey PRIMARY KEY (stat_id);
ALTER TABLE ONLY core.game_types ADD CONSTRAINT game_types_name_key UNIQUE (name);
ALTER TABLE ONLY core.game_types ADD CONSTRAINT game_types_pkey PRIMARY KEY (game_type_id);
ALTER TABLE ONLY core.players ADD CONSTRAINT players_pkey PRIMARY KEY (player_id);
ALTER TABLE ONLY core.teams ADD CONSTRAINT teams_name_key UNIQUE (name);
ALTER TABLE ONLY core.teams ADD CONSTRAINT teams_pkey PRIMARY KEY (team_id);
ALTER TABLE ONLY core.tournaments ADD CONSTRAINT tournaments_pkey PRIMARY KEY (tournament_id);
ALTER TABLE ONLY game_content.heroes ADD CONSTRAINT heroes_name_key UNIQUE (name);
ALTER TABLE ONLY game_content.heroes ADD CONSTRAINT heroes_pkey PRIMARY KEY (hero_id);
ALTER TABLE ONLY game_content.maps ADD CONSTRAINT maps_name_key UNIQUE (name);
ALTER TABLE ONLY game_content.maps ADD CONSTRAINT maps_pkey PRIMARY KEY (map_id);
ALTER TABLE ONLY gameplay.games ADD CONSTRAINT games_pkey PRIMARY KEY (game_id);
ALTER TABLE ONLY gameplay.match_history ADD CONSTRAINT match_history_pkey PRIMARY KEY (history_id);
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_pkey PRIMARY KEY (match_id);
ALTER TABLE ONLY public.games ADD CONSTRAINT games_pkey PRIMARY KEY (game_id);
ALTER TABLE ONLY public.heroes ADD CONSTRAINT heroes_name_key UNIQUE (name);
ALTER TABLE ONLY public.heroes ADD CONSTRAINT heroes_pkey PRIMARY KEY (hero_id);
ALTER TABLE ONLY public.maps ADD CONSTRAINT maps_name_key UNIQUE (name);
ALTER TABLE ONLY public.maps ADD CONSTRAINT maps_pkey PRIMARY KEY (map_id);
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_pkey PRIMARY KEY (match_id);
ALTER TABLE ONLY public.player_stats ADD CONSTRAINT player_stats_pkey PRIMARY KEY (stat_id);
ALTER TABLE ONLY public.players ADD CONSTRAINT players_pkey PRIMARY KEY (player_id);
ALTER TABLE ONLY public.teams ADD CONSTRAINT teams_name_key UNIQUE (name);
ALTER TABLE ONLY public.teams ADD CONSTRAINT teams_pkey PRIMARY KEY (team_id);
ALTER TABLE ONLY public.tournaments ADD CONSTRAINT tournaments_pkey PRIMARY KEY (tournament_id);
CREATE INDEX idx_player_stats_game ON analytics.player_stats USING btree (game_id);
CREATE INDEX idx_player_stats_hero ON analytics.player_stats USING btree (hero_id);
CREATE INDEX idx_player_stats_player ON analytics.player_stats USING btree (player_id);
CREATE INDEX idx_games_map ON gameplay.games USING btree (map_id);
CREATE INDEX idx_games_match ON gameplay.games USING btree (match_id);
CREATE INDEX idx_matches_tournament ON gameplay.matches USING btree (tournament_id);
CREATE INDEX idx_games_map ON public.games USING btree (map_id);
CREATE INDEX idx_games_match ON public.games USING btree (match_id);
CREATE INDEX idx_matches_tournament ON public.matches USING btree (tournament_id);
CREATE INDEX idx_player_stats_game ON public.player_stats USING btree (game_id);
CREATE INDEX idx_player_stats_hero ON public.player_stats USING btree (hero_id);
CREATE INDEX idx_player_stats_player ON public.player_stats USING btree (player_id);
-- -- Name: player_stats player_stats_history_trigger; Type: TRIGGER; Schema: analytics; Owner: postgres
CREATE TRIGGER player_stats_history_trigger AFTER INSERT OR DELETE OR UPDATE ON analytics.player_stats FOR EACH ROW EXECUTE FUNCTION analytics.log_player_stats_changes();
CREATE TRIGGER match_history_trigger AFTER INSERT OR DELETE OR UPDATE ON gameplay.matches FOR EACH ROW EXECUTE FUNCTION gameplay.log_match_changes();
-- -- Name: player_stats player_stats_game_id_fkey; Type: FK CONSTRAINT; Schema: analytics; Owner: postgres
ALTER TABLE ONLY analytics.player_stats ADD CONSTRAINT player_stats_game_id_fkey FOREIGN KEY (game_id) REFERENCES gameplay.games(game_id) ON DELETE CASCADE;
-- -- Name: player_stats player_stats_hero_id_fkey; Type: FK CONSTRAINT; Schema: analytics; Owner: postgres
ALTER TABLE ONLY analytics.player_stats ADD CONSTRAINT player_stats_hero_id_fkey FOREIGN KEY (hero_id) REFERENCES game_content.heroes(hero_id);
-- -- Name: player_stats_history player_stats_history_stat_id_fkey; Type: FK CONSTRAINT; Schema: analytics; Owner: postgres
ALTER TABLE ONLY analytics.player_stats_history ADD CONSTRAINT player_stats_history_stat_id_fkey FOREIGN KEY (stat_id) REFERENCES analytics.player_stats(stat_id);
-- -- Name: player_stats player_stats_player_id_fkey; Type: FK CONSTRAINT; Schema: analytics; Owner: postgres
ALTER TABLE ONLY analytics.player_stats ADD CONSTRAINT player_stats_player_id_fkey FOREIGN KEY (player_id) REFERENCES core.players(player_id);
ALTER TABLE ONLY core.players ADD CONSTRAINT players_team_id_fkey FOREIGN KEY (team_id) REFERENCES core.teams(team_id) ON DELETE CASCADE;
-- -- Name: tournaments tournaments_game_type_id_fkey; Type: FK CONSTRAINT; Schema: core; Owner: postgres
ALTER TABLE ONLY core.tournaments ADD CONSTRAINT tournaments_game_type_id_fkey FOREIGN KEY (game_type_id) REFERENCES core.game_types(game_type_id);
-- -- Name: heroes heroes_game_type_id_fkey; Type: FK CONSTRAINT; Schema: game_content; Owner: postgres
ALTER TABLE ONLY game_content.heroes ADD CONSTRAINT heroes_game_type_id_fkey FOREIGN KEY (game_type_id) REFERENCES core.game_types(game_type_id);
ALTER TABLE ONLY game_content.maps ADD CONSTRAINT maps_game_type_id_fkey FOREIGN KEY (game_type_id) REFERENCES core.game_types(game_type_id);
ALTER TABLE ONLY gameplay.games ADD CONSTRAINT games_map_id_fkey FOREIGN KEY (map_id) REFERENCES game_content.maps(map_id);
ALTER TABLE ONLY gameplay.games ADD CONSTRAINT games_match_id_fkey FOREIGN KEY (match_id) REFERENCES gameplay.matches(match_id) ON DELETE CASCADE;
ALTER TABLE ONLY gameplay.games ADD CONSTRAINT games_winner_team_id_fkey FOREIGN KEY (winner_team_id) REFERENCES core.teams(team_id);
-- -- Name: match_history match_history_match_id_fkey; Type: FK CONSTRAINT; Schema: gameplay; Owner: postgres
ALTER TABLE ONLY gameplay.match_history ADD CONSTRAINT match_history_match_id_fkey FOREIGN KEY (match_id) REFERENCES gameplay.matches(match_id);
-- -- Name: matches matches_parent_match_id_fkey; Type: FK CONSTRAINT; Schema: gameplay; Owner: postgres
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_parent_match_id_fkey FOREIGN KEY (parent_match_id) REFERENCES gameplay.matches(match_id);
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_team1_id_fkey FOREIGN KEY (team1_id) REFERENCES core.teams(team_id);
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_team2_id_fkey FOREIGN KEY (team2_id) REFERENCES core.teams(team_id);
-- -- Name: matches matches_tournament_id_fkey; Type: FK CONSTRAINT; Schema: gameplay; Owner: postgres
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_tournament_id_fkey FOREIGN KEY (tournament_id) REFERENCES core.tournaments(tournament_id) ON DELETE CASCADE;
ALTER TABLE ONLY gameplay.matches ADD CONSTRAINT matches_winner_id_fkey FOREIGN KEY (winner_id) REFERENCES core.teams(team_id);
ALTER TABLE ONLY public.games ADD CONSTRAINT games_map_id_fkey FOREIGN KEY (map_id) REFERENCES public.maps(map_id);
ALTER TABLE ONLY public.games ADD CONSTRAINT games_match_id_fkey FOREIGN KEY (match_id) REFERENCES public.matches(match_id) ON DELETE CASCADE;
ALTER TABLE ONLY public.games ADD CONSTRAINT games_winner_team_id_fkey FOREIGN KEY (winner_team_id) REFERENCES public.teams(team_id);
-- -- Name: matches matches_parent_match_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_parent_match_id_fkey FOREIGN KEY (parent_match_id) REFERENCES public.matches(match_id);
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_team1_id_fkey FOREIGN KEY (team1_id) REFERENCES public.teams(team_id);
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_team2_id_fkey FOREIGN KEY (team2_id) REFERENCES public.teams(team_id);
-- -- Name: matches matches_tournament_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_tournament_id_fkey FOREIGN KEY (tournament_id) REFERENCES public.tournaments(tournament_id) ON DELETE CASCADE;
ALTER TABLE ONLY public.matches ADD CONSTRAINT matches_winner_id_fkey FOREIGN KEY (winner_id) REFERENCES public.teams(team_id);
-- -- Name: player_stats player_stats_game_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
ALTER TABLE ONLY public.player_stats ADD CONSTRAINT player_stats_game_id_fkey FOREIGN KEY (game_id) REFERENCES public.games(game_id) ON DELETE CASCADE;
-- -- Name: player_stats player_stats_hero_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
ALTER TABLE ONLY public.player_stats ADD CONSTRAINT player_stats_hero_id_fkey FOREIGN KEY (hero_id) REFERENCES public.heroes(hero_id);
-- -- Name: player_stats player_stats_player_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
ALTER TABLE ONLY public.player_stats ADD CONSTRAINT player_stats_player_id_fkey FOREIGN KEY (player_id) REFERENCES public.players(player_id);
ALTER TABLE ONLY public.players ADD CONSTRAINT players_team_id_fkey FOREIGN KEY (team_id) REFERENCES public.teams(team_id) ON DELETE CASCADE;
\unrestrict ZKFwz32JCQnwq2gwoaBzlgpvHoJbPzKHpeaag8wdVrNs8lCtlV7bq2OU8S8bfv6