create table `ohrm_modified_language_strings`(
	`id` int(13) not null auto_increment,
	`language` varchar(10) not null,
	`string` TEXT not null,
	`localized_string` TEXT not null,
	primary key (`id`)
)engine=innodb default charset=utf8;

INSERT INTO `ohrm_event_listener` (`id`, `event`, `listener_class`, `enabled`, `position`) VALUES (NULL, 'admin.acknowledgement', 'AcknowledgmentListener', '1', '7');

CREATE TABLE `ohrm_consecutive_attempt_screen` (
    `id` INT AUTO_INCREMENT,
    `class` varchar(255) NOT NULL ,
    PRIMARY KEY(`id`)
) ENGINE = INNODB default charset=utf8;

INSERT INTO `ohrm_consecutive_attempt_screen`(`id`, `class`) VALUES
(1, 'LoginAttemptProcessor'),
(2, 'VacancyApplicationAttemptProcessor'),
(3, 'UserAccountIdentificationAttemptProcessor'),
(4, 'IssueTokenAttemptProcessor');

CREATE  TABLE `ohrm_consecutive_attempt_log` (
  `id` int(13) not null auto_increment,
  `ip` VARCHAR(72) NOT NULL ,
  `username` VARCHAR(50) ,
  `screen_id` INT NOT NULL,
  `last_attempt_time` DATETIME NOT NULL ,
  `no_of_attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 1 ,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ohrm_consecutive_attempt_log_ohrm_consecutive_attempt_screen` FOREIGN KEY (`screen_id`) REFERENCES `ohrm_consecutive_attempt_screen`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
ENGINE = InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE ohrm_i18n_language (
    code VARCHAR(15),
    name VARCHAR(255) NOT NULL,
    thousand_separator varchar(2) null,
    decimal_separator varchar(2) null,
    is_currency_symbol_at_right tinyint DEFAULT 0,
    is_hidden tinyint DEFAULT 0,
    PRIMARY KEY(code)
) ENGINE = INNODB DEFAULT CHARSET=UTF8;

INSERT INTO `ohrm_i18n_language`(`code`, `name`, `thousand_separator`, `decimal_separator`, `is_currency_symbol_at_right`, `is_hidden`) VALUES
    ('en', 'English', null, null, 0, 0),
    ('zh-cn', 'Chinese (Simplified Han) - 中文（简体中文）', null, null, 0, 0),
    ('zh-tw', 'Chinese (Traditional Han) - 中文 (繁體中文)', null, null, 0, 0),
    ('fr', 'French - Français', ' ', ',', 1, 0),
    ('de', 'German - Deutsch', '.', ',', 1, 0),
    ('es', 'Spanish - Español', '.', ',', 1, 0),
    ('th_TH', 'Thai - Thailand', null, null, 0, 0),
    ('hu', 'Hungarian - magyar', ' ', ',', 1, 0),
    ('it', 'Italian', '.', ',', 1, 0),
    ('ru', 'Russian - pyccкий', ' ', ',', 1, 0),
    ('zz_ZZ', 'test - TEST', null, null, 0, 1);

CREATE TABLE IF NOT EXISTS `ohrm_file_transfer_connection_type` (
    `id` int unsigned not null auto_increment,
    `name` varchar(100) NOT NULL UNIQUE,
    `connection_class` varchar(100) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_file_transfer_connection` (
    `id` int unsigned not null auto_increment,
    `connection_type_id` int unsigned NOT NULL,
    `name` varchar(100) NOT NULL,
    `settings` TEXT,
    `created_by` int NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE(`name`),
    CONSTRAINT `fk_ohrm_file_transfer_connection_created_by` FOREIGN KEY (`created_by`)
        REFERENCES `ohrm_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_ohrm_file_transfer_connection_connection_type` FOREIGN KEY (`connection_type_id`)
        REFERENCES ohrm_file_transfer_connection_type (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_file_transfer_connection_type`(`name`, `connection_class`) VALUES ('SFTP', 'OrangeHRM\\FileTransferConnection\\SFTPConnection');

# Add new System User
SET @system_role_id := (SELECT `id` FROM ohrm_user_role WHERE `name` = 'SYSTEM');
INSERT INTO `ohrm_user` ( `user_name`,`user_role_id`) VALUES ('System',@system_role_id);

SET @system_user_id := (SELECT `id` FROM `ohrm_user` WHERE `user_name` = 'System');
SET @fixed_schedule_type_id := (SELECT `id` FROM ohrm_work_schedule_type WHERE `name` = 'fixed');
SET @default_work_schedule_definition = '{"days": {"monday": {"day_type": "working_day", "shift_start_time": "09:00:00", "shift_end_time": "17:00:00", "break_start_time": null, "break_end_time": null, "duration": "8:00"}, "tuesday": {"day_type": "working_day", "shift_start_time": "09:00:00", "shift_end_time": "17:00:00", "break_start_time": null, "break_end_time": null, "duration": "8:00"}, "wednesday": {"day_type": "working_day", "shift_start_time": "09:00:00", "shift_end_time": "17:00:00", "break_start_time": null, "break_end_time": null, "duration": "8:00"}, "thursday": {"day_type": "working_day", "shift_start_time": "09:00:00", "shift_end_time": "17:00:00", "break_start_time": null, "break_end_time": null, "duration": "8:00"}, "friday": {"day_type": "working_day", "shift_start_time": "09:00:00", "shift_end_time": "17:00:00", "break_start_time": null, "break_end_time": null, "duration": "8:00"}, "saturday": {"day_type": "non_working_day", "shift_start_time": null, "shift_end_time": null, "break_start_time": null, "break_end_time": null, "duration": null}, "sunday": {"day_type": "non_working_day", "shift_start_time": null, "shift_end_time": null, "break_start_time": null, "break_end_time": null, "duration": null}}, "exclude_break_from_duration": true}';
INSERT INTO `ohrm_work_schedule` (`name`, `work_schedule_type_id`, `is_default`, `definition`, `created_through`, `deletable`, `editable`, `created_by`)
VALUES ('Default Work Schedule', @fixed_schedule_type_id, 1, @default_work_schedule_definition, 0, 0, 1, @system_user_id);

INSERT INTO `ohrm_secure_configuration` (`configuration_name`, `updatable_from_frontend`) VALUES ('core.open_ai_api_key', '1');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES
    ('core.open_ai_configuration', '{"temperature":1,"max_tokens":3000,"model":"gpt-3.5-turbo","frequency_penalty":0,"presence_penalty":0,"stream":false}');

CREATE TABLE IF NOT EXISTS `ohrm_ai_content_config` (
    `id` int(7) not null auto_increment,
    `name` varchar(100),
    `data_group_id` int(11),
    `prompt_generator` varchar(200),
    `prompt_template` varchar(2048),
    CONSTRAINT `fk_ohrm_ai_document_data_group_id` FOREIGN KEY (`data_group_id`)
    REFERENCES `ohrm_data_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (`id`)
    ) engine=innodb DEFAULT charset=utf8;

INSERT INTO `ohrm_ai_content_config` (`name`, `data_group_id`,`prompt_generator`, `prompt_template`) VALUES ('recruitment-document', (SELECT `id` FROM `ohrm_data_group` WHERE `name` = 'recruitment_configuration'),'RecruitmentDocumentPromptGenerator', "You are a HR with clarity, professionalism, accuracy, empathy, confidentiality, and \
    negotiation skills as your qualities. In the recruitment process we are at {stage} stage, \
    need to send the candidate a letter, which addresses only the current recruitment stage {vacancy_information}. \
    below are a list of super set of tokens that should be used \
    to replace values when and if required as it is with the starting and ending hashtags.

{token_list}");

CREATE TABLE IF NOT EXISTS ohrm_data_field (
     `id` int AUTO_INCREMENT,
     `name` VARCHAR(255) NOT NULL UNIQUE,
     `label` VARCHAR(255),
     `data_group_id` int,
     `can_read` TINYINT,
     `can_create` TINYINT,
     `can_update` TINYINT,
     `can_delete` TINYINT,
     PRIMARY KEY(`id`),
     CONSTRAINT `fk_data_field_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 IF NOT EXISTS ohrm_user_role_data_field (
       id int AUTO_INCREMENT,
       user_role_id int,
       data_field_id int,
       can_read TINYINT,
       can_create TINYINT,
       can_update TINYINT,
       can_delete TINYINT,
       self TINYINT DEFAULT 0,
       PRIMARY KEY(id),
       CONSTRAINT `fk_user_role_data_field_user_role_id` FOREIGN KEY (`user_role_id`)
           REFERENCES `ohrm_user_role` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
       CONSTRAINT `fk_user_role_data_field_data_field_id` FOREIGN KEY (`data_field_id`)
           REFERENCES `ohrm_data_field` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=utf8;

SET @job_data_group_id := (SELECT id FROM ohrm_data_group WHERE name = 'job_history_details');

INSERT INTO ohrm_data_field (`name`, `label`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES
    ('work_schedule', 'Work Schedule', @job_data_group_id, 1, 0, 1, 0);
SET @data_field_id := (SELECT id FROM ohrm_data_field WHERE name = 'work_schedule');

INSERT INTO ohrm_user_role_data_field (user_role_id, data_field_id, can_read, can_update)
SELECT user_role_id, @data_field_id, can_read, can_update
FROM ohrm_user_role_data_group
WHERE data_group_id = @job_data_group_id;

UPDATE ohrm_user_role_data_field SET can_update = 1 WHERE user_role_id IN (SELECT `id` FROM `ohrm_user_role` WHERE `name` IN ('HeadOfDepartment', 'Supervisor'));
SET @ess_type_id := (SELECT id FROM `ohrm_user_role_type` WHERE name = 'ESS');
UPDATE ohrm_user_role_data_field SET self = 1 WHERE user_role_id IN (SELECT id FROM `ohrm_user_role` where type_id = @ess_type_id);
