572 lines
22 KiB
PHP
572 lines
22 KiB
PHP
<?php
|
|
|
|
declare(strict_types=1);
|
|
|
|
namespace App\Console\Commands;
|
|
|
|
use App\Models\User;
|
|
use App\Models\UserActivity;
|
|
use App\Services\Activity\UserActivityService;
|
|
use Illuminate\Console\Command;
|
|
use Illuminate\Database\Query\Builder;
|
|
use Illuminate\Support\Collection;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
|
|
class BackfillUserActivitiesCommand extends Command
|
|
{
|
|
protected $signature = 'skinbase:backfill-user-activities
|
|
{--chunk=1000 : Number of source records to process per batch}
|
|
{--user-id= : Backfill only one actor user id}
|
|
{--types=all : Comma-separated groups: all, uploads, comments, likes, follows, achievements, forum}
|
|
{--dry-run : Preview inserts without writing changes}';
|
|
|
|
protected $description = 'Backfill historical profile activity into user_activities for existing users.';
|
|
|
|
public function __construct(private readonly UserActivityService $activities)
|
|
{
|
|
parent::__construct();
|
|
}
|
|
|
|
public function handle(): int
|
|
{
|
|
if (! Schema::hasTable('user_activities')) {
|
|
$this->error('The user_activities table does not exist. Run migrations first.');
|
|
|
|
return self::FAILURE;
|
|
}
|
|
|
|
$chunk = max(1, (int) $this->option('chunk'));
|
|
$userId = $this->option('user-id') !== null ? max(1, (int) $this->option('user-id')) : null;
|
|
$dryRun = (bool) $this->option('dry-run');
|
|
$groups = $this->parseGroups((string) $this->option('types'));
|
|
|
|
if ($groups === null) {
|
|
$this->error('Invalid --types value. Use one or more of: all, uploads, comments, likes, follows, achievements, forum.');
|
|
|
|
return self::FAILURE;
|
|
}
|
|
|
|
if ($userId !== null && ! User::query()->whereKey($userId)->exists()) {
|
|
$this->error("User id={$userId} was not found.");
|
|
|
|
return self::FAILURE;
|
|
}
|
|
|
|
if ($dryRun) {
|
|
$this->warn('[DRY RUN] No activity rows will be inserted.');
|
|
}
|
|
|
|
$this->info('Backfilling historical profile activity.');
|
|
|
|
$summary = [];
|
|
|
|
foreach ($groups as $group) {
|
|
$groupSummary = match ($group) {
|
|
'uploads' => [
|
|
'uploads' => $this->backfillUploads($chunk, $userId, $dryRun),
|
|
],
|
|
'comments' => [
|
|
'comments' => $this->backfillArtworkComments($chunk, $userId, $dryRun),
|
|
],
|
|
'likes' => [
|
|
'likes' => $this->backfillArtworkLikes($chunk, $userId, $dryRun),
|
|
'favourites' => $this->backfillArtworkFavourites($chunk, $userId, $dryRun),
|
|
],
|
|
'follows' => [
|
|
'follows' => $this->backfillFollows($chunk, $userId, $dryRun),
|
|
],
|
|
'achievements' => [
|
|
'achievements' => $this->backfillAchievements($chunk, $userId, $dryRun),
|
|
],
|
|
'forum' => [
|
|
'forum_posts' => $this->backfillForumThreads($chunk, $userId, $dryRun),
|
|
'forum_replies' => $this->backfillForumReplies($chunk, $userId, $dryRun),
|
|
],
|
|
default => [],
|
|
};
|
|
|
|
$summary = [...$summary, ...$groupSummary];
|
|
}
|
|
|
|
foreach ($summary as $label => $stats) {
|
|
$this->line(sprintf(
|
|
'%s: processed=%d inserted=%d existing=%d skipped=%d',
|
|
$label,
|
|
(int) ($stats['processed'] ?? 0),
|
|
(int) ($stats['inserted'] ?? 0),
|
|
(int) ($stats['existing'] ?? 0),
|
|
(int) ($stats['skipped'] ?? 0),
|
|
));
|
|
}
|
|
|
|
$totalProcessed = array_sum(array_map(static fn (array $stats): int => (int) ($stats['processed'] ?? 0), $summary));
|
|
$totalInserted = array_sum(array_map(static fn (array $stats): int => (int) ($stats['inserted'] ?? 0), $summary));
|
|
$totalExisting = array_sum(array_map(static fn (array $stats): int => (int) ($stats['existing'] ?? 0), $summary));
|
|
$totalSkipped = array_sum(array_map(static fn (array $stats): int => (int) ($stats['skipped'] ?? 0), $summary));
|
|
|
|
$this->info(sprintf(
|
|
'Finished. processed=%d inserted=%d existing=%d skipped=%d',
|
|
$totalProcessed,
|
|
$totalInserted,
|
|
$totalExisting,
|
|
$totalSkipped,
|
|
));
|
|
|
|
return self::SUCCESS;
|
|
}
|
|
|
|
/**
|
|
* @return array<int, string>|null
|
|
*/
|
|
private function parseGroups(string $value): ?array
|
|
{
|
|
$items = collect(explode(',', strtolower(trim($value))))
|
|
->map(static fn (string $item): string => trim($item))
|
|
->filter()
|
|
->values();
|
|
|
|
if ($items->isEmpty() || $items->contains('all')) {
|
|
return ['uploads', 'comments', 'likes', 'follows', 'achievements', 'forum'];
|
|
}
|
|
|
|
$allowed = ['uploads', 'comments', 'likes', 'follows', 'achievements', 'forum'];
|
|
if ($items->contains(static fn (string $item): bool => ! in_array($item, $allowed, true))) {
|
|
return null;
|
|
}
|
|
|
|
return $items->unique()->values()->all();
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillUploads(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('artworks')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('artworks')
|
|
->select(['id', 'user_id', 'created_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('artworks.user_id'))
|
|
->where('is_public', true)
|
|
->where('is_approved', true)
|
|
->whereNotNull('published_at')
|
|
->whereNull('deleted_at')
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'uploads',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_UPLOAD,
|
|
'entity_type' => UserActivity::ENTITY_ARTWORK,
|
|
'entity_id' => (int) $row->id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillArtworkComments(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('artwork_comments') || ! Schema::hasTable('artworks')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('artwork_comments')
|
|
->select(['id', 'user_id', 'parent_id', 'created_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('artwork_comments.user_id'))
|
|
->where('is_approved', true)
|
|
->whereNull('deleted_at')
|
|
->whereExists(function ($subquery): void {
|
|
$subquery->selectRaw('1')
|
|
->from('artworks')
|
|
->whereColumn('artworks.id', 'artwork_comments.artwork_id')
|
|
->where('artworks.is_public', true)
|
|
->where('artworks.is_approved', true)
|
|
->whereNotNull('artworks.published_at')
|
|
->whereNull('artworks.deleted_at');
|
|
})
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'comments',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => $row->parent_id ? UserActivity::TYPE_REPLY : UserActivity::TYPE_COMMENT,
|
|
'entity_type' => UserActivity::ENTITY_ARTWORK_COMMENT,
|
|
'entity_id' => (int) $row->id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillArtworkLikes(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('artwork_likes') || ! Schema::hasTable('artworks')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('artwork_likes')
|
|
->select(['id', 'user_id', 'artwork_id', 'created_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('artwork_likes.user_id'))
|
|
->whereExists(function ($subquery): void {
|
|
$subquery->selectRaw('1')
|
|
->from('artworks')
|
|
->whereColumn('artworks.id', 'artwork_likes.artwork_id')
|
|
->where('artworks.is_public', true)
|
|
->where('artworks.is_approved', true)
|
|
->whereNotNull('artworks.published_at')
|
|
->whereNull('artworks.deleted_at');
|
|
})
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'likes',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_LIKE,
|
|
'entity_type' => UserActivity::ENTITY_ARTWORK,
|
|
'entity_id' => (int) $row->artwork_id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillArtworkFavourites(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('artwork_favourites') || ! Schema::hasTable('artworks')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('artwork_favourites')
|
|
->select(['id', 'user_id', 'artwork_id', 'created_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('artwork_favourites.user_id'))
|
|
->whereExists(function ($subquery): void {
|
|
$subquery->selectRaw('1')
|
|
->from('artworks')
|
|
->whereColumn('artworks.id', 'artwork_favourites.artwork_id')
|
|
->where('artworks.is_public', true)
|
|
->where('artworks.is_approved', true)
|
|
->whereNotNull('artworks.published_at')
|
|
->whereNull('artworks.deleted_at');
|
|
})
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'favourites',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_FAVOURITE,
|
|
'entity_type' => UserActivity::ENTITY_ARTWORK,
|
|
'entity_id' => (int) $row->artwork_id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillFollows(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('user_followers')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('user_followers')
|
|
->select(['id', 'follower_id', 'user_id', 'created_at'])
|
|
->where('follower_id', '>', 0)
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('user_followers.follower_id'))
|
|
->whereExists($this->existingUserSubquery('user_followers.user_id'))
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('follower_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'follows',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->follower_id,
|
|
'type' => UserActivity::TYPE_FOLLOW,
|
|
'entity_type' => UserActivity::ENTITY_USER,
|
|
'entity_id' => (int) $row->user_id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillAchievements(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('user_achievements')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('user_achievements')
|
|
->select(['id', 'user_id', 'achievement_id', 'unlocked_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('user_achievements.user_id'))
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'achievements',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_ACHIEVEMENT,
|
|
'entity_type' => UserActivity::ENTITY_ACHIEVEMENT,
|
|
'entity_id' => (int) $row->achievement_id,
|
|
'meta' => null,
|
|
'created_at' => $row->unlocked_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillForumThreads(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('forum_threads')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('forum_threads')
|
|
->select(['id', 'user_id', 'created_at'])
|
|
->where('user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('forum_threads.user_id'))
|
|
->where('visibility', 'public')
|
|
->whereNull('deleted_at')
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'forum_posts',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_FORUM_POST,
|
|
'entity_type' => UserActivity::ENTITY_FORUM_THREAD,
|
|
'entity_id' => (int) $row->id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillForumReplies(int $chunk, ?int $userId, bool $dryRun): array
|
|
{
|
|
if (! Schema::hasTable('forum_posts') || ! Schema::hasTable('forum_threads')) {
|
|
return $this->emptyStats();
|
|
}
|
|
|
|
$query = DB::table('forum_posts')
|
|
->select(['forum_posts.id', 'forum_posts.user_id', 'forum_posts.created_at'])
|
|
->join('forum_threads', 'forum_threads.id', '=', 'forum_posts.thread_id')
|
|
->where('forum_posts.user_id', '>', 0)
|
|
->whereExists($this->existingUserSubquery('forum_posts.user_id'))
|
|
->whereNull('forum_posts.deleted_at')
|
|
->where('forum_threads.visibility', 'public')
|
|
->whereNull('forum_threads.deleted_at')
|
|
->whereRaw('forum_posts.id <> (SELECT MIN(fp2.id) FROM forum_posts as fp2 WHERE fp2.thread_id = forum_posts.thread_id)')
|
|
->when(Schema::hasColumn('forum_posts', 'flagged'), fn (Builder $builder) => $builder->where('forum_posts.flagged', false))
|
|
->when($userId !== null, fn (Builder $builder) => $builder->where('forum_posts.user_id', $userId));
|
|
|
|
return $this->backfillRows(
|
|
label: 'forum_replies',
|
|
query: $query,
|
|
chunk: $chunk,
|
|
chunkColumn: 'forum_posts.id',
|
|
mapper: static fn (object $row): ?array => [
|
|
'user_id' => (int) $row->user_id,
|
|
'type' => UserActivity::TYPE_FORUM_REPLY,
|
|
'entity_type' => UserActivity::ENTITY_FORUM_POST,
|
|
'entity_id' => (int) $row->id,
|
|
'meta' => null,
|
|
'created_at' => $row->created_at,
|
|
],
|
|
dryRun: $dryRun,
|
|
chunkAlias: 'id',
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @param callable(object): ?array{user_id:int,type:string,entity_type:string,entity_id:int,meta:?array,created_at:mixed} $mapper
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function backfillRows(
|
|
string $label,
|
|
Builder $query,
|
|
int $chunk,
|
|
string $chunkColumn,
|
|
callable $mapper,
|
|
bool $dryRun,
|
|
?string $chunkAlias = null,
|
|
): array {
|
|
$stats = $this->emptyStats();
|
|
|
|
$query->chunkById($chunk, function (Collection $rows) use (&$stats, $mapper, $dryRun): void {
|
|
$stats['processed'] += $rows->count();
|
|
|
|
$entries = $rows
|
|
->map($mapper)
|
|
->filter(static fn (?array $entry): bool => $entry !== null && (int) ($entry['user_id'] ?? 0) > 0 && (int) ($entry['entity_id'] ?? 0) > 0 && ! empty($entry['created_at']))
|
|
->values();
|
|
|
|
if ($entries->isEmpty()) {
|
|
$stats['skipped'] += $rows->count();
|
|
return;
|
|
}
|
|
|
|
$existing = $this->existingKeysForEntries($entries);
|
|
$pending = [];
|
|
|
|
foreach ($entries as $entry) {
|
|
$key = $this->entryKey($entry['user_id'], $entry['type'], $entry['entity_type'], $entry['entity_id']);
|
|
if (isset($existing[$key])) {
|
|
$stats['existing']++;
|
|
continue;
|
|
}
|
|
|
|
$pending[] = [
|
|
'user_id' => (int) $entry['user_id'],
|
|
'type' => (string) $entry['type'],
|
|
'entity_type' => (string) $entry['entity_type'],
|
|
'entity_id' => (int) $entry['entity_id'],
|
|
'meta' => $entry['meta'] !== null
|
|
? json_encode($entry['meta'], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_THROW_ON_ERROR)
|
|
: null,
|
|
'created_at' => $entry['created_at'],
|
|
];
|
|
}
|
|
|
|
if ($pending === []) {
|
|
return;
|
|
}
|
|
|
|
if ($dryRun) {
|
|
$stats['inserted'] += count($pending);
|
|
return;
|
|
}
|
|
|
|
DB::table('user_activities')->insert($pending);
|
|
$stats['inserted'] += count($pending);
|
|
|
|
collect($pending)
|
|
->pluck('user_id')
|
|
->unique()
|
|
->each(fn (int $userId): bool => tap(true, fn () => $this->activities->invalidateUserFeed($userId)));
|
|
}, $chunkColumn, $chunkAlias);
|
|
|
|
$this->line(sprintf('%s backfill complete.', $label));
|
|
|
|
return $stats;
|
|
}
|
|
|
|
/**
|
|
* @param Collection<int, array{user_id:int,type:string,entity_type:string,entity_id:int,meta:?array,created_at:mixed}> $entries
|
|
* @return array<string, true>
|
|
*/
|
|
private function existingKeysForEntries(Collection $entries): array
|
|
{
|
|
$existing = [];
|
|
|
|
$entries
|
|
->groupBy(fn (array $entry): string => $entry['type'] . '|' . $entry['entity_type'])
|
|
->each(function (Collection $groupedEntries, string $groupKey) use (&$existing): void {
|
|
[$type, $entityType] = explode('|', $groupKey, 2);
|
|
|
|
$userIds = $groupedEntries->pluck('user_id')->unique()->values()->all();
|
|
$entityIds = $groupedEntries->pluck('entity_id')->unique()->values()->all();
|
|
|
|
DB::table('user_activities')
|
|
->select(['user_id', 'entity_id'])
|
|
->where('type', $type)
|
|
->where('entity_type', $entityType)
|
|
->whereIn('user_id', $userIds)
|
|
->whereIn('entity_id', $entityIds)
|
|
->get()
|
|
->each(function (object $row) use (&$existing, $type, $entityType): void {
|
|
$existing[$this->entryKey((int) $row->user_id, $type, $entityType, (int) $row->entity_id)] = true;
|
|
});
|
|
});
|
|
|
|
return $existing;
|
|
}
|
|
|
|
private function entryKey(int $userId, string $type, string $entityType, int $entityId): string
|
|
{
|
|
return $userId . ':' . $type . ':' . $entityType . ':' . $entityId;
|
|
}
|
|
|
|
private function existingUserSubquery(string $column): \Closure
|
|
{
|
|
return static function ($subquery) use ($column): void {
|
|
$subquery->selectRaw('1')
|
|
->from('users')
|
|
->whereColumn('users.id', $column);
|
|
};
|
|
}
|
|
|
|
/**
|
|
* @return array{processed:int, inserted:int, existing:int, skipped:int}
|
|
*/
|
|
private function emptyStats(): array
|
|
{
|
|
return [
|
|
'processed' => 0,
|
|
'inserted' => 0,
|
|
'existing' => 0,
|
|
'skipped' => 0,
|
|
];
|
|
}
|
|
}
|