select 
                p.id, p.code, concat(p.name, ' ', lcase(c.name)) as name, 
                case 
                    when p.discountPrice > 0 then p.discountPrice
                    when p.offer = 1 then 
                        case 
                        when p.discountPrice > 0 then p.discountPrice
                        else p.price
                        end
                    when o.amount is not null then p.price - convert((o.amount / 100) * p.price, decimal(10, 2))
                    else p.price
                end as price,  
                p.price as initialPrice, 
                case 
                    when p.discountPercent > 0 then p.discountPercent
                    when o.amount is not null then o.amount
                    else p.discountPercent
                end as discountPercent,
                p.newArrival, 
                p.offer, 
                p.published, 
                p.storePrice,
                pc.colorId, 
                concat(p.slug, '-', c.slug) as slug, 
                p.slug as productSlug,
                pi.image
                from products p
                inner join products_colors pc on pc.productId = p.id
                inner join colors c on c.id = pc.colorId
                inner join productimages pi on pi.productId = p.id and pi.colorId = pc.colorId and pi.ordering = 1
                inner join categories parent on parent.id = ?
                left join (
                    select 
                    amount 
                    from offers
                    where now() between dateFrom and dateTo
                    limit 1
                ) o on 1 = 1
                where p.published = 1 and EXISTS (
                                    SELECT 1
                                    FROM products_categories pc
                                    JOIN categories cat ON cat.id = pc.categoryId
                                    WHERE pc.productId = p.id
                                    AND cat.lft BETWEEN parent.lft AND parent.rgt
                                ) and 1 = (case 
                                        when exists (
                                            select 1 from productavailability pa 
                                            where pa.productId = p.id 
                                            and pa.colorId = pc.colorId 
                                            and pa.available = 1 
                                            and pa.sizeId in (?)) 
                                        then 1 else 0 
                                    end) and (p.winter = 1 or p.plusSize = 1 or p.casualCollection = 1) order by p.id desc, pc.colorId desc LIMIT ?, ?