product_set.rb

class ProductSet
  attr_reader :brands, :min, :max, :colors, :sizes, :types, :discounts, :products

  def initialize(category:, currency:, page:, params:)
    @category = category
    @currency = currency
    @page = page.nil? ? 1 : page.to_i
    @params = params
    @discounts = {}

    process
    find_discounts
  end

  def find_discounts
    @discounts = Discount.for_products(@products, @currency) || {}
  end

  def empty?
    @products.empty?
  end

  private

  def process
    category_query = "category_id IN (#{@category.self_and_descendant_ids.join(', ')})
      AND" if @category.present?

    if @params[:discounted].present?
      discounted_ids = Discount.discounted_product_ids.join(', ')
      discounted_only = discounted_ids.present? ? "p.id IN (#{discounted_ids}) AND" : ''
    end

    brand = ''
    if (@params[:brand].try(:size) == 1 && @params[:all_products].present? && @params[:discounted].present?) || (@params[:brand].try(:size) == 1 && @params[:category].blank?)
      brand = "attribute_values->>'znacka' ILIKE #{ActiveRecord::Base::sanitize(@params[:brand].first)} AND "
    end

    sql = "
    WITH category_products AS (
    SELECT
      attribute_values,
      quantity,
      price_cents
    FROM products p
      JOIN product_variants pv ON pv.product_id = p.id
      JOIN product_prices pc ON pc.product_id = p.id
    WHERE #{category_query} #{discounted_only} #{brand} price_currency = '#{@currency.upcase}' AND image_file_name IS NOT NULL AND visible = true
    )

    SELECT
    MIN(price_cents)/100 as min, MAX(price_cents)/100 as max,
    json_build_object(" +
    %w(barva znacka velikost upresneni).map { |a|
        "'#{a}', json_object(
            (SELECT DISTINCT array_agg(DISTINCT UPPER(attribute_values ->> '#{a}'))
             FROM category_products
             WHERE LOWER(attribute_values ->> '#{a}') IS NOT NULL),
            (SELECT DISTINCT array(SELECT sum(quantity) :: TEXT
                                   FROM category_products
                                   WHERE LOWER(attribute_values ->> '#{a}') IS NOT NULL
                                   GROUP BY LOWER(attribute_values ->> '#{a}')
                                   ORDER BY LOWER(attribute_values ->> '#{a}')))
        )" }.join(', ') +
        ') as data
    FROM category_products
    LIMIT 1;
    '

    result = ActiveRecord::Base.connection.execute(sql).first
    data = JSON.parse(result['data'])

    @sizes = data['velikost'].present? ? data['velikost']
                 .map { |attr, val| {raw: attr, value: attr, quantity: val} }
                 .reject { |v| v[:value].blank? || !Product::SIZES_STR.include?(v[:value]) }
                 .uniq { |v| v[:value] }
                 .sort { |a, b| Product.sort_sizes(a[:value], b[:value]) }
                 .map { |s| s[:value] = Product.pretify_size(s[:value]); s } : []

    @colors = data['barva'].present? ? data['barva']
                  .map { |attr, val| {value: attr, quantity: val} }
                  .reject { |v| v[:value].blank? }
                  .sort { |a, b| a[:value] <=> b[:value] }
                  .uniq { |v| v[:value].strip } : []

    @types = data['upresneni'].present? ? data['upresneni']
                 .map { |attr, val| {value: attr, quantity: val} }
                 .reject { |v| v[:value].blank? }
                 .uniq { |v| v[:value].strip } : []

    @brands = data['znacka'] ? data['znacka'].map { |attr, val| {id: attr, name: attr, quantity: val} }
                                   .reject { |v| v[:name].blank? }
                                   .uniq { |v| v[:name].strip } : []

    @min = result['min']
    @max = result['max']

    base_query = Product
                     .listable
                     .joins(:prices, :paths, :variants)
                     .left_outer_joins(:category, :brand)
                     .preload(:prices, :paths)
                     .where(
                         product_prices: {price_currency: @currency.upcase},
                         paths: {canonical: true}
                     )
    @products = ProductSearch.new(base_query.preload(:prices)).search(
        gender: @params[:product_conditions].try(:[], :gender) || nil,
        category: @category,
        attributes: @params[:attributes],
        brand: @params[:brand],
        discounted_only: @params[:discounted],
        min: @params[:min],
        max: @params[:max],
        currency: @currency,
        sort_by: @params[:order] || 'created_at',
        order: @params[:sort] || 'desc'
    ).paginate(page: @page)
  end


  def _process

    criteria = {}
    if @params[:brand].present?
      criteria['znacka'] = @params[:brand]
    end
    @params[:attributes].each do |attr, values|
      criteria[attr] = values
    end unless @params[:attributes].blank?

    post_filter = {
        bool: {
            filter: [
                {
                    nested: {
                        path: 'variants',
                        query: {
                            bool: {
                                filter: criteria.map do |c, val|
                                  {terms: {"variants.attribute_values.#{c}": [val].flatten}}
                                end,
                                must: {
                                    range: {
                                        'variants.quantity': {
                                            gt: 0
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            ],
            must: [
                {range: {
                    quantity: {
                        gt: 0
                    }
                }}
            ]
        }
    }

    query = {
        from: (@page -1) * 30,
        size: 30,
        query: {
            bool: {
                filter: [
                    {exists: {field: 'category.id'}}
                ],
                must: {
                    range: {
                        "price_#{@currency.downcase}": {
                            gt: 0
                        }
                    }
                }
            }
        },
        post_filter: post_filter,
        aggregations: {
            variants: {
                nested: {
                    path: 'variants'
                },
                aggregations: {
                    color: {
                        terms: {field: 'variants.attribute_values.barva', size: 100},
                        aggregations: {
                            qty: {
                                sum: {field: 'variants.quantity'}
                            }
                        }
                    },
                    size: {
                        terms: {field: 'variants.attribute_values.velikost', size: 100},
                        aggregations: {
                            qty: {
                                sum: {field: 'variants.quantity'}
                            }
                        }
                    },
                    type: {
                        terms: {field: 'variants.attribute_values.upresneni', size: 100},
                        aggregations: {
                            qty: {
                                sum: {field: 'variants.quantity'}
                            }
                        }
                    }
                }
            },
            brands: {
                terms: {field: 'brand.name.keyword', size: 100},
                aggregations: {
                    qty: {
                        sum: {field: 'quantity'}
                    }
                }
            },
            max: {
                max: {field: "price_#{@currency.downcase}"}
            },
            min: {
                min: {field: "price_#{@currency.downcase}"}
            }
        }
    }

    if @category.present?
      query[:query][:bool][:filter].push ({terms: {
          'category.id': @category.self_and_descendant_ids
      }})
    end
    if @category.blank? and @params[:brand].try(:size) == 1 and @params[:discounted].blank?
      query[:query][:bool][:filter].push ({term: {
          'brand.id': Brand.where('name ilike :brand', brand: @params[:brand].first).first.id
      }})
    end
    if @params[:discounted]
      query[:query][:bool][:filter].push ({terms: {
          '_id': Discount.discounted_product_ids
      }})
    end

    if @params[:min].present?
      post_filter[:bool][:must].push({range: {"price_#{@currency.downcase}": {gte: @params[:min]}}})
    end
    if @params[:max].present?
      post_filter[:bool][:must].push({range: {"price_#{@currency.downcase}": {lte: @params[:max]}}})
    end

    if @params[:order].present?
      query[:sort] = {
          "#{@params[:order].gsub('cents', @currency.downcase)}": {
              order: @params[:sort] # ano, mame to naopak ¯\_(ツ)_/¯
          }
      }
    end

    if @params[:product_conditions] and @params[:product_conditions][:gender]
      query[:query][:bool][:filter].push({
                                             nested: {
                                                 path: 'variants',
                                                 query: {
                                                     bool: {
                                                         filter: [
                                                             {terms: {'variants.attribute_values.pohlavi': [@params[:product_conditions][:gender], 'Unisex']}}
                                                         ]
                                                     }
                                                 }
                                             }
                                         })
    end

    result = Product.search(query)

    @brands = result.aggregations['brands']['buckets'].map { |b| {id: b['key'], name: b['key'], quantity: b['qty']['value']} }

    @sizes = result.aggregations['variants']['size']['buckets']
                 .map { |b| {raw: b['key'], value: b['key'], quantity: b['qty']['value']} }
                 .reject { |v| v[:value].blank? || !Product::SIZES_STR.include?(v[:value]) }
                 .uniq { |v| v[:value] }
                 .sort { |a, b| Product.sort_sizes(a[:value], b[:value]) }
                 .map { |s| s[:value] = Product.pretify_size(s[:value]); s }

    @colors = result.aggregations['variants']['color']['buckets']
                  .map { |b| {value: b['key'], quantity: b['qty']['value']} }
                  .reject { |v| v[:value].blank? }
                  .sort { |a, b| a[:value] <=> b[:value] }
                  .uniq { |v| v[:value] }

    @types = result.aggregations['variants']['type']['buckets']
                 .map { |b| {value: b['key'], quantity: b['qty']['value']} }
                 .reject { |v| v[:value].blank? }
                 .uniq { |v| v[:value] }

    @max = result.aggregations['max']['value']
    @min = result.aggregations['min']['value']

    @products = result
                    .records
                    .joins(:paths, :prices)
                    .includes(:paths, :prices)

    if @params[:order].present?
      @products = @products
                      .reorder("#{@params[:order]} #{@params[:sort]}")
    end
  end
end