RelationalDesign: synergymed_ddl.sql

-- Drop all tables if they exist
DROP TABLE IF EXISTS synergymed.branded_medicine_instanceof_medicine CASCADE;
DROP TABLE IF EXISTS synergymed.allergicreaction_healthprofile_medicine CASCADE;
DROP TABLE IF EXISTS synergymed.supplyorder_brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.shoppingcart_brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.pharmacy_catalog CASCADE;
DROP TABLE IF EXISTS synergymed.patientorder_brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.patientorder CASCADE;
DROP TABLE IF EXISTS synergymed.inventory_brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.healthprofile CASCADE;
DROP TABLE IF EXISTS synergymed.distributor_brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.contactinformation CASCADE;
DROP TABLE IF EXISTS synergymed.clubcard CASCADE;
DROP TABLE IF EXISTS synergymed.brandedmedicineimage CASCADE;
DROP TABLE IF EXISTS synergymed.brandedmedicine CASCADE;
DROP TABLE IF EXISTS synergymed.supplyorder CASCADE;
DROP TABLE IF EXISTS synergymed.shoppingcart CASCADE;
DROP TABLE IF EXISTS synergymed.sensitivepatientdata CASCADE;
DROP TABLE IF EXISTS synergymed.prescription CASCADE;
DROP TABLE IF EXISTS synergymed.pharmacy CASCADE;
DROP TABLE IF EXISTS synergymed.pharmacist CASCADE;
DROP TABLE IF EXISTS synergymed.payment CASCADE;
DROP TABLE IF EXISTS synergymed.patient CASCADE;
DROP TABLE IF EXISTS synergymed.medicineinteraction CASCADE;
DROP TABLE IF EXISTS synergymed.manufacturer CASCADE;
DROP TABLE IF EXISTS synergymed.inventory CASCADE;
DROP TABLE IF EXISTS synergymed.facility CASCADE;
DROP TABLE IF EXISTS synergymed.distributor CASCADE;
DROP TABLE IF EXISTS synergymed.deliverycompany CASCADE;
DROP TABLE IF EXISTS synergymed.admins CASCADE;
DROP TABLE IF EXISTS synergymed.users CASCADE;
DROP TABLE IF EXISTS synergymed.paymentmethod CASCADE;
DROP TABLE IF EXISTS synergymed.medicine CASCADE;
DROP TABLE IF EXISTS synergymed.company CASCADE;

-- Create schema
CREATE SCHEMA IF NOT EXISTS synergymed AUTHORIZATION postgres;

-- Table: company
CREATE TABLE synergymed.company (
  id INT4 NOT NULL,
  company_name VARCHAR(255) NOT NULL,
  description TEXT,
  registration_number VARCHAR(100) NOT NULL,
  CONSTRAINT company_pkey PRIMARY KEY (id),
  CONSTRAINT company_registration_number_key UNIQUE (registration_number)
);

-- Table: medicine
CREATE TABLE synergymed.medicine (
  id INT4 NOT NULL,
  medicine_name VARCHAR(255) NOT NULL,
  active_ingredient VARCHAR(255) NOT NULL,
  CONSTRAINT medicine_pkey PRIMARY KEY (id)
);

-- Table: paymentmethod
CREATE TABLE synergymed.paymentmethod (
  id INT4 NOT NULL,
  method_name VARCHAR(50) NOT NULL,
  CONSTRAINT paymentmethod_pkey PRIMARY KEY (id)
);

-- Table: users
CREATE TABLE synergymed.users (
  id INT4 NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  username VARCHAR(100) NOT NULL,
  hashed_password VARCHAR(255) NOT NULL,
  e_mail VARCHAR(255) NOT NULL,
  gender VARCHAR(50),
  date_created DATE NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_username_key UNIQUE (username),
  CONSTRAINT users_e_mail_key UNIQUE (e_mail)
);

-- Table: admins
CREATE TABLE synergymed.admins (
  user_id INT4 NOT NULL,
  CONSTRAINT admin_pkey PRIMARY KEY (user_id),
  CONSTRAINT admin_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id)
);

-- Table: deliverycompany
CREATE TABLE synergymed.deliverycompany (
  company_id INT4 NOT NULL,
  CONSTRAINT deliverycompany_pkey PRIMARY KEY (company_id),
  CONSTRAINT deliverycompany_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);

