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 "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,
PRIMARY KEY ("vote_id"),
FOREIGN KEY ("contestant_number") REFERENCES "CONTESTANTS"("contestant_number")
);





INSERT INTO "CONTESTANTS" VALUES ("1", "Alice");
INSERT INTO "CONTESTANTS" VALUES ("2", "bob");
INSERT INTO "CONTESTANTS" VALUES ("3", "Charlie");
INSERT INTO "CONTESTANTS" VALUES ("4", "alice");
INSERT INTO "CONTESTANTS" VALUES ("5", "Bob");
INSERT INTO "CONTESTANTS" VALUES ("6", "D'Angelo");
INSERT INTO "CONTESTANTS" VALUES ("7", "Élodie");
INSERT INTO "CONTESTANTS" VALUES ("8", "charlie");
INSERT INTO "CONTESTANTS" VALUES ("9", "");
INSERT INTO "CONTESTANTS" VALUES ("10", "NULL");
INSERT INTO "CONTESTANTS" VALUES ("11", "Anna-Marie");
INSERT INTO "CONTESTANTS" VALUES ("12", "anna-marie");
INSERT INTO "CONTESTANTS" VALUES ("13", "ALICE");
INSERT INTO "CONTESTANTS" VALUES ("14", "Bob");
INSERT INTO "CONTESTANTS" VALUES ("15", "Zoë");
INSERT INTO "VOTES" VALUES ("1", "5551234", "CA", "1");
INSERT INTO "VOTES" VALUES ("2", "5552345", "NY", "2");
INSERT INTO "VOTES" VALUES ("3", "5553456", "TX", "3");
INSERT INTO "VOTES" VALUES ("4", "5554567", "CA", "4");
INSERT INTO "VOTES" VALUES ("5", "5555678", "NV", "5");
INSERT INTO "VOTES" VALUES ("6", "5556789", "WA", "6");
INSERT INTO "VOTES" VALUES ("7", "5557890", "NY", "7");
INSERT INTO "VOTES" VALUES ("8", "5558901", "TX", "8");
INSERT INTO "VOTES" VALUES ("9", "5559012", "CA", "9");
INSERT INTO "VOTES" VALUES ("10", "5550123", "NV", "10");
INSERT INTO "VOTES" VALUES ("11", "5551111", "WA", "11");
INSERT INTO "VOTES" VALUES ("12", "5552222", "NY", "12");
INSERT INTO "VOTES" VALUES ("13", "5553333", "TX", "13");
INSERT INTO "VOTES" VALUES ("14", "5554444", "CA", "14");
INSERT INTO "VOTES" VALUES ("15", "5555555", "NV", "15");
INSERT INTO "VOTES" VALUES ("16", "5556666", "WA", "1");
INSERT INTO "VOTES" VALUES ("17", "5557777", "NY", "2");
INSERT INTO "VOTES" VALUES ("18", "5558888", "TX", "3");
INSERT INTO "VOTES" VALUES ("19", "5559999", "CA", "4");
INSERT INTO "VOTES" VALUES ("20", "5550000", "NV", "5");