RelationalDesign: synergymed_ddl.2.sql

-- DROP SCHEMA synergymed;

CREATE SCHEMA synergymed AUTHORIZATION postgres;

COMMENT ON SCHEMA synergymed IS 'standard synergymed schema';

-- Drop table

-- DROP TABLE synergymed.company;

CREATE TABLE synergymed.company (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        company_name varchar(255) NOT NULL,
        description text NULL,
        registration_number varchar(100) NOT NULL,
        CONSTRAINT company_pkey PRIMARY KEY (id),
        CONSTRAINT company_registration_number_key UNIQUE (registration_number)
);


-- synergymed.medicine definition

-- Drop table

-- DROP TABLE synergymed.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)
);


-- synergymed.paymentmethod definition

-- Drop table

-- DROP TABLE synergymed.paymentmethod;

CREATE TABLE synergymed.paymentmethod (
        id int4 NOT NULL,
        method_name varchar(50) NOT NULL,
        CONSTRAINT paymentmethod_pkey PRIMARY KEY (id)
);


-- synergymed.users definition

-- Drop table

-- DROP TABLE synergymed.users;

CREATE TABLE synergymed.users (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) 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) NULL,
        date_created date DEFAULT CURRENT_DATE NOT NULL,
        date_of_birth date NOT NULL,
        is_account_non_expired bool DEFAULT true NOT NULL,
        is_account_non_locked bool DEFAULT true NOT NULL,
        is_credentials_non_expired bool DEFAULT true NOT NULL,
        is_enabled bool DEFAULT true NOT NULL,
        CONSTRAINT users_e_mail_key UNIQUE (e_mail),
        CONSTRAINT users_pkey PRIMARY KEY (id),
        CONSTRAINT users_username_key UNIQUE (username)
);


-- synergymed.admins definition

-- Drop table

-- DROP TABLE synergymed.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)
);


-- synergymed.client definition

-- Drop table

-- DROP TABLE synergymed.client;

CREATE TABLE synergymed.client (
        user_id int4 NOT NULL,
        is_verified bool NOT NULL,
        CONSTRAINT client_pkey PRIMARY KEY (user_id),
        CONSTRAINT client_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id)
);


-- synergymed.clubcard definition

-- Drop table

-- DROP TABLE synergymed.clubcard;

CREATE TABLE synergymed.clubcard (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) 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_client_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.client(user_id)
);


-- synergymed.deliverycompany definition

-- Drop table

-- DROP TABLE synergymed.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) ON DELETE CASCADE
);


-- synergymed.distributor definition

-- Drop table

-- DROP TABLE synergymed.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) ON DELETE CASCADE
);


-- synergymed.facility definition

-- Drop table

-- DROP TABLE synergymed.facility;

CREATE TABLE synergymed.facility (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        company_id int4 NOT NULL,
        facility_name varchar(150) NOT NULL,
        code varchar(50) NOT NULL,
        CONSTRAINT facility_code_key UNIQUE (code),
        CONSTRAINT facility_pkey PRIMARY KEY (id),
        CONSTRAINT facility_company_id_fkey FOREIGN KEY (company_id) REFERENCES synergymed.company(id)
);


-- synergymed.healthprofile definition

-- Drop table

-- DROP TABLE synergymed.healthprofile;

CREATE TABLE synergymed.healthprofile (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        client_id int4 NOT NULL,
        blood_type varchar(10) NULL,
        CONSTRAINT healthprofile_pkey PRIMARY KEY (id),
        CONSTRAINT healthprofile_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id)
);


-- synergymed.inventory definition

-- Drop table

-- DROP TABLE synergymed.inventory;

CREATE TABLE synergymed.inventory (
        facility_id int4 NOT NULL,
        id serial4 NOT NULL,
        CONSTRAINT inventory_facility_id_key UNIQUE (facility_id),
        CONSTRAINT inventory_pkey PRIMARY KEY (id),
        CONSTRAINT uq_inventory_facility UNIQUE (facility_id),
        CONSTRAINT inventory_facility_id_fkey FOREIGN KEY (facility_id) REFERENCES synergymed.facility(id)
);


-- synergymed.manufacturer definition

-- Drop table

-- DROP TABLE synergymed.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) ON DELETE CASCADE
);


