class ZuoraConnect::AppInstanceBase

def catalog_lookup(entity_id: nil, object: :product, object_id: nil, child_objects: false, cache: false, source: 'DB')
  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 source == 'API'
    catalog_container = {}
    if (Redis.current.hget(CATALOG_LOOKUP_CACHE_TIME_KEY, self.id).to_i + CATALOG_LOOKUP_TTL.to_i) > Time.now.to_i
      begin
        catalog_container = JSON.parse(Redis.current.hget(CATALOG_LOOKUP_CACHE_RESULT_KEY, self.id))
      rescue JSON::ParserError => ex
        Rails.logger.warn('Failed to parse catalog cache', ex)
      end
    else
      zuora_login = self.login_lookup(type: 'Zuora').first
      login = zuora_login.client(entity_reference)
      response = {
        'nextPage' => login.rest_endpoint("catalog/products?pageSize=#{CATALOG_LOOKUP_PAGE_SIZE}")
      }
      while response['nextPage'].present?
        url = login.rest_endpoint(response['nextPage'].split('/v1/').last)
        output_json, response = login.rest_call(debug: false, url: url, timeout_retry: true)
        case object
        when :product
          output_json.fetch('products', []).each do |product|
            rate_plans = {}
            product['productRatePlans'].each do |rate_plan|
              charges = {}
              rate_plan['productRatePlanCharges'].each do |charge|
                charges[charge['id']] = charge.merge(
                  {
                    'productId' => product['id'],
                    'productName' => product['name'],
                    'productRatePlanId' => rate_plan['id'],
                    'productRatePlanName' => rate_plan['name'],
                  }
                )
              end
              rate_plan['productRatePlanCharges'] = charges
              rate_plans[rate_plan['id']] = rate_plan.merge(
                {
                  'productId' => product['id'],
                  'productName' => product['name']
                }
              )
            end
            product['productRatePlans'] = rate_plans
            catalog_container[product['id']] = product
          end
        else
          raise "Available objects include [:product]"
        end
      end
      Redis.current.hset(CATALOG_LOOKUP_CACHE_RESULT_KEY, self.id, catalog_container.to_json)
      Redis.current.hset(CATALOG_LOOKUP_CACHE_TIME_KEY, self.id, Time.now.to_i)
    end
    if object_id.nil?
      catalog_container.transform_values! { |v| v.except('productRatePlans') }
    elsif object_id.is_a?(String)
      catalog_container = catalog_container[object_id]
    end
    return catalog_container || {}
  end
  if defined?(Redis.current) && object_id.present? && object_id.class == String && object_id.present?
    stub_catalog = cache ? decrypt_data(data: Redis.current.get("Catalog:#{self.id}:#{object_id}:Children:#{child_objects}")) : nil
    object_hierarchy = decrypt_data(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.nil?
      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.blank? ? BLANK_OBJECT_ID_LOOKUP : 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.nil?
      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.blank? ? BLANK_OBJECT_ID_LOOKUP : 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.nil?
      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.blank? ? BLANK_OBJECT_ID_LOOKUP : 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 && object_id.present?
    if cache
      Redis.current.sadd("Catalog:#{self.id}:Keys", ["Catalog:#{self.id}:#{object_id}:Hierarchy", "Catalog:#{self.id}:#{object_id}:Children:#{child_objects}"])
      Redis.current.set("Catalog:#{self.id}:#{object_id}:Hierarchy", encrypt_data(data: object_hierarchy))
      Redis.current.set("Catalog:#{self.id}:#{object_id}:Children:#{child_objects}", encrypt_data(data: stub_catalog))
    else
      Redis.current.sadd("Catalog:#{self.id}:Keys", ["Catalog:#{self.id}:#{object_id}:Hierarchy"])
      Redis.current.set("Catalog:#{self.id}:#{object_id}:Hierarchy", encrypt_data(data: object_hierarchy))
    end
  end
  return stub_catalog
end