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

CREATE TABLE "Dogs" (
"dog_id" INT UNIQUE,
"breed_code" VARCHAR(10),
"age" VARCHAR(20),
"date_of_birth" DATETIME,
PRIMARY KEY ("dog_id"),
FOREIGN KEY ("breed_code") REFERENCES "Breeds"("breed_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 ("BD001", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("BD002", "German Shepherd");
INSERT INTO "Breeds" VALUES ("BD003", "Beagle");
INSERT INTO "Breeds" VALUES ("BD004", "Bulldog");
INSERT INTO "Breeds" VALUES ("BD005", "Poodle");
INSERT INTO "Dogs" VALUES ("101", "BD001", "5 years", "2018-04-15");
INSERT INTO "Dogs" VALUES ("102", "BD002", "3 years", "2020-06-20");
INSERT INTO "Dogs" VALUES ("103", "BD003", "7 years", "2016-01-10");
INSERT INTO "Dogs" VALUES ("104", "BD004", "1 year", "2022-03-05");
INSERT INTO "Dogs" VALUES ("105", "BD005", "10 years", "2013-11-25");
INSERT INTO "Dogs" VALUES ("106", "BD001", "6 years", "2017-08-30");
INSERT INTO "Dogs" VALUES ("107", "BD002", "No age info", "2019-12-01");
INSERT INTO "Dogs" VALUES ("108", "BD003", "4 years", "2019-05-15");
INSERT INTO "Dogs" VALUES ("109", "BD004", "2 years", "2021-07-22");
INSERT INTO "Dogs" VALUES ("110", "BD005", "8 years", "2015-09-10");
INSERT INTO "Treatments" VALUES ("201", "101", "2023-01-10");
INSERT INTO "Treatments" VALUES ("202", "103", "2023-02-15");
INSERT INTO "Treatments" VALUES ("203", "105", "2023-03-20");
INSERT INTO "Treatments" VALUES ("204", "106", "2023-04-25");
INSERT INTO "Treatments" VALUES ("205", "108", "2023-05-30");
INSERT INTO "Treatments" VALUES ("206", "110", "2023-06-05");
INSERT INTO "Treatments" VALUES ("207", "109", "2023-07-10");