USE provisioning;

-- create triggers that populate kamailio tables
DELIMITER |

CREATE TRIGGER voip_sub_crepl_trig AFTER INSERT ON voip_subscribers
  FOR EACH ROW BEGIN
  DECLARE subscriber_domain varchar(127);

  SELECT domain INTO subscriber_domain FROM voip_domains where id = NEW.domain_id;

  INSERT INTO kamailio.subscriber (username, domain, uuid, timezone, password, datetime_created, ha1, ha1b)
                   VALUES(NEW.username, subscriber_domain, NEW.uuid, NEW.timezone, NEW.password, '0',
                          MD5(CONCAT(NEW.username, ':', subscriber_domain, ':', NEW.password)),
                          MD5(CONCAT(NEW.username, '@', subscriber_domain, ':', subscriber_domain, ':', NEW.password)));
  END;
|

CREATE TRIGGER voip_sub_urepl_trig AFTER UPDATE ON voip_subscribers
  FOR EACH ROW BEGIN
  DECLARE old_subscriber_domain varchar(127);
  DECLARE new_subscriber_domain varchar(127);

  SELECT domain INTO old_subscriber_domain FROM voip_domains where id = OLD.domain_id;
  SELECT domain INTO new_subscriber_domain FROM voip_domains where id = NEW.domain_id;

  UPDATE kamailio.subscriber SET username = NEW.username, domain = new_subscriber_domain,
                                uuid = NEW.uuid, timezone = NEW.timezone, password = NEW.password,
                                ha1 = MD5(CONCAT(NEW.username, ':', new_subscriber_domain, ':', NEW.password)),
                                ha1b = MD5(CONCAT(NEW.username, '@', new_subscriber_domain, ':', new_subscriber_domain, ':', NEW.password))
                          WHERE username = OLD.username
                            AND domain = old_subscriber_domain;
  END;
|

CREATE TRIGGER voip_sub_drepl_trig BEFORE DELETE ON voip_subscribers
  FOR EACH ROW BEGIN
  DECLARE subscriber_domain varchar(127);
  DECLARE os_subscriber_id int(10) UNSIGNED;

  SELECT domain INTO subscriber_domain FROM voip_domains where id = OLD.domain_id;
  SELECT id INTO os_subscriber_id FROM kamailio.subscriber
   WHERE username = OLD.username AND domain = subscriber_domain;

  DELETE FROM kamailio.subscriber WHERE username = OLD.username
                                   AND domain = subscriber_domain;

  -- should be implemented via a provisioning.voicemail_users table
  -- and a foreign key to voip_subscribers
  DELETE FROM kamailio.voicemail_users WHERE customer_id = OLD.uuid;

  -- work around MySQL bug. the cascaded delete should trigger our
  -- delete actions on the provisioning tables, but doesn't
  DELETE FROM kamailio.usr_preferences WHERE username = OLD.username
                                        AND domain = subscriber_domain;
  DELETE FROM kamailio.dbaliases WHERE username = OLD.username
                                  AND domain = subscriber_domain;
  DELETE FROM kamailio.speed_dial WHERE username = OLD.username
                                  AND domain = subscriber_domain;
  DELETE FROM kamailio.fax_preferences WHERE subscriber_id = os_subscriber_id;
  DELETE FROM kamailio.fax_destinations WHERE subscriber_id = os_subscriber_id;
  END;
|

CREATE TRIGGER voip_dba_crepl_trig AFTER INSERT ON voip_dbaliases
  FOR EACH ROW BEGIN
  DECLARE dbalias_domain varchar(127);
  DECLARE target_username varchar(127);
  DECLARE target_domain varchar(127);

  SELECT domain INTO dbalias_domain FROM voip_domains where id = NEW.domain_id;
  SELECT a.username, b.domain INTO target_username, target_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = NEW.subscriber_id
                               AND b.id = a.domain_id;

  INSERT INTO kamailio.dbaliases (alias_username, alias_domain, username, domain)
                          VALUES(NEW.username, dbalias_domain, target_username, target_domain);
  END;
|

