"""Synthetic SQL answer review: Python 3 standard library, no network or files."""
import json
import math
import sqlite3

SQL = """
WITH test_batches(team, passed, attempted, status) AS (
  VALUES ('A', 9, 10, 'complete'),
         ('B', 10, 100, 'complete'),
         ('C', 0, 0, 'complete'),
         ('D', 100, 100, 'cancelled')
)
SELECT team, SUM(passed) AS passed, SUM(attempted) AS attempted,
       100.0 * SUM(passed) / NULLIF(SUM(attempted), 0) AS pass_rate_pct
FROM test_batches
WHERE status = 'complete'
GROUP BY team
ORDER BY team;
"""

def main():
    with sqlite3.connect(':memory:') as conn:
        rows = conn.execute(SQL).fetchall()
    assert [row[0] for row in rows] == ['A', 'B', 'C']
    assert rows[0][1:] == (9, 10, 90.0)
    assert rows[1][1:] == (10, 100, 10.0)
    assert rows[2][1:] == (0, 0, None)
    passed = sum(row[1] for row in rows)
    attempted = sum(row[2] for row in rows)
    assert (passed, attempted) == (19, 110)
    pooled_pct = 100.0 * passed / attempted
    rates = [row[3] for row in rows if row[3] is not None]
    equal_team_pct = sum(rates) / len(rates)
    assert math.isclose(pooled_pct, 17.272727272727273)
    assert equal_team_pct == 50.0
    assert not math.isclose(equal_team_pct, pooled_pct)
    # Reconcile the all-attempt answer independently from the group result.
    pooled_sql = SQL[:SQL.index('SELECT team,')] + """
SELECT SUM(passed), SUM(attempted),
       100.0 * SUM(passed) / NULLIF(SUM(attempted), 0)
FROM test_batches WHERE status = 'complete';
"""
    with sqlite3.connect(':memory:') as conn:
        direct = conn.execute(pooled_sql).fetchone()
    assert direct[:2] == (19, 110) and math.isclose(direct[2], pooled_pct)
    print(json.dumps({
        'scope': 'synthetic local SQLite reference, not a live AI test',
        'rows': rows,
        'pooled_pass_rate_pct': round(pooled_pct, 2),
        'equal_team_average_pct': equal_team_pct,
        'checks': '9 assertions passed'
    }, indent=2))

if __name__ == '__main__':
    main()