export_sql(explain=True)

import sqlite3
 
from _setup import X_test, artifact, feature_names
 
from compileml.export import export_sql
from compileml.runtime import decide
 
TOP_K = 3
 
# explain=True relies on MATERIALIZED common table expressions, so the query
# needs SQLite 3.35+, PostgreSQL 12+ or DuckDB. The score-only query does not.
sql = export_sql(artifact, table="applications", dialect="sqlite", explain=True, top_k=TOP_K)
print(f"generated {len(sql.splitlines())} lines of SQL")
 
connection = sqlite3.connect(":memory:")
columns = ", ".join(["row_id INTEGER"] + [f'"{name}" REAL' for name in feature_names])
connection.execute(f"CREATE TABLE applications ({columns})")
connection.executemany(
    f"INSERT INTO applications VALUES ({', '.join('?' * (len(feature_names) + 1))})",
    [[i, *values.tolist()] for i, values in enumerate(X_test)],
)
 
cursor = connection.execute(sql)
names = [d[0] for d in cursor.description]
rows = sorted((dict(zip(names, r)) for r in cursor), key=lambda r: r["row_id"])
 
first = rows[0]
print()
print("row 0 from SQLite:")
print(f"  band {first['band']}   pd_ppm {first['pd_ppm']}")
for slot in range(1, TOP_K + 1):
    print(f"  reason_neg_{slot}  {first[f'reason_neg_{slot}_code']!s:<22} {first[f'reason_neg_{slot}_impact']}")
 
# Every reason, both directions, every row, against the Python runtime.
compared = mismatched = 0
for values, sql_row in zip(X_test, rows):
    python = decide(artifact, values.tolist(), explain=True, top_k=TOP_K)
    for direction, key in (("neg", "reasons_negative"), ("pos", "reasons_positive")):
        expected = [(r["code"], r["impact_int"]) for r in python[key]]
        emitted = [
            (sql_row[f"reason_{direction}_{s}_code"], sql_row[f"reason_{direction}_{s}_impact"])
            for s in range(1, TOP_K + 1)
            if sql_row[f"reason_{direction}_{s}_code"] is not None
        ]
        compared += 1
        mismatched += emitted != expected
    mismatched += (sql_row["band"], sql_row["pd_ppm"]) != (python["band"], python["pd_ppm"])
 
print()
print(f"rows compared        : {len(rows):,}")
print(f"reason lists compared: {compared:,}")
print(f"mismatches           : {mismatched}")