Backup #315

Dateiinhalt

<?php

namespace Controller;

use Framework\Controller;
use Infrastructure\Config\DatabaseFactory;
use Infrastructure\Docs\HybridSearchService;

/**
 * SystemExplorerController - Interne System-Dokumentation
 *
 * Zeigt Code-Dokumentation, Seiten und Chunks aus der dokumentation_chunks Pipeline.
 * Für Admin/Entwickler - System-Wissen.
 */
class SystemExplorerController extends Controller
{
    private \PDO $db;

    public function __construct()
    {
        $this->db = DatabaseFactory::dev();
    }

    /**
     * GET /explorer
     * Dashboard mit Statistiken
     */
    public function index(): void
    {
        // Dokumente (depth=0) = Hauptbereiche
        $dokumenteCount = $this->db->query(
            'SELECT COUNT(*) FROM dokumentation WHERE depth = 0'
        )->fetchColumn();

        // Seiten (depth>0) = Unterseiten
        $seitenCount = $this->db->query(
            'SELECT COUNT(*) FROM dokumentation WHERE depth > 0'
        )->fetchColumn();

        // Chunk-Statistiken
        $chunkStats = $this->db->query(
            'SELECT
                COUNT(*) as total,
                COALESCE(SUM(token_count), 0) as tokens,
                SUM(CASE WHEN analysis_status = "completed" THEN 1 ELSE 0 END) as analyzed,
                SUM(CASE WHEN qdrant_id IS NOT NULL THEN 1 ELSE 0 END) as synced
             FROM dokumentation_chunks'
        )->fetch();

        // Taxonomie-Kategorien
        $taxonomyCategories = $this->db->query(
            'SELECT taxonomy_category, COUNT(*) as count
             FROM dokumentation_chunks
             WHERE taxonomy_category IS NOT NULL
             GROUP BY taxonomy_category
             ORDER BY count DESC
             LIMIT 10'
        )->fetchAll();

        // Dokumente (Hauptbereiche)
        $dokumente = $this->db->query(
            'SELECT d.id, d.title, d.path,
                    (SELECT COUNT(*) FROM dokumentation WHERE parent_id = d.id) as seiten_count,
                    (SELECT COUNT(*) FROM dokumentation_chunks WHERE dokumentation_id = d.id) as chunks_count
             FROM dokumentation d
             WHERE d.depth = 0
             ORDER BY d.title'
        )->fetchAll();

        // Neueste Chunks
        $recentChunks = $this->db->query(
            'SELECT c.id, c.content, c.taxonomy_category, c.token_count, c.created_at,
                    d.title as dokument_title, d.path as dokument_path
             FROM dokumentation_chunks c
             JOIN dokumentation d ON c.dokumentation_id = d.id
             ORDER BY c.created_at DESC
             LIMIT 5'
        )->fetchAll();

        $this->view('system-explorer.index', [
            'title' => 'Doc2Vector Explorer',
            'dokumenteCount' => $dokumenteCount,
            'seitenCount' => $seitenCount,
            'chunkStats' => $chunkStats,
            'taxonomyCategories' => $taxonomyCategories,
            'dokumente' => $dokumente,
            'recentChunks' => $recentChunks,
        ]);
    }

    /**
     * GET /explorer/dokumente
     * Liste aller Hauptbereiche (depth=0)
     */
    public function dokumente(): void
    {
        $dokumente = $this->db->query(
            'SELECT d.id, d.title, d.path, d.content, d.created_at, d.updated_at,
                    (SELECT COUNT(*) FROM dokumentation WHERE parent_id = d.id) as seiten_count,
                    (SELECT COUNT(*) FROM dokumentation_chunks WHERE dokumentation_id IN
                        (SELECT id FROM dokumentation WHERE parent_id = d.id OR id = d.id)) as total_chunks,
                    (SELECT COALESCE(SUM(token_count), 0) FROM dokumentation_chunks WHERE dokumentation_id IN
                        (SELECT id FROM dokumentation WHERE parent_id = d.id OR id = d.id)) as total_tokens
             FROM dokumentation d
             WHERE d.depth = 0
             ORDER BY d.title'
        )->fetchAll();

        $this->view('system-explorer.dokumente.index', [
            'title' => 'Dokumente',
            'dokumente' => $dokumente,
        ]);
    }

