<?php
// app/Http/Controllers/Admin/CommissionRatesController.php
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\CommissionRate;
use App\Models\Warehouse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Validation\Rule;

class CommissionRatesController extends Controller
{
    public function __construct()
    {
        $this->middleware('auth');
        $this->middleware('verified');
        $this->middleware('role:admin');
    }

    /**
     * Display a listing of commission rates
     */
    public function index(Request $request)
    {
        $query = CommissionRate::with('warehouse');

        // Filter by warehouse
        if ($request->filled('warehouse')) {
            $query->where('warehouse_code', $request->warehouse);
        }

        // Filter by vehicle type
        if ($request->filled('vehicle_type')) {
            $query->where('vehicle_type', $request->vehicle_type);
        }

        // Filter by status
        if ($request->filled('status')) {
            $isActive = $request->status === 'active';
            $query->where('is_active', $isActive);
        }

        $commissionRates = $query->orderBy('warehouse_code')
                                ->orderBy('vehicle_type')
                                ->orderBy('tier')
                                ->get();

        $warehouses = Warehouse::active()->orderBy('code')->get();

        // Statistics
        $allRates = CommissionRate::all();
        $stats = [
            'total' => $allRates->count(),
            'active' => $allRates->where('is_active', true)->count(),
            'inactive' => $allRates->where('is_active', false)->count(),
            'warehouses' => $allRates->groupBy('warehouse_code')->count(),
            'avg_tiers' => $allRates->groupBy(['warehouse_code', 'vehicle_type'])->map->count()->avg(),
        ];

        return view('admin.commission-rates.index', compact('commissionRates', 'warehouses', 'stats'));
    }

    /**
     * Show the form for creating a new commission rate
     */
    public function create(Request $request)
    {
        $warehouses = Warehouse::active()->orderBy('code')->get();
        $selectedWarehouse = $request->warehouse;
        
        return view('admin.commission-rates.create', compact('warehouses', 'selectedWarehouse'));
    }

