--
-- Databases
--
DROP DATABASE IF EXISTS `astrad`;
CREATE DATABASE `astrad`;
DROP DATABASE IF EXISTS `ast-replication`;
CREATE DATABASE `ast-replication`;
DROP DATABASE IF EXISTS `porta-billing`;
CREATE DATABASE `porta-billing`;

--
-- Dumping database 'astrad'
--
USE `astrad`;

--
-- Table structure for table `sippeers`
--

CREATE TABLE `sippeers` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  `host` varchar(15) NOT NULL default 'dynamic',
  `context` varchar(80) NOT NULL default 'forbidden',
  `insecure` varchar(15) NOT NULL default 'no',
  `ipaddr` varchar(15) default NULL,
  `port` smallint(5) unsigned default NULL,
  `regseconds` int(11) default NULL,
  `username` varchar(80) default NULL,
  `md5secret` varchar(32) default NULL,
  `nat` varchar(15) default 'yes',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ;

--
-- Table structure for table `sipusers`
--

CREATE TABLE `sipusers` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(80) NOT NULL default '',
  `context` varchar(80) NOT NULL default 'fromaccount',
  `md5secret` varchar(32) default NULL,
  `nat` varchar(15) default 'yes',
  `deny` text,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ;

--
-- Dumping database 'ast-replication'
--
USE `ast-replication`;

--
-- Table structure for table `ip_customers`
--

CREATE TABLE `ip_customers` (
  `id` int(11) NOT NULL auto_increment,
  `remote_ip` varchar(15) NOT NULL default '',
  `is_node` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `remote_ip` (`remote_ip`)
) ;

--
-- Dumping triggers for table `ip_customers`
--

DELIMITER |
CREATE TRIGGER `Trg_Insert_ip_customers` AFTER INSERT ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN 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 astrad.sippeers WHERE name=NEW.remote_ip AND insecure='no' AND context='forbidden') IS NULL THEN
    INSERT IGNORE INTO astrad.sippeers (name,host,port,context,insecure) VALUES (NEW.remote_ip,NEW.remote_ip,5060,cont,'port');
    INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Add to sippeers ',NEW.remote_ip,' context=',cont));
ELSE
    UPDATE astrad.sippeers SET context=cont, insecure='port' WHERE name=NEW.remote_ip AND insecure='no' AND context='forbidden';
    INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Set context (',cont,') to ip_customer in sippeers ',NEW.remote_ip));
END IF;
SET DN=(SELECT get_deny());
UPDATE astrad.sipusers SET deny=DN;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Update deny in sipusers',NEW.remote_ip));
END |

--

CREATE TRIGGER `Trg_Update_ip_customers` AFTER UPDATE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN 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;
UPDATE astrad.sippeers SET name=NEW.remote_ip, host=NEW.remote_ip, context=cont WHERE name=OLD.remote_ip;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','UPDATE',NOW(),CONCAT('update ip customer in sippeers remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));
IF OLD.remote_ip <> NEW.remote_ip THEN
  SET DN=(SELECT get_deny());
  UPDATE astrad.sipusers SET deny=DN;
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update deny in sipusers from',OLD.remote_ip,' to ',NEW.remote_ip));
END IF;
END |

--

CREATE TRIGGER `Trg_Delete_ip_customers` AFTER DELETE ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
IF (SELECT 1 FROM vendors WHERE remote_ip=OLD.remote_ip) THEN
  UPDATE astrad.sippeers SET context='forbidden', insecure='no' WHERE name=OLD.remote_ip;
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update context to forbidden in sippeers ',OLD.remote_ip));
ELSE
  DELETE FROM astrad.sippeers WHERE name=OLD.remote_ip;
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Delete from sippeers ',OLD.remote_ip));
END IF;
SET DN=(SELECT get_deny());
UPDATE astrad.sipusers SET deny=DN;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','DELETE',NOW(),CONCAT('Update deny in sipusers',OLD.remote_ip));
END |

DELIMITER ;

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

CREATE TABLE `realm` (
  `id` int(11) NOT NULL auto_increment,
  `val` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `val` (`val`)
) ;

--
-- Dumping data for table `realm`
--

INSERT INTO `realm` (val) VALUES ('switzernet');

--
-- Dumping triggers for table `realm`
--

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

--

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) THEN
    IF NEW.val <> OLD.val THEN
      CALL SET_REALM(NEW.val,'UPDATE');
