-- 2026-04-16
-- Diagnostico de orfaos em permissoes, telas e menus
-- Uso:
-- 1) rode em homologacao ou em um dump importado
-- 2) revise o resumo
-- 3) use os blocos detalhados para decidir o que limpar
--
-- Observacoes:
-- - este arquivo nao altera dados
-- - o match entre menu e tela tenta ser tolerante com legado:
--   primeiro por rota exata e depois por basename do link/arquivo

SET @menu_admin_id := 35;

-- =========================================================
-- RESUMO
-- =========================================================
SELECT
  (SELECT COUNT(*)
     FROM wpjy_permissoes p
     LEFT JOIN wpjy_telas t ON t.id = p.id_tela
    WHERE p.id_tela IS NOT NULL
      AND t.id IS NULL) AS permissoes_com_id_tela_inexistente,

  (SELECT COUNT(*)
     FROM wpjy_permissoes p
     LEFT JOIN wpjy_menus_items m ON m.slug = p.slug
    WHERE p.tipo = 'menu'
      AND m.id IS NULL) AS permissoes_de_menu_sem_item_menu,

  (SELECT COUNT(*)
     FROM wpjy_permissoes p
     LEFT JOIN wpjy_grupo_permissao gp ON gp.id_permissao = p.id
    WHERE gp.id_permissao IS NULL) AS permissoes_sem_grupo,

  (SELECT COUNT(*)
     FROM wpjy_telas t
     LEFT JOIN wpjy_permissoes p ON p.id_tela = t.id
    WHERE p.id IS NULL) AS telas_sem_permissoes,

  (SELECT COUNT(*)
     FROM wpjy_menus_items m
     LEFT JOIN wpjy_permissoes p ON p.slug = m.slug
    WHERE COALESCE(m.link, '') NOT IN ('', '#', 'heading')
      AND COALESCE(m.link, '') NOT REGEXP '^(https?:)?//'
      AND COALESCE(m.link, '') NOT LIKE 'javascript:%'
      AND (COALESCE(m.slug, '') = '' OR p.id IS NULL)) AS menus_sem_slug_ou_sem_permissao;

-- =========================================================
-- 1) PERMISSOES COM id_tela APONTANDO PARA TELA INEXISTENTE
-- =========================================================
SELECT
  p.id,
  p.slug,
  p.nome,
  p.tipo,
  p.id_tela
FROM wpjy_permissoes p
LEFT JOIN wpjy_telas t
  ON t.id = p.id_tela
WHERE p.id_tela IS NOT NULL
  AND t.id IS NULL
ORDER BY p.id;

-- =========================================================
-- 2) PERMISSOES DE MENU SEM ITEM DE MENU CORRESPONDENTE
-- =========================================================
SELECT
  p.id,
  p.slug,
  p.nome,
  p.descricao,
  p.tipo
FROM wpjy_permissoes p
LEFT JOIN wpjy_menus_items m
  ON m.slug = p.slug
WHERE p.tipo = 'menu'
  AND m.id IS NULL
ORDER BY p.slug;

-- =========================================================
-- 3) PERMISSOES SEM VINCULO COM NENHUM GRUPO
-- =========================================================
SELECT
  p.id,
  p.slug,
  p.nome,
  p.tipo,
  p.id_tela
FROM wpjy_permissoes p
LEFT JOIN wpjy_grupo_permissao gp
  ON gp.id_permissao = p.id
WHERE gp.id_permissao IS NULL
ORDER BY p.tipo, p.slug;

-- =========================================================
-- 4) MENUS SEM SLUG OU SEM PERMISSAO CADASTRADA
-- =========================================================
SELECT
  m.id,
  m.menu,
  m.parent,
  m.area,
  m.label,
  m.link,
  m.slug,
  CASE
    WHEN COALESCE(m.slug, '') = '' THEN 'menu_sem_slug'
    WHEN p.id IS NULL THEN 'slug_sem_permissao'
    ELSE 'ok'
  END AS diagnostico
FROM wpjy_menus_items m
LEFT JOIN wpjy_permissoes p
  ON p.slug = m.slug
WHERE COALESCE(m.link, '') NOT IN ('', '#', 'heading')
  AND COALESCE(m.link, '') NOT REGEXP '^(https?:)?//'
  AND COALESCE(m.link, '') NOT LIKE 'javascript:%'
  AND (
    COALESCE(m.slug, '') = ''
    OR p.id IS NULL
  )
ORDER BY m.menu, m.parent, m.sort, m.id;

-- =========================================================
-- 5) MENUS SEM TELA CADASTRADA
--    Match por rota exata ou, no legado, por basename.
-- =========================================================
WITH telas_norm AS (
  SELECT
    t.id,
    t.nome,
    t.arquivo,
    LOWER(
      REPLACE(
        TRIM(BOTH '/' FROM
          CASE
            WHEN t.arquivo LIKE 'pages/%' THEN SUBSTRING(t.arquivo, 7)
            ELSE t.arquivo
          END
        ),
        '.php',
        ''
      )
    ) AS tela_rota,
    LOWER(
      REPLACE(
        SUBSTRING_INDEX(
          TRIM(BOTH '/' FROM
            CASE
              WHEN t.arquivo LIKE 'pages/%' THEN SUBSTRING(t.arquivo, 7)
              ELSE t.arquivo
            END
          ),
          '/',
          -1
        ),
        '.php',
        ''
      )
    ) AS tela_base
  FROM wpjy_telas t
),
menus_norm AS (
  SELECT
    m.id,
    m.menu,
    m.parent,
    m.area,
    m.label,
    m.link,
    m.slug,
    LOWER(
      TRIM(BOTH '/' FROM
        CASE
          WHEN COALESCE(m.area, '') <> ''
           AND m.link NOT LIKE '%/%'
           AND m.link NOT LIKE '%.php'
            THEN CONCAT(m.area, '/', m.link)
          WHEN COALESCE(m.area, '') <> ''
           AND m.link NOT LIKE '%/%'
           AND m.link LIKE '%.php'
            THEN CONCAT(m.area, '/', REPLACE(m.link, '.php', ''))
          ELSE REPLACE(m.link, '.php', '')
        END
      )
    ) AS menu_rota,
    LOWER(REPLACE(TRIM(BOTH '/' FROM m.link), '.php', '')) AS menu_base
  FROM wpjy_menus_items m
  WHERE COALESCE(m.link, '') NOT IN ('', '#', 'heading')
    AND COALESCE(m.link, '') NOT REGEXP '^(https?:)?//'
    AND COALESCE(m.link, '') NOT LIKE 'javascript:%'
)
SELECT
  m.id,
  m.menu,
  m.parent,
  m.area,
  m.label,
  m.link,
  m.slug,
  m.menu_rota
