alter table phoneline_forwarding add column when_unconditional_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_unconditional_allow CHECK (when_unconditional_allow IN ('yes','no'));

alter table phoneline_forwarding add column when_busy_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_busy_allow CHECK (when_busy_allow IN ('yes','no'));

alter table phoneline_forwarding add column when_noanswer_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_noanswer_allow CHECK (when_noanswer_allow IN ('yes','no'));

alter table phoneline_forwarding add column when_loggedoff_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_loggedoff_allow CHECK (when_loggedoff_allow IN ('yes','no'));

alter table phoneline_forwarding add column when_dnd_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_dnd_allow CHECK (when_dnd_allow IN ('yes','no'));

alter table phoneline_forwarding add column when_unreachable_allow varchar(3) not NULL default 'no';
alter table phoneline_forwarding add constraint phoneline_forwarding_when_unreachable_allow CHECK (when_unreachable_allow IN ('yes','no'));

CREATE OR REPLACE FUNCTION insert_phoneline() RETURNS trigger SECURITY DEFINER AS '
  DECLARE
    _pid integer;
    _ctid integer;
  BEGIN
    select into _pid id from profile where company = NEW.company and name = ''Default'';
    select into _ctid id from contacttype where company = NEW.company and name = ''default'';
    insert into phoneline_forwarding (profile, phoneline, contacttype, when_unconditional_allow, when_busy_allow,
                when_noanswer_allow, when_loggedoff_allow, when_dnd_allow, when_unreachable_allow)
                values (_pid, NEW.id, _ctid, ''yes'', ''yes'', ''yes'', ''yes'', ''yes'', ''yes'');
    update phoneline set profile = _pid where id = NEW.id;
    NEW.profile = _pid;
    RETURN NEW;
  END;
' LANGUAGE plpgsql;

DROP TRIGGER insert_phoneline_trigger on phoneline;
CREATE TRIGGER insert_phoneline_trigger AFTER INSERT ON phoneline FOR EACH ROW EXECUTE PROCEDURE insert_phoneline();

update phoneline_forwarding set when_unconditional_allow = 'yes', when_busy_allow = 'yes', when_noanswer_allow = 'yes', 
       when_loggedoff_allow = 'yes', when_dnd_allow = 'yes', when_unreachable_allow = 'yes' 
 where contacttype = (select contacttype.id 
                        from phoneline left join contacttype on contacttype.company = phoneline.company and name = 'default' 
                       where phoneline.id = phoneline_forwarding.phoneline);

-- Call Forward Unconditional
CREATE or REPLACE FUNCTION  cfwd_unconditional_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_unconditional varchar;
    _when_unconditional_number varchar;
  BEGIN
    select into _id, _profile, _contact, _when_unconditional, _when_unconditional_number
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_unconditional,
           phoneline_forwarding.when_unconditional_number
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_unconditional_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_unconditional = $3 where profile = _profile and phoneline = _id and when_unconditional_allow = ''yes'';
      IF FOUND THEN
        insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
        insert into history (created, creator, contact, type, lookup, lookupid, value) values
               (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
               ''when_unconditional from '' ||  quote_literal(_when_unconditional) || ''to '' || quote_literal($3));
        select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_unconditional_allow = ''yes'' limit 1;
        INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
      END IF;
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_unconditional_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_unconditional_number varchar;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact
                phoneline.id, phoneline.profile, phoneline.contact
      from phoneline 
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    select into _when_unconditional_number when_unconditional_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and when_unconditional_allow = ''yes'' limit 1;
    update phoneline_forwarding set when_unconditional_number = $3 where profile = _profile and phoneline = _id and when_unconditional_allow = ''yes'';
    IF FOUND THEN
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      IF _when_unconditional_number IS NULL THEN
          _when_unconditional_number := '''';
      END IF;
      _value := ''when_unconditional_number from '' ||  quote_literal(_when_unconditional_number) || ''to '' || quote_literal($3);
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
      select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_unconditional_allow = ''yes'' limit 1;
      INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
    END IF;
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_unconditional_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_unconditional
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_unconditional_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_unconditional_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

-- Call Forward Busy
CREATE or REPLACE FUNCTION  cfwd_busy_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_busy varchar;
    _when_busy_number varchar;
  BEGIN
    select into _id, _profile, _contact, _when_busy, _when_busy_number
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_busy,
           phoneline_forwarding.when_busy_number
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_busy_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_busy = $3 where profile = _profile and phoneline = _id and when_busy_allow = ''yes'';
      IF FOUND THEN
        insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
        insert into history (created, creator, contact, type, lookup, lookupid, value) values
               (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
               ''when_busy from '' ||  quote_literal(_when_busy) || ''to '' || quote_literal($3));
        select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_busy_allow = ''yes'' limit 1;
        INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
      END IF;
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_busy_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_busy_number varchar;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact
                phoneline.id, phoneline.profile, phoneline.contact
      from phoneline 
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    select into _when_busy_number when_busy_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and when_busy_allow = ''yes'' limit 1;
    update phoneline_forwarding set when_busy_number = $3 where profile = _profile and phoneline = _id and when_busy_allow = ''yes'';
    IF FOUND THEN
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      IF _when_busy_number IS NULL THEN
          _when_busy_number := '''';
      END IF;
      _value := ''when_busy_number from '' ||  quote_literal(_when_busy_number) || ''to '' || quote_literal($3);
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
      select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_busy_allow = ''yes'' limit 1;
      INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
    END IF;
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_busy_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_busy
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_busy_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_busy_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;


