-- 2026-04-16
-- Reorganizacao conceitual do menu com portais no topo
-- Objetivos:
-- 1) manter os portais sempre no topo do primeiro nivel
-- 2) manter TIIX HEALTH e TIIXSTORE logo abaixo dos portais
-- 3) reorganizar o admin em ate 3 niveis reais da tabela
-- 4) preservar slugs finais e ACL dos itens ja existentes
-- 5) expor a estrutura completa para perfis admin-like, em especial super_admin

START TRANSACTION;

SET @menu_id = 35;

-- Roots principais em ordem fixa
UPDATE wpjy_menus_items
   SET label = CASE id
         WHEN 599 THEN 'Trabalho'
         WHEN 670 THEN 'CRM & Operacao'
         WHEN 552 THEN 'Governanca & Plataforma'
         WHEN 697 THEN 'Relatorios'
         ELSE label
       END,
       parent = 0,
       sort = CASE id
         WHEN 531 THEN 0
         WHEN 542 THEN 1
         WHEN 714 THEN 2
         WHEN 387 THEN 3
         WHEN 504 THEN 4
         WHEN 740 THEN 5
         WHEN 633 THEN 6
         WHEN 599 THEN 7
         WHEN 553 THEN 8
         WHEN 670 THEN 9
         WHEN 551 THEN 10
         WHEN 552 THEN 11
         WHEN 697 THEN 12
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (531, 542, 714, 387, 504, 740, 633, 599, 553, 670, 551, 552, 697);

-- Roots sinteticos antigos deixam de participar da navegacao principal
UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 741 THEN 90
         WHEN 742 THEN 91
         WHEN 743 THEN 92
         WHEN 744 THEN 93
         WHEN 745 THEN 94
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (741, 742, 743, 744, 745);

-- Trabalho: itens pessoais primeiro, secoes depois
UPDATE wpjy_menus_items
   SET parent = 599,
       sort = CASE id
         WHEN 600 THEN 0
         WHEN 610 THEN 1
         WHEN 611 THEN 2
         WHEN 612 THEN 3
         WHEN 616 THEN 4
         WHEN 617 THEN 5
         WHEN 604 THEN 6
         WHEN 601 THEN 7
         WHEN 615 THEN 8
         WHEN 618 THEN 20
         WHEN 619 THEN 21
         WHEN 558 THEN 22
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (600, 610, 611, 612, 616, 617, 604, 601, 615, 618, 619, 558);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 613 THEN 0
         WHEN 606 THEN 1
         WHEN 607 THEN 2
         WHEN 608 THEN 3
         WHEN 603 THEN 4
         WHEN 652 THEN 5
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 618
   AND id IN (613, 606, 607, 608, 603, 652);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 605 THEN 0
         WHEN 448 THEN 1
         WHEN 446 THEN 2
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 619
   AND id IN (605, 448, 446);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 602 THEN 0
         WHEN 594 THEN 1
         WHEN 595 THEN 2
         WHEN 346 THEN 3
         WHEN 348 THEN 4
         WHEN 415 THEN 5
         WHEN 590 THEN 6
         WHEN 648 THEN 7
         WHEN 649 THEN 8
         WHEN 614 THEN 9
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 558
   AND id IN (602, 594, 595, 346, 348, 415, 590, 648, 649, 614);

-- Financeiro como root principal e Comissoes como secao propria
UPDATE wpjy_menus_items
   SET parent = 553,
       sort = 20
 WHERE menu = @menu_id
   AND id = 554;

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 340 THEN 0
         WHEN 341 THEN 1
         WHEN 434 THEN 2
         WHEN 440 THEN 3
         WHEN 455 THEN 4
         WHEN 469 THEN 5
         WHEN 472 THEN 6
         WHEN 471 THEN 7
         WHEN 491 THEN 8
         WHEN 495 THEN 9
         WHEN 382 THEN 10
         WHEN 358 THEN 11
         WHEN 435 THEN 12
         WHEN 436 THEN 13
         WHEN 367 THEN 14
         WHEN 342 THEN 15
         WHEN 343 THEN 16
         WHEN 554 THEN 20
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 553
   AND id IN (340, 341, 434, 440, 455, 469, 472, 471, 491, 495, 382, 358, 435, 436, 367, 342, 343, 554);

