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", "Circuit de Catalunya", "Montmeló", "Spain", "41.57", "2.26111");
INSERT INTO "circuits" VALUES ("5", "Istanbul Park", "Istanbul", "Turkey", "40.9517", "29.4058");
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.5272", "-73.5228");
INSERT INTO "circuits" VALUES ("8", "Circuit de Nevers Magny-Cours", "Magny-Cours", "France", "46.8642", "3.16361");
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 "circuits" VALUES ("11", "Hungaroring", "Budapest", "Hungary", "47.5789", "19.2486");
INSERT INTO "circuits" VALUES ("12", "Valencia Street Circuit", "Valencia", "Spain", "39.4625", "-0.358056");
INSERT INTO "circuits" VALUES ("13", "Spa-Francorchamps", "Spa", "Belgium", "50.4394", "5.97139");
INSERT INTO "circuits" VALUES ("14", "Autodromo Nazionale di Monza", "Monza", "Italy", "45.6156", "9.28111");
INSERT INTO "circuits" VALUES ("15", "Marina Bay Street Circuit", "Singapore", "Singapore", "1.29167", "103.864");
INSERT INTO "circuits" VALUES ("16", "Fuji Speedway", "Oyama", "Japan", "35.3717", "138.927");
INSERT INTO "circuits" VALUES ("17", "Shanghai International Circuit", "Shanghai", "China", "31.3389", "121.22");
INSERT INTO "circuits" VALUES ("18", "Autódromo José Carlos Pace", "São Paulo", "Brazil", "-23.7036", "-46.6997");
INSERT INTO "circuits" VALUES ("19", "Yas Marina Circuit", "Abu Dhabi", "UAE", "24.4672", "54.6031");
INSERT INTO "drivers" VALUES ("1", "Lewis", "Hamilton", "British");
INSERT INTO "drivers" VALUES ("2", "Sebastian", "Vettel", "German");
INSERT INTO "drivers" VALUES ("3", "Fernando", "Alonso", "Spanish");
INSERT INTO "drivers" VALUES ("4", "Max", "Verstappen", "Dutch");
INSERT INTO "drivers" VALUES ("5", "Charles", "Leclerc", "Monegasque");
INSERT INTO "drivers" VALUES ("6", "Valtteri", "Bottas", "Finnish");
INSERT INTO "drivers" VALUES ("7", "Sergio", "Pérez", "Mexican");
INSERT INTO "drivers" VALUES ("8", "Daniel", "Ricciardo", "Australian");
INSERT INTO "drivers" VALUES ("9", "Lando", "Norris", "British");
INSERT INTO "drivers" VALUES ("10", "Carlos", "Sainz", "Spanish");
INSERT INTO "races" VALUES ("1", "2023", "1", "Australian Grand Prix");
INSERT INTO "races" VALUES ("2", "2023", "3", "Bahrain Grand Prix");
INSERT INTO "races" VALUES ("3", "2023", "17", "Chinese Grand Prix");
INSERT INTO "races" VALUES ("4", "2023", "4", "Spanish Grand Prix");
INSERT INTO "races" VALUES ("5", "2023", "6", "Monaco Grand Prix");
INSERT INTO "races" VALUES ("6", "2022", "9", "British Grand Prix");
INSERT INTO "races" VALUES ("7", "2022", "13", "Belgian Grand Prix");
INSERT INTO "races" VALUES ("8", "2022", "14", "Italian Grand Prix");
INSERT INTO "races" VALUES ("9", "2021", "7", "Canadian Grand Prix");
INSERT INTO "races" VALUES ("10", "2021", "11", "Hungarian Grand Prix");
INSERT INTO "results" VALUES ("1", "1", "1", "1", "2", "1:29:00.000", "5", "1:23.000");
INSERT INTO "results" VALUES ("2", "1", "2", "2", "1", "1:28:50.000", "3", "1:23.500");
INSERT INTO "results" VALUES ("3", "1", "3", "3", "3", "1:29:10.000", "7", "1:23.200");
INSERT INTO "results" VALUES ("4", "2", "1", "1", "1", "1:30:00.000", "2", "1:31.000");
INSERT INTO "results" VALUES ("5", "2", "4", "4", "2", "1:30:05.000", "1", "1:30.800");
INSERT INTO "results" VALUES ("6", "3", "1", "1", "4", "1:35:00.000", "6", "1:33.500");
INSERT INTO "results" VALUES ("7", "3", "5", "5", "1", "1:34:45.000", "1", "1:33.200");
INSERT INTO "results" VALUES ("8", "4", "1", "1", "3", "1:25:00.000", "4", "1:20.100");
INSERT INTO "results" VALUES ("9", "4", "6", "1", "2", "1:24:55.000", "1", "1:20.050");
INSERT INTO "results" VALUES ("10", "5", "1", "1", "1", "1:40:00.000", "1", "1:15.000");
INSERT INTO "results" VALUES ("11", "5", "7", "7", "5", "1:40:15.000", "3", "1:15.200");
INSERT INTO "results" VALUES ("12", "6", "1", "1", "2", "1:20:00.000", "1", "1:28.000");
INSERT INTO "results" VALUES ("13", "6", "8", "8", "1", "1:19:50.000", "2", "1:28.100");
INSERT INTO "results" VALUES ("14", "7", "1", "1", "6", "1:22:00.000", "1", "1:47.000");
INSERT INTO "results" VALUES ("15", "7", "9", "9", "3", "1:22:10.000", "2", "1:47.100");
INSERT INTO "results" VALUES ("16", "8", "1", "1", "1", "1:18:00.000", "1", "1:21.000");
INSERT INTO "results" VALUES ("17", "8", "10", "10", "4", "1:18:05.000", "3", "1:21.200");
INSERT INTO "results" VALUES ("18", "9", "1", "1", "1", "1:25:00.000", "1", "1:13.000");
INSERT INTO "results" VALUES ("19", "10", "1", "1", "2", "1:28:00.000", "1", "1:18.000");
INSERT INTO "results" VALUES ("20", "10", "2", "2", "1", "1:27:55.000", "2", "1:18.100");