"""Check the constructed examples in the technical-explanation topic chapter.

Uses only Python's standard library and an in-memory SQLite database. These
checks establish example behavior; they do not measure reader understanding.
Run from the repository root:
    python3 apps/site/public/downloads/works/language-data-cases/case-checks.py
"""

import json
import platform
import sqlite3


def make_readers():
    readers = []
    for i in range(3):
        readers.append(lambda: i)
    return readers


def make_default_readers():
    readers = []
    for i in range(3):
        readers.append(lambda value=i: value)
    return readers


def make_named_readers():
    readers = []
    for i in range(3):
        def reader():
            return i

        readers.append(reader)
    return readers


def observe_during_loop():
    readers = []
    observed = []
    for i in range(3):
        readers.append(lambda: i)
        observed.append(readers[-1]())
    return observed, [reader() for reader in readers]


python_results = {
    "call_after_loop": [reader() for reader in make_readers()],
    "default_parameter": [reader() for reader in make_default_readers()],
    "named_function": [reader() for reader in make_named_readers()],
    "during_and_after_loop": observe_during_loop(),
}
item = [0]
read_item = lambda value=item: value[0]
item[0] = 9
python_results["mutable_default_object"] = read_item()

assert python_results["call_after_loop"] == [2, 2, 2]
assert python_results["default_parameter"] == [0, 1, 2]
assert python_results["named_function"] == [2, 2, 2]
assert python_results["during_and_after_loop"] == ([0, 1, 2], [2, 2, 2])
assert python_results["mutable_default_object"] == 9

with sqlite3.connect(":memory:") as database:
    database.executescript("""
        CREATE TABLE customers (id INTEGER PRIMARY KEY);
        CREATE TABLE payments (
            id INTEGER PRIMARY KEY,
            customer_id INTEGER NOT NULL,
            status TEXT NOT NULL
        );
        INSERT INTO customers VALUES (1), (2), (3);
        INSERT INTO payments VALUES (10, 1, 'paid'), (30, 3, 'cancelled');
    """)
    on_query = """
        SELECT c.id, p.id AS payment_id
        FROM customers AS c
        LEFT JOIN payments AS p
          ON p.customer_id = c.id AND p.status = 'paid'
        ORDER BY c.id, p.id
    """
    where_query = """
        SELECT c.id, p.id AS payment_id
        FROM customers AS c
        LEFT JOIN payments AS p ON p.customer_id = c.id
        WHERE p.status = 'paid'
        ORDER BY c.id, p.id
    """
    or_query = """
        SELECT c.id, p.id AS payment_id
        FROM customers AS c
        LEFT JOIN payments AS p ON p.customer_id = c.id
        WHERE p.status = 'paid' OR p.customer_id IS NULL
        ORDER BY c.id, p.id
    """
    sql_results = {
        "condition_in_on": database.execute(on_query).fetchall(),
        "condition_in_where": database.execute(where_query).fetchall(),
        "where_or_null": database.execute(or_query).fetchall(),
    }
    assert sql_results["condition_in_on"] == [(1, 10), (2, None), (3, None)]
    assert sql_results["condition_in_where"] == [(1, 10)]
    assert sql_results["where_or_null"] == [(1, 10), (2, None)]

    database.execute("INSERT INTO payments VALUES (11, 1, 'paid')")
    sql_results["additional_matching_payment"] = database.execute(on_query).fetchall()
    assert sql_results["additional_matching_payment"] == [
        (1, 10), (1, 11), (2, None), (3, None)
    ]

print(json.dumps({
    "environment": {
        "python": platform.python_version(),
        "sqlite": sqlite3.sqlite_version,
        "database": ":memory:",
    },
    "python": python_results,
    "sql": sql_results,
}, ensure_ascii=False, indent=2))