class ZuoraConnect::AppInstanceBase

def catalog_lookup(entity_id: nil, object: :product, object_id: nil, map_lower_objects: true)
  entity_reference = entity_id.blank? ? 'Default' : entity_id
  object_hierarchy = (JSON.parse(ActiveRecord::Base.connection.execute('SELECT catalog_mapping #> \'{%s}\' AS item FROM "public"."zuora_connect_app_instances" WHERE "id" = %s LIMIT 1' % [entity_reference, self.id]).first["item"] || "{}") [object_id] || {"productId" => "SAFTEY", "productRatePlanId" => "SAFTEY", "productRatePlanChargeId" => "SAFTEY"})
  case object
  when :product
    if object_id.blank?
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT catalog #> \'{%s}\' AS item FROM "public"."zuora_connect_app_instances" WHERE "id" = %s' % [entity_reference, self.id]).first["item"] || "{}")
    else
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT catalog #> \'{%s, %s}\' AS item FROM "public"."zuora_connect_app_instances" WHERE "id" = %s' % [entity_reference, object_id,  self.id]).first["item"] || "{}")
    end
    if !map_lower_objects
      stub_catalog = stub_catalog.map{|k,v| [k,v.except("productRatePlans").merge({"productRatePlans" =>  v["productRatePlans"].map {|k,v| v.except("productRatePlanCharges").merge({"productRatePlanCharges" =>  v["productRatePlanCharges"].map {|k,v| v}})}})]}.to_h
    end
  when :rateplan
    if object_id.blank?
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT json_object_agg(rateplan_id, rateplan) AS item FROM "public"."zuora_connect_app_instances", jsonb_each(("public"."zuora_connect_app_instances"."catalog" #> \'{%s}\' )) AS e(product_id, product), jsonb_each(product #> \'{productRatePlans}\') AS ee(rateplan_id, rateplan) WHERE "id" = %s' % [entity_reference, self.id]).first["item"] || "{}")
    else
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT catalog #> \'{%s, %s, productRatePlans, %s}\' AS item FROM "public"."zuora_connect_app_instances" WHERE "id" = %s' % [entity_reference, object_hierarchy['productId'], object_id,  self.id]).first["item"] || "{}")
    end
    if !map_lower_objects
      stub_catalog = stub_catalog.map {|k,v| [k,v.except("productRatePlanCharges").merge({"productRatePlanCharges" =>  v["productRatePlanCharges"].map {|k,v| v}})]}.to_h
    end
  when :charge
    if object_id.blank?
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT json_object_agg(charge_id, charge) as item FROM "public"."zuora_connect_app_instances", jsonb_each(("public"."zuora_connect_app_instances"."catalog" #> \'{%s}\' )) AS e(product_id, product), jsonb_each(product #> \'{productRatePlans}\') AS ee(rateplan_id, rateplan), jsonb_each(rateplan #> \'{productRatePlanCharges}\') AS eee(charge_id, charge) WHERE "id" = %s' % [entity_reference, self.id]).first["item"] || "{}")
    else
      stub_catalog = JSON.parse(ActiveRecord::Base.connection.execute('SELECT catalog #> \'{%s, %s, productRatePlans, %s, productRatePlanCharges, %s}\' AS item FROM "public"."zuora_connect_app_instances" WHERE "id" = %s' % [entity_reference, object_hierarchy['productId'], object_hierarchy['productRatePlanId'], object_id, self.id]).first["item"] || "{}")
    end
  end
  return stub_catalog
end