CREATE TABLE ohrm_event_template_type(
  `id` INT AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `is_active` TINYINT,
  PRIMARY KEY(`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 0;



INSERT INTO ohrm_event_template_type (  `name`, `is_active`) values
('Onboarding',1),
('Offboarding',1);

ALTER TABLE
    `ohrm_job` ADD `event_template_type_id` INT(11)  NULL AFTER `new_hires_ids`,
    ADD `is_template` TINYINT(1) NOT NULL DEFAULT '0' AFTER `event_template_type_id`;

ALTER TABLE `ohrm_job` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` ) ;

ALTER TABLE `ohrm_job` DROP FOREIGN KEY `ohrm_job_ibfk_1` ;

ALTER TABLE  `ohrm_job` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`) USING BTREE;
ALTER TABLE `ohrm_job` CHANGE `location_id` `location_id` INT(11) NULL DEFAULT NULL;

DROP TABLE ohrm_job_task_type;

CREATE TABLE ohrm_job_task_type (job_id INT, task_type_id INT, PRIMARY KEY(job_id, task_type_id)) ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
ALTER TABLE `ohrm_job_task_type` ADD FOREIGN KEY ( `job_id` ) REFERENCES `ohrm_job` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;
ALTER TABLE `ohrm_job_task_type` ADD FOREIGN KEY ( `task_type_id` ) REFERENCES `ohrm_task_type` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ;

ALTER TABLE  ohrm_job_owner DROP FOREIGN KEY `ohrm_job_owner_ibfk_1`;
ALTER TABLE `ohrm_job_owner` ADD  CONSTRAINT `ohrm_job_owner_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `ohrm_job`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE  ohrm_task DROP FOREIGN KEY `ohrm_task_ibfk_1`;
ALTER TABLE `ohrm_task` ADD  CONSTRAINT `ohrm_task_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `ohrm_job`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('pim_wizard_status_onboarding', '0') ;
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('days_to_onboarding', '15') ;
INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('days_to_offboarding', '0') ;

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 @onboarding_folder_id := (SELECT id FROM ohrm_report_folder WHERE name = 'Onboarding');

UPDATE `ohrm_advanced_report`
SET `folder_id` = @onboarding_folder_id, `custom_url` = 'report_view({"id": "Employee_Tasks_Report", "filters": null})', `created_by`= @system_user_id
WHERE `unique_name` = 'Employee_Tasks_Report';

ALTER TABLE ohrm_task_type ADD owner_type varchar(255);

ALTER TABLE ohrm_task ADD completed_by varchar(100) AFTER completed_date;

ALTER TABLE ohrm_task ADD completed_by_id INT AFTER completed_by;

UPDATE `ohrm_task_type` SET `owner_type`='3' WHERE `owner_id` > 0;

UPDATE `ohrm_task_type` SET `owner_type`='1' WHERE `owner_type` IS NULL;