END IF;
  END IF;
END |

--

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 THEN
    IF rm <> OLD.val THEN
      CALL SET_REALM(rm,'DELETE');
END IF;
  END IF;
END |

DELIMITER ;

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

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`)
) ;

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `md5secret` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
) ;

--
-- Dumping triggers for table `users`
--

DELIMITER |
CREATE TRIGGER `Trg_Insert_users` AFTER INSERT ON `users` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
SET DN=(SELECT get_deny());
INSERT IGNORE INTO astrad.sipusers (name,md5secret,deny) VALUES (NEW.username,NEW.md5secret,DN);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sipusers ',NEW.username));
INSERT IGNORE INTO astrad.sippeers (name,md5secret) VALUES (NEW.username,NEW.md5secret);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sippeers ',NEW.username));
END |

--

CREATE TRIGGER `Trg_Update_users` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
UPDATE astrad.sipusers SET name=NEW.username, md5secret=NEW.md5secret WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','UPDATE',NOW(),CONCAT('update sipusers new username=',NEW.username,' old username=',OLD.username));
UPDATE astrad.sippeers SET name=NEW.username, md5secret=NEW.md5secret WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','UPDATE',NOW(),CONCAT('update sippeers user=',NEW.username,' old username=',OLD.username));
END |

--

CREATE TRIGGER `Trg_Delete_users` AFTER DELETE ON `users` FOR EACH ROW BEGIN
DELETE FROM astrad.sipusers WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','DELETE',NOW(),CONCAT('Delete from sipusers ',OLD.username));
DELETE FROM astrad.sippeers WHERE name=OLD.username;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','DELETE',NOW(),CONCAT('Delete from sippeers ',OLD.username));
END |

DELIMITER ;

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

CREATE TABLE `vendors` (
  `id` int(11) NOT NULL auto_increment,
  `remote_ip` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `remote_ip` (`remote_ip`)
) ;

--
-- Dumping triggers for table `vendors`
--

DELIMITER |
CREATE TRIGGER `Trg_Insert_vendors` AFTER INSERT ON `vendors` FOR EACH ROW BEGIN
IF (SELECT 1 FROM astrad.sippeers WHERE name=NEW.remote_ip) IS NULL THEN
  INSERT IGNORE INTO astrad.sippeers (name,host,port) VALUES (NEW.remote_ip,NEW.remote_ip,5060);
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Add vendor to sippeers ',NEW.remote_ip));
ELSE
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Vendor already exist on sippeers (ip existe on ip_customers)',NEW.remote_ip));
END IF;
END |

--

CREATE TRIGGER `Trg_Update_vendors` AFTER UPDATE ON `vendors` FOR EACH ROW BEGIN
UPDATE astrad.sippeers SET name=NEW.remote_ip, host=NEW.remote_ip WHERE name=OLD.remote_ip;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','UPDATE',NOW(),CONCAT('update sippeers remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));
END |

--

CREATE TRIGGER `Trg_Delete_vendors` AFTER DELETE ON `vendors` FOR EACH ROW BEGIN
IF (SELECT 1 FROM astrad.sippeers WHERE name=OLD.remote_ip AND insecure='no' AND context='forbidden') THEN
  DELETE FROM astrad.sippeers WHERE name=OLD.remote_ip AND insecure='no';
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','DELETE',NOW(),CONCAT('Delete vendor from sippeers ',OLD.remote_ip));
ELSE
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','DELETE',NOW(),CONCAT('Deletion vendor from sippeers ignored (ip existe on ip_customers) ',OLD.remote_ip));
END IF;
END |

DELIMITER ;

--
-- Dumping routines for database 'ast-replication'
--

DELIMITER |
CREATE FUNCTION `get_deny`() 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 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 |

--

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,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 database 'porta-billing'
--
USE `porta-billing`;

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

CREATE TABLE `Accounts` (
  `i_account` int(10) unsigned NOT NULL auto_increment,
  `id` char(32) NOT NULL default '',
  `password` char(16) default NULL,
  `h323_password` char(255) 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 NOT NULL default '0',
  `i_env` tinyint(1) unsigned NOT NULL default '1',
  `first_usage` date default NULL,
  `life_time` smallint(4) unsigned default NULL,
  `redirect_number` char(15) default NULL,
  `control_number` int(10) unsigned NOT NULL default '0',
  `iso_639_1` char(2) default NULL,
  `i_time_zone` int(10) unsigned NOT NULL default '1',
  `credit_limit` decimal(15,5) default NULL,
  `iso_4217` char(3) NOT NULL default '',
  `non_call_related_charges` decimal(15,5) default NULL,
  `refunds` decimal(15,5) default NULL,
  `login` char(32) default NULL,
  `email` char(128) 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` char(23) default NULL,
  `i_ua_profile` int(10) unsigned default NULL,
  `follow_me_enabled` char(1) NOT NULL default 'N',
  `timer` datetime default NULL,
  `ecommerce_enabled` char(1) default 'N',
  `out_date_format` char(16) NOT NULL default '',
  `out_time_format` char(16) NOT NULL default '',
  `out_date_time_format` char(32) NOT NULL default '',
  `in_date_format` char(16) NOT NULL default '',
  `in_time_format` char(16) NOT NULL default '',
  `i_vd_plan` int(10) unsigned default NULL,
  `last_recharge` datetime default NULL,
  `i_moh` int(10) unsigned default NULL,
  `service_flags` char(32) NOT NULL default '',
  PRIMARY KEY  (`i_account`),
  UNIQUE KEY `Accounts_id` (`id`,`i_env`),
  UNIQUE KEY `Accounts_cn_batch` (`i_batch`,`control_number`),
  UNIQUE KEY `Accounts_login` (`login`),
  KEY `Account_customer` (`i_customer`),
  KEY `Accounts_acl` (`i_acl`),
  KEY `Accounts_i_product` (`i_product`),
  KEY `Accounts_product` (`i_product`),
  KEY `AccountsProfileId` (`ua_profile_id`),
  KEY `Accounts_mac` (`mac`,`i_env`),
  KEY `Accounts_um_enabled` (`um_enabled`),
  KEY `Accounts_iso_4217` (`iso_4217`),
  KEY `AccountFirstUsage` (`first_usage`),
  KEY `i_vd_plan` (`i_vd_plan`),
  KEY `accounts_maintenance` (`i_product`,`blocked`,`expiration_date`)
) ;

