<?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,
        ];
    }
}