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),
"name" VARCHAR(50),
"date_arrived" DATETIME,
PRIMARY KEY ("dog_id"),
FOREIGN KEY ("breed_code") REFERENCES "Breeds"("breed_code")
);

CREATE TABLE "Owners" (
"owner_id" INT UNIQUE,
PRIMARY KEY ("owner_id")
);

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





INSERT INTO "Breeds" VALUES ("BR001", "Labrador Retriever");
INSERT INTO "Breeds" VALUES ("BR002", "German Shepherd");
INSERT INTO "Breeds" VALUES ("BR003", "Bulldog");
INSERT INTO "Breeds" VALUES ("BR004", "Beagle");
INSERT INTO "Breeds" VALUES ("BR005", "Azawakh");
INSERT INTO "Breeds" VALUES ("BR006", "Norwegian Lundehund");
INSERT INTO "Breeds" VALUES ("BR007", "Mudi");
INSERT INTO "Dogs" VALUES ("1", "BR001", "Max", "2023-01-15 10:00:00");
INSERT INTO "Dogs" VALUES ("2", "BR002", "Bella", "2023-02-20 11:30:00");
INSERT INTO "Dogs" VALUES ("3", "BR005", "Zephyr", "2023-03-05 09:45:00");
INSERT INTO "Dogs" VALUES ("4", "BR005", "Nala", "2023-03-10 14:20:00");
INSERT INTO "Dogs" VALUES ("5", "BR006", "Loki", "2023-04-01 08:00:00");
INSERT INTO "Dogs" VALUES ("6", "BR007", "Rex", "2023-04-15 16:10:00");
INSERT INTO "Dogs" VALUES ("7", "BR001", "Daisy", "2023-05-01 12:00:00");
INSERT INTO "Dogs" VALUES ("8", "BR003", "Rocky", "2023-05-10 13:30:00");
INSERT INTO "Dogs" VALUES ("9", "BR004", "Molly", "2023-05-20 15:00:00");
INSERT INTO "Dogs" VALUES ("10", "BR007", "Scout", "2023-06-01 10:30:00");
INSERT INTO "Owners" VALUES ("101");
INSERT INTO "Owners" VALUES ("102");
INSERT INTO "Owners" VALUES ("103");
INSERT INTO "Owners" VALUES ("104");
INSERT INTO "Treatments" VALUES ("1001", "1", "2023-06-10 09:00:00", "150.00");
INSERT INTO "Treatments" VALUES ("1002", "2", "2023-06-11 10:00:00", "200.00");
INSERT INTO "Treatments" VALUES ("1003", "3", "2023-06-12 11:00:00", "300.00");
INSERT INTO "Treatments" VALUES ("1004", "4", "2023-06-13 12:00:00", "350.00");
INSERT INTO "Treatments" VALUES ("1005", "5", "2023-06-14 13:00:00", "400.00");
INSERT INTO "Treatments" VALUES ("1006", "6", "2023-06-15 14:00:00", "250.00");
INSERT INTO "Treatments" VALUES ("1007", "7", "2023-06-16 15:00:00", "180.00");
INSERT INTO "Treatments" VALUES ("1008", "8", "2023-06-17 16:00:00", "220.00");
INSERT INTO "Treatments" VALUES ("1009", "9", "2023-06-18 17:00:00", "210.00");
INSERT INTO "Treatments" VALUES ("1010", "10", "2023-06-19 18:00:00", "270.00");
INSERT INTO "Treatments" VALUES ("1011", "3", "2023-07-01 09:00:00", "320.00");
INSERT INTO "Treatments" VALUES ("1012", "4", "2023-07-02 10:00:00", "360.00");
INSERT INTO "Treatments" VALUES ("1013", "5", "2023-07-03 11:00:00", "410.00");