api/routes/grants.py

"""
Grant-detail endpoint, backed by the nonprofit grant mart (public."grant").

Serves a single grant by its text PK (grant_id), which the /search grant results
link to (url=/grants/{grant_id}). The frontend grant detail page calls
GET /api/grants/{grant_id} (router prefix "/grants" + app-level "/api" prefix).

NOTE: `grant` is a SQL reserved word — it is ALWAYS double-quoted in SQL here.

Naming contract (CLAUDE.md): expose BOTH the 2-letter `*_state_code` and the full
`*_state` name for grantor and grantee. The full name is recovered in Python from
STATE_NAME_TO_CODE (reused from search_postgres) so the two never drift.

Calendar-year rule (CLAUDE.md): `tax_year` is an integer column in storage but is
serialized as a STRING at the JSON boundary (UI clients locale-format bare JSON
numbers, e.g. 2,024). Dollar amounts stay numeric.
"""
from __future__ import annotations

from typing import Dict, List, Optional

from fastapi import APIRouter, HTTPException, Query
from loguru import logger
from pydantic import BaseModel

from opentelemetry import trace

from api.routes.lenses import money_fmt
from api.routes.search_postgres import (
    STATE_NAME_TO_CODE,
    get_db_pool,
    normalize_state_input,
)

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

tracer = trace.get_tracer(__name__)

# Reverse of STATE_NAME_TO_CODE: 2-letter code -> full state name, so a grant row
# (which carries only *_state_code) can expose the full `*_state` per the naming
# contract. Derived from the canonical dict so the two never drift.
_CODE_TO_STATE_NAME: Dict[str, str] = {
    code: name for name, code in STATE_NAME_TO_CODE.items()
}


def _state_name(state_code: Optional[str]) -> Optional[str]:
    """Full state name for a 2-letter code, or None if unknown/missing."""
    if not state_code:
        return None
    return _CODE_TO_STATE_NAME.get(state_code.upper())


class GrantDetail(BaseModel):
    """A single nonprofit grant (990 Schedule I grant line), keyed by grant_id."""
    grant_id: str

    # Grantor (the org making the grant).
    grantor_master_org_id: Optional[str] = None
    grantor_ein: Optional[str] = None
    grantor_name: Optional[str] = None
    grantor_state_code: Optional[str] = None
    grantor_state: Optional[str] = None
    grantor_city: Optional[str] = None

    # Grantee (the org receiving the grant).
    grantee_master_org_id: Optional[str] = None
    grantee_ein: Optional[str] = None
    grantee_name: Optional[str] = None
    grantee_state_code: Optional[str] = None
    grantee_state: Optional[str] = None
    grantee_city: Optional[str] = None
    grantee_zip: Optional[str] = None

    # Grant terms.
    irc_section: Optional[str] = None
    amount: Optional[int] = None  # cash grant (bigint), numeric on the wire
    noncash_assistance_amount: Optional[int] = None  # bigint, numeric on the wire
    valuation_method: Optional[str] = None
    noncash_description: Optional[str] = None
    purpose: Optional[str] = None

    # tax_year is an integer column but is serialized as a STRING (calendar-year rule).
    tax_year: Optional[str] = None
    source_url: Optional[str] = None


# Single grant row by its text PK. `grant` is reserved -> double-quoted.
_GRANT_SQL = """
    SELECT
        grant_id,
        grantor_master_org_id,
        grantor_ein,
        grantor_name,
        grantor_state_code,
        grantor_city_norm,
        grantee_master_org_id,
        grantee_ein,
        grantee_name,
        grantee_state_code,
        grantee_city,
        grantee_zip,
        irc_section,
        amount,
        noncash_assistance_amount,
        valuation_method,
        noncash_description,
        purpose,
        tax_year,
        source_url
    FROM "grant"
    WHERE grant_id = $1
"""