    /**
     * GET /explorer/dokumente/{id}
     * Dokument-Details mit Seiten und Chunks
     */
    public function dokumentShow(int $id): void
    {
        $stmt = $this->db->prepare(
            'SELECT * FROM dokumentation WHERE id = :id AND depth = 0'
        );
        $stmt->execute(['id' => $id]);
        $dokument = $stmt->fetch();

        if ($dokument === false) {
            $this->notFound('Dokument nicht gefunden');
        }

        // Direkte Seiten (depth=1)
        $stmt = $this->db->prepare(
            'SELECT s.id, s.title, s.path, s.depth,
                    (SELECT COUNT(*) FROM dokumentation_chunks WHERE dokumentation_id = s.id) as chunks_count,
                    (SELECT COALESCE(SUM(token_count), 0) FROM dokumentation_chunks WHERE dokumentation_id = s.id) as token_count
             FROM dokumentation s
             WHERE s.parent_id = :id
             ORDER BY s.title'
        );
        $stmt->execute(['id' => $id]);
        $seiten = $stmt->fetchAll();

        // Chunks direkt am Dokument
        $stmt = $this->db->prepare(
            'SELECT c.id, c.chunk_index, c.content, c.token_count, c.taxonomy_category,
                    c.entities, c.keywords, c.analysis_status, c.qdrant_id
             FROM dokumentation_chunks c
             WHERE c.dokumentation_id = :id
             ORDER BY c.chunk_index'
        );
        $stmt->execute(['id' => $id]);
        $chunks = $stmt->fetchAll();

        // Taxonomie-Aggregation für dieses Dokument (inkl. Seiten)
        $stmt = $this->db->prepare(
            'SELECT taxonomy_category, COUNT(*) as count
             FROM dokumentation_chunks
             WHERE dokumentation_id IN (
                 SELECT id FROM dokumentation WHERE id = :id OR parent_id = :id2
             )
             AND taxonomy_category IS NOT NULL
             GROUP BY taxonomy_category
             ORDER BY count DESC'
        );
        $stmt->execute(['id' => $id, 'id2' => $id]);
        $taxonomy = $stmt->fetchAll();

        $this->view('system-explorer.dokumente.show', [
            'title' => $dokument['title'],
            'dokument' => $dokument,
            'seiten' => $seiten,
            'chunks' => $chunks,
            'taxonomy' => $taxonomy,
        ]);
    }

    /**
     * GET /explorer/seiten
     * Liste aller Seiten (depth>0)
     */
    public function seiten(): void
    {
        $search = $this->getString('search');
        $parentId = $this->getString('parent');

        $sql = 'SELECT s.id, s.title, s.path, s.depth, s.parent_id,
                       p.title as parent_title,
                       (SELECT COUNT(*) FROM dokumentation_chunks WHERE dokumentation_id = s.id) as chunks_count,
                       (SELECT COALESCE(SUM(token_count), 0) FROM dokumentation_chunks WHERE dokumentation_id = s.id) as token_count
                FROM dokumentation s
                LEFT JOIN dokumentation p ON s.parent_id = p.id
                WHERE s.depth > 0';

        $params = [];

        if ($search !== '') {
            $sql .= ' AND (s.title LIKE :search OR s.path LIKE :search2)';
            $params['search'] = '%' . $search . '%';
            $params['search2'] = '%' . $search . '%';
        }

        if ($parentId !== '') {
            $sql .= ' AND s.parent_id = :parent';
            $params['parent'] = (int) $parentId;
        }

        $sql .= ' ORDER BY p.title, s.depth, s.title';

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $seiten = $stmt->fetchAll();

        // Dokumente für Filter
        $dokumente = $this->db->query(
            'SELECT id, title FROM dokumentation WHERE depth = 0 ORDER BY title'
        )->fetchAll();

        $this->view('system-explorer.seiten.index', [
            'title' => 'Seiten',
            'seiten' => $seiten,
            'dokumente' => $dokumente,
            'currentSearch' => $search,
            'currentParent' => $parentId,
        ]);
    }

    /**
     * GET /explorer/seiten/{id}
     * Seiten-Details mit Chunks
     */
    public function seiteShow(int $id): void
    {
        $stmt = $this->db->prepare(
            'SELECT s.*, p.title as parent_title, p.path as parent_path
             FROM dokumentation s
             LEFT JOIN dokumentation p ON s.parent_id = p.id
             WHERE s.id = :id'
        );
        $stmt->execute(['id' => $id]);
        $seite = $stmt->fetch();

        if ($seite === false) {
            $this->notFound('Seite nicht gefunden');
        }

        // Chunks dieser Seite
        $stmt = $this->db->prepare(
            'SELECT c.id, c.chunk_index, c.content, c.token_count, c.taxonomy_category,
                    c.taxonomy_path, c.entities, c.keywords, c.analysis_status, c.qdrant_id,
                    c.heading_path, c.analyzed_at
             FROM dokumentation_chunks c
             WHERE c.dokumentation_id = :id
             ORDER BY c.chunk_index'
        );
        $stmt->execute(['id' => $id]);
        $chunks = $stmt->fetchAll();

        // Unterseiten (falls depth=1)
        $stmt = $this->db->prepare(
            'SELECT id, title, path, depth FROM dokumentation WHERE parent_id = :id ORDER BY title'
        );
        $stmt->execute(['id' => $id]);
        $unterseiten = $stmt->fetchAll();

        // Breadcrumb aufbauen
        $breadcrumb = $this->buildBreadcrumb($seite);

        $this->view('system-explorer.seiten.show', [
            'title' => $seite['title'],
            'seite' => $seite,
            'chunks' => $chunks,
            'unterseiten' => $unterseiten,
            'breadcrumb' => $breadcrumb,
        ]);
    }

