<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Str;

/**
 * php artisan skinbase:migrate-favourites
 *
 * Migrates rows from the legacy `favourites` table (projekti_old_skinbase)
 * into the new `artwork_favourites` table on the default connection.
 *
 * Skipped rows (logged as warnings):
 *   - artwork_id not found in new artworks table
 *   - user_id not found in new OR legacy users table (unless --import-missing-users)
 *   - row already imported (duplicate legacy_id)
 *   - would create a duplicate (user_id, artwork_id) pair
 *
 * Dropped legacy columns (not migrated):
 *   - user_type  — membership tier, not relevant to the relationship
 *   - author_id  — always derivable via artworks.user_id
 *
 * Options:
 *   --dry-run                Preview without writing
 *   --chunk=500              Rows per batch
 *   --start-id=0             Resume from this favourite_id
 *   --limit=0                Stop after N inserts (0 = no limit)
 *   --import-missing-users   Auto-create a stub user from legacy data when the
 *                            user is missing from the new DB (needs_password_reset=true)
 *   --legacy-connection      Override legacy DB connection name (default: legacy)
 *   --legacy-table           Override legacy favourites table name (default: favourites)
 *   --legacy-users-table     Override legacy users table name (default: users)
 */
class MigrateFavourites extends Command
{
    protected $signature = 'skinbase:migrate-favourites
                            {--dry-run : Preview changes without writing to the database}
                            {--chunk=500 : Number of rows to process per batch}
                            {--start-id=0 : Resume processing from this favourite_id}
                            {--limit=0 : Stop after inserting this many rows (0 = unlimited)}
                            {--import-missing-users : Auto-create stub users from legacy data when missing from new DB}
                            {--legacy-connection=legacy : Name of the legacy DB connection}
                            {--legacy-table=favourites : Name of the legacy favourites table}
                            {--legacy-users-table=users : Name of the legacy users table}';

    protected $description = 'Migrate legacy favourites into artwork_favourites.';

    // ── Counters ─────────────────────────────────────────────────────────────

    private int $inserted      = 0;
    private int $skipped       = 0;
    private int $total         = 0;
    private int $usersImported = 0;

    // ── Runtime config (set in handle()) ─────────────────────────────────────

    private bool   $importMissingUsers = false;
    private string $legacyConn        = 'legacy';
    private string $legacyUsersTable  = 'users';

    public function handle(): int
    {
        $dryRun    = (bool) $this->option('dry-run');
        $chunk     = max(1, (int) $this->option('chunk'));
        $startId   = max(0, (int) $this->option('start-id'));
        $limit     = max(0, (int) $this->option('limit'));

        $this->importMissingUsers = (bool) $this->option('import-missing-users');
        $this->legacyConn         = (string) $this->option('legacy-connection');
        $this->legacyUsersTable   = (string) $this->option('legacy-users-table');
        $legacyTable              = (string) $this->option('legacy-table');

        $this->info("Migrating <comment>{$this->legacyConn}.{$legacyTable}</comment> → <info>artwork_favourites</info>");

        if ($this->importMissingUsers) {
            $this->warn('--import-missing-users: stub users will be created with needs_password_reset=true.');
        }

        if ($dryRun) {
            $this->warn('DRY-RUN mode — no rows will be written.');
        }
        if ($startId > 0) {
            $this->line("Resuming from favourite_id >= {$startId}");
        }
        if ($limit > 0) {
            $this->line("Will stop after {$limit} inserts.");
        }

        $query = DB::connection($this->legacyConn)
            ->table($legacyTable)
            ->orderBy('favourite_id');

        if ($startId > 0) {
            $query->where('favourite_id', '>=', $startId);
        }

        $query->chunkById(
            $chunk,
            function ($rows) use ($dryRun, $limit): bool {
                foreach ($rows as $row) {
                    $this->total++;

                    if ($limit > 0 && $this->inserted >= $limit) {
                        return false; // stop chunking
                    }

                    if ($this->processRow($row, $dryRun) === false) {
                        $this->skipped++;
                    }
                }
                return true;
            },
            'favourite_id',
        );

        $this->newLine();
        $this->info(sprintf(
            'Done. %d scanned, %d %s, %d skipped%s.',
            $this->total,
            $this->inserted,
            $dryRun ? 'would be inserted' : 'inserted',
            $this->skipped,
            $this->usersImported > 0
                ? ", {$this->usersImported} stub users " . ($dryRun ? 'would be ' : '') . 'created'
                : '',
        ));

        return self::SUCCESS;
    }

    // ── Row processing ────────────────────────────────────────────────────────

