-- credits: André Restivo https://web.fe.up.pt/~arestivo
-- Revised by Gonçalo Gonçalves

PRAGMA foreign_keys = ON;
.headers on
.mode columns

DROP TABLE IF EXISTS diagnosed;
DROP TABLE IF EXISTS appointment;
DROP TABLE IF EXISTS physician;
DROP TABLE IF EXISTS patient;
DROP TABLE IF EXISTS condition;

CREATE TABLE patient (
	code TEXT PRIMARY KEY,
	name TEXT,
	address TEXT
);
 
CREATE TABLE physician (
	number TEXT PRIMARY KEY,
	name TEXT,
	address TEXT
);
 
CREATE TABLE condition (
	ref INTEGER PRIMARY KEY,
	designation TEXT
);
 
CREATE TABLE appointment (
	num INTEGER PRIMARY KEY,
	date TEXT,
	room INTEGER,
	number TEXT REFERENCES physician,
	code TEXT REFERENCES patient
);

CREATE TABLE diagnosed (
	ref INTEGER REFERENCES condition,
	num INTEGER REFERENCES appointment,
	PRIMARY KEY (ref, num)
);
 
INSERT INTO physician (number, name, address) VALUES ('99030', 'Luca Moore', '16 Victoria Road');
INSERT INTO physician (number, name, address) VALUES ('12312', 'Tommy Cooke', '47 Bootham Terrace');
INSERT INTO physician (number, name, address) VALUES ('43642', 'Joshua Bailey', '95 Nottingham Rd');
 
INSERT INTO patient (code, name, address) VALUES ('12345', 'Xander Gibbs', '45 Petworth Rd');
INSERT INTO patient (code, name, address) VALUES ('45643', 'Tatiana Barber', '16 Vicar Lane');
INSERT INTO patient (code, name, address) VALUES ('23613', 'Amelie Harrison', '32 Thompsons Lane');
INSERT INTO patient (code, name, address) VALUES ('74927', 'Kathryn Savege', '67 Cosworth Rd');
 
INSERT INTO appointment (num, date, room, number, code) VALUES (456, '2007-01-01', 190, '99030', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (457, '2007-01-01', 187, '99030', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (458, '2007-01-01', 190, '12312', '23613');
INSERT INTO appointment (num, date, room, number, code) VALUES (459, '2007-01-01', 190, '99030', '45643');
INSERT INTO appointment (num, date, room, number, code) VALUES (460, '2007-01-01', 187, '99030', '45643');
INSERT INTO appointment (num, date, room, number, code) VALUES (461, '2007-01-01', 187, '12312', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (462, '2007-01-01', 204, '12312', '23613');
INSERT INTO appointment (num, date, room, number, code) VALUES (463, '2007-01-01', 187, '99030', '45643');
INSERT INTO appointment (num, date, room, number, code) VALUES (464, '2007-01-02', 190, '12312', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (465, '2007-01-02', 190, '12312', '45643');
INSERT INTO appointment (num, date, room, number, code) VALUES (466, '2007-01-03', 190, '43642', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (467, '2007-01-03', 190, '99030', '12345');
INSERT INTO appointment (num, date, room, number, code) VALUES (468, '2007-01-03', 190, '43642', '74927');
 
INSERT INTO condition (ref, designation) VALUES (1, 'Wizard Plague');
INSERT INTO condition (ref, designation) VALUES (2, 'Arachnid Hepatitis');
INSERT INTO condition (ref, designation) VALUES (3, 'Anxious Sleepwalking');
 
INSERT INTO diagnosed (ref, num) VALUES (1, 456);
INSERT INTO diagnosed (ref, num) VALUES (2, 456);
INSERT INTO diagnosed (ref, num) VALUES (2, 457);
INSERT INTO diagnosed (ref, num) VALUES (3, 457);
INSERT INTO diagnosed (ref, num) VALUES (3, 458);
INSERT INTO diagnosed (ref, num) VALUES (2, 459);
INSERT INTO diagnosed (ref, num) VALUES (2, 460);
INSERT INTO diagnosed (ref, num) VALUES (3, 460);
INSERT INTO diagnosed (ref, num) VALUES (1, 460);
INSERT INTO diagnosed (ref, num) VALUES (2, 461);
INSERT INTO diagnosed (ref, num) VALUES (3, 462);
INSERT INTO diagnosed (ref, num) VALUES (1, 463);
INSERT INTO diagnosed (ref, num) VALUES (2, 464);
INSERT INTO diagnosed (ref, num) VALUES (3, 464);
INSERT INTO diagnosed (ref, num) VALUES (1, 465);
INSERT INTO diagnosed (ref, num) VALUES (3, 466);
INSERT INTO diagnosed (ref, num) VALUES (2, 467);