-- Table: distributor
CREATE TABLE synergymed.distributor (
  company_id INT4 NOT NULL,
  CONSTRAINT distributor_pkey PRIMARY KEY (company_id),
  CONSTRAINT distributor_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);

-- Table: facility
CREATE TABLE synergymed.facility (
  id INT4 NOT NULL,
  company_id INT4 NOT NULL,
  facility_name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NOT NULL,
  CONSTRAINT facility_pkey PRIMARY KEY (id),
  CONSTRAINT facility_code_key UNIQUE (code),
  CONSTRAINT facility_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);

-- Table: inventory
CREATE TABLE synergymed.inventory (
  facility_id INT4 NOT NULL,
  CONSTRAINT inventory_pkey PRIMARY KEY (facility_id),
  CONSTRAINT inventory_facility_id_key UNIQUE (facility_id),
  CONSTRAINT inventory_facility_id_fkey FOREIGN KEY (facility_id) REFERENCES synergymed.facility(id)
);

-- Table: manufacturer
CREATE TABLE synergymed.manufacturer (
  company_id INT4 NOT NULL,
  CONSTRAINT manufacturer_pkey PRIMARY KEY (company_id),
  CONSTRAINT manufacturer_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);

-- Table: medicineinteraction
CREATE TABLE synergymed.medicineinteraction (
  medicine_id_1 INT4 NOT NULL,
  medicine_id_2 INT4 NOT NULL,
  type VARCHAR(100) NOT NULL,
  description TEXT,
  severity VARCHAR(50) NOT NULL,
  CONSTRAINT medicineinteraction_pkey PRIMARY KEY (medicine_id_1, medicine_id_2),
  CONSTRAINT medicineinteraction_check CHECK (medicine_id_1 <> medicine_id_2),
  CONSTRAINT medicineinteraction_medicine_id_1_fkey FOREIGN KEY (medicine_id_1) REFERENCES synergymed.medicine(id),
  CONSTRAINT medicineinteraction_medicine_id_2_fkey FOREIGN KEY (medicine_id_2) REFERENCES synergymed.medicine(id)
);

-- Table: patient
CREATE TABLE synergymed.patient (
  user_id INT4 NOT NULL,
  is_verified BOOL NOT NULL,
  CONSTRAINT patient_pkey PRIMARY KEY (user_id),
  CONSTRAINT patient_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id)
);

-- Table: payment
CREATE TABLE synergymed.payment (
  id INT4 NOT NULL,
  patient_id INT4 NOT NULL,
  payment_method_id INT4 NOT NULL,
  payment_date DATE NOT NULL,
  amount INT4 NOT NULL,
  status VARCHAR(50) NOT NULL,
  CONSTRAINT payment_pkey PRIMARY KEY (id),
  CONSTRAINT payment_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id),
  CONSTRAINT payment_payment_method_id_fkey FOREIGN KEY (payment_method_id) REFERENCES synergymed.paymentmethod(id)
);

-- Table: pharmacist
CREATE TABLE synergymed.pharmacist (
  user_id INT4 NOT NULL,
  CONSTRAINT pharmacist_pkey PRIMARY KEY (user_id),
  CONSTRAINT pharmacist_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id)
);

-- Table: pharmacy
CREATE TABLE synergymed.pharmacy (
  company_id INT4 NOT NULL,
  CONSTRAINT pharmacy_pkey PRIMARY KEY (company_id),
  CONSTRAINT pharmacy_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);

-- Table: prescription
CREATE TABLE synergymed.prescription (
  id INT4 NOT NULL,
  patient_id INT4,
  medicine_id INT4 NOT NULL,
  issued_by VARCHAR(20) NOT NULL,
  issued_at DATE NOT NULL,
  valid_to DATE NOT NULL,
  embg VARCHAR(20) NOT NULL,
  CONSTRAINT prescription_pkey PRIMARY KEY (id),
  CONSTRAINT prescription_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id),
  CONSTRAINT prescription_medicine_id_fkey FOREIGN KEY (medicine_id) REFERENCES synergymed.medicine(id)
);

-- Table: sensitivepatientdata
CREATE TABLE synergymed.sensitivepatientdata (
  id INT4 NOT NULL,
  patient_id INT4 NOT NULL,
  pharmacist_id INT4,
  embg VARCHAR(20) NOT NULL,
  portrait_photo VARCHAR(255) NOT NULL,
  CONSTRAINT sensitivepatientdata_pkey PRIMARY KEY (id),
  CONSTRAINT sensitivepatientdata_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id),
  CONSTRAINT sensitivepatientdata_pharmacist_id_fkey FOREIGN KEY (pharmacist_id) REFERENCES synergymed.pharmacist(user_id)
);

