"""Intelligence Summary — aggregates every signal we have about a company
into a single endpoint. Designed for a hero card at the top of CompanyDetail;
heavy on headline numbers, light on detail."""
from typing import Annotated, Any
from uuid import UUID

from fastapi import APIRouter, Depends, HTTPException, status
from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

from app.auth.deps import CurrentUser, current_user
from app.db import get_session

router = APIRouter(prefix="/companies/{company_id}/intelligence", tags=["intelligence"])


@router.get("")
async def company_intelligence(
    company_id: UUID,
    session: Annotated[AsyncSession, Depends(get_session)],
    _user: Annotated[CurrentUser, Depends(current_user)],
) -> dict[str, Any]:
    cid = str(company_id)

    # Company base info (need name for insights + check existence)
    company = (
        await session.execute(
            text(
                """
                SELECT id::text, legal_name, nif, monitoring_type, monitored, active,
                       risk_score, contracts_total, contracts_total_value,
                       data_coverage_start, data_coverage_end
                FROM companies WHERE id = :id
                """
            ),
            {"id": cid},
        )
    ).first()
    if not company:
        raise HTTPException(status.HTTP_404_NOT_FOUND)

    # Judicial counts — single query, filtered by date window
    jud = (
        await session.execute(
            text(
                """
                SELECT
                  count(*)::int AS total,
                  count(*) FILTER (WHERE date_filed >= (now() - interval '30 days')::date)::int AS d30,
                  count(*) FILTER (WHERE date_filed >= (now() - interval '180 days')::date)::int AS d180,
                  count(*) FILTER (WHERE source = 'cire')::int AS cire_total,
                  count(*) FILTER (WHERE source = 'distribuicao')::int AS dist_total,
                  count(*) FILTER (WHERE role_in_process ILIKE '%executado%' OR role_in_process ILIKE '%insolvente%')::int AS as_debtor
                FROM processes WHERE company_id = :id
                """
            ),
            {"id": cid},
        )
    ).first()

    # Contracts — last 12m (local aggregate; cached totals on `companies`)
    ctr = (
        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)::int AS active
                FROM public_contracts WHERE company_id = :id
                """
            ),
            {"id": cid},
        )
    ).first()

    # Contracts per year — sparkline-ready breakdown. Uses signing_date; falls
    # back to publication_date when signing is missing.
    by_year_rows = (
        await session.execute(
            text(
                """
                SELECT
                  EXTRACT(YEAR FROM coalesce(signing_date, publication_date))::int AS yr,
                  count(*)::int AS n,
                  coalesce(sum(contract_price), 0)::numeric AS val
                FROM public_contracts
                WHERE company_id = :id
                  AND role = 'supplier'
                  AND coalesce(signing_date, publication_date) IS NOT NULL
                GROUP BY yr
                ORDER BY yr
                """
            ),
            {"id": cid},
        )
    ).all()

    # DRE publications — counts by relevance + latest HIGH
    pubs = (
        await session.execute(
            text(
                """
                SELECT
                  count(*) FILTER (WHERE relevance = 'high')::int AS high,
                  count(*) FILTER (WHERE relevance = 'medium')::int AS medium,
                  count(*) FILTER (
                    WHERE relevance = 'high'
                      AND date >= (now() - interval '90 days')::date
                  )::int AS high_recent
                FROM dre_publications WHERE company_id = :id
                """
            ),
            {"id": cid},
        )
    ).first()
    latest_high = (
        await session.execute(
            text(
                """
                SELECT title, change_kind, date, source
                FROM dre_publications
                WHERE company_id = :id AND relevance = 'high'
                ORDER BY date DESC NULLS LAST LIMIT 1
                """
            ),
            {"id": cid},
        )
    ).first()

    # Relations — count distinct connected companies + highest confidence
    rel = (
        await session.execute(
            # Passou a contar o grafo do registo comercial. Antes vinha do
            # `entity_relations`, derivado de partes de processos judiciais —
            # 578 420 das 588 966 partes eram *credores*, portanto duas empresas
            # ficavam "ligadas" por partilharem um credor de insolvência. Isso
            # não é uma relação societária e a tabela saiu.
            text(
                """
                WITH me AS (
                    SELECT e.id FROM registry_entities e
                     WHERE e.nif = (SELECT nif FROM companies WHERE id = :id)
                )
                SELECT
                  (SELECT count(DISTINCT other)::int FROM (
                      SELECT r.holder_id AS other FROM registry_edges r, me
                       WHERE r.subject_id = me.id AND r.is_current
                       UNION
                      SELECT r.subject_id FROM registry_edges r, me
                       WHERE r.holder_id = me.id AND r.is_current
                  ) x) AS connected_companies,
                  (SELECT count(*)::int FROM registry_edges r, me
                    WHERE r.subject_id = me.id AND r.edge_type = 'holds_quota'
                      AND r.is_current) AS socios,
                  (SELECT count(*)::int FROM registry_edges r, me
                    WHERE r.holder_id = me.id AND r.edge_type = 'holds_quota'
                      AND r.is_current) AS participacoes
                """
            ),
            {"id": cid},
        )
    ).first()

    summary = {
        "company": {
            "id": company[0],
            "legal_name": company[1],
            "nif": company[2],
            "monitoring_type": company[3],
            "monitored": company[4],
            "active": company[5],
            "risk_score": int(company[6]) if company[6] is not None else None,
            "data_coverage_start": company[9],
            "data_coverage_end": company[10],
        },
        "judicial": {
            "total": int(jud[0] or 0),
            "last_30d": int(jud[1] or 0),
            "last_6m": int(jud[2] or 0),
            "cire_total": int(jud[3] or 0),
            "distribuicao_total": int(jud[4] or 0),
            "as_debtor": int(jud[5] or 0),
        },
        "contracts": {
            "total": int(company[7] or 0),
            "total_value": float(company[8] or 0),
            "last_12m": int(ctr[0] or 0),
            "last_12m_value": float(ctr[1] or 0),
            "active": int(ctr[2] or 0),
            "by_year": [
                {"year": int(r[0]), "count": int(r[1]), "value": float(r[2] or 0)}
                for r in by_year_rows
            ],
        },
        "publications": {
            "high": int(pubs[0] or 0),
            "medium": int(pubs[1] or 0),
            "high_recent": int(pubs[2] or 0),
            "latest_high": (
                {
                    "title": latest_high[0],
                    "change_kind": latest_high[1],
                    "date": latest_high[2],
                    "source": latest_high[3],
                }
                if latest_high else None
            ),
        },
        "relations": {
            "connected_companies": int(rel[0] or 0),
            "socios": int(rel[1] or 0),
            "participacoes": int(rel[2] or 0),
        },
    }
    summary["insights"] = _insights(summary)
    return summary


def _insights(s: dict[str, Any]) -> list[dict[str, str]]:
    """Rule-based headline generation. Each insight is a small dict
    {severity: high|medium|info, text: '…'}. Order matters — the UI shows
    them top-down."""
    out: list[dict[str, str]] = []
    c = s["company"]
    j = s["judicial"]
    ctr = s["contracts"]
    p = s["publications"]
    r = s["relations"]

    if c["risk_score"] is not None and c["risk_score"] >= 70:
        out.append({
            "severity": "high",
            "text": f"Risk score elevado ({c['risk_score']}/100) — acumular insolvência ou execuções.",
        })
    if j["as_debtor"] >= 3:
        out.append({
            "severity": "high",
            "text": f"Aparece como devedora/executada em {j['as_debtor']} processos.",
        })
    if j["last_30d"] >= 5:
        out.append({
            "severity": "high",
            "text": f"Actividade judicial elevada: {j['last_30d']} processos novos nos últimos 30 dias.",
        })
    elif j["last_30d"] >= 2:
        out.append({
            "severity": "medium",
            "text": f"{j['last_30d']} processos novos no último mês.",
        })
    if p["high_recent"] > 0:
        kind = (p["latest_high"] or {}).get("change_kind") if p["latest_high"] else None
        label = {
            "liquidation": "Liquidação/Insolvência",
            "capital_change": "Alteração de capital",
            "management_change": "Alteração de gerência",
            "headquarters_change": "Mudança de sede",
            "name_change": "Alteração de denominação",
        }.get(kind or "", "Alteração corporativa")
        out.append({
            "severity": "high",
            "text": f"{label} detectada recentemente ({p['high_recent']} publicação(ões) em 90 dias).",
        })
    if ctr["last_12m"] > 0:
        eur = f"{ctr['last_12m_value']/1000:.0f}k€" if ctr["last_12m_value"] < 1_000_000 else f"{ctr['last_12m_value']/1_000_000:.1f}M€"
        out.append({
            "severity": "info",
            "text": f"Activa em contratos públicos: {ctr['last_12m']} contrato(s) últimos 12 meses ({eur}).",
        })
    if r["connected_companies"] >= 3:
        out.append({
            "severity": "info",
            "text": (
                f"Ligada a {r['connected_companies']} entidades no registo comercial "
                "(sócios, gerência, participações)."
            ),
        })
    if r["participacoes"] > 0:
        out.append({
            "severity": "medium",
            "text": (
                f"Detém participação em {r['participacoes']} outra(s) sociedade(s) — "
                "ver a rede para o grupo completo."
            ),
        })
    if not c["active"]:
        out.append({
            "severity": "info",
            "text": "Empresa arquivada — apenas visível aqui para consulta histórica.",
        })
    if not out:
        out.append({
            "severity": "info",
            "text": "Sem sinais recentes de nota. Monitorização em curso.",
        })
    return out