<?php

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

/**
 * An agent's softphone registers a single Twilio device (identity keyed on the
 * user id alone), bound to one workspace at a time. Presence is therefore a
 * single-workspace fact, so an agent should have exactly one agent_statuses row
 * — the workspace they are currently in — rather than one per solicitor they
 * have ever visited. This migration collapses the table to one row per agent
 * and replaces the per-(solicitor, agent) uniqueness with uniqueness on agent.
 */
return new class extends Migration
{
    public function up(): void
    {
        // Drop the per-workspace uniques before deduping / re-keying.
        Schema::table('agent_statuses', function (Blueprint $table) {
            $table->dropUnique(['solicitor_id', 'agent_id']);
            $table->dropUnique(['solicitor_id', 'extension']);
        });

        $this->collapseToOneRowPerAgent();

        Schema::table('agent_statuses', function (Blueprint $table) {
            $table->unique('agent_id');
        });
    }

    public function down(): void
    {
        Schema::table('agent_statuses', function (Blueprint $table) {
            $table->dropUnique(['agent_id']);
            $table->unique(['solicitor_id', 'agent_id']);
            $table->unique(['solicitor_id', 'extension']);
        });
    }

    /**
     * Keep the most "present" row per agent (available > busy > wrap_up >
     * do_not_disturb > offline), tie-broken by highest id, and delete the rest.
     * Presence is ephemeral — it is rebuilt on the agent's next page load — so
     * the surviving workspace only needs to be a sensible default.
     */
    protected function collapseToOneRowPerAgent(): void
    {
        $priority = [
            'available'      => 5,
            'busy'           => 4,
            'wrap_up'        => 3,
            'do_not_disturb' => 2,
            'offline'        => 1,
        ];

        $deleteIds = [];

        DB::table('agent_statuses')
            ->get(['id', 'agent_id', 'status'])
            ->groupBy('agent_id')
            ->each(function ($rows) use (&$deleteIds, $priority) {
                $best = $rows->sort(function ($a, $b) use ($priority) {
                    return [$priority[$b->status] ?? 0, $b->id]
                        <=> [$priority[$a->status] ?? 0, $a->id];
                })->first();

                foreach ($rows as $row) {
                    if ($row->id !== $best->id) {
                        $deleteIds[] = $row->id;
                    }
                }
            });

        if ($deleteIds) {
            DB::table('agent_statuses')->whereIn('id', $deleteIds)->delete();
        }
    }
};
