<?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('appreciations')) {
            return;
        }

        Schema::table('appreciations', function (Blueprint $table) {
            if (! Schema::hasColumn('appreciations', 'title')) {
                $table->string('title')->nullable()->after('user_id');
            }
            if (! Schema::hasColumn('appreciations', 'appreciation_date')) {
                $table->date('appreciation_date')->nullable()->after('description');
            }
            if (! Schema::hasColumn('appreciations', 'given_by')) {
                $table->unsignedBigInteger('given_by')->nullable()->after('appreciation_date');
            }
        });

        $driver = Schema::getConnection()->getDriverName();
        if ($driver === 'mysql') {
            if (Schema::hasColumn('appreciations', 'date')) {
                DB::statement(
                    'UPDATE `appreciations` SET `appreciation_date` = DATE(`date`) WHERE `appreciation_date` IS NULL AND `date` IS NOT NULL'
                );
            }
            if (Schema::hasColumn('appreciations', 'created_by')) {
                DB::statement(
                    'UPDATE `appreciations` SET `given_by` = `created_by` WHERE `given_by` IS NULL AND `created_by` IS NOT NULL'
                );
            }
            if (Schema::hasColumn('appreciations', 'award_id')) {
                DB::statement(
                    'UPDATE `appreciations` SET `title` = CONCAT(\'Award #\', `award_id`) WHERE (`title` IS NULL OR `title` = \'\') AND `award_id` IS NOT NULL'
                );
            }
        }
    }

    public function down(): void
    {
        if (! Schema::hasTable('appreciations')) {
            return;
        }

        Schema::table('appreciations', function (Blueprint $table) {
            if (Schema::hasColumn('appreciations', 'given_by')) {
                $table->dropColumn('given_by');
            }
            if (Schema::hasColumn('appreciations', 'appreciation_date')) {
                $table->dropColumn('appreciation_date');
            }
            if (Schema::hasColumn('appreciations', 'title')) {
                $table->dropColumn('title');
            }
        });
    }
};