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 "Dogs" (
"dog_id" INT,
"breed_code" VARCHAR(10),
PRIMARY KEY ("dog_id")
);

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

CREATE TABLE "Treatments" (
"treatment_id" INT,
"dog_id" INT,
"treatment_type_code" VARCHAR(10),
"date_of_treatment" DATETIME,
"cost_of_treatment" DECIMAL(19, 4),
PRIMARY KEY ("treatment_id"),
FOREIGN KEY ("dog_id") REFERENCES "Dogs"("dog_id"),
FOREIGN KEY ("treatment_type_code") REFERENCES "Treatment_Types"("treatment_type_code")
);





INSERT INTO "Dogs" VALUES ("1", "BC01");
INSERT INTO "Dogs" VALUES ("2", "BC02");
INSERT INTO "Dogs" VALUES ("3", "BC03");
INSERT INTO "Dogs" VALUES ("4", "BC01");
INSERT INTO "Dogs" VALUES ("5", "BC04");
INSERT INTO "Dogs" VALUES ("6", "BC05");
INSERT INTO "Dogs" VALUES ("7", "BC02");
INSERT INTO "Dogs" VALUES ("8", "BC03");
INSERT INTO "Dogs" VALUES ("9", "BC06");
INSERT INTO "Dogs" VALUES ("10", "BC07");
INSERT INTO "Treatment_Types" VALUES ("TT01", "Vaccination");
INSERT INTO "Treatment_Types" VALUES ("TT02", "Deworming");
INSERT INTO "Treatment_Types" VALUES ("TT03", "Dental Cleaning");
INSERT INTO "Treatment_Types" VALUES ("TT04", "Ear Infection Treatment");
INSERT INTO "Treatment_Types" VALUES ("TT05", "Skin Allergy Treatment");
INSERT INTO "Treatment_Types" VALUES ("TT06", "Nail Trimming");
INSERT INTO "Treatments" VALUES ("1", "1", "TT01", "2024-01-10 09:00:00", "50.00");
INSERT INTO "Treatments" VALUES ("2", "2", "TT01", "2024-01-11 10:00:00", "50.00");
INSERT INTO "Treatments" VALUES ("3", "3", "TT02", "2024-01-12 11:00:00", "30.00");
INSERT INTO "Treatments" VALUES ("4", "4", "TT02", "2024-01-13 12:00:00", "30.00");
INSERT INTO "Treatments" VALUES ("5", "5", "TT03", "2024-01-14 13:00:00", "120.00");
INSERT INTO "Treatments" VALUES ("6", "6", "TT04", "2024-01-15 14:00:00", "80.00");
INSERT INTO "Treatments" VALUES ("7", "7", "TT05", "2024-01-16 15:00:00", "90.00");
INSERT INTO "Treatments" VALUES ("8", "8", "TT06", "2024-01-17 16:00:00", "10.00");
INSERT INTO "Treatments" VALUES ("9", "9", "TT06", "2024-01-18 17:00:00", "10.00");
INSERT INTO "Treatments" VALUES ("10", "10", "TT06", "2024-01-19 18:00:00", "10.00");
INSERT INTO "Treatments" VALUES ("11", "1", "TT05", "2024-01-20 09:00:00", "90.00");
INSERT INTO "Treatments" VALUES ("12", "2", "TT03", "2024-01-21 10:00:00", "120.00");
INSERT INTO "Treatments" VALUES ("13", "3", "TT04", "2024-01-22 11:00:00", "80.00");
INSERT INTO "Treatments" VALUES ("14", "4", "TT01", "2024-01-23 12:00:00", "50.00");
INSERT INTO "Treatments" VALUES ("15", "5", "TT02", "2024-01-24 13:00:00", "30.00");