-- =====================================================================
-- MIGRATION 009  (Hotfix 10.1 — Saneamento de bairros)
-- Corrige duplicados e problemas de encoding em `neighborhoods` que
-- ocorreram em instalações onde os seeds rodaram mais de uma vez ou com
-- collation/encoding divergente (o UNIQUE incluía `city`, então grafias
-- ou bytes diferentes driblavam a checagem).
--
-- O que faz, de forma IDEMPOTENTE e SEGURA para produção:
--   1) Normaliza o charset da coluna para utf8mb4.
--   2) Para cada grupo de bairros com o MESMO nome (no mesmo tenant),
--      elege o de menor id como "sobrevivente" e:
--        - remapeia neighborhood_id em TODAS as tabelas que referenciam,
--        - copia coordenadas para o sobrevivente se ele não tiver,
--        - marca os duplicados como inativos e os exclui (soft via flag
--          is_active=0 + remoção física, já que não têm mais referências).
--   3) Reforça a unicidade por (tenant_id, name) com índice único, para
--      impedir novos duplicados (a aplicação também valida).
--
-- NENHUMA tabela nova.
-- =====================================================================

-- 1) Garante charset correto da coluna de nome (não destrutivo).
ALTER TABLE neighborhoods
  MODIFY name VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- 2) Funde duplicados. Usa procedure para remapear e remover com segurança.
DROP PROCEDURE IF EXISTS sgg_merge_neighborhoods;
DELIMITER //
CREATE PROCEDURE sgg_merge_neighborhoods()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_keep BIGINT;
  DECLARE v_dup BIGINT;
  -- pares (sobrevivente, duplicado): para cada nome+tenant, o menor id fica
  DECLARE cur CURSOR FOR
    SELECT m.keep_id, n.id
    FROM neighborhoods n
    JOIN (
      SELECT tenant_id, name, MIN(id) AS keep_id
      FROM neighborhoods
      GROUP BY tenant_id, name
    ) m ON m.tenant_id = n.tenant_id AND m.name = n.name
    WHERE n.id <> m.keep_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_keep, v_dup;
    IF done = 1 THEN LEAVE read_loop; END IF;

    -- remapeia todas as referências do duplicado para o sobrevivente
    UPDATE citizens          SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE demands           SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE demand_groups     SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE technical_visits  SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE agenda_events     SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE mandate_timeline  SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE public_reports    SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE media_files       SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;
    UPDATE media_collections SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;

    -- user_neighborhoods é N:N: evita violar PK ao remapear
    DELETE FROM user_neighborhoods
      WHERE neighborhood_id = v_dup
        AND user_id IN (SELECT user_id FROM (SELECT user_id FROM user_neighborhoods WHERE neighborhood_id = v_keep) x);
    UPDATE user_neighborhoods SET neighborhood_id = v_keep WHERE neighborhood_id = v_dup;

    -- copia coordenadas para o sobrevivente, se ele não tiver e o duplicado tiver
    UPDATE neighborhoods k
      JOIN neighborhoods d ON d.id = v_dup
      SET k.latitude = d.latitude, k.longitude = d.longitude
      WHERE k.id = v_keep AND k.latitude IS NULL AND d.latitude IS NOT NULL;

    -- remove o duplicado (já sem referências)
    DELETE FROM neighborhoods WHERE id = v_dup;
  END LOOP;
  CLOSE cur;
END //
DELIMITER ;

CALL sgg_merge_neighborhoods();
DROP PROCEDURE IF EXISTS sgg_merge_neighborhoods;

-- 3) Reforça unicidade por (tenant_id, name) para impedir novos duplicados,
--    e remove o índice antigo (tenant, name, city) que permitia duplicar o
--    mesmo bairro quando a cidade vinha com grafia/encoding diferente.
DROP PROCEDURE IF EXISTS sgg_fix_uniques;
DELIMITER //
CREATE PROCEDURE sgg_fix_uniques()
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.statistics
    WHERE table_schema=DATABASE() AND table_name='neighborhoods' AND index_name='uq_neigh_tenant_name') THEN
    ALTER TABLE neighborhoods ADD UNIQUE KEY uq_neigh_tenant_name (tenant_id, name);
  END IF;
  IF EXISTS (SELECT 1 FROM information_schema.statistics
    WHERE table_schema=DATABASE() AND table_name='neighborhoods' AND index_name='uq_neigh_name_city_tenant') THEN
    ALTER TABLE neighborhoods DROP INDEX uq_neigh_name_city_tenant;
  END IF;
END //
DELIMITER ;
CALL sgg_fix_uniques();
DROP PROCEDURE IF EXISTS sgg_fix_uniques;