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", "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 "CONTESTANTS" VALUES ("6", "Frank White");
INSERT INTO "VOTES" VALUES ("1", "1234567890", "NJ", "1", "2023-10-26 10:00:00");
INSERT INTO "VOTES" VALUES ("2", "1234567891", "DC", "2", "2023-10-26 10:01:00");
INSERT INTO "VOTES" VALUES ("3", "1234567892", "CT", "1", "2023-10-26 10:02:00");
INSERT INTO "VOTES" VALUES ("4", "1234567893", "AL", "3", "2023-10-26 10:03:00");
INSERT INTO "VOTES" VALUES ("5", "1234567894", "WA", "2", "2023-10-26 10:04:00");
INSERT INTO "VOTES" VALUES ("6", "1234567895", "ME", "4", "2023-10-26 10:05:00");
INSERT INTO "VOTES" VALUES ("7", "1234567896", "ID", "1", "2023-10-26 10:06:00");
INSERT INTO "VOTES" VALUES ("8", "1234567897", "CA", "5", "2023-10-26 10:07:00");
INSERT INTO "VOTES" VALUES ("9", "1234567898", "TX", "3", "2023-10-26 10:08:00");
INSERT INTO "VOTES" VALUES ("10", "1234567899", "NY", "6", "2023-10-26 10:09:00");
INSERT INTO "VOTES" VALUES ("11", "1234567900", "NJ", "2", "2023-10-26 10:10:00");
INSERT INTO "VOTES" VALUES ("12", "1234567901", "DC", "1", "2023-10-26 10:11:00");
INSERT INTO "VOTES" VALUES ("13", "1234567902", "CT", "3", "2023-10-26 10:12:00");
INSERT INTO "VOTES" VALUES ("14", "1234567903", "AL", "4", "2023-10-26 10:13:00");
INSERT INTO "VOTES" VALUES ("15", "1234567904", "WA", "5", "2023-10-26 10:14:00");