/*	List of Screens
1. 	Department wise doc config
2.	Document templete
3.	Document questionnaires
4.	Room and store wise self or rows
5.	Rowwise x and y axis and auto polation of cells to keep books
6	Document creation, movement approval tracker 
7.	Document upload and access
*/

/*Department wise Document admin*/
CREATE TABLE deptwise_document_privilege (
	id int NOT NULL AUTO_INCREMENT,
	department_id	int NOT NULL, /*Table-HRMS-department-id*/
	primery_admin_id	int NOT NULL,	/*Table-HRMS-users-id*/
	secondary_admin_id	VARCHAR(100), /*multiple sub admin id capture against single record-HRMS-users-id*/
	doc_creator_id	VARCHAR(100), /*multiple doc creator id capture against single record-HRMS-users-id*/
	is_active enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Document type wise templete*/
CREATE TABLE document_templates  (
	id int NOT NULL AUTO_INCREMENT,
	document_type	varchar(50) NOT NULL, /*Lookup*/
	doc_template	long text NOT NULL,
	is_active 		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	is_any_questionnaires	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;


/*Document type wise templete*/
CREATE TABLE document_questionnaires  (
	id int NOT NULL AUTO_INCREMENT,
	document_type	varchar(50) NOT NULL, /*Lookup*/
	document_template_id	int NULL, /*Table-document_templates*/
	topic	text NOT NULL,	/*actual question*/
	suggested_answer	text NULL,
	is_active 		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	updated_at 		datetime,
	updated_by 		int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*list of store room rows*/
CREATE TABLE store_rows  (
	id int NOT NULL AUTO_INCREMENT,
	room_id	int NOT NULL, /*Table-floorwise_room-ID*/
	row_code	varchar2(50) NOT NULL, 
	row_name 	varchar2(200)	NULL,
	is_active 		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	updated_at 		datetime,
	updated_by 		int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*list of store room rows for each x(row) and y(col)*/
CREATE TABLE store_rowwise_xy  (
	id int NOT NULL AUTO_INCREMENT,
	room_id	int NOT NULL, /*Table-floorwise_room-ID*/
	store_row_id	int NOT NULL, /*Table-floorwise_room-ID*/
	no_of_x			int NOT NULL default 1, 
	no_of_y			int NOT NULL default 1, 
	is_active 		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	updated_at 		datetime,
	updated_by 		int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*list of store room rows*/
CREATE TABLE store_cells  (
	id int NOT NULL AUTO_INCREMENT,
	room_id	int NOT NULL, /*Table-floorwise_room-ID*/
	store_row_id	int NOT NULL, /*Table-floorwise_room-ID*/
	store_rowwise_xy_id int NOT NULL, /*Table-store_rowwise_xy*/
	cell_code		varchar(100), /*Auto generated code*/
	remarks			text 
	is_active 		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	updated_at 		datetime,
	updated_by 		int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Create a of document Header*/
CREATE TABLE document_create_header  (
	id int NOT NULL AUTO_INCREMENT,
	creator_department_id	int NOT NULL, 	 /*Table-HRMS-department-id*/
	document_type	varchar(50) NOT NULL, /*Lookup*/
	document_template_id	int NULL, /*Table-HRMS-document_templates-id*/
	document_questioner_id	int NULL, /*Table-HRMS-document_questionnaires-id*/
	is_doc_attached		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	creator_note		text NULL,
	final_status			enum('A', 'U', 'R', 'C') NOT NULL DEFAULT 'U', /*A-Approve, U-Unapprove, R-Reject, C-Clarification require*/
	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 a document movement*/
CREATE TABLE document_create_movement  (
	id int NOT NULL AUTO_INCREMENT,
	document_create_header_id	int NOT NULL, 	 /*Table-document_create_header-id*/
	move_datetime	datetime NOT NULL,
	from_department_id int NOT NULL, 	 /*Table-HRMS-department-id*/
	from_user	int NOT NULL, 	 /*Table-HRMS-users-id*/
	to_department_id int NOT NULL, 	 /*Table-HRMS-users-id*/
	to_user		int NOT NULL, 	 /*Table-HRMS-users-id*/
	movement_reson varchar(50) NOT NULL, /*Lookup*/
	before_move_status			enum('A', 'U', 'R', 'C', 'H') NOT NULL DEFAULT 'U', /*A-Approve, U-Unapprove, R-Reject, C-Clarification require, H-Hold*/
	current_status			enum('A', 'U', 'R', 'C', 'H') NULL DEFAULT 'U',
	prev_document_movement int NULL, /*Table-Self Referance-prev_document_movement-id*/
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Create a document Detail*/
CREATE TABLE document_create_detail  (
	id int NOT NULL AUTO_INCREMENT,
	document_create_header_id	int NOT NULL, 	 /*Table-document_create_header-id*/
	doc_text		long	text NOT NULL,
	doc_text_edit		long	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;


/*Create a document - questionnaires */
CREATE TABLE document_create_questionnaires (
	id int NOT NULL AUTO_INCREMENT,
	document_create_header_id	int NOT NULL, 	 /*Table-document_create_header-id*/
	department_id	int NOT NULL, 	 /*Table-HRMS-department-id*/
	qunation		text NOT NULL,
	is_qunation_clear		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	answer			text NULL,
	is_any_ref 		enum('Y', 'N') NOT NULL DEFAULT 'N',
	prev_questioner_id	int NULL, /*Table-Self Referance-document_create_questionnaires-id*/
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	answered_at 		datetime NULL,
	answered_by 		int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Create a document - attachment*/
CREATE TABLE document_attachment (
	id int NOT NULL AUTO_INCREMENT,
	source_of_doc varchar(50) NOT NULL, /*document_create_header, document_upload*/
	document_source_id	int NOT NULL, 	 /*document_create_header-document_create_header-id, 
	document_upload-document_upload-id*/
	document_create_questioner_id	int NULL, 
	page_num	int NOT NULL DEFAULT 0,
	srl_num	int NOT NULL DEFAULT 0,
	department_id	int NOT NULL, 	 /*Table-HRMS-department-id*/
	document_heading	varchar(200) NOT NULL, 	 /*Lookup*/
	document_detail	text NOT NULL, 
	document_location	varchar(200) NOT NULL,
	created_at 		datetime NOT NULL,
	created_by 		int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Create a document upload*/
CREATE TABLE document_upload  (
	id int NOT NULL AUTO_INCREMENT,
	document_create_header_id	int NOT NULL, 	 /*Table-document_create_header-id*/
	creator_department_id	int NOT NULL, 	 /*Table-HRMS-department-id*/
	document_type	varchar(50) NOT NULL, /*Lookup*/
	document_heading	varchar(200) NOT NULL,
	document_detail		int NOT NULL, 
	is_doc_attached		enum('Y', 'N') NOT NULL DEFAULT 'Y',
	is_active	enum('Y', 'N') NOT NULL DEFAULT 'Y',
	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;


/*uploaded document acces by*/
CREATE TABLE document_upload_access  (
	id int NOT NULL AUTO_INCREMENT,
	document_upload_id	int NOT NULL, 	 /*Table-document_upload-id*/
	doc_user_id 	int NOT NULL,/*Table-HRMS-users-id*/
	access_type    enum('R', 'U', 'D')  /*R-Read, U-Update, D-Delete*/
	created_by 		int NOT NULL,
	updated_at 		datetime NULL,
	updated_by 		int NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;
