<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Effective-dated grade history (promotions, increments, conversions,
 * demotions). Each row is the grade an employee holds from `effective_from`;
 * the row with the latest effective_from on or before a date is in force then.
 * Payroll resolves each run's grade from this timeline.
 */
return new class extends Migration
{
    public function up(): void
    {
        Schema::create('employee_gradings', function (Blueprint $table) {
            $table->id();
            $table->foreignId('employee_id')->constrained('employees')->cascadeOnDelete();
            $table->date('effective_from');

            $table->string('salary_structure', 32)->nullable();
            $table->string('grade_level', 16)->nullable();
            $table->unsignedSmallInteger('salary_step')->nullable();

            // INITIAL | PROMOTION | INCREMENT | CONVERSION | DEMOTION | CORRECTION
            $table->string('type', 16)->default('PROMOTION');
            $table->string('reference')->nullable(); // e.g. promotion letter no.
            $table->text('reason')->nullable();

            $table->foreignId('created_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['employee_id', 'effective_from']);
        });

        // Backfill one INITIAL grading per employee from their current grade,
        // effective from their join date (or a baseline) so payroll has a full
        // timeline for existing staff.
        $now = now();
        DB::table('employees')->whereNull('deleted_at')->orderBy('id')
            ->chunkById(500, function ($employees) use ($now) {
                $rows = [];
                foreach ($employees as $e) {
                    $rows[] = [
                        'employee_id' => $e->id,
                        'effective_from' => $e->date_of_join ?: '2025-01-01',
                        'salary_structure' => $e->salary_structure,
                        'grade_level' => $e->grade_level,
                        'salary_step' => $e->salary_step,
                        'type' => 'INITIAL',
                        'created_at' => $now,
                        'updated_at' => $now,
                    ];
                }
                if ($rows !== []) {
                    DB::table('employee_gradings')->insert($rows);
                }
            });
    }

    public function down(): void
    {
        Schema::dropIfExists('employee_gradings');
    }
};
