# ============================================================
# SPID — analytics / dashboard aggregations
# ============================================================
from __future__ import annotations

from spid.store import Store


def _rows(store: Store, sql: str) -> list[dict]:
    return store.con.execute(sql).to_arrow_table().to_pylist()


def overview(store: Store) -> dict:
    con = store.con
    return {
        "companies_with_projects": con.execute("SELECT COUNT(DISTINCT cik) FROM projects").fetchone()[0],
        "total_projects": store.count("projects"),
        "total_mentions": store.count("project_mentions"),
        "total_capex_usd": con.execute("SELECT coalesce(sum(total_amount_usd),0) FROM projects").fetchone()[0],
        "timeline_rows": store.count("project_timeline"),
        "kg_nodes": store.count("kg_nodes"),
        "kg_edges": store.count("kg_edges"),
    }


def by_type(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT project_type, COUNT(*) AS projects,
               coalesce(sum(total_amount_usd),0) AS total_usd,
               round(avg(avg_confidence),3) AS avg_conf
        FROM projects GROUP BY 1 ORDER BY projects DESC""")


def by_sector(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT coalesce(sector,'(unknown)') AS sector, COUNT(*) AS projects,
               coalesce(sum(total_amount_usd),0) AS total_usd
        FROM projects GROUP BY 1 ORDER BY projects DESC LIMIT 25""")


def by_technology(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT t AS technology, COUNT(*) AS projects
        FROM (SELECT unnest(technologies) AS t FROM projects)
        WHERE t <> '' GROUP BY 1 ORDER BY projects DESC LIMIT 25""")


def by_geography(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT canonical_location AS location, COUNT(*) AS projects,
               coalesce(sum(total_amount_usd),0) AS total_usd
        FROM projects WHERE canonical_location IS NOT NULL AND canonical_location <> ''
        GROUP BY 1 ORDER BY projects DESC LIMIT 25""")


def capex_by_year(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT EXTRACT(year FROM first_seen) AS year, COUNT(*) AS projects_started,
               coalesce(sum(total_amount_usd),0) AS announced_usd
        FROM projects WHERE first_seen IS NOT NULL GROUP BY 1 ORDER BY 1""")


def ai_adoption(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT ticker, company_name, sector, COUNT(*) AS ai_projects,
               coalesce(sum(total_amount_usd),0) AS total_usd
        FROM projects
        WHERE project_type = 'ai_initiative' OR list_contains(technologies, 'AI/ML')
        GROUP BY 1,2,3 ORDER BY ai_projects DESC LIMIT 25""")


def top_companies(store: Store) -> list[dict]:
    return _rows(store, """
        SELECT ticker, company_name, sector, COUNT(*) AS projects,
               coalesce(sum(total_amount_usd),0) AS total_usd
        FROM projects GROUP BY 1,2,3 ORDER BY projects DESC LIMIT 25""")


def full_report(store: Store) -> dict:
    return {
        "overview": overview(store),
        "by_type": by_type(store),
        "by_sector": by_sector(store),
        "by_technology": by_technology(store),
        "by_geography": by_geography(store),
        "capex_by_year": capex_by_year(store),
        "ai_adoption": ai_adoption(store),
        "top_companies": top_companies(store),
    }