CREATE OR REPLACE  PACKAGE BODY "PURGE_METHODS"  is
/**********************************************************************/
/*** Purge Objects                                                  ***/
/***                                                                ***/
/*** Deletes objects marked for deletion in child object tables and ***/
/*** then the master objects table based on date value              ***/
/**********************************************************************/

  FUNCTION Purge_objects
    (date_in       in objects.deleted_date%type)
  RETURN PLS_INTEGER
  IS
    v_count PLS_INTEGER := 0;

  Obj_list Obj_list_t;  -- VARRAY object to hold ids to bulk delete for child objects
  Obj_rowid_list Obj_rowid_list_t;  -- VARRAY object to hold ids to bulk delete for parent objects
  Obj_list_index PLS_INTEGER; -- index into VARRAY object 
    
  BEGIN
  
  Obj_list := Obj_list_t();   -- initialize VARRAY object
  Obj_list.extend(chunk_size); -- allocate VARRAY object memory to chunk size
  
  LOGGING.LOG_STATUS(1,'Starting child object deletions.');
  
  --open the cursor for the object ids we want to purge.
  --we will use these ids to delete child records from other tables
    FOR x IN (SELECT /*+ INDEX (OBJECTS DELETED_OBJECT_IDX) */ object_id
              FROM objects
              WHERE deleted_date < date_in) LOOP
        
       v_count := v_count + 1;     
       Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
       
       If Obj_list_index = 0 then 
          Obj_list(chunk_size) := x.object_id; -- make sure we fill last VARRAY element          
          Purge_objects_work(Obj_list); -- bulk delete object ids in VARRAY list
          COMMIT;
          LOGGING.LOG_STATUS(v_count,'batch purged');
       Else
          Obj_list(Obj_list_index) := x.object_id; -- fill VARRAY with ids
       End If;
       
    END LOOP;
    
  LOGGING.LOG_STATUS(v_count,'Child object main loop exited.');    
        
    --Now delete remaining partially full object list for child objects
    If Obj_list_index != 0 then
        Obj_list.trim(chunk_size - Obj_list_index);
        Purge_objects_work(Obj_list); 
        COMMIT;
        LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
    End If;   
    
  Obj_list.delete; -- cleanup - delete VARRAY list  
        
  LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' references to child objects.');      
        
    --Now delete parent objects
    v_count := 0; -- reinitialize
    Obj_rowid_list := Obj_rowid_list_t();  -- initialize VARRAY object
    Obj_rowid_list.extend(chunk_size); -- allocate VARRAY memory at chunk size   
  
  LOGGING.LOG_STATUS(1,'Starting parent object deletions.');  
    
    FOR x IN (SELECT /*+ INDEX (OBJECTS DELETED_OBJECT_IDX) */ ROWID
              FROM objects
              WHERE deleted_date < date_in) LOOP
   
       v_count := v_count + 1;     
       Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
       
       If Obj_list_index = 0 then
          Obj_rowid_list(chunk_size) := x.ROWID; -- make sure we fill last VARRAY element      
          FORALL i IN Obj_rowid_list.First..Obj_rowid_list.Last DELETE FROM objects WHERE ROWID = Obj_rowid_list(i); --bulk delete
          COMMIT;
       Else
          Obj_rowid_list(Obj_list_index) := x.ROWID;  -- fill VARRAY with ids       
       End If;

    END LOOP;
    
    LOGGING.LOG_STATUS(v_count,'Parent object main loop exited.');
    
    --Now delete remaining partially full object list for parent objects
    If Obj_list_index != 0 then
        Obj_rowid_list.trim(chunk_size - Obj_list_index);
        FORALL i IN Obj_rowid_list.First..Obj_rowid_list.Last DELETE FROM objects WHERE ROWID = Obj_rowid_list(i);
        COMMIT;
        LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
    End If;    

  Obj_rowid_list.delete; -- cleanup - delete VARRAY list 
   
  LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' parent objects.');      
  RETURN 0;

    EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      
      IF v_count = 0 THEN
        dbms_output.put_line ('never deleted');
        LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
      END IF;
      dbms_output.put_line (v_count||': iterations');
      LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
      RETURN SQLCODE;
  END Purge_objects;

