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 "circuits" (
"circuitId" INT UNIQUE,
"name" TEXT,
"location" TEXT,
"country" TEXT,
"lat" FLOAT,
"lng" FLOAT,
PRIMARY KEY ("circuitId")
);

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

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

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



INSERT INTO "circuits" VALUES ("1", "Albert Park Grand Prix Circuit", "Melbourne", "Australia", "-37.8497", "144.968");
INSERT INTO "circuits" VALUES ("2", "Sepang International Circuit", "Kuala Lumpur", "Malaysia", "2.76083", "101.738");
INSERT INTO "circuits" VALUES ("3", "Bahrain International Circuit", "Sakhir", "Bahrain", "26.0325", "50.5106");
INSERT INTO "circuits" VALUES ("4", "Shanghai International Circuit", "Shanghai", "China", "31.3389", "121.22");
INSERT INTO "circuits" VALUES ("5", "Circuit de Barcelona-Catalunya", "Montmeló", "Spain", "41.57", "2.26111");
INSERT INTO "circuits" VALUES ("6", "Circuit de Monaco", "Monte-Carlo", "Monaco", "43.7347", "7.42056");
INSERT INTO "circuits" VALUES ("7", "Circuit Gilles Villeneuve", "Montreal", "Canada", "45.5", "-73.5228");
INSERT INTO "circuits" VALUES ("8", "Red Bull Ring", "Spielberg", "Austria", "47.2197", "14.7647");
INSERT INTO "circuits" VALUES ("9", "Silverstone Circuit", "Silverstone", "UK", "52.0786", "-1.01694");
INSERT INTO "circuits" VALUES ("10", "Hockenheimring", "Hockenheim", "Germany", "49.3278", "8.56583");
INSERT INTO "drivers" VALUES ("1", "Lewis", "Hamilton", "British");
INSERT INTO "drivers" VALUES ("2", "Sebastian", "Vettel", "German");
INSERT INTO "drivers" VALUES ("3", "Max", "Verstappen", "Dutch");
INSERT INTO "drivers" VALUES ("4", "Charles", "Leclerc", "Monegasque");
INSERT INTO "drivers" VALUES ("5", "Valtteri", "Bottas", "Finnish");
INSERT INTO "drivers" VALUES ("6", "Daniel", "Ricciardo", "Australian");
INSERT INTO "drivers" VALUES ("7", "Fernando", "Alonso", "Spanish");
INSERT INTO "drivers" VALUES ("8", "Kimi", "Räikkönen", "Finnish");
INSERT INTO "races" VALUES ("1", "2018", "1", "Australian Grand Prix");
INSERT INTO "races" VALUES ("2", "2018", "3", "Bahrain Grand Prix");
INSERT INTO "races" VALUES ("3", "2018", "4", "Chinese Grand Prix");
INSERT INTO "races" VALUES ("4", "2019", "5", "Spanish Grand Prix");
INSERT INTO "races" VALUES ("5", "2019", "6", "Monaco Grand Prix");
INSERT INTO "races" VALUES ("6", "2020", "7", "Canadian Grand Prix");
INSERT INTO "races" VALUES ("7", "2020", "8", "Austrian Grand Prix");
INSERT INTO "races" VALUES ("8", "2021", "9", "British Grand Prix");
INSERT INTO "races" VALUES ("9", "2021", "10", "German Grand Prix");
INSERT INTO "races" VALUES ("10", "2022", "1", "Australian Grand Prix");
INSERT INTO "results" VALUES ("1", "1", "1", "1", "2", "1:30:00.000", "1", "1:26.000");
INSERT INTO "results" VALUES ("2", "1", "2", "2", "1", "1:29:55.000", "2", "1:26.500");
INSERT INTO "results" VALUES ("3", "2", "1", "1", "3", "1:35:00.000", "3", "1:32.000");
INSERT INTO "results" VALUES ("4", "2", "3", "3", "1", "1:34:50.000", "4", "1:31.500");
INSERT INTO "results" VALUES ("5", "3", "1", "1", "1", "1:38:00.000", "5", "1:34.000");
INSERT INTO "results" VALUES ("6", "3", "4", "4", "2", "1:38:05.000", "6", "1:34.200");
INSERT INTO "results" VALUES ("7", "4", "1", "1", "1", "1:25:00.000", "7", "1:18.000");
INSERT INTO "results" VALUES ("8", "4", "2", "2", "2", "1:25:02.000", "8", "1:18.100");
INSERT INTO "results" VALUES ("9", "5", "1", "1", "1", "1:40:00.000", "9", "1:14.000");
INSERT INTO "results" VALUES ("10", "5", "3", "3", "3", "1:40:10.000", "10", "1:14.500");
INSERT INTO "results" VALUES ("11", "6", "1", "1", "1", "1:28:00.000", "11", "1:13.000");
INSERT INTO "results" VALUES ("12", "6", "5", "5", "2", "1:28:05.000", "12", "1:13.200");
INSERT INTO "results" VALUES ("13", "7", "1", "1", "1", "1:20:00.000", "13", "1:05.000");
INSERT INTO "results" VALUES ("14", "7", "6", "6", "3", "1:20:10.000", "14", "1:05.500");
INSERT INTO "results" VALUES ("15", "8", "1", "1", "1", "1:22:00.000", "15", "1:27.000");
INSERT INTO "results" VALUES ("16", "8", "7", "7", "4", "1:22:15.000", "16", "1:27.500");
INSERT INTO "results" VALUES ("17", "9", "1", "1", "1", "1:30:00.000", "17", "1:15.000");
INSERT INTO "results" VALUES ("18", "9", "8", "8", "5", "1:30:20.000", "18", "1:15.800");
INSERT INTO "results" VALUES ("19", "10", "1", "1", "1", "1:29:00.000", "19", "1:25.000");
INSERT INTO "results" VALUES ("20", "10", "2", "2", "2", "1:29:05.000", "20", "1:25.100");