
CREATE  TABLE `ohrm_secauth_reset_password_log` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `reset_email` VARCHAR(60) NOT NULL,
  `reset_request_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `reset_code` VARCHAR(200) NOT NULL,
  `status` TINYINT NULL,
  `type` ENUM('default', 'new_user') NULL,
  PRIMARY KEY(`id`))
ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE  TABLE `ohrm_secauth_secondary_password` (
  `user_id` INT NOT NULL,
  `secondary_password` VARCHAR(255) NULL,
  PRIMARY KEY (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`)
    ON UPDATE CASCADE
    ON DELETE CASCADE
)
ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_remember_me_token` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `selector` VARCHAR(88) NOT NULL,
    `hashed_token` VARCHAR(88) NOT NULL,
    `user_id` INT NOT NULL,
    `expires` datetime NOT NULL,
    `initial_session_hash` VARCHAR (255) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_ohrm_remember_me_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX ndx_ohrm_remember_me_selector (selector)
)
ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ohrm_password_verification_failure_attempts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) NOT NULL,
    `no_of_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `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_ohrm_password_verification_failure_attempts_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX ndx_ohrm_password_verification_failure_attempts_user (user_id)
)
ENGINE = InnoDB DEFAULT CHARSET=UTF8;

-- adding menu items

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

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

INSERT INTO ohrm_screen (`name`, `module_id`, `action_url`) VALUES
('Authentication Configuration', @module_id, 'securityAuthenticationConfigure');

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

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

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


INSERT INTO `ohrm_data_group` ( `name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
('security_authentication_configuration', 'HR Administration - Security Authentication Configuration', 1, 0, 0, 0);

SET @security_configuration_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, @security_configuration_data_group_id, 1, 0, 0, 0, 0),
(@config_admin_role_id, @security_configuration_data_group_id, 1, 0, 1, 0, 0);

SET @view_admin_module_id := (SELECT `id` FROM `ohrm_screen` WHERE `module_id` =2 AND `action_url` = 'viewAdminModule');
SET @configure_authentication_screen_id := (SELECT `id` from ohrm_screen where `module_id` = 2 AND `action_url` = 'authenticationConfiguration');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`) VALUES
(@security_configuration_data_group_id, @authentication_configuration_screen_id, 1),
(@security_configuration_data_group_id, @view_admin_module_id, 1),
(@security_configuration_data_group_id, @configure_authentication_screen_id, 1);

INSERT INTO `ohrm_user_role_type_data_group`(user_role_type_id, data_group_id) VALUES
(@admin_role_id, @security_configuration_data_group_id);

# Reset Password Email Notification

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` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'securityAuthentication');
SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PASSWORD' AND `action` = 'Reset');

INSERT INTO `ohrm_notification` (`id`, `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES (NULL, 'Password Reset Notification', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"ResetPasswordEmailProcessor", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', 'PASSWORD', 'Reset', @module_id, '0');
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 ('Password Reset Notification Template', @notification_id, @notification_media_id, 'en_US', 'OrangeHRM Password Reset - %timeStamp%', "<p>Hi %recipientFirstName% %recipientLastName%,<br /><br />A %resetOption% password reset has been requested for your OrangeHRM \"%userName%\" user account.<br /><br />To reset the %resetOption% password please click %passwordResetLink%.<br /><br />Alternately, you may click %passwordResetCodeLink% and enter the following password reset code:<br />%code%<br /><br />Please note that this request is only valid for %validityPeriod%.<br /><br />If you didn't request this change please ignore this email.<br /><br /><br />Regards,<br />OrangeHRM System</p>");
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`)
VALUES (NULL, @notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id_affected_employee := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id_affected_employee, @notification_template_id);

# Candidate Reset Password Email Notification

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` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'securityAuthentication');
SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PASSWORD' AND `action` = 'Reset');

# Below notification will not be used in the system till we provide a login to the Candidate Users
# So the notification will be added as a hidden one for now. Have to do necessary changes to the notification at the time of enabling it.
INSERT INTO `ohrm_notification` ( `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Candidate Password Reset Notification', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', 'PASSWORD', 'Reset', @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 ('Candidate Password Reset Notification Template', @notification_id, @notification_media_id, 'en_US', 'Your New %CompanyName% Candidate Portal Password',
        'Hi %CandidateFirstName%,</br></br>
<p>You recently requested to reset your password for the %CompanyName% candidate portal.</p>
<p><strong>Your new password is:</strong> %NewPassword%</p>
<p>Please use this password to log in to the candidate portal.</p>
<p>If you have any questions or need further assistance, please contact our Recruitment team.</p></br>
<p>Regards,</p>
<p>Recruitment Team at %CompanyName%</br>');
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`notification_id`, `notify_affected_employee`, `workflow_item_id`, `hidden`)
VALUES (@notification_id, '0', @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"prevent_delete":true}', 'Custom', 'Affected Candidate');
SET @notification_subscriber_id_affected_employee := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id_affected_employee, @notification_template_id);

# Set Password on First Login Notification

SET @workflow_state_machine_id := (SELECT `id` FROM `ohrm_workflow_state_machine` WHERE `workflow` = 'PASSWORD' AND `action` = 'Set');

