# ============================================================
# SPID — project resolution & timelines
#
# Merges many mentions (across filings/years) into a single
# Project record + a chronological timeline.  Pure DuckDB SQL
# so it scales to millions of mentions.
# ============================================================
from __future__ import annotations

from spid.config import get_logger
from spid.store import Store

log = get_logger("resolve")

# Grouping key: a project is identified by (company, type, anchor)
# where anchor = first location if present, else first salient name token.
_BUILD_PROJECTS = """
CREATE OR REPLACE TEMP TABLE _m AS
SELECT *,
   lower(coalesce(try(locations[1]), '')) AS loc1,
   regexp_extract(
       lower(regexp_replace(project_name, '[^A-Za-z ]', '', 'g')),
       '([a-z]{4,})', 1) AS name_tok
FROM project_mentions;

CREATE OR REPLACE TEMP TABLE _keyed AS
SELECT *,
   cik || '|' || project_type || '|' ||
   CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END AS gkey,
   md5(cik || '|' || project_type || '|' ||
       CASE WHEN loc1 <> '' THEN loc1 ELSE coalesce(nullif(name_tok,''),'general') END) AS project_id
FROM _m;

DELETE FROM projects;
INSERT INTO projects
SELECT
   project_id,
   any_value(cik)            AS cik,
   any_value(ticker)         AS ticker,
   any_value(company_name)   AS company_name,
   any_value(sector)         AS sector,
   any_value(project_type)   AS project_type,
   -- representative name: the highest-confidence mention's name
   arg_max(project_name, confidence) AS project_name,
   nullif(any_value(loc1), '') AS canonical_location,
   list_distinct(flatten(list(technologies))) AS technologies,
   -- latest-by-date status
   arg_max(status, filing_date) AS status,
   max(amount_usd)           AS total_amount_usd,
   count(*)                  AS n_mentions,
   count(DISTINCT accession_number) AS n_filings,
   min(filing_date)          AS first_seen,
   max(filing_date)          AS last_seen,
   min(list_min(years))      AS first_year,
   max(list_max(years))      AS last_year,
   round(avg(confidence), 3) AS avg_confidence,
   arg_max(description, confidence) AS description
FROM _keyed
GROUP BY project_id;

DELETE FROM project_timeline;
INSERT INTO project_timeline
SELECT project_id, accession_number, filing_date, form_type, status, amount_usd,
       confidence, left(description, 300) AS snippet
FROM _keyed
ORDER BY project_id, filing_date;
"""


def resolve_projects(store: Store) -> dict:
    store.con.execute(_BUILD_PROJECTS)
    n_proj = store.count("projects")
    n_tl = store.count("project_timeline")
    log.info("Resolved %d projects from mentions (%d timeline rows).", n_proj, n_tl)
    return {"projects": n_proj, "timeline_rows": n_tl}