-- If you don't see a header file, this fall under GPL license and
-- Artistic lincense as the rest of GUPPS or MAPPS by Mark Nielsen, 
-- Copyrighted 2001,2002.


-- create a method to unpurge just one item.  
-- create a method to purge one item. 
--  \i __HOME__/__TABLENAME__.table


   --- Very dangerous delete command. Glad I formatted 
   --- names of functions in a standard way. 
delete from pg_proc where proname like 'sql___TABLENAME___insert';
delete from pg_proc where proname like 'sql___TABLENAME___delete';
delete from pg_proc where proname like 'sql___TABLENAME___undelete';
delete from pg_proc where proname like 'sql___TABLENAME___update';
delete from pg_proc where proname like 'sql___TABLENAME___copy';
delete from pg_proc where proname like 'sql___TABLENAME___purge';
delete from pg_proc where proname like 'sql___TABLENAME___purgeone';
delete from pg_proc where proname like 'sql___TABLENAME___unpurge';
delete from pg_proc where proname like 'sql___TABLENAME___unpurgeone';

---------------------------------------------------------------------

--drop  function sql___TABLENAME___insert ();
CREATE FUNCTION sql___TABLENAME___insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into __TABLENAME__ (date_updated, date_created, active)
        values (CURRENT_TIMESTAMP,CURRENT_TIMESTAMP, 1);
     -- Get the unique oid of the row just inserted. 
   GET DIAGNOSTICS oid1 = RESULT_OID;
     -- Get the __TABLENAME__ id. 
   FOR record1 IN SELECT __TABLENAME___id FROM __TABLENAME__ where oid = oid1
      LOOP
      id := record1.__TABLENAME___id;
   END LOOP;
   
     -- If id is NULL, insert failed or something is wrong.
   IF id is NULL THEN return (-1); END IF;
     -- It should also be greater than 0, otherwise something is wrong.
   IF id < 1 THEN return (-2); END IF;

      -- Now backup the data. 
    FOR record_backup IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = id
       LOOP
       insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created, 
           active, error_code) 
         values (id, record_backup.date_updated, record_backup.date_created,
            record_backup.active, ''insert'');
    END LOOP;

     -- Everything has passed, return id as __TABLENAME___id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

--drop  function sql___TABLENAME___delete (int4);
CREATE FUNCTION sql___TABLENAME___delete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 0. 
   FOR record1 IN SELECT __TABLENAME___id FROM __TABLENAME__ 
          where __TABLENAME___id = id
      LOOP
      update __TABLENAME__ set active=0, date_updated = CURRENT_TIMESTAMP
           where __TABLENAME___id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = id
      LOOP
      insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__ ,error_code)
           values (record_backup.__TABLENAME___id, record_backup.date_updated, 
             record_backup.date_created, record_backup.active
             __BACKUPVALUES__ , ''delete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed. 
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------

--drop  function sql___TABLENAME___undelete (int4);
CREATE FUNCTION sql___TABLENAME___undelete (int4) RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; 
    record_backup RECORD;
    return_int4 int4 :=0;

BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

     -- If we find the id, set active = 1. 
   FOR record1 IN SELECT __TABLENAME___id FROM __TABLENAME__ 
          where __TABLENAME___id = id
      LOOP
      update __TABLENAME__ set active=1, date_updated = CURRENT_TIMESTAMP
           where __TABLENAME___id = id;  
      GET DIAGNOSTICS return_int4 = ROW_COUNT;       
      id_exists := 1;
   END LOOP;
      
     -- If we did not find the id, abort and return -2.  
   IF id_exists = 0 THEN return (-2); END IF;

   FOR record_backup IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = id
      LOOP
      insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__ ,error_code)
           values (record_backup.__TABLENAME___id, record_backup.date_updated, 
             record_backup.date_created, record_backup.active
             __BACKUPVALUES__ , ''undelete''
      );
   END LOOP;

     -- If id_exists == 0, Return error.
     -- It means it never existed. 
   IF id_exists = 0 THEN return (-1); END IF;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
--drop  function sql___TABLENAME___update (int4 __FIELDS__);
CREATE FUNCTION sql___TABLENAME___update  (int4 __FIELDS__) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
    __CLEANVARIABLES__
BEGIN
    __REMAKEVARIABLES__
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record_update IN SELECT __TABLENAME___id FROM __TABLENAME__
         where __TABLENAME___id = id
      LOOP
      id_exists := 1;
   END LOOP;

   IF id_exists = 0 THEN return (-2); END IF;

   update __TABLENAME__ set date_updated = CURRENT_TIMESTAMP
      __UPDATEFIELDS__ 
        where __TABLENAME___id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = id
      LOOP
     insert into __TABLENAME___backup (__TABLENAME___id,
         date_updated, date_created, active
         __BACKUPCOLUMNS__, error_code)
       values (record_update.__TABLENAME___id, record_backup.date_updated,
         record_backup.date_created, record_backup.active
         __BACKUPVALUES__, ''update''
      );
   END LOOP;

     -- We got this far, it must be true, return ROW_COUNT.   
   return (return_int4);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

