USE `asterisk`;

UPDATE sipconfig  SET value = 'astrad011' WHERE name = 'version';

--
-- Sippeers and sipusers replaced by sipdevices
--

DROP TABLE sippeers2;
DROP TABLE sippeers;
DROP TABLE sipusers;

--
-- Table structure for table `sipdevices`
--

CREATE TABLE IF NOT EXISTS `sipdevices` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  `md5secret` varchar(32) default NULL,
  `host` varchar(15) NOT NULL default 'dynamic',
  `port` smallint(5) unsigned default NULL,
  `context` varchar(80) NOT NULL default 'forbidden',
  `insecure` varchar(15) DEFAULT 'no',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ;

--
-- Trigger of  table `sipdevices`
--

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_sipdevices`|
CREATE TRIGGER `Trg_Insert_sipdevices` AFTER INSERT ON `sipdevices` FOR EACH ROW BEGIN
DECLARE devicetrustrpid enum('yes','no') DEFAULT NULL;
DECLARE devicesendrpid enum('yes','no') DEFAULT NULL;
DECLARE devicefromuser varchar(80) DEFAULT NULL;
DECLARE devicetype enum('friend','user','peer') DEFAULT 'peer';
IF NEW.context='fromnode' THEN
  SET devicetrustrpid='yes';
  SET devicesendrpid='yes';
  SET devicefromuser='nodes';
END IF;
IF NEW.context='fromaccount' THEN
  SET devicetype='friend';
END IF;
INSERT IGNORE INTO sipdevices2 (name,host,port,context,insecure,md5secret,trustrpid,sendrpid,fromuser,type) VALUES (NEW.name,NEW.host,NEW.port,NEW.context,NEW.insecure,NEW.md5secret,devicetrustrpid,devicesendrpid,devicefromuser,devicetype);
END |

DROP TRIGGER IF EXISTS `Trg_Update_sipdevices`|
CREATE TRIGGER `Trg_Update_sipdevices` AFTER UPDATE ON `sipdevices` FOR EACH ROW BEGIN
DECLARE devicetrustrpid enum('yes','no') DEFAULT NULL;
DECLARE devicesendrpid enum('yes','no') DEFAULT NULL;
DECLARE devicefromuser varchar(80) DEFAULT NULL;
DECLARE devicetype enum('friend','user','peer') DEFAULT 'peer';
IF NEW.context='fromnode' THEN
  SET devicetrustrpid='yes';
  SET devicesendrpid='yes';
  SET devicefromuser='nodes';
END IF;
IF NEW.context='fromaccount' THEN
  SET devicetype='friend';
END IF;
IF NEW.port IS NOT NULL THEN
  UPDATE sipdevices2 SET name=NEW.name, host=NEW.host, port=NEW.port, context=NEW.context, insecure=NEW.insecure, md5secret=NEW.md5secret, trustrpid=devicetrustrpid, sendrpid=devicesendrpid, fromuser=devicefromuser, type=devicetype WHERE name=OLD.name;
ELSE
  UPDATE sipdevices2 SET name=NEW.name, host=NEW.host, context=NEW.context, insecure=NEW.insecure, md5secret=NEW.md5secret, trustrpid=devicetrustrpid, sendrpid=devicesendrpid, fromuser=devicefromuser, type=devicetype WHERE name=OLD.name;
END IF;
END |

DROP TRIGGER IF EXISTS `Trg_Delete_sipdevices`|
CREATE TRIGGER `Trg_Delete_sipdevices` AFTER DELETE ON `sipdevices` FOR EACH ROW BEGIN
DELETE FROM sipdevices2 WHERE name=OLD.name;
END |

DELIMITER ;

--
-- Table structure for table `sipdevices2`
--

CREATE TABLE IF NOT EXISTS `sipdevices2` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  `md5secret` varchar(32) default NULL,
  `host` varchar(15) NOT NULL default 'dynamic',
  `port` smallint(5) unsigned default NULL,
  `context` varchar(80) NOT NULL default 'forbidden',
  `insecure` varchar(15) DEFAULT 'no',
  `ipaddr` varchar(45) DEFAULT NULL,
  `regserver` varchar(20) DEFAULT NULL,
  `regseconds` int(11) DEFAULT NULL,
  `fullcontact` varchar(80) NOT NULL DEFAULT '',
  `lastms` int(11) DEFAULT NULL,
  `defaultuser` varchar(45) DEFAULT NULL,
  `useragent` varchar(20) DEFAULT 'Unknown',
  `nat` varchar(5) NOT NULL DEFAULT 'yes',
  `type` enum('friend','user','peer') DEFAULT 'peer',
  `fromuser` varchar(80) DEFAULT NULL,
  `trustrpid` enum('yes','no') DEFAULT NULL,
  `sendrpid` enum('yes','no') DEFAULT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ;

--
-- Trigger of  table `sipdevices2`
--

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_sipdevices2`|
CREATE TRIGGER `Trg_Update_sipdevices2` AFTER UPDATE ON `sipdevices2` FOR EACH ROW BEGIN
IF NEW.regseconds IS NOT NULL AND NEW.port IS NOT NULL AND NEW.ipaddr IS NOT NULL AND NEW.name IS NOT NULL AND FROM_UNIXTIME(NEW.regseconds) > (NOW() - INTERVAL 5 SECOND) AND NEW.ipaddr NOT LIKE '0.0.0.0' AND NEW.port != 0 THEN
  CALL REGISTER (NEW.name,NEW.ipaddr,NEW.port,FROM_UNIXTIME(NEW.regseconds),NEW.useragent);
