"""Independently check semantic outputs against original Chinook rows and fixed calendar facts."""
from chapter18_support import *
from collections import defaultdict
from datetime import date, timedelta
from decimal import Decimal
import sqlite3

e = Path(json.loads((ROOT/'latest-run.json').read_text(encoding='utf-8'))['evidenceDirectory'])
D = lambda x: Decimal(str(x))

def load(name):
    return json.loads((e/(name+'.json')).read_text(encoding='utf-8'))

def rows(name):
    assert load(name+'-validate')['success'] and load(name+'-execute')['success'], name
    return load(name+'-execute')['data']['items']

def near(actual, expected):
    assert actual is not None and abs(D(actual)-D(expected)) < D('1e-8'), (actual, expected)

con = sqlite3.connect((LAB/'data/Chinook_Sqlite.sqlite').as_uri()+'?mode=ro', uri=True)
original = con.execute('select InvoiceId,InvoiceDate,Total from Invoice order by InvoiceId').fetchall()
con.close()
fixture = sqlite3.connect((PROJECT/'data/calendar-teaching.sqlite').as_uri()+'?mode=ro', uri=True)
assert fixture.execute('select InvoiceId,InvoiceDate,Total from TeachingInvoice order by InvoiceId').fetchall() == original
calendar = fixture.execute('select * from ReportingCalendar order by CalendarDate').fetchall()
assert len(calendar) == 378 and len({r[0] for r in calendar}) == 378
assert [r[0] for r in calendar] == [(date(2011,12,26)+timedelta(days=i)).isoformat() for i in range(378)]
assert [(r[0],r[7]) for r in calendar[:5]] == [
    ('2011-12-26',18),('2011-12-27',19),('2011-12-28',20),('2011-12-29',21),('2011-12-30',22)]
fixture.close()
daily = defaultdict(Decimal)
monthly = defaultdict(Decimal)
first15 = defaultdict(Decimal)
working = defaultdict(Decimal)
loaded = defaultdict(Decimal)
counts = defaultdict(int)
for key, timestamp, value in original:
    day = date.fromisoformat(timestamp[:10]); amount = D(value)
    daily[day.isoformat()] += amount
    monthly[timestamp[:7]] += amount
    loaded[(day+timedelta(days=2)).strftime('%Y-%m')] += amount
    if day.day <= 15:
        first15[timestamp[:7]] += amount
        counts[timestamp[:7]] += 1
        if day.weekday() < 5:
            working[timestamp[:7]] += amount

a = rows('01-calendar-daily')
assert [r['calendarDate'] for r in a] == [f'2012-11-{i:02}' for i in range(1,18)]
for r in a:
    k = r['calendarDate']
    if k < '2012-11-16':
        assert r['dataComplete'] == 1
        near(r['reportedAmount'], daily.get(k, D(0)))
    else:
        assert r['dataComplete'] == 0 and r.get('reportedAmount') is None
assert a[1]['reportedAmount'] == 0 and a[1].get('rawAmount') is None

near(rows('02-business-month')[0]['amount'], monthly['2012-11'])
near(rows('03-loaded-month')[0]['amount'], loaded['2012-11'])
assert rows('02-business-month')[0]['bills'] == 7 and rows('03-loaded-month')[0]['bills'] == 6
r = rows('04-boundary-invoice')[0]
assert r['invoiceId'] == 325 and r['invoiceDate'].startswith('2012-11-29') and r['loadedAt'].startswith('2012-12-01')
near(r['invoiceAmount'], '5.94')
assert [r['invoiceMonth'] for r in rows('05-comparable-periods')] == ['2012-10','2012-11']
for r in rows('05-comparable-periods'):
    near(r['amount'], first15[r['invoiceMonth']]); assert r['bills'] == counts[r['invoiceMonth']]
r = rows('06-period-difference')[0]
near(r['currentAmount'], first15['2012-11']); near(r['priorAmount'], first15['2012-10'])
near(r['difference'], first15['2012-11']-first15['2012-10'])
near(r['changeRate'], (first15['2012-11']-first15['2012-10'])/first15['2012-10'])
assert abs((first15['2012-11']/monthly['2012-10']-1)*100 - D('-44.25152510558423')) < D('1e-8')

a = rows('07-period-coverage')
assert [(r['days'],r['confirmedDays']) for r in a] == [(31,31),(30,15)]
near(a[0]['knownAmount'], monthly['2012-10']); near(a[1]['knownAmount'], first15['2012-11'])
assert [r['yearMonth'] for r in rows('08-known-period-coverage')] == ['2012-10','2012-11']
for r in rows('08-known-period-coverage'):
    assert r['days'] == r['confirmedDays'] == 15
    near(r['amount'], first15[r['yearMonth']])