--drop  function sql___TABLENAME___copy (int4);
CREATE FUNCTION sql___TABLENAME___copy (int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record1 RECORD; record2 RECORD; record3 RECORD;    
    return_int4 int4 := 0;
    id_new int4 := 0;
    __TABLENAME___new int4 :=0;
BEGIN
     -- If the id is not greater than 0, return error.
   id := clean_numeric($1);
   IF id < 1 THEN return -1; END IF;

   FOR record1 IN SELECT __TABLENAME___id FROM __TABLENAME__ where __TABLENAME___id = id
      LOOP
      id_exists := 1;
   END LOOP;
   IF id_exists = 0 THEN return (-2); END IF;

     --- Get the new id
   FOR record1 IN SELECT sql___TABLENAME___insert() as __TABLENAME___insert
      LOOP
      __TABLENAME___new := record1.__TABLENAME___insert;
   END LOOP;
     -- If the __TABLENAME___new is not greater than 0, return error.
   IF __TABLENAME___new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = id
      LOOP

     FOR record1 IN SELECT sql___TABLENAME___update(__TABLENAME___new __COPYFIELDS__)
        as __TABLENAME___insert
      LOOP
        -- execute some arbitrary command just to get it to pass. 
      id_exists := 1;
     END LOOP;
   END LOOP;

     -- We got this far, it must be true, return new id.   
   return (__TABLENAME___new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
--drop  function sql___TABLENAME___purge ();
CREATE FUNCTION sql___TABLENAME___purge () RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;

BEGIN 

     -- Now delete one by one. 
   FOR record_backup IN SELECT * FROM __TABLENAME__ where active = 0
      LOOP
         -- Record the id we want to delete. 
      delete_id = record_backup.__TABLENAME___id;

      insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__ ,error_code)
           values (record_backup.__TABLENAME___id, record_backup.date_updated, 
             record_backup.date_created, record_backup.active
             __BACKUPVALUES__ , ''purge''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.   
      delete from __TABLENAME__ where __TABLENAME___id = delete_id;

        -- Get row count of row just deleted, should be 1. 
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.  
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
--drop  function sql___TABLENAME___purgeone (int4);
CREATE FUNCTION sql___TABLENAME___purgeone (int4) RETURNS int4 AS '
DECLARE
    record_backup RECORD; oid1 int4 := 0;
    record1 RECORD;
    return_int4 int4 :=0;
    deleted int4 := 0;
    delete_count int4 :=0;
    delete_id int4;
    purged_no int4 := 0;

BEGIN

    delete_id := $1;
        -- If purged_id less than 1, return -4
    IF delete_id < 1 THEN return (-4); END IF;

   FOR record1 IN SELECT * FROM __TABLENAME__ 
      where active = 0 and __TABLENAME___id = delete_id 
      LOOP
      purged_no := purged_no + 1;
   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-1); END IF;

     -- Now delete one by one.
   FOR record_backup IN SELECT * FROM __TABLENAME__ where __TABLENAME___id = delete_id
      LOOP

      insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__ ,error_code)
           values (record_backup.__TABLENAME___id, record_backup.date_updated,
             record_backup.date_created, record_backup.active
             __BACKUPVALUES__ , ''purgeone''
          );

        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;

        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;
        -- Now delete this from the main table.
      delete from __TABLENAME__ where __TABLENAME___id = delete_id;

        -- Get row count of row just deleted, should be 1.
      GET DIAGNOSTICS deleted = ROW_COUNT;
        -- If deleted less than 1, return -3
      IF deleted < 1 THEN return (-3); END IF;
      delete_count := delete_count + 1;

    END LOOP;

     -- We got this far, it must be true, return the number of ones we had.
   return (delete_count);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------------