class TopGrant(BaseModel):
    """A single grant row for the homepage "Grants" card (largest by amount)."""
    grant_id: str
    grantor_name: Optional[str] = None
    grantee_name: Optional[str] = None
    amount: int
    amount_label: str  # compact $ (e.g. "$2.9M")
    jurisdiction_label: Optional[str] = None  # "Tuscaloosa, AL" from grantor geo
    # tax_year is an integer column but is serialized as a STRING (calendar-year rule).
    tax_year: Optional[str] = None
    url: str


class TopGrantsResponse(BaseModel):
    """Top grants by amount, scoped to a grantor geography, for the Grants card."""
    grants: List[TopGrant]
    location_label: str


def _jurisdiction_label(
    city_norm: Optional[str], state_code: Optional[str]
) -> Optional[str]:
    """
    Display label from a grantor's normalized city + 2-letter state.

    grantor_city_norm is lowercase normalized (e.g. "tuscaloosa"); title-case it.
    Returns "City, ST", state-only ("ST") if no city, or None if neither.
    """
    city = city_norm.strip() if city_norm else None
    state = state_code.strip().upper() if state_code else None
    if city and state:
        return f"{city.title()}, {state}"
    if state:
        return state
    if city:
        return city.title()
    return None


# Top grants by amount, scoped on GRANTOR geography. `grant` is reserved -> quoted.
# Mirrors the grantor-location scope of search_grants_pg (search_postgres.py).
@router.get("/top", response_model=TopGrantsResponse)
async def get_top_grants(
    state: Optional[str] = Query(None, description="2-letter code or full state name"),
    city: Optional[str] = Query(None, description="Grantor city"),
    jurisdiction_id: Optional[int] = Query(None, description="Grantor jurisdiction id"),
    limit: int = Query(6, description="Max grants (clamped 1..20)"),
) -> TopGrantsResponse:
    """
    Return the largest grants (by amount) for a grantor geography.

    Scope mirrors search_grants_pg's grantor-location filter exactly:
    jurisdiction_id (via the org bridge) wins; otherwise grantor_state_code and/or
    grantor_city_norm. Always requires amount IS NOT NULL AND amount > 0. Backs the
    homepage "Grants" card. Returns an empty list (never fabricated rows) when the
    scoped query finds nothing.
    """
    limit = max(1, min(20, limit))
    state_code = normalize_state_input(state)

    with tracer.start_as_current_span("grants-top") as span:
        span.set_attribute("grants.limit", limit)
        if jurisdiction_id:
            span.set_attribute("grants.jurisdiction_id", jurisdiction_id)
        if state_code:
            span.set_attribute("grants.state_code", state_code)
        if city:
            span.set_attribute("grants.city", city)

        # Scope label for the card header ("United States" when unscoped).
        if city and state_code:
            location_label = f"{city.strip().title()}, {state_code}"
        elif state_code:
            location_label = _state_name(state_code) or state_code
        elif city:
            location_label = city.strip().title()
        else:
            location_label = "United States"

        try:
            where_clauses: List[str] = []
            params: List[object] = []
            param_idx = 1

            # Grantor-location scope (mirrors search_grants_pg).
            if jurisdiction_id:
                where_clauses.append(
                    f"grantor_master_org_id IN ("
                    f"SELECT master_org_id FROM mdm_bridge_org_jurisdiction "
                    f"WHERE jurisdiction_id = ${param_idx})"
                )
                params.append(jurisdiction_id)
                param_idx += 1
            else:
                if state_code:
                    where_clauses.append(f"grantor_state_code = ${param_idx}")
                    params.append(state_code.upper())
                    param_idx += 1
                if city:
                    where_clauses.append(
                        f"lower(grantor_city_norm) = lower(${param_idx})"
                    )
                    params.append(city.strip())
                    param_idx += 1

            # Always: only real, positive amounts.
            where_clauses.append("amount IS NOT NULL AND amount > 0")
            where_sql = " AND ".join(where_clauses)

            sql = f"""
                SELECT
                    grant_id,
                    grantor_name,
                    grantee_name,
                    amount,
                    grantor_state_code,
                    grantor_city_norm,
                    tax_year
                FROM "grant"
                WHERE {where_sql}
                ORDER BY amount DESC NULLS LAST
                LIMIT ${param_idx}
            """
            params.append(limit)

            pool = await get_db_pool()
            async with pool.acquire() as conn:
                with tracer.start_as_current_span("grants-top.query"):
                    rows = await conn.fetch(sql, *params)

            span.set_attribute("grants.row_count", len(rows))

            grants = [
                TopGrant(
                    grant_id=row["grant_id"],
                    grantor_name=row["grantor_name"],
                    grantee_name=row["grantee_name"],
                    amount=row["amount"],
                    amount_label=money_fmt(row["amount"]),
                    jurisdiction_label=_jurisdiction_label(
                        row["grantor_city_norm"], row["grantor_state_code"]
                    ),
                    tax_year=(
                        str(row["tax_year"])
                        if row["tax_year"] is not None
                        else None
                    ),
                    url=f"/grants/{row['grant_id']}",
                )
                for row in rows
            ]

            logger.info(
                "💰 Top grants ({}): {} row(s)", location_label, len(grants)
            )

            return TopGrantsResponse(grants=grants, location_label=location_label)

        except Exception as e:
            span.record_exception(e)
            logger.error("Top grants error ({}): {}", location_label, e)
            raise HTTPException(status_code=500, detail="Failed to load top grants")


