#!/usr/bin/env python3
import os
import base64
import requests
import csv

# --- Configuration (set these environment variables) ---
APP_ID      = os.getenv("sales-reporting")       # Your Commerce7 App ID
APP_SECRET  = os.getenv("VXxLgWA5JsEOxzLQGD31WCZGpIWDUKffgiCDNGTrYTioDtKwhTO9zgCVUZenvpiM")   # Your Commerce7 App Secret Key
TENANT_ID   = os.getenv("st-amant-winery")    # Your Commerce7 tenant (subdomain)
BASE_URL    = "https://api.commerce7.com/v1/order"

# Build auth headers
credentials = f"{APP_ID}:{APP_SECRET}"
auth_header = {
    "Authorization": "Basic " + base64.b64encode(credentials.encode()).decode(),
    "tenant": TENANT_ID,
    "Content-Type": "application/json"
}

def fetch_all_orders(filters=None):
    """Fetch all orders matching `filters` using cursor-based pagination."""
    orders = []
    cursor = "start"
    params = filters.copy() if filters else {}
    while cursor:
        params["cursor"] = cursor
        resp = requests.get(BASE_URL, headers=auth_header, params=params)
        resp.raise_for_status()
        data = resp.json()
        orders.extend(data.get("orders", []))
        cursor = data.get("cursor")
    return orders

if __name__ == "__main__":
    # Example: all Web orders paid since Jan 1, 2025
    filters = {
        "orderPaidDate": "gte:2025-01-01",
        "channel": "Web"
    }

    print("Fetching orders…")
    all_orders = fetch_all_orders(filters)
    print(f"Retrieved {len(all_orders)} orders.")

    # Define CSV columns (flatten nested fields and items)
    fieldnames = [
        "orderPaidDate", "orderNumber", "complianceStatus",
        "subTotal", "shipTotal", "taxTotal", "total",
        "shipTo_stateCode",  # flattened field
        "sku", "quantity", "departmentCode"  # item-level fields
    ]

    output_file = "orders_export.csv"
    with open(output_file, "w", newline="") as f:
        writer = csv.DictWriter(f, fieldnames=fieldnames)
        writer.writeheader()

        for order in all_orders:
            # Extract nested ship-to state code
            state = order.get("shipTo", {}).get("stateCode")

            # Loop through each item in the order
            for item in order.get("items", []):
                writer.writerow({
                    "orderPaidDate":      order.get("orderPaidDate"),
                    "orderNumber":        order.get("orderNumber"),
                    "complianceStatus":   order.get("complianceStatus"),
                    "subTotal":           order.get("subTotal"),
                    "shipTotal":          order.get("shipTotal"),
                    "taxTotal":           order.get("taxTotal"),
                    "total":              order.get("total"),
                    "shipTo_stateCode":   state,
                    "sku":                item.get("sku"),
                    "quantity":           item.get("quantity"),
                    "departmentCode":     item.get("departmentCode"),
                })

    print(f"Saved data to {output_file}")