SELECT 1 as availableInLocation, 
COALESCE(uwlp.uwlp_selprod_id, 0) as is_in_any_wishlist, 
product_id, 
selprod_sku, 
product_identifier, 
COALESCE(product_name,product_identifier) as product_name, 
product_seller_id, 
product_model, 
product_type, 
prodcat_id, 
COALESCE(prodcat_name,prodcat_identifier) as prodcat_name, 
product_upc, 
product_isbn, 
product_short_description, 
product_description, 
selprod_id, 
selprod_user_id, 
selprod_code, 
selprod_condition, 
selprod_price, 
special_price_found, 
selprod_fulfillment_from, 
splprice_start_date, 
splprice_end_date, 
COALESCE(selprod_title, product_name, product_identifier) as selprod_title, 
selprod_warranty, 
selprod_return_policy, 
selprodComments, 
theprice, 
selprod_stock, 
selprod_threshold_stock_level, 
IF(selprod_stock > 0, 1, 0) AS in_stock, 
brand_id, 
COALESCE(brand_name, brand_identifier) as brand_name, 
brand_short_description, 
user_name, 
shop_id, 
COALESCE(shop_name, shop_identifier) as shop_name, 
splprice_display_dis_type, 
splprice_display_dis_val, 
splprice_display_list_price, 
product_attrgrp_id, 
product_youtube_video, 
product_cod_enabled, 
selprod_cod_enabled, 
selprod_available_from, 
selprod_min_order_qty, 
product_updated_on, 
product_warranty, 
selprod_return_age, 
selprod_cancellation_age, 
selprod_exchange_age, 
shop_return_age, 
shop_cancellation_age, 
shop_exchange_age, 
shop_shipment_days, 
shop_fulfillment_by, 
selprod_fulfillment_type, 
shop_fulfillment_type, 
product_fulfillment_type, 
product_attachements_with_inventory, 
selprod_product_id, 
COALESCE(shop_state_l.state_name,state_identifier) as shop_state_name, 
COALESCE(shop_country_l.country_name,shop_country.country_code) as shop_country_name, 
product_warranty_unit, 
product_sizechart_id, 
IF((FIND_IN_SET(173, p.product_country_ids) OR p.product_country_ids IS NULL OR p.product_country_ids = ""), 1, 0) AS availableInLocation
 FROM 
`tbl_products` p  
LEFT OUTER JOIN `tbl_products_lang`  AS tp_l  
 ON productlang_product_id = p.product_id AND productlang_lang_id = 1  
LEFT OUTER JOIN (SELECT sprods.*, 
m.*, 
if(m.splprice_selprod_id IS NULL,0,1) AS special_price_found, 
COALESCE(m.splprice_price, selprod_price) AS theprice, 
selprod_title, 
selprod_warranty, 
selprod_return_policy, 
sprods_l.selprod_comments as selprodComments, 
sprods_l.selprod_keywords
 FROM 
`tbl_seller_products` sprods  
LEFT OUTER JOIN `tbl_seller_products_lang`  AS sprods_l  
 ON sprods.selprod_id = sprods_l.selprodlang_selprod_id AND sprods_l.selprodlang_lang_id = 1  
LEFT OUTER JOIN `tbl_product_special_prices`  AS m  
 ON m.splprice_selprod_id = selprod_id AND '2026-04-08' BETWEEN m.splprice_start_date AND m.splprice_end_date  
LEFT OUTER JOIN `tbl_product_special_prices`  AS s  
 ON s.splprice_selprod_id = selprod_id AND s.splprice_price < m.splprice_price
                 AND '2026-04-08' BETWEEN s.splprice_start_date AND s.splprice_end_date  
WHERE s.`splprice_selprod_id` IS NULL 
AND `selprod_active` = 1 
AND `selprod_deleted` = 0 
AND `selprod_product_id` = '1658')  AS pricetbl  
 ON p.product_id = pricetbl.selprod_product_id  
INNER JOIN `tbl_users`  AS seller_user  
 ON selprod_user_id = seller_user.user_id and seller_user.user_is_supplier = 1 AND seller_user.user_deleted = 0  
