-- MySQL dump 10.13  Distrib 5.1.49, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: 
-- ------------------------------------------------------
-- Server version	5.1.49-3~bpo50+1-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `asterisk`
--

DROP DATABASE IF EXISTS `asterisk`;

CREATE DATABASE IF NOT EXISTS `asterisk` DEFAULT CHARACTER SET latin1;

USE `asterisk`;

--
-- Table structure for table `multiple_ua`
--

DROP TABLE IF EXISTS `multiple_ua`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `multiple_ua` (
  `username` varchar(80) NOT NULL DEFAULT '',
  `domain` varchar(15) NOT NULL DEFAULT '',
  `ipaddr` varchar(15) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`username`,`domain`,`ipaddr`,`port`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

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

DROP TABLE IF EXISTS `sipdevices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sipdevices` (
  `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',
  `i_bill_auth` tinyint (2) unsigned,
  `i_bill_acc` set('1','2','3','4'),
  PRIMARY KEY (`name`),
  KEY `name_2` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=79 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Current Database: `astrad`
--

DROP DATABASE IF EXISTS `astrad`;

CREATE DATABASE IF NOT EXISTS `astrad` DEFAULT CHARACTER SET latin1;

USE `astrad`;

--
-- Table structure for table `config`
--

DROP TABLE IF EXISTS `config`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `config` (
  `name` varchar(80) NOT NULL DEFAULT '',
  `value` varchar(80) NOT NULL DEFAULT '',
  `description` text,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`name`,`i_billing`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `location2`
--

DROP TABLE IF EXISTS `location2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location2` (
  `username` varchar(80) NOT NULL DEFAULT '',
  `domain` varchar(15) NOT NULL DEFAULT '',
  `dba` varchar(15) NOT NULL DEFAULT '',
  `ipaddr` varchar(15) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  `expires` datetime DEFAULT NULL,
  `register` datetime DEFAULT NULL,
  `reception` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `useragent` varchar(20) DEFAULT 'Unknown',
  PRIMARY KEY (`username`,`domain`,`dba`,`ipaddr`,`port`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

DELIMITER |
CREATE TRIGGER `Trg_Insert_location2` AFTER INSERT ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN
  INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
CALL add_multiple_ua(NEW.username,NEW.domain,NEW.ipaddr,NEW.port,NULL,NULL,NULL,NULL,"ADD");
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_location2` AFTER UPDATE ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF NEW.ipaddr IS NOT NULL AND NEW.port IS NOT NULL AND NEW.expires IS NOT NULL AND NEW.dba=IPAD THEN
  IF OLD.username != NEW.username OR OLD.dba != NEW.dba OR OLD.domain != NEW.domain OR OLD.ipaddr != NEW.ipaddr OR OLD.port != NEW.port THEN
    INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (OLD.username,OLD.dba,OLD.domain,OLD.ipaddr,OLD.port,OLD.expires,OLD.register,OLD.useragent,'UNREGISTER');
  END IF;
  INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (NEW.username,NEW.dba,NEW.domain,NEW.ipaddr,NEW.port,NEW.expires,NEW.register,NEW.useragent,'REGISTER');
END IF;
CALL add_multiple_ua(NEW.username,NEW.domain,NEW.ipaddr,NEW.port,OLD.username,OLD.domain,OLD.ipaddr,OLD.port,"UPD");
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_location2` AFTER DELETE ON `location2` FOR EACH ROW BEGIN
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF OLD.dba=IPAD THEN
  INSERT INTO registration (username,dba,domain,ipaddr,port,expires,register,useragent,action) VALUES (OLD.username,OLD.dba,OLD.domain,OLD.ipaddr,OLD.port,OLD.expires,OLD.register,OLD.useragent,'UNREGISTER');
END IF;
CALL add_multiple_ua(OLD.username,OLD.domain,OLD.ipaddr,OLD.port,NULL,NULL,NULL,NULL,"DEL");
END |
DELIMITER ;



DELIMITER |
DROP EVENT IF EXISTS `Evt_location2_Delete`|
CREATE EVENT `Evt_location2_Delete` ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
DELETE  FROM location2 WHERE expires < DATE_SUB(NOW(), INTERVAL 1 HOUR);
END |
DELIMITER ;



--
-- Table structure for table `location2_history`
--

DROP TABLE IF EXISTS `location2_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location2_history` (
  `start` datetime DEFAULT NULL,
  `stop` datetime DEFAULT NULL,
  `domain` varchar(15) NOT NULL DEFAULT '',
  `username` varchar(80) NOT NULL DEFAULT '',
  `ipaddr` varchar(15) NOT NULL DEFAULT '',
  `port` smallint(5) unsigned NOT NULL DEFAULT '0',
  `count` int(10) unsigned DEFAULT '1',
  PRIMARY KEY (`username`,`domain`,`ipaddr`,`port`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `realm`
--

DROP TABLE IF EXISTS `realm`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `realm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(80) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `val` (`val`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;



DELIMITER |
CREATE TRIGGER `Trg_Insert_realm` AFTER INSERT ON `realm` FOR EACH ROW BEGIN
    CALL SET_REALM(NEW.val,'INSERT');
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_realm` AFTER UPDATE ON `realm` FOR EACH ROW BEGIN
  IF NEW.id=(SELECT id FROM realm ORDER BY id DESC LIMIT 1) AND NEW.val <> OLD.val THEN
      CALL SET_REALM(NEW.val,'UPDATE');
  END IF;
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_realm` AFTER DELETE ON `realm` FOR EACH ROW BEGIN
DECLARE rm VARCHAR(30);
SET rm=(SELECT val FROM realm ORDER BY id DESC LIMIT 1);
  IF rm IS NOT NULL AND rm <> OLD.val THEN
      CALL SET_REALM(rm,'DELETE');
  END IF;
END |
DELIMITER ;

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

DROP TABLE IF EXISTS `registration`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `registration` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(80) NOT NULL DEFAULT '',
  `dba` varchar(15) DEFAULT NULL,
  `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,
  `action` varchar(15) DEFAULT 'register',
  `useragent` varchar(20) DEFAULT 'Unknown',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11646 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `request`
--

DROP TABLE IF EXISTS `request`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `request` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=96721 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `triggers_log`
--

DROP TABLE IF EXISTS `triggers_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `triggers_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(30) DEFAULT NULL,
  `event` varchar(30) DEFAULT NULL,
  `log_time` datetime DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=131070 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `vendors`
--

DROP TABLE IF EXISTS `vendors`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `vendors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `remote_ip` varchar(15) NOT NULL DEFAULT '',
  `port` int(11) DEFAULT '5060',
  `i_bill_auth` tinyint (2) unsigned,
  `i_bill_acc` set('1','2','3','4'),
  PRIMARY KEY (`id`),
  UNIQUE KEY `remote_ip` (`remote_ip`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;


DELIMITER |
CREATE TRIGGER `Trg_Insert_vendors` AFTER INSERT ON `vendors` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
IF (SELECT 1 FROM ip_customers WHERE remote_ip=NEW.remote_ip ) IS NULL THEN
  SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,host,port,i_bill_auth,i_bill_acc) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"','",NEW.i_bill_auth,"','",NEW.i_bill_acc,"');");
  INSERT INTO request (val) VALUES (REQ);
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Add vendor to sipdevices ',NEW.remote_ip,' from billing=',NEW.i_bill_auth,''));
ELSE
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Vendor already exist on sipdevices (ip existe on ip_customers)',NEW.remote_ip,' from billing=',NEW.i_bill_auth,''));
END IF;
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_vendors` AFTER UPDATE ON `vendors` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', port='",NEW.port,"', i_bill_auth='",NEW.i_bill_auth,"', i_bill_acc='",NEW.i_bill_acc,"' WHERE name='",OLD.remote_ip,"' AND context='forbidden';");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','UPDATE',NOW(),CONCAT('Update sipdevices remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip,' from billing=',NEW.i_bill_auth,''));
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_vendors` AFTER DELETE ON `vendors` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("DELETE FROM sipdevices WHERE name='",OLD.remote_ip,"' AND context='forbidden';");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','DELETE',NOW(),CONCAT('Delete vendor from sipdevices ',OLD.remote_ip,' from billing=',OLD.i_bill_auth,''));
END |
DELIMITER ;

--
-- Dumping routines for database 'astrad'
--

USE `astrad`;

DELIMITER |
DROP FUNCTION IF EXISTS `get_deny`|
CREATE FUNCTION `get_deny`(b int(2)) RETURNS text CHARSET latin1
    DETERMINISTIC
BEGIN
     DECLARE DN text DEFAULT '';
     DECLARE done INT DEFAULT 0;
     DECLARE IP VARCHAR(15);
     DECLARE cur CURSOR FOR SELECT DISTINCT remote_ip FROM ip_customers WHERE i_bill_auth=b ORDER BY remote_ip;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
     OPEN cur;
     read_loop: LOOP
       FETCH cur INTO IP;
       IF done THEN
         LEAVE read_loop;
       END IF;
       SET DN=CONCAT(DN,';',IP);
     END LOOP;
     CLOSE cur;
     RETURN LTRIM(SUBSTRING(DN,2,LENGTH(DN)-1));
END |
DELIMITER ;

--

USE `astrad`;

DELIMITER |
DROP PROCEDURE IF EXISTS `SET_REALM`|
CREATE PROCEDURE `SET_REALM`(IN val varchar(80),IN event varchar(30))
BEGIN
  DECLARE account VARCHAR(30);
  DECLARE secret char(80);
  DECLARE md5_secret char(32);
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE friends_cur CURSOR FOR SELECT acc.id, acc.h323_password FROM `porta-billing`.Accounts acc,`porta-billing`.Customers cus WHERE acc.i_customer=cus.i_customer AND cus.i_rep IN (3,17,5,9,12) AND acc.i_env=1 AND acc.id NOT regexp '^([0-9]{1,3}[.]){3}[0-9]{1,3}$';
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;
  OPEN friends_cur;
  SELECT FOUND_ROWS() INTO num_rows;
  the_loop: LOOP
    FETCH friends_cur INTO account, secret;
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;
IF secret IS NOT NULL THEN
      UPDATE users SET md5secret=md5(CONCAT(account,':',val,':',secret)) WHERE username=account;
ELSE
  UPDATE users SET md5secret=md5(CONCAT(account,':',val,':')) WHERE username=account;
END IF;
END LOOP the_loop;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('realm',event,NOW(),CONCAT('update realm of all users :',num_rows));
END |
DELIMITER ;

--
-- Dumping procedure REGISTER2
--

DELIMITER |
DROP PROCEDURE IF EXISTS `REGISTER2`|
CREATE PROCEDURE `REGISTER2`(IN USR VARCHAR(80),IN DOM VARCHAR(15),IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN EXR DATETIME,IN REG DATETIME,IN UA_ VARCHAR(20))
BEGIN
DECLARE MAXD INT(5) DEFAULT 0;
DECLARE MAXI INT(5) DEFAULT 0;
DECLARE IPAD varchar(15) DEFAULT '';
SET IPAD=(SELECT value FROM config WHERE name='ipaddr');
IF (UA_ is NULL) THEN
  SET UA_='Not Available';
END IF;
IF (SELECT 1 FROM location2 WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD) THEN
  UPDATE location2 SET expires=EXR,register=REG WHERE username=USR AND ipaddr=IP_ AND port=PORT_ AND domain=DOM AND dba=IPAD;
ELSE
  INSERT INTO location2 (username,domain,dba,ipaddr,port,expires,register,useragent) VALUES (USR,DOM,IPAD,IP_,PORT_,EXR,REG,UA_);
END IF;
INSERT INTO location2_history (start,stop,domain,username,ipaddr,port,count) VALUES (REG,REG,DOM,USR,IP_,PORT_,1) ON DUPLICATE KEY UPDATE count=count+1, stop=REG;
SET MAXI=(SELECT value FROM config WHERE name='max_reg_to_check');
IF (SELECT value FROM config WHERE name='tmp_reg_to_check') < MAXI THEN
    UPDATE config SET value=value+1 WHERE name='tmp_reg_to_check';
ELSE
    UPDATE config SET value=1 WHERE name='tmp_reg_to_check';
    SET MAXD=(SELECT value FROM config WHERE name='day_log_history');
    DELETE FROM location2_history WHERE stop < DATE_ADD(NOW(), INTERVAL -MAXD DAY);
END IF;
END |
DELIMITER ;

--
-- Dumping procedure add_multiple_ua
--

DELIMITER |
DROP PROCEDURE IF EXISTS `add_multiple_ua`|
CREATE PROCEDURE `add_multiple_ua`(IN USR VARCHAR(80), IN DOM VARCHAR(15),IN IP_ VARCHAR(15), IN PORT_ smallint(5) unsigned, IN USROLD VARCHAR(80), IN DOMOLD VARCHAR(15),IN IP_OLD VARCHAR(15), IN PORT_OLD smallint(5) unsigned, IN ACTION VARCHAR(80))
BEGIN
DECLARE REQ text DEFAULT '';
DECLARE tdomain varchar(15) default NULL;
DECLARE tipaddr varchar(15) default NULL;
DECLARE tport smallint(5) unsigned default NULL;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR select domain,ipaddr,port from location2 where username=USR;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

IF ACTION = 'ADD' THEN
  IF (SELECT COUNT(username) FROM location2 WHERE username=USR AND expires > DATE_SUB(NOW(), INTERVAL 30 MINUTE)) > 1 THEN
    OPEN cur;
      read_loop: LOOP
      FETCH cur INTO tdomain,tipaddr,tport;
      IF done THEN
        LEAVE read_loop;
      END IF;
	  IF (SELECT COUNT(username) FROM asterisk.multiple_ua WHERE username=USR AND domain=tdomain AND ipaddr=tipaddr AND port=tport) = 0 THEN
        SET REQ=CONCAT(REQ,"('",USR,"','",tdomain,"','",tipaddr,"','",tport,"'),");
      END IF;
      END LOOP;
    CLOSE cur;
	IF REQ != '' THEN
      SET REQ=LEFT(REQ, char_length(REQ) - 1);
      SET REQ=CONCAT("INSERT IGNORE INTO multiple_ua VALUES ",REQ);
      INSERT INTO request (val) VALUES (REQ);
	END IF;
  END IF;
ELSEIF ACTION = 'DEL' THEN
  SET REQ=CONCAT("DELETE FROM multiple_ua WHERE username='",USR,"' AND domain='",DOM,"' AND ipaddr='",IP_,"' AND port=",PORT_);
  INSERT INTO request (val) VALUES (REQ);
ELSE
  IF USR != USROLD OR DOM != DOMOLD OR IP_ != IP_OLD OR PORT_ != PORT_OLD THEN
    SET REQ=CONCAT("UPDATE multiple_ua SET username='",USR,"', domain='",DOM,"', ipaddr='",IP_,"', port=",PORT_," WHERE username='",USROLD,"' AND domain='",DOMOLD,"' AND ipaddr='",IP_OLD,"' AND port=",PORT_OLD);
    INSERT INTO request (val) VALUES (REQ);
  END IF;
END IF;
END |
DELIMITER ;

--
-- Current Database: `porta-billing`
--

DROP DATABASE IF EXISTS `porta-billing`;

CREATE DATABASE IF NOT EXISTS `porta-billing` DEFAULT CHARACTER SET latin1;

USE `porta-billing`;

--
-- Table structure for table `Accounts`
--

DROP TABLE IF EXISTS `Accounts`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Accounts` (
  `i_account` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id` varchar(32) NOT NULL,
  `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `h323_password` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `balance` decimal(15,5) NOT NULL DEFAULT '0.00000',
  `opening_balance` decimal(15,5) NOT NULL DEFAULT '0.00000',
  `issue_date` date NOT NULL DEFAULT '0000-00-00',
  `zero_balance_date` date DEFAULT NULL,
  `i_customer` int(10) unsigned NOT NULL DEFAULT '0',
  `billing_model` tinyint(2) NOT NULL DEFAULT '0',
  `activation_date` date NOT NULL DEFAULT '0000-00-00',
  `expiration_date` date DEFAULT NULL,
  `i_product` int(10) unsigned NOT NULL DEFAULT '0',
  `blocked` char(1) NOT NULL DEFAULT 'N',
  `last_usage` datetime DEFAULT NULL,
  `i_batch` int(10) unsigned DEFAULT NULL,
  `i_env` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `first_usage` date DEFAULT NULL,
  `life_time` smallint(4) unsigned DEFAULT NULL,
  `redirect_number` varchar(32) DEFAULT NULL,
  `control_number` int(10) unsigned DEFAULT NULL,
  `iso_639_1` char(5) DEFAULT NULL,
  `i_time_zone` int(10) unsigned NOT NULL DEFAULT '1',
  `credit_limit` decimal(15,5) DEFAULT NULL,
  `iso_4217` char(3) CHARACTER SET utf8 NOT NULL,
  `non_call_related_charges` decimal(15,5) DEFAULT NULL,
  `refunds` decimal(15,5) DEFAULT NULL,
  `login` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `email` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `password_timestamp` datetime DEFAULT NULL,
  `um_enabled` char(1) NOT NULL DEFAULT 'N',
  `i_acl` int(10) unsigned NOT NULL DEFAULT '155',
  `i_subscriber` int(10) unsigned DEFAULT NULL,
  `i_lang` char(5) DEFAULT NULL,
  `ua_profile_id` int(10) unsigned DEFAULT NULL,
  `mac` varchar(23) CHARACTER SET utf8 DEFAULT NULL,
  `follow_me_enabled` char(1) NOT NULL DEFAULT 'N',
  `timer` datetime DEFAULT NULL,
  `ecommerce_enabled` char(1) DEFAULT 'N',
  `out_date_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `out_time_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `out_date_time_format` varchar(32) CHARACTER SET utf8 NOT NULL,
  `in_date_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `in_time_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `i_vd_plan` int(10) unsigned DEFAULT NULL,
  `last_recharge` datetime DEFAULT NULL,
  `i_moh` int(10) unsigned DEFAULT NULL,
  `service_flags` varchar(32) CHARACTER SET utf8 NOT NULL,
  `i_master_account` int(10) DEFAULT NULL,
  `bill_status` enum('O','C','I') NOT NULL DEFAULT 'O',
  `i_routing_plan` int(10) NOT NULL DEFAULT '-1',
  `i_distributor` int(10) unsigned DEFAULT NULL,
  `management_number` varchar(32) DEFAULT NULL,
  `profitability` tinyint(3) NOT NULL DEFAULT '100',
  `i_customer_site` int(10) DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_account`,`i_billing`),
  UNIQUE KEY `Accounts_id` (`id`,`i_env`,`i_billing`),
  UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`,`i_billing`),
  UNIQUE KEY `Accounts_login` (`login`,`i_billing`),
  KEY `Account_customer` (`i_customer`),
  KEY `AccountsProfileId` (`ua_profile_id`),
  KEY `Accounts_mac` (`mac`,`i_env`),
  KEY `Accounts_um_enabled` (`um_enabled`),
  KEY `Accounts_iso_4217` (`iso_4217`),
  KEY `env_id` (`i_env`,`id`),
  KEY `AccountFirstUsage` (`first_usage`),
  KEY `i_vd_plan` (`i_vd_plan`),
  KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`),
  KEY `Accounts_i_acl` (`i_acl`),
  KEY `i_master_account` (`i_master_account`),
  KEY `i_routing_plan` (`i_routing_plan`),
  KEY `management_number` (`management_number`,`i_env`),
  KEY `distributor` (`i_distributor`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


--
-- Table structure for table `Active_Calls`
--

DROP TABLE IF EXISTS `Active_Calls`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Active_Calls` (
  `i_active_callleg` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `h323_conf_id` varchar(36) CHARACTER SET utf8 DEFAULT NULL,
  `parent_session_id` varchar(36) DEFAULT NULL,
  `call_id` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `i_env` int(10) unsigned NOT NULL DEFAULT '0',
  `i_node` int(10) unsigned NOT NULL DEFAULT '0',
  `call_type` enum('VoIP','Telephony') CHARACTER SET utf8 NOT NULL DEFAULT 'VoIP',
  `call_origin` enum('answer','originate') CHARACTER SET utf8 NOT NULL DEFAULT 'answer',
  `port` varchar(32) CHARACTER SET utf8 NOT NULL,
  `setup_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `connect_time` datetime DEFAULT NULL,
  `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `disconnect_time` datetime DEFAULT NULL,
  `CLI` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `CLD` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `account_id` varchar(32) DEFAULT NULL,
  `i_account` int(10) unsigned DEFAULT NULL,
  `i_customer` int(10) unsigned DEFAULT NULL,
  `i_connection` int(10) unsigned DEFAULT NULL,
  `i_service_type` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `expire_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `subscriber_ip` int(10) unsigned DEFAULT NULL,
  `srv_int_value1` int(10) unsigned DEFAULT NULL,
  `net_throughput` int(10) unsigned DEFAULT NULL,
  `net_aggregator_id` int(10) unsigned DEFAULT NULL,
  `net_flow_id` tinyint(3) unsigned DEFAULT NULL,
  `session_connect_code` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `nominal_price` decimal(15,5) unsigned DEFAULT NULL,
  `discount_rate` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `i_accessibility` int(10) unsigned DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_active_callleg`,`i_billing`),
  UNIQUE KEY `leg_accounting_idx` (`h323_conf_id`,`i_service_type`,`i_env`,`i_node`,`call_type`,`call_origin`,`port`,`i_billing`),
  KEY `node_h323id` (`i_node`,`h323_conf_id`),
  KEY `h323_port_idx` (`h323_conf_id`,`port`),
  KEY `call_idx` (`call_id`),
  KEY `env_connection` (`i_env`,`i_connection`),
  KEY `env_netflow` (`i_env`,`i_customer`,`net_aggregator_id`,`net_flow_id`),
  KEY `expire_timex` (`expire_time`) USING BTREE,
  KEY `update_time` (`update_time`) USING BTREE,
  KEY `disconnect_time` (`disconnect_time`) USING BTREE
) ENGINE=MEMORY AUTO_INCREMENT=3520 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Connections`
--

DROP TABLE IF EXISTS `Connections`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Connections` (
  `i_connection` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i_vendor` int(10) unsigned NOT NULL DEFAULT '0',
  `description` varchar(255) CHARACTER SET utf8 NOT NULL,
  `i_node` int(10) unsigned DEFAULT NULL,
  `i_tariff` int(10) unsigned DEFAULT NULL,
  `call_origin` enum('answer','originate','callback','unknown') CHARACTER SET utf8 NOT NULL DEFAULT 'unknown',
  `call_type` enum('Telephony','VoIP','unknown') CHARACTER SET utf8 NOT NULL DEFAULT 'unknown',
  `port` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `CLD` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `i_env` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `cld_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `remote_ip` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `capacity` smallint(5) unsigned NOT NULL DEFAULT '0',
  `asr` float(7,4) DEFAULT NULL,
  `i_vendor_acc` int(10) unsigned DEFAULT NULL,
  `outgoing_cld_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `i_oli` int(10) unsigned DEFAULT NULL,
  `sip` enum('Y','N') NOT NULL DEFAULT 'Y',
  `h323` enum('Y','N') NOT NULL DEFAULT 'N',
  `cli_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `rtpp_level` tinyint(1) unsigned DEFAULT NULL,
  `clir_method` enum('A','P') DEFAULT 'A',
  `i_service_type` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `i_criteria` int(10) unsigned DEFAULT NULL,
  `sip_specific_settings` varchar(100) DEFAULT NULL,
  `cli_trust` enum('Y','N') NOT NULL DEFAULT 'N',
  `gateway_id` varchar(64) DEFAULT NULL,
  `utilization_routing` enum('Y','N') NOT NULL DEFAULT 'N',
  `utilization_balthreshold` smallint(5) unsigned DEFAULT NULL,
  `i_service_policy` int(10) unsigned DEFAULT NULL,
  `autogen_tr_rules` enum('Y','N') NOT NULL DEFAULT 'N',
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_connection`,`i_billing`),
  KEY `Connection_i_vendor` (`i_vendor`),
  KEY `Connections_port` (`port`),
  KEY `i_tariff` (`i_tariff`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Customers`
--

DROP TABLE IF EXISTS `Customers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Customers` (
  `i_customer` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `refnum` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `name` varchar(41) CHARACTER SET utf8 NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `balance` decimal(15,5) NOT NULL DEFAULT '0.00000',
  `iso_4217` char(3) CHARACTER SET utf8 NOT NULL,
  `companyname` varchar(41) CHARACTER SET utf8 NOT NULL,
  `salutation` varchar(15) CHARACTER SET utf8 NOT NULL,
  `firstname` varchar(25) CHARACTER SET utf8 NOT NULL,
  `midinit` varchar(25) CHARACTER SET utf8 NOT NULL,
  `lastname` varchar(25) CHARACTER SET utf8 NOT NULL,
  `baddr1` varchar(41) CHARACTER SET utf8 NOT NULL,
  `baddr2` varchar(41) CHARACTER SET utf8 NOT NULL,
  `baddr3` varchar(41) CHARACTER SET utf8 NOT NULL,
  `baddr4` varchar(41) CHARACTER SET utf8 NOT NULL,
  `baddr5` varchar(41) CHARACTER SET utf8 NOT NULL,
  `city` varchar(31) CHARACTER SET utf8 NOT NULL,
  `state` varchar(21) CHARACTER SET utf8 NOT NULL,
  `zip` varchar(13) CHARACTER SET utf8 NOT NULL,
  `country` varchar(31) CHARACTER SET utf8 NOT NULL,
  `note` varchar(41) CHARACTER SET utf8 NOT NULL,
  `cont1` varchar(41) CHARACTER SET utf8 NOT NULL,
  `phone1` varchar(21) CHARACTER SET utf8 NOT NULL,
  `faxnum` varchar(21) CHARACTER SET utf8 NOT NULL,
  `phone2` varchar(21) CHARACTER SET utf8 NOT NULL,
  `cont2` varchar(41) CHARACTER SET utf8 NOT NULL,
  `email` varchar(99) CHARACTER SET utf8 DEFAULT NULL,
  `bcc` varchar(99) CHARACTER SET utf8 DEFAULT NULL,
  `send_statistics` enum('F','S','N') DEFAULT NULL,
  `login` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `i_customer_type` int(10) unsigned NOT NULL DEFAULT '1',
  `i_billing_period` tinyint(2) unsigned NOT NULL DEFAULT '4',
  `i_tariff` int(10) unsigned DEFAULT NULL,
  `i_time_zone` int(10) unsigned NOT NULL DEFAULT '1',
  `credit_limit` decimal(15,5) DEFAULT NULL,
  `i_credit_card` int(10) unsigned DEFAULT NULL,
  `i_env` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `i_template` int(10) unsigned DEFAULT NULL,
  `tax_id` varchar(16) CHARACTER SET utf8 DEFAULT NULL,
  `i_payment_term` int(10) unsigned NOT NULL DEFAULT '0',
  `blocked` enum('Y','N') NOT NULL DEFAULT 'N',
  `ppm_enabled` char(1) NOT NULL DEFAULT 'N',
  `i_rep` int(10) unsigned DEFAULT NULL,
  `drm_enabled` char(1) NOT NULL DEFAULT 'N',
  `max_abbreviated_length` tinyint(1) unsigned DEFAULT NULL,
  `password_timestamp` datetime DEFAULT NULL,
  `out_date_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `out_time_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `out_date_time_format` varchar(32) CHARACTER SET utf8 NOT NULL,
  `in_date_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `in_time_format` varchar(16) CHARACTER SET utf8 NOT NULL,
  `i_online_payment_processor` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `merch_acc_password` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `merch_acc_login` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `reccuring_enabled` char(1) NOT NULL DEFAULT 'N',
  `test_mode` char(1) NOT NULL DEFAULT 'Y',
  `i_acl` int(10) unsigned NOT NULL DEFAULT '0',
  `opening_balance` decimal(15,5) NOT NULL DEFAULT '0.00000',
  `min_allowed_payment` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `merch_acc_iso_4217` char(3) CHARACTER SET utf8 DEFAULT NULL,
  `cld_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `i_lang` char(5) DEFAULT NULL,
  `i_tariff_incoming` int(10) unsigned DEFAULT NULL,
  `credit_limit_warning` varchar(25) CHARACTER SET utf8 DEFAULT NULL,
  `callshop_enabled` char(1) NOT NULL DEFAULT 'N',
  `billed_to` datetime DEFAULT NULL,
  `i_parent` int(10) unsigned DEFAULT NULL,
  `i_routing_plan` int(10) DEFAULT NULL,
  `i_vd_plan` int(10) unsigned DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `cli_in_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `i_moh` int(10) unsigned DEFAULT NULL,
  `service_flags` varchar(32) CHARACTER SET utf8 NOT NULL,
  `i_customer_class` int(10) unsigned NOT NULL DEFAULT '0',
  `bp_charge_cc` enum('N','Y') DEFAULT NULL,
  `unallocated_payments` decimal(15,5) NOT NULL DEFAULT '0.00000',
  `bill_status` enum('O','S','C') NOT NULL DEFAULT 'O',
  `discount_rate` decimal(8,5) unsigned DEFAULT NULL,
  `taxed_to` datetime DEFAULT NULL,
  `i_billing_processor` int(11) DEFAULT NULL,
  `tax_info` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `suspension_delay_date` datetime DEFAULT NULL,
  `send_invoices` enum('Y','N') DEFAULT NULL,
  `shifted_billing_date` datetime DEFAULT NULL,
  `sale_commission_rate` decimal(8,5) unsigned DEFAULT NULL,
  `payment_commission_rate` decimal(8,5) unsigned DEFAULT NULL,
  `i_distributor` int(10) unsigned DEFAULT NULL,
  `profitability` tinyint(3) NOT NULL DEFAULT '100',
  `override_tariffs_enabled` enum('Y','N') NOT NULL DEFAULT 'N',
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_customer`,`i_billing`),
  UNIQUE KEY `customer` (`name`,`i_env`,`i_billing`),
  UNIQUE KEY `login` (`login`,`i_billing`),
  KEY `parent_type` (`i_parent`,`i_customer_type`),
  KEY `i_tariff` (`i_tariff`),
  KEY `i_tariff_incoming` (`i_tariff_incoming`),
  KEY `Customers_iso_4217` (`iso_4217`),
  KEY `i_vd_plan` (`i_vd_plan`),
  KEY `i_rep` (`i_rep`),
  KEY `i_template` (`i_template`),
  KEY `i_customer_class` (`i_customer_class`),
  KEY `bill_status` (`bill_status`),
  KEY `Customers_i_acl` (`i_acl`),
  KEY `distributor` (`i_distributor`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Destinations`
--

DROP TABLE IF EXISTS `Destinations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Destinations` (
  `i_dest` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `destination` varchar(16) NOT NULL,
  `iso_3166_1_a2` char(2) DEFAULT '',
  `i_country_subdivision` smallint(5) unsigned DEFAULT NULL,
  `i_time_zone` int(10) unsigned DEFAULT NULL,
  `description` varchar(64) CHARACTER SET utf8 NOT NULL,
  `i_group` int(10) unsigned DEFAULT '0',
  `i_env` tinyint(1) unsigned DEFAULT NULL,
  `dest_type` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_dest`,`i_billing`),
  UNIQUE KEY `destination` (`destination`,`i_env`,`i_billing`),
  KEY `Destinations_i_group` (`i_group`),
  KEY `dest_type` (`dest_type`),
  KEY `destination_env_country` (`i_env`,`iso_3166_1_a2`),
  KEY `env_dest_i_dest_iso` (`destination`,`i_env`,`i_dest`,`iso_3166_1_a2`)
) ENGINE=InnoDB AUTO_INCREMENT=10048 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Follow_Me`
--

DROP TABLE IF EXISTS `Follow_Me`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Follow_Me` (
  `i_follow_me` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i_account` int(10) unsigned NOT NULL DEFAULT '0',
  `mode` enum('Never','Always','Unavail') CHARACTER SET utf8 DEFAULT NULL,
  `sequence` enum('Order','Random','Simultaneous') CHARACTER SET utf8 DEFAULT NULL,
  `timeout` smallint(4) unsigned DEFAULT NULL,
  `max_forwards` tinyint(4) unsigned DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_follow_me`,`i_billing`),
  UNIQUE KEY `i_account` (`i_account`,`i_billing`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Nodes`
--

DROP TABLE IF EXISTS `Nodes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Nodes` (
  `i_node` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ip` varchar(64) CHARACTER SET utf8 NOT NULL,
  `h323_id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `name` varchar(41) CHARACTER SET utf8 DEFAULT NULL,
  `radius_client` char(1) NOT NULL DEFAULT 'N',
  `radius_key` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `i_env` tinyint(1) unsigned DEFAULT '1',
  `hostname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `domain` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `i_time_zone` int(10) unsigned NOT NULL DEFAULT '1',
  `h323_password` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `cld_translation_rule` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `radius_source_ip` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `id` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
  `i_node_type` int(10) unsigned DEFAULT NULL,
  `rtpp_level` tinyint(1) unsigned DEFAULT NULL,
  `node_index` tinyint(1) unsigned DEFAULT NULL,
  `pod_server` char(1) NOT NULL DEFAULT 'N',
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_node`,`i_billing`),
  UNIQUE KEY `ip` (`ip`,`i_billing`),
  UNIQUE KEY `h323_id` (`h323_id`,`i_billing`),
  UNIQUE KEY `node` (`name`,`i_env`,`i_billing`),
  KEY `node_env_type` (`i_env`,`i_node_type`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Rates`
--

DROP TABLE IF EXISTS `Rates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Rates` (
  `i_tariff` int(10) unsigned NOT NULL DEFAULT '0',
  `i_dest` int(10) unsigned NOT NULL DEFAULT '0',
  `i_rate` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `i_offpeak` int(10) unsigned DEFAULT NULL,
  `interval_1` smallint(5) unsigned NOT NULL DEFAULT '1',
  `interval_n` smallint(5) unsigned NOT NULL DEFAULT '1',
  `price_1` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `price_n` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `op_interval_1` smallint(5) unsigned NOT NULL DEFAULT '1',
  `op_interval_n` smallint(5) unsigned NOT NULL DEFAULT '1',
  `op_price_1` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `op_price_n` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `effective_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `inactive_from` datetime DEFAULT NULL,
  `hidden` char(1) NOT NULL DEFAULT 'N',
  `discontinued` char(1) NOT NULL DEFAULT 'N',
  `forbidden` char(1) NOT NULL DEFAULT 'N',
  `preference` tinyint(1) unsigned NOT NULL DEFAULT '5',
  `huntstop` char(1) NOT NULL DEFAULT 'N',
  `formula` varchar(255) DEFAULT NULL,
  `fake_rate` char(1) NOT NULL DEFAULT 'Y',
  `route_category` int(10) unsigned DEFAULT NULL,
  `op_route_category` int(10) unsigned DEFAULT NULL,
  `op_preference` tinyint(1) unsigned DEFAULT NULL,
  `op_huntstop` char(1) DEFAULT 'N',
  `active` char(1) NOT NULL DEFAULT 'Y',
  `i_rate_formula` int(10) unsigned DEFAULT NULL,
  `reverse_rating` enum('N','Y') NOT NULL DEFAULT 'N',
  `special_price_1` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `special_price_n` decimal(15,5) unsigned NOT NULL DEFAULT '0.00000',
  `i_dest_group` int(10) unsigned DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_rate`,`i_billing`),
  KEY `Rates_dest_index` (`i_dest`),
  KEY `Rates_effective_from` (`effective_from`),
  KEY `Rates_inactive_from` (`inactive_from`),
  KEY `tar_dest_effrom_rate` (`i_tariff`,`i_dest`,`discontinued`,`effective_from`),
  KEY `tar_dest_active_effrom_rate` (`i_tariff`,`i_dest`,`active`,`discontinued`,`effective_from`),
  KEY `active_effrom_rate` (`active`,`effective_from`),
  KEY `rates_period_idx` (`i_offpeak`),
  KEY `rates_route_cat_idx` (`route_category`),
  KEY `rates_route_op_cat_idx` (`op_route_category`),
  KEY `rates_tariff_offpeak` (`i_tariff`,`i_offpeak`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `Service_Attribute_Values`
--

DROP TABLE IF EXISTS `Service_Attribute_Values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Service_Attribute_Values` (
  `i_savalue` int(10) NOT NULL AUTO_INCREMENT,
  `i_sattribute` int(10) NOT NULL,
  `i_foreign` int(10) NOT NULL,
  `value` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ord` tinyint(4) DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`i_savalue`,`i_billing`),
  KEY `savalue_sattribute` (`i_sattribute`,`i_foreign`)
) ENGINE=InnoDB AUTO_INCREMENT=218 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `astrad_confs`
--

DROP TABLE IF EXISTS `astrad_confs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `astrad_confs` (
  `id` int(11) NOT NULL auto_increment,
  `host` varchar(15) NOT NULL,
  `port` int(11) DEFAULT 5060,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY  (`id`,`i_billing`),
  UNIQUE KEY `name` (`host`,`i_billing`),
  KEY `name_2` (`host`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;



DELIMITER |
CREATE TRIGGER `Trg_Insert_astrad_confs` AFTER INSERT ON `astrad_confs` FOR EACH ROW BEGIN
DECLARE exist INT default 0;
SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=NEW.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.ip_customers SET port=NEW.port WHERE remote_ip=NEW.host;
END IF;
SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=NEW.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.vendors SET port=NEW.port WHERE remote_ip=NEW.host;
END IF;
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_astrad_confs` AFTER UPDATE ON `astrad_confs` FOR EACH ROW BEGIN
DECLARE exist INT default 0;
IF OLD.host <> NEW.host THEN
  SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.host),0);
  IF exist = 1 THEN
    UPDATE `astrad`.ip_customers SET port=5060 WHERE remote_ip=OLD.host;
  END IF;
  SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=OLD.host),0);
  IF exist = 1 THEN
    UPDATE `astrad`.vendors SET port=5060 WHERE remote_ip=OLD.host;
  END IF;
