"""Check teaching SQL equivalence in SQLite; this does not run a semantic compiler."""

import json
import sqlite3
from pathlib import Path


def main():
    folder = Path(__file__).resolve().parent
    payload = json.loads((folder / 'chapter-04-query.json').read_text(encoding='utf-8'))
    with sqlite3.connect(':memory:') as db:
        db.executescript((folder / 'seed.sql').read_text(encoding='utf-8'))
        # 仅模拟第 4、18 章约定的简单字段映射，不模拟语义编译器。
        db.executescript('''
            CREATE TEMP VIEW SalesInvoiceQueryModel AS
            SELECT c.customer_id AS customerId,
                   c.customer_name AS customerName,
                   i.invoice_date AS invoiceDate,
                   i.total_amount_cents / 100.0 AS salesAmount
            FROM invoices AS i
            JOIN customers AS c ON c.customer_id = i.customer_id
            WHERE i.status = 'ISSUED';
        ''')
        db.execute('PRAGMA query_only = ON')
        baseline = db.execute((folder / 'chapter-01-top-customers.sql').read_text(encoding='utf-8')).fetchall()
        physical = db.execute((folder / 'chapter-04-equivalent-physical.sql').read_text(encoding='utf-8')).fetchall()
        semantic_example = db.execute((folder / 'chapter-18-semantic-sql-example.sql').read_text(encoding='utf-8')).fetchall()
        if not (baseline == physical == semantic_example):
            raise RuntimeError('教学 SQL 结果与第一章基线不一致')
        output = {
            'status': 'passed',
            'sqlite_version': sqlite3.sqlite_version,
            'scope': 'SQLite SQL equivalence and JSON parsing only; no semantic compiler, DSL execution or authorization validation',
            'dsl_json_parsed': isinstance(payload, dict),
            'june_top3': [
                {'customer_id': row[0], 'customer_name': row[1], 'sales_amount_yuan': format(row[2], '.2f')}
                for row in physical
            ],
            'comparisons': ['chapter_04_physical_equals_chapter_01', 'chapter_18_sqlite_view_example_equals_chapter_01'],
        }
    (folder / 'query-equivalence-results.json').write_text(
        json.dumps(output, ensure_ascii=False, indent=2) + '\n', encoding='utf-8'
    )
    print(json.dumps(output, ensure_ascii=False, indent=2))


if __name__ == '__main__':
    main()