CREATE OR REPLACE FUNCTION scope_path (
      p_id    DECIMAL(20)
    , p_quote BOOLEAN DEFAULT false)
RETURNS TEXT 
AS $$
DECLARE
      v_path        TEXT;
      v_parent_id   DECIMAL(20);
      v_name        VARCHAR(64);
BEGIN
      SELECT name
           , parent_id
        INTO v_path
           , v_parent_id
        FROM sci_scope
       WHERE id = p_id;

      IF p_quote AND v_parent_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_parent_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_parent_id
           FROM sci_scope
          WHERE id = v_parent_id;

         IF p_quote THEN
            IF v_name = 'GLOBAL' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
END;
$$ language plpgsql;
   
CREATE OR REPLACE FUNCTION scheduled_event_path (
      p_id    DECIMAL(20)
    , p_quote BOOLEAN DEFAULT false)
RETURNS TEXT 
AS $$
DECLARE
      v_path        TEXT;
      v_schedule_id DECIMAL(20);
      v_name        VARCHAR(64);
BEGIN
      SELECT ev.name
           , sce.sce_id
        INTO v_path
           , v_schedule_id
        FROM sci_scheduled_event sce
           , sci_event ev
       WHERE sce.id = p_id
         AND ev.id = sce.evt_id;

      IF p_quote THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_schedule_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_schedule_id
           FROM sci_schedule
          WHERE id = v_schedule_id;

         IF p_quote THEN
            IF v_name = 'ROOT' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;
$$ language plpgsql;
  
CREATE OR REPLACE FUNCTION category_path (
      p_id    DECIMAL(20)
    , p_quote BOOLEAN DEFAULT false)
RETURNS TEXT 
AS $$
DECLARE
      v_path        TEXT;
      v_parent_id   DECIMAL(20);
      v_name        VARCHAR(64);
BEGIN
      SELECT name
           , parent_id
        INTO v_path
           , v_parent_id
        FROM sci_named_resource
       WHERE id = p_id;

      IF p_quote AND v_parent_id IS NOT NULL THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_parent_id IS NOT NULL LOOP
         SELECT NAME
              , parent_id
           INTO v_name
              , v_parent_id
           FROM sci_named_resource
          WHERE ID = v_parent_id;

         IF p_quote THEN
            IF v_name = 'RESOURCE' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION folder_path (
      p_id    DECIMAL(20)
    , p_quote BOOLEAN DEFAULT false)
RETURNS TEXT 
AS $$
DECLARE
      v_path        TEXT;
      v_folder_id DECIMAL(20);
      v_name        VARCHAR(64);
BEGIN
      BEGIN
         SELECT NAME
              , folder_id
           INTO v_path
              , v_folder_id
           FROM sci_c_scheduling_entity
          WHERE id = p_id;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            SELECT NAME
                 , parent_id
              INTO v_path
                 , v_folder_id
              FROM sci_c_folder
             WHERE id = p_id;
      END;

      IF p_quote THEN
         v_path := v_path || '''';
      END IF;

      WHILE v_folder_id IS NOT NULL LOOP
         SELECT name
              , parent_id
           INTO v_name
              , v_folder_id
           FROM sci_c_folder
          WHERE id = v_folder_id;

         IF p_quote THEN
            IF v_name = 'ROOT' THEN
               v_path := v_name || '.''' || v_path;
            ELSE
               v_path := v_name || '''.''' || v_path;
            END IF;
         ELSE
            v_path := v_name || '.' || v_path;
         END IF;
      END LOOP;

      RETURN v_path;
   END;
$$ language plpgsql;

