-- 2026-04-16
-- Ajuste do menu admin para super admin e limite de tres niveis
-- Objetivos:
-- 1) garantir que super_admin / prevclube / tiix continuem vendo todas as areas
-- 2) manter o menu reorganizado em hubs, mas sem ultrapassar tres niveis
-- 3) propagar as permissoes dos roots antigos para os hubs novos
-- 4) achatar secoes intermediarias criadas na proposta anterior quando existirem

START TRANSACTION;

SET @menu_id = 35;

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Trabalho', '#', NULL, 0, 10, @menu_id, '_self', 'lucide:briefcase', 'menu.adm.hub.trabalho', 'adm_hub_trabalho', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.trabalho');

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Operacao', '#', NULL, 0, 11, @menu_id, '_self', 'solar:settings-linear', 'menu.adm.hub.operacao', 'adm_hub_operacao', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.operacao');

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Negocio', '#', NULL, 0, 12, @menu_id, '_self', 'lucide:briefcase-business', 'menu.adm.hub.negocio', 'adm_hub_negocio', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.negocio');

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Inteligencia', '#', NULL, 0, 13, @menu_id, '_self', 'solar:chart-line-duotone', 'menu.adm.hub.inteligencia', 'adm_hub_inteligencia', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.inteligencia');

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Plataforma', '#', NULL, 0, 14, @menu_id, '_self', 'lucide:settings', 'menu.adm.hub.plataforma', 'adm_hub_plataforma', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.plataforma');

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Portais', '#', NULL, 0, 15, @menu_id, '_self', 'solar:square-alt-arrow-right-line-duotone', 'menu.adm.hub.portais', 'adm_hub_portais', NULL
WHERE NOT EXISTS (SELECT 1 FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.portais');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.trabalho', 'Menu - Hub Trabalho', 'Hub de navegacao Trabalho', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.trabalho');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.operacao', 'Menu - Hub Operacao', 'Hub de navegacao Operacao', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.operacao');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.negocio', 'Menu - Hub Negocio', 'Hub de navegacao Negocio', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.negocio');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.inteligencia', 'Menu - Hub Inteligencia', 'Hub de navegacao Inteligencia', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.inteligencia');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.plataforma', 'Menu - Hub Plataforma', 'Hub de navegacao Plataforma', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.plataforma');

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.hub.portais', 'Menu - Hub Portais', 'Hub de navegacao Portais', NULL, 'menu'
WHERE NOT EXISTS (SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.hub.portais');

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.trabalho'
WHERE src.slug IN ('menu.adm.workspacehub', 'menu.adm.colaboracao', 'menu.adm.atendimento', 'menu.adm.suporte')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.operacao'
WHERE src.slug IN ('menu.adm.pessoas_operacao', 'menu.adm.operacional', 'menu.adm.parceiros', 'menu.tiixhealth.operacional')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.negocio'
WHERE src.slug IN ('menu.adm.admin', 'menu.adm.financeiro', 'menu.adm.comissoes', 'menu.adm.tiixstore')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.inteligencia'
WHERE src.slug IN ('menu.adm.relatorios', 'menu.adm.auditoria', 'menu.adm.repositorios')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.plataforma'
WHERE src.slug IN ('menu.adm.acessos', 'menu.adm.integracoes', 'menu.adm.config', 'menu.adm.tecnico')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT DISTINCT gp.id_grupo, hub.id
FROM wpjy_grupo_permissao gp
JOIN wpjy_permissoes src ON src.id = gp.id_permissao
JOIN wpjy_permissoes hub ON hub.slug = 'menu.adm.hub.portais'
WHERE src.slug IN ('menu.area-da-corretora', 'menu.area-da-empresa.35.18', 'menu.area-da-unidade', 'menu.novos-21-10.35.14', 'menu.area-do-cliente')
  AND NOT EXISTS (SELECT 1 FROM wpjy_grupo_permissao x WHERE x.id_grupo = gp.id_grupo AND x.id_permissao = hub.id);

SET @hub_trabalho = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.trabalho' LIMIT 1);
SET @hub_operacao = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.operacao' LIMIT 1);
SET @hub_negocio = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.negocio' LIMIT 1);
SET @hub_inteligencia = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.inteligencia' LIMIT 1);
SET @hub_plataforma = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.plataforma' LIMIT 1);
SET @hub_portais = (SELECT id FROM wpjy_menus_items WHERE menu = @menu_id AND slug = 'menu.adm.hub.portais' LIMIT 1);

UPDATE wpjy_menus_items
   SET parent = @hub_trabalho,
       sort = CASE id WHEN 599 THEN 0 WHEN 618 THEN 1 WHEN 619 THEN 2 WHEN 558 THEN 3 ELSE sort END
 WHERE @hub_trabalho IS NOT NULL AND menu = @menu_id AND id IN (599, 618, 619, 558);

UPDATE wpjy_menus_items
   SET parent = @hub_operacao,
       sort = CASE id WHEN 670 THEN 0 WHEN 555 THEN 1 WHEN 566 THEN 2 WHEN 740 THEN 3 ELSE sort END
 WHERE @hub_operacao IS NOT NULL AND menu = @menu_id AND id IN (670, 555, 566, 740);

UPDATE wpjy_menus_items
   SET parent = @hub_negocio,
       sort = CASE id WHEN 551 THEN 0 WHEN 553 THEN 1 WHEN 554 THEN 2 WHEN 633 THEN 3 ELSE sort END
 WHERE @hub_negocio IS NOT NULL AND menu = @menu_id AND id IN (551, 553, 554, 633);

UPDATE wpjy_menus_items
   SET parent = @hub_inteligencia,
       sort = CASE id WHEN 697 THEN 0 WHEN 556 THEN 1 WHEN 669 THEN 2 WHEN 696 THEN 3 ELSE sort END
 WHERE @hub_inteligencia IS NOT NULL AND menu = @menu_id AND id IN (697, 556, 669, 696);

UPDATE wpjy_menus_items
   SET parent = @hub_plataforma,
       sort = CASE id WHEN 552 THEN 0 WHEN 557 THEN 1 WHEN 559 THEN 2 WHEN 560 THEN 3 ELSE sort END
 WHERE @hub_plataforma IS NOT NULL AND menu = @menu_id AND id IN (552, 557, 559, 560);

UPDATE wpjy_menus_items
   SET parent = @hub_portais,
       sort = CASE id WHEN 531 THEN 0 WHEN 542 THEN 1 WHEN 714 THEN 2 WHEN 387 THEN 3 WHEN 504 THEN 4 ELSE sort END
 WHERE @hub_portais IS NOT NULL AND menu = @menu_id AND id IN (531, 542, 714, 387, 504);

SET @root_552 = 552;
SET @root_553 = 553;
SET @root_556 = 556;
SET @root_559 = 559;
SET @root_560 = 560;
SET @root_566 = 566;
SET @root_670 = 670;
SET @root_697 = 697;

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_552
 WHERE @root_552 IS NOT NULL AND sec.slug IN ('menu.adm.acessos.governanca', 'menu.adm.acessos.diagnostico');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_553
 WHERE @root_553 IS NOT NULL AND sec.slug IN ('menu.adm.financeiro.cobranca', 'menu.adm.financeiro.operacoes', 'menu.adm.financeiro.gateways');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_556
 WHERE @root_556 IS NOT NULL AND sec.slug IN ('menu.adm.relatorios_legacy.comercial', 'menu.adm.relatorios_legacy.carteira', 'menu.adm.relatorios_legacy.kpis');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_559
 WHERE @root_559 IS NOT NULL AND sec.slug IN ('menu.adm.config.base', 'menu.adm.config.documentos', 'menu.adm.config.conteudo', 'menu.adm.config.leads');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_560
 WHERE @root_560 IS NOT NULL AND sec.slug IN ('menu.adm.tecnico.estrutura', 'menu.adm.tecnico.engenharia');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_566
 WHERE @root_566 IS NOT NULL AND sec.slug IN ('menu.adm.parceiros.base', 'menu.adm.parceiros.catalogos', 'menu.adm.parceiros.relacionamentos', 'menu.adm.parceiros.leads_hub');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_670
 WHERE @root_670 IS NOT NULL AND sec.slug IN ('menu.adm.pessoas_operacao.pessoas', 'menu.adm.pessoas_operacao.estrutura', 'menu.adm.pessoas_operacao.crm');

UPDATE wpjy_menus_items i JOIN wpjy_menus_items sec ON sec.id = i.parent
   SET i.parent = @root_697
 WHERE @root_697 IS NOT NULL AND sec.slug IN ('menu.adm.relatorios.hub.centrais', 'menu.adm.relatorios.hub.financeiro', 'menu.adm.relatorios.hub.operacao', 'menu.adm.relatorios.hub.crescimento');

DELETE FROM wpjy_menus_items
 WHERE menu = @menu_id
   AND slug IN (
     'menu.adm.acessos.governanca',
     'menu.adm.acessos.diagnostico',
     'menu.adm.financeiro.cobranca',
     'menu.adm.financeiro.operacoes',
     'menu.adm.financeiro.gateways',
     'menu.adm.relatorios_legacy.comercial',
     'menu.adm.relatorios_legacy.carteira',
     'menu.adm.relatorios_legacy.kpis',
     'menu.adm.config.base',
     'menu.adm.config.documentos',
     'menu.adm.config.conteudo',
     'menu.adm.config.leads',
     'menu.adm.tecnico.estrutura',
     'menu.adm.tecnico.engenharia',
     'menu.adm.parceiros.base',
     'menu.adm.parceiros.catalogos',
     'menu.adm.parceiros.relacionamentos',
     'menu.adm.parceiros.leads_hub',
     'menu.adm.pessoas_operacao.pessoas',
     'menu.adm.pessoas_operacao.estrutura',
     'menu.adm.pessoas_operacao.crm',
     'menu.adm.relatorios.hub.centrais',
     'menu.adm.relatorios.hub.financeiro',
     'menu.adm.relatorios.hub.operacao',
     'menu.adm.relatorios.hub.crescimento'
   );

COMMIT;

SELECT id, label, parent, sort, slug
  FROM wpjy_menus_items
 WHERE menu = @menu_id
   AND parent = 0
 ORDER BY sort, id;

SELECT id, label, parent, sort, slug
  FROM wpjy_menus_items
 WHERE menu = @menu_id
   AND id IN (531, 542, 714, 387, 504, 551, 552, 553, 556, 559, 560, 566, 599, 618, 619, 633, 670, 696, 697, 740)
 ORDER BY parent, sort, id;