"""Recheck #163/#164 using release assets and the public CLI; preserve old evidence."""
from composition_support import *
import composition_support as support
from decimal import Decimal
from collections import defaultdict
import shutil, sqlite3

support.URL = 'http://127.0.0.1:18068'
e = LAB/'evidence/derived-interfaces-0.1.42'
requests=e/'requests'; requests.mkdir(exist_ok=True)
fixture=load(PROJECT/'fixture.json')
protected=[Path(fixture['database']), *list((PROJECT/'models').rglob('*'))]
before={str(p):sha(p) for p in protected if p.is_file()}
call(e,'capabilities','capabilities')
call(e,'add-datasource','datasources','add','--name',fixture['datasource'],'--type','sqlite','--jdbc-url','jdbc:sqlite:'+fixture['database'],'--replace')
call(e,'test-datasource','datasources','test',fixture['datasource'])
call(e,'bind-datasource','datasources','bind','--namespace',NS,'--data-source',fixture['datasource'])
call(e,'validate-models','models','validate','--models-dir',PROJECT/'models')
call(e,'add-bundle','bundles','add','--name',fixture['bundle'],'--path',PROJECT/'models','--replace')
for model in fixture['models']:
    call(e,'refresh-'+model,'models','refresh','--model',model)
    call(e,'describe-'+model,'models','describe',model)

with sqlite3.connect(Path(fixture['database']).as_uri()+'?mode=ro',uri=True) as db:
    totals=defaultdict(Decimal)
    for cid,date,amount in db.execute('select CustomerId,InvoiceDate,Total from Invoice'):
        if '2012-01-01'<=date<'2013-01-01':totals[cid]+=Decimal(str(amount))
assert len(totals)==47 and sum(totals.values())==Decimal('477.53')

passed=[]; rejected=[]
def run(name,text,expected=None):
    path=requests/(name+'.fsscript');path.write_text(text,encoding='utf-8')
    validation=call(e,name+'-validate','compose','validate','--script',path)
    assert validation['data']['valid'],validation
    preview=call(e,name+'-preview','compose','preview','--script',path)
    assert preview['data']['valid'],preview
    result=call(e,name+'-execute','compose','execute','--script',path)
    assert result['data']['valid'],result
    rows=result['data']['value']['plans']
    if expected is not None:
        assert len(rows)==len(expected),(name,len(rows),len(expected))
        for actual,wanted in zip(rows,expected):
            for k in set(actual)|set(wanted):
                a,b=actual.get(k),wanted.get(k)
                if isinstance(b,(float,int,Decimal)) and not isinstance(b,bool):
                    assert a is not None and abs(Decimal(str(a))-Decimal(str(b)))<Decimal('1e-9'),(name,k,a,b)
                else:assert a==b,(name,k,a,b)
    passed.append({'case':name,'rows':len(rows),'requestSha256':sha(path),'expectedRowsChecked':expected is not None})
    print(name,'PASS',len(rows),flush=True)
    return rows

def reject(name,text,code,parameter=None):
    path=requests/(name+'.fsscript');path.write_text(text,encoding='utf-8')
    obj=call(e,name+'-validate','compose','validate','--script',path,required=False)
    assert obj.get('success') is False,(name,obj)
    error=obj['error']
    assert error['code']=='COMPOSE_SCRIPT_INVALID' and obj['diagnostics']['attributes']['composeErrorCode']==code,(name,obj)
    assert 'ClassCastException' not in json.dumps(obj) and 'LaunchedClassLoader' not in json.dumps(obj)
    if parameter:
        assert parameter in json.dumps(obj),(name,parameter,obj)
        assert error['safeToAutoRepair'] is False,(name,error)
    rejected.append({'case':name,'error':error,'diagnostics':obj.get('diagnostics'),'executed':False})
    print(name,'EXPECTED REJECTION',code,flush=True)