/**********************************************************************/
/*** Purge Objects Work                                             ***/
/***                                                                ***/
/*** Bulk delete objects marked for deletion in child object tables ***/
/*** given a list of object ids passed in from a VARRAY. Called     ***/
/*** purge objects function.                                        ***/
/**********************************************************************/
 
  PROCEDURE Purge_objects_work
    (Obj_list IN Obj_list_t) IS

  BEGIN

          --delete all children
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM armor WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM attributes WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_marker_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_participants WHERE region_object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_participants WHERE character_object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM biographies WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM building_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM cell_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM city_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM creature_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM experience_points WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM factory_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM guild_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM harvester_installation_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM installation_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM intangible_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM location_lists WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manf_schematic_attributes WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manf_schematic_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM manufacture_inst_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM mission_objects WHERE object_id = Obj_list(i);

          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM object_variables WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM planet_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM player_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM property_lists WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_container_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_pool_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_type_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM scripts WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM static_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM tangible_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM token_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM universe_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM vehicle_objects WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM waypoints WHERE object_id = Obj_list(i);
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM weapon_objects WHERE object_id = Obj_list(i);
          --delete messages target(object_id)
          FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM messages WHERE target = Obj_list(i);
      
  END Purge_objects_work;
  --
  --
  --
  FUNCTION time_purge
    (date_in          IN VARCHAR2)
  RETURN PLS_INTEGER IS
    v_retval PLS_INTEGER := 0;
    v_date VARCHAR2(20) := date_in;
    v_time NUMBER := 1;
  BEGIN

    FOR i IN 1..24 LOOP
      dbms_output.put_line(to_char(to_date(v_date, 'dd-mon-yy hh24:mi:ss') + v_time/24, 'DD-MON-YY hh24:mi:ss'));
      v_retval := purge_objects(to_date(v_date, 'dd-mon-yy hh24:mi:ss') + v_time/24);
      EXIT WHEN v_retval <> 0;

      IF v_time = 23 THEN
       v_time := v_time + (59/60);
      ELSE
        v_time := v_time + 1;
      END IF;
    END LOOP;

    RETURN 0;

    EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      dbms_output.put_line('Purge return values: '||v_retval);
      dbms_output.put_line('Time it failed at: '||v_time);
      RETURN SQLCODE;
  END time_purge;

/**********************************************************************/
/*** Purge Consumables                                              ***/
/***                                                                ***/
/*** Deletes consumable objects marked for deletion in child object ***/
/*** tables and then the master objects table based on date value   ***/
/**********************************************************************/

  FUNCTION Purge_consumables
    (start_date_in       in objects.deleted_date%type,
     end_date_in         in objects.deleted_date%type)
  RETURN PLS_INTEGER
  IS
    v_count PLS_INTEGER := 0;
    
  Obj_list Obj_list_t;  -- VARRAY object to hold ids to bulk delete for child objects
  Obj_rowid_list Obj_rowid_list_t;  -- VARRAY object to hold ids to bulk delete for parent objects
  Obj_list_index PLS_INTEGER; -- index into VARRAY object 
    
  BEGIN
  
  Obj_list := Obj_list_t();   -- initialize VARRAY object
  Obj_list.extend(chunk_size); -- allocate VARRAY object memory to chunk size
  
  LOGGING.LOG_STATUS(1,'Starting child object deletions for consumables.');
  
  --open the cursor for the object ids we want to purge.
  --we will use these ids to delete child records from other tables
    FOR x IN (SELECT object_id
              FROM objects
              WHERE deleted_date is not NULL
              AND deleted_date BETWEEN start_date_in AND end_date_in
              and object_template_id in (
           300281711,
           352903310,
          1006954225,
             9343642,
            35614291,
           339194814,
          1001781097,
          1632911215,
          1139268412,
          2059421636,
          1349101341,
         -1657672270,
          1931602976,
         -1440714292,
         -1436615854,
         -1614624973,
         -1556205486,
          -679440489,
         -1397391672,
          -541746271,
          -718648728,
          -789429729,
          -786663538,
          -610128810,
          -132487792,
         -1129945606,
          -104858180,
           -82570045)) LOOP
 
       v_count := v_count + 1;     
       Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
       
       If Obj_list_index = 0 then
          Obj_list(chunk_size) := x.object_id; -- make sure we fill last VARRAY element      
          Purge_consumables_work(Obj_list); -- bulk delete object ids in VARRAY list
          COMMIT;
          LOGGING.LOG_STATUS(v_count,'batch purged');
       Else
          Obj_list(Obj_list_index) := x.object_id; -- fill VARRAY with ids         
       End If;
       
    END LOOP;
    
  LOGGING.LOG_STATUS(v_count,'Child consumables object main loop exited.');
    
    --Now delete remaining partially full object list for child objects
    If Obj_list_index != 0 then
        Obj_list.trim(chunk_size - Obj_list_index);
        Purge_consumables_work(Obj_list); 
        COMMIT;
        LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
    End If;  
    
    Obj_list.delete; -- cleanup - delete VARRAY list  
        
  LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' references to consumable child objects.');      
        
    --Now delete parent objects
    v_count := 0; -- reinitialize
    Obj_rowid_list := Obj_rowid_list_t();  -- initialize VARRAY object
    Obj_rowid_list.extend(chunk_size); -- allocate VARRAY memory at chunk size   
  
  LOGGING.LOG_STATUS(1,'Starting parent consumable object deletions.');  
   
    FOR x IN (SELECT ROWID
              FROM objects
              WHERE deleted_date is not NULL
              AND deleted_date BETWEEN start_date_in AND end_date_in
              and object_template_id in (
           300281711,
           352903310,
          1006954225,
             9343642,
            35614291,
           339194814,
          1001781097,
          1632911215,
          1139268412,
          2059421636,
          1349101341,
         -1657672270,
          1931602976,
         -1440714292,
         -1436615854,
         -1614624973,
         -1556205486,
          -679440489,
         -1397391672,
          -541746271,
          -718648728,
          -789429729,
          -786663538,
          -610128810,
          -132487792,
         -1129945606,
          -104858180,
           -82570045)) LOOP
 
       v_count := v_count + 1;     
       Obj_list_index := MOD(v_count, chunk_size); -- what obj index for this batch
       
       If Obj_list_index = 0 then
          Obj_rowid_list(chunk_size) := x.ROWID; -- make sure we fill last VARRAY element      
          FORALL i IN Obj_rowid_list.First..Obj_rowid_list.Last DELETE FROM objects WHERE ROWID = Obj_rowid_list(i); -- bulk delete
          COMMIT;
          LOGGING.LOG_STATUS(v_count,'batch purged');
       Else
          Obj_rowid_list(Obj_list_index) := x.ROWID; -- fill VARRAY with ids        
       End If;
       
    END LOOP;
        
  LOGGING.LOG_STATUS(v_count,'Parent consumable object main loop exited.');
  
    --Now delete remaining partially full object list for child objects
    If Obj_list_index != 0 then
        Obj_rowid_list.trim(chunk_size - Obj_list_index);
        FORALL i IN Obj_rowid_list.First..Obj_rowid_list.Last DELETE FROM objects WHERE rowid = Obj_rowid_list(i);
        COMMIT;
        LOGGING.LOG_STATUS(v_count,'Deleting chunk remainder.');
    End If;  
    
    Obj_rowid_list.delete; -- cleanup - delete VARRAY list 
   
  LOGGING.LOG_STATUS(v_count,'Finished deleting '|| to_char(v_count) ||' consumable parent objects.');      
  RETURN 0;

    EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      IF v_count = 0 THEN
        dbms_output.put_line ('never deleted');
        LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
      END IF;
      dbms_output.put_line (v_count||': iterations');
      LOGGING.LOG_ERROR(SQLCODE, SQLERRM);
      RETURN SQLCODE;
  END Purge_consumables;
  
