
/* TPA */
/*LIst of TPA organisations*/
CREATE TABLE tpa_organisations  (
	id int NOT NULL AUTO_INCREMENT,
	organisation_name varchar(200) NOT NULL,	/*Organisation name*/
	org_code varchar(50) NOT NULL,	/*Organisation code*/
	time_span_unit enum('D','M','Y') NOT NULL, /*TPA sattelment reporting days, Unit - D-Day, M-Month, Y-Year*/
	invoice_time_span int NOT NULL, /*Total sattelment report*/
	contact_no varchar(200) NOT NULL,
	email varchar(255) NOT NULL,
	address varchar(300) NOT NULL,
	contact_person_name varchar(200) NOT NULL,
	contact_person_phone varchar(200) NOT NULL,
	contact_person_mail varchar(255) NOT NULL,
	mail_to_patient enum('Y', 'N') NOT NULL DEFAULT 'N',
	mail_to_provider enum('Y', 'N') NOT NULL DEFAULT 'N',
	sms_to_patient enum('Y', 'N') NOT NULL DEFAULT 'N',
	sms_to_provider 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;


/*Type of insurance policy and scheme under each organazation*/
CREATE TABLE tpa_policies  (
	id int NOT NULL AUTO_INCREMENT,
	tpa_id int NULL DEFAULT NULL,
	policy_payment_type enum('R','C') NOT NULL DEFAULT 'C', /* R-Reimbursement, C-Cash Less*/
	policy_name varchar(100)	NULL DEFAULT NULL, /*insurance policy name*/
	policy_code varchar(255)	NOT NULL,	/*insurance policy code*/
	module_id int NOT NULL,	/*Table - permissions - perm_id*/
	policy_type enum('R','A','S','I','C') NOT NULL COMMENT '\'R\'=>relief,\'A\'=>assuarance,\'S\'=>scheme,\'I\'=>insuarance,\'C\'=>contract',
	amount decimal(12, 0) NOT NULL, /*Not in use*/
	start_date date NOT NULL,	/*insurance scheme start date*/
	end_date date NULL DEFAULT NULL,	/*insurance scheme end date*/
	patient_acceptance_start_dt date NOT NULL,	/*patient acceptance start date*/
	patient_acceptance_end_dt date NULL DEFAULT NULL,	/*patient acceptance end date*/
	max_applicable_patient int NOT NULL DEFAULT 0,	/*max patient applicable*/
	in_ex_set enum('Y','N') NOT NULL DEFAULT 'N',	/*Include exclude set ot not*/
	charges_set enum('Y','N') NOT NULL DEFAULT 'N', /*charge set or not*/
	is_active enum('Y','N') NULL DEFAULT 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;


/*Type of beneficiary and their patient details for each insurance scheme, policy*/
CREATE TABLE tpa_insurance_beneficiary  (
	id int NOT NULL AUTO_INCREMENT,
	tpa_organisation_id int NOT NULL,	/*Table - tpa_organisations-ID*/
	tpa_policy_id int NOT NULL,	/*Table - tpa_policies-ID*/
	holder_type varchar(50) NOT NULL,	/*holder of the policy (Primary, secondary, child, aged etc.)*/
	num_of_eligible int NOT NULL, /*Max eligible beneficiary*/
	tpa_percent decimal(12, 2) NULL, /*TPA percentage on bill amount based on beneficiary*/
	patient_percent decimal(12, 2) NULL, /*Patient percentage on bill amount based on beneficiary*/
	is_active enum('Y','N') NULL DEFAULT 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;


/*Insurance policy wise service charges include and exclude details*/
CREATE TABLE tpa_include_charges  (
	id int NOT NULL AUTO_INCREMENT,
	tpa_organisation_id int NOT NULL,	/*Table - tpa_organisations-ID*/
	tpa_policy_id int NOT NULL,			/*Table - tpa_policies-ID*/
	charge_id int NOT NULL,			/*Table - service_items -ID */
	charge_rate decimal(12, 2) NOT NULL, /*Table - Service charge rate from service_rates, user can change it */
	effective_from date NOT NULL, /*Service rate effective from*/
	is_active enum('Y','N') NULL DEFAULT 'Y',
	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;


/*Beneficiary wise insurance policy service charges include and exclude details*/
--CREATE TABLE tpa_beneficiarywise_charges  (
--	id int NOT NULL AUTO_INCREMENT,
--	tpa_insurance_beneficiary_id int NOT NULL, /*Table - tpa_insurance_beneficiary - ID*/
--	tpa_include_charges_id int NOT NULL, /*Table - tpa_include_charges -ID*/
--	charge_type enum ('A', 'P'), /* A - Amount , P - Percentage*/
--	tpa_part decimal(12, 2) NOT NULL, /*Actual amount and percentage*/
--	patient_part decimal(12, 2)  NULL DEFAULT 0.00,
--	PRIMARY KEY (id) USING BTREE
--) ENGINE = InnoDB;


/*TPA workflow timeline history for an admited patient its will capture full tpa tracking*/
CREATE TABLE tpa_patient_claim_workflow (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT NULL,	/*Table - patients-ID*/
	ipd_admission_id int not null,
	tpa_policy_id int NOT NULL,			/*Table - tpa_policies-ID*/
	tpa_entry_date	datetime not null,
	status enum('A', 'I', 'C', 'S', 'F', 'D') DEFAULT 'A', /*A-Admitted, I-Initial Approval, C-Claim Sanction, S-Sanction, F-Final Claim, D-Discharge*/

	tpa_initial_approve_amount decimal(15, 2)	null,
	tpa_ini_app_created_by	int null,
	tpa_ini_app_created_at datetime null,
	tpa_ini_app_approved_by	int null,
	tpa_ini_app_approved_at datetime null,

	tpa_claim_amount decimal(15, 2)	null,
	tpa_claim_created_by	int null,
	tpa_claim_created_at datetime null,
	tpa_claim_approved_by	int null,
	tpa_claim_approved_at datetime null,

	tpa_sanction_amount decimal(15, 2)	null,
	tpa_sanction_created_by	int null,
	tpa_sanction_created_at datetime null,
	tpa_sanction_approved_by	int null,
	tpa_sanction_approved_at datetime null,
	apprv_ref_id	varchar(100) null,
	mail_document	varchar(255) null,
	tpa_trans_id	varchar(100) null,
	trans_doc_upld	varchar(255) null,
	
	paid_amt	decimal(16, 2) null,
	bank_name	varchar(100) null,
	tpa_pay_created_by	int null,
	tpa_pay_created_at	datetime null,
	tpa_pay_approved_by	int null,
	tpa_pay_approved_at		datetime null,
	
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture TPA payment details*/
CREATE TABLE tpa_payment_dtls  (
  id int NOT NULL,
  provider_id int NULL DEFAULT NULL,
  paid_amt varchar(255)  NULL DEFAULT NULL,
  paid_date date NULL DEFAULT NULL,
  trans_id int NULL DEFAULT NULL,
  doc_root varchar(255)  NULL DEFAULT NULL,
  created_at datetime(0) NULL DEFAULT NULL,
  created_by int NULL DEFAULT NULL,
  approved_at datetime(0) NULL DEFAULT NULL,
  approved_by int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB

 