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 "lapTimes" (
"raceId" INT,
"driverId" INT,
"lap" INT,
"position" INT,
"time" TEXT,
"milliseconds" INT,
PRIMARY KEY ("raceId", "driverId", "lap"),
FOREIGN KEY ("raceId") REFERENCES "races"("raceId"),
FOREIGN KEY ("driverId") REFERENCES "drivers"("driverId")
);

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

CREATE TABLE "drivers" (
"driverId" INT UNIQUE,
"forename" TEXT,
"surname" TEXT,
PRIMARY KEY ("driverId")
);

CREATE TABLE "races" (
"raceId" INT UNIQUE,
"year" INT,
"round" INT,
"circuitId" INT,
"name" TEXT,
"date" DATE,
"time" TEXT,
PRIMARY KEY ("raceId"),
FOREIGN KEY ("circuitId") REFERENCES "circuits"("circuitId")
);

CREATE TABLE "results" (
"resultId" INT,
"raceId" INT,
"driverId" INT,
"milliseconds" INT,
"fastestLap" INT,
"fastestLapTime" TEXT,
PRIMARY KEY ("resultId"),
FOREIGN KEY ("raceId") REFERENCES "races"("raceId"),
FOREIGN KEY ("driverId") REFERENCES "drivers"("driverId")
);



INSERT INTO "circuits" VALUES ("1", "Silverstone Circuit", "Silverstone", "United Kingdom", "52.0786");
INSERT INTO "circuits" VALUES ("2", "Monza Circuit", "Monza", "Italy", "45.6156");
INSERT INTO "circuits" VALUES ("3", "Circuit de Monaco", "Monte Carlo", "Monaco", "43.7347");
INSERT INTO "circuits" VALUES ("4", "Spa-Francorchamps", "Stavelot", "Belgium", "50.4372");
INSERT INTO "circuits" VALUES ("5", "Suzuka Circuit", "Suzuka", "Japan", "34.8431");
INSERT INTO "drivers" VALUES ("44", "Lewis", "Hamilton");
INSERT INTO "drivers" VALUES ("33", "Max", "Verstappen");
INSERT INTO "drivers" VALUES ("16", "Charles", "Leclerc");
INSERT INTO "drivers" VALUES ("77", "Valtteri", "Bottas");
INSERT INTO "races" VALUES ("101", "2023", "10", "1", "British Grand Prix", "2023-07-09", "14:00:00");
INSERT INTO "races" VALUES ("102", "2023", "11", "2", "Italian Grand Prix", "2023-09-03", "14:00:00");
INSERT INTO "races" VALUES ("103", "2023", "12", "3", "Monaco Grand Prix", "2023-05-28", "14:00:00");
INSERT INTO "races" VALUES ("104", "2023", "13", "4", "Belgian Grand Prix", "2023-08-27", "14:00:00");
INSERT INTO "races" VALUES ("105", "2023", "14", "5", "Japanese Grand Prix", "2023-10-08", "14:00:00");
INSERT INTO "results" VALUES ("201", "101", "44", "5400000", "15", "1:27.345");
INSERT INTO "results" VALUES ("202", "102", "44", "5450000", "20", "1:21.789");
INSERT INTO "results" VALUES ("203", "103", "44", "6000000", "12", "1:12.456");
INSERT INTO "results" VALUES ("204", "104", "44", "5300000", "18", "1:45.123");
INSERT INTO "results" VALUES ("205", "105", "44", "5500000", "22", "1:30.987");
INSERT INTO "lapTimes" VALUES ("101", "44", "15", "1", "1:27.345", "87345");
INSERT INTO "lapTimes" VALUES ("101", "33", "15", "2", "1:27.500", "87500");
INSERT INTO "lapTimes" VALUES ("101", "16", "15", "3", "1:27.600", "87600");
INSERT INTO "lapTimes" VALUES ("102", "44", "20", "2", "1:21.789", "81789");
INSERT INTO "lapTimes" VALUES ("102", "33", "20", "1", "1:21.500", "81500");
INSERT INTO "lapTimes" VALUES ("102", "77", "20", "3", "1:22.000", "82000");
INSERT INTO "lapTimes" VALUES ("103", "44", "12", "1", "1:12.456", "72456");
INSERT INTO "lapTimes" VALUES ("103", "16", "12", "2", "1:12.789", "72789");
INSERT INTO "lapTimes" VALUES ("103", "33", "12", "3", "1:13.000", "73000");
INSERT INTO "lapTimes" VALUES ("104", "44", "18", "3", "1:45.123", "105123");
INSERT INTO "lapTimes" VALUES ("104", "77", "18", "1", "1:44.500", "104500");
INSERT INTO "lapTimes" VALUES ("104", "33", "18", "2", "1:44.900", "104900");
INSERT INTO "lapTimes" VALUES ("105", "44", "22", "1", "1:30.987", "90987");
INSERT INTO "lapTimes" VALUES ("105", "77", "22", "2", "1:31.500", "91500");
INSERT INTO "lapTimes" VALUES ("105", "16", "22", "3", "1:32.000", "92000");