-- ==================================================================
/**
  Purpose:

    Purpose of this procedure to rollup the stats data at third interval
            to the fourth interval which defined at vpx_interval_def table
            It happens every day which is the length of the fouth interval.
            Out of the box the interval is defined as following
            Interval Name       Interval Value    Interval Length
            5 mins Interval       300                86400
            30 mins Interval      1800               604800
            2 hrs Interval        6400               2592000
            1 day Interval        86400              31536000
*/
--
-- ==================================================================

CREATE OR REPLACE FUNCTION stats_rollup3_proc()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
AS
$BODY$
DECLARE
  l_time_cur cursor (current_time_in timestamp without time zone) for
          select sample_time, partition_index
            from vpx_sample_time3
           where rollup_counter is null
             and sample_time < current_time_in - '1 hour'::interval
           order by 1 asc
           limit 18;

  l_time                      vpx_sample_time3.sample_time%type;
  l_time_rowid                vpx_sample_time4.time_id%type;
  l_cnt                       int;
  l_rollup_counter            vpx_sample_time4.rollup_counter%type;
  l_start_time_id             vpx_sample_time3.time_id%type;
  l_rollup_start_time         vpx_sample_time3.sample_time%type;
  l_rollup_flag               numeric(1):=1;
  l_next_interval             numeric(10,0);
  l_current_interval          numeric(10,0);
  l_stat_rollup_level         numeric(10,0);
  l_null_device_id            numeric(4);
  l_last_sample_time          vpx_sample_time3.sample_time%type;
  l_source_interval_seq_num   numeric(1) := 3;

  l_hs1_partition_index        vpx_sample_time3.partition_index%type;
  l_hs1_frompartitionidxclause varchar(255);
  l_hs2_frompartitionidxclause varchar(255);
  l_previous_time              vpx_sample_time3.sample_time%type;
  l_next_time                  vpx_sample_time3.sample_time%type;
  l_last_part2                 vpx_sample_time3.partition_index%type;
  v_errmsg                     vpx_proc_log.description%type;
  v_err_log                    CHAR(3);

BEGIN

l_rollup_start_time := timezone('UTC', clock_timestamp());

-- Get DB procedures loging
select COALESCE(value,'OFF') into v_err_log
   from vpx_parameter
   where name = 'DBProc.Log.Debug.Info';

--check if rollup has been turnoff by user.
--hardcode the interval_seq_num here as there will be 3 differenct procedures
--rolup on vpx_hist_stat1, vpx_hist_stat2, vpx_hist_stat3. The table name will be hardcoded in these
--procedures so as the interval seq number.
select rollup_enabled_flg into l_rollup_flag
  from vpx_stat_interval_def
 where interval_seq_num = l_source_interval_seq_num + 1;

select max(sample_time) into l_last_sample_time
  from vpx_sample_time3
 where rollup_counter is null;

if (l_rollup_flag = 1) and (l_last_sample_time is not null) then
  select interval_val into l_current_interval
    from vpx_stat_interval_def
   where interval_seq_num = l_source_interval_seq_num;

  select stats_level, interval_val into l_stat_rollup_level, l_next_interval
    from vpx_stat_interval_def
   where interval_seq_num = l_source_interval_seq_num + 1;

  SELECT DEVICE_ID
  INTO l_null_device_id
  FROM VPX_DEVICE
  WHERE DEVICE_NAME = '';
