breakpilot-compliance/backend-compliance/compliance/api/wiki_routes.py

"""
FastAPI routes for Compliance Wiki (read-only knowledge base).

Endpoints:
- GET /v1/wiki/categories       → All categories with article counts
- GET /v1/wiki/articles          → All articles (optional category filter)
- GET /v1/wiki/articles/{id}     → Single article
- GET /v1/wiki/search            → Full-text search (PostgreSQL tsvector)
"""

import logging
from typing import Optional

from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel
from sqlalchemy import text

from database import SessionLocal

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/v1/wiki", tags=["wiki"])


# =============================================================================
# RESPONSE MODELS
# =============================================================================

class WikiCategoryResponse(BaseModel):
    id: str
    name: str
    description: str
    icon: str
    sort_order: int
    article_count: int


class WikiArticleResponse(BaseModel):
    id: str
    category_id: str
    category_name: str
    title: str
    summary: str
    content: str
    legal_refs: list[str]
    tags: list[str]
    relevance: str
    source_urls: list[str]
    version: int
    updated_at: str


class WikiSearchResultResponse(BaseModel):
    id: str
    title: str
    summary: str
    category_name: str
    relevance: str
    highlight: str


# =============================================================================
# HELPERS
# =============================================================================

def _article_row_to_response(row) -> dict:
    """Convert a DB row to WikiArticleResponse dict."""
    return {
        "id": row.id,
        "category_id": row.category_id,
        "category_name": getattr(row, "category_name", ""),
        "title": row.title,
        "summary": row.summary,
        "content": row.content,
        "legal_refs": list(row.legal_refs) if row.legal_refs else [],
        "tags": list(row.tags) if row.tags else [],
        "relevance": row.relevance or "info",
        "source_urls": list(row.source_urls) if row.source_urls else [],
        "version": row.version or 1,
        "updated_at": row.updated_at.isoformat() if row.updated_at else "",
    }


# =============================================================================
# ENDPOINTS
# =============================================================================

@router.get("/categories")
async def list_categories():
    """List all wiki categories with article counts."""
    db = SessionLocal()
    try:
        result = db.execute(text("""
            SELECT c.id, c.name, c.description, c.icon, c.sort_order,
                   COUNT(a.id) AS article_count
            FROM compliance_wiki_categories c
            LEFT JOIN compliance_wiki_articles a ON a.category_id = c.id
            GROUP BY c.id, c.name, c.description, c.icon, c.sort_order
            ORDER BY c.sort_order
        """))
        rows = result.fetchall()
        return {
            "categories": [
                {
                    "id": row.id,
                    "name": row.name,
                    "description": row.description or "",
                    "icon": row.icon or "",
                    "sort_order": row.sort_order or 0,
                    "article_count": row.article_count or 0,
                }
                for row in rows
            ]
        }
    finally:
        db.close()


@router.get("/articles")
async def list_articles(
    category_id: Optional[str] = Query(None, description="Filter by category"),
):
    """List all wiki articles, optionally filtered by category."""
    db = SessionLocal()
    try:
        if category_id:
            result = db.execute(text("""
                SELECT a.*, c.name AS category_name
                FROM compliance_wiki_articles a
                JOIN compliance_wiki_categories c ON c.id = a.category_id
                WHERE a.category_id = :category_id
                ORDER BY
                    CASE a.relevance
                        WHEN 'critical' THEN 0
                        WHEN 'important' THEN 1
                        ELSE 2
                    END,
                    a.title
            """), {"category_id": category_id})
        else:
            result = db.execute(text("""
                SELECT a.*, c.name AS category_name
                FROM compliance_wiki_articles a
                JOIN compliance_wiki_categories c ON c.id = a.category_id
                ORDER BY c.sort_order,
                    CASE a.relevance
                        WHEN 'critical' THEN 0
                        WHEN 'important' THEN 1
                        ELSE 2
                    END,
                    a.title
            """))
        rows = result.fetchall()
        return {
            "articles": [_article_row_to_response(row) for row in rows],
            "total": len(rows),
        }
    finally:
        db.close()


@router.get("/articles/{article_id}")
async def get_article(article_id: str):
    """Get a single wiki article by ID."""
    db = SessionLocal()
    try:
        result = db.execute(text("""
            SELECT a.*, c.name AS category_name
            FROM compliance_wiki_articles a
            JOIN compliance_wiki_categories c ON c.id = a.category_id
            WHERE a.id = :article_id
        """), {"article_id": article_id})
        row = result.fetchone()
        if not row:
            raise HTTPException(status_code=404, detail="Article not found")
        return _article_row_to_response(row)
    finally:
        db.close()


@router.get("/search")
async def search_wiki(
    q: str = Query(..., min_length=2, description="Search query"),
):
    """Full-text search across wiki articles using PostgreSQL tsvector."""
    db = SessionLocal()
    try:
        result = db.execute(text("""
            SELECT a.id, a.title, a.summary, a.relevance,
                   c.name AS category_name,
                   ts_headline('german', a.content, plainto_tsquery('german', :query),
                               'MaxWords=40, MinWords=20, StartSel=**, StopSel=**') AS highlight
            FROM compliance_wiki_articles a
            JOIN compliance_wiki_categories c ON c.id = a.category_id
            WHERE to_tsvector('german', a.title || ' ' || a.summary || ' ' || a.content)
                  @@ plainto_tsquery('german', :query)
            ORDER BY
                ts_rank(to_tsvector('german', a.title || ' ' || a.summary || ' ' || a.content),
                        plainto_tsquery('german', :query)) DESC
            LIMIT 20
        """), {"query": q})
        rows = result.fetchall()
        return {
            "results": [
                {
                    "id": row.id,
                    "title": row.title,
                    "summary": row.summary,
                    "category_name": row.category_name,
                    "relevance": row.relevance or "info",
                    "highlight": row.highlight or "",
                }
                for row in rows
            ],
            "total": len(rows),
            "query": q,
        }
    finally:
        db.close()