 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();

-- P O L Y C O M   P H O N E S 
create or replace function migrate_polycom_phones() returns integer as '
declare
  phone record;
  line record;
  next_id int;
  default_group_id int;
begin

  select into default_group_id group_id from group_storage where name = ''default'' and resource = ''phone'';

  for phone in select * from dblink(''select p.phone_id, p.serial_number, p.name, p.factory_id, p.storage_id, p.folder_id from phone p'') as (id int, serial_number text, name text, factory_id text, storage_id int, folder_id int) loop
   
    next_id := nextval(''phone_seq'');
    insert into phone (phone_id, name, serial_number, value_storage_id, bean_id, model_id) 
      values (next_id, phone.name, phone.serial_number, phone.storage_id, ''polycom'',
              substring(phone.factory_id from ''\\\\d*$''));

    for line in select * from dblink(''select position, storage_id, user_id from 
        line where phone_id ='' || phone.id) as (position int, storage_id int, user_id int) loop
    
        insert into line (line_id, phone_id, position, user_id, value_storage_id) 
          values (nextval(''line_seq''), next_id, line.position, line.user_id, line.storage_id);
         
    end loop;
    

-- notes:
--  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

-- U S E R   G R O U P S
create or replace function migrate_user_groups() returns integer as '
