BEGIN;

alter table role add column ismanager varchar(3) NOT NULL default 'no';
alter table role add constraint role_ismanager CHECK (ismanager IN ('yes','no'));

alter table role add column iscompanymanager varchar(3) NOT NULL default 'no';
alter table role add constraint role_iscompanymanager CHECK (iscompanymanager IN ('yes','no'));

alter table role add column isoperator varchar(3) NOT NULL default 'no';
alter table role add constraint role_isoperator CHECK (isoperator IN ('yes','no'));

alter table role add column isreceptionist varchar(3) NOT NULL default 'no';
alter table role add constraint role_isreceptionist CHECK (isreceptionist IN ('yes','no'));

alter table role add column spyable varchar(3) NOT NULL default 'no';
alter table role add constraint role_spyable CHECK (spyable IN ('yes','no'));

alter table role add column bargeable varchar(3) NOT NULL default 'no';
alter table role add constraint role_bargeable CHECK (bargeable IN ('yes','no'));

alter table role add column canlogout varchar(3) NOT NULL default 'yes';
alter table role add constraint role_canlogout CHECK (canlogout IN ('yes','no'));

alter table role add column visible varchar(3) NOT NULL default 'yes';
alter table role add constraint role_visible CHECK (visible IN ('yes','no'));

alter table role add column allow_add_fastdial varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_add_fastdial CHECK (allow_add_fastdial IN ('yes','no'));

alter table role add column allow_dial_tollfree varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_tollfree CHECK (allow_dial_tollfree IN ('yes','no', 'pin'));

alter table role add column allow_dial_local varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_local CHECK (allow_dial_local IN ('yes','no', 'pin'));

alter table role add column allow_dial_longdistance varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_longdistance CHECK (allow_dial_longdistance IN ('yes','no', 'pin'));

alter table role add column allow_dial_mobile varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_mobile CHECK (allow_dial_mobile IN ('yes','no', 'pin'));

alter table role add column allow_dial_international varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_international CHECK (allow_dial_international IN ('yes','no', 'pin'));

alter table role add column allow_dial_entertain varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_dial_entertain CHECK (allow_dial_entertain IN ('yes','no', 'pin'));

alter table role add column allow_dial_premium varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_premium CHECK (allow_dial_premium IN ('yes','no', 'pin'));

alter table role add column allow_dial_adult varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_dial_adult CHECK (allow_dial_adult IN ('yes','no', 'pin'));

alter table role add column allow_dial_voip varchar(3) NOT NULL default 'yes';
alter table role add constraint role_allow_dial_voip CHECK (allow_dial_voip IN ('yes','no', 'pin'));

alter table role add column allow_bypass_forward varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_bypass_forward CHECK (allow_bypass_forward IN ('yes','no', 'pin'));

alter table role add column allow_view_reports varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_view_reports CHECK (allow_view_reports IN ('yes','no', 'pin'));

alter table role add column allow_spy varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_spy CHECK (allow_spy IN ('yes','no', 'pin'));

alter table role add column allow_barge varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_barge CHECK (allow_barge IN ('yes','no', 'pin'));

alter table role add column allow_page varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_page CHECK (allow_page IN ('yes','no', 'pin'));

alter table role add column allow_record varchar(3) NOT NULL default 'no';
alter table role add constraint role_allow_record CHECK (allow_record IN ('yes','no', 'pin'));

alter table history alter column created set default CURRENT_TIMESTAMP;

update role set allow_page = 'yes' where id = 1;
SELECT setval('role_id_seq', max(id)) FROM role;
INSERT INTO role (name, company, description, isoperator) VALUES ('empty', 2, 'empty', 'yes');
INSERT INTO role (name, company, description) VALUES ('Default', 3, 'Default Role');

CREATE OR REPLACE FUNCTION insert_company() RETURNS trigger SECURITY DEFINER AS '
  DECLARE
    list record;
  BEGIN
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES 
                                (NEW.id, ''110'', 1, '''', '''', ''HOLD'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES 
                                (NEW.id, ''110'', 2, '''', '''', ''TRANSFER'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES 
                                (NEW.id, ''110'', 3, '''', '''', ''MUTE'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES 
                                (NEW.id, ''110'', 4, '''', '''', ''DND'');

    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 1, '''', '''', ''mynumber'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 2, '''', '''', ''myextra'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 3, '''', '''', ''F_CONFERENCE'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 4, '''', '''', ''F_REDIAL'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 5, '''', '''', ''F_TRANSFER'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''46'', 6, '''', '''', ''none'');

    -- Aastra 6753i
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 1, '''', '''', ''record'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 2, '''', '''', ''F_DIRECTORY'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 3, '''', '''', ''callers'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 4, ''*25'', '''', ''speed'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 5, '''', '''', ''F_DND'');
    insert into phonetypebutton (company, phonetype, button, number, label, function) VALUES (NEW.id, ''93'', 6, '''', '''', ''F_TRANSFER'');

    insert into contacttype (name, company, description) values (''default'', NEW.id, ''Default'');
    insert into contacttype (name, company, description) values (''internal'', NEW.id, ''Internal Call'');

    insert into profile (name, company) values (''Default'', NEW.id);
    insert into presence (name, company) values (''Available'', NEW.id);
    insert into role(name, company) values (''Default'', NEW.id);

    INSERT INTO phonebook (company, name, description) values (NEW.id, ''phonebook 1'', '''');
    INSERT INTO phonebook (company, name, description) values (NEW.id, ''phonebook 2'', '''');
    INSERT INTO phonebook (company, name, description) values (NEW.id, ''phonebook 3'', '''');
    INSERT INTO phonebook (company, name, description) values (NEW.id, ''phonebook 4'', '''');
    INSERT INTO phonebook (company, name, description) values (NEW.id, ''phonebook 5'', '''');

    -- give this new company all system extensions
    IF NEW.id > 3 THEN
      FOR list in SELECT extension, function, callerid from phoneline where phoneline.company = 3 and function = ''system'' LOOP
        insert into phoneline (company, extension, function, callerid) VALUES (NEW.id, list.extension, ''system'', list.callerid);
      END LOOP;
    END IF;
    -- and phonetype defaults
    IF NEW.id > 2 THEN
      FOR list in SELECT id, hash_xfer_in, hash_xfer_out, qualify, dtmfmode, vol_speaker, vol_speaker_mic, 
                         vol_handset, vol_handset_mic, vol_headset, vol_headset_mic from phonetype LOOP
        insert into phonetypephone (company, phonetype,  hash_xfer_in, hash_xfer_out, qualify, dtmfmode, vol_speaker, 
                                    vol_speaker_mic, vol_handset, vol_handset_mic, vol_headset, vol_headset_mic) VALUES 
                                   (NEW.id, list.id,  list.hash_xfer_in, list.hash_xfer_out, list.qualify, list.dtmfmode, list.vol_speaker,
                                   list.vol_speaker_mic, list.vol_handset, list.vol_handset_mic, list.vol_headset, list.vol_headset_mic);
      END LOOP;
    END IF;

    RETURN NEW;
  END;
' LANGUAGE plpgsql;

DROP TRIGGER insert_company ON company;
CREATE TRIGGER insert_company AFTER INSERT ON company FOR EACH ROW EXECUTE PROCEDURE insert_company();

COMMIT;