    /**
     * Store a newly created commission rate
     */
    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
            'tier' => 'required|integer|min:1',
            'min_packages' => 'required|integer|min:0',
            'max_packages' => 'nullable|integer|gt:min_packages',
            'rate_per_package' => 'required|numeric|min:0',
            'is_active' => 'boolean',
        ], [
            'warehouse_code.required' => 'กรุณาเลือกคลังสินค้า',
            'warehouse_code.exists' => 'คลังสินค้าที่เลือกไม่ถูกต้อง',
            'vehicle_type.required' => 'กรุณาเลือกประเภทรถ',
            'tier.required' => 'กรุณาระบุขั้น',
            'tier.min' => 'ขั้นต้องมากกว่า 0',
            'min_packages.required' => 'กรุณาระบุจำนวนพัสดุขั้นต่ำ',
            'min_packages.min' => 'จำนวนพัสดุขั้นต่ำต้องมากกว่าหรือเท่ากับ 0',
            'max_packages.gt' => 'จำนวนพัสดุสูงสุดต้องมากกว่าจำนวนขั้นต่ำ',
            'rate_per_package.required' => 'กรุณาระบุอัตราค่าคอมมิชชั่นต่อชิ้น',
            'rate_per_package.min' => 'อัตราค่าคอมมิชชั่นต้องมากกว่าหรือเท่ากับ 0',
        ]);

        // Custom validation for tier uniqueness
        $existingTier = CommissionRate::where('warehouse_code', $request->warehouse_code)
                                     ->where('vehicle_type', $request->vehicle_type)
                                     ->where('tier', $request->tier)
                                     ->exists();

        if ($existingTier) {
            $validator->errors()->add('tier', 'ขั้นนี้มีอยู่แล้วสำหรับคลังและประเภทรถนี้');
        }

        // Custom validation for package range overlap
        $overlapCheck = $this->checkRangeOverlap(
            $request->warehouse_code,
            $request->vehicle_type,
            $request->min_packages,
            $request->max_packages
        );

        if ($overlapCheck['hasOverlap']) {
            $validator->errors()->add('min_packages', $overlapCheck['message']);
        }

        if ($validator->fails()) {
            return back()->withErrors($validator)->withInput();
        }

        try {
            CommissionRate::create([
                'warehouse_code' => $request->warehouse_code,
                'vehicle_type' => $request->vehicle_type,
                'tier' => $request->tier,
                'min_packages' => $request->min_packages,
                'max_packages' => $request->max_packages,
                'rate_per_package' => $request->rate_per_package,
                'is_active' => $request->boolean('is_active', true),
            ]);

            return redirect()
                ->route('admin.commission-rates.index', ['warehouse' => $request->warehouse_code])
                ->with('success', 'เพิ่มอัตราค่าคอมมิชชั่นเรียบร้อยแล้ว');

        } catch (\Exception $e) {
            return back()
                ->withErrors(['error' => 'เกิดข้อผิดพลาดในการสร้างอัตรา: ' . $e->getMessage()])
                ->withInput();
        }
    }

    /**
     * Show the form for editing the specified commission rate
     */
    public function edit(CommissionRate $commissionRate)
    {
        $warehouses = Warehouse::active()->orderBy('code')->get();
        return view('admin.commission-rates.edit', compact('commissionRate', 'warehouses'));
    }

    /**
     * Update the specified commission rate
     */
    public function update(Request $request, CommissionRate $commissionRate)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
            'tier' => 'required|integer|min:1',
            'min_packages' => 'required|integer|min:0',
            'max_packages' => 'nullable|integer|gt:min_packages',
            'rate_per_package' => 'required|numeric|min:0',
            'is_active' => 'boolean',
        ], [
            'warehouse_code.required' => 'กรุณาเลือกคลังสินค้า',
            'warehouse_code.exists' => 'คลังสินค้าที่เลือกไม่ถูกต้อง',
            'vehicle_type.required' => 'กรุณาเลือกประเภทรถ',
            'tier.required' => 'กรุณาระบุขั้น',
            'tier.min' => 'ขั้นต้องมากกว่า 0',
            'min_packages.required' => 'กรุณาระบุจำนวนพัสดุขั้นต่ำ',
            'min_packages.min' => 'จำนวนพัสดุขั้นต่ำต้องมากกว่าหรือเท่ากับ 0',
            'max_packages.gt' => 'จำนวนพัสดุสูงสุดต้องมากกว่าจำนวนขั้นต่ำ',
            'rate_per_package.required' => 'กรุณาระบุอัตราค่าคอมมิชชั่นต่อชิ้น',
            'rate_per_package.min' => 'อัตราค่าคอมมิชชั่นต้องมากกว่าหรือเท่ากับ 0',
        ]);

        // Custom validation for tier uniqueness (excluding current record)
        $existingTier = CommissionRate::where('warehouse_code', $request->warehouse_code)
                                     ->where('vehicle_type', $request->vehicle_type)
                                     ->where('tier', $request->tier)
                                     ->where('id', '!=', $commissionRate->id)
                                     ->exists();

        if ($existingTier) {
            $validator->errors()->add('tier', 'ขั้นนี้มีอยู่แล้วสำหรับคลังและประเภทรถนี้');
        }

        // Custom validation for package range overlap (excluding current record)
        $overlapCheck = $this->checkRangeOverlap(
            $request->warehouse_code,
            $request->vehicle_type,
            $request->min_packages,
            $request->max_packages,
            $commissionRate->id
        );

        if ($overlapCheck['hasOverlap']) {
            $validator->errors()->add('min_packages', $overlapCheck['message']);
        }

        if ($validator->fails()) {
            return back()->withErrors($validator)->withInput();
        }

        try {
            $commissionRate->update([
                'warehouse_code' => $request->warehouse_code,
                'vehicle_type' => $request->vehicle_type,
                'tier' => $request->tier,
                'min_packages' => $request->min_packages,
                'max_packages' => $request->max_packages,
                'rate_per_package' => $request->rate_per_package,
                'is_active' => $request->boolean('is_active'),
            ]);

            return redirect()
                ->route('admin.commission-rates.index', ['warehouse' => $request->warehouse_code])
                ->with('success', 'อัปเดตอัตราค่าคอมมิชชั่นเรียบร้อยแล้ว');

        } catch (\Exception $e) {
            return back()
                ->withErrors(['error' => 'เกิดข้อผิดพลาดในการอัปเดต: ' . $e->getMessage()])
                ->withInput();
        }
    }

    /**
     * Remove the specified commission rate
     */
    public function destroy(CommissionRate $commissionRate)
    {
        try {
            $commissionRate->delete();

            return response()->json([
                'success' => true,
                'message' => 'ลบอัตราค่าคอมมิชชั่นเรียบร้อยแล้ว'
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาดในการลบ: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Toggle commission rate status
     */
    public function toggle(CommissionRate $commissionRate)
    {
        try {
            $commissionRate->update([
                'is_active' => !$commissionRate->is_active
            ]);

            $status = $commissionRate->is_active ? 'เปิดใช้งาน' : 'ปิดใช้งาน';

            return response()->json([
                'success' => true,
                'message' => "เปลี่ยนสถานะอัตราเป็น {$status} เรียบร้อยแล้ว"
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาดในการเปลี่ยนสถานะ: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Calculate commission for given parameters
     */
    public function calculate(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
            'package_count' => 'required|integer|min:1',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ข้อมูลไม่ถูกต้อง'
            ]);
        }

        try {
            $rates = CommissionRate::where('warehouse_code', $request->warehouse_code)
                                  ->where('vehicle_type', $request->vehicle_type)
                                  ->where('is_active', true)
                                  ->orderBy('tier')
                                  ->get();

            if ($rates->isEmpty()) {
                return response()->json([
                    'success' => false,
                    'message' => 'ไม่พบอัตราค่าคอมมิชชั่นสำหรับคลังและประเภทรถนี้'
                ]);
            }

            $commission = $this->calculateCommissionAmount($request->package_count, $rates);

            return response()->json([
                'success' => true,
                'commission' => $commission,
                'package_count' => $request->package_count,
                'average_per_package' => $commission / $request->package_count,
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาดในการคำนวณ: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Bulk operations for commission rates
     */
    public function bulk(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'action' => 'required|in:activate,deactivate,delete',
            'warehouse' => 'nullable|exists:warehouses,code',
            'vehicle_type' => 'nullable|in:2wheel,4wheel',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ข้อมูลไม่ถูกต้อง'
            ]);
        }

        try {
            $query = CommissionRate::query();

            // Apply filters
            if ($request->filled('warehouse')) {
                $query->where('warehouse_code', $request->warehouse);
            }

            if ($request->filled('vehicle_type')) {
                $query->where('vehicle_type', $request->vehicle_type);
            }

            $count = 0;

            switch ($request->action) {
                case 'activate':
                    $count = $query->update(['is_active' => true]);
                    break;

                case 'deactivate':
                    $count = $query->update(['is_active' => false]);
                    break;

                case 'delete':
                    $count = $query->delete();
                    break;
            }

            $actionText = [
                'activate' => 'เปิดใช้งาน',
                'deactivate' => 'ปิดใช้งาน',
                'delete' => 'ลบ'
            ];

            return response()->json([
                'success' => true,
                'message' => "{$actionText[$request->action]} {$count} รายการเรียบร้อยแล้ว",
                'affected' => $count
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาด: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Import commission rates from CSV
     */
    public function import(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'file' => 'required|file|mimes:csv,txt|max:2048',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ไฟล์ไม่ถูกต้อง'
            ]);
        }

        try {
            $file = $request->file('file');
            $csvData = array_map('str_getcsv', file($file->path()));
            
            // Remove header row
            $header = array_shift($csvData);
            
            $imported = 0;
            $errors = [];

            DB::beginTransaction();

            foreach ($csvData as $index => $row) {
                $rowNumber = $index + 2; // +2 because we removed header and arrays are 0-indexed

                if (count($row) < 6) {
                    $errors[] = "แถวที่ {$rowNumber}: ข้อมูลไม่ครบ";
                    continue;
                }

                $data = [
                    'warehouse_code' => trim($row[0]),
                    'vehicle_type' => trim($row[1]),
                    'tier' => (int)trim($row[2]),
                    'min_packages' => (int)trim($row[3]),
                    'max_packages' => !empty(trim($row[4])) ? (int)trim($row[4]) : null,
                    'rate_per_package' => (float)trim($row[5]),
                ];

                // Validate row data
                $rowValidator = Validator::make($data, [
                    'warehouse_code' => 'required|exists:warehouses,code',
                    'vehicle_type' => 'required|in:2wheel,4wheel',
                    'tier' => 'required|integer|min:1',
                    'min_packages' => 'required|integer|min:0',
                    'max_packages' => 'nullable|integer|gt:min_packages',
                    'rate_per_package' => 'required|numeric|min:0',
                ]);

                if ($rowValidator->fails()) {
                    $errors[] = "แถวที่ {$rowNumber}: " . implode(', ', $rowValidator->errors()->all());
                    continue;
                }

                // Check for existing tier
                $existing = CommissionRate::where('warehouse_code', $data['warehouse_code'])
                                         ->where('vehicle_type', $data['vehicle_type'])
                                         ->where('tier', $data['tier'])
                                         ->first();

                if ($existing) {
                    // Update existing
                    $existing->update($data + ['is_active' => true]);
                } else {
                    // Create new
                    CommissionRate::create($data + ['is_active' => true]);
                }

                $imported++;
            }

            if (!empty($errors) && $imported === 0) {
                DB::rollBack();
                return response()->json([
                    'success' => false,
                    'message' => 'ไม่สามารถนำเข้าข้อมูลได้: ' . implode(', ', array_slice($errors, 0, 3))
                ]);
            }

            DB::commit();

            $message = "นำเข้าข้อมูล {$imported} รายการเรียบร้อยแล้ว";
            if (!empty($errors)) {
                $message .= " (มีข้อผิดพลาด " . count($errors) . " แถว)";
            }

            return response()->json([
                'success' => true,
                'message' => $message,
                'imported' => $imported,
                'errors' => $errors
            ]);

        } catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาดในการนำเข้า: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Export commission rates to CSV
     */
    public function export(Request $request)
    {
        try {
            $query = CommissionRate::with('warehouse');

            // Apply filters
            if ($request->filled('warehouse')) {
                $query->where('warehouse_code', $request->warehouse);
            }

            if ($request->filled('vehicle_type')) {
                $query->where('vehicle_type', $request->vehicle_type);
            }

            if ($request->filled('status')) {
                $isActive = $request->status === 'active';
                $query->where('is_active', $isActive);
            }

            $rates = $query->orderBy('warehouse_code')
                          ->orderBy('vehicle_type')
                          ->orderBy('tier')
                          ->get();

            $filename = 'commission_rates_' . date('Y-m-d_H-i-s') . '.csv';
            
            // Check if it's a template request
            if ($request->get('format') === 'template') {
                $filename = 'commission_rates_template.csv';
            }
            
            $headers = [
                'Content-Type' => 'text/csv',
                'Content-Disposition' => "attachment; filename=\"{$filename}\"",
            ];

            $callback = function() use ($rates, $request) {
                $file = fopen('php://output', 'w');
                
                // UTF-8 BOM for proper Thai character display in Excel
                fwrite($file, "\xEF\xBB\xBF");
                
                // Headers
                fputcsv($file, [
                    'warehouse_code',
                    'vehicle_type',
                    'tier',
                    'min_packages',
                    'max_packages',
                    'rate_per_package',
                    'is_active',
                    'warehouse_name',
                    'created_at',
                    'updated_at'
                ]);

                // Template data
                if ($request->get('format') === 'template') {
                    fputcsv($file, ['WH001', '2wheel', '1', '1', '49', '0', '1', '', '', '']);
                    fputcsv($file, ['WH001', '2wheel', '2', '50', '99', '8', '1', '', '', '']);
                    fputcsv($file, ['WH001', '2wheel', '3', '100', '', '10', '1', '', '', '']);
                    fputcsv($file, ['WH001', '4wheel', '1', '1', '49', '0', '1', '', '', '']);
                    fputcsv($file, ['WH001', '4wheel', '2', '50', '99', '12', '1', '', '', '']);
                    fputcsv($file, ['WH001', '4wheel', '3', '100', '', '15', '1', '', '', '']);
                } else {
                    // Actual data
                    foreach ($rates as $rate) {
                        fputcsv($file, [
                            $rate->warehouse_code,
                            $rate->vehicle_type,
                            $rate->tier,
                            $rate->min_packages,
                            $rate->max_packages ?? '',
                            $rate->rate_per_package,
                            $rate->is_active ? '1' : '0',
                            $rate->warehouse ? $rate->warehouse->name : '',
                            $rate->created_at ? $rate->created_at->format('Y-m-d H:i:s') : '',
                            $rate->updated_at ? $rate->updated_at->format('Y-m-d H:i:s') : '',
                        ]);
                    }
                }

                fclose($file);
            };

            return response()->stream($callback, 200, $headers);

        } catch (\Exception $e) {
            return back()->with('error', 'เกิดข้อผิดพลาดในการส่งออกข้อมูล: ' . $e->getMessage());
        }
    }

    /**
     * Get existing rates for AJAX requests (for overlap checking)
     */
    public function getExistingRates(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ข้อมูลไม่ถูกต้อง'
            ]);
        }

        try {
            $rates = CommissionRate::where('warehouse_code', $request->warehouse_code)
                                  ->where('vehicle_type', $request->vehicle_type)
                                  ->orderBy('tier')
                                  ->get();

            return response()->json([
                'success' => true,
                'rates' => $rates->map(function ($rate) {
                    return [
                        'id' => $rate->id,
                        'tier' => $rate->tier,
                        'min_packages' => $rate->min_packages,
                        'max_packages' => $rate->max_packages,
                        'rate_per_package' => $rate->rate_per_package,
                        'is_active' => $rate->is_active,
                    ];
                })
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาด: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Check for range overlap (AJAX endpoint)
     */
    public function checkOverlap(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
            'min_packages' => 'required|integer|min:0',
            'max_packages' => 'nullable|integer|gt:min_packages',
            'exclude_id' => 'nullable|integer|exists:commission_rates,id',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ข้อมูลไม่ถูกต้อง'
            ]);
        }

        try {
            $overlapCheck = $this->checkRangeOverlap(
                $request->warehouse_code,
                $request->vehicle_type,
                $request->min_packages,
                $request->max_packages,
                $request->exclude_id
            );

            return response()->json([
                'success' => true,
                'hasOverlap' => $overlapCheck['hasOverlap'],
                'message' => $overlapCheck['message']
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาด: ' . $e->getMessage()
            ]);
        }
    }

    /**
     * Private method to check for range overlap
     */
    private function checkRangeOverlap($warehouseCode, $vehicleType, $minPackages, $maxPackages = null, $excludeId = null)
    {
        $query = CommissionRate::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;

            // If no max is set, treat as infinite
            if ($maxPackages === null) {
                $maxPackages = PHP_INT_MAX;
            }
            if ($existingMax === null) {
                $existingMax = PHP_INT_MAX;
            }

            // Check for overlap
            if ($minPackages <= $existingMax && $maxPackages >= $existingMin) {
                return [
                    'hasOverlap' => true,
                    'message' => "ช่วงจำนวนพัสดุทับซ้อนกับขั้นที่ {$rate->tier} ({$rate->min_packages}" . 
                                ($rate->max_packages ? "-{$rate->max_packages}" : '+') . " ชิ้น)"
                ];
            }
        }

        return [
            'hasOverlap' => false,
            'message' => ''
        ];
    }

    /**
     * Private method to calculate commission amount
     */
    private function calculateCommissionAmount($packageCount, $rates)
    {
        $totalCommission = 0;
        $remainingPackages = $packageCount;

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

            $minPackages = $rate->min_packages;
            $maxPackages = $rate->max_packages ?? PHP_INT_MAX;
            $ratePerPackage = $rate->rate_per_package;

            // Skip if package count is below this tier's minimum
            if ($packageCount < $minPackages) {
                continue;
            }

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

            if ($packagesInTier > 0) {
                $totalCommission += $packagesInTier * $ratePerPackage;
                $remainingPackages -= $packagesInTier;
            }
        }

        return round($totalCommission, 2);
    }

    /**
     * Get commission calculation breakdown (for detailed display)
     */
    public function getCalculationBreakdown(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'warehouse_code' => 'required|exists:warehouses,code',
            'vehicle_type' => 'required|in:2wheel,4wheel',
            'package_count' => 'required|integer|min:1',
        ]);

        if ($validator->fails()) {
            return response()->json([
                'success' => false,
                'message' => 'ข้อมูลไม่ถูกต้อง'
            ]);
        }

        try {
            $rates = CommissionRate::where('warehouse_code', $request->warehouse_code)
                                  ->where('vehicle_type', $request->vehicle_type)
                                  ->where('is_active', true)
                                  ->orderBy('tier')
                                  ->get();

            if ($rates->isEmpty()) {
                return response()->json([
                    'success' => false,
                    'message' => 'ไม่พบอัตราค่าคอมมิชชั่นสำหรับคลังและประเภทรถนี้'
                ]);
            }

            $packageCount = $request->package_count;
            $totalCommission = 0;
            $breakdown = [];
            $remainingPackages = $packageCount;

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

                $minPackages = $rate->min_packages;
                $maxPackages = $rate->max_packages ?? PHP_INT_MAX;
                $ratePerPackage = $rate->rate_per_package;

                if ($packageCount < $minPackages) {
                    continue;
                }

                $packagesInTier = min(
                    $remainingPackages,
                    $maxPackages - $minPackages + 1,
                    $packageCount - $minPackages + 1
                );

                if ($packagesInTier > 0) {
                    $tierCommission = $packagesInTier * $ratePerPackage;
                    $totalCommission += $tierCommission;

                    $breakdown[] = [
                        'tier' => $rate->tier,
                        'range' => $maxPackages === PHP_INT_MAX ? "{$minPackages}+" : "{$minPackages}-{$maxPackages}",
                        'packages_in_tier' => $packagesInTier,
                        'rate_per_package' => $ratePerPackage,
                        'tier_commission' => $tierCommission,
                    ];

                    $remainingPackages -= $packagesInTier;
                }
            }

            return response()->json([
                'success' => true,
                'total_commission' => round($totalCommission, 2),
                'package_count' => $packageCount,
                'breakdown' => $breakdown,
                'average_per_package' => $packageCount > 0 ? round($totalCommission / $packageCount, 2) : 0,
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'เกิดข้อผิดพลาดในการคำนวณ: ' . $e->getMessage()
            ]);
        }
    }
}