CREATE TABLE ohrm_external_data_warehouse_data_extraction_scheduler (
    `id` int(11) not null auto_increment,
    `report_id` int(10) not null,
    `configuration_type` VARCHAR(255) default null,
    `index_name` VARCHAR(255) not null,
    `frequency` VARCHAR(255) not null,
    `last_executed_time` datetime default null,
    PRIMARY KEY (id),
    CONSTRAINT `fk_data_extraction_scheduler_advanced_report` FOREIGN KEY (`report_id`) REFERENCES `ohrm_advanced_report` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB DEFAULT CHARSET=UTF8;


SET @configuration_information_report_id := (SELECT `id` FROM `ohrm_advanced_report` WHERE `unique_name`='Configuration_Extraction_For_Data_Warehouse');
INSERT INTO `ohrm_external_data_warehouse_data_extraction_scheduler` (`report_id`, `configuration_type`, `index_name`, `frequency`) VALUES (@configuration_information_report_id, 'product_config', 'monthly-product-stat', 'monthly');

SET @leave_information_report_id := (SELECT `id` FROM `ohrm_advanced_report` WHERE `unique_name`='Leave_Nominee_Information_Extraction_For_Data_Warehouse');
INSERT INTO `ohrm_external_data_warehouse_data_extraction_scheduler` (`report_id`, `configuration_type`, `index_name`, `frequency`) VALUES (@leave_information_report_id, 'leave_nominee', 'monthly-product-stat', 'monthly');

SET @feature_usage_information_report_id := (SELECT `id` FROM `ohrm_advanced_report` WHERE `unique_name`='Feature_Usage_Information_Extraction_For_Data_Warehouse');
INSERT INTO `ohrm_external_data_warehouse_data_extraction_scheduler` (`report_id`, `configuration_type`, `index_name`, `frequency`) VALUES (@feature_usage_information_report_id, 'feature_usage_stat', 'monthly-feature-usage-stat', 'monthly');


