<?php

namespace App\Services;

use App\Models\Category;
use Illuminate\Support\Facades\DB;

class RetailCategoryService
{
    /**
     * Get retail-focused statistics for a category.
     */
    public function getRetailStatistics(Category $category): array
    {
        return [
            'basic' => $this->getBasicStats($category),
            'stock' => $this->getStockStats($category),
            'pricing' => $this->getPricingStats($category),
            'sales' => $this->getSalesStats($category),
            'financial' => $this->getFinancialStats($category),
            'inventory' => $this->getInventoryStats($category),
        ];
    }
    
    private function getBasicStats(Category $category): array
    {
        return [
            'total_products' => $category->products()->count(),
            'active_products' => $category->products()->where('status', 'active')->count(),
            'inactive_products' => $category->products()->where('status', 'inactive')->count(),
            'digital_products' => $category->products()->where('is_digital', true)->count(),
            'service_products' => $category->products()->where('is_service', true)->count(),
            'subcategories' => $category->children()->count(),
        ];
    }
    
    private function getStockStats(Category $category): array
    {
        return [
            'available_stock' => $category->products()->sum('available_stock'),
            'committed_stock' => $category->products()->sum('committed_stock'),
            'reserved_stock' => $category->products()->sum('reserved_stock'),
            'out_of_stock' => $category->products()->where('available_stock', '<=', 0)->count(),
            'low_stock' => $category->products()->whereColumn('available_stock', '<=', 'minimum_stock')->count(),
            'need_reorder' => $category->products()->whereColumn('available_stock', '<=', 'reorder_point')->count(),
            'over_stock' => $category->products()->whereColumn('available_stock', '>', 'maximum_stock')->count(),
        ];
    }
    
    private function getPricingStats(Category $category): array
    {
        $products = $category->products();
        
        return [
            'avg_sale_price' => $products->avg('sale_price') ?? 0,
            'avg_cost_price' => $products->avg('cost_price') ?? 0,
            'avg_wholesale_price' => $products->avg('wholesale_price') ?? 0,
            'min_sale_price' => $products->min('sale_price') ?? 0,
            'max_sale_price' => $products->max('sale_price') ?? 0,
            'on_sale_count' => $products->where('is_on_sale', true)->count(),
            'discounted_count' => $products->whereNotNull('discount_price')->count(),
        ];
    }
    
    private function getSalesStats(Category $category): array
    {
        return [
            'total_sold' => $category->products()->sum('total_sold'),
            'total_revenue' => $category->products()->sum('total_revenue'),
            'avg_sales_per_product' => $category->products()->avg('total_sold') ?? 0,
            'best_selling_count' => $category->products()->where('is_bestseller', true)->count(),
            'featured_count' => $category->products()->where('is_featured', true)->count(),
            'new_products' => $category->products()->where('is_new', true)->count(),
        ];
    }
    
    private function getFinancialStats(Category $category): array
    {
        $profitMargin = $category->products()
            ->whereNotNull('cost_price')
            ->whereNotNull('sale_price')
            ->where('cost_price', '>', 0)
            ->avg(DB::raw('((sale_price - cost_price) / cost_price) * 100')) ?? 0;
        
        $stockValue = $category->products()
            ->sum(DB::raw('COALESCE(sale_price, 0) * COALESCE(available_stock, 0)'));
        
        $costValue = $category->products()
            ->sum(DB::raw('COALESCE(cost_price, 0) * COALESCE(available_stock, 0)'));
        
        return [
            'stock_value' => $stockValue,
            'cost_value' => $costValue,
            'potential_profit' => $stockValue - $costValue,
            'avg_profit_margin' => round($profitMargin, 2),
            'vat_products' => $category->products()->where('has_vat', true)->count(),
            'vat_amount' => $stockValue * 0.16, // Kenya VAT 16%
        ];
    }
    
    private function getInventoryStats(Category $category): array
    {
        return [
            'track_inventory' => $category->products()->where('track_inventory', true)->count(),
            'allow_backorders' => $category->products()->where('allow_backorders', true)->count(),
            'has_expiry' => $category->products()->where('has_expiry', true)->count(),
            'track_batches' => $category->products()->where('track_batches', true)->count(),
            'manage_serial' => $category->products()->where('manage_serial_numbers', true)->count(),
        ];
    }
}