#!/usr/bin/env python3
"""Reproduce the 2026-10-18 Gumi article using only an in-memory SQLite DB.

Run with Python 3.9+: python3 sql-join-cardinality.py > reproduced.json
All data are synthetic; no files or network services are changed.
"""

import json
import platform
import sqlite3
import sys


FIXTURE = """
PRAGMA foreign_keys = ON;
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    total_cents INTEGER NOT NULL CHECK(total_cents >= 0)
);
CREATE TABLE payments (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    amount_cents INTEGER NOT NULL CHECK(amount_cents >= 0),
    status TEXT NOT NULL CHECK(status IN ('settled', 'pending'))
);
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    label TEXT NOT NULL
);
INSERT INTO orders VALUES (1, 10000), (2, 10000), (3, 6000), (4, 4000);
INSERT INTO payments VALUES
    (11, 1, 6000, 'settled'), (12, 1, 4000, 'settled'),
    (21, 2, 10000, 'pending'), (31, 3, 6000, 'settled');
INSERT INTO order_items VALUES (101, 1, 'A'), (102, 1, 'B'), (103, 1, 'C');
"""

QUERIES = {
    "baseline": """
SELECT COUNT(*) AS order_count, SUM(total_cents) AS order_cents
FROM orders;
""",
    "joined_rows": """
SELECT o.id AS order_id, o.total_cents,
       p.id AS payment_id, p.amount_cents
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
ORDER BY o.id, p.id;
""",
    "joined_totals": """
SELECT COUNT(*) AS joined_rows,
       COUNT(p.id) AS payment_count,
       COUNT(DISTINCT o.id) AS order_count,
       SUM(o.total_cents) AS wrong_order_cents,
       SUM(DISTINCT o.total_cents) AS distinct_amount_cents
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled';
""",
    "per_order_counts": """
SELECT o.id AS order_id, COUNT(*) AS joined_rows,
       COUNT(p.id) AS payment_count
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
GROUP BY o.id
ORDER BY o.id;
""",
    "grouped_order_totals": """
SELECT o.id AS order_id, SUM(o.total_cents) AS wrong_order_cents
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
GROUP BY o.id
ORDER BY o.id;
""",
    "preaggregated_rows": """
WITH paid AS (
  SELECT order_id, COUNT(*) AS payment_count,
         SUM(amount_cents) AS paid_cents
  FROM payments
  WHERE status = 'settled'
  GROUP BY order_id
)
SELECT o.id AS order_id, o.total_cents,
       COALESCE(paid.payment_count, 0) AS payment_count,
       COALESCE(paid.paid_cents, 0) AS paid_cents
FROM orders AS o
LEFT JOIN paid ON paid.order_id = o.id
ORDER BY o.id;
""",
    "preaggregated_totals": """
WITH paid AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments
  WHERE status = 'settled'
  GROUP BY order_id
)
SELECT COUNT(*) AS order_count,
       COALESCE(SUM(o.total_cents), 0) AS order_cents,
       COALESCE(SUM(paid.paid_cents), 0) AS paid_cents
FROM orders AS o
LEFT JOIN paid ON paid.order_id = o.id;
""",
    "exists_orders": """
SELECT o.id AS order_id, o.total_cents
FROM orders AS o
WHERE EXISTS (
  SELECT 1 FROM payments AS p
  WHERE p.order_id = o.id AND p.status = 'settled'
)
ORDER BY o.id;
""",
    "where_filter": """
SELECT o.id AS order_id, p.id AS payment_id
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE p.status = 'settled'
ORDER BY o.id, p.id;
""",
    "where_or_null": """
SELECT o.id AS order_id, p.id AS payment_id
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE p.status = 'settled' OR p.id IS NULL
ORDER BY o.id, p.id;
""",
    "two_children_rows": """
SELECT o.id AS order_id, p.id AS payment_id, i.id AS item_id
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.id = 1
ORDER BY p.id, i.id;
""",
    "two_children_totals": """
SELECT COUNT(*) AS joined_rows,
       SUM(o.total_cents) AS wrong_order_cents,
       SUM(p.amount_cents) AS wrong_paid_cents
FROM orders AS o
LEFT JOIN payments AS p
  ON p.order_id = o.id AND p.status = 'settled'
LEFT JOIN order_items AS i ON i.order_id = o.id
WHERE o.id = 1;
""",
    "two_children_preaggregated": """
WITH paid AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'settled' GROUP BY order_id
), items AS (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items GROUP BY order_id
)
SELECT o.id AS order_id, o.total_cents,
       COALESCE(paid.paid_cents, 0) AS paid_cents,
       COALESCE(items.item_count, 0) AS item_count
FROM orders AS o
LEFT JOIN paid ON paid.order_id = o.id
LEFT JOIN items ON items.order_id = o.id
WHERE o.id = 1;
""",
}


