<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

/**
 * Per-session refresh tokens.
 *
 * `refresh_tokens` was created with the PRIMARY KEY on `user_id` (one row per
 * user), so every login overwrote the user's single refresh token — a login on
 * one device invalidated every other device's token, whose refresh call then
 * 404'd ("No record found") and logged them out at the ~20-minute access-token
 * expiry.
 *
 * This gives the table a surrogate auto-increment `id` primary key so a user
 * can hold many refresh tokens at once (one per session / device / app). The
 * existing `user_id` and `token` indexes are retained: lookups on refresh are
 * by the hashed `token` column, so existing valid sessions keep working.
 *
 * Pairs with RefreshToken::PRIMARY_KEY = 'id' and RefreshTokenFactory inserting
 * a fresh row per login. Raw, guarded SQL is used (rather than the Phinx table
 * builder) because swapping a composite/natural primary key for a new
 * auto-increment column is not expressible with changeColumn(), and the guards
 * keep it idempotent across environments that may already carry the column.
 */
final class RefreshTokensPerSession extends AbstractMigration
{
    public function up(): void
    {
        if ($this->hasIdColumn()) {
            return;
        }

        // Drop the user_id primary key and add a surrogate auto-increment id.
        // user_id keeps its (non-unique) index from the create migration, so
        // multiple rows per user are now allowed.
        $this->execute(
            'ALTER TABLE refresh_tokens
                DROP PRIMARY KEY,
                ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST'
        );
    }

    public function down(): void
    {
        if (!$this->hasIdColumn()) {
            return;
        }

        // Revert to a single row per user: keep only the newest token per user
        // (there must be no duplicates for user_id to become the PK again).
        $this->execute(
            'DELETE t1 FROM refresh_tokens t1
                INNER JOIN refresh_tokens t2
                WHERE t1.user_id = t2.user_id AND t1.id < t2.id'
        );
        $this->execute(
            'ALTER TABLE refresh_tokens
                DROP PRIMARY KEY,
                DROP COLUMN id,
                ADD PRIMARY KEY (user_id)'
        );
    }

    private function hasIdColumn(): bool
    {
        return (int) $this->fetchRow(
            "SELECT COUNT(*) c FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME = 'refresh_tokens'
               AND COLUMN_NAME = 'id'"
        )['c'] === 1;
    }
}