-----------------
  -- Initializing HS4_PI(runs only once - first run of this procedure)
   select count(*) into l_cnt
     from vpx_sample_time3
    where rollup_counter is not null and
      partition_index_hs4 is not null;

   -- If no row found for RC=NULL and HS4PI=NULL,
   -- then set PARTITION_INDEX_HS4 =1
   IF l_cnt = 0 then
      --Update HS3_PI=1, for all the HS2_PI values within the min(sample_time)
      UPDATE VPX_SAMPLE_TIME3
         SET PARTITION_INDEX_HS4 = 1
       WHERE PARTITION_INDEX =
               (SELECT PARTITION_INDEX
                  FROM VPX_SAMPLE_TIME3
                 WHERE SAMPLE_TIME =
                        (SELECT MIN(SAMPLE_TIME)
                           FROM VPX_SAMPLE_TIME3
                          WHERE ROLLUP_COUNTER IS NULL
                            AND SAMPLE_TIME < l_rollup_start_time - '1 hour'::interval));
         l_last_part2 := 1;
   END IF;

   l_cnt:=1;
   open l_time_cur(l_rollup_start_time);

   loop
    fetch l_time_cur into l_time, l_hs1_partition_index;
    if not found then
      exit;
    end if;

    -- Execute for all runs:
    -- Figure out which is the next destination partition index
    -- comparing the difference between the "first sample_time for last partition index4"
    -- and the "next sample_time we will have to rollup now"

    -- Get max(sample_time) from previous run, and get the previous PI# for it
    select partition_index_hs4 into l_last_part2
      from vpx_sample_time3
     where sample_time = (select max(sample_time)
                            from vpx_sample_time3
                           where partition_index_hs4 is not null);

    --Using the previous PI#, get the min(sample_time).
    select min(sample_time) into l_previous_time
      from vpx_sample_time3
     where partition_index_hs4 = l_last_part2;

    select min(sample_time) into l_next_time
      from vpx_sample_time3
     where rollup_counter is null
       and sample_time < l_rollup_start_time - '60 min'::interval;

    if l_next_time - '10 day'::interval >= l_previous_time then
       l_last_part2 := l_last_part2 + 1;
    end if;

    -- VPX_HIST_STAT4 table is splited into 183 tables (custom partitions)
    -- for every 10 days window and a view over them.
    -- So we have to cycle destination partitions during rollup on time basis
    if l_last_part2 > 183 then
       l_last_part2 := 1;
    end if;

     --record the rollup_counter for this time sample and its time_id
     select rollup_counter, time_id
       into l_rollup_counter, l_start_time_id
       from vpx_sample_time3
      where sample_time = l_time;

      l_time:=date_trunc('day',l_time);

     -- in case of long non-rolluped HS3 (e.g. for some reasons the job will have consecutive failures)
     -- apply the same cycle logic for the destination HS4 table
     if l_time - '10 day'::interval >= l_next_time then
        l_last_part2 := l_last_part2 + 1;
        l_next_time := l_time;
        if l_last_part2 > 183 then
           l_last_part2 := 1;
        end if;
     end if;

     l_hs1_fromPartitionIdxClause := 'VPX_HIST_STAT3_' || TO_CHAR(l_hs1_partition_index, 'FM9999');
     l_hs2_fromPartitionIdxClause := 'VPX_HIST_STAT4_' || TO_CHAR(l_last_part2, 'FM9999');

    --check if this sample time has been updated by previous bulk rollup_counter update.
    --check between the time which is 1 hour prior to this rollup and the time of this cursor sample time
    -- the difference is more than 30mins in this case which is next interval, if not, it is time to
    --end the rollup and commit previous transactions.
    if  l_rollup_counter is null
        and (l_last_sample_time + (l_current_interval || ' sec')::interval) >= (l_time + (l_next_interval || ' sec')::interval) then

        --tag the sample time data start from currect cursor sample time record and rest of sample time record which is within l_next_interval
        update vpx_sample_time3
           set rollup_counter = (l_start_time_id::text || l_cnt)::numeric(38,0),
               partition_index_hs4 = l_last_part2
         where sample_time < l_time + (l_next_interval || ' sec')::interval
           and sample_time >= l_time;

        select time_id
          into l_time_rowid
          from vpx_sample_time4
         where sample_time = l_time
           and sample_interval=l_next_interval;


        if not found then
          select NEXTVAL('VPX_SAMPLE_TIME4_SEQ')
            into l_time_rowid;
          insert into vpx_sample_time4
                 (time_id, sample_time, sample_interval, partition_index)
          values
                 (l_time_rowid, l_time, l_next_interval, l_last_part2);
        end if;


        --ROLLUP based on the ROLLUP_TYPE:
        --when 0 then 'average'
        --when 1 then 'maximum'
        --when 2 then 'minimum'
        --when 3 then 'latest'
        --when 4 then 'summation'
        --when 5 then 'none'
        begin
          execute '
          insert into '||l_hs2_fromPartitionIdxClause||'(counter_id, time_id, stat_val)
          SELECT st.counter_id, $1, avg(st.stat_val)
            FROM vpx_sample_time3 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
           WHERE sm.rollup_counter = ($2::text || $3)::numeric(38,0)
             AND st.time_id = sm.time_id
             AND st.counter_id = SC.COUNTER_ID
             AND SC.STAT_ID = SD.ID
             AND ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                  or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
             AND SD.ROLLUP_TYPE = 0
           group BY st.counter_id'
          using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
        exception
          when OTHERS then
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              if v_err_log = 'ON' then
                 INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                      VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
              end if;
        end;

        begin
          execute '
          insert into '||l_hs2_fromPartitionIdxClause||'
          (counter_id, time_id, stat_val)
          SELECT  st.counter_id, $1, max(st.stat_val)
          FROM    vpx_sample_time3 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
          WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
            AND   st.time_id = sm.time_id
            AND   st.counter_id = SC.COUNTER_ID
            AND   SC.STAT_ID = SD.ID
            AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                    or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
            AND   SD.ROLLUP_TYPE = 1
          group BY st.counter_id'
          using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
        exception
          when OTHERS then
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              if v_err_log = 'ON' then
                 INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                      VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
              end if;
        end;

        begin
          execute '
          insert into '||l_hs2_fromPartitionIdxClause||'
          (counter_id, time_id, stat_val)
          SELECT  st.counter_id, $1, MIN(st.stat_val)
          FROM    vpx_sample_time3 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
          WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
            AND   st.time_id = sm.time_id
            AND   st.counter_id = SC.COUNTER_ID
            AND   SC.STAT_ID = SD.ID
            AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                    or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
            AND   SD.ROLLUP_TYPE = 2
          group BY st.counter_id'
          using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
        exception
          when OTHERS then
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              if v_err_log = 'ON' then
                 INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                      VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
              end if;
        end;

        begin
          execute '
          insert into '||l_hs2_fromPartitionIdxClause||'
          (counter_id, time_id, stat_val)
          SELECT  COUNTER_ID, TIME_ID, STAT_VAL
          FROM    (
                  SELECT
                        st.counter_id AS COUNTER_ID
                        ,$1 AS TIME_ID
                        ,ST.STAT_VAL AS STAT_VAL
                        ,DENSE_RANK()
                        OVER (PARTITION BY ST.COUNTER_ID ORDER BY SM.SAMPLE_TIME DESC) AS DENSE_RANK
                  FROM  vpx_sample_time3 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
                  WHERE sm.rollup_counter = ($2::text || $3)::numeric(38,0)
                    AND st.time_id = sm.time_id
                    AND st.counter_id = SC.COUNTER_ID
                    AND SC.STAT_ID = SD.ID
                    AND ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                          or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
                    AND SD.ROLLUP_TYPE = 3) as hist_stat2
          WHERE DENSE_RANK = 1'
          using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
        exception
          when OTHERS then
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              if v_err_log = 'ON' then
                 INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                      VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
              end if;
        end;

        begin
          execute '
          insert into '||l_hs2_fromPartitionIdxClause||'
          (counter_id, time_id, stat_val)
          SELECT  st.counter_id, $1, SUM(st.stat_val)
          FROM    vpx_sample_time3 sm,vpx_stat_counter sc, vpx_stat_def sd, '||l_hs1_fromPartitionIdxClause||' st
          WHERE   sm.rollup_counter = ($2::text || $3)::numeric(38,0)
            AND   st.time_id = sm.time_id
            AND   st.counter_id = SC.COUNTER_ID
            AND   SC.STAT_ID = SD.ID
            AND   ((SC.DEVICE_ID = $4 and SD.STAT_LEVEL <= $5)
                    or (SC.DEVICE_ID <> $6 and SD.PERDEVICE_STAT_LEVEL <= $7))
            AND   SD.ROLLUP_TYPE = 4
          group BY st.counter_id'
          using l_time_rowid, l_start_time_id, l_cnt, l_null_device_id, l_stat_rollup_level, l_null_device_id, l_stat_rollup_level;
        exception
          when OTHERS then
              v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
              if v_err_log = 'ON' then
                 INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                      VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
              end if;
        end;

        l_cnt:=l_cnt+1;

      end if;

      end loop;
    close l_time_cur;


ELSE
     NULL;
END IF;
--
EXCEPTION
  WHEN OTHERS THEN
    PERFORM 1
    FROM    pg_cursors
    WHERE   name = 'l_time_cur';

    IF FOUND THEN
      CLOSE l_time_cur; -- close the cursor no matter what happens
    END IF;
    v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
    if v_err_log = 'ON' then
        INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
           VALUES ('STATS_ROLLUP3_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
    end if;
end;
$BODY$