@router.get("/{grant_id}", response_model=GrantDetail)
async def get_grant(grant_id: str) -> GrantDetail:
    """
    Return a single nonprofit grant by grant_id. 404 if no grant row matches.
    """
    with tracer.start_as_current_span("grant-detail") as span:
        span.set_attribute("grant.grant_id", grant_id)
        try:
            pool = await get_db_pool()
            async with pool.acquire() as conn:
                with tracer.start_as_current_span("grant-detail.query-grant"):
                    row = await conn.fetchrow(_GRANT_SQL, grant_id)

            if row is None:
                span.set_attribute("grant.found", False)
                raise HTTPException(
                    status_code=404,
                    detail=f"No grant found for grant_id '{grant_id}'",
                )
            span.set_attribute("grant.found", True)

            tax_year = (
                str(row["tax_year"]) if row["tax_year"] is not None else None
            )

            logger.info(
                "💰 Grant detail {} ({} → {})",
                grant_id,
                row["grantor_name"] or "Unknown grantor",
                row["grantee_name"] or "Unknown grantee",
            )

            return GrantDetail(
                grant_id=row["grant_id"],
                grantor_master_org_id=row["grantor_master_org_id"],
                grantor_ein=row["grantor_ein"],
                grantor_name=row["grantor_name"],
                grantor_state_code=row["grantor_state_code"],
                grantor_state=_state_name(row["grantor_state_code"]),
                grantor_city=row["grantor_city_norm"],
                grantee_master_org_id=row["grantee_master_org_id"],
                grantee_ein=row["grantee_ein"],
                grantee_name=row["grantee_name"],
                grantee_state_code=row["grantee_state_code"],
                grantee_state=_state_name(row["grantee_state_code"]),
                grantee_city=row["grantee_city"],
                grantee_zip=row["grantee_zip"],
                irc_section=row["irc_section"],
                amount=row["amount"],
                noncash_assistance_amount=row["noncash_assistance_amount"],
                valuation_method=row["valuation_method"],
                noncash_description=row["noncash_description"],
                purpose=row["purpose"],
                tax_year=tax_year,
                source_url=row["source_url"],
            )

        except HTTPException:
            raise
        except Exception as e:
            span.record_exception(e)
            logger.error("Grant detail error for {}: {}", grant_id, e)
            raise HTTPException(status_code=500, detail="Failed to load grant detail")