from pathlib import Path

from fastapi import APIRouter, Request
from fastapi.templating import Jinja2Templates

from app.db import fetch_all, fetch_one

router = APIRouter(prefix="/documents", tags=["documents"])
BASE_DIR = Path(__file__).resolve().parent.parent.parent
templates = Jinja2Templates(directory=str(BASE_DIR / "templates"))


def _iso_rows(rows: list) -> list:
    for row in rows:
        for key, val in list(row.items()):
            if hasattr(val, "isoformat"):
                row[key] = val.isoformat()
    return rows


@router.get("")
async def documents_page(request: Request):
    summary = {
        "documents": 0,
        "total_words": 0,
        "unique_words": 0,
        "avg_sentiment": 0.0,
        "positive": 0,
        "neutral": 0,
        "negative": 0,
    }
    try:
        row = fetch_one(
            """
            SELECT COUNT(*) AS docs,
                   COALESCE(SUM(word_count), 0) AS words,
                   COALESCE(AVG(sentiment_compound), 0) AS avg_sent
            FROM document_analytics
            """
        )
        if row:
            summary["documents"] = int(row["docs"] or 0)
            summary["total_words"] = int(row["words"] or 0)
            summary["avg_sentiment"] = round(float(row["avg_sent"] or 0), 3)
        row = fetch_one("SELECT COUNT(DISTINCT lemma) AS c FROM document_word_counts WHERE NOT is_stopword")
        summary["unique_words"] = int(row["c"] or 0) if row else 0
        for label in ("positive", "neutral", "negative"):
            row = fetch_one(
                "SELECT COUNT(*) AS c FROM document_analytics WHERE sentiment_label = %s",
                (label,),
            )
            summary[label] = int(row["c"] or 0) if row else 0
    except Exception:
        pass

    top_words: list = []
    documents: list = []
    try:
        top_words = fetch_all(
            """
            SELECT lemma, MAX(token) AS token, SUM(count) AS total_count,
                   COUNT(DISTINCT storage_path) AS doc_count
            FROM document_word_counts
            WHERE NOT is_stopword
            GROUP BY lemma
            ORDER BY total_count DESC
            LIMIT 30
            """
        )
        documents = _iso_rows(
            fetch_all(
                """
                SELECT filename, storage_path, doc_type, language, word_count,
                       unique_lemmas, sentiment_label, sentiment_compound,
                       sentiment_positive, sentiment_negative, sentiment_neutral,
                       extraction_method, analyzed_at,
                       COALESCE(user_labels, '{}') AS user_labels, label_notes, labeled_at
                FROM document_analytics
                ORDER BY analyzed_at DESC
                LIMIT 50
                """
            )
        )
    except Exception:
        pass

    return templates.TemplateResponse(
        "documents.html",
        {
            "request": request,
            "page_title": "Documents & Sentiment",
            "summary": summary,
            "top_words": top_words,
            "documents": documents,
        },
    )