/*
 | JobScheduler Reporting Interface tables for PostgreSQL
 | SOS GmbH, 2015-01-30, robert.ehrlich@sos-berlin.com
*/


/* Fact Tables */
/* Table for REPORTING_TRIGGERS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_TRIGGERS' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_TRIGGERS(
                "ID"                    BIGINT          NOT NULL,
                "SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
                "HISTORY_ID"            BIGINT          NOT NULL,
                "NAME"                  VARCHAR(255)    NOT NULL,
                "TITLE"                 VARCHAR(255),
                "PARENT_FOLDER"         VARCHAR(255)    DEFAULT ''/'' NOT NULL,
                "PARENT_NAME"           VARCHAR(255)    NOT NULL,
                "PARENT_BASENAME"       VARCHAR(255)    NOT NULL,
                "PARENT_TITLE"          VARCHAR(255),
                "STATE"                 VARCHAR(255),
                "STATE_TEXT"            VARCHAR(255),
                "START_TIME"            TIMESTAMP       NOT NULL,
                "END_TIME"              TIMESTAMP,
                "IS_RUNTIME_DEFINED"    NUMERIC(1)      DEFAULT 0   NOT NULL,
                "RESULT_START_CAUSE"    VARCHAR(50)     NOT NULL,
                "RESULT_STEPS"          BIGINT          NOT NULL,
                "RESULT_ERROR"          NUMERIC(1)      NOT NULL,
                "RESULT_ERROR_CODE"     VARCHAR(50),
                "RESULT_ERROR_TEXT"     VARCHAR(255),
                "SYNC_COMPLETED"        NUMERIC(1)      DEFAULT 0   NOT NULL,
                "RESULTS_COMPLETED"     NUMERIC(1)      DEFAULT 0   NOT NULL,
                "CREATED"               TIMESTAMP       NOT NULL,
                "MODIFIED"              TIMESTAMP       NOT NULL,
                CONSTRAINT REPORTING_INX_RT_UNIQUE UNIQUE("SCHEDULER_ID","HISTORY_ID"),
                PRIMARY KEY ("ID")
            )'; 
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_NAME     ON REPORTING_TRIGGERS("NAME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_PFOLDER  ON REPORTING_TRIGGERS("PARENT_FOLDER")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_PNAME    ON REPORTING_TRIGGERS("PARENT_NAME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_STIME    ON REPORTING_TRIGGERS("START_TIME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_SCOMP    ON REPORTING_TRIGGERS("SYNC_COMPLETED")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RT_RCOMP    ON REPORTING_TRIGGERS("RESULTS_COMPLETED")';
        DECLARE sequence_exist integer; 
        BEGIN 
            SELECT COUNT(*) INTO sequence_exist FROM pg_class, pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_SEQ_RT' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_schema; 
            IF sequence_exist = 0 THEN 
                EXECUTE 'CREATE SEQUENCE REPORTING_SEQ_RT;';
            END IF; 
        END;
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Table for REPORTING_EXECUTIONS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_EXECUTIONS' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_EXECUTIONS(
                    "ID"                    BIGINT          NOT NULL,
                    "SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
                    "HISTORY_ID"            BIGINT          NOT NULL,
                    "TRIGGER_ID"            BIGINT          NOT NULL,
                    "TASK_ID"               BIGINT          NOT NULL,
                    "CLUSTER_MEMBER_ID"     VARCHAR(100),
                    "STEP"                  BIGINT          NOT NULL,
                    "FOLDER"                VARCHAR(255)    DEFAULT ''/'' NOT NULL,
                    "NAME"                  VARCHAR(255)    NOT NULL,
                    "BASENAME"              VARCHAR(255)    NOT NULL,
                    "TITLE"                 VARCHAR(255),
                    "START_TIME"            TIMESTAMP       NOT NULL,
                    "END_TIME"              TIMESTAMP,
                    "STATE"                 VARCHAR(255)    NOT NULL,
                    "CAUSE"                 VARCHAR(50)     NOT NULL,
                    "EXIT_CODE"             NUMERIC(10)     DEFAULT 0   NOT NULL,
                    "ERROR"                 NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "ERROR_CODE"            VARCHAR(50),
                    "ERROR_TEXT"            VARCHAR(255),
                    "AGENT_URL"             VARCHAR(100),
                    "IS_RUNTIME_DEFINED"    NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "SYNC_COMPLETED"        NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "RESULTS_COMPLETED"     NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "CREATED"               TIMESTAMP       NOT NULL,
                    "MODIFIED"              TIMESTAMP       NOT NULL,
                    CONSTRAINT REPORTING_INX_RE_UNIQUE UNIQUE ("SCHEDULER_ID","HISTORY_ID","TRIGGER_ID","STEP"),
                    PRIMARY KEY ("ID")
                )'; 
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_TASKID   ON REPORTING_EXECUTIONS("TASK_ID")';        
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_FOLDER   ON REPORTING_EXECUTIONS("FOLDER")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_NAME     ON REPORTING_EXECUTIONS("NAME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_STIME    ON REPORTING_EXECUTIONS("START_TIME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_SCOMP    ON REPORTING_EXECUTIONS("SYNC_COMPLETED")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RE_RCOMP    ON REPORTING_EXECUTIONS("RESULTS_COMPLETED")';
        DECLARE sequence_exist integer; 
        BEGIN 
            SELECT COUNT(*) INTO sequence_exist FROM pg_class, pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_SEQ_RE' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_schema; 
            IF sequence_exist = 0 THEN 
                EXECUTE 'CREATE SEQUENCE REPORTING_SEQ_RE;';
            END IF; 
        END;
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Table for REPORTING_TASKS */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_TASKS' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_TASKS(
                    "ID"                    BIGINT          NOT NULL,
                    "SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
                    "HISTORY_ID"            BIGINT          NOT NULL,
                    "IS_ORDER"              NUMERIC(1)      NOT NULL,
                    "CLUSTER_MEMBER_ID"     VARCHAR(100),
                    "STEPS"                 NUMERIC(10)     NOT NULL,
                    "FOLDER"                VARCHAR(255)    DEFAULT ''/'' NOT NULL,
                    "NAME"                  VARCHAR(255)    NOT NULL,
                    "BASENAME"              VARCHAR(255)    NOT NULL,
                    "TITLE"                 VARCHAR(255),
                    "START_TIME"            TIMESTAMP       NOT NULL,
                    "END_TIME"              TIMESTAMP,
                    "CAUSE"                 VARCHAR(50)     NOT NULL,
                    "EXIT_CODE"             NUMERIC(10)     DEFAULT 0   NOT NULL,
                    "ERROR"                 NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "ERROR_CODE"            VARCHAR(50),
                    "ERROR_TEXT"            VARCHAR(255),
                    "AGENT_URL"             VARCHAR(100),
                    "IS_RUNTIME_DEFINED"    NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "SYNC_COMPLETED"        NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "RESULTS_COMPLETED"     NUMERIC(1)      DEFAULT 0   NOT NULL,
                    "CREATED"               TIMESTAMP       NOT NULL,
                    "MODIFIED"              TIMESTAMP       NOT NULL,
                    CONSTRAINT REPORTING_INX_RTS_UNIQUE UNIQUE ("SCHEDULER_ID","HISTORY_ID"),
                    PRIMARY KEY ("ID")
                )'; 
        EXECUTE 'CREATE INDEX REPORTING_INX_RTS_FOLDER   ON REPORTING_TASKS("FOLDER")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RTS_NAME     ON REPORTING_TASKS("NAME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RTS_STIME    ON REPORTING_TASKS("START_TIME")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RTS_SCOMP    ON REPORTING_TASKS("SYNC_COMPLETED")';
        EXECUTE 'CREATE INDEX REPORTING_INX_RTS_RCOMP    ON REPORTING_TASKS("RESULTS_COMPLETED")';
        DECLARE sequence_exist integer; 
        BEGIN 
            SELECT COUNT(*) INTO sequence_exist FROM pg_class, pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_SEQ_RTS' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_schema; 
            IF sequence_exist = 0 THEN 
                EXECUTE 'CREATE SEQUENCE REPORTING_SEQ_RTS;';
            END IF; 
        END;
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Aggregation tables */
/* Table for REPORTING_EXECUTION_DATES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_EXECUTION_DATES' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_EXECUTION_DATES(
                    "ID"                    BIGINT          NOT NULL,
                    "SCHEDULER_ID"          VARCHAR(100)    NOT NULL,
                    "HISTORY_ID"            BIGINT          NOT NULL,
                    "REFERENCE_ID"          BIGINT          NOT NULL,
                    "REFERENCE_TYPE"        BIGINT          NOT NULL,
                    "START_DAY"             NUMERIC(2)      NOT NULL,
                    "START_WEEK"            NUMERIC(2)      NOT NULL,
                    "START_MONTH"           NUMERIC(2)      NOT NULL,
                    "START_QUARTER"         NUMERIC(1)      NOT NULL,
                    "START_YEAR"            NUMERIC(4)      NOT NULL,
                    "END_DAY"               NUMERIC(2)      NOT NULL,
                    "END_WEEK"              NUMERIC(2)      NOT NULL,
                    "END_MONTH"             NUMERIC(2)      NOT NULL,
                    "END_QUARTER"           NUMERIC(1)      NOT NULL,
                    "END_YEAR"              NUMERIC(4)      NOT NULL,
                    "CREATED"               TIMESTAMP       NOT NULL,
                    "MODIFIED"              TIMESTAMP       NOT NULL,
                    CONSTRAINT REPORTING_INX_RED_UNIQUE UNIQUE ("REFERENCE_ID","REFERENCE_TYPE"),
                    PRIMARY KEY ("ID")
                )';
        DECLARE sequence_exist integer; 
        BEGIN 
            SELECT COUNT(*) INTO sequence_exist FROM pg_class, pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_SEQ_RED' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_schema; 
            IF sequence_exist = 0 THEN 
                EXECUTE 'CREATE SEQUENCE REPORTING_SEQ_RED;';
            END IF; 
        END;
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Mapping tables */
/* Table for REPORTING_MAP_CAUSES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_MAP_CAUSES' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_MAP_CAUSES(
                    "ID"                    BIGINT          NOT NULL,
                    "CAUSE"                 VARCHAR(100)    NOT NULL,
                    "MAPPED_CAUSE"          VARCHAR(255)    NOT NULL,
                    "CREATED"               TIMESTAMP       NOT NULL,
                    "MODIFIED"              TIMESTAMP       NOT NULL,
                    PRIMARY KEY ("ID")
                )';
        EXECUTE 'CREATE INDEX REPORTING_INX_RMC_CAUSE     ON REPORTING_MAP_CAUSES("CAUSE")';
        DECLARE sequence_exist integer; 
        BEGIN 
            SELECT COUNT(*) INTO sequence_exist FROM pg_class, pg_namespace WHERE UPPER(pg_class.relname) = 'REPORTING_SEQ_RMC' AND pg_namespace.oid = pg_class.relnamespace AND pg_namespace.nspname = current_schema; 
            IF sequence_exist = 0 THEN 
                EXECUTE 'CREATE SEQUENCE REPORTING_SEQ_RMC;';
            END IF; 
        END;
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;

/* Table for REPORTING_VARIABLES */
CREATE OR REPLACE FUNCTION tmp() RETURNS VOID AS $$ 
DECLARE table_exist integer; 
BEGIN 
    SELECT COUNT(*) INTO table_exist FROM INFORMATION_SCHEMA.TABLES WHERE  UPPER(TABLE_NAME)='REPORTING_VARIABLES' AND TABLE_CATALOG=CURRENT_DATABASE() AND TABLE_SCHEMA=CURRENT_SCHEMA(); 
    IF table_exist = 0 THEN 
        EXECUTE 'CREATE TABLE REPORTING_VARIABLES(
                    "NAME"              VARCHAR(255)   NOT NULL,             
                    "NUMERIC_VALUE"     BIGINT,                
                    "TEXT_VALUE"        VARCHAR(255),          
                    PRIMARY KEY ("NAME")
                )';
    END IF; 
RETURN; 
END; $$ LANGUAGE plpgsql;
SELECT tmp();
COMMIT;
/* clean-up temporary objects */
DROP FUNCTION tmp();
COMMIT;