"""Run outer join examples in an in-memory SQLite database."""
import json
import platform
import sqlite3
import sys

SCHEMA = """
CREATE TABLE customers (id INTEGER PRIMARY KEY, active INTEGER NOT NULL);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL,
    status TEXT, amount INTEGER NOT NULL, note TEXT
);
CREATE TABLE refunds (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, amount INTEGER NOT NULL);
INSERT INTO customers VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 0);
INSERT INTO orders VALUES
    (101, 1, 'paid', 50, NULL),
    (102, 1, 'paid', 70, 'gift'),
    (103, 3, 'pending', 30, NULL),
    (104, 4, NULL, 40, NULL),
    (105, 5, 'paid', 80, NULL);
INSERT INTO refunds VALUES (201, 101, 4), (202, 101, 6), (203, 102, 7);
"""

ON_PAID = """
SELECT c.id, o.id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE c.active = 1
ORDER BY c.id, o.id
"""

WHERE_PAID = """
SELECT c.id, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.active = 1 AND o.status = 'paid'
ORDER BY c.id, o.id
"""

COUNTS = """
SELECT c.id, COUNT(*), COUNT(o.id), COUNT(o.note)
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
WHERE c.active = 1
GROUP BY c.id
ORDER BY c.id
"""

NO_PAID = """
SELECT c.id
FROM customers AS c
WHERE c.active = 1
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id AND o.status = 'paid'
  )
ORDER BY c.id
"""

REFUND_TOTALS = """
WITH refund_by_order AS (
  SELECT order_id, SUM(amount) AS refund_total
  FROM refunds
  GROUP BY order_id
)
SELECT c.id, COUNT(o.id),
       COALESCE(SUM(o.amount), 0),
       COALESCE(SUM(r.refund_total), 0)
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id AND o.status = 'paid'
LEFT JOIN refund_by_order AS r ON r.order_id = o.id
WHERE c.active = 1
GROUP BY c.id
ORDER BY c.id
"""

QUERIES = {
    "paid_filter_in_on": ON_PAID,
    "paid_filter_in_where": WHERE_PAID,
    "or_unmatched": WHERE_PAID.replace("AND o.status = 'paid'", "AND (o.status = 'paid' OR o.id IS NULL)"),
    "left_filter_in_on": ON_PAID.replace("WHERE c.active = 1", "  AND c.active = 1"),
    "no_paid_by_key": ON_PAID.replace("WHERE c.active = 1", "WHERE c.active = 1 AND o.id IS NULL"),
    "no_order_by_key": WHERE_PAID.replace("o.status = 'paid'", "o.id IS NULL"),
    "null_order_status": WHERE_PAID.replace("o.status = 'paid'", "o.status IS NULL"),
    "counts": COUNTS,
    "counts_by_status": """
SELECT c.id, COUNT(o.id),
       COUNT(o.id) FILTER (WHERE o.status = 'paid'),
       COUNT(o.id) FILTER (WHERE o.status = 'pending'),
       COUNT(o.id) FILTER (WHERE o.status IS NULL)
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.active = 1
GROUP BY c.id
ORDER BY c.id
""",
    "no_paid_by_existence": NO_PAID,
    "expanded_totals": """
SELECT COUNT(o.id), COUNT(DISTINCT o.id), SUM(o.amount), SUM(r.amount)
FROM orders AS o
LEFT JOIN refunds AS r ON r.order_id = o.id
WHERE o.customer_id = 1 AND o.status = 'paid'
""",
    "totals_after_refund_grouping": REFUND_TOTALS,
}

EXPECTED = {
    "paid_filter_in_on": [(1, 101), (1, 102), (2, None), (3, None), (4, None)],
    "paid_filter_in_where": [(1, 101), (1, 102)],
    "or_unmatched": [(1, 101), (1, 102), (2, None)],
    "left_filter_in_on": [(1, 101), (1, 102), (2, None), (3, None), (4, None), (5, None)],
    "no_paid_by_key": [(2, None), (3, None), (4, None)],
    "no_order_by_key": [(2, None)],
    "null_order_status": [(2, None), (4, 104)],
    "counts": [(1, 2, 2, 1), (2, 1, 0, 0), (3, 1, 0, 0), (4, 1, 0, 0)],
    "no_paid_by_existence": [(2,), (3,), (4,)],
    "counts_by_status": [(1, 2, 2, 0, 0), (2, 0, 0, 0, 0), (3, 1, 0, 1, 0), (4, 1, 0, 0, 1)],
    "expanded_totals": [(3, 2, 170, 17)],
    "totals_after_refund_grouping": [(1, 2, 120, 17), (2, 0, 0, 0), (3, 0, 0, 0), (4, 0, 0, 0)],
}


def run():
    with sqlite3.connect(":memory:") as connection:
        connection.executescript(SCHEMA)
        results = {name: connection.execute(sql).fetchall() for name, sql in QUERIES.items()}
        assert results == EXPECTED
        connection.execute("UPDATE orders SET amount = 50 WHERE id = 102")
        same_amount = connection.execute("""
SELECT SUM(o.amount), SUM(DISTINCT o.amount)
FROM orders AS o
WHERE o.customer_id = 1 AND o.status = 'paid'
""").fetchone()
        assert same_amount == (100, 50)
        connection.execute("DELETE FROM orders")
        empty = connection.execute(COUNTS).fetchall()
        assert empty == [(customer, 1, 0, 0) for customer in range(1, 5)]
    return {
        "environment": {"os": platform.system(), "os_version": platform.mac_ver()[0] or platform.release(),
                        "machine": platform.machine(), "python": sys.version.split()[0],
                        "sqlite": sqlite3.sqlite_version},
        "results": results,
        "counts_with_empty_orders_table": empty,
        "same_amount_order_sum_and_distinct_amount_sum": same_amount,
    }


if __name__ == "__main__":
    print(json.dumps(run(), indent=2))
