"""NeoLogic / 同じ検索結果で、DB区間だけを比べる教材。
Python 3標準機能のみ。実行: python3 search-diagnosis-example-20261026.py
架空10万件をメモリー上のSQLiteへ作成。外部接続・実データ・本番DBは使いません。
時間は実行端末ごとに変わります。Web画面や実サービスの性能試験ではありません。
"""
import json
import platform
import sqlite3
import statistics
import time


def run():
    database = sqlite3.connect(":memory:")
    try:
        database.execute("CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, yen INTEGER)")
        database.executemany("INSERT INTO orders VALUES(?,?,?)",
                             ((i, i % 1000, (i % 27 + 1) * 1000) for i in range(1, 100001)))
        query = "SELECT id,customer_id,yen FROM orders WHERE customer_id=? ORDER BY id DESC LIMIT 50"
        expected = [(i, 173, (i % 27 + 1) * 1000)
                    for i in range(100000, 0, -1) if i % 1000 == 173][:50]

        def measure():
            database.execute(query, (173,)).fetchall()  # 準備1回は測定へ含めない
            plan = [row[3] for row in database.execute("EXPLAIN QUERY PLAN " + query, (173,))]
            durations = []
            for _ in range(7):
                start = time.perf_counter_ns()
                result = database.execute(query, (173,)).fetchall()
                elapsed = (time.perf_counter_ns() - start) / 1000000
                assert result == expected
                durations.append(elapsed)
            return {"plan": plan, "rows": len(expected), "ids": [r[0] for r in expected],
                    "milliseconds": durations, "median_ms": statistics.median(durations)}

        before = measure()
        database.execute("CREATE INDEX orders_customer_id_desc ON orders(customer_id,id DESC)")
        after = measure()
        assert before["ids"] == after["ids"] and before["rows"] == after["rows"] == 50
        assert any("SCAN" in p for p in before["plan"])
        assert any("SEARCH" in p for p in after["plan"])
        return {"environment": {"python": platform.python_version(), "sqlite": sqlite3.sqlite_version},
                "conditions": {"data_rows": 100000, "customer": 173, "limit": 50,
                               "warmup_each": 1, "measured_each": 7, "scope": "database query + result read only"},
                "before": before, "after": after}
    finally:
        database.close()


if __name__ == "__main__":
    print(json.dumps(run(), ensure_ascii=False, indent=2))