--drop  function sql___TABLENAME___unpurge ();
CREATE FUNCTION sql___TABLENAME___unpurge () RETURNS int2 AS '
DECLARE
    record1 RECORD;
    record2 RECORD; 
    record_backup RECORD;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

     -- Now get the unique ids that were purged. 
   FOR record1 IN select distinct __TABLENAME___id
        from __TABLENAME___backup 
       where ((__TABLENAME___backup.error_code = ''purge'') 
               or (__TABLENAME___backup.error_code = ''purgeone''))
          and NOT __TABLENAME___id = ANY (select __TABLENAME___id from __TABLENAME__)
     LOOP

      purged_id := record1.__TABLENAME___id;
      timestamp1 := CURRENT_TIMESTAMP;
      purged_no := purged_no + 1;
      oid_found := 0;
      highest_oid := 0;

        -- Now we have the unique id, find its latest date. 

      FOR record2 IN select max(oid) from __TABLENAME___backup 
          where __TABLENAME___id = purged_id 
            and ((error_code = ''purge'') or (error_code = ''purgeone''))
        LOOP 
          -- record we got the date and also record the highest date.
        oid_found := 1; 
        highest_oid := record2.max;
      END LOOP;
 
         -- If the oid_found is 0, return error. 
      IF oid_found = 0 THEN return (-3); END IF;

        -- Now we have the latest date, get the values and insert them. 
      FOR record_backup IN select * from __TABLENAME___backup 
          where oid = highest_oid
        LOOP 
  
        insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
            active __BACKUPCOLUMNS__ ,error_code)
            values (purged_id, timestamp1, record_backup.date_created, 
                 1
               __BACKUPVALUES__ , ''unpurge''
            );

          -- Get the unique oid of the row just inserted. 
        GET DIAGNOSTICS oid1 = RESULT_OID;
          -- If oid1 less than 1, return -1
        IF oid1 < 1 THEN return (-1); END IF;

        insert into __TABLENAME__ (__TABLENAME___id, date_updated, date_created,
            active __BACKUPCOLUMNS__)
             values (purged_id, timestamp1, record_backup.date_created, 1
               __BACKUPVALUES__ );
          -- Get the unique oid of the row just inserted.
        GET DIAGNOSTICS oid1 = RESULT_OID;
          -- If oid1 less than 1, return -2
        IF oid1 < 1 THEN return (-2); END IF;

          -- Now restore anything from update_tablefield.
       -- We really should disable it since it is impossible for
       -- update_tablefield to contain newer data after a purge.
       -- It actually works if you modify the table after a purge, so keep it
       -- just for kicks.
        PERFORM restore_tablefields(''__TABLENAME__'',purged_id, record_backup.date_updated);

        END LOOP;

     END LOOP;

     -- We got this far, it must be true, return how many were affected.  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

---------------------------------------------------------------------
--drop  function sql___TABLENAME___unpurgeone (int4);
CREATE FUNCTION sql___TABLENAME___unpurgeone (int4) RETURNS int2 AS '
DECLARE
    record_id int4;
    record1 RECORD;
    record2 RECORD;
    record_backup RECORD;
    return_int4 int4 :=0;
    purged_id int4 := 0;
    purge_count int4 :=0;
    timestamp1 timestamp;
    purged_no int4 := 0;
    oid1 int4 := 0;
    oid_found int4 := 0;
    highest_oid int4 := 0;

BEGIN

      purged_id := $1;
        -- If purged_id less than 1, return -1
      IF purged_id < 1 THEN return (-1); END IF;
        --- Get the current timestamp.
      timestamp1 := CURRENT_TIMESTAMP;

   FOR record1 IN select distinct __TABLENAME___id
       from __TABLENAME___backup
       where ((__TABLENAME___backup.error_code = ''purge'')
           or (__TABLENAME___backup.error_code = ''purgeone''))
          and NOT __TABLENAME___id = ANY (select __TABLENAME___id from __TABLENAME__)
          and __TABLENAME___id = purged_id
      LOOP
      purged_no := purged_no + 1;

   END LOOP;

        -- If purged_no less than 1, return -1
   IF purged_no < 1 THEN return (-3); END IF;

        -- Now find the highest oid.  
   FOR record2 IN select max(oid) from __TABLENAME___backup
          where __TABLENAME___id = purged_id 
            and ((error_code = ''purge'') or (error_code = ''purgeone''))
        LOOP
          -- record we got the date and also record the highest date.
        oid_found := 1;
        highest_oid := record2.max;
    END LOOP;

         -- If the oid_found is 0, return error.
    IF oid_found = 0 THEN return (-4); END IF;

        -- Now get the data and restore it. 
    FOR record_backup IN select * from __TABLENAME___backup 
          where oid  = highest_oid
        LOOP 
        -- Insert into backup that it was unpurged. 
      insert into __TABLENAME___backup (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__ ,error_code)
           values (purged_id, timestamp1, 
             record_backup.date_created, 1
             __BACKUPVALUES__ , ''unpurgeone''
          );

        -- Get the unique oid of the row just inserted. 
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -1
      IF oid1 < 1 THEN return (-1); END IF;
        -- Insert into live table. 
      insert into __TABLENAME__ (__TABLENAME___id, date_updated, date_created,
          active __BACKUPCOLUMNS__)
           values (record_backup.__TABLENAME___id, timestamp1,
             record_backup.date_created, 1
             __BACKUPVALUES__ );
        -- Get the unique oid of the row just inserted.
      GET DIAGNOSTICS oid1 = RESULT_OID;
        -- If oid1 less than 1, return -2
      IF oid1 < 1 THEN return (-2); END IF;

       -- Now restore anything from update_tablefield.
       -- We reallyt should disable it since it is impossible for
       -- update_tablefield to contain newer data after a purge.
       -- It actually works if you modify the table after a purge, so keep it
       -- just for kicks.
      PERFORM restore_tablefields(''__TABLENAME__'',purged_id, record_backup.date_updated);

      END LOOP;

     -- We got this far, it must be true, return how many were affected (1).  
   return (purged_no);
END;
' LANGUAGE 'plpgsql';

--insert into __TABLENAME__ (__TABLENAME___id, date_updated, date_created, active)
--    values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
--insert into __TABLENAME___backup (backup_id, __TABLENAME___id, 
--     date_updated, date_created, active, error_code)
--    values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation');