CREATE OR REPLACE FUNCTION public.fn_get_customer_monthly_recurring_charge(p_customer_id bigint)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
    SELECT COALESCE(SUM(amount), 0)
    FROM (
        SELECT p.monthly_fee AS amount
        FROM public.accounts a
        JOIN public.subscriptions s ON s.account_id = a.account_id
        JOIN public.plans p ON p.plan_id = s.plan_id
        WHERE a.customer_id = p_customer_id
          AND s.status = 'active'
          AND p.status = 'active'
        UNION ALL
        SELECT sa.price_at_activation
        FROM public.accounts a
        JOIN public.subscriptions s ON s.account_id = a.account_id
        JOIN public.subscription_addons sa ON sa.subscription_id = s.subscription_id
        JOIN public.addons ad ON ad.addon_id = sa.addon_id
        WHERE a.customer_id = p_customer_id
          AND s.status = 'active'
          AND sa.status = 'active'
          AND sa.deactivation_date IS NULL
          AND ad.is_recurring
          AND ad.status = 'active'
    ) recurring_items;
$$;