-- CRM & Operacao: base direta de pessoas e CRM, com Operacional e Parceiros como secoes
UPDATE wpjy_menus_items
   SET parent = 670,
       sort = CASE id
         WHEN 498 THEN 0
         WHEN 627 THEN 1
         WHEN 625 THEN 2
         WHEN 624 THEN 3
         WHEN 501 THEN 4
         WHEN 502 THEN 5
         WHEN 646 THEN 6
         WHEN 647 THEN 7
         WHEN 658 THEN 8
         WHEN 417 THEN 9
         WHEN 443 THEN 10
         WHEN 317 THEN 11
         WHEN 318 THEN 12
         WHEN 319 THEN 13
         WHEN 380 THEN 14
         WHEN 555 THEN 20
         WHEN 566 THEN 21
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (498, 627, 625, 624, 501, 502, 646, 647, 658, 417, 443, 317, 318, 319, 380, 555, 566);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 338 THEN 0
         WHEN 437 THEN 1
         WHEN 496 THEN 2
         WHEN 441 THEN 3
         WHEN 567 THEN 4
         WHEN 568 THEN 5
         WHEN 569 THEN 6
         WHEN 570 THEN 7
         WHEN 571 THEN 8
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 555
   AND id IN (338, 437, 496, 441, 567, 568, 569, 570, 571);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 467 THEN 0
         WHEN 302 THEN 1
         WHEN 372 THEN 2
         WHEN 369 THEN 3
         WHEN 370 THEN 4
         WHEN 371 THEN 5
         WHEN 373 THEN 6
         WHEN 503 THEN 7
         WHEN 656 THEN 8
         WHEN 657 THEN 9
         WHEN 457 THEN 10
         WHEN 456 THEN 11
         WHEN 459 THEN 12
         WHEN 464 THEN 13
         WHEN 386 THEN 14
         WHEN 466 THEN 15
         WHEN 461 THEN 16
         WHEN 458 THEN 17
         WHEN 462 THEN 18
         WHEN 463 THEN 19
         WHEN 659 THEN 20
         WHEN 660 THEN 21
         WHEN 488 THEN 22
         WHEN 489 THEN 23
         WHEN 490 THEN 24
         WHEN 626 THEN 25
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 566
   AND id IN (467, 302, 372, 369, 370, 371, 373, 503, 656, 657, 457, 456, 459, 464, 386, 466, 461, 458, 462, 463, 659, 660, 488, 489, 490, 626);

-- Administrativo enxuto
UPDATE wpjy_menus_items
   SET parent = CASE
         WHEN id = 772 THEN 551
         ELSE parent
       END,
       sort = CASE id
         WHEN 310 THEN 0
         WHEN 352 THEN 1
         WHEN 487 THEN 2
         WHEN 500 THEN 3
         WHEN 314 THEN 4
         WHEN 452 THEN 5
         WHEN 453 THEN 6
         WHEN 772 THEN 8
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (310, 352, 487, 500, 314, 452, 453, 772);

