-- 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)
);
