/*  SCORE TRACKER DATABASE LAYOUT

scores:

    sports:
        *sport_id*   |   sport_name  |   currently_active
    
    divisions:
        *division_id*   |   division_name   |   gender   |   *sport_id*   |   currently_active

    teams:
        *team_id*   |   team_name   |   ~sport_id~  |   currently_active

    seasons:
        *season_id*   |   school_year

    games:
        *game_id*   |   ~division_id~   |   ~season_id~  |   game_date   |   ~team1_id~  |   ~team2_id~   |   team1_score   |   team2_score   |   ~submitter_id~   |   updated_timestamp   |   submitter_name



accounts:

    users:
        *user_id*   |   email   |   password   |   admin   |   full_name

*/


BEGIN;


CREATE SCHEMA IF NOT EXISTS accounts;

CREATE TABLE IF NOT EXISTS accounts.users(
        user_id BIGINT GENERATED ALWAYS AS IDENTITY,
        email TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        admin BOOLEAN NOT NULL DEFAULT FALSE,
        full_name TEXT NOT NULL,
        PRIMARY KEY(user_id)
    );

    

CREATE SCHEMA IF NOT EXISTS scores;


CREATE TABLE IF NOT EXISTS scores.sports(
        sport_id BIGINT GENERATED ALWAYS AS IDENTITY,
        sport_name TEXT UNIQUE NOT NULL,
        currently_active BOOLEAN DEFAULT TRUE,
        PRIMARY KEY(sport_id)
    );

CREATE TABLE IF NOT EXISTS scores.divisions(
        division_id BIGINT GENERATED ALWAYS AS IDENTITY,
        division_name TEXT NOT NULL,
        gender VARCHAR(1) NOT NULL CHECK (gender IN ( 'F', 'M' ) ),
        sport_id BIGINT NOT NULL,
        currently_active BOOLEAN DEFAULT TRUE,
        PRIMARY KEY(division_id),
        CONSTRAINT fk_sport
            FOREIGN KEY(sport_id)
                REFERENCES scores.sports(sport_id)
    );

CREATE TABLE IF NOT EXISTS scores.teams(
        team_id BIGINT GENERATED ALWAYS AS IDENTITY,
        team_name TEXT NOT NULL,
        sport_id BIGINT NOT NULL,
        currently_active BOOLEAN DEFAULT TRUE,
        PRIMARY KEY(team_id),
        CONSTRAINT fk_sport
            FOREIGN KEY(sport_id)
                REFERENCES scores.sports(sport_id)
    );

CREATE TABLE IF NOT EXISTS scores.seasons(
        season_id BIGINT GENERATED ALWAYS AS IDENTITY,
        school_year INTEGER NOT NULL,
        PRIMARY KEY(season_id)
    );

CREATE TABLE IF NOT EXISTS scores.games(
        game_id BIGINT GENERATED ALWAYS AS IDENTITY,
        division_id BIGINT NOT NULL,
        season_id BIGINT NOT NULL,
        game_date DATE NOT NULL,
        team1_id BIGINT NOT NULL,
        team2_id BIGINT NOT NULL,
        team1_score INTEGER NOT NULL,
        team2_score INTEGER NOT NULL,
        submitter_name TEXT,
        submitter_id BIGINT,
        updated_timestamp TIMESTAMP WITH TIME ZONE DEFAULT now(),
        PRIMARY KEY(game_id),
        CONSTRAINT fk_division
            FOREIGN KEY(division_id)
                REFERENCES scores.divisions(division_id),
        CONSTRAINT fk_season
            FOREIGN KEY(season_id)
                REFERENCES scores.seasons(season_id),
        CONSTRAINT fk_team1
            FOREIGN KEY(team1_id)
                REFERENCES scores.teams(team_id),
        CONSTRAINT fk_team2
            FOREIGN KEY(team2_id)
                REFERENCES scores.teams(team_id),
        CONSTRAINT fk_submitter
            FOREIGN KEY(submitter_id)
                REFERENCES accounts.users(user_id)
    );


CREATE TABLE IF NOT EXISTS metadata(
    property TEXT UNIQUE NOT NULL,
    value TEXT NOT NULL
);

INSERT INTO metadata(property, value)
    VALUES("latest_migration", "3");

COMMIT;