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 "constructorResults" (
"constructorResultsId" INT,
"raceId" INT,
"constructorId" INT,
"points" FLOAT,
"status" TEXT,
PRIMARY KEY ("constructorResultsId"),
FOREIGN KEY ("raceId") REFERENCES "races"("raceId"),
FOREIGN KEY ("constructorId") REFERENCES "constructors"("constructorId")
);

CREATE TABLE "constructorStandings" (
"constructorStandingsId" INT,
"raceId" INT,
"constructorId" INT,
"points" FLOAT,
"position" INT,
"positionText" TEXT,
"wins" INT,
PRIMARY KEY ("constructorStandingsId"),
FOREIGN KEY ("raceId") REFERENCES "races"("raceId"),
FOREIGN KEY ("constructorId") REFERENCES "constructors"("constructorId")
);

CREATE TABLE "circuits" (
"circuitId" INT UNIQUE,
"name" TEXT,
"location" TEXT,
"country" TEXT,
PRIMARY KEY ("circuitId")
);

CREATE TABLE "constructors" (
"constructorId" INT UNIQUE,
"name" TEXT,
"nationality" TEXT,
PRIMARY KEY ("constructorId")
);

CREATE TABLE "races" (
"raceId" INT UNIQUE,
"year" INT,
"round" INT,
"circuitId" INT,
PRIMARY KEY ("raceId"),
FOREIGN KEY ("circuitId") REFERENCES "circuits"("circuitId")
);



INSERT INTO "circuits" VALUES ("1", "Circuit de Monaco", "Monte Carlo", "Monaco");
INSERT INTO "circuits" VALUES ("2", "Silverstone Circuit", "Silverstone", "United Kingdom");
INSERT INTO "circuits" VALUES ("3", "Circuit Gilles Villeneuve", "Montreal", "Canada");
INSERT INTO "circuits" VALUES ("4", "Suzuka Circuit", "Suzuka", "Japan");
INSERT INTO "circuits" VALUES ("5", "Circuit de Spa-Francorchamps", "Stavelot", "Belgium");
INSERT INTO "constructors" VALUES ("10", "Renault", "French");
INSERT INTO "constructors" VALUES ("20", "Mercedes", "German");
INSERT INTO "constructors" VALUES ("30", "Ferrari", "Italian");
INSERT INTO "races" VALUES ("100", "2023", "1", "1");
INSERT INTO "races" VALUES ("101", "2023", "2", "2");
INSERT INTO "races" VALUES ("102", "2023", "3", "3");
INSERT INTO "races" VALUES ("103", "2023", "4", "4");
INSERT INTO "races" VALUES ("104", "2023", "5", "5");
INSERT INTO "constructorResults" VALUES ("1000", "100", "10", "18.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1001", "101", "10", "15.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1002", "102", "10", "12.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1003", "103", "10", "10.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1004", "104", "10", "8.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1005", "100", "20", "25.0", "Finished");
INSERT INTO "constructorResults" VALUES ("1006", "101", "20", "22.0", "Finished");
INSERT INTO "constructorStandings" VALUES ("2000", "100", "10", "18.0", "3", "3", "1");
INSERT INTO "constructorStandings" VALUES ("2001", "101", "10", "33.0", "4", "4", "1");
INSERT INTO "constructorStandings" VALUES ("2002", "102", "10", "45.0", "5", "5", "1");
INSERT INTO "constructorStandings" VALUES ("2003", "103", "10", "55.0", "6", "6", "1");
INSERT INTO "constructorStandings" VALUES ("2004", "104", "10", "63.0", "7", "7", "1");
INSERT INTO "constructorStandings" VALUES ("2005", "100", "20", "25.0", "1", "1", "3");
INSERT INTO "constructorStandings" VALUES ("2006", "101", "20", "47.0", "2", "2", "3");