CREATE  TABLE IF NOT EXISTS `ohrm_training_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) DEFAULT NULL ,
  `version` VARCHAR(45) DEFAULT NULL ,
  `company` VARCHAR(255) DEFAULT NULL ,
  `subunit_id` int(11) DEFAULT NULL ,
  `coordinator_id` int(11) DEFAULT NULL ,
  `status` int(11) DEFAULT NULL ,
  `duration` time DEFAULT NULL ,
  `cost` VARCHAR(255) DEFAULT NULL ,
  `currency_id` char(3) DEFAULT NULL ,
  `description` text DEFAULT NULL ,
  `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `archived_time` TIMESTAMP NULL DEFAULT NULL,
  `archived_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_student` (
  `emp_number` int(11) NOT NULL ,
  `course_id` int(11) NOT NULL ,
  PRIMARY KEY (`emp_number`, `course_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  `company` VARCHAR(255)  DEFAULT NULL ,
  `cost` VARCHAR(255) DEFAULT NULL ,
  `status` int(11) DEFAULT NULL,
  `code` VARCHAR(255) DEFAULT NULL,
  `currency_id` char(3) DEFAULT NULL ,
  `description` text DEFAULT NULL ,
  `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `archived_time` TIMESTAMP NULL DEFAULT NULL,
  `archived_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_course_package` (
  `course_id` int(11) NOT NULL ,
  `package_id` int(11) NOT NULL ,
  PRIMARY KEY (`course_id`, `package_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_student` (
  `emp_number` int(11) NOT NULL ,
  `package_id` int(11) NOT NULL ,
  `due_date` DATE DEFAULT NULL ,
  `status` int(11) NOT NULL ,
  PRIMARY KEY (`emp_number`, `package_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_package_student_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  `course_id` int(11) DEFAULT NULL ,
  `scheduled_date` DATE DEFAULT NULL ,
  `due_date` DATE DEFAULT NULL ,
  `delivery_method` int(11) DEFAULT NULL ,
  `description` TEXT DEFAULT NULL ,
  `status` int(11) DEFAULT NULL ,
  `duration` VARCHAR(255) DEFAULT NULL ,
  `delivery_location` VARCHAR(255) DEFAULT NULL ,
  `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_user_id` int(11) DEFAULT NULL,
  `updated_time` TIMESTAMP NULL DEFAULT NULL,
  `updated_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_status` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_delivery_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer` (
  `emp_number` int(11) NOT NULL ,
  `session_id` int(11) NOT NULL ,
  `attendance` int(1),
  `attend_date` DATE DEFAULT NULL ,
  `rating` int(11) DEFAULT NULL ,
  `comment` TEXT DEFAULT NULL ,
  PRIMARY KEY (`emp_number`, `session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer_rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_trainer_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student` (
  `emp_number` int(11) NOT NULL ,
  `session_id` int(11) NOT NULL ,
  `attendance` int(1),
  `attend_date` DATE DEFAULT NULL ,
  `rating` int(11) DEFAULT NULL ,
  `score` INT DEFAULT NULL ,
  `comment` TEXT DEFAULT NULL ,
  PRIMARY KEY (`emp_number`, `session_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student_rating` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_session_student_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE  TABLE IF NOT EXISTS `ohrm_training_report` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `ohrm_course_attachment` (
    `course_id` INT,
    `attach_id` BIGINT,
    `attach_size` INT DEFAULT '0',
    `attach_desc` VARCHAR(200),
    `attach_filename` VARCHAR(255),
    `attach_attachment` LONGBLOB,
    `attach_type` VARCHAR(200),
    `screen` VARCHAR(100),
    `attached_by` INT,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(course_id,attach_id)
  ) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `ohrm_session_attachment` (
    `session_id` INT,
    `attach_id` BIGINT,
    `attach_size` INT DEFAULT '0',
    `attach_desc` VARCHAR(200),
    `attach_filename` VARCHAR(255),
    `attach_attachment` LONGBLOB,
    `attach_type` VARCHAR(200),
    `screen` VARCHAR(100),
    `attached_by` INT,
    `attached_by_name` VARCHAR(200),
    `attached_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(session_id,attach_id)
  ) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

ALTER TABLE `ohrm_training_session_student`
  ADD CONSTRAINT `ohrm_training_session_student_session` FOREIGN KEY (`session_id`) REFERENCES `ohrm_training_session` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_rating` FOREIGN KEY (`rating`) REFERENCES `ohrm_training_session_student_rating` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_attendance` FOREIGN KEY (`attendance`) REFERENCES `ohrm_training_session_student_attendance` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_student_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_session_trainer`
  ADD CONSTRAINT `ohrm_training_session_trainer_session` FOREIGN KEY (`session_id`) REFERENCES `ohrm_training_session` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_rating` FOREIGN KEY (`rating`) REFERENCES `ohrm_training_session_trainer_rating` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_attendance` FOREIGN KEY (`attendance`) REFERENCES `ohrm_training_session_trainer_attendance` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_trainer_employee` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_session`
  ADD CONSTRAINT `ohrm_training_session_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_session_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_delivery_method` FOREIGN KEY (`delivery_method`) REFERENCES `ohrm_training_session_delivery_method` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_course` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_session_updated_user` FOREIGN KEY (`updated_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_course`
  ADD CONSTRAINT `ohrm_training_course_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_course_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_coordinator` FOREIGN KEY (`coordinator_id`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_subunit` FOREIGN KEY (`subunit_id`) REFERENCES `ohrm_subunit` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_currency` FOREIGN KEY (`currency_id`) REFERENCES `hs_hr_currency_type` (`currency_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_archived_user` FOREIGN KEY (`archived_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_package`
  ADD CONSTRAINT `ohrm_training_package_created_user` FOREIGN KEY (`created_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_archived_user` FOREIGN KEY (`archived_user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_course_student`
  ADD CONSTRAINT `ohrm_training_course_student_emp_number` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_student_course_id` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_course_package`
  ADD CONSTRAINT `ohrm_training_course_package_course_id` FOREIGN KEY (`course_id`) REFERENCES `ohrm_training_course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_course_package_package` FOREIGN KEY (`package_id`) REFERENCES `ohrm_training_package` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `ohrm_training_package_student`
  ADD CONSTRAINT `ohrm_training_package_student_course_id` FOREIGN KEY (`emp_number`) REFERENCES `hs_hr_employee` (`emp_number`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_student_package` FOREIGN KEY (`package_id`) REFERENCES `ohrm_training_package` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `ohrm_training_package_student_status` FOREIGN KEY (`status`) REFERENCES `ohrm_training_package_student_status` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Insert Data

INSERT INTO `ohrm_user_role` (`name` ,`display_name` ,`is_assignable` ,`is_predefined`)
VALUES ('CourseCoordinator', 'CourseCoordinator', '0', '1'),
('SessionTrainer', 'SessionTrainer', '0', '1'),
('SessionStudent', 'SessionStudent', '0', '1');

INSERT INTO `ohrm_training_course_status` (`id`, `name`) VALUES
(1, 'Active'),
(2, 'Archived');

INSERT INTO `ohrm_training_package_status` (`id`, `name`) VALUES
(1, 'Active'),
(2, 'Archived');

INSERT INTO `ohrm_training_package_student_status` (`id`, `name`) VALUES
(1, 'Pending'),
(2, 'Pass'),
(3, 'Fail');

INSERT INTO `ohrm_training_session_trainer_rating` (`id`, `name`) VALUES
(1, 'Poor'),
(2, 'Fair'),
(3, 'Good'),
(4, 'Very Good'),
(5, 'Excellent');

INSERT INTO `ohrm_training_session_trainer_attendance` (`id`, `name`) VALUES
(1, 'Completed');

INSERT INTO `ohrm_training_session_student_rating` (`id`, `name`) VALUES
(1, 'Poor'),
(2, 'Fair'),
(3, 'Good'),
(4, 'Very Good'),
(5, 'Excellent');

INSERT INTO `ohrm_training_session_student_attendance` (`id`, `name`) VALUES
(1, 'Participated');

INSERT INTO `ohrm_training_session_status` (`id`, `name`) VALUES
(1, 'Pending Approval'),
(2, 'Scheduled'),
(3, 'Completed'),
(4, 'Cancelled');

INSERT INTO `ohrm_training_session_delivery_method` (`id`, `name`) VALUES
(1, 'Classroom'),
(2, 'WebEx'),
(3, 'Self-study');

INSERT INTO `ohrm_training_report` (`id`, `name`) VALUES
(20, 'Student Session Information'),
(21, 'Trainer Session Information');

SET @admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'Admin');
SET @coordinator_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'CourseCoordinator');
SET @trainer_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'SessionTrainer');
SET @session_student_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'SessionStudent');
SET @ess_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ESS');
SET @config_admin_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'ConfigAdmin');

INSERT INTO `ohrm_module` (`name`, `status`) VALUES ('training', 1);
SET @training_module_id := (SELECT `id` FROM ohrm_module WHERE `name` = 'training');

-- Insert Screens

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('ViewCourseList', @training_module_id, 'viewCourseList', 'training_viewCourseList');
SET @view_course_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddCourse', @training_module_id, 'addCourse');
SET @add_course_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineCoursePackage', @training_module_id, 'defineCoursePackage');
SET @define_course_package_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('Packages', @training_module_id, 'viewPackageList', 'training_viewPackageList');
SET @view_packages_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddPackage', @training_module_id, 'addPackage');
SET @add_package_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('ViewSessionList', @training_module_id, 'viewSessionList', 'training_viewSessionList');
SET @view_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('AddSession', @training_module_id, 'addSession');
SET @add_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineSessionTrainer', @training_module_id, 'defineSessionTrainer');
SET @define_session_trainer_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('DefineSessionStudent', @training_module_id, 'defineSessionStudent');
SET @define_session_student_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('ViewTrainerSessionList', @training_module_id, 'viewTrainerSessionList', 'training_viewTrainerSessionList');
SET @view_trainer_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditTrainerSession', @training_module_id, 'editTrainerSession');
SET @edit_trainer_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('ViewMyTrainingSessionList', @training_module_id, 'viewMyTrainingSessionList', 'training_viewMyTrainingSessionList');
SET @view_my_training_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditStudentSession', @training_module_id, 'editStudentSession');
SET @edit_student_session_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewDefinedReportList', @training_module_id, 'viewDefinedReportList');
SET @view_defined_report_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewTrainingReport', @training_module_id, 'viewTrainingReport');
SET @view_training_report_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('ViewPackageSessionList', @training_module_id, 'viewPackageSessionList');
SET @view_package_session_list_screen_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('EditPackageSessionList', @training_module_id, 'editPackageSessionList');
SET @edit_package_session_list_screen_id := (SELECT LAST_INSERT_ID());


INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`, `route_name`) VALUES
('DefaultTrainingModule', @training_module_id, 'defaultTrainingModulePage', 'training_defaultTrainingModulePage');
SET @default_training_modeul_page := (SELECT LAST_INSERT_ID());

-- Insert Menu items
SET @culture_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Culture' AND parent_id IS NULL AND level = 1);

INSERT INTO ohrm_menu_item (`menu_title`, `parent_id`, `level`, `order_hint`, `status`, `image_name`)
VALUES ('Training', @culture_pillar_menu_id, 2, 20, 1, 'training.svg');

SET @training_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Training' AND parent_id = @culture_pillar_menu_id AND level = 2);

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
    ('Learning Courses', NULL, @training_menu_id, 3, 100, '', NULL, 1);
SET @learning_courses_menu_id := (SELECT LAST_INSERT_ID());

INSERT INTO ohrm_menu_item (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `url_extras`, `uisref`, `status`) VALUES
     ('Programs', @view_packages_screen_id, @learning_courses_menu_id, 4, 100, '', NULL, 0),
     ('Courses', @view_course_screen_id, @learning_courses_menu_id, 4, 200, '', NULL, 1),
     ('Sessions', @view_session_screen_id, @learning_courses_menu_id, 4, 300, '', NULL, 1),
     ('My Participating Sessions', @view_my_training_session_list_screen_id, @learning_courses_menu_id, 4, 400, '', NULL, 1),
     ('My Conducting Sessions', @view_trainer_session_list_screen_id, @learning_courses_menu_id, 4, 500, '', NULL, 1);


-- Insert User Role base access to screens

INSERT INTO ohrm_user_role_screen (user_role_id, screen_id, can_read, can_create, can_update, can_delete) VALUES
(@admin_role_id, @view_course_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_course_screen_id, 1, 1, 1, 1),
-- (@admin_role_id, @define_course_package_screen_id, 1, 1, 1, 1),
-- (@admin_role_id, @view_packages_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_package_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @view_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @add_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @add_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_session_trainer_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @define_session_trainer_screen_id, 1, 1, 1, 1),
(@admin_role_id, @define_session_student_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @define_session_student_screen_id, 1, 1, 1, 1),
(@trainer_role_id, @view_trainer_session_list_screen_id, 1, 1, 1, 1),
(@trainer_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @edit_trainer_session_screen_id, 1, 1, 1, 1),
(@session_student_role_id, @view_my_training_session_list_screen_id, 1, 1, 1, 1),
(@ess_role_id, @view_my_training_session_list_screen_id, 1, 1, 1, 1),
(@session_student_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@coordinator_role_id, @edit_student_session_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_defined_report_list_screen_id, 1, 1, 1, 1),
(@admin_role_id, @view_training_report_screen_id, 1, 1, 1, 1),
-- (@admin_role_id, @view_package_session_list_screen_id, 1, 1, 1, 1),
-- (@admin_role_id, @edit_package_session_list_screen_id, 1, 1, 1, 1),
(@admin_role_id, @default_training_modeul_page, 1, 1, 1, 1),
(@coordinator_role_id, @default_training_modeul_page, 1, 1, 1, 1),
(@trainer_role_id, @default_training_modeul_page, 1, 1, 1, 1),
(@session_student_role_id, @default_training_modeul_page, 1, 1, 1, 1),
(@ess_role_id, @default_training_modeul_page, 1, 1, 1, 1),
(@config_admin_role_id, @default_training_modeul_page, 1, 1, 1, 1);

-- Insert Data Groups

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_course_list', 'Learning Courses - Courses', 1, 1, 1, 1);
SET @manage_course_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_session_list', 'Learning Courses - Sessions', 1, 1, 1, 1);
SET @manage_session_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('edit_session_attachments', 'Learning Courses - Edit Session Attachments', 1, 1, 1, 1);
SET @edit_session_attachments_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_trainer_session_list', 'Learning Courses - My Conducting Sessions', 1, NULL, 1, NULL);
SET @manage_trainer_session_list_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_my_training_session_list', 'Learning Courses - My Participating Sessions', 1, NULL, 1, NULL);
SET @manage_my_training_session_list_data_group_id := (SELECT LAST_INSERT_ID());

-- INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
-- ('edit_package_details', 'Advanced Training - Training Program', 1, 1, 1, 1);
-- SET @edit_package_details_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('manage_training_report_details', 'Learning Courses - Manage Report Details', 1, NULL, NULL, NULL);
SET @manage_training_report_details_data_group_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('edit_course_attachment', 'Learning Courses - Edit Course Attachment', 1, 1, 1, 1);
SET @edit_course_attachment_data_group_id := (SELECT LAST_INSERT_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, @manage_course_list_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_course_list_data_group_id, 1, 1, 1, 0, 0),
(@admin_role_id, @manage_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_session_list_data_group_id, 1, 1, 1, 1, 0),
(@trainer_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@session_student_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
(@ess_role_id, @manage_my_training_session_list_data_group_id, 1, 0, 0, 0, 0),
(@trainer_role_id, @edit_session_attachments_data_group_id, 1, 1, 1, 0, 0),
(@session_student_role_id, @edit_session_attachments_data_group_id, 1, 0, 0, 0, 0),
(@admin_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_trainer_session_list_data_group_id, 1, 1, 1, 1, 0),
(@coordinator_role_id, @manage_my_training_session_list_data_group_id, 1, 1, 1, 1, 0),
-- (@admin_role_id, @edit_package_details_data_group_id, 1, 1, 1, 1, 0),
(@admin_role_id, @manage_training_report_details_data_group_id, 1, 0, 0, 0, 0),
(@admin_role_id, @edit_course_attachment_data_group_id, 1, 1, 1, 1, 0);


INSERT INTO `ohrm_data_group_screen` (`data_group_id`, `screen_id`, `permission`) VALUES
(@manage_course_list_data_group_id, @view_course_screen_id, 1),
(@manage_course_list_data_group_id, @view_course_screen_id, 2),
(@manage_course_list_data_group_id, @view_course_screen_id, 3),
(@manage_course_list_data_group_id, @view_course_screen_id, 4),
(@manage_course_list_data_group_id, @add_course_screen_id, 1),
(@manage_course_list_data_group_id, @add_course_screen_id, 2),
(@manage_course_list_data_group_id, @add_course_screen_id, 3),
-- (@manage_course_list_data_group_id, @define_course_package_screen_id, 1),
-- (@manage_course_list_data_group_id, @define_course_package_screen_id, 2),
-- (@manage_course_list_data_group_id, @define_course_package_screen_id, 3),
(@manage_session_list_data_group_id, @view_session_screen_id, 1),
(@manage_session_list_data_group_id, @view_session_screen_id, 2),
(@manage_session_list_data_group_id, @view_session_screen_id, 3),
(@manage_session_list_data_group_id, @view_session_screen_id, 4),
(@manage_session_list_data_group_id, @add_session_screen_id, 1),
(@manage_session_list_data_group_id, @add_session_screen_id, 2),
(@manage_session_list_data_group_id, @add_session_screen_id, 3),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 1),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 2),
(@manage_session_list_data_group_id, @define_session_trainer_screen_id, 3),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 1),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 2),
(@manage_session_list_data_group_id, @define_session_student_screen_id, 3),
-- (@edit_package_details_data_group_id, @view_packages_screen_id, 1),
-- (@edit_package_details_data_group_id, @view_packages_screen_id, 2),
-- (@edit_package_details_data_group_id, @view_packages_screen_id, 3),
-- (@edit_package_details_data_group_id, @view_packages_screen_id, 4),
-- (@edit_package_details_data_group_id, @add_package_screen_id, 1),
-- (@edit_package_details_data_group_id, @add_package_screen_id, 2),
-- (@edit_package_details_data_group_id, @add_package_screen_id, 3),
-- (@edit_package_details_data_group_id, @view_package_session_list_screen_id, 1),
-- (@edit_package_details_data_group_id, @view_package_session_list_screen_id, 2),
-- (@edit_package_details_data_group_id, @view_package_session_list_screen_id, 3),
-- (@edit_package_details_data_group_id, @view_package_session_list_screen_id, 4),
-- (@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 1),
-- (@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 2),
-- (@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 3),
-- (@edit_package_details_data_group_id, @edit_package_session_list_screen_id, 4),
(@manage_trainer_session_list_data_group_id, @view_trainer_session_list_screen_id, 1),
(@manage_trainer_session_list_data_group_id, @view_trainer_session_list_screen_id, 3),
(@manage_trainer_session_list_data_group_id, @edit_trainer_session_screen_id, 1),
(@manage_trainer_session_list_data_group_id, @edit_trainer_session_screen_id, 3),
(@manage_my_training_session_list_data_group_id, @view_my_training_session_list_screen_id, 1),
(@manage_my_training_session_list_data_group_id, @view_my_training_session_list_screen_id, 3),
(@manage_my_training_session_list_data_group_id, @edit_student_session_screen_id, 1),
(@manage_my_training_session_list_data_group_id, @edit_student_session_screen_id, 3),
(@manage_training_report_details_data_group_id, @view_training_report_screen_id, 1),
(@manage_my_training_session_list_data_group_id, @default_training_modeul_page, 1);


SET @manage_course_list_data_group_id = (SELECT `id` FROM ohrm_data_group WHERE `name` = 'manage_course_list');
INSERT INTO ohrm_user_role_type_data_group(user_role_type_id, data_group_id) VALUES
(1, @manage_course_list_data_group_id),
(1, @edit_course_attachment_data_group_id),
(1, @edit_session_attachments_data_group_id),
(1, @manage_training_report_details_data_group_id),
-- (1, @edit_package_details_data_group_id),
(1, @manage_session_list_data_group_id),
(1,@manage_my_training_session_list_data_group_id);
-- (1, @manage_trainer_session_list_data_group_id);

INSERT INTO ohrm_module_default_page (`module_id`, `user_role_id`, `action`, `enable_class`, `priority`) VALUES
(@training_module_id, @admin_role_id, 'training/viewSessionList', NULL, 100),
(@training_module_id, @coordinator_role_id, 'training/viewSessionList', NULL, 100),
(@training_module_id, @trainer_role_id, 'training/viewTrainerSessionList', NULL, 100),
(@training_module_id, @session_student_role_id, 'training/viewMyTrainingSessionList', NULL, 100),
(@training_module_id, @ess_role_id, 'training/viewMyTrainingSessionList', NULL, 10);

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('training.program.enabled', '0');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('training.enableGeneralCourses', '1');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('training.enableOnlineCourses', '1');
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('training.enableAdminCannotEditParticipatingCourses', '0');

UPDATE ohrm_advanced_report SET enabled = 0 WHERE unique_name IN ('Student_Session_Information_Package', 'Trainer_Session_Information_Package');

ALTER TABLE `ohrm_training_course` MODIFY `cost` DOUBLE;


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Student Assign Reminder Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Student Assign Reminder Notification Template', @notification_id, @notification_media_id, 'en_US', "#studentFirstName #studentLastName Assigned to #sessionName.", '<p>Dear #studentFirstName #studentLastName,<br /><br />You Have been Assigned to #sessionName session of #sessionCourse course as a participant.<br /><br />Start Date - #scheduledDate.<br />End Date - #dueDate.<br /><br />Thank you.<br />This is an automated notification.</p>');


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Student Session Complete Reminder Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Student Session Complete Reminder Notification Template', @notification_id, @notification_media_id, 'en_US', "#studentFirstName #studentLastName Completed #sessionName.", '<p>Dear #studentFirstName #studentLastName,<br /><br />You Have Completed #sessionName session of #sessionCourse course as a Participant held on #scheduledDate.<br /><br />Please login into the system and complete the session by signing off.<br /><br />Thank you.<br />This is an automated notification.</p>');


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Trainer Assign Reminder Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Trainer Assign Reminder Notification Template', @notification_id, @notification_media_id, 'en_US', "#triainerFirstName #trainerLastName Assigned as a trainer for #sessionName.", '<p>Dear #triainerFirstName #trainerLastName,<br /><br />You Have been Assigned to #sessionName session of #sessionCourse course as a Trainer.<br /><br />Start Date - #scheduledDate.<br />End Date - #dueDate.<br /><br />Thank you.<br />This is an automated notification.</p>');


SET @notification_media_id := (SELECT `id` FROM `ohrm_notification_media` WHERE `name` = 'Email');

SET @notification_type_id := (SELECT `id` FROM `ohrm_notification_type` WHERE `name` = 'Scheduled');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'training');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Trainer Session Complete Reminder Notification', 1, @notification_type_id, 'DummyEmailProcessor', @module_id, 1);

SET @notification_id := LAST_INSERT_ID();

INSERT INTO `ohrm_notification_supported_media` (`notification_id`, `media_id`)
VALUES (@notification_id, @notification_media_id);

INSERT INTO `ohrm_notification_subscriber_subscription_template` (`name`,`notification_id`,`media_id`,`locale`,`subject`,`body`)
VALUES ('Trainer Session Complete Reminder Notification Template', @notification_id, @notification_media_id, 'en_US', "#trainerFirstName #trainerLastName Completed #sessionName.", '<p>Dear #trainerFirstName #trainerLastName,<br /><br />You Have Completed #sessionName session of #sessionCourse course as a Trainer held on #scheduledDate.<br /><br />Please login into the system and complete the session by signing off.<br /><br />Thank you.<br />This is an automated notification.</p>');

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 @training_menu_id := (SELECT id
                          FROM ohrm_menu_item
                          WHERE menu_title = 'Training'
                            and level = 2);
INSERT INTO ohrm_report_folder (`name`, `added_user`, `display_order`)
VALUES ('Training', @system_user_id, 7);
SET @training_folder_id := (SELECT id
                            FROM ohrm_report_folder
                            WHERE name = 'Training');
INSERT INTO ohrm_folder_menu_item (`folder_id`, `menu_item_id`)
VALUES (@training_folder_id, @training_menu_id);

SET @student_session_information_report := (SELECT id
                                            FROM ohrm_advanced_report
                                            WHERE unique_name = 'Student_Session_Information');
SET @trainer_session_information_report := (SELECT id
                                            FROM ohrm_advanced_report
                                            WHERE unique_name = 'Trainer_Session_Information');
SET @student_session_information_package_report := (SELECT id
                                                    FROM ohrm_advanced_report
                                                    WHERE unique_name = 'Student_Session_Information_Package');
SET @trainer_session_information_package_report := (SELECT id
                                                    FROM ohrm_advanced_report
                                                    WHERE unique_name = 'Trainer_Session_Information_Package');

UPDATE `ohrm_advanced_report`
SET `folder_id`= @training_folder_id
WHERE `id` IN (@student_session_information_report,
               @trainer_session_information_report,
               @student_session_information_package_report,
               @trainer_session_information_package_report);

UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Trainer_Session_Information", "filters": null})'
WHERE `id` = @trainer_session_information_report;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Student_Session_Information", "filters": null})'
WHERE `id` = @student_session_information_report;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Student_Session_Information_Package", "filters": null})'
WHERE `id` = @student_session_information_package_report;
UPDATE `ohrm_advanced_report`
SET `custom_url`= 'report_view({"id": "Trainer_Session_Information_Package", "filters": null})'
WHERE `id` = @trainer_session_information_package_report;

UPDATE `ohrm_module` SET data_group_prefix = 'Learning Courses' WHERE `name` = 'training';


