<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

class HiringTrend extends Model
{
    protected $table = 'employees';
    public $timestamps = false;

    public static function getTrend($year, $companyId = null, $dealershipId = null)
    {
        $query = DB::table('employees')
            ->select(
                DB::raw('MONTH(date_hired) as month'),
                DB::raw('COUNT(*) as hires')
            )
            ->whereYear('date_hired', $year)
            ->when($companyId, fn($q) => $q->where('company_id', $companyId))
            ->when($dealershipId, fn($q) => $q->where('dealership_id', $dealershipId))
            ->groupBy('month');

        $hires = $query->pluck('hires', 'month')->toArray();

        $resignations = DB::table('employees')
            ->select(
                DB::raw('MONTH(date_resign) as month'),
                DB::raw('COUNT(*) as resigns')
            )
            ->whereYear('date_resign', $year)
            ->when($companyId, fn($q) => $q->where('company_id', $companyId))
            ->when($dealershipId, fn($q) => $q->where('dealership_id', $dealershipId))
            ->groupBy('month')
            ->pluck('resigns', 'month')
            ->toArray();

        $months = range(1, 12);
        $trend = [];

        foreach ($months as $m) {
            $trend[] = [
                'month' => date('F', mktime(0, 0, 0, $m, 1)),
                'hires' => $hires[$m] ?? 0,
                'resigns' => $resignations[$m] ?? 0,
            ];
        }

        return $trend;
    }

    /**
     * Get current month's new hires and compare with previous month
     */
    public static function getCurrentMonthStats($companyId = null, $dealershipId = null)
    {
        $currentMonth = Carbon::now()->month;
        $previousMonth = $currentMonth - 1;
        $year = Carbon::now()->year;

        // Current month hires
        $currentHires = DB::table('employees')
            ->whereYear('date_hired', $year)
            ->whereMonth('date_hired', $currentMonth)
            ->when($companyId, fn($q) => $q->where('company_id', $companyId))
            ->when($dealershipId, fn($q) => $q->where('dealership_id', $dealershipId))
            ->count();

        // Previous month hires
        $previousHires = 0;
        if ($previousMonth > 0) {
            $previousHires = DB::table('employees')
                ->whereYear('date_hired', $year)
                ->whereMonth('date_hired', $previousMonth)
                ->when($companyId, fn($q) => $q->where('company_id', $companyId))
                ->when($dealershipId, fn($q) => $q->where('dealership_id', $dealershipId))
                ->count();
        } else {
            // Handle January (compare with previous year's December)
            $previousHires = DB::table('employees')
                ->whereYear('date_hired', $year - 1)
                ->whereMonth('date_hired', 12)
                ->when($companyId, fn($q) => $q->where('company_id', $companyId))
                ->when($dealershipId, fn($q) => $q->where('dealership_id', $dealershipId))
                ->count();
        }

        // Calculate percentage difference
        if ($previousHires > 0) {
            $percentageChange = (($currentHires - $previousHires) / $previousHires) * 100;
        } else {
            $percentageChange = $currentHires > 0 ? 100 : 0;
        }

        return [
            'month' => date('F'),
            'current_hires' => $currentHires,
            'previous_hires' => $previousHires,
            'percentage_change' => round($percentageChange, 2),
            'status' => $percentageChange > 0
                ? 'increase'
                : ($percentageChange < 0 ? 'decrease' : 'no change'),
        ];
    }
}