CREATE TRIGGER voip_dba_urepl_trig AFTER UPDATE ON voip_dbaliases
  FOR EACH ROW BEGIN
  DECLARE old_dbalias_domain varchar(127);
  DECLARE new_dbalias_domain varchar(127);
  DECLARE target_username varchar(127);
  DECLARE target_domain varchar(127);

  SELECT domain INTO old_dbalias_domain FROM voip_domains where id = OLD.domain_id;
  SELECT domain INTO new_dbalias_domain FROM voip_domains where id = NEW.domain_id;
  SELECT a.username, b.domain INTO target_username, target_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = NEW.subscriber_id
                               AND b.id = a.domain_id;

  UPDATE kamailio.dbaliases SET alias_username = NEW.username, alias_domain = new_dbalias_domain,
                               username = target_username, domain = target_domain
                         WHERE alias_username = OLD.username
                           AND alias_domain = old_dbalias_domain;
  END;
|

CREATE TRIGGER voip_dba_drepl_trig BEFORE DELETE ON voip_dbaliases
  FOR EACH ROW BEGIN
  DECLARE dbalias_domain varchar(127);

  SELECT domain INTO dbalias_domain FROM voip_domains where id = OLD.domain_id;

  DELETE FROM kamailio.dbaliases WHERE alias_username = OLD.username
                                  AND alias_domain = dbalias_domain;
  END;
|

CREATE TRIGGER voip_prul_crepl_trig AFTER INSERT ON voip_peer_rules
  FOR EACH ROW BEGIN
  DECLARE prio int(11) unsigned;

  SELECT priority INTO prio FROM voip_peer_groups
      WHERE id = NEW.group_id;

  INSERT INTO kamailio.lcr (lcr_id, prefix, from_uri, grp_id, priority)
      VALUES(1, NEW.callee_prefix, IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), ''), NEW.group_id, prio);

  END;
|

CREATE TRIGGER voip_prul_urepl_trig AFTER UPDATE ON voip_peer_rules
  FOR EACH ROW BEGIN
  DECLARE prio int(11) unsigned;
  
  SELECT priority INTO prio FROM voip_peer_groups
      WHERE id = NEW.group_id;

  UPDATE kamailio.lcr SET prefix = NEW.callee_prefix, 
      from_uri = IF(LENGTH(NEW.caller_prefix), CONCAT('^', NEW.caller_prefix), '')
      WHERE grp_id = OLD.group_id 
      AND prefix = OLD.callee_prefix
      AND from_uri = IF(LENGTH(OLD.caller_prefix), CONCAT('^', OLD.caller_prefix), '')
      AND priority = prio;

  END;
|

CREATE TRIGGER voip_pgrp_urepl_trig AFTER UPDATE ON voip_peer_groups
  FOR EACH ROW BEGIN

  UPDATE kamailio.lcr SET priority = NEW.priority
      WHERE grp_id = NEW.id;

  END;
|

CREATE TRIGGER voip_prul_drepl_trig AFTER DELETE ON voip_peer_rules
  FOR EACH ROW BEGIN
  DECLARE prio int(11) unsigned;
  
  SELECT priority INTO prio FROM voip_peer_groups
      WHERE id = OLD.group_id;

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

  END;
|

CREATE TRIGGER voip_pgrp_drepl_trig AFTER DELETE ON voip_peer_groups
  FOR EACH ROW BEGIN

  DELETE FROM kamailio.lcr WHERE grp_id = OLD.id;
  DELETE FROM kamailio.gw WHERE grp_id = OLD.id;

  END;
|

CREATE TRIGGER voip_phost_crepl_trig AFTER INSERT ON voip_peer_hosts
  FOR EACH ROW BEGIN

  INSERT INTO kamailio.gw (lcr_id, gw_name, grp_id, ip_addr, port, weight,
      uri_scheme, transport, strip, tag, flags, defunct) VALUES
      (1, NEW.name, NEW.group_id, NEW.ip, NEW.port, NEW.weight,
       1, 1, 0, NULL, NEW.id, NULL);

  END;
|

CREATE TRIGGER voip_phost_urepl_trig AFTER UPDATE ON voip_peer_hosts
  FOR EACH ROW BEGIN
  
  UPDATE kamailio.gw SET gw_name = NEW.name, 
      ip_addr = NEW.ip, port = NEW.port, weight = NEW.weight, tag = NULL
      WHERE grp_id = OLD.group_id AND gw_name = OLD.name AND
      ip_addr = OLD.ip AND port = OLD.port AND weight = OLD.weight;

  END;
|

