#!/usr/bin/env python3
"""
Measurement script for thread lifespan and half-life analysis on GetPostingBoard.
Dataset: SQLite ledger containing posts table (id, seq, thread_id, created_at).
"""
import sqlite3
import statistics

def analyze(db_path="projects/p2p-ledger/live_ledger.sqlite"):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    # 1. Total roots & mortality
    cur.execute("SELECT COUNT(*) FROM posts WHERE thread_id IS NULL")
    total_roots = cur.fetchone()[0]

    cur.execute("""
        SELECT COUNT(r.id)
        FROM posts t
        LEFT JOIN posts r ON r.thread_id = t.id
        WHERE t.thread_id IS NULL
        GROUP BY t.id
    """)
    reply_counts = [r[0] for r in cur.fetchall()]
    lte_5 = sum(1 for c in reply_counts if c <= 5)
    zero_replies = sum(1 for c in reply_counts if c == 0)

    # 2. Spans from root to last reply
    cur.execute("""
        SELECT MAX(r.seq) - t.seq
        FROM posts t
        JOIN posts r ON r.thread_id = t.id
        WHERE t.thread_id IS NULL
        GROUP BY t.id
    """)
    full_spans = [r[0] for r in cur.fetchall() if r[0] is not None]

    # 3. Span from root to median reply arrival (half-life)
    cur.execute("""
        SELECT t.id, t.seq, r.seq
        FROM posts t
        JOIN posts r ON r.thread_id = t.id
        WHERE t.thread_id IS NULL
        ORDER BY t.id, r.seq ASC
    """)
    from collections import defaultdict
    t_replies = defaultdict(list)
    for tid, tseq, rseq in cur.fetchall():
        t_replies[(tid, tseq)].append(rseq)

    half_spans = []
    for (tid, tseq), rseqs in t_replies.items():
        if len(rseqs) >= 2:
            med_r = statistics.median(rseqs)
            half_spans.append(med_r - tseq)

    med_full = statistics.median(full_spans)
    med_half = statistics.median(half_spans)
    midpoint = (med_full + med_half) / 2.0

    print(f"Total Roots: {total_roots}")
    print(f"<= 5 replies: {lte_5}/{total_roots} ({lte_5*100.0/total_roots:.1f}%)")
    print(f"0 replies: {zero_replies}/{total_roots} ({zero_replies*100.0/total_roots:.1f}%)")
    print(f"Median Full Span (root -> last reply): {med_full} seq")
    print(f"Median Half-Arrival (root -> 50% replies arrived): {med_half} seq")
    print(f"Midpoint (Active Thread Decay Point): {midpoint:.1f} seq (~283)")

if __name__ == "__main__":
    analyze()