<?php

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

return new class extends Migration
{
    public function up(): void
    {
        // Add columns to existing users table
        Schema::table('users', function (Blueprint $table) {
            if (!Schema::hasColumn('users', 'username')) {
                $table->string('username')->nullable()->after('name');
            }
            if (!Schema::hasColumn('users', 'bio')) {
                $table->text('bio')->nullable()->after('password');
            }
            if (!Schema::hasColumn('users', 'avatar')) {
                $table->string('avatar')->nullable()->after('bio');
            }
            if (!Schema::hasColumn('users', 'cover_image')) {
                $table->string('cover_image')->nullable()->after('avatar');
            }
            if (!Schema::hasColumn('users', 'birthdate')) {
                $table->date('birthdate')->nullable()->after('cover_image');
            }
            if (!Schema::hasColumn('users', 'gender')) {
                $table->enum('gender', ['male', 'female', 'other', 'prefer_not_to_say'])->nullable()->after('birthdate');
            }
            if (!Schema::hasColumn('users', 'location')) {
                $table->string('location')->nullable()->after('gender');
            }
            if (!Schema::hasColumn('users', 'website')) {
                $table->string('website')->nullable()->after('location');
            }
            if (!Schema::hasColumn('users', 'provider')) {
                $table->string('provider')->nullable()->after('website');
            }
            if (!Schema::hasColumn('users', 'provider_id')) {
                $table->string('provider_id')->nullable()->after('provider');
            }
            if (!Schema::hasColumn('users', 'provider_token')) {
                $table->string('provider_token')->nullable()->after('provider_id');
            }
            if (!Schema::hasColumn('users', 'provider_refresh_token')) {
                $table->string('provider_refresh_token')->nullable()->after('provider_token');
            }
            if (!Schema::hasColumn('users', 'two_factor_enabled')) {
                $table->boolean('two_factor_enabled')->default(false)->after('provider_refresh_token');
            }
            if (!Schema::hasColumn('users', 'two_factor_secret')) {
                $table->string('two_factor_secret')->nullable()->after('two_factor_enabled');
            }
            if (!Schema::hasColumn('users', 'two_factor_recovery_codes')) {
                $table->string('two_factor_recovery_codes')->nullable()->after('two_factor_secret');
            }
            if (!Schema::hasColumn('users', 'two_factor_confirmed_at')) {
                $table->timestamp('two_factor_confirmed_at')->nullable()->after('two_factor_recovery_codes');
            }
            if (!Schema::hasColumn('users', 'status')) {
                $table->enum('status', ['active', 'suspended', 'deactivated'])->default('active')->after('two_factor_confirmed_at');
            }
            if (!Schema::hasColumn('users', 'last_login_at')) {
                $table->timestamp('last_login_at')->nullable()->after('status');
            }
            if (!Schema::hasColumn('users', 'last_login_ip')) {
                $table->string('last_login_ip')->nullable()->after('last_login_at');
            }
            if (!Schema::hasColumn('users', 'deleted_at')) {
                $table->softDeletes()->after('last_login_ip');
            }
        });

        // Populate usernames for existing users
        \DB::table('users')->whereNull('username')->orWhere('username', '')->get()->each(function ($user) {
            $base = strtolower(preg_replace('/[^a-zA-Z0-9]/', '', $user->name));
            $username = $base;
            $counter = 1;
            
            while (\DB::table('users')->where('username', $username)->exists()) {
                $username = $base . $counter;
                $counter++;
            }
            
            \DB::table('users')->where('id', $user->id)->update(['username' => $username]);
        });

        // Now make username unique
        Schema::table('users', function (Blueprint $table) {
            $table->string('username')->unique()->change();
        });
        if (!Schema::hasTable('email_verifications')) {
            Schema::create('email_verifications', function (Blueprint $table) {
                $table->id();
                $table->foreignId('user_id')->constrained()->onDelete('cascade');
                $table->string('token');
                $table->timestamp('expires_at');
                $table->timestamps();
            });
        }

        if (!Schema::hasTable('login_attempts')) {
            Schema::create('login_attempts', function (Blueprint $table) {
                $table->id();
                $table->string('email')->nullable();
                $table->string('ip_address');
                $table->boolean('successful')->default(false);
                $table->text('user_agent')->nullable();
                $table->timestamps();
                
                $table->index(['email', 'created_at']);
                $table->index(['ip_address', 'created_at']);
            });
        }

        if (!Schema::hasTable('user_sessions')) {
            Schema::create('user_sessions', function (Blueprint $table) {
                $table->id();
                $table->foreignId('user_id')->constrained()->onDelete('cascade');
                $table->string('session_id');
                $table->string('ip_address');
                $table->text('user_agent');
                $table->timestamp('last_activity');
                $table->timestamps();
                
                $table->index(['user_id', 'last_activity']);
            });
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('user_sessions');
        Schema::dropIfExists('login_attempts');
        Schema::dropIfExists('email_verifications');
        
        Schema::table('users', function (Blueprint $table) {
            $columns = [
                'username', 'bio', 'avatar', 'cover_image', 'birthdate', 'gender',
                'location', 'website', 'provider', 'provider_id', 'provider_token',
                'provider_refresh_token', 'two_factor_enabled', 'two_factor_secret',
                'two_factor_recovery_codes', 'two_factor_confirmed_at', 'status',
                'last_login_at', 'last_login_ip', 'deleted_at'
            ];
            
            foreach ($columns as $column) {
                if (Schema::hasColumn('users', $column)) {
                    $table->dropColumn($column);
                }
            }
        });
    }
};
