"""Teaching examples for the ActionBox data reconciliation and contracts articles.
Python 3.10+, standard library only. Runs locally with synthetic data.
Not an ODCS validator or a production reconciliation engine.
"""
import sqlite3
import unittest

COMPARE_SQL = """
WITH all_keys AS (
  SELECT order_id FROM source_orders
  UNION
  SELECT order_id FROM target_orders
), comparison AS (
  SELECT k.order_id,
         s.amount_cents AS source_amount_cents,
         t.amount_cents AS target_amount_cents,
         CASE
           WHEN s.order_id IS NULL THEN 'target_only'
           WHEN t.order_id IS NULL THEN 'source_only'
           WHEN s.amount_cents IS NOT t.amount_cents THEN 'value_mismatch'
           ELSE 'match'
         END AS result
  FROM all_keys AS k
  LEFT JOIN source_orders AS s ON s.order_id = k.order_id
  LEFT JOIN target_orders AS t ON t.order_id = k.order_id
)
SELECT * FROM comparison WHERE result <> 'match' ORDER BY order_id
"""


def validate_orders(rows):
    """Validate only the example's exact fields, types, and unique key."""
    seen = set()
    for row in rows:
        if not isinstance(row, dict) or set(row) != {"order_id", "amount_cents"}:
            raise ValueError("Expected exactly order_id and amount_cents")
        key = row["order_id"]
        if type(key) is not int or key in seen:
            raise ValueError("order_id must be a unique non-null integer")
        amount = row["amount_cents"]
        if amount is not None and type(amount) is not int:
            raise ValueError("amount_cents must be an integer or null")
        seen.add(key)


def reconcile(source, target, *, extraction_complete, allow_empty=False):
    """Reject incomplete inputs before comparison; caller supplies completeness."""
    if extraction_complete is not True:
        raise ValueError("Extraction completeness has not been confirmed")
    if not allow_empty and (not source or not target):
        raise ValueError("Empty input needs an explicit policy")
    validate_orders(source)
    validate_orders(target)
    db = sqlite3.connect(":memory:")
    try:
        for table, rows in (("source_orders", source), ("target_orders", target)):
            db.execute(f"CREATE TABLE {table}(order_id INTEGER, amount_cents INTEGER)")
            db.executemany(
                f"INSERT INTO {table} VALUES (?, ?)",
                [(r["order_id"], r["amount_cents"]) for r in rows],
            )
        return db.execute(COMPARE_SQL).fetchall()
    finally:
        db.close()


def order(key, amount):
    return {"order_id": key, "amount_cents": amount}


class ExampleTests(unittest.TestCase):
    def test_article_output(self):
        source = [order(101, 12000), order(102, 2500), order(103, 900), order(105, None)]
        target = [order(101, 12000), order(102, 2600), order(104, 900), order(105, 0)]
        self.assertEqual(reconcile(source, target, extraction_complete=True), [
            (102, 2500, 2600, "value_mismatch"),
            (103, 900, None, "source_only"),
            (104, None, 900, "target_only"),
            (105, None, 0, "value_mismatch"),
        ])

    def test_equal_values(self):
        self.assertEqual(reconcile([order(1, 5)], [order(1, 5)], extraction_complete=True), [])

    def test_equal_nulls(self):
        self.assertEqual(reconcile([order(1, None)], [order(1, None)], extraction_complete=True), [])

    def test_offsetting_errors(self):
        source, target = [order(1, 100), order(2, 200)], [order(1, 110), order(2, 190)]
        self.assertEqual(sum(r["amount_cents"] for r in source), sum(r["amount_cents"] for r in target))
        self.assertEqual(len(reconcile(source, target, extraction_complete=True)), 2)

    def test_duplicate_keys_on_either_side(self):
        valid, duplicate = [order(1, 5)], [order(1, 5), order(1, 6)]
        for source, target in ((valid, duplicate), (duplicate, valid)):
            with self.assertRaises(ValueError):
                reconcile(source, target, extraction_complete=True)

    def test_null_key(self):
        with self.assertRaises(ValueError):
            validate_orders([order(None, 5)])

    def test_invalid_integer_values(self):
        for row in (order(True, 5), order(1, True), order(1, 1.5), order("1", 5)):
            with self.subTest(row=row), self.assertRaises(ValueError):
                validate_orders([row])

    def test_missing_or_extra_fields(self):
        for row in ({"order_id": 1}, {"order_id": 1, "amount_cents": 5, "extra": 0}):
            with self.assertRaises(ValueError):
                validate_orders([row])

    def test_incomplete_extraction(self):
        with self.assertRaises(ValueError):
            reconcile([order(1, 5)], [order(1, 5)], extraction_complete=False)

    def test_empty_requires_policy(self):
        with self.assertRaises(ValueError):
            reconcile([], [], extraction_complete=True)

    def test_explicitly_empty(self):
        self.assertEqual(reconcile([], [], extraction_complete=True, allow_empty=True), [])

    def test_one_sided_empty(self):
        self.assertEqual(reconcile([], [order(1, 5)], extraction_complete=True, allow_empty=True),
                         [(1, None, 5, "target_only")])

    def test_units_can_change_without_schema_failure(self):
        original, changed = [order(1, 12000)], [order(1, 120)]
        validate_orders(original)
        validate_orders(changed)
        # Both pass the structural checks. A known business fixture reveals the change.
        self.assertEqual(original[0]["amount_cents"], 12000)
        self.assertNotEqual(changed[0]["amount_cents"], 12000)


if __name__ == "__main__":
    unittest.main(verbosity=2)