"""Admin endpoints for the PSP alvará watchlist.

Used by /admin/alvaras UI to:
  - show the full current roster (default: Alvará A)
  - surface "new in last N days" + "removed in last N days" deltas
  - manually trigger a fresh scrape run (for testing / on-demand refresh)
  - get a small summary for the sidebar badge
"""
from typing import Annotated

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

from app.auth.deps import CurrentUser, require_admin
from app.db import get_session
from app.services.alvara_sync import sync_alvara_type

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


@router.get("/alvara-summary")
async def alvara_summary(
    session: Annotated[AsyncSession, Depends(get_session)],
    _admin: Annotated[CurrentUser, Depends(require_admin)],
    days: int = Query(default=30, ge=1, le=365),
) -> dict:
    """Counts + last-run status for the header / sidebar."""
    # Counts
    counts = (
        await session.execute(
            text(
                """
                SELECT
                    count(*) FILTER (WHERE removed_at IS NULL) AS active,
                    count(*) FILTER (WHERE first_seen_at > now() - make_interval(days := :d)
                                     AND removed_at IS NULL) AS new_recent,
                    count(*) FILTER (WHERE removed_at > now() - make_interval(days := :d)) AS removed_recent
                FROM alvara_companies
                WHERE alvara_type = 'A'
                """
            ),
            {"d": days},
        )
    ).first()
    last = (
        await session.execute(
            text(
                """
                SELECT fetched_at, success, total_count, duration_ms, error_message
                FROM alvara_snapshots
                WHERE alvara_type = 'A'
                ORDER BY fetched_at DESC LIMIT 1
                """
            )
        )
    ).first()
    return {
        "active": int(counts[0] or 0),
        "new_recent": int(counts[1] or 0),
        "removed_recent": int(counts[2] or 0),
        "window_days": days,
        "last_run": {
            "fetched_at": last[0].isoformat() if last and last[0] else None,
            "success": bool(last[1]) if last else None,
            "total": int(last[2]) if last and last[2] is not None else None,
            "duration_ms": int(last[3]) if last and last[3] is not None else None,
            "error": last[4] if last else None,
        } if last else None,
    }


@router.get("/alvara-companies")
async def alvara_companies(
    session: Annotated[AsyncSession, Depends(get_session)],
    _admin: Annotated[CurrentUser, Depends(require_admin)],
    status: str = Query(default="all", pattern="^(all|new|removed|active)$"),
    days: int = Query(default=30, ge=1, le=365),
) -> dict:
    """List rows from alvara_companies filtered by status. `new` = first
    seen inside the window; `removed` = removed inside the window."""
    where: list[str] = ["alvara_type = 'A'"]
    params: dict = {"d": days}
    if status == "new":
        where.append("first_seen_at > now() - make_interval(days := :d)")
        where.append("removed_at IS NULL")
    elif status == "removed":
        where.append("removed_at > now() - make_interval(days := :d)")
    elif status == "active":
        where.append("removed_at IS NULL")
    where_sql = " AND ".join(where)
    rows = (
        await session.execute(
            text(
                f"""
                SELECT ac.nipc, ac.nome, ac.alvara_number,
                       ac.first_seen_at, ac.last_seen_at, ac.removed_at,
                       c.id::text AS company_id,
                       c.monitoring_type
                FROM alvara_companies ac
                LEFT JOIN companies c ON c.nif = ac.nipc
                WHERE {where_sql}
                ORDER BY
                  CASE WHEN ac.removed_at IS NOT NULL
                       THEN ac.removed_at ELSE ac.first_seen_at END DESC
                """
            ),
            params,
        )
    ).all()
    return {
        "items": [
            {
                "nipc": r[0], "nome": r[1], "alvara_number": r[2],
                "first_seen_at": r[3].isoformat() if r[3] else None,
                "last_seen_at": r[4].isoformat() if r[4] else None,
                "removed_at": r[5].isoformat() if r[5] else None,
                "company_id": r[6],
                "monitoring_type": r[7],
            }
            for r in rows
        ],
    }


@router.post("/alvara-sync")
async def alvara_sync_now(
    session: Annotated[AsyncSession, Depends(get_session)],
    _admin: Annotated[CurrentUser, Depends(require_admin)],
) -> dict:
    """On-demand sync — useful for admin testing without waiting for the
    daily 06:30 cron. Takes ~10-20 seconds due to headless browser."""
    return await sync_alvara_type(session, "A")