CREATE TRIGGER voip_phost_drepl_trig AFTER DELETE ON voip_peer_hosts
  FOR EACH ROW BEGIN
  
  DELETE FROM kamailio.gw 
      WHERE grp_id = OLD.group_id AND gw_name = OLD.name AND 
      ip_addr = OLD.ip AND port = OLD.port AND weight = OLD.weight;
  DELETE FROM kamailio.dialplan
      WHERE dpid IN (OLD.dp_caller_in_id, OLD.dp_callee_in_id, 
      OLD.dp_caller_out_id, OLD.dp_callee_out_id);

  END;
|


CREATE TRIGGER voip_domrw_crepl_trig AFTER INSERT ON voip_domain_rewrites
  FOR EACH ROW BEGIN
  
  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  
  IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;

  INSERT INTO kamailio.dialplan (dpid, pr, match_op, match_exp, 
      match_len, subst_exp, repl_exp, attrs)
      VALUES(dp_id, NEW.priority, 1, NEW.match_pattern, 0, NEW.match_pattern, NEW.replace_pattern, '');

  END;
|

CREATE TRIGGER voip_domrw_urepl_trig AFTER UPDATE ON voip_domain_rewrites
  FOR EACH ROW BEGIN

  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_caller_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = NEW.domain_id AND vp.attribute = 'dp_dom_callee_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  
  IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;

  UPDATE kamailio.dialplan SET match_exp = NEW.match_pattern, 
      subst_exp = NEW.match_pattern, repl_exp = NEW.replace_pattern, pr = NEW.priority
      WHERE dpid = dp_id AND match_exp = OLD.match_pattern AND pr = OLD.priority
      AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;

  END;
|

CREATE TRIGGER voip_domrw_drepl_trig AFTER DELETE ON voip_domain_rewrites
  FOR EACH ROW BEGIN

  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_caller_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_callee_in' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_caller_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp, 
      voip_domains vd WHERE vd.id = OLD.domain_id AND vp.attribute = 'dp_dom_callee_out' 
      AND vdp.attribute_id = vp.id AND vd.id = vdp.domain_id;
  
  IF OLD.direction = 'in' AND OLD.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF OLD.direction = 'in' AND OLD.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF OLD.direction = 'out' AND OLD.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF OLD.direction = 'out' AND OLD.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;

  DELETE FROM kamailio.dialplan
      WHERE dpid = dp_id AND match_exp = OLD.match_pattern 
      AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;

  END;
|

CREATE TRIGGER voip_peerrw_crepl_trig AFTER INSERT ON voip_peer_rewrites
  FOR EACH ROW BEGIN
  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id 
      INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
      FROM voip_peer_hosts WHERE id = NEW.peer_id;

  IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;

  INSERT INTO kamailio.dialplan (dpid, pr, match_op, match_exp, 
      match_len, subst_exp, repl_exp, attrs)
      VALUES(dp_id, NEW.priority, 1, NEW.match_pattern, 0, NEW.match_pattern, NEW.replace_pattern, '');

  END;
|

CREATE TRIGGER voip_peerrw_urepl_trig AFTER UPDATE ON voip_peer_rewrites
  FOR EACH ROW BEGIN
  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id 
      INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
      FROM voip_peer_hosts WHERE id = NEW.peer_id;

  IF NEW.direction = 'in' AND NEW.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF NEW.direction = 'in' AND NEW.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF NEW.direction = 'out' AND NEW.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;
  
  UPDATE kamailio.dialplan SET match_exp = NEW.match_pattern, 
      subst_exp = NEW.match_pattern, repl_exp = NEW.replace_pattern,
      pr = NEW.priority
      WHERE dpid = dp_id AND match_exp = OLD.match_pattern AND pr = OLD.priority
      AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;

  END;
|

CREATE TRIGGER voip_peerrw_drepl_trig AFTER DELETE ON voip_peer_rewrites
  FOR EACH ROW BEGIN
  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;
  DECLARE dp_id int(11) unsigned default 0;

  SELECT dp_caller_in_id, dp_callee_in_id, dp_caller_out_id, dp_callee_out_id 
      INTO caller_in_id, callee_in_id, caller_out_id, callee_out_id
      FROM voip_peer_hosts WHERE id = OLD.peer_id;

  IF OLD.direction = 'in' AND OLD.field = 'caller' THEN
      SET dp_id = caller_in_id;
  ELSEIF OLD.direction = 'in' AND OLD.field = 'callee' THEN
      SET dp_id = callee_in_id;
  ELSEIF OLD.direction = 'out' AND OLD.field = 'caller' THEN
      SET dp_id = caller_out_id;
  ELSEIF OLD.direction = 'out' AND OLD.field = 'callee' THEN
      SET dp_id = callee_out_id;
  END IF;
  
  DELETE FROM kamailio.dialplan
      WHERE dpid = dp_id AND match_exp = OLD.match_pattern 
      AND subst_exp = OLD.match_pattern AND repl_exp = OLD.replace_pattern;

  END;
