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

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

CREATE TABLE "races" (
"raceId" INT UNIQUE,
"date" DATE,
PRIMARY KEY ("raceId")
);

CREATE TABLE "results" (
"resultId" INT,
"position" INT,
"milliseconds" INT,
PRIMARY KEY ("resultId")
);



INSERT INTO "races" VALUES ("160", "2023-07-10");
INSERT INTO "races" VALUES ("161", "2023-07-17");
INSERT INTO "races" VALUES ("162", "2023-07-24");
INSERT INTO "drivers" VALUES ("10", "http://f1drivers.com/driver10");
INSERT INTO "drivers" VALUES ("11", "http://f1drivers.com/driver11");
INSERT INTO "drivers" VALUES ("12", "http://f1drivers.com/driver12");
INSERT INTO "drivers" VALUES ("13", "http://f1drivers.com/driver13");
INSERT INTO "lapTimes" VALUES ("161", "10", "1", "0:01:27.123");
INSERT INTO "lapTimes" VALUES ("161", "11", "1", "0:01:28.456");
INSERT INTO "lapTimes" VALUES ("161", "12", "1", "0:01:27.999");
INSERT INTO "lapTimes" VALUES ("161", "13", "1", "0:01:30.000");
INSERT INTO "lapTimes" VALUES ("160", "10", "1", "0:01:26.500");
INSERT INTO "lapTimes" VALUES ("162", "11", "1", "0:01:27.000");
INSERT INTO "results" VALUES ("1001", "1", "54000");
INSERT INTO "results" VALUES ("1002", "2", "54500");
INSERT INTO "results" VALUES ("1003", "3", "55000");
INSERT INTO "results" VALUES ("1004", "4", "55500");