/*
 | Job Scheduler Reporting Interface cleanup procedure for PostgreSQL
 | SOS GmbH, 2017-02-24
 | 
 | Works for PostgreSQL version 8.4 or higher
 |
 | Usage
 | 1) Remove entries older as n (14) days
 | 1.1) Remove all entries (reporting, dailyplan, yade)
 |      SELECT REPORT_CLEANUP('all',14); 
 | 1.2) Remove only reporting entries
 |      SELECT REPORT_CLEANUP('reporting',14); 
 | 1.3) Remove only dailyplan entries
 |      SELECT REPORT_CLEANUP('dailyplan',14); 
 | 1.4) Remove only yade entries
 |      SELECT REPORT_CLEANUP('yade',14); 
 | 2) Remove all entries
 | 2.1) Remove all entries (reporting, dailyplan, yade)
 |      SELECT REPORT_CLEANUP('all',0); 
 | 2.2) Remove only reporting entries
 |      SELECT REPORT_CLEANUP('reporting',0); 
 | 2.3) Remove only dailyplan entries
 |      SELECT REPORT_CLEANUP('dailyplan',0); 
 | 2.4) Remove only yade entries
 |      SELECT REPORT_CLEANUP('yade',0);  
*/
CREATE OR REPLACE FUNCTION REPORT_CLEANUP(cleanupRange VARCHAR, age NUMERIC) 
RETURNS void AS $$
DECLARE endDatetime TIMESTAMP;
BEGIN
	IF age <= 0 THEN
		IF cleanupRange = 'all' OR cleanupRange = 'reporting' THEN
            TRUNCATE TABLE REPORTING_TRIGGERS;
            ALTER SEQUENCE REPORTING_SEQ_RT RESTART WITH 1;
		 
            TRUNCATE TABLE REPORTING_EXECUTIONS; 
            ALTER SEQUENCE REPORTING_SEQ_RE RESTART WITH 1;
		
            TRUNCATE TABLE REPORTING_TASKS; 
            ALTER SEQUENCE REPORTING_SEQ_RTS RESTART WITH 1;
            
            TRUNCATE TABLE REPORTING_EXECUTION_DATES;
            ALTER SEQUENCE REPORTING_SEQ_RED RESTART WITH 1;
		END IF;
        
        IF cleanupRange = 'all' OR cleanupRange = 'dailyplan' THEN
            TRUNCATE TABLE DAILY_PLAN;
            ALTER SEQUENCE DAILY_PLAN_ID_SEQ RESTART WITH 1;
        END IF;
            
        IF cleanupRange = 'all' OR cleanupRange = 'yade' THEN
            TRUNCATE TABLE JADE_FILES;
            ALTER SEQUENCE JADE_FILES_ID_SEQ RESTART WITH 1;
          
            TRUNCATE TABLE JADE_FILES_HISTORY;
            TRUNCATE TABLE JADE_FILES_POSITIONS;                
        END IF;
        
	ELSE
		endDatetime := CURRENT_DATE-(age-1)*INTERVAL '1 day'; -- yyyy-mm-dd 00:00:00.000
		
        IF cleanupRange = 'all' OR cleanupRange = 'reporting' THEN
            DELETE FROM REPORTING_EXECUTION_DATES
            WHERE "REFERENCE_TYPE" = 0
            AND "REFERENCE_ID" IN (SELECT "ID" FROM REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime);   
            
            DELETE FROM REPORTING_EXECUTION_DATES
            WHERE "REFERENCE_TYPE" = 1 AND "REFERENCE_ID" IN
            (SELECT "ID" FROM REPORTING_EXECUTIONS WHERE "TRIGGER_ID" IN 
                (SELECT "ID" from REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime));
                
            DELETE FROM REPORTING_EXECUTION_DATES
            WHERE "REFERENCE_TYPE" = 2
            AND "REFERENCE_ID" IN (SELECT "ID" FROM REPORTING_TASKS WHERE "START_TIME" < endDatetime);   
                
            DELETE FROM REPORTING_EXECUTIONS
            WHERE "TRIGGER_ID" IN (SELECT "ID" from REPORTING_TRIGGERS WHERE "START_TIME" < endDatetime);
            
            DELETE FROM REPORTING_TRIGGERS
            WHERE "START_TIME" < endDatetime;  

            DELETE FROM REPORTING_TASKS
            WHERE "START_TIME" < endDatetime;
        END IF;
        
        IF cleanupRange = 'all' OR cleanupRange = 'dailyplan' THEN
            DELETE FROM DAILY_PLAN
            WHERE "PLANNED_START" < endDatetime; 
        END IF;
            
        IF cleanupRange = 'all' OR cleanupRange = 'yade' THEN
            DELETE FROM JADE_FILES
            WHERE "ID" IN (SELECT "JADE_ID" FROM JADE_FILES_HISTORY WHERE "CREATED" < endDatetime); 
                
            DELETE FROM JADE_FILES_HISTORY
            WHERE "CREATED" < endDatetime;
        END IF;
        
	END IF;
END;
$$ LANGUAGE plpgsql;

COMMIT;