--
-- Dumping triggers for table `Accounts`
--

DELIMITER |
CREATE TRIGGER `Trg_Insert_Accounts` AFTER INSERT ON `Accounts` FOR EACH ROW BEGIN
  IF NEW.i_env=1 AND (SELECT 1 FROM Customers WHERE i_customer=NEW.i_customer AND i_rep IN (3,5,9,12)) THEN
    IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to ip_customers ',NEW.id));
    ELSE
      CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','INSERT',NOW(),CONCAT('Add to users ',NEW.id));
    END IF;
  END IF;
END |

--

CREATE TRIGGER `Trg_Update_Accounts` AFTER UPDATE ON `Accounts` FOR EACH ROW BEGIN
DECLARE is_ip INT DEFAULT 0;
DECLARE todo VARCHAR(10) DEFAULT 0;
IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
  SET is_ip = 1;
END IF;
IF NEW.i_env=1 AND OLD.i_env=1 AND (NEW.id <> OLD.id OR NEW.h323_password <> OLD.h323_password) THEN
  SET todo = 'UPDATE';
ELSE
  IF NEW.i_env=1 AND OLD.i_env <> 1 THEN
    SET todo = 'INSERT';
  ELSEIF NEW.i_env <> 1 AND OLD.i_env=1 THEN
    SET todo = 'DELETE';
  END IF;
END IF;
CASE todo
  WHEN 'UPDATE' THEN
    IF is_ip THEN
      UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.id WHERE remote_ip=OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Update ip_customers set ',OLD.id,' to ',NEW.id));
ELSE
      CALL INSERT_USER(NEW.id,NEW.h323_password,'UPDATE',OLD.id);
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('update user password : ',OLD.id));
END IF;
  WHEN 'INSERT' THEN
    IF is_ip THEN
  INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.id,'N');
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to ip_customers ',NEW.id));
ELSE
    CALL INSERT_USER(NEW.id,NEW.h323_password,'INSERT','');
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Add to user ',NEW.id));
END IF;
  WHEN 'DELETE' THEN
    IF is_ip THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from ip_customers ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));
