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

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_ip_customers`|
CREATE TRIGGER `Trg_Insert_ip_customers` AFTER INSERT ON `ip_customers` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
DECLARE REQ text DEFAULT '';
DECLARE REQ2 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 sippeers (name,host,port,context,insecure) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"',5060,'",cont,"','port');");
    SET REQ2=CONCAT("INSERT IGNORE INTO sipdevices(name,host,port,context,insecure) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"',5060,'",cont,"','port');");
    INSERT INTO request (val) VALUES (REQ);
    INSERT INTO request (val) VALUES (REQ2);
    INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Add to sippeers and sipdevices ',NEW.remote_ip,' context=',cont));
ELSE
    SET REQ=CONCAT("UPDATE sippeers SET context='",cont,"', insecure='port' WHERE name='",NEW.remote_ip,"' AND context='forbidden';");
    SET REQ2=CONCAT("UPDATE sipdevices SET context='",cont,"', insecure='port' WHERE name='",NEW.remote_ip,"' AND context='forbidden';");
    INSERT INTO request (val) VALUES (REQ);
    INSERT INTO request (val) VALUES (REQ2);
    INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Set context (',cont,') to ip_customer in sippeers and sipdevices ',NEW.remote_ip));
END IF;
SET DN=(SELECT get_deny());
SET REQ=CONCAT("UPDATE sipusers SET deny='",DN,"';");
SET REQ2=CONCAT("UPDATE config SET value='",DN,"' WHERE id = 1;");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO request (val) VALUES (REQ2);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('ip_customers','INSERT',NOW(),CONCAT('Update deny in sipusers and config ',NEW.remote_ip));
END |

--

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

--

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

DELIMITER ;

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

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_vendors`|
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 sippeers (name,host,port) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"',5060);");
  INSERT INTO request (val) VALUES (REQ);
  SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,host,port) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"',5060);");
  INSERT INTO request (val) VALUES (REQ);
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Add vendor to sippeers and sipdevices ',NEW.remote_ip));
ELSE
  INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('vendors','INSERT',NOW(),CONCAT('Vendor already exist on sippeers/sipdevices (ip existe on ip_customers)',NEW.remote_ip));
END IF;
END |

--

DROP TRIGGER IF EXISTS `Trg_Update_vendors`|
CREATE TRIGGER `Trg_Update_vendors` AFTER UPDATE ON `vendors` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("UPDATE sippeers SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"' WHERE name='",OLD.remote_ip,"' AND context='forbidden';");
INSERT INTO request (val) VALUES (REQ);
SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"' 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 sippeers and sipdevices remote_ip=',NEW.remote_ip,' old remote_ip=',OLD.remote_ip));
END |

--

DROP TRIGGER IF EXISTS `Trg_Delete_vendors`|
CREATE TRIGGER `Trg_Delete_vendors` AFTER DELETE ON `vendors` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("DELETE FROM sippeers WHERE name='",OLD.remote_ip,"' AND context='forbidden';");
INSERT INTO request (val) VALUES (REQ);
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 sippeers and sipdevices ',OLD.remote_ip));
END |

DELIMITER ;

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

DELIMITER |
DROP TRIGGER IF EXISTS `Trg_Insert_users`|
CREATE TRIGGER `Trg_Insert_users` AFTER INSERT ON `users` FOR EACH ROW BEGIN
DECLARE DN text DEFAULT '';
DECLARE REQ text DEFAULT '';
SET DN=(SELECT get_deny());
SET REQ=CONCAT("INSERT IGNORE INTO sipusers (name,md5secret,deny) VALUES ('",NEW.username,"','",NEW.md5secret,"','",DN,"');");
INSERT INTO request (val) VALUES (REQ);
SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,md5secret,context,host) VALUES ('",NEW.username,"','",NEW.md5secret,"','fromaccount','dynamic');");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sipusers and sipdevices ',NEW.username));
SET REQ=CONCAT("INSERT IGNORE INTO sippeers (name,md5secret) VALUES ('",NEW.username,"','",NEW.md5secret,"');");
INSERT INTO request (val) VALUES (REQ);
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('users','INSERT',NOW(),CONCAT('Add to sippeers ',NEW.username));
END |

--

DROP TRIGGER IF EXISTS `Trg_Update_users`|
CREATE TRIGGER `Trg_Update_users` AFTER UPDATE ON `users` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("UPDATE sipusers SET name='",NEW.username,"', md5secret='",NEW.md5secret,"' WHERE name='",OLD.username,"';");
INSERT INTO request (val) VALUES (REQ);
SET REQ=CONCAT("UPDATE sipdevices SET name='",NEW.username,"', md5secret='",NEW.md5secret,"' 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 sipusers and sipdevices new username=',NEW.username,' old username=',OLD.username));
SET REQ=CONCAT("UPDATE sippeers SET name='",NEW.username,"', md5secret='",NEW.md5secret,"' 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 sippeers user=',NEW.username,' old username=',OLD.username));
END |

--

DROP TRIGGER IF EXISTS `Trg_Delete_users`|
CREATE TRIGGER `Trg_Delete_users` AFTER DELETE ON `users` FOR EACH ROW BEGIN
DECLARE REQ text DEFAULT '';
SET REQ=CONCAT("DELETE FROM sipusers WHERE name='",OLD.username,"';");
INSERT INTO request (val) VALUES (REQ);
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 sipusers ',OLD.username));
SET REQ=CONCAT("DELETE FROM sippeers 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 sippeers ',OLD.username));
END |

DELIMITER ;