SELECT 
    bl.name as location_name,
    p.name as product_name,
    SUM(tsl.quantity) as quantity_sold,
    vld.qty_available as current_stock,
    CASE 
        WHEN vld.qty_available > 0 
        THEN SUM(tsl.quantity)/vld.qty_available 
        ELSE 0 
    END as turnover_ratio
FROM business_locations bl
JOIN transactions t ON bl.id = t.location_id
JOIN transaction_sell_lines tsl ON t.id = tsl.transaction_id
JOIN products p ON tsl.product_id = p.id
LEFT JOIN variation_location_details vld ON p.id = vld.product_id AND bl.id = vld.location_id
WHERE t.type = "sell" 
AND t.status = "final"
AND t.transaction_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY bl.id, bl.name, p.id, p.name, vld.qty_available
HAVING quantity_sold > 0
ORDER BY bl.name, turnover_ratio DESC;