<?php

namespace App\Console\Commands;

use App\Helpers\MigrationHelpers;
use Illuminate\Console\Attributes\Description;
use Illuminate\Console\Attributes\Signature;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

#[Signature('migrate:news:execute {--limit=}')]
#[Description("Migrate WP news to news table")]
class MigrateNewsExecute extends Command
{
    private const array STATE_MAP = [
        'draft' => 'draft',
        'pending' => 'pending',
        'publish' => 'published',
        'private' => 'hidden',
        'locked' => 'locked',
    ];

    private array $stats = [];

    private function uniqueSlug(string $baseSlug, string $table, ?int $ignoreId = null): string
    {
        $slug = $baseSlug;
        $i = 1;
        while (
            DB::table($table)->where('slug', $slug)
                ->when($ignoreId, fn ($q) => $q->where('id', '!=', $ignoreId))
                ->exists() && $i < 100
        ) {
            $slug = $baseSlug . '-' . $i++;
        }
        if ($i >= 100) {
            $slug = (string) Str::uuid();
        }
        return $slug;
    }

    private function migrateNews($post): void
    {
        $exists = DB::table('migrations_logs')
            ->where('source_system', 'wp')
            ->where('source_table', 'wp_posts__news')
            ->where('source_id', $post->ID)
            ->exists();
        if ($exists)
            return;

        $meta = DB::connection('old_wp')
            ->table('postmeta')
            ->where('post_id', $post->ID)
            ->whereIn('meta_key', ['release_site', 'romhacks_page', 'youtube_video' ])
            ->pluck('meta_value', 'meta_key');

        $categoryTtId = DB::connection('old_wp')
            ->table('term_relationships as tr')
            ->join('term_taxonomy as tt', 'tr.term_taxonomy_id', '=', 'tt.term_taxonomy_id')
            ->where('tr.object_id', $post->ID)
            ->where('tt.taxonomy', 'news-category')
            ->value('tt.term_taxonomy_id');

        $categoryId = null;
        if( $categoryTtId ){
            $categoryId = DB::table('migrations_logs')
                ->where('source_system', 'wp')
                ->where('target_table', 'categories' )
                ->where('source_id', $categoryTtId)
                ->value('target_id');
            if( !$categoryId ){
                $this->stats['missing_category']++;
            }
        }

        $userId = DB::table('migration_user_plan')
            ->where('wp_user_id', $post->post_author )
            ->value('user_id');

        if( !$userId ){
            $this->stats['missing_author']++;
            return;
        }

        $slug = $this->uniqueSlug(rawurldecode($post->post_name), 'news');
        $description = trim($post->post_content) === '' ? '' : MigrationHelpers::htmlToMarkdown($post->post_content);

        if( isset( $meta['romhacks_page'] ) && $meta['romhacks_page'] !== null && $meta['romhacks_page'] !== '' ){
            $description .= "\n\nLink to: " . $meta['romhacks_page'];
        }

        $newsId = DB::table('news')
            ->insertGetId([
                'title' => $post->post_title,
                'slug' => $slug,
                'category_id' => $categoryId,
                'description' => $description,
                'state' => self::STATE_MAP[$post->post_status],
                'entry_id' => null,
                'relevant_link' => $meta['release_site'] ?? null,
                'youtube_link' => $meta['youtube_video'] ?? null,
                'user_id' => $userId,
                'created_at' => $post->post_date,
                'updated_at' => $post->post_modified,
            ]);

        DB::table('migrations_logs')->insert([
            'source_system' => 'wp',
            'source_table' => 'wp_posts__news',
            'source_id' => $post->ID,
            'target_table' => 'news',
            'target_id' => $newsId,
            'status' => 'done',
            'migrated_at' => now(),
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        $this->stats['created']++;
    }

    public function handle()
    {
        $this->stats = [ 'created' => 0, 'missing_author' => 0, 'missing_category' => 0 ];

        $query = DB::connection('old_wp')
            ->table('posts')
            ->where('post_type', 'news')
            ->whereIn('post_status', array_keys(self::STATE_MAP))
        ;

        if( $limit = $this->option('limit') ) {
            $query->limit((int)$limit);
        }

        $posts = $query->select('ID', 'post_title', 'post_name', 'post_content', 'post_author', 'post_status', 'post_date', 'post_modified')->get();

        $this->info( "{$posts->count()} posts found" );

        $this->withProgressBar($posts, fn($post) => $this->migrateNews($post) );

        $this->newLine();
        $this->info( "Created {$this->stats['created']} posts. Missing authors: {$this->stats['missing_author']}. Missing category: {$this->stats['missing_category']}" );

        return self::SUCCESS;
    }
}