Untitled

CREATE OR REPLACE FUNCTION public.update_user_subscription() RETURNS TRIGGER AS $$
DECLARE
    customer_id text;
    user_email text;
    latest_invoice_status text;
BEGIN
    BEGIN
        customer_id := NEW.customer;  -- Stripe customer ID
        SELECT email INTO user_email FROM stripe.customers WHERE id = customer_id;

        SELECT status INTO latest_invoice_status FROM stripe.invoices
        WHERE customer = customer_id
        ORDER BY created DESC
        LIMIT 1;

        -- Insert a log entry for debugging (this part also fails)
        INSERT INTO public.debug_log(message) VALUES ('Function triggered. Customer: ' || customer_id || ', Status: ' || latest_invoice_status);

        IF latest_invoice_status = 'paid' THEN
            UPDATE public.user_profiles SET subscribed = TRUE
            WHERE user_id = (SELECT id FROM auth.users WHERE email = user_email);
        ELSE
            UPDATE public.user_profiles SET subscribed = FALSE
            WHERE user_id = (SELECT id FROM auth.users WHERE email = user_email);
        END IF;

        RETURN NEW;
    EXCEPTION WHEN OTHERS THEN
        -- Insert exception info into debug_log for debugging (not doing anything)
        INSERT INTO public.debug_log(message) VALUES ('Error occurred: ' || SQLERRM);
        RETURN NULL;
    END;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_subscription_status ON stripe.invoices;
CREATE TRIGGER update_subscription_status
AFTER INSERT OR UPDATE ON stripe.invoices
FOR EACH ROW
EXECUTE FUNCTION public.update_user_subscription();