Crawl/backend/app/routers/stats.py

from __future__ import annotations

import time
from typing import List

from fastapi import APIRouter
from pydantic import BaseModel

from app.db import get_pool

START_TIME = time.time()

router = APIRouter(tags=["stats"])


# ---------- Pydantic models ----------

class StatsOut(BaseModel):
    uptime_seconds: float
    total_hits: int
    active_ips: int
    avg_latency_ms: float


class GeoDistributionItem(BaseModel):
    country: str
    count: int
    percentage: float


class GeoPoint(BaseModel):
    latitude: float
    longitude: float
    country: str
    city: str
    hit_count: int


# ---------- Endpoints ----------

@router.get("/stats", response_model=StatsOut)
async def get_stats() -> StatsOut:
    pool = get_pool()
    async with pool.acquire() as conn:
        total_hits: int = await conn.fetchval(
            "SELECT COUNT(*) FROM access_logs WHERE path LIKE '/api/public/prices%'"
        ) or 0
        active_ips: int = (
            await conn.fetchval(
                "SELECT COUNT(DISTINCT ip) FROM access_logs "
                "WHERE created_at > NOW() - INTERVAL '5 minutes' "
                "AND path LIKE '/api/public/prices%'"
            )
            or 0
        )
        avg_latency = await conn.fetchval("SELECT AVG(latency_ms) FROM access_logs")

    return StatsOut(
        uptime_seconds=time.time() - START_TIME,
        total_hits=total_hits,
        active_ips=active_ips,
        avg_latency_ms=round(float(avg_latency), 2) if avg_latency is not None else 0.0,
    )


@router.get("/geo/distribution", response_model=List[GeoDistributionItem])
async def get_geo_distribution() -> List[GeoDistributionItem]:
    pool = get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT country, COUNT(*) AS cnt FROM access_logs "
            "GROUP BY country ORDER BY cnt DESC"
        )
        total = sum(r["cnt"] for r in rows)

    return [
        GeoDistributionItem(
            country=row["country"],
            count=row["cnt"],
            percentage=round(row["cnt"] / total * 100, 2) if total else 0.0,
        )
        for row in rows
    ]


@router.get("/prices/top-ips", response_model=List[dict])
async def get_top_price_ips() -> List[dict]:
    pool = get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """
            SELECT ip, COUNT(*) AS hit_count
            FROM access_logs
            WHERE path LIKE '/api/public/prices%'
            GROUP BY ip
            ORDER BY hit_count DESC
            LIMIT 20
            """
        )
    total = sum(r["hit_count"] for r in rows) or 1
    return [
        {
            "ip": r["ip"],
            "hit_count": r["hit_count"],
            "percentage": round(r["hit_count"] / total * 100, 2),
        }
        for r in rows
    ]


@router.get("/geo/points", response_model=List[GeoPoint])
async def get_geo_points() -> List[GeoPoint]:
    pool = get_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """
            SELECT
                latitude, longitude,
                country,
                MAX(CASE WHEN city != 'Unknown' THEN city END) AS city,
                SUM(cnt) AS hit_count
            FROM (
                SELECT latitude, longitude, country, city, COUNT(*) AS cnt
                FROM access_logs
                WHERE latitude IS NOT NULL AND longitude IS NOT NULL
                  AND path LIKE '/api/public/prices%'
                GROUP BY latitude, longitude, country, city
            ) sub
            GROUP BY latitude, longitude, country
            ORDER BY hit_count DESC
            LIMIT 1000
            """
        )

    return [
        GeoPoint(
            latitude=row["latitude"],
            longitude=row["longitude"],
            country=row["country"],
            city=row["city"] or "Unknown",
            hit_count=row["hit_count"],
        )
        for row in rows
    ]