import csv,re,io
from decimal import Decimal
txt=open('input.md').read()
body=txt.split('```csv\n')[1].split('```')[0]
rows=list(csv.DictReader(io.StringIO(body)))
assert len(rows)==14
ex=[];out=[]
def e(r,field,issue,treat,dec):
    ex.append(dict(source_row=r['source_row'],expense_id=r['expense_id'],field=field,issue=issue,treatment=treat,decision_needed=dec))
seen={}
for r in rows:
    o=dict(r); n=r['source_row']; flags=[]; inc=True; why=[]
    o['person_clean']=r['person'].strip()
    if r['person']!=o['person_clean']: e(r,'person','Surrounding whitespace','Trimmed into person_clean; original kept','None')
    o['cost_center_clean']=r['cost_center'].upper()
    if r['cost_center']!=o['cost_center_clean']: e(r,'cost_center','Lowercase code','Uppercased into cost_center_clean; original kept','None')
    d=r['date']
    if re.fullmatch(r'\d{4}-\d{2}-\d{2}',d): o['date_iso']=d
    else:
        o['date_iso']=''; inc=False; why.append('date locale unknown')
        e(r,'date','Ambiguous date 03/04/2026 (3 Apr or 4 Mar)','date_iso left blank; original unchanged; excluded from totals','Confirm date format/locale')
    a=r['amount']; o['amount_value']=''; o['currency_clean']=r['currency']
    if re.fullmatch(r'\d{1,3}(,\d{3})*(\.\d+)?|\d+(\.\d+)?',a) and r['currency']=='USD' or re.fullmatch(r'\d+\.\d+',a):
        o['amount_value']=str(Decimal(a.replace(',','')))
    elif re.fullmatch(r'\(\d+(\.\d+)?\)',a) and r['currency']=='USD':
        o['amount_value']='-'+a[1:-1]
    else:
        inc=False
        if a=='TBC':
            why.append('amount unknown'); e(r,'amount','Amount is TBC (awaiting invoice)','amount_value blank (not zero); excluded','Provide final invoice amount')
        else:
            why.append('amount locale unknown'); e(r,'amount','Amount 1.234,50 locale unknown (1234.50 or 1.2345)','amount_value blank; excluded','Confirm number locale')
    if not r['currency']:
        inc=False; why.append('currency missing'); e(r,'currency','Currency missing','Left blank; no currency assumed; excluded','Supply currency')
    key=tuple(r[k] for k in r if k!='source_row')
    status='ok'
    if key in seen:
        status='exact_duplicate_of_row_'+seen[key]; inc=False; why.append('exact duplicate (2nd occurrence)')
        e(r,'record','Exact duplicate of row '+seen[key]+' apart from source_row','Flagged and retained; excluded from totals so first occurrence counts once','Confirm duplicate; approve removal or keep')
    else: seen[key]=n
    o['record_flag']=status
    o['conflict_flag']=''
    o['in_scoped_total']=''; o['exclusion_reason']=''
    out.append((o,inc,why))
ids={}
for o,_,_ in out: ids.setdefault(o['expense_id'],[]).append(o)
for k,v in ids.items():
    if len({(x['amount']) for x in v})>1 and len(v)>1:
        rs=[x['source_row'] for x in v]
        for x in v:
            x['conflict_flag']='conflict_same_id_rows_'+'_'.join(rs)
        for o,_,_ in out:
            if o['expense_id']==k:
                pass
        ex.append(dict(source_row='/'.join(rs),expense_id=k,field='amount',issue='Same expense_id with differing amounts (120.00 vs 140.00)',treatment='Both retained unmerged; both excluded from totals',decision_needed='Which amount is correct, or are they separate expenses?'))
fin=[]
for o,inc,why in out:
    if o['conflict_flag']: inc=False; why.append('conflicting expense_id')
    o['in_scoped_total']='Y' if inc else 'N'; o['exclusion_reason']='; '.join(why)
    fin.append(o)
cols=list(rows[0].keys())+['person_clean','cost_center_clean','date_iso','currency_clean','amount_value','record_flag','conflict_flag','in_scoped_total','exclusion_reason']
with open('cleaned.csv','w',newline='') as f:
    w=csv.DictWriter(f,cols,quoting=csv.QUOTE_NONNUMERIC if False else csv.QUOTE_MINIMAL); w.writeheader(); w.writerows(fin)
with open('exceptions.csv','w',newline='') as f:
    w=csv.DictWriter(f,list(ex[0].keys())); w.writeheader(); w.writerows(ex)
tot={}
inc_rows={}
for o in fin:
    if o['in_scoped_total']=='Y':
        tot[o['currency']]=tot.get(o['currency'],Decimal(0))+Decimal(o['amount_value']); inc_rows.setdefault(o['currency'],[]).append(o['source_row'])
print(tot,inc_rows,len(ex))
print([ (o['source_row'],o['exclusion_reason']) for o in fin if o['in_scoped_total']=='N'])