END IF;
SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=NEW.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.ip_customers SET port=NEW.port WHERE remote_ip=NEW.host;
END IF;
SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=NEW.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.vendors SET port=NEW.port WHERE remote_ip=NEW.host;
END IF;
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_astrad_confs` AFTER DELETE ON `astrad_confs` FOR EACH ROW BEGIN
DECLARE exist INT default 0;
SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.ip_customers SET port=5060 WHERE remote_ip=OLD.host;
END IF;
SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip=OLD.host),0);
IF exist = 1 THEN
  UPDATE `astrad`.vendors SET port=5060 WHERE remote_ip=OLD.host;
END IF;
END |
DELIMITER ;


--
-- Table structure for table `customer_confs`
--

DROP TABLE IF EXISTS `customer_confs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `customer_confs` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL,
  `nat` enum('yes','no','force_rport','comedia') NOT NULL DEFAULT 'yes',
  `sipalg` int(11) DEFAULT 0,
  `video` bool DEFAULT 0,
  `codecorder` varchar(100) DEFAULT NULL,
  `codecusefirstonly` bool DEFAULT 0,
  `TTL` int(11) DEFAULT 3600,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY  (`id`,`i_billing`),
  UNIQUE KEY `name` (`name`,`i_billing`),
  KEY `name_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Current Database: `porta-sip`
--

CREATE DATABASE IF NOT EXISTS `porta-sip` DEFAULT CHARACTER SET latin1;

USE `porta-sip`;

--
-- Table structure for table `location`
--

DROP TABLE IF EXISTS `location`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `location` (
  `username` varchar(50) NOT NULL DEFAULT '',
  `domain` varchar(100) NOT NULL DEFAULT '',
  `contact` varchar(255) NOT NULL DEFAULT '',
  `i_env` int(11) NOT NULL DEFAULT '1',
  `expires` datetime DEFAULT NULL,
  `q` float(10,2) DEFAULT NULL,
  `callid` varchar(255) DEFAULT NULL,
  `cseq` int(11) DEFAULT NULL,
  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `replicate` int(10) unsigned DEFAULT NULL,
  `state` tinyint(1) unsigned DEFAULT NULL,
  `flags` int(11) NOT NULL DEFAULT '0',
  `user_agent` varchar(50) NOT NULL DEFAULT '',
  `received` varchar(255) DEFAULT NULL,
  `i_billing` tinyint (2) unsigned,
  PRIMARY KEY (`username`,`domain`,`contact`,`i_env`,`i_billing`),
  KEY `location_idx1` (`username`,`i_env`,`last_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;


