<?php

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

class FixFkCascadesOnNstPrepareProducts extends Migration
{
    public function up()
    {
        // Drop foreign keys first (without using change() which requires Doctrine)
        Schema::table('nst_prepare_products', function (Blueprint $table) {
            try {
                $table->dropForeign('nst_prepare_products_from_location_id_foreign');
            } catch (\Exception $e) {}
            
            try {
                $table->dropForeign('nst_prepare_products_to_location_id_foreign');
            } catch (\Exception $e) {}
            
            try {
                $table->dropForeign('nst_prepare_products_nst_shipping_id_foreign');
            } catch (\Exception $e) {}
        });

        // Alter the columns directly using raw SQL to avoid Doctrine dependency
        DB::statement('ALTER TABLE nst_prepare_products MODIFY COLUMN from_location_id INT UNSIGNED NULL');
        DB::statement('ALTER TABLE nst_prepare_products MODIFY COLUMN to_location_id INT UNSIGNED NULL');

        // Re-add foreign keys with SET NULL on delete
        Schema::table('nst_prepare_products', function (Blueprint $table) {
            $table->foreign('from_location_id')
                  ->references('id')->on('business_locations')
                  ->onDelete('set null');

            $table->foreign('to_location_id')
                  ->references('id')->on('business_locations')
                  ->onDelete('set null');

            $table->foreign('nst_shipping_id')
                  ->references('id')->on('nst_shipping')
                  ->onDelete('set null');
        });
    }

    public function down()
    {
        // Drop the new foreign keys
        Schema::table('nst_prepare_products', function (Blueprint $table) {
            try {
                $table->dropForeign(['from_location_id']);
            } catch (\Exception $e) {}
            
            try {
                $table->dropForeign(['to_location_id']);
            } catch (\Exception $e) {}
            
            try {
                $table->dropForeign(['nst_shipping_id']);
            } catch (\Exception $e) {}
        });

        // Restore original NOT NULL constraint on location columns using raw SQL
        DB::statement('ALTER TABLE nst_prepare_products MODIFY COLUMN from_location_id INT UNSIGNED NOT NULL');
        DB::statement('ALTER TABLE nst_prepare_products MODIFY COLUMN to_location_id INT UNSIGNED NOT NULL');

        // Restore original FKs (no onDelete)
        Schema::table('nst_prepare_products', function (Blueprint $table) {
            $table->foreign('from_location_id')->references('id')->on('business_locations');
            $table->foreign('to_location_id')->references('id')->on('business_locations');
            $table->foreign('nst_shipping_id')->references('id')->on('nst_shipping');
        });
    }
}

