--------------------------------------------------------------------  
-- Accounts
--------------------------------------------------------------------
-- Procedures
--

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 ;

--
-- Triggers	 
--

Delimiter |

CREATE TRIGGER Trg_Insert_Accounts AFTER INSERT ON Accounts
  FOR EACH ROW BEGIN
  IF NEW.i_env=1 THEN
    IF (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 IF;
  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;
|

CREATE TRIGGER Trg_Update_Accounts AFTER UPDATE ON Accounts
FOR EACH ROW BEGIN
IF NEW.id REGEXP '^([0-9]{1,3}[.]){3}[0-9]{1,3}$' THEN 
  IF NEW.i_env=1 THEN
    IF OLD.i_env=1 THEN
	  IF NEW.id <> OLD.id 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));
	  END IF;
    ELSE
	  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));
    END IF;
  ELSEIF OLD.i_env=1 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));
  END IF;
ELSE
  IF NEW.i_env=1 THEN
    IF OLD.i_env=1 THEN
	  IF NEW.h323_password <> OLD.h323_password THEN
  	    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));
	  ELSEIF NEW.id <> OLD.id THEN
	    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 account',OLD.id,' to ',NEW.id));
	  END IF;
    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;
  ELSEIF OLD.i_env=1 THEN
    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 IF;
END;
|

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

Delimiter ;

--------------------------------------------------------------------  
-- Nodes
--------------------------------------------------------------------  
-- Triggers	 
--

Delimiter |

CREATE TRIGGER Trg_Insert_Nodes AFTER INSERT ON Nodes
  FOR EACH ROW BEGIN
  IF NEW.i_node_type=11 THEN
    IF NEW.i_env=1 THEN
      IF 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 IF;
  END IF;
  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;
|

CREATE TRIGGER Trg_Update_Nodes AFTER UPDATE ON Nodes
FOR EACH ROW BEGIN
  IF NEW.i_env=1 THEN
    IF NEW.name NOT LIKE '%[Deleted]%' THEN 
	  IF NULLIF(NEW.ip,OLD.ip) THEN
	    IF OLD.name NOT LIKE '%[Deleted]%' 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));
		ELSE
		  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,' old name should containte Deleted -> ',OLD.name));
		END IF;
	  ELSE
	    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,' new in our env.[1] old -> ',OLD.i_env,' or name changed from ',OLD.name,' to ',NEW.name));
	  END IF;
	ELSEIF OLD.ip NOT LIKE '%[Deleted]%' 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,' name -> ',New.name));
	END IF;
  ELSEIF OLD.i_env=1 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,' drop from our env.[1] new -> ',NEW.i_env));
  END IF;
  END;
|

Delimiter ;

--------------------------------------------------------------------  
-- Connections
--------------------------------------------------------------------  
-- Triggers	
-- 

Delimiter |

CREATE TRIGGER Trg_Insert_Connections AFTER INSERT ON Connections
FOR EACH ROW BEGIN
  IF NEW.i_env=1 THEN
    IF NEW.remote_ip NOT IN  ('SIP-UA','') 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 IF;
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;
|

CREATE TRIGGER Trg_Update_Connections AFTER UPDATE ON Connections
FOR EACH ROW BEGIN
  IF NEW.i_env=1 THEN
    IF NEW.remote_ip NOT IN  ('SIP-UA','') THEN 
	  IF NULLIF(NEW.remote_ip,OLD.remote_ip) THEN
	    IF OLD.remote_ip NOT IN  ('SIP-UA','') 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));
		ELSE
		  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));
		END IF;
	  ELSE
	    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,' new in our env.[1] old -> ',OLD.i_env));
	  END IF;
	ELSEIF OLD.remote_ip NOT IN  ('SIP-UA','') 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));
	END IF;
  ELSEIF OLD.i_env=1 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,' drop from our env.[1] new -> ',NEW.i_env));
  END IF;
  END;
|
Delimiter ;
