-- oastat. General create table file RESTRICT2MYSQL RESTRICT2POSTGRESQL
-- oastat. Create table file for PostgreSQL    RESTRICT2POSTGRESQL
-- oastat. Create table file for MySQL         RESTRICT2MYSQL

-- Note: Lines containing RESTRICT2MYSQL are MySQL specific
-- Note: Lines containing RESTRICT2POSTGRESQL are PostgreSQL specific

-- Warning. InnoDb is the only MySQL backend supporting transactions RESTRICT2MYSQL
SET table_type=INNODB; -- RESTRICT2MYSQL

CREATE TABLE oastat_awards (
    eventNumber SERIAL PRIMARY KEY,
    gamenumber bigint NOT NULL,
    second integer,
    player character varying(64),
    award integer NOT NULL
);


CREATE TABLE oastat_team_events (
    eventnumber SERIAL PRIMARY KEY,
    gamenumber bigint NOT NULL,
    second integer NOT NULL,
    team integer,
    amount integer DEFAULT 1,
    gametype character varying(20) NOT NULL,
    player bigint,
    player2 bigint,
    eventtype integer NOT NULL,
    generic1 integer
);



CREATE TABLE oastat_challenges (
    eventnumber SERIAL PRIMARY KEY,
    gamenumber bigint NOT NULL,
    player bigint NOT NULL,
    challenge integer NOT NULL,
    amount integer DEFAULT 1 NOT NULL
);



CREATE TABLE oastat_games (
    gamenumber SERIAL PRIMARY KEY,
    gametype integer NOT NULL,
    mapname character varying(64) NOT NULL,
    time DATETIME NOT NULL,
    basegame character varying(64) DEFAULT 'baseoa' NOT NULL,
    second integer,
    servername character varying(64) DEFAULT 'noname' NOT NULL
);

COMMENT ON TABLE oastat_games IS 'Has the information common for an entire match/map'; -- RESTRICT2POSTGRESQL

CREATE TABLE oastat_gamecvars (
    gamenumber bigint NOT NULL, -- will be used as primary key later
    cvar character varying(100) NOT NULL, -- will be used as primary key later
    value character varying(256), -- defined as MAX_CVAR_VALUE_STRING in q_shared.h
    numericvalue float -- if value is a float or int, then the value will also be stored here for easier comparison
);

CREATE TABLE oastat_kills (
    eventnumber SERIAL PRIMARY KEY,
    gamenumber bigint NOT NULL,
    second integer NOT NULL,
    attacker bigint NOT NULL,
    target bigint NOT NULL,
    modtype integer NOT NULL
);


CREATE TABLE oastat_players (
    guid character varying(64) NOT NULL,
    playerid SERIAL NOT NULL,
    lastseen DATETIME NOT NULL,
    isbot char(1) DEFAULT 'n' NOT NULL,
    model character varying(64) NOT NULL,
    headmodel character varying(64) NOT NULL,
    nickname character varying(64) NOT NULL
);



CREATE TABLE oastat_points (
    eventnumber SERIAL PRIMARY KEY,
    player bigint NOT NULL,
    second integer NOT NULL,
    score integer NOT NULL,
    gamenumber bigint NOT NULL
);



CREATE TABLE oastat_userinfo (
    eventNumber SERIAL PRIMARY KEY,
    gamenumber bigint NOT NULL,
    player bigint NOT NULL,
    team integer,
    model character varying(64) NOT NULL,
    skill integer,
    second integer NOT NULL
);

CREATE TABLE oastat_uservars (
    userinfoevent bigint NOT NULL, -- will be used as primary key
    thekey character varying(100) NOT NULL, -- will be used as primary key later
    thevalue character varying(256),
    numericvalue float -- if value is a float or int, then the value will also be stored here for easier comparison
);

CREATE TABLE oastat_config (
    thekey character varying(64) PRIMARY KEY,
    thevalue character varying(256)
);

CREATE TABLE oastat_config_uservars2save (
    thekey character varying(100) PRIMARY KEY
);

CREATE TABLE oastat_config_gamevars2save (
    cvar character varying(100) PRIMARY KEY
);

-- CHECKS START

ALTER TABLE oastat_games
    ADD CONSTRAINT oastat_duplet_check UNIQUE (time, servername);

ALTER TABLE oastat_players
    ADD CONSTRAINT oastat_player_index UNIQUE (playerid);

ALTER TABLE oastat_userinfo
    ADD CONSTRAINT oastat_userinfo_second_limit UNIQUE (gamenumber,player,second);

-- CHECKs END

-- PRIMARY KEYS START

-- Notice that pRIMARY KEY must not be only uppercase letters or it will be replaced by a script! 
ALTER TABLE oastat_players
    ADD CONSTRAINT oastat_players_pk pRIMARY KEY (guid);

ALTER TABLE oastat_gamecvars
    ADD CONSTRAINT oastat_gamecvars_pk pRIMARY KEY (gamenumber,cvar);

ALTER TABLE oastat_uservars
    ADD CONSTRAINT oastat_gamevars_pk pRIMARY KEY (userinfoevent,thekey);


-- PRIMARY KEYS END

-- FOREIGN KEYS START

set foreign_key_checks = 0 ; -- RESTRICT2MYSQL

ALTER TABLE oastat_awards -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_awards_gn_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_team_events -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_captures_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_challenges -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_challenges_fk1 FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_challenges -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_challenges_fk2 FOREIGN KEY (player) REFERENCES oastat_players(playerid) ON UPDATE CASCADE ON DELETE RESTRICT; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_kills -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_kills_gn_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_points -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_points_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_points -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_points_fk2 FOREIGN KEY (player) REFERENCES oastat_players(playerid) ON UPDATE CASCADE ON DELETE RESTRICT; -- RESTRICT2POSTGRESQL


ALTER TABLE oastat_userinfo -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_userinfo_gn_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL

ALTER TABLE oastat_gamecvars -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_gamecvars_gn_fk FOREIGN KEY (gamenumber) REFERENCES oastat_games(gamenumber)  ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL

ALTER TABLE oastat_uservars -- RESTRICT2POSTGRESQL
    ADD CONSTRAINT oastat_uservars_ui_fk FOREIGN KEY (userinfoevent) REFERENCES oastat_userinfo(eventNumber) ON UPDATE CASCADE ON DELETE CASCADE; -- RESTRICT2POSTGRESQL

set foreign_key_checks = 1 ; -- RESTRICT2MYSQL

-- FOREIGN KEYS END

-- STANDARD VALUES

-- default uservars to save:
INSERT INTO oastat_config_uservars2save (thekey) VALUES ('hc');

-- default cvars to save:
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('g_instantgib');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('g_rockets');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('dmflags');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('g_lms_mode');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('elimination_roundtime');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('capturelimit');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('sv_maxclients');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('fraglimit');
INSERT INTO oastat_config_gamevars2save (cvar) VALUES ('bot_minplayers');


-- default player for WORLD events:
INSERT INTO oastat_players (guid,playerid,lastseen, isbot, model,headmodel,nickname) VALUES ('WORLD',-1,now(),'n','-','-','-');

COMMIT;

-- STANDARD VALUES END