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 "drivers" (
"driverId" INT,
"driverRef" TEXT,
PRIMARY KEY ("driverId")
);

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

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

CREATE TABLE "status" (
"statusId" INT UNIQUE,
"status" TEXT,
PRIMARY KEY ("statusId")
);



INSERT INTO "drivers" VALUES ("1", "hamilton");
INSERT INTO "drivers" VALUES ("2", "verstappen");
INSERT INTO "drivers" VALUES ("3", "vettel");
INSERT INTO "drivers" VALUES ("4", "leclerc");
INSERT INTO "drivers" VALUES ("5", "bottas");
INSERT INTO "drivers" VALUES ("6", "norris");
INSERT INTO "drivers" VALUES ("7", "ricciardo");
INSERT INTO "drivers" VALUES ("8", "alonso");
INSERT INTO "races" VALUES ("49", "2023-06-01");
INSERT INTO "races" VALUES ("50", "2023-06-15");
INSERT INTO "races" VALUES ("51", "2023-06-29");
INSERT INTO "races" VALUES ("75", "2023-09-10");
INSERT INTO "races" VALUES ("99", "2023-11-05");
INSERT INTO "races" VALUES ("100", "2023-11-19");
INSERT INTO "races" VALUES ("101", "2023-12-03");
INSERT INTO "status" VALUES ("1", "Finished");
INSERT INTO "status" VALUES ("2", "Disqualified");
INSERT INTO "status" VALUES ("3", "Accident");
INSERT INTO "status" VALUES ("4", "Retired");
INSERT INTO "results" VALUES ("1", "50", "1", "1", "1:30:15.123", "1");
INSERT INTO "results" VALUES ("2", "50", "2", "2", "1:30:20.456", "1");
INSERT INTO "results" VALUES ("3", "50", "3", "3", "1:30:45.789", "1");
INSERT INTO "results" VALUES ("4", "50", "4", "4", "1:31:00.000", "2");
INSERT INTO "results" VALUES ("5", "50", "5", "5", "null", "2");
INSERT INTO "results" VALUES ("6", "51", "6", "1", "1:29:50.321", "1");
INSERT INTO "results" VALUES ("7", "51", "7", "2", "1:30:10.654", "2");
INSERT INTO "results" VALUES ("8", "51", "8", "3", "null", "3");
INSERT INTO "results" VALUES ("9", "75", "1", "1", "1:28:30.111", "1");
INSERT INTO "results" VALUES ("10", "75", "2", "2", "1:28:45.222", "2");
INSERT INTO "results" VALUES ("11", "75", "3", "3", "null", "4");
INSERT INTO "results" VALUES ("12", "99", "4", "1", "1:27:15.333", "1");
INSERT INTO "results" VALUES ("13", "99", "5", "2", "1:27:30.444", "2");
INSERT INTO "results" VALUES ("14", "99", "6", "3", "null", "2");
INSERT INTO "results" VALUES ("15", "100", "7", "1", "1:26:50.555", "1");
INSERT INTO "results" VALUES ("16", "100", "8", "2", "1:27:05.666", "1");
INSERT INTO "results" VALUES ("17", "100", "1", "3", "1:27:20.777", "2");
INSERT INTO "results" VALUES ("18", "101", "2", "1", "1:26:00.888", "1");
INSERT INTO "results" VALUES ("19", "101", "3", "2", "1:26:15.999", "2");