"""Python 3 standard library. Run python pipeline.py to regenerate all data and results."""
import csv, json, random, sqlite3, statistics, math
from datetime import datetime, timedelta
from pathlib import Path
ROOT=Path(__file__).resolve().parent
SEED=20261011
SNAP=datetime(2025,7,1)
START=datetime(2025,1,1)
TARGETS={'Urgent':(1,8),'High':(4,24),'Normal':(12,72),'Low':(24,120)}
def stamp(d): return d.isoformat(timespec='seconds') if d else ''
def write_csv(name,rows):
    with (ROOT/name).open('w',newline='',encoding='utf-8') as f:
        w=csv.DictWriter(f,fieldnames=list(rows[0]));w.writeheader();w.writerows(rows)
def pctile(a,p):
    a=sorted(a);i=(len(a)-1)*p;l=int(i);return a[l]+(a[min(l+1,len(a)-1)]-a[l])*(i-l) if a else None
def generate():
    rng=random.Random(SEED); rows=[]
    for i in range(1800):
        created=START+timedelta(minutes=rng.randrange(181*24*60))
        channel=rng.choices(['Email','WhatsApp','Web chat'],[.45,.35,.20])[0]
        priority=rng.choices(list(TARGETS),[.08,.22,.55,.15])[0]
        category=rng.choice(['Delivery','Billing','Account access','Product information','Refund'])
        rs,ss=TARGETS[priority]
        response=round(rs*rng.lognormvariate(-.4,.8)*{'Email':1.5,'WhatsApp':1,'Web chat':.5}[channel],2)
        if created.month>=5: response=round(response*1.25,2)
        resolution=round(max(response+.1,ss*rng.lognormvariate(-.3,.8)*(1.5 if category=='Refund' else 1)),2)
        if rng.random()<.08: resolution+=24*190
        first=created+timedelta(hours=response);resolved=created+timedelta(hours=resolution)
        first=first if first<=SNAP else None;resolved=resolved if resolved<=SNAP else None
        reopened=int(bool(resolved) and rng.random()<.12)
        csat=''
        if resolved and rng.random()<.48:
            csat=rng.choices([1,2,3,4,5],[.15,.20,.30,.25,.10] if resolution>ss else [.03,.05,.12,.40,.40])[0]
        rows.append(dict(ticket_id=f'T{i+1:05}',created_at=stamp(created),first_response_at=stamp(first),resolved_at=stamp(resolved),priority=priority,channel=channel,category=category,reopened=reopened,csat=csat))
    for i in range(25): rows[i]['channel']=' '+rows[i]['channel'].lower()+' '
    for i in range(30,40): rows[i]['priority']=''
    invalid=[r for r in rows if r['csat']!=''][:5]
    for r in invalid:r['csat']=7
    raw=rows+[dict(r) for r in rows[100:120]]
    write_csv('tickets_raw.csv',raw)
    return raw
def clean(raw):
    seen={};out=[];log=[]
    for index,r0 in enumerate(raw,2):
        r=dict(r0);tid=r['ticket_id']
        if tid in seen:
            assert r==seen[tid], 'Conflicting duplicate needs review'
            log.append(dict(row=index,ticket_id=tid,issue='Exact duplicate',action='Removed duplicate record'));continue
        seen[tid]=dict(r)
        label=r['channel'].strip().lower();canonical={'email':'Email','whatsapp':'WhatsApp','web chat':'Web chat'}[label]
        if canonical!=r['channel']:log.append(dict(row=index,ticket_id=tid,issue='Channel formatting',action='Trimmed and standardised'))
        r['channel']=canonical
        if not r['priority']:log.append(dict(row=index,ticket_id=tid,issue='Missing priority',action='Retained in volumes; excluded from target metrics'))
        if r['csat']!='' and int(r['csat']) not in range(1,6):
            log.append(dict(row=index,ticket_id=tid,issue='Invalid CSAT',action='Set missing; excluded from CSAT score denominator'));r['csat']=''
        out.append(r)
    write_csv('tickets_clean.csv',out);write_csv('cleaning_log.csv',log)
    return out,log
