USE kamailio;

CREATE TABLE `kamailio`.`lcr_gw` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lcr_id` smallint(5) unsigned NOT NULL,
  `gw_name` varchar(128) NOT NULL,
  `ip_addr` varchar(15) NOT NULL,
  `hostname` varchar(64) DEFAULT NULL,
  `port` smallint(5) unsigned DEFAULT NULL,
  `params` varchar(64) DEFAULT NULL,
  `uri_scheme` tinyint(3) unsigned DEFAULT NULL,
  `transport` tinyint(3) unsigned DEFAULT NULL,
  `strip` tinyint(3) unsigned DEFAULT NULL,
  `tag` varchar(16) DEFAULT NULL,
  `flags` int(10) unsigned NOT NULL DEFAULT '0',
  `defunct` int(10) unsigned DEFAULT NULL,
  `group_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lcr_id_gw_name_idx` (`lcr_id`,`gw_name`),
  UNIQUE KEY `lcr_id_ip_addr_idx` (`lcr_id`,`ip_addr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `kamailio`.`lcr_rule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lcr_id` smallint(5) unsigned NOT NULL,
  `prefix` varchar(16) DEFAULT NULL,
  `from_uri` varchar(64) DEFAULT NULL,
  `stopper` int(10) unsigned NOT NULL DEFAULT '0',
  `enabled` int(10) unsigned NOT NULL DEFAULT '1',
  `group_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `lcr_id_idx` (`lcr_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `kamailio`.`lcr_rule_target` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `lcr_id` smallint(5) unsigned NOT NULL,
  `rule_id` int(10) unsigned NOT NULL,
  `gw_id` int(10) unsigned NOT NULL,
  `priority` tinyint(3) unsigned NOT NULL,
  `weight` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_id_gw_id_idx` (`rule_id`,`gw_id`),
  KEY `lcr_id_idx` (`lcr_id`),
  KEY `gw_id_idx` (`gw_id`),
  CONSTRAINT `l_r_t_ruleid_ref` FOREIGN KEY (`rule_id`)
                                REFERENCES `lcr_rule` (`id`)
                                ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `l_r_t_gwid_ref` FOREIGN KEY (`gw_id`)
                              REFERENCES `lcr_gw` (`id`)
                              ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


USE provisioning;

DROP trigger voip_prul_crepl_trig;
DROP trigger voip_prul_urepl_trig;
DROP trigger voip_prul_drepl_trig;
DROP trigger voip_pgrp_urepl_trig;
DROP trigger voip_pgrp_drepl_trig;
DROP trigger voip_phost_crepl_trig;
DROP trigger voip_phost_urepl_trig;
DROP trigger voip_phost_drepl_trig;

DELIMITER |

CREATE TRIGGER voip_prul_crepl_trig AFTER INSERT ON voip_peer_rules
  FOR EACH ROW BEGIN

  INSERT INTO kamailio.lcr_rule (lcr_id, prefix, from_uri, stopper, enabled, group_id)
                          VALUES(1, NEW.callee_prefix,
                                 IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), ''),
                                 0, 1, NEW.group_id);

  INSERT INTO kamailio.lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
         SELECT gw.lcr_id, LAST_INSERT_ID(), gw.id, vpg.priority, vph.weight
           FROM kamailio.lcr_gw gw
           INNER JOIN provisioning.voip_peer_hosts vph ON vph.ip = gw.ip_addr
                                                      AND gw.lcr_id = 1
                                                      AND vph.group_id = gw.group_id
           INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = vph.group_id
          WHERE vph.group_id = NEW.group_id;

  END;
|

CREATE TRIGGER voip_prul_urepl_trig AFTER UPDATE ON voip_peer_rules
  FOR EACH ROW BEGIN
  
  UPDATE kamailio.lcr_rule
     SET prefix = NEW.callee_prefix,
         from_uri = IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), '')
   WHERE prefix = OLD.callee_prefix
     AND from_uri = IF(LENGTH(OLD.caller_prefix), CONCAT('^', OLD.caller_prefix), '')
     AND group_id = OLD.group_id;

  END;
|

CREATE TRIGGER voip_prul_drepl_trig AFTER DELETE ON voip_peer_rules
  FOR EACH ROW BEGIN

  DELETE FROM kamailio.lcr_rule
        WHERE prefix = OLD.callee_prefix
          AND from_uri = IF(LENGTH(OLD.caller_prefix), CONCAT('^', OLD.caller_prefix), '')
          AND group_id = OLD.group_id;

  -- foreign key will delete from kamailio.lcr_rule_target

  END;
|

CREATE TRIGGER voip_pgrp_urepl_trig AFTER UPDATE ON voip_peer_groups
  FOR EACH ROW BEGIN

  UPDATE kamailio.lcr_rule_target rt, kamailio.lcr_gw gw
     SET rt.priority = NEW.priority
   WHERE gw.id = rt.gw_id
     AND gw.lcr_id = 1
     AND gw.group_id = NEW.id;

  END;
|

CREATE TRIGGER voip_pgrp_drepl_trig AFTER DELETE ON voip_peer_groups
  FOR EACH ROW BEGIN

  DELETE FROM kamailio.lcr_rule WHERE group_id = OLD.id;
  DELETE FROM kamailio.lcr_gw WHERE group_id = OLD.id;

  END;
|

CREATE TRIGGER voip_phost_crepl_trig AFTER INSERT ON voip_peer_hosts
  FOR EACH ROW BEGIN

  INSERT INTO kamailio.lcr_gw (lcr_id, gw_name, ip_addr, port, uri_scheme, transport, strip, flags, group_id)
                        VALUES(1, NEW.name, NEW.ip, NEW.port, 1, 1, 0, NEW.id, NEW.group_id);

  INSERT INTO kamailio.lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
         SELECT rule.lcr_id, rule.id, LAST_INSERT_ID(), vpg.priority, NEW.weight
           FROM kamailio.lcr_rule rule
           INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = rule.group_id
          WHERE vpg.id = NEW.group_id;

  END;
|

CREATE TRIGGER voip_phost_urepl_trig AFTER UPDATE ON voip_peer_hosts
  FOR EACH ROW BEGIN

  UPDATE kamailio.lcr_gw
     SET gw_name = NEW.name, ip_addr = NEW.ip, port = NEW.port, flags = NEW.id
   WHERE lcr_id = 1 AND ip_addr = OLD.ip;

  UPDATE kamailio.lcr_rule_target rt, kamailio.lcr_gw gw
     SET rt.weight = NEW.weight
   WHERE gw.id = rt.gw_id
     AND gw.lcr_id = 1
     AND gw.group_id = NEW.group_id
     AND gw.ip_addr = NEW.ip;

  END;
|

CREATE TRIGGER voip_phost_drepl_trig AFTER DELETE ON voip_peer_hosts
  FOR EACH ROW BEGIN

  DELETE FROM kamailio.lcr_gw
        WHERE lcr_id = 1
          AND group_id = OLD.group_id
          AND ip_addr = OLD.ip;

  -- foreign key will delete from kamailio.lcr_rule_target

  END;
|

DELIMITER ;

USE kamailio;

INSERT INTO lcr_rule (lcr_id, prefix, from_uri, stopper, enabled, group_id)
       SELECT 1, callee_prefix,
              IF(LENGTH(caller_prefix), CONCAT('^', caller_prefix), ''),
                0, 1, group_id
         FROM provisioning.voip_peer_rules;

INSERT INTO lcr_gw (lcr_id, gw_name, ip_addr, port, uri_scheme, transport, strip, flags, group_id)
       SELECT 1, name, ip, port, 1, 1, 0, id, group_id
         FROM provisioning.voip_peer_hosts;

INSERT INTO lcr_rule_target (lcr_id, rule_id, gw_id, priority, weight)
       SELECT rule.lcr_id, rule.id, gw.id, vpg.priority, vph.weight
         FROM kamailio.lcr_rule rule
         INNER JOIN provisioning.voip_peer_groups vpg ON vpg.id = rule.group_id
         INNER JOIN provisioning.voip_peer_hosts vph ON vpg.id = vph.group_id
         INNER JOIN lcr_gw gw ON gw.ip_addr = vph.ip AND gw.lcr_id = 1;

DROP table lcr;
DROP table gw;