<?php

declare(strict_types=1);

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;
use Carbon\Carbon;

class RepairLegacyWallzUsersCommand extends Command
{
    protected $signature = 'skinbase:repair-legacy-wallz-users
        {--chunk=500 : Number of legacy wallz rows to scan per batch}
        {--legacy-connection=legacy : Legacy database connection name}
        {--legacy-table=wallz : Legacy table to update}
        {--artworks-table=artworks : Current DB artworks table name}
        {--fix-artworks : Backfill `artworks.user_id` from legacy `wallz.user_id` for rows where user_id = 0}
        {--dry-run : Preview matches and inserts without writing changes}';

    protected $description = 'Backfill legacy wallz.user_id from uname by matching or creating users in the new users table';

    public function handle(): int
    {
        $chunk = max(1, (int) $this->option('chunk'));
        $legacyConnection = (string) $this->option('legacy-connection');
        $legacyTable = (string) $this->option('legacy-table');
        $artworksTable = (string) $this->option('artworks-table');
        $fixArtworks = (bool) $this->option('fix-artworks');
        $dryRun = (bool) $this->option('dry-run');

        if (! $this->legacyTableExists($legacyConnection, $legacyTable)) {
            $this->error("Legacy table {$legacyConnection}.{$legacyTable} does not exist or the connection is unavailable.");

            return self::FAILURE;
        }

        if ($dryRun) {
            $this->warn('[DRY RUN] No changes will be written.');
        }

        if ($fixArtworks) {
            $this->handleFixArtworks($chunk, $legacyConnection, $legacyTable, $artworksTable, $dryRun);
        }

        $total = (int) DB::connection($legacyConnection)
            ->table($legacyTable)
            ->where('user_id', 0)
            ->count();

        if ($total === 0) {
            if (! $fixArtworks) {
                $this->info('No legacy wallz rows with user_id = 0 were found.');
            }

            return self::SUCCESS;
        }

        $this->info("Scanning {$total} legacy rows in {$legacyConnection}.{$legacyTable}.");

        $processed = 0;
        $updatedRows = 0;
        $matchedUsers = 0;
        $createdUsers = 0;
        $skippedRows = 0;
        $usernameMap = [];

        DB::connection($legacyConnection)
            ->table($legacyTable)
            ->select(['id', 'uname'])
            ->where('user_id', 0)
            ->orderBy('id')
            ->chunkById($chunk, function ($rows) use (
                &$processed,
                &$updatedRows,
                &$matchedUsers,
                &$createdUsers,
                &$skippedRows,
                &$usernameMap,
                $dryRun,
                $legacyConnection,
                $legacyTable
            ) {
                foreach ($rows as $row) {
                    $processed++;

                    $rawUsername = trim((string) ($row->uname ?? ''));
                    if ($rawUsername === '') {
                        $skippedRows++;
                        $this->warn("Skipping wallz id={$row->id}: uname is empty.");
                        continue;
                    }

                    $lookupKey = UsernamePolicy::normalize($rawUsername);
                    if ($lookupKey === '') {
                        $skippedRows++;
                        $this->warn("Skipping wallz id={$row->id}: uname normalizes to empty.");
                        continue;
                    }

                    if (! array_key_exists($lookupKey, $usernameMap)) {
                        $existingUser = $this->findUserByUsername($lookupKey);

                        if ($existingUser !== null) {
                            $usernameMap[$lookupKey] = [
                                'user_id' => (int) $existingUser->id,
                                'created' => false,
                            ];
                        } else {
                            $usernameMap[$lookupKey] = [
                                    'user_id' => $dryRun
                                        ? 0
                                        : $this->createUserForLegacyUsername($rawUsername, $legacyConnection),
                                    'created' => true,
                                ];
                        }
                    }

                    $resolved = $usernameMap[$lookupKey];

                    if ($resolved['created']) {
                        $createdUsers++;
                        $usernameMap[$lookupKey]['created'] = false;
                        $resolved['created'] = false;
                        $this->line($dryRun
                            ? "[dry] Would create user for uname='{$rawUsername}'"
                            : "[create] Created user_id={$usernameMap[$lookupKey]['user_id']} for uname='{$rawUsername}'");
                    } else {
                        $matchedUsers++;
                    }

                    if ($dryRun) {
                        $targetUser = $usernameMap[$lookupKey]['user_id'] > 0
                            ? (string) $usernameMap[$lookupKey]['user_id']
                            : '<new-user-id>';
                        $this->line("[dry] Would update wallz id={$row->id} to user_id={$targetUser} using uname='{$rawUsername}'");
                        $updatedRows++;
                        continue;
                    }

                    $affected = DB::connection($legacyConnection)
                        ->table($legacyTable)
                        ->where('id', $row->id)
                        ->where('user_id', 0)
                        ->update([
                            'user_id' => $usernameMap[$lookupKey]['user_id'],
                        ]);

                    if ($affected > 0) {
                        $updatedRows += $affected;
                    }
                }
            }, 'id');

        $this->info(sprintf(
            'Finished. processed=%d updated=%d matched=%d created=%d skipped=%d',
            $processed,
            $updatedRows,
            $matchedUsers,
            $createdUsers,
            $skippedRows
        ));

        return self::SUCCESS;
    }

