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 "CONTESTANTS" (
"contestant_number" INT UNIQUE,
"contestant_name" VARCHAR(50),
PRIMARY KEY ("contestant_number")
);

CREATE TABLE "VOTES" (
"vote_id" INT,
"state" VARCHAR(2),
"contestant_number" INT,
"created" TIMESTAMP,
PRIMARY KEY ("vote_id"),
FOREIGN KEY ("contestant_number") REFERENCES "CONTESTANTS"("contestant_number")
);





INSERT INTO "CONTESTANTS" VALUES ("1", "Alice");
INSERT INTO "CONTESTANTS" VALUES ("2", "Bob");
INSERT INTO "CONTESTANTS" VALUES ("3", "Charlie");
INSERT INTO "CONTESTANTS" VALUES ("4", "David");
INSERT INTO "CONTESTANTS" VALUES ("5", "Eve");
INSERT INTO "CONTESTANTS" VALUES ("6", "Frank");
INSERT INTO "VOTES" VALUES ("1", "CA", "1", "2023-01-01 10:00:00");
INSERT INTO "VOTES" VALUES ("2", "NY", "1", "2023-01-01 10:05:00");
INSERT INTO "VOTES" VALUES ("3", "TX", "2", "2023-01-01 10:10:00");
INSERT INTO "VOTES" VALUES ("4", "FL", "2", "2023-01-01 10:15:00");
INSERT INTO "VOTES" VALUES ("5", "CA", "2", "2023-01-01 10:20:00");
INSERT INTO "VOTES" VALUES ("6", "NY", "3", "2023-01-01 10:25:00");
INSERT INTO "VOTES" VALUES ("7", "TX", "3", "2023-01-01 10:30:00");
INSERT INTO "VOTES" VALUES ("8", "FL", "3", "2023-01-01 10:35:00");
INSERT INTO "VOTES" VALUES ("9", "CA", "3", "2023-01-01 10:40:00");
INSERT INTO "VOTES" VALUES ("10", "NY", "3", "2023-01-01 10:45:00");
INSERT INTO "VOTES" VALUES ("11", "TX", "4", "2023-01-01 10:50:00");
INSERT INTO "VOTES" VALUES ("12", "FL", "4", "2023-01-01 10:55:00");
INSERT INTO "VOTES" VALUES ("13", "CA", "5", "2023-01-01 11:00:00");
INSERT INTO "VOTES" VALUES ("14", "NY", "5", "2023-01-01 11:05:00");
INSERT INTO "VOTES" VALUES ("15", "TX", "5", "2023-01-01 11:10:00");
INSERT INTO "VOTES" VALUES ("16", "FL", "6", "2023-01-01 11:15:00");