"""N0 probe: open both DuckDB files read-only, describe the tables used by N1-N13."""
import sys, json
from pathlib import Path
sys.path.insert(0, str(Path(__file__).resolve().parent))
from common import *

r = Run("n0_probe")
con = connect(SPID_DB)
out = {}
for t in ["extracted_sections", "project_mentions", "projects", "project_timeline", "spid_sections", "v_sections", "src_filings", "src_sections", "src_companies"]:
    out[t] = [tuple(x[:2]) for x in con.execute(f"DESCRIBE {t}").fetchall()]
out["views"] = con.execute("select view_name, sql from duckdb_views() where not internal").fetchall()
out["counts"] = {t: con.execute(f"select count(*) from {t}").fetchone()[0] for t in ["extracted_sections", "project_mentions", "projects", "project_timeline", "spid_sections"]}
out["ledger_words"] = con.execute("select count(*), sum(v.word_count) from extracted_sections e join (select section_id, word_count from src_sections union all select section_id, word_count from spid_sections) v using(section_id)").fetchone()
out["parquet_files"] = con.execute("select count(*), sum(length(text))/1e9 from src_sections").fetchone()
e = connect(EDGAR_DB)
for t in ["filings", "filing_sections", "companies", "annual_panel"]:
    out["edgar_"+t] = [tuple(x[:2]) for x in e.execute(f"DESCRIBE {t}").fetchall()]
out["edgar_tables"] = [x[0] for x in e.execute("select table_name from duckdb_tables() where not internal").fetchall()]
r.write_json("schema.json", out)
# can we create a temp table on a read_only connection?
try:
    con.execute("CREATE TEMP TABLE _t AS SELECT 1 AS x"); out["temp_ok"] = True
except Exception as ex:
    out["temp_ok"] = str(ex)
r.write_json("schema.json", out)
r.finish()