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 "Examination" (
"ID" INT,
"Examination Date" DATE,
"Diagnosis" TEXT,
"Symptoms" TEXT,
FOREIGN KEY ("ID") REFERENCES "Patient"("ID")
);

CREATE TABLE "Laboratory" (
"ID" INT,
"Date" DATE,
"PLT" INT,
PRIMARY KEY ("ID", "Date"),
FOREIGN KEY ("ID") REFERENCES "Patient"("ID")
);

CREATE TABLE "Patient" (
"ID" INT UNIQUE,
"SEX" TEXT,
"Diagnosis" TEXT,
PRIMARY KEY ("ID")
);



INSERT INTO "Patient" VALUES ("1", "M", "MCTD");
INSERT INTO "Patient" VALUES ("2", "F", "SLE");
INSERT INTO "Patient" VALUES ("3", "M", "MCTD");
INSERT INTO "Patient" VALUES ("4", "F", "RA");
INSERT INTO "Patient" VALUES ("5", "M", "MCTD");
INSERT INTO "Patient" VALUES ("6", "F", "Sjogrens Syndrome");
INSERT INTO "Patient" VALUES ("7", "M", "MCTD");
INSERT INTO "Patient" VALUES ("8", "F", "Fibromyalgia");
INSERT INTO "Patient" VALUES ("9", "M", "MCTD");
INSERT INTO "Patient" VALUES ("10", "F", "Lupus");
INSERT INTO "Examination" VALUES ("1", "2023-01-15", "MCTD", "Joint pain, fatigue");
INSERT INTO "Examination" VALUES ("2", "2023-01-20", "SLE", "Rash, fever");
INSERT INTO "Examination" VALUES ("3", "2023-02-01", "MCTD", "Raynauds phenomenon");
INSERT INTO "Examination" VALUES ("4", "2023-02-10", "RA", "Swollen joints");
INSERT INTO "Examination" VALUES ("5", "2023-03-05", "MCTD", "Muscle weakness");
INSERT INTO "Examination" VALUES ("6", "2023-03-12", "Sjogrens Syndrome", "Dry eyes, dry mouth");
INSERT INTO "Examination" VALUES ("7", "2023-04-01", "MCTD", "Dyspnea");
INSERT INTO "Examination" VALUES ("8", "2023-04-10", "Fibromyalgia", "Widespread pain");
INSERT INTO "Examination" VALUES ("9", "2023-05-01", "MCTD", "Esophageal dysmotility");
INSERT INTO "Examination" VALUES ("10", "2023-05-15", "Lupus", "Hair loss");
INSERT INTO "Laboratory" VALUES ("1", "2023-01-16", "250");
INSERT INTO "Laboratory" VALUES ("1", "2023-02-16", "90");
INSERT INTO "Laboratory" VALUES ("2", "2023-01-21", "350");
INSERT INTO "Laboratory" VALUES ("3", "2023-02-02", "120");
INSERT INTO "Laboratory" VALUES ("3", "2023-03-02", "450");
INSERT INTO "Laboratory" VALUES ("4", "2023-02-11", "200");
INSERT INTO "Laboratory" VALUES ("5", "2023-03-06", "380");
INSERT INTO "Laboratory" VALUES ("5", "2023-04-06", "105");
INSERT INTO "Laboratory" VALUES ("6", "2023-03-13", "290");
INSERT INTO "Laboratory" VALUES ("7", "2023-04-02", "110");
INSERT INTO "Laboratory" VALUES ("7", "2023-05-02", "410");
INSERT INTO "Laboratory" VALUES ("8", "2023-04-11", "180");
INSERT INTO "Laboratory" VALUES ("9", "2023-05-02", "220");
INSERT INTO "Laboratory" VALUES ("9", "2023-06-02", "95");
INSERT INTO "Laboratory" VALUES ("10", "2023-05-16", "300");