-- Copyright (c) 1997-now, NeoNova BV, The Netherlands. All rights Reserved.
--
-- This software is the copyrighted work of NeoNova BV.
-- Use of the software is subject to the general business terms and conditions of NeoNova BV
-- and its related transaction documents like license sheets or other written notices.#
-- Any reproduction or redistribution of the software not in accordance with the license terms
-- is expressly prohibited by law, and may result in severe civil and criminally penalties.
-- Violators will be prosecuted to the maximum extent possible.

BEGIN;

CREATE TABLE phonebook (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this phonebook belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar dit telefoonboek bij hoort.

 name varchar(255) NOT NULL default '',
--HELP_EN_INFO:phonebook name
--HELP_EN_LONG:The name of this phonebook.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van dit telefoonboek.

 description varchar(256) NOT NULL default '',
--HELP_EN_INFO:Description
--HELP_EN_LONG:The description for this phonebook.
--HELP_NL_INFO:Omschrijving
--HELP_NL_LONG:Een omschrijving voor dit telefoonboek.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:state
--HELP_EN_LONG:Role status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van dit telefoonboek.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON phonebook TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON phonebook_id_seq TO astium;

INSERT INTO phonebook (name, company, description, status) VALUES ('empty', 1, 'empty', 'deleted');

ALTER TABLE phonebook ADD CONSTRAINT phonebook_company_constraint FOREIGN KEY (company) REFERENCES company (id);

alter table fastdial_contact drop column descr_prompt;
alter table fastdial_company drop column descr_prompt;

alter table fastdial_contact add column customerid varchar(24);
update fastdial_contact set customerid = '' where customerid IS NULL;
alter table fastdial_contact alter column customerid set default '';
alter table fastdial_contact alter column customerid set not NULL;

alter table fastdial_company drop column pin;

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 phonebook (company, name) values (NEW.id, ''phonebook 1'');
    INSERT INTO phonebook (company, name) values (NEW.id, ''phonebook 2'');
    INSERT INTO phonebook (company, name) values (NEW.id, ''phonebook 3'');
    INSERT INTO phonebook (company, name) values (NEW.id, ''phonebook 4'');
    INSERT INTO phonebook (company, name) 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();

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

COMMIT;

