subscriptions.py

from supabase import create_client, Client
import os

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_KEY = os.getenv("SUPABASE_SERVICE_ROLE_KEY")
supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)


async def fetch_subscription(email: str):
    user_res = supabase.rpc("get_user_info", {"email_input": email}).execute()
    if not user_res.data:
        return None

    user = user_res.data[0]

    # 2. Fetch Stripe customer
    cust_res = (
        supabase.table("stripe.customers")
        .select("*")
        .eq("email", email)
        .execute()
    )

    if not cust_res.data:
        return {
            "email": email,
            "signed_up": user["created_at"],
            "subscription": None
        }

    customer = cust_res.data[0]

    # 3. Fetch subscriptions
    sub_res = (
        supabase.table("stripe.subscriptions")
        .select("*")
        .eq("customer", customer["id"])
        .in_("status", ["active", "trialing", "past_due"])
        .execute()
    )

    if not sub_res.data:
        return {
            "email": email,
            "signed_up": user["created_at"],
            "subscription": None
        }

    subscriptions = []

    for s in sub_res.data:
        # Extract price_id from subscription item
        price_id = (
            s["items"]["data"][0]["price"]["id"]
            if s.get("items") and s["items"].get("data")
            else None
        )

        # 4. Fetch product info for this price
        product_info = None
        product_name = None
        nickname = None

        if price_id:
            price_res = (
                supabase.table("stripe.prices")
                .select("id, nickname, product:product_id(name, description)")
                .eq("id", price_id)
                .execute()
            )

            if price_res.data:
                product_info = price_res.data[0]
                nickname = product_info.get("nickname")
                if product_info.get("product"):
                    product_name = product_info["product"].get("name")

        subscriptions.append({
            "status": s["status"],
            "subscription_id": s["id"],
            "current_period_end": s["current_period_end"],
            "product_name": product_name,
            "nickname": nickname
        })

    return {
        "email": email,
        "signed_up": user["created_at"],
        "subscription": subscriptions
    }