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,
"phone_number" INT,
"state" VARCHAR(2),
"contestant_number" INT,
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", "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 "CONTESTANTS" VALUES ("1", "Alice Johnson");
INSERT INTO "CONTESTANTS" VALUES ("2", "Bob Smith");
INSERT INTO "CONTESTANTS" VALUES ("3", "Carol Lee");
INSERT INTO "CONTESTANTS" VALUES ("4", "David Kim");
INSERT INTO "CONTESTANTS" VALUES ("5", "Eva Brown");
INSERT INTO "VOTES" VALUES ("1", "2015550001", "NY", "1");
INSERT INTO "VOTES" VALUES ("2", "2015550002", "NY", "2");
INSERT INTO "VOTES" VALUES ("3", "2015550003", "NY", "1");
INSERT INTO "VOTES" VALUES ("4", "2015550004", "NY", "3");
INSERT INTO "VOTES" VALUES ("5", "2015550005", "NY", "2");
INSERT INTO "VOTES" VALUES ("6", "2025550001", "DC", "2");
INSERT INTO "VOTES" VALUES ("7", "2025550002", "DC", "2");
INSERT INTO "VOTES" VALUES ("8", "2025550003", "DC", "3");
INSERT INTO "VOTES" VALUES ("9", "2035550001", "CT", "1");
INSERT INTO "VOTES" VALUES ("10", "2035550002", "CT", "1");
INSERT INTO "VOTES" VALUES ("11", "2035550003", "CT", "4");
INSERT INTO "VOTES" VALUES ("12", "2045550001", "MB", "5");
INSERT INTO "VOTES" VALUES ("13", "2045550002", "MB", "5");
INSERT INTO "VOTES" VALUES ("14", "2045550003", "MB", "5");
INSERT INTO "VOTES" VALUES ("15", "2045550004", "MB", "4");
INSERT INTO "VOTES" VALUES ("16", "2055550001", "AL", "3");
INSERT INTO "VOTES" VALUES ("17", "2055550002", "AL", "3");
INSERT INTO "VOTES" VALUES ("18", "2055550003", "AL", "2");
INSERT INTO "VOTES" VALUES ("19", "2055550004", "AL", "2");
INSERT INTO "VOTES" VALUES ("20", "2055550005", "AL", "1");
INSERT INTO "VOTES" VALUES ("21", "2065550001", "WA", "4");
INSERT INTO "VOTES" VALUES ("22", "2065550002", "WA", "4");
INSERT INTO "VOTES" VALUES ("23", "2065550003", "WA", "4");
INSERT INTO "VOTES" VALUES ("24", "2065550004", "WA", "5");
INSERT INTO "VOTES" VALUES ("25", "2075550001", "ME", "1");
INSERT INTO "VOTES" VALUES ("26", "2075550002", "ME", "1");
INSERT INTO "VOTES" VALUES ("27", "2075550003", "ME", "2");
INSERT INTO "VOTES" VALUES ("28", "2085550001", "ID", "3");
INSERT INTO "VOTES" VALUES ("29", "2085550002", "ID", "3");
INSERT INTO "VOTES" VALUES ("30", "2085550003", "ID", "3");
INSERT INTO "VOTES" VALUES ("31", "2085550004", "ID", "4");
INSERT INTO "VOTES" VALUES ("32", "2095550001", "CA", "1");
INSERT INTO "VOTES" VALUES ("33", "2095550002", "CA", "2");
INSERT INTO "VOTES" VALUES ("34", "2095550003", "CA", "2");
INSERT INTO "VOTES" VALUES ("35", "2095550004", "CA", "3");
INSERT INTO "VOTES" VALUES ("36", "2095550005", "CA", "3");
INSERT INTO "VOTES" VALUES ("37", "2095550006", "CA", "3");
INSERT INTO "VOTES" VALUES ("38", "2105550001", "TX", "5");
INSERT INTO "VOTES" VALUES ("39", "2105550002", "TX", "5");
INSERT INTO "VOTES" VALUES ("40", "2105550003", "TX", "5");
INSERT INTO "VOTES" VALUES ("41", "2105550004", "TX", "5");
INSERT INTO "VOTES" VALUES ("42", "2105550005", "TX", "4");
INSERT INTO "VOTES" VALUES ("43", "2105550006", "TX", "4");
INSERT INTO "VOTES" VALUES ("44", "2105550007", "TX", "3");
INSERT INTO "VOTES" VALUES ("45", "2105550008", "TX", "2");
INSERT INTO "VOTES" VALUES ("46", "2105550009", "TX", "1");