/*A MASTER to maintain symptom, disease and alregies*/
CREATE TABLE symptom_disease  (
	id int NOT NULL AUTO_INCREMENT,
	symptom_disease varchar(50) NOT NULL, /*Lookup values PRE_EX_DISEASE_SYMPTOMS_TYPE - symptom, disease and alregies*/
	symptom_disease_name varchar(500) NOT NULL,
	gender_type enum('M','F','B') NOT NULL, /*Male, Female and both*/
	description text NOT NULL,
	is_active enum('Y','N') NOT NULL default 'Y',
	is_system enum('Y','N') NOT NULL default 'N',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*A MASTER to maintain vitals*/
CREATE TABLE vitals  (
	id int NOT NULL AUTO_INCREMENT,
	vital_module varchar(50) NOT NULL, /*Lookup values VITAL_MODULE - OPD, IPD, NURSING*/
	vital_name varchar(100) NOT NULL,
	measurement_unit	varchar(50) NOT NULL DEFAULT '',
	description text NOT NULL,  /*html text field column ID*/
	gender_type enum('M','F','B') NOT NULL,	/*Male, Female and both*/
	is_active enum('Y','N') NOT NULL default 'Y',
	is_system enum('Y','N') NOT NULL default 'N',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*A table to maintain doctor appointment booking */
CREATE TABLE opd_doctor_appointments  (
	id int NOT NULL AUTO_INCREMENT,
	booking_date	date NOT NULL, /*booking date*/
	doctor_appointment_date	datetime NOT NULL, /*doctor date and time*/
	appointment_no varchar(100) NOT NULL, /*Auto generated number*/
	appointment_srl int NOT NULL,	/*Serial for booking*/
	appointment_queue int NOT NULL default 0,	/*Serial for payment*/
	doctor_clinic_schedule_id int NOT NULL  default 0, /*Table - doctor_clinic_schedule - ID*/
	room_id int NOT NULL default 0, /*Table - floorwise_room - ID*/
	patient_id int NOT NULL,	/*Table - patients - ID*/
	is_register enum('Y','N', 'E', 'NA'), /*Y-Yes, N-No, E-registration expire, NA - Not applicable*/
	registration_fees	decimal(15, 2) NOT NULL DEFAULT 0.00, 
	doctor_id int NOT NULL, /*Table - doctors - ID*/
	doctor_fees decimal(15, 2) NOT NULL, /*Table - doctors - visit_charge*/
	message varchar(500) NULL DEFAULT NULL, /*use for reschedule and cancel*/
	appointment_status enum('B', 'C', 'M', 'N', 'R'), /*B-Booked, C-Confirmed, M-Completed, N-Cancelled, R-Re-Scheduled*/
	rescheduled_type enum('D','T', 'R', 'N') NOT NULL DEFAULT 'N', /*B-Doctor, C-Time, R-Room, N-Not Applicable*/
	appointment_note text, /*Note during booking of appoinment*/
	prev_appointment_id	INT NULL DEFAULT NULL, /*Capture previous appointment_id for reschedule case*/
	calc_rchd_requested_by	enum ('P', 'H') DEFAULT 'P',
	prev_case_id INT NULL DEFAULT NULL, /*Table-Case_detail-ID*, if patient visit against an existing open case*/
	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, /*Table - ipd_admission-ID*/
	patient_id int NOT NULL,
	casualty_flag enum('Y','N') NOT NULL default 'N',
	casualty_note varchar(500) NOT NULL,
	note varchar(500) NULL DEFAULT NULL,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Maintain case detail for each operation of Hospital like OPD, IPD, OT
For each section we open a new case we can open a multiple case for same IPD admission NUMBER
Through case a patient time line should be track*/
CREATE TABLE case_details  (
	id int NOT NULL AUTO_INCREMENT,
	case_no varchar(100) NOT NULL,	/*Auto generated case number*/
	reopen_id int NOT NULL DEFAULT 0,	/*If same case reopen in IPD case start with same number and next reopen id (i.e. 0 -> 1 -> 2)*/
	patient_id int NOT NULL,	/*Table - patients - ID*/
	opd_ipd_ot_flag	enum('O','I', 'T', 'E'), /*O-OPD, I-IPD, T-OT, E-Emergency*/
	opd_ipd_ot_id int NOT NULL,	/*OPD-opd_doctor_appointments-ID, OT-ot_bookings-ID, IPD-ipd_admission-ID*/
	case_register_date date NOT NULL,
	clearence_status enum('P','U', 'R', 'NA') NULL DEFAULT 'U', /* COMMENT P- paid, U-unpaid, NA-Not applicable, R-Partial Paid*/
	case_status enum('O','C', 'H', 'N') NOT NULL, /*COMMENT O - Open, C -Close, H- Hold, N-Cancel*/
	case_note text NULL,
	is_settled enum('Y','N') NOT NULL,
	prev_link_case_id int NULL,
	case_close_date datetime null,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Maintain visit detail under each case for each operation of Hospital like OPD, IPD, OT, earlier visit will close when open a new visit
OPD - insert new visit row for each visit, new visit can create under any open case
IPD - multiple visit under a single case, multi visit create depend on user choice based on patient condition and hospital policy. 
OT - Only one visit will create under a CASE, on complition of OT this visit will close
Through case and visit a patient time line should be track*/
CREATE TABLE visit_details  (
	id int NOT NULL AUTO_INCREMENT,
	visit_no varchar(100) NOT NULL, /*Auto generated visit number*/
	case_id int NOT NULL,	/*Table - case_details - ID*/
	opd_id int NULL DEFAULT 0,/*OPD-opd_doctor_appointments-ID*/
	visit_open_dt date NOT NULL,	
	visit_close_dt date NULL DEFAULT NULL,
	room_id int NOT NULL,	/*Table - floorwise_room - ID*/
	bed_id int  NULL DEFAULT 0, /*Table - bed - ID, for OPD it should be 0*/
	doctor_id int NULL DEFAULT NULL, /*Table - doctors - ID*/
	visit_status enum('O','C', 'H') NOT NULL, /*COMMENT O - Open, C -Close, H- Hold, N-Cancel*/
	is_prescription_uploaded enum('Y','N') NOT NULL DEFAULT 'N', /*If operator use prescription upload instead of digital prescription*/
	pres_doc_location varchar(500) NULL DEFAULT NULL, /*file location of uploaed prescription*/
	is_digital_prescription_filled enum('Y','N') NOT NULL DEFAULT 'N', /*If operator use digital prescription*/
	is_cancelled enum('Y','N') NOT NULL DEFAULT 'N', /*Table - if the visit cancel*/
	cancel_remarks text NULL,
	visit_close_date datetime null,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Patient symptom, disease, alergy capture trail for diffrent department OPD, IPD, OT, Emergency*/
CREATE TABLE patient_symptom_disease (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT NULL,	/*Table - patients - ID*/
	capture_date date NULL,	/*capture date*/
	opd_ipd_ot_flag	enum('O','I', 'T', 'E'), /*O-OPD, I-IPD, T-OT, E-Emergency*/
	opd_ipd_ot_id int NOT NULL, /*OPD-opd_doctor_appointments-ID, OT-ot_bookings-ID, IPD-ipd_admission-ID*/
	visit_id int NOT NULL default 0, /*Table - visit_details - ID*/
	symptom_disease_id  int NOT NULL, /*Table - symptom_disease - ID*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Patient vital capture tracking with staff and date for diffrent department OPD, IPD, OT, Emergency*/
CREATE TABLE patient_vitals (
	id int NOT NULL AUTO_INCREMENT,
	patient_id int NOT NULL,	/*Table - patients - ID*/
	vital_capture_date date  NULL, /*vital capture date*/
	opd_ipd_ot_flag	enum('O','I', 'T', 'E'), /*O-OPD, I-IPD, T-OT, E-Emergency*/
	opd_ipd_ot_id int NOT NULL, /*OPD-opd_doctor_appointments-ID, OT-ot_bookings-ID, IPD-ipd_admission-ID*/
	vital_taken_time enum('A','P') DEFAULT 'A', /*A-OPD and IPD appoinment, P-Patient Vital*/
	visit_id int NOT NULL default 0, /*Table - visit_details - ID*/
	task_staff_assignment_id int NOT NULL DEFAULT 0, /*Table task_staff_assignment - ID*/
	vital_id  int NOT NULL, /*Table vitals - ID*/
	vital_data	varchar(50) NULL, /*Capture vital data*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Prescription*/
/*International Classification of Diseases code master download from (https://www.cdc.gov/)*/
CREATE TABLE icd_master  (
	id int NOT NULL AUTO_INCREMENT,
	icd_general_code varchar(100) NOT  NULL,	/*ICD Code*/
	seq_no int NULL DEFAULT NULL,
	icd_specific_code varchar(100) NOT NULL,	/*Actual ICD Code*/
	icd_description varchar(250) NOT NULL,
	icd_name text NOT NULL,		/*Actual disease name*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Template mapping against ICD user can map multiple ICD master agsinst a single templet mapping*/
CREATE TABLE icd_templet_mapping  (
  id int NOT NULL,
  icd_master_id int NOT NULL, /*Table - icd_master - ID*/
  templet_name varchar(11) NOT NULL, /*disease wise ICD code templet name*/
  templet_desc text NOT NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*digital prescriptions header table link with visit details*/
CREATE TABLE prescriptions (
	id INT NOT NULL AUTO_INCREMENT,
	visit_id INT NOT NULL,	/*Table-visit_details-ID*/
	patient_vitals_id INT NULL, /*No need to capture value on it*/
	doctor_id INT NULL,
	doctor_note text NULL, /*doctor notes or remarks*/
	generated_on DATETIME NOT NULL, /*prescriptions generate date*/
	is_slip enum('Y', 'N', 'D', 'P') NOT NULL DEFAULT 'N', /*degital prescription type*, Y-prescription, N-Indention Slip, D-Discharge, P-Paient Order*/
	patient_order_cart_id int	NULL,
	doc_upload_location VARCHAR(500) NULL,
	is_deleted	enum('Y', 'N') DEFAULT 'N', /*Y-Deleted, N-Not Deleted */
	created_at datetime NOT NULL, 
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
);


/*prescription wise doctor observation*/
CREATE TABLE prescriptions_wise_observations (
	id int NOT NULL AUTO_INCREMENT,
	prescriptions_id int NOT NULL,	/*Table - prescriptions - ID*/
	srl_no int NOT NULL,
	observation varchar(1000), /*doctor observation */
	PRIMARY KEY (id) USING BTREE
);


/*capture ICD code against prescription*/
CREATE TABLE icd_codewise_prescriptions (
	id INT NOT NULL AUTO_INCREMENT,
	prescriptions_id int NOT NULL,	/*Table - prescriptions - ID*/
	icd_master_id int NOT NULL, /*Table - icd_master - ID*/
	icd_template_id int NOT NULL, /*Table - icd_templet_mapping - ID*/
	dscription text NOT NULL,
	PRIMARY KEY (id) USING BTREE
);


/*order  medicine and test against prescription*/
CREATE TABLE prescriptionwise_medicine_test (
	ID INT NOT NULL AUTO_INCREMENT,
	prescriptions_id int NOT NULL, 	/*Table - prescriptions - ID*/
	source varchar(50) NOT NULL, /*M-Medicine , T-Test*/
	source_id INT NOT NULL,	/*Medicine - Items - ID , Test - lab_analyte_master - ID*/
	order_quantity	INT NULL DEFAULT 0,
	dosage_instruction_condition varchar(500) NOT NULL, /*instruction for how to use it*/
	note text NOT NULL,
	order_status enum('S', 'G', 'C', 'H') default 'S', /*S-Submitted, G-Order Generated, C-Cancelled, H-Hold*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,	/*update when status change*/
	updated_by int,	/*update when status change*/
	PRIMARY KEY (id) USING BTREE
);
