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 ("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 ("617", "MA");
INSERT INTO "AREA_CODE_STATE" VALUES ("305", "FL");
INSERT INTO "AREA_CODE_STATE" VALUES ("404", "GA");
INSERT INTO "AREA_CODE_STATE" VALUES ("206", "WA");
INSERT INTO "AREA_CODE_STATE" VALUES ("702", "NV");
INSERT INTO "AREA_CODE_STATE" VALUES ("503", "OR");
INSERT INTO "CONTESTANTS" VALUES ("1", "Tabatha Gehling");
INSERT INTO "CONTESTANTS" VALUES ("2", "Jared McDonald");
INSERT INTO "CONTESTANTS" VALUES ("3", "Lena Smith");
INSERT INTO "CONTESTANTS" VALUES ("4", "Carlos Ramirez");
INSERT INTO "CONTESTANTS" VALUES ("5", "Maya Patel");
INSERT INTO "CONTESTANTS" VALUES ("6", "Tabatha Gehling");
INSERT INTO "VOTES" VALUES ("101", "2125551234", "NY", "1", "2024-05-01 10:15:00");
INSERT INTO "VOTES" VALUES ("102", "2135555678", "CA", "1", "2024-05-01 11:00:00");
INSERT INTO "VOTES" VALUES ("103", "3125554321", "IL", "2", "2024-05-01 11:30:00");
INSERT INTO "VOTES" VALUES ("104", "4155558765", "CA", "1", "2024-05-02 09:45:00");
INSERT INTO "VOTES" VALUES ("105", "6175553456", "MA", "3", "2024-05-02 10:00:00");
INSERT INTO "VOTES" VALUES ("106", "3055557890", "FL", "6", "2024-05-02 10:15:00");
INSERT INTO "VOTES" VALUES ("107", "4045552345", "GA", "1", "2024-05-03 14:20:00");
INSERT INTO "VOTES" VALUES ("108", "2065556789", "WA", "4", "2024-05-03 15:00:00");
INSERT INTO "VOTES" VALUES ("109", "7025559876", "NV", "1", "2024-05-04 08:30:00");
INSERT INTO "VOTES" VALUES ("110", "5035556543", "OR", "5", "2024-05-04 09:00:00");
INSERT INTO "VOTES" VALUES ("111", "2125551111", "NY", "1", "2024-05-04 09:15:00");
INSERT INTO "VOTES" VALUES ("112", "2135552222", "CA", "6", "2024-05-04 09:30:00");
INSERT INTO "VOTES" VALUES ("113", "3125553333", "IL", "1", "2024-05-05 10:00:00");
INSERT INTO "VOTES" VALUES ("114", "4155554444", "CA", "2", "2024-05-05 10:15:00");
INSERT INTO "VOTES" VALUES ("115", "6175555555", "MA", "1", "2024-05-05 10:30:00");