ELSE
      DELETE FROM `ast-replication`.users WHERE username = OLD.id;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','UPDATE',NOW(),CONCAT('Delete from users ',OLD.id,' (i_env <> 1) i_env: ',OLD.i_env,' to ',NEW.i_env));
END IF;
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Accounts` AFTER DELETE ON `Accounts` FOR EACH ROW BEGIN
  IF OLD.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.id AND is_node='N') THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.id AND is_node='N';
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from ip_customers ',OLD.id));
    END IF;
  ELSE
    IF (SELECT 1 FROM `ast-replication`.users  WHERE username = OLD.id) THEN
      DELETE FROM `ast-replication`.users WHERE username = OLD.id;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Accounts','DELETE',NOW(),CONCAT('Delete from users ',OLD.id));
    END IF;
  END IF;
  END |

DELIMITER ;

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

CREATE TABLE `Connections` (
  `i_connection` int(10) unsigned NOT NULL auto_increment,
  `i_vendor` int(10) unsigned NOT NULL default '0',
  `description` char(255) NOT NULL default '',
  `i_node` int(10) unsigned default NULL,
  `i_tariff` int(10) unsigned default NULL,
  `call_origin` enum('answer','originate','callback','proxy','unknown') NOT NULL default 'unknown',
  `call_type` enum('Telephony','VoIP','unknown') NOT NULL default 'unknown',
  `port` char(64) default NULL,
  `CLD` char(32) default NULL,
  `i_env` tinyint(1) unsigned NOT NULL default '1',
  `cld_translation_rule` char(255) default NULL,
  `remote_ip` char(64) 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` char(255) 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` char(255) default NULL,
  `rtpp_level` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`i_connection`),
  KEY `Connection_i_vendor` (`i_vendor`),
  KEY `Connections_port` (`port`),
  KEY `i_tariff` (`i_tariff`)
) ;

--
-- Dumping triggers for table `Connections`
--

DELIMITER |
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
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','INSERT',NOW(),CONCAT('Add to vendors ',NEW.remote_ip));
  END IF;
END |

--

CREATE TRIGGER `Trg_Update_Connections` AFTER UPDATE ON `Connections` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
SET exist=IFNULL((SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip),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;
CASE todo
  WHEN 'UPDATE' THEN
    UPDATE `ast-replication`.vendors SET remote_ip=NEW.remote_ip WHERE remote_ip=OLD.remote_ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Update vendors set ',OLD.remote_ip,' to ',NEW.remote_ip));
  WHEN 'INSERT' THEN
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.remote_ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Add to vendors ',NEW.remote_ip,' old ip should be *SIP-UA* or NULL -> ',OLD.remote_ip));
  WHEN 'DELETE' THEN
    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','UPDATE',NOW(),CONCAT('Delete from vendors ',NEW.remote_ip,' OLD.remote_ip -> ',OLD.remote_ip));
  WHEN 'IGNORED' THEN
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.remote_ip));
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Connections` AFTER DELETE ON `Connections` FOR EACH ROW BEGIN
    IF (SELECT 1 FROM `ast-replication`.vendors  WHERE remote_ip = OLD.remote_ip) THEN
      DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.remote_ip;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Connections','DELETE',NOW(),CONCAT('Delete from vendors ',OLD.remote_ip));
    END IF;
  END |

DELIMITER ;

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

