352 lines
14 KiB
PHP
352 lines
14 KiB
PHP
<?php
|
||
|
||
namespace App\Console\Commands;
|
||
|
||
use App\Support\UsernamePolicy;
|
||
use Illuminate\Console\Command;
|
||
use Illuminate\Support\Facades\DB;
|
||
use Illuminate\Support\Facades\Hash;
|
||
use Illuminate\Support\Str;
|
||
|
||
/**
|
||
* Migrates legacy friends_list (from the legacy DB connection) into user_followers.
|
||
*
|
||
* Usage:
|
||
* php artisan skinbase:migrate-follows [--dry-run] [--chunk=1000] [--import-missing-users]
|
||
*
|
||
* Legacy table: friends_list
|
||
* user_id -> follower_id (the user who added the friend = someone who follows)
|
||
* friend_id -> user_id (the user being followed)
|
||
*
|
||
* With --import-missing-users: any user referenced in friends_list that does not
|
||
* exist in the new DB will be fetched from the legacy `users` table and created
|
||
* as a stub before the follow row is inserted.
|
||
*/
|
||
class MigrateFollows extends Command
|
||
{
|
||
protected $signature = 'skinbase:migrate-follows
|
||
{--dry-run : Simulate without writing to the database}
|
||
{--chunk=1000 : Number of rows to process per batch}
|
||
{--import-missing-users : Import unknown users from legacy DB instead of skipping them}';
|
||
|
||
protected $description = 'Migrate legacy friends_list into user_followers';
|
||
|
||
/** Cache per-run: id => true (resolved) | null (not in legacy DB) | false (import error) */
|
||
private array $legacyUserCache = [];
|
||
|
||
public function handle(): int
|
||
{
|
||
$isDryRun = (bool) $this->option('dry-run');
|
||
$chunkSize = max(1, (int) $this->option('chunk'));
|
||
$importMissing = (bool) $this->option('import-missing-users');
|
||
|
||
$this->info($isDryRun
|
||
? '🔍 Dry-run mode – nothing will be written.'
|
||
: '🚀 Live mode – writing to user_followers.'
|
||
);
|
||
if ($importMissing) {
|
||
$this->info('👤 --import-missing-users: orphan users will be fetched from legacy DB.');
|
||
}
|
||
|
||
try {
|
||
$totalLegacy = DB::connection('legacy')->table('friends_list')->count();
|
||
} catch (\Throwable $e) {
|
||
$this->error('Cannot read legacy friends_list: ' . $e->getMessage());
|
||
return self::FAILURE;
|
||
}
|
||
|
||
$this->info("Total rows in legacy friends_list: {$totalLegacy}");
|
||
|
||
$validUserIds = DB::table('users')->pluck('id')->flip()->all();
|
||
|
||
$stats = [
|
||
'processed' => 0,
|
||
'inserted' => 0,
|
||
'duplicates' => 0,
|
||
'self_follows' => 0,
|
||
'invalid' => 0, // total orphan rows skipped
|
||
'invalid_zero_id' => 0, // follower_id or friend_id was 0
|
||
'invalid_not_in_new' => 0, // not in new DB (--import-missing-users not used)
|
||
'invalid_not_in_legacy' => 0, // not in new DB AND not in legacy DB
|
||
'invalid_import_error' => 0, // in legacy DB but stub import failed
|
||
'users_imported' => 0,
|
||
'errors' => 0,
|
||
];
|
||
|
||
$logPath = storage_path('logs/migrate_follows.log');
|
||
$logFile = fopen($logPath, 'a');
|
||
$this->logLine($logFile, '=== migrate-follows started at ' . now()->toISOString()
|
||
. " (dry_run={$isDryRun}, import_missing={$importMissing}) ===");
|
||
|
||
$chunkNum = 0;
|
||
$reportEvery = max(1, (int) ceil($totalLegacy / $chunkSize / 10));
|
||
|
||
DB::connection('legacy')
|
||
->table('friends_list')
|
||
->orderBy('id')
|
||
->chunk($chunkSize, function ($rows) use (
|
||
$isDryRun,
|
||
$importMissing,
|
||
&$validUserIds,
|
||
&$stats,
|
||
&$chunkNum,
|
||
$reportEvery,
|
||
$totalLegacy,
|
||
$logFile
|
||
) {
|
||
$toInsert = [];
|
||
|
||
foreach ($rows as $row) {
|
||
$stats['processed']++;
|
||
|
||
$followerId = (int) ($row->user_id ?? 0);
|
||
$followedId = (int) ($row->friend_id ?? 0);
|
||
$createdAt = $row->date_added ?? now();
|
||
|
||
if ($followerId === $followedId) {
|
||
$stats['self_follows']++;
|
||
$this->logLine($logFile, "SKIP self-follow: user_id={$followerId}");
|
||
continue;
|
||
}
|
||
|
||
// Try to resolve any user_id that isn't in the new DB yet
|
||
$skipReasons = [];
|
||
$sides = ['follower' => $followerId, 'followed' => $followedId];
|
||
|
||
foreach ($sides as $role => $uid) {
|
||
if (isset($validUserIds[$uid])) {
|
||
continue; // already valid
|
||
}
|
||
|
||
if ($uid === 0) {
|
||
$skipReasons[] = "{$role}_id is 0/null";
|
||
$stats['invalid_zero_id']++;
|
||
continue;
|
||
}
|
||
|
||
if (! $importMissing) {
|
||
$skipReasons[] = "{$role}={$uid} not in users table (use --import-missing-users to auto-import)";
|
||
$stats['invalid_not_in_new']++;
|
||
continue;
|
||
}
|
||
|
||
// ensureLegacyUser returns: true = resolved, null = not in legacy, false = import error
|
||
$result = $this->ensureLegacyUser($uid, $isDryRun, $logFile);
|
||
if ($result === true) {
|
||
$validUserIds[$uid] = true;
|
||
$stats['users_imported']++;
|
||
} elseif ($result === null) {
|
||
$skipReasons[] = "{$role}={$uid} not found in legacy DB";
|
||
$stats['invalid_not_in_legacy']++;
|
||
} else {
|
||
$skipReasons[] = "{$role}={$uid} found in legacy DB but import failed";
|
||
$stats['invalid_import_error']++;
|
||
}
|
||
}
|
||
|
||
if (! isset($validUserIds[$followerId]) || ! isset($validUserIds[$followedId])) {
|
||
$stats['invalid']++;
|
||
$reason = implode('; ', $skipReasons) ?: 'unknown';
|
||
$this->logLine($logFile, "SKIP orphan [row_id={$row->id}] follower={$followerId} followed={$followedId} — {$reason}");
|
||
continue;
|
||
}
|
||
|
||
$toInsert[] = [
|
||
'follower_id' => $followerId,
|
||
'user_id' => $followedId,
|
||
'created_at' => $createdAt,
|
||
];
|
||
}
|
||
|
||
if (! $isDryRun && ! empty($toInsert)) {
|
||
try {
|
||
$inserted = DB::table('user_followers')->insertOrIgnore($toInsert);
|
||
$stats['inserted'] += $inserted;
|
||
$stats['duplicates'] += count($toInsert) - $inserted;
|
||
} catch (\Throwable $e) {
|
||
$stats['errors']++;
|
||
$this->logLine($logFile, 'ERROR batch insert: ' . $e->getMessage());
|
||
}
|
||
} elseif ($isDryRun) {
|
||
$stats['inserted'] += count($toInsert);
|
||
}
|
||
|
||
$chunkNum++;
|
||
if ($chunkNum % $reportEvery === 0 || $stats['processed'] >= $totalLegacy) {
|
||
$pct = $totalLegacy > 0 ? round($stats['processed'] / $totalLegacy * 100) : 100;
|
||
$this->line(" {$stats['processed']} / {$totalLegacy} rows ({$pct}%)"
|
||
. " inserted: {$stats['inserted']}"
|
||
. " imported: {$stats['users_imported']}"
|
||
. " skipped: " . ($stats['self_follows'] + $stats['invalid']));
|
||
}
|
||
});
|
||
|
||
$this->newLine();
|
||
|
||
if (! $isDryRun) {
|
||
$this->info('Backfilling user_statistics counters...');
|
||
$this->backfillCounters();
|
||
}
|
||
|
||
$this->table(
|
||
['Metric', 'Count'],
|
||
[
|
||
['Processed', $stats['processed']],
|
||
['Inserted', $stats['inserted']],
|
||
['Duplicates (already exist)', $stats['duplicates']],
|
||
['Self-follows skipped', $stats['self_follows']],
|
||
['Users stub-imported from legacy', $stats['users_imported']],
|
||
['Invalid (orphan) — total', $stats['invalid']],
|
||
[' ↳ zero/null user_id', $stats['invalid_zero_id']],
|
||
[' ↳ not in new DB (not imported)', $stats['invalid_not_in_new']],
|
||
[' ↳ not in legacy DB either', $stats['invalid_not_in_legacy']],
|
||
[' ↳ legacy import error', $stats['invalid_import_error']],
|
||
['Errors', $stats['errors']],
|
||
]
|
||
);
|
||
|
||
$summary = "Processed={$stats['processed']} Inserted={$stats['inserted']} "
|
||
. "Duplicates={$stats['duplicates']} SelfFollows={$stats['self_follows']} "
|
||
. "UsersImported={$stats['users_imported']} Invalid={$stats['invalid']} "
|
||
. "(ZeroId={$stats['invalid_zero_id']} NotInNew={$stats['invalid_not_in_new']} "
|
||
. "NotInLegacy={$stats['invalid_not_in_legacy']} ImportError={$stats['invalid_import_error']}) "
|
||
. "Errors={$stats['errors']}";
|
||
|
||
$this->logLine($logFile, "=== DONE: {$summary} ===");
|
||
fclose($logFile);
|
||
|
||
$this->info("Log written to: {$logPath}");
|
||
|
||
return self::SUCCESS;
|
||
}
|
||
|
||
// -------------------------------------------------------------------------
|
||
|
||
/**
|
||
* Ensure a legacy user_id exists in the new `users` table.
|
||
*
|
||
* Returns:
|
||
* true – user is valid (was already there, or was just imported / dry-run pretend-imported)
|
||
* null – user not found in the legacy DB either → cannot be imported
|
||
* false – user found in legacy DB but the stub-import threw an exception
|
||
*
|
||
* Results are cached per command run to avoid redundant DB queries.
|
||
*/
|
||
private function ensureLegacyUser(int $legacyId, bool $isDryRun, $logFile): ?bool
|
||
{
|
||
if (array_key_exists($legacyId, $this->legacyUserCache)) {
|
||
return $this->legacyUserCache[$legacyId];
|
||
}
|
||
|
||
if (DB::table('users')->where('id', $legacyId)->exists()) {
|
||
return $this->legacyUserCache[$legacyId] = true;
|
||
}
|
||
|
||
$legacyUser = DB::connection('legacy')
|
||
->table('users')
|
||
->where('user_id', $legacyId)
|
||
->first();
|
||
|
||
if (! $legacyUser) {
|
||
$this->logLine($logFile, "IMPORT FAIL: user_id={$legacyId} not found in legacy DB");
|
||
return $this->legacyUserCache[$legacyId] = null;
|
||
}
|
||
|
||
if ($isDryRun) {
|
||
$this->logLine($logFile, "DRY-RUN IMPORT: would create user_id={$legacyId} uname={$legacyUser->uname}");
|
||
return $this->legacyUserCache[$legacyId] = true;
|
||
}
|
||
|
||
try {
|
||
$this->importLegacyUserStub($legacyUser);
|
||
$this->logLine($logFile, "IMPORTED user_id={$legacyId} uname={$legacyUser->uname}");
|
||
return $this->legacyUserCache[$legacyId] = true;
|
||
} catch (\Throwable $e) {
|
||
$this->logLine($logFile, "IMPORT ERROR user_id={$legacyId}: " . $e->getMessage());
|
||
return $this->legacyUserCache[$legacyId] = false;
|
||
}
|
||
}
|
||
|
||
private function importLegacyUserStub(object $row): void
|
||
{
|
||
$legacyId = (int) $row->user_id;
|
||
$now = now();
|
||
|
||
$username = UsernamePolicy::sanitizeLegacy((string) ($row->uname ?: ('user' . $legacyId)));
|
||
if (! $username) {
|
||
$username = 'user' . $legacyId;
|
||
}
|
||
|
||
if (DB::table('users')->whereRaw('LOWER(username) = ?', [strtolower($username)])->exists()) {
|
||
$username = $username . $legacyId;
|
||
}
|
||
|
||
$email = ($row->email ? strtolower(trim($row->email)) : null)
|
||
?: ('user' . $legacyId . '@users.skinbase.org');
|
||
|
||
DB::transaction(function () use ($legacyId, $username, $email, $row, $now) {
|
||
DB::table('users')->insertOrIgnore([
|
||
'id' => $legacyId,
|
||
'username' => $username,
|
||
'name' => $row->real_name ?: $username,
|
||
'email' => $email,
|
||
'password' => Hash::make(Str::random(32)),
|
||
'is_active' => (int) ($row->active ?? 1) === 1,
|
||
'needs_password_reset' => true,
|
||
'role' => 'user',
|
||
'created_at' => $row->joinDate ?? $now,
|
||
'updated_at' => $now,
|
||
]);
|
||
|
||
DB::table('user_profiles')->updateOrInsert(
|
||
['user_id' => $legacyId],
|
||
[
|
||
'country' => $row->country ?? null,
|
||
'country_code' => $row->country_code ? substr((string) $row->country_code, 0, 2) : null,
|
||
'website' => $row->web ?? null,
|
||
'updated_at' => $now,
|
||
]
|
||
);
|
||
|
||
DB::table('user_statistics')->updateOrInsert(
|
||
['user_id' => $legacyId],
|
||
['updated_at' => $now, 'created_at' => $now]
|
||
);
|
||
});
|
||
}
|
||
|
||
// -------------------------------------------------------------------------
|
||
|
||
private function backfillCounters(): void
|
||
{
|
||
DB::statement('
|
||
UPDATE user_statistics us
|
||
JOIN (
|
||
SELECT user_id, COUNT(*) AS cnt
|
||
FROM user_followers
|
||
GROUP BY user_id
|
||
) AS f ON f.user_id = us.user_id
|
||
SET us.followers_count = f.cnt, us.updated_at = NOW()
|
||
');
|
||
|
||
DB::statement('
|
||
UPDATE user_statistics us
|
||
JOIN (
|
||
SELECT follower_id, COUNT(*) AS cnt
|
||
FROM user_followers
|
||
GROUP BY follower_id
|
||
) AS f ON f.follower_id = us.user_id
|
||
SET us.following_count = f.cnt, us.updated_at = NOW()
|
||
');
|
||
|
||
$this->info('Counters backfilled.');
|
||
}
|
||
|
||
private function logLine($handle, string $message): void
|
||
{
|
||
if (is_resource($handle)) {
|
||
fwrite($handle, '[' . now()->toISOString() . '] ' . $message . PHP_EOL);
|
||
}
|
||
}
|
||
}
|