-- =====================================================================
-- MIGRATION 010  —  Carga oficial de bairros de Uberlândia (100 bairros)
-- =====================================================================
-- ESTRATÉGIA SEGURA: UPSERT (NÃO apaga, NÃO reinicia AUTO_INCREMENT).
--
-- Justificativa (análise de arquiteto):
--   9 tabelas referenciam neighborhoods.neighborhood_id. Apagar os bairros
--   e reiniciar o AUTO_INCREMENT zeraria o vínculo territorial de demandas,
--   cidadãos, visitas, agenda, mídias e linha do tempo (ON DELETE SET NULL)
--   e APAGARIA em cascata os bairros atendidos pela equipe (user_neighborhoods,
--   ON DELETE CASCADE). Como dos 23 bairros atuais apenas "Roosevelt" não
--   consta na nova base (e ele é apenas RENOMEADO para "Presidente Roosevelt"),
--   nenhum vínculo precisa ser destruído.
--
--   Por isso usamos ON DUPLICATE KEY UPDATE, casando pelo índice único
--   uq_neigh_tenant_name (tenant_id, name):
--     - 22 bairros existentes  -> ATUALIZADOS (mesmo id, vínculos preservados)
--     - 78 bairros novos        -> INSERIDOS
--     - "Roosevelt"             -> RENOMEADO antes do upsert (o id e todos os
--                                  vínculos migram naturalmente)
--
-- IDEMPOTENTE: pode ser reexecutado sem efeitos colaterais.
-- REQUISITO: faça BACKUP do banco antes de rodar em produção.
-- Compatível com MySQL 8.0+ e MariaDB 10.4+.
-- =====================================================================

SET NAMES utf8mb4;

-- Observação técnica: os passos 1 (ALTER) e 2 (procedure de merge) são DDL e
-- causam commit implícito em MySQL/MariaDB; por isso a transação explícita
-- envolve apenas o passo 3 (UPSERT, que é DML). Os passos 1 e 2 são, eles
-- próprios, idempotentes e seguros (verificam existência antes de agir).

-- 1) Coluna textual da região (rótulo de apoio para relatórios/exports).
--    ADD COLUMN IF NOT EXISTS é suportado no MariaDB 10.4+; em MySQL 8.0 que
--    não suporte o IF NOT EXISTS, a coluna já existe via schema e o erro pode
--    ser ignorado com segurança.
ALTER TABLE neighborhoods
  ADD COLUMN IF NOT EXISTS region_name VARCHAR(20) NULL AFTER region_id;

