from typing import Annotated, Any
from uuid import UUID

from fastapi import APIRouter, Depends, Query
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.auth.deps import CurrentUser, current_user
from app.db import get_session
from app.schemas.dre import DrePublicationOut

router = APIRouter(prefix="/dre", tags=["dre"])


@router.get("/company/{company_id}")
async def dre_for_company(
    company_id: UUID,
    session: Annotated[AsyncSession, Depends(get_session)],
    _user: Annotated[CurrentUser, Depends(current_user)],
    page: int = Query(default=1, ge=1),
    page_size: int = Query(default=15, ge=1, le=200),
    only_relevant: bool = Query(default=False),
) -> dict[str, Any]:
    """Paginated publications list, HIGH relevance first. Same row shape as
    before plus standard `{items, total, page, page_size}` envelope.

    `only_relevant=true` drops rows tagged `low` — useful when the UI wants
    to show just the corporate-change signals."""
    where = ["company_id = :cid"]
    params: dict[str, Any] = {"cid": str(company_id)}
    if only_relevant:
        where.append("relevance IN ('high','medium')")
    where_sql = " AND ".join(where)

    total = (
        await session.execute(
            text(f"SELECT count(*) FROM dre_publications WHERE {where_sql}"),
            params,
        )
    ).scalar_one()

    params["lim"] = page_size
    params["off"] = (page - 1) * page_size
    rows = (
        await session.execute(
            text(
                f"""
                SELECT id::text, company_id::text, title, summary, date, type,
                       source_url, raw_json, created_at, relevance, change_kind,
                       source
                FROM dre_publications
                WHERE {where_sql}
                -- Cronológica, e só. Ordenar primeiro por relevância enchia a
                -- primeira página com publicações antigas de alta relevância e
                -- empurrava as recentes para o fim: com 274 "high" e 3616 "low",
                -- a lista lia-se como se estivesse baralhada. Filtrar por
                -- relevância é o que o botão "ocultar irrelevantes" faz.
                ORDER BY date DESC NULLS LAST, created_at DESC
                LIMIT :lim OFFSET :off
                """
            ),
            params,
        )
    ).all()
    items = [
        DrePublicationOut(
            id=r[0], company_id=r[1], title=r[2], summary=r[3], date=r[4],
            type=r[5], source_url=r[6], raw_json=r[7], created_at=r[8],
            relevance=r[9], change_kind=r[10], source=r[11] or "dre",
        ).model_dump(mode="json")
        for r in rows
    ]
    return {
        "items": items,
        "total": int(total),
        "page": page,
        "page_size": page_size,
    }