<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('courseware_categories', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->unsignedInteger('sort_order')->default(0);
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });

        $now = now();
        $categories = [
            'MANAGEMENT',
            'PROCTORS',
            'HEALTH AND SAFETY',
            'CIVIL AND ARCHITECTURE',
            'COMPUTER AND IT',
            'FASHION AND DESIGN',
            'ENGINEERING AND TECHNOLOGY',
            'Isotope testing',
            'FOOD & BEVERAGE',
        ];

        foreach ($categories as $index => $name) {
            DB::table('courseware_categories')->insert([
                'name' => $name,
                'slug' => Str::slug($name),
                'sort_order' => $index + 1,
                'is_active' => true,
                'created_at' => $now,
                'updated_at' => $now,
            ]);
        }

        Schema::table('courseware_partners', function (Blueprint $table) {
            $table->foreignId('courseware_category_id')->nullable()->after('user_id')->constrained()->nullOnDelete();
        });

        if (Schema::hasColumn('courseware_partners', 'categories')) {
            $slugMap = [
                'management' => 'management',
                'proctors' => 'proctors',
                'health_and_safety' => 'health-and-safety',
                'civil_and_architecture' => 'civil-and-architecture',
                'computer_and_it' => 'computer-and-it',
                'fashion_and_design' => 'fashion-and-design',
                'engineering_and_technology' => 'engineering-and-technology',
                'isotope_testing' => 'isotope-testing',
                'food_and_beverage' => 'food-beverage',
            ];

            $categoryIds = DB::table('courseware_categories')->pluck('id', 'slug');

            foreach (DB::table('courseware_partners')->orderBy('id')->get() as $partner) {
                $stored = json_decode($partner->categories ?? '[]', true);
                $first = is_array($stored) ? collect($stored)->filter()->first() : $stored;

                if (! $first) {
                    continue;
                }

                $slug = $slugMap[$first] ?? Str::slug((string) $first);
                $categoryId = $categoryIds[$slug] ?? null;

                if ($categoryId) {
                    DB::table('courseware_partners')->where('id', $partner->id)->update([
                        'courseware_category_id' => $categoryId,
                    ]);
                }
            }

            Schema::table('courseware_partners', function (Blueprint $table) {
                $table->dropColumn('categories');
            });
        }
    }

    public function down(): void
    {
        Schema::table('courseware_partners', function (Blueprint $table) {
            $table->dropConstrainedForeignId('courseware_category_id');
            $table->json('categories')->nullable();
        });

        Schema::dropIfExists('courseware_categories');
    }
};
