<?php

use App\Models\Operator;
use App\Models\User;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use Spatie\Permission\PermissionRegistrar;

// RuntimeException is a global-namespace class — a `use` for it is a no-op,
// and this repo turns that notice into a fatal ErrorException during tests.

/**
 * RBAC moves to the operator guard. It is a move, not a copy.
 *
 * Every one of the seventeen permissions is a console permission — there is no
 * customer-facing one among them — so duplicating the set under a second guard
 * would create two role sets that drift apart. Moving them leaves `users` with
 * no roles at all, which is exactly right.
 *
 * Four columns elsewhere are foreign keys into `users` that have only ever
 * held an operator's id — never a customer's:
 *
 *  - vpn_peers.user_id              ON DELETE RESTRICT
 *  - vpn_peers.created_by           ON DELETE SET NULL
 *  - maintenance_windows.created_by ON DELETE SET NULL
 *  - app_secrets.updated_by         ON DELETE SET NULL
 *
 * (customers.user_id is also a `users` FK, ON DELETE SET NULL, but it is the
 * one link a `users` row genuinely has to a customer — that one is read, not
 * rewritten; see removeUserRow().)
 *
 * Deleting an operator's `users` row before those four are dealt with does not
 * fail safely. RESTRICT aborts the delete outright — on a live server this
 * operator owns a VPN peer or two and the migration would never get past the
 * first one. The three SET NULL columns are worse: the delete would succeed
 * and silently erase who created that maintenance window or last rotated that
 * secret. So the order in this migration is: create every operator, remap
 * those four columns from the old users.id to the new operators.id (the map
 * is built once, in carryAcross(), while both ids are still in hand — not
 * reconstructed from email afterwards), swap the four constraints to point at
 * `operators`, and only then delete the `users` rows. By the time a row is
 * deleted nothing anywhere still points at it, so it does not matter which
 * ON DELETE rule used to apply.
 *
 * Deliberately NOT wrapped in DB::transaction(). Laravel's own migrator
 * already does that for a grammar that supports transactional DDL — SQLite
 * (tests) and Postgres — via $withinTransaction (default true, left alone
 * here). MySQL/MariaDB's grammar reports supportsSchemaTransactions() as
 * false precisely because an ALTER TABLE there commits on its own regardless
 * of any surrounding transaction, so the migrator does not open one and this
 * file must not open one either: a manual DB::transaction() around the DDL
 * below does not get rolled back by it on a real server — it gets desynced by
 * it, and the migration fails on its own final commit() with "There is no
 * active transaction" even on the fully successful path. Found by actually
 * running this against dev's MariaDB, not by the test suite, which uses
 * SQLite and does not surface the difference. What keeps this safe without a
 * transaction is the same thing that keeps it safe on SQLite if the migrator's
 * own transaction rolls back: every step is idempotent (firstOrNew, guarded
 * drops, remaps keyed on a still-matching id), so a run that stops partway —
 * the customer-conflict abort below, or a dropped connection — leaves
 * something a second run can finish rather than something a second run
 * chokes on.
 */
