--
-- Table structure for table `ip_customers`
--

CREATE TABLE `ip_customers` (
  `id` int(11) NOT NULL auto_increment,
  `remote_ip` varchar(15) NOT NULL default '',
  `is_node` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `remote_ip` (`remote_ip`)
);

--
-- Table structure for table `realm`
--

CREATE TABLE `realm` (
  `id` int(11) NOT NULL auto_increment,
  `val` varchar(80) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `val` (`val`)
);

--
-- Table structure for table `triggers_log`
--

CREATE TABLE `triggers_log` (
  `id` int(11) NOT NULL auto_increment,
  `table_name` varchar(30) default NULL,
  `event` varchar(30) default NULL,
  `log_time` datetime default NULL,
  `comment` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
);

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(30) NOT NULL default '',
  `md5secret` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `username` (`username`)
);

--
-- Table structure for table `vendors`
--

CREATE TABLE `vendors` (
  `id` int(11) NOT NULL auto_increment,
  `remote_ip` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `remote_ip` (`remote_ip`)
);
--
-- Procedures
--

DELIMITER |

CREATE PROCEDURE SET_REALM (IN val varchar(80),IN event varchar(30))
BEGIN
  DECLARE account VARCHAR(30);
  DECLARE secret char(80);
  DECLARE md5_secret char(32);
  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;
  DECLARE friends_cur CURSOR FOR SELECT acc.id, acc.h323_password FROM `porta-billing`.Accounts acc,`porta-billing`.Customers cus WHERE acc.i_customer=cus.i_customer AND cus.i_rep IN (3,5,9,12) AND acc.i_env=1 AND acc.id NOT regexp '^([0-9]{1,3}[.]){3}[0-9]{1,3}$';
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;
  OPEN friends_cur;
  SELECT FOUND_ROWS() INTO num_rows;
  the_loop: LOOP
    FETCH friends_cur INTO account, secret;
    IF no_more_rows THEN
        CLOSE friends_cur;
        LEAVE the_loop;
    END IF;
	IF secret IS NOT NULL THEN
      UPDATE users SET md5secret=md5(CONCAT(account,':',val,':',secret)) WHERE username=account;
	ELSE
	  UPDATE users SET md5secret=md5(CONCAT(account,':',val,':')) WHERE username=account;
	END IF;
	END LOOP the_loop;
INSERT INTO triggers_log (table_name,event,log_time,comment) VALUES ('realm',event,NOW(),CONCAT('update realm of all users :',num_rows));
END |

DELIMITER ;

--
-- Triggers
--

DELIMITER |

CREATE TRIGGER Trg_Insert_realm AFTER INSERT ON realm
FOR EACH ROW BEGIN
    CALL SET_REALM(NEW.val,'INSERT');
END;
|

CREATE TRIGGER Trg_Update_realm AFTER UPDATE ON realm
FOR EACH ROW BEGIN
  IF NEW.id=(SELECT id FROM realm ORDER BY id DESC LIMIT 1) THEN
    IF NEW.val <> OLD.val THEN
      CALL SET_REALM(NEW.val,'UPDATE');
	END IF;
  END IF;
END;
|

CREATE TRIGGER Trg_Delete_realm AFTER DELETE ON realm
FOR EACH ROW BEGIN
DECLARE rm VARCHAR(30);
SET rm=(SELECT val FROM realm ORDER BY id DESC LIMIT 1);
  IF rm IS NOT NULL THEN 
    IF rm <> OLD.val THEN
      CALL SET_REALM(rm,'DELETE');
	END IF;
  END IF;
END;
|

DELIMITER ;