-- Governanca & Plataforma com secoes de integracao, configuracao e operacao tecnica
UPDATE wpjy_menus_items
   SET parent = 552,
       sort = CASE id
         WHEN 598 THEN 0
         WHEN 497 THEN 1
         WHEN 438 THEN 2
         WHEN 493 THEN 3
         WHEN 492 THEN 4
         WHEN 363 THEN 5
         WHEN 473 THEN 6
         WHEN 362 THEN 7
         WHEN 360 THEN 8
         WHEN 474 THEN 20
         WHEN 475 THEN 21
         WHEN 476 THEN 22
         WHEN 477 THEN 23
         WHEN 478 THEN 24
         WHEN 479 THEN 25
         WHEN 480 THEN 26
         WHEN 481 THEN 27
         WHEN 482 THEN 28
         WHEN 483 THEN 29
         WHEN 484 THEN 30
         WHEN 485 THEN 31
         WHEN 486 THEN 32
         WHEN 557 THEN 40
         WHEN 559 THEN 41
         WHEN 560 THEN 42
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (598, 497, 438, 493, 492, 363, 473, 362, 360, 474, 475, 476, 477, 478, 479, 480, 481, 482, 483, 484, 485, 486, 557, 559, 560);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 364 THEN 0
         WHEN 409 THEN 1
         WHEN 365 THEN 2
         WHEN 499 THEN 3
         WHEN 621 THEN 4
         WHEN 637 THEN 5
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 557
   AND id IN (364, 409, 365, 499, 621, 637);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 428 THEN 0
         WHEN 301 THEN 1
         WHEN 623 THEN 2
         WHEN 353 THEN 3
         WHEN 354 THEN 4
         WHEN 375 THEN 5
         WHEN 383 THEN 6
         WHEN 384 THEN 7
         WHEN 385 THEN 8
         WHEN 655 THEN 9
         WHEN 374 THEN 10
         WHEN 351 THEN 11
         WHEN 429 THEN 12
         WHEN 430 THEN 13
         WHEN 653 THEN 14
         WHEN 572 THEN 15
         WHEN 573 THEN 16
         WHEN 574 THEN 17
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 559
   AND id IN (428, 301, 623, 353, 354, 375, 383, 384, 385, 655, 374, 351, 429, 430, 653, 572, 573, 574);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 576 THEN 0
         WHEN 575 THEN 1
         WHEN 577 THEN 2
         WHEN 578 THEN 3
         WHEN 580 THEN 4
         WHEN 581 THEN 5
         WHEN 586 THEN 6
         WHEN 596 THEN 7
         WHEN 638 THEN 8
         WHEN 639 THEN 9
         WHEN 640 THEN 10
         WHEN 650 THEN 11
         WHEN 651 THEN 12
         WHEN 661 THEN 13
         WHEN 356 THEN 14
         WHEN 444 THEN 15
         WHEN 450 THEN 16
         WHEN 449 THEN 17
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 560
   AND id IN (576, 575, 577, 578, 580, 581, 586, 596, 638, 639, 640, 650, 651, 661, 356, 444, 450, 449);

-- Relatorios: secoes reais somente no segundo nivel
UPDATE wpjy_menus_items
   SET parent = 697,
       sort = CASE id
         WHEN 556 THEN 0
         WHEN 669 THEN 1
         WHEN 696 THEN 2
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (556, 669, 696);

UPDATE wpjy_menus_items
   SET parent = 556,
       sort = CASE id
         WHEN 698 THEN 0
         WHEN 699 THEN 1
         WHEN 431 THEN 2
         WHEN 442 THEN 3
         WHEN 408 THEN 4
         WHEN 326 THEN 10
         WHEN 327 THEN 11
         WHEN 328 THEN 12
         WHEN 329 THEN 13
         WHEN 330 THEN 14
         WHEN 331 THEN 15
         WHEN 332 THEN 16
         WHEN 333 THEN 17
         WHEN 334 THEN 18
         WHEN 335 THEN 19
         WHEN 336 THEN 20
         WHEN 700 THEN 30
         WHEN 701 THEN 31
         WHEN 702 THEN 32
         WHEN 703 THEN 33
         WHEN 704 THEN 34
         WHEN 705 THEN 35
         WHEN 706 THEN 36
         WHEN 707 THEN 37
         WHEN 708 THEN 38
         WHEN 709 THEN 39
         WHEN 710 THEN 40
         WHEN 711 THEN 41
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (698, 699, 431, 442, 408, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 700, 701, 702, 703, 704, 705, 706, 707, 708, 709, 710, 711);

-- Corretora: adicionar Niveis, que ja possui ACL propria
INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT
  'Niveis',
  'niveis',
  'corretora',
  531,
  6,
  @menu_id,
  '_self',
  'solar:medal-ribbon-star-line-duotone',
  'corretora-niveis',
  'corretora_niveis',
  NULL
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_menus_items
   WHERE menu = @menu_id
     AND slug = 'corretora-niveis'
);

UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 532 THEN 0
         WHEN 533 THEN 1
         WHEN 540 THEN 2
         WHEN 541 THEN 3
         WHEN 535 THEN 4
         WHEN 537 THEN 5
         WHEN 538 THEN 7
         WHEN 539 THEN 8
         WHEN 592 THEN 9
         WHEN 534 THEN 10
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 531
   AND id IN (532, 533, 540, 541, 535, 537, 538, 539, 592, 534);

-- Empresa e Unidade: itens operacionais voltam para seus proprios portais
UPDATE wpjy_menus_items
   SET parent = 542,
       sort = CASE id
         WHEN 543 THEN 0
         WHEN 544 THEN 1
         WHEN 545 THEN 2
         WHEN 712 THEN 3
         WHEN 726 THEN 4
         WHEN 728 THEN 5
         WHEN 738 THEN 6
         WHEN 729 THEN 7
         WHEN 730 THEN 8
         WHEN 731 THEN 9
         WHEN 546 THEN 10
         WHEN 724 THEN 11
         WHEN 593 THEN 12
         WHEN 713 THEN 13
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (543, 544, 545, 712, 726, 728, 738, 729, 730, 731, 546, 724, 593, 713);

UPDATE wpjy_menus_items
   SET parent = 714,
       sort = CASE id
         WHEN 715 THEN 0
         WHEN 716 THEN 1
         WHEN 717 THEN 2
         WHEN 718 THEN 3
         WHEN 727 THEN 4
         WHEN 732 THEN 5
         WHEN 739 THEN 6
         WHEN 733 THEN 7
         WHEN 734 THEN 8
         WHEN 735 THEN 9
         WHEN 719 THEN 10
         WHEN 725 THEN 11
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (715, 716, 717, 718, 727, 732, 739, 733, 734, 735, 719, 725);

-- Remover tela secundaria de edicao do menu navegavel
DELETE FROM wpjy_menus_items
 WHERE menu = @menu_id
   AND id IN (547);

-- TIIX HEALTH fica como hub de produto transversal e nao como espelho de empresa/unidade
UPDATE wpjy_menus_items
   SET parent = 740,
       sort = CASE id
         WHEN 410 THEN 0
         WHEN 412 THEN 1
         WHEN 433 THEN 2
         WHEN 687 THEN 3
         ELSE sort
       END
 WHERE menu = @menu_id
   AND id IN (410, 412, 433, 687);

-- TIIXSTORE com ordem consistente
UPDATE wpjy_menus_items
   SET sort = CASE id
         WHEN 686 THEN 0
         WHEN 629 THEN 1
         WHEN 630 THEN 2
         WHEN 681 THEN 3
         WHEN 682 THEN 4
         WHEN 631 THEN 5
         WHEN 632 THEN 6
         WHEN 671 THEN 7
         WHEN 672 THEN 8
         WHEN 683 THEN 9
         WHEN 684 THEN 10
         WHEN 685 THEN 11
         ELSE sort
       END
 WHERE menu = @menu_id
   AND parent = 633
   AND id IN (686, 629, 630, 681, 682, 631, 632, 671, 672, 683, 684, 685);

-- Comites: tela existente e relevante para super_admin/prevclube/tiix
INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'comites', 'Comites', 'Acesso ao menu de comites', NULL, 'menu'
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_permissoes
   WHERE slug = 'comites'
);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT g.id, p.id
  FROM wpjy_grupos_permissoes g
  JOIN wpjy_permissoes p
    ON p.slug = 'comites'
 WHERE g.slug IN ('super_admin', 'prevclube', 'tiix')
   AND NOT EXISTS (
     SELECT 1
       FROM wpjy_grupo_permissao gp
      WHERE gp.id_grupo = g.id
        AND gp.id_permissao = p.id
   );

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT
  'Comites',
  'comites',
  'adm',
  551,
  7,
  @menu_id,
  '_self',
  'ti ti-sitemap',
  'comites',
  'comites',
  NULL
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_menus_items
   WHERE menu = @menu_id
     AND slug = 'comites'
);

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, 740, 633, 599, 553, 670, 551, 552, 697, 554, 555, 556, 557, 558, 559, 560, 566, 618, 619, 669, 696)
 ORDER BY parent, sort, id;