CREATE TABLE `Customers` (
  `i_customer` int(10) unsigned NOT NULL auto_increment,
  `refnum` char(32) default NULL,
  `name` char(41) NOT NULL default '',
  `timestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `balance` decimal(15,5) NOT NULL default '0.00000',
  `iso_4217` char(3) NOT NULL default '',
  `companyname` char(41) NOT NULL default '',
  `salutation` char(15) NOT NULL default '',
  `firstname` char(25) NOT NULL default '',
  `midinit` char(5) NOT NULL default '',
  `lastname` char(25) NOT NULL default '',
  `baddr1` char(41) NOT NULL default '',
  `baddr2` char(41) NOT NULL default '',
  `baddr3` char(41) NOT NULL default '',
  `baddr4` char(41) NOT NULL default '',
  `baddr5` char(41) NOT NULL default '',
  `city` char(31) NOT NULL default '',
  `state` char(21) NOT NULL default '',
  `zip` char(13) NOT NULL default '',
  `country` char(31) NOT NULL default '',
  `note` char(41) NOT NULL default '',
  `cont1` char(41) NOT NULL default '',
  `phone1` char(21) NOT NULL default '',
  `faxnum` char(21) NOT NULL default '',
  `phone2` char(21) NOT NULL default '',
  `cont2` char(41) NOT NULL default '',
  `email` char(99) default NULL,
  `bcc` char(99) default NULL,
  `send_statistics` char(1) NOT NULL default 'N',
  `login` char(16) default NULL,
  `password` char(16) 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` char(16) 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 NOT NULL default '0',
  `drm_enabled` char(1) NOT NULL default 'N',
  `max_abbreviated_length` tinyint(1) unsigned default NULL,
  `password_timestamp` datetime default NULL,
  `out_date_format` char(16) NOT NULL default '',
  `out_time_format` char(16) NOT NULL default '',
  `out_date_time_format` char(32) NOT NULL default '',
  `in_date_format` char(16) NOT NULL default '',
  `in_time_format` char(16) NOT NULL default '',
  `i_online_payment_processor` tinyint(3) unsigned NOT NULL default '0',
  `merch_acc_password` char(64) default NULL,
  `merch_acc_login` char(64) 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) default NULL,
  `i_lang` char(5) default NULL,
  `cld_translation_rule` char(255) default NULL,
  `i_tariff_incoming` int(10) unsigned default NULL,
  `credit_limit_warning` char(25) 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) unsigned default NULL,
  `i_vd_plan` int(10) unsigned default NULL,
  `creation_date` datetime default NULL,
  `cli_in_translation_rule` char(255) default NULL,
  `i_moh` int(10) unsigned default NULL,
  `service_flags` char(32) NOT NULL default '',
  `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',
  PRIMARY KEY  (`i_customer`),
  UNIQUE KEY `customer` (`name`,`i_env`),
  UNIQUE KEY `login` (`login`),
  KEY `Customers_acl` (`i_acl`),
  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`)
) ;

--
-- Dumping triggers for table `Customers`
--

DELIMITER |
CREATE TRIGGER `Trg_Update_Customers` AFTER UPDATE ON `Customers` FOR EACH ROW BEGIN
  IF OLD.i_rep IN (3,5,9,12) AND NEW.i_rep NOT IN (3,5,9,12) THEN
      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);
  ELSEIF OLD.i_rep NOT IN (3,5,9,12) AND NEW.i_rep IN (3,5,9,12) THEN
      CALL UPDATE_CUSTOMER(NEW.i_rep,NEW.i_customer);
  END IF;
END |

DELIMITER ;

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

CREATE TABLE `Nodes` (
  `i_node` int(10) unsigned NOT NULL auto_increment,
  `ip` char(64) NOT NULL default '',
  `h323_id` char(255) NOT NULL default '',
  `name` char(41) default NULL,
  `type` enum('VOIP-GW','H323-GW','H323-GK','TERM','MCU','SIP Server','MVTS') default 'VOIP-GW',
  `radius_client` char(1) NOT NULL default 'N',
  `radius_key` char(32) default NULL,
  `i_env` tinyint(1) unsigned default '1',
  `hostname` char(255) default NULL,
  `domain` char(255) default NULL,
  `manufacturer` enum('Cisco','Quintum') default 'Cisco',
  `i_time_zone` int(10) unsigned NOT NULL default '1',
  `h323_password` char(255) default NULL,
  `cld_translation_rule` char(255) default NULL,
  `radius_source_ip` char(64) default NULL,
  `id` char(32) default NULL,
  `i_node_type` int(10) unsigned default NULL,
  `rtpp_level` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`i_node`),
  UNIQUE KEY `ip` (`ip`),
  UNIQUE KEY `h323_id` (`h323_id`),
  UNIQUE KEY `node` (`name`,`i_env`)
) ;

--
-- Dumping data for table `Nodes`
--

DELIMITER |
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
    INSERT INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');
    INSERT INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','INSERT',NOW(),CONCAT('Add to ip_customers and vendors ',NEW.ip));
  END IF;
END |

--

