/*Type of OT as per Specialties*/
CREATE TABLE ot_name  (
	id int NOT NULL AUTO_INCREMENT,
	ot_speciality_type	VARCHAR(50), /*Lookup - OT_SPCL_TYPE - types of Occupational Therapy Specialties*/
	ot_name	VARCHAR(200) NOT NULL, /*Name of the particular OT*/
	ot_desc text,
	service_item_id int NULL, /*Table service_items-ID*/
	service_rate_id int NULL,	/*Table service_rates-ID*/
	is_active enum('Y', 'N'),
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*OT booking details - room, time, IPD/OPD case etc*/
CREATE TABLE ot_bookings  (
	id int NOT NULL AUTO_INCREMENT,
	ot_main_consultant INT NULL,
	ref_opd_ipd_flag	enum('O','I', 'E') NOT NULL, /*O-OPD, I-IPD, E-Emergency*/
	ref_opd_ipd_id int NOT NULL, /*IPD-ipd_admission-ID, OPD-opd_doctor_appointments-ID*/
	ipd_case_id int NOT NULL, /*Table case_detail-ID, OT aganst the case*/
	ipd_visit_id int NOT NULL, /*Table visit_details-ID, OT aganst the visit*/
	is_tpa_approved enum('Y','N', 'NA') NOT NULL DEFAULT 'NA',
	room_id	int NOT NULL, /*Table-floorwise_room - ID*/
	ot_booking_date	date NOT NULL, /*CURRENT_DATE*/
	ot_date	date NOT NULL, /*OT Date choose by operator*/
	ot_name_id	int NOT NULL, /*Table - ot_name - ID*/
	planed_start_dt	datetime NOT NULL, /*OT Planed date and time */
	planed_end_dt	datetime NOT NULL,
	planed_duration int NOT NULL,
	actual_start_dt	datetime NULL,	/*Actual OT date and time */
	actual_end_dt	datetime NULL,
	actual_duration int NOT NULL,
	ot_note text NULL,
	status	enum ('P', 'N', 'C', 'S') DEFAULT 'P', /*Planned -P, S-Started, C-Completed, N-Canceled*/
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*doctor / assistant details for a booked OT*/
CREATE TABLE ot_staff_details  (
	id int NOT NULL AUTO_INCREMENT,
	ot_booking_id	int not null, /*ot_bookings - ID */
	staff_role	varchar(50) not null, /*staff_designation - ID, link users table with designation_id*/
	staff_id	int not null,	/*Table - users -ID*/ 
	remarks	text null,
	is_deleted	enum('Y', 'N'), 
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;

