QueenFruits/Backend/app/Services/ProductService.php

<?php

namespace App\Services;

use App\Http\Resources\Home\ProductByOutletResource;
use App\Http\Resources\Product\ProductInfoResource;
use App\Models\Product;
use App\Models\Tenant;
use App\Traits\Multitenantable;

class ProductService {
    use Multitenantable;

    public function getProductById(string $uuid)
    {
        $tenantId = config('app.current_tenant_id');

        $tenant = Tenant::withoutGlobalScopes()
        ->select('review_display_type', 'review_display_policy')
        ->where('uuid', $tenantId)
        ->first();

        $product = Product::select([
        'products.uuid', 
        'products.server_image_url', 
        'products.name', 
        'products.has_variant',
        'products.description',
        'products.unit_id'
        ])
        ->with(['unit' => function($q) {
            $q->select('units.uuid', 'units.name');
        },'variants' => function($q) {
            $q->select(
            'product_variants.uuid', 
            'product_variants.product_id', 
            'product_variants.server_image_url', 
            'product_variants.total_sold', 
            'product_variants.name', 
            'product_variants.is_product_variant'
            )
            ->where('product_variants.is_visible_online', true);
        },'variants.inventory' => function($q) {
            $q->select(
            'outlet_inventories.item_id', 
            'outlet_inventories.selling_price',
            'outlet_inventories.stock_type', 
            'outlet_inventories.stock'
            );
        },'variants.reviews' => function($q) {
            $q->select(
            'product_reviews.uuid',
            'product_reviews.customer_id', 
            'product_reviews.product_variant_id',
            'product_reviews.rating',
            'product_reviews.comment'
            )
            ->with(['customer' => function($q2) {
                $q2->select('customers.uuid','customers.name');
            }]);
        }])
        ->where('uuid', $uuid)
        ->first();
        
        return [
            'review_display_type' => $tenant->review_display_type,
            'review_display_policy' => $tenant->review_display_policy,
            'product_info' => new ProductInfoResource($product)
        ];
    }

    public function getProductByName($currentOutletId, $name)
    { 
        $products = Product::select([
        'products.uuid', 
        'products.name', 
        'products.server_image_url'
        ])
        ->whereHas('variants', function($q) use ($currentOutletId) {
            $q->select('product_variants.is_visible_online')
            ->whereHas('inventory', function($q2) use ($currentOutletId) {
                $q2->where('outlet_inventories.outlet_id', $currentOutletId);
            })
            ->where('product_variants.is_visible_online', true);
        })
        ->withSum(['variants as total_sold'], 'total_sold')
        ->withAvg(['variants as average_rating'], 'average_rating')
        ->with([
            'variants.inventory' => function ($q) use ($currentOutletId) {
                $q->select('outlet_inventories.item_id', 'selling_price')
                ->where('outlet_id', $currentOutletId);
            }
        ])
        ->where('products.name', 'like', '%'.$name.'%')
        ->orderByDesc('total_sold')
        ->withSum(['variants as total_rating'], 'total_rating')
        ->selectRaw('
            (SELECT SUM(average_rating * total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ) / 
            NULLIF((SELECT SUM(total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ), 0) as average_rating
        ')
        ->withCount('likes')
        ->get();

        return ProductByOutletResource::collection($products);   
    }

    public function getProductByCategory($currentOutletId, $cateogryId)
    {
        $products = Product::select([
        'products.uuid', 
        'products.name', 
        'products.server_image_url'
        ])
        ->whereHas('variants', function($q) use ($currentOutletId) {
            $q->select('product_variants.is_visible_online')
            ->whereHas('inventory', function($q2) use ($currentOutletId) {
                $q2->where('outlet_inventories.outlet_id', $currentOutletId);
            })
            ->where('product_variants.is_visible_online', true);
        })
        ->withSum(['variants as total_sold'], 'total_sold')
        ->withSum(['variants as total_rating'], 'total_rating')
        ->selectRaw('
            (SELECT SUM(average_rating * total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ) / 
            NULLIF((SELECT SUM(total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ), 0) as average_rating
        ')
        ->with([
            'variants.inventory' => function ($q) use ($currentOutletId) {
                $q->select('outlet_inventories.item_id', 'selling_price')
                ->where('outlet_id', $currentOutletId);
            }
        ])
        ->where('products.category_id', $cateogryId)
        ->orderByDesc('total_sold')
        ->orderByDesc('average_rating')
        ->withCount('likes')
        ->get();

        return ProductByOutletResource::collection($products);
    }

    public function getProductBestSellerByOutlet($currentOutletId)
    {
        $products = Product::select([
        'products.uuid', 
        'products.name', 
        'products.server_image_url'
        ])
        ->whereHas('variants', function($q) use ($currentOutletId) {
            $q->select('product_variants.is_visible_online')
            ->whereHas('inventory', function($q2) use ($currentOutletId) {
                $q2->where('outlet_inventories.outlet_id', $currentOutletId);
            })
            ->where('product_variants.is_visible_online', true);
        })
        ->withSum(['variants as total_sold'], 'total_sold')
        ->withSum(['variants as total_rating'], 'total_rating')
        ->selectRaw('
            (SELECT SUM(average_rating * total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ) / 
            NULLIF((SELECT SUM(total_rating) 
            FROM product_variants 
            WHERE product_variants.product_id = products.uuid
            ), 0) as average_rating
        ')
        ->with([
            'variants.inventory' => function ($q) use ($currentOutletId) {
                $q->select('outlet_inventories.item_id', 'selling_price')
                ->where('outlet_id', $currentOutletId);
            }
        ])
        ->orderByDesc('total_sold')
        ->orderByDesc('average_rating')
        ->withCount('likes')
        ->take(5)
        ->get();

        return ProductByOutletResource::collection($products);
    }
    
}