spb/company-atlas

1"""`/sitemap?kind=companies|industries|countries&page=` — only companies worth indexing (spec §84: no thin profiles)."""2from __future__ import annotations34from typing import Any56from fastapi import APIRouter, Query, Request78from companyatlas.api import aggregates as agg9from companyatlas.api.common import cached, cached_response10from companyatlas.config import settings11from companyatlas.db import connection, fetch_all, fetch_val1213ORDER = 1014router = APIRouter(prefix="/api/v1", tags=["exports"])15PAGE_SIZE = 50001617INDEXED_SQL = """18with ev as (select company_id, count(*) as n from events where status = 'active' group by company_id),19     se as (select company_id, count(*) as n from sensors where status in ('active', 'failing', 'stale') group by company_id)20select c.slug, greatest(c.updated_at, coalesce(c.last_event_at, c.updated_at), coalesce(c.last_observed_at, c.updated_at)) as updated_at21from companies c left join ev on ev.company_id = c.id left join se on se.company_id = c.id22where c.status = 'ACTIVE' and (c.indexed or (coalesce(ev.n, 0) >= :min_events and coalesce(se.n, 0) >= :min_sensors))23order by c.slug limit :lim offset :off24"""25INDEXED_COUNT_SQL = """26with ev as (select company_id, count(*) as n from events where status = 'active' group by company_id),27     se as (select company_id, count(*) as n from sensors where status in ('active', 'failing', 'stale') group by company_id)28select count(*) from companies c left join ev on ev.company_id = c.id left join se on se.company_id = c.id29where c.status = 'ACTIVE' and (c.indexed or (coalesce(ev.n, 0) >= :min_events and coalesce(se.n, 0) >= :min_sensors))30"""313233@router.get("/sitemap", summary="Sitemap entries")34async def sitemap(request: Request, kind: str = Query("companies", pattern="^(companies|industries|countries)$"), page: int = Query(1, ge=1)) -> Any:35    async def produce() -> dict[str, Any]:36        if kind == "industries":37            rows = await agg.cached_industry_rows()38            return {"kind": kind, "items": [{"slug": r["slug"], "updated_at": None} for r in rows if r["companies"] > 0], "pages": 1, "page": 1}39        if kind == "countries":40            rows = await agg.cached_country_rows()41            return {"kind": kind, "items": [{"slug": r["slug"], "code": r["code"], "updated_at": None} for r in rows if r["companies"] > 0], "pages": 1, "page": 1}42        async with connection() as conn:43            params = {"min_events": settings.seo_min_events, "min_sensors": settings.seo_min_sensors}44            total = int(await fetch_val(conn, INDEXED_COUNT_SQL, **params) or 0)45            rows = await fetch_all(conn, INDEXED_SQL, **params, lim=PAGE_SIZE, off=(page - 1) * PAGE_SIZE)46        return {"kind": kind, "items": [{"slug": r["slug"], "updated_at": r["updated_at"]} for r in rows], "pages": max(1, -(-total // PAGE_SIZE)), "page": page,47                "total": total}48    return cached_response(request, await cached(f"sitemap:{kind}:{page}", 600, produce), 600)49