/*capture module (OPD, IPD, OT) wise billing summery information*/
drop TABLE patient_service_charges ;
CREATE TABLE patient_service_charges  (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT NULL,	/*Table-patients-ID*/
	admission_no varchar(100)	NULL DEFAULT 0, /*Capture only for IPD patient from ipd_admission.admission_no*/
	bill_no varchar(100) NOT NULL, /*Auto generate bill number*/
	bill_date date NOT NULL, /*Bill date auto generated*/
	billing_module_id varchar(10) NOT NULL, /*permissions table perm_id*/
	service_source_id	int NOT NULL, /*For OPD - opd_doctor_appointments.ID, IPD - ipd_admission.id same for OT - ot_bookings.ID*/
	gross_amount decimal(15, 2) NOT NULL,
	discount_amount decimal(15, 2) NOT NULL,
	discount_percentage decimal(15, 2) NOT NULL,
	cgst_amount decimal(15, 2) NOT NULL,
	sgst_amount decimal(15, 2) NOT NULL,
	total_gst_amount decimal(15, 2) NOT NULL,
	net_amount decimal(15, 2) NOT NULL, 
	advance_amount decimal(15, 2) NOT NULL,
	refund_amount decimal(15, 2) NOT NULL,
	paid_amount decimal(15, 2) NOT NULL,
	due_amount decimal(15, 2) NOT NULL,
	tpa_claim_amount	decimal(15, 2) NOT NULL DEFAULT 0.00,
	tpa_approved_amount	decimal(15, 2) NOT NULL DEFAULT 0.00,
	note text NULL,
	status varchar(50) NULL DEFAULT NULL, /*Capture bill payment status P- Paid, U-Unpaid, R-Partial Paid, H-Hold */
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*capture individual chnages under each module (OPD, IPD, OT) or patient_service_charges*/
drop table patient_service_charge_details;
CREATE TABLE patient_service_charge_details (
	id int NOT NULL AUTO_INCREMENT,
	patient_service_charge_id	int NOT NULL, /*Table-patient_service_charges-ID*/
	visit_id	int  NULL DEFAULT 0,
	service_charge_date	datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, /*Service added TIMESTAMP*/
	service_id int NOT NULL, /*service_items ID column*/
	service_rate_id int NOT NULL, /*service_rates ID column*/
	service_from_time	datetime NULL DEFAULT NULL, /*Service added from time user only rule base service*/
	service_to_time	datetime NULL DEFAULT NULL, /*Service added to time user only rule base service*/
	total_service_time	decimal(15, 2) NULL DEFAULT NULL, /*Total time of used service in minute*/
	standard_rate	decimal(15, 2) NOT NULL, /*fetch from service_rates.rate*/
	tpa_rate decimal(15, 2) NOT NULL DEFAULT 0.00, /*fetch from tpa_include_charges.charge_rate based on beneficiary percentage (tpa_beneficiarywise_charges and tpa_insurance_beneficiary), user can edit it*/
	applicable_rate decimal(15, 2) NOT NULL, /*fetch from standard_rate or tpa_rate(if tpa approved), user can change it*/
	discount_amount decimal(15, 2) NOT NULL,
	discount_percentage decimal(15, 2) NOT NULL,
	cgst_amount decimal(15, 2) NOT NULL,
	sgst_amount decimal(15, 2) NOT NULL,
	gst_amount decimal(15, 2) NOT NULL,
	net_amount decimal(15, 2) NOT NULL, /*net applicable charge*/
	status varchar(50) NULL DEFAULT NULL, /*C - Add as Charge, B - Add in Bill, R-Revart from Bill, D - Delete, A- Approved*/
	is_tpa_charge	enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_approved_by_tpa enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_voucher_posted enum('Y', 'N') DEFAULT 'N' NOT NULL,
	is_discount_vouch_posted enum('Y', 'N') DEFAULT 'N' NOT NULL,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*capture all type of patient payment details OPD, IPD, OT, Any Aervice, Pharmacy */
Drop table patient_payments;
CREATE TABLE patient_payments  (
	id int NOT NULL AUTO_INCREMENT,
	charge_source varchar(50) NOT NULL, /*Pharmacy, Service(OPD, IPD, OT), Order*/
	patient_service_charge_id	int NOT NULL, /*Pharmacy - pharmacy_billing_header - ID, Service - patient_service_charges - ID*/
	payment_mode_id enum('D', 'C', 'Q', 'T', 'W', 'A', 'I', 'F') NOT NULL,	/* Lookup - PAYMENT MODE - D-Credit Card,C - Cash, Q- Cheque, T - Online Transfer, W - Payment Wallet, A-Adjest from advance, I-Discount, F-Fund TRANSFER*/
	admission_id	int NUll default 0, /*Used for advance payment , Table - ipd_admission.ID*/
	patient_order_id	int NULL,	/*Order - Prescriptions - ID*/
	payment_type enum('P', 'A', 'R', 'T', 'J') NOT NULL, /* Type of payments P-Payment, J-Adjustment, A-Advance, R-Refund, T-Return */
	payment_date date NOT NULL, /*Auto generated payment date*/
	paid_amount decimal(15, 2) NOT NULL, /*Actual paid amount*/
	receiving_bank INT DEFAULT NULL,
	note text NULL,
	document_location varchar(500) NULL, /*payment document location*/
	other_payment_dtls json,	/*other payment details capture on json*/
	is_voucher_posted enum('Y','N') NOT NULL DEFAULT 'N',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*capture advance payment and transaction history*/
CREATE TABLE patient_advance_balance  (
	id int NOT NULL AUTO_INCREMENT,
	advance_for	enum('I', 'P'), /*I - IPD, P-Pharmacy*/
	payment_type enum('A', 'P'), /*A - Adjustment, P-Payment, R-Adjust from Refund*/
	admission_id	int NUll default 0, /*Used for advance payment and adjustment , Table - ipd_admission.ID*/
	patient_id int NOT NULL, /*Table-patients-ID*/
	balance_date datetime NOT NULL, /*Auto capture balance date*/
	trans_amount decimal(15, 2) NOT NULL, /*advance transaction amount (payment, adjustment)*/
	balance_amount decimal(15, 2) NOT NULL, /*Cumulative balance for advance*/
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*capture all type of vendor payment details for any kind inventory payment*/
CREATE TABLE vendor_payments  (
	id int NOT NULL AUTO_INCREMENT,
	data_source varchar(50) NOT NULL, /*Purchase, Asset, Lab Refund, Miscellaneous*/
	data_source_id	int NOT NULL, /*Purchase - purchase_order_receipts - ID*, Others- asset_trans_header-id */
	payment_mode_id enum('C', 'Q', 'T') NOT NULL,	/* Lookup - VENDOR_PAYMENT_MODE - C - Cash, Q- Cheque, T - Online Transfer*/
	payment_type enum('P','T','R') NULL DEFAULT 'P', /*P-payment and T-Return, 'R'-Receipt, 'A'-Purchase Order Advance*/
	payment_date date NOT NULL, /*Payment Disbursed date*/
	paid_amount decimal(15, 2) NOT NULL, /*Actual paid amount*/
	note text NULL,
	document_location varchar(500) NULL, /*payment document location*/
	other_payment_dtls json,	/*other payment details capture on json*/
	receiver_source varchar(255) NULL, /*Table name - 'Suppliers' (for vendor), 'Lab' (for refund), 'Miscellaneous' (for miscellaneous) */
	receiver_id int NULL, /*Supplier or Vendor id*/
	parent_payment_id int NULL, /*Payment id (particular payment id for which events are paid)*/
	approval_status enum ('Y', 'N', 'R') /* 'Y' - 'Approved' , 'N' - 'Un-Approved' ,'R' - 'Rejected' */
	approved_by int NULL, /* Payment Approved By - 'users.id' */
	approved_at date NOT NULL, /* Payment Approved at */
	disbursement_status enum ('Y', 'N') /* 'Y' - 'Disbursed' , 'N' - 'Not Disbursed' */
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*miscellaneous_income_expenditure Master, any kind of miscellaneous transaction master should be stored here*/
CREATE TABLE miscellaneous_income_expenditure  (
	id int NOT NULL AUTO_INCREMENT,
	name varchar(255) NOT NULL, /*Miscellaneous Name*/
	is_exp_income enum('E', 'I', 'T') NOT NULL,	/* E- Expences, I- Income */
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	is_active enum('Y', 'N') DEFAULT 'Y',
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;