USE `astrad`;
--
-- Table structure for table `ip_customers`
--

DROP TABLE IF EXISTS `ip_customers`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ip_customers` (
  `remote_ip` varchar(15) NOT NULL DEFAULT '',
  `is_node` enum('Y','N') NOT NULL DEFAULT 'N',
  `port` int(11) DEFAULT '5060',
  `i_bill_auth` tinyint (2) unsigned,
  `i_bill_acc` set('1','2','3','4'),
  PRIMARY KEY `remote_ip` (`remote_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER |
CREATE TRIGGER `Trg_Insert_ip_customers` AFTER INSERT ON `ip_customers` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
DECLARE cont VARCHAR(10) DEFAULT '';
IF NEW.is_node='Y' THEN
  SET cont='fromnode';
ELSEIF NEW.is_node='N' THEN
  SET cont='fromhost';
END IF;
IF (SELECT 1 FROM vendors WHERE remote_ip=NEW.remote_ip) IS NULL THEN
   SET REQ=CONCAT("INSERT IGNORE INTO sipdevices(name,host,port,context,insecure,i_bill_auth,i_bill_acc) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"','",cont,"','port','",NEW.i_bill_auth,"','",NEW.i_bill_acc,"');");
   INSERT INTO request (val) VALUES (REQ);
   INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Add to sipdevices ',NEW.remote_ip,' context=',cont,' from billing=',NEW.i_bill_auth,''));
ELSE
   SET REQ=CONCAT("UPDATE sipdevices SET context='",cont,"', insecure='port', i_bill_auth='",NEW.i_bill_auth,"', i_bill_acc='",NEW.i_bill_acc,"' WHERE name='",NEW.remote_ip,"' AND context='forbidden';");
   INSERT INTO request (val) VALUES (REQ);
   INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Set context (',cont,') to ip_customer in sipdevices ',NEW.remote_ip,' from billing=',NEW.i_bill_auth,''));
END IF;
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_ip_customers` AFTER UPDATE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
DECLARE cont VARCHAR(10) DEFAULT '';
IF NEW.is_node='Y' THEN
  SET cont='fromnode';
ELSEIF NEW.is_node='N' THEN
  SET cont='fromhost';
END IF;
SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', context='",cont,"', port='",NEW.port,"', i_bill_auth='",NEW.i_bill_auth,"', i_bill_acc='",NEW.i_bill_acc,"' WHERE name='",OLD.remote_ip,"';");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','UPDATE',NOW(),CONCAT('Update ip customer in sipdevices remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip,' from billing=',NEW.i_bill_auth,''));
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_ip_customers` AFTER DELETE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
IF (SELECT 1 FROM vendors WHERE remote_ip=OLD.remote_ip) THEN
  SET REQ=CONCAT("UPDATE sipdevices SET context='forbidden', insecure='no', i_bill_auth='",OLD.i_bill_auth,"', i_bill_acc='",OLD.i_bill_acc,"' WHERE name='",OLD.remote_ip,"';");
  INSERT INTO request (val) VALUES (REQ);
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update context to forbidden in sipdevices ',OLD.remote_ip,' from billing=',OLD.i_bill_auth,''));
ELSE
  SET REQ=CONCAT("DELETE FROM sipdevices WHERE name='",OLD.remote_ip,"';");
  INSERT INTO request (val) VALUES (REQ);
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Delete from sipdevices ',OLD.remote_ip,' from billing=',OLD.i_bill_auth,''));
END IF;
END |
DELIMITER ;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `username` varchar(30) NOT NULL DEFAULT '',
  `md5secret` varchar(32) NOT NULL DEFAULT '',
  `i_bill_auth` tinyint (2) unsigned,
  `i_bill_acc` set('1','2','3','4'),
  PRIMARY KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER |
CREATE TRIGGER `Trg_Insert_users` AFTER INSERT ON `users` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,md5secret,context,host,i_bill_auth,i_bill_acc) VALUES ('",NEW.username,"','",NEW.md5secret,"','fromaccount','dynamic','",NEW.i_bill_auth,"','",NEW.i_bill_acc,"');");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sipdevices ',NEW.username,' from billing=',NEW.i_bill_auth,''));
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Update_users` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.username,"', md5secret='",NEW.md5secret,"', i_bill_auth='",NEW.i_bill_auth,"', i_bill_acc='",NEW.i_bill_acc,"' WHERE name='",OLD.username,"';");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','UPDATE',NOW(),CONCAT('Update sipdevices new username=',NEW.username,' old username=',OLD.username,' from billing=',NEW.i_bill_auth,''));
END |
DELIMITER ;

DELIMITER |
CREATE TRIGGER `Trg_Delete_users` AFTER DELETE ON `users` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("DELETE FROM sipdevices WHERE name='",OLD.username,"';");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','DELETE',NOW(),CONCAT('Delete from sipdevices ',OLD.username,' from billing=',OLD.i_bill_auth,''));
END |
DELIMITER ;











USE `porta-billing`;

DELIMITER |
DROP PROCEDURE IF EXISTS `LOG_TRIGGER`|
CREATE PROCEDURE `LOG_TRIGGER`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN actiontext VARCHAR(150))
BEGIN
  IF actiontext != '' AND actiontext is not NULL THEN
    INSERT INTO `astrad`.triggers_log (table_name,event,log_time,comment) VALUES (tablename,todo,NOW(),actiontext);
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_VENDORS` |
CREATE FUNCTION `GET_IBILLACC_FOR_VENDORS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE Temp VARCHAR(100);
  SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT i_billing FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND ip = accountid UNION SELECT i_billing FROM Connections WHERE i_env=1 AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' AND remote_ip = accountid) a;
  RETURN Temp;
END |
DELIMITER ;

DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_USERS` |
CREATE FUNCTION `GET_IBILLACC_FOR_USERS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE Temp VARCHAR(100);
  SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT distinct `porta-billing`.Accounts.id, `porta-billing`.Accounts.i_billing FROM `porta-billing`.Accounts, `porta-billing`.Customers WHERE `porta-billing`.Accounts.i_env=1 AND `porta-billing`.Customers.i_rep IN (3,5,9,12,17) AND `porta-billing`.Accounts.i_customer = `porta-billing`.Customers.i_customer AND `porta-billing`.Accounts.i_billing = `porta-billing`.Customers.i_billing AND `porta-billing`.Accounts.id = accountid) a GROUP BY id;
  RETURN Temp;
END |
DELIMITER ;

DELIMITER |
DROP FUNCTION IF EXISTS `GET_IBILLACC_FOR_IPCUSTOMERS` |
CREATE FUNCTION `GET_IBILLACC_FOR_IPCUSTOMERS`(accountid VARCHAR(100)) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE Temp VARCHAR(100);
  SELECT GROUP_CONCAT(i_billing) INTO Temp FROM (SELECT Accounts.i_billing FROM Accounts, Customers WHERE Accounts.i_env=1 AND Customers.i_rep IN (3,5,9,12,17) AND Accounts.i_customer = Customers.i_customer AND Accounts.i_billing = Customers.i_billing AND Accounts.id = accountid UNION SELECT Nodes.i_billing FROM Nodes WHERE ip = accountid AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%') a;
  RETURN Temp;
END |
DELIMITER ;

DELIMITER |
DROP FUNCTION IF EXISTS `GET_HOST_PORT` |
CREATE FUNCTION `GET_HOST_PORT`(accountid VARCHAR(100)) RETURNS INT(11) DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE Temp INT(11);
  SELECT IFNULL((SELECT port FROM astrad_confs WHERE host = accountid),5060) INTO Temp;
  RETURN Temp;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_USERS`|