/**********************************************************************/
/*** Purge Consumables Work                                         ***/
/***                                                                ***/
/*** Bulk delete consumable objects marked for deletion in child    ***/
/*** object tables given a list of object ids passed in from a      ***/
/*** VARRAY. Called purge consumables function.                     ***/
/**********************************************************************/

  PROCEDURE Purge_consumables_work
  (Obj_list IN Obj_list_t)
  IS
  
  BEGIN
  
        --delete all children
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM armor WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM attributes WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM battlefield_marker_objects WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM biographies nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM creature_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM experience_points nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM factory_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM intangible_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM location_lists nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM mission_objects nologging WHERE object_id = Obj_list(i);

       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM object_variables nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM property_lists nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM resource_container_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM scripts nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM tangible_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM token_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM vehicle_objects nologging WHERE object_id = Obj_list(i);
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM weapon_objects nologging WHERE object_id = Obj_list(i);
       --delete messages target(object_id)
       FORALL i IN Obj_list.First..Obj_list.Last DELETE FROM messages nologging WHERE target = Obj_list(i);
  
  END Purge_consumables_work;
  
    
  --
  --
  --
  FUNCTION time_purge_consumables
    (start_date_in       in objects.deleted_date%type,
     end_date_in         in objects.deleted_date%type)
  RETURN PLS_INTEGER IS
    v_retval PLS_INTEGER := 0;
    v_start_date DATE := start_date_in;
    v_end_date DATE := end_date_in;
    v_hours NUMBER := (v_end_date - v_start_date) * 24;
    v_time NUMBER := 1;
  BEGIN
      dbms_output.put_line('Hours between dates: '||v_hours);
    FOR i IN 1..v_hours LOOP
      v_end_date := v_start_date + v_time/24;
      dbms_output.put_line ('Calling proc with Start Date: '||to_char(v_start_date, 'dd-mon-yy hh24:mi:ss')||'-'||'End Date: '||to_char(v_end_date, 'dd-mon-yy hh24:mi:ss'));
      v_retval := purge_consumables(v_start_date, v_end_date);
      v_start_date := v_start_date + v_time/24;
      
      EXIT WHEN v_retval <> 0;

    END LOOP;
    RETURN 0;

    EXCEPTION
      WHEN OTHERS THEN
      ROLLBACK;
      dbms_output.put_line('Purge return value: '||v_retval);
      RETURN SQLCODE;
  END time_purge_consumables;

end purge_methods;
