-- change zap to DAHDI in the database
alter table phonetype drop constraint phonetype_protocol_check;
alter table phonetype drop constraint phonetype_protocol;
ALTER TABLE phone DROP CONSTRAINT phone_zap_channel_constraint;
alter table trunk drop constraint trunk_type_check;
alter table trunk drop constraint trunk_type;
alter table callrouting drop constraint callrouting_trunk1type_check;
alter table callrouting drop constraint callrouting_trunk1type;
alter table callrouting drop constraint callrouting_trunk2type_check;
alter table callrouting drop constraint callrouting_trunk2type;
alter table callrouting drop constraint callrouting_trunk3type_check;
alter table callrouting drop constraint callrouting_trunk3type;
alter table channel drop constraint channel_protocol_check;
alter table channel drop constraint channel_protocol;
BEGIN;

CREATE or REPLACE FUNCTION  rename_zap_fields() RETURNS integer AS '
  BEGIN
    IF SPLIT_PART(VERSION(), '' '', 2) = ''7.4.7''
    THEN
      alter table trunk add column newtype varchar(12);
      update trunk set newtype = '''' where newtype IS NULL;
      alter table trunk alter column newtype set not NULL;
      alter table trunk alter column newtype set default ''dahdi'';
      update trunk set newtype = type;
      alter table trunk drop column type;
      alter table trunk rename newtype to type;

      alter table callrouting add column newtype varchar(12);
      update callrouting set newtype = ''dahdi'' where newtype IS NULL;
      alter table callrouting alter column newtype set not NULL;
      alter table callrouting alter column newtype set default ''dahdi'';
      update callrouting set newtype = type;
      alter table callrouting drop column trunk1type;
      alter table callrouting rename newtype to trunk1type;

      alter table callrouting add column newtype varchar(12);
      update callrouting set newtype = ''dahdi'' where newtype IS NULL;
      alter table callrouting alter column newtype set not NULL;
      alter table callrouting alter column newtype set default ''dahdi'';
      update callrouting set newtype = type;
      alter table callrouting drop column trunk2type;
      alter table callrouting rename newtype to trunk2type;

      alter table callrouting add column newtype varchar(12);
      update callrouting set newtype = ''dahdi'' where newtype IS NULL;
      alter table callrouting alter column newtype set not NULL;
      alter table callrouting alter column newtype set default ''dahdi'';
      update callrouting set newtype = type;
      alter table callrouting drop column trunk3type;
      alter table callrouting rename newtype to trunk3type;

      alter table phonetype add column newprotocol varchar(12);
      update phonetype set newprotocol = '''' where newprotocol IS NULL;
      alter table phonetype alter column newprotocol set not NULL;
      alter table phonetype alter column newprotocol set default ''DAHDI'';
      update phonetype set newprotocol = type;
      alter table phonetype drop column type;
      alter table phonetype rename newprotocol to type;

      alter table channel add column newprotocol varchar(12);
      update channel set newprotocol = '''' where newprotocol IS NULL;
      alter table channel alter column newprotocol set not NULL;
      alter table channel alter column newprotocol set default ''DAHDI'';
      update channel set newprotocol = type;
      alter table channel drop column type;
      alter table channel rename newprotocol to type;
    ELSE
      alter table trunk alter column type TYPE varchar(12);
      alter table callrouting alter column trunk1type TYPE varchar(12);
      alter table callrouting alter column trunk2type TYPE varchar(12);
      alter table callrouting alter column trunk3type TYPE varchar(12);
      alter table phonetype alter column protocol TYPE varchar(12);
      alter table channel alter column protocol TYPE varchar(12);
    END IF;
    RETURN 0;
  END;
' LANGUAGE plpgsql;

DROP VIEW v_my_channels;
DROP VIEW v_my_colleagues;
DROP VIEW v_userext_by_zapchannel;
DROP VIEW v_userext_by_iaxchannel;
DROP VIEW v_userext_by_sipchannel;

select rename_zap_fields();

ALTER TABLE zap_channel RENAME TO dahdi_channel;

ALTER TABLE phone RENAME zap_channel to dahdi_channel;
ALTER TABLE phone ADD CONSTRAINT phone_dahdi_channel_constraint FOREIGN KEY (dahdi_channel) REFERENCES dahdi_channel (id);
alter table phone rename zap_adsi to dahdi_adsi;
alter table phonetype rename zap_adsi to dahdi_adsi;
alter table phonetypephone rename zap_adsi to dahdi_adsi;
alter table phonetypecontactphone rename zap_adsi to dahdi_adsi;
ALTER TABLE dahdi_channel DROP CONSTRAINT zap_channel_astium_constraint;
ALTER TABLE dahdi_channel ADD CONSTRAINT dahdi_channel_astium_constraint FOREIGN KEY (astium) REFERENCES astium (id);
alter table astium rename zapchanged to dahdichanged;

update trunk set type = 'dahdi' where type = 'zap';
alter table trunk add constraint trunk_type_check CHECK (type IN ('none', 'dahdi', 'iax', 'sip', 'h323', 'lcr', 'skinny', 'skype'));

update callrouting set trunk1type = 'dahdi' where trunk1type = 'zap';
alter table callrouting add constraint callrouting_trunk1type_check CHECK (trunk1type IN ('none', 'dahdi', 'iax', 'sip', 'h323', 'lcr', 'skinny', 'skype'));

update callrouting set trunk2type = 'dahdi' where trunk2type = 'zap';
alter table callrouting add constraint callrouting_trunk2type_check CHECK (trunk2type IN ('none', 'dahdi', 'iax', 'sip', 'h323', 'lcr', 'skinny', 'skype'));

update callrouting set trunk3type = 'dahdi' where trunk3type = 'zap';
alter table callrouting add constraint callrouting_trunk3type_check CHECK (trunk3type IN ('none', 'dahdi', 'iax', 'sip', 'h323', 'lcr', 'skinny', 'skype'));

update channel set protocol = 'DAHDI' where protocol = 'Zap';
alter table channel add constraint channel_protocol_check CHECK (protocol IN ('DAHDI', 'IAX2', 'SIP'));

update phonetype set protocol = 'DAHDI' where protocol = 'Zap';
alter table phonetype add constraint phonetype_protocol_check CHECK (protocol IN ('DAHDI', 'IAX2', 'SIP'));

CREATE OR REPLACE FUNCTION cdr_owner_query(varchar) RETURNS varchar AS '
  DECLARE
    i integer;
    q varchar;
    proto varchar;
    chan varchar;
  BEGIN
    proto = split_part($1, ''/'', 1);
    chan = split_part(split_part($1, ''/'', 2), ''-'', 1);
    IF proto = ''DAHDI'' AND chan <> ''1'' THEN
      RETURN ''select contact.id as contact, department.id as department, department.company, company.shortname
                 from dahdi_channel left join phone on phone.dahdi_channel = dahdi_channel.channel
                                    left join contact on contact.phone = phone.id
                                    left join contact_department on contact_department.contact = contact.id
                                    left join department on department.id = contact_department.department
                                    left join company on company.id = department.company
                 where dahdi_channel.channel = '' || quote_literal(chan) || '' and contact.status <> ''
             || quote_literal(''deleted'');
    END IF;
    IF proto = ''SIP'' OR proto = ''IAX'' OR proto = ''IAX2'' THEN
      RETURN ''select contact.id as contact, department.id as department, department.company, company.shortname
                 from channel left join contact on contact.phone = channel.phone
                              left join contact_department on contact_department.contact = contact.id
                              left join department on department.id = contact_department.department
                              left join company on company.id = department.company
               where channel.username = '' || quote_literal(chan) || '' and contact.status <> ''
            || quote_literal(''deleted'');
    END IF;
    RETURN ''select 1 as contact, 1 as department, 1 as company, cast (1 as varchar) as shortname'';
  END;
' LANGUAGE plpgsql;

CREATE VIEW v_my_channels AS
SELECT channel.protocol, channel.username, 'DAHDI/' || dahdi_channel.channel as dahdi_channel
  FROM contact left join phone on phone.id = contact.phone and phone.status <> 'deleted'
               left join channel on channel.phone = contact.phone and channel.inuse = 'yes'
               left join dahdi_channel on dahdi_channel.id = phone.dahdi_channel and dahdi_channel.channel <> '1'
 WHERE contact.username = USER and contact.status <> 'deleted';
GRANT SELECT on v_my_channels to GROUP astium_users;

CREATE VIEW v_my_colleagues AS
select contact.id,contact_department.department,contact.visible,contact.firstname, contact.lastname,
       phoneline.extension, phoneline.company, phoneline.when_unconditional,
       phoneline.when_unconditional_number, channel.protocol, channel.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'
 where contact.id > 1 and contact.status <> 'deleted' and contact.visible = 'yes'
       and phoneline.company = mycompanyid(CAST(USER as varchar));
GRANT SELECT ON v_my_colleagues TO GROUP astium_users;

CREATE VIEW v_userext_by_dahdichannel AS
select contact.username, phoneline.extension, dahdi_channel.channel as resource
 from  dahdi_channel left join phone on phone.dahdi_channel = dahdi_channel.channel
                   left join contact on contact.phone = phone.id
                   left join phoneline on phoneline.lookupid = contact.id and phoneline.function = 'contact'
                                      and phoneline.line = 1;
GRANT SELECT ON v_userext_by_dahdichannel TO GROUP astium_users;

-- give list of usernames + ext for SIP channels
CREATE VIEW v_userext_by_sipchannel AS
select contact.username, phoneline.extension, channel.username as resource
 from  channel left join phone on channel.phone = phone.id
               left join contact on contact.phone = phone.id
               left join phoneline on phoneline.lookupid = contact.id
                                  and phoneline.function = 'contact'
                                  and phoneline.line = channel.line
                                  and phoneline.status <> 'deleted'
where channel.protocol = 'SIP'; 
GRANT SELECT ON v_userext_by_sipchannel TO GROUP astium_users;
                                    
-- give list of usernames + ext for IAX2 channels
CREATE VIEW v_userext_by_iaxchannel AS
select contact.username, phoneline.extension, channel.username as resource
 from  channel left join phone on channel.phone = phone.id
               left join contact on contact.phone = phone.id
               left join phoneline on phoneline.lookupid = contact.id
                                  and phoneline.function = 'contact'
                                  and phoneline.line = channel.line
                                  and phoneline.status <> 'deleted'
where channel.protocol = 'IAX2';
GRANT SELECT ON v_userext_by_iaxchannel TO GROUP astium_users;

CREATE OR REPLACE FUNCTION insert_phone() RETURNS trigger SECURITY DEFINER AS '
  DECLARE
    pt record;
    ptb record;
  BEGIN
    select into pt * from phonetype where id = NEW.phonetype;
    NEW.dahdi_adsi = pt.dahdi_adsi;
    NEW.threewaycalling = pt.threewaycalling;
    NEW.cancallforward = pt.cancallforward;
    NEW.send_cid = pt.send_cid;
    NEW.send_cid_cw = pt.send_cid_cw;
    NEW.qualify = pt.qualify;
--    NEW.codec1 = pt.codec1; -- leave empty, so they will default to the values for this phonetype.
--    NEW.codec2 = pt.codec2;
--    NEW.codec3 = pt.codec3;
--    NEW.codec4 = pt.codec4;
    NEW.ringtone = pt.ringtone;
    NEW.mwi_notification = pt.mwi_notification;
    NEW.mwi_dialtone = pt.mwi_dialtone;
    NEW.transfer = pt.transfer;
    NEW.callwaiting = pt.callwaiting;
    NEW.maxcalls = pt.maxcalls;
    NEW.logo_url = pt.logo_url;
    NEW.directory_url = pt.directory_url;
    NEW.services_url = pt.services_url;
    NEW.headset_device = pt.headset_device;
    NEW.vol_headset = pt.vol_headset;
    NEW.vol_headset_mic = pt.vol_headset_mic;
    NEW.vol_handset = pt.vol_handset;
    NEW.vol_handset_mic = pt.vol_handset_mic;
    NEW.vol_speaker = pt.vol_speaker;
    NEW.vol_speaker_mic = pt.vol_speaker_mic;
    NEW.vol_ringer = pt.vol_ringer;
    NEW.vol_callwaiting = pt.vol_callwaiting;
    NEW.transfer_on_hangup = pt.transfer_on_hangup;
    NEW.allow_dnd = pt.allow_dnd;
    NEW.show_missed = pt.show_missed;
    NEW.cmc = pt.cmc;
    NEW.vframerate = pt.vframerate;
    NEW.vbitrate = pt.vbitrate;
    NEW.vpacketsize = pt.vpacketsize;
    NEW.hash_xfer_in = pt.hash_xfer_in;
    NEW.hash_xfer_out = pt.hash_xfer_out;

    RETURN NEW;
  END;
' LANGUAGE plpgsql;
DROP TRIGGER insert_phone_trigger ON phone;
CREATE TRIGGER insert_phone_trigger AFTER INSERT ON phone FOR EACH ROW EXECUTE PROCEDURE insert_phone();

DROP FUNCTION rename_zap_fields();
COMMIT;

