PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "Country" (
"id" INT UNIQUE,
"name" TEXT,
PRIMARY KEY ("id")
);

CREATE TABLE "League" (
"id" INT UNIQUE,
"country_id" INT,
PRIMARY KEY ("id")
);

CREATE TABLE "Match" (
"id" INT,
"country_id" INT,
"league_id" INT,
"season" TEXT,
"home_team_api_id" INT,
"away_team_api_id" INT,
PRIMARY KEY ("id"),
FOREIGN KEY ("away_team_api_id") REFERENCES "Team"("team_api_id"),
FOREIGN KEY ("home_team_api_id") REFERENCES "Team"("team_api_id"),
FOREIGN KEY ("league_id") REFERENCES "League"("id"),
FOREIGN KEY ("country_id") REFERENCES "Country"("id")
);

CREATE TABLE "Player" (
"id" INT,
"player_api_id" INT UNIQUE,
"height" INT,
PRIMARY KEY ("id")
);

CREATE TABLE "Player_Attributes" (
"id" INT,
"overall_rating" INT,
PRIMARY KEY ("id")
);

CREATE TABLE "Team" (
"id" INT,
"team_api_id" INT UNIQUE,
"team_fifa_api_id" INT UNIQUE,
PRIMARY KEY ("id")
);

CREATE TABLE "Team_Attributes" (
"id" INT,
"buildUpPlaySpeed" INT,
PRIMARY KEY ("id")
);



INSERT INTO "Country" VALUES ("1", "Italy");
INSERT INTO "Country" VALUES ("2", "Spain");
INSERT INTO "Country" VALUES ("3", "Germany");
INSERT INTO "League" VALUES ("10", "1");
INSERT INTO "League" VALUES ("20", "2");
INSERT INTO "League" VALUES ("30", "3");
INSERT INTO "Team" VALUES ("100", "1000", "5000");
INSERT INTO "Team" VALUES ("101", "1001", "5001");
INSERT INTO "Team" VALUES ("102", "1002", "5002");
INSERT INTO "Team" VALUES ("200", "2000", "6000");
INSERT INTO "Team" VALUES ("201", "2001", "6001");
INSERT INTO "Match" VALUES ("1000", "1", "10", "2019/2020", "1000", "1001");
INSERT INTO "Match" VALUES ("1001", "1", "10", "2019/2020", "1001", "1002");
INSERT INTO "Match" VALUES ("1002", "2", "20", "2019/2020", "2000", "2001");
INSERT INTO "Player" VALUES ("1", "50000", "180");
INSERT INTO "Player" VALUES ("2", "50001", "175");
INSERT INTO "Player" VALUES ("3", "50002", "190");
INSERT INTO "Player" VALUES ("4", "60000", "185");
INSERT INTO "Player" VALUES ("5", "60001", "178");
INSERT INTO "Player_Attributes" VALUES ("1", "85");
INSERT INTO "Player_Attributes" VALUES ("2", "88");
INSERT INTO "Player_Attributes" VALUES ("3", "90");
INSERT INTO "Player_Attributes" VALUES ("4", "80");
INSERT INTO "Player_Attributes" VALUES ("5", "82");
INSERT INTO "Team_Attributes" VALUES ("100", "70");
INSERT INTO "Team_Attributes" VALUES ("101", "75");
INSERT INTO "Team_Attributes" VALUES ("102", "65");
INSERT INTO "Team_Attributes" VALUES ("200", "80");
INSERT INTO "Team_Attributes" VALUES ("201", "85");