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, current_user
from app.db import get_session
from app.schemas.process import DashboardMetrics

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


@router.get("/metrics", response_model=DashboardMetrics)
async def metrics(
    session: Annotated[AsyncSession, Depends(get_session)],
    _user: Annotated[CurrentUser, Depends(current_user)],
    monitoring_type: str | None = Query(
        default=None, pattern="^(internal|competitor|analysis|client|related)$"
    ),
) -> DashboardMetrics:
    where_join_companies = ""
    where_clause = ""
    params: dict[str, object] = {}
    if monitoring_type:
        where_join_companies = "JOIN companies c ON c.id = p.company_id"
        where_clause = "WHERE c.monitoring_type = :mt"
        params["mt"] = monitoring_type

    total = (
        await session.execute(
            text(
                f"SELECT count(*) FROM processes p {where_join_companies} {where_clause}"
            ),
            params,
        )
    ).scalar_one()
    new_24h = (
        await session.execute(
            text(
                f"""
                SELECT count(*) FROM processes p {where_join_companies}
                {where_clause}{' AND' if where_clause else 'WHERE'} p.first_seen_at > now() - interval '24 hours'
                """
            ),
            params,
        )
    ).scalar_one()
    by_source_rows = (
        await session.execute(
            text(
                f"""
                SELECT p.source, count(*) FROM processes p {where_join_companies}
                {where_clause}
                GROUP BY p.source
                """
            ),
            params,
        )
    ).all()
    by_source = {r[0]: r[1] for r in by_source_rows}
    timeline_rows = (
        await session.execute(
            text(
                f"""
                SELECT d::date AS day, COALESCE(c, 0)
                FROM generate_series((now() - interval '30 days')::date, now()::date, '1 day') d
                LEFT JOIN (
                    SELECT p.date_filed AS day, count(*) AS c
                    FROM processes p {where_join_companies}
                    {where_clause}{' AND' if where_clause else 'WHERE'} p.date_filed >= (now() - interval '30 days')::date
                    GROUP BY p.date_filed
                ) x ON x.day = d
                ORDER BY d
                """
            ),
            params,
        )
    ).all()
    timeline = [{"day": r[0].isoformat(), "count": int(r[1])} for r in timeline_rows]
    last_scrapes_rows = (
        await session.execute(
            text(
                """
                SELECT source, started_at, finished_at, status, rows_seen, rows_new, error
                FROM scraping_logs ORDER BY started_at DESC LIMIT 10
                """
            )
        )
    ).all()
    last_scrapes = [
        {
            "source": r[0],
            "started_at": r[1].isoformat() if r[1] else None,
            "finished_at": r[2].isoformat() if r[2] else None,
            "status": r[3],
            "rows_seen": r[4],
            "rows_new": r[5],
            "error": r[6],
        }
        for r in last_scrapes_rows
    ]
    return DashboardMetrics(
        total_processes=total,
        new_24h=new_24h,
        by_source=by_source,
        timeline=timeline,
        last_scrapes=last_scrapes,
    )