use kylo;

ALTER TABLE FEED
ADD COLUMN `IS_STREAM` VARCHAR(1) NULL DEFAULT 'N';

ALTER TABLE FEED
ADD COLUMN `TIME_BTWN_BATCH_JOBS` INT(11) NULL DEFAULT 1000;

ALTER TABLE BATCH_JOB_EXECUTION
ADD COLUMN `IS_STREAM` VARCHAR(1) NULL DEFAULT 'N';

ALTER TABLE NIFI_FEED_PROCESSOR_STATS
ADD COLUMN FAILED_EVENTS BIGINT(20);

CREATE OR REPLACE VIEW LATEST_FEED_JOB_VW AS
    SELECT
        f.id AS FEED_ID,
        f.name AS FEED_NAME,
        f.is_stream AS IS_STREAM,
        MAX(e.JOB_EXECUTION_ID) AS JOB_EXECUTION_ID
    FROM
        ((BATCH_JOB_EXECUTION e
        JOIN BATCH_JOB_INSTANCE i ON ((i.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID)))
        JOIN FEED f ON ((f.id = i.FEED_ID)))
    GROUP BY f.id , f.name, f.is_stream ;
 
 
    CREATE OR REPLACE VIEW FEED_HEALTH_VW AS
    SELECT
        summary.FEED_ID AS FEED_ID,
        summary.FEED_NAME AS FEED_NAME,
        maxJobs.IS_STREAM AS IS_STREAM,
        e.JOB_EXECUTION_ID AS JOB_EXECUTION_ID,
        i.JOB_INSTANCE_ID AS JOB_INSTANCE_ID,
        e.START_TIME AS START_TIME,
        e.END_TIME AS END_TIME,
        e.STATUS AS STATUS,
        e.EXIT_CODE AS EXIT_CODE,
        e.EXIT_MESSAGE AS EXIT_MESSAGE,
        summary.FAILED_COUNT AS FAILED_COUNT,
        summary.COMPLETED_COUNT AS COMPLETED_COUNT,
        summary.ABANDONED_COUNT AS ABANDONED_COUNT,
        summary.ALL_COUNT AS ALL_COUNT,
        summary.RUNNING_COUNT AS RUNNING_COUNT
    FROM
        (((BATCH_JOB_EXECUTION e
        JOIN BATCH_JOB_INSTANCE i ON ((i.JOB_INSTANCE_ID = e.JOB_INSTANCE_ID)))
        JOIN BATCH_FEED_SUMMARY_COUNTS_VW summary ON ((summary.FEED_ID = i.FEED_ID)))
        JOIN LATEST_FEED_JOB_VW maxJobs ON (((maxJobs.FEED_ID = summary.FEED_ID)
            AND (maxJobs.JOB_EXECUTION_ID = e.JOB_EXECUTION_ID))));

            CREATE OR REPLACE VIEW `LATEST_FINISHED_FEED_JOB_VW` AS
    SELECT
        `f`.`id` AS `FEED_ID`,
        `f`.`name` AS `FEED_NAME`,
        `f`.`FEED_TYPE` AS `FEED_TYPE`,
        `e`.`JOB_EXECUTION_ID` AS `JOB_EXECUTION_ID`,
        `i`.`JOB_INSTANCE_ID` AS `JOB_INSTANCE_ID`,
        `e`.`START_TIME` AS `START_TIME`,
        `e`.`END_TIME` AS `END_TIME`,
        `e`.`STATUS` AS `STATUS`,
        `e`.`EXIT_CODE` AS `EXIT_CODE`,
        `e`.`EXIT_MESSAGE` AS `EXIT_MESSAGE`,
        `e`.`IS_STREAM` AS `IS_STREAM`
    FROM
        (((`BATCH_JOB_EXECUTION` `e`
        JOIN `BATCH_JOB_INSTANCE` `i` ON ((`i`.`JOB_INSTANCE_ID` = `e`.`JOB_INSTANCE_ID`)))
        JOIN `FEED` `f` ON ((`f`.`id` = `i`.`FEED_ID`)))
        JOIN `LATEST_FEED_JOB_END_TIME_VW` `maxJobs` ON (((`maxJobs`.`FEED_ID` = `f`.`id`)
            AND (`maxJobs`.`END_TIME` = `e`.`END_TIME`))));


CREATE TABLE `kylo`.`BATCH_RELATED_FLOW_FILES` (
  `FLOW_FILE_ID` VARCHAR(45) NOT NULL COMMENT '',
  `BATCH_JOB_FLOW_FILE_ID` VARCHAR(45) NULL COMMENT '',
  `BATCH_JOB_EXECUTION_ID` BIGINT(20) NULL COMMENT '',
  PRIMARY KEY (`FLOW_FILE_ID`)  COMMENT '');