Log Reddit brand mentions with drafted replies

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

QUERY, SHEET_ID, RANGE = '"Northwind CRM"', "your-spreadsheet-id", "Mentions!A:E"
SCHEMA = {"type": "object", "required": ["sentiment", "reply"], "properties": {
    "sentiment": {"enum": ["positive", "neutral", "negative"]}, "reply": {"type": "string"}}}

def reddit(path, **params):
    headers = {"Authorization": "Bearer " + credentials.connection("reddit", "main"), "User-Agent": "clickruntime/1.0"}
    return [c["data"] for c in http.get(f"https://oauth.reddit.com{path}", headers=headers, params=params).json()["data"]["children"]]

def append_rows(rows):
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False)
    sheets.spreadsheets().values().append(spreadsheetId=SHEET_ID, range=RANGE, valueInputOption="RAW", body={"values": rows}).execute()

@on_trigger
def run(event):
    seen = set(state.get("seen") or [])
    posts = [p for p in reddit("/search", q=QUERY, sort="new", limit=25) if p["id"] not in seen]
    report_fetched(len(posts))
    if not posts:
        return {"mentions": 0}
    rows = []
    for p in posts:
        r = ai.generate(f"{p['title']}\n{p['selftext'][:1500]}", json_schema=SCHEMA, max_output_tokens=250,
                        system="Rate sentiment toward Northwind CRM and draft a short, helpful reply from the Northwind team.").json
        link = "https://reddit.com" + p["permalink"]
        rows.append([p["subreddit"], p["title"], r["sentiment"], r["reply"], link])
        if r["sentiment"] == "negative":
            notify.email_me(f"Negative Reddit mention: {p['title'][:80]}", f"{link}\n\nSuggested reply:\n{r['reply']}")
    append_rows(rows)
    state.set("seen", (list(seen) + [p["id"] for p in posts])[-2000:])
    return {"mentions": len(rows)}