"""Check saved AI translations and one explicitly injected semantic error; no SQL parser."""
from file_series_support import *
import files_support as common
import sqlite3
from collections import defaultdict

folder = LAB/'semantic-sql/chapter-36'
E = folder/'evidence'
E.mkdir(parents=True, exist_ok=True)
common.E = E
NS, MODEL = 'file-legacy', 'LegacyBillQueryModel'
before = load(E/'protected-before.json')
cap = call('verification-capabilities', 'capabilities', ns=NS)
assert cap['data']['securityMode'] == 'none-dev-test-only'
meta = call('verification-describe', 'models', 'describe', MODEL, ns=NS)
assert set(meta['data']['data']['fields']) == {'invoiceId','customerCode','invoiceDate','billingCountry','invoiceAmount'}

source = LAB/'data/Chinook_Sqlite.sqlite'
with sqlite3.connect(source.as_uri()+'?mode=ro', uri=True) as db:
    rows = [r for r in db.execute('SELECT InvoiceId, CustomerId, InvoiceDate, BillingCountry, Total FROM Invoice')
            if '2012-01-01' <= r[2] < '2013-01-01']
by_customer, by_country = defaultdict(list), defaultdict(list)
for invoice_id, customer, date, country, amount in rows:
    by_customer[str(customer).zfill(5)].append(Decimal(str(amount)))
    by_country[country].append(Decimal(str(amount)))
total = sum((Decimal(str(r[4])) for r in rows), Decimal(0))
assert len(rows) == 83 and total == Decimal('477.53')
ranked = [{'customerCode':c,'amount':sum(v)} for c,v in by_customer.items()]
ranked.sort(key=lambda r:(-r['amount'],r['customerCode']))
countries = [{'billingCountry':c, 'amount':sum(v), 'bills':len(v), 'averageAmount':sum(v)/len(v)}
             for c,v in sorted(by_country.items())]
assert '00999' not in by_customer
assert max(Decimal(str(r[4])) for r in rows) < 25
ties = {str(amount):[r['customerCode'] for r in ranked if r['amount']==amount]
        for amount in {r['amount'] for r in ranked}}
ties = {a:customers for a,customers in ties.items() if len(customers)>1}
assert ties, 'Need actual tied amounts for the secondary ordering case'

q = folder/'queries'
baseline = LAB/'semantic-sql/chapter-35/queries'
cases = [
    ('36-baseline-top', baseline/'35-top-customers.json', baseline/'35-top-customers.sql', ranked[:3], 'prior reviewed baseline'),
    ('36-baseline-having', baseline/'35-having.json', baseline/'35-having.sql', [r for r in ranked if r['amount']>25], 'prior reviewed baseline'),
    ('36-average-total', q/'36-average-total.json', q/'36-average-total.sql', [{'amount':total,'bills':len(rows),'averageAmount':total/len(rows)}], 'current AI translation'),
    ('36-average-countries', q/'36-average-countries.json', q/'36-average-countries.sql', countries, 'current AI translation'),
    ('36-rank-all', q/'36-rank-all.json', q/'36-rank-all.sql', ranked, 'current AI translation'),
    ('36-empty-details', q/'36-empty-details.json', q/'36-empty-details.sql', [], 'current AI translation'),
    ('36-empty-total', q/'36-empty-total.json', q/'36-empty-total.sql', [{'amount':None,'bills':0,'averageAmount':None}], 'current AI translation'),
]
ledger = []
for label, payload, sql, expected, origin in cases:
    call(label+'-validate','query','validate',MODEL,'--payload',payload,ns=NS)
    executed = call(label+'-execute','query','execute',MODEL,'--payload',payload,ns=NS)
    same(executed['data']['items'], expected)
    ledger.append({'case':label,'payload':str(payload.relative_to(LAB/'semantic-sql')),
                   'payloadSha256':sha(payload),'sql':str(sql.relative_to(LAB/'semantic-sql')),
                   'sqlSha256':sha(sql),'origin':origin,'resultRows':len(expected)})
    print(label,len(expected),flush=True)

# The following payload is deliberately wrong for chapter 35's HAVING SQL.
control = q/'36-wrong-stage.json'
valid = call('36-wrong-stage-validate','query','validate',MODEL,'--payload',control,ns=NS)
wrong = call('36-wrong-stage-execute','query','execute',MODEL,'--payload',control,ns=NS)
assert valid['success'] and wrong['data']['items'] == []
assert [r for r in ranked if r['amount']>25] == [{'customerCode':'00026','amount':Decimal('25.84')}]
save(E/'wrong-stage-comparison.json',{'kind':'intentional teaching mutation, not an observed LLM failure',
    'inputSql':'chapter-35/queries/35-having.sql','correctDsl':'chapter-35/queries/35-having.json',
    'mutatedDsl':'chapter-36/queries/36-wrong-stage.json','validationPassed':True,
    'expectedCustomerCodes':['00026'],'actualItems':[], 'preservesInputMeaning':False,
    'reason':'HAVING sum(invoiceAmount)>25 was incorrectly moved to a source-row slice invoiceAmount>25'})

bad = call('36-invalid-order-alias-validate','query','validate',MODEL,'--payload',q/'36-invalid-order-alias.json',ns=NS,required=False)
assert not bad['success'], ('Unexpected acceptance of unknown ORDER BY alias',bad)
save(E/'ai-review.json', {'nativeSqlParserUsed':False,'reviews':[
    {'sql':'36-invalid-order-alias.sql','decision':'report undefined totalAmount; do not guess a replacement',
     'negativeControl':'invalid DSL separately sent to semantic validator only'},
    {'sql':'36-ambiguous-aggregate-where.sql','decision':'amount is SUM(invoiceAmount); ask whether the intent is row filtering or aggregate filtering',
     'dslGenerated':False,'executed':False,'reason':'Moving WHERE to HAVING without confirmation would change the question'}]})

for relative,digest in before.items():
    assert sha(LAB/relative)==digest,relative
plain_average = sum((r['averageAmount'] for r in countries),Decimal(0))/len(countries)
save(E/'oracle.json',{'source':str(source),'sourceSha256':sha(source),'bills':len(rows),'amount':str(total),
    'overallAverage':str(total/len(rows)),'countryAverageUnweighted':str(plain_average),
    'countryGroups':len(countries),'tiedAmounts':ties,
    'countryResults':[{k:str(v) if isinstance(v,Decimal) else v for k,v in r.items()} for r in countries]})
save(E/'translation-ledger.json',ledger)
save(E/'acceptance.json',{'launcher':'0.1.42','cli':'0.1.25','runtime':URL,'namespace':NS,'model':MODEL,
    'normalExecutions':len(cases),'baselineReplays':2,'newPositiveExecutions':len(cases)-2,
    'intentionalWrongMeaningExecutions':1,'validationRejections':1,'rejectedPayloadExecuted':False,
    'overallAverage':str(total/len(rows)),'unweightedCountryAverage':str(plain_average),
    'emptyAggregateObserved':load(E/'36-empty-total-execute.json')['data']['items'],
    'wrongMeaningPassedValidation':True,'wrongMeaningDetectedByIndependentOracle':True,
    'normalCasesMatchOriginalRows':True,'protectedFilesUnchanged':True,
    'nativeSqlParserTested':False,'productionIdentityTested':False,'separateCleanCodexConversationTested':False,
    'llmRepeatedGenerationBenchmarkRun':False,'replayBoundary':'saved DSL replay, not regeneration of AI translations'})
print(json.dumps(load(E/'acceptance.json'),ensure_ascii=True),flush=True)