|

CREATE TRIGGER voip_usrpref_crepl_trig AFTER INSERT ON voip_usr_preferences
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE subscriber_uuid char(36);
  DECLARE attribute_name varchar(31);
  DECLARE attribute_type tinyint(3);

  SELECT a.username, b.domain, a.uuid INTO subscriber_username, subscriber_domain, subscriber_uuid
                                      FROM voip_subscribers a, voip_domains b
                                     WHERE a.id = NEW.subscriber_id
                                       AND a.domain_id = b.id;
  SELECT attribute, type INTO attribute_name, attribute_type
                         FROM voip_preferences
                        WHERE id = NEW.attribute_id;

  INSERT INTO kamailio.usr_preferences (uuid, username, domain, attribute, type, value)
                                VALUES(subscriber_uuid, subscriber_username, subscriber_domain,
                                       attribute_name, attribute_type, NEW.value);
  END;
|

CREATE TRIGGER voip_usrpref_urepl_trig AFTER UPDATE ON voip_usr_preferences
  FOR EACH ROW BEGIN
  DECLARE old_subscriber_username varchar(127);
  DECLARE new_subscriber_username varchar(127);
  DECLARE old_subscriber_domain varchar(127);
  DECLARE new_subscriber_domain varchar(127);
  DECLARE old_attribute_name varchar(31);
  DECLARE new_attribute_name varchar(31);

  SELECT a.username, b.domain INTO old_subscriber_username, old_subscriber_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = OLD.subscriber_id
                               AND a.domain_id = b.id;
  SELECT a.username, b.domain INTO new_subscriber_username, new_subscriber_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = NEW.subscriber_id
                               AND a.domain_id = b.id;
  SELECT attribute INTO old_attribute_name
                   FROM voip_preferences
                  WHERE id = OLD.attribute_id;
  SELECT attribute INTO new_attribute_name
                   FROM voip_preferences
                  WHERE id = NEW.attribute_id;

  UPDATE kamailio.usr_preferences SET username = new_subscriber_username, domain = new_subscriber_domain,
                                     attribute = new_attribute_name, value = NEW.value
                               WHERE username = old_subscriber_username
                                 AND domain = old_subscriber_domain
                                 AND attribute = old_attribute_name
                                 AND value = OLD.value;
  END;
|

CREATE TRIGGER voip_usrpref_drepl_trig BEFORE DELETE ON voip_usr_preferences
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE attribute_name varchar(31);

  SELECT a.username, b.domain INTO subscriber_username, subscriber_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = OLD.subscriber_id
                               AND a.domain_id = b.id;
  SELECT attribute INTO attribute_name
                   FROM voip_preferences
                  WHERE id = OLD.attribute_id;

  DELETE FROM kamailio.usr_preferences WHERE username = subscriber_username
                                        AND domain = subscriber_domain
                                        AND attribute = attribute_name
                                        AND value = OLD.value;
  END;
|

CREATE TRIGGER voip_dom_crepl_trig AFTER INSERT ON voip_domains
  FOR EACH ROW BEGIN

  IF NEW.local IS TRUE THEN
    INSERT INTO kamailio.domain (domain) VALUES(NEW.domain);
  END IF;

  END;
|

CREATE TRIGGER voip_dom_urepl_trig AFTER UPDATE ON voip_domains
  FOR EACH ROW BEGIN

  IF NEW.local IS TRUE AND OLD.local IS FALSE THEN
    INSERT INTO kamailio.domain (domain) VALUES(NEW.domain);
  ELSEIF NEW.local IS FALSE AND OLD.local IS TRUE THEN
    DELETE FROM kamailio.domain WHERE domain = OLD.domain;
  END IF;

  END;
|

