CREATE TABLE ohrm_asset
(
    id                  BIGINT AUTO_INCREMENT,
    asset_code          VARCHAR(50) UNIQUE,
    asset_name          VARCHAR(255),
    status              smallint(6) NOT NULL 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),
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    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(255),
    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 DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_asset_picture
(
    asset_id            BIGINT,
    asset_picture       LONGBLOB,
    asset_filename      VARCHAR(100),
    asset_type          VARCHAR(50),
    asset_small_picture blob,
    asset_big_picture   blob,
    PRIMARY KEY (asset_id)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_asset_status`
(
    `id`   smallint(6) NOT NULL,
    `name` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_brand
(
    id         BIGINT AUTO_INCREMENT,
    brand_code VARCHAR(25) UNIQUE,
    brand_name VARCHAR(110) UNIQUE,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    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),
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    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),
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    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;

ALTER TABLE ohrm_asset
    ADD FOREIGN KEY (`status`) references `ohrm_asset_status` (`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_asset_status`
values ('0', 'In-stock'),
       ('1', 'In-use'),
       ('2', 'Decommissioned'),
       ('3', 'Stolen');

INSERT INTO `ohrm_data_group` (`id`, `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES (@max_id, 'asset_information', 'HR Administration - Asset Details', 1, 1, 1, 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('user_asset_information', 'User Asset Details', 1, 1, 1, 1);

SET
@user_asset_info_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='user_asset_information');
SET
@asset_info_datagroup_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='asset_information');

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id)
VALUES (1, @max_id);

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, @asset_info_datagroup_id, 1, 1, 1, 1, 0),
       (@ess_role_id, @user_asset_info_datagroup_id, 1, NULL, NULL, NULL, 0),
       (@supervisor_role_id, @asset_info_datagroup_id, 1, NULL, NULL, 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`, `route_name`)
VALUES ('View Assets', @module_id, 'viewAssets', 'asset_viewAssets');
SET
@view_asssets_screen_id := (SELECT LAST_INSERT_ID());

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

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

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

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`)
VALUES ('change asset status', @module_id, 'changeAssetStatus');
SET
@change_asset_status_screen_id := (SELECT LAST_INSERT_ID());

SET
@people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND `level` = 1);
SET
@hr_administration_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'HR Administration'
                                                                            AND `level` = 2 AND parent_id = @people_management_pillar_menu_id);
SET @emplopyee_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management'
                                                                            AND `level` = 2 AND parent_id = @people_management_pillar_menu_id);


INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`)
VALUES ('Assets', @user_asset_screen_id, @emplopyee_module_menu_id, 3, 1117, '', 'vueadmin.asset.viewUserAssets' , 1);

SET
@asset_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Assets' AND level = 3);

-- Vue.js Screens
INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`, `status`)
VALUES ('Brands', @brands_screen_id, @asset_menu_id, 4, 200, 'vueadmin.asset.viewBrands', 1),
       ('Categories', @categories_screen_id, @asset_menu_id, 4, 300, 'vueadmin.asset.viewCategories', 1),
       ('Vendors', @vendors_screen_id, @asset_menu_id, 4, 400, 'vueadmin.asset.viewVendors', 1),
       ('View Assets', @view_asssets_screen_id, @asset_menu_id, 4, 100, 'vueadmin.asset.viewAssets', 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),
       (@admin_role_id, @change_asset_status_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),
       (@supervisor_role_id, @change_asset_status_screen_id, 1, 0, 0, 0),
       (@ess_role_id, @change_asset_status_screen_id, 1, 0, 0, 0),
       (@ess_role_id, @user_asset_screen_id, 1, 0, 0, 0);

INSERT INTO ohrm_data_group_screen (`data_group_id`, `screen_id`, `permission`)
VALUES (@asset_info_datagroup_id, @view_asssets_screen_id, 1);

INSERT INTO ohrm_data_group_screen (`data_group_id`, `screen_id`, `permission`)
VALUES (@user_asset_info_datagroup_id, @user_asset_screen_id, 1);

INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`)
VALUES (3, @user_asset_screen_id);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`)
VALUES ('asset_brands', 'HR Administration - Asset Brands', 1, 1, 1, 1),
       ('asset_categories', 'HR Administration - Asset Categories', 1, 1, 1, 1),
       ('asset_vendor', 'HR Administration - Asset Vendor', 1, 1, 1, 1);


SET
@asset_brands := (SELECT `id` FROM  `ohrm_data_group` WHERE  `name` = 'asset_brands');
SET
@asset_categories := (SELECT `id` FROM  `ohrm_data_group` WHERE  `name` = 'asset_categories');
SET
@asset_vendor := (SELECT `id` FROM  `ohrm_data_group` WHERE  `name` = 'asset_vendor');


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');


SET
@brands_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` ='viewBrands');
SET
@categories_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` ='viewCategories');
SET
@vendors_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` ='viewVendorList');
SET
@asset_screen_id := (SELECT `id` FROM  `ohrm_screen` WHERE  `action_url` ='viewAssets');


INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`)
VALUES (@asset_brands, @brands_screen_id, 1),
       (@asset_categories, @categories_screen_id, 1),
       (@asset_vendor, @vendors_screen_id, 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, @asset_brands, 1, 1, 1, 1, 0),
       (@admin_role_id, @asset_categories, 1, 1, 1, 1, 0),
       (@admin_role_id, @asset_vendor, 1, 1, 1, 1, 0);

INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id)
VALUES (1, @asset_brands),
       (1, @asset_categories),
       (1, @asset_vendor);

INSERT INTO ohrm_data_list (`name`, `meta_data`)
VALUES ('Asset',
        '{"mandatoryFields":["assetCode","assetName"],"availableFields":{"assetCode":"Asset Id","categoryName":"Category","location":"Location","assetName":"Serial Number","model":"Model","status":"Status","assignedEmployeeId":"Assigned Employee Id","empFullName":"Assigned Employee","fromDate":"Assigned On","warrantyEndDate":"Warranty Ends"},"defaultFields":["categoryName","model","status","empFullName","fromDate","warrantyEndDate"],"listFields":{"categoryName":"categoryName","model":"model","status":"status","empFullName":"empFullName","fromDate":"fromDate","warrantyEndDate":"warrantyEndDate"}}');
