<?php

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

class AddNmJbgUntungPembiayaanColumnsToPalsuTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */

    private function getActiveDB() : array
    {
        $config_db = array_keys(config("database.connections"));

        $db_connection = array_filter($config_db, function($connection) {
            try {
                if ($connection == 'mysql7') {
                    return false;
                }

                DB::connection($connection)->select('SELECT 1');
                return true;
            } catch (\Exception $e) {
                return false;
            }
        });
        
        return $db_connection ? array_values($db_connection) : [];

    }

    public function up()
    {
        $db_connection = $this->getActiveDB();

        foreach ($db_connection as $dbase) {
            Schema::connection($dbase)->table('palsu', function (Blueprint $table) use ($dbase) {
                
                if (!Schema::connection($dbase)->hasColumn('palsu', 'pembiayaan')) {
                    $table->decimal('pembiayaan', 11, 2)->nullable()->comment('Pembiayaan amount');
                }
                
                if (!Schema::connection($dbase)->hasColumn('palsu', 'nilaimarhun')) {
                    $table->decimal('nilaimarhun', 11, 2)->nullable()->comment('Nilai marhun amount');
                }
                
                if (!Schema::connection($dbase)->hasColumn('palsu', 'jbg')) {
                    $table->float('jbg', 4, 2)->nullable()->comment('JBG value');
                }
                
                if (!Schema::connection($dbase)->hasColumn('palsu', 'untung')) {
                    $table->decimal('untung', 11, 2)->nullable()->comment('Untung amount');
                }

                if (!Schema::connection($dbase)->hasColumn('palsu', 'untungonepercent')) {
                    $table->decimal('untungonepercent', 11, 2)->nullable()->comment('Satu Persen amount');
                }
                
                if (!Schema::connection($dbase)->hasColumn('palsu', 'untungcajlain')) {
                    $table->decimal('untungcajlain', 11, 2)->nullable()->comment('Caj Lain-Lain amount');
                }

                // Add index and foreign key constraint to existing norujukan column
                if (Schema::connection($dbase)->hasColumn('palsu', 'norujukan')) {
                    // Add index to norujukan column for better performance
                    // $table->index('norujukan');
                    
                    // Add foreign key constraint named 'norujukan'
                    // $table->foreign('norujukan', 'norujukan')->references('norujukan')->on('gadai');
                }

            });
        }
    }

    public function down()
    {
        $db_connection = $this->getActiveDB();

        foreach ($db_connection as $dbase) {
            Schema::connection($dbase)->table('palsu', function (Blueprint $table) use ($dbase) {
                
                // Drop foreign key constraint and index from norujukan column
                // if (Schema::connection($dbase)->hasColumn('palsu', 'norujukan')) {
                //     try {
                //         $table->dropForeign('norujukan');
                //         echo "Dropped foreign key 'norujukan' on connection '{$dbase}'\n";
                //     } catch (\Exception $e) {
                //         echo "Warning: Could not drop foreign key 'norujukan' on connection '{$dbase}': " . $e->getMessage() . "\n";
                //     }
                    
                //     try {
                //         $table->dropIndex(['norujukan']);
                //         echo "Dropped index on 'norujukan' column on connection '{$dbase}'\n";
                //     } catch (\Exception $e) {
                //         echo "Warning: Could not drop index on 'norujukan' on connection '{$dbase}': " . $e->getMessage() . "\n";
                //     }
                // }
                
                // Drop other columns if they exist
                $columnsToCheck = ['nilaimarhun', 'jbg', 'untung','untungonepercent','untungcajlain', 'pembiayaan'];
                
                foreach ($columnsToCheck as $column) {
                    if (Schema::connection($dbase)->hasColumn('palsu', $column)) {
                        try {
                            $table->dropColumn($column);
                            echo "Dropped column '{$column}' on connection '{$dbase}'\n";
                        } catch (\Exception $e) {
                            echo "Warning: Could not drop column '{$column}' on connection '{$dbase}': " . $e->getMessage() . "\n";
                        }
                    }
                }
            });
        }
    }
}