<?php
// app/Models/CommissionRate.php

namespace App\Models;

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

class CommissionRate extends Model
{
    use HasFactory;

    protected $primaryKey = 'commission_id';

    protected $fillable = [
        'commission_name',
        'commission_type',
        'commission_value',
        'min_amount',
        'max_amount',
        'applicable_to',
        'category_id', // ✅ NEW FIELD
        'is_active',
        'effective_from',
        'effective_to',
        'created_by'
    ];

    protected $casts = [
        'commission_value' => 'decimal:2',
        'min_amount' => 'decimal:2',
        'max_amount' => 'decimal:2',
        'is_active' => 'boolean',
        'effective_from' => 'date',
        'effective_to' => 'date',
        'created_at' => 'datetime',
        'updated_at' => 'datetime'
    ];

    /**
     * Get the user who created this commission rate.
     */
    public function createdBy()
    {
        return $this->belongsTo(User::class, 'created_by', 'user_id');
    }

    /**
     * ✅ NEW: Get the occupation category for this commission rate
     */
    public function occupationCategory()
    {
        return $this->belongsTo(OccupationCategory::class, 'category_id', 'category_id');
    }

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

    /**
     * Scope currently effective commission rates
     */
    public function scopeCurrentlyEffective($query)
    {
        return $query->where('effective_from', '<=', now())
                    ->where(function($q) {
                        $q->whereNull('effective_to')
                          ->orWhere('effective_to', '>=', now());
                    });
    }

    /**
     * Scope by applicable category
     */
    public function scopeByApplicableTo($query, $applicableTo)
    {
        return $query->where('applicable_to', $applicableTo)
                    ->orWhere('applicable_to', 'all');
    }

    /**
     * ✅ NEW: Scope by occupation category
     */
    public function scopeByOccupationCategory($query, $categoryId)
    {
        return $query->where('category_id', $categoryId);
    }

    /**
     * ✅ UPDATED: Get commission rate for specific category - PERFECT VERSION
     */
    public static function getRateFor($categoryType, $categoryId = null)
    {
        return self::active()
            ->currentlyEffective()
            ->where(function($query) use ($categoryType, $categoryId) {
                // ✅ FIRST PRIORITY: Direct category ID match
                if ($categoryId) {
                    $query->where('category_id', $categoryId);
                }
                // ✅ SECOND PRIORITY: applicable_to match
                $query->orWhere('applicable_to', $categoryType)
                      // ✅ THIRD PRIORITY: applicable_to = 'all'
                      ->orWhere('applicable_to', 'all');
            })
            // ✅ PRIORITY ORDER: Direct category > applicable_to > all
            ->orderByRaw("
                CASE
                    WHEN category_id IS NOT NULL THEN 1
                    WHEN applicable_to = ? THEN 2
                    ELSE 3
                END
            ", [$categoryType])
            ->orderBy('commission_value', 'asc')
            ->first();
    }

    /**
     * ✅ NEW: Get commission rate by occupation category ID
     */
    public static function getRateByOccupationCategory($categoryId)
    {
        return self::active()
            ->currentlyEffective()
            ->where(function($query) use ($categoryId) {
                $query->where('category_id', $categoryId)
                      ->orWhere('applicable_to', 'all');
            })
            ->orderByRaw("CASE WHEN category_id = ? THEN 1 ELSE 2 END", [$categoryId])
            ->orderBy('commission_value', 'asc')
            ->first();
    }

    /**
     * Check if commission rate is currently effective
     */
    public function isCurrentlyEffective()
    {
        $now = now();
        return $this->effective_from <= $now &&
               ($this->effective_to === null || $this->effective_to >= $now);
    }

    /**
     * Calculate commission amount for a given total
     */
    public function calculateCommission($totalAmount)
    {
        if ($this->commission_type === 'percentage') {
            $commission = $totalAmount * ($this->commission_value / 100);
        } else {
            $commission = $this->commission_value;
        }

        // Apply min/max limits
        if ($this->min_amount > 0 && $commission < $this->min_amount) {
            $commission = $this->min_amount;
        }

        if ($this->max_amount > 0 && $commission > $this->max_amount) {
            $commission = $this->max_amount;
        }

        return round($commission, 2);
    }
}
?>
