#!/usr/bin/env python3
"""WP44 — RQ1 volume series from corpus_works (eligible, CS core) with indexing-lag diagnostics.
Outputs (results/bibliometrics/): volume_monthly.csv (first_month × type × membership), volume_yearly.csv,
indexing_lag.csv (publication month → distribution of created_date − publication_date), penumbra_monthly.csv,
dblp_only_yearly.csv. All tables are regenerated from the DuckDB database; nothing is hand-edited."""
import argparse, os, duckdb


def main():
    ap = argparse.ArgumentParser(); ap.add_argument("--root", required=True); a = ap.parse_args(); R = a.root
    out = os.path.join(R, "results/bibliometrics"); os.makedirs(out, exist_ok=True)
    con = duckdb.connect(os.path.join(R, "data/processed/wp44.duckdb"), read_only=True); con.execute("SET threads TO 16;")
    con.execute(f"""COPY (SELECT first_month, period, date_precision, type, in_dblp, cs_primary, (arxiv_id_linked IS NOT NULL) AS has_arxiv, count(*) AS n,
                        sum(has_abstract::int) AS n_abstract, sum((n_refs>0)::int) AS n_with_refs, sum(is_oa::int) AS n_oa
                 FROM corpus_works WHERE eligible GROUP BY ALL ORDER BY 1,2,3) TO '{out}/volume_monthly.csv' (HEADER)""")
    con.execute(f"""COPY (SELECT publication_year AS year, count(*) AS n, sum(in_dblp::int) AS n_dblp, sum(cs_primary::int) AS n_cs_primary,
                        sum((arxiv_id_linked IS NOT NULL)::int) AS n_arxiv, sum(in_acl::int) AS n_acl, sum(has_abstract::int) AS n_abstract,
                        sum(is_oa::int) AS n_oa, sum((type='preprint')::int) AS n_preprint, sum((type='conference-paper')::int) AS n_conf,
                        sum((type='article')::int) AS n_article, sum(e6_retracted::int) AS n_retracted, sum(xpac_kept_dblp::int) AS n_xpac_dblp,
                        round(avg(n_authors),2) AS mean_authors, round(avg(n_refs),2) AS mean_refs, round(avg(n_countries),3) AS mean_countries
                 FROM corpus_works WHERE eligible GROUP BY 1 ORDER BY 1) TO '{out}/volume_yearly.csv' (HEADER)""")
    con.execute(f"""COPY (SELECT substr(publication_date,1,7) AS pub_month, count(*) AS n,
                        quantile_cont(datediff('day', publication_date::DATE, created_date::DATE), 0.5) AS lag_days_p50,
                        quantile_cont(datediff('day', publication_date::DATE, created_date::DATE), 0.9) AS lag_days_p90,
                        sum((datediff('day', publication_date::DATE, created_date::DATE) <= 90)::int) AS n_indexed_within_90d,
                        sum((created_date::DATE > publication_date::DATE + INTERVAL 180 DAY)::int) AS n_indexed_after_180d
                 FROM corpus_works WHERE eligible AND created_date IS NOT NULL AND publication_date IS NOT NULL GROUP BY 1 ORDER BY 1) TO '{out}/indexing_lag.csv' (HEADER)""")
    con.execute(f"""COPY (SELECT first_month, count(*) AS n_penumbra FROM corpus_works WHERE cs_penumbra AND NOT e1_outside_window AND NOT e2_type AND NOT e3_xpac AND NOT e4_no_title AND NOT e5_duplicate
                 GROUP BY 1 ORDER BY 1) TO '{out}/penumbra_monthly.csv' (HEADER)""")
    con.execute(f"COPY (SELECT * FROM dblp_only_counts) TO '{out}/dblp_only_yearly.csv' (HEADER)")
    for f in ["volume_yearly.csv", "indexing_lag.csv"]:
        print(f, open(os.path.join(out, f)).read()[:1500])


if __name__ == "__main__":
    main()