"""Reproduce chapter 1 in an in-memory SQLite database, without external packages."""

import json
import sqlite3
from collections import defaultdict
from datetime import datetime
from decimal import Decimal
from pathlib import Path


def money(cents):
    return format(Decimal(cents) / 100, '.2f')


def main():
    folder = Path(__file__).resolve().parent
    query = (folder / 'chapter-01-top-customers.sql').read_text(encoding='utf-8')
    with sqlite3.connect(':memory:') as connection:
        connection.row_factory = sqlite3.Row
        connection.executescript((folder / 'seed.sql').read_text(encoding='utf-8'))
        connection.execute('PRAGMA query_only = ON')
        facts = connection.execute(
            'SELECT invoice_id, customer_id, invoice_date, total_amount_cents, status '
            'FROM invoices ORDER BY invoice_id'
        ).fetchall()
        names = dict(connection.execute(
            'SELECT customer_id, customer_name FROM customers'
        ).fetchall())

        # 独立按原始记录和日期计算基线，不复用正文 SQL 的 JOIN/GROUP BY。
        totals = defaultdict(int)
        accepted = []
        for row in facts:
            date = datetime.fromisoformat(row['invoice_date'])
            if row['status'] == 'ISSUED' and (date.year, date.month) == (2025, 6):
                totals[row['customer_id']] += row['total_amount_cents']
                accepted.append(row['invoice_id'])
        if dict(totals) != {1: 550000, 2: 420000, 3: 300000, 4: 90000}:
            raise RuntimeError('教学数据与已核对的六月金额不一致')
        if accepted != [1002, 1003, 1004, 1006, 1007, 1008]:
            raise RuntimeError('日期或状态边界与预期不一致')

        june = [dict(row) for row in connection.execute(query).fetchall()]
        expected = [
            (customer_id, names[customer_id], money(cents))
            for customer_id, cents in sorted(totals.items(), key=lambda pair: (-pair[1], pair[0]))[:3]
        ]
        actual = [
            (row['customer_id'], row['customer_name'], format(row['sales_amount'], '.2f'))
            for row in june
        ]
        if actual != expected:
            raise RuntimeError(f'六月查询与独立基线不同: {actual!r}')

        july_query = query.replace("'2025-07-01'", "'2025-08-01'").replace(
            "'2025-06-01'", "'2025-07-01'"
        ).replace('LIMIT 3', 'LIMIT 2')
        july = [dict(row) for row in connection.execute(july_query).fetchall()]
        july_actual = [
            (row['customer_id'], format(row['sales_amount'], '.2f'))
            for row in july
        ]
        if july_actual != [(2, '9000.00'), (3, '6000.00')]:
            raise RuntimeError('七月练习结果与预期不一致')

        output = {
            'status': 'passed',
            'sqlite_version': sqlite3.sqlite_version,
            'june_top3': [
                {'customer_id': row[0], 'customer_name': row[1], 'sales_amount_yuan': row[2]}
                for row in actual
            ],
            'june_all_customers_total_yuan': money(sum(totals.values())),
            'june_included_invoice_ids': accepted,
            'july_top2': [
                {'customer_id': row['customer_id'], 'customer_name': row['customer_name'],
                 'sales_amount_yuan': format(row['sales_amount'], '.2f')}
                for row in july
            ],
            'checks': [
                '六月前三名与逐笔基线一致',
                '六月三十日末尾记录计入', '七月一日记录未计入六月',
                '作废发票未计入', '七月练习结果一致',
            ],
        }
        result_path = folder / 'chapter-01-results.json'
        result_path.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()