INNER JOIN `tbl_user_credentials`  AS seller_user_cred  
 ON credential_user_id = seller_user.user_id and credential_active = 1 and credential_verified = 1  
INNER JOIN `tbl_shops`  AS shop  
 ON selprod_user_id = shop.shop_user_id  and shop.shop_user_valid = 1 and shop.shop_active = 1 and shop.shop_supplier_display_status = 1  
LEFT OUTER JOIN `tbl_shops_lang`  AS s_l  
 ON shop.shop_id = s_l.shoplang_shop_id AND shoplang_lang_id = 1  
INNER JOIN `tbl_countries`  AS shop_country  
 ON shop.shop_country_id = shop_country.country_id and shop_country.country_active = 1  
LEFT OUTER JOIN `tbl_countries_lang`  AS shop_country_l  
 ON shop_country.country_id = shop_country_l.countrylang_country_id AND shop_country_l.countrylang_lang_id = 1  
INNER JOIN `tbl_states`  AS shop_state  
 ON shop.shop_state_id = shop_state.state_id and shop_state.state_active = 1  
LEFT OUTER JOIN `tbl_states_lang`  AS shop_state_l  
 ON shop_state.state_id = shop_state_l.statelang_state_id AND shop_state_l.statelang_lang_id = 1  
LEFT OUTER JOIN `tbl_brands`  AS brand  
 ON p.product_brand_id = brand.brand_id  
LEFT OUTER JOIN `tbl_brands_lang`  AS tb_l  
 ON brand.brand_id = tb_l.brandlang_brand_id AND brandlang_lang_id = 1  
LEFT OUTER JOIN `tbl_shipping_packages`  AS shipkg  
 ON shipkg.shippack_id = p.product_ship_package  
INNER JOIN `tbl_product_to_category`  AS ptc  
 ON ptc.ptc_product_id = p.product_id  
INNER JOIN `tbl_product_categories`  AS c  
 ON c.prodcat_id = ptc.ptc_prodcat_id and c.prodcat_active = 1 and c.prodcat_deleted = 0  
LEFT OUTER JOIN `tbl_product_categories_lang`  AS c_l  
 ON c_l.prodcatlang_prodcat_id = c.prodcat_id AND prodcatlang_lang_id = 1  
LEFT OUTER JOIN `tbl_shop_specifics`  AS ss  
 ON shop.shop_id = ss.ss_shop_id  
LEFT JOIN `tbl_product_specifics`  AS ps  
 ON ps.ps_product_id = p.product_id  
LEFT JOIN `tbl_seller_product_specifics`  AS sps  
 ON sps.sps_selprod_id = selprod_id  
INNER JOIN `tbl_product_to_tax`  AS ptt  
 ON ptt.ptt_product_id = product_id  
INNER JOIN `tbl_tax_categories`  AS pt  
 ON pt.taxcat_id = ptt.ptt_taxcat_id AND pt.taxcat_plugin_id = 0 and pt.taxcat_active = 1 and pt.taxcat_deleted = 0  
LEFT OUTER JOIN (SELECT uwlp_selprod_id, 
uwlp_uwlist_id
 FROM 
`tbl_user_wish_list_products` uwlp  
INNER JOIN `tbl_user_wish_lists`  AS uwl  
 ON uwl.uwlist_id = uwlp.uwlp_uwlist_id  
WHERE `uwlist_user_id` = 0)  AS uwlp  
 ON uwlp.uwlp_selprod_id = selprod_id  
WHERE `product_active` = 1 
AND `product_deleted` = 0 
AND `product_approved` = 1 
AND `selprod_user_id` > 0 
AND shop.`shop_user_valid` = 1 
AND shop.`shop_active` = 1 
AND shop.`shop_supplier_display_status` = 1 
AND c.`prodcat_active` = 1 
AND c.`prodcat_deleted` = 0 
AND c.`prodcat_status` = 1 
AND `selprod_id` = '3165' 
AND `selprod_deleted` = '0'