Check payment complaints against your payments sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, report_fetched, http, credentials, ai, state, notify

ZD, AGENT = "https://yourcompany.zendesk.com/api/v2", "you@yourcompany.com"

def zd(method, path, **kw):
    r = http.request(method, ZD + path, auth=(f"{AGENT}/token", credentials.secret("ZENDESK_API_TOKEN")), **kw)
    return r.raise_for_status() or (r.json() if r.content else None)


sheets = lambda: build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    seen = set(state.get("seen") or [])
    new = [t for t in zd("GET", "/search.json", params={"query": "type:ticket created>1hours"})["results"] if t["id"] not in seen]
    report_fetched(len(new))
    if not new:
        return {"summary": "No new tickets"}
    rows = {r[0]: r for r in sheets().values().get(spreadsheetId=SHEET_ID, range="Payments!A2:D").execute().get("values", []) if r}
    for t in new:
        res = ai.generate(f"{t['subject']}\n{t['description'][:1500]}", system="Is this about a payment problem? Extract the order number if any.",
                          json_schema={"type": "object", "properties": {"payment": {"type": "boolean"}, "order": {"type": "string"}}},
                          max_output_tokens=40).json
        row = rows.get(res.get("order", ""))
        if res["payment"] and row and row[3].lower() in ("failed", "duplicate"):
            zd("PUT", f"/tickets/{t['id']}", json={"ticket": {"priority": "urgent", "comment": {"body": f"Payments sheet: {', '.join(row)}", "public": False}}})
            notify.slack(f"💳 #{t['id']} order {row[0]} is {row[3]} ({row[2]})", channel="#billing")
        seen.add(t["id"])
    state.set("seen", sorted(seen)[-2000:])
    return {"checked": len(new)}