#!/usr/bin/env python3
"""N2-A-001 Cold Start Data — step 1: cohort discovery via ClickHouse public playground.

Route (a) of the pre-registered method (episodes/N2-A-001/research.md §4).
Uses play.clickhouse.com (user "play", no auth) github_events dataset.

Verified at run time (2026-08-27): dataset spans 2023-01-13 13:00 .. 2026-07-02 23:00 UTC.

DEVIATIONS from the pre-registration (recorded here and in data/coldstart/README.md):
  D1. Dataset floor is 2023-01-13, not 2011: repos created 2023-01-01..2023-01-13
      are invisible to this route (covered only by the Search API cross-check).
  D2. "Creation" cannot come from CreateEvent alone: repos born private and later
      made public (twitter/the-algorithm, yoheinakajima/babyagi, AntonOsika/gpt-engineer —
      all verified) emit NO public repository-CreateEvent. Discovery therefore keys on
      first_seen = min(created_at) over ALL events for the repo (public-life proxy),
      requires first_seen in [2023-01-14, 2025-06-30], and counts stars in the 60 days
      after first_seen. True creation dates are then fetched from the GitHub API in
      step 3 and the pre-registered rule (>=5,000 stars within 60 days of CREATION)
      is re-applied day-granular in step 4. Deleted repos fall back to
      CreateEvent(repository) date, else first_seen.
  D3. Repo renames split rows (Torantulino/Auto-GPT vs Significant-Gravitas/Auto-GPT):
      resolved in step 3 via API redirects; merged in step 4.

Outputs (data/coldstart/raw/):
  clickhouse_meta.txt        dataset span + row counts at run time
  cohort_broad.tsv           repo, first_seen, stars_60d(from first_seen), stars_total_archive
  daily_series.tsv           repo, day, watch_events, push_events  (full daily history)
  createvent_creations.tsv   repo, min CreateEvent(repository) ts  (deleted-repo fallback)
"""
import sys, time, urllib.request, urllib.parse, pathlib

ROOT = pathlib.Path(__file__).resolve().parents[2]
RAW = ROOT / "data" / "coldstart" / "raw"
RAW.mkdir(parents=True, exist_ok=True)
CH = "https://play.clickhouse.com/?user=play"

def ch(query, timeout=300):
    req = urllib.request.Request(CH, data=query.encode(), method="POST")
    for attempt in range(4):
        try:
            with urllib.request.urlopen(req, timeout=timeout) as r:
                return r.read().decode()
        except Exception as e:
            if attempt == 3:
                raise
            print(f"  retry {attempt+1} after error: {e}", file=sys.stderr)
            time.sleep(10 * (attempt + 1))

def main():
    print("== dataset currency check ==")
    meta = ch("SELECT min(created_at), max(created_at), count() FROM github_events FORMAT TSV")
    print(meta)
    (RAW / "clickhouse_meta.txt").write_text(
        "min_created_at\tmax_created_at\trows\n" + meta +
        f"run_utc\t{time.strftime('%Y-%m-%dT%H:%M:%SZ', time.gmtime())}\n")

    print("== broad cohort discovery (first_seen basis, D2) ==")
    q = """
WITH candidates AS (
    SELECT repo_name FROM github_events
    WHERE event_type = 'WatchEvent'
    GROUP BY repo_name HAVING count() >= 5000
),
firstseen AS (
    SELECT repo_name, min(created_at) AS first_seen
    FROM github_events
    WHERE repo_name IN (SELECT repo_name FROM candidates)
    GROUP BY repo_name
    HAVING first_seen BETWEEN '2023-01-14 00:00:00' AND '2025-06-30 23:59:59'
)
SELECT g.repo_name AS repo,
       any(f.first_seen) AS first_seen,
       countIf(g.created_at <= f.first_seen + INTERVAL 60 DAY) AS stars_60d,
       count() AS stars_total_archive
FROM github_events g
INNER JOIN firstseen f ON g.repo_name = f.repo_name
WHERE g.event_type = 'WatchEvent'
GROUP BY g.repo_name
HAVING stars_60d >= 5000
ORDER BY stars_60d DESC
FORMAT TSVWithNames
"""
    out = ch(q)
    (RAW / "cohort_broad.tsv").write_text(out)
    repos = [l.split("\t")[0] for l in out.strip().split("\n")[1:]]
    print(f"candidate cohort: {len(repos)} repos")

    inlist = ",".join("'" + r.replace("\\", "").replace("'", "") + "'" for r in repos)

    print("== daily WatchEvent+PushEvent series ==")
    q2 = f"""
SELECT repo_name AS repo, toDate(created_at) AS day,
       countIf(event_type='WatchEvent') AS watch_events,
       countIf(event_type='PushEvent') AS push_events
FROM github_events
WHERE event_type IN ('WatchEvent','PushEvent') AND repo_name IN ({inlist})
GROUP BY repo, day ORDER BY repo, day
FORMAT TSVWithNames
"""
    out2 = ch(q2, timeout=600)
    (RAW / "daily_series.tsv").write_text(out2)
    print(f"daily series rows: {len(out2.splitlines()) - 1}")

    print("== CreateEvent(repository) fallback creations ==")
    q3 = f"""
SELECT repo_name AS repo, min(created_at) AS create_event_ts
FROM github_events
WHERE event_type='CreateEvent' AND ref_type='repository' AND repo_name IN ({inlist})
GROUP BY repo ORDER BY repo
FORMAT TSVWithNames
"""
    out3 = ch(q3)
    (RAW / "createvent_creations.tsv").write_text(out3)
    print(f"createvent rows: {len(out3.splitlines()) - 1}")
    print("done.")

if __name__ == "__main__":
    main()