<?php
// app/Models/CommissionRate.php - Fixed and Enhanced
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;

class CommissionRate extends Model
{
    use HasFactory;

    protected $fillable = [
        'warehouse_code',
        'vehicle_type',
        'tier',
        'min_packages',
        'max_packages',
        'rate_per_package',
        'is_active',
    ];

    protected $casts = [
        'tier' => 'integer',
        'min_packages' => 'integer',
        'max_packages' => 'integer',
        'rate_per_package' => 'decimal:2',
        'is_active' => 'boolean',
    ];

    protected $appends = [
        'range_display',
        'vehicle_display_name'
    ];

    /**
     * Relationship with Warehouse
     */
    public function warehouse()
    {
        return $this->belongsTo(Warehouse::class, 'warehouse_code', 'code');
    }

    /**
     * Scope for active rates
     */
    public function scopeActive(Builder $query): Builder
    {
        return $query->where('is_active', true);
    }

    /**
     * Scope for specific warehouse and vehicle type
     */
    public function scopeForWarehouseAndVehicle(Builder $query, string $warehouseCode, string $vehicleType): Builder
    {
        return $query->where('warehouse_code', $warehouseCode)
                    ->where('vehicle_type', $vehicleType)
                    ->active()
                    ->orderBy('tier');
    }

    /**
     * Scope for specific warehouse
     */
    public function scopeForWarehouse(Builder $query, string $warehouseCode): Builder
    {
        return $query->where('warehouse_code', $warehouseCode);
    }

    /**
     * Scope for specific vehicle type
     */
    public function scopeForVehicleType(Builder $query, string $vehicleType): Builder
    {
        return $query->where('vehicle_type', $vehicleType);
    }

    /**
     * Get display name for range
     */
    public function getRangeDisplayAttribute(): string
    {
        if ($this->max_packages) {
            return number_format($this->min_packages) . ' - ' . number_format($this->max_packages) . ' ชิ้น';
        }
        return number_format($this->min_packages) . '+ ชิ้น';
    }

    /**
     * Get display name for vehicle type
     */
    public function getVehicleDisplayNameAttribute(): string
    {
        return match($this->vehicle_type) {
            '2wheel' => 'รถ 2 ล้อ',
            '4wheel' => 'รถ 4 ล้อ',
            default => $this->vehicle_type
        };
    }

    /**
     * Get formatted rate per package
     */
    public function getFormattedRateAttribute(): string
    {
        return number_format($this->rate_per_package, 2) . ' บาท/ชิ้น';
    }

    /**
     * Calculate commission for given package count
     */
    public static function calculateCommission(string $warehouseCode, string $vehicleType, int $packageCount): array
    {
        $rates = self::forWarehouseAndVehicle(null, $warehouseCode, $vehicleType)->get();
        
        if ($rates->isEmpty()) {
            return [
                'total_commission' => 0,
                'breakdown' => [],
                'error' => 'ไม่พบอัตราค่าคอมมิชชั่นสำหรับคลังและประเภทรถนี้'
            ];
        }

        $totalCommission = 0;
        $remainingPackages = $packageCount;
        $breakdown = [];

        foreach ($rates as $rate) {
            if ($remainingPackages <= 0) {
                break;
            }

            $tierMin = $rate->min_packages;
            $tierMax = $rate->max_packages ?? PHP_INT_MAX;
            
            // Skip if package count is below this tier's minimum
            if ($packageCount < $tierMin) {
                continue;
            }

            // Calculate packages in this tier
            $packagesInTier = min(
                $remainingPackages,
                $tierMax - $tierMin + 1,
                $packageCount - $tierMin + 1
            );

            if ($packagesInTier > 0) {
                $tierCommission = $packagesInTier * $rate->rate_per_package;
                $totalCommission += $tierCommission;

                $breakdown[] = [
                    'tier' => $rate->tier,
                    'range' => $rate->range_display,
                    'packages_in_tier' => $packagesInTier,
                    'rate_per_package' => $rate->rate_per_package,
                    'tier_commission' => $tierCommission,
                    'formatted_rate' => $rate->formatted_rate,
                    'formatted_commission' => number_format($tierCommission, 2) . ' บาท'
                ];

                $remainingPackages -= $packagesInTier;
            }
        }

        return [
            'total_commission' => round($totalCommission, 2),
            'formatted_total' => number_format($totalCommission, 2) . ' บาท',
            'breakdown' => $breakdown,
            'package_count' => $packageCount,
            'average_per_package' => $packageCount > 0 ? round($totalCommission / $packageCount, 2) : 0,
            'formatted_average' => $packageCount > 0 ? number_format($totalCommission / $packageCount, 2) . ' บาท/ชิ้น' : '0 บาท/ชิ้น'
        ];
    }

