class ZuoraConnect::AppInstanceBase

def catalog_lookup(entity_id: nil, object: :product, object_id: nil, child_objects: false, cache: false)
  entity_reference = entity_id.blank? ? 'Default' : entity_id
  if object_id.present? && ![Array, String].include?(object_id.class) 
    raise "Object Id can only be a string or an array of strings"
  end
  if defined?(Redis.current) && object_id.present? && object_id.class == String
    stub_catalog = decrypt_data(Redis.current.get("Catalog:#{self.id}:#{object_id}:Children:#{child_objects}"))
    object_hierarchy = decrypt_data(Redis.current.get("Catalog:#{self.id}:#{object_id}:Hierarchy"))
  end
  if defined?(object_hierarchy)
    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"})
  end
  case object
  when :product
    if object_id.blank?
      string = 
        "SELECT "\
          "json_object_agg(product_id, product #{child_objects ? '' : '- \'productRatePlans\''}) AS item "\
        "FROM "\
          "\"public\".\"zuora_connect_app_instances\", "\
          "jsonb_each((\"public\".\"zuora_connect_app_instances\".\"catalog\" #> '{%s}' )) AS e(product_id, product) "\
        "WHERE "\
          "\"id\" = %s" % [entity_reference, self.id]          
    else
      if object_id.class == String
        string = 
          "SELECT "\
            "(catalog #> '{%s, %s}') #{child_objects ? '' : '- \'productRatePlans\''} AS item "\
          "FROM "\
            "\"public\".\"zuora_connect_app_instances\" "\
          "WHERE "\
            "\"id\" = %s" % [entity_reference, object_id, self.id]
      elsif object_id.class == Array
        string = 
          "SELECT "\
            "json_object_agg(product_id, product #{child_objects ? '' : '- \'productRatePlans\''}) AS item "\
          "FROM "\
            "\"public\".\"zuora_connect_app_instances\", "\
            "jsonb_each((\"public\".\"zuora_connect_app_instances\".\"catalog\" #> '{%s}' )) AS e(product_id, product) "\
          "WHERE "\
            "\"product_id\" IN (\'%s\') AND "\
            "\"id\" = %s" % [entity_reference, object_id.join("\',\'"), self.id]
      end
    end
  when :rateplan
    if object_id.blank?
      string =
        "SELECT "\
          "json_object_agg(rateplan_id, rateplan #{child_objects ? '' : '- \'productRatePlanCharges\''}) 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]
    else
      if object_id.class == String
        string = 
          "SELECT "\
            "(catalog #> '{%s, %s, productRatePlans, %s}') #{child_objects ? '' : '- \'productRatePlanCharges\''} AS item "\
          "FROM "\
            "\"public\".\"zuora_connect_app_instances\" "\
          "WHERE "\
            "\"id\" = %s" % [entity_reference, object_hierarchy['productId'], object_id,  self.id]
      elsif object_id.class == Array
        string = 
          "SELECT "\
            "json_object_agg(rateplan_id, rateplan #{child_objects ? '' : '- \'productRatePlanCharges\''}) 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 "\
            "\"rateplan_id\" IN (\'%s\') AND "\
            "\"id\" = %s" % [entity_reference, object_id.join("\',\'"), self.id]
      end
    end
  when :charge
    if object_id.blank?
      string =
        "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]
    else
      if object_id.class == String
        string = 
          "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]
      elsif object_id.class == Array
        string = 
          "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 "\
            "\"charge_id\" IN (\'%s\') AND "\
            "\"id\" = %s" % [entity_reference, object_id.join("\',\'"), self.id]
      end
    end
  else
    raise "Available objects include [:product, :rateplan, :charge]"
  end
  stub_catalog ||= JSON.parse(ActiveRecord::Base.connection.execute(string).first["item"] || "{}")
  if defined?(Redis.current) && object_id.present? && object_id.class == String 
    Redis.current.set("Catalog:#{self.id}:#{object_id}:Hierarchy", encrypt_data(object_hierarchy))
    Redis.current.set("Catalog:#{self.id}:#{object_id}:Children:#{child_objects}", encrypt_data(stub_catalog))  if cache 
  end
  return stub_catalog
end