-- 2) Normalização de nomes ANTES do upsert (renomeados / acentuação).
--    ATENÇÃO: o nome de destino pode JÁ existir (ex.: a base já tem
--    "Presidente Roosevelt" e também "Roosevelt"). Renomear cegamente violaria
--    o índice único. Por isso usamos um procedimento que:
--      - se o destino NÃO existe: apenas renomeia (preserva id e vínculos);
--      - se o destino JÁ existe: FUNDE — remapeia os vínculos do antigo para o
--        destino em todas as tabelas e remove o registro antigo.
DROP PROCEDURE IF EXISTS sgg_rename_or_merge_neigh;
DELIMITER //
CREATE PROCEDURE sgg_rename_or_merge_neigh(IN p_old VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, IN p_new VARCHAR(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci)
BEGIN
  DECLARE v_old BIGINT DEFAULT NULL;
  DECLARE v_new BIGINT DEFAULT NULL;
  SELECT id INTO v_old FROM neighborhoods WHERE tenant_id=1 AND name=p_old LIMIT 1;
  IF v_old IS NOT NULL THEN
    SELECT id INTO v_new FROM neighborhoods WHERE tenant_id=1 AND name=p_new LIMIT 1;
    IF v_new IS NULL THEN
      -- destino não existe: renomeia preservando id e vínculos
      UPDATE neighborhoods SET name=p_new WHERE id=v_old;
    ELSE
      -- destino já existe: funde v_old -> v_new (remapeia tudo e remove o antigo)
      UPDATE citizens          SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE demands           SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE demand_groups     SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE technical_visits  SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE agenda_events     SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE mandate_timeline  SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE public_reports    SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE media_files       SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      UPDATE media_collections SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      DELETE FROM user_neighborhoods
        WHERE neighborhood_id=v_old
          AND user_id IN (SELECT user_id FROM (SELECT user_id FROM user_neighborhoods WHERE neighborhood_id=v_new) z);
      UPDATE user_neighborhoods SET neighborhood_id=v_new WHERE neighborhood_id=v_old;
      DELETE FROM neighborhoods WHERE id=v_old;
    END IF;
  END IF;
END //
DELIMITER ;

CALL sgg_rename_or_merge_neigh('Roosevelt',        'Presidente Roosevelt');
CALL sgg_rename_or_merge_neigh('Jardim Brasilia',  'Jardim Brasília');
CALL sgg_rename_or_merge_neigh('Santa Monica',     'Santa Mônica');
CALL sgg_rename_or_merge_neigh('Lidice',           'Lídice');
CALL sgg_rename_or_merge_neigh('Sao Jose',         'São José');
DROP PROCEDURE IF EXISTS sgg_rename_or_merge_neigh;

-- 3) UPSERT dos 100 bairros (casa por uq_neigh_tenant_name).
START TRANSACTION;
INSERT INTO neighborhoods (tenant_id, region_id, region_name, name, city, latitude, longitude, is_active, created_at, updated_at)
VALUES
(1, 4, 'Leste', 'Aclimação', 'Uberlândia', -18.905000, -48.218000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Altamira', 'Uberlândia', -18.958000, -48.260000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Alto Umuarama', 'Uberlândia', -18.895000, -48.240000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Alvorada', 'Uberlândia', -18.930000, -48.210000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Aparecida', 'Uberlândia', -18.915000, -48.240000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Aurora', 'Uberlândia', -18.940000, -48.210000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Bom Jesus', 'Uberlândia', -18.908500, -48.272000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Bosque dos Buritis', 'Uberlândia', -18.990000, -48.255000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Brasil', 'Uberlândia', -18.905500, -48.263500, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Buritis', 'Uberlândia', -18.988000, -48.260000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Carajás', 'Uberlândia', -18.945000, -48.245000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Cazeca', 'Uberlândia', -18.922000, -48.264500, 1, NOW(), NOW()),
(1, 1, 'Central', 'Centro', 'Uberlândia', -18.918600, -48.277200, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Chácaras Tubalina', 'Uberlândia', -18.944000, -48.318000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Cidade Jardim', 'Uberlândia', -18.952000, -48.272000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Cidade Verde', 'Uberlândia', -18.925000, -48.334000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Cruzeiro do Sul', 'Uberlândia', -18.895000, -48.265000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Custódio Pereira', 'Uberlândia', -18.905000, -48.230000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Daniel Fonseca', 'Uberlândia', -18.914500, -48.292500, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Distrito Industrial', 'Uberlândia', -18.865000, -48.285000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Dom Almir', 'Uberlândia', -18.930000, -48.185000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Dona Zulmira', 'Uberlândia', -18.910000, -48.304000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Fruta do Conde', 'Uberlândia', -18.920000, -48.326000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Fundinho', 'Uberlândia', -18.913200, -48.282000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Granada', 'Uberlândia', -18.963000, -48.248000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Grand Ville', 'Uberlândia', -18.925000, -48.205000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Guarani', 'Uberlândia', -18.918000, -48.302000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Gávea', 'Uberlândia', -18.940000, -48.285000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Ipanema', 'Uberlândia', -18.905000, -48.202000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jaraguá', 'Uberlândia', -18.935000, -48.292000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Jardim América', 'Uberlândia', -18.872000, -48.235000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Jardim Botânico', 'Uberlândia', -18.980000, -48.267000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Jardim Brasília', 'Uberlândia', -18.872000, -48.282000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Jardim Califórnia', 'Uberlândia', -18.925000, -48.210000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Canaã', 'Uberlândia', -18.935000, -48.340000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Célia', 'Uberlândia', -18.915000, -48.335000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim das Palmeiras', 'Uberlândia', -18.936000, -48.326000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Europa', 'Uberlândia', -18.910000, -48.322000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Holanda', 'Uberlândia', -18.952000, -48.333000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Inconfidência', 'Uberlândia', -18.955000, -48.320000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Jardim Ipanema', 'Uberlândia', -18.900000, -48.200000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Jardim Ipanema I', 'Uberlândia', -18.870000, -48.220000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Jardim Karaíba', 'Uberlândia', -18.957000, -48.288000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Patrícia', 'Uberlândia', -18.945000, -48.315000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Jardim Sul', 'Uberlândia', -18.985000, -48.275000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Jardim Umuarama', 'Uberlândia', -18.912000, -48.312000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Joana D''Arc', 'Uberlândia', -18.935000, -48.195000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Lagoinha', 'Uberlândia', -18.955000, -48.250000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Laranjeiras', 'Uberlândia', -18.980000, -48.245000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Liberdade', 'Uberlândia', -18.860000, -48.245000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Luizote de Freitas', 'Uberlândia', -18.930000, -48.305000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Lídice', 'Uberlândia', -18.916000, -48.271000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Mansour', 'Uberlândia', -18.925000, -48.318000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Mansões Aeroporto', 'Uberlândia', -18.885000, -48.225000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Maravilha', 'Uberlândia', -18.875000, -48.295000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Marta Helena', 'Uberlândia', -18.878000, -48.265000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Martins', 'Uberlândia', -18.930000, -48.256000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Minas Gerais', 'Uberlândia', -18.870000, -48.273000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Monte Hebron', 'Uberlândia', -18.890000, -48.338000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Morada da Colina', 'Uberlândia', -18.948000, -48.286000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Morada dos Pássaros', 'Uberlândia', -18.915000, -48.195000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Morada Nova', 'Uberlândia', -18.905000, -48.355000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Morumbi', 'Uberlândia', -18.935000, -48.230000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Nossa Senhora Aparecida', 'Uberlândia', -18.912000, -48.265000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Nossa Senhora das Graças', 'Uberlândia', -18.882000, -48.258000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Nova Uberlândia', 'Uberlândia', -18.952000, -48.310000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Osvaldo Rezende', 'Uberlândia', -18.923000, -48.274000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Pacaembu', 'Uberlândia', -18.887000, -48.258000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Pampulha', 'Uberlândia', -18.965000, -48.260000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Panorama', 'Uberlândia', -18.925000, -48.335000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Patrimônio', 'Uberlândia', -18.942000, -48.284000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Pequis', 'Uberlândia', -18.895000, -48.350000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Planalto', 'Uberlândia', -18.925000, -48.298000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Portal do Vale', 'Uberlândia', -18.910000, -48.205000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Presidente Roosevelt', 'Uberlândia', -18.880000, -48.278000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Prosperidade', 'Uberlândia', -18.920000, -48.190000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Residencial Gramado', 'Uberlândia', -18.862000, -48.260000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Residencial Integração', 'Uberlândia', -18.900000, -48.335000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Residencial Liberdade', 'Uberlândia', -18.858000, -48.242000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Residencial Pequis', 'Uberlândia', -18.890000, -48.352000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Residencial Vitória', 'Uberlândia', -18.982000, -48.232000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Santa Luzia', 'Uberlândia', -18.963000, -48.238000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Santa Mônica', 'Uberlândia', -18.945000, -48.265000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'Santa Rosa', 'Uberlândia', -18.925000, -48.235000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Saraiva', 'Uberlândia', -18.908000, -48.288000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Savana', 'Uberlândia', -18.972000, -48.270000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Segismundo Pereira', 'Uberlândia', -18.925000, -48.238000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Shopping Park', 'Uberlândia', -18.997000, -48.278000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'São Francisco', 'Uberlândia', -18.940000, -48.220000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'São Jorge', 'Uberlândia', -18.975000, -48.230000, 1, NOW(), NOW()),
(1, 2, 'Norte', 'São José', 'Uberlândia', -18.872000, -48.248000, 1, NOW(), NOW()),
(1, 1, 'Central', 'Tabajaras', 'Uberlândia', -18.908900, -48.273500, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Taiaman', 'Uberlândia', -18.908000, -48.312000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Tibery', 'Uberlândia', -18.905000, -48.245000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Tocantins', 'Uberlândia', -18.938000, -48.310000, 1, NOW(), NOW()),
(1, 5, 'Oeste', 'Tubalina', 'Uberlândia', -18.943000, -48.302000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Umuarama', 'Uberlândia', -18.900000, -48.252000, 1, NOW(), NOW()),
(1, 4, 'Leste', 'Vida Nova', 'Uberlândia', -18.940000, -48.180000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Vigilato Pereira', 'Uberlândia', -18.940000, -48.279000, 1, NOW(), NOW()),
(1, 3, 'Sul', 'Vila Marielza', 'Uberlândia', -18.970000, -48.255000, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
  region_id   = VALUES(region_id),
  region_name = VALUES(region_name),
  city        = VALUES(city),
  latitude    = VALUES(latitude),
  longitude   = VALUES(longitude),
  is_active   = 1,
  updated_at  = NOW();

COMMIT;