"""Generate the page tables for /assessment-review-cross-listing/ from the frozen datasets, so that no number on the page is typed by
hand: one 32-row study table with cohort labels (cohorts 1, 2 and 3 in roster order), then the four appendix rows, separately
labelled and outside every denominator. Cells: displayed count / one-decimal rating linked to the canonical profile URL, or the
protocol outcome; every row carries the retrieval date. Output: draft-tables-<date>.md. Usage: python3 scripts/build-tables.py 2026-09-02"""
import json,sys,re,os
from collections import defaultdict
date=sys.argv[1]; SITES=('Capterra','GetApp','Software Advice','G2','Trustpilot')
def load(c):
    f=f'data/signatures-{c}-{date}.json'; return json.load(open(f)) if os.path.exists(f) else []
def cell(r):
    if not r.get('saved'): return 'not found in steps 1 to 3' if 'no deter' not in (r.get('status') or '') else 'not found (no deterministic pattern)'
    if r.get('saved') and not r.get('category_ok',True): return f'rejected: <a href="{r["url"]}">listing</a> in category "{r.get("listing_category")}", outside the HR family'
    if r.get('capture_status','page')!='page': return f'<a href="{r["url"]}">profile found in discovery</a>; capture unresolved ({r.get("http_status") or r["capture_status"]})'
    if r.get('count') is None: return f'<a href="{r["url"]}">profile</a>, no count or rating displayed'
    if not r.get('eligible'): return f'<a href="{r["url"]}">{r["count"]}</a>, no rating displayed'
    return f'<a href="{r["url"]}">{r["count"]} / {r["rating_display"]}</a>'
def groups_for(rows,minc=0):
    el=[r for r in rows if r.get('eligible') and r['count']>=minc]; g=defaultdict(list)
    for r in el: g[(r['count'],r['rating_display'])].append(r)
    def same(a,b):
        ka,kb=a.get('listing_key') or '',b.get('listing_key') or ''; return bool(ka and kb) and (ka==kb or ka in kb or kb in ka)
    out=[]
    for v in g.values():
        if len(v)<2: continue
        cl=[]
        for r in v:
            for c in cl:
                if all(same(r,q) for q in c): c.append(r); break
            else: cl.append([r])
        out+=[c for c in cl if len(c)>=2]
    return el,out
def date_of(rows):
    ds=sorted({(r.get('fetched_utc') or '')[:10] for r in rows if r.get('fetched_utc')}); return ', '.join(ds) if ds else date
def table(cohorts,with_cohort=True):
    hdr='| Cohort | Product | Capterra | GetApp | Software Advice | G2 | Trustpilot | Matching group | R_p | Read (UTC) |' if with_cohort else '| Product | Capterra | GetApp | Software Advice | G2 | Trustpilot | Matching group | R_p | Read (UTC) |'
    lines=[hdr,'|'+'---|'*(hdr.count('|')-1)]
    for label,c in cohorts:
        rows=load(c)
        for p in dict.fromkeys(r['product'] for r in rows):
            pr={r['site']:r for r in rows if r['product']==p}; prow=[r for r in rows if r['product']==p]; el,gs=groups_for(prow)
            T=sum(r['count'] for r in el); D=sum(sum(r['count'] for r in v)-max(r['count'] for r in v) for v in gs)
            grp='; '.join(', '.join(r['site'] for r in v) for v in gs) or 'none'; rp=('%.3f'%(D/T)) if T and len(el)>=2 else 'n/a'
            cells=[cell(pr[s]) if s in pr else 'not run' for s in SITES]
            lines.append(('| '+label+' ' if with_cohort else '')+f"| {p} | "+' | '.join(cells)+f" | {grp} | {rp} | {date_of(prow)} |")
    return '\n'.join(lines)
study=table([('1','cohort1'),('2','cohort2'),('3','cohort3')]); appendix=table([('appendix','appendix')],with_cohort=False)
s3=open(f's3-results-cohort1-{date}.md').read(); head=s3[s3.index('## Headline sentences'):s3.index('## Displayed count')]
open(f'draft-tables-{date}.md','w').write('# Page tables generated from the frozen datasets, '+date+'\n\n'+head+'\n## Study table (32 products: cohort 1 headline frame, cohorts 2 and 3 sensitivity samples)\n\n'+study+'\n\n## Appendix (4 products read the same day, in no cohort and no denominator)\n\n'+appendix+'\n')
print('study rows',study.count('\n')-1,'| appendix rows',appendix.count('\n')-1)