<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // First, let's see what division names we have in incoming_operations
        $divisionMappings = [
            // Add mappings for common division names found in your database
            'BMC ABU DHABI' => 1,
            'IMKK' => 2,
            'CRES ABU DHABI' => 5,
            'SOGEC' => 345,
            'E3D' => 492,
            // Add more mappings as needed based on actual data
        ];

        // Update incoming_operations where division contains text names
        foreach ($divisionMappings as $divisionName => $divisionId) {
            DB::table('incoming_operations')
                ->where('division', $divisionName)
                ->update(['division' => $divisionId]);
        }

        // For exact matches, also try partial matches in case there are variations
        $projectDivisions = DB::table('project_divisions')->get(['id', 'division_name', 'division_code']);

        foreach ($projectDivisions as $division) {
            // Update exact matches
            DB::table('incoming_operations')
                ->where('division', $division->division_name)
                ->update(['division' => $division->id]);

            // Update code matches
            DB::table('incoming_operations')
                ->where('division', $division->division_code)
                ->update(['division' => $division->id]);
        }

        // Log what we couldn't match
        $unmatchedDivisions = DB::table('incoming_operations')
            ->whereNotNull('division')
            ->where('division', 'not regexp', '^[0-9]+$') // Not numeric
            ->distinct()
            ->pluck('division');

        if ($unmatchedDivisions->isNotEmpty()) {
            \Log::warning('Unmatched division names in incoming_operations:', $unmatchedDivisions->toArray());
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Convert division IDs back to names (if needed for rollback)
        $projectDivisions = DB::table('project_divisions')->get(['id', 'division_name']);

        foreach ($projectDivisions as $division) {
            DB::table('incoming_operations')
                ->where('division', $division->id)
                ->update(['division' => $division->division_name]);
        }
    }
};