/*
//-------------------------------------------------------------------------
// (Internal) representation of a DateTime as long value:
//
//    6     |      5  |        4|         | 3       |   2     |      1  |            content               remarks
// 3210 9876|5432 1098|7654 3210|9876 5432|1098 7654|3210 9876|5432 1098|7654 3210
// xxxx xxxx|xxxx ....|.... ....|.... ....|.... ....|.... ....|.... ....|.... ....   n/a                   reserved, must be 0
//                V...|.... ....|.... ....|.... ....|.... ....|.... ....|.... ....   seconds suppressed?   1=yes, 0=no
//                 V..|.... ....|.... ....|.... ....|.... ....|.... ....|.... ....   GMT valid?            1=yes, 0=no
//                 1gg|gggg gggg|gggg gggg|gggg gggg|gggg gggg|gggg gggg|gggg gggg   GMT (ms)              offset from epoch (1970-01-01T00:00:00)
//                 0D.|.... ....|.... ....|.... ....|.... ....|.... ....|.... ....   is a duration?        1=yes, 0=no
//                 0 V|.... ....|.... ....|.... ....|.... ....|.... ....|.... ....   year valid?           1=yes, 0=no
//                 0   YYYY YYYY|YYYY YY..|.... ....|.... ....|.... ....|.... ....   year (0-9999)         must be 0, if not valid
//                 0                    V.|.... ....|.... ....|.... ....|.... ....   month/week valid?     1=yes, 0=no
//                 0                     M|MMMx x...|.... ....|.... ....|.... ....   month (1-12)          must be 0, if not valid; 'x' must always be 0
//                 0                     w|wwww w...|.... ....|.... ....|.... ....   week (1-53)           must be 0, if not valid
//                 0                             T..|.... ....|.... ....|.... ....   month/week?           1=week, 0=month
//                 0                              V.|.... ....|.... ....|.... ....   day valid?            1=yes, 0=no
//                 0                               D|DDDD ....|.... ....|.... ....   day (1-31)            must be 0, if not valid
//                 0                                      V...|.... ....|.... ....   hour valid?           1=yes, 0=no
//                 0                                       hhh|hh.. ....|.... ....   hour (0-23)           must be 0, if not valid
//                 0                                             V. ....|.... ....   minute valid?         1=yes, 0=no
//                 0                                              m mmmm|m... ....   minute (0-53)         must be 0, if not valid
//                 0                                                      V.. ....   second valid?         1=yes, 0=no
//                 0                                                       ss ssss   second (0-53)         must be 0, if not valid
//-------------------------------------------------------------------------
// AS BIT(52);
                      |      1  |        2|           3       |   4     |      5  
                  1234|5678 9012|3456 7890|1234 5678|9012 3456|7890 1234|5678 9012
                  VVDV YYYY YYYY|YYYY YYVM|MMMx xTVD|DDDD Vhhh|hhVm mmmm|mVss ssss                           
 */
CREATE OR REPLACE FUNCTION dateTime2str (
      p_period    DECIMAL(20))
RETURNS TEXT 
AS $$
DECLARE
      v_pBits        BIT(52);
      v_result       VARCHAR(128);
BEGIN
      v_pBits  := CAST (p_period::BIGINT AS BIT(52));
      v_result := '';

      IF SUBSTRING(v_pBits,2,1)::INTEGER = 1 THEN
          v_result := to_char (TIMESTAMP 'epoch' + CAST(TO_CHAR(MOD(p_period, 1125899906842624)/1000, '999999999999') AS INTERVAL), 'YYYY.MM.DD"T"HH24:MI:SS');
          IF SUBSTR(v_result, 17, 3) = ':00' THEN
              v_result := SUBSTR(v_result, 1, 16);
          END IF;
          RETURN v_result;
      ELSE
          IF SUBSTRING(v_pBits,4,1)::INTEGER = 1 THEN
               v_result := v_result || SUBSTRING(v_pBits,  5, 14)::INTEGER::TEXT;
          END IF;
          v_result := v_result || '-';
          IF SUBSTRING(v_pBits,19,1)::INTEGER = 1 THEN
               v_result := v_result || LPAD (SUBSTRING(v_pBits, 20,  4)::INTEGER::TEXT, 2, '0');
          END IF;
          v_result := v_result || '-';
          IF SUBSTRING(v_pBits,27,1)::INTEGER = 1 THEN
               v_result := v_result || LPAD (SUBSTRING(v_pBits, 28,  5)::INTEGER::TEXT, 2, '0');
          END IF;
          v_result := v_result || 'T';
          IF SUBSTRING(v_pBits,33,1)::INTEGER = 1 THEN
               v_result := v_result || LPAD (SUBSTRING(v_pBits, 34,  5)::INTEGER::TEXT, 2, '0');
          END IF;
          v_result := v_result || ':';
          IF SUBSTRING(v_pBits,39,1)::INTEGER = 1 THEN
               v_result := v_result || LPAD (SUBSTRING(v_pBits, 40,  6)::INTEGER::TEXT, 2, '0');
          END IF;
          v_result := v_result || ':';
          IF SUBSTRING(v_pBits,46,1)::INTEGER = 1 THEN
               v_result := v_result || LPAD (SUBSTRING(v_pBits, 47,  6)::INTEGER::TEXT, 2, '0');
          END IF;
          RETURN RTRIM(RTRIM(RTRIM(v_result,':'),'T'),'-');
      END IF;
END;
$$ language plpgsql;
   
