{
"tool_response": {
"type": "text",
"file": {
"filePath": "\/var\/www\/dev.campus.systemische-tools.de\/src\/Infrastructure\/Persistence\/ContentRepository.php",
"content": "<?php\n\nnamespace Infrastructure\\Persistence;\n\nuse Infrastructure\\Config\\DatabaseFactory;\n\nclass ContentRepository\n{\n private \\PDO $pdo;\n\n public function __construct()\n {\n $this->pdo = DatabaseFactory::content();\n }\n\n \/\/ ==================== Orders ====================\n\n public function findAllOrders(array $filters = [], int $limit = 50, int $offset = 0): array\n {\n $sql = 'SELECT co.*,\n ap.name as profile_name,\n cc.name as contract_name,\n cs.name as structure_name,\n (SELECT COUNT(*) FROM content_versions WHERE order_id = co.id) as version_count\n FROM content_orders co\n LEFT JOIN author_profiles ap ON co.author_profile_id = ap.id\n LEFT JOIN content_contracts cc ON co.contract_id = cc.id\n LEFT JOIN content_structures cs ON co.structure_id = cs.id\n WHERE 1=1';\n\n $params = [];\n\n if (isset($filters['status']) && $filters['status'] !== '') {\n $sql .= ' AND co.status = :status';\n $params['status'] = $filters['status'];\n }\n\n if (isset($filters['profile_id']) && $filters['profile_id'] !== '') {\n $sql .= ' AND co.author_profile_id = :profile_id';\n $params['profile_id'] = $filters['profile_id'];\n }\n\n $sql .= ' ORDER BY co.updated_at DESC LIMIT :limit OFFSET :offset';\n\n $stmt = $this->pdo->prepare($sql);\n foreach ($params as $key => $value) {\n $stmt->bindValue(':' . $key, $value);\n }\n $stmt->bindValue(':limit', $limit, \\PDO::PARAM_INT);\n $stmt->bindValue(':offset', $offset, \\PDO::PARAM_INT);\n $stmt->execute();\n\n return $stmt->fetchAll();\n }\n\n public function findOrder(int $id): ?array\n {\n $stmt = $this->pdo->prepare('\n SELECT co.*,\n ap.name as profile_name, ap.config as profile_config,\n cc.name as contract_name, cc.config as contract_config,\n cs.name as structure_name, cs.config as structure_config\n FROM content_orders co\n LEFT JOIN author_profiles ap ON co.author_profile_id = ap.id\n LEFT JOIN content_contracts cc ON co.contract_id = cc.id\n LEFT JOIN content_structures cs ON co.structure_id = cs.id\n WHERE co.id = :id\n ');\n $stmt->execute(['id' => $id]);\n $result = $stmt->fetch();\n\n return $result !== false ? $result : null;\n }\n\n public function createOrder(array $data): int\n {\n $stmt = $this->pdo->prepare(\"\n INSERT INTO content_orders\n (title, briefing, author_profile_id, contract_id, structure_id, status)\n VALUES (:title, :briefing, :profile_id, :contract_id, :structure_id, 'draft')\n \");\n\n $stmt->execute([\n 'title' => $data['title'],\n 'briefing' => $data['briefing'],\n 'profile_id' => ($data['author_profile_id'] !== '' && $data['author_profile_id'] !== null) ? $data['author_profile_id'] : null,\n 'contract_id' => ($data['contract_id'] !== '' && $data['contract_id'] !== null) ? $data['contract_id'] : null,\n 'structure_id' => ($data['structure_id'] !== '' && $data['structure_id'] !== null) ? $data['structure_id'] : null,\n ]);\n\n return (int) $this->pdo->lastInsertId();\n }\n\n public function updateOrderStatus(int $id, string $status): void\n {\n $stmt = $this->pdo->prepare('\n UPDATE content_orders SET status = :status, updated_at = NOW() WHERE id = :id\n ');\n $stmt->execute(['id' => $id, 'status' => $status]);\n }\n\n public function updateOrder(int $id, array $data): bool\n {\n $stmt = $this->pdo->prepare('\n UPDATE content_orders SET\n title = :title,\n briefing = :briefing,\n author_profile_id = :profile_id,\n contract_id = :contract_id,\n structure_id = :structure_id,\n updated_at = NOW()\n WHERE id = :id\n ');\n\n return $stmt->execute([\n 'id' => $id,\n 'title' => $data['title'],\n 'briefing' => $data['briefing'],\n 'profile_id' => ($data['author_profile_id'] !== '' && $data['author_profile_id'] !== null) ? $data['author_profile_id'] : null,\n 'contract_id' => ($data['contract_id'] !== '' && $data['contract_id'] !== null) ? $data['contract_id'] : null,\n 'structure_id' => ($data['structure_id'] !== '' && $data['structure_id'] !== null) ? $data['structure_id'] : null,\n ]);\n }\n\n \/\/ ==================== Versions ====================\n\n public function findVersionsByOrder(int $orderId): array\n {\n $stmt = $this->pdo->prepare('\n SELECT * FROM content_versions\n WHERE order_id = :order_id\n ORDER BY version_number DESC\n ');\n $stmt->execute(['order_id' => $orderId]);\n\n return $stmt->fetchAll();\n }\n\n public function findLatestVersion(int $orderId): ?array\n {\n $stmt = $this->pdo->prepare('\n SELECT * FROM content_versions\n WHERE order_id = :order_id\n ORDER BY version_number DESC\n LIMIT 1\n ');\n $stmt->execute(['order_id' => $orderId]);\n $result = $stmt->fetch();\n\n return $result !== false ? $result : null;\n }\n\n public function findVersion(int $id): ?array\n {\n $stmt = $this->pdo->prepare('SELECT * FROM content_versions WHERE id = :id');\n $stmt->execute(['id' => $id]);\n $result = $stmt->fetch();\n\n return $result !== false ? $result : null;\n }\n\n \/\/ ==================== Critiques ====================\n\n public function findCritiquesByVersion(int $versionId): array\n {\n $stmt = $this->pdo->prepare('\n SELECT cc.*, c.name as critic_name\n FROM content_critiques cc\n JOIN critics c ON cc.critic_id = c.id\n WHERE cc.version_id = :version_id\n ORDER BY cc.round DESC, c.sort_order ASC\n ');\n $stmt->execute(['version_id' => $versionId]);\n\n $results = $stmt->fetchAll();\n\n \/\/ Parse feedback JSON and merge with row\n foreach ($results as &$row) {\n if (isset($row['feedback']) && $row['feedback'] !== '') {\n $feedback = json_decode($row['feedback'], true);\n if (is_array($feedback)) {\n $row = array_merge($row, $feedback);\n }\n }\n }\n\n return $results;\n }\n\n \/\/ ==================== Sources ====================\n\n public function findSourcesByOrder(int $orderId): array\n {\n $stmt = $this->pdo->prepare('\n SELECT cs.*, c.content, d.filename as document_name\n FROM content_sources cs\n JOIN chunks c ON cs.chunk_id = c.id\n JOIN documents d ON c.document_id = d.id\n WHERE cs.order_id = :order_id\n ORDER BY cs.relevance_score DESC\n ');\n $stmt->execute(['order_id' => $orderId]);\n\n return $stmt->fetchAll();\n }\n\n \/\/ ==================== Profiles ====================\n\n public function findAllProfiles(): array\n {\n $stmt = $this->pdo->query('\n SELECT * FROM author_profiles WHERE is_active = 1 ORDER BY name\n ');\n\n return $stmt->fetchAll();\n }\n\n \/\/ ==================== Contracts ====================\n\n public function findAllContracts(): array\n {\n $stmt = $this->pdo->query('\n SELECT * FROM content_contracts WHERE is_active = 1 ORDER BY name\n ');\n\n return $stmt->fetchAll();\n }\n\n \/\/ ==================== Structures ====================\n\n public function findAllStructures(): array\n {\n $stmt = $this->pdo->query('\n SELECT * FROM content_structures WHERE is_active = 1 ORDER BY name\n ');\n\n return $stmt->fetchAll();\n }\n\n \/\/ ==================== Critics ====================\n\n public function findAllCritics(): array\n {\n $stmt = $this->pdo->query('\n SELECT * FROM critics WHERE is_active = 1 ORDER BY sort_order\n ');\n\n return $stmt->fetchAll();\n }\n\n \/\/ ==================== Statistics ====================\n\n public function getStatistics(): array\n {\n $stats = [];\n\n $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_orders');\n $stats['total_orders'] = (int) $stmt->fetchColumn();\n\n $stmt = $this->pdo->query('SELECT status, COUNT(*) as count FROM content_orders GROUP BY status');\n $stats['by_status'] = $stmt->fetchAll(\\PDO::FETCH_KEY_PAIR);\n\n $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_versions');\n $stats['total_versions'] = (int) $stmt->fetchColumn();\n\n $stmt = $this->pdo->query('SELECT COUNT(*) FROM content_critiques');\n $stats['total_critiques'] = (int) $stmt->fetchColumn();\n\n return $stats;\n }\n}\n",
"numLines": 272,
"startLine": 1,
"totalLines": 272
}
}
}