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 "VOTES" (
"vote_id" INT,
"phone_number" INT,
"state" VARCHAR(2),
"contestant_number" INT,
PRIMARY KEY ("vote_id"),
FOREIGN KEY ("state") REFERENCES "AREA_CODE_STATE"("state")
);





INSERT INTO "AREA_CODE_STATE" VALUES ("201", "NY");
INSERT INTO "AREA_CODE_STATE" VALUES ("202", "DC");
INSERT INTO "AREA_CODE_STATE" VALUES ("203", "CT");
INSERT INTO "AREA_CODE_STATE" VALUES ("204", "MB");
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 "VOTES" VALUES ("1", "2015551234", "NY", "1");
INSERT INTO "VOTES" VALUES ("2", "2025552345", "DC", "2");
INSERT INTO "VOTES" VALUES ("3", "2035553456", "CT", "1");
INSERT INTO "VOTES" VALUES ("4", "2045554567", "MB", "3");
INSERT INTO "VOTES" VALUES ("5", "2055555678", "AL", "2");
INSERT INTO "VOTES" VALUES ("6", "2065556789", "WA", "1");
INSERT INTO "VOTES" VALUES ("7", "2075557890", "ME", "3");
INSERT INTO "VOTES" VALUES ("8", "2085558901", "ID", "2");
INSERT INTO "VOTES" VALUES ("9", "2095559012", "CA", "1");
INSERT INTO "VOTES" VALUES ("10", "2105550123", "TX", "3");
INSERT INTO "VOTES" VALUES ("11", "2015556789", "NY", "2");
INSERT INTO "VOTES" VALUES ("12", "2025557890", "DC", "1");
INSERT INTO "VOTES" VALUES ("13", "2035558901", "CT", "3");
INSERT INTO "VOTES" VALUES ("14", "2045559012", "MB", "1");
INSERT INTO "VOTES" VALUES ("15", "2055550123", "AL", "3");