safedelete-1.0.0/modules/safedelete_menu_report/src/Service/ReportService.php

<?php

namespace Drupal\safedelete_menu_report\Service;

use Drupal\Core\Cache\Cache;
use Drupal\Core\Cache\CacheBackendInterface;
use Drupal\Core\Database\Connection;
use Drupal\Core\Language\LanguageManagerInterface;

/**
 * Report service.
 */
final class ReportService {

  protected Connection $database;
  protected LanguageManagerInterface $languageManager;
  protected CacheBackendInterface $cache;

  public function __construct(Connection $database, LanguageManagerInterface $language_manager, CacheBackendInterface $cache) {
    $this->database = $database;
    $this->languageManager = $language_manager;
    $this->cache = $cache;
  }

  /**
   * Find published child nodes under parents whose menu link is disabled.
   *
   * Uses DBTNG with cross-DB CONCATs instead of SUBSTRING_INDEX/CAST.
   * Parents are content parents only (menu_link_content),
   * children are nodes pointed to by the child link__uri (entity:node/* or internal:/node/*).
   *
   * @param string $menu_name
   *   Menu machine name, e.g., 'main'.
   * @param string|null $langcode
   *   Language code; defaults to current if NULL.
   *
   * @return array<int, array<string, scalar|null>>
   *   Rows with keys:
   *     - child_nid
   *     - child_enabled
   *     - parent_nid
   *     - parent_type
   *     - parent_node_published
   *     - parent_enabled
   */
  public function findDisabledParentsWithPublishedChildren(string $menu_name = 'main', ?string $langcode = NULL): array {
    $menu_name = trim($menu_name);
    $langcode = $langcode ?: $this->languageManager->getCurrentLanguage()->getId();
    $cid = "safedelete.parents_children.$menu_name.$langcode";

    if ($cache = $this->cache->get($cid)) {
      return $cache->data;
    }

    $conn = $this->database;
    $driver = $conn->driver(); // 'mysql' | 'pgsql' | 'sqlite'

    $q = $conn->select('menu_link_content_data', 'c');

    // Keep the parent content entity join (cheap) — but *do not* touch node_field_data here.
    // Driver-specific concat for c.parent = 'menu_link_content:' . pbase.uuid
    if ($driver === 'mysql') {
      $parentPidCond = "c.parent = CONCAT(:p_prefix, pbase.uuid)";
    }
    elseif ($driver === 'pgsql') {
      $parentPidCond = "c.parent = :p_prefix || pbase.uuid";
    }
    elseif ($driver === 'sqlite') {
      $parentPidCond = "c.parent = :p_prefix || pbase.uuid";
    }
    else {
      throw new \RuntimeException('Unsupported database driver: ' . $driver);
    }

    $q->innerJoin('menu_link_content', 'pbase', $parentPidCond, [':p_prefix' => 'menu_link_content:']);
    $q->innerJoin('menu_link_content_data', 'pmdat', 'pmdat.id = pbase.id AND pmdat.langcode = c.langcode AND pmdat.enabled = 0');

    // Prefilter to node targets only (reduces rows a lot).
    $childUriGroup = $q->orConditionGroup()
      ->condition('c.link__uri', 'internal:/node/%', 'LIKE')
      ->condition('c.link__uri', 'entity:node/%', 'LIKE');
    $q->condition($childUriGroup);

    $parentUriGroup = $q->orConditionGroup()
      ->condition('pmdat.link__uri', 'internal:/node/%', 'LIKE')
      ->condition('pmdat.link__uri', 'entity:node/%', 'LIKE');
    $q->condition($parentUriGroup);

    // Minimal fields; we’ll parse NIDs in PHP then do tiny IN-queries to node_field_data.
    $q->addField('c', 'enabled', 'child_enabled');
    $q->addField('c', 'link__uri', 'child_uri');
    // Should be 0 by join.
    $q->addField('pmdat', 'enabled', 'parent_enabled');
    $q->addField('pmdat', 'link__uri', 'parent_uri');

    // Where.
    $q->isNotNull('c.enabled');
    $q->condition('c.menu_name', $menu_name);
    $q->condition('c.langcode', $langcode);

    $raw = $q->execute()->fetchAll(\PDO::FETCH_ASSOC);
    if (!$raw) {
      return [];
    }

    // Fast URI -> nid parsing.
    $parse_nid = static function (?string $uri): ?int {
      if (!$uri) {
        return NULL;
      }
      // Accept 'entity:node/<nid>' or 'internal:/node/<nid>'.
      $pos = strrpos($uri, '/');
      if ($pos === false) {
        return NULL;
      }
      $tail = substr($uri, $pos + 1);
      return ctype_digit($tail) ? (int) $tail : NULL;
    };

    $pairs = [];
    $child_nids = [];
    $parent_nids = [];

    foreach ($raw as $r) {
      $cn = $parse_nid($r['child_uri'] ?? NULL);
      $pn = $parse_nid($r['parent_uri'] ?? NULL);
      if (!$cn || !$pn) {
        continue;
      }
      $pairs[] = [$cn, (int) ($r['child_enabled'] ?? 0), $pn];
      $child_nids[$cn] = $cn;
      $parent_nids[$pn] = $pn;
    }

    if (!$pairs) {
      return [];
    }

    // Tiny IN-queries to node_field_data (fast, indexed on nid).
    // Children: get publication status only (we filter by this).
    $cq = $conn->select('node_field_data', 'n')
      ->fields('n', ['nid', 'status'])
      ->condition('n.nid', array_values($child_nids), 'IN')
      ->condition('n.langcode', $langcode);
    $child_status = $cq->execute()->fetchAllKeyed(); // nid => status

    // Parents: need nid, type, status.
    $pq = $conn->select('node_field_data', 'n')
      ->fields('n', ['nid', 'type', 'status'])
      ->condition('n.nid', array_values($parent_nids), 'IN')
      ->condition('n.langcode', $langcode);
    $parent_rows = $pq->execute()->fetchAll(\PDO::FETCH_ASSOC);
    $parent_map = [];
    foreach ($parent_rows as $row) {
      $parent_map[(int) $row['nid']] = [
        'type' => (string) $row['type'],
        'status' => (int) $row['status'],
      ];
    }

    // Build final rows in the exact shape expected by ReportHelper.
    $rows = [];
    foreach ($pairs as [$child_nid, $child_enabled, $parent_nid]) {
      // Only keep children that are published.
      if (empty($child_status[$child_nid])) {
        continue;
      }
      $pinfo = $parent_map[$parent_nid] ?? NULL;
      if (!$pinfo) {
        continue;
      }
      $rows[] = [
        'child_nid' => $child_nid,
        'child_enabled' => $child_enabled,
        'parent_nid' => $parent_nid,
        'parent_type' => $pinfo['type'],
        'parent_node_published' => $pinfo['status'],
        'parent_enabled' => 0,
      ];
    }

    // Invalidate when menu or nodes change.
    $tags = [
      "config:system.menu.$menu_name",
      'node_list',
      'menu_link_content_list',
    ];

    $expire = Cache::PERMANENT;
    $this->cache->set($cid, $rows, $expire, $tags);
    return $rows;
  }

}