INSERT IGNORE INTO locations (location) SELECT location FROM atms_list;

CREATE TABLE `locations` (
  `location_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `location` varchar(255) NOT NULL,
  `region_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `city_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`location_id`),
  UNIQUE KEY `location_UNIQUE` (`location`),
  KEY `fk_atms_cities_idx` (`city_id`),
  KEY `fk_atms_regions_idx` (`region_id`),
  CONSTRAINT `fk_atms_cities` FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`),
  CONSTRAINT `fk_atms_regions` FOREIGN KEY (`region_id`) REFERENCES `regions` (`region_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4102 DEFAULT CHARSET=utf8;

CREATE TRIGGER `locations_insert` BEFORE INSERT ON `locations` FOR EACH ROW BEGIN
SET NEW.city_id=get_city(NEW.location);
SET NEW.region_id=get_region(NEW.location);
END;

CREATE FUNCTION `get_city`(in_location VARCHAR(255)) RETURNS smallint(5) unsigned
    READS SQL DATA
BEGIN
	DECLARE out_city SMALLINT(5) UNSIGNED DEFAULT 0;
	DECLARE done INT DEFAULT FALSE;
	DECLARE city VARCHAR(255);
	DECLARE id SMALLINT(5);
	DECLARE cur CURSOR FOR SELECT city_id, city_name FROM cities;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	SET @a = 0;

	OPEN cur;
	
	read_loop: LOOP
		FETCH cur INTO id, city;
		IF done THEN
      LEAVE read_loop;
    END IF;
		IF (SELECT in_location REGEXP CONCAT('[[:<:]]',city,'[[:>:]]')) = 1 THEN
			SELECT id INTO out_city;
			LEAVE read_loop;
    END IF;
	END LOOP;

	CLOSE cur;

CREATE TRIGGER locations_insert BEFORE INSERT ON locations
FOR EACH ROW 
BEGIN
SET NEW.city_id = IFNULL((SELECT city_id FROM cities WHERE NEW.location REGEXP CONCAT('[[:<:]]',city_name,'[[:>:]]') LIMIT 1),0);
SET NEW.region_id = IFNULL((SELECT region_id FROM regions WHERE LOCATE(region_name,NEW.location) <> 0 LIMIT 1),0);
END;