USE `asterisk`;

INSERT INTO sipconfig  VALUES ('version','astrad010');

ALTER TABLE sippeers2 ADD COLUMN `fromuser` varchar(80) DEFAULT NULL;
ALTER TABLE sippeers2 ADD COLUMN `trustrpid` enum('yes','no') DEFAULT NULL;
ALTER TABLE sippeers2 ADD COLUMN `sendrpid` enum('yes','no') DEFAULT NULL;
ALTER TABLE sippeers2 ADD COLUMN `useragent` varchar(20) DEFAULT 'Unknown';

--
-- Trigger of  table `sippeers`
--

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

DROP TRIGGER IF EXISTS `Trg_Update_sippeers`|
CREATE TRIGGER `Trg_Update_sippeers` AFTER UPDATE ON `sippeers` FOR EACH ROW BEGIN
DECLARE devicetrustrpid enum('yes','no') DEFAULT NULL;
DECLARE devicesendrpid enum('yes','no') DEFAULT NULL;
DECLARE devicefromuser varchar(80) DEFAULT NULL;
IF NEW.context='fromnode' THEN
  SET devicetrustrpid='yes';
  SET devicesendrpid='yes';
  SET devicefromuser='nodes';
END IF;
IF NEW.port IS NOT NULL THEN
  UPDATE sippeers2 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 WHERE name=OLD.name;
ELSE
  UPDATE sippeers2 SET name=NEW.name, host=NEW.host, context=NEW.context, insecure=NEW.insecure, md5secret=NEW.md5secret, trustrpid=devicetrustrpid, sendrpid=devicesendrpid, fromuser=devicefromuser WHERE name=OLD.name;
END IF;
END |

DROP TRIGGER IF EXISTS `Trg_Delete_sippeers`|
CREATE TRIGGER `Trg_Delete_sippeers` AFTER DELETE ON `sippeers` FOR EACH ROW BEGIN
DELETE FROM sippeers2 WHERE name=OLD.name;
END |

DELIMITER ;

--
-- Trigger of  table `sippeers2`
--

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_sippeers2`|
CREATE TRIGGER `Trg_Update_sippeers2` AFTER UPDATE ON `sippeers2` 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 REGISTER
--

DELIMITER |
DROP PROCEDURE IF EXISTS `REGISTER`|
CREATE PROCEDURE `REGISTER`(IN USR VARCHAR(80), IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN EXR DATETIME, IN UA_ VARCHAR(20))
BEGIN
IF (PORT_ != 0 && IP_ != '(null)') THEN
  IF (SELECT 1 FROM location3 WHERE username=USR AND ipaddr=IP_ AND port=PORT_) THEN
    UPDATE location3 SET expires=EXR WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND useragent=UA_;
  ELSE
    INSERT INTO location3 (username,ipaddr,port,expires,useragent) VALUES (USR,IP_,PORT_,EXR,UA_);
  END IF;
END IF;
END |
DELIMITER ;

--
-- Table structure for table `location3`
--

CREATE TABLE `location3` (
  `username` varchar(80) NOT NULL default '',
  `ipaddr` varchar(15) default NULL,
  `port` smallint(5) unsigned default NULL,
  `expires` datetime default NULL,
  `register` timestamp(14) NOT NULL,
  `useragent` varchar(20) DEFAULT NULL,
  PRIMARY KEY  (`username`,`ipaddr`,`port`)
) ;

--
-- Trigger of table `location3`
--

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_location3`|
CREATE TRIGGER `Trg_Insert_location3` AFTER INSERT ON `location3` FOR EACH ROW BEGIN
DECLARE IP  VARCHAR(15)  DEFAULT '';
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL THEN
  SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');
  INSERT INTO registration (username,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,IP,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
END |

--

DROP TRIGGER IF EXISTS `Trg_Update_location3`|
CREATE TRIGGER `Trg_Update_location3` AFTER UPDATE ON `location3` FOR EACH ROW BEGIN
DECLARE IP  VARCHAR(15)  DEFAULT '';
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL THEN
  SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');
  INSERT INTO registration (username,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,IP,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
END |

--

DROP TRIGGER IF EXISTS `Trg_Delete_location3`|
CREATE TRIGGER `Trg_Delete_location3` AFTER DELETE ON `location3` FOR EACH ROW BEGIN
DECLARE IP  VARCHAR(15)  DEFAULT '';
SET IP=(SELECT value FROM sipconfig WHERE name='ipaddr');
INSERT INTO registration (username,domain,ipaddr,port,expires,useragent,action) VALUES (OLD.username,IP,OLD.ipaddr,OLD.port,OLD.expires,OLD.useragent,'UNREGISTER');
END |
DELIMITER ;

--
-- Table structure for table `registration`
--

CREATE TABLE `registration` (
  `id`  int(11) NOT NULL auto_increment,
  `username` varchar(80) NOT NULL default '',
  `domain` varchar(15) NOT NULL default '',
  `ipaddr` varchar(15) default NULL,
  `port` smallint(5) unsigned default NULL,
  `expires` datetime default NULL,
  `register` datetime default NULL,
  `useragent` varchar(20) DEFAULT NULL,
  `action`  varchar(15) default 'REGISTER',
  PRIMARY KEY  (`id`)
) ;

--
-- 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 sippeers2 (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 sippeers2 (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 sippeers2 (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 ;
