drop sequence users_sequence;
create sequence users_sequence;
drop sequence users_sequence_backup;
create sequence users_sequence_backup;
drop table users;
create table users (
users_id int4 NOT NULL PRIMARY KEY DEFAULT nextval('users_sequence'),
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
username text NOT NULL default '' ,
password text NOT NULL default '' ,
user_type int4 not null default 1 ,
contact_id int4 not null default 0  REFERENCES contact
       ON DELETE NO ACTION
       ON UPDATE CASCADE 
);drop table users_backup;
create table users_backup (
backup_id int4 NOT NULL UNIQUE DEFAULT nextval('users_sequence_backup'), 
    users_id int4 NOT NULL DEFAULT 0,
    date_updated  timestamp NOT NULL default CURRENT_TIMESTAMP,
    date_created  timestamp NOT NULL default CURRENT_TIMESTAMP,
    active int2 CHECK (active in (0,1)) DEFAULT 0,
    username text NOT NULL default '',
password text NOT NULL default '',
user_type int4 not null default 1,
contact_id int4 not null default 0, error_code text NOT NULL DEFAULT ''
);
drop view users_active;
create view users_active as select * from users
        where active = 1;
drop view users_deleted;
create view users_deleted as select * from users
        where active = 0;
drop view users_backup_ids;
create view users_backup_ids as 
           select distinct users_id from users_backup;
drop view users_purged;
create view users_purged as
   select * from users_backup where oid = ANY (
     select max(oid) from users_backup where users_id = ANY
        (
        select distinct users_id from users_backup
          where users_backup.error_code = 'purge'
           and NOT users_id = ANY (select users_id from users)
        )
        group by users_id
     )
    ;
---              Generic Functions for Perl/Postgresql version 1.0

---                       Copyright 2001, Mark Nielsen
---                            All rights reserved.
---    This Copyright notice was copied and modified from the Perl 
---    Copyright notice. 
---    This program is free software; you can redistribute it and/or modify
---    it under the terms of either:

---        a) the GNU General Public License as published by the Free
---        Software Foundation; either version 1, or (at your option) any
---        later version, or

---        b) the "Artistic License" which comes with this Kit.

---    This program is distributed in the hope that it will be useful,
---    but WITHOUT ANY WARRANTY; without even the implied warranty of
---    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See either
---    the GNU General Public License or the Artistic License for more details.

---    You should have received a copy of the Artistic License with this
---    Kit, in the file named "Artistic".  If not, I'll be glad to provide one.

---    You should also have received a copy of the GNU General Public License
---   along with this program in the file named "Copying". If not, write to the 
---   Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 
---    02111-1307, USA or visit their web page on the internet at
---    https://www.gnu.org/copyleft/gpl.html.

-- create a method to unpurge just one item.  
-- create a method to purge one item. 
--  \i /tmp/Test/sample/users.table
---------------------------------------------------------------------

drop function sql_users_insert ();
CREATE FUNCTION sql_users_insert () RETURNS int4 AS '
DECLARE
    record1 record;  oid1 int4; id int4 :=0; record_backup RECORD;
