<?php

namespace App\Console\Commands;

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

class MigrateFeaturedWorks extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'migrate:featured-works {--dry-run : Do not write any rows} {--limit=0 : Stop after inserting this many rows} {--legacy-connection=legacy : name of legacy DB connection} {--legacy-table=featured_works : legacy table name} {--start-id=0 : Start processing from this legacy featured_id} {--chunk=500 : Chunk size for processing}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Migrate rows from legacy featured_works into artwork_features safely';

    public function handle()
    {
        $dryRun = $this->option('dry-run');
        $limit = (int) $this->option('limit');

        $this->info('Starting migration from `featured_works` to `artwork_features`');
        if ($dryRun) {
            $this->info('Running in dry-run mode; no inserts will be performed.');
        }

        $inserted = 0;
        $skipped = 0;
        $total = 0;

        $startId = (int) $this->option('start-id');
        $chunk = (int) $this->option('chunk');

        $mapping = [
            3 => 10, // Gold -> high priority
            2 => 20, // Silver
            1 => 30, // Bronze
            4 => 50, // Featured
            0 => 100 // default
        ];

        $legacyConn = $this->option('legacy-connection');
        $legacyTable = $this->option('legacy-table');

        $this->info("Reading from legacy connection '{$legacyConn}' table '{$legacyTable}'");

        $query = DB::connection($legacyConn)->table($legacyTable)->orderBy('featured_id');
        if ($startId > 0) {
            $this->info("Resuming from featured_id >= {$startId}");
            $query = $query->where('featured_id', '>=', $startId);
        }

        $query->chunkById($chunk, function ($rows) use (&$inserted, &$skipped, &$total, $dryRun, $limit, $mapping) {
            foreach ($rows as $row) {
                $total++;

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

                $artworkId = isset($row->artwork_id) ? (int) $row->artwork_id : 0;

                if ($artworkId <= 0) {
                    $skipped++;
                    continue;
                }

                // Verify artwork exists
                $exists = DB::table('artworks')->where('id', $artworkId)->exists();
                if (! $exists) {
                    $skipped++;
                    continue;
                }

                // Avoid duplicate active feature for same artwork
                $already = DB::table('artwork_features')
                    ->where('artwork_id', $artworkId)
                    ->where('is_active', true)
                    ->exists();

                if ($already) {
                    $skipped++;
                    continue;
                }

                // Determine featured_at
                $postDate = $row->post_date ?? null;
                if (empty($postDate) || $postDate === '0000-00-00' || $postDate === '0000-00-00 00:00:00') {
                    $featuredAt = Carbon::now();
                } else {
                    try {
                        $featuredAt = Carbon::parse($postDate);
                    } catch (\Throwable $e) {
                        $featuredAt = Carbon::now();
                    }
                }

                // Map priority from legacy 'type'
                $type = isset($row->type) ? (int) $row->type : 0;
                $priority = $mapping[$type] ?? 100;

                // Validate created_by: only set if a valid user id exists in new users table
                $createdBy = isset($row->user_id) ? (int) $row->user_id : null;
                if ($createdBy <= 0 || ! DB::table('users')->where('id', $createdBy)->exists()) {
                    $createdBy = null;
                }

                $record = [
                    'artwork_id' => $artworkId,
                    'featured_at' => $featuredAt->toDateTimeString(),
                    'expires_at' => null,
                    'priority' => $priority,
                    'label' => null,
                    'note' => $row->description ?? null,
                    'is_active' => 1,
                    'created_by' => $createdBy,
                    'created_at' => Carbon::now()->toDateTimeString(),
                    'updated_at' => Carbon::now()->toDateTimeString(),
                ];

                if ($dryRun) {
                    $this->line('[dry] Insert: artwork_id=' . $artworkId . ' featured_at=' . $record['featured_at'] . ' priority=' . $priority);
                    $inserted++;
                    continue;
                }

                try {
                    DB::table('artwork_features')->insert($record);
                    $inserted++;
                } catch (\Throwable $e) {
                    $this->error('Failed to insert artwork_id=' . $artworkId . ' : ' . $e->getMessage());
                    $skipped++;
                }
            }

            // Return true to continue, false to stop chunking
            return ($limit > 0 && $inserted >= $limit) ? false : true;
        }, 'featured_id');

        $this->info("Done. Processed: {$total}, Inserted: {$inserted}, Skipped: {$skipped}");

        return 0;
    }
}