"""Anonymised assessment reconstruction. Python 3.10+, standard library only.
Run: python analysis.py
Writes results.json. No original company or customer data are included.
"""
import csv, json, math, sqlite3
from pathlib import Path
ROOT=Path(__file__).resolve().parent

def database():
 db=sqlite3.connect(':memory:');db.row_factory=sqlite3.Row
 db.executescript('CREATE TABLE assignments(event_id TEXT,client_id TEXT,arm TEXT,assigned_at TEXT); CREATE TABLE purchase_export(order_id TEXT,client_id TEXT,order_value REAL,purchased_at TEXT);')
 for table in ('assignments','purchase_export'):
  with (ROOT/f'{table}.csv').open(newline='') as f:
   reader=csv.reader(f); columns=next(reader)
   db.executemany(f'INSERT INTO {table} VALUES ({",".join("?" for _ in columns)})',reader)
 return db

def integrity(db):
 if db.execute("SELECT COUNT(*) FROM assignments WHERE arm NOT IN ('A','B') OR client_id IS NULL OR assigned_at IS NULL").fetchone()[0]:raise ValueError('Invalid assignment')
 if db.execute('SELECT COUNT(*) FROM purchase_export WHERE order_value<0 OR order_id IS NULL OR client_id IS NULL OR purchased_at IS NULL').fetchone()[0]:raise ValueError('Invalid order')
 if db.execute('SELECT COUNT(*) FROM (SELECT order_id FROM (SELECT DISTINCT * FROM purchase_export) GROUP BY order_id HAVING COUNT(*)>1)').fetchone()[0]:raise ValueError('Conflicting order ID')
 if db.execute('SELECT COUNT(*) FROM (SELECT a.client_id FROM assignments a JOIN (SELECT client_id,MIN(assigned_at) AS t FROM assignments GROUP BY client_id) f ON a.client_id=f.client_id AND a.assigned_at=f.t GROUP BY a.client_id HAVING COUNT(DISTINCT arm)>1)').fetchone()[0]:raise ValueError('Ambiguous earliest assignment')
 if db.execute('SELECT COUNT(*) FROM purchase_export p LEFT JOIN assignments a ON p.client_id=a.client_id WHERE a.client_id IS NULL').fetchone()[0]:raise ValueError('Orphan order')

def metrics(db,filename):return [dict(r) for r in db.execute((ROOT/filename).read_text())]

def main():
 db=database();integrity(db);checks=[]
 def check(name,condition):
  if not condition:raise AssertionError(name)
  checks.append(name)
 before=metrics(db,'flawed.sql');after=metrics(db,'corrected.sql')
 check('Eight assigned users remain in the denominator',sum(r['users'] for r in after)==8)
 check('Earliest assignment keeps u01 in A',db.execute("SELECT arm FROM assignments WHERE client_id='u01' ORDER BY assigned_at,event_id LIMIT 1").fetchone()[0]=='A')
 check('A metrics reconcile',after[0]=={'arm':'A','users':4,'buyers':2,'revenue':2000.0,'rpu':500.0})
 check('B metrics reconcile',after[1]=={'arm':'B','users':4,'buyers':1,'revenue':1500.0,'rpu':375.0})
 check('The deliberately flawed query reproduces A RPU 1000',before[0]['rpu']==1000)
 check('Exactly one duplicate export row',db.execute('SELECT COUNT(*) FROM purchase_export').fetchone()[0]-db.execute('SELECT COUNT(*) FROM (SELECT DISTINCT * FROM purchase_export)').fetchone()[0]==1)
 db.execute("INSERT INTO purchase_export VALUES ('o04','u02',1000,'2026-01-04 10:00:00')")
 check('A second same-value order is retained',metrics(db,'corrected.sql')[0]['revenue']==3000)
 db.execute("DELETE FROM purchase_export WHERE order_id='o04'")
 db.execute("INSERT INTO purchase_export VALUES ('late','u02',500,'2026-01-10 10:00:00')")
 check('Out-of-window purchases excluded',metrics(db,'corrected.sql')[0]['revenue']==2000)
 db.execute("DELETE FROM purchase_export WHERE order_id='late'")
 db.execute("INSERT INTO purchase_export VALUES ('o01','u01',999,'2026-01-03 10:00:00')")
 try:integrity(db);failed=False
 except ValueError:failed=True
 check('Conflicting order IDs stop analysis',failed)
 db.execute("DELETE FROM purchase_export WHERE order_value=999")
 db.execute("INSERT INTO assignments VALUES ('tie','u02','B','2026-01-01 09:00:00')")
 try:integrity(db);failed=False
 except ValueError:failed=True
 check('Conflicting earliest assignments stop analysis',failed)
 db.execute("DELETE FROM assignments WHERE event_id='tie'")
 with (ROOT/'reported_counts.csv').open(newline='') as f:counts=list(csv.DictReader(f))
 a,b=counts;na,nb=int(a['included']),int(b['included']);xa,xb=int(a['buyers_included']),int(b['buyers_included']);miss_a=3000-na;miss_b=3000-nb
 pa,pb=xa/na,xb/nb;pooled=(xa+xb)/(na+nb);z=(pb-pa)/math.sqrt(pooled*(1-pooled)*(1/na+1/nb));p=math.erfc(abs(z)/math.sqrt(2));srm_chi=(na-nb)**2/(na+nb);srm_p=math.erfc(math.sqrt(srm_chi/2))
 check('Reported population excludes 767 assigned users',miss_a+miss_b==767)
 check('18 net recovered B buyers is the first strict reversal',xb+17==xa and xb+18>xa)
 # Full-population result remains unidentified: unknown is not zero.
 out={'disclosure':'Assessment-derived aggregate counts; miniature rows reconstructed and synthetic. These are reporting corrections, not revenue earned or a valid causal rollout result.','before':before,'after':after,'reported':{'assigned':6000,'included':na+nb,'excluded':miss_a+miss_b,'excluded_pct':100*(miss_a+miss_b)/6000,'a_rate_pct':pa*100,'b_rate_pct':pb*100,'difference_pp':(pb-pa)*100,'descriptive_two_sided_p':p,'included_population_srm_chi_square':srm_chi,'included_population_srm_p':srm_p,'a_inclusion_pct':na/3000*100,'b_inclusion_pct':nb/3000*100,'minimum_net_b_recovered_buyers':18,'full_population_effect':'unknown','worst_case_difference_pp':[(xb-xa-miss_a)/3000*100,(xb+miss_b-xa)/3000*100]},'checks_passed':len(checks),'checks':checks}
 (ROOT/'results.json').write_text(json.dumps(out,indent=2)+'\n');print(json.dumps(out,indent=2))
if __name__=='__main__':main()