CREATE OR REPLACE FUNCTION rule_topn2_proc()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
-----------------------------------------------------------------------------
-- Procedure	: rule_topn2_proc
-- Description	: This procedure controls processing and retention of weekly
--                Top N Ranking values.
--                Should be run from job.
-- Parameters   : no
-----------------------------------------------------------------------------

AS $procedure$
    /*variables declaration*/
   DECLARE
   v_err_log     char(3);
   v_err         char(1) := 'N';
   v_errmsg      vpx_proc_log.description%type;

   v_status      vpx_job_log.status%type;
   v_recovery_period  smallint;
   v_last_run    vpx_job_log.last_run%type;
   v_last_stat   vpx_sample_time1.sample_time%type;
   v_job_id      int;
   v_log_rtn     int;
   v_time_level       smallint;
BEGIN
   v_job_id             := 2;
   v_time_level         := 2;
   v_recovery_period    := 90;

   SELECT COALESCE(value,'OFF') into v_err_log
   FROM vpx_parameter
   WHERE name = 'DBProc.Log.Debug.Info';

   if v_err_log = 'ON' then
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('RULE_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   end if;


   -- Step 1. Check whether the same job is currently runing
   select status into v_status
   from vpx_job_log
   where job_id = v_job_id;

   -- If yes - do nothing
   if v_status = 'RUNNING' then
      RETURN;
   end if;


    -- Get the last_processed_date,meaning when was the job last run
   SELECT SAMPLE_TIME into v_last_run
     FROM VPX_TOPN_STATE
    WHERE TIME_LEVEL = v_time_level;


   --Get the lastest_time, data that is not processed yet
   SELECT max(sample_time) - (v_recovery_period || ' min')::interval into v_last_stat
   FROM vpx_sample_time2;
   IF v_last_stat IS NULL THEN
      if v_err_log = 'ON' then
          INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
             VALUES ('RULE_TOPN2_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'vpx_sample_time2 is empty.');
      end if;
      return;
   END IF;


   -- when v_last_run is null, set v_last_run =(v_last_stat -v_recovery_period) -(happens @ VC initial start)
   v_last_run := coalesce(v_last_run,v_last_stat -(v_recovery_period || ' min')::interval);


    /*
    If the difference between v_last_stat and v_last_run is GTE v_recovery_period,
        set v_last_run= v_last_stat -v_recovery_period
    */
   IF v_last_run <= v_last_stat - (v_recovery_period || ' min')::interval then
      v_last_run := v_last_stat - (v_recovery_period || ' min')::interval;
   END IF;


  --If not in RUNNING state, set the job status to running now
   UPDATE vpx_job_log
   SET status = 'RUNNING'
   WHERE job_id = v_job_id;

   if v_err_log = 'ON' then
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('CALC_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   end if;

   BEGIN
      PERFORM calc_topn2_proc(v_last_run,v_last_stat);
      EXCEPTION
      WHEN OTHERS THEN
         v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
         v_err := 'Y';
          if v_err_log = 'ON' then
             INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
               VALUES ('CALC_TOPN2_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
         end if;
   END;

   if v_err_log = 'ON' then
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('CALC_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
   end if;

   -- Mark job as waiting
   UPDATE vpx_job_log
   SET status = 'WAITING',last_run = COALESCE(v_last_stat,v_last_run)
   WHERE job_id = v_job_id;

   v_err := 'N';
   -- Step 5. Clear expired daily integrals
    -- Check what to log for clear topn procedure execution from VPX_PARAMETER


    if v_err_log = 'ON' then
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('CLEAR_TOPN2_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   end if;

   BEGIN
      PERFORM clear_topn2_proc();
      EXCEPTION
      WHEN OTHERS THEN
         v_errmsg := 'Error:' || cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, '');
         v_err := 'Y';
          if v_err_log = 'ON' then
             INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
               VALUES ('CLEAR_TOPN2_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, SUBSTR(v_errmsg, 1, 1000));
         end if;
   END;

    if v_err_log = 'ON' then
     INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
          VALUES ('CLEAR_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'End procedure');
   end if;

   -- COMMIT
   if v_err_log = 'ON' then
       INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('RULE_TOPN2_PROC','END', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
   end if;

EXCEPTION
   WHEN OTHERS THEN

      UPDATE vpx_job_log
      SET status = 'WAITING'
      WHERE job_id = v_job_id;
      -- COMMIT
      if v_err_log = 'ON' then
          INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
            VALUES ('RULE_TOPN2_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, 'Error:' || SUBSTR(cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, ''), 1, 1000));
      end if;
END;
$procedure$
