"""Verify actual correct results and detect successful-but-wrong window queries."""
import hashlib
import json
import sqlite3
from collections import defaultdict
from decimal import Decimal
from pathlib import Path

LAB=Path(__file__).resolve().parents[1]
ROOT=LAB/'evidence/chapter-11-diagnostics-2026-10-20'
TOLERANCE=Decimal('1E-12')


def load(p):
    return json.loads(Path(p).read_text(encoding='utf-8-sig'))


def near(a,b):
    assert abs(Decimal(str(a))-b)<TOLERANCE,(a,b)


def main():
    cases={c['name']:c for c in load(ROOT/'diagnostic-manifest.json')['cases']}
    assert len(cases)==13
    original=LAB.parent/'2026-10-13/open-data/chinook-v1.4.5/Chinook_Sqlite.sqlite'
    expected='bdf635be69850bd3be09c9a2dbeef7ddfb80036bd3ef3381383cd03b61e4a61a'
    for p in [original,LAB/'data/Chinook_Sqlite.sqlite']:
        assert hashlib.sha256(p.read_bytes()).hexdigest()==expected
    for name,digest in load(LAB/'evidence/chapter-11/model-validation.json')['modelSha256'].items():
        assert hashlib.sha256((LAB/name).read_bytes()).hexdigest()==digest
    with sqlite3.connect(original.as_uri()+'?mode=ro&immutable=1',uri=True) as db:
        invoices=list(db.execute('select InvoiceId, InvoiceDate, Total from Invoice'))
    month=defaultdict(lambda:Decimal('0'))
    count=defaultdict(int)
    for _,date,value in invoices:
        month[date[:7]]+=Decimal(str(value));count[date[:7]]+=1

    def result(tag):
        c=cases[tag]
        assert load(c['validation'])['success']
        r=load(c['response']);assert r['success']
        assert not r['data'].get('hasNext') and not r['data'].get('pagination',{}).get('hasMore')
        return r['data']['items']

    def monthly_lag(tag,first):
        rows=result(tag)
        keys=sorted(k for k in month if first<=k<'2013-01')
        assert [r['invoiceDate$yearMonth'] for r in rows]==keys
        for i,r in enumerate(rows):
            near(r['totalAmount'],month[keys[i]])
            assert r['billCount']==count[keys[i]]
            if not i:assert r.get('previousAmount') is None
            else:near(r['previousAmount'],month[keys[i-1]])
        return rows

    a=monthly_lag('03-lag-group-measure','2012-01')
    b=monthly_lag('12-lag-control-without-explain','2012-01')
    assert a==b and len(a)==12
    cross=monthly_lag('13-lag-including-prior-december','2011-12')
    assert len(cross)==13
    detail=result('02-lag-detail')
    raw=sorted((i,Decimal(str(v))) for i,d,v in invoices if '2012-01-01'<=d<'2013-01-01')
    assert len(detail)==len(raw)==83
    for idx,(r,(ident,value)) in enumerate(zip(detail,raw)):
        assert r['invoiceId']==ident;near(r['invoiceAmount'],value)
        if idx:near(r['previousAmount'],raw[idx-1][1])
        else:assert r.get('previousAmount') is None

    wrong_rows=result('04-lag-group-nested-sum')
    assert len(wrong_rows)==1,'Expected reproduction of unintended second aggregation'
    dep=result('10-lag-separate-calculations')
    november=next(r for r in dep if r['invoiceDate$yearMonth']=='2012-11')
    correct_diff=month['2012-11']-month['2012-10']
    assert Decimal(str(november['difference']))==Decimal('37.62')!=correct_diff
    assert correct_diff==Decimal('-5.00')
    assert november.get('changeRatio') is None
    assert load(cases['05-lag-group-alias']['validation'])['success'] is False
    assert load(cases['11-lag-compound-no-sum']['response'])['success'] is False

    yoy=load(cases['07-time-yoy']['explain'])['data']['sqlTrace']['finalPhysicalSql']
    assert 'WHERE' not in yoy.upper(),'The declared timeWindow.value was not translated to a period filter in the observed SQL'
    mom=load(cases['09-mom-extra-day-key-changes-grain']['explain'])['data']['sqlTrace']['finalPhysicalSql']
    assert 'cte_1."invoiceDate$id" = cte_2."invoiceDate$id"' in mom
    assert '("invoiceDate$month" + 1)' in mom
    primary=load(ROOT/'primary-sqlite-schema-check.json')
    assert not primary['invoiceTableExists']
    report=dict(success=True,meaning='Diagnosis assertions passed; not all query candidates are correct.',
        cases=13,correctMonthlyLagRows=12,correctDetailLagRows=83,crossYearLagRows=13,
        nestedSumReturnedRows=1,dependentFormulaActualNovemberDifference=str(november['difference']),
        expectedNovemberDifference=str(correct_diff),
        correctNovemberPriorAmount=str(next(r for r in a if r['invoiceDate$yearMonth']=='2012-11')['previousAmount']),
        observations=['Simple LAG is supported and independently verified',
            'Nested SUM in Stage2 is an extra aggregation, not the correct monthly LAG call',
            'Arithmetic plus window metadata emits invalid OVER placement',
            'Same-query window-output dependencies execute but give wrong differences',
            'YoY explained SQL omits timeWindow.value scope',
            'MoM month plan requires a day key and joins equal raw dates',
            'Primary SQLite has no Invoice; named Chinook and six model hashes unchanged'],
        productCodeChanged=False,runtimeRestarted=False,modelsOrDataChanged=False)
    (ROOT/'verification.json').write_text(json.dumps(report,ensure_ascii=False,indent=2),encoding='utf-8')
    print(json.dumps(report,ensure_ascii=False,indent=2))


if __name__=='__main__':main()