    private function handleFixArtworks(int $chunk, string $legacyConnection, string $legacyTable, string $artworksTable, bool $dryRun): void
    {
        $this->info("\nAttempting to backfill `{$artworksTable}.user_id` from legacy {$legacyConnection}.{$legacyTable} where user_id = 0");

        $total = (int) DB::table($artworksTable)->where('user_id', 0)->count();
        $this->info("Found {$total} rows in {$artworksTable} with user_id = 0. Chunk size: {$chunk}.");

        $processed = 0;
        $updated = 0;
        DB::table($artworksTable)
            ->select(['id'])
            ->where('user_id', 0)
            ->orderBy('id')
            ->chunkById($chunk, function ($rows) use (&$processed, &$updated, $legacyConnection, $legacyTable, $artworksTable, $dryRun) {
                foreach ($rows as $row) {
                    $processed++;

                    $legacyUser = DB::connection($legacyConnection)
                        ->table($legacyTable)
                        ->where('id', $row->id)
                        ->value('user_id');

                    $legacyUser = (int) ($legacyUser ?? 0);
                    if ($legacyUser <= 0) {
                        continue;
                    }

                    if ($dryRun) {
                        $this->line("[dry] Would update {$artworksTable} id={$row->id} to user_id={$legacyUser}");
                        $updated++;
                        continue;
                    }

                    $affected = DB::table($artworksTable)
                        ->where('id', $row->id)
                        ->where('user_id', 0)
                        ->update(['user_id' => $legacyUser]);

                    if ($affected > 0) {
                        $updated += $affected;
                    }
                }
            }, 'id');

        $this->info(sprintf('Artworks backfill complete. processed=%d updated=%d', $processed, $updated));
    }

    private function legacyTableExists(string $connection, string $table): bool
    {
        try {
            return DB::connection($connection)->getSchemaBuilder()->hasTable($table);
        } catch (\Throwable) {
            return false;
        }
    }

    private function findUserByUsername(string $normalizedUsername): ?object
    {
        return DB::table('users')
            ->select(['id', 'username'])
            ->whereRaw('LOWER(username) = ?', [$normalizedUsername])
            ->first();
    }

    private function createUserForLegacyUsername(string $legacyUsername, string $legacyConnection): int
    {
        $username = UsernamePolicy::uniqueCandidate($legacyUsername);
        $emailLocal = $this->sanitizeEmailLocal($username);
        $email = $this->uniqueEmailCandidate($emailLocal . '@users.skinbase.org');
        $now = now();

        // Attempt to copy legacy joinDate from the legacy `users` table when available.
        $legacyJoin = null;
        try {
            $legacyJoin = DB::connection($legacyConnection)
                ->table('users')
                ->whereRaw('LOWER(uname) = ?', [strtolower((string) $legacyUsername)])
                ->value('joinDate');
        } catch (\Throwable) {
            $legacyJoin = null;
        }

        $createdAt = $now;
        if (! empty($legacyJoin) && strpos((string) $legacyJoin, '0000') !== 0) {
            try {
                $createdAt = Carbon::parse($legacyJoin);
            } catch (\Throwable) {
                $createdAt = $now;
            }
        }

        $userId = (int) DB::table('users')->insertGetId([
            'username' => $username,
            'username_changed_at' => $now,
            'name' => $legacyUsername,
            'email' => $email,
            'password' => Hash::make(Str::random(64)),
            'is_active' => true,
            'needs_password_reset' => true,
            'role' => 'user',
            'legacy_password_algo' => null,
            'created_at' => $createdAt,
            'updated_at' => $now,
        ]);

        return $userId;
    }

    private function uniqueEmailCandidate(string $email): string
    {
        $candidate = strtolower(trim($email));
        $suffix = 1;

        while (DB::table('users')->whereRaw('LOWER(email) = ?', [$candidate])->exists()) {
            $parts = explode('@', $email, 2);
            $local = $parts[0] ?? 'user';
            $domain = $parts[1] ?? 'users.skinbase.org';
            $candidate = $local . '+' . $suffix . '@' . $domain;
            $suffix++;
        }

        return $candidate;
    }

    private function sanitizeEmailLocal(string $value): string
    {
        $local = strtolower(trim($value));
        $local = preg_replace('/[^a-z0-9._-]/', '-', $local) ?: 'user';

        return trim($local, '.-') ?: 'user';
    }
}