List at-risk clients from Supabase in a sheet every morning

from datetime import date, timedelta
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify, ai

SHEET_ID = "your-spreadsheet-id"
SUPABASE = "https://your-project.supabase.co/rest/v1"

def sb(method, path, **kw):
    key = credentials.secret("SUPABASE_KEY")
    r = http.request(method, f"{SUPABASE}/{path}", **kw, headers={"apikey": key, "Authorization": f"Bearer {key}", "Prefer": "return=representation,resolution=merge-duplicates"})
    return r.raise_for_status() or (r.json() if r.content else None)

@on_trigger
def run(event):
    cutoff = date.today() - timedelta(days=14)
    clients = sb("GET", f"clients?status=eq.active&last_visit=lt.{cutoff}&select=name,email,phone,last_visit&order=last_visit.asc")
    rows = [["Name", "Last visit", "Email", "Phone"]] + [[c["name"], c["last_visit"], c["email"], c.get("phone", "")] for c in clients]
    sheets = build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
    sheets.values().clear(spreadsheetId=SHEET_ID, range="At Risk!A:D").execute()
    sheets.values().update(spreadsheetId=SHEET_ID, range="At Risk!A1", valueInputOption="RAW", body={"values": rows}).execute()
    text = f"{len(clients)} clients haven't visited since {cutoff}.\n" + "\n".join(f"{r[0]} – last visit {r[1]}" for r in rows[1:6])
    try:
        text += "\n\n" + ai.generate(f"One sentence on what stands out:\n{text}", max_output_tokens=60).text
    except ai.AIError:
        pass
    notify.email_me(f"{len(clients)} at-risk clients today", text)
    return {"at_risk": len(clients)}