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 ("212", "NY");
INSERT INTO "AREA_CODE_STATE" VALUES ("213", "CA");
INSERT INTO "AREA_CODE_STATE" VALUES ("312", "IL");
INSERT INTO "AREA_CODE_STATE" VALUES ("415", "CA");
INSERT INTO "AREA_CODE_STATE" VALUES ("646", "NY");
INSERT INTO "AREA_CODE_STATE" VALUES ("718", "NY");
INSERT INTO "AREA_CODE_STATE" VALUES ("202", "DC");
INSERT INTO "CONTESTANTS" VALUES ("1", "Alice Johnson");
INSERT INTO "CONTESTANTS" VALUES ("2", "Bob Smith");
INSERT INTO "CONTESTANTS" VALUES ("3", "Charlie Lee");
INSERT INTO "CONTESTANTS" VALUES ("4", "Diana Prince");
INSERT INTO "VOTES" VALUES ("1", "2125550001", "NY", "1");
INSERT INTO "VOTES" VALUES ("2", "2125550002", "NY", "2");
INSERT INTO "VOTES" VALUES ("3", "2125550003", "NY", "1");
INSERT INTO "VOTES" VALUES ("4", "2125550004", "NY", "3");
INSERT INTO "VOTES" VALUES ("5", "2125550005", "NY", "2");
INSERT INTO "VOTES" VALUES ("6", "6465550001", "NY", "1");
INSERT INTO "VOTES" VALUES ("7", "6465550002", "NY", "4");
INSERT INTO "VOTES" VALUES ("8", "7185550001", "NY", "3");
INSERT INTO "VOTES" VALUES ("9", "7185550002", "NY", "3");
INSERT INTO "VOTES" VALUES ("10", "2135550001", "CA", "2");
INSERT INTO "VOTES" VALUES ("11", "2135550002", "CA", "2");
INSERT INTO "VOTES" VALUES ("12", "2135550003", "CA", "4");
INSERT INTO "VOTES" VALUES ("13", "4155550001", "CA", "1");
INSERT INTO "VOTES" VALUES ("14", "4155550002", "CA", "3");
INSERT INTO "VOTES" VALUES ("15", "3125550001", "IL", "4");
INSERT INTO "VOTES" VALUES ("16", "3125550002", "IL", "4");
INSERT INTO "VOTES" VALUES ("17", "3125550003", "IL", "1");
INSERT INTO "VOTES" VALUES ("18", "2025550001", "DC", "2");
INSERT INTO "VOTES" VALUES ("19", "2025550002", "DC", "3");
INSERT INTO "VOTES" VALUES ("20", "2025550003", "DC", "1");
INSERT INTO "VOTES" VALUES ("21", "2125550006", "NY", "4");
INSERT INTO "VOTES" VALUES ("22", "2125550007", "NY", "4");
INSERT INTO "VOTES" VALUES ("23", "2125550008", "NY", "1");
INSERT INTO "VOTES" VALUES ("24", "2125550009", "NY", "2");
INSERT INTO "VOTES" VALUES ("25", "2125550010", "NY", "3");