-- =====================================================================
-- MIGRATION 002  (Bloco 2)
-- Dados iniciais para operação: bairros de Uberlândia, regiões e um
-- órgão/secretaria de exemplo. Idempotente (INSERT IGNORE por unique).
-- =====================================================================

-- Regiões de Uberlândia (setores administrativos)
INSERT IGNORE INTO regions (tenant_id, name, description, is_active) VALUES
(1,'Setor Central','Região central de Uberlândia',1),
(1,'Setor Norte','Região norte',1),
(1,'Setor Sul','Região sul',1),
(1,'Setor Leste','Região leste',1),
(1,'Setor Oeste','Região oeste',1);

-- Bairros de Uberlândia (amostra ampla; edite/adicione no sistema depois)
INSERT IGNORE INTO neighborhoods (tenant_id, region_id, name, city, is_active) VALUES
(1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1 LIMIT 1),'Centro','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1 LIMIT 1),'Fundinho','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1 LIMIT 1),'Lídice','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1 LIMIT 1),'Osvaldo Rezende','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1 LIMIT 1),'Martins','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1 LIMIT 1),'Santa Mônica','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1 LIMIT 1),'Saraiva','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1 LIMIT 1),'Tabajaras','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1 LIMIT 1),'Cidade Jardim','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1 LIMIT 1),'Gávea','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1 LIMIT 1),'Tibery','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1 LIMIT 1),'Aparecida','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1 LIMIT 1),'Santa Rosa','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1 LIMIT 1),'Morumbi','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1 LIMIT 1),'Roosevelt','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1 LIMIT 1),'Marta Helena','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1 LIMIT 1),'Presidente Roosevelt','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1 LIMIT 1),'Jardim Brasília','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1 LIMIT 1),'Luizote de Freitas','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1 LIMIT 1),'Tocantins','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1 LIMIT 1),'Planalto','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1 LIMIT 1),'Jardim Patrícia','Uberlândia',1),
(1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1 LIMIT 1),'Guarani','Uberlândia',1);

-- Secretarias/órgãos de exemplo (vincule às categorias depois)
INSERT IGNORE INTO departments (tenant_id, name, acronym, is_active) VALUES
(1,'Secretaria Municipal de Obras','SEOBRAS',1),
(1,'Secretaria Municipal de Trânsito e Transportes','SETTRAN',1),
(1,'Secretaria Municipal de Serviços Urbanos','SESU',1),
(1,'Secretaria Municipal de Saúde','SMS',1),
(1,'Secretaria Municipal de Meio Ambiente','SMMA',1),
(1,'DMAE - Água e Esgoto','DMAE',1);

-- Vincular categorias às secretarias padrão (best-effort por slug/acrônimo)
UPDATE demand_categories SET default_department_id =
  (SELECT id FROM departments WHERE acronym='SEOBRAS' AND tenant_id=1 LIMIT 1)
  WHERE tenant_id=1 AND slug IN ('asfalto');
UPDATE demand_categories SET default_department_id =
  (SELECT id FROM departments WHERE acronym='SESU' AND tenant_id=1 LIMIT 1)
  WHERE tenant_id=1 AND slug IN ('iluminacao','limpeza','praca_lazer');
UPDATE demand_categories SET default_department_id =
  (SELECT id FROM departments WHERE acronym='SETTRAN' AND tenant_id=1 LIMIT 1)
  WHERE tenant_id=1 AND slug IN ('transito','transporte');
UPDATE demand_categories SET default_department_id =
  (SELECT id FROM departments WHERE acronym='SMS' AND tenant_id=1 LIMIT 1)
  WHERE tenant_id=1 AND slug IN ('saude');
UPDATE demand_categories SET default_department_id =
  (SELECT id FROM departments WHERE acronym='DMAE' AND tenant_id=1 LIMIT 1)
  WHERE tenant_id=1 AND slug IN ('agua_esgoto');

-- Cidade padrão nas settings
INSERT INTO settings (tenant_id, `key`, `value`) VALUES (1,'city_name','Uberlândia')
  ON DUPLICATE KEY UPDATE `value`='Uberlândia';