SQLize | PHPize | SQLtest

CREATE TABLE Person ( idperson INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT NOT NULL, familyname TEXT NOT NULL, dateofbirth TEXT NOT NULL ); CREATE TABLE Credentials ( email VARCHAR(255) PRIMARY KEY, -- Changed TEXT to VARCHAR(255) to fix key length issue password TEXT NOT NULL, user_type TEXT NOT NULL CHECK(user_type IN ('doctor', 'patient', 'nurse', 'center_admin')), person_id INTEGER UNIQUE, FOREIGN KEY (person_id) REFERENCES Person(idperson) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE Doctor ( iddoctor INTEGER PRIMARY KEY AUTOINCREMENT, idperson INTEGER NOT NULL UNIQUE, FOREIGN KEY (idperson) REFERENCES Person(idperson) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE Nurse ( idnurse INTEGER PRIMARY KEY AUTOINCREMENT, idperson INTEGER NOT NULL UNIQUE, FOREIGN KEY (idperson) REFERENCES Person(idperson) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE CenterAdmin ( idadmin INTEGER PRIMARY KEY AUTOINCREMENT, idperson INTEGER NOT NULL UNIQUE, FOREIGN KEY (idperson) REFERENCES Person(idperson) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE Patient ( idpatient INTEGER PRIMARY KEY AUTOINCREMENT, idperson INTEGER NOT NULL UNIQUE, FOREIGN KEY (idperson) REFERENCES Person(idperson) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE DoctorPatient ( iddoctor INTEGER, idpatient INTEGER, PRIMARY KEY (iddoctor, idpatient), FOREIGN KEY (iddoctor) REFERENCES Doctor(iddoctor) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (idpatient) REFERENCES Patient(idpatient) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE Medicine ( id INTEGER PRIMARY KEY AUTOINCREMENT, Med_name TEXT NOT NULL UNIQUE ); CREATE TABLE Prescription ( id_prescription INTEGER PRIMARY KEY AUTOINCREMENT, idpatient INTEGER, id_medicine INTEGER, iddoctor INTEGER, quantity INTEGER DEFAULT 1, status TEXT DEFAULT 'pending' CHECK(status IN ('pending', 'administered', 'cancelled')), prescription_date TEXT, FOREIGN KEY (idpatient) REFERENCES Patient(idpatient) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (id_medicine) REFERENCES Medicine(id) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (iddoctor) REFERENCES Doctor(iddoctor) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE VaccinationCenter ( idcenter INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, address TEXT, admin_id INTEGER UNIQUE, FOREIGN KEY (admin_id) REFERENCES CenterAdmin(idadmin) ON DELETE SET NULL ON UPDATE CASCADE ); CREATE TABLE CenterStock ( id INTEGER PRIMARY KEY AUTOINCREMENT, center_id INTEGER, vaccine_id INTEGER, quantity INTEGER DEFAULT 0 CHECK(quantity >= 0), last_updated TEXT, UNIQUE (center_id, vaccine_id), FOREIGN KEY (center_id) REFERENCES VaccinationCenter(idcenter) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (vaccine_id) REFERENCES Medicine(id) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE AdministrationLog ( id INTEGER PRIMARY KEY AUTOINCREMENT, prescription_id INTEGER UNIQUE, nurse_id INTEGER, center_id INTEGER, administered_at TEXT, FOREIGN KEY (prescription_id) REFERENCES Prescription(id_prescription) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (nurse_id) REFERENCES Nurse(idnurse) ON DELETE SET NULL ON UPDATE CASCADE, FOREIGN KEY (center_id) REFERENCES VaccinationCenter(idcenter) ON DELETE SET NULL ON UPDATE CASCADE );