-- Table: shoppingcart
CREATE TABLE synergymed.shoppingcart (
  id INT4 NOT NULL,
  patient_id INT4 NOT NULL,
  CONSTRAINT shoppingcart_pkey PRIMARY KEY (id),
  CONSTRAINT shoppingcart_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id)
);

-- Table: supplyorder
CREATE TABLE synergymed.supplyorder (
  id INT4 NOT NULL,
  distributor_id INT4 NOT NULL,
  pharmacy_id INT4 NOT NULL,
  order_date DATE NOT NULL,
  expected_arrival_date DATE NOT NULL,
  CONSTRAINT supplyorder_pkey PRIMARY KEY (id),
  CONSTRAINT supplyorder_distributor_id_fkey FOREIGN KEY (distributor_id) REFERENCES synergymed.distributor(company_id),
  CONSTRAINT supplyorder_pharmacy_id_fkey FOREIGN KEY (pharmacy_id) REFERENCES synergymed.pharmacy(company_id)
);

-- Table: brandedmedicine
CREATE TABLE synergymed.brandedmedicine (
  id INT4 NOT NULL,
  manufacturer_id INT4 NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  description TEXT,
  dosage_form VARCHAR(100) NOT NULL,
  strength VARCHAR(100) NOT NULL,
  origin_country VARCHAR(100),
  CONSTRAINT brandedmedicine_pkey PRIMARY KEY (id),
  CONSTRAINT brandedmedicine_manufacturer_id_fkey FOREIGN KEY (manufacturer_id) REFERENCES synergymed.manufacturer(company_id)
);

-- Table: brandedmedicineimage
CREATE TABLE synergymed.brandedmedicineimage (
  id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  image VARCHAR(255) NOT NULL,
  CONSTRAINT brandedmedicineimage_pkey PRIMARY KEY (id),
  CONSTRAINT brandedmedicineimage_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: clubcard
CREATE TABLE synergymed.clubcard (
  id INT4 NOT NULL,
  user_id INT4 NOT NULL,
  club_program VARCHAR(100) NOT NULL,
  points INT4 NOT NULL,
  CONSTRAINT clubcard_pkey PRIMARY KEY (id),
  CONSTRAINT clubcard_patient_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.patient(user_id)
);

-- Table: contactinformation
CREATE TABLE synergymed.contactinformation (
  id INT4 NOT NULL,
  phone VARCHAR(20),
  address VARCHAR(255),
  user_id INT4,
  facility_id INT4,
  CONSTRAINT contactinformation_pkey PRIMARY KEY (id),
  CONSTRAINT contactinformation_target_oneof_chk CHECK (((user_id IS NOT NULL)::int + (facility_id IS NOT NULL)::int) = 1),
  CONSTRAINT contactinformation_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id),
  CONSTRAINT contactinformation_facility_id_fkey FOREIGN KEY (facility_id) REFERENCES synergymed.facility(id)
);

-- Table: distributor_brandedmedicine
CREATE TABLE synergymed.distributor_brandedmedicine (
  id INT4 NOT NULL,
  distributor_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  CONSTRAINT distributorbrandedmedicine_pkey PRIMARY KEY (id),
  CONSTRAINT distributorbrandedmedicine_distributor_id_fkey FOREIGN KEY (distributor_id) REFERENCES synergymed.distributor(company_id),
  CONSTRAINT distributorbrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: healthprofile
CREATE TABLE synergymed.healthprofile (
  id INT4 NOT NULL,
  patient_id INT4 NOT NULL,
  blood_type VARCHAR(10),
  CONSTRAINT healthprofile_pkey PRIMARY KEY (id),
  CONSTRAINT healthprofile_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id)
);

