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_type (
     id INT AUTO_INCREMENT,
     name VARCHAR(255) NOT NULL,
     processor VARCHAR(255) NOT NULL,
     PRIMARY KEY(id)
) ENGINE = INNODB  default charset=utf8;

CREATE TABLE ohrm_ics_calendar_feed (
    url VARCHAR(255),
    user_id INT,
    feed_type_id INT NOT NULL,
    is_enabled TINYINT NOT NULL DEFAULT 0,
    filters VARCHAR(1000),
    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(url),
    CONSTRAINT `fk_ohrm_ics_calendar_feed_ohrm_ics_calendar_feed_type` FOREIGN KEY (`feed_type_id`) REFERENCES `ohrm_ics_calendar_feed_type`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    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;

INSERT INTO ohrm_ics_calendar_feed_type (name, processor) VALUES ('my_leave','IcsCalendarFeedMyLeaveProcessor');
INSERT INTO ohrm_ics_calendar_feed_type (name, processor) VALUES ('leave_list','IcsCalendarFeedLeaveListProcessor');
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');
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 @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');

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');
