CREATE OR REPLACE FUNCTION rule_topn1_proc()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
-----------------------------------------------------------------------------
-- Procedure	: rule_topn1_proc
-- Description	: This procedure controls processing and retention of daily
--                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

    BEGIN
       v_job_id             := 1;
       v_time_level         := 1;
       v_recovery_period    := 30;


       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_TOPN1_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
           if v_err_log = 'ON' then
              INSERT INTO VPX_PROC_LOG (PROC_NAME, EVENT, EVENT_DT, ERR_FLAG, DESCRIPTION)
                  VALUES ('RULE_TOPN1_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'The job is running.');
           end if;
           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_time1;
       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_TOPN1_PROC','EXIT', TIMEZONE('UTC',clock_timestamp()), 0, 'vpx_sample_time1 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_TOPN1_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
       end if;

       BEGIN
          PERFORM calc_topn1_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_TOPN1_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_TOPN1_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_TOPN1_PROC','START', TIMEZONE('UTC',clock_timestamp()), 0, 'Start procedure');
       end if;


       BEGIN
          PERFORM clear_topn1_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_TOPN1_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_TOPN1_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_TOPN1_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_TOPN1_PROC','ERR', TIMEZONE('UTC',clock_timestamp()), -1, 'Error:' || SUBSTR(cast(sqlstate as VARCHAR(30)) || ' :' || coalesce(SQLERRM, ''), 1, 1000));
          end if;
          return;
    END;

END;
$procedure$
