SELECT 
    c.id,
    c.name,
    c.description,
    c.is_active,
    COUNT(p.id) as product_count,
    COALESCE(SUM(i.quantity), 0) as total_stock,
    c.created_at,
    c.updated_at
FROM product_categories c
LEFT JOIN products p ON c.id = p.category_id AND p.deleted_at IS NULL
LEFT JOIN inventory i ON p.id = i.product_id
WHERE c.deleted_at IS NULL
GROUP BY c.id