def enrich(rows):
    out=[]
    for r in rows:
        created=datetime.fromisoformat(r['created_at']);first=datetime.fromisoformat(r['first_response_at']) if r['first_response_at'] else None;resolved=datetime.fromisoformat(r['resolved_at']) if r['resolved_at'] else None
        assert START<=created<SNAP and (first is None or created<=first<=SNAP)
        assert resolved is None or (first is not None and first<=resolved<=SNAP)
        age=(SNAP-created).total_seconds()/3600;rh=(first-created).total_seconds()/3600 if first else None;sh=(resolved-created).total_seconds()/3600 if resolved else None
        target=TARGETS.get(r['priority']);re=int(bool(target) and (rh is not None or age>=target[0]));se=int(bool(target) and (sh is not None or age>=target[1]))
        rm=int(bool(target) and rh is not None and rh<=target[0]);sm=int(bool(target) and sh is not None and sh<=target[1])
        out.append({**r,'month':created.strftime('%Y-%m'),'age_h':age,'response_h':rh,'resolution_h':sh,'open':int(resolved is None),'response_eligible':re,'response_met':rm,'response_breach':re-rm,'resolution_eligible':se,'resolution_met':sm,'resolution_breach':se-sm,'survey_valid':int(r['csat']!=''),'satisfied':int(r['csat']!='' and int(r['csat'])>=4)})
    return out
def summarise(a):
    n=len(a);responded=[r['response_h'] for r in a if r['response_h'] is not None];resolved=[r['resolution_h'] for r in a if r['resolution_h'] is not None]
    total=lambda k:sum(r[k] for r in a)
    rate=lambda x,y:x/y if y else None
    return dict(tickets=n,open=total('open'),resolved=len(resolved),response_eligible=total('response_eligible'),response_met=total('response_met'),response_breach=total('response_breach'),response_sla=rate(total('response_met'),total('response_eligible')),resolution_eligible=total('resolution_eligible'),resolution_met=total('resolution_met'),resolution_breach=total('resolution_breach'),resolution_sla=rate(total('resolution_met'),total('resolution_eligible')),median_response_h=statistics.median(responded) if responded else None,p90_response_h=pctile(responded,.9),median_resolution_h=statistics.median(resolved) if resolved else None,valid_surveys=total('survey_valid'),satisfied=total('satisfied'),csat=rate(total('satisfied'),total('survey_valid')),survey_response=rate(total('survey_valid'),len(resolved)),reopen_rate=rate(sum(int(r['reopened']) for r in a),len(resolved)),open_over_7d=sum(r['open'] and r['age_h']>168 for r in a))
if __name__=='__main__':
    rows,log=clean(generate()); enriched=enrich(rows)
    assert len(rows)==1800 and len(set(r['ticket_id'] for r in rows))==1800
    assert sum(r['issue']=='Exact duplicate' for r in log)==20
    result={'source':'Original synthetic data generated with ChatGPT; no external or employer records','seed':SEED,'snapshot':stamp(SNAP),'overview':summarise(enriched),'cleaning':{k:sum(x['issue']==k for x in log) for k in sorted(set(x['issue'] for x in log))}}
    for key in ['channel','category','month','priority']:
        result[key]=[{key:value,**summarise([r for r in enriched if r[key]==value])} for value in sorted(set(r[key] for r in enriched))]
    # Execute actual SQL on ticket-level data and reconcile against independent Python aggregation.
    db=sqlite3.connect(':memory:');db.row_factory=sqlite3.Row
    keys=list(enriched[0]);db.execute('CREATE TABLE tickets ('+','.join(k+(' REAL' if k not in ['ticket_id','created_at','first_response_at','resolved_at','priority','channel','category','month'] else ' TEXT') for k in keys)+')')
    db.executemany('INSERT INTO tickets VALUES ('+','.join('?' for _ in keys)+')',[[r[k] if r[k]!='' else None for k in keys] for r in enriched])
    sections=(ROOT/'analysis.sql').read_text().split('-- query: ')[1:];sql_results={}
    for section in sections:
        name,q=section.split('\n',1);sql_results[name]=[dict(x) for x in db.execute(q)]
    for group in sql_results['channels']:
        py=next(x for x in result['channel'] if x['channel']==group['channel'])
        for k in ['tickets','response_met','response_eligible','open','valid_surveys','satisfied']:assert group[k]==py[k],k
    # Boundary fixtures: exact deadline qualifies, not-yet-due missing response stays pending.
    fixture=dict(ticket_id='test',created_at=stamp(SNAP-timedelta(hours=4)),first_response_at=stamp(SNAP),resolved_at='',priority='High',channel='Email',category='Billing',reopened=0,csat='')
    assert enrich([fixture])[0]['response_met']==1
    fixture['created_at']=stamp(SNAP-timedelta(hours=3));fixture['first_response_at']=''
    assert enrich([fixture])[0]['response_eligible']==0
    fixture['priority']='';assert enrich([fixture])[0]['resolution_eligible']==0
    for field,name in [(result,'summary.json'),(sql_results,'sql_results.json'),(enriched,'tickets_enriched.json')]: (ROOT/name).write_text(json.dumps(field,indent=2))
    print(json.dumps(result['overview'],indent=2));print('PASS: uniqueness, cleaning counts, chronology, SQL/Python reconciliation and deadline boundary checks.')