<?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 {
    public function up(): void
    {
        // 1) company_id কলাম ensure (nullable + unsignedBigInteger)
        Schema::table('users', function (Blueprint $table) {
            if (!Schema::hasColumn('users', 'company_id')) {
                $table->unsignedBigInteger('company_id')->nullable()->after('id');
            } else {
                // doctrine/dbal না থাকলেও try/catch এ safe
                try {
                    $table->unsignedBigInteger('company_id')->nullable()->change();
                } catch (\Throwable $e) {}
            }
        });

        // 2) companies টেবিল থাকলে ডাটা ক্লিনআপ + FK add
        if (Schema::hasTable('companies')) {
            // ⚠️ invalid company_id -> NULL
            // (0, negative, or not present in companies.id)
            try {
                DB::statement("
                    UPDATE users u
                    LEFT JOIN companies c ON u.company_id = c.id
                    SET u.company_id = NULL
                    WHERE u.company_id IS NOT NULL
                      AND (u.company_id <= 0 OR c.id IS NULL)
                ");
            } catch (\Throwable $e) {
                // কিছু হোস্টে LEFT JOIN UPDATE ব্লক থাকলে builder fallback:
                try {
                    DB::table('users')
                        ->whereNotNull('company_id')
                        ->where(function($q){
                            $q->where('company_id','<=',0)
                              ->orWhereNotIn('company_id', DB::table('companies')->select('id'));
                        })
                        ->update(['company_id' => null]);
                } catch (\Throwable $e2) {}
            }

            // 3) FK add (nullable => ON DELETE SET NULL)
            Schema::table('users', function (Blueprint $table) {
                try {
                    $table->foreign('company_id', 'users_company_id_foreign')
                          ->references('id')->on('companies')
                          ->nullOnDelete()
                          ->cascadeOnUpdate();
                } catch (\Throwable $e) {
                    // already exists / name differs — ignore
                }
            });
        }
    }

    public function down(): void
    {
        Schema::table('users', function (Blueprint $table) {
            try { $table->dropForeign('users_company_id_foreign'); } catch (\Throwable $e) {}
            try { $table->dropForeign(['company_id']); } catch (\Throwable $e) {}
            // চাইলে কলামও ড্রপ করতে পারেন:
            // if (Schema::hasColumn('users','company_id')) $table->dropColumn('company_id');
        });
    }
};



