SQL.txt

CREATE TABLE users(
id INTEGER PRIMARY KEY AUTOINCREMENT,
role_name TEXT NOT NULL,
gender TEXT,
birth DATE
DNI TEXT UNIQUE,
phone_number TEXT UNIQUE,
postcode TEXT,
specialization TEXT,
name TEXT NOT NULL,
hospital TEXT,
email TEXT NOT NULL,
<<<<<<< HEAD
password BLOB NOT NULL,
photo BLOB 
=======
password BLOB NOT NULL
photo BLOB
>>>>>>> branch 'master' of https://github.com/SanchaKvK/MedicalConsultancy.git

);


CREATE TABLE videoconsultation(
id_video INTEGER PRIMARY KEY AUTOINCREMENT,
consultation_date DATE NOT NULL,
consultation_time TIME NOT NULL,
duration INTEGER,
type_of_call TEXT,
notes TEXT,
id_doctor INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
id_patient INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL	
);

CREATE TABLE prescription(
id_prescription INTEGER PRIMARY KEY AUTOINCREMENT,
doses INTEGER NOT NULL,
notes TEXT,
duration INTEGER NOT NULL,
name TEXT NOT NULL,
id_video INTEGER REFERENCES videoconsultation(id_video) ON UPDATE CASCADE ON DELETE SET NULL
);

CREATE TABLE pathology(
id_pathology INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
type TEXT
);
CREATE TABLE patient_pathology(
id_patient INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
id_pathology INTEGER REFERENCES pathology(id_pathology) ON UPDATE CASCADE ON DELETE SET NULL,
PRIMARY KEY(id_patient,id_pathology)
);

CREATE TABLE rating(
id_patient INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
id_doctor INTEGER REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
score INTEGER,
review TEXT,
PRIMARY KEY(id_patient,id_doctor)
);