assert [r['yearMonth'] for r in rows('09-equal-workdays')] == ['2012-10','2012-11']
for r in rows('09-equal-workdays'):
    assert r['workdays'] == 11
    near(r['amount'], working[r['yearMonth']])
assert [r['calendarDate'] for r in rows('10-leap-day')] == ['2012-02-28','2012-02-29','2012-03-01']
a = rows('11-cross-year-week')
assert [(r['calendarDate'],r['weekStart'],r['isoWeekYear'],r['isoWeek']) for r in a] == [
    ('2012-12-30','2012-12-24',2012,52), ('2012-12-31','2012-12-31',2013,1),
    ('2013-01-01','2012-12-31',2013,1), ('2013-01-02','2012-12-31',2013,1)]
assert [r['calendarDate'] for r in rows('12-unconfirmed-with-records')] == ['2012-11-27','2012-11-28','2012-11-29']
for r in rows('12-unconfirmed-with-records'):
    assert r['dataComplete'] == 0 and r.get('reportedAmount') is None
    near(r['rawAmount'], daily[r['calendarDate']])
r = rows('13-year-join-check')[0]
assert r['days'] == 366 and r['bills'] == 83
near(r['amount'], sum(v for k,v in daily.items() if k.startswith('2012-')))
r = rows('15-original-invoice-regression')[0]
assert r['bills'] == 83; near(r['amount'], '477.53')
bad = load('14-invalid-payment-field-validate')
assert not bad['success'] and bad['error']['code'] == 'FIELD_NOT_FOUND'
assert not (e/'14-invalid-payment-field-execute.json').exists()

assert hashes() == load('hashes-before') == load('hashes-after')
manifest = json.loads((PROJECT/'fixture.json').read_text(encoding='utf-8'))
assert hashlib.sha256((PROJECT/'data/calendar-teaching.sqlite').read_bytes()).hexdigest() == manifest['databaseSha256']
cap = load('capabilities')
assert cap['engine'] == 'java' and cap['data']['securityMode'] == 'none-dev-test-only'
for m in ['TeachingInvoiceDatesQueryModel','TeachingCalendarReportQueryModel']:
    assert load('metadata-'+m)['success']
fields = load('metadata-TeachingInvoiceDatesQueryModel')['data']['data']['fields']
markdown = load('markdown-TeachingInvoiceDatesQueryModel')['data']['content']
assert 'loadedAt$id' not in fields and fields['loadedAt']['type'] == 'DATETIME'
expected_roles = {
    'invoiceDate': ('business_date', '按账单发生时间统计业务金额'),
    'loadedAt': ('system_time', '仅按模拟入库时间统计，不得代替账单发生时间或付款时间')
}
for name,(role,use) in expected_roles.items():
    assert fields[name]['timeRole'] == role and fields[name]['recommendedUse'] == use
    role_line = next(line for line in markdown.splitlines() if line.startswith('| '+name+' |'))
    assert role in role_line and use in role_line
report = {
    'launcher': '0.1.36', 'cli': '0.1.25', 'runtime': URL, 'namespace': NS,
    'dataSource': 'chinook-calendar-sqlite', 'bundle': 'chinook-calendar-teaching',
    'executedQueriesPassed': 14, 'expectedValidationRejections': 1,
    'originalChinookDataAndModelsUnchanged': True,
    'datePropertiesRolesAndUsesMatchJsonAndMarkdown': True,
    'ingestionField': 'loadedAt',
    'businessMonthAmount': str(monthly['2012-11']), 'loadedMonthAmount': str(loaded['2012-11']),
    'comparablePeriods': {'current': str(first15['2012-11']), 'prior': str(first15['2012-10']),
                          'difference': str(first15['2012-11']-first15['2012-10']), 'changePercentDisplayed': '-17.39%'},
    'calendar': {'annualDays':366, 'annualAmount':'477.53', 'annualBills':83, 'knownDaysInNovember':15},
    'scope': 'Current Codex session drove public CLI; independent Python Decimal verification; no clean-session prompt-only or Console/FAP acceptance',
    'evidenceDirectory':str(e)
}
save(e/'acceptance.json', report); save(ROOT/'acceptance.json', report)
print(json.dumps(report, ensure_ascii=True, indent=2))