/*
1.	Visitor Module  - Visitor Card master, Visitor card issue management against IPD patient (visitor_card_issue) & their relatives (visitor_card_issue_details), On Visit maintain the Visit book record - visitor_book)
2.	Package Dispatch & Receive - Package Dispatch & Receive tracking
3.	Call Log - Call log management
4.	Complain Log - Complain Log management against call and direct complain, complain resolution tracking and status
*/


/*Visitor card master*/
Drop table if EXISTS visitor_cards;
create table visitor_cards (
	id int not null auto_increment,
	card_type	varchar(50) not null, /*IPD Patient relatieves - Lookup - VISITOR_CARD_TYPE*/
	card_no varchar(100) not null,
	card_access_dept	varchar(500) NOT null, /*IPD Patient relatieves - Lookup - HIMS_DEPARTMENT*/
	card_access_floors varchar(500) null , /*List of floors - Table - blockwise_floor - ID*/
	card_access_rooms varchar(500) null , /*List of rooms - floorwise_room - ID*/
	card_detail varchar(500) null,
	valid_from date null,
	valid_to date null,
	issued_by int null,	/*update when issued*/
	issued_on date null,
	document	varchar(200) null , 
	is_active enum('Y', 'N') default 'N',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Visitor card issue */
create table visitor_card_issue (
	id int not null auto_increment,
	visitor_card_id int	not null,	/*Table-visitor_cards-ID*/
	ipd_admission_id int null,	/*Table-ipd_admission-ID*/	/*For IPD visitor (VC_TYPE_PATIENT) admission_id  & patient_id should not be blank*/
	patient_id int  null,/*Table-patients-ID*/
	staff_id int null, /*For NON IPD visitor (VC_TYPE_PATIENT) staff_id should not be blank*/
	visit_purpose varchar(500) null,
	no_of_allowed_person int not null	default 1,
	issued_on datetime not null,
	cancelled_on datetime  null,
	status enum('I', 'C') default 'I', /*I-Issue, C-Cencel*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Visitor card issue detail*/
create table visitor_card_issue_details (
	id int not null auto_increment,
	visitor_card_issue_id int not null, /*Table-visitor_card_issue-ID*/
	added_on datetime not null,

	relative_id int null default 0,	/*Table-next_ofkin-ID*/
	card_issued_to	varchar(100) null, /*For Not IPD visitor (VC_TYPE_PATIENT) card_issued_to  should not be blank*/
	contact_number	varchar(100) null,
	relation varchar(50) null,

	is_delete enum('Y', 'N') default 'N', /*Y-Yes, N-No*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Visitor book detail*/
create table visitor_book (
	id int not null auto_increment,
	visitor_card_id int	not null,	/*Table-visitor_cards-ID*/
	visitor_card_issue_detail_id int not null, /*Table-visitor_card_issue_detail-ID*/
	visited_on date not null,	
	purpose varchar(50) not null, /*lookup-VISIT_PURPOSE*/
	in_time datetime not null,
	out_time datetime null,
	no_of_person_visited int null,
	note varchar(255),
	is_delete enum('Y', 'N') default 'N', /*Y-Yes, N-No*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Package dispatch receive details*/
create table package_dispatch_receive_details (
	id int not null auto_increment,
	reference_no varchar(255) not null,
	dispatch_receive_flag enum('D', 'R') not null, /*D-dispatch, R-receive*/
	dispatch_receive_date date not null,
	expected_date	date null,
	actual_reach_date	datetime null,

	package_conatct_person varchar(200) not null,
	package_care_of varchar(200)  null,
	package_address varchar(300) null,
	package_pin int null,

	courier_by varchar(255) null,
	courier_trans_id varchar(255) null,
	courier_contact varchar(500) null,
	courier_address varchar(500) null,

	tracking_id varchar(500),
	tracking_url varchar(500),

	note text,
	document varchar(200),
	is_delete enum('Y', 'N') not null,	/*Y-yes, N-no*/
	primary key (id)
);


/*Call log history*/
create table call_logs (
	id int not null auto_increment,
	is_patient_related enum('Y', 'N') default 'Y',
	patient_id int null,
	calling_relation varchar(50) null,	/*lookup-TELE_CALL_REASON*/
	call_reason varchar(50) not null,
	caller_name varchar(50) not null,
	call_purpose text not null,
	calling_on datetime  null,
	calling_duration int null,
	is_action_require enum('Y', 'N') default 'Y',
	is_complain_register enum('Y', 'N') default 'N',
	action_type varchar(50) null, /*lookup*/
	action_source varchar(50) null,
	action_source_id int null,
	is_deleted enum('Y', 'N') default 'N',
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Complain Log*/
create table complain_logs (
	id int not null auto_increment,
	reference_no varchar(100) not null,
	complain_date datetime not null,
	complain_type varchar(255) not null, /*lookup*/
	complain_register_from enum('portal', 'application') not null, /*portal, application*/
	complain_source enum('patient', 'relative', 'staff', 'others') not null,	/*patient, relative, staff, others*/
	complain_by int  null, /*Who generate that complain - patients table ID, next_ofkin-ID, users-ID*/
	complain_dept int null, /*department-ID */ 
	complain_against int null, 
	call_log_id int null, /*Only for call log related complain*/
	cantact_no varchar(20) not null,
	email varchar(100) not null,
	complain_title varchar(200) not null,
	complain_description text  null,
	complain_doc varchar(200) not null,
	is_deleted enum('Y', 'N') default 'N',
	resolution_status enum('S', 'N', 'W', 'R') default 'W', /*S-Solved, N-Not Solved, W-Working In Progress, R-Reopen an old  complain*/
	previous_complain_id int not null, /*Table-complain_logs-ID*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);


/*Complain Log status*/
create table complain_log_status (
	id int not null auto_increment,	
	complain_log_id int not null, 	/*Table-complain_logs-ID*/
	action_date datetime not null,
	action_taken varchar(255) not null,	/*lookup-list of action taken*/
	complain_attendby varchar(255) not null, /*Table-users-ID*/
	complain_handle_dept int null, /*department-ID */ 
	complain_handleby varchar(255) not null,	/*Table-users-ID*/
	is_complain_trf_dept enum('Y', 'N'),
	status enum('C', 'W', 'H', 'N', 'R') not null, /*C-Complain Taken, W-Working on, H-Hold, N-Not Resolved, R-Resolved*/
	complain_log_title varchar(200) not null,
	complain_log_note text  null,
	document_loc varchar(300) not null,
	prev_complain_log_status_id int null,
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);