CREATE OR REPLACE FUNCTION period2str (
      p_period_from DECIMAL(20),
      p_period_to   DECIMAL(20))
RETURNS TEXT 
AS $$
DECLARE
      v_from        TEXT;
      v_to          TEXT;
      v_result      TEXT;
BEGIN
      v_from   := dateTime2str(p_period_from);
      v_to     := dateTime2str(p_period_to);
      IF LENGTH(v_from) = 16 THEN
          IF SUBSTR(v_from, 1, 10) = SUBSTR(v_to, 1, 10) AND SUBSTR(v_from, 12, 5) = '00:00' AND SUBSTR(v_to, 12, 5) = '23:59' THEN
              v_from := SUBSTR(v_from, 1, 10);
              v_to   := SUBSTR(v_to  , 1, 10);
          ELSE
              IF SUBSTR(v_from, 1, 14) = SUBSTR(v_to, 1, 14) AND SUBSTR(v_from, 15, 2) = '00' AND SUBSTR(v_to, 15, 2) = '59' THEN
                  v_from := SUBSTR(v_from, 1, 13);
                  v_to   := SUBSTR(v_from, 1, 13);
              END IF;          
          END IF;          
      END IF;
      v_result := v_from;
      IF v_to IS NOT NULL AND v_to NOT IN (v_from, '') THEN
          v_result := v_result || ' - ' || v_to;
      END IF;
      RETURN v_result;
END;
$$ language plpgsql;

DROP VIEW xci_schedules_report;
DROP VIEW xci_schedules;

DROP VIEW xci_calendars_report;
DROP VIEW xci_calendars;

DROP VIEW xci_interval_hierarchy;

CREATE OR REPLACE VIEW 
       xci_interval_hierarchy
    AS WITH RECURSIVE tree AS (
       SELECT id, obj_id, ID AS master, 1 AS level
         FROM sci_intervall      
        UNION ALL                                                                                                                                                                               
       SELECT child.id, child.obj_id, parent.master, parent.level + 1 
         FROM tree parent
         JOIN sci_intervall child ON child.obj_id = parent.id                                                                                  
       )                                                                                                                                                                                     
SELECT *                                                                                                                                                           
  FROM tree;

