from supabase import create_client, Client

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):
    # Fetch user
    user_res = supabase.table("auth.users").select("*").eq("email", email).execute()
    if not user_res.data:
        return None

    user = user_res.data[0]

    # 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]

    # 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
        }

    subs = []
    for s in sub_res.data:
        price_id = (
            s["items"]["data"][0]["price"]["id"]
            if s.get("items") and s["items"].get("data")
            else None
        )

        subs.append({
            "status": s["status"],
            "subscription_id": s["id"],
            "current_period_end": s["current_period_end"],
            "price_id": price_id
        })

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