import os
import sys
import psycopg2 as dbapi2



INIT_STATEMENTS = [
    '''CREATE TABLE IF NOT EXISTS PERSON (
        ID SERIAL PRIMARY KEY, 
        NAME VARCHAR(30) NOT NULL,
        BIRTHYEAR INTEGER CHECK(BIRTHYEAR > 1950 AND BIRTHYEAR < 2019),
        NATIONALITY VARCHAR(20) NOT NULL DEFAULT 'unknown',
        PERSONPHOTO VARCHAR(600) NOT NULL,
        HEIGHT INTEGER CHECK(HEIGHT > 0),
        WEIGHT INTEGER CHECK (WEIGHT > 0) )''',

    '''CREATE TABLE IF NOT EXISTS LEAGUE (
        ID SERIAL PRIMARY KEY, 
        NAME VARCHAR(20) NOT NULL,
        TEAMCOUNT INTEGER DEFAULT 0 CHECK(TEAMCOUNT > 0),
        COUNTRY VARCHAR(20) NOT NULL,
        ESTABLISHYEAR INTEGER CHECK (ESTABLISHYEAR > 1860),
        DIVISION INTEGER CHECK (DIVISION >0) )''',

    '''CREATE TABLE IF NOT EXISTS STADIUM (
        ID SERIAL PRIMARY KEY, 
        NAME VARCHAR(20) NOT NULL,
        CAPACITY INTEGER CHECK(CAPACITY >= 0),
        CITY VARCHAR(20) DEFAULT 'unknown',
        ESTABLISHYEAR INTEGER CHECK (ESTABLISHYEAR > 1860),
        BUDGET INTEGER)''',

    '''CREATE TABLE IF NOT EXISTS TEAM (
        ID SERIAL PRIMARY KEY, 
        NAME VARCHAR(20) NOT NULL,
        LEAGUEID INTEGER REFERENCES LEAGUE(ID),
        STADIUMID INTEGER REFERENCES STADIUM(ID),
        COACH INTEGER REFERENCES PERSON(ID),
        TEAMLOGO VARCHAR (600) NOT NULL,
        FANCOUNT INTEGER CHECK (FANCOUNT > 0),
        CITY VARCHAR(20) DEFAULT 'unknown',
        ESTABLISHYEAR INTEGER CHECK (ESTABLISHYEAR > 1860))
        ''',

    '''CREATE TABLE IF NOT EXISTS STANDING (
        TEAMID INTEGER REFERENCES TEAM(ID), 
        LEAGUEID INTEGER REFERENCES LEAGUE(ID),
        WIN INTEGER CHECK(WIN >= 0),
        LOSE INTEGER CHECK(LOSE >= 0),
        DRAW INTEGER CHECK(DRAW >= 0),
        SCOREDGOALS INTEGER CHECK (SCOREDGOALS >= 0),
        AGAINSTGOALS INTEGER CHECK (AGAINSTGOALS >= 0),
        PRIMARY KEY (TEAMID, LEAGUEID))''',

    '''CREATE TABLE IF NOT EXISTS MATCH (
        ID SERIAL PRIMARY KEY, 
        HOMEID INTEGER REFERENCES TEAM(ID),
        AWAYID INTEGER REFERENCES TEAM(ID),
        HOMESCORE INTEGER CHECK(HOMESCORE>=0),
        AWAYSCORE INTEGER CHECK(AWAYSCORE>=0),
        STADIUMID INTEGER REFERENCES STADIUM(ID),
        LEAGUEID INTEGER REFERENCES LEAGUE(ID),
        MATCHDATE DATE CHECK(MATCHDATE<CURRENT_DATE),
        EXTRATIME1 INTEGER CHECK(EXTRATIME1 > 0),
        EXTRATIME2 INTEGER CHECK(EXTRATIME2 > 0) )''',

    '''CREATE TABLE IF NOT EXISTS GOAL (
        ID SERIAL PRIMARY KEY, 
        PLAYERID INTEGER REFERENCES PERSON(ID),
        MATCHID INTEGER REFERENCES MATCH(ID),
        MINUTE INTEGER CHECK(MINUTE<=120 AND MINUTE >=0),
        GOALTYPE INTEGER,
        DISTANCE INTEGER CHECK(DISTANCE > 0),
        ISFREEKICK BOOL DEFAULT FALSE,
        RATING INTEGER CHECK (RATING > 0 AND RATING < 10))
        ''',

    '''CREATE TABLE IF NOT EXISTS CARD (
        ID SERIAL PRIMARY KEY, 
        PLAYERID INTEGER REFERENCES PERSON(ID),
        RED BOOL DEFAULT FALSE,
        MATCHID INTEGER REFERENCES MATCH(ID),
        MINUTE INTEGER CHECK(MINUTE<=120 AND MINUTE >=0),
        YELLOW1 BOOL DEFAULT TRUE,
        YELLOW2 BOOL DEFAULT FALSE,
        BANDURATION INTEGER DEFAULT 1)''',

    '''CREATE TABLE IF NOT EXISTS SUBSTITUTION (
        ID SERIAL PRIMARY KEY, 
        OUTPLAYERID INTEGER REFERENCES PERSON(ID),
        INPLAYERID INTEGER REFERENCES PERSON(ID),
        MATCHID INTEGER REFERENCES MATCH(ID),
        MINUTE INTEGER CHECK(MINUTE<=120 AND MINUTE >=0),
        AFTERCORNER	BOOL DEFAULT FALSE,
        AFTEROFFSIDE BOOL DEFAULT FALSE,
        AFTERGOAL BOOL DEFAULT FALSE,
        AFTEROUT BOOL DEFAULT FALSE)''',

    '''CREATE TABLE IF NOT EXISTS ASSIST (
        ID SERIAL PRIMARY KEY, 
        PLAYERID INTEGER REFERENCES PERSON(ID),
        GOALID INTEGER REFERENCES GOAL(ID),
        PASSDISTANCE INTEGER CHECK(PASSDISTANCE > 0),
        ASSISTTYPE INTEGER,
        ISFREEKICK BOOL DEFAULT FALSE,
        ISCROSS BOOL DEFAULT FALSE,
        RATING INTEGER CHECK(RATING > 0 AND RATING < 10))''',

    '''CREATE TABLE IF NOT EXISTS NEGOTITATION (
        ID SERIAL PRIMARY KEY, 
        PERSONID INTEGER REFERENCES PERSON(ID),
        TEAMID INTEGER REFERENCES TEAM(ID),
        DURATION INTEGER CHECK(DURATION < 10),
        STARTDATE DATE CHECK(STARTDATE < CURRENT_DATE),
        AMOUNT INTEGER CHECK(AMOUNT>=0),
        RELASECOST INTEGER CHECK (RELASECOST > 0),
        ISRENT BOOL DEFAULT FALSE)''',
    
    '''CREATE TABLE IF NOT EXISTS SQUAD (
        PERSONID INTEGER REFERENCES PERSON(ID),
        TEAMID INTEGER REFERENCES TEAM(ID),
        POSITION VARCHAR(3),
        KITNUMBER INTEGER,
        SECONDPOSITION VARCHAR(3),
        FOOT VARCHAR(5),
        INJURYMONTH	INTEGER DEFAULT 0,
        PRIMARY KEY (PERSONID, TEAMID) )''',

    '''CREATE TABLE IF NOT EXISTS ACCOUNT (
        ID SERIAL PRIMARY KEY,
        USERNAME VARCHAR(20) UNIQUE,
        PASSWORD VARCHAR(256),
        NAME VARCHAR(20),
        EMAIL VARCHAR(30),
        AGE INTEGER CHECK(AGE > 0))''',
        
]

def initialize(url):
    with dbapi2.connect(url) as connection:
        cursor = connection.cursor()
        for statement in INIT_STATEMENTS:
            cursor.execute(statement)
        cursor.close()


if __name__ == "__main__":
    url =  os.environ.get('DB_URI', None)
    initialize(url)