CREATE TABLE ohrm_report_folder (
    id int(11) not null auto_increment,
    name VARCHAR(100) NOT NULL,
    `predefined` tinyint(1) DEFAULT '1',
    `added_user` int(13) default null,
    `display_order` int(11) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id),
    CONSTRAINT `fk_report_folder_created_by` FOREIGN KEY (`added_user`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL
) ENGINE = INNODB  default charset=utf8;

CREATE TABLE ohrm_folder_menu_item (
    id int(11) not null auto_increment,
    folder_id int(11) ,
    menu_item_id int(11) default null,
    module_id int(11) default null,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(id,folder_id),
    CONSTRAINT `fk_ohrm_folder_menu_item_menu` FOREIGN KEY (`menu_item_id`) REFERENCES `ohrm_menu_item`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_folder_menu_item_folder` FOREIGN KEY (`folder_id`) REFERENCES `ohrm_report_folder`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB;
create table `ohrm_advanced_report` (
  `id` int(10) not null auto_increment,
  `name` varchar(100) not null,
  `unique_name` varchar(150) not null UNIQUE,
  `definition` longtext not null,
  `enabled` tinyint(1) DEFAULT '1',
  `icon` VARCHAR(200) DEFAULT NULL,
  `folder_id` int default null,
  `parent_report_id` int(10) default null,
  `custom_url` varchar(300) default null,
  `created_by` int(10) default null,
  `schedulable` tinyint(1) DEFAULT '1',
  `is_private` tinyint(1) DEFAULT '0',
  `created_at` DATETIME ,
  `updated_at` DATETIME ,
  primary key (`id`),
  CONSTRAINT `fk_advanced_report_created_by` FOREIGN KEY (`created_by`) REFERENCES `ohrm_user` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_advanced_report_folder_item` FOREIGN KEY (`folder_id`) REFERENCES `ohrm_report_folder` (`id`) ON DELETE SET NULL
) engine=innodb default charset=utf8;

alter table `ohrm_data_list_settings` ADD CONSTRAINT `fk_ohrm_data_list_settings_ohrm_advanced_report`
    foreign key (`report_id`) references `ohrm_advanced_report`(`id`) on delete cascade;

create table IF NOT EXISTS `ohrm_report_configuration` (
  `report_id` int(10) not null,
  `configuration` text not null,
  primary key (`report_id`),
  FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) engine=innodb default charset=utf8;

INSERT INTO `ohrm_module` (`name`, `status`) VALUES
('report', 1);

SET @report_module_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('View Reports', @report_module_id, 'viewReport');

SET @report_screen_id := (SELECT LAST_INSERT_ID());

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @report_screen_id, 1, 1, 1, 1);

INSERT INTO `ohrm_config` VALUES ('csv_fill_empty_rows', '1');

