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

CREATE TABLE "Professionals" (
"professional_id" INT UNIQUE,
"role_code" VARCHAR(10),
PRIMARY KEY ("professional_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,
"professional_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 ("professional_id") REFERENCES "Professionals"("professional_id"),
FOREIGN KEY ("treatment_type_code") REFERENCES "Treatment_Types"("treatment_type_code")
);





INSERT INTO "Dogs" VALUES ("1", "BC001");
INSERT INTO "Dogs" VALUES ("2", "BC002");
INSERT INTO "Dogs" VALUES ("3", "BC003");
INSERT INTO "Dogs" VALUES ("4", "BC004");
INSERT INTO "Dogs" VALUES ("5", "BC005");
INSERT INTO "Professionals" VALUES ("101", "ROLE01");
INSERT INTO "Professionals" VALUES ("102", "ROLE02");
INSERT INTO "Professionals" VALUES ("103", "ROLE03");
INSERT INTO "Professionals" VALUES ("104", "ROLE04");
INSERT INTO "Treatment_Types" VALUES ("TT01", "Vaccination");
INSERT INTO "Treatment_Types" VALUES ("TT02", "Deworming");
INSERT INTO "Treatment_Types" VALUES ("TT03", "Grooming");
INSERT INTO "Treatment_Types" VALUES ("TT04", "Dental Cleaning");
INSERT INTO "Treatment_Types" VALUES ("TT05", "Surgery");
INSERT INTO "Treatments" VALUES ("1001", "1", "101", "TT01", "2024-05-01 09:00:00", "50.0000");
INSERT INTO "Treatments" VALUES ("1002", "2", "102", "TT02", "2024-05-02 10:30:00", "30.0000");
INSERT INTO "Treatments" VALUES ("1003", "3", "103", "TT03", "2024-05-03 11:00:00", "45.5000");
INSERT INTO "Treatments" VALUES ("1004", "4", "104", "TT04", "2024-05-04 14:00:00", "120.0000");
INSERT INTO "Treatments" VALUES ("1005", "5", "101", "TT05", "2024-05-05 15:30:00", "500.0000");
INSERT INTO "Treatments" VALUES ("1006", "1", "102", "TT02", "2024-05-05 15:30:00", "35.0000");
INSERT INTO "Treatments" VALUES ("1007", "2", "103", "TT01", "2024-05-06 08:00:00", "55.0000");
INSERT INTO "Treatments" VALUES ("1008", "3", "104", "TT05", "2024-05-06 08:00:00", "600.0000");
INSERT INTO "Treatments" VALUES ("1009", "4", "101", "TT03", "2024-05-07 09:15:00", "40.0000");
INSERT INTO "Treatments" VALUES ("1010", "5", "102", "TT04", "2024-05-07 09:15:00", "130.0000");
INSERT INTO "Treatments" VALUES ("1011", "1", "103", "TT01", "2024-05-08 10:00:00", "60.0000");
INSERT INTO "Treatments" VALUES ("1012", "2", "104", "TT02", "2024-05-08 10:00:00", "32.0000");
INSERT INTO "Treatments" VALUES ("1013", "3", "101", "TT03", "2024-05-09 11:30:00", "48.0000");
INSERT INTO "Treatments" VALUES ("1014", "4", "102", "TT04", "2024-05-09 11:30:00", "125.0000");
INSERT INTO "Treatments" VALUES ("1015", "5", "103", "TT05", "2024-05-10 12:00:00", "550.0000");
INSERT INTO "Treatments" VALUES ("1016", "1", "104", "TT01", "2024-05-10 12:00:00", "65.0000");
INSERT INTO "Treatments" VALUES ("1017", "2", "101", "TT02", "2024-05-11 13:45:00", "33.0000");
INSERT INTO "Treatments" VALUES ("1018", "3", "102", "TT03", "2024-05-11 13:45:00", "47.0000");
INSERT INTO "Treatments" VALUES ("1019", "4", "103", "TT04", "2024-05-12 14:30:00", "128.0000");
INSERT INTO "Treatments" VALUES ("1020", "5", "104", "TT05", "2024-05-12 14:30:00", "580.0000");
INSERT INTO "Treatments" VALUES ("1021", "1", "101", "TT01", "2024-05-13 15:00:00", "70.0000");
INSERT INTO "Treatments" VALUES ("1022", "2", "102", "TT02", "2024-05-13 15:00:00", "34.0000");
INSERT INTO "Treatments" VALUES ("1023", "3", "103", "TT03", "2024-05-14 16:00:00", "49.0000");
INSERT INTO "Treatments" VALUES ("1024", "4", "104", "TT04", "2024-05-14 16:00:00", "130.0000");
INSERT INTO "Treatments" VALUES ("1025", "5", "101", "TT05", "2024-05-15 17:00:00", "600.0000");