Email renewal reminders from your Supabase subscriptions

import base64
from email.message import EmailMessage
from datetime import date, timedelta
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from clickruntime import on_trigger, credentials, http, notify
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)

def send(to, subject, text):
    msg = EmailMessage()
    msg["To"], msg["Subject"] = to, subject
    msg.set_content(text)
    build("gmail", "v1", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).users().messages().send(userId="me", body={"raw": base64.urlsafe_b64encode(msg.as_bytes()).decode()}).execute()

@on_trigger
def run(event):
    today = date.today()
    subs = sb("GET", f"subscriptions?renews_at=lte.{today + timedelta(days=7)}&renews_at=gte.{today}&reminded_at=is.null")
    for s in subs:
        send(s["email"], f"Your {s['plan']} plan renews on {s['renews_at']}",
             f"Hi,\n\nA quick note: your {s['plan']} plan renews on {s['renews_at']} for {s['price']}. "
             "Nothing to do if you'd like to continue. Reply to this email with any questions.")
        sb("PATCH", f"subscriptions?id=eq.{s['id']}", json={"reminded_at": str(today)})
    risky = [s for s in subs if not s.get("last_login") or s["last_login"][:10] < str(today - timedelta(days=30))]
    if risky:
        notify.email_me(f"{len(risky)} renewing customers look at risk",
                        "\n".join(f"{s['email']} – {s['plan']}, renews {s['renews_at']}, last login {s.get('last_login') or 'never'}" for s in risky))
    return {"reminded": len(subs), "at_risk": len(risky)}