def make_db():
    db = sqlite3.connect(":memory:")
    db.executescript(FIXTURE)
    return db


def run(db, name):
    cursor = db.execute(QUERIES[name])
    return {
        "columns": [column[0] for column in cursor.description],
        "rows": [list(row) for row in cursor.fetchall()],
    }


def check(actual, expected):
    # Do not use assert: checks must also run under python -O.
    if actual != expected:
        raise RuntimeError("unexpected result: {!r} != {!r}".format(actual, expected))


def main():
    db = make_db()
    results = {name: run(db, name) for name in QUERIES}
    expected = {
        "baseline": [[4, 30000]],
        "joined_rows": [[1, 10000, 11, 6000], [1, 10000, 12, 4000],
                        [2, 10000, None, None], [3, 6000, 31, 6000],
                        [4, 4000, None, None]],
        "joined_totals": [[5, 3, 4, 40000, 20000]],
        "per_order_counts": [[1, 2, 2], [2, 1, 0], [3, 1, 1], [4, 1, 0]],
        "grouped_order_totals": [[1, 20000], [2, 10000], [3, 6000], [4, 4000]],
        "preaggregated_rows": [[1, 10000, 2, 10000], [2, 10000, 0, 0],
                               [3, 6000, 1, 6000], [4, 4000, 0, 0]],
        "preaggregated_totals": [[4, 30000, 16000]],
        "exists_orders": [[1, 10000], [3, 6000]],
        "where_filter": [[1, 11], [1, 12], [3, 31]],
        "where_or_null": [[1, 11], [1, 12], [3, 31], [4, None]],
        "two_children_rows": [[1, p, i] for p in [11, 12] for i in [101, 102, 103]],
        "two_children_totals": [[6, 60000, 30000]],
        "two_children_preaggregated": [[1, 10000, 10000, 3]],
    }
    for name, rows in expected.items():
        check(results[name]["rows"], rows)
    check(db.execute("PRAGMA foreign_keys").fetchone()[0], 1)
    db.close()

    boundaries = {}
    changes = {
        "at_most_one_settled_payment": "DELETE FROM payments WHERE id = 12;",
        "no_payments": "DELETE FROM payments;",
        "no_orders": "DELETE FROM order_items; DELETE FROM payments; DELETE FROM orders;",
    }
    for name, sql in changes.items():
        db = make_db()
        db.executescript(sql)
        boundaries[name] = {
            query: run(db, query) for query in
            ["baseline", "joined_totals", "preaggregated_totals"]
        }
        db.close()
    check(boundaries["at_most_one_settled_payment"]["joined_totals"]["rows"],
          [[4, 2, 4, 30000, 20000]])
    check(boundaries["at_most_one_settled_payment"]["baseline"]["rows"], [[4, 30000]])
    check(boundaries["at_most_one_settled_payment"]["preaggregated_totals"]["rows"],
          [[4, 30000, 12000]])
    check(boundaries["no_payments"]["baseline"]["rows"], [[4, 30000]])
    check(boundaries["no_payments"]["joined_totals"]["rows"],
          [[4, 0, 4, 30000, 20000]])
    check(boundaries["no_payments"]["preaggregated_totals"]["rows"], [[4, 30000, 0]])
    check(boundaries["no_orders"]["baseline"]["rows"], [[0, None]])
    check(boundaries["no_orders"]["joined_totals"]["rows"], [[0, 0, 0, None, None]])
    check(boundaries["no_orders"]["preaggregated_totals"]["rows"], [[0, 0, 0]])

    report = {
        "experiment_date": "2026-10-18",
        "environment": {
            "python": platform.python_version(),
            "implementation": platform.python_implementation(),
            "sqlite": sqlite3.sqlite_version,
            "platform": platform.platform(),
        },
        "scope": "Synthetic in-memory examples; no production data, benchmark, or other DB engine tested.",
        "foreign_keys_enabled": True,
        "fixture_sql": FIXTURE.strip(),
        "queries": {name: sql.strip() for name, sql in QUERIES.items()},
        "results": results,
        "boundaries": boundaries,
        "checks": "passed",
    }
    json.dump(report, sys.stdout, ensure_ascii=False, indent=2)
    sys.stdout.write("\n")


if __name__ == "__main__":
    main()
