/*Table used for patient admission in IPD, its store - admission date, doctor, tpa policy details*/
CREATE TABLE ipd_admission  (
	id int NOT NULL AUTO_INCREMENT,
	admission_date date NOT NULL,	/*admited date*/
	admission_no varchar(50) NOT NULL,	/*autogenerated admited number*/
	admission_note text NOT NULL, 
	patient_id int NOT NULL,	/*Table - pateints - ID*/
	is_register enum('Y','N', 'E', 'NA'), /*Y-Yes, N-No, R-registration expire, NA - Not applicable*/
	registration_fees	decimal(15, 2) NOT NULL DEFAULT 0.00,
	doctor_id int NOT NULL, /*Table - doctors - ID*/
	is_deceased enum('Y','N') NOT NULL DEFAULT 'N', /*COMMENT Y-yes,N-no, NA-Not applicable*/
	is_discharged enum('Y','N') NOT NULL DEFAULT 'N', /*COMMENT Y-yes,N-no, NA-Not applicable*/
	discharged_date datetime(0)  NULL,	/*Date of discharged*/
	policy_id int NULL DEFAULT NULL,	/*Table - tpa_policies - ID*/
	beneficiary_id int NULL DEFAULT NULL,	/*Table - tpa_insurance_beneficiary - ID*/
	is_tpa_approved enum('Y','N', 'NA') NOT NULL DEFAULT 'NA', /*Default NA - Set Y once TPA is approved*/
	is_tpa_paid enum('Y','N', 'NA') NOT NULL DEFAULT 'NA',  /*Default NA - Set Y once TPA is sattled the case*/
	card_no varchar(50) NULL ,	/*insurance policy card number*/
	is_pharmacy_settled enum('Y','N') NOT NULL DEFAULT 'N',
	crdt_lmt int NULL DEFAULT 0,	/*insurance policy card limit*/
	is_discharge_death_certi_ready enum('Y','N', 'NA') NOT NULL DEFAULT 'NA', 
	is_bed_charge_applied enum('Y','N') NOT NULL DEFAULT 'N',
	is_emergency 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 ;


CREATE TABLE emergency_details  (
  id int NOT NULL,
  admission_id int NOT NULL,
  patient_id int NOT NULL,
  op_ip_flag enum('O','I') NOT NULL DEFAULT 'O',
  casualty_flag enum('Y','N') NOT NULL DEFAULT 'N',
  casualty_note varchar(500) NOT NULL,
  note text NULL DEFAULT NULL,
  emergency_trf_date datetime DEFAULT NULL,
  emergency_trf_from int NULL,
  emergency_trf_from_id int NULL,
  emergency_trf_to int NULL,
  emergency_trf_to_id int NULL,
  created_at datetime(0) NOT NULL,
  created_by int NOT NULL,
  updated_at datetime(0) NULL DEFAULT NULL,
  updated_by int NULL DEFAULT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture IPD bed transfer history against a patient*/
CREATE TABLE ipd_bed_details  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	case_id int NOT NULL,	/*Table-case_details-ID*/
	visit_id int NOT NULL, /*Table-visit_details-ID*/
	occupied_from datetime NOT NULL,	/*Bed occupied from date and time*/
	occupied_to datetime NULL,	/*Bed occupied to date and time*/
	bed_id	int NOT NULL DEFAULT 0, /*Table-Bed-ID*/
	ipd_case_visit_transfer_id int NULL DEFAULT 0,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture IPD consultant list / audit trail history against a patient and admission*/
CREATE TABLE ipd_patient_consultant  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	addtion_date date NOT NULL,	/*Doctor/consultant added on*/
	patient_id int NOT NULL,	/*Table-patients-ID*/
	doctor_id int NOT NULL,		/*Table-doctors-ID*/
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	remarks	text 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 IPD case and visit transfer history against each IPD admission*/
drop table ipd_case_visit_transfer;
CREATE TABLE ipd_case_visit_transfer  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	transfer_date datetime NOT NULL,	/*Transfer Date*/
	patient_id int NOT NULL,	/*Table-patients-ID*/
	operation_type enum('A', 'C', 'V') NOT NULL, /*A-Admit, C-Case Transfer, V-Visit Transfer*/
	from_case_id	int NULL, /*case_detail ID, Transfer from*/
	from_visit_id	int NULL,	/*visit_detail ID, Transfer from*/
	to_case_id	int NOT NULL,	 /*case_detail ID, Transfer to*/
	to_visit_id	int NOT NULL,	/*visit_detail ID, Transfer to*/
	is_trf_new_case enum('Y', 'N') NOT NULL DEFAULT 'N',	
	is_trf_new_visit enum('Y', 'N') NOT NULL DEFAULT 'N',	
	remarks	text 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 IPD consultant / doctor physical visit history against case and visit ID for a patient*/
CREATE TABLE ipd_patient_consultant_visit  (
	id int NOT NULL AUTO_INCREMENT,
	visited_date datetime NOT NULL,	/*Doctor/consultant visited date time*/
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	case_id int NOT NULL, /*Table - case_details - ID*/ 
	visit_id int NOT NULL, /*Table - visit_details - ID*/ 
	patient_id int NOT NULL,	/*Table-patients-ID*/
	doctor_id int NOT NULL,		/*Table-doctors-ID*/
	service_item_id int NOT NULL, /*Table-service_items-ID*/
	service_rate_id int NOT NULL, 	/*Table-service_rates-ID*/
	standard_rate	decimal(12, 0) NOT NULL ,
	applicable_rate	decimal(12, 0) NOT NULL ,
	trns_srv_id	int  NULL, /*Table-patient_service_charge_details-ID*/
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	remarks	text 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 ;


/*List of IPD templet*/
CREATE TABLE ipd_templates  (
	id int NOT NULL AUTO_INCREMENT,
	template_category enum('B', 'D', 'T'), /*Lookup - TEMPLATE_CATEGORY - B-Birth, T-Death, D-Discharge*/
	type_of_template	varchar(50), /*Lookup - TEMPLATE_TYPE*/
	template_heading	varchar(500) NOT NULL,
	template	longtext 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 Birth record for a patient*/
CREATE TABLE ipd_birth_records  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	child_admission_id int  NULL,	/*Table-ipd_admission-ID, update admission record created*/
	parent_patient_id int NOT NULL,	/*Table - pateints - ID*/
	child_patient_id int NULL,	/*Table - pateints - ID, update aftre approve when child patient ID created*/
	last_active_case	int NOT NULL,	/*Table-case_details-ID*/
	last_active_visit	int NOT NULL,	/*Table-visit_details-ID*/
	title varchar(50) NOT NULL ,
	first_name varchar(100) NOT NULL ,
	middle_name varchar(100) DEFAULT '' ,
	last_name varchar(100) NOT NULL ,
	image varchar(100)  NULL DEFAULT NULL,
	dob date NULL DEFAULT NULL,
	so_do	varchar(100)	NULL DEFAULT NULL,
	guardian_name varchar(100) NULL DEFAULT NULL,
	weight int null default 0,	/*Weight of child in Grams*/
	note varchar(1000)  NOT NULL,
	status enum('U','A')  NOT NULL DEFAULT 'U',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime NULL,
	updated_by int NULL,
	approved_at datetime NULL,
	approved_by int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*IPD death and discharge templet details*/
CREATE TABLE ipd_discharge_details  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	patient_id int NOT NULL,	/*Table - pateints - ID*/
	last_active_case	int NOT NULL,	/*Table-case_details-ID*/
	last_active_visit	int NOT NULL,	/*Table-visit_details-ID*/
	discharge_datetime	datetime NULL, /*Daeth and Discharge date time*/
	discharge_template_type	varchar(50), /*Lookup - TEMPLATE_TYPE*/
	template	longtext NOT NULL,
	discharge_prescription_id	int NULL,	/*Table-prescriptions-ID*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;


/*capture IPD bed charge details*/
CREATE TABLE ipd_bed_charge_details  (
	id int NOT NULL AUTO_INCREMENT,
	ipd_admission_id int NOT NULL,	/*Table-ipd_admission-ID*/
	patient_id int NOT NULL,	/*Table - pateints - ID*/
	bed_id	int NOT NULL,
	occupied_from	datetime NOT NULL,
	occupied_to	datetime NOT NULL,
	occupied_days	int NOT NULL,
	occupied_hours	int NOT NULL,
	early_checkin_flag	int NOT NULL,
	late_checkout_flag	int NOT NULL,
	total_days	int NOT NULL,
	per_day_charge	decimal(10, 2),
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB ;

