-- 2026-04-17
-- Menu tecnico e atalhos para changelogs autorais e atividade GitHub da equipe
-- Objetivos:
-- 1) cadastrar as telas /adm/dev-changelogs e /adm/github-minha-atividade
-- 2) cadastrar/garantir as permissoes tecnicas para os devs
-- 3) incluir os atalhos no hub Trabalho sem duplicidade

START TRANSACTION;

SET @menu_id = 35;

DROP TEMPORARY TABLE IF EXISTS tmp_dev_menu_pages;

CREATE TEMPORARY TABLE tmp_dev_menu_pages (
  page_file VARCHAR(120) NOT NULL,
  tela_nome VARCHAR(120) NOT NULL,
  tela_descricao VARCHAR(120) NOT NULL,
  tela_icone VARCHAR(80) NOT NULL,
  perm_slug VARCHAR(120) NOT NULL,
  perm_nome VARCHAR(120) NOT NULL,
  perm_descricao VARCHAR(190) NOT NULL,
  menu_label VARCHAR(120) NOT NULL,
  menu_link VARCHAR(120) NOT NULL,
  menu_sort INT NOT NULL,
  menu_icon VARCHAR(80) NOT NULL,
  menu_tag VARCHAR(120) NOT NULL
);

INSERT INTO tmp_dev_menu_pages
  (page_file, tela_nome, tela_descricao, tela_icone, perm_slug, perm_nome, perm_descricao, menu_label, menu_link, menu_sort, menu_icon, menu_tag)
VALUES
  (
    'dev-changelogs.php',
    'Changelogs da Equipe',
    'Changelogs autorais da equipe',
    'ti ti-notebook',
    'devwork.changelogs.view',
    'Changelogs da Equipe',
    'Acesso a tela de changelogs autorais da equipe tecnica e colaboradores internos.',
    'Meus Changelogs',
    'dev-changelogs',
    9,
    'lucide:notebook-pen',
    'dev_changelogs'
  ),
  (
    'github-minha-atividade.php',
    'Atividade GitHub',
    'Atividade GitHub do colaborador',
    'ti ti-brand-github',
    'devwork.github.activity.view',
    'Atividade GitHub',
    'Acesso a tela pessoal de atividade GitHub com commits, timeline e vinculo tecnico.',
    'Minha Atividade GitHub',
    'github-minha-atividade',
    10,
    'lucide:github',
    'github_minha_atividade'
  );

INSERT INTO wpjy_telas (arquivo, nome, descricao, icone, ativa, ajuda)
SELECT
  src.page_file,
  src.tela_nome,
  src.tela_descricao,
  src.tela_icone,
  1,
  NULL
FROM tmp_dev_menu_pages src
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_telas t
   WHERE LOWER(CONVERT(t.arquivo USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
         LOWER(CONVERT(src.page_file USING utf8mb4)) COLLATE utf8mb4_unicode_ci
);

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT
  src.perm_slug,
  src.perm_nome,
  src.perm_descricao,
  t.id,
  'tela'
FROM tmp_dev_menu_pages src
JOIN wpjy_telas t
  ON LOWER(CONVERT(t.arquivo USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
     LOWER(CONVERT(src.page_file USING utf8mb4)) COLLATE utf8mb4_unicode_ci
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_permissoes p
   WHERE LOWER(CONVERT(p.slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
         LOWER(CONVERT(src.perm_slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci
);

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT
  src.menu_label,
  src.menu_link,
  'adm',
  599,
  src.menu_sort,
  @menu_id,
  '_self',
  src.menu_icon,
  src.perm_slug,
  src.menu_tag,
  NULL
FROM tmp_dev_menu_pages src
WHERE NOT EXISTS (
  SELECT 1
  FROM wpjy_menus_items mi
   WHERE mi.menu = @menu_id
     AND mi.area = 'adm'
     AND (
       LOWER(CONVERT(mi.link USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
       LOWER(CONVERT(src.menu_link USING utf8mb4)) COLLATE utf8mb4_unicode_ci
       OR LOWER(CONVERT(mi.slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
          LOWER(CONVERT(src.perm_slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci
       OR LOWER(CONVERT(mi.tag USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
          LOWER(CONVERT(src.menu_tag USING utf8mb4)) COLLATE utf8mb4_unicode_ci
     )
);

UPDATE wpjy_menus_items mi
JOIN tmp_dev_menu_pages src
  ON LOWER(CONVERT(mi.link USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
     LOWER(CONVERT(src.menu_link USING utf8mb4)) COLLATE utf8mb4_unicode_ci
SET
  mi.parent = 599,
  mi.sort = src.menu_sort,
  mi.slug = src.perm_slug,
  mi.tag = src.menu_tag,
  mi.icon = src.menu_icon,
  mi.label = src.menu_label
WHERE mi.menu = @menu_id
  AND mi.area = 'adm';

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT g.id, p.id
  FROM wpjy_grupos_permissoes g
  JOIN tmp_dev_menu_pages src
    ON 1 = 1
  JOIN wpjy_permissoes p
    ON LOWER(CONVERT(p.slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci =
       LOWER(CONVERT(src.perm_slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci
 WHERE LOWER(CONVERT(g.slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci IN (
   'super_admin',
   'tiix',
   'dev-arquiteto',
   'dev-lead',
   'tech-lead',
   'people-ops-dev',
   'delivery-manager',
   'product-owner-dev',
   'dev-viewer',
   'dev-colaborador',
   'design-colaborador',
   'homologador'
 )
   AND NOT EXISTS (
     SELECT 1
       FROM wpjy_grupo_permissao gp
      WHERE gp.id_grupo = g.id
        AND gp.id_permissao = p.id
   );

COMMIT;

SELECT t.id, t.arquivo, t.nome
  FROM wpjy_telas t
 WHERE LOWER(CONVERT(t.arquivo USING utf8mb4)) COLLATE utf8mb4_unicode_ci IN (
   'dev-changelogs.php',
   'github-minha-atividade.php'
 )
 ORDER BY t.arquivo;

SELECT p.id, p.slug, p.nome, p.tipo
  FROM wpjy_permissoes p
 WHERE LOWER(CONVERT(p.slug USING utf8mb4)) COLLATE utf8mb4_unicode_ci IN (
   'devwork.changelogs.view',
   'devwork.github.activity.view'
 )
 ORDER BY p.slug;

SELECT mi.id, mi.label, mi.link, mi.parent, mi.sort, mi.slug, mi.tag
 FROM wpjy_menus_items mi
 WHERE mi.menu = @menu_id
   AND mi.area = 'adm'
   AND LOWER(CONVERT(mi.link USING utf8mb4)) COLLATE utf8mb4_unicode_ci IN (
     'dev-changelogs',
     'github-minha-atividade'
   )
 ORDER BY mi.sort;