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

CREATE TABLE "VOTES" (
"vote_id" INT,
"phone_number" 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", "Tabatha Gehling");
INSERT INTO "CONTESTANTS" VALUES ("2", "John Doe");
INSERT INTO "CONTESTANTS" VALUES ("3", "Jane Smith");
INSERT INTO "CONTESTANTS" VALUES ("4", "Alice Brown");
INSERT INTO "CONTESTANTS" VALUES ("5", "Bob White");
INSERT INTO "CONTESTANTS" VALUES ("6", "Charlie Green");
INSERT INTO "CONTESTANTS" VALUES ("7", "Diana Prince");
INSERT INTO "CONTESTANTS" VALUES ("8", "Clark Kent");
INSERT INTO "CONTESTANTS" VALUES ("9", "Bruce Wayne");
INSERT INTO "CONTESTANTS" VALUES ("10", "Lois Lane");
INSERT INTO "VOTES" VALUES ("1", "1234567", "NJ", "1", "2023-01-01 10:00:00");
INSERT INTO "VOTES" VALUES ("2", "2345678", "CA", "2", "2023-01-01 10:05:00");
INSERT INTO "VOTES" VALUES ("3", "3456789", "NY", "1", "2023-01-01 10:10:00");
INSERT INTO "VOTES" VALUES ("4", "4567890", "TX", "3", "2023-01-01 10:15:00");
INSERT INTO "VOTES" VALUES ("5", "5678901", "NJ", "1", "2023-01-01 10:20:00");
INSERT INTO "VOTES" VALUES ("6", "6789012", "WA", "4", "2023-01-01 10:25:00");
INSERT INTO "VOTES" VALUES ("7", "7890123", "DC", "1", "2023-01-01 10:30:00");
INSERT INTO "VOTES" VALUES ("8", "8901234", "AL", "5", "2023-01-01 10:35:00");
INSERT INTO "VOTES" VALUES ("9", "9012345", "CT", "1", "2023-01-01 10:40:00");
INSERT INTO "VOTES" VALUES ("10", "1122334", "ME", "6", "2023-01-01 10:45:00");
INSERT INTO "VOTES" VALUES ("11", "2233445", "ID", "1", "2023-01-01 10:50:00");
INSERT INTO "VOTES" VALUES ("12", "3344556", "CA", "7", "2023-01-01 10:55:00");
INSERT INTO "VOTES" VALUES ("13", "4455667", "NY", "1", "2023-01-01 11:00:00");
INSERT INTO "VOTES" VALUES ("14", "5566778", "TX", "8", "2023-01-01 11:05:00");
INSERT INTO "VOTES" VALUES ("15", "6677889", "NJ", "1", "2023-01-01 11:10:00");