    /**
     * GET /explorer/chunks
     * Liste aller Chunks mit Filtern
     */
    public function chunks(): void
    {
        $category = $_GET['category'] ?? '';
        $status = $_GET['status'] ?? '';
        $search = $_GET['search'] ?? '';
        $page = max(1, (int) ($_GET['page'] ?? 1));
        $limit = 50;
        $offset = ($page - 1) * $limit;

        $sql = 'SELECT c.id, c.chunk_index, c.content, c.token_count, c.taxonomy_category,
                       c.analysis_status, c.qdrant_id, c.created_at,
                       d.title as dokument_title, d.path as dokument_path
                FROM dokumentation_chunks c
                JOIN dokumentation d ON c.dokumentation_id = d.id
                WHERE 1=1';

        $countSql = 'SELECT COUNT(*) FROM dokumentation_chunks c
                     JOIN dokumentation d ON c.dokumentation_id = d.id
                     WHERE 1=1';

        $params = [];

        if ($category !== '') {
            $sql .= ' AND c.taxonomy_category = :category';
            $countSql .= ' AND c.taxonomy_category = :category';
            $params['category'] = $category;
        }

        if ($status !== '') {
            $sql .= ' AND c.analysis_status = :status';
            $countSql .= ' AND c.analysis_status = :status';
            $params['status'] = $status;
        }

        if ($search !== '') {
            $sql .= ' AND (c.content LIKE :search OR c.keywords LIKE :search2)';
            $countSql .= ' AND (c.content LIKE :search OR c.keywords LIKE :search2)';
            $params['search'] = '%' . $search . '%';
            $params['search2'] = '%' . $search . '%';
        }

        // Count total
        $countStmt = $this->db->prepare($countSql);
        $countStmt->execute($params);
        $totalCount = $countStmt->fetchColumn();

        $sql .= ' ORDER BY c.created_at DESC LIMIT :limit OFFSET :offset';

        $stmt = $this->db->prepare($sql);
        foreach ($params as $key => $value) {
            $stmt->bindValue(':' . $key, $value);
        }
        $stmt->bindValue(':limit', $limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, \PDO::PARAM_INT);
        $stmt->execute();
        $chunks = $stmt->fetchAll();

        // Kategorien für Filter
        $categories = $this->db->query(
            'SELECT DISTINCT taxonomy_category FROM dokumentation_chunks
             WHERE taxonomy_category IS NOT NULL ORDER BY taxonomy_category'
        )->fetchAll(\PDO::FETCH_COLUMN);

        $this->view('system-explorer.chunks.index', [
            'title' => 'Chunks',
            'chunks' => $chunks,
            'categories' => $categories,
            'currentCategory' => $category,
            'currentStatus' => $status,
            'currentSearch' => $search,
            'currentPage' => $page,
            'totalCount' => $totalCount,
            'totalPages' => ceil($totalCount / $limit),
        ]);
    }

