


CREATE TABLE oastat_awards (
    eventNumber INTEGER PRIMARY KEY,
    gamenumber bigint unsigned NOT NULL,
    second integer NOT NULL,
    player bigint unsigned NOT NULL,
    award integer NOT NULL
);


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



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



CREATE TABLE oastat_games (
    gamenumber INTEGER 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, -- is null until the match is over
    servername character varying(64) DEFAULT 'noname' NOT NULL
);


CREATE TABLE oastat_gamecvars (
    gamenumber bigint unsigned 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 INTEGER PRIMARY KEY,
    gamenumber bigint unsigned NOT NULL,
    second integer NOT NULL,
    attacker bigint unsigned NOT NULL,
    target bigint unsigned NOT NULL,
    modtype integer NOT NULL
);


CREATE TABLE oastat_players (
    guid character varying(64) NOT NULL,
    playerid INTEGER PRIMARY KEY NOT NULL, -- all small UNIQUE will be removed in mySQL, required for postgres
    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 INTEGER PRIMARY KEY,
    player bigint unsigned NOT NULL,
    second integer NOT NULL,
    score integer NOT NULL,
    gamenumber bigint unsigned NOT NULL
);



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

CREATE TABLE oastat_uservars (
    userinfoevent bigint unsigned 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



-- CHECKs END

-- pRIMARY KEYS START

-- Notice that pRIMARY KEY must not be only uppercase letters or it will be replaced by a script! 




-- pRIMARY KEYS END

-- FOREIGN KEYS START



















-- FOREIGN KEYS END

-- STANDARD VALUES

BEGIN;

-- 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,lastseen, isbot, model,headmodel,nickname) VALUES ('WORLD',date('now'),'n','-','-','-');
UPDATE oastat_players SET playerid = 0 WHERE guid = 'WORLD';

COMMIT;

-- STANDARD VALUES END

-- VERSION 2

CREATE TABLE oastat_accuracy (
    gamenumber bigint unsigned NOT NULL,
    player bigint unsigned NOT NULL,
    shotsfired integer NOT NULL,
	shotshit integer NOT NULL,
    modtype integer NOT NULL
);




