{
  "filters": {
    "field": "payableDate",
    "operator": "between",
    "value": { "from": "2026-08-01T00:00:00.000Z", "to": "2026-08-31T23:59:59.999Z" }
  },
  "limit": 100
}

"""Export one month of Spendesk spend to monthly_spend_<month>.csv.

Usage: python3 monthly_spend.py [YYYY-MM]   (default: last month)
Needs SPENDESK_CLIENT_ID and SPENDESK_CLIENT_SECRET. With an organisation-level key it covers every
company, or only SPENDESK_COMPANY_ID if set.
Scopes: experimental:payable-search:read, supplier:read, user:read, cost-center:read
(and experimental:company:read with an organisation-level key).
"""
import base64, csv, datetime as dt, json, os, sys, urllib.error, urllib.parse, urllib.request

BASE = os.environ.get("SPENDESK_BASE_URL", "https://public-api.spendesk.com")


def token():
    creds = base64.b64encode(f"{os.environ['SPENDESK_CLIENT_ID']}:{os.environ['SPENDESK_CLIENT_SECRET']}".encode()).decode()
    req = urllib.request.Request(BASE + "/v1/auth/token", data=b"grant_type=client_credentials",
                                 headers={"Authorization": "Basic " + creds, "Content-Type": "application/x-www-form-urlencoded"})
    return json.load(urllib.request.urlopen(req))["access_token"]


TOKEN = token()


def api(method, path, query=None, body=None, company=None):
    url = BASE + path + ("?" + urllib.parse.urlencode(query, doseq=True) if query else "")
    headers = {"Authorization": "Bearer " + TOKEN, "Content-Type": "application/json"}
    if company:
        headers["X-Company-Id"] = company
    req = urllib.request.Request(url, method=method, headers=headers, data=json.dumps(body).encode() if body else None)
    return json.load(urllib.request.urlopen(req))


def companies(need_id=False):
    """The companies to cover, as (id, name, X-Company-Id header).

    Organisation-level key: every company of the organisation, or only SPENDESK_COMPANY_ID if set.
    Company-level key: the key's own company (no header needed)."""
    only = os.environ.get("SPENDESK_COMPANY_ID")
    try:
        found, page = [], 1
        while True:
            result = api("GET", "/v2/companies", {"page": page, "pageSize": 30})
            found += [(c["id"], c["name"], c["id"]) for c in result["data"]]
            if page * 30 >= result["meta"]["pagination"]["total"]:
                return [c for c in found if not only or c[0] == only]
            page += 1
    except urllib.error.HTTPError:
        if only:  # organisation-level key without experimental:company:read
            return [(only, only, only)]
        own = api("GET", "/v1/users", {"pageSize": 1})["data"][0]["companyId"] if need_id else None
        return [(own, "", None)]

# 1. The month to export: first day 00:00 to last day 23:59:59 (UTC).
today = dt.date.today()
month = sys.argv[1] if len(sys.argv) > 1 else (today.replace(day=1) - dt.timedelta(days=1)).strftime("%Y-%m")
first = dt.date.fromisoformat(month + "-01")
last = (first + dt.timedelta(days=32)).replace(day=1) - dt.timedelta(days=1)


def names(path, ids, label, company):
    """Names for the IDs the payables carry, 30 per call."""
    found, ids = {}, sorted(ids)
    for i in range(0, len(ids), 30):
        for item in api("GET", path, {"ids": ids[i:i + 30], "pageSize": 30}, company=company)["data"]:
            found[item["id"]] = label(item)
    return found


with open(f"monthly_spend_{month}.csv", "w", newline="") as f:
    out = csv.writer(f)
    out.writerow(["company", "date", "type", "description", "counterparty", "amount", "currency",
                  "amount_company_currency", "company_currency", "cost_centers", "bookkeeping_status", "receipt"])
    total = 0
    for company_id, company_name, header in companies():
        # 2. Every payable of the month for this company, page by page.
        payables, cursor = [], None
        while True:
            body = {"filters": {"field": "payableDate", "operator": "between",
                                "value": {"from": f"{first}T00:00:00.000Z", "to": f"{last}T23:59:59.999Z"}},
                    "limit": 100}
            if cursor:
                body["cursor"] = cursor
            page = api("POST", "/v1/payables/search", body=body, company=header)
            payables += page["payables"]
            cursor = page.get("nextCursor")
            if not cursor:
                break

        # 3. Names for suppliers, employees and cost centers.
        counterparties = [p.get("counterparty") or {} for p in payables]
        suppliers = names("/v1/suppliers", {c["supplierId"] for c in counterparties if c.get("type") == "supplier"},
                          lambda s: s["name"], header)
        employees = names("/v1/users", {c["memberId"] for c in counterparties if c.get("type") == "employee"},
                          lambda u: f"{u['firstName']} {u['lastName']}", header)
        cost_centers = {c["id"]: c["name"] for c in api("GET", "/v1/cost-centers", {"includeArchived": "true"}, company=header)["data"]}

        # 4. One row per payable. Amounts are in cents: divide by 100.
        for p, c in zip(payables, counterparties):
            centers = {cost_centers.get(a["fieldEntityValueId"], a["fieldEntityValueId"])
                       for line in p.get("itemLines", []) for a in line.get("analyticalFieldAssociations", [])
                       if a["fieldKind"] == "costCenter"}
            out.writerow([company_name or company_id or "", p["creationDate"][:10], p["subType"], p.get("description", ""),
                          suppliers.get(c.get("supplierId")) or employees.get(c.get("memberId")) or "",
                          p["amount"] / 100, p["currency"], p["functionalAmount"] / 100, p["functionalCurrency"],
                          "; ".join(sorted(centers)), p["state"], "yes" if p.get("documentaryEvidence") else "no"])
        total += len(payables)
        print(f"{company_name or 'Company'}: {len(payables)} payables")

print(f"{total} payables written to monthly_spend_{month}.csv")