<?php

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

return new class extends Migration {
    public function up(): void
    {
        if (!Schema::hasTable('telegram_bots')) {
            Schema::create('telegram_bots', function (Blueprint $table) {
                $table->id();
                $table->string('bot_username', 64)->unique();
                $table->string('bot_name', 255);
                $table->text('bot_token');
                $table->timestamps();
            });
        }

        if (!Schema::hasColumn('telegram_channels', 'bot_id')) {
            Schema::table('telegram_channels', function (Blueprint $table) {
                $table->foreignId('bot_id')->nullable()->constrained('telegram_bots')->restrictOnDelete();
            });
        }

        DB::transaction(function () {
            DB::table('telegram_channels')->whereNull('bot_id')->whereNotNull('bot_token')
                ->orderByDesc('updated_at')->orderByDesc('id')->get()->each(function ($channel) {
                    $username = strtolower($channel->bot_username);
                    $botId = DB::table('telegram_bots')->where('bot_username', $username)->value('id')
                        ?? DB::table('telegram_bots')->insertGetId([
                            'bot_username' => $username,
                            'bot_name' => $channel->bot_name ?? $username,
                            'bot_token' => $channel->bot_token,
                            'created_at' => now(),
                            'updated_at' => now(),
                        ]);
                    DB::table('telegram_channels')->where('id', $channel->id)->update([
                        'bot_id' => $botId,
                        'bot_username' => null,
                        'bot_name' => null,
                        'bot_token' => null,
                    ]);
                });
        });
    }

    public function down(): void
    {
        DB::transaction(function () {
            foreach (DB::table('telegram_bots')->get() as $bot) {
                DB::table('telegram_channels')->where('bot_id', $bot->id)->update([
                    'bot_username' => $bot->bot_username,
                    'bot_name' => $bot->bot_name,
                    'bot_token' => $bot->bot_token,
                ]);
            }
        });
        Schema::table('telegram_channels', function (Blueprint $table) {
            $table->dropConstrainedForeignId('bot_id');
        });
        Schema::dropIfExists('telegram_bots');
    }
};