create_tables.sql

CREATE DATABASE IF NOT EXISTS drugs_db;

USE drugs_db;

CREATE TABLE IF NOT EXISTS Patient (
  patientId INT AUTO_INCREMENT PRIMARY KEY,
  firstName VARCHAR(255) NOT NULL,
  middleName VARCHAR(255),
  lastName VARCHAR(255) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  dateOfBirth DATE DEFAULT '1970-01-01',
  residentialAddress VARCHAR(255) NOT NULL,
  phoneNumber VARCHAR(15) NOT NULL,
  emailAddress VARCHAR(255) NOT NULL,
  passwordHash VARCHAR(255) NOT NULL,
  lastSeen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  SSN VARCHAR(255) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Specialty (
  specialtyId INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Practitioner (
  practitionerId INT AUTO_INCREMENT PRIMARY KEY,
  firstName VARCHAR(255) NOT NULL,
  middleName VARCHAR(255),
  lastName VARCHAR(255) NOT NULL,
  gender VARCHAR(6) NOT NULL,
  dateOfBirth DATE DEFAULT '1970-01-01',
  phoneNumber VARCHAR(15) NOT NULL,
  emailAddress VARCHAR(255) NOT NULL,
  passwordHash VARCHAR(255) NOT NULL,
  lastSeen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  SSN VARCHAR(255) NOT NULL,
  activeYear YEAR NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  specialtyId INT,
  FOREIGN KEY (specialtyId) REFERENCES Specialty(specialtyId)
);

CREATE TABLE IF NOT EXISTS Patient_Practitioner (
  patientPractitionerId INT AUTO_INCREMENT PRIMARY KEY,
  patientId INT NOT NULL,
  practitionerId INT NOT NULL,
  primaryPractitioner BOOLEAN NOT NULL DEFAULT FALSE,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (patientId) REFERENCES Patient(patientId),
  FOREIGN KEY (practitionerId) REFERENCES Practitioner(practitionerId)
);

CREATE TABLE IF NOT EXISTS Pharmaceutical (
  pharmaceuticalId INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  locationAddress VARCHAR(255) NOT NULL,
  emailAddress VARCHAR(255) NOT NULL,
  phoneNumber VARCHAR(15) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Pharmacy (
  pharmacyId INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  locationAddress VARCHAR(255) NOT NULL,
  emailAddress VARCHAR(255) NOT NULL,
  phoneNumber VARCHAR(15) NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Drug (
  drugId INT AUTO_INCREMENT PRIMARY KEY,
  scientificName VARCHAR(255) NOT NULL,
  formula VARCHAR(255),
  form VARCHAR(255),
  dateCreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  lastUpdated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS Supervisor (
  supervisorId INT AUTO_INCREMENT PRIMARY KEY,
  firstName VARCHAR(255) NOT NULL,
  middleName VARCHAR(255),
  lastName VARCHAR(255) NOT NULL,
  emailAddress VARCHAR(255) NOT NULL,
  phoneNumber VARCHAR(20) NOT NULL,
  active TINYINT(1) DEFAULT 1,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW()
);

CREATE TABLE IF NOT EXISTS Contract (
  contractId INT AUTO_INCREMENT PRIMARY KEY,
  pharmacyId INT NOT NULL,
  pharmaceuticalId INT NOT NULL,
  startDate DATE NOT NULL,
  endDate DATE NOT NULL,
  description VARCHAR(255),
  fileUrl VARCHAR(255),
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (pharmacyId) REFERENCES Pharmacy(pharmacyId),
  FOREIGN KEY (pharmaceuticalId) REFERENCES Pharmaceutical(pharmaceuticalId)
);

CREATE TABLE IF NOT EXISTS Contract_Supervisor (
  contractSupervisorId INT AUTO_INCREMENT PRIMARY KEY,
  contractId INT NOT NULL,
  supervisorId INT NOT NULL,
  active TINYINT(1) DEFAULT 1,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (contractId) REFERENCES Contract(contractId),
  FOREIGN KEY (supervisorId) REFERENCES Supervisor(supervisorId)
);

CREATE TABLE IF NOT EXISTS Contract_Supply (
  contractSupplyId INT AUTO_INCREMENT PRIMARY KEY,
  contractId INT NOT NULL,
  paymentComplete TINYINT(1) DEFAULT 0,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (contractId) REFERENCES Contract(contractId)
);

CREATE TABLE IF NOT EXISTS Supply_Item (
  supplyItemId INT AUTO_INCREMENT PRIMARY KEY,
  contractSupplyId INT NOT NULL,
  drugId INT NOT NULL,
  tradename VARCHAR(255) NOT NULL,
  quantity INT NOT NULL,
  costPrice DECIMAL(10,2) NOT NULL,
  sellingPrice DECIMAL(10,2) NOT NULL,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (contractSupplyId) REFERENCES Contract_Supply(contractSupplyId),
  FOREIGN KEY (drugId) REFERENCES Drug(drugId)
);

CREATE TABLE IF NOT EXISTS Supervisor_Contract (
  supervisorContractId INT AUTO_INCREMENT PRIMARY KEY,
  supervisorId INT NOT NULL,
  contractId INT NOT NULL,
  active TINYINT(1) DEFAULT 1,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (supervisorId) REFERENCES Supervisor(supervisorId),
  FOREIGN KEY (contractId) REFERENCES Contract(contractId)
);

CREATE TABLE IF NOT EXISTS Prescription (
  prescriptionId INT AUTO_INCREMENT PRIMARY KEY,
  quantity INT NOT NULL,
  frequency VARCHAR(255) NOT NULL,
  practitionerId INT NOT NULL,
  supplyItemId INT NOT NULL,
  assigned TINYINT(1) DEFAULT 0,
  dateCreated DATETIME NOT NULL DEFAULT NOW(),
  lastUpdated DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW(),
  FOREIGN KEY (practitionerId) REFERENCES Practitioner(practitionerId),
  FOREIGN KEY (supplyItemId) REFERENCES Supply_Item(supplyItemId)
);