INSERT INTO `ohrm_notification` (`id`, `name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES (NULL, 'Set Password on First Login Notification', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"ResetPasswordEmailProcessor", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', 'PASSWORD', 'Set', @module_id, '0');
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 ('Set Password on First Login Notification Template', @notification_id, @notification_media_id, 'en_US', 'Your OrangeHRM account has been created', "<p>Hi %recipientFirstName% %recipientLastName%,<br /><br />Your OrangeHRM \"%userName%\" user account has been created successfully.<br /><br />To set a password on the first login please click %passwordResetLink%.<br /><br />Alternately, you may click %passwordResetCodeLink% and enter the following code:<br />%code%<br /><br />Please note that this request is only valid for %validityPeriod%.<br /><br /><br />Regards,<br />OrangeHRM System</p>");
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `workflow_item_id`, `hidden`)
VALUES (NULL, @notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, @workflow_state_machine_id, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id_affected_employee := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id_affected_employee, @notification_template_id);

# Access Blocked Email Notification

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` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'securityAuthentication');

INSERT INTO `ohrm_notification` (`name`, `is_enable`,`type_id`, `generator_class`, `module_id`, `hidden`)
VALUES ('Access Blocked Notification', 1, @notification_type_id, NULL, @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 ('Access Blocked Notification Template', @notification_id, @notification_media_id, 'en_US', 'Access to your account has been temporarily restricted', '<p>Hi {firstName} {lastName},<br /><br />Access to your account from IP Address {IPAddress} has been temporarily restricted as a result of too many failed login attempts.<br /><br />The restriction will automatically end in {timeAsString}.<br /><br />Thank you.<br />This is an automated notification.</p>');

create table `ohrm_mfa_method`(
      `id` INT(11) not null AUTO_INCREMENT,
      `name` VARCHAR(255),
      `identifier` VARCHAR(255) not null unique,
      `processor` VARCHAR(255),
      `enabled` boolean not null default true,
      primary key (`id`)
) engine=innodb default charset=utf8;

create table `ohrm_user_registered_mfa_method`(
  `id` INT(11) not null AUTO_INCREMENT,
  `configuration` longtext,
  `user_id` INT(11) not null,
  `method_id` INT(11) not null,
  `verified` boolean not null default false,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  primary key (`id`),
  CONSTRAINT `fk_ohrm_system_user_mfa_method_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ohrm_system_user_mfa_method_method_id` FOREIGN KEY (`method_id`) REFERENCES `ohrm_mfa_method` (`id`) ON DELETE CASCADE,
  INDEX ohrm_system_user_mfa_method_idx_user_id (user_id),
  INDEX ohrm_system_user_mfa_method_idx_method_id (method_id),
  CONSTRAINT ohrm_user_registered_mfa_method_user_id_method_id UNIQUE (user_id,method_id)
) engine=innodb default charset=utf8;

create table `ohrm_user_mfa_setting`
(
    `id`              INT(11) NOT NULL AUTO_INCREMENT,
    `user_id`         INT(11) NOT NULL,
    `method_id`       INT(11) NOT NULL,
    `is_acknowledged` BOOLEAN NOT NULL DEFAULT FALSE,
    `acknowledged_at` TIMESTAMP NULL DEFAULT NULL,
    primary key (`id`),
    CONSTRAINT `fk_ohrm_user_mfa_setting_user_id` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_ohrm_user_mfa_setting_method_id` FOREIGN KEY (`method_id`) REFERENCES `ohrm_mfa_method` (`id`) ON DELETE CASCADE,
    INDEX             ohrm_user_mfa_setting_idx_user_id (user_id),
    INDEX             ohrm_user_mfa_setting_idx_method_id (method_id),
    CONSTRAINT ohrm_user_mfa_setting_user_id_method_id UNIQUE (user_id, method_id)
) engine=innodb default charset=utf8;


INSERT INTO `ohrm_mfa_method` (`name`,`identifier`,`processor`,`enabled`)VALUES
 ('Totp', 'totp', 'TotpAuthenticator', 1),
 ('Backup Code', 'backupcode', 'BackupCodeAuthenticator', 1);

-- MFA Reset Notification

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` = 'Action Based');
SET @module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'securityAuthentication');

INSERT INTO `ohrm_notification` (`name`, `is_enable`, `type_id`, `generator_class`, `parameters`, `workflow_id`, `workflow_action`, `module_id`, `hidden`)
VALUES ('Reset Two-Factor Authentication', '1', @notification_type_id, 'LegacyEmailProcessorWrapperEventNotification', '{"email_processor":"ResetTwoFactorEmailProcessor", "list_action_rights":{"change_status":false,"change_subscribers":false,"copy_templates":false}}', null, null, @module_id, '0');
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 ('Two-Factor Authentication reset notification sent to subscriber', @notification_id, @notification_media_id, 'en_US', 'OrangeHRM Two-Factor Authentication Reset - %timeStamp%', "<p>Hi %recipientFirstName% %recipientLastName%,<br /><br /><p>Two-factor authentication on your OrangeHRM account has been reset.<br /></p><p>You will be asked to set up two-factor authentication the next time you log into your account.</p><br /><p>This is an auto-generated email.</p>");
SET @notification_template_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscription` (`id`, `notification_id`, `parameters`, `affected_emp_number`, `notify_affected_employee`, `notify_before_value`, `notify_before_unit`, `schedule_dom`, `schedule_dow`, `schedule_lastrun`, `hidden`)
VALUES (NULL, @notification_id, NULL, NULL, '0', NULL, NULL, NULL, NULL, NULL, '0');
SET @notification_subscription_id := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber` (media_id, parameters, type, name) VALUES
    (@notification_media_id, '{"prevent_delete":true,"restrict_employees":true}', 'Custom', 'Affected Employee');
SET @notification_subscriber_id_affected_employee := (SELECT LAST_INSERT_ID());

INSERT INTO `ohrm_notification_subscriber_subscription` (`subscription_id`, `subscriber_id`, `template_id`)
VALUES (@notification_subscription_id, @notification_subscriber_id_affected_employee, @notification_template_id);
