/*Create medicine group like paracetamol, antacid etc.*/
create table medicine_group (
	id int not null auto_increment,
	medicine_group_name varchar(50) not null,
	medicine_groupdescription varchar(50) null,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
)
;

/*Capture pharmacy related detail for an item*/
CREATE TABLE pharmacy_items  (
	id int NOT NULL auto_increment,
	item_id int not null, /*Table - items - ID*/
	medicine_group_id int not null,	/*Table - medicine_group - ID*/
	medicine_composition_dtl varchar(1000) null, /*medicine composition rule*/
	hsn varchar(100)  NOT NULL DEFAULT '\'\'', /*Harmonised System of Nomenclature code*/
	description text  NULL,
	nabh_category varchar(50)  NULL,	/*National Accreditation Board for Hospitals and Healthcare Providers (NABH) category Lookup - NABH_CATG*/
	drug_type varchar(50)  NULL DEFAULT NULL, /*Lookup - DRUG_TYPE*/
	special_instruction text NULL DEFAULT NULL,
	potency text NULL DEFAULT NULL,
	drug_risk text NULL DEFAULT NULL,
	formulation text  NULL DEFAULT NULL, /*Medicine composition or formula*/
	is_loose_sale_allowed enum('N','Y')  NULL DEFAULT 'N', /*is the medicine allow for loose sale*/
	is_prescription_required enum('N','Y')  NULL DEFAULT 'Y', /*is the prescription required for the medicine*/
	is_expirable	enum('N','Y')  NULL DEFAULT 'Y', /*is the medicine expirable*/
	expire_note text  NULL DEFAULT NULL, /*expire note if expire date was not mandatary and the  medicine is_expirable = Y*/
	max_expire_time	int DEFAULT NULL, /*Max expire time expirable medicine*/
	medicine_image varchar(200)  NULL DEFAULT NULL, /*medicine image location*/
	is_ot_medicine enum('Y', 'N') DEFAULT 'N', /*is the medicine allow in OT*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	 primary key (id) using btree
) ;


/*Saefty information for the medicine*/
CREATE TABLE medicine_saefty_info  (
	id int NOT NULL auto_increment,
	item_id int not null, /*Table - items - ID*/
	gender varchar(50) not null, /*Lookup - GENDER_TYPE */
	saefty_category varchar(50) not null, /*Lookup - MEDICINE_SAEFTY_CATEGORY Pregnancy, Alcoholic, Driving etc.*/
	saefty_details text  not null,
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	 primary key (id) using btree
) ;


/*Medicine Dosages information for the medicine*/
CREATE TABLE medicine_dosages  (
	id int NOT NULL auto_increment,
	item_id int not null,	/*Table - items - ID*/
	gender varchar(50) not null, 	/*Lookup - GENDER_TYPE */
	from_age decimal(12, 2) not null, 
	to_age decimal(12, 2) not null,
	medicine_taken_uom varchar(50) not null, /*Lookup - COMMON_UOM*/
	medicine_taken_min_qty decimal(12, 2) not null, /*Minimum consumption quantity as per age range and gender*/
	medicine_taken_max_qty decimal(12, 2) not null,/*Maximum consumption quantity as per age range and gender*/
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
) ;


/*Capture benifit of taken that medicine*/
CREATE TABLE medicine_benifit_effect  (
	id int NOT NULL auto_increment,
	item_id int not null,	/*Table - items - ID*/
	benifit text  not null,
	side_effect text  not null,
	how_to_use text  not null, /*Rule to taken that medicine*/
	how_it_work text  not null,	/*How medicine cure us or work with our body*/
	is_active enum('N','Y') default 'Y',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
) ;


/*Capture data of walkin patient who is not from OPD or IPD, just come outside to purchase a medicine*/
CREATE TABLE pharmacy_walkin_patient (
	id int NOT NULL auto_increment,
	patient_name varchar(200)  NOT NULL,
	patient_contact varchar(50)  NULL,
	last_appear_date	date null,
	created_at datetime,
	created_by int,
	primary key (id) using btree
);


/*Header table to capture total bill summery for sell of medicine*/
CREATE TABLE pharmacy_billing_header (
	id int NOT NULL auto_increment,	
	bill_no varchar(50)  NOT NULL,	/*Auto generated PB/YYYY/MM/Serial number 6 digit padded with zero number*/
	bill_date datetime(0) NULL DEFAULT NULL,	/*pharmacy bill date*/
	patient_type varchar(10)  NOT NULL, /*Not display E-Existing (OPD, IPD) W-Walk-In*/
	patient_id int NOT NULL,	/*Table-patients-ID*/
	opd_ipd_no INT  NULL DEFAULT 0,	/*OPD-opd_doctor_appointments-ID, OT-ot_bookings-ID, IPD-ipd_admission-ID*/
	case_id int NOT NULL DEFAULT 0,	/*Table-case_details-ID*/
	visit_id int NOT NULL DEFAULT 0,	/*Table-visit_details-ID*/
	doctor_id int NOT NULL DEFAULT 0, /*Table-doctors-ID, ID capture for OPD, IPD Table - doctors*/
	doctor_name varchar(50) NOT NULL DEFAULT '', /*Name of doctor enter manually for walkin patient*/
	prescription_image varchar(200)  NULL, /*prescription image location*/
	total_sgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_cgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_gross_amt decimal(15, 2) NULL DEFAULT 0.00,
	discount_amt decimal(15, 2) NULL DEFAULT 0.00,
	total_gst_amount decimal(15, 2) NULL DEFAULT 0.00,
	total_discount_percent decimal(15, 2) NULL DEFAULT 0.00,
	due_amount	decimal(15, 2) NULL DEFAULT 0.00,	/*These field are helping */
	paid_amount	decimal(15, 2) NULL DEFAULT 0.00,
	refund_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_net_amount decimal(15, 2) NOT NULL,
	pharmacy_note varchar(1000)  NULL DEFAULT NULL,
	status varchar(50)  NOT NULL, /*P - Billed and Paid, B- Billed but not paid*/
	prescription_id int NULL DEFAULT NULL, /*Table - prescription - ID*/
	billing_module_id int NULL DEFAULT NULL, /*Table-permissions-perm_id*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
) ;


/*pharmacy billing item under pharmacy_billing_header table*/
CREATE TABLE pharmacy_billing_item  (
	id int NOT NULL,
	pharmacy_billing_header_id INT  NOT NULL, /*Table-pharmacy_billing_header-ID*/
	item_id int NOT NULL, /*Table-items-ID*/
	expire_date varchar(100)  NOT NULL, /*Expire date of selling medicine*/
	batch_no varchar(200)  NOT NULL, /*medicine match number*/
	sale_quantity int  NOT NULL, /*medicine sell quantity*/
	is_loose_sold enum('Y','N')  NULL DEFAULT 'N', 
	strip_qty	int default 0,
	tax_group_id int not null,  /*Table-tax_group-id*/
	mrp decimal(15, 2) NOT NULL,
	sale_price decimal(15, 2) NOT NULL DEFAULT 0.00,  /*Gross sale price*/
	gross_sale_price decimal(15, 2) NOT NULL,
	unit_price decimal(10, 2) NULL DEFAULT NULL, /*use for loose sale*/
	discount_perc decimal(15, 2) NOT NULL,
	discount_value decimal(15, 2) NOT NULL,
	cgst_amt decimal(15, 2) NOT NULL,
	sgst_amt	decimal(15, 2) NOT NULL,
	net_sale_price decimal(15, 2) NOT NULL,	/*Net sale price*/
	status varchar(50)  NOT NULL, /*Y - Billed, N-Delete*/
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Capture Pharmacy return item summery - amount, discount, etc.*/
CREATE TABLE pharmacy_return_header (
	id int NOT NULL auto_increment,
	bill_no varchar(50)  NOT NULL,	/*Auto generated PB/YYYY/MM/Serial number 6 digit padded with zero number*/
	bill_date datetime(0) NULL DEFAULT NULL, /*pharmacy_billing_header-bill_date*/
	return_date	datetime(0) NULL DEFAULT NULL, /*auto generated current date*/
	patient_type varchar(10)  NOT NULL, /*Not display E-Existing (OPD, IPD) W-Walk-In*/
	patient_id int NOT NULL, /*Table-patients-ID*/
	pharmacy_billing_header_id int NOT NULL, /*Table-pharmacy_billing_header-id*/
	opd_ipd_ot_flag	enum('O','I', 'T', 'E', 'NA') NULL DEFAULT 'NA', /*O-OPD, I-IPD, T-OT, E-Emergency*/
	opd_ipd_no INT  NULL DEFAULT 0,/*OPD-opd_doctor_appointments-ID, OT-ot_bookings-ID, IPD-ipd_admission-ID*/
	case_id int NOT NULL DEFAULT 0,	/*Table-case_details-ID*/
	doctor_id int NOT NULL DEFAULT 0, /* ID capture for OPD, IPD Table - doctors*/
	doctor_name varchar(50) NOT NULL DEFAULT '',
	discount_amount decimal(12, 2) not null default 0.00,
	total_sgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_cgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_gst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	total_gross_amt decimal(15, 2) NULL DEFAULT 0.00,	/*Net amount after adding gst and substract discount*/
	pharmacy_return_note varchar(1000)  NULL DEFAULT NULL,
	status varchar(50)  NOT NULL, /*U - Unapprove, A-Approved, R-Reject*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
) ;


/*Capture Pharmacy return item under pharmacy_return_header*/
CREATE TABLE pharmacy_return_items  (
	id int NOT NULL,
	pharmacy_return_header_id int NOT NULL, /*Table - pharmacy_return_header - ID*/
	pharmacy_billing_header_id INT  NOT NULL,	/*Table - pharmacy_billing_header - ID*/
	pharmacy_billing_item_id INT  NOT NULL, /*Table - pharmacy_billing_item - ID*/
	item_id int NOT NULL, /*Table - items - ID*/
	batch_no varchar(200)  NOT NULL, /*return item batch number*/
	sale_quantity decimal(15, 2)  NOT NULL, /*sale quantity against the return item*/
	available_quantity decimal(15, 2)  NOT NULL, /*available quantity for return, sale_quantity - previous return_quantity */
	return_quantity decimal(15, 2)  NOT NULL,
	is_loose_sold enum('Y','N')  NULL DEFAULT 'N',
	loose_item_qty	int default 0,
	tax_group_id int not null, 	/*Table-tax_group-id*/
	mrp decimal(15, 2) NOT NULL, 
	sale_price decimal(15, 2) NOT NULL, 
	gross_return_price decimal(15, 2) NOT NULL,
	discount_amount NOT NULL default 0.00,
	unit_price decimal(10, 2) NULL DEFAULT NULL, /*use for loose sale*/
	cgst_amt decimal(15, 2) NOT NULL,
	sgst_amt	decimal(15, 2) NOT NULL,
	net_return_price decimal(15, 2) NOT NULL, /*actual/net reutn price*/
	status varchar(50)  NOT NULL, /*Y - Return, N-Delete*/
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Create a view by joining 2 table patients (OPD/IPD/Emergency) and pharmacy_walkin_patient(Walkin)*/
Drop view if EXISTS vw_pharmacy_patient;
CREATE VIEW vw_pharmacy_patient as
SELECT
	'E' AS `patient_type`,
	`patients`.`id` AS `id`,
	`patients`.`patient_unique_id` AS `patient_unique_id`,
	`patients`.`title` AS `patient_title`,
	`patients`.`patient_name` AS `patient_name`,
	`patients`.`first_name` AS `first_name`,
	`patients`.`last_name` AS `last_name`,
	`patients`.`age` AS `age_year`,
	`patients`.`month` AS `age_month`,
	`patients`.`days` AS `age_days`,
	`patients`.`dob` AS `dob`,
	`patients`.`gender` AS `gender`,
	`patients`.`marital_status` AS `marital_status`,
	`patients`.`blood_group` AS `blood_group`,
	`contacts`.`contact_details` AS `contact_details`,
	`email`.`contact_details` AS `email`,
	`addresses`.`pin` AS `pin`,
	concat_ws( ' ', `addresses`.`house_no`, `addresses`.`street` ) AS `street`,
	`addresses`.`locality` AS `locality` 
FROM
	(((
				`patients`
				LEFT JOIN `contacts` ON (((
							`contacts`.`data_source_id` = `patients`.`id` 
							) 
						AND ( `contacts`.`data_source` = 'Patient' ) 
						AND ( `contacts`.`contact_type` = 'M' ) 
					AND ( `contacts`.`is_active` = 'Y' ))))
			LEFT JOIN `contacts` `email` ON (((
						`email`.`data_source_id` = `patients`.`id` 
						) 
					AND ( `email`.`data_source` = 'Patient' ) 
				AND ( `email`.`contact_type` = 'E' ))))
		LEFT JOIN `addresses` ON (((
					`addresses`.`data_source_id` = `patients`.`id` 
					) 
			AND ( `addresses`.`data_source` = 'Patient' ) 
	AND ( `addresses`.`address_type` = 'P' ))))
union
select 'W', id, '', '', patient_name, '', '', 0, 0, 0,'01/01/1970', '', '', '', patient_contact, '', '', '', ''
from pharmacy_walkin_patient;


/*Pharmacy return item details and price*/
Create or replace view vw_pharmacy_return_details
as
Select 	
	prh.pharmacy_billing_header_id pharmacy_billing_header_id,
	pri.pharmacy_billing_item_id pharmacy_billing_item_id,
	pri.item_id item_id, 
	sum(ifnull(prh.total_round_amount, 0)) return_round_amount,
	max(pri.strip_qty) return_strip_qty,
	sum(pri.sale_quantity) sale_quantity, 
	sum(pri.return_quantity) return_quantity, 
	sum(ifnull(pri.return_loose_quantity, 0)) return_loose_quantity, 
	sum(pri.available_quantity) available_quantity, 
	sum(ifnull(pri.available_loose_quantity, 0)) available_loose_quantity, 
	sum(pri.discount_amount) return_discount_amount, 
	sum(pri.gross_return_price) return_gross_price,
	sum(pri.cgst_amt) return_cgst,
	sum(pri.sgst_amt) return_sgst,
	sum(pri.net_return_price) return_net_price
from	
	pharmacy_return_header prh	
	inner join 	pharmacy_return_items pri 
		on (prh.id = pri.pharmacy_return_header_id 
		and pri.status IN ('A', 'Y') 
		and prh.status = 'A')
group by 	prh.pharmacy_billing_header_id, 
		pri.pharmacy_billing_item_id,
		item_id;
		