-- Table: inventory_brandedmedicine
CREATE TABLE synergymed.inventory_brandedmedicine (
  inventory_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  quantity INT4 NOT NULL,
  last_changed DATE NOT NULL,
  CONSTRAINT inventorybrandedmedicine_pkey PRIMARY KEY (inventory_id, branded_medicine_id),
  CONSTRAINT inventorybrandedmedicine_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES synergymed.inventory(facility_id),
  CONSTRAINT inventorybrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: patientorder
CREATE TABLE synergymed.patientorder (
  id INT4 NOT NULL,
  patient_id INT4 NOT NULL,
  delivery_company_id INT4 NOT NULL,
  payment_id INT4 NOT NULL,
  order_date DATE NOT NULL,
  expected_arrival_date DATE NOT NULL,
  status VARCHAR(100) NOT NULL,
  total_price INT4 NOT NULL,
  CONSTRAINT orders_pkey PRIMARY KEY (id),
  CONSTRAINT orders_payment_id_key UNIQUE (payment_id),
  CONSTRAINT orders_patient_id_fkey FOREIGN KEY (patient_id) REFERENCES synergymed.patient(user_id),
  CONSTRAINT orders_delivery_company_id_fkey FOREIGN KEY (delivery_company_id) REFERENCES synergymed.deliverycompany(company_id),
  CONSTRAINT orders_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES synergymed.payment(id)
);

-- Table: patientorder_brandedmedicine
CREATE TABLE synergymed.patientorder_brandedmedicine (
  id INT4 NOT NULL,
  order_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  quantity INT4 NOT NULL,
  CONSTRAINT patientorder_brandedmedicine_pkey PRIMARY KEY (id),
  CONSTRAINT orderbrandedmedicine_order_id_fkey FOREIGN KEY (order_id) REFERENCES synergymed.patientorder(id),
  CONSTRAINT orderbrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: pharmacy_catalog
CREATE TABLE synergymed.pharmacy_catalog (
  pharmacy_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  CONSTRAINT pharmacy_catalog_pkey PRIMARY KEY (pharmacy_id, branded_medicine_id),
  CONSTRAINT pharmacybrandedmedicine_pharmacy_id_fkey FOREIGN KEY (pharmacy_id) REFERENCES synergymed.pharmacy(company_id),
  CONSTRAINT pharmacybrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: shoppingcart_brandedmedicine
CREATE TABLE synergymed.shoppingcart_brandedmedicine (
  shopping_cart_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  quantity INT4 NOT NULL,
  CONSTRAINT shoppingcartbrandedmedicine_pkey PRIMARY KEY (shopping_cart_id, branded_medicine_id),
  CONSTRAINT shoppingcartbrandedmedicine_shopping_cart_id_fkey FOREIGN KEY (shopping_cart_id) REFERENCES synergymed.shoppingcart(id),
  CONSTRAINT shoppingcartbrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: supplyorder_brandedmedicine
CREATE TABLE synergymed.supplyorder_brandedmedicine (
  supply_order_id INT4 NOT NULL,
  branded_medicine_id INT4 NOT NULL,
  quantity INT4 NOT NULL,
  CONSTRAINT supplyorderbrandedmedicine_pkey PRIMARY KEY (supply_order_id, branded_medicine_id),
  CONSTRAINT supplyorderbrandedmedicine_supply_order_id_fkey FOREIGN KEY (supply_order_id) REFERENCES synergymed.supplyorder(id),
  CONSTRAINT supplyorderbrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);

-- Table: allergicreaction_healthprofile_medicine
CREATE TABLE synergymed.allergicreaction_healthprofile_medicine (
  health_profile_id INT4 NOT NULL,
  medicine_id INT4 NOT NULL,
  date_diagnosed DATE,
  description TEXT,
  severity VARCHAR(50),
  CONSTRAINT healthprofilemedicineallergy_pkey PRIMARY KEY (health_profile_id, medicine_id),
  CONSTRAINT healthprofilemedicineallergy_health_profile_id_fkey FOREIGN KEY (health_profile_id) REFERENCES synergymed.healthprofile(id),
  CONSTRAINT healthprofilemedicineallergy_medicine_id_fkey FOREIGN KEY (medicine_id) REFERENCES synergymed.medicine(id)
);

-- Table: branded_medicine_instanceof_medicine
CREATE TABLE synergymed.branded_medicine_instanceof_medicine (
  branded_medicine_id INT4 NOT NULL,
  medicine_id INT4 NOT NULL,
  CONSTRAINT brandedmedicinemedicine_pkey PRIMARY KEY (branded_medicine_id, medicine_id),
  CONSTRAINT brandedmedicinemedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
  CONSTRAINT brandedmedicinemedicine_medicine_id_fkey FOREIGN KEY (medicine_id) REFERENCES synergymed.medicine(id)
);