BEGIN
   insert into users (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 users id. 
   FOR record1 IN SELECT users_id FROM users where oid = oid1
      LOOP
      id := record1.users_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 users where users_id = id
       LOOP
       insert into users_backup (users_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 users_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
---------------------------------------------------------------------

drop function sql_users_delete (int4);
CREATE FUNCTION sql_users_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 users_id FROM users 
          where users_id = id
      LOOP
      update users set active=0, date_updated = CURRENT_TIMESTAMP
           where users_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 users where users_id = id
      LOOP
      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (record_backup.users_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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_users_undelete (int4);
CREATE FUNCTION sql_users_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 users_id FROM users 
          where users_id = id
      LOOP
      update users set active=1, date_updated = CURRENT_TIMESTAMP
           where users_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 users where users_id = id
      LOOP
      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (record_backup.users_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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_users_update (int4 , text, text, int4, int4);
CREATE FUNCTION sql_users_update  (int4 , text, text, int4, int4) 
  RETURNS int2 AS '
DECLARE
    id int4 := 0;
    id_exists int4 := 0;
    record_update RECORD; record_backup RECORD;
    return_int4 int4 :=0;
              var_2 text;
          var_3 text;
          var_4 int4;
          var_5 int4;

BEGIN
             var_2 := clean_text($2);
         var_3 := clean_text($3);
         var_4 := clean_numeric($4);
         var_5 := clean_numeric($5);

     -- 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 users_id FROM users
         where users_id = id
      LOOP
      id_exists := 1;
   END LOOP;

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

   update users set date_updated = CURRENT_TIMESTAMP
      , username = var_2, password = var_3, user_type = var_4, contact_id = var_5 
        where users_id = id;
   GET DIAGNOSTICS return_int4 = ROW_COUNT;

   FOR record_backup IN SELECT * FROM users where users_id = id
      LOOP
     insert into users_backup (users_id,
         date_updated, date_created, active
         , username, password, user_type, contact_id, error_code)
       values (record_update.users_id, record_backup.date_updated,
         record_backup.date_updated, record_backup.active
         , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id, ''update''
      );
   END LOOP;

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

drop function sql_users_copy (int4);
CREATE FUNCTION sql_users_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;
    users_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 users_id FROM users where users_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_users_insert() as users_insert
      LOOP
      users_new := record1.users_insert;
   END LOOP;
     -- If the users_new is not greater than 0, return error.
   IF users_new < 1 THEN return -3; END IF;

   FOR record2 IN SELECT * FROM users where users_id = id
      LOOP

     FOR record1 IN SELECT sql_users_update(users_new , clean_text(record2.username), clean_text(record2.password), clean_text(record2.user_type), clean_text(record2.contact_id))
        as users_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 (users_new);
END;
' LANGUAGE 'plpgsql';

------------------------------------------------------------------
drop function sql_users_purge ();
CREATE FUNCTION sql_users_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 users where active = 0
      LOOP
         -- Record the id we want to delete. 
      delete_id = record_backup.users_id;

      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (record_backup.users_id, record_backup.date_updated, 
             record_backup.date_updated, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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 users where users_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_users_purgeone (int4);
CREATE FUNCTION sql_users_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 users 
      where active = 0 and users_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 users where users_id = delete_id
      LOOP

      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (record_backup.users_id, record_backup.date_updated,
             record_backup.date_updated, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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 users where users_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_users_unpurge ();
CREATE FUNCTION sql_users_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 users_id from users_backup 
       where users_backup.error_code = ''purge''
          and NOT users_id = ANY (select users_id from users)
      LOOP

      purged_id := record1.users_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 users_backup 
          where users_id = purged_id and error_code = ''purge''
        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 users_backup 
          where oid = highest_oid
        LOOP 

      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (purged_id, record_backup.date_updated, 
             timestamp1, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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 users (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id)
           values (purged_id, timestamp1,
             timestamp1, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id );
        -- 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;

      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_users_unpurgeone (int4);
CREATE FUNCTION sql_users_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 users_id from users_backup
       where users_backup.error_code = ''purge''
          and NOT users_id = ANY (select users_id from users)
          and users_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 users_backup
          where users_id = purged_id and error_code = ''purge''
        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 users_backup 
          where oid  = highest_oid
        LOOP 
        -- Insert into backup that it was unpurged. 
      insert into users_backup (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id ,error_code)
           values (purged_id, timestamp1, 
             record_backup.date_created, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id , ''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 users (users_id, date_updated, date_created,
          active , username, password, user_type, contact_id)
           values (record_backup.users_id, timestamp1,
             record_backup.date_updated, record_backup.active
             , record_backup.username, record_backup.password, record_backup.user_type, record_backup.contact_id );
        -- 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;

      END LOOP;

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

insert into users (users_id, date_updated, date_created, active)
    values (0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
insert into users_backup (backup_id, users_id, 
     date_updated, date_created, active, error_code)
    values (0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0, 'table creation');




drop function clean_text (text);
CREATE FUNCTION  clean_text (text) RETURNS text AS '
  my $Text = shift;
    # Get rid of whitespace in front. 
  $Text =~ s/^\\s+//;
    # Get rid of whitespace at end. 
  $Text =~ s/\\s+$//;
    # Get rid of anything not text.
  $Text =~ s/[^ a-z0-9\\/\\`\\~\\!\\@\\#\\$\\%\\^\\&\\*\\(\\)\\-\\_\\=\\+\\\\\\|\[\\{\\]\\}\\;\\:\\''\\"\\,\\<\\.\\>\\?\\t\\n]//gi;
    # Replace all multiple whitespace with one space. 
  $Text =~ s/\\s+/ /g;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up. 
select clean_text ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_alpha (text);
CREATE FUNCTION  clean_alpha (text) RETURNS text AS '
  my $Text = shift;
  $Text =~ s/[^a-z0-9_]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up. 
select clean_alpha ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (text);
CREATE FUNCTION  clean_numeric (text) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric ('       ,./<>?aaa aa      !@#$%^&*()_+| ');

drop function clean_numeric (int4);
CREATE FUNCTION  clean_numeric (int4) RETURNS int4 AS '
  my $Text = shift;
  $Text =~ s/[^0-9]//gi;
  return $Text;
' LANGUAGE 'plperl';
 -- Just do show you what this function cleans up.
select clean_numeric (11111);



select sql_users_insert();
select sql_users_update(1,'Username1','Password1',1);
select sql_users_insert();
select sql_users_update(2,'Username2','Password2',1);

drop function sql_users_verify(text,text);
CREATE FUNCTION sql_users_verify (text,text) RETURNS int4 AS '
DECLARE
    record1 record;  id int4 :=0;
    username text; password text;
    active int4 := 0;
BEGIN
   username := clean_text($1);
   password := clean_text($2);

   FOR record1 IN SELECT users_id FROM users
        where users.username = username
          and users.password = password
          and users.active = 1
      LOOP
      id := record1.users_id;
   END LOOP;

     -- If id < 1 check if username exists .
   IF id is NULL THEN  
     FOR record1 IN SELECT users_id,active FROM users
        where users.username = username
       LOOP
       id := record1.users_id;
--       active := record1.active;
         -- If active is < 1, isn not active.
--       IF active < 1 THEN return (-3); END IF;
         -- If id is > 0, password is incorrect.
       IF id  > 0 THEN return (-2); END IF;
       END LOOP;
   END IF;

        -- If id is < 1, username does not exist. 
   IF id  < 1 THEN return (-1); END IF;

     -- Everything has passed, return id as users_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
select sql_users_verify('Username1','Password1');

drop function sql_users_exists(text);
CREATE FUNCTION sql_users_exists (text) RETURNS int4 AS '
DECLARE
    record1 record;  id int4 :=0;
    username text; password text;
BEGIN
   username := clean_text($1);

   FOR record1 IN SELECT users_id FROM users
        where users.username = username
      LOOP
      id := record1.users_id;
   END LOOP;

     -- Everything has passed, return id as users_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
select sql_users_exists('Username1');

drop function sql_users_active(text);
CREATE FUNCTION sql_users_active (text) RETURNS int4 AS '
DECLARE
    record1 record;  id int4 :=0;
    username text; password text;
BEGIN
   username := clean_text($1);

   FOR record1 IN SELECT users_id FROM users where users.username = username
      LOOP
      id := record1.users_id;
   END LOOP;

     -- Everything has passed, return id as users_id.
   return (id);
END;
' LANGUAGE 'plpgsql';
select sql_users_active('Username1');


vacuum;