CREATE TABLE IF NOT EXISTS `ohrm_ics_calendar_invitation_event` (
      `id` int NOT NULL AUTO_INCREMENT,
      `uid` VARCHAR(255) NOT NULL,
      `sequence` int(4) NOT NULL,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_ics_calendar_invitation_processor` (
      `id` int NOT NULL AUTO_INCREMENT,
      `notification_id` int(6) NOT NULL,
      `processor` VARCHAR(255) NOT NULL,
      `is_enabled` int NOT NULL DEFAULT 1,
      `calendar_method` VARCHAR(100),
      `status` VARCHAR(100),
      `opacity` VARCHAR(100),
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `ohrm_ics_calendar_content_template` (
      `id` int NOT NULL AUTO_INCREMENT,
      `notification_id` int(6) NOT NULL,
      `organizer` VARCHAR(255),
      `recipient` VARCHAR(255),
      `summary_text` VARCHAR(255),
      `description_text` VARCHAR(255),
      PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ohrm_ics_calendar_invitation_processor`
ADD CONSTRAINT FOREIGN KEY (`notification_id`)
REFERENCES `ohrm_notification`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE `ohrm_ics_calendar_content_template`
ADD CONSTRAINT FOREIGN KEY (`notification_id`)
REFERENCES `ohrm_notification`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('ics.enable', 'No');

INSERT INTO `ohrm_config` (`property`, `value`) VALUES ('default_timezone', 'America/New_York');

CREATE TABLE ohrm_ics_calendar_feed_event_type
(
    id   INT AUTO_INCREMENT,
    identifier VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(30) NOT NULL UNIQUE,
    PRIMARY KEY (id)
) ENGINE = INNODB  default charset=utf8;

INSERT INTO ohrm_ics_calendar_feed_event_type (id, identifier, name)
VALUES (1, 'leave', 'Leave'),
       (2, 'holiday', 'Holiday');

CREATE TABLE ohrm_ics_calendar_feed_processor_type
(
    id            INT AUTO_INCREMENT,
    identifier    VARCHAR(30) NOT NULL UNIQUE,
    name          VARCHAR(30) NOT NULL,
    display_order INT         NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB default charset=utf8;

INSERT INTO ohrm_ics_calendar_feed_processor_type (id, identifier, name, display_order)
VALUES (1, 'whole_company', 'All Accessible Employees', 1),
       (2, 'my_events', 'My Events', 2),
       (3, 'selected_employee', 'Selected Employees', 3);

CREATE TABLE ohrm_ics_calendar_feed_type (
     id INT AUTO_INCREMENT,
     processor VARCHAR(255) NOT NULL,
     processor_type_id INT NOT NULL,
     event_type_id INT NOT NULL,
     PRIMARY KEY(id),
     CONSTRAINT `fk_ohrm_ics_calendar_feed_processor_type` FOREIGN KEY (`processor_type_id`) REFERENCES `ohrm_ics_calendar_feed_processor_type`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_ohrm_ics_calendar_feed_event_type` FOREIGN KEY (`event_type_id`) REFERENCES `ohrm_ics_calendar_feed_event_type`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB  default charset=utf8;

CREATE TABLE ohrm_ics_calendar_feed (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    url VARCHAR(255),
    user_id INT,
    employee_filters TEXT,
    filter_from DATE DEFAULT NULL,
    filter_to DATE DEFAULT NULL,
    is_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    count_reset_at DATETIME,
    request_count_in_last_five_minutes INT DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY(id),
    UNIQUE (url),
    CONSTRAINT `fk_ohrm_ics_calendar_feed_ohrm_user` FOREIGN KEY (`user_id`) REFERENCES `ohrm_user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB  default charset=utf8;

CREATE TABLE ohrm_ics_calendar_feed_feed_type
(
    calendar_feed_id INT NOT NULL,
    feed_type_id     INT NOT NULL,
    event_filters    TEXT,
    PRIMARY KEY (calendar_feed_id, feed_type_id),
    CONSTRAINT fk_feed FOREIGN KEY (calendar_feed_id) REFERENCES ohrm_ics_calendar_feed (id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_feed_type FOREIGN KEY (feed_type_id) REFERENCES ohrm_ics_calendar_feed_type (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8;

SET @leave_event_type := (SELECT `id` FROM `ohrm_ics_calendar_feed_event_type` WHERE `identifier` = 'leave');
SET @holiday_event_type := (SELECT `id` FROM `ohrm_ics_calendar_feed_event_type` WHERE `identifier` = 'holiday');

SET @my_events_processor_type := (SELECT `id` FROM `ohrm_ics_calendar_feed_processor_type` WHERE `identifier` = 'my_events');
SET @selected_employee_processor_type := (SELECT `id` FROM `ohrm_ics_calendar_feed_processor_type` WHERE `identifier` = 'selected_employee');
SET @whole_company_processor_type := (SELECT `id` FROM `ohrm_ics_calendar_feed_processor_type` WHERE `identifier` = 'whole_company');

INSERT INTO ohrm_ics_calendar_feed_type (processor, event_type_id, processor_type_id)
VALUES ('IcsCalendarFeedLeaveMyEventsProcessor', @leave_event_type, @my_events_processor_type),
       ('IcsCalendarFeedLeaveSelectedEmployeeProcessor', @leave_event_type, @selected_employee_processor_type),
       ('IcsCalendarFeedLeaveWholeCompanyProcessor', @leave_event_type, @whole_company_processor_type);

INSERT INTO ohrm_ics_calendar_feed_type (processor, event_type_id, processor_type_id)
VALUES ('IcsCalendarFeedHolidayMyEventsProcessor', @holiday_event_type, @my_events_processor_type),
       ('IcsCalendarFeedHolidaySelectedEmployeeProcessor', @holiday_event_type, @selected_employee_processor_type),
       ('IcsCalendarFeedHolidayWholeCompanyProcessor', @holiday_event_type, @whole_company_processor_type);

INSERT INTO ohrm_config (property, value ) VALUES ('ics_feed_refresh_wait_time_in_seconds', '300');
INSERT INTO ohrm_config (property, value ) VALUES ('ics_feed_enabled', '1');

SET @core_module_id := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'core');
SET @pimModuleId := (SELECT `id` FROM `ohrm_module` WHERE `name` = 'pim');

INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`) VALUES ('iCalendar Configuration', @core_module_id, 'configureiCalendar');
SET @icalendar_config_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name`='iCalendar Configuration');

SET @admin_user_role_type_id = (SELECT `id` FROM `ohrm_user_role_type` WHERE `name`='Admin');
SET @config_admin_user_role_type_id = (SELECT `id` FROM `ohrm_user_role_type` WHERE `name`='ConfigAdmin');
SET @ess_user_role_type_id = (SELECT `id` FROM `ohrm_user_role_type` WHERE `name`='ESS');

SET @config_admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name`='ConfigAdmin');
SET @admin_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name`='Admin');
SET @ess_user_role_id := (SELECT `id` FROM `ohrm_user_role` WHERE `name` ='ESS');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`) VALUES (@config_admin_user_role_id, @icalendar_config_screen_id, '1');
INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`) VALUES (@admin_user_role_id, @icalendar_config_screen_id, '1');
SET @admin_configurations_menu_item_id := (SELECT menu_item.id FROM `ohrm_menu_item` menu_item LEFT JOIN `ohrm_menu_item` parent_menu_item ON menu_item.parent_id = parent_menu_item.id WHERE menu_item.menu_title = 'Configurations' AND parent_menu_item.menu_title = 'HR Administration');
INSERT INTO `ohrm_menu_item` (`menu_title`, `screen_id`, `parent_id`, `level`, `order_hint`, `uisref`) VALUES ('iCalendar',@icalendar_config_screen_id, @admin_configurations_menu_item_id, '4', '1000', 'admin.configure_icalendar_feed');

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_update`) VALUES ('icalendar_global_config', 'HR Administration - Configuration - iCalendar', '1', '1');

SET @icalendar_global_config_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='icalendar_global_config');
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`,`can_update`, `self`) VALUES (@config_admin_user_role_id, @icalendar_global_config_data_group_id, '1', '1', '0');
INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`,`can_update`, `self`) VALUES (@admin_user_role_id, @icalendar_global_config_data_group_id, '1', '1', '0');


INSERT INTO `ohrm_screen` (`name`, `module_id`, `action_url`, `route_name`) VALUES ('iCalendar Feed', @pimModuleId, 'viewICalendarFeed', null );
SET @view_iCalendar_feed_screen_id := (SELECT `id` FROM `ohrm_screen` WHERE `name`='iCalendar Feed');

INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@admin_user_role_id, @view_iCalendar_feed_screen_id, '1','1','1','1');
INSERT INTO `ohrm_user_role_screen` (`user_role_id`, `screen_id`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES (@ess_user_role_id, @view_iCalendar_feed_screen_id, '1','1','1','1');

SET @people_management_pillar_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'People Management' AND parent_id IS NULL AND level = 1);
SET @employee_management_module_menu_id := (SELECT `id` FROM ohrm_menu_item WHERE `menu_title` = 'Employee Management' AND parent_id = @people_management_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
    ('iCalendar Feeds', @view_iCalendar_feed_screen_id, @employee_management_module_menu_id, 3, 1130, NULL, 'vuepim.viewiCalendarList', 1);

INSERT INTO `ohrm_data_group` (`name`, `description`, `can_read`, `can_create`, `can_update`, `can_delete`) VALUES ('employee_icalendar', 'Employee Management - iCalendar Feeds', '1', '1', '1', '1');
SET @employee_icalendar_data_group_id := (SELECT `id` FROM `ohrm_data_group` WHERE `name`='employee_icalendar');

INSERT INTO `ohrm_data_group_screen`(`data_group_id`, `screen_id`, `permission`, `self`) VALUES
    (@employee_icalendar_data_group_id, @view_iCalendar_feed_screen_id, 1, 1);

INSERT INTO `ohrm_user_role_type_data_group` (`user_role_type_id`, `data_group_id`, `can_read`, `can_create`,
                                              `can_update`, `can_delete`)
VALUES (@ess_user_role_type_id, @employee_icalendar_data_group_id, 1, 1, 1, 1);

INSERT INTO `ohrm_user_role_data_group` (`user_role_id`, `data_group_id`, `can_read`, `can_create`, `can_update`, `can_delete`, `self`)
VALUES (@admin_user_role_id, @employee_icalendar_data_group_id, '1', '1', '1', '1', '1'),
       (@ess_user_role_id, @employee_icalendar_data_group_id, '1', '1', '1', '1', '1');

-- iCalendar Audit trail
SET @ohrm_pim_audittrail_module_id := (SELECT `id` FROM `ohrm_audittrail_module` WHERE `name`='Employee Management');

CREATE TABLE IF NOT EXISTS `ohrm_audittrail_calendar_feed_trail` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `action_owner_id` varchar(6),
    `action_owner_name` VARCHAR(300),
    `version_id` INT NOT NULL DEFAULT 0,
    `action` varchar(255) NOT NULL,
    `affected_entity_id` VARCHAR(20),
    `affected_entity_name` VARCHAR(300) NOT NULL DEFAULT '',
    `action_description` MEDIUMTEXT,
    `app_id` INT DEFAULT NULL,
    `app_name` VARCHAR(1000) DEFAULT NULL,
    `screen_name` VARCHAR(1000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX audit_icalendar_trail_idx_app_id (app_id),
    INDEX audit_icalendar_trail_idx_screen_name (screen_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ohrm_audittrail_section` (`name`, `description`, `affected_entity`, `module_id`) VALUES
    ('CalendarFeed', 'iCalendar Feeds', 'iCalendar Name', @ohrm_pim_audittrail_module_id);

SET @ohrm_audittrail_section_id := (SELECT `id` FROM `ohrm_audittrail_section` WHERE `name`='CalendarFeed');

INSERT INTO `ohrm_audittrail_section_actions` (`section_id`, `action`, `description`)
VALUES (@ohrm_audittrail_section_id, 'ADD iCALENDAR', 'Add iCalendar'),
       (@ohrm_audittrail_section_id, 'UPDATE iCALENDAR', 'Update iCalendar'),
       (@ohrm_audittrail_section_id, 'DELETE iCALENDAR', 'Delete iCalendar'),
       (@ohrm_audittrail_section_id, 'DISABLE URL', 'Disable URL'),
       (@ohrm_audittrail_section_id, 'ENABLE URL', 'Enable URL'),
       (@ohrm_audittrail_section_id, 'GENERATE NEW URL', 'Generate New URL');

SET @max_event_listener_position := (SELECT MAX(`position`)FROM ohrm_event_listener);

INSERT INTO `ohrm_event_listener` (`event`, `listener_class`, `enabled`, `position`)
VALUES ('pim.add_icalendar', 'AddCalendarFeedAuditListener', '1', @max_event_listener_position + 1),
       ('pim.update_icalendar', 'UpdateCalendarFeedAuditListener', '1', @max_event_listener_position + 2),
       ('pim.delete_icalendar', 'DeleteCalendarFeedAuditListener', '1', @max_event_listener_position + 3),
       ('pim.toggle_icalendar_url_status', 'ToggleStatusCalendarFeedAuditListener', '1', @max_event_listener_position + 4),
       ('pim.generate_new_icalendar_url', 'GenerateNewUrlCalendarFeedAuditListener', '1', @max_event_listener_position + 5);