    /**
     * GET /explorer/chunks/{id}
     * Chunk-Details
     */
    public function chunkShow(int $id): void
    {
        $stmt = $this->db->prepare(
            'SELECT c.*, d.title as dokument_title, d.path as dokument_path, d.id as dokument_id
             FROM dokumentation_chunks c
             JOIN dokumentation d ON c.dokumentation_id = d.id
             WHERE c.id = :id'
        );
        $stmt->execute(['id' => $id]);
        $chunk = $stmt->fetch();

        if ($chunk === false) {
            $this->notFound('Chunk nicht gefunden');
        }

        // JSON-Felder dekodieren
        $chunk['entities_decoded'] = $this->decodeJson($chunk['entities'] ?? null);
        $chunk['keywords_decoded'] = $this->decodeJson($chunk['keywords'] ?? null);
        $chunk['taxonomy_path_decoded'] = $this->decodeJson($chunk['taxonomy_path'] ?? null);
        $chunk['heading_path_decoded'] = $this->decodeJson($chunk['heading_path'] ?? null);

        // Nachbar-Chunks
        $stmt = $this->db->prepare(
            'SELECT id, chunk_index FROM dokumentation_chunks
             WHERE dokumentation_id = :doc_id AND chunk_index = :prev'
        );
        $stmt->execute(['doc_id' => $chunk['dokumentation_id'], 'prev' => $chunk['chunk_index'] - 1]);
        $prevChunk = $stmt->fetch();

        $stmt = $this->db->prepare(
            'SELECT id, chunk_index FROM dokumentation_chunks
             WHERE dokumentation_id = :doc_id AND chunk_index = :next'
        );
        $stmt->execute(['doc_id' => $chunk['dokumentation_id'], 'next' => $chunk['chunk_index'] + 1]);
        $nextChunk = $stmt->fetch();

        $this->view('system-explorer.chunks.show', [
            'title' => 'Chunk #' . $chunk['id'],
            'chunk' => $chunk,
            'prevChunk' => $prevChunk,
            'nextChunk' => $nextChunk,
        ]);
    }

    /**
     * GET /explorer/taxonomie
     * Taxonomie-Übersicht
     */
    public function taxonomie(): void
    {
        // Kategorien mit Counts
        $categories = $this->db->query(
            'SELECT taxonomy_category, COUNT(*) as chunk_count,
                    COALESCE(SUM(token_count), 0) as token_count
             FROM dokumentation_chunks
             WHERE taxonomy_category IS NOT NULL
             GROUP BY taxonomy_category
             ORDER BY chunk_count DESC'
        )->fetchAll();

        // Top Keywords aggregiert
        $keywordsRaw = $this->db->query(
            'SELECT keywords FROM dokumentation_chunks WHERE keywords IS NOT NULL'
        )->fetchAll(\PDO::FETCH_COLUMN);

        $keywordCounts = [];
        foreach ($keywordsRaw as $json) {
            $keywords = $this->decodeJson($json);
            foreach ($keywords as $kw) {
                $kw = strtolower(trim($kw));
                if ($kw !== '') {
                    $keywordCounts[$kw] = ($keywordCounts[$kw] ?? 0) + 1;
                }
            }
        }
        arsort($keywordCounts);
        $topKeywords = array_slice($keywordCounts, 0, 30, true);

        // Entities aggregiert
        $entitiesRaw = $this->db->query(
            'SELECT entities FROM dokumentation_chunks WHERE entities IS NOT NULL'
        )->fetchAll(\PDO::FETCH_COLUMN);

        $entityCounts = [];
        foreach ($entitiesRaw as $json) {
            $entities = $this->decodeJson($json);
            foreach ($entities as $entity) {
                $name = $entity['name'] ?? '';
                $type = $entity['type'] ?? 'OTHER';
                if ($name !== '') {
                    $key = $name . '|' . $type;
                    $entityCounts[$key] = ($entityCounts[$key] ?? 0) + 1;
                }
            }
        }
        arsort($entityCounts);
        $topEntities = array_slice($entityCounts, 0, 30, true);

        $this->view('system-explorer.taxonomie', [
            'title' => 'Taxonomie & Entities',
            'categories' => $categories,
            'topKeywords' => $topKeywords,
            'topEntities' => $topEntities,
        ]);
    }

    /**
     * GET /explorer/suche
     * Suche-Formular
     */
    public function suche(): void
    {
        $query = $_GET['q'] ?? '';
        $category = $_GET['category'] ?? '';
        $limit = min(20, max(1, (int) ($_GET['limit'] ?? 10)));

        $results = [];
        $suggestions = [];

        if ($query !== '') {
            $filters = [];
            if ($category !== '') {
                $filters['taxonomy_category'] = $category;
            }

            $search = new HybridSearchService();
            $results = $search->search($query, $filters, $limit);
            $suggestions = $search->suggestRelatedSearches($results);
        }

        // Kategorien für Filter
        $categories = $this->db->query(
            'SELECT DISTINCT taxonomy_category FROM dokumentation_chunks
             WHERE taxonomy_category IS NOT NULL ORDER BY taxonomy_category'
        )->fetchAll(\PDO::FETCH_COLUMN);

        $this->view('system-explorer.suche', [
            'title' => 'Dokumentation durchsuchen',
            'query' => $query,
            'results' => $results,

... (36 weitere Zeilen)

Vollständig herunterladen

Aktionen

Andere Versionen dieser Datei

← Zurück zur Übersicht