# ============================================================
# SPID — structured & keyword search over the project DB
# ============================================================
from __future__ import annotations

from spid.store import Store


def search_projects(store: Store, *, keyword: str | None = None,
                    project_type: str | None = None, sector: str | None = None,
                    technology: str | None = None, location: str | None = None,
                    ticker: str | None = None, min_amount: float | None = None,
                    year_from: int | None = None, year_to: int | None = None,
                    status: str | None = None, limit: int = 25) -> list[dict]:
    w, p = [], []
    if keyword:
        w.append("(lower(project_name) LIKE ? OR lower(description) LIKE ?)")
        p += [f"%{keyword.lower()}%", f"%{keyword.lower()}%"]
    if project_type:
        w.append("project_type = ?"); p.append(project_type)
    if sector:
        w.append("lower(sector) LIKE ?"); p.append(f"%{sector.lower()}%")
    if technology:
        w.append("list_contains(technologies, ?)"); p.append(technology)
    if location:
        w.append("lower(canonical_location) LIKE ?"); p.append(f"%{location.lower()}%")
    if ticker:
        w.append("ticker = ?"); p.append(ticker.upper())
    if min_amount is not None:
        w.append("total_amount_usd >= ?"); p.append(float(min_amount))
    if year_from is not None:
        w.append("last_year >= ?"); p.append(int(year_from))
    if year_to is not None:
        w.append("first_year <= ?"); p.append(int(year_to))
    if status:
        w.append("status = ?"); p.append(status)
    where = (" WHERE " + " AND ".join(w)) if w else ""
    sql = (f"SELECT project_id, ticker, company_name, sector, project_type, project_name, "
           f"canonical_location, technologies, status, total_amount_usd, n_filings, "
           f"first_seen, last_seen, avg_confidence, description "
           f"FROM projects{where} "
           f"ORDER BY coalesce(total_amount_usd,0) DESC, n_filings DESC LIMIT {int(limit)}")
    return store.con.execute(sql, p).to_arrow_table().to_pylist()


def get_project(store: Store, project_id: str) -> dict | None:
    rows = store.con.execute(
        "SELECT * FROM projects WHERE project_id = ?", [project_id]
    ).to_arrow_table().to_pylist()
    if not rows:
        return None
    proj = rows[0]
    proj["timeline"] = store.con.execute(
        "SELECT filing_date, form_type, status, amount_usd, confidence, snippet "
        "FROM project_timeline WHERE project_id = ? ORDER BY filing_date", [project_id]
    ).to_arrow_table().to_pylist()
    return proj