-- synergymed.medicineinteraction definition

-- Drop table

-- DROP TABLE synergymed.medicineinteraction;

CREATE TABLE synergymed.medicineinteraction (
        medicine_id_1 int4 NOT NULL,
        medicine_id_2 int4 NOT NULL,
        "type" varchar(100) NOT NULL,
        description text NULL,
        severity varchar(50) NOT NULL,
        CONSTRAINT medicineinteraction_check CHECK ((medicine_id_1 <> medicine_id_2)),
        CONSTRAINT medicineinteraction_pkey PRIMARY KEY (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)
);


-- synergymed.payment definition

-- Drop table

-- DROP TABLE synergymed.payment;

CREATE TABLE synergymed.payment (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        client_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_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id),
        CONSTRAINT payment_payment_method_id_fkey FOREIGN KEY (payment_method_id) REFERENCES synergymed.paymentmethod(id)
);




-- synergymed.pharmacist definition

-- Drop table

-- DROP TABLE synergymed.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)
);


-- synergymed.pharmacy definition

-- Drop table

-- DROP TABLE synergymed.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) ON DELETE CASCADE
);


-- synergymed.prescription definition

-- Drop table

-- DROP TABLE synergymed.prescription;

CREATE TABLE synergymed.prescription (
        id int4 NOT NULL,
        client_id int4 NULL,
        medicine_id int4 NOT NULL,
        issued_by varchar(50) 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_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id),
        CONSTRAINT prescription_medicine_id_fkey FOREIGN KEY (medicine_id) REFERENCES synergymed.medicine(id)   
);


-- synergymed.sensitiveclientdata definition

-- Drop table

-- DROP TABLE synergymed.sensitiveclientdata;

CREATE TABLE synergymed.sensitiveclientdata (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        client_id int4 NOT NULL,
        pharmacist_id int4 NULL,
        embg varchar(20) NOT NULL,
        portrait_photo varchar(255) NOT NULL,
        verification_status varchar(50) NOT NULL,
        CONSTRAINT sensitiveclientdata_pkey PRIMARY KEY (id),
        CONSTRAINT sensitiveclientdata_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id),
        CONSTRAINT sensitiveclientdata_pharmacist_id_fkey FOREIGN KEY (pharmacist_id) REFERENCES synergymed.pharmacist(user_id)
);



-- synergymed.shoppingcart definition

-- Drop table

-- DROP TABLE synergymed.shoppingcart;

CREATE TABLE synergymed.shoppingcart (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        client_id int4 NOT NULL,
        CONSTRAINT shoppingcart_pkey PRIMARY KEY (id),
        CONSTRAINT shoppingcart_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id)
);


-- synergymed.supplyorder definition

-- Drop table

-- DROP TABLE synergymed.supplyorder;

CREATE TABLE synergymed.supplyorder (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        distributor_id int4 NOT NULL,
        pharmacy_id int4 NOT NULL,
        order_date date NOT NULL,
        expected_arrival_date date NOT NULL,
        status varchar(50) NOT NULL,
        facility_id int4 NOT NULL,
        CONSTRAINT supplyorder_pkey PRIMARY KEY (id),
        CONSTRAINT fk_supplyorder_facility FOREIGN KEY (facility_id) REFERENCES synergymed.facility(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)
);


-- synergymed.allergicreaction_healthprofile_medicine definition

-- Drop table

-- DROP TABLE synergymed.allergicreaction_healthprofile_medicine;

CREATE TABLE synergymed.allergicreaction_healthprofile_medicine (
        health_profile_id int4 NOT NULL,
        medicine_id int4 NOT NULL,
        date_diagnosed date NULL,
        description text NULL,
        severity varchar(50) NULL,
        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)
);


-- synergymed.brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.brandedmedicine;

CREATE TABLE synergymed.brandedmedicine (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        manufacturer_id int4 NOT NULL,
        price numeric(10, 2) NOT NULL,
        description text NULL,
        dosage_form varchar(100) NOT NULL,
        strength varchar(100) NOT NULL,
        origin_country varchar(100) NULL,
        "name" varchar(255) NOT NULL,
        CONSTRAINT brandedmedicine_pkey PRIMARY KEY (id),
        CONSTRAINT brandedmedicine_manufacturer_id_fkey FOREIGN KEY (manufacturer_id) REFERENCES synergymed.manufacturer(company_id)
);


