<?php

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

return new class extends Migration {
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // 1. Get the IDs of the roles
        $typoRole = DB::table('roles')->where('key', 'ops_chashier')->first();
        $correctRole = DB::table('roles')->where('key', 'ops_cashier')->first();

        // Also check for 'ops_chasier' just in case
        $altTypoRole = DB::table('roles')->where('key', 'ops_chasier')->first();

        $this->mergeRoles($typoRole, $correctRole, 'ops_cashier', 'Ops Cashier');

        if ($altTypoRole) {
            // Refresh correct role in case it was just created/updated by previous step
            $correctRole = DB::table('roles')->where('key', 'ops_cashier')->first();
            $this->mergeRoles($altTypoRole, $correctRole, 'ops_cashier', 'Ops Cashier');
        }
    }

    private function mergeRoles($sourceRole, $targetRole, $targetKey, $targetName)
    {
        if (!$sourceRole) {
            return; // Source doesn't exist, nothing to do
        }

        if (!$targetRole) {
            // Target doesn't exist, so we can just rename source
            DB::table('roles')->where('id', $sourceRole->id)->update([
                'key' => $targetKey,
                'name' => $targetName
            ]);
            return;
        }

        // Both exist. We must merge source into target.
        // Reassign users from source to target
        $sourceUsers = DB::table('user_roles')->where('role_id', $sourceRole->id)->get();

        foreach ($sourceUsers as $ur) {
            // Check if user already has the target role
            $exists = DB::table('user_roles')
                ->where('user_id', $ur->user_id)
                ->where('role_id', $targetRole->id)
                ->exists();

            if (!$exists) {
                // Reassign to target
                DB::table('user_roles')->where('id', $ur->id)->update(['role_id' => $targetRole->id]);
            } else {
                // User already has target, just remove the source assignment
                DB::table('user_roles')->where('id', $ur->id)->delete();
            }
        }

        // Delete the source role
        DB::table('roles')->where('id', $sourceRole->id)->delete();
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        // Merging is destructive, difficult to reverse automatically.
    }
};