CREATE TRIGGER voip_dom_drepl_trig BEFORE DELETE ON voip_domains
  FOR EACH ROW BEGIN

  DECLARE caller_in_id int(11) unsigned;
  DECLARE callee_in_id int(11) unsigned;
  DECLARE caller_out_id int(11) unsigned;
  DECLARE callee_out_id int(11) unsigned;

  SELECT vdp.value INTO caller_in_id FROM voip_dom_preferences vdp, voip_preferences vp
      WHERE vp.attribute = 'dp_dom_caller_in' AND vdp.attribute_id = vp.id AND vdp.domain_id = OLD.id;
  SELECT vdp.value INTO callee_in_id FROM voip_dom_preferences vdp, voip_preferences vp 
      WHERE vp.attribute = 'dp_dom_callee_in' AND vdp.attribute_id = vp.id AND vdp.domain_id = OLD.id;
  SELECT vdp.value INTO caller_out_id FROM voip_dom_preferences vdp, voip_preferences vp 
      WHERE vp.attribute = 'dp_dom_caller_out' AND vdp.attribute_id = vp.id AND vdp.domain_id = OLD.id;
  SELECT vdp.value INTO callee_out_id FROM voip_dom_preferences vdp, voip_preferences vp 
      WHERE vp.attribute = 'dp_dom_callee_out' AND vdp.attribute_id = vp.id AND vdp.domain_id = OLD.id;

  IF OLD.local IS TRUE THEN
    DELETE FROM kamailio.domain WHERE domain = OLD.domain;
  END IF;
  
  DELETE FROM kamailio.dialplan WHERE dpid IN 
      (caller_in_id, callee_in_id, caller_out_id, callee_out_id);

  -- work around MySQL bug. the cascaded delete should trigger our
  -- voip_dom_preferences delete action, but doesn't
  DELETE FROM kamailio.dom_preferences WHERE domain = OLD.domain;
  -- this will trigger the delete action for each subscriber
  DELETE FROM provisioning.voip_subscribers WHERE domain_id = OLD.id;
  

  END;
|

CREATE TRIGGER voip_dompref_crepl_trig AFTER INSERT ON voip_dom_preferences
  FOR EACH ROW BEGIN
  DECLARE domain_name varchar(127);
  DECLARE attribute_name varchar(31);
  DECLARE attribute_type tinyint(3);

  SELECT domain INTO domain_name
                FROM voip_domains
               WHERE id = NEW.domain_id;
  SELECT attribute, type INTO attribute_name, attribute_type
                         FROM voip_preferences
                        WHERE id = NEW.attribute_id;

  INSERT INTO kamailio.dom_preferences (domain, attribute, type, value)
                                 VALUES(domain_name, attribute_name, attribute_type, NEW.value);
  END;
|

CREATE TRIGGER voip_dompref_urepl_trig AFTER UPDATE ON voip_dom_preferences
  FOR EACH ROW BEGIN
  DECLARE old_domain_name varchar(127);
  DECLARE new_domain_name varchar(127);
  DECLARE old_attribute_name varchar(31);
  DECLARE new_attribute_name varchar(31);

  SELECT domain INTO old_domain_name
                FROM voip_domains
               WHERE id = OLD.domain_id;
  SELECT domain INTO new_domain_name
                FROM voip_domains
               WHERE id = NEW.domain_id;
  SELECT attribute INTO old_attribute_name
                   FROM voip_preferences
                  WHERE id = OLD.attribute_id;
  SELECT attribute INTO new_attribute_name
                   FROM voip_preferences
                  WHERE id = NEW.attribute_id;

  UPDATE kamailio.dom_preferences SET domain = new_domain_name,
                                      attribute = new_attribute_name,
                                      value = NEW.value
                                WHERE domain = old_domain_name
                                  AND attribute = old_attribute_name
                                  AND value = OLD.value;
  END;
|

CREATE TRIGGER voip_dompref_drepl_trig BEFORE DELETE ON voip_dom_preferences
  FOR EACH ROW BEGIN
  DECLARE domain_name varchar(127);
  DECLARE attribute_name varchar(31);

  SELECT domain INTO domain_name
                FROM voip_domains
               WHERE id = OLD.domain_id;
  SELECT attribute INTO attribute_name
                   FROM voip_preferences
                  WHERE id = OLD.attribute_id;

  DELETE FROM kamailio.dom_preferences WHERE domain = domain_name
                                         AND attribute = attribute_name
                                         AND value = OLD.value;
  END;
|

