CREATE OR REPLACE FUNCTION fn_get_customer_monthly_recurring_charge(
    p_customer_id BIGINT
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    v_total NUMERIC;
BEGIN
    SELECT COALESCE(SUM(x.amount), 0)
    INTO v_total
    FROM (
        -- Active plan monthly fees
        SELECT p.monthly_fee AS amount
        FROM accounts a
        JOIN subscriptions s
            ON s.account_id = a.account_id
        JOIN 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

        -- Active recurring add-ons
        SELECT sa.price_at_activation AS amount
        FROM accounts a
        JOIN subscriptions s
            ON s.account_id = a.account_id
        JOIN subscription_addons sa
            ON sa.subscription_id = s.subscription_id
        JOIN 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 = true
          AND ad.status = 'active'
    ) x;

    RETURN v_total;
END;
$$;

SELECT fn_get_customer_monthly_recurring_charge(1);