gistfile1.sql

SELECT
            p.sku AS sku,
            p.price_amount::int AS price_amount,
            p.price_iso_code AS price_iso_code,
            p.discount as discount,
            true AS is_from_db,
            p.is_new AS is_new,
            CASE WHEN old_price_amount IS NULL THEN False ELSE True END AS is_sale,
            p.new_from AS new_from,
            p.gender AS gender,
            trunc(p.price_amount::int / 100) * 100 AS discrete_price,
            p.heel_height::float AS heel_height,
            p.collection_order || '___' || p.collection || '___' || p.collection_id as collection,
            (SELECT b.url || '___' || b.name || '___' || b.id FROM brand b WHERE b.id = p.brand_id) as brand,
            GREATEST(array_to_string(array(SELECT DISTINCT unnest(path) FROM category c WHERE c.id IN (SELECT pc.category_id FROM product_category pc WHERE pc.product_sku = p.sku)), '__arrsep__', ''), '0') AS category,
            array_to_string(array(SELECT DISTINCT ps.order || '___' || ps.size || '___' || ps.qty || '___' || ps.size_id FROM product_size ps WHERE ps.product_sku = p.sku AND ps.qty > 0), '__arrsep__', '') AS size,
            array_to_string(array(SELECT DISTINCT CAST(ps.size_id AS varchar) AS size_id FROM product_size ps WHERE ps.product_sku = p.sku AND ps.qty > 0 ORDER BY size_id), '_', '') AS size_sorted,
            array_to_string(array(SELECT DISTINCT psb.order || '___' || psb.brand_size || '___' || psb.brand_size_id FROM product_size psb WHERE psb.product_sku = p.sku AND psb.qty > 0), '__arrsep__', '') AS brand_size,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(colors) AS k, svals(colors) AS v FROM product pl WHERE pl.sku = p.sku) sub_color) AS color,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(base_materials) AS k, svals(base_materials) AS v FROM product pl WHERE pl.sku = p.sku) sub_base_material) AS base_material,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(filler_materials) AS k, svals(filler_materials) AS v FROM product pl WHERE pl.sku = p.sku) sub_filler_material) AS filler_material,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(upper_materials) AS k, svals(upper_materials) AS v FROM product pl WHERE pl.sku = p.sku) sub_upper_material) AS upper_material,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(inside_materials) AS k, svals(inside_materials) AS v FROM product pl WHERE pl.sku = p.sku) sub_inside_material) AS inside_material,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT DISTINCT (each(ages)).key AS k, (each(ages)).value AS v FROM product_size ps WHERE ps.product_sku = p.sku AND qty > 0) sub_age) AS age,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(seasons) AS k, svals(seasons) AS v FROM product pl WHERE pl.sku = p.sku) sub_season) AS season,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(styles) AS k, svals(styles) AS v FROM product pl WHERE pl.sku = p.sku) sub_style) AS style,
            (SELECT string_agg(v || '___' || k, '__arrsep__') FROM (SELECT skeys(sport_sections) AS k, svals(sport_sections) AS v FROM product pl WHERE pl.sku = p.sku) sub_sport_section) AS sport_section,
            CASE
                WHEN stock_type IS NULL OR stock_type = 'regular' OR stock_type = 'stock1'
                    THEN
                        CASE
                            /* 000 */ WHEN NOT COALESCE(is_outlet, False) AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NULL THEN 'regular'
                            /* 001 */ WHEN NOT COALESCE(is_outlet, False) AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NOT NULL THEN 'regular__arrsep__outlet'
                            /* 010 */ WHEN NOT COALESCE(is_outlet, False) AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NULL THEN 'regular'
                            /* 011 */ WHEN NOT COALESCE(is_outlet, False) AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NOT NULL THEN 'regular'
                            /* 100 */ WHEN COALESCE(is_outlet, False)     AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NULL THEN 'nowhere'
                            /* 101 */ WHEN COALESCE(is_outlet, False)     AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NOT NULL THEN 'outlet'
                            /* 110 */ WHEN COALESCE(is_outlet, False)     AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NULL THEN 'nowhere'
                            /* 111 */ WHEN COALESCE(is_outlet, False)     AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NOT NULL THEN 'nowhere'
                        END
                WHEN stock_type = 'stock2'
                    THEN
                        CASE
                            /* 000 */ WHEN NOT COALESCE(is_outlet, False) AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NULL THEN 'nowhere'
                            /* 001 */ WHEN NOT COALESCE(is_outlet, False) AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NOT NULL THEN 'nowhere'
                            /* 010 */ WHEN NOT COALESCE(is_outlet, False) AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NULL THEN 'nowhere'
                            /* 011 */ WHEN NOT COALESCE(is_outlet, False) AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NOT NULL THEN 'nowhere'
                            /* 100 */ WHEN COALESCE(is_outlet, False)     AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NULL THEN 'nowhere'
                            /* 101 */ WHEN COALESCE(is_outlet, False)     AND NOT COALESCE(is_outlet_forbidden, False) AND old_price_amount IS NOT NULL THEN 'outlet'
                            /* 110 */ WHEN COALESCE(is_outlet, False)     AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NULL THEN 'nowhere'
                            /* 111 */ WHEN COALESCE(is_outlet, False)     AND COALESCE(is_outlet_forbidden, False)     AND old_price_amount IS NOT NULL THEN 'nowhere'
                        END
                ELSE
                    NULL
            END AS display_location,
            NULLIF(array_to_string(p.labels, '__arrsep__'), '') AS label,

            CASE
                WHEN (SELECT SUM(ps.qty) FROM product_size AS ps WHERE ps.product_sku = p.sku) > 0
                    THEN
            COALESCE(p.name, '') || ' ' ||
            COALESCE((SELECT name || ' ' || array_to_string(COALESCE(synonyms, '{}'), ' ', '') FROM brand WHERE p.brand_id = id), '') || ' ' ||
            COALESCE((SELECT bg.name || ' ' || array_to_string(COALESCE(bg.synonyms, '{}'), ' ', '')
                      FROM brand_group bg JOIN brand b ON bg.id = b.brand_group_id WHERE p.brand_id = b.id), '') || ' ' ||
            array_to_string(COALESCE(avals(p.colors), '{}'), ' ', '') || ' ' ||
            array_to_string(COALESCE(avals(p.seasons), '{}'), ' ', '') || ' ' ||
            array_to_string(COALESCE(avals(p.styles), '{}'), ' ', '') || ' ' ||
            CASE WHEN p.styles IS NULL THEN '' ELSE array_to_string(COALESCE(avals(p.styles), '{}'), ' ', '') || ' стиль ' END ||
            array_to_string(COALESCE(p.synonyms, '{}'), ' ', '') || ' ' ||
            COALESCE(p.attributes->'upper_material', '') || ' ' ||
            COALESCE(p.attributes->'material_filling', '') || ' ' ||
            COALESCE(p.attributes->'material_filler', '') || ' ' ||
            CASE WHEN CAST(p.attributes->'waist' AS VARCHAR) IS NULL THEN '' ELSE p.attributes->'waist' || ' талия ' END ||
            CASE WHEN CAST(p.attributes->'neckline' AS VARCHAR) IS NULL THEN '' ELSE p.attributes->'neckline' || ' вырез ' END ||
            CASE WHEN CAST(p.attributes->'sleeve' AS VARCHAR) IS NULL THEN '' ELSE p.attributes->'sleeve' || ' рукав ' END ||
            CASE WHEN CAST(p.attributes->'fit' AS VARCHAR) IS NULL THEN '' ELSE p.attributes->'fit' || ' посадка ' END ||
            COALESCE(p.attributes->'print', '') || ' ' ||
            COALESCE(p.gender, '') || ' ' ||
            COALESCE(p.attributes->'model_name', '') || ' ' ||
            COALESCE(p.attributes->'production_country', '') || ' ' ||

            /* extra attributes */
            COALESCE(p.attributes->'type_of_ankle_boots', '') || ' ' ||
            COALESCE(p.attributes->'type_of_men_boots', '') || ' ' ||
            COALESCE(p.attributes->'type_of_boots', '') || ' ' ||
            COALESCE(p.attributes->'type_of_flat_sandals', '') || ' ' ||
            COALESCE(p.attributes->'type_of_women_shoes', '') || ' ' ||
            COALESCE(p.attributes->'type_of_men_shoes', '') || ' ' ||
            COALESCE(p.attributes->'type_of_wallets', '') || ' ' ||
            COALESCE(p.attributes->'type_of_outwear', '') || ' ' ||
            COALESCE(p.attributes->'hair_type', '') || ' ' ||
            COALESCE(p.attributes->'type_of_headwear', '') || ' ' ||
            COALESCE(p.attributes->'type_of_heels', '') || ' ' ||
            COALESCE(p.attributes->'skin_type', '') || ' ' ||
            COALESCE(p.attributes->'type_of_jumpsuits', '') || ' ' ||
            COALESCE(p.attributes->'lens_type', '') || ' ' ||
            COALESCE(p.attributes->'type_of_outwear', '') || ' ' ||
            COALESCE(p.attributes->'type_of_dresses', '') || ' ' ||
            COALESCE(p.attributes->'type_of_blazer_fit', '') || ' ' ||
            COALESCE(p.attributes->'type_of_shirts', '') || ' ' ||
            COALESCE(p.attributes->'type_of_cup_for_swimwear', '') || ' ' ||
            COALESCE(p.attributes->'spike_type', '') || ' ' ||
            COALESCE(p.attributes->'type_of_shorts', '') || ' ' ||
            COALESCE(p.attributes->'type_of_skirt', '') || ' ' ||
            COALESCE(p.attributes->'washout', '') || ' ' ||
            COALESCE(p.attributes->'frame_type', '') || ' ' ||
            COALESCE(p.attributes->'type_of_eyewear', '') || ' ' ||
            COALESCE(p.attributes->'shoulder_strap', '') || ' ' ||
            COALESCE(p.attributes->'type_of_clutches', '') || ' ' ||
            COALESCE(p.attributes->'type_of_necklaces', '') || ' ' ||
            COALESCE(p.attributes->'type_of_backpacks', '') || ' ' ||
            COALESCE(p.attributes->'type_of_bags', '') || ' ' ||
            COALESCE(p.attributes->'type_of_watch', '') || ' ' ||
            COALESCE(p.attributes->'aroma_type', '') || ' ' ||
            COALESCE(p.attributes->'age_type', '') || ' ' ||
            COALESCE(p.attributes->'color_glass', '') || ' ' ||
            COALESCE(p.attributes->'clothes_length', '') || ' ' ||
            COALESCE(p.attributes->'watch_mechanism', '') || ' ' ||
            /* end of extra attributes */

            COALESCE(p.attributes->'base_materials', '') || ' ' ||
            COALESCE(p.attributes->'filler_materials', '') || ' ' ||
            COALESCE(p.attributes->'upper_materials', '') || ' ' ||
            COALESCE(p.attributes->'inside_materials', '') || ' ' ||
            CASE WHEN p.old_price_amount IS NULL THEN '' ELSE 'скидки' END || ' ' ||
            COALESCE((SELECT string_agg(label.name, ',') FROM label WHERE label.id = ANY(p.labels) AND owner='seo'), '') || ' ' ||
            COALESCE((SELECT string_agg(COALESCE(name, '') || ' ' || array_to_string(COALESCE(synonyms, '{}'), ' ', ''), ',')
                FROM category c
                WHERE c.id IN (SELECT unnest(p.categories_denormalized) AS category_id) AND
                      c.is_active AND c.id NOT IN (1, 2)), '')
                    ELSE '' END AS full_text_search,

            array_to_string(COALESCE(avals(p.seasons), '{}'), ' ', '') || ' ' ||
            COALESCE(p.name, '') || ' ' ||
            COALESCE((SELECT name FROM brand WHERE p.brand_id = id), '') || ' ' ||
            COALESCE(p.collection) AS title,
            (SELECT SUM(ps.qty) FROM product_size AS ps WHERE ps.product_sku = p.sku) AS qty,
            p.is_sellable AS is_sellable
          FROM product p
          WHERE NOT p.is_gift AND
            ('${dataimporter.request.clean}' != 'false' OR
             p.sku IN (SELECT product_sku FROM product_size WHERE updated_at >
             CAST('${dataimporter.last_index_time}'::timestamp with time zone at time zone 'UTC' AS timestamp without time zone)))