CREATE TRIGGER `Trg_Update_Nodes` AFTER UPDATE ON `Nodes` FOR EACH ROW BEGIN
DECLARE todo VARCHAR(10) DEFAULT 'IGNORED';
DECLARE exist INT default 0;
DECLARE valid INT default 0;
SET exist=IFNULL((SELECT 1 FROM `ast-replication`.ip_customers WHERE remote_ip=OLD.ip AND is_node='Y'),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 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;
INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('TEST ',NEW.ip,' valid ',valid,' exist ',exist));
CASE todo
  WHEN 'UPDATE' THEN
    UPDATE `ast-replication`.ip_customers SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip AND is_node='Y';
    UPDATE `ast-replication`.vendors SET remote_ip=NEW.ip WHERE remote_ip=OLD.ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ip_customers and vendors set ',OLD.ip,' to ',NEW.ip));
  WHEN 'INSERT' THEN
    INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (NEW.ip,'Y');
    INSERT IGNORE INTO `ast-replication`.vendors (remote_ip) values (NEW.ip);
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Add to ip_customers and vendors',NEW.ip));
  WHEN 'DELETE' THEN
    DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';
    DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Delete from ip_customers and vendors',NEW.ip));
  WHEN 'IGNORED' THEN
    INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','UPDATE',NOW(),CONCAT('Update ignored ',NEW.ip));
END CASE;
END |

--

CREATE TRIGGER `Trg_Delete_Nodes` AFTER DELETE ON `Nodes` FOR EACH ROW BEGIN
    IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = OLD.ip AND is_node='Y') THEN
      DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = OLD.ip AND is_node='Y';
          DELETE FROM `ast-replication`.vendors WHERE remote_ip = OLD.ip;
      INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Nodes','DELETE',NOW(),CONCAT('Delete from ip_customers and vendors',OLD.ip));
    END IF;
  END |

DELIMITER ;

--
-- Dumping routines for database 'porta-billing'
--

DELIMITER |
CREATE PROCEDURE `INSERT_USER`(IN account varchar(30), IN secret VARCHAR(80), IN todo VARCHAR(30), IN us VARCHAR(30))
BEGIN
  DECLARE rm VARCHAR(80);
  DECLARE md varchar(32);
  SET rm = (SELECT val FROM `ast-replication`.realm ORDER BY id DESC LIMIT 1);
  SET md = (SELECT md5(CONCAT(account,':',rm,':',secret)));
  IF secret IS NULL THEN
    SET md = (SELECT md5(CONCAT(account,':',rm,':')));
  END IF;
  IF todo LIKE "INSERT" THEN
    INSERT IGNORE INTO `ast-replication`.users (username,md5secret) values (account,md);
  ELSEIF todo LIKE "UPDATE" THEN
    UPDATE `ast-replication`.users SET md5secret=md, username=account WHERE username=us;
  END IF;
  END |

--

CREATE PROCEDURE `UPDATE_CUSTOMER`(IN irep INT(10) unsigned, IN icustomer INT(10) unsigned)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE account VARCHAR(30);
  DECLARE ienv    VARCHAR(30);
  DECLARE pwd     VARCHAR(30);
  DECLARE cur CURSOR FOR SELECT id,i_env,h323_password FROM Accounts where i_customer=icustomer;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  read_loop: LOOP
  FETCH cur INTO account,ienv,pwd ;
  IF done THEN
    LEAVE read_loop;
  END IF;
  IF irep NOT IN (3,5,9,12) THEN
    IF ienv=1 THEN
      IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        IF (SELECT 1 FROM `ast-replication`.ip_customers  WHERE remote_ip = account AND is_node='N') THEN
          DELETE FROM `ast-replication`.ip_customers WHERE remote_ip = account AND is_node='N';
          INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from ip_customers ',account));
        END IF;
      ELSE
        IF (SELECT 1 FROM `ast-replication`.users  WHERE username = account) THEN
          DELETE FROM `ast-replication`.users WHERE username = account;
          INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Delete from users ',account));
        END IF;
      END IF;
END IF;
  ELSE
    IF ienv=1 THEN
  IF account REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN
        INSERT IGNORE INTO `ast-replication`.ip_customers (remote_ip,is_node) values (account,'N');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to ip_customers ',account));
  ELSE
    CALL INSERT_USER(account,pwd,'INSERT','');
        INSERT INTO `ast-replication`.triggers_log (table_name,event,log_time,comment) VALUES ('Customers','UPDATE',NOW(),CONCAT('Add to users ',account));
  END IF;
END IF;
  END IF;
  END LOOP;
  CLOSE cur;
  END |

DELIMITER ;

-- Completed on 2011-03-17