WITH ProductRatings AS (
        SELECT 
            p.id,
            c.category_name,
            s.subcategory_name,
            b.brand_name,
            p.product_name,
            p.product_description,
            pi.image_url,
            ps.price,
            ps.sale_price,
            ps.discount,
            ps.stock,
            ps.bv,
            COUNT(pr.id) AS review_count,
            COALESCE(ROUND(AVG(pr.review_rating), 1), 0) AS average_rating
        FROM shop_products p
        LEFT JOIN shop_categories c ON p.category_id = c.id
        LEFT JOIN shop_subcategories s ON p.subcategory_id = s.id
        LEFT JOIN shop_brand b ON p.brand_id = b.id
        LEFT JOIN shop_product_images pi ON p.id = pi.product_id
        LEFT JOIN shop_stock ps ON p.id = ps.product_id
        LEFT JOIN shop_warehouse w ON ps.warehouse_id = w.id
        LEFT JOIN shop_reviews pr ON p.id = pr.product_id
        WHERE w.pincode = 822114 AND w.status = 1 GROUP BY p.id, pi.image_url, ps.price, ps.sale_price, ps.discount, ps.stock, ps.bv
    ) SELECT * FROM ProductRatings WHERE 1=1 ORDER BY id LIMIT 10 OFFSET 0

WITH ProductRatings AS (
SELECT
p.id,
c.category_name,
s.subcategory_name,
b.brand_name,
p.product_name,
p.product_description,
pi.image_url,
ps.price,
ps.sale_price,
ps.discount,
ps.stock,
ps.bv,
COUNT(pr.id) AS review_count,
COALESCE(ROUND(AVG(pr.review_rating), 1), 0) AS average_rating
FROM shop_products p
LEFT JOIN shop_categories c ON p.category_id = c.id
LEFT JOIN shop_subcategories s ON p.subcategory_id = s.id
LEFT JOIN shop_brand b ON p.brand_id = b.id
LEFT JOIN shop_product_images pi ON p.id = pi.product_id
LEFT JOIN shop_stock ps ON p.id = ps.product_id
LEFT JOIN shop_warehouse w ON ps.warehouse_id = w.id
LEFT JOIN shop_reviews pr ON p.id = pr.product_id
WHERE w.pincode = 822114 AND w.status = 1 GROUP BY p.id, pi.image_url, ps.price, ps.sale_price, ps.discount,
ps.stock, ps.bv
) SELECT * FROM ProductRatings WHERE 1=1 AND review_count > 0 AND average_rating >= 2.9 ORDER BY id LIMIT 10
OFFSET 0;

ALTER TABLE `order_number` 
MODIFY COLUMN `date` date DEFAULT NULL,
MODIFY COLUMN `order_time` time DEFAULT NULL,
MODIFY COLUMN `mts` int DEFAULT NULL,
MODIFY COLUMN `total_items` text DEFAULT NULL,
MODIFY COLUMN `bv` text DEFAULT NULL,
MODIFY COLUMN `delivery_charge` text DEFAULT NULL,
MODIFY COLUMN `total_amount` text DEFAULT NULL,
MODIFY COLUMN `status` text DEFAULT NULL,
MODIFY COLUMN `canreason` text DEFAULT NULL,
MODIFY COLUMN `returnamount` text DEFAULT NULL,
MODIFY COLUMN `employee` text DEFAULT NULL,
MODIFY COLUMN `c_time` time DEFAULT NULL,
MODIFY COLUMN `pr_time` time DEFAULT NULL,
MODIFY COLUMN `p_time` time DEFAULT NULL,
MODIFY COLUMN `out_time` time DEFAULT NULL,
MODIFY COLUMN `del_time` time DEFAULT NULL,
MODIFY COLUMN `delivery_date` date DEFAULT NULL,
MODIFY COLUMN `cancel_date` datetime DEFAULT NULL,
MODIFY COLUMN `name` text DEFAULT NULL,
MODIFY COLUMN `a_number` text DEFAULT NULL,
MODIFY COLUMN `address` text DEFAULT NULL,
MODIFY COLUMN `longi` text DEFAULT NULL,
MODIFY COLUMN `lati` text DEFAULT NULL,
MODIFY COLUMN `city` text DEFAULT NULL,
MODIFY COLUMN `state` text DEFAULT NULL,
MODIFY COLUMN `pincode` text DEFAULT NULL,
MODIFY COLUMN `payment` text DEFAULT NULL,
MODIFY COLUMN `delivery_employee` text DEFAULT NULL,
MODIFY COLUMN `Checking_Employee` text DEFAULT NULL,
MODIFY COLUMN `officestaff` text DEFAULT NULL,
MODIFY COLUMN `AgentId` varchar(20) DEFAULT NULL,
MODIFY COLUMN `officepaid` text DEFAULT NULL,
MODIFY COLUMN `amount` text DEFAULT NULL,
MODIFY COLUMN `branch` text DEFAULT NULL,
MODIFY COLUMN `discount` text DEFAULT NULL,
MODIFY COLUMN `gst` text DEFAULT NULL,
MODIFY COLUMN `offer` text DEFAULT NULL,
MODIFY COLUMN `offeramount` text DEFAULT NULL,
MODIFY COLUMN `RedeemSerial` text DEFAULT NULL,
MODIFY COLUMN `redeemPoints` text DEFAULT NULL,
MODIFY COLUMN `managerid` text DEFAULT NULL,
MODIFY COLUMN `AppVersion` text DEFAULT NULL,
MODIFY COLUMN `OS` text DEFAULT NULL,
MODIFY COLUMN `print_type` enum('core','noncore') DEFAULT NULL,
MODIFY COLUMN `redeem_type` enum('account','cash') DEFAULT 'cash',
MODIFY COLUMN `cancel_by` varchar(200) DEFAULT NULL;