CREATE TRIGGER voip_sd_crepl_trig AFTER INSERT ON voip_speed_dial
  FOR EACH ROW BEGIN
  DECLARE target_username varchar(64);
  DECLARE target_domain varchar(64);

  SELECT a.username, b.domain INTO target_username, target_domain
                              FROM voip_subscribers a, voip_domains b
                              WHERE a.id = NEW.subscriber_id
                              AND b.id = a.domain_id;

  INSERT INTO kamailio.speed_dial (username, domain, sd_username, sd_domain,
                                  new_uri, fname, lname, description)
                          VALUES(target_username, target_domain,
                                 NEW.slot, target_domain,
                                 NEW.destination, '', '', '');
  END;
|

CREATE TRIGGER voip_sd_urepl_trig AFTER UPDATE ON voip_speed_dial
  FOR EACH ROW BEGIN
  DECLARE old_username varchar(127);
  DECLARE old_domain varchar(127);
  DECLARE new_username varchar(127);
  DECLARE new_domain varchar(127);

  SELECT a.username, b.domain INTO old_username, old_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = OLD.subscriber_id
                               AND b.id = a.domain_id;
  SELECT a.username, b.domain INTO new_username, new_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = NEW.subscriber_id
                               AND b.id = a.domain_id;

  UPDATE kamailio.speed_dial SET username = new_username, domain = new_domain,
                               sd_username = NEW.slot, sd_domain = new_domain,
                               new_uri = NEW.destination
                           WHERE username = old_username
                           AND domain = old_domain
                           AND sd_username = OLD.slot;
  END;
|

CREATE TRIGGER voip_sd_drepl_trig BEFORE DELETE ON voip_speed_dial
  FOR EACH ROW BEGIN
  DECLARE old_username varchar(127);
  DECLARE old_domain varchar(127);

  SELECT a.username, b.domain INTO old_username, old_domain
                              FROM voip_subscribers a, voip_domains b
                             WHERE a.id = OLD.subscriber_id
                               AND b.id = a.domain_id;

  DELETE FROM kamailio.speed_dial WHERE username = old_username
                                  AND domain = old_domain
                                  AND sd_username = OLD.slot;
  END;
|

CREATE TRIGGER voip_faxp_crepl_trig AFTER INSERT ON voip_fax_preferences
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO subscriber_username, subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = NEW.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO os_subscriber_id FROM kamailio.subscriber
   WHERE username = subscriber_username AND domain = subscriber_domain;

  INSERT INTO kamailio.fax_preferences
             (subscriber_id, password, name, active, send_status, send_copy)
       VALUES(os_subscriber_id, NEW.password, NEW.name, IF(NEW.active, 'true', 'false'),
              IF(NEW.send_status, 'true', 'false'), IF(NEW.send_copy, 'true', 'false'));

  END;
|

CREATE TRIGGER voip_faxp_urepl_trig AFTER UPDATE ON voip_fax_preferences
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE os_subscriber_id int(10) UNSIGNED;
  DECLARE old_subscriber_username varchar(127);
  DECLARE old_subscriber_domain varchar(127);
  DECLARE old_os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO subscriber_username, subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = NEW.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO os_subscriber_id FROM kamailio.subscriber
   WHERE username = subscriber_username AND domain = subscriber_domain;

  SELECT a.username, b.domain INTO old_subscriber_username, old_subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = OLD.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO old_os_subscriber_id FROM kamailio.subscriber
   WHERE username = old_subscriber_username AND domain = old_subscriber_domain;

  UPDATE kamailio.fax_preferences SET subscriber_id = os_subscriber_id, password = NEW.password,
                                      name = NEW.name, active = IF(NEW.active, 'true', 'false'),
                                      send_status = IF(NEW.send_status, 'true', 'false'),
                                      send_copy = IF(NEW.send_copy, 'true', 'false')
                                WHERE subscriber_id = old_os_subscriber_id;

  END;
|

CREATE TRIGGER voip_faxp_drepl_trig BEFORE DELETE ON voip_fax_preferences
  FOR EACH ROW BEGIN
  DECLARE old_subscriber_username varchar(127);
  DECLARE old_subscriber_domain varchar(127);
  DECLARE old_os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO old_subscriber_username, old_subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = OLD.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO old_os_subscriber_id FROM kamailio.subscriber
   WHERE username = old_subscriber_username AND domain = old_subscriber_domain;

  DELETE FROM kamailio.fax_preferences WHERE subscriber_id = old_os_subscriber_id;

  END;
|

