SELECT pe.entity_id as 'product_id', pe.category_mid, pe.category_mid_id, pe.type_id,
name.value as 'product_name', img_url.value as 'image_src', url0.value as product_url_store_0, url1.value as product_url_store_1,
GREATEST(IFNULL(rating.numericScore,0), IFNULL(review.reviews_count,0)) as 'rating_count', review.rating_summary,
if(sp.value = 0, 0, concat(format(sp.value, 0,'vi_VN'),'đ')) as 'display_final_price',
if(p.value = 0, 0, concat(format(p.value, 0, 'vi_VN'), 'đ')) as 'display_price',
if(sp.value = 0, 0, ROUND(((p.value - sp.value)/p.value)*100)) as 'discount',
if(bd.final_price = 0, 0, concat(format(bd.final_price, 0, 'vi_VN'), 'đ')) as 'bundle_display_final_price',
if(bd.price = 0, 0, concat(format(bd.price, 0, 'vi_VN'), 'đ')) as 'bundle_display_price',
if(bd.final_price = 0, 0, ROUND(((bd.price - bd.final_price) / bd.price) * 100)) as 'bundle_discount'
FROM fhs_personalize_products pp
JOIN fhs_catalog_product_entity pe ON pp.related_pid = pe.entity_id
LEFT JOIN fhs_catalog_product_entity_varchar name ON name.entity_id = pe.entity_id AND name.attribute_id = 71
LEFT JOIN fhs_catalog_product_entity_varchar img_url ON pe.entity_id=img_url.entity_id AND img_url.attribute_id=85
LEFT JOIN fhs_product_rating rating ON rating.sku = pe.sku
LEFT JOIN fhs_review_entity_summary review ON review.entity_pk_value = pe.entity_id AND review.store_id=1
LEFT JOIN fhs_catalog_product_entity_decimal p ON p.entity_id = pe.entity_id AND p.attribute_id = 75
LEFT JOIN fhs_catalog_product_entity_decimal sp ON sp.entity_id = pe.entity_id AND sp.attribute_id = 76 AND sp.store_id = 0
LEFT JOIN fhs_catalog_product_entity_varchar url0 ON pe.entity_id = url0.entity_id AND url0.attribute_id = 98 AND url0.store_id=0
LEFT JOIN fhs_catalog_product_entity_varchar url1 ON pe.entity_id = url1.entity_id AND url1.attribute_id = 98 AND url1.store_id=1
LEFT JOIN fhs_catalog_product_index_price_store bd ON pe.entity_id = bd.entity_id AND bd.customer_group_id = 0 AND bd.store_id = 1
INNER JOIN fhs_catalog_product_entity_int AS ei ON ei.entity_id = pe.entity_id AND ei.attribute_id = '102' AND ei.store_id = 0 AND ei.value = 4
INNER JOIN fhs_catalog_product_entity_int AS ei2 ON ei2.entity_id = pe.entity_id AND ei2.attribute_id = '96' AND ei2.store_id = 0 AND ei2.value = 1
GROUP BY pe.entity_id;