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 "AREA_CODE_STATE" (
"area_code" INT,
"state" VARCHAR(2) UNIQUE,
PRIMARY KEY ("area_code")
);

CREATE TABLE "CONTESTANTS" (
"contestant_number" INT,
"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"),
FOREIGN KEY ("state") REFERENCES "AREA_CODE_STATE"("state")
);





INSERT INTO "AREA_CODE_STATE" VALUES ("201", "NJ");
INSERT INTO "AREA_CODE_STATE" VALUES ("202", "DC");
INSERT INTO "AREA_CODE_STATE" VALUES ("203", "CT");
INSERT INTO "AREA_CODE_STATE" VALUES ("205", "AL");
INSERT INTO "AREA_CODE_STATE" VALUES ("206", "WA");
INSERT INTO "AREA_CODE_STATE" VALUES ("207", "ME");
INSERT INTO "AREA_CODE_STATE" VALUES ("208", "ID");
INSERT INTO "AREA_CODE_STATE" VALUES ("209", "CA");
INSERT INTO "AREA_CODE_STATE" VALUES ("210", "TX");
INSERT INTO "AREA_CODE_STATE" VALUES ("212", "NY");
INSERT INTO "CONTESTANTS" VALUES ("1", "Alice Smith");
INSERT INTO "CONTESTANTS" VALUES ("2", "Bob Johnson");
INSERT INTO "CONTESTANTS" VALUES ("3", "Charlie Brown");
INSERT INTO "CONTESTANTS" VALUES ("4", "Diana Prince");
INSERT INTO "CONTESTANTS" VALUES ("5", "Eve Adams");
INSERT INTO "VOTES" VALUES ("1", "NJ", "1", "2023-10-26 10:00:00");
INSERT INTO "VOTES" VALUES ("2", "DC", "2", "2023-10-26 10:05:00");
INSERT INTO "VOTES" VALUES ("3", "NJ", "1", "2023-10-26 10:10:00");
INSERT INTO "VOTES" VALUES ("4", "CT", "3", "2023-10-26 10:15:00");
INSERT INTO "VOTES" VALUES ("5", "AL", "4", "2023-10-26 10:20:00");
INSERT INTO "VOTES" VALUES ("6", "WA", "1", "2023-10-26 10:25:00");
INSERT INTO "VOTES" VALUES ("7", "NJ", "2", "2023-10-26 10:30:00");
INSERT INTO "VOTES" VALUES ("8", "DC", "3", "2023-10-26 10:35:00");
INSERT INTO "VOTES" VALUES ("9", "CT", "1", "2023-10-26 10:40:00");
INSERT INTO "VOTES" VALUES ("10", "AL", "5", "2023-10-26 10:45:00");
INSERT INTO "VOTES" VALUES ("11", "WA", "2", "2023-10-27 11:00:00");
INSERT INTO "VOTES" VALUES ("12", "ME", "3", "2023-10-27 11:05:00");
INSERT INTO "VOTES" VALUES ("13", "ID", "4", "2023-10-27 11:10:00");
INSERT INTO "VOTES" VALUES ("14", "CA", "5", "2023-10-27 11:15:00");
INSERT INTO "VOTES" VALUES ("15", "TX", "1", "2023-10-27 11:20:00");
INSERT INTO "VOTES" VALUES ("16", "NY", "2", "2023-10-27 11:25:00");
INSERT INTO "VOTES" VALUES ("17", "NJ", "3", "2023-10-27 11:30:00");
INSERT INTO "VOTES" VALUES ("18", "DC", "4", "2023-10-27 11:35:00");
INSERT INTO "VOTES" VALUES ("19", "NJ", "5", "2023-10-27 11:40:00");
INSERT INTO "VOTES" VALUES ("20", "CT", "1", "2023-10-27 11:45:00");
INSERT INTO "VOTES" VALUES ("21", "NJ", "1", "2023-10-26 10:00:00");
INSERT INTO "VOTES" VALUES ("22", "DC", "2", "2023-10-26 10:05:00");
INSERT INTO "VOTES" VALUES ("23", "NJ", "1", "2023-10-26 10:10:00");
INSERT INTO "VOTES" VALUES ("24", "CT", "3", "2023-10-26 10:15:00");
INSERT INTO "VOTES" VALUES ("25", "AL", "4", "2023-10-26 10:20:00");