CREATE OR REPLACE VIEW 
       xci_schedules
    AS SELECT se.id              AS job_id,
              /* se.name            AS job_name, */
              folder_path(se.id) AS job_name,
              sc.name            AS schedule,
              sc.time_zone,
              sc.active          AS active,
              d.seq_no + 1       AS subschedule,
              d.name             AS subschedule_name,
              si.start_time      AS "from",
              si.end_time        AS "to",
              CASE
                  WHEN d.is_active = 'TRUE' THEN 'ACTIVE'
                  ELSE 'INACTIVE'
              END AS subschedule_active,
              CASE
                  WHEN fi.name = 'FIX'
                  THEN NULL
                  ELSE fih.level 
              END                AS filter,
              CASE
                  WHEN fi.name = 'FIX'
                  THEN NULL
                  ELSE fi.name 
              END                AS filter_type,
              e.name             AS calendar,
              CASE
                  WHEN fi.name IN ('CLD', 'CLF')
                  THEN fi.base_interval
                  ELSE NULL 
              END AS calendar_select_on,
              CASE
                  WHEN fi.is_inverse = 'TRUE' THEN 'INVERSE'
                  ELSE 'NORMAL'
              END                AS select_mode,
              CASE 
                  WHEN fi.name = 'DOW' 
                  THEN CASE 
                          WHEN s.value = 1 THEN 'Monday'
                          WHEN s.value = 2 THEN 'Tuesday'
                          WHEN s.value = 3 THEN 'Wednesday'
                          WHEN s.value = 4 THEN 'Thursday'
                          WHEN s.value = 5 THEN 'Friday'
                          WHEN s.value = 6 THEN 'Saturday'
                          WHEN s.value = 7 THEN 'Sunday'
                      END
                  WHEN fi.name = 'TOD' 
                  THEN LPAD ((CAST (s.period_from::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_from::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0')
                  WHEN fi.name = 'ROD' 
                  THEN LPAD ((CAST (s.period_from::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_from::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0') || ' - ' ||
                       LPAD ((CAST (s.period_to::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_to::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0')
                  WHEN fi.name = 'WOM'
                  THEN (s.value / 7)::TEXT
                  ELSE s.value::TEXT
              END                AS selection,
              CASE
                  WHEN fi.name IN ('TOD', 'ROD')
                  THEN s.period_from
                  WHEN s.value < 0
                  THEN 1000 + s.value
                  ELSE s.value
              END                AS selection_sort,
              folder_path(se.id) AS job_sort
         FROM sci_c_scheduling_entity se,
              sci_schedule            sc,
              sci_intervall           hi,
              sci_interval_dispatcher d,
              sci_intervall           si,
              xci_interval_hierarchy  fih,
              sci_intervall           fi
         LEFT OUTER JOIN
              sci_intervall e ON e.id = fi.embedded_int_id
         LEFT OUTER JOIN
              sci_interval_selection s ON s.int_id = fi.id AND (fi.name != 'WOM' OR MOD(s.value, 7) = 0 )
        WHERE sc.se_id  = se.id
          AND hi.id     = sc.int_id
          AND d.int_id  = hi.id
          AND si.id     = d.select_int_id
          AND fih.master = d.filter_int_id
          AND fi.id     = fih.id;

CREATE OR REPLACE VIEW
       xci_schedules_report AS
       SELECT job_id,
              CASE WHEN rn_0 = 1 THEN job_name           ELSE NULL END AS job_name,
              CASE WHEN rn_1 = 1 THEN schedule           ELSE NULL END AS schedule,
              CASE WHEN rn_1 = 1 THEN time_zone          ELSE NULL END AS time_zone,
              CASE WHEN rn_1 = 1 THEN active             ELSE NULL END AS active,
              CASE WHEN rn_2 = 1 THEN subschedule        ELSE NULL END AS subschedule,
              CASE WHEN rn_2 = 1 THEN subschedule_name   ELSE NULL END AS subschedule_name,
              CASE WHEN rn_2 = 1 THEN "from"             ELSE NULL END AS "from",
              CASE WHEN rn_2 = 1 THEN "to"               ELSE NULL END AS "to",
              CASE WHEN rn_2 = 1 THEN subschedule_active ELSE NULL END AS subschedule_active,
              CASE WHEN rn_3 = 1 THEN filter             ELSE NULL END AS filter,
              CASE WHEN rn_3 = 1 THEN filter_type        ELSE NULL END AS filter_type,
              CASE WHEN rn_3 = 1 THEN calendar           ELSE NULL END AS calendar,
              CASE WHEN rn_3 = 1 THEN calendar_select_on ELSE NULL END AS calendar_select_on,
              CASE WHEN rn_3 = 1 THEN select_mode        ELSE NULL END AS select_mode,
              selection
         FROM (
              SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY job_name
                                            ORDER BY job_name, schedule, subschedule, filter, selection_sort) AS rn_0,
                     ROW_NUMBER() OVER (PARTITION BY job_name, schedule
                                            ORDER BY job_name, schedule, subschedule, filter, selection_sort) AS rn_1,
                     ROW_NUMBER() OVER (PARTITION BY job_name, schedule, subschedule
                                            ORDER BY job_name, schedule, subschedule, filter, selection_sort) AS rn_2,
                     ROW_NUMBER() OVER (PARTITION BY job_name, schedule, subschedule, filter
                                            ORDER BY job_name, schedule, subschedule, filter, selection_sort) AS rn_3
                FROM xci_schedules
              ) t
        ORDER BY
              job_sort, rn_0, rn_1, rn_2, rn_3, selection_sort;

CREATE OR REPLACE VIEW 
       xci_calendars
    AS SELECT hi.id            AS interval_id,
              hi.name          AS interval_name,
              d.seq_no + 1     AS dispatcher,
              d.name           AS dispatcher_name,
              CASE
                  WHEN d.is_enabled = 'TRUE' THEN 'ENABLED'
                  ELSE 'DISABLED'
              END              AS dispatcher_enabled,
              si.start_time    AS "from",
              si.end_time      AS "to",
              (SELECT name FROM sci_intervall WHERE id = (SELECT child_id from sci_interval_hierarchy WHERE parent_id = d.select_int_id)) AS select_interval,
              CASE
                  WHEN d.is_active = 'TRUE' THEN 'ACTIVE'
                  ELSE 'INACTIVE'
              END              AS dispatcher_active,
              CASE
                  WHEN fi.name = 'FIX'
                  THEN NULL
                  ELSE fih.level 
              END              AS filter,
              CASE
                  WHEN fi.name = 'FIX'
                  THEN NULL
                  ELSE fi.name 
              END              AS filter_type,
              fe.name          AS calendar,
              CASE
                  WHEN fi.name IN ('CLD', 'CLF')
                  THEN fi.base_interval
                  ELSE NULL 
              END              AS calendar_select_on,
              CASE
                  WHEN fi.is_inverse = 'TRUE' THEN 'INVERSE'
                  ELSE 'NORMAL'
              END              AS select_mode,
              CASE 
                  WHEN fi.name = 'DOW' 
                  THEN CASE 
                          WHEN s.value = 1 THEN 'Monday'
                          WHEN s.value = 2 THEN 'Tuesday'
                          WHEN s.value = 3 THEN 'Wednesday'
                          WHEN s.value = 4 THEN 'Thursday'
                          WHEN s.value = 5 THEN 'Friday'
                          WHEN s.value = 6 THEN 'Saturday'
                          WHEN s.value = 7 THEN 'Sunday'
                      END
                  WHEN fi.name = 'TOD' 
                  THEN LPAD ((CAST (s.period_from::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_from::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0')
                  WHEN fi.name = 'ROD' 
                  THEN LPAD ((CAST (s.period_from::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_from::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0') || ' - ' ||
                       LPAD ((CAST (s.period_to::BIGINT AS BIT(19))::INTEGER >> 14)::TEXT, 2, '0') || ':' || LPAD ((CAST (s.period_to::BIGINT AS BIT(13))::INTEGER >> 7)::TEXT, 2, '0')
                  WHEN fi.name = 'WOM'
                  THEN (s.value / 7)::TEXT
                  WHEN fi.name = 'LST'
                  THEN period2str(s.period_from, period_to)
                  ELSE s.value::TEXT
              END              AS selection,
              CASE
                  WHEN fi.name IN ('TOD', 'ROD')
                  THEN s.period_from
                  WHEN s.value < 0
                  THEN 1000 + s.value
                  ELSE s.value
              END              AS selection_sort,
              hi.name    AS interval_sort
         FROM sci_intervall           hi,
              sci_interval_dispatcher d,
              sci_intervall           si,
              xci_interval_hierarchy  fih,
              sci_intervall           fi
         LEFT OUTER JOIN
              sci_intervall fe ON fe.id = fi.embedded_int_id
         LEFT OUTER JOIN
              sci_interval_selection s ON s.int_id = fi.id AND (fi.name != 'WOM' OR MOD(s.value, 7) = 0 )
        WHERE hi.se_id   = 0
          AND d.int_id   = hi.id
          AND si.id      = d.select_int_id
          AND fih.master = d.filter_int_id
          AND fi.id      = fih.id;

CREATE OR REPLACE VIEW
       xci_calendars_report AS
       SELECT interval_id,
              CASE WHEN rn_0 = 1 THEN interval_name      ELSE NULL END AS interval_name,
              CASE WHEN rn_1 = 1 THEN dispatcher         ELSE NULL END AS dispatcher,
              CASE WHEN rn_1 = 1 THEN dispatcher_name    ELSE NULL END AS dispatcher_name,
              CASE WHEN rn_1 = 1 THEN dispatcher_enabled ELSE NULL END AS dispatcher_enabled,
              CASE WHEN rn_1 = 1 THEN "from"             ELSE NULL END AS "from",
              CASE WHEN rn_1 = 1 THEN "to"               ELSE NULL END AS "to",
              CASE WHEN rn_1 = 1 THEN select_interval    ELSE NULL END AS select_interval,
              CASE WHEN rn_1 = 1 THEN dispatcher_active  ELSE NULL END AS dispatcher_active,
              CASE WHEN rn_2 = 1 THEN filter             ELSE NULL END AS filter,
              CASE WHEN rn_2 = 1 THEN filter_type        ELSE NULL END AS filter_type,
              CASE WHEN rn_2 = 1 THEN calendar           ELSE NULL END AS calendar,
              CASE WHEN rn_2 = 1 THEN calendar_select_on ELSE NULL END AS calendar_select_on,
              CASE WHEN rn_2 = 1 THEN select_mode        ELSE NULL END AS select_mode,
              selection
         FROM (
              SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY interval_name
                                            ORDER BY interval_name, dispatcher, filter, selection_sort) AS rn_0,
                     ROW_NUMBER() OVER (PARTITION BY interval_name, dispatcher
                                            ORDER BY interval_name, dispatcher, filter, selection_sort) AS rn_1,
                     ROW_NUMBER() OVER (PARTITION BY interval_name, dispatcher, filter
                                            ORDER BY interval_name, dispatcher, filter, selection_sort) AS rn_2
                FROM xci_calendars
              ) t
        ORDER BY
              interval_sort, rn_0, rn_1, rn_2, selection_sort;

