import sqlite3

conn = sqlite3.connect("football.db")

conn.execute('''CREATE TABLE TEAM(TEAM_NAME VARCHAR(20) PRIMARY KEY NOT NULL ,
TEAM_LOGO_URL VARCHAR(200),
SQUAD_PIC_URL VARCHAR(200),
FOUNDED_ON VARCHAR(20),
HOMEGROUND TEXT(15),
TEAM_COST INT(10),
TEAM_WEBSITE VARCHAR(30),
TEAM_OWNER TEXT(15),
TEAM_COACH TEXT(15),
TEAM_SPONSER TEXT(10),
COUNTRY TEXT(10),
ABOUT TEXT(50))''')

conn.execute('''CREATE TABLE PLAYER(TEAM_NAME VARCHAR(20),
PLAYER_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ,
PLAYER_NAME TEXT(30),
COUNTRY TEXT(10),
AGE INT(2),
PLAYER_PHOTOGRAPH_URL VARCHAR(200),
DATE_OF_BIRTH VARCHAR(15),
NUMBER_OF_GOALS INT(2),
PLAYER_POSITION TEXT(15),
PLAYER_COST INT(10),
JERSEY_NUMBER INT(2),
PLAYER_ABOUT VARCHAR(50),
FOREIGN KEY(TEAM_NAME) REFERENCES TEAM(TEAM_NAME) ON DELETE CASCADE )''')

conn.execute('''CREATE TABLE MATCH_FIXTURE(MATCH_ID INTEGER PRIMARY KEY AUTOINCREMENT ,
MATCH_DATE DATE,
 TEAM1 VARCHAR(20),
 TEAM2 VARCHAR(20),
 FOREIGN KEY(TEAM1) REFERENCES TEAM(TEAM_NAME) ON DELETE CASCADE,
  FOREIGN KEY(TEAM2) REFERENCES TEAM(TEAM_NAME) ON DELETE CASCADE )
 ''')
conn.execute('''CREATE TABLE MATCH_VENUE(
MATCH_ID INTEGER ,
LOCATION VARCHAR(20),
STADIUM VARCHAR(20),
FOREIGN KEY (MATCH_ID) REFERENCES MATCH_FIXTURE(MATCH_ID) ON DELETE CASCADE )''')

conn.execute('''CREATE TABLE MATCH_RESULT(
MATCH_ID VARCHAR(20),
WIN VARCHAR(20),
LOSE VARCHAR(20),
FOREIGN KEY (MATCH_ID) REFERENCES MATCH_FIXTURE(MATCH_ID) ON DELETE  CASCADE ,
FOREIGN KEY(WIN) REFERENCES TEAM(TEAM_NAME) ON DELETE CASCADE,
FOREIGN KEY(LOSE) REFERENCES TEAM(TEAM_NAME) ON DELETE CASCADE
)''')
conn.execute('''CREATE TABLE FEEDBACK(
NAME TEXT,
EMAIL VARCHAR(20),
PRESENTATION VARCHAR(20),
IDEA VARCHAR(20),
OBJECTIVES VARCHAR(20),
SUGGESTION VARCHAR(200),
PRESENTATION_COUNT INT(3),
IDEA_COUNT INT(3),
OBJECTTIVES_COUNT INT(3))
''')

conn.close()
