use asterisk;

UPDATE sipconfig SET value = '<%= astrad_version %>' WHERE name = 'version';

DELIMITER |
DROP PROCEDURE IF EXISTS `set_route`|
CREATE PROCEDURE `set_route`(IN USR VARCHAR(80), IN ipsource VARCHAR(15), IN multipleua INT)
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 cur2 CURSOR FOR SELECT domain,ipaddr,port FROM multiple_ua WHERE username=USR AND domain=(select value from sipconfig where name = 'ipaddr');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TABLE IF EXISTS tmpdomain;
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;
OPEN cur2;
read_loop: LOOP
  IF multipleua = 1 THEN
    FETCH cur INTO tdomain,tipaddr,tport;
  ELSE
    FETCH cur2 INTO tdomain,tipaddr,tport;
  END IF;
  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;
CLOSE cur2;
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 ;