<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * Creates the leadership_assignments table to track which users are
     * assigned to which leadership positions with start and end dates.
     *
     * @return void
     */
    public function up(): void
    {
        Schema::create('leadership_assignments', function (Blueprint $table) {
            // Primary key
            $table->id();

            // Foreign key to leadership positions
            $table->foreignId('leadership_position_id')
                ->constrained('leadership_positions')
                ->onDelete('cascade');

            // Foreign key to users
            $table->foreignId('user_id')
                ->constrained('users')
                ->onDelete('cascade');

            // Assignment dates
            $table->date('start_date');
            $table->date('end_date')->nullable();

            // Current assignment flag
            $table->boolean('is_current')->default(true);

            // Timestamps and soft deletes
            $table->timestamps();
            $table->softDeletes();

            // Indexes for performance
            $table->index('leadership_position_id');
            $table->index('user_id');
            $table->index('is_current');
            $table->index(['leadership_position_id', 'is_current']);

            // Note: We intentionally do NOT add a unique constraint on
            // (leadership_position_id, is_current) because MySQL does not support
            // partial indexes. The uniqueness of current assignments is enforced
            // at the application level in LeadershipPositionController::storeAssignment()
            // which ends any existing current assignment before creating a new one.
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down(): void
    {
        Schema::dropIfExists('leadership_assignments');
    }
};
