-- this is some schema for a database driven ivr system I mocked up in
-- FreeSWITCH. The JavaScript that exercises this schema is here:
--
-- http://www.anders.com/1offs/ivr.js.txt
--
-- also see: http://www.anders.com/cms/266/Asterisk.vs.FreeSWITCH
--
-- 2008-05-28 anders-ivr-sql at evantide.com

CREATE TABLE numbers
(
  numberid bigserial NOT NULL,
  datecreated timestamp NOT NULL DEFAULT now(),
  obsolete boolean NOT NULL DEFAULT false,
  number character varying(16) NOT NULL,
  tiedto character varying(16),
  CONSTRAINT numbers_number_unique UNIQUE (number),
  CONSTRAINT numbers_numberid_unique UNIQUE (numberid)
)
WITHOUT OIDS;

-- insert into numbers ( number ) values ( '9195551212' );

CREATE TABLE ivrs (
  ivrid bigserial NOT NULL,
  datecreated timestamp NOT NULL DEFAULT now(),
  obsolete boolean NOT NULL DEFAULT false,
  userid bigint NOT NULL,
  numberid bigint NOT NULL,
  name character varying(128),
  ivrnodeid bigint,
  welcomefile character varying(255),
  CONSTRAINT ivrs_ivrid_unique UNIQUE (ivrid),
  CONSTRAINT ivrs_numberid_numbers_numberid_exists FOREIGN KEY (numberid)
      REFERENCES numbers (numberid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

CREATE TABLE ivrnodes (
  ivrnodeid bigserial NOT NULL,
  datecreated timestamp NOT NULL DEFAULT now(),
  obsolete boolean NOT NULL DEFAULT false,
  ordinal integer NOT NULL,
  ivrid bigint NOT NULL,
  nodename character varying(128),
  promptfile character varying(255),
  invalidselectionfile character varying(255),
  hangupafter int NOT NULL DEFAULT 3,
  CONSTRAINT ivrnodes_ivrnodeid_unique UNIQUE (ivrnodeid),
  CONSTRAINT ivrnodes_ordinal_ivrid_unique UNIQUE (ordinal, ivrid),
  CONSTRAINT ivrnodes_ivrid_ivrs_ivrid_exists FOREIGN KEY (ivrid)
      REFERENCES ivrs (ivrid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

-- alter table ivrs drop CONSTRAINT ivrs_ivrnodeid_ivrs_ivrid_exists;
alter table ivrs add CONSTRAINT ivrs_ivrnodeid_ivrs_ivrid_exists FOREIGN KEY (ivrnodeid)
      REFERENCES ivrnodes (ivrnodeid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;

CREATE TABLE ivroptions (
  ivroptionid bigserial NOT NULL,
  datecreated timestamp NOT NULL DEFAULT now(),
  obsolete boolean NOT NULL DEFAULT false,
  ivrnodeid bigint NOT NULL,
  option character varying(16),           -- [0-9,#,*]
  destinationtype character varying(16),  -- nodeid / number
  destinationdata character varying(255),
  CONSTRAINT ivroptions_ivrnodeid_ivrnodes_ivrnodeid_exists FOREIGN KEY (ivrnodeid)
      REFERENCES ivrnodes (ivrnodeid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

-- Example: Multi Level IVR
--
-- main menu
--  |
--  |--0-- Operator
--  |
--  |--1-- Sales Hunt Group
--  |
--  |--2-- Service IVR
--          |
--          |--1-- Voice Service Queue
--          |
--          |--2-- Internet Support Queue
--

-- -- Make an IVR (we will build our submenu first so we can refrence it from our main menu)
-- insert into ivrs ( userid, numberid, name ) values ( 1, 13, 'Main Tree' );

-- -- Service submenu: Please dial 1 if you need help with your voice service or 2 if you need help with your Internet service.
-- insert into ivrnodes ( ordinal, ivrid, nodename, promptfile, invalidselectionfile, hangupafter )
--   values ( 2, 1, 'Voice - Internet', 'voice-internet.wav', 'invalid-selection.wav', 3 );
-- insert into ivroptions ( ivrnodeid, option, destinationtype, destinationdata )
--   values ( 1, '1', 'queue', '2' ); -- option 1 goes to the queue with id of 2 (voice service queue)
-- insert into ivroptions ( ivrnodeid, option, destinationtype, destinationdata )
--   values ( 1, '2', 'queue', '3' ); -- option 2 goes to the queue with id of 3 (Internet service queue)

-- -- Main Menu: Welcome. Please dial 1 for sales, 2 for service or 0 for the operator.
-- insert into ivrnodes ( ordinal, ivrid, nodename, promptfile, invalidselectionfile, hangupafter )
--   values ( 1, 1, 'Sales - Service - Operator', 'sales-service-operator.wav', 'invalid-selection.wav', 3 );
-- update ivrs set ivrnodeid = 2 where ivrid = 1; -- mark this node (id 2) as our main entry point for this ivr
-- insert into ivroptions ( ivrnodeid, option, destinationtype, destinationdata )
--   values ( 2, '1', 'huntgroup', '3' ); -- option 1 goes to the sales hunt group with id = 3
-- insert into ivroptions ( ivrnodeid, option, destinationtype, destinationdata )
--   values ( 2, '2', 'ivrnode', '2' ); -- option 2 goes to the ivr node with id = 2 (service submenu defined above that lets you pick a product)
-- insert into ivroptions ( ivrnodeid, option, destinationtype, destinationdata )
--   values ( 2, '0', 'user', '9194391000' ); -- option 0 goes to the user at 9194391000