<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::table('user_statistics', function (Blueprint $table) {
            $table->unsignedInteger('followers_count')->default(0)->after('profile_views');
            $table->unsignedInteger('following_count')->default(0)->after('followers_count');
        });

        // Backfill follow counters using subquery syntax (compatible with MySQL + SQLite).
        DB::statement("
            UPDATE user_statistics
            SET followers_count = (
                SELECT COUNT(*) FROM user_followers
                WHERE user_followers.user_id = user_statistics.user_id
            )
        ");

        DB::statement("
            UPDATE user_statistics
            SET following_count = (
                SELECT COUNT(*) FROM user_followers
                WHERE user_followers.follower_id = user_statistics.user_id
            )
        ");
    }

    public function down(): void
    {
        Schema::table('user_statistics', function (Blueprint $table) {
            $table->dropColumn(['followers_count', 'following_count']);
        });
    }
};
