 -- notes:
---  Dont use apostrophes in comments
---  Dont use variable names that match column names when setting variable (see my_* usages)
+--  Dont use apostrophes in comments inside functions
+--  Double single quote in all function bodies.
+--  Dont use variable names that match tables or column names when setting variable (see my_* usages)
 --  primative logging by raising notices. level debug is useless w/o modifications to postgres.conf
 --  some functions preserve primary keys from PDS, others do not.  depends on how easy it is to adjust
 --    and if destination table has to merge values from multiple tables in old schema.  if primary keys
 --    are preserved, sequences must be updated manually
---
+--  use environment vars to control output, example
+--    
+--  > env PGOPTIONS='-c client_min_messages=ERROR' \
+--     psql -U postgres SIPXCONFIG_TEST < etc\database\migrate-2.8.sql > /dev/null
+-- 
 -- todos:
 -- bail on any error
 -- workout logging that can keep a audit trail of migration incase there was a bad translation
 --   discovered post migration AND after users have added new data and running another migration is
 --   not possible and custom repair script can be written
-
-delete from line;
-delete from phone_group;
-delete from phone;
-
--- todo, translate superuser as well
-delete from user_group;
-delete from users where user_id != 1;
-
-delete from group_storage;
-delete from setting_value;
-delete from value_storage;
 
 -- U S E R   G R O U P S
 create or replace function migrate_user_groups() returns integer as '

  insert into phone_type_migration values (''xpressa_strongarm_vxworks'', ''genericPhone'', null);
  insert into phone_type_migration values (''ixpressa_x86_win32'', ''genericPhone'', null);

  for phone in select * from 
      dblink(''select p.id, p.serial_number, p.pg_id, p.usrs_id, pt.model 
      from logical_phones p, phone_types pt where p.pt_id = pt.id'') 
      as (id int, serial_number text, pg_id int, usrs_id int, pt_model text) loop

    select into my_phone_type * from phone_type_migration where pds_model = phone.pt_model; 

    raise notice ''importing phone %, %, %...'', phone.serial_number, my_phone_type.bean_id;

    insert into phone (phone_id, serial_number, bean_id, model_id) 
      values (phone.id, phone.serial_number, my_phone_type.bean_id, my_phone_type.model_id);

    select into my_group_id group_id from phone_group_migration where pds_group_id = phone.pg_id;

    insert into phone_group (phone_id, group_id) values (phone.id, my_group_id);

    if phone.usrs_id is not null 
    then
      insert into line (line_id, phone_id, position, user_id) 
        values (nextval(''line_seq''), phone.id, 0, phone.usrs_id);
    end if;

    -- todo, phone settings, only cisco? very difficult

select dblink_connect('dbname=PDS');

-- todo migrate folder and folder values
delete from line;
delete from phone_group;
delete from phone;

-- todo, translate superuser as well
delete from user_group;
delete from users where user_id != 1;

delete from group_storage;
delete from setting_value;
delete from value_storage;

select migrate_settings();
select migrate_user_groups();
