USE `porta-billing`;

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

DROP TABLE IF EXISTS `customer_confs`;
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,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
);

DROP TABLE IF EXISTS `astrad_confs`;
CREATE TABLE IF NOT EXISTS `astrad_confs` (
  `id` int(11) NOT NULL auto_increment,
  `host` varchar(15) NOT NULL,
  `port` int(11) DEFAULT 5060,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`host`),
  KEY `name_2` (`host`)
);


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 |

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 |

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 ;


USE `astrad`;
ALTER TABLE ip_customers ADD COLUMN port INT DEFAULT 5060;
ALTER TABLE vendors ADD COLUMN port INT DEFAULT 5060;

--
-- 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,"','",NEW.port,"','",cont,"','port');");
   SET REQ2=CONCAT("INSERT IGNORE INTO sipdevices(name,host,port,context,insecure) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"','",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,"', port='",NEW.port,"' WHERE name='",OLD.remote_ip,"';");
SET REQ2=CONCAT("UPDATE sipdevices SET name='",NEW.remote_ip,"', host='",NEW.remote_ip,"', context='",cont,"', port='",NEW.port,"' 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 |
DELIMITER ;


--
-- 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,"','",NEW.port,"');");
  INSERT INTO request (val) VALUES (REQ);
  SET REQ=CONCAT("INSERT IGNORE INTO sipdevices (name,host,port) VALUES ('",NEW.remote_ip,"','",NEW.remote_ip,"','",NEW.port,"');");
  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,"', port='",NEW.port,"' 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,"', port='",NEW.port,"' 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 |
DELIMITER ;