    /**
     * Process a single legacy row.  Returns true on success, false when skipped.
     */
    private function processRow(object $row, bool $dryRun): bool
    {
        $legacyId  = (int) ($row->favourite_id ?? 0);
        $artworkId = (int) ($row->artwork_id   ?? 0);
        $userId    = (int) ($row->user_id      ?? 0);
        $datum     = $row->datum ?? null;

        // ── Validate IDs ────────────────────────────────────────────────────

        if ($artworkId <= 0 || $userId <= 0) {
            $this->skip($legacyId, "invalid artwork_id={$artworkId} or user_id={$userId}");
            return false;
        }

        if (! DB::table('artworks')->where('id', $artworkId)->exists()) {
            $this->skip($legacyId, "artwork #{$artworkId} not found in new DB");
            return false;
        }

        if (! DB::table('users')->where('id', $userId)->exists()) {
            if ($this->importMissingUsers) {
                if (! $this->importUserStub($userId, $dryRun)) {
                    $this->skip($legacyId, "user #{$userId} not found in legacy DB either — skipped");
                    return false;
                }
            } else {
                $this->skip($legacyId, "user #{$userId} not found in new DB (use --import-missing-users to auto-create)");
                return false;
            }
        }

        // ── Idempotency guards ───────────────────────────────────────────────

        if (DB::table('artwork_favourites')->where('legacy_id', $legacyId)->exists()) {
            // Already imported — silently skip (not counted as "skipped" error)
            return true;
        }

        if (DB::table('artwork_favourites')
                ->where('user_id', $userId)
                ->where('artwork_id', $artworkId)
                ->exists()
        ) {
            $this->skip($legacyId, "duplicate (user={$userId}, artwork={$artworkId}) already exists");
            return false;
        }

        // ── Map timestamp ────────────────────────────────────────────────────

        $createdAt = $this->parseDate($datum);

        // ── Insert ───────────────────────────────────────────────────────────

        if (! $dryRun) {
            DB::table('artwork_favourites')->insert([
                'user_id'    => $userId,
                'artwork_id' => $artworkId,
                'legacy_id'  => $legacyId,
                'created_at' => $createdAt,
                'updated_at' => $createdAt,
            ]);
        }

        $this->inserted++;

        if ($this->inserted % 500 === 0) {
            $this->line("  {$this->inserted} inserted, {$this->skipped} skipped…");
        }

        return true;
    }

    // ── Helpers ───────────────────────────────────────────────────────────────

    /**
     * Look up $userId in the legacy users table and create a stub record in
     * the new users table preserving the same primary key.
     *
     * The stub has:
     *   - needs_password_reset = true  (user must reset before logging in)
     *   - legacy_password_algo = 'legacy'  (marks imported credential)
     *   - is_active determined from legacy `active` flag
     *   - email placeholder if original email is null or already taken
     *
     * @return bool  true = stub created (or already existed), false = not in legacy DB
     */
    private function importUserStub(int $userId, bool $dryRun): bool
    {
        // Already exists — nothing to do.
        if (DB::table('users')->where('id', $userId)->exists()) {
            return true;
        }

        $legacyUser = DB::connection($this->legacyConn)
            ->table($this->legacyUsersTable)
            ->where('user_id', $userId)
            ->first();

        if (! $legacyUser) {
            return false;
        }

        // ── Map fields ──────────────────────────────────────────────────────

        $username = trim((string) ($legacyUser->uname ?? '')) ?: "user_{$userId}";

        // Ensure username is unique in the new DB.
        if (DB::table('users')->where('username', $username)->exists()) {
            $username = $username . '_' . $userId;
        }

        $name  = trim((string) ($legacyUser->real_name ?? '')) ?: $username;
        $email = trim((string) ($legacyUser->email     ?? ''));

        // Resolve email: use placeholder when blank or already taken.
        if ($email === '' || DB::table('users')->where('email', $email)->exists()) {
            $email = "legacy_{$userId}@legacy.skinbase.org";
        }

        $isActive  = ((int) ($legacyUser->active ?? 0)) === 1;
        $createdAt = $this->parseDate($legacyUser->joinDate   ?? null);
        $lastVisit = $this->parseDate($legacyUser->LastVisit  ?? null);

        $stub = [
            'id'                    => $userId,
            'username'              => $username,
            'name'                  => $name,
            'email'                 => $email,
            'password'              => bcrypt(Str::random(48)), // unusable random password
            'needs_password_reset'  => true,
            'legacy_password_algo'  => 'legacy',
            'is_active'             => $isActive,
            'role'                  => 'user',
            'last_visit_at'         => $lastVisit !== $createdAt ? $lastVisit : null,
            'created_at'            => $createdAt,
            'updated_at'            => $createdAt,
        ];

        $msg = "Stub user created: #{$userId} ({$username}, {$email})";

        if ($dryRun) {
            $this->line("  [dry] {$msg}");
            $this->usersImported++;
            return true;
        }

        try {
            // Force explicit ID insert — MySQL respects it even with auto_increment.
            DB::table('users')->insert($stub);
            $this->usersImported++;
            $this->line("  <info>{$msg}</info>");
            Log::info("skinbase:migrate-favourites {$msg}");
        } catch (\Throwable $e) {
            $err = "Failed to create stub user #{$userId}: {$e->getMessage()}";
            $this->warn("  {$err}");
            Log::error("skinbase:migrate-favourites {$err}");
            return false;
        }

        return true;
    }

    /**
     * Parse a legacy date value (DATE string / null / zero-date) to a
     * full datetime string safe for MySQL.
     */
    private function parseDate(mixed $value): string
    {
        if (empty($value) || $value === '0000-00-00' || $value === '0000-00-00 00:00:00') {
            return Carbon::now()->toDateTimeString();
        }

        try {
            return Carbon::parse((string) $value)->toDateTimeString();
        } catch (\Throwable) {
            return Carbon::now()->toDateTimeString();
        }
    }

    private function skip(int $legacyId, string $reason): void
    {
        $msg = "SKIP favourite#{$legacyId}: {$reason}";
        $this->warn("  {$msg}");
        Log::warning("skinbase:migrate-favourites {$msg}");
    }
}