CREATE TRIGGER voip_faxd_crepl_trig AFTER INSERT ON voip_fax_destinations
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO subscriber_username, subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = NEW.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO os_subscriber_id FROM kamailio.subscriber
   WHERE username = subscriber_username AND domain = subscriber_domain;

  INSERT INTO kamailio.fax_destinations (subscriber_id, destination, filetype,
                                        cc, incoming, outgoing, status)
                                 VALUES(os_subscriber_id, NEW.destination, NEW.filetype,
                                        IF(NEW.cc, 'true', 'false'), IF(NEW.incoming, 'true', 'false'),
                                        IF(NEW.outgoing, 'true', 'false'), IF(NEW.status, 'true', 'false'));

  END;
|

CREATE TRIGGER voip_faxd_urepl_trig AFTER UPDATE ON voip_fax_destinations
  FOR EACH ROW BEGIN
  DECLARE subscriber_username varchar(127);
  DECLARE subscriber_domain varchar(127);
  DECLARE os_subscriber_id int(10) UNSIGNED;
  DECLARE old_subscriber_username varchar(127);
  DECLARE old_subscriber_domain varchar(127);
  DECLARE old_os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO subscriber_username, subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = NEW.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO os_subscriber_id FROM kamailio.subscriber
   WHERE username = subscriber_username AND domain = subscriber_domain;

  SELECT a.username, b.domain INTO old_subscriber_username, old_subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = OLD.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO old_os_subscriber_id FROM kamailio.subscriber
   WHERE username = old_subscriber_username AND domain = old_subscriber_domain;

  UPDATE kamailio.fax_destinations SET subscriber_id = os_subscriber_id, destination = NEW.destination,
                                      filetype = NEW.filetype, cc = IF(NEW.cc, 'true', 'false'),
                                      incoming = IF(NEW.incoming, 'true', 'false'),
                                      outgoing = IF(NEW.outgoing, 'true', 'false'),
                                      status = IF(NEW.status, 'true', 'false')
                                WHERE subscriber_id = old_os_subscriber_id
                                  AND destination = OLD.destination;

  END;
|

CREATE TRIGGER voip_faxd_drepl_trig BEFORE DELETE ON voip_fax_destinations
  FOR EACH ROW BEGIN
  DECLARE old_subscriber_username varchar(127);
  DECLARE old_subscriber_domain varchar(127);
  DECLARE old_os_subscriber_id int(10) UNSIGNED;

  SELECT a.username, b.domain INTO old_subscriber_username, old_subscriber_domain
    FROM voip_subscribers a, voip_domains b
   WHERE a.id = OLD.subscriber_id
     AND b.id = a.domain_id;

  SELECT id INTO old_os_subscriber_id FROM kamailio.subscriber
   WHERE username = old_subscriber_username AND domain = old_subscriber_domain;

  DELETE FROM kamailio.fax_destinations WHERE subscriber_id = old_os_subscriber_id
                                         AND destination = OLD.destination;

  END;
|

CREATE TRIGGER voip_aig_crepl_trig AFTER INSERT ON voip_allowed_ip_groups
  FOR EACH ROW BEGIN

  INSERT INTO kamailio.address (id, grp, ip_addr, mask)
                         VALUES(NEW.id, NEW.group_id,
                                IF(LOCATE('/', NEW.ipnet), SUBSTRING_INDEX(NEW.ipnet, '/', 1), NEW.ipnet),
                                IF(LOCATE('/', NEW.ipnet), SUBSTRING_INDEX(NEW.ipnet, '/', -1), 32));

  END;
|

CREATE TRIGGER voip_aig_urepl_trig AFTER UPDATE ON voip_allowed_ip_groups
  FOR EACH ROW BEGIN

  UPDATE kamailio.address SET id = NEW.id, grp = NEW.group_id,
                              ip_addr = IF(LOCATE('/', NEW.ipnet), SUBSTRING_INDEX(NEW.ipnet, '/', 1), NEW.ipnet),
                              mask = IF(LOCATE('/', NEW.ipnet), SUBSTRING_INDEX(NEW.ipnet, '/', -1), 32)
                        WHERE id = OLD.id;

  END;
|

CREATE TRIGGER voip_aig_drepl_trig BEFORE DELETE ON voip_allowed_ip_groups
  FOR EACH ROW BEGIN

  DELETE FROM kamailio.address WHERE id = OLD.id;

  END;
|

DELIMITER ;