-- synergymed.brandedmedicineimage definition

-- Drop table

-- DROP TABLE synergymed.brandedmedicineimage;

CREATE TABLE synergymed.brandedmedicineimage (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        branded_medicine_id int4 NOT NULL,
        image varchar(255) NOT NULL,
        is_main_image bool DEFAULT false NOT NULL,
        CONSTRAINT brandedmedicineimage_pkey PRIMARY KEY (id),
        CONSTRAINT brandedmedicineimage_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id)
);


-- synergymed.clientorder definition

-- Drop table

-- DROP TABLE synergymed.clientorder;

CREATE TABLE synergymed.clientorder (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        client_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 clientorder_payment_id_key UNIQUE (payment_id),
        CONSTRAINT clientorder_pkey PRIMARY KEY (id),
        CONSTRAINT clientorder_client_id_fkey FOREIGN KEY (client_id) REFERENCES synergymed.client(user_id),
        CONSTRAINT clientorder_delivery_company_id_fkey FOREIGN KEY (delivery_company_id) REFERENCES synergymed.deliverycompany(company_id),
        CONSTRAINT clientorder_payment_id_fkey FOREIGN KEY (payment_id) REFERENCES synergymed.payment(id)
);


-- synergymed.clientorder_brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.clientorder_brandedmedicine;

CREATE TABLE synergymed.clientorder_brandedmedicine (
        order_id int4 NOT NULL,
        branded_medicine_id int4 NOT NULL,
        quantity int4 NOT NULL,
        CONSTRAINT clientorder_brandedmedicine_pkey PRIMARY KEY (order_id, branded_medicine_id),
        CONSTRAINT clientorderbrandedmedicine_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
        CONSTRAINT clientorderbrandedmedicine_order_id_fkey FOREIGN KEY (order_id) REFERENCES synergymed.clientorder(id)
);


-- synergymed.contactinformation definition

-- Drop table

-- DROP TABLE synergymed.contactinformation;

CREATE TABLE synergymed.contactinformation (
        id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
        phone varchar(20) NULL,
        address varchar(255) NULL,
        user_id int4 NULL,
        facility_id int4 NULL,
        CONSTRAINT contactinformation_pkey PRIMARY KEY (id),
        CONSTRAINT contactinformation_target_oneof_chk CHECK (((((user_id IS NOT NULL))::integer + ((facility_id IS NOT NULL))::integer) = 1)),
        CONSTRAINT contactinformation_facility_id_fkey FOREIGN KEY (facility_id) REFERENCES synergymed.facility(id) ON DELETE CASCADE,
        CONSTRAINT contactinformation_user_id_fkey FOREIGN KEY (user_id) REFERENCES synergymed.users(id)
);


-- synergymed.distributor_brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.distributor_brandedmedicine;

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


-- synergymed.inventory_brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.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_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
        CONSTRAINT inventorybrandedmedicine_inventory_id_fkey FOREIGN KEY (inventory_id) REFERENCES synergymed.inventory(id)
);


-- synergymed.pharmacy_catalog definition

-- Drop table

-- DROP TABLE synergymed.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_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
        CONSTRAINT pharmacybrandedmedicine_pharmacy_id_fkey FOREIGN KEY (pharmacy_id) REFERENCES synergymed.pharmacy(company_id)
);


-- synergymed.shoppingcart_brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.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_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
        CONSTRAINT shoppingcartbrandedmedicine_shopping_cart_id_fkey FOREIGN KEY (shopping_cart_id) REFERENCES synergymed.shoppingcart(id)
);


-- synergymed.supplyorder_brandedmedicine definition

-- Drop table

-- DROP TABLE synergymed.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_branded_medicine_id_fkey FOREIGN KEY (branded_medicine_id) REFERENCES synergymed.brandedmedicine(id),
        CONSTRAINT supplyorderbrandedmedicine_supply_order_id_fkey FOREIGN KEY (supply_order_id) REFERENCES synergymed.supplyorder(id)
);


-- synergymed.branded_medicine_instanceof_medicine definition

-- Drop table

-- DROP TABLE synergymed.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)
);