-- Fix empty categories first
UPDATE locations 
SET category = 'DISTRICT' 
WHERE name LIKE '%District' 
  AND (category IS NULL OR category = '')
  AND parent_id = 116;

-- Delete duplicates, keeping the oldest one for each district
DELETE FROM locations
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY name, parent_id, map_id ORDER BY id) AS rn
    FROM locations
    WHERE parent_id = 116 
      AND name LIKE '%District'
      AND map_id = 1
  ) AS duplicates
  WHERE rn > 1
);

-- Verify remaining districts
SELECT name, category, COUNT(*) as count
FROM locations
WHERE parent_id = 116 AND name LIKE '%District'
GROUP BY name, category
ORDER BY name;
