/*Shifts wise staff assignment*/
CREATE TABLE `shift_assignments`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `shift_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `is_emergency` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT 'N',
  `shift_date` date NOT NULL,
  `department_id` int NULL DEFAULT NULL,
  `staff_id` int NOT NULL,
  `is_active` enum('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT 'Y',
  `created_at` datetime(0) NOT NULL,
  `created_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `updated_at` datetime(0) NULL DEFAULT NULL,
  `updated_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 56 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;



/*Daily task for Hospital staff*/
CREATE TABLE daily_tasks  (
	id int NOT NULL AUTO_INCREMENT,
	task_name varchar(100) NOT NULL,	/*Name of Task*/
	task_desc longtext NULL,
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	is_system enum('Y', 'N') NOT NULL DEFAULT 'N',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Daily sub task for Hospital staff*/
CREATE TABLE daily_subtasks  (
	id int NOT NULL AUTO_INCREMENT,
	daily_task_id int NOT NULL,	/*Table - daily_tasks -ID */
	subtask_name varchar(255)  NOT NULL,	/*Name of Sub Task*/
	subtask_desc longtext  NULL,
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Daily sub task mapping with Vital capture*/
CREATE TABLE daily_subtask_vital_map  (
	id int NOT NULL AUTO_INCREMENT,
	is_task_or_subtask enum('S', 'T') NOT NULL DEFAULT 'S', /*Map with T-Task or S-Subtask*/
	daily_subtask_id	int NOT NULL,	/*Table - daily_subtasks -ID */
	vital_id varchar(255)  NOT NULL,	/*Table - vitals -ID */
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Daily sub task assignment to staff that mapped with shift_assignments, floorwise_room and bed*/
CREATE TABLE task_staff_assignments  (
	id int NOT NULL AUTO_INCREMENT,
	is_task_or_subtask enum('S', 'T') NOT NULL DEFAULT 'S', /*Map with T-Task or S-Subtask*/
	daily_subtask_id	int NOT NULL,	/*Table - daily_subtasks -ID */	
	room_id	int NOT NULL,				/*Table - floorwise_room - ID*/
	bed_id int  NULL DEFAULT 0,	/*Table - bed - ID*/
	shift_assignment_id int NOT NULL,		/*Table - shift_assignments - ID*/
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture task wise admin detail*/
CREATE TABLE taskwsie_department_admin(
	id int NOT NULL AUTO_INCREMENT,
	department_id int NOT NULL, /*HRMS department table ID*/
	is_primary_admin enum('Y', 'N') NOT NULL DEFAULT 'N',
	staff_id int NOT NULL, /*Table-users-ID, Employee master ID*/
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Daily sub task assignment (task_staff_assignments) Comments and remarks*/
/*Task approve, reject, complain mark capture by only department admins*/
CREATE TABLE taskwise_assignment_tracker  (
	id int NOT NULL AUTO_INCREMENT,
	task_staff_assignment_id	int NOT NULL, /*Table - task_staff_assignments -ID*/
	room_id	int NOT NULL,				/*Table - floorwise_room - ID*/
	bed_id int  NULL DEFAULT 0,	/*Table - bed - ID*/
	doer_id		int NOT NULL, /*Table - users -ID, Employee ID*/
	is_task_completed enum('N', 'C', 'I') NOT NULL DEFAULT 'N', /*N-Not Completed, C-Completed, I-InProgress*/
	task_complication_time	datetime NOT NULL, /*Input by user*/
	task_complication_mark_time	datetime NOT NULL,	/*System date and time*/
	task_doer_note text NULL,
	approver_id		int NULL, /*Table - users -ID, Manager of the employee / Department task admin*/
	is_task_accepted enum('A', 'R', 'N', 'X') NOT NULL DEFAULT 'N',	/*N-Acepted, R-Rejected, N-Not Checked by reviewer, X-Not valid */
	task_accepted_time datetime NULL,	/*System date and time*/
	task_approver_note text NULL,
	ref_task_tracker_id int NULL, /*If their is any reference of previous task - taskwise_assignment_tracker - ID*/
	is_any_complain int NULL,
	complain_id int NULL,	/*Once complain feedback module developed we will link it*/
	created_at datetime(0) NOT NULL,
	created_by varchar(50) NOT NULL,
	updated_at datetime(0) NULL,
	updated_by varchar(50) NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


