create table packages(
	id int NOT NULL AUTO_INCREMENT,
	package_code varchar(50) NOT NULL,
	package_name varchar(200) NOT NULL,
	description text NULL,
	package_category varchar(50) NOT NULL,
	min_discharge_days int NOT NULL,
	max_discharge_days int NULL,
	is_active enum('Y','N') NOT NULL DEAFULT 'Y',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
	);


create table packages_include_charges(
	id int NOT NULL AUTO_INCREMENT,
	charge_source varchar(50) NOT NULL, /*service, pharmacy items */
	charge_source_id int NOT NULL,
	applicable_rate NULL,
	is_active enum('Y','N') NOT NULL DEAFULT 'Y',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
);


create table package_tpa_beneficiary_map(
	id int NOT NULL AUTO_INCREMENT,
	tpa_policies_id INT NOT NULL,	/*tpa_policies table ID column*/
	tpa_insurance_beneficiary_id int NOT NULL,	/*tpa_insurance_beneficiary table ID column*/
	package_amount decimal(15, 2) NOT NULL,
	tpa_percent decimal(12, 2) NOT NULL DEAFULT 100.00,
	patient_percent decimal(12, 2) NOT NULL DEAFULT 0.00,
	is_active enum('Y','N') NOT NULL DEAFULT 'Y',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	PRIMARY KEY (id) USING BTREE
);
