alter table contact drop column surfpermit;
begin;
drop view v_my_account;
drop view v_my_colleagues;
drop view people;
alter table contact drop column ismanager;
alter table contact drop column iscompanymanager;
alter table contact drop column isoperator;
alter table contact drop column isreceptionist;
alter table contact drop column spyable;
alter table contact drop column bargeable;
alter table contact drop column allow_add_fastdial;
alter table contact drop column allow_dial_tollfree;
alter table contact drop column allow_dial_local;
alter table contact drop column allow_dial_longdistance;
alter table contact drop column allow_dial_mobile;
alter table contact drop column allow_dial_international;
alter table contact drop column allow_dial_entertain;
alter table contact drop column allow_dial_premium;
alter table contact drop column allow_dial_adult;
alter table contact drop column allow_dial_voip;
alter table contact drop column allow_bypass_forward;
alter table contact drop column allow_view_reports;
alter table contact drop column allow_spy;
alter table contact drop column allow_barge;
alter table contact drop column allow_page;
alter table contact drop column allow_record;

CREATE VIEW v_my_account AS
SELECT *
   FROM contact
  WHERE contact.username::name = "current_user"();
GRANT SELECT, UPDATE ON v_my_account TO GROUP astium_users;

CREATE RULE update_v_my_account AS ON UPDATE TO v_my_account
  DO INSTEAD
  UPDATE contact set pause=NEW.pause where username=USER;

CREATE RULE activate_v_my_account AS ON UPDATE TO v_my_account
  DO INSTEAD
  INSERT INTO activate(action, type, which, status, created )
  VALUES
  ('modify', 'user', OLD.id, 'QUEUED', CURRENT_TIMESTAMP );

GRANT SELECT, INSERT, UPDATE ON v_my_account TO GROUP astium_users;

GRANT INSERT, UPDATE ON activate_id_seq TO GROUP astium_users;

CREATE VIEW v_my_colleagues AS
select contact.id as id, contact_department.department as department, role.visible as visible,
       contact.firstname as firstname, contact.lastname as lastname, phoneline.extension as extension,
       phoneline.company as company, channel.protocol as protocol, channel.username as username,
       'DAHDI/' || dahdi_channel.channel as dahdi_channel
  from contact left join phoneline on phoneline.lookupid = contact.id and phoneline.status <> 'deleted'
                                  and phoneline.function = 'contact'
               left join phone on phone.id = contact.phone and phone.status <> 'deleted'
               left join contact_department on contact.id = contact_department.contact
               left join channel on channel.phone = contact.phone and channel.inuse = 'yes'
                                and channel.line = phoneline.line
               left join dahdi_channel on dahdi_channel.id = phone.dahdi_channel and dahdi_channel.channel <> '1'
                                    and phone.dahdi_channel <> '1'
               left join role on role.id = contact.role
 where contact.id > 1 and contact.status <> 'deleted' and role.visible = 'yes'
       and phoneline.company = mycompanyid(CAST(USER as varchar));
GRANT SELECT ON v_my_colleagues TO GROUP astium_users;

CREATE VIEW people AS
  SELECT contact.firstname as firstname, contact.lastname as lastname, contact.username as username, role.visible as visible,
         phoneline.extension as extension, department.name as name,
         contact.field1 as field1, contact.field2 as field2, contact.field3 as field3, contact.field4 as field4,
         contact.field5 as field5
    FROM contact left join contact_department on contact_department.contact = contact.id
                 left join department on department.id = contact_department.department
                 left join phoneline on phoneline.lookupid = contact.id and phoneline.function = 'contact'
                 left join role on role.id = contact.role
   WHERE contact.status = 'active' AND phoneline.status = 'active';

GRANT UPDATE, SELECT, INSERT, DELETE ON people TO astium;
GRANT SELECT ON people TO GROUP astium_users;

drop table permissions;

commit;


