CREATE TABLE ohrm_asset (id BIGINT AUTO_INCREMENT, asset_code VARCHAR(50) UNIQUE, asset_name VARCHAR(255), status BIGINT DEFAULT 0, is_deleted TINYINT DEFAULT '0', description text, category_id BIGINT, brand_id BIGINT, model TEXT, vendor_id BIGINT, location_id INT, warranty_start_date DATE, warranty_end_date DATE, acquired_date DATE, INDEX category_id_idx (category_id), INDEX brand_id_idx (brand_id), INDEX vendor_id_idx (vendor_id),INDEX location_id_idx (location_id), PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_asset_assignment (id BIGINT AUTO_INCREMENT, emp_number INT, asset_id BIGINT, from_date DATE, to_date DATE, updated_by INT, comments text, is_history TINYINT DEFAULT '0', created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX asset_id_idx (asset_id), PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_asset_attachment (asset_id BIGINT, asset_attach_id BIGINT, asset_attach_size INT DEFAULT '0', asset_attach_desc VARCHAR(200), asset_attach_filename VARCHAR(100), asset_attach_attachment LONGBLOB, asset_attach_type VARCHAR(200), attached_by INT, attached_by_name VARCHAR(200), attached_time DATETIME, PRIMARY KEY(asset_id, asset_attach_id)) ENGINE = INNODB;

CREATE TABLE ohrm_asset_picture (asset_id BIGINT, asset_picture LONGBLOB, asset_filename VARCHAR(100), asset_type VARCHAR(50), asset_file_size VARCHAR(20), asset_file_width VARCHAR(20), asset_file_height VARCHAR(20), PRIMARY KEY(asset_id)) ENGINE = INNODB;

CREATE TABLE ohrm_brand (id BIGINT AUTO_INCREMENT, brand_code VARCHAR(25) UNIQUE, brand_name VARCHAR(110) UNIQUE, PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_category (id BIGINT AUTO_INCREMENT, parent_id BIGINT, category_code VARCHAR(25) UNIQUE, category_name VARCHAR(110) UNIQUE, INDEX parent_id_idx (parent_id), PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;

CREATE TABLE ohrm_vendor (id BIGINT AUTO_INCREMENT, vendor_code VARCHAR(50) UNIQUE, vendor_name VARCHAR(255), address VARCHAR(255), is_deleted TINYINT DEFAULT '0', contact_no VARCHAR(255), email VARCHAR(50), website VARCHAR(255), PRIMARY KEY(id)) ENGINE = INNODB default charset=utf8;


ALTER TABLE ohrm_asset ADD CONSTRAINT fk_ohrm_asset_vendor_id_ohrm_vendor_id FOREIGN KEY (vendor_id) REFERENCES ohrm_vendor(id) ON DELETE SET NULL;

ALTER TABLE ohrm_asset ADD CONSTRAINT fk_ohrm_asset_category_id_ohrm_category_id FOREIGN KEY (category_id) REFERENCES ohrm_category(id) ON DELETE SET NULL;

ALTER TABLE ohrm_asset ADD CONSTRAINT fk_ohrm_asset_brand_id_ohrm_brand_id FOREIGN KEY (brand_id) REFERENCES ohrm_brand(id) ON DELETE SET NULL;

ALTER TABLE ohrm_asset ADD CONSTRAINT fk_ohrm_asset_location_id_ohrm_location_id FOREIGN KEY (location_id) REFERENCES ohrm_location(id) ON DELETE SET NULL;

ALTER TABLE ohrm_asset_attachment ADD CONSTRAINT fk_ohrm_asset_attachment_asset_id_ohrm_asset_id FOREIGN KEY (asset_id) REFERENCES ohrm_asset(id) ON DELETE CASCADE;

ALTER TABLE ohrm_category ADD CONSTRAINT fk_ohrm_category_parent_id_ohrm_category_id FOREIGN KEY (parent_id) REFERENCES ohrm_category(id);

ALTER TABLE ohrm_asset_assignment ADD CONSTRAINT fk_ohrm_asset_assignment_emp_number_hs_hr_employee_emp_number FOREIGN KEY (emp_number) REFERENCES hs_hr_employee (emp_number) ON DELETE CASCADE;

ALTER TABLE ohrm_asset_picture ADD CONSTRAINT fk_ohrm_asset_picture_asset_id_ohrm_asset_id FOREIGN KEY (asset_id) REFERENCES ohrm_asset(id) ON DELETE CASCADE;


 SET @max_id := (SELECT CASE WHEN MAX(id) IS NULL THEN 0 ELSE 1 + MAX(CAST(id AS UNSIGNED)) END AS max_id FROM `ohrm_data_group`);
 SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin'); 
 SET @supervisor_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Supervisor');  
 SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');  

INSERT INTO `ohrm_data_group` (`id`, `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
(@max_id, 'asset_information', 'Asset Details', 1, 1, 1, 1),
(@max_id+1, 'asset_assignment', 'Asset Assignment Details', 1, 1, 1, 1);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`) VALUES
(@admin_role_id, @max_id, 1, 1, 1, 1, 0),
(@ess_role_id, @max_id, 1, NULL, NULL, NULL, 0),
(@supervisor_role_id, @max_id, 1, NULL, NULL, NULL, 0),
(@admin_role_id, @max_id+1, 1, 1, 1, 1, 0),
(@ess_role_id, @max_id+1, 1, NULL, NULL, NULL, 0),
(@supervisor_role_id, @max_id+1, 1, NULL, 1, NULL, 0);


-- menu items

 
      
INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('asset', 1);  
      
SET @module_id := (SELECT LAST_INSERT_ID());  
      
INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('View Assets', @module_id, 'viewAssets');  
SET @view_asssets_screen_id := (SELECT LAST_INSERT_ID());  

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Brands', @module_id, 'viewBrands');  
SET @brands_screen_id := (SELECT LAST_INSERT_ID());  

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Categories', @module_id, 'viewCategories');  
SET @categories_screen_id := (SELECT LAST_INSERT_ID());  

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('Vendors', @module_id, 'viewVendorList');  
SET @vendors_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES  
('User Assets', @module_id, 'viewUserAssetList');  
SET @user_asset_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('Assets', @user_asset_screen_id, NULL, 1, 1117, '', 1); 
      
SET @asset_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Assets' AND `level` = 1);   
      
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `status`) VALUES  
('View Assets', @view_asssets_screen_id, @asset_menu_id, 2, 100, '/reset/1', 1),
('Brands', @brands_screen_id, @asset_menu_id, 2, 200, '/reset/1', 1),
('Categories', @categories_screen_id, @asset_menu_id, 2, 300, '/reset/1', 1),  
('Vendors', @vendors_screen_id, @asset_menu_id, 2, 400, '', 1);
      
INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES  
(@admin_role_id, @user_asset_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_asssets_screen_id, 1, 1, 1, 1),
(@admin_role_id, @brands_screen_id, 1, 1, 1, 1),
(@admin_role_id, @categories_screen_id, 1, 1, 1, 1),
(@admin_role_id, @vendors_screen_id, 1, 1, 1, 1),
(@supervisor_role_id, @view_asssets_screen_id, 1, 0, 0, 0),
(@supervisor_role_id, @user_asset_screen_id, 1, 0, 0, 0),
(@ess_role_id, @user_asset_screen_id, 1, 0, 0, 0);