    /**
     * Check if package range overlaps with existing rates
     */
    public static function checkRangeOverlap(string $warehouseCode, string $vehicleType, int $minPackages, ?int $maxPackages = null, ?int $excludeId = null): array
    {
        $query = self::where('warehouse_code', $warehouseCode)
                    ->where('vehicle_type', $vehicleType);

        if ($excludeId) {
            $query->where('id', '!=', $excludeId);
        }

        $existingRates = $query->get();

        foreach ($existingRates as $rate) {
            $existingMin = $rate->min_packages;
            $existingMax = $rate->max_packages ?? PHP_INT_MAX;

            // If no max is set, treat as infinite
            $checkMax = $maxPackages ?? PHP_INT_MAX;

            // Check for overlap
            if ($minPackages <= $existingMax && $checkMax >= $existingMin) {
                return [
                    'has_overlap' => true,
                    'message' => "ช่วงจำนวนพัสดุทับซ้อนกับขั้นที่ {$rate->tier} ({$rate->range_display})",
                    'conflicting_tier' => $rate->tier
                ];
            }
        }

        return [
            'has_overlap' => false,
            'message' => 'ช่วงจำนวนพัสดุไม่ทับซ้อน'
        ];
    }

    /**
     * Get next available tier number
     */
    public static function getNextTierNumber(string $warehouseCode, string $vehicleType): int
    {
        $maxTier = self::where('warehouse_code', $warehouseCode)
                     ->where('vehicle_type', $vehicleType)
                     ->max('tier');

        return $maxTier ? $maxTier + 1 : 1;
    }

    /**
     * Validate tier data
     */
    public static function validateTierData(array $data): array
    {
        $errors = [];

        // Check required fields
        if (empty($data['warehouse_code'])) {
            $errors['warehouse_code'] = 'กรุณาเลือกคลังสินค้า';
        }

        if (empty($data['vehicle_type'])) {
            $errors['vehicle_type'] = 'กรุณาเลือกประเภทรถ';
        }

        if (!isset($data['tier']) || $data['tier'] < 1) {
            $errors['tier'] = 'ขั้นต้องมากกว่า 0';
        }

        if (!isset($data['min_packages']) || $data['min_packages'] < 0) {
            $errors['min_packages'] = 'จำนวนพัสดุขั้นต่ำต้องมากกว่าหรือเท่ากับ 0';
        }

        if (isset($data['max_packages']) && $data['max_packages'] <= $data['min_packages']) {
            $errors['max_packages'] = 'จำนวนพัสดุสูงสุดต้องมากกว่าจำนวนขั้นต่ำ';
        }

        if (!isset($data['rate_per_package']) || $data['rate_per_package'] < 0) {
            $errors['rate_per_package'] = 'อัตราค่าคอมมิชชั่นต้องมากกว่าหรือเท่ากับ 0';
        }

        return $errors;
    }

    /**
     * Get statistics for warehouse commission rates
     */
    public static function getWarehouseStats(string $warehouseCode): array
    {
        $rates = self::where('warehouse_code', $warehouseCode)->get();

        return [
            'total_rates' => $rates->count(),
            'active_rates' => $rates->where('is_active', true)->count(),
            'inactive_rates' => $rates->where('is_active', false)->count(),
            'vehicle_types' => $rates->pluck('vehicle_type')->unique()->count(),
            'avg_rate_2wheel' => $rates->where('vehicle_type', '2wheel')->avg('rate_per_package') ?? 0,
            'avg_rate_4wheel' => $rates->where('vehicle_type', '4wheel')->avg('rate_per_package') ?? 0,
            'max_tier_2wheel' => $rates->where('vehicle_type', '2wheel')->max('tier') ?? 0,
            'max_tier_4wheel' => $rates->where('vehicle_type', '4wheel')->max('tier') ?? 0,
        ];
    }

    /**
     * Export rates to array format
     */
    public function toExportArray(): array
    {
        return [
            'warehouse_code' => $this->warehouse_code,
            'warehouse_name' => $this->warehouse?->name ?? '',
            'vehicle_type' => $this->vehicle_type,
            'vehicle_display' => $this->vehicle_display_name,
            'tier' => $this->tier,
            'min_packages' => $this->min_packages,
            'max_packages' => $this->max_packages ?? '',
            'range_display' => $this->range_display,
            'rate_per_package' => $this->rate_per_package,
            'formatted_rate' => $this->formatted_rate,
            'is_active' => $this->is_active ? 'ใช้งาน' : 'ปิดใช้งาน',
            'created_at' => $this->created_at?->format('d/m/Y H:i:s') ?? '',
            'updated_at' => $this->updated_at?->format('d/m/Y H:i:s') ?? '',
        ];
    }
}