<?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);
        }
    }
}