<?php

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

class UpdateNonSempornaRecordsJenisToHapuskira extends Migration
{
    /**
     * Get active database connections
     */
    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) : [];
    }

    /**
     * Semporna norujukan records to exclude
     */
    private function getSempornaRecords(): array
    {
        return [
            '011237240808-0003',
            '011237240719-0015',
            '011237240806-0011',
            '011237240828-0001',
            '011237240904-0007',
            '011237240904-0008',
            '011237240904-0009',
            '011237240719-0023',
            '011237240719-0024',
            '011237240719-0025',
            '011237240618-0026',
            '011237240719-0027',
            '011237240719-0028',
            '011237240618-0020',
            '011237240719-0030',
            '011237240618-0029',
            '011237240731-0026',
            '011237240912-0009'
        ];
    }

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        $db_connection = $this->getActiveDB();
        $semporna_records = $this->getSempornaRecords();

        foreach ($db_connection as $dbase) {
            try {
                if ($dbase == 'mysql34') {
                    // For Semporna (mysql34), update only non-Semporna records to 'hapuskira'
                    $updated = DB::connection($dbase)->table('palsu')
                        ->whereNotIn('norujukan', $semporna_records)
                        ->update(['jenis' => 'hapuskira']);
                        
                    if ($updated > 0) {
                        echo "Updated {$updated} non-Semporna records in {$dbase} to jenis = 'hapuskira'\n";
                    }
                } else {
                    // For all other databases, update all records to 'hapuskira'
                    $updated = DB::connection($dbase)->table('palsu')
                        ->update(['jenis' => 'hapuskira']);
                        
                    if ($updated > 0) {
                        echo "Updated {$updated} records in {$dbase} to jenis = 'hapuskira'\n";
                    }
                }
            } catch (\Exception $e) {
                echo "Error updating {$dbase}: " . $e->getMessage() . "\n";
            }
        }
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        $db_connection = $this->getActiveDB();

        foreach ($db_connection as $dbase) {
            try {
                // Revert all jenis values to null except Semporna records which should remain 'pampasan'
                if ($dbase == 'mysql34') {
                    $semporna_records = $this->getSempornaRecords();
                    $reverted = DB::connection($dbase)->table('palsu')
                        ->whereNotIn('norujukan', $semporna_records)
                        ->update(['jenis' => null]);
                        
                    if ($reverted > 0) {
                        echo "Reverted {$reverted} non-Semporna records in {$dbase} jenis to null\n";
                    }
                } else {
                    $reverted = DB::connection($dbase)->table('palsu')
                        ->update(['jenis' => null]);
                        
                    if ($reverted > 0) {
                        echo "Reverted {$reverted} records in {$dbase} jenis to null\n";
                    }
                }
            } catch (\Exception $e) {
                echo "Error reverting {$dbase}: " . $e->getMessage() . "\n";
            }
        }
    }
}
