"""Flatten a nested orders JSON export into two joinable tables (orders, order_lines) and prove totals still match.
Usage: python flatten_orders.py orders_export.json -> orders.csv, order_lines.csv, flatten_checks.txt"""
import json, csv, sys
from decimal import Decimal as D
def main(fn):
    data = json.load(open(fn))["orders"]; orders, lines, checks = [], [], []
    for o in data:
        orders.append({"order_id": o["id"], "created_at": o["created_at"][:19].replace("T", " "), "customer_id": o["customer"]["id"], "customer_name": o["customer"]["name"],
                       "customer_email": o["customer"]["email"] or "", "ship_method": o["shipping"]["method"], "ship_price": o["shipping"]["price"],
                       "ship_city": o["shipping"]["address"]["city"], "ship_state": o["shipping"]["address"]["state"], "tags": ";".join(o["tags"]), "line_count": len(o["line_items"]), "order_total": o["total"]})
        for n, l in enumerate(o["line_items"], 1):
            disc = sum(D(str(d["amount"])) for d in l["discounts"]); gross = D(l["unit_price"]["amount"]) * l["quantity"]
            lines.append({"order_id": o["id"], "line_no": n, "sku": l["sku"], "title": l["title"], "quantity": l["quantity"], "unit_price": l["unit_price"]["amount"],
                          "currency": l["unit_price"]["currency"], "discount_codes": ";".join(d["code"] for d in l["discounts"]), "discount": f"{disc:.2f}", "line_net": f"{gross - disc:.2f}"})
    for name, rows in [("orders.csv", orders), ("order_lines.csv", lines)]:
        with open(name, "w", newline="") as f: w = csv.DictWriter(f, fieldnames=list(rows[0])); w.writeheader(); w.writerows(rows)
    mism = [o["order_id"] for o in orders if D(o["order_total"]) != sum(D(l["line_net"]) for l in lines if l["order_id"] == o["order_id"]) + D(o["ship_price"])]
    checks += [f"{'PASS' if len(orders) == len(data) else 'FAIL'}  every order became one row ({len(orders)} of {len(data)})",
               f"{'PASS' if len(lines) == sum(len(o['line_items']) for o in data) else 'FAIL'}  every line item became one row ({len(lines)})",
               f"{'PASS' if not mism else 'FAIL'}  line totals + shipping equal the order total for every order (mismatches: {len(mism)})",
               f"{'PASS' if all(l['order_id'] in {o['order_id'] for o in orders} for l in lines) else 'FAIL'}  every line joins back to an order",
               f"INFO  orders with no customer email: {sum(1 for o in orders if not o['customer_email'])}",
               f"INFO  lines carrying a discount code: {sum(1 for l in lines if l['discount_codes'])}"]
    open("flatten_checks.txt", "w").write("\n".join(checks) + "\n"); print("\n".join(checks))
if __name__ == "__main__": main(sys.argv[1] if len(sys.argv) > 1 else "orders_export.json")