sqlplus -s $DB_USER/$DB_PASSWORD << EOF >> $RANGERHOME/LOG/fraudulentsummary.log 2>&1

whenever sqlerror exit 5;
whenever oserror exit 5 ;
set serveroutput on;

DECLARE
        Last_processed_Alarm_id NUMBER(10)  ;
        Previous_BlackList_Close_Date date;
        Total_Value                     NUMBER(16, 6) ;
        Num_of_Days                     NUMBER ;
        AvgSubscriberUsage      NUMBER(16, 6) ;
        First_CDR_Time          Date ;
    rs_str          varchar2(200) ;

    CURSOR DATA_STREAM is
        select distinct tname, ID from record_configs where id in (select record_config_id from record_configs_rules) ;

    CURSOR Black_Listed_Subscribers (Last_processed_BlackList Date)  IS
                        SELECT S.ACCOUNT_NAME, S.PHONE_NUMBER, S.NETWORK_ID, A.VALUE, A.CREATED_DATE, A.MODIFIED_DATE, A.REFERENCE_ID AS SUBSCRIBER_ID, A.ID, A.DISPLAY_VALUE AS FRAUD_TYPE
                        FROM SUBSCRIBER S, ALARMS A
                        WHERE S.SUBSCRIBER_TYPE = 1
                        AND S.ID = A.REFERENCE_ID
                        AND A.REFERENCE_TYPE=1
                        AND A.MODIFIED_DATE >= to_date(to_char(Last_processed_BlackList,'dd-mm-yyyy hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss')
                        ORDER BY MODIFIED_DATE ASC;

    BEGIN

            DELETE FROM fraudulent_info
                WHERE alarm_modified_date < (SYSDATE - (SELECT value FROM configurations WHERE config_key='CLEANUP.ALARMS.INTERVAL_IN_DAYS'));

            BEGIN
                SELECT VALUE INTO Last_processed_Alarm_id  FROM Configurations WHERE CONFIG_KEY = 'LAST_PROCESSED_BLACKLIST_ALARM_ID' ;
                EXCEPTION
                    WHEN OTHERS THEN
                    Last_processed_Alarm_id := 1025 ;
            END ;

            BEGIN
                SELECT modified_date INTO Previous_BlackList_Close_Date  FROM ALARMS  WHERE ID = Last_processed_Alarm_id and status=2;
                EXCEPTION
                    WHEN OTHERS THEN
                    SELECT to_date ('01/01/1970' ,'dd/mm/yyyy') INTO Previous_BlackList_Close_Date FROM DUAL ;
            END ;
	FOR subscriber IN Black_Listed_Subscribers (Previous_BlackList_Close_Date)
            LOOP
                AvgSubscriberUsage := 0 ;
                
                FOR datastream IN DATA_STREAM
                LOOP

                BEGIN

                    dbms_output.put_line('tname '|| datastream.tname) ;
                    EXECUTE IMMEDIATE 'SELECT trunc(SUM(Value)/(COUNT(DISTINCT Day_of_Year)),4) FROM (SELECT day_of_year AS Day_of_Year,SUM(value) AS Value FROM :1 WHERE phone_number = subscriber.Phone_Number AND subscriber_id = subscriber.subscriber_id GROUP BY day_of_year)'
                    into Total_value using datastream.tname ;

                    dbms_output.put_line('Total_value' ||Total_value) ;

                    AvgSubscriberUsage := AvgSubscriberUsage + Total_Value ;

                INSERT INTO FRAUDULENT_INFO (ACCOUNT_NAME, PHONE_NUMBER, NETWORK_ID , FRAUD_TYPE, ALARM_MODIFIED_DATE, AVERAGE_USAGE_PER_DAY, FIRST_CDR_TIME, ALARM_CREATED_DATE, ALARM_VALUE) VALUES (subscriber.Account_name, subscriber.Phone_number, subscriber.network_id , subscriber.Fraud_Type, subscriber.modified_date, AvgSubscriberUsage, sysdate, subscriber.Created_Date, 0) ;

                EXCEPTION
                    WHEN others THEN
                        NULL;

                END ;

                END LOOP ;
            END LOOP ;
    commit;
    END ;
/