{
"tool_response": {
"type": "update",
"filePath": "\/var\/www\/dev.campus.systemische-tools.de\/src\/Controller\/SemanticExplorerController.php",
"content": "<?php\n\nnamespace Controller;\n\nuse Framework\\Controller;\nuse Infrastructure\\SemanticExplorerRepository;\n\n\/**\n * SemanticExplorerController - Nutzdaten Explorer\n *\n * Zeigt Dokumente und Chunks aus Nextcloud (documents, chunks Tabellen).\n * Für Endnutzer - Coaching-Materialien, PDFs, später Mails.\n *\/\nclass SemanticExplorerController extends Controller\n{\n private SemanticExplorerRepository $repository;\n\n public function __construct()\n {\n $this->repository = new SemanticExplorerRepository();\n }\n\n \/**\n * GET \/semantic-explorer\n * Dashboard mit Statistiken\n *\/\n public function index(): void\n {\n $docStats = $this->repository->getDocumentStats();\n $chunkStats = $this->repository->getChunkStats();\n $documents = $this->repository->getDocuments();\n $recentChunks = $this->repository->getRecentChunks(5);\n\n $this->view('semantic-explorer.index', [\n 'title' => 'Semantic Explorer',\n 'docStats' => $docStats,\n 'chunkStats' => $chunkStats,\n 'documents' => $documents,\n 'recentChunks' => $recentChunks,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/dokumente\n * Liste aller Dokumente\n *\/\n public function dokumente(): void\n {\n $status = $_GET['status'] ?? '';\n $search = $_GET['search'] ?? '';\n\n $documents = $this->repository->getDocumentsFiltered($status, $search);\n\n $this->view('semantic-explorer.dokumente.index', [\n 'title' => 'Dokumente',\n 'documents' => $documents,\n 'currentStatus' => $status,\n 'currentSearch' => $search,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/dokumente\/{id}\n * Dokument-Details mit Chunks\n *\/\n public function dokumentShow(int $id): void\n {\n $document = $this->repository->getDocument($id);\n\n if ($document === null) {\n http_response_code(404);\n echo '404 - Dokument nicht gefunden';\n\n return;\n }\n\n $chunks = $this->repository->getChunksForDocument($id);\n\n \/\/ Heading-Paths dekodieren\n foreach ($chunks as &$chunk) {\n $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];\n $chunk['metadata_decoded'] = json_decode($chunk['metadata'] ?? '{}', true) ?: [];\n }\n\n $this->view('semantic-explorer.dokumente.show', [\n 'title' => $document['filename'],\n 'document' => $document,\n 'chunks' => $chunks,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/chunks\n * Liste aller Chunks\n *\/\n public function chunks(): void\n {\n $search = $_GET['search'] ?? '';\n $embedded = $_GET['embedded'] ?? '';\n $page = max(1, (int) ($_GET['page'] ?? 1));\n $limit = 50;\n $offset = ($page - 1) * $limit;\n\n $totalCount = $this->repository->getChunksCount($search, $embedded);\n $chunks = $this->repository->getChunksFiltered($search, $embedded, $limit, $offset);\n\n $this->view('semantic-explorer.chunks.index', [\n 'title' => 'Chunks',\n 'chunks' => $chunks,\n 'currentSearch' => $search,\n 'currentEmbedded' => $embedded,\n 'currentPage' => $page,\n 'totalCount' => $totalCount,\n 'totalPages' => ceil($totalCount \/ $limit),\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/chunks\/{id}\n * Chunk-Details\n *\/\n public function chunkShow(int $id): void\n {\n $chunk = $this->repository->getChunk($id);\n\n if ($chunk === null) {\n http_response_code(404);\n echo '404 - Chunk nicht gefunden';\n\n return;\n }\n\n \/\/ JSON-Felder dekodieren\n $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];\n $chunk['metadata_decoded'] = json_decode($chunk['metadata'] ?? '{}', true) ?: [];\n\n \/\/ Nachbar-Chunks\n $prevChunk = $this->repository->getChunkByDocumentAndIndex(\n $chunk['document_id'],\n $chunk['chunk_index'] - 1\n );\n $nextChunk = $this->repository->getChunkByDocumentAndIndex(\n $chunk['document_id'],\n $chunk['chunk_index'] + 1\n );\n\n $this->view('semantic-explorer.chunks.show', [\n 'title' => 'Chunk #' . $chunk['id'],\n 'chunk' => $chunk,\n 'prevChunk' => $prevChunk,\n 'nextChunk' => $nextChunk,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/suche\n * Semantische Suche in Nutzdaten\n *\/\n public function suche(): void\n {\n $query = $_GET['q'] ?? '';\n $limit = min(20, max(1, (int) ($_GET['limit'] ?? 10)));\n\n $results = [];\n\n if ($query !== '') {\n \/\/ Vektor-Suche via Qdrant\n $results = $this->vectorSearch($query, $limit);\n }\n\n $this->view('semantic-explorer.suche', [\n 'title' => 'Semantische Suche',\n 'query' => $query,\n 'results' => $results,\n 'limit' => $limit,\n ]);\n }\n\n \/**\n * Vektor-Suche in documents Collection\n *\/\n private function vectorSearch(string $query, int $limit): array\n {\n \/\/ Embedding generieren\n $embedding = $this->getEmbedding($query);\n if (empty($embedding)) {\n return [];\n }\n\n \/\/ Qdrant suchen\n $response = $this->qdrantSearch($embedding, $limit);\n if (empty($response)) {\n return [];\n }\n\n \/\/ Chunk-Details aus DB laden\n $results = [];\n foreach ($response as $point) {\n $chunkId = $point['payload']['chunk_id'] ?? null;\n if ($chunkId === null) {\n continue;\n }\n\n $chunk = $this->repository->getChunkById($chunkId);\n\n if ($chunk !== null) {\n $chunk['score'] = $point['score'];\n $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];\n $results[] = $chunk;\n }\n }\n\n return $results;\n }\n\n \/**\n * Embedding via Ollama\n *\/\n private function getEmbedding(string $text): array\n {\n $ch = curl_init('http:\/\/localhost:11434\/api\/embeddings');\n curl_setopt_array($ch, [\n CURLOPT_RETURNTRANSFER => true,\n CURLOPT_POST => true,\n CURLOPT_HTTPHEADER => ['Content-Type: application\/json'],\n CURLOPT_POSTFIELDS => json_encode([\n 'model' => 'mxbai-embed-large',\n 'prompt' => $text,\n ]),\n ]);\n\n $response = curl_exec($ch);\n curl_close($ch);\n\n $data = json_decode($response, true);\n\n return $data['embedding'] ?? [];\n }\n\n \/**\n * Qdrant-Suche\n *\/\n private function qdrantSearch(array $embedding, int $limit): array\n {\n $ch = curl_init('http:\/\/localhost:6333\/collections\/documents\/points\/search');\n curl_setopt_array($ch, [\n CURLOPT_RETURNTRANSFER => true,\n CURLOPT_POST => true,\n CURLOPT_HTTPHEADER => ['Content-Type: application\/json'],\n CURLOPT_POSTFIELDS => json_encode([\n 'vector' => $embedding,\n 'limit' => $limit,\n 'with_payload' => true,\n ]),\n ]);\n\n $response = curl_exec($ch);\n curl_close($ch);\n\n $data = json_decode($response, true);\n\n return $data['result'] ?? [];\n }\n\n \/**\n * GET \/semantic-explorer\/entitaeten\n * Liste aller Entitaeten\n *\/\n public function entitaeten(): void\n {\n $type = $_GET['type'] ?? '';\n $search = $_GET['search'] ?? '';\n\n $entities = $this->repository->getEntitiesFiltered($type, $search);\n $stats = $this->repository->getEntityStats();\n\n $this->view('semantic-explorer.entitaeten.index', [\n 'title' => 'Entitaeten',\n 'entities' => $entities,\n 'stats' => $stats,\n 'currentType' => $type,\n 'currentSearch' => $search,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/entitaeten\/{id}\n * Entitaet-Details\n *\/\n public function entitaetShow(int $id): void\n {\n $entity = $this->repository->getEntity($id);\n\n if ($entity === null) {\n http_response_code(404);\n echo '404 - Entitaet nicht gefunden';\n\n return;\n }\n\n $synonyms = $this->repository->getEntitySynonyms($id);\n $outgoingRelations = $this->repository->getOutgoingRelations($id);\n $incomingRelations = $this->repository->getIncomingRelations($id);\n $chunks = $this->repository->getChunksForEntity($id);\n $classifications = $this->repository->getEntityClassifications($id);\n\n $this->view('semantic-explorer.entitaeten.show', [\n 'title' => $entity['name'],\n 'entity' => $entity,\n 'synonyms' => $synonyms,\n 'outgoingRelations' => $outgoingRelations,\n 'incomingRelations' => $incomingRelations,\n 'chunks' => $chunks,\n 'classifications' => $classifications,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/relationen\n * Beziehungen zwischen Entitaeten\n *\/\n public function relationen(): void\n {\n $type = $_GET['type'] ?? '';\n\n $relations = $this->repository->getRelationsFiltered($type);\n $relationTypes = $this->repository->getRelationTypes();\n $stats = $this->repository->getRelationStats();\n\n $this->view('semantic-explorer.relationen', [\n 'title' => 'Relationen',\n 'relations' => $relations,\n 'relationTypes' => $relationTypes,\n 'stats' => $stats,\n 'currentType' => $type,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/taxonomie\n * Hierarchische Kategorisierung\n *\/\n public function taxonomie()... [TRUNCATED-58ccedb7efd46c84]",
"structuredPatch": [
{
"oldStart": 3,
"oldLines": 6,
"newStart": 3,
"newLines": 7,
"lines": [
" namespace Controller;",
" ",
" use Framework\\Controller;",
"+use Infrastructure\\SemanticExplorerRepository;",
" ",
" \/**",
" * SemanticExplorerController - Nutzdaten Explorer"
]
},
{
"oldStart": 12,
"oldLines": 80,
"newStart": 13,
"newLines": 24,
"lines": [
" *\/",
" class SemanticExplorerController extends Controller",
" {",
"- private \\PDO $db;",
"+ private SemanticExplorerRepository $repository;",
" ",
" public function __construct()",
" {",
"- $this->db = new \\PDO(",
"- 'mysql:host=localhost;dbname=ki_content;charset=utf8mb4',",
"- 'root',",
"- $this->getPassword(),",
"- [",
"- \\PDO::ATTR_ERRMODE => \\PDO::ERRMODE_EXCEPTION,",
"- \\PDO::ATTR_DEFAULT_FETCH_MODE => \\PDO::FETCH_ASSOC,",
"- ]",
"- );",
"+ $this->repository = new SemanticExplorerRepository();",
" }",
" ",
"- private function getPassword(): string",
"- {",
"- $file = '\/var\/www\/docs\/credentials\/credentials.md';",
"- $content = file_get_contents($file);",
"- foreach (explode(\"\\n\", $content) as $line) {",
"- if (str_contains($line, 'MariaDB') && str_contains($line, 'root')) {",
"- $parts = explode('|', $line);",
"- if (count($parts) >= 4) {",
"- return trim($parts[3]);",
"- }",
"- }",
"- }",
"-",
"- return '';",
"- }",
"-",
" \/**",
" * GET \/semantic-explorer",
" * Dashboard mit Statistiken",
" *\/",
" public function index(): void",
" {",
"- \/\/ Dokument-Statistiken",
"- $docStats = $this->db->query(",
"- 'SELECT",
"- COUNT(*) as total,",
"- SUM(CASE WHEN status = \"done\" THEN 1 ELSE 0 END) as processed,",
"- SUM(CASE WHEN status = \"error\" THEN 1 ELSE 0 END) as errors",
"- FROM documents'",
"- )->fetch();",
"+ $docStats = $this->repository->getDocumentStats();",
"+ $chunkStats = $this->repository->getChunkStats();",
"+ $documents = $this->repository->getDocuments();",
"+ $recentChunks = $this->repository->getRecentChunks(5);",
" ",
"- \/\/ Chunk-Statistiken",
"- $chunkStats = $this->db->query(",
"- 'SELECT",
"- COUNT(*) as total,",
"- COALESCE(SUM(token_count), 0) as tokens,",
"- SUM(CASE WHEN qdrant_id IS NOT NULL THEN 1 ELSE 0 END) as embedded",
"- FROM chunks'",
"- )->fetch();",
"-",
"- \/\/ Dokumente",
"- $documents = $this->db->query(",
"- 'SELECT d.id, d.filename, d.folder_path, d.mime_type, d.file_size,",
"- d.status, d.imported_at, d.processed_at,",
"- (SELECT COUNT(*) FROM chunks WHERE document_id = d.id) as chunk_count",
"- FROM documents d",
"- ORDER BY d.imported_at DESC'",
"- )->fetchAll();",
"-",
"- \/\/ Neueste Chunks",
"- $recentChunks = $this->db->query(",
"- 'SELECT c.id, c.content, c.token_count, c.created_at, c.qdrant_id,",
"- d.filename",
"- FROM chunks c",
"- JOIN documents d ON c.document_id = d.id",
"- ORDER BY c.created_at DESC",
"- LIMIT 5'",
"- )->fetchAll();",
"-",
" $this->view('semantic-explorer.index', [",
" 'title' => 'Semantic Explorer',",
" 'docStats' => $docStats,"
]
},
{
"oldStart": 104,
"oldLines": 32,
"newStart": 49,
"newLines": 8,
"lines": [
" $status = $_GET['status'] ?? '';",
" $search = $_GET['search'] ?? '';",
" ",
"- $sql = 'SELECT d.id, d.filename, d.folder_path, d.source_path, d.mime_type,",
"- d.file_size, d.status, d.imported_at, d.processed_at, d.error_message,",
"- (SELECT COUNT(*) FROM chunks WHERE document_id = d.id) as chunk_count,",
"- (SELECT COALESCE(SUM(token_count), 0) FROM chunks WHERE document_id = d.id) as token_count",
"- FROM documents d",
"- WHERE 1=1';",
"+ $documents = $this->repository->getDocumentsFiltered($status, $search);",
" ",
"- $params = [];",
"-",
"- if ($status !== '') {",
"- $sql .= ' AND d.status = :status';",
"- $params['status'] = $status;",
"- }",
"-",
"- if ($search !== '') {",
"- $sql .= ' AND (d.filename LIKE :search OR d.source_path LIKE :search2)';",
"- $params['search'] = '%' . $search . '%';",
"- $params['search2'] = '%' . $search . '%';",
"- }",
"-",
"- $sql .= ' ORDER BY d.imported_at DESC';",
"-",
"- $stmt = $this->db->prepare($sql);",
"- $stmt->execute($params);",
"- $documents = $stmt->fetchAll();",
"-",
" $this->view('semantic-explorer.dokumente.index', [",
" 'title' => 'Dokumente',",
" 'documents' => $documents,"
]
},
{
"oldStart": 144,
"oldLines": 27,
"newStart": 65,
"newLines": 16,
"lines": [
" *\/",
" public function dokumentShow(int $id): void",
" {",
"- $stmt = $this->db->prepare('SELECT * FROM documents WHERE id = :id');",
"- $stmt->execute(['id' => $id]);",
"- $document = $stmt->fetch();",
"+ $document = $this->repository->getDocument($id);",
" ",
"- if ($document === false) {",
"+ if ($document === null) {",
" http_response_code(404);",
" echo '404 - Dokument nicht gefunden';",
" ",
" return;",
" }",
" ",
"- \/\/ Chunks dieses Dokuments",
"- $stmt = $this->db->prepare(",
"- 'SELECT c.id, c.chunk_index, c.content, c.token_count, c.heading_path,",
"- c.metadata, c.qdrant_id, c.created_at",
"- FROM chunks c",
"- WHERE c.document_id = :id",
"- ORDER BY c.chunk_index'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $chunks = $stmt->fetchAll();",
"+ $chunks = $this->repository->getChunksForDocument($id);",
" ",
" \/\/ Heading-Paths dekodieren",
" foreach ($chunks as &$chunk) {"
]
},
{
"oldStart": 191,
"oldLines": 43,
"newStart": 101,
"newLines": 9,
"lines": [
" $limit = 50;",
" $offset = ($page - 1) * $limit;",
" ",
"- $sql = 'SELECT c.id, c.chunk_index, c.content, c.token_count, c.qdrant_id, c.created_at,",
"- d.filename, d.id as document_id",
"- FROM chunks c",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE 1=1';",
"+ $totalCount = $this->repository->getChunksCount($search, $embedded);",
"+ $chunks = $this->repository->getChunksFiltered($search, $embedded, $limit, $offset);",
" ",
"- $countSql = 'SELECT COUNT(*) FROM chunks c",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE 1=1';",
"-",
"- $params = [];",
"-",
"- if ($search !== '') {",
"- $sql .= ' AND c.content LIKE :search';",
"- $countSql .= ' AND c.content LIKE :search';",
"- $params['search'] = '%' . $search . '%';",
"- }",
"-",
"- if ($embedded === 'yes') {",
"- $sql .= ' AND c.qdrant_id IS NOT NULL';",
"- $countSql .= ' AND c.qdrant_id IS NOT NULL';",
"- } elseif ($embedded === 'no') {",
"- $sql .= ' AND c.qdrant_id IS NULL';",
"- $countSql .= ' AND c.qdrant_id IS NULL';",
"- }",
"-",
"- \/\/ 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);",
"- $stmt->execute($params);",
"- $chunks = $stmt->fetchAll();",
"-",
" $this->view('semantic-explorer.chunks.index', [",
" 'title' => 'Chunks',",
" 'chunks' => $chunks,"
]
},
{
"oldStart": 245,
"oldLines": 16,
"newStart": 121,
"newLines": 9,
"lines": [
" *\/",
" public function chunkShow(int $id): void",
" {",
"- $stmt = $this->db->prepare(",
"- 'SELECT c.*, d.filename, d.source_path, d.id as document_id",
"- FROM chunks c",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE c.id = :id'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $chunk = $stmt->fetch();",
"+ $chunk = $this->repository->getChunk($id);",
" ",
"- if ($chunk === false) {",
"+ if ($chunk === null) {",
" http_response_code(404);",
" echo '404 - Chunk nicht gefunden';",
" "
]
},
{
"oldStart": 266,
"oldLines": 19,
"newStart": 135,
"newLines": 14,
"lines": [
" $chunk['metadata_decoded'] = json_decode($chunk['metadata'] ?? '{}', true) ?: [];",
" ",
" \/\/ Nachbar-Chunks",
"- $stmt = $this->db->prepare(",
"- 'SELECT id, chunk_index FROM chunks",
"- WHERE document_id = :doc_id AND chunk_index = :prev'",
"+ $prevChunk = $this->repository->getChunkByDocumentAndIndex(",
"+ $chunk['document_id'],",
"+ $chunk['chunk_index'] - 1",
" );",
"- $stmt->execute(['doc_id' => $chunk['document_id'], 'prev' => $chunk['chunk_index'] - 1]);",
"- $prevChunk = $stmt->fetch();",
"-",
"- $stmt = $this->db->prepare(",
"- 'SELECT id, chunk_index FROM chunks",
"- WHERE document_id = :doc_id AND chunk_index = :next'",
"+ $nextChunk = $this->repository->getChunkByDocumentAndIndex(",
"+ $chunk['document_id'],",
"+ $chunk['chunk_index'] + 1",
" );",
"- $stmt->execute(['doc_id' => $chunk['document_id'], 'next' => $chunk['chunk_index'] + 1]);",
"- $nextChunk = $stmt->fetch();",
" ",
" $this->view('semantic-explorer.chunks.show', [",
" 'title' => 'Chunk #' . $chunk['id'],"
]
},
{
"oldStart": 337,
"oldLines": 16,
"newStart": 201,
"newLines": 9,
"lines": [
" continue;",
" }",
" ",
"- $stmt = $this->db->prepare(",
"- 'SELECT c.*, d.filename, d.source_path",
"- FROM chunks c",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE c.id = :id'",
"- );",
"- $stmt->execute(['id' => $chunkId]);",
"- $chunk = $stmt->fetch();",
"+ $chunk = $this->repository->getChunkById($chunkId);",
" ",
"- if ($chunk !== false) {",
"+ if ($chunk !== null) {",
" $chunk['score'] = $point['score'];",
" $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];",
" $results[] = $chunk;"
]
},
{
"oldStart": 414,
"oldLines": 38,
"newStart": 271,
"newLines": 9,
"lines": [
" $type = $_GET['type'] ?? '';",
" $search = $_GET['search'] ?? '';",
" ",
"- $sql = 'SELECT e.*,",
"- COUNT(DISTINCT ce.chunk_id) as chunk_count,",
"- COUNT(DISTINCT er.id) as relation_count",
"- FROM entities e",
"- LEFT JOIN chunk_entities ce ON e.id = ce.entity_id",
"- LEFT JOIN entity_relations er ON e.id = er.source_entity_id OR e.id = er.target_entity_id",
"- WHERE 1=1';",
"+ $entities = $this->repository->getEntitiesFiltered($type, $search);",
"+ $stats = $this->repository->getEntityStats();",
" ",
"- $params = [];",
"-",
"- if ($type !== '') {",
"- $sql .= ' AND e.type = :type';",
"- $params['type'] = $type;",
"- }",
"-",
"- if ($search !== '') {",
"- $sql .= ' AND (e.name LIKE :search OR e.description LIKE :search2)';",
"- $params['search'] = '%' . $search . '%';",
"- $params['search2'] = '%' . $search . '%';",
"- }",
"-",
"- $sql .= ' GROUP BY e.id ORDER BY chunk_count DESC, e.name LIMIT 100';",
"-",
"- $stmt = $this->db->prepare($sql);",
"- $stmt->execute($params);",
"- $entities = $stmt->fetchAll();",
"-",
"- \/\/ Statistiken",
"- $stats = $this->db->query(",
"- 'SELECT type, COUNT(*) as count FROM entities GROUP BY type'",
"- )->fetchAll();",
"-",
" $this->view('semantic-explorer.entitaeten.index', [",
" 'title' => 'Entitaeten',",
" 'entities' => $entities,"
]
},
{
"oldStart": 461,
"oldLines": 67,
"newStart": 289,
"newLines": 21,
"lines": [
" *\/",
" public function entitaetShow(int $id): void",
" {",
"- $stmt = $this->db->prepare('SELECT * FROM entities WHERE id = :id');",
"- $stmt->execute(['id' => $id]);",
"- $entity = $stmt->fetch();",
"+ $entity = $this->repository->getEntity($id);",
" ",
"- if ($entity === false) {",
"+ if ($entity === null) {",
" http_response_code(404);",
" echo '404 - Entitaet nicht gefunden';",
" ",
" return;",
" }",
" ",
"- \/\/ Synonyme",
"- $stmt = $this->db->prepare('SELECT * FROM entity_synonyms WHERE entity_id = :id');",
"- $stmt->execute(['id' => $id]);",
"- $synonyms = $stmt->fetchAll();",
"+ $synonyms = $this->repository->getEntitySynonyms($id);",
"+ $outgoingRelations = $this->repository->getOutgoingRelations($id);",
"+ $incomingRelations = $this->repository->getIncomingRelations($id);",
"+ $chunks = $this->repository->getChunksForEntity($id);",
"+ $classifications = $this->repository->getEntityClassifications($id);",
" ",
"- \/\/ Relationen (ausgehend)",
"- $stmt = $this->db->prepare(",
"- 'SELECT er.*, e.name as target_name, e.type as target_type",
"- FROM entity_relations er",
"- JOIN entities e ON er.target_entity_id = e.id",
"- WHERE er.source_entity_id = :id",
"- ORDER BY er.strength DESC'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $outgoingRelations = $stmt->fetchAll();",
"-",
"- \/\/ Relationen (eingehend)",
"- $stmt = $this->db->prepare(",
"- 'SELECT er.*, e.name as source_name, e.type as source_type",
"- FROM entity_relations er",
"- JOIN entities e ON er.source_entity_id = e.id",
"- WHERE er.target_entity_id = :id",
"- ORDER BY er.strength DESC'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $incomingRelations = $stmt->fetchAll();",
"-",
"- \/\/ Chunks mit dieser Entitaet",
"- $stmt = $this->db->prepare(",
"- 'SELECT c.id, c.content, c.token_count, d.filename, ce.relevance_score",
"- FROM chunk_entities ce",
"- JOIN chunks c ON ce.chunk_id = c.id",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE ce.entity_id = :id",
"- ORDER BY ce.relevance_score DESC",
"- LIMIT 20'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $chunks = $stmt->fetchAll();",
"-",
"- \/\/ Ontologie-Klassen",
"- $stmt = $this->db->prepare(",
"- 'SELECT oc.*, ec.confidence",
"- FROM entity_classifications ec",
"- JOIN ontology_classes oc ON ec.ontology_class_id = oc.id",
"- WHERE ec.entity_id = :id'",
"- );",
"- $stmt->execute(['id' => $id]);",
"- $classifications = $stmt->fetchAll();",
"-",
" $this->view('semantic-explorer.entitaeten.show', [",
" 'title' => $entity['name'],",
" 'entity' => $entity,"
]
},
{
"oldStart": 541,
"oldLines": 43,
"newStart": 323,
"newLines": 10,
"lines": [
" {",
" $type = $_GET['type'] ?? '';",
" ",
"- $sql = 'SELECT er.*,",
"- es.name as source_name, es.type as source_type,",
"- et.name as target_name, et.type as target_type",
"- FROM entity_relations er",
"- JOIN entities es ON er.source_entity_id = es.id",
"- JOIN entities et ON er.target_entity_id = et.id",
"- WHERE 1=1';",
"+ $relations = $this->repository->getRelationsFiltered($type);",
"+ $relationTypes = $this->repository->getRelationTypes();",
"+ $stats = $this->repository->getRelationStats();",
" ",
"- $params = [];",
"-",
"- if ($type !== '') {",
"- $sql .= ' AND er.relation_type = :type';",
"- $params['type'] = $type;",
"- }",
"-",
"- $sql .= ' ORDER BY er.strength DESC LIMIT 100';",
"-",
"- $stmt = $this->db->prepare($sql);",
"- $stmt->execute($params);",
"- $relations = $stmt->fetchAll();",
"-",
"- \/\/ Relation-Typen",
"- $relationTypes = $this->db->query(",
"- 'SELECT relation_type, COUNT(*) as count",
"- FROM entity_relations",
"- GROUP BY relation_type",
"- ORDER BY count DESC'",
"- )->fetchAll();",
"-",
"- \/\/ Statistiken",
"- $stats = $this->db->query(",
"- 'SELECT",
"- (SELECT COUNT(*) FROM entity_relations) as total,",
"- (SELECT COUNT(DISTINCT source_entity_id) FROM entity_relations) as sources,",
"- (SELECT COUNT(DISTINCT target_entity_id) FROM entity_relations) as targets'",
"- )->fetch();",
"-",
" $this->view('semantic-explorer.relationen', [",
" 'title' => 'Relationen',",
" 'relations' => $relations,"
]
},
{
"oldStart": 593,
"oldLines": 29,
"newStart": 342,
"newLines": 10,
"lines": [
" *\/",
" public function taxonomie(): void",
" {",
"- \/\/ Alle Terms mit Chunk-Counts",
"- $terms = $this->db->query(",
"- 'SELECT t.*,",
"- COUNT(DISTINCT ct.chunk_id) as chunk_count,",
"- (SELECT COUNT(*) FROM taxonomy_terms WHERE parent_id = t.id) as children_count",
"- FROM taxonomy_terms t",
"- LEFT JOIN chunk_taxonomy ct ON t.id = ct.taxonomy_term_id",
"- GROUP BY t.id",
"- ORDER BY t.path, t.name'",
"- )->fetchAll();",
"-",
"- \/\/ Hierarchie aufbauen",
"+ $terms = $this->repository->getTaxonomyTerms();",
" $hierarchy = $this->buildTaxonomyTree($terms);",
"+ $stats = $this->repository->getTaxonomyStats();",
" ",
"- \/\/ Statistiken",
"- $stats = $this->db->query(",
"- 'SELECT",
"- (SELECT COUNT(*) FROM taxonomy_terms) as total_terms,",
"- (SELECT COUNT(*) FROM taxonomy_terms WHERE parent_id IS NULL) as root_terms,",
"- (SELECT MAX(depth) FROM taxonomy_terms) as max_depth,",
"- (SELECT COUNT(DISTINCT chunk_id) FROM chunk_taxonomy) as tagged_chunks'",
"- )->fetch();",
"-",
" $this->view('semantic-explorer.taxonomie', [",
" 'title' => 'Taxonomie',",
" 'terms' => $terms,"
]
},
{
"oldStart": 646,
"oldLines": 29,
"newStart": 376,
"newLines": 14,
"lines": [
" *\/",
" public function ontologie(): void",
" {",
"- \/\/ Alle Klassen mit Entity-Counts",
"- $classes = $this->db->query(",
"- 'SELECT oc.*,",
"- COUNT(DISTINCT ec.entity_id) as entity_count,",
"- (SELECT COUNT(*) FROM ontology_classes WHERE parent_class_id = oc.id) as subclass_count",
"- FROM ontology_classes oc",
"- LEFT JOIN entity_classifications ec ON oc.id = ec.ontology_class_id",
"- GROUP BY oc.id",
"- ORDER BY oc.name'",
"- )->fetchAll();",
"+ $classes = $this->repository->getOntologyClasses();",
" ",
" \/\/ Properties dekodieren",
" foreach ($classes as &$class) {",
" $class['properties_decoded'] = json_decode($class['properties'] ?? '{}', true) ?: [];",
" }",
" ",
"- \/\/ Statistiken",
"- $stats = $this->db->query(",
"- 'SELECT",
"- (SELECT COUNT(*) FROM ontology_classes) as total_classes,",
"- (SELECT COUNT(*) FROM ontology_classes WHERE parent_class_id IS NULL) as root_classes,",
"- (SELECT COUNT(DISTINCT entity_id) FROM entity_classifications) as classified_entities'",
"- )->fetch();",
"+ $stats = $this->repository->getOntologyStats();",
" ",
" $this->view('semantic-explorer.ontologie', [",
" 'title' => 'Ontologie',"
]
},
{
"oldStart": 688,
"oldLines": 48,
"newStart": 403,
"newLines": 16,
"lines": [
" $limit = 50;",
" $offset = ($page - 1) * $limit;",
" ",
"- $sql = 'SELECT cs.*, c.content, c.token_count, d.filename, d.id as document_id",
"- FROM chunk_semantics cs",
"- JOIN chunks c ON cs.chunk_id = c.id",
"- JOIN documents d ON c.document_id = d.id",
"- WHERE 1=1';",
"+ $totalCount = $this->repository->getSemanticsCount($sentiment);",
"+ $semantics = $this->repository->getSemanticsFiltered($sentiment, $limit, $offset);",
" ",
"- $countSql = 'SELECT COUNT(*) FROM chunk_semantics cs WHERE 1=1';",
"- $params = [];",
"-",
"- if ($sentiment !== '') {",
"- $sql .= ' AND cs.sentiment = :sentiment';",
"- $countSql .= ' AND cs.sentiment = :sentiment';",
"- $params['sentiment'] = $sentiment;",
"- }",
"-",
"- \/\/ Count",
"- $countStmt = $this->db->prepare($countSql);",
"- $countStmt->execute($params);",
"- $totalCount = $countStmt->fetchColumn();",
"-",
"- $sql .= ' ORDER BY cs.analyzed_at DESC LIMIT ' . $limit . ' OFFSET ' . $offset;",
"-",
"- $stmt = $this->db->prepare($sql);",
"- $stmt->execute($params);",
"- $semantics = $stmt->fetchAll();",
"-",
" \/\/ JSON dekodieren",
" foreach ($semantics as &$s) {",
" $s['keywords_decoded'] = json_decode($s['keywords'] ?? '[]', true) ?: [];",
" $s['topics_decoded'] = json_decode($s['topics'] ?? '[]', true) ?: [];",
" }",
" ",
"- \/\/ Statistiken",
"- $stats = $this->db->query(",
"- 'SELECT",
"- COUNT(*) as total,",
"- SUM(CASE WHEN sentiment = \"positive\" THEN 1 ELSE 0 END) as positive,",
"- SUM(CASE WHEN sentiment = \"negative\" THEN 1 ELSE 0 END) as negative,",
"- SUM(CASE WHEN sentiment = \"neutral\" THEN 1 ELSE 0 END) as neutral,",
"- SUM(CASE WHEN sentiment = \"mixed\" THEN 1 ELSE 0 END) as mixed",
"- FROM chunk_semantics'",
"- )->fetch();",
"+ $stats = $this->repository->getSemanticStats();",
" ",
" $this->view('semantic-explorer.semantik', [",
" 'title' => 'Semantik',"
]
}
],
"originalFile": "<?php\n\nnamespace Controller;\n\nuse Framework\\Controller;\n\n\/**\n * SemanticExplorerController - Nutzdaten Explorer\n *\n * Zeigt Dokumente und Chunks aus Nextcloud (documents, chunks Tabellen).\n * Für Endnutzer - Coaching-Materialien, PDFs, später Mails.\n *\/\nclass SemanticExplorerController extends Controller\n{\n private \\PDO $db;\n\n public function __construct()\n {\n $this->db = new \\PDO(\n 'mysql:host=localhost;dbname=ki_content;charset=utf8mb4',\n 'root',\n $this->getPassword(),\n [\n \\PDO::ATTR_ERRMODE => \\PDO::ERRMODE_EXCEPTION,\n \\PDO::ATTR_DEFAULT_FETCH_MODE => \\PDO::FETCH_ASSOC,\n ]\n );\n }\n\n private function getPassword(): string\n {\n $file = '\/var\/www\/docs\/credentials\/credentials.md';\n $content = file_get_contents($file);\n foreach (explode(\"\\n\", $content) as $line) {\n if (str_contains($line, 'MariaDB') && str_contains($line, 'root')) {\n $parts = explode('|', $line);\n if (count($parts) >= 4) {\n return trim($parts[3]);\n }\n }\n }\n\n return '';\n }\n\n \/**\n * GET \/semantic-explorer\n * Dashboard mit Statistiken\n *\/\n public function index(): void\n {\n \/\/ Dokument-Statistiken\n $docStats = $this->db->query(\n 'SELECT\n COUNT(*) as total,\n SUM(CASE WHEN status = \"done\" THEN 1 ELSE 0 END) as processed,\n SUM(CASE WHEN status = \"error\" THEN 1 ELSE 0 END) as errors\n FROM documents'\n )->fetch();\n\n \/\/ Chunk-Statistiken\n $chunkStats = $this->db->query(\n 'SELECT\n COUNT(*) as total,\n COALESCE(SUM(token_count), 0) as tokens,\n SUM(CASE WHEN qdrant_id IS NOT NULL THEN 1 ELSE 0 END) as embedded\n FROM chunks'\n )->fetch();\n\n \/\/ Dokumente\n $documents = $this->db->query(\n 'SELECT d.id, d.filename, d.folder_path, d.mime_type, d.file_size,\n d.status, d.imported_at, d.processed_at,\n (SELECT COUNT(*) FROM chunks WHERE document_id = d.id) as chunk_count\n FROM documents d\n ORDER BY d.imported_at DESC'\n )->fetchAll();\n\n \/\/ Neueste Chunks\n $recentChunks = $this->db->query(\n 'SELECT c.id, c.content, c.token_count, c.created_at, c.qdrant_id,\n d.filename\n FROM chunks c\n JOIN documents d ON c.document_id = d.id\n ORDER BY c.created_at DESC\n LIMIT 5'\n )->fetchAll();\n\n $this->view('semantic-explorer.index', [\n 'title' => 'Semantic Explorer',\n 'docStats' => $docStats,\n 'chunkStats' => $chunkStats,\n 'documents' => $documents,\n 'recentChunks' => $recentChunks,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/dokumente\n * Liste aller Dokumente\n *\/\n public function dokumente(): void\n {\n $status = $_GET['status'] ?? '';\n $search = $_GET['search'] ?? '';\n\n $sql = 'SELECT d.id, d.filename, d.folder_path, d.source_path, d.mime_type,\n d.file_size, d.status, d.imported_at, d.processed_at, d.error_message,\n (SELECT COUNT(*) FROM chunks WHERE document_id = d.id) as chunk_count,\n (SELECT COALESCE(SUM(token_count), 0) FROM chunks WHERE document_id = d.id) as token_count\n FROM documents d\n WHERE 1=1';\n\n $params = [];\n\n if ($status !== '') {\n $sql .= ' AND d.status = :status';\n $params['status'] = $status;\n }\n\n if ($search !== '') {\n $sql .= ' AND (d.filename LIKE :search OR d.source_path LIKE :search2)';\n $params['search'] = '%' . $search . '%';\n $params['search2'] = '%' . $search . '%';\n }\n\n $sql .= ' ORDER BY d.imported_at DESC';\n\n $stmt = $this->db->prepare($sql);\n $stmt->execute($params);\n $documents = $stmt->fetchAll();\n\n $this->view('semantic-explorer.dokumente.index', [\n 'title' => 'Dokumente',\n 'documents' => $documents,\n 'currentStatus' => $status,\n 'currentSearch' => $search,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/dokumente\/{id}\n * Dokument-Details mit Chunks\n *\/\n public function dokumentShow(int $id): void\n {\n $stmt = $this->db->prepare('SELECT * FROM documents WHERE id = :id');\n $stmt->execute(['id' => $id]);\n $document = $stmt->fetch();\n\n if ($document === false) {\n http_response_code(404);\n echo '404 - Dokument nicht gefunden';\n\n return;\n }\n\n \/\/ Chunks dieses Dokuments\n $stmt = $this->db->prepare(\n 'SELECT c.id, c.chunk_index, c.content, c.token_count, c.heading_path,\n c.metadata, c.qdrant_id, c.created_at\n FROM chunks c\n WHERE c.document_id = :id\n ORDER BY c.chunk_index'\n );\n $stmt->execute(['id' => $id]);\n $chunks = $stmt->fetchAll();\n\n \/\/ Heading-Paths dekodieren\n foreach ($chunks as &$chunk) {\n $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];\n $chunk['metadata_decoded'] = json_decode($chunk['metadata'] ?? '{}', true) ?: [];\n }\n\n $this->view('semantic-explorer.dokumente.show', [\n 'title' => $document['filename'],\n 'document' => $document,\n 'chunks' => $chunks,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/chunks\n * Liste aller Chunks\n *\/\n public function chunks(): void\n {\n $search = $_GET['search'] ?? '';\n $embedded = $_GET['embedded'] ?? '';\n $page = max(1, (int) ($_GET['page'] ?? 1));\n $limit = 50;\n $offset = ($page - 1) * $limit;\n\n $sql = 'SELECT c.id, c.chunk_index, c.content, c.token_count, c.qdrant_id, c.created_at,\n d.filename, d.id as document_id\n FROM chunks c\n JOIN documents d ON c.document_id = d.id\n WHERE 1=1';\n\n $countSql = 'SELECT COUNT(*) FROM chunks c\n JOIN documents d ON c.document_id = d.id\n WHERE 1=1';\n\n $params = [];\n\n if ($search !== '') {\n $sql .= ' AND c.content LIKE :search';\n $countSql .= ' AND c.content LIKE :search';\n $params['search'] = '%' . $search . '%';\n }\n\n if ($embedded === 'yes') {\n $sql .= ' AND c.qdrant_id IS NOT NULL';\n $countSql .= ' AND c.qdrant_id IS NOT NULL';\n } elseif ($embedded === 'no') {\n $sql .= ' AND c.qdrant_id IS NULL';\n $countSql .= ' AND c.qdrant_id IS NULL';\n }\n\n \/\/ Count total\n $countStmt = $this->db->prepare($countSql);\n $countStmt->execute($params);\n $totalCount = $countStmt->fetchColumn();\n\n $sql .= ' ORDER BY c.created_at DESC LIMIT ' . $limit . ' OFFSET ' . $offset;\n\n $stmt = $this->db->prepare($sql);\n $stmt->execute($params);\n $chunks = $stmt->fetchAll();\n\n $this->view('semantic-explorer.chunks.index', [\n 'title' => 'Chunks',\n 'chunks' => $chunks,\n 'currentSearch' => $search,\n 'currentEmbedded' => $embedded,\n 'currentPage' => $page,\n 'totalCount' => $totalCount,\n 'totalPages' => ceil($totalCount \/ $limit),\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/chunks\/{id}\n * Chunk-Details\n *\/\n public function chunkShow(int $id): void\n {\n $stmt = $this->db->prepare(\n 'SELECT c.*, d.filename, d.source_path, d.id as document_id\n FROM chunks c\n JOIN documents d ON c.document_id = d.id\n WHERE c.id = :id'\n );\n $stmt->execute(['id' => $id]);\n $chunk = $stmt->fetch();\n\n if ($chunk === false) {\n http_response_code(404);\n echo '404 - Chunk nicht gefunden';\n\n return;\n }\n\n \/\/ JSON-Felder dekodieren\n $chunk['heading_path_decoded'] = json_decode($chunk['heading_path'] ?? '[]', true) ?: [];\n $chunk['metadata_decoded'] = json_decode($chunk['metadata'] ?? '{}', true) ?: [];\n\n \/\/ Nachbar-Chunks\n $stmt = $this->db->prepare(\n 'SELECT id, chunk_index FROM chunks\n WHERE document_id = :doc_id AND chunk_index = :prev'\n );\n $stmt->execute(['doc_id' => $chunk['document_id'], 'prev' => $chunk['chunk_index'] - 1]);\n $prevChunk = $stmt->fetch();\n\n $stmt = $this->db->prepare(\n 'SELECT id, chunk_index FROM chunks\n WHERE document_id = :doc_id AND chunk_index = :next'\n );\n $stmt->execute(['doc_id' => $chunk['document_id'], 'next' => $chunk['chunk_index'] + 1]);\n $nextChunk = $stmt->fetch();\n\n $this->view('semantic-explorer.chunks.show', [\n 'title' => 'Chunk #' . $chunk['id'],\n 'chunk' => $chunk,\n 'prevChunk' => $prevChunk,\n 'nextChunk' => $nextChunk,\n ]);\n }\n\n \/**\n * GET \/semantic-explorer\/suche\n * Semantische Suche in Nutzdaten\n *\/\n public function suche(): void\n {\n $query = $_GET['q'] ?? '';\n $limit = min(20, max(1, (int) ($_GET['limit'] ?? 10)));\n\n $results = [];\n\n if ($query !== '') {\n \/\/ Vektor-Suche via Qdrant\n $results = $this->vectorSearch($query, $limit);\n }\n\n $this->view('semantic-explorer.suche', [\n 'title' => 'Semantische Suche',\n 'query' => $query,\n 'results' => $results,\n 'limit' => $limit,\n ]);\n }\n\n \/**\n * Vektor-Suche in... [TRUNCATED-c09ecc2cb5a8fff7]"
}
}