-- up.sql: Initial database migration for AO

CREATE OR REPLACE FUNCTION public.GETDATE() RETURNS TIMESTAMPTZ
    STABLE LANGUAGE SQL AS 'SELECT NOW()';

 -- base table for users
CREATE TABLE users (
    userid SERIAL,
    email VARCHAR(40) UNIQUE NOT NULL,
    firstname VARCHAR(20) NOT NULL,
    lastname VARCHAR(20) NOT NULL,
    password VARCHAR(255) NOT NULL,    -- intend to use bcrypt hash
    joindate DATE DEFAULT GETDATE(),
    activated BIT(1) DEFAULT B'0',     -- see activation_keys table
    PRIMARY KEY (userid)
);

 -- when activation is completed, key gets deleted and activated bit for user gets flipped to 1.
CREATE TABLE activation_keys (
    code VARCHAR(128),
    userid SERIAL,
    PRIMARY KEY (code),
    CONSTRAINT activation_keys_userid
        FOREIGN KEY (userid) REFERENCES users(userid)
);

 -- base table for games
CREATE TABLE games (
    gameid SERIAL,
    title VARCHAR(140) NOT NULL,
    gametypeid SERIAL NOT NULL,        -- future proofing: suppose different game types
    players SMALLINT NOT NULL,         -- number of players in a game
    waitfor INTEGER NOT NULL,          -- time in seconds to wait for player to complete a turn
    lastturn DATE DEFAULT GETDATE(),
    gamestate INTEGER DEFAULT 1,
    PRIMARY KEY (gameid)
);

 -- associative table: many users, many games
CREATE TABLE allegiances (
    gameid SERIAL,
    userid SERIAL,
    allegiance VARCHAR(140),           -- important/unimportant role for user (depends on gametype)
    ordernum SMALLINT NOT NULL,        -- user # spot in game
    playing SMALLINT DEFAULT 1,        -- default is binary: 0 means player OOP
    PRIMARY KEY (gameid, userid),
    CONSTRAINT users_games_gameid
        FOREIGN KEY (gameid) REFERENCES games(gameid),
    CONSTRAINT users_games_userid
        FOREIGN KEY (userid) REFERENCES users(userid)
);

 -- Regions are composed of nationstates
CREATE TABLE regions (
    regionid SERIAL,
    name VARCHAR(64) NOT NULL,
    abbreviation CHAR(2),
    bonus INTEGER NOT NULL,
    PRIMARY KEY (regionid)
);

 -- base table for nationstates
CREATE TABLE nationstates (
    nationid SERIAL,
    regionid SERIAL,
    name VARCHAR(64) NOT NULL,
    abbreviation CHAR(4),
    PRIMARY KEY (nationid),
    CONSTRAINT nationstates_regionid
        FOREIGN KEY (regionid) REFERENCES regions(regionid)
);

 -- nationstates border nationstates
CREATE TABLE borders (
    nationid SERIAL,
    borderid SERIAL,
    PRIMARY KEY (nationid, borderid),
    CONSTRAINT borders_nationid
        FOREIGN KEY (nationid) REFERENCES nationstates(nationid),
    CONSTRAINT borders_borderid
        FOREIGN KEY (borderid) REFERENCES nationstates(nationid)
);

 -- associative table: many games, many nationstates. Users control them.
CREATE TABLE games_nationstates (
    gameid SERIAL,
    nationid SERIAL,
    userid SERIAL,                     -- owner of nationstate for particular game
    PRIMARY KEY (gameid, nationid),
    CONSTRAINT games_nationstates_gameid
        FOREIGN KEY (gameid) REFERENCES games(gameid),
    CONSTRAINT games_nationstates_nationid
        FOREIGN KEY (nationid) REFERENCES nationstates(nationid),
    CONSTRAINT games_nationstates_userid
        FOREIGN KEY (userid) REFERENCES users(userid)
);