old=LAB/'issues/derived-interfaces-0.1.39/requests'
base_expected=[{'customer_id':cid,'bill_amount':value} for cid,value in sorted(totals.items())]
double_expected=[dict(row,double_amount=row['bill_amount']*2) for row in base_expected]
reject('163-original', (old/'01-derived-calculated-fields.fsscript').read_text(encoding='utf-8'),'DERIVED_PARAMETER_UNSUPPORTED','calculatedFields')
run('163-inline-control',(old/'01-inline-expression-control.fsscript').read_text(encoding='utf-8'),double_expected)
run('164-original-base',(old/'02-dsl-plan-model.fsscript').read_text(encoding='utf-8'),base_expected)
run('164-query-control',(old/'02-query-method-control.fsscript').read_text(encoding='utf-8'),base_expected)
join_expected=load(Path(load(LAB/'evidence/composition-0.1.39/latest-chapter27-final.json')['evidenceDirectory'])/'results.json')['03-reconcile-customers']
run('164-original-join',(old/'02-dsl-join-plan-model.fsscript').read_text(encoding='utf-8'),join_expected)
original_join=LAB/'evidence/composition-0.1.39/chapter27/20261024-221759-781972/inputs/unsupported-derived-calculated-fields.fsscript'
reject('163-original-join',original_join.read_text(encoding='utf-8'),'DERIVED_PARAMETER_UNSUPPORTED','calculatedFields')

base=(old/'02-query-method-control.fsscript').read_text(encoding='utf-8').split('return {plans:')[0]
for entry in ['query','model','source']:
    prefix='bills.query({' if entry=='query' else 'dsl({'+entry+': bills,'
    for param,value in [('calculatedFields',"[{name:'double_amount',expression:'bill_amount * 2'}]"),('having',"[{field:'bill_amount',op:'>',value:10}]")]:
        reject('163-'+entry+'-'+param,base+'return {plans: '+prefix+"columns:['customer_id','bill_amount'],"+param+':'+value+'})};','DERIVED_PARAMETER_UNSUPPORTED',param)
    for tag,value in [('empty','[]'),('null','null')]:
        run('163-'+entry+'-'+tag,base+'return {plans: '+prefix+"columns:['customer_id','bill_amount'],orderBy:['customer_id'],limit:100,calculatedFields:"+value+',having:'+value+'})};',base_expected)

# A computed alias must be filtered in a following stage.
two_stage=base+"const doubled=bills.query({columns:['customer_id','bill_amount','bill_amount * 2 as double_amount']});\nreturn {plans:dsl({model:doubled,columns:['customer_id','bill_amount','double_amount'],slice:[{field:'double_amount',op:'>',value:20}],orderBy:['customer_id'],limit:100})};"
run('164-derived-and-alias-filter',two_stage,[r for r in double_expected if r['double_amount']>20])

# Re-run the existing published scripts byte-for-byte, comparing every returned value.
for chapter,pointer,kind in [(26,'latest-chapter26.json','chapter26'),(27,'latest-chapter27-final.json','chapter27')]:
    historic=Path(load(LAB/'evidence/composition-0.1.39'/pointer)['evidenceDirectory'])
    old_results=load(historic/'results.json')
    for name,wanted in old_results.items():
        text=(PROJECT/'queries'/('chapter-'+str(chapter))/(name+'.fsscript')).read_text(encoding='utf-8')
        run(kind+'-'+name,text,wanted)

assert all(sha(Path(p))==h for p,h in before.items())
assert all(sha(Path(p))==h for p,h in fixture['originalFiles'].items())
save(e/'acceptance.json',{'launcher':'0.1.42','cli':'0.1.25','runtime':support.URL,'namespace':NS,'identity':'anonymous local dev/test; Console/FAP disabled','passed':passed,'expectedRejections':rejected,'modelsAndDatabaseUnchanged':True,'originalFilesUnchanged':True,'oracle':{'source':'read-only raw Invoice rows; Decimal per-customer totals','customers':len(totals),'amount':str(sum(totals.values()))},'cleanSeparateCodexConversationTested':False})
print(json.dumps({'passedExecutions':len(passed),'expectedRejections':len(rejected)},ensure_ascii=True),flush=True)