CREATE TABLE ohrm_report_export_history (
    id BIGINT AUTO_INCREMENT,
    report_id INT NOT NULL,
    file_name MEDIUMTEXT NOT NULL,
    user_id INT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT `fk_report_export_history_advanced_report` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_report_export_history_advanced_user` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user`(`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;


CREATE TABLE  `ohrm_reports_catalog` (
`id` INT NOT NULL AUTO_INCREMENT ,
`report_id` INT NOT NULL ,
`data_group_id` INT NOT NULL ,
`active` TINYINT(1) NOT NULL DEFAULT '1' ,
PRIMARY KEY (`id`),
 CONSTRAINT `fk_ohrm_reports_catalog_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_ohrm_reports_catalog_data_group_id` FOREIGN KEY (`data_group_id`) REFERENCES `ohrm_data_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_report_accessible_role_type (
    `id` INT NOT NULL AUTO_INCREMENT ,
    `name` varchar(100) not null,
    `default_selected` tinyint(1) DEFAULT '0',
    `read_only` tinyint(1) DEFAULT '0',
    PRIMARY KEY(id)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

INSERT INTO ohrm_report_accessible_role_type (`id`, `name`, `default_selected`, `read_only`)
VALUES (1, 'Admin', 1, 1),
       (2, 'Supervisor', 0, 0),
       (3, 'Head of Department', 0, 0),
       (4, 'ESS', 0, 0),
       (5, 'Hiring Manager', 0, 0),
       (6, 'Stage Coordinators/ Interviewers/ Test Coordinators', 0, 0);

CREATE TABLE ohrm_report_share (
    report_id INT NOT NULL,
    role_type_id INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(report_id, role_type_id),
    CONSTRAINT `fk_report_share_report_id` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_report_share_role_type_id` FOREIGN KEY (`role_type_id`) REFERENCES `ohrm_report_accessible_role_type`(`id`) ON DELETE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'admin');
SET @global_admin_id := (SELECT id FROM `ohrm_user_role` WHERE `name` = 'Admin');

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Reports Catalog', @module_id , 'reportsCatalog');

SET @view_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(1, @view_report_screen_id, 1, 1, 1, 1);
INSERT INTO `ohrm_default_permitted_screen`(`user_role_type_id`, `screen_id`) VALUES
(1, @view_report_screen_id);

CREATE TABLE ohrm_report_definition_master_data (
  `id`                   INT(4) AUTO_INCREMENT,
  `module`               VARCHAR(200) DEFAULT NULL,
  `prefix`               VARCHAR(200) DEFAULT NULL,
  `template_name`        VARCHAR(200) DEFAULT NULL,
  `data_group`           VARCHAR(200) DEFAULT NULL,
  `display_label`        VARCHAR(200) DEFAULT NULL,
  `icon`                 VARCHAR(200) DEFAULT NULL,
  `chart_configurable` TINYINT(1) DEFAULT NULL,
  `default_folder_id` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_report_definition_master_data_folder` FOREIGN KEY (`default_folder_id`) REFERENCES `ohrm_report_folder`(`id`) ON DELETE SET NULL
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;

CREATE TABLE ohrm_report_definition_accessible_role_type (
   `definition_id`       INT(4) NOT NULL,
   `role_type_id`         INT NOT NULL,
   PRIMARY KEY (definition_id, role_type_id),
   CONSTRAINT `fk_ohrm_report_definition_accessible_role_type_master_data` FOREIGN KEY (`definition_id`) REFERENCES `ohrm_report_definition_master_data`(`id`) ON DELETE CASCADE,
   CONSTRAINT `fk_ohrm_report_definition_accessible_role_type_role_type` FOREIGN KEY (`role_type_id`) REFERENCES `ohrm_report_accessible_role_type`(`id`) ON DELETE CASCADE
)
  DEFAULT CHARACTER SET UTF8
  ENGINE = INNODB;


SET @system_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` = 'SYSTEM');
SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_role_id` = @system_user_role_id);

SET @employee_management_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Employee Management' and level = 2);
SET @travel_and_expense_menu_id := (SELECT id FROM ohrm_menu_item WHERE menu_title = 'Travel and Expense' and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Employee Management', @system_user_id, 2);
SET @employee_management_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Employee Management');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@employee_management_folder_id, @employee_management_menu_id );

INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`) VALUES
    ('Travel and Expense', @system_user_id, 9);
SET @travel_and_expense_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Travel and Expense');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@travel_and_expense_folder_id, @travel_and_expense_menu_id );
INSERT INTO ohrm_report_definition_master_data
    (module, prefix, template_name, data_group, display_label, icon, chart_configurable, default_folder_id) VALUES
                                                                                                              ('pim', 'pim', 'PIM_Report_template', 'pim_reports', 'Employee Informational Report', NULL, 1, @employee_management_folder_id),
                                                                                                              ('expense', 'expense', 'EXPENSE_Report_template', 'expense_reports', 'Travel and Expense Detailed Report', 'document-blue.svg', 0, @travel_and_expense_folder_id),
                                                                                                              ('expense', 'expense_summary', 'Expense_summary_report_template', 'expense_reports', 'Travel and Expense Summary Report', 'document-yellow.svg', 0, @travel_and_expense_folder_id);

SET @pim_template :=
    (SELECT id FROM ohrm_report_definition_master_data WHERE template_name = 'PIM_Report_template');
SET @expense_template :=
    (SELECT id FROM ohrm_report_definition_master_data WHERE template_name = 'EXPENSE_Report_template');
SET @expense_summary_template :=
    (SELECT id FROM ohrm_report_definition_master_data WHERE template_name = 'Expense_summary_report_template');
SET @admin_accessible_report_type_id :=
    (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Admin');
SET @supervisor_accessible_report_type_id :=
    (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Supervisor');
SET @hod_accessible_report_type_id :=
    (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'Head of Department');
SET @ess_accessible_report_type_id :=
    (SELECT id FROM `ohrm_report_accessible_role_type` WHERE `name` = 'ESS');


INSERT INTO ohrm_report_definition_accessible_role_type
(definition_id, role_type_id) VALUES
                              (@pim_template, @admin_accessible_report_type_id),
                              (@pim_template, @supervisor_accessible_report_type_id),
                              (@pim_template, @hod_accessible_report_type_id),
                              (@pim_template, @ess_accessible_report_type_id),
                              (@expense_template, @admin_accessible_report_type_id),
                              (@expense_template, @supervisor_accessible_report_type_id),
                              (@expense_template, @hod_accessible_report_type_id),
                              (@expense_template, @ess_accessible_report_type_id),
                              (@expense_summary_template, @admin_accessible_report_type_id),
                              (@expense_summary_template, @supervisor_accessible_report_type_id),
                              (@expense_summary_template, @hod_accessible_report_type_id),
                              (@expense_summary_template, @ess_accessible_report_type_id);