END IF;
END |
DELIMITER ;

--
-- PROCEDURE set_route
--

DELIMITER |
DROP PROCEDURE IF EXISTS `set_route`|
CREATE PROCEDURE `set_route`(IN USR VARCHAR(80), IN ipsource VARCHAR(15))
BEGIN
DECLARE ROUTE text DEFAULT '';
DECLARE done INT DEFAULT 0;
DECLARE tdomain varchar(15) default NULL;
DECLARE tipaddr varchar(15) default NULL;
DECLARE tport smallint(5) unsigned default NULL;
DECLARE prev_ipaddr varchar(15) default NULL;
DECLARE prev_port smallint(5) unsigned default NULL;
DECLARE fg  INT DEFAULT 0;
DECLARE ID_ INT DEFAULT 0;
DECLARE myIP VARCHAR(15);
DECLARE local_call INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT domain,ipaddr,port FROM multiple_ua WHERE username=USR;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
CREATE TEMPORARY TABLE tmpdomain (
  domain varchar(15) NOT NULL DEFAULT '' ,
  PRIMARY KEY  (`domain`)
);
DELETE FROM route_multiple_ua WHERE username=USR;
IF (SELECT 1 FROM  multiple_ua WHERE domain=ipsource AND username=USR LIMIT 1) THEN
  SET local_call=1;
END IF;
SET myIP=(SELECT value FROM sipconfig WHERE name='ipaddr');
SET ID_=(SELECT value FROM sipconfig WHERE name='ua_id');
  OPEN cur;
  read_loop: LOOP
  FETCH cur INTO tdomain,tipaddr,tport;
  IF done THEN
    LEAVE read_loop;
  END IF;
  IF (tdomain=myIP) THEN
    IF (fg=0) THEN
      SET fg=1;
      SET prev_ipaddr=tipaddr;
      SET prev_port=tport;
    ELSEIF (fg=1) THEN
      SET fg=2;
      INSERT INTO sipdevices2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),prev_ipaddr,prev_ipaddr,prev_port) ON DUPLICATE KEY UPDATE host=prev_ipaddr, ipaddr=prev_ipaddr, port=prev_port;
      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);
      SET ID_=ID_+1;
      IF (ID_>1000) THEN
        SET ID_=1;
      END IF;
      INSERT INTO sipdevices2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),tipaddr,tipaddr,tport) ON DUPLICATE KEY UPDATE host=tipaddr, ipaddr=tipaddr, port=tport;
      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);
      SET ID_=ID_+1;
      IF (ID_>1000) THEN
        SET ID_=1;
      END IF;
    ELSE
      INSERT INTO sipdevices2 (name,host,ipaddr,port) VALUES (CONCAT('UA',ID_),tipaddr,tipaddr,tport) ON DUPLICATE KEY UPDATE host=tipaddr, ipaddr=tipaddr, port=tport;
      SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@UA',ID_);
      SET ID_=ID_+1;
      IF (ID_>1000) THEN
        SET ID_=1;
      END IF;
    END IF;
  ELSEIF local_call=0 AND (SELECT 1 FROM tmpdomain WHERE domain=tdomain LIMIT 1) IS NULL THEN
    SET ROUTE=CONCAT(ROUTE,'&SIP/',USR,'@',tdomain);
    INSERT INTO tmpdomain VALUES (tdomain);
  END IF;
  END LOOP;
  CLOSE cur;
  IF (fg=1) THEN
    SET ROUTE=CONCAT(ROUTE,'&SIP/',USR);
  ELSE
    UPDATE sipconfig SET value=ID_ WHERE name='ua_id';
  END IF;
  IF ROUTE NOT LIKE '' THEN
    INSERT IGNORE INTO route_multiple_ua VALUES (USR,SUBSTRING(ROUTE,2));
  END IF;
END |
DELIMITER ;

--
-- Event SCHEDULE `Evt_Unregister`
--

DROP EVENT IF EXISTS `Evt_Unregister`;
CREATE EVENT `Evt_Unregister` ON SCHEDULE EVERY 5 MINUTE
DO
  DELETE FROM location3 WHERE expires < DATE_SUB(NOW(), INTERVAL 5 MINUTE);

