SQLize | PHPize | SQLtest

CREATE TABLE Person (     idperson INTEGER PRIMARY KEY AUTOINCREMENT,     firstname TEXT NOT NULL,     familyname TEXT NOT NULL,     dateofbirth TEXT NOT NULL -- Storing as TEXT, consider-MM-DD format ); CREATE TABLE Credentials (     email TEXT PRIMARY KEY,     password TEXT NOT NULL,     user_type TEXT NOT NULL CHECK(user_type IN ('doctor', 'patient', 'nurse', 'center_admin')),     person_id INTEGER UNIQUE, -- Ensure one credential per person     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, -- Ensure one doctor profile per person     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, -- Ensure one nurse profile per person     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, -- Ensure one admin profile per person     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, -- Ensure one patient profile per person     FOREIGN KEY (idperson) REFERENCES Person(idperson) ON DELETE CASCADE ON UPDATE CASCADE ); -- Junction table to link doctors and their patients CREATE TABLE DoctorPatient (     iddoctor INTEGER,     idpatient INTEGER,     PRIMARY KEY (iddoctor, idpatient), -- Ensures a doctor can only be linked to a patient once     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 -- Vaccine names should be 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, -- Date of prescription (e.g.,-MM-DD)     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, -- Assuming one admin per center     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, -- This refers to Medicine.id     quantity INTEGER DEFAULT 0 CHECK(quantity >= 0), -- Quantity cannot be negative     last_updated TEXT, -- Timestamp for when the stock was last updated     UNIQUE (center_id, vaccine_id), -- Ensures one stock entry per vaccine per center     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, -- A prescription should only be administered once     nurse_id INTEGER,     center_id INTEGER, -- Center where the vaccine was administered     administered_at TEXT, -- Timestamp of administration (e.g., ISO8601 format)     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 );