#!/usr/bin/env python3
"""WP44 — RQ3/RQ4 citation impact with explicit citation-window control.

From oa_counts_by_year (citations received per calendar year) we build fixed-window counts:
  c12 = citations received in the publication year and the following year (≈ 0–24 months), c36 = pub year + 2 years.
Normalisation: NCS_k = c_k / mean(c_k | same publication year, same type class, same primary subfield) over the eligible
corpus (field-normalised within OpenAlex subfield; category-level normalisation is added in 05_category_impact.py).
Outputs: results/bibliometrics/citation_windows_yearly.csv (means, medians, shares uncited, Gini of c36 by year),
work_citation_windows.parquet (work-level c12/c36/NCS for downstream scripts), top_cited_by_year.csv (top 50 per year by c36),
fwci_summary_yearly.csv (OpenAlex FWCI as a secondary indicator)."""
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")); con.execute("SET threads TO 20; SET memory_limit='" + os.environ.get("WP44_DUCKDB_MEM", "60GB") + "';")
    con.execute("""CREATE OR REPLACE TABLE work_citation_windows AS
        WITH E AS (SELECT id, preprint_work_id, publication_year AS y, coalesce(type, 'unknown') AS type, coalesce(primary_subfield_id, -1) AS sf, cited_by_count, fwci
                   FROM corpus_works WHERE eligible AND NOT e6_retracted),
             -- citations received by the canonical node include those of its merged preprint version (E5)
             CY AS (SELECT e.id AS work_id, c.year, c.cited_by_count FROM oa_counts_by_year c JOIN E e ON e.id = c.work_id
                    UNION ALL SELECT e.id, c.year, c.cited_by_count FROM oa_counts_by_year c JOIN E e ON e.preprint_work_id = c.work_id),
             C AS (SELECT c.work_id, sum(CASE WHEN c.year <= e.y + 1 THEN c.cited_by_count ELSE 0 END) AS c12,
                          sum(CASE WHEN c.year <= e.y + 2 THEN c.cited_by_count ELSE 0 END) AS c36,
                          sum(CASE WHEN c.year <= e.y + 3 THEN c.cited_by_count ELSE 0 END) AS c48
                   FROM CY c JOIN E e ON e.id = c.work_id GROUP BY 1)
        SELECT e.id AS work_id, e.y AS year, e.type, e.sf, e.cited_by_count, e.fwci, coalesce(c.c12,0) c12, coalesce(c.c36,0) c36, coalesce(c.c48,0) c48,
               (e.y + 1 <= 2025) AS c12_complete, (e.y + 2 <= 2025) AS c36_complete, (e.y + 3 <= 2025) AS c48_complete
        FROM E e LEFT JOIN C c ON c.work_id = e.id""")
    con.execute("""CREATE OR REPLACE TABLE work_citation_windows2 AS
        SELECT w.*, CASE WHEN m12 > 0 THEN c12 / m12 END AS ncs12, CASE WHEN m36 > 0 THEN c36 / m36 END AS ncs36
        FROM work_citation_windows w JOIN (SELECT year, type, sf, avg(c12) m12, avg(c36) m36, count(*) n_ref FROM work_citation_windows GROUP BY 1,2,3) r
        USING (year, type, sf)""")
    con.execute("DROP TABLE work_citation_windows; ALTER TABLE work_citation_windows2 RENAME TO work_citation_windows")
    con.execute(f"COPY work_citation_windows TO '{R}/data/processed/corpus/work_citation_windows.parquet' (FORMAT PARQUET, COMPRESSION ZSTD)")
    con.execute(f"""COPY (SELECT year, count(*) n, any_value(c12_complete) c12_complete, any_value(c36_complete) c36_complete, round(avg(c12),3) mean_c12, round(avg(c36),3) mean_c36, quantile_cont(c36,0.5) med_c36,
        round(sum((c36=0)::int)*1.0/count(*),4) share_uncited_36, round(sum((c12=0)::int)*1.0/count(*),4) share_uncited_12,
        quantile_cont(c36,0.99) p99_c36, max(c36) max_c36,
        round((2*sum(c36*rk) / (count(*)*sum(c36)) - (count(*)+1.0)/count(*)),4) gini_c36,
        round((2*sum(c12*rk12) / (count(*)*sum(c12)) - (count(*)+1.0)/count(*)),4) gini_c12,
        round(avg(fwci),3) mean_fwci, round(quantile_cont(fwci,0.5),3) med_fwci
        FROM (SELECT *, row_number() OVER (PARTITION BY year ORDER BY c36, work_id) rk, row_number() OVER (PARTITION BY year ORDER BY c12, work_id) rk12 FROM work_citation_windows) GROUP BY 1 ORDER BY 1) TO '{out}/citation_windows_yearly.csv' (HEADER)""")
    con.execute(f"""COPY (SELECT * FROM (SELECT w.year, w.work_id, c.title, c.source_name, c.type, w.c12, w.c36, w.cited_by_count, w.fwci, w.ncs36,
        row_number() OVER (PARTITION BY w.year ORDER BY w.c36 DESC, w.cited_by_count DESC) rk FROM work_citation_windows w JOIN corpus_works c ON c.id = w.work_id) WHERE rk <= 50 ORDER BY year, rk) TO '{out}/top_cited_by_year.csv' (HEADER)""")
    print(open(f"{out}/citation_windows_yearly.csv").read())


if __name__ == "__main__":
    main()