<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class Product extends Model
{
    use HasFactory, SoftDeletes;

    protected $fillable = [
        // Basic Information
        'sku', 'barcode', 'name', 'slug', 
        
        // Descriptions
        'description', 'short_description', 'specifications',
        
        // Relationships
        'category_id', 'brand_id', 'unit_id', 'tax_id', 'supplier_id',
        
        // Reference Numbers
        'ref_number', 'supplier_ref', 'manufacturer_ref',
        
        // Inventory Management
        'stock', 'reorder_point', 'minimum_stock', 'maximum_stock',
        'reserved_stock', 'committed_stock', 
        'stock_status', 'track_inventory', 'allow_backorders',
        
        // Batch & Expiry Management
        'has_expiry', 'track_batches', 'expiry_warning_days', 'expiry_alert_level',
        'manage_serial_numbers', 'manage_batch_numbers',
        
        // Pricing
        'cost_price', 'sale_price', 'wholesale_price', 
        'discount_price', 'discount_percent', 'old_price',
        'has_vat',
        
        // Product Attributes
        'weight', 'dimensions', 'color', 'size', 'material',
        
        // Media
        'primary_image', 'gallery_images', 'video_url',
        
        // SEO & Marketing
        'meta_title', 'meta_description', 'meta_keywords',
        
        // Status & Visibility
        'status', 'is_featured', 'is_new', 'is_bestseller', 
        'is_on_sale', 'is_digital', 'is_service',
        
        // Warranty & Support
        'warranty_months', 'warranty_terms', 'support_email', 'support_phone',
        
        // Shipping
        'shipping_weight', 'shipping_class', 'free_shipping',
        
        // Analytics
        'total_sold', 'total_revenue', 'view_count', 'wishlist_count',
        
        // Supplier Information
        'supplier_sku', 'supplier_name', 'supplier_cost', 'lead_time_days',
        
        // Product Identification
        'upc', 'ean', 'isbn', 'mpn',
        
        // Audit Timestamps
        'price_last_updated_at', 'stock_last_updated_at', 'last_sold_at',
    ];

    protected $casts = [
        // Pricing
        'cost_price' => 'decimal:2',
        'sale_price' => 'decimal:2',
        'wholesale_price' => 'decimal:2',
        'discount_price' => 'decimal:2',
        'old_price' => 'decimal:2',
        'supplier_cost' => 'decimal:2',
        'total_revenue' => 'decimal:2',
        
        // Shipping
        'shipping_weight' => 'decimal:2',
        
        // Boolean Flags
        'has_vat' => 'boolean',
        'has_expiry' => 'boolean',
        'track_batches' => 'boolean',
        'track_inventory' => 'boolean',
        'allow_backorders' => 'boolean',
        'manage_serial_numbers' => 'boolean',
        'manage_batch_numbers' => 'boolean',
        'is_featured' => 'boolean',
        'is_new' => 'boolean',
        'is_bestseller' => 'boolean',
        'is_on_sale' => 'boolean',
        'is_digital' => 'boolean',
        'is_service' => 'boolean',
        'free_shipping' => 'boolean',
        
        // Arrays/JSON
        'gallery_images' => 'array',
        'meta_keywords' => 'array',
        
        // Integers
        'stock' => 'integer',
        'reorder_point' => 'integer',
        'minimum_stock' => 'integer',
        'maximum_stock' => 'integer',
        'reserved_stock' => 'integer',
        'committed_stock' => 'integer',
        'discount_percent' => 'integer',
        'warranty_months' => 'integer',
        'lead_time_days' => 'integer',
        'total_sold' => 'integer',
        'view_count' => 'integer',
        'wishlist_count' => 'integer',
        'expiry_warning_days' => 'integer',
        
        // Dates
        'price_last_updated_at' => 'datetime',
        'stock_last_updated_at' => 'datetime',
        'last_sold_at' => 'datetime',
    ];

    protected $appends = [
        'final_price',
        'is_low_stock',
        'stock_status',
        'expiry_summary',
        'requires_batch_tracking',
        'available_stock',
        'profit_margin',
        'profit_margin_percent',
        'is_discounted',
        'discount_amount',
        'expiry_status',
        'age_in_days',
        'is_vatable',
        'tax_info',
        'is_stock_item',
        'is_service',
        'inventory_value',
        'potential_revenue',
        'days_of_inventory',
    ];

    /**
     * Boot the model.
     */
    protected static function boot()
    {
        parent::boot();

        static::creating(function ($product) {
            // Generate slug if not provided
            if (empty($product->slug)) {
                $product->slug = \Illuminate\Support\Str::slug($product->name);
            }
            
            // Set default values
            if (is_null($product->stock_status)) {
                $product->stock_status = $product->stock > 0 ? 'in_stock' : 'out_of_stock';
            }
            
            if (is_null($product->track_inventory)) {
                $product->track_inventory = true;
            }
            
            // Set expiry defaults
            if ($product->has_expiry) {
                if (is_null($product->expiry_warning_days)) {
                    $product->expiry_warning_days = 30;
                }
                if (empty($product->expiry_alert_level)) {
                    $product->expiry_alert_level = 'warning';
                }
            }
        });

        static::saving(function ($product) {
            // Calculate final price
            $product->final_price = $product->calculateFinalPrice();
            
            // Update is_on_sale based on discount
            $product->is_on_sale = ($product->discount_price > 0) || ($product->discount_percent > 0);
            
            // Update stock status
            $product->updateStockStatus();
        });

        static::updated(function ($product) {
            // Record price history
            if ($product->wasChanged(['sale_price', 'cost_price', 'wholesale_price'])) {
                $product->recordPriceHistory();
            }
            
            // Update timestamps
            if ($product->wasChanged('sale_price') || $product->wasChanged('cost_price')) {
                $product->price_last_updated_at = now();
            }
            
            if ($product->wasChanged('stock')) {
                $product->stock_last_updated_at = now();
            }
        });
    }

    /**
     * Relationships
     */
    public function category(): BelongsTo
    {
        return $this->belongsTo(Category::class);
    }

    public function brand(): BelongsTo
    {
        return $this->belongsTo(Brand::class);
    }

    public function unit(): BelongsTo
    {
        return $this->belongsTo(Unit::class);
    }

    public function tax(): BelongsTo
    {
        return $this->belongsTo(Tax::class);
    }

    public function supplier(): BelongsTo
    {
        return $this->belongsTo(Supplier::class);
    }

    public function batches(): HasMany
    {
        return $this->hasMany(ProductBatch::class)->orderBy('expiry_date');
    }

    public function activeBatches(): HasMany
    {
        return $this->hasMany(ProductBatch::class)
            ->where('current_quantity', '>', 0)
            ->where('quality_status', 'approved')
            ->orderBy('expiry_date');
    }

    public function priceHistories(): HasMany
    {
        return $this->hasMany(PriceHistory::class)->latest();
    }

    public function stockMovements(): HasMany
    {
        return $this->hasMany(StockMovement::class)->latest();
    }

    public function purchaseItems(): HasMany
    {
        return $this->hasMany(PurchaseItem::class);
    }

    public function saleItems(): HasMany
    {
        return $this->hasMany(SaleItem::class);
    }

    /**
     * Accessors
     */
    public function getFinalPriceAttribute(): float
    {
        return $this->calculateFinalPrice();
    }

    public function calculateFinalPrice(): float
    {
        if ($this->discount_price && $this->discount_price > 0) {
            return $this->discount_price;
        }
        
        if ($this->discount_percent && $this->discount_percent > 0) {
            return $this->sale_price * (1 - ($this->discount_percent / 100));
        }
        
        return $this->sale_price;
    }

    public function getIsLowStockAttribute(): bool
    {
        return $this->stock <= $this->reorder_point && $this->stock > 0;
    }

    public function getStockStatusAttribute(): string
    {
        if ($this->stock <= 0) {
            return 'out_of_stock';
        } elseif ($this->stock <= $this->minimum_stock) {
            return 'low_stock';
        } else {
            return 'in_stock';
        }
    }

    public function getAvailableStockAttribute(): int
    {
        return $this->stock - $this->reserved_stock;
    }

    public function getProfitMarginAttribute(): float
    {
        return $this->sale_price - $this->cost_price;
    }

    public function getProfitMarginPercentAttribute(): float
    {
        if ($this->cost_price == 0) return 0;
        return (($this->sale_price - $this->cost_price) / $this->cost_price) * 100;
    }

    public function getIsDiscountedAttribute(): bool
    {
        return $this->discount_price > 0 || $this->discount_percent > 0;
    }

    public function getDiscountAmountAttribute(): float
    {
        if ($this->discount_price > 0) {
            return $this->sale_price - $this->discount_price;
        } elseif ($this->discount_percent > 0) {
            return $this->sale_price * ($this->discount_percent / 100);
        }
        return 0;
    }

    public function getExpirySummaryAttribute(): array
    {
        if (!$this->has_expiry || !$this->track_batches) {
            return [
                'has_expiry' => false,
                'track_batches' => false,
                'message' => 'No expiry or batch tracking required',
                'expired_quantity' => 0,
                'expiring_soon_quantity' => 0,
                'valid_quantity' => $this->stock,
                'total_batches' => 0,
                'alert_level' => 'normal',
            ];
        }

        $expired = $this->batches()
            ->where('current_quantity', '>', 0)
            ->where('expiry_date', '<', now())
            ->sum('current_quantity');

        $expiringSoon = $this->batches()
            ->where('current_quantity', '>', 0)
            ->where('expiry_date', '>=', now())
            ->where('expiry_date', '<=', now()->addDays($this->expiry_warning_days))
            ->sum('current_quantity');

        return [
            'has_expiry' => true,
            'track_batches' => true,
            'expired_quantity' => $expired,
            'expiring_soon_quantity' => $expiringSoon,
            'valid_quantity' => $this->stock - $expired - $expiringSoon,
            'total_batches' => $this->batches()->count(),
            'active_batches' => $this->activeBatches()->count(),
            'alert_level' => $expired > 0 ? 'critical' : ($expiringSoon > 0 ? 'warning' : 'normal'),
        ];
    }

    public function getRequiresBatchTrackingAttribute(): bool
    {
        return $this->track_batches;
    }

    public function getExpiryStatusAttribute(): string
    {
        if (!$this->has_expiry) return 'no_expiry';
        
        $summary = $this->expiry_summary;
        return $summary['alert_level'];
    }

    public function getAgeInDaysAttribute(): ?int
    {
        if (!$this->created_at) return null;
        return $this->created_at->diffInDays();
    }

    public function getIsServiceAttribute(): bool
    {
        return $this->attributes['is_service'] ?? false;
    }

    public function getIsStockItemAttribute(): bool
    {
        return ($this->attributes['is_service'] ?? false) === false && 
               ($this->attributes['track_inventory'] ?? true) === true;
    }

// Add this method to your existing Product model (App\Models\Product.php)
public function getTaxRateAttribute(): float
{
    // If product has explicit tax assigned, use that
    if ($this->tax_id) {
        $tax = Tax::find($this->tax_id);
        if ($tax && $tax->is_active) {
            return (float) $tax->rate;
        }
    }
    
    // If product doesn't have VAT
    if (!$this->has_vat) {
        return 0.00;
    }
    
    // Try to get default tax from system
    $defaultTax = Tax::where('is_default', true)
        ->where('is_active', true)
        ->first();
    
    // Return 0 if no default tax found (don't assume 16%)
    return $defaultTax ? (float) $defaultTax->rate : 0.00;
}

public function getIsVatableAttribute(): bool
{
    // Product is vatable only if it has VAT enabled AND has a tax rate > 0
    return $this->has_vat && $this->tax_rate > 0;
}

public function getTaxInfoAttribute(): array
{
    $tax = $this->tax_id ? Tax::find($this->tax_id) : null;
    
    if ($tax && $tax->is_active) {
        return [
            'id' => $tax->id,
            'rate' => (float) $tax->rate,
            'name' => $tax->name,
            'code' => $tax->code,
            'is_vatable' => $this->is_vatable,
            'is_active' => $tax->is_active,
        ];
    }
    
    // If no tax assigned or tax is inactive
    if (!$this->has_vat) {
        return [
            'id' => null,
            'rate' => 0.00,
            'name' => 'No VAT',
            'code' => 'NONE',
            'is_vatable' => false,
            'is_active' => false,
        ];
    }
    
    // Check for default tax
    $defaultTax = Tax::where('is_default', true)
        ->where('is_active', true)
        ->first();
    
    if ($defaultTax) {
        return [
            'id' => $defaultTax->id,
            'rate' => (float) $defaultTax->rate,
            'name' => $defaultTax->name,
            'code' => $defaultTax->code,
            'is_vatable' => true,
            'is_active' => true,
        ];
    }
    
    // No tax system configured
    return [
        'id' => null,
        'rate' => 0.00,
        'name' => 'No Tax Configured',
        'code' => 'NONE',
        'is_vatable' => false,
        'is_active' => false,
    ];
}

public function getAssignedTaxAttribute(): ?Tax
{
    if ($this->tax_id) {
        $tax = Tax::find($this->tax_id);
        if ($tax && $tax->is_active) {
            return $tax;
        }
    }
    return null;
}

public function getApplicableTaxAttribute(): ?Tax
{
    // If product has assigned tax and it's active, use it
    if ($this->assigned_tax) {
        return $this->assigned_tax;
    }
    
    // If product doesn't have VAT, no tax applies
    if (!$this->has_vat) {
        return null;
    }
    
    // Try to get default tax
    $defaultTax = Tax::where('is_default', true)
        ->where('is_active', true)
        ->first();
    
    return $defaultTax ?? null;
}

    public function getInventoryValueAttribute(): float
    {
        return round($this->cost_price * $this->available_stock, 2);
    }

    public function getPotentialRevenueAttribute(): float
    {
        return round($this->final_price * $this->available_stock, 2);
    }

    public function getDaysOfInventoryAttribute(): float
    {
        $dailySales = $this->getAverageMonthlySales() / 30;
        
        if ($dailySales <= 0) {
            return $this->available_stock > 0 ? 999 : 0;
        }
        
        return round($this->available_stock / $dailySales, 1);
    }

    /**
     * Stock Management Methods
     */
    public function addStock(int $quantity, array $batchData = [], string $reason = 'purchase'): array
    {
        DB::beginTransaction();
        
        try {
            if ($this->track_batches && ($this->has_expiry || !empty($batchData))) {
                // Add to batch
                $batch = $this->addToBatch($quantity, $batchData);
                $result = [
                    'success' => true,
                    'batch_id' => $batch->id,
                    'batch_number' => $batch->batch_number,
                    'message' => "Added {$quantity} units to batch {$batch->batch_number}",
                    'method' => 'batch',
                ];
            } else {
                // Add to general stock
                $oldStock = $this->stock;
                $this->increment('stock', $quantity);
                
                // Log stock movement
                StockMovement::create([
                    'product_id' => $this->id,
                    'type' => 'in',
                    'qty_change' => $quantity,
                    'qty_before' => $oldStock,
                    'reason' => $reason,
                    'reference' => $batchData['reference'] ?? null,
                    'notes' => $batchData['notes'] ?? 'Stock addition',
                    'user_id' => auth()->id(),
                ]);
                
                $result = [
                    'success' => true,
                    'message' => "Added {$quantity} units to general stock",
                    'method' => 'general',
                ];
            }
            
            // Update stock status
            $this->updateStockStatus();
            
            DB::commit();
            return $result;
            
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    public function removeStock(int $quantity, array $options = []): array
    {
        DB::beginTransaction();
        
        try {
            $reason = $options['reason'] ?? 'sale';
            
            if ($this->track_batches && $this->activeBatches()->exists()) {
                // Remove from batches
                $result = $this->removeFromBatches($quantity, $options);
            } else {
                // Remove from general stock
                if ($this->available_stock < $quantity) {
                    throw new \Exception("Insufficient available stock. Available: {$this->available_stock}, Requested: {$quantity}");
                }
                
                $oldStock = $this->stock;
                $this->decrement('stock', $quantity);
                
                // Log stock movement
                StockMovement::create([
                    'product_id' => $this->id,
                    'type' => 'out',
                    'qty_change' => -$quantity,
                    'qty_before' => $oldStock,
                    'reason' => $reason,
                    'reference' => $options['reference'] ?? null,
                    'notes' => $options['notes'] ?? 'Stock reduction',
                    'user_id' => auth()->id(),
                ]);
                
                $result = [
                    'success' => true,
                    'message' => "Removed {$quantity} units from general stock",
                    'method' => 'general',
                ];
            }
            
            // Update stock status
            $this->updateStockStatus();
            
            DB::commit();
            return $result;
            
        } catch (\Exception $e) {
            DB::rollBack();
            throw $e;
        }
    }

    public function reserveStock(int $quantity): bool
    {
        if ($this->available_stock < $quantity) {
            return false;
        }
        
        $this->increment('reserved_stock', $quantity);
        return true;
    }

    public function releaseStock(int $quantity): bool
    {
        if ($this->reserved_stock < $quantity) {
            return false;
        }
        
        $this->decrement('reserved_stock', $quantity);
        return true;
    }

    public function commitStock(int $quantity): bool
    {
        if ($this->available_stock < $quantity) {
            return false;
        }
        
        $this->increment('committed_stock', $quantity);
        return true;
    }

    public function uncommitStock(int $quantity): bool
    {
        if ($this->committed_stock < $quantity) {
            return false;
        }
        
        $this->decrement('committed_stock', $quantity);
        return true;
    }

    /**
     * Batch Management Methods
     */
    private function addToBatch(int $quantity, array $data = []): ProductBatch
    {
        if ($this->has_expiry && empty($data['expiry_date'])) {
            throw new \Exception('Expiry date is required for expiry-tracked products.');
        }

        $batch = ProductBatch::create([
            'product_id' => $this->id,
            'supplier_id' => $data['supplier_id'] ?? $this->supplier_id,
            'purchase_id' => $data['purchase_id'] ?? null,
            'batch_number' => $data['batch_number'] ?? $this->generateBatchNumber(),
            'lot_number' => $data['lot_number'] ?? null,
            'serial_number' => $data['serial_number'] ?? null,
            'manufacture_date' => $data['manufacture_date'] ?? now(),
            'expiry_date' => $this->has_expiry ? ($data['expiry_date'] ?? null) : null,
            'initial_quantity' => $quantity,
            'current_quantity' => $quantity,
            'batch_cost_price' => $data['cost_price'] ?? $this->cost_price,
            'batch_sale_price' => $data['sale_price'] ?? $this->sale_price,
            'warehouse_location' => $data['location'] ?? null,
            'quality_status' => $data['quality_status'] ?? 'approved',
            'quality_notes' => $data['quality_notes'] ?? null,
            'notes' => $data['notes'] ?? null,
            'created_by' => auth()->id(),
        ]);

        // Log batch movement
        BatchStockMovement::create([
            'batch_id' => $batch->id,
            'product_id' => $this->id,
            'movement_type' => 'in',
            'quantity' => $quantity,
            'quantity_before' => 0,
            'reason' => $data['reason'] ?? 'purchase',
            'reference_number' => $data['reference_number'] ?? null,
            'notes' => $data['movement_notes'] ?? 'Initial stock',
            'user_id' => auth()->id(),
        ]);

        // Update product stock
        $this->increment('stock', $quantity);

        return $batch;
    }

    private function removeFromBatches(int $quantity, array $options = []): array
    {
        $method = $options['method'] ?? ($this->has_expiry ? 'fefo' : 'fifo');
        $remaining = $quantity;
        $allocations = [];
        
        // Get batches based on method
        $query = $this->activeBatches();
        
        if ($method === 'fefo' && $this->has_expiry) {
            $query->orderBy('expiry_date')->orderBy('created_at');
        } else {
            $query->orderBy('created_at');
        }
        
        $batches = $query->get();
        
        foreach ($batches as $batch) {
            if ($remaining <= 0) break;
            
            $available = $batch->current_quantity - $batch->reserved_quantity;
            $take = min($available, $remaining);
            
            if ($take > 0) {
                $batch->decrement('current_quantity', $take);
                
                // Log batch movement
                BatchStockMovement::create([
                    'batch_id' => $batch->id,
                    'product_id' => $this->id,
                    'movement_type' => 'out',
                    'quantity' => -$take,
                    'quantity_before' => $batch->current_quantity + $take,
                    'reason' => $options['reason'] ?? 'sale',
                    'reference_number' => $options['reference_number'] ?? null,
                    'notes' => $options['notes'] ?? 'Stock reduction',
                    'user_id' => auth()->id(),
                ]);
                
                $allocations[] = [
                    'batch_id' => $batch->id,
                    'batch_number' => $batch->batch_number,
                    'expiry_date' => $batch->expiry_date?->format('Y-m-d'),
                    'quantity' => $take,
                ];
                
                $remaining -= $take;
            }
        }
        
        if ($remaining > 0) {
            throw new \Exception("Insufficient available stock in batches. Need {$remaining} more units.");
        }
        
        // Update product stock
        $this->decrement('stock', $quantity);
        
        return [
            'success' => true,
            'allocations' => $allocations,
            'method' => $method,
            'message' => "Removed {$quantity} units from batches",
        ];
    }

    /**
     * Price History Methods
     */
    public function recordPriceHistory(): void
    {
        $changes = [];
        
        if ($this->wasChanged('sale_price')) {
            $changes[] = [
                'price_type' => 'sale_price',
                'old_price' => $this->getOriginal('sale_price'),
                'new_price' => $this->sale_price,
            ];
        }
        
        if ($this->wasChanged('cost_price')) {
            $changes[] = [
                'price_type' => 'cost_price',
                'old_price' => $this->getOriginal('cost_price'),
                'new_price' => $this->cost_price,
            ];
        }
        
        if ($this->wasChanged('wholesale_price')) {
            $changes[] = [
                'price_type' => 'wholesale_price',
                'old_price' => $this->getOriginal('wholesale_price'),
                'new_price' => $this->wholesale_price,
            ];
        }
        
        foreach ($changes as $change) {
            PriceHistory::create([
                'product_id' => $this->id,
                'user_id' => auth()->id(),
                'price_type' => $change['price_type'],
                'old_price' => $change['old_price'],
                'new_price' => $change['new_price'],
                'change_reason' => 'price_update',
                'notes' => 'Price updated in product management',
                'effective_from' => now(),
            ]);
        }
    }

    /**
     * Update stock status based on current stock
     */
    public function updateStockStatus(): void
    {
        if ($this->stock <= 0) {
            $this->stock_status = 'out_of_stock';
        } elseif ($this->stock <= $this->minimum_stock) {
            $this->stock_status = 'low_stock';
        } elseif ($this->stock <= $this->reorder_point) {
            $this->stock_status = 'reorder';
        } else {
            $this->stock_status = 'in_stock';
        }
        
        $this->saveQuietly();
    }

    /**
     * Save the model without triggering any events.
     */
    public function saveQuietly(array $options = []): bool
    {
        return static::withoutEvents(function () use ($options) {
            return $this->save($options);
        });
    }

    /**
     * Helper Methods
     */
    private function generateBatchNumber(): string
    {
        $date = now()->format('Ymd');
        $random = strtoupper(substr(md5(uniqid()), 0, 6));
        return "BATCH-{$date}-{$random}";
    }

    /**
     * Calculate tax amount for a given price
     */
    public function calculateTaxAmount(float $price): float
    {
        if (!$this->is_vatable) {
            return 0.00;
        }
        
        $rate = $this->tax_rate / 100;
        return round($price * $rate, 2);
    }

    /**
     * Get price excluding tax
     */
        public function getPriceExcludingTax(float $price): float
        {
            if (!$this->is_vatable || $this->tax_rate == 0) {
                return round($price, 2);
            }
            
            $rate = $this->tax_rate / 100;
            return round($price / (1 + $rate), 2);
        }

    /**
     * Get price including tax
     */
        public function getPriceIncludingTax(float $priceExcludingTax): float
        {
            if (!$this->is_vatable || $this->tax_rate == 0) {
                return round($priceExcludingTax, 2);
            }
            
            $rate = $this->tax_rate / 100;
            return round($priceExcludingTax * (1 + $rate), 2);
        }



        public function calculateTaxDetails(float $price = null, int $quantity = 1): array
{
    // Use provided price or final price
    $price = $price ?? $this->final_price;
    $subtotal = $price * $quantity;
    
    // Get tax rate
    $taxRate = $this->tax_rate;
    
    // Calculate tax amounts
    if ($this->is_vatable) {
        // For vatable items (has_vat = true AND tax_rate > 0)
        $taxAmount = round($subtotal * ($taxRate / 100), 2);
        $priceWithoutTax = round($subtotal, 2);
        $priceWithTax = round($subtotal + $taxAmount, 2);
    } else {
        // For non-vatable items (has_vat = false OR tax_rate = 0)
        $taxAmount = 0;
        $priceWithoutTax = round($subtotal, 2);
        $priceWithTax = round($subtotal, 2);
    }
    
    // Get tax info
    $tax = $this->applicable_tax;
    
    return [
        'unit_price' => round($price, 2),
        'quantity' => $quantity,
        'subtotal' => round($subtotal, 2),
        'has_vat' => $this->has_vat,
        'is_vatable' => $this->is_vatable,
        'tax_rate' => $taxRate,
        'tax_amount' => $taxAmount,
        'price_without_tax' => $priceWithoutTax,  // This is what the view expects
        'price_with_tax' => $priceWithTax,        // This is what the view expects
        'total' => $priceWithTax,
        'tax' => $tax ? [
            'id' => $tax->id,
            'name' => $tax->name,
            'rate' => (float) $tax->rate,
            'code' => $tax->code,
        ] : null,
    ];
}

/**
 * Get price excluding tax (clearer naming)
 */
public function getPriceWithoutTax(float $price): float
{
    return $this->getPriceExcludingTax($price);
}

/**
 * Get price including tax (clearer naming)
 */
public function getPriceWithTax(float $priceWithoutTax): float
{
    return $this->getPriceIncludingTax($priceWithoutTax);
}
    /**
     * Get total sold quantity in a period
     */
    public function getSoldQuantityInPeriod(Carbon $startDate, Carbon $endDate): int
    {
        return $this->saleItems()
            ->whereHas('sale', function ($query) use ($startDate, $endDate) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
            })
            ->sum('quantity');
    }

    /**
     * Get average monthly sales
     */
    public function getAverageMonthlySales(int $months = 6): float
    {
        $endDate = now();
        $startDate = $endDate->copy()->subMonths($months);
        
        $totalSold = $this->getSoldQuantityInPeriod($startDate, $endDate);
        
        return $months > 0 ? $totalSold / $months : $totalSold;
    }

    /**
     * Get total cost of goods sold (COGS) for a period
     */
    public function getCOGS(Carbon $startDate, Carbon $endDate): float
    {
        $quantitySold = $this->getSoldQuantityInPeriod($startDate, $endDate);
        return round($this->cost_price * $quantitySold, 2);
    }

    /**
     * Get total revenue for a period
     */
    public function getRevenueInPeriod(Carbon $startDate, Carbon $endDate): float
    {
        return $this->saleItems()
            ->whereHas('sale', function ($query) use ($startDate, $endDate) {
                $query->whereBetween('sale_date', [$startDate, $endDate])
                      ->where('status', 'completed');
            })
            ->sum(DB::raw('quantity * unit_price'));
    }

    /**
     * Get profit for a period
     */
    public function getProfitInPeriod(Carbon $startDate, Carbon $endDate): float
    {
        $revenue = $this->getRevenueInPeriod($startDate, $endDate);
        $cogs = $this->getCOGS($startDate, $endDate);
        
        return round($revenue - $cogs, 2);
    }

    /**
     * Check if product needs to be reordered
     */
    public function needsReorder(): bool
    {
        if (!$this->track_inventory) {
            return false;
        }
        
        return $this->available_stock <= $this->reorder_point;
    }

    /**
     * Get suggested reorder quantity
     */
    public function getSuggestedReorderQuantity(): int
    {
        if (!$this->needsReorder()) {
            return 0;
        }
        
        if ($this->maximum_stock) {
            $suggested = $this->maximum_stock - $this->available_stock;
        } else {
            $averageSales = $this->getAverageMonthlySales();
            $suggested = max(
                $this->minimum_stock * 3,
                ceil($averageSales * 1.5)
            );
        }
        
        return max($suggested, $this->minimum_stock);
    }

    /**
     * Validate if discount can be applied
     */
    public function validateDiscount(float $discountPrice = null, int $discountPercent = null): array
    {
        $errors = [];
        
        if ($discountPrice !== null) {
            if ($discountPrice > $this->sale_price) {
                $errors['discount_price'] = 'Discount price cannot be higher than sale price';
            }
            if ($discountPrice < $this->cost_price) {
                $errors['discount_price'] = 'Discount price cannot be lower than cost price';
            }
        }
        
        if ($discountPercent !== null) {
            if ($discountPercent < 0 || $discountPercent > 100) {
                $errors['discount_percent'] = 'Discount percentage must be between 0 and 100';
            }
            
            $discountedPrice = $this->sale_price * (1 - ($discountPercent / 100));
            if ($discountedPrice < $this->cost_price) {
                $errors['discount_percent'] = 'Discounted price would be below cost price';
            }
        }
        
        return $errors;
    }

    /**
     * Validate stock adjustment
     */
    public function validateStockAdjustment(int $quantity, string $direction = 'add'): array
    {
        $errors = [];
        
        if ($quantity <= 0) {
            $errors['quantity'] = 'Quantity must be greater than 0';
        }
        
        if ($direction === 'remove') {
            if ($quantity > $this->available_stock && $this->track_inventory && !$this->allow_backorders) {
                $errors['quantity'] = "Cannot remove {$quantity} units. Available stock: {$this->available_stock}";
            }
        }
        
        if ($this->maximum_stock && $direction === 'add') {
            $newStock = $this->stock + $quantity;
            if ($newStock > $this->maximum_stock) {
                $errors['quantity'] = "Cannot add {$quantity} units. Would exceed maximum stock of {$this->maximum_stock}";
            }
        }
        
        return $errors;
    }

    /**
     * Prepare product data for export
     */
    public function toExportArray(): array
    {
        return [
            'sku' => $this->sku,
            'barcode' => $this->barcode,
            'name' => $this->name,
            'category' => $this->category ? $this->category->name : null,
            'brand' => $this->brand ? $this->brand->name : null,
            'supplier' => $this->supplier ? $this->supplier->name : null,
            'unit' => $this->unit ? $this->unit->name : null,
            'stock' => $this->stock,
            'available_stock' => $this->available_stock,
            'cost_price' => $this->cost_price,
            'sale_price' => $this->sale_price,
            'final_price' => $this->final_price,
            'profit_margin' => $this->profit_margin,
            'profit_margin_percent' => $this->profit_margin_percent,
            'status' => $this->status,
            'stock_status' => $this->stock_status,
            'has_expiry' => $this->has_expiry ? 'Yes' : 'No',
            'track_batches' => $this->track_batches ? 'Yes' : 'No',
            'created_at' => $this->created_at ? $this->created_at->format('Y-m-d') : null,
            'last_sold_at' => $this->last_sold_at ? $this->last_sold_at->format('Y-m-d') : null,
        ];
    }

    /**
     * Get CSV headers for export
     */
    public static function getExportHeaders(): array
    {
        return [
            'SKU',
            'Barcode',
            'Product Name',
            'Category',
            'Brand',
            'Supplier',
            'Unit',
            'Current Stock',
            'Available Stock',
            'Cost Price',
            'Sale Price',
            'Final Price',
            'Profit Margin',
            'Profit Margin %',
            'Status',
            'Stock Status',
            'Has Expiry',
            'Track Batches',
            'Created Date',
            'Last Sold Date',
        ];
    }

    /**
     * Business Logic Methods
     */
    public function shouldReorder(): bool
    {
        return $this->track_inventory && $this->stock <= $this->reorder_point;
    }

    public function getReorderQuantity(): int
    {
        if ($this->maximum_stock) {
            return $this->maximum_stock - $this->stock;
        }
        
        return $this->minimum_stock * 2;
    }

    public function updateSaleStatistics(int $quantity, float $revenue): void
    {
        $this->increment('total_sold', $quantity);
        $this->increment('total_revenue', $revenue);
        $this->last_sold_at = now();
        $this->save();
    }

    public function incrementViewCount(): void
    {
        $this->increment('view_count');
    }

    public function incrementWishlistCount(): void
    {
        $this->increment('wishlist_count');
    }

    public function decrementWishlistCount(): void
    {
        $this->decrement('wishlist_count');
    }

    /**
     * Scopes
     */
    public function scopeActive($query)
    {
        return $query->where('status', 'active');
    }

    public function scopeWithExpiry($query)
    {
        return $query->where('has_expiry', true);
    }

    public function scopeWithoutExpiry($query)
    {
        return $query->where('has_expiry', false);
    }

    public function scopeWithBatches($query)
    {
        return $query->where('track_batches', true);
    }

    public function scopeWithoutBatches($query)
    {
        return $query->where('track_batches', false);
    }

    public function scopeLowStock($query)
    {
        return $query->whereColumn('stock', '<=', 'minimum_stock')
                    ->where('stock', '>', 0);
    }

    public function scopeOutOfStock($query)
    {
        return $query->where('stock', '<=', 0);
    }

    public function scopeInStock($query)
    {
        return $query->where('stock', '>', 0);
    }

    public function scopeFeatured($query)
    {
        return $query->where('is_featured', true);
    }

    public function scopeOnSale($query)
    {
        return $query->where('is_on_sale', true);
    }

    public function scopeNew($query)
    {
        return $query->where('is_new', true);
    }

    public function scopeBestseller($query)
    {
        return $query->where('is_bestseller', true);
    }

    public function scopeSearch($query, $search)
    {
        return $query->where(function ($q) use ($search) {
            $q->where('name', 'like', "%{$search}%")
              ->orWhere('sku', 'like', "%{$search}%")
              ->orWhere('barcode', 'like', "%{$search}%")
              ->orWhere('ref_number', 'like', "%{$search}%")
              ->orWhere('supplier_ref', 'like', "%{$search}%")
              ->orWhere('manufacturer_ref', 'like', "%{$search}%");
        });
    }

    public function scopeByCategory($query, $categoryId)
    {
        return $query->where('category_id', $categoryId);
    }

    public function scopeByBrand($query, $brandId)
    {
        return $query->where('brand_id', $brandId);
    }

    public function scopeBySupplier($query, $supplierId)
    {
        return $query->where('supplier_id', $supplierId);
    }

    public function scopeProducts($query)
    {
        return $query->where('is_service', false);
    }

    public function scopeServices($query)
    {
        return $query->where('is_service', true);
    }

    public function scopeDigitalProducts($query)
    {
        return $query->where('is_digital', true);
    }

    public function scopeInventoryProducts($query)
    {
        return $query->where('is_service', false)
                     ->where('is_digital', false)
                     ->where('track_inventory', true);
    }

    public function scopeCanAddToStock($query)
    {
        return $query->where('is_service', false)
                     ->where('track_inventory', true);
    }

    public function scopeNeedsReorder($query)
    {
        return $query->where('track_inventory', true)
            ->where(function ($q) {
                $q->whereColumn('available_stock', '<=', 'reorder_point')
                  ->orWhereColumn('available_stock', '<=', 'minimum_stock');
            })
            ->where('available_stock', '>', 0);
    }

    public function scopeExpiryAlert($query, string $type = 'expiring')
    {
        return $query->where('has_expiry', true)
            ->whereHas('batches', function ($q) use ($type) {
                if ($type === 'expired') {
                    $q->where('expiry_date', '<', now());
                } else {
                    $q->where('expiry_date', '>=', now())
                      ->where('expiry_date', '<=', now()->addDays(30));
                }
                $q->where('current_quantity', '>', 0);
            });
    }

    public function scopeDiscounted($query)
    {
        return $query->where(function ($q) {
            $q->where('discount_price', '>', 0)
              ->orWhere('discount_percent', '>', 0);
        });
    }

    public function scopeHighMargin($query, float $minMarginPercent = 30)
    {
        return $query->whereRaw('((sale_price - cost_price) / cost_price * 100) >= ?', [$minMarginPercent]);
    }

    /**
     * Validation Rules
     */
    public static function getValidationRules($productId = null): array
    {
        $skuRule = $productId 
            ? "required|string|max:100|unique:products,sku,{$productId}"
            : "required|string|max:100|unique:products,sku";

        return [
            'sku' => $skuRule,
            'name' => 'required|string|max:255',
            'barcode' => 'nullable|string|max:100|unique:products,barcode,' . ($productId ?: 'NULL'),
            'ref_number' => 'nullable|string|max:100|unique:products,ref_number,' . ($productId ?: 'NULL'),
            'category_id' => 'nullable|exists:categories,id',
            'brand_id' => 'nullable|exists:brands,id',
            'supplier_id' => 'nullable|exists:suppliers,id',
            'unit_id' => 'nullable|exists:units,id',
            'tax_id' => 'nullable|exists:taxes,id',
            'description' => 'nullable|string',
            'short_description' => 'nullable|string|max:500',
            'stock' => 'required|integer|min:0',
            'reorder_point' => 'required|integer|min:0',
            'minimum_stock' => 'required|integer|min:0',
            'maximum_stock' => 'nullable|integer|min:0|gte:minimum_stock',
            'cost_price' => 'required|numeric|min:0',
            'sale_price' => 'required|numeric|min:0|gte:cost_price',
            'wholesale_price' => 'nullable|numeric|min:0',
            'discount_price' => 'nullable|numeric|min:0|lte:sale_price',
            'discount_percent' => 'nullable|integer|min:0|max:100',
            'has_vat' => 'boolean',
            'has_expiry' => 'boolean',
            'track_batches' => 'boolean',
            'expiry_warning_days' => 'nullable|integer|min:1|max:365',
            'status' => 'required|in:active,inactive,discontinued',
            'is_featured' => 'boolean',
            'is_new' => 'boolean',
            'is_bestseller' => 'boolean',
            'is_on_sale' => 'boolean',
            'is_service' => 'boolean',
            'is_digital' => 'boolean',
            'track_inventory' => 'boolean',
        ];
    }

    /**
     * Custom validation messages
     */
    public static function getValidationMessages(): array
    {
        return [
            'sku.required' => 'SKU is required',
            'sku.unique' => 'This SKU already exists',
            'sku.max' => 'SKU cannot exceed 100 characters',
            'name.required' => 'Product name is required',
            'name.max' => 'Product name cannot exceed 255 characters',
            'barcode.unique' => 'This barcode already exists',
            'ref_number.unique' => 'This reference number already exists',
            'category_id.exists' => 'Selected category does not exist',
            'brand_id.exists' => 'Selected brand does not exist',
            'supplier_id.exists' => 'Selected supplier does not exist',
            'unit_id.exists' => 'Selected unit does not exist',
            'tax_id.exists' => 'Selected tax does not exist',
            'cost_price.required' => 'Cost price is required',
            'cost_price.min' => 'Cost price cannot be negative',
            'sale_price.required' => 'Sale price is required',
            'sale_price.min' => 'Sale price cannot be negative',
            'sale_price.gte' => 'Sale price must be greater than or equal to cost price',
            'stock.required' => 'Initial stock quantity is required',
            'stock.min' => 'Stock cannot be negative',
            'reorder_point.required' => 'Reorder point is required',
            'reorder_point.min' => 'Reorder point cannot be negative',
            'minimum_stock.required' => 'Minimum stock level is required',
            'minimum_stock.min' => 'Minimum stock cannot be negative',
            'maximum_stock.gte' => 'Maximum stock must be greater than or equal to minimum stock',
            'discount_price.lte' => 'Discount price cannot exceed sale price',
            'discount_percent.max' => 'Discount percentage cannot exceed 100%',
            'expiry_warning_days.required_if' => 'Expiry warning days is required when expiry tracking is enabled',
            'expiry_warning_days.min' => 'Expiry warning days must be at least 1',
            'expiry_warning_days.max' => 'Expiry warning days cannot exceed 365',
            'status.required' => 'Status is required',
            'status.in' => 'Invalid status value',
        ];
    }
}