<?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 AddHapusAnsuranPropertiesToMohonidTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */

    protected $db_connection;

    public function getActiveDB()
    {
        $connections = array_keys(config("database.connections"));
        $excluded = config('database.excluded_connections', ['mysql7']);
        
        $active = [];
        
        foreach ($connections as $connection) {
            if (in_array($connection, $excluded)) {
                Log::debug("Skipping excluded connection: {$connection}");
                continue;
            }
            
            try {
                DB::connection($connection)->select('SELECT 1');
                $active[] = $connection;
            } catch (\Exception $e) {
                continue;
            }
        }
        
        return $active;
    }

    public function up()
    {
        $databases = $this->getActiveDB();
        Log::info("Active databases: " . implode(', ', $databases));
        foreach ($databases as $database) {
            Log::info("Migrating database: {$database}");
            $this->safeAddProperties($database, 'mohonid');
        }
    }

    private function safeAddProperties($database, $table_name)
    {
        // Skip if table doesn't exist
        if (!Schema::connection($database)->hasTable($table_name)) {
            Log::warning("Table {$table_name} does not exist in database {$database}. Skipping migration.");
            return;
        }

        try {
            // Get existing columns once
            $existing_columns = Schema::connection($database)->getColumnListing($table_name);
        
            $columns_to_add = [
                'tagoperasi' => 'boolean',
                'ansuran' => 'decimal',
                'masa' => 'time',
                'operasi' => 'string', 
                'timestamp_approved_by_pc' => 'timestamp',
                'timestamp_approved_by_operasi' => 'timestamp',
            ];

            // Find missing columns (this was missing!)
            $missing_columns = array_diff(array_keys($columns_to_add), $existing_columns);

            // Add all missing columns in one schema call
            if (!empty($missing_columns)) {
                Schema::connection($database)->table($table_name, function (Blueprint $table) use ($existing_columns) {
                    // Add ansuran column - decimal with default 0
                    if( !in_array('tagoperasi', $existing_columns)) {
                        $table->boolean('tagoperasi')->default(0)->comment('Tag Operasi');
                    }

                    if (!in_array('ansuran', $existing_columns)) {
                        $table->decimal('ansuran', 10, 2)->default(0.00)->after('pinjaman')->comment('Ansuran Amount');
                    }
                    
                    // Add masa column - TIME type
                    if (!in_array('masa', $existing_columns)) {
                        $table->time('masa')->nullable()->after('tarikh')->comment('Time period');
                    }
                    
                    // Add operasi column - string  
                    if (!in_array('operasi', $existing_columns)) {
                        $table->string('operasi')->nullable()->comment('Nama Operasi');
                    }
                    
                    // Add approval timestamp columns (changed from boolean to timestamp)
                    if (!in_array('timestamp_approved_by_pc', $existing_columns)) {
                        $table->timestamp('timestamp_approved_by_pc')->nullable()->after('id')->comment('Time Approved by PC');
                    }
                    
                    if (!in_array('timestamp_approved_by_operasi', $existing_columns)) {
                        $table->timestamp('timestamp_approved_by_operasi')->nullable()->comment('Time approved by Operasi');
                    }
                });
            }
            
        } catch (\Exception $e) {
            // Don't fail entire migration for one database
            Log::error("Migration failed for database {$database}: " . $e->getMessage());
        }
    }

    public function down()
    {
        $databases = $this->getActiveDB();
    
        foreach ($databases as $database) {
            if (Schema::connection($database)->hasTable('mohonid')) {
                try {
                    Schema::connection($database)->table('mohonid', function (Blueprint $table) use ($database) {
                        // Check each column before dropping
                        if (Schema::connection($database)->hasColumn('mohonid', 'ansuran')) {
                            $table->dropColumn('ansuran');
                        }
                        if (Schema::connection($database)->hasColumn('mohonid', 'masa')) {
                            $table->dropColumn('masa');
                        }
                        if (Schema::connection($database)->hasColumn('mohonid', 'operasi')) {
                            $table->dropColumn('operasi');
                        }
                        if (Schema::connection($database)->hasColumn('mohonid', 'is_approved_by_pc')) {
                            $table->dropColumn('is_approved_by_pc');
                        }
                        if (Schema::connection($database)->hasColumn('mohonid', 'is_approved_by_operasi')) {
                            $table->dropColumn('is_approved_by_operasi');
                        }
                    });
                } catch (\Exception $e) {
                    \Log::error("Rollback failed for database {$database}: " . $e->getMessage());
                }
            }
        }


    }
}
