-- ============================================================
-- Populate `regions` from existing `cities` and link each city
-- to its matching region (match by country_id + key).
--
-- Run this BEFORE trying to set cities.region_id manually,
-- otherwise you get foreign key error #1452
-- (regions table must contain the row first).
--
-- Idempotent: safe to run multiple times.
-- ============================================================

-- 1) Create one region per city (skips ones already created)
INSERT INTO regions (`title`, `key`, `country_id`, `status`, `priority`, `created_at`, `updated_at`)
SELECT c.`title`, c.`key`, c.`country_id`, 'active', IFNULL(c.`priority`, 1), NOW(), NOW()
FROM `cities` c
WHERE c.`key` IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM `regions` r
      WHERE r.`country_id` = c.`country_id`
        AND r.`key` = c.`key`
  );

-- 2) Link each city to its matching region
UPDATE `cities` c
JOIN `regions` r ON r.`country_id` = c.`country_id`
                AND r.`key` = c.`key`
SET c.`region_id` = r.`id`
WHERE c.`region_id` IS NULL;

-- Optional check: list any city that still has no region
-- SELECT * FROM cities WHERE region_id IS NULL;