SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


-- ----------------------------
-- Table structure for numbers
-- ----------------------------
CREATE TABLE `numbers`  (
  `n` int NOT NULL,
  PRIMARY KEY (`n`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of numbers
-- ----------------------------
INSERT INTO `numbers` VALUES (1);
INSERT INTO `numbers` VALUES (2);
INSERT INTO `numbers` VALUES (3);
INSERT INTO `numbers` VALUES (4);
INSERT INTO `numbers` VALUES (5);
INSERT INTO `numbers` VALUES (6);
INSERT INTO `numbers` VALUES (7);
INSERT INTO `numbers` VALUES (8);
INSERT INTO `numbers` VALUES (9);
INSERT INTO `numbers` VALUES (10);

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Function structure for ip_cidr_to_mask
-- ----------------------------
DROP FUNCTION IF EXISTS `ip_cidr_to_mask`;
delimiter ;;
CREATE FUNCTION `ip_cidr_to_mask`(cidr INT(2))
 RETURNS char(15) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
  DETERMINISTIC
RETURN INET_NTOA(CONV(CONCAT(REPEAT(1,cidr),REPEAT(0,32-cidr)),2,10))
;;
delimiter ;

-- ----------------------------
-- Function structure for ip_in_subnet_mask
-- ----------------------------
DROP FUNCTION IF EXISTS `ip_in_subnet_mask`;
delimiter ;;
CREATE FUNCTION `ip_in_subnet_mask`(ip VARCHAR(20), subnet VARCHAR(20), netmask VARCHAR(20))
 RETURNS tinyint(1)
  DETERMINISTIC
BEGIN

    RETURN (INET_ATON(ip) & INET_ATON(netmask)) = INET_ATON(subnet);

END
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table history
-- ----------------------------
DROP TRIGGER IF EXISTS `UpdateHostLocation`;
delimiter ;;
CREATE TRIGGER `UpdateHostLocation` AFTER INSERT ON `history` FOR EACH ROW UpdateHostLocaton:BEGIN

	DECLARE varsgGroupID INT ( 10 );
	
	DECLARE	varHostID varchar ( 256 );

	IF trim(substring_index( substring_index( NEW.hText, 'Host ID:', - 1 ), 'NAME:', 1 )) > 0 THEN 

			SELECT
				trim(substring_index( substring_index( NEW.hText, 'Host ID:', - 1 ), 'NAME:', 1 )) INTO varHostID;

	ELSE 

			LEAVE UpdateHostLocaton;

	END IF;

			SELECT
				sgGroupID INTO varsgGroupID 
			FROM
				(
				SELECT
					sgGroupID,
					ip_in_subnet_mask (
						NEW.hIP,
						SUBSTRING_INDEX( subnet, '/', 1 ),
						ip_cidr_to_mask (
						SUBSTRING_INDEX( subnet, '/',- 1 ))) AS ipInSubnet 
				FROM
					(
					SELECT
						subnetgroup.sgID,
						subnetgroup.sgGroupID,
						TRIM(
						SUBSTRING_INDEX( SUBSTRING_INDEX( subnetgroup.sgSubnets, ',', numbers.n ), ',', - 1 )) subnet 
					FROM
						numbers
						INNER JOIN subnetgroup ON CHAR_LENGTH( subnetgroup.sgSubnets ) - CHAR_LENGTH(
						REPLACE ( subnetgroup.sgSubnets, ',', '' ))>= numbers.n - 1 
					) AS temp 
				HAVING
					ipInSubnet = 1 
					LIMIT 1 
				) AS temp_final;

	IF FOUND_ROWS() = 1 THEN

			IF varHostID > 0 AND varsgGroupID > 0 THEN
					
					INSERT IGNORE INTO groupMembers ( gmHostID, gmGroupID ) VALUES ( varHostID, varsgGroupID );
				
			END IF;

	END IF;

END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