FROM menus_norm m
WHERE NOT EXISTS (
  SELECT 1
  FROM telas_norm t
  WHERE t.tela_rota = m.menu_rota
     OR t.tela_base = m.menu_base
)
ORDER BY m.menu, m.parent, m.id;

-- =========================================================
-- 6) TELAS SEM ITEM DE MENU
--    Ajuda a identificar telas abandonadas ou nao expostas.
-- =========================================================
WITH telas_norm AS (
  SELECT
    t.id,
    t.nome,
    t.arquivo,
    LOWER(
      REPLACE(
        TRIM(BOTH '/' FROM
          CASE
            WHEN t.arquivo LIKE 'pages/%' THEN SUBSTRING(t.arquivo, 7)
            ELSE t.arquivo
          END
        ),
        '.php',
        ''
      )
    ) AS tela_rota,
    LOWER(
      REPLACE(
        SUBSTRING_INDEX(
          TRIM(BOTH '/' FROM
            CASE
              WHEN t.arquivo LIKE 'pages/%' THEN SUBSTRING(t.arquivo, 7)
              ELSE t.arquivo
            END
          ),
          '/',
          -1
        ),
        '.php',
        ''
      )
    ) AS tela_base
  FROM wpjy_telas t
),
menus_norm AS (
  SELECT
    m.id,
    m.menu,
    m.area,
    m.link,
    LOWER(
      TRIM(BOTH '/' FROM
        CASE
          WHEN COALESCE(m.area, '') <> ''
           AND m.link NOT LIKE '%/%'
           AND m.link NOT LIKE '%.php'
            THEN CONCAT(m.area, '/', m.link)
          WHEN COALESCE(m.area, '') <> ''
           AND m.link NOT LIKE '%/%'
           AND m.link LIKE '%.php'
            THEN CONCAT(m.area, '/', REPLACE(m.link, '.php', ''))
          ELSE REPLACE(m.link, '.php', '')
        END
      )
    ) AS menu_rota,
    LOWER(REPLACE(TRIM(BOTH '/' FROM m.link), '.php', '')) AS menu_base
  FROM wpjy_menus_items m
  WHERE COALESCE(m.link, '') NOT IN ('', '#', 'heading')
    AND COALESCE(m.link, '') NOT REGEXP '^(https?:)?//'
    AND COALESCE(m.link, '') NOT LIKE 'javascript:%'
)
SELECT
  t.id,
  t.nome,
  t.arquivo
FROM telas_norm t
WHERE NOT EXISTS (
  SELECT 1
  FROM menus_norm m
  WHERE m.menu_rota = t.tela_rota
     OR m.menu_base = t.tela_base
)
ORDER BY t.arquivo, t.id;

-- =========================================================
-- 7) TELAS SEM PERMISSOES
-- =========================================================
SELECT
  t.id,
  t.nome,
  t.arquivo,
  t.ativa
FROM wpjy_telas t
LEFT JOIN wpjy_permissoes p
  ON p.id_tela = t.id
WHERE p.id IS NULL
ORDER BY t.arquivo, t.id;

-- =========================================================
-- 8) ITENS DE MENU DUPLICADOS POR AREA + LINK
-- =========================================================
SELECT
  COALESCE(area, '') AS area,
  link,
  COUNT(*) AS total,
  GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS ids,
  GROUP_CONCAT(label ORDER BY id SEPARATOR ' | ') AS labels
FROM wpjy_menus_items
WHERE COALESCE(link, '') NOT IN ('', '#', 'heading')
  AND COALESCE(link, '') NOT REGEXP '^(https?:)?//'
  AND COALESCE(link, '') NOT LIKE 'javascript:%'
GROUP BY COALESCE(area, ''), link
HAVING COUNT(*) > 1
ORDER BY total DESC, area, link;

-- =========================================================
-- 9) SLUGS DUPLICADOS EM PERMISSOES
-- =========================================================
SELECT
  slug,
  COUNT(*) AS total,
  GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS ids
FROM wpjy_permissoes
GROUP BY slug
HAVING COUNT(*) > 1
ORDER BY total DESC, slug;

-- =========================================================
-- 10) RECORTE RAPIDO DO MENU ADMIN
--     Ajuste @menu_admin_id se quiser outro menu.
-- =========================================================
SELECT
  m.id,
  m.parent,
  m.area,
  m.sort,
  m.label,
  m.link,
  m.slug,
  p.id AS permissao_id,
  p.tipo AS permissao_tipo
FROM wpjy_menus_items m
LEFT JOIN wpjy_permissoes p
  ON p.slug = m.slug
WHERE m.menu = @menu_admin_id
ORDER BY m.parent, m.sort, m.id;