return new class extends Migration
{
    /** @var array<int, array{table: string, column: string, onDelete: string}> */
    private const OWNED_COLUMNS = [
        ['table' => 'vpn_peers', 'column' => 'user_id', 'onDelete' => 'restrict'],
        ['table' => 'vpn_peers', 'column' => 'created_by', 'onDelete' => 'null'],
        ['table' => 'maintenance_windows', 'column' => 'created_by', 'onDelete' => 'null'],
        ['table' => 'app_secrets', 'column' => 'updated_by', 'onDelete' => 'null'],
    ];

    public function up(): void
    {
        // Preflight, before a single UPDATE, ALTER TABLE or row copy below:
        // walk the whole worklist and refuse up front if ANY account on it is
        // also a customer's portal login, naming every conflicting address at
        // once. The check used to live only inside removeUserRow(), reached
        // after permissions, roles, ownership ids and the four OWNED_COLUMNS
        // constraints had already moved — none of which is transactional on
        // MariaDB (see the class docblock above), so that abort left a live
        // server's currently-deployed (web-guard) code unable to authenticate
        // anyone until a human resolved the conflict and re-ran. Nothing here
        // needs any of that to have happened first: the worklist and the
        // customer link are both already knowable.
        $this->preflightCustomerConflicts();

        app(PermissionRegistrar::class)->forgetCachedPermissions();

        DB::table('permissions')->where('guard_name', 'web')->update(['guard_name' => 'operator']);
        DB::table('roles')->where('guard_name', 'web')->update(['guard_name' => 'operator']);

        // Dropped before any value is remapped: the four columns are
        // about to hold operators.id instead of users.id, and the
        // constraint that is still active while that happens has to
        // accept neither, one, or both — simplest is to accept anything
        // until the values are settled.
        $this->dropOwnedColumnForeignKeys();

        foreach ($this->operatorUserIds() as $userId) {
            $this->carryAcross($userId);
        }

        // Every operator exists now and every owned-column value already
        // points at one — safe to require that going forward, and doing
        // it here (before a single `users` row is gone) means a
        // customer-conflict abort just below leaves the columns correctly
        // constrained against `operators`, not against an id that no
        // longer carries the role it used to.
        $this->addOwnedColumnForeignKeys('operators');

        // NOT driven by operatorUserIds(): carryAcross() above repoints
        // model_has_roles to Operator unconditionally, for every operator,
        // before a single `users` row is deleted. So on a retry after a
        // partial run — the customer-conflict abort below, or simply a
        // dropped connection, since this file deliberately runs without a
        // transaction (see the class docblock) — model_has_roles already has
        // nothing left with model_type=User, even though a `users` row can
        // still be sitting there untouched. A worklist built from that query
        // would find nothing to remove and `migrate` would record this
        // migration as applied with the leftover row still granting a live
        // portal login. Driven by `operators` joined to `users` on email
        // instead: that join finds every carried-but-not-yet-deleted row
        // regardless of which run carried it, so a second `migrate` actually
        // finishes the job rather than silently no-opping.
        foreach ($this->carriedUserRows() as $row) {
            $this->removeUserRow($row);
        }

        app(PermissionRegistrar::class)->forgetCachedPermissions();
    }

    /**
     * Every `users` row that currently holds a console role, a directly
     * granted console permission, or both — and has not yet been carried
     * across to `operators`.
     *
     * Spatie allows a permission to be granted straight to a model with no
     * role in between. Reading only model_has_roles missed exactly that
     * case: an account like that kept working right up until this migration
     * flips the permission's own guard to `operator` a few lines above, at
     * which point it silently lost console access while staying behind as a
     * portal user — nothing downstream ever noticed, because carryAcross()
     * was never asked to look at it.
     *
     * Both queries keep `orderBy('model_id')`, and the merged, deduplicated
     * result is sorted again in PHP — the ordering matters regardless of
     * which table a given id came from: carryAcross() below remaps the four
     * OWNED_COLUMNS in place, keyed on the OLD users.id, while operators.id —
     * auto-increment — hands out its values in exactly the order this list is
     * walked. Unordered, a later operator's freshly assigned operators.id
     * can equal an earlier operator's still-unprocessed users.id, and the
     * second remap's `WHERE user_id = X` then catches a row the first remap
     * already rewrote to that same X. Collision-free today only because
     * MariaDB happens to satisfy the model_has_roles query from an index
     * that already returns rows in ascending model_id order; forcing it to
     * answer from PRIMARY (role_id, model_id, model_type) instead returns
     * them out of order, and the collision hands one operator another
     * operator's VPN peer — silently, since both updates report success.
     */
    private function operatorUserIds(): array
    {
        $viaRoles = DB::table('model_has_roles')
            ->where('model_type', User::class)
            ->orderBy('model_id')
            ->pluck('model_id');

        $viaPermissions = DB::table('model_has_permissions')
            ->where('model_type', User::class)
            ->orderBy('model_id')
            ->pluck('model_id');

        return $viaRoles->merge($viaPermissions)->unique()->sort()->values()->all();
    }

    /**
     * Every `users` row whose email already has a matching `operators` row —
     * meaning carryAcross() has already run for it, in this pass or an
     * earlier one, and only the delete in removeUserRow() is still
     * outstanding. See the comment in up() for why this drives the removal
     * loop instead of operatorUserIds()/model_has_roles.
     *
     * Ordered by users.id for the same reason operatorUserIds() is: a
     * deterministic order is one less thing that can vary between a run that
     * was tested and a run that was not.
     *
     * @return Collection<int, object>
     */
    private function carriedUserRows(): Collection
    {
        return DB::table('users')
            ->join('operators', 'operators.email', '=', 'users.email')
            ->orderBy('users.id')
            ->select('users.*')
            ->get();
    }

    /** Idempotent: safe to call again for a userId that was already carried across. */
    private function carryAcross(int $userId): void
    {
        $row = DB::table('users')->where('id', $userId)->first();

        if ($row === null) {
            return;
        }

        // Idempotent: re-running must fix a half-finished move, not fail on the
        // unique email.
        $operator = Operator::firstOrNew(['email' => $row->email]);
        $operator->name = $row->name;
        $operator->uuid ??= (string) Str::uuid();
        // operators.password is NOT NULL. The raw update just below is what
        // actually carries every sensitive field across, but this first save()
        // still has to satisfy the column — with the row's own hash, not a
        // throwaway placeholder a crash between the two statements could leave
        // behind.
        $operator->password ??= $row->password;
        $operator->save();

        // The hash and the two-factor secret are carried, not translated — the
        // operator signs in with the same password as before.
        DB::table('operators')->where('id', $operator->id)->update([
            'password' => $row->password,
            'two_factor_secret' => $row->two_factor_secret,
            'two_factor_recovery_codes' => $row->two_factor_recovery_codes,
            'two_factor_confirmed_at' => $row->two_factor_confirmed_at,
            'remember_token' => $row->remember_token,
        ]);

        // Re-point the role assignment at the new model.
        DB::table('model_has_roles')
            ->where('model_type', User::class)->where('model_id', $userId)
            ->update(['model_type' => Operator::class, 'model_id' => $operator->id]);

        DB::table('model_has_permissions')
            ->where('model_type', User::class)->where('model_id', $userId)
            ->update(['model_type' => Operator::class, 'model_id' => $operator->id]);

        // The old-id -> new-id map, built here while both are still in hand
        // rather than reconstructed from email once the users row is gone:
        // every column that has only ever held this operator's id follows
        // them to their new home.
        DB::table('vpn_peers')->where('user_id', $userId)->update(['user_id' => $operator->id]);
        DB::table('vpn_peers')->where('created_by', $userId)->update(['created_by' => $operator->id]);
        DB::table('maintenance_windows')->where('created_by', $userId)->update(['created_by' => $operator->id]);
        DB::table('app_secrets')->where('updated_by', $userId)->update(['updated_by' => $operator->id]);
    }

    /**
     * Refuse up front, for every conflicting address at once, before this
     * migration has changed anything. Shares isPortalLoginForCustomer() with
     * removeUserRow() below rather than a second copy of the rule — one
     * decision, checked twice. removeUserRow()'s own check stays in place as
     * a backstop for a conflict created in the (short) window between this
     * preflight and that row's own delete — a race, not a duplicated
     * decision.
     */
    private function preflightCustomerConflicts(): void
    {
        $conflicts = [];

        foreach ($this->operatorUserIds() as $userId) {
            $row = DB::table('users')->where('id', $userId)->first();

            if ($row !== null && $this->isPortalLoginForCustomer($row->id)) {
                $conflicts[] = $row->email;
            }
        }

        if ($conflicts !== []) {
            throw new RuntimeException(
                'Refusing to move: the following accounts are both an operator and a customer. '
                .'Resolve them by hand before migrating: '.implode(', ', $conflicts)
            );
        }
    }

    /**
     * `users` carries no `customer_id` of its own (checked against the real
     * schema) — the link runs the other way, `customers.user_id`, exactly the
     * column Customer::ensureUser() writes when it attaches a portal login. That
     * is the one place a users row is "also a customer"; seats and orders both
     * hang off `customers.id`, never off a user directly, so there is nothing
     * further to check once that link is clear.
     */
    private function isPortalLoginForCustomer(int $userId): bool
    {
        return DB::table('customers')->where('user_id', $userId)->exists();
    }

    /**
     * The users row goes — whoever is in `operators` has no business in `users`.
     *
     * One exception, and it deletes nothing: a row that is also a customer's
     * portal login means the same person is operator AND paying customer. Then
     * it is not the row that is wrong but the assumption, and a silent delete
     * would take billing data with it. preflightCustomerConflicts() above
     * already refused the whole migration if this were true when it started;
     * reaching here with a conflict means one appeared in between — worth
     * catching, not worth trusting to have already been caught.
     */
    private function removeUserRow(object $row): void
    {
        if ($this->isPortalLoginForCustomer($row->id)) {
            throw new RuntimeException(
                "Refusing to move [{$row->email}]: this account is both an operator and a customer. "
                .'Resolve that by hand before migrating.'
            );
        }

        DB::table('users')->where('id', $row->id)->delete();
    }

    /**
     * Drop the four OWNED_COLUMNS foreign keys — by column name, so it does
     * not matter whether the existing constraint currently points at `users`
     * or `operators`. That is what makes this safe to call on an
     * already-migrated schema (RefreshDatabase re-running this file) as well
     * as a fresh one.
     *
     * Guarded with hasForeignKey(): on real MariaDB, DDL is not transactional
     * — an ALTER TABLE commits immediately, on its own, regardless of what
     * DB::transaction() around it thinks is happening. That means a run that
     * fails after this point (the customer-conflict abort below, or simply a
     * dropped connection) leaves the constraint gone for good, and the next
     * attempt at this migration has to cope with that rather than fail
     * trying to drop something that is already dropped. SQLite tolerates a
     * redundant drop silently; MariaDB does not, which is how this was found
     * — dev's own admin account already had a VPN peer and a maintenance
     * window, and a first, buggy version of this method failed exactly here.
     */
    private function dropOwnedColumnForeignKeys(): void
    {
        foreach (self::OWNED_COLUMNS as ['table' => $table, 'column' => $column]) {
            if (! Schema::hasForeignKey($table, [$column])) {
                continue;
            }

            Schema::table($table, fn (Blueprint $blueprint) => $blueprint->dropForeign([$column]));
        }
    }

    private function addOwnedColumnForeignKeys(string $to): void
    {
        foreach (self::OWNED_COLUMNS as ['table' => $table, 'column' => $column, 'onDelete' => $onDelete]) {
            Schema::table($table, function (Blueprint $blueprint) use ($column, $to, $onDelete) {
                $foreign = $blueprint->foreign($column)->references('id')->on($to);
                $onDelete === 'restrict' ? $foreign->restrictOnDelete() : $foreign->nullOnDelete();
            });
        }
    }

    /**
     * `down()` recreates the `users` rows from `operators`. What it cannot bring
     * back is anything that only ever existed on the operator — `last_login_at`
     * has no column on `users` and is dropped on the way back, along with
     * `disabled_at`.
     */
    public function down(): void
    {
        app(PermissionRegistrar::class)->forgetCachedPermissions();

        $this->dropOwnedColumnForeignKeys();

        foreach (DB::table('operators')->get() as $row) {
            $userId = DB::table('users')->insertGetId([
                'name' => $row->name, 'email' => $row->email, 'password' => $row->password,
                'two_factor_secret' => $row->two_factor_secret,
                'two_factor_recovery_codes' => $row->two_factor_recovery_codes,
                'two_factor_confirmed_at' => $row->two_factor_confirmed_at,
                'remember_token' => $row->remember_token,
                'is_admin' => true,
                'created_at' => $row->created_at, 'updated_at' => $row->updated_at,
            ]);

            DB::table('model_has_roles')
                ->where('model_type', Operator::class)->where('model_id', $row->id)
                ->update(['model_type' => User::class, 'model_id' => $userId]);

            // Mirrors carryAcross(): that method moves BOTH model_has_roles and
            // model_has_permissions forward, so an operator holding a direct
            // (not role-granted) permission does not lose it on the way back.
            DB::table('model_has_permissions')
                ->where('model_type', Operator::class)->where('model_id', $row->id)
                ->update(['model_type' => User::class, 'model_id' => $userId]);

            DB::table('vpn_peers')->where('user_id', $row->id)->update(['user_id' => $userId]);
            DB::table('vpn_peers')->where('created_by', $row->id)->update(['created_by' => $userId]);
            DB::table('maintenance_windows')->where('created_by', $row->id)->update(['created_by' => $userId]);
            DB::table('app_secrets')->where('updated_by', $row->id)->update(['updated_by' => $userId]);
        }

        $this->addOwnedColumnForeignKeys('users');

        DB::table('permissions')->where('guard_name', 'operator')->update(['guard_name' => 'web']);
        DB::table('roles')->where('guard_name', 'operator')->update(['guard_name' => 'web']);

        app(PermissionRegistrar::class)->forgetCachedPermissions();
    }
};
