"""Independently compare semantic query results with raw SQLite rows and Decimal."""
import hashlib
import json
import sqlite3
from collections import defaultdict
from datetime import datetime
from decimal import Decimal
from pathlib import Path

LAB = Path(__file__).resolve().parents[1]
EVIDENCE = LAB / 'evidence' / 'chapter-08'
ZERO = Decimal('0')
CENT = Decimal('0.01')
HASH = 'bdf635be69850bd3be09c9a2dbeef7ddfb80036bd3ef3381383cd03b61e4a61a'


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


def money(value):
    return Decimal(str(value)).quantize(CENT)


def main():
    manifest = load(EVIDENCE / 'query-manifest.json')
    cases = {item['name']: item for item in manifest['queries']}

    def response(name):
        item = load(cases[name]['response'])
        assert item['success'] is True, name
        return item['data']

    def rows(name, complete=True):
        item = response(name)
        if complete:
            assert not item.get('hasNext'), name
            assert not item.get('pagination', {}).get('hasMore'), name
        return item['items']

    source = LAB.parent / '2026-10-13/open-data/chinook-v1.4.5/Chinook_Sqlite.sqlite'
    database = LAB / 'data/Chinook_Sqlite.sqlite'
    for path in [source, database]:
        assert hashlib.sha256(path.read_bytes()).hexdigest() == HASH, path
    with sqlite3.connect(database.as_uri() + '?mode=ro&immutable=1', uri=True) as db:
        db.row_factory = sqlite3.Row
        raw = {table: [dict(row) for row in db.execute('SELECT * FROM ' + table)]
               for table in ['Invoice', 'InvoiceLine', 'Customer', 'Track', 'Genre']}
    invoices = {row['InvoiceId']: row for row in raw['Invoice']}
    customers = {row['CustomerId']: row for row in raw['Customer']}
    tracks = {row['TrackId']: row for row in raw['Track']}
    genres = {row['GenreId']: row for row in raw['Genre']}

    def within(invoice, bounds):
        return bounds is None or bounds[0] <= invoice['InvoiceDate'] < bounds[1]

    totals = {}
    for label, bounds in [('all', None), ('2012', ('2012-01-01', '2013-01-01')),
                          ('october', ('2012-10-01', '2012-11-01')),
                          ('november', ('2012-11-01', '2012-12-01'))]:
        selected = [row for row in raw['Invoice'] if within(row, bounds)]
        selected_ids = {row['InvoiceId'] for row in selected}
        lines = [row for row in raw['InvoiceLine'] if row['InvoiceId'] in selected_ids]
        invoice_amount = sum((Decimal(str(row['Total'])) for row in selected), ZERO)
        line_amount = sum((Decimal(str(row['UnitPrice'])) * row['Quantity'] for row in lines), ZERO)
        quantity = sum(row['Quantity'] for row in lines)
        assert invoice_amount == line_amount
        actual_invoice = rows(f'02-invoice-{label}')[0]
        actual_line = rows(f'02-line-{label}')[0]
        assert money(actual_invoice['totalAmount']) == money(actual_line['totalAmount']) == invoice_amount, label
        assert actual_invoice['invoiceCount'] == len(selected), label
        assert actual_line['lineCount'] == len(lines) and actual_line['quantity'] == quantity, label
        totals[label] = dict(invoiceCount=len(selected), lineCount=len(lines), quantity=quantity,
                             amount=str(invoice_amount))

    amounts = defaultdict(lambda: ZERO)
    category_amounts = defaultdict(lambda: ZERO)
    category_quantity = defaultdict(int)
    for invoice in raw['Invoice']:
        if within(invoice, ('2012-01-01', '2013-01-01')):
            amounts[invoice['CustomerId']] += Decimal(str(invoice['Total']))
    ranked = sorted(amounts, key=lambda key: (-amounts[key], key))[:5]
    expected_top = [dict(customerId=key,
                         customerName=customers[key]['FirstName'] + ' ' + customers[key]['LastName'],
                         amount=str(amounts[key])) for key in ranked]
    for name in ['01-top-customers', '06-repaired-top-customers']:
        actual = rows(name, complete=False)  # Intentionally Top 5, not all customers.
        assert len(actual) == 5
        for row, expected in zip(actual, expected_top):
            assert row['customerId'] == expected['customerId'], (name, row, expected)
            assert row['customer$fullName'] == expected['customerName']
            assert money(row['totalAmount']) == Decimal(expected['amount'])

    for line in raw['InvoiceLine']:
        if within(invoices[line['InvoiceId']], ('2012-01-01', '2013-01-01')):
            genre_id = tracks[line['TrackId']]['GenreId']
            category_amounts[genre_id] += Decimal(str(line['UnitPrice'])) * line['Quantity']
            category_quantity[genre_id] += line['Quantity']
    category_rows = rows('03-category-2012')
    assert {row['track_genre$genreId'] for row in category_rows} == set(category_amounts)
    for row in category_rows:
        key = row['track_genre$genreId']
        assert row['track_genre$genreName'] == genres[key]['Name']
        assert money(row['totalAmount']) == category_amounts[key]
        assert row['quantity'] == category_quantity[key]
    assert sum((money(row['totalAmount']) for row in category_rows), ZERO) == Decimal(totals['2012']['amount'])
    assert sum(row['quantity'] for row in category_rows) == totals['2012']['quantity']

    sample = rows('04-invoice-252-lines')
    expected_lines = sorted([row for row in raw['InvoiceLine'] if row['InvoiceId'] == 252], key=lambda row: row['InvoiceLineId'])
    assert len(sample) == len(expected_lines) == 2
    for actual, line in zip(sample, expected_lines):
        invoice = invoices[line['InvoiceId']]
        customer = customers[invoice['CustomerId']]
        track = tracks[line['TrackId']]
        assert actual['invoiceLineId'] == line['InvoiceLineId']
        assert actual['trackId'] == line['TrackId']
        assert money(actual['unitPrice']) == Decimal(str(line['UnitPrice']))
        assert money(actual['lineAmount']) == Decimal(str(line['UnitPrice'])) * line['Quantity']
        assert actual['salesQuantity'] == line['Quantity']
        assert actual['invoice$invoiceDate'] == invoice['InvoiceDate']
        assert actual['invoice$customerId'] == customer['CustomerId']
        assert actual['invoice_customer$fullName'] == customer['FirstName'] + ' ' + customer['LastName']
        assert actual['track$title'] == track['Name']
        assert actual['track_genre$genreName'] == genres[track['GenreId']]['Name']
    rejected = load(cases['05-invalid-field']['response'])
    assert rejected['success'] is False
    assert rejected['error']['code'] == 'FIELD_NOT_FOUND'
    assert rejected['error']['phase'] == 'query.validate'
    assert cases['05-invalid-field']['executed'] is False
    assert 'invoiceAmount' not in manifest['lineFields']
    validation = load(EVIDENCE / '22-final-models-validate.json')
    assert validation['success'] and validation['data']['validFiles'] == 4
    assert validation['data']['invalidFiles'] == 0

    for tag, model in [('invoice', 'ChinookInvoiceQueryModel'), ('line', 'ChinookInvoiceLineQueryModel')]:
        description = load(EVIDENCE / f'25-final-describe-{tag}.json')['data']['data']
        assert '不指定币种' in description['models'][model]['description']
        expected_fields = manifest['invoiceFields' if tag == 'invoice' else 'lineFields']
        assert sorted(description['fields']) == expected_fields
    model_hashes = {str(path.relative_to(LAB)): hashlib.sha256(path.read_bytes()).hexdigest()
                    for path in sorted((LAB / 'models').rglob('*')) if path.suffix in {'.tm', '.qm'}}
    assert len(model_hashes) == 4
    baseline = dict(topCustomers2012=expected_top, totals=totals,
                    categories2012=[dict(genreId=k, name=genres[k]['Name'], amount=str(category_amounts[k]),
                                         quantity=category_quantity[k]) for k in sorted(category_amounts)],
                    calculation='Read-only original rows, Python dictionaries, Decimal; no semantic result used as expected value.')
    (EVIDENCE / 'baseline.json').write_text(json.dumps(baseline, ensure_ascii=False, indent=2), encoding='utf-8')
    accepted = dict(checkedAt=datetime.now().astimezone().isoformat(), success=True,
                    runtimeUrl='http://127.0.0.1:18067', namespace='chinook', dataSource='chinook-sqlite',
                    securityMode='none-dev-test-only', bundle='chinook-teaching',
                    modelDirectory=str(LAB / 'models'), modelFiles=4, modelSha256=model_hashes,
                    passingQueryCases=12, intentionalRejectedQueries=1,
                    independentChecks=['Top 5 identities/names/amounts/order', '4 periods invoice and line totals/counts/quantities',
                                       f'{len(category_rows)} categories and grand total', 'Invoice 252 joins and row amounts',
                                       'FIELD_NOT_FOUND rejected before execution; described field repair succeeds', 'Unchanged source and copy SHA256'],
                    amountComparison='Two decimal places; raw floating point tails preserved in responses',
                    executionMode='Current Codex session authored models and payloads and executed public CLI; no separate clean Codex prompt run.',
                    cleanSessionPromptVerified=False, browserFapVerified=False, productionPermissionsVerified=False)
    (EVIDENCE / 'acceptance.json').write_text(json.dumps(accepted, ensure_ascii=False, indent=2), encoding='utf-8')
    print(json.dumps(accepted, ensure_ascii=False, indent=2))


if __name__ == '__main__':
    main()