-- Call Forward Noanswer
CREATE or REPLACE FUNCTION  cfwd_noanswer_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_noanswer varchar;
    _when_noanswer_number varchar;
  BEGIN
    select into _id, _profile, _contact, _when_noanswer, _when_noanswer_number
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_noanswer,
           phoneline_forwarding.when_noanswer_number
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_noanswer_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_noanswer = $3 where profile = _profile and phoneline = _id and when_noanswer_allow = ''yes'';
      IF FOUND THEN
        insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
        insert into history (created, creator, contact, type, lookup, lookupid, value) values
               (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
               ''when_noanswer from '' ||  quote_literal(_when_noanswer) || ''to '' || quote_literal($3));
        select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_noanswer_allow = ''yes'' limit 1;
        INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
      END IF;
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_noanswer_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_noanswer_number varchar;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact
                phoneline.id, phoneline.profile, phoneline.contact
      from phoneline 
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    select into _when_noanswer_number when_noanswer_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and when_noanswer_allow = ''yes'' limit 1;
    update phoneline_forwarding set when_noanswer_number = $3 where profile = _profile and phoneline = _id and when_noanswer_allow = ''yes'';
    IF FOUND THEN
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      IF _when_noanswer_number IS NULL THEN
          _when_noanswer_number := '''';
      END IF;
      _value := ''when_noanswer_number from '' ||  quote_literal(_when_noanswer_number) || ''to '' || quote_literal($3);
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
       select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_noanswer_allow = ''yes'' limit 1;
      INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
    END IF;
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_noanswer_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_noanswer
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_noanswer_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_noanswer_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

-- Call Forward Loggedoff
CREATE or REPLACE FUNCTION  cfwd_loggedoff_set(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_loggedoff varchar;
    _when_loggedoff_number varchar;
  BEGIN
    select into _id, _profile, _contact, _when_loggedoff, _when_loggedoff_number
           phoneline.id, phoneline.profile, phoneline.contact, phoneline_forwarding.when_loggedoff,
           phoneline_forwarding.when_loggedoff_number
      from phoneline left join phoneline_forwarding on phoneline.id = phoneline_forwarding.phoneline
                                                   and phoneline.profile = phoneline_forwarding.profile
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    IF _when_loggedoff_number = '''' AND $3 = ''forward'' THEN
      return ''0'';
    ELSE
      update phoneline_forwarding set when_loggedoff = $3 where profile = _profile and phoneline = _id and when_loggedoff_allow = ''yes'';
      IF FOUND THEN
        insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
        insert into history (created, creator, contact, type, lookup, lookupid, value) values
               (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id,
               ''when_loggedoff from '' ||  quote_literal(_when_loggedoff) || ''to '' || quote_literal($3));
        select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_loggedoff_allow = ''yes'' limit 1;
        INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
      END IF;
      return ''0'';
    END IF;
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_setnumber(varchar, varchar, varchar, varchar) RETURNS INTEGER AS '
  DECLARE
    _id int;
    _profile int;
    _contact int;
    _when_loggedoff_number varchar;
    _value varchar;
  BEGIN
    select into _id, _profile, _contact
                phoneline.id, phoneline.profile, phoneline.contact
      from phoneline
     where phoneline.company = ( select id from company  where shortname = $1 and status = ''active'') and
           extension = $2 and phoneline.status <> ''deleted'';
    select into _when_loggedoff_number when_loggedoff_number from phoneline_forwarding
     where profile = _profile and phoneline = _id and when_loggedoff_allow = ''yes'' limit 1;
    update phoneline_forwarding set when_loggedoff_number = $3 where profile = _profile and phoneline = _id and when_loggedoff_allow = ''yes'';
    IF FOUND THEN
      insert into activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline'', _id, ''QUEUED'', NOW() );
      IF _when_loggedoff_number IS NULL THEN
          _when_loggedoff_number := '''';
      END IF;
      _value := ''when_loggedoff_number from '' ||  quote_literal(_when_loggedoff_number) || ''to '' || quote_literal($3);
      insert into history (created, creator, contact, type, lookup, lookupid, value) values
             (now(), $4::text::int4, _contact, ''update'', ''phoneline'', _id, _value);
      select into _id id from phoneline_forwarding where profile = _profile and phoneline = _id and when_loggedoff_allow = ''yes'' limit 1;
      INSERT INTO activate(action, type, which, status, created ) VALUES (''modify'', ''phoneline_forwarding'', _id, ''QUEUED'', NOW() );
    END IF;
    return ''0'';
  END;
' LANGUAGE plpgsql;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_get(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_loggedoff
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype = 
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

CREATE or REPLACE FUNCTION  cfwd_loggedoff_getnumber(varchar, varchar) RETURNS varchar AS
'select phoneline_forwarding.when_loggedoff_number
 from   phoneline left join company on company.id = phoneline.company
                  left join phoneline_forwarding on (phoneline_forwarding.profile = phoneline.profile
                                                 and phoneline_forwarding.phoneline = phoneline.id
                                                 and phoneline_forwarding.contacttype =
                                                     (select id from contacttype where company = phoneline.company and name = ''default''))
 where  company.shortname = $1 and extension = $2 and phoneline.status <> ''deleted'';
' LANGUAGE SQL;