CREATE PROCEDURE `INS_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE rm VARCHAR(80);
  DECLARE md varchar(32);
  DECLARE ibillacc VARCHAR(100); 
  IF (account REGEXP '^[0-9]+$') AND
    (SELECT IFNULL((SELECT 1 FROM Customers WHERE i_customer = (SELECT i_customer FROM Accounts WHERE id = account AND i_billing=billing AND i_env=1 limit 1) AND i_rep IN (3,5,9,12,17) AND i_billing=billing limit 1),0)) THEN
    SET ibillacc = (SELECT GET_IBILLACC_FOR_USERS(account));
    IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
      SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
      SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=billing limit 1),''))));
      INSERT INTO `astrad`.users (username, md5secret, i_bill_auth, i_bill_acc) values (account, md, billing, ibillacc) ON DUPLICATE KEY UPDATE md5secret=md, i_bill_auth = billing, i_bill_acc = ibillacc;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Created or modified users account ',account));
    ELSE
      UPDATE `astrad`.users SET i_bill_acc = ibillacc WHERE username = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Modified users account ',account));
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_USERS`|
CREATE PROCEDURE `DEL_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE nb tinyint(2) unsigned default 1;
  DECLARE ibillacc VARCHAR(100);
  DECLARE rm VARCHAR(80);
  DECLARE md varchar(32);
  IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account LIMIT 1),0)) THEN
    SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM Accounts WHERE id = account AND i_billing > billing AND i_customer IN (SELECT i_customer FROM Customers where i_rep IN (3,5,9,12,17) AND i_billing > billing) AND i_env=1 AND account REGEXP '^[0-9]+$' LIMIT 1),0));
    IF nb > 0 THEN
      SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
      SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=nb limit 1),''))));
      SET ibillacc = (SELECT GET_IBILLACC_FOR_USERS(account)); 
      UPDATE `astrad`.users SET md5secret=md, i_bill_auth = nb, i_bill_acc = ibillacc WHERE username = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated users account ',account));
    ELSE
      DELETE FROM `astrad`.users WHERE username = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted users account ',account));
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_VENDORS`|
CREATE PROCEDURE `INS_ACC_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE ibillacc VARCHAR(100);
  DECLARE hostport INT(11) DEFAULT 5060;
  IF (SELECT IFNULL((SELECT 1 FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND ip = account AND i_billing = billing AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),0)) OR
  (SELECT IFNULL((SELECT 1 FROM Connections WHERE i_env=1 AND remote_ip = account AND i_billing = billing AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' LIMIT 1),0))
  THEN
    SET ibillacc = (SELECT GET_IBILLACC_FOR_VENDORS(account));
    IF (SELECT IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
      SET hostport = (SELECT GET_HOST_PORT(account));
      INSERT INTO `astrad`.vendors (remote_ip,port,i_bill_auth,i_bill_acc) values (account,hostport,billing,ibillacc) ON DUPLICATE KEY UPDATE i_bill_auth = billing,i_bill_acc=ibillacc;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Created vendor with IP ',account));
    ELSE
      UPDATE `astrad`.vendors SET i_bill_acc = ibillacc WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated vendor with IP ',account));
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_VENDORS`|
CREATE PROCEDURE `DEL_ACC_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE nb tinyint(2) unsigned DEFAULT 1;
  DECLARE ibillacc VARCHAR(100);
  DECLARE hostport INT(11) DEFAULT 5060;
  IF (SELECT IFNULL((SELECT 1 FROM `astrad`.vendors WHERE remote_ip = account LIMIT 1),0)) THEN
    SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM (SELECT i_billing FROM Nodes WHERE i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' AND name REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND name = account UNION SELECT i_billing FROM Connections WHERE i_env=1 AND remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND sip='Y' AND remote_ip = account) a LIMIT 1),0));
    IF nb > 0 THEN
      SET ibillacc = (SELECT GET_IBILLACC_FOR_VENDORS(account)); 
      SET hostport = (SELECT GET_HOST_PORT(account));
      UPDATE `astrad`.vendors SET port = hostport, i_bill_auth = nb, i_bill_acc = ibillacc WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated vendor with IP ',account));
    ELSE
      DELETE FROM `astrad`.vendors WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted vendors account ',account));
    END IF;   
  END IF;
END |
DELIMITER ;
 
DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_IPCUSTOMERS`|
CREATE PROCEDURE `INS_ACC_IPCUSTOMERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE isnode enum('Y','N') default 'N';
  DECLARE ibillacc VARCHAR(100);
  DECLARE hostport INT(11) DEFAULT 5060;
  IF (SELECT IFNULL((SELECT 1 FROM Customers WHERE i_billing = billing AND i_rep IN (3,5,9,12,17) AND i_customer = (SELECT i_customer FROM Accounts WHERE id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND id = account AND i_billing = billing LIMIT 1) LIMIT 1),0)) OR
  (SELECT IFNULL((SELECT 1 FROM Nodes WHERE ip = account AND i_billing = billing AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' LIMIT 1),0))
  THEN
    SET ibillacc = (SELECT GET_IBILLACC_FOR_IPCUSTOMERS(account));
    IF (SELECT IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = account AND i_bill_auth < billing limit 1),0)) = 0 THEN
      SET isnode = (SELECT IFNULL((SELECT 'Y' FROM Nodes WHERE i_env = 1 AND i_node_type = 11 and name not like '%[Deleted]%' AND ip = account AND i_billing = billing AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),'N'));
      SET hostport = (SELECT GET_HOST_PORT(account));
      INSERT INTO `astrad`.ip_customers (remote_ip,port,is_node,i_bill_auth,i_bill_acc) values (account,hostport,isnode,billing,ibillacc) ON DUPLICATE KEY UPDATE i_bill_auth = billing,is_node=isnode,i_bill_acc=ibillacc;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Created ip_customer with IP ',account));
    ELSE
      UPDATE `astrad`.ip_customers SET i_bill_acc = ibillacc WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated ip_customer with IP ',account));
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `DEL_ACC_IPCUSTOMERS`|
CREATE PROCEDURE `DEL_ACC_IPCUSTOMERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE isnode enum('Y','N') default 'N';
  DECLARE nb tinyint(2) unsigned default 1;
  DECLARE hostport INT(11) DEFAULT 5060;
  DECLARE ibillacc VARCHAR(100);
  IF (SELECT IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip = account AND i_bill_auth = billing limit 1),0)) THEN
    SET nb = (SELECT IFNULL((SELECT MIN(i_billing) FROM Nodes WHERE ip = account AND i_billing > billing AND i_env = 1 AND i_node_type = 11 AND name not like '%[Deleted]%' limit 1),0));
    IF nb > 0 THEN
      SET isnode = (SELECT IFNULL((SELECT 'Y' FROM Nodes WHERE i_env = 1 AND i_node_type = 11 and name not like '%[Deleted]%' AND ip = account AND i_billing = nb AND ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' LIMIT 1),'N'));
      SET hostport = (SELECT GET_HOST_PORT(account));
      SET ibillacc = (SELECT GET_IBILLACC_FOR_IPCUSTOMERS(account));
      UPDATE `astrad`.ip_customers SET port = hostport, is_node = isnode, i_bill_acc = ibillacc WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Updated ip_customer with IP ',account));
    ELSE
      DELETE FROM `astrad`.ip_customers WHERE remote_ip = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted ip_customer with IP ',account));
    END IF;
  END IF;
END |
DELIMITER ;

-- This procedure doesn't support a conversion between node to account and vice-versa
DELIMITER |
DROP PROCEDURE IF EXISTS `CHANGE_IP_CUSTOMERS_AND_USERS`|
CREATE PROCEDURE `CHANGE_IP_CUSTOMERS_AND_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN newaccount varchar(30), IN oldaccount VARCHAR(30))
BEGIN
  IF todo != "IGNORE" THEN
    IF newaccount REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
      IF todo = "DELETE" THEN
        CALL DEL_ACC_IPCUSTOMERS(todo, tablename, billing, newaccount);
      END IF;
      IF todo = "UPDATE" AND newaccount != oldaccount THEN
        CALL DEL_ACC_IPCUSTOMERS(todo, tablename, billing, oldaccount);
      END IF;
      IF todo = "INSERT" OR todo = "UPDATE" THEN
        CALL INS_ACC_IPCUSTOMERS(todo, tablename, billing, newaccount);
      END IF;
    ELSE
      IF todo = "DELETE" THEN
        CALL DEL_ACC_USERS(todo, tablename, billing, newaccount);
      END IF;
      IF todo = "UPDATE" AND newaccount != oldaccount THEN
        CALL DEL_ACC_USERS(todo, tablename, billing, oldaccount);
      END IF;
      IF todo = "INSERT" OR todo = "UPDATE" THEN
        CALL INS_ACC_USERS(todo, tablename, billing, newaccount);
      END IF;
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `CHANGE_VENDORS`|
CREATE PROCEDURE `CHANGE_VENDORS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN newaccount varchar(30), IN oldaccount VARCHAR(30))
BEGIN
  IF todo != "IGNORE" THEN
    IF todo = "DELETE" THEN
      CALL DEL_ACC_VENDORS(todo, tablename, billing, newaccount);
    END IF;
    IF todo = "UPDATE" AND newaccount != oldaccount THEN
      CALL DEL_ACC_VENDORS(todo, tablename, billing, oldaccount);
    END IF;
    IF todo = "INSERT" OR todo = "UPDATE" THEN
      CALL INS_ACC_VENDORS(todo, tablename, billing, newaccount);
    END IF;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Accounts`;
CREATE TRIGGER `Trg_Insert_Accounts` AFTER INSERT ON `Accounts` FOR EACH ROW BEGIN
  CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Accounts',NEW.i_billing,NEW.id,NULL);
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Accounts`;
CREATE TRIGGER `Trg_Update_Accounts` AFTER UPDATE ON `Accounts` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
  IF NEW.i_env=1 AND OLD.i_env=1 AND (NEW.id <> OLD.id OR IFNULL(NEW.h323_password,'_PBNULL_') <>  IFNULL(OLD.h323_password,'_PBNULL_')) THEN
    SET todo = 'UPDATE';
  ELSE
    IF NEW.i_env=1 AND IFNULL(OLD.i_env,-1) <> 1 THEN
      SET todo = 'INSERT';
    ELSEIF IFNULL(NEW.i_env,-1) <> 1 AND OLD.i_env=1 THEN
      SET todo = 'DELETE';
    END IF;
  END IF;
  IF todo != "IGNORE" THEN
    CALL CHANGE_IP_CUSTOMERS_AND_USERS(todo,'Accounts',NEW.i_billing,NEW.id,OLD.id);
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Accounts`;
CREATE TRIGGER `Trg_Delete_Accounts` AFTER DELETE ON `Accounts` FOR EACH ROW BEGIN
  CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Accounts',OLD.i_billing,OLD.id,NULL);
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Connections`|
CREATE TRIGGER `Trg_Insert_Connections` AFTER INSERT ON `Connections` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN
    CALL CHANGE_VENDORS('INSERT','Connections',NEW.i_billing,NEW.remote_ip,NULL);
  END IF;
END |
DELIMITER ;
 
DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Connections`|
CREATE TRIGGER `Trg_Update_Connections` AFTER UPDATE ON `Connections` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
IF  NEW.remote_ip!=OLD.remote_ip OR NEW.i_env!=OLD.i_env OR NEW.sip!=OLD.sip THEN
  SET exist=IFNULL((SELECT 1 FROM `astrad`.vendors  WHERE remote_ip = OLD.remote_ip AND i_bill_auth = NEW.i_billing),0);
  IF  NEW.i_env=1 AND NEW.remote_ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' AND NEW.sip='Y' THEN
    SET valid=1;
  END IF;
  IF valid = 1 AND exist = 1 THEN
    SET todo = 'UPDATE';
  ELSEIF valid <> 1 AND exist = 1 THEN
    SET todo = 'DELETE';
  ELSEIF valid = 1 AND exist <> 1 THEN
    SET todo = 'INSERT';
  END IF;
  CALL CHANGE_VENDORS(todo,'Connections',NEW.i_billing,NEW.remote_ip,OLD.remote_ip);
END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Connections`|
CREATE TRIGGER `Trg_Delete_Connections` AFTER DELETE ON `Connections` FOR EACH ROW BEGIN
  CALL CHANGE_VENDORS('DELETE','Connections',OLD.i_billing,OLD.remote_ip,NULL);
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_Nodes`|
CREATE TRIGGER `Trg_Insert_Nodes` AFTER INSERT ON `Nodes` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND NEW.i_node_type=11 AND NEW.name not like '%[Deleted]%' THEN
    CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Nodes',NEW.i_billing,NEW.ip,NULL);
    CALL CHANGE_VENDORS('INSERT','Nodes',NEW.i_billing,NEW.ip,NULL);
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Nodes`|
CREATE TRIGGER `Trg_Update_Nodes` AFTER UPDATE ON `Nodes` FOR EACH ROW BEGIN
  DECLARE todo VARCHAR(10) DEFAULT 'IGNORE';
  DECLARE exist INT default 0;
  DECLARE valid INT default 0;
  SET exist=IFNULL((SELECT 1 FROM `astrad`.ip_customers WHERE remote_ip=OLD.ip AND is_node='Y' AND i_bill_auth=NEW.i_billing),0);
  IF NEW.i_node_type=11 AND NEW.name NOT LIKE '%[Deleted]%' AND NEW.i_env=1 AND NEW.ip REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
    SET valid=1;
  END IF;
  IF valid = 1 THEN
    IF exist = 1 AND NEW.ip != OLD.ip THEN
      SET todo = 'UPDATE';
    ELSE
      SET todo = 'INSERT';
    END IF;
  ELSE
    IF exist = 1 THEN
      SET todo = 'DELETE';
    END IF;
  END IF;
  CALL CHANGE_IP_CUSTOMERS_AND_USERS(todo,'Nodes',NEW.i_billing,NEW.ip,OLD.ip);
  CALL CHANGE_VENDORS(todo,'Nodes',NEW.i_billing,NEW.ip,OLD.ip);
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Delete_Nodes`|
CREATE TRIGGER `Trg_Delete_Nodes` AFTER DELETE ON `Nodes` FOR EACH ROW BEGIN
    IF (SELECT 1 FROM `astrad`.ip_customers  WHERE remote_ip = OLD.ip AND is_node='Y' AND i_bill_auth=OLD.i_billing) THEN
      CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Nodes',OLD.i_billing,OLD.ip,NULL);
      CALL CHANGE_VENDORS('DELETE','Nodes',OLD.i_billing,OLD.ip,NULL);
    END IF;
  END |
DELIMITER ;

DELIMITER |
DROP PROCEDURE IF EXISTS `UPDATE_CUSTOMER`|
CREATE PROCEDURE `UPDATE_CUSTOMER`(IN billing INT(2), IN icustomer INT(10) unsigned, IN oirep INT(10) unsigned, IN nirep INT(10) unsigned, IN oienv tinyint(1) unsigned, IN nienv tinyint(1) unsigned)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE test INT DEFAULT 1;
  DECLARE account VARCHAR(30);
  DECLARE todo    VARCHAR(10) DEFAULT 'IGNORE';
  DECLARE cur CURSOR FOR SELECT id FROM Accounts where i_customer=icustomer AND i_billing=billing;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  IF ((oirep NOT IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv=1 AND nienv=1) OR
  (oirep NOT IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv!=1 AND nienv=1) OR
  (oirep IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv!=1 AND nienv=1)) THEN
    SET todo = 'INSERT';
  ELSEIF ((oirep IN (3,17,5,9,12) AND nirep IN (3,17,5,9,12) AND oienv=1 AND nienv!=1) OR
  (oirep IN (3,17,5,9,12) AND nirep NOT IN (3,17,5,9,12) AND oienv=1 AND nienv=1) OR
  (oirep IN (3,17,5,9,12) AND nirep NOT IN (3,17,5,9,12) AND oienv=1 AND nienv!=1)) THEN
    SET todo = 'DELETE';
  END IF;
  IF todo != "IGNORE" THEN
    OPEN cur;
    read_loop: LOOP
      FETCH cur INTO account;
      IF done THEN
        LEAVE read_loop;
      END IF;
      IF todo = "INSERT" THEN
        CALL CHANGE_IP_CUSTOMERS_AND_USERS('INSERT','Customers',billing,account,NULL);
      ELSEIF todo = "DELETE" THEN
        CALL CHANGE_IP_CUSTOMERS_AND_USERS('DELETE','Customers',billing,account,NULL);
      END IF;
    END LOOP;
    CLOSE cur;
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Update_Customers`|
CREATE TRIGGER `Trg_Update_Customers` AFTER UPDATE ON `Customers` FOR EACH ROW BEGIN
  DECLARE n_i_rep int(10) unsigned;
  DECLARE o_i_rep int(10) unsigned;
  DECLARE n_i_env tinyint(1) unsigned;
  DECLARE o_i_env tinyint(1) unsigned;
  SET n_i_rep=IFNULL(NEW.i_rep,0);
  SET o_i_rep=IFNULL(OLD.i_rep,0);
  SET n_i_env=IFNULL(NEW.i_env,0);
  SET o_i_env=IFNULL(OLD.i_env,0);
  IF o_i_rep != n_i_rep OR o_i_env != n_i_env THEN 
    CALL UPDATE_CUSTOMER(NEW.i_billing,NEW.i_customer,o_i_rep,n_i_rep,o_i_env,n_i_env);
  END IF;
END |
DELIMITER ;



-- 
-- ADD `astrob` USER
-- 

INSERT IGNORE INTO mysql.user (User,Host,Password) VALUES('<%= mysql_localhost_user %>','localhost','');
FLUSH PRIVILEGES;
GRANT all privileges ON `asterisk`.* TO '<%= mysql_localhost_user %>'@'localhost' IDENTIFIED BY '';
FLUSH PRIVILEGES;

--
-- ADD `monitoring` USERS
--

INSERT IGNORE INTO mysql.user (User,Host,Password) VALUES('<%= monitor_db_user  %>','<%= cacti_ip %>','');
INSERT IGNORE INTO mysql.user (User,Host,Password) VALUES ('<%= monitor_db_user  %>','<%= monitor_db_ip %>',PASSWORD(''));
INSERT IGNORE INTO mysql.user (User,Host,Password) VALUES ('<%= monitor_db_user  %>','<%= monitor_db_ip2 %>',PASSWORD(''));
FLUSH PRIVILEGES;
GRANT SUPER, PROCESS, SELECT ON *.* TO '<%= monitor_db_user  %>'@'<%= cacti_ip %>' IDENTIFIED BY "";
GRANT all privileges ON *.* TO '<%= monitor_db_user  %>'@'<%= monitor_db_ip %>' IDENTIFIED BY '';
GRANT all privileges ON *.* TO '<%= monitor_db_user  %>'@'<%= monitor_db_ip2 %>' IDENTIFIED BY '';
FLUSH PRIVILEGES;


USE `astrad`;

INSERT INTO `realm` (val) VALUE ('switzernet');
INSERT INTO `config` values('version','<%= dba_version %>','DBA version','');
INSERT INTO `config` VALUES ('day_log_history',60,'Keep log registration for number of days specified.','');
INSERT INTO `config` VALUES ('max_reg_to_check',1000,'Execute delete query  every N registration received.','');
INSERT INTO `config` VALUES ('tmp_reg_to_check',1,'Used by procedure REGISTER, auto-increment for each registration received.','');
INSERT INTO `config` VALUES ('ipaddr','<%= ipaddress %>','ip adress.','');
INSERT INTO `config` VALUES ('hostname','<%= fqdn %>','server hostname.','');

USE `porta-billing`;

DROP TABLE IF EXISTS `AccountsAuth`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `AccountsAuth` (
  `accountid` varchar(32) NOT NULL,
  `ibillauth` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`accountid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

DELIMITER |
DROP PROCEDURE IF EXISTS `INS_ACC_USERS`|
CREATE PROCEDURE `INS_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE rm VARCHAR(80);
  DECLARE md varchar(32);
  DECLARE ibillauth tinyint(11);
  DECLARE ibillacc VARCHAR(100);
  IF (account REGEXP '^[0-9]+$') AND
    (SELECT IFNULL((SELECT 1 FROM Customers WHERE i_customer = (SELECT i_customer FROM Accounts WHERE id = account AND i_billing=billing AND i_env=1 limit 1) AND i_rep IN (3,5,9,12,17) AND i_billing=billing limit 1),0)) THEN
    SET ibillauth = (SELECT IFNULL((SELECT ibillauth FROM AccountsAuth WHERE accountid=account LIMIT 1),billing));
    SET ibillacc  = (SELECT GET_IBILLACC_FOR_USERS(account));
    IF (ibillauth = billing) THEN
      SET rm = (SELECT val FROM `astrad`.realm ORDER BY id DESC LIMIT 1);
      SET md = (SELECT md5(CONCAT(account,':',rm,':',IFNULL((SELECT h323_password FROM Accounts WHERE id = account AND i_billing=ibillauth limit 1),''))));
      INSERT INTO `astrad`.users (username, md5secret, i_bill_auth, i_bill_acc) values (account, md, billing, ibillacc) ON DUPLICATE KEY UPDATE md5secret=md, i_bill_auth = ibillauth, i_bill_acc = ibillacc;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Created or modified users account ',account));
    ELSE
      UPDATE `astrad`.users SET i_bill_acc = ibillacc WHERE username = account;
      CALL LOG_TRIGGER(todo,tablename,CONCAT('Modified users account ',account));
    END IF;
  END IF;
END |

DROP PROCEDURE IF EXISTS `DEL_ACC_USERS`|
CREATE PROCEDURE `DEL_ACC_USERS`(IN todo VARCHAR(30), IN tablename VARCHAR(30), IN billing tinyint(2) unsigned, IN account varchar(30))
BEGIN
  DECLARE ibillauth tinyint(2);
  SET ibillauth = (SELECT IFNULL((SELECT ibillauth FROM AccountsAuth WHERE accountid=account LIMIT 1),billing));
  IF (SELECT IFNULL((SELECT 1 FROM `astrad`.users WHERE username = account AND i_bill_auth = ibillauth LIMIT 1),0)) THEN
    DELETE FROM `astrad`.users WHERE username = account;
    CALL LOG_TRIGGER(todo,tablename,CONCAT('Deleted users account ',account));
  END IF;
END |
DELIMITER ;

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_AccountsAuth`|
CREATE TRIGGER `Trg_Insert_AccountsAuth` AFTER INSERT ON `AccountsAuth` FOR EACH ROW BEGIN
  CALL INS_ACC_USERS("INSERT", "AccountsAuth", NEW.ibillauth, NEW.accountid);
END |

DROP TRIGGER IF EXISTS `Trg_Update_AccountsAuth`|
CREATE TRIGGER `Trg_Update_AccountsAuth` AFTER UPDATE ON `AccountsAuth` FOR EACH ROW BEGIN
  IF (OLD.accountid != NEW.accountid OR OLD.ibillauth != NEW.ibillauth) THEN
    CALL DEL_ACC_USERS("DELETE", "AccountsAuth", OLD.ibillauth, OLD.accountid);
  END IF;
  CALL INS_ACC_USERS("INSERT", "AccountsAuth", NEW.ibillauth, NEW.accountid);
END |

DROP TRIGGER IF EXISTS `Trg_Delete_AccountsAuth`|
CREATE TRIGGER `Trg_Delete_AccountsAuth` AFTER DELETE ON `AccountsAuth` FOR EACH ROW BEGIN
  CALL DEL_ACC_USERS("DELETE", "AccountsAuth", OLD.ibillauth, OLD.accountid);
END |
DELIMITER ;