/*
List of screens
--------------------------------------------
1.	Waste bin category (in respect of color)
2.	Waste item mapping with waste category
3.	List of bins mapped with category
4.	Waste collection with bin type and waste items
5.	Dispatch entry screens
6.	Dash Board
*/

DROP TABLE IF EXISTS mm_waste_bin_master;
DROP TABLE IF EXISTS mm_bin_item_tag;
DROP TABLE IF EXISTS trn_waste_collection_master;
DROP TABLE IF EXISTS trn_waste_collection_items;

-- --------------------------------------------------------
-- Table structure for waste_bin_categories - Type of waste bin
-- --------------------------------------------------------
CREATE TABLE waste_bin_categories  (
	id int NOT NULL AUTO_INCREMENT,
	bin_colour varchar(255) NULL DEFAULT NULL,
	bin_name varchar(255) NULL DEFAULT NULL,
	bin_type varchar(255) NULL DEFAULT NULL,
	bin_description text NULL,
	is_active enum('Y','N') NULL DEFAULT 'Y',
	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) 	ENGINE = InnoDB;


-- ---------------------------------------------------------------------------
-- Table structure for waste_item_map - Waste category wise waste item tagging
-- ---------------------------------------------------------------------------
CREATE TABLE waste_item_map  (
	id int NOT NULL AUTO_INCREMENT,
	waste_bin_category_id int NOT NULL, /*Table - waste_bin_categories - ID*/
	item_id int NOT NULL, /*Table - items - ID*/
	is_active enum('Y','N') NULL DEFAULT 'Y',
	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
  PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


-- -----------------------------------------------------------------------------
-- Table structure for mm_bin_item_tag - Waste category wise bin asset mapping
-- -----------------------------------------------------------------------------
CREATE TABLE waste_bins  (
	id int NOT NULL AUTO_INCREMENT,
	waste_bin_category_id	int NOT NULL, /*Table - waste_bin_category - ID*/
	item_id int NOT NULL, /*Table - items - ID*/
	organization_asset_id int NOT NULL, /*Table - organization_assets - ID*/
	waste_bin_description varchar(200) NOT NULL,
	used_department varchar(50) NOT NULL, /*Table - Lookup - HIMS_DEPARTMENT*/
	floor_id	int null, /*Table - blockwise_floor - ID*/
	room_id	int null, /*Table - floorwise_room - ID*/
	is_active enum('Y','N') NULL DEFAULT 'Y',
	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) 	ENGINE = InnoDB;


-- ----------------------------------------------------------------------------------------------------------------
--Table structure for trn_waste_collection_items 
--Waste collection bin and item wise
--waste_collections - Header table to capture total waste
--waste_collection_bins - waste bin wise collections under waste_collection 
--waste_collection_items - item wise waste collection under waste_collection and waste_collection_bins
-- ----------------------------------------------------------------------------------------------------------------
DROP TABLE waste_collections;
CREATE TABLE waste_collections  (
	id int NOT NULL AUTO_INCREMENT,
	waste_collection_date	datetime,

	task_staff_assignment_id int NOT NULL, /*Table-task_staff_assignment-ID*/
	staff_id	int NOT NULL, /*Table - users-ID*/
	taskwise_assignment_tracker_id int NULL, 	/*Table-taskwise_assignment_tracker-ID*/

	status enum('U', 'A', 'R') DEFAULT 'U',
	dispatched_date	datetime,

	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
	
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


DROP TABLE waste_collection_bins;
CREATE TABLE waste_collection_bins  (
	id int NOT NULL AUTO_INCREMENT,
	waste_collection_id	int NOT NULL,	/*Table - waste_collection - ID*/
	waste_bin_id int  NULL,	 /*Table - waste_bins - ID*/
	waste_bin_category_id int NOT NULL, /*Table - waste_bin_categories - ID*/
	waste_collection_department varchar(50) NOT NULL, /*Table - Lookup - HIMS_DEPARTMENT*/
	floor_id	int null, /*Table - blockwise_floor - ID*/
	room_id	int null, /*Table - floorwise_room - ID*/
	waste_qty decimal(10, 2) NOT NULL DEFAULT 0.00,
	waste_uom varchar(50)  NULL DEFAULT NULL,		/*lookup-COMMON_UOM*/

	is_deleted enum('Y', 'N') DEFAULT 'N',
	is_dispatched	enum('Y', 'N') DEFAULT 'N',
	dispatched_date	datetime,

	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
	
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


DROP TABLE waste_collection_items;
CREATE TABLE waste_collection_items  (
	id int NOT NULL AUTO_INCREMENT,
	waste_collection_id	int NOT NULL,	/*Table - waste_collection - ID*/
	waste_collection_bin_id	int NULL, /*Table - waste_collection_bins - ID*/
	item_id decimal(10, 2) NOT NULL DEFAULT 0.00, /*Waste items from items.ID*/
	waste_uom varchar(50)  NOT NULL ,		/*lookup-COMMON_UOM*/
	is_deleted enum('Y', 'N') DEFAULT 'N',

	created_at datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
	created_by int,
	updated_at datetime,
	updated_by int,
	
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;
