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 "Breeds" (
"breed_code" VARCHAR(10) UNIQUE,
"breed_name" VARCHAR(80),
PRIMARY KEY ("breed_code")
);

CREATE TABLE "Dogs" (
"dog_id" INT UNIQUE,
"breed_code" VARCHAR(10),
"size_code" VARCHAR(10),
"name" VARCHAR(50),
"date_adopted" DATETIME,
PRIMARY KEY ("dog_id"),
FOREIGN KEY ("breed_code") REFERENCES "Breeds"("breed_code")
);

CREATE TABLE "Treatment_Types" (
"treatment_type_code" VARCHAR(10),
"treatment_type_description" VARCHAR(80),
PRIMARY KEY ("treatment_type_code")
);

CREATE TABLE "Treatments" (
"treatment_id" INT,
"dog_id" INT,
"date_of_treatment" DATETIME,
PRIMARY KEY ("treatment_id"),
FOREIGN KEY ("dog_id") REFERENCES "Dogs"("dog_id")
);





INSERT INTO "Breeds" VALUES ("LAB", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("GSD", "German Shepherd");
INSERT INTO "Breeds" VALUES ("POO", "Poodle");
INSERT INTO "Breeds" VALUES ("BEA", "Beagle");
INSERT INTO "Breeds" VALUES ("BUL", "Bulldog");
INSERT INTO "Breeds" VALUES ("CHI", "Chihuahua");
INSERT INTO "Breeds" VALUES ("DACH", "Dachshund");
INSERT INTO "Breeds" VALUES ("BOX", "Boxer");
INSERT INTO "Breeds" VALUES ("SIBE", "Siberian Husky");
INSERT INTO "Breeds" VALUES ("AUS", "Australian Shepherd");
INSERT INTO "Breeds" VALUES ("RARE1", "Rare Breed One");
INSERT INTO "Breeds" VALUES ("RARE2", "Rare Breed Two");
INSERT INTO "Breeds" VALUES ("RARE3", "Rare Breed Three");
INSERT INTO "Dogs" VALUES ("1", "LAB", "L", "Buddy", "2022-01-15");
INSERT INTO "Dogs" VALUES ("2", "GSD", "L", "Max", "2022-02-20");
INSERT INTO "Dogs" VALUES ("3", "POO", "M", "Bella", "2022-03-10");
INSERT INTO "Dogs" VALUES ("4", "BEA", "M", "Lucy", "2022-04-05");
INSERT INTO "Dogs" VALUES ("5", "BUL", "M", "Rocky", "2022-05-12");
INSERT INTO "Dogs" VALUES ("6", "CHI", "S", "Daisy", "2022-06-01");
INSERT INTO "Dogs" VALUES ("7", "LAB", "L", "Charlie", "2022-07-18");
INSERT INTO "Dogs" VALUES ("8", "GSD", "L", "Duke", "2022-08-25");
INSERT INTO "Dogs" VALUES ("9", "POO", "M", "Molly", "2022-09-03");
INSERT INTO "Dogs" VALUES ("10", "BEA", "M", "Sadie", "2022-10-11");
INSERT INTO "Dogs" VALUES ("11", "RARE1", "M", "Zoe", "2023-01-01");
INSERT INTO "Dogs" VALUES ("12", "RARE2", "S", "Gizmo", "2023-02-14");
INSERT INTO "Dogs" VALUES ("13", "RARE3", "L", "Titan", "2023-03-20");
INSERT INTO "Dogs" VALUES ("14", "LAB", "L", "Cooper", "2023-04-01");
INSERT INTO "Dogs" VALUES ("15", "RARE1", "M", "Luna", "2023-05-05");
INSERT INTO "Dogs" VALUES ("16", "RARE2", "S", "Pip", "2023-06-10");
INSERT INTO "Dogs" VALUES ("17", "RARE3", "L", "Zeus", "2023-07-15");
INSERT INTO "Dogs" VALUES ("18", "RARE1", "M", "Shadow", "2023-08-22");
INSERT INTO "Treatment_Types" VALUES ("VACC", "Vaccination");
INSERT INTO "Treatment_Types" VALUES ("CHECK", "Annual Check-up");
INSERT INTO "Treatment_Types" VALUES ("GROOM", "Grooming");
INSERT INTO "Treatment_Types" VALUES ("SURG", "Surgery");
INSERT INTO "Treatment_Types" VALUES ("DENT", "Dental Cleaning");
INSERT INTO "Treatments" VALUES ("101", "1", "2023-01-20");
INSERT INTO "Treatments" VALUES ("102", "2", "2023-02-28");
INSERT INTO "Treatments" VALUES ("103", "3", "2023-03-15");
INSERT INTO "Treatments" VALUES ("104", "4", "2023-04-10");
INSERT INTO "Treatments" VALUES ("105", "5", "2023-05-20");
INSERT INTO "Treatments" VALUES ("106", "6", "2023-06-05");
INSERT INTO "Treatments" VALUES ("107", "7", "2023-07-20");
INSERT INTO "Treatments" VALUES ("108", "8", "2023-08-30");
INSERT INTO "Treatments" VALUES ("109", "9", "2023-09-10");
INSERT INTO "Treatments" VALUES ("110", "10", "2023-10-15");
INSERT INTO "Treatments" VALUES ("111", "11", "2023-01-05");
INSERT INTO "Treatments" VALUES ("112", "12", "2023-02-20");
INSERT INTO "Treatments" VALUES ("113", "13", "2023-03-25");
INSERT INTO "Treatments" VALUES ("114", "14", "2023-04-05");
INSERT INTO "Treatments" VALUES ("115", "15", "2023-05-10");
INSERT INTO "Treatments" VALUES ("116", "16", "2023-06-15");
INSERT INTO "Treatments" VALUES ("117", "17", "2023-07-20");
INSERT INTO "Treatments" VALUES ("118", "18", "2023-08-25");
INSERT INTO "Treatments" VALUES ("119", "11", "2023-09-01");
INSERT INTO "Treatments" VALUES ("120", "12", "2023-10-01");
INSERT INTO "Treatments" VALUES ("121", "13", "2023-11-01");
INSERT INTO "Treatments" VALUES ("122", "15", "2023-12-01");
INSERT INTO "Treatments" VALUES ("123", "16", "2024-01-01");
INSERT INTO "Treatments" VALUES ("124", "17", "2024-02-01");
INSERT INTO "Treatments" VALUES ("125", "18", "2024-03-01");