alter table switch add column redirect_on varchar(3);
update switch set redirect_on = 'no' where redirect_on IS NULL;
alter table switch alter column redirect_on set NOT NULL;
alter table switch alter column redirect_on set default 'no';

alter table switch add column greeting integer;
update switch set greeting = '1' where greeting is null;
alter table switch alter column greeting set not null; 
alter table switch alter column greeting set default '1';

alter table switch add column phoneline1 integer;
update switch set phoneline1 = '1' where phoneline1 is null;
alter table switch alter column phoneline1 set not null; 
alter table switch alter column phoneline1 set default '1';

alter table switch add column phoneline2 integer;
update switch set phoneline2 = '1' where phoneline2 is null;
alter table switch alter column phoneline2 set not null; 
alter table switch alter column phoneline2 set default '1';

alter table switch add column phoneline3 integer;
update switch set phoneline3 = '1' where phoneline3 is null;
alter table switch alter column phoneline3 set not null; 
alter table switch alter column phoneline3 set default '1';

ALTER TABLE switch ADD CONSTRAINT switch_greeting_constraint  FOREIGN KEY (greeting) REFERENCES soundfile(id);
ALTER TABLE switch ADD CONSTRAINT switch_phoneline1_constraint  FOREIGN KEY (phoneline1) REFERENCES phoneline (id);
ALTER TABLE switch ADD CONSTRAINT switch_phoneline2_constraint  FOREIGN KEY (phoneline2) REFERENCES phoneline (id);
ALTER TABLE switch ADD CONSTRAINT switch_phoneline3_constraint  FOREIGN KEY (phoneline3) REFERENCES phoneline (id);

CREATE TABLE inbound_switch (
 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.

 inbound integer NOT NULL default '1',
--GENERATE::
--SERVICE::
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 switch integer NOT NULL default '1',
--GENERATE::
--SERVICE::
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 priority integer,
--GENERATE::
--SERVICE::
--HELP_EN_INFO:switch priority
--HELP_EN_LONG:<p>A priority can be assigned to a switch. This can be used if there are two active switches.
--HELP_EN_LONG:Choose a priority number between 1 and 99, where 1 has the highest priority.</p>
--HELP_NL_INFO:schema prioriteit
--HELP_NL_LONG:<p>Een schema kan een prioriteit toegekend krijgen. Kies een nummer tussen 1 en 99. 1 heeft de hoogste prioriteit. </p>

 UNIQUE (inbound, switch),
 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON inbound_switch TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON inbound_switch_id_seq TO astium;

ALTER TABLE inbound_switch ADD CONSTRAINT inbound_switch_inbound_constraint FOREIGN KEY (inbound) REFERENCES inbound (id);
ALTER TABLE inbound_switch ADD CONSTRAINT inbound_switch_switch_constraint FOREIGN KEY (switch) REFERENCES switch (id);

ALTER TABLE inbound add column switch_on varchar(3);
UPDATE inbound SET switch_on = 'no' where switch_on is NULL;
ALTER TABLE inbound ALTER column switch_on SET not NULL;
ALTER TABLE inbound ALTER column switch_on SET default 'no';
alter table inbound add constraint inbound_switch_on CHECK (switch_on IN ('yes','no'));

CREATE TABLE phoneline_switch (
 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.

 phoneline integer NOT NULL default '1',
--GENERATE::
--SERVICE::
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 switch integer NOT NULL default '1',
--GENERATE::
--SERVICE::
--HELP_EN_INFO:
--HELP_EN_LONG:
--HELP_NL_INFO:
--HELP_NL_LONG:

 priority integer,
--GENERATE::
--SERVICE::
--HELP_EN_INFO:switch priority
--HELP_EN_LONG:<p>A priority can be assigned to a switch. This can be used if there are two active switches.
--HELP_EN_LONG:Choose a priority number between 1 and 99, where 1 has the highest priority.</p>
--HELP_NL_INFO:schema prioriteit
--HELP_NL_LONG:<p>Een schema kan een prioriteit toegekend krijgen. Kies een nummer tussen 1 en 99. 1 heeft de hoogste prioriteit. </p>

 UNIQUE (phoneline, switch),
 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON phoneline_switch TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON phoneline_switch_id_seq TO astium;

ALTER TABLE phoneline_switch ADD CONSTRAINT phoneline_switch_phoneline_constraint FOREIGN KEY (phoneline) REFERENCES phoneline (id);
ALTER TABLE phoneline_switch ADD CONSTRAINT phoneline_switch_switch_constraint FOREIGN KEY (switch) REFERENCES switch (id);

ALTER TABLE phoneline add column switch_on varchar(3);
UPDATE phoneline SET switch_on = 'no' where switch_on is NULL;
ALTER TABLE phoneline ALTER column switch_on SET not NULL;
ALTER TABLE phoneline ALTER column switch_on SET default 'no';
alter table phoneline add constraint phoneline_switch_on CHECK (switch_on IN ('yes','no'));

-- Get extension to switch to, if switch is active for inbound
CREATE or REPLACE FUNCTION mustinboundswitch(integer) RETURNS varchar AS
'select soundfile.name || ''#'' || pl1.extension || ''#'' || pl2.extension || ''#'' || pl3.extension
   from inbound left join inbound_switch on inbound_switch.inbound = inbound.id 
                left join switch on switch.id = inbound_switch.switch
                left join phoneline as pl1 on pl1.id = switch.phoneline1
                left join phoneline as pl2 on pl2.id = switch.phoneline2
                left join phoneline as pl3 on pl3.id = switch.phoneline3
                left join soundfile on soundfile.id = switch.greeting

-- first, define basic relationship between the tables we are selecting from.

  where inbound.switch_on = ''yes'' and inbound.id = $1 and switch.position = ''on''
  order by priority limit 1;
' LANGUAGE SQL;
 
-- Get extension to switch to, if switch is active for phoneline
CREATE or REPLACE FUNCTION mustphonelineswitch(integer) RETURNS varchar AS
'select soundfile.name || ''#'' || pl1.extension || ''#'' || pl2.extension || ''#'' || pl3.extension
   from phoneline left join phoneline_switch on phoneline_switch.phoneline = phoneline.id 
                left join switch on switch.id = phoneline_switch.switch
                left join phoneline as pl1 on pl1.id = switch.phoneline1
                left join phoneline as pl2 on pl2.id = switch.phoneline2
                left join phoneline as pl3 on pl3.id = switch.phoneline3
                left join soundfile on soundfile.id = switch.greeting

-- first, define basic relationship between the tables we are selecting from.

  where phoneline.switch_on = ''yes'' and phoneline.id = $1 and switch.position = ''on''
  order by priority limit 1;
' LANGUAGE SQL;
 

