/*
List of screens
1.	Deprecation master aginst assets
2.	Organization assets master
3.	Assets wise maintenance master
4.	Manage machinery Installation, Deprecation, Repair, Maintenance, replace transaction (asset_trans_header & asset_trans_details)
5.	Deprecation calculation
6.	Assets sale/scrap/waste sell
*/


/*Capture assets wise deprecation percentage*/
Create TABLE	deprecation_master
(	id		int NOT NULL AUTO_INCREMENT,
	assets_group	VARCHAR(50)	NOT NULL , /*Group of assets from Lookup-ITEM_GROUP*/
	item_id		int NOT NULL, /*Table-Items_ID*/
	deprecation_percentage	decimal(15, 2) NOT NULL,
	dep_period	int NOT NULL,
	effected_from	DATE	NOT NULL,	/*rate effected from*/
	effected_to		DATE	NULL,	/*rate effected to*/
	last_dep_calcdate	date,
	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;


/*Each and every assets detail for an Hospital against good_receipt_details*/
Create TABLE	organization_assets
(	id		int NOT NULL AUTO_INCREMENT,
	good_receipt_details_id	int NOT NULL, /*Table - good_receipt_details - ID*/
	assets_group	VARCHAR(50)	NOT NULL , /*Group of assets from Lookup-ITEM_GROUP*/
	item_id	int NOT NULL, /*Table-Items.ID*/
	asset_code VARCHAR(50)	NOT NULL ,/*Fetch from item code*/
	asset_srl int NOT NULL ,
	purchase_date	date NOT NULL , /*good_receipt_details - good_receipt_date*/
	asset_purchase_value decimal(15, 2) NOT NULL,
	asset_current_value	decimal(15, 2) NOT NULL,	/*last updated value for an assets for new parts addtion, service and deprecation that change assets value*/
	last_asset_value_update_date	date NULL ,	/*last updated date value for an assets for new parts addtion, service and deprecation that change assets value*/
	last_deprecation_calc_date	date NULL ,
	asset_value_beforelast_dep decimal(15, 2) NULL, /*capture value after last deprecation*/
	asset_value_afterlast_dep decimal(15, 2) NULL, /*capture value after last deprecation*/
	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;


/*Capture assets maintenance information and supplier info*/
drop table if EXISTS organization_asset_maintenance;
Create TABLE	organization_asset_maintenance
(	id		int NOT NULL AUTO_INCREMENT,
	item_id	int NOT NULL, /*Table-Items-ID*/
	supplier_id	int NOT NULL, /*Table-Suppliers-ID*/
	maintenance_period int NULL,
	maintenance_from	date NOT NULL,
	maintenance_to	date NULL,
	maintenance_charge	decimal(15, 2) NOT NULL DEFAULT 0.00, 
	contact_person	varchar(200) NULL,
	contact_number	varchar(50) NULL,
	contact_email	varchar(100) NULL,
	maintenance_note	text NULL,
	maintenance_doc		varchar(200) NULL,
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	updated_at datetime,
	updated_by int,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture assets maintenance information and supplier info with individual assets details*/
drop table if EXISTS organization_asset_maintenance_dtls;
Create TABLE	organization_asset_maintenance_dtls(
	id int NOT NULL AUTO_INCREMENT,
	organization_asset_maintenance_id	int NOT NULL,	/*Table - organization_asset_maintenance - ID*/
	organization_asset_id	int NOT NULL,
	is_active	enum('Y', 'N') NOT NULL DEFAULT 'Y',
	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
);


/*Header table to maintain machinery Installation, Deprecation, Repair, Maintenance, replace TRANSACTION
It capture total price of parts and services*/

Drop table if EXISTS asset_trans_header;
Create TABLE	asset_trans_header
(	id		int NOT NULL AUTO_INCREMENT,
	organization_asset_id int NOT NULL, /*Table-organization_asset-ID*/
	source_item_id	int NULL default 0, /* Table-asset_trans_details-id */
	type_of_trans varchar(50), /*Lookup-ASSETS_TRANS_TYPE - Installation, Depreciation, Repair, Maintenance, Replace, Calibration*/
	organization_asset_maintenance_dtl_id	int NULL,
	maintenance_group_id	int NULL,

	supplier_id	 int not  null, /*Table-Suppliers-ID, record fetch from organization_asset_maintenance*/
	transaction_date	DATETIME NOT NULL,

	parts_price	decimal(15, 2) NOT NULL default 0.00,
	parts_disc_perc decimal(15, 2) NOT NULL default 0.00,
	parts_discount decimal(15, 2) NOT NULL default 0.00,
	parts_sgst	decimal(15, 2) NOT NULL default 0.00,
	parts_cgst	decimal(15, 2) NOT NULL default 0.00,
	net_parts_price	decimal(15, 2) NOT NULL default 0.00,

	service_charge	decimal(15, 2) NOT NULL default 0.00,
	service_disc_perc decimal(15, 2) NOT NULL default 0.00,
	service_discount decimal(15, 2) NOT NULL default 0.00,
	service_sgst	decimal(15, 2) NOT NULL default 0.00,
	service_cgst	decimal(15, 2) NOT NULL default 0.00,
	net_service_charge	decimal(15, 2) NOT NULL default 0.00,

	is_depreciated enum('Y', 'N') NOT NULL DEFAULT 'N',
	deprecation_value	decimal(15, 2) NOT NULL default 0.00,
	deprecation_date	date NULL,

	inc_dec_flag enum('I', 'D') NOT NULL DEFAULT 'I', /*I-Increment and D-Decrement (only for Deprecation) flag*/
	is_parts_price_added	enum('Y', 'N') NOT NULL DEFAULT 'N',
	added_parts_price	decimal(15, 2) NOT NULL default 0.00,

	is_service_price_added	enum('Y', 'N') NOT NULL DEFAULT 'N',
	added_service_price	decimal(15, 2) NOT NULL default 0.00,
	added_total_value	decimal(15, 2) NOT NULL default 0.00,

	trans_note text NULL,
	purchase_order_receipt_id int NULL, /* Purchase Order Receipt generated for Asset Calibration Required Items*/
	due_amount decimal(15,2) NULL default 0.0,  /*Due amount against current event*/
	trans_docs	varchar(200) NULL,
	purchase_order_receipt_id int NULL default 0, /*Table - purchase_order_receipts-ID*/
	status	enum('A', 'U', 'R') NOT NULL, /*A-Approve, U-Unapprove, R-Reject*/

	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Detail table to maintain machinery Installation, Deprecation, Repair, Maintenance, replace transaction
It capture each and every parts and services detail price*/
drop table if EXISTS asset_trans_details;
Create TABLE	asset_trans_details
(	id		int NOT NULL AUTO_INCREMENT,
	asset_trans_header_id int NOT NULL,
	organization_asset_id int NOT NULL, /*Table-organization_asset-ID*/
	item_id	int NULL, /*Table-Items_ID, for asset_trans_type=P, M it is mandatory*/

	asset_trans_type enum('S', 'P', 'M', 'D') NOT NULL, /*S-Service Charge, P-Parts Parts, M-Machine, D-Deprecation*/
	asset_purchase_date	date NULL,

	service_acc_code	VARCHAR(200) NULL, /*Service Name for asset_trans_type=S, D it is mandatory, account head*/	
	
	parts_qty	int NULL default 0,
	gross_charge	decimal(15, 2) NOT NULL default 0.00,
	disc_perc decimal(15, 2) NOT NULL default 0.00,
	discount	decimal(15, 2) NOT NULL default 0.00,
	sgst	decimal(15, 2) NOT NULL default 0.00,
	cgst	decimal(15, 2) NOT NULL default 0.00,
	net_charge	decimal(15, 2) NOT NULL default 0.00,
	purchase_order_item_id int NULL default 0, /*Table - purchase_order_items-ID*/
	trans_note text.
	status	enum('Y', 'N') NOT NULL, /*Y-Active, N-In-Active*/

	created_at datetime NOT NULL,
	created_by int NOT NULL,
	PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB;


/*Capture assets sale/scrap/waste item summery - amount, discount, etc.*/
CREATE TABLE assets_sale_header (
	id int NOT NULL auto_increment,
	bill_no varchar(50)  NOT NULL,	/*Auto generated Bill number*/
	bill_date datetime(0) NULL DEFAULT NULL, /*date of sale or scrap*/
	customer_name	varchar(200) NOT NULL ,
	customer_contact	varchar(50)  NULL ,
	customer_email	varchar(100)  NULL ,
	is_sale_scrap_waste enum('S', 'R', 'W') NOT NULL, /*Type of sale - S-Sale, R-Scrap, W-Waste*/
	gross_amount	decimal(12, 2) not null default 0.00,
	discount_amount decimal(12, 2) not null default 0.00,
	sgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	cgst_amount	decimal(15, 2) NULL DEFAULT 0.00,
	net_amount decimal(15, 2) NULL DEFAULT 0.00,
	note varchar(1000)  NULL DEFAULT NULL,
	status varchar(50)  NOT NULL, /*U - Unapprove, A-Approved, R-Reject*/
	created_at datetime,
	created_by int,
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
) ;


/*Capture sale/scrap/waste item detail agsinst assets_sale_header*/
CREATE TABLE assets_sale_details  (
	id int NOT NULL,
	assets_sale_header_id int NOT NULL, /*Table - assets_sale_header - ID*/
	item_id int NOT NULL, /*Table - items - ID*/
	organization_asset_id int NOT NULL, /*Table - organization_assets- ID*/
	asset_srl	int NOT NULL, /*Fetch from  - organization_assets- asset_srl*/
	tax_group_id int not null, 	/*Table-tax_group-id*/
	sale_quantity decimal(15, 2)  NOT NULL, /*sale quantity against the item*/
	sale_price decimal(15, 2) NOT NULL default 0.00, 
	discount_perc decimal(15, 2) NOT NULL default 0.00,
	discount_amount decimal(15, 2) NOT NULL default 0.00,
	cgst_amount decimal(15, 2) NOT NULL default 0.00,
	sgst_amount	decimal(15, 2) NOT NULL default 0.00,
	net_sale_price decimal(15, 2) NOT NULL default 0.00,/*actual/net sale price*/
	status varchar(50)  NOT NULL, /*Y - OK, N-Delete*/
	updated_at datetime,
	updated_by int,
	primary key (id) using btree
);

