"""Contract KPIs helper — reads the cached totals maintained by
`contracts_impic.sync_impic_contracts`, joined with local aggregates over
`public_contracts`.

The heavy sync logic lives in `contracts_impic.py`. This module is just the
read-side helper used by `GET /companies/{id}/contracts/summary`.
"""
from typing import Any

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


async def contracts_summary(
    session: AsyncSession, company_id: str
) -> dict[str, Any]:
    """Company-level KPIs for the UI card.

    Totals come from `companies.contracts_total` / `contracts_total_value`
    (refreshed weekly by the IMPIC sync). Last-12m + active counts are
    computed live from `public_contracts` because those windows are always
    time-sensitive."""
    cached = (
        await session.execute(
            text(
                """
                SELECT contracts_total, contracts_total_value, contracts_fetched_at
                FROM companies WHERE id = :id
                """
            ),
            {"id": company_id},
        )
    ).first()
    local = (
        await session.execute(
            text(
                """
                SELECT
                  count(*) FILTER (
                    WHERE role = 'supplier'
                      AND signing_date >= (now() - interval '12 months')::date
                  )::int AS last_12m,
                  coalesce(sum(contract_price) FILTER (
                    WHERE role = 'supplier'
                      AND signing_date >= (now() - interval '12 months')::date
                  ), 0)::numeric AS last_12m_value,
                  count(*) FILTER (
                    WHERE role = 'supplier'
                      AND (
                        close_date IS NULL
                        AND (
                          signing_date IS NULL OR execution_days IS NULL
                          OR signing_date + (execution_days || ' days')::interval >= CURRENT_DATE
                        )
                      )
                  )::int AS active,
                  count(*) FILTER (WHERE role = 'supplier')::int AS local_total
                FROM public_contracts
                WHERE company_id = :cid
                """
            ),
            {"cid": company_id},
        )
    ).first()
    return {
        "total": int(cached[0] or 0) if cached else 0,
        "total_value": float(cached[1] or 0) if cached else 0.0,
        "fetched_at": cached[2].isoformat() if cached and cached[2] else None,
        "last_12m": int(local[0] or 0),
        "last_12m_value": float(local[1] or 0),
        "active": int(local[2] or 0),
        "local_total": int(local[3] or 0),
    }