<?php

namespace App\Console\Commands;

use App\Models\Article;
use App\Models\ArticleCategory;
use App\Models\ArticleTag;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class ImportWordpressPosts extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:wordpress-posts';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import posts from WordPress system';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $posts = DB::connection('wordpress')
            ->table('wp_posts')
            ->where('post_type', 'post')
            ->select('*')
            ->get();

        collect($posts)->chunk(50)->each(function ($chunk) {
            foreach ($chunk as $key => $post) {
                $postId = $post->ID;
                $postTitle = trim(html_entity_decode($post->post_title, ENT_QUOTES | ENT_HTML5, 'UTF-8'));
                $postSlug = urldecode($post->post_name);
                $postStatus = $post->post_status;

                // Post status: publish, draft, pending, private, future, trash
                if (($postTitle == 'Auto Draft' && $postStatus == 'trash') ||
                    ($postTitle == '自動下書き' && $postStatus == 'trash') ||
                    (($postTitle == '' || $postTitle == null) && $postStatus == 'trash') ||
                    $postStatus == 'auto-draft') {
                    continue;
                }

                if ($postSlug == '' || $postSlug == null) {
                    $postSlug = Str::slug($this->convertJapaneseToRomaji($postTitle));
                }

                // Lấy hình ảnh đại diện
                $thumbnail_id = DB::connection('wordpress')
                    ->table('wp_postmeta')
                    ->where('post_id', $postId)
                    ->where('meta_key', '_thumbnail_id')
                    ->value('meta_value');

                $thumbnailUrl = $thumbnail_id
                    ? DB::connection('wordpress')
                        ->table('wp_postmeta')
                        ->where('post_id', $thumbnail_id)
                        ->where('meta_key', '_wp_attached_file')
                        ->value('meta_value')
                    : null;

                // Cập nhật URL nếu cần (ví dụ: từ wp-content/uploads sang storage/uploads)
                if ($thumbnailUrl) {
                    $thumbnailUrl = str_replace('wp-content/uploads/', '', $thumbnailUrl);
                    $thumbnailUrl = '/storage/uploads/' . $thumbnailUrl;
                }

                // Lấy categories
                $categories = DB::connection('wordpress')
                    ->table('wp_term_relationships as tr')
                    ->join('wp_term_taxonomy as tt', 'tr.term_taxonomy_id', '=', 'tt.term_taxonomy_id')
                    ->where('tr.object_id', $postId)
                    ->where('tt.taxonomy', 'category')
                    ->pluck('tt.term_id')
                    ->toArray();

                // Lấy tags
                $tags = DB::connection('wordpress')
                    ->table('wp_term_relationships as tr')
                    ->join('wp_term_taxonomy as tt', 'tr.term_taxonomy_id', '=', 'tt.term_taxonomy_id')
                    ->where('tr.object_id', $postId)
                    ->where('tt.taxonomy', 'post_tag')
                    ->pluck('tt.term_id')
                    ->toArray();

                // Lấy primary category (Rank Math)
                $primaryCategoryId = DB::connection('wordpress')
                    ->table('wp_postmeta')
                    ->where('post_id', $postId)
                    ->where('meta_key', 'rank_math_primary_category')
                    ->value('meta_value');

                // Lấy số lượt xem
                $views = DB::connection('wordpress')
                    ->table('wp_postmeta')
                    ->where('post_id', $postId)
                    ->whereIn('meta_key', ['post_views_count', 'views'])
                    ->orderByRaw("CASE WHEN meta_key = 'post_views_count' THEN 1 ELSE 2 END")
                    ->value('meta_value') ?? 0;

                // Lấy meta data Rank Math
                $rank_math_meta = DB::connection('wordpress')
                    ->table('wp_postmeta')
                    ->where('post_id', $postId)
                    ->where('meta_key', 'like', 'rank_math_%')
                    ->pluck('meta_value', 'meta_key')
                    ->map(function ($value) {
                        return html_entity_decode($value, ENT_QUOTES | ENT_HTML5, 'UTF-8');
                    })
                    ->toArray();

                $postContent = trim(html_entity_decode($post->post_content, ENT_QUOTES | ENT_HTML5, 'UTF-8'));
                $postExcerpt = trim(html_entity_decode($post->post_excerpt, ENT_QUOTES | ENT_HTML5, 'UTF-8'));
                $postDescription = null;
                $focusKeyword = null;
                $robots = null;
                $canonicalUrl = null;
                $ogImage = null;
                $seoScore = null;
                $userId = $post->post_author;
                $createdAt = $post->post_date ?? now();
                $updatedAt = $post->post_modified ?? now();

                if ($postContent != null && $postContent != '') {
                    $postContent = str_replace('https://mattock.jp/blog/wp-content/uploads', '/storage/uploads', $postContent);
                    $postContent = str_replace('/wp-content/uploads', '/storage/uploads', $postContent);
                    $postContent = str_replace('wp-content/uploads', '/storage/uploads', $postContent);
                    $postContent = str_replace('https://mattock.jp/blog/', '/blog/', $postContent);
                    $postContent = str_replace('https://mattock.jp/', '/', $postContent);
                    $postContent = str_replace('https://mattock.jp', '/', $postContent);
                }

                // Lưu meta data Rank Math
                foreach ($rank_math_meta as $metaKey => $metaValue) {
                    if ($metaKey == 'rank_math_robots') {
                        $robots = $metaValue;
                    } elseif ($metaKey == 'rank_math_canonical_url') {
                        $canonicalUrl = $metaValue;
                    } elseif ($metaKey == 'rank_math_og_image') {
                        $ogImage = $metaValue;
                    } elseif ($metaKey == 'rank_math_seo_score') {
                        $seoScore = $metaValue;
                    } elseif ($metaKey == 'rank_math_description') {
                        $postDescription = $metaValue;
                    } elseif ($metaKey == 'rank_math_focus_keyword') {
                        $focusKeyword = $metaValue;
                    }
                }

                if (empty($primaryCategoryId) && count($categories) > 0) {
                    $primaryCategoryId = $categories[0];
                }

                $articleData = [
                    'id'                  => $postId,
                    'type'                => 0,
                    'title'               => $postTitle,
                    'slug'                => $postSlug,
                    'image'               => $thumbnailUrl,
                    'description'         => $postDescription,
                    'content'             => $postContent,
                    'excerpt'             => $postExcerpt,
                    'primary_category_id' => $primaryCategoryId ?? null,
                    'focus_keyword'       => $focusKeyword,
                    'robots'              => $robots,
                    'canonical_url'       => $canonicalUrl,
                    'og_image'            => $ogImage,
                    'seo_score'           => $seoScore,
                    'views'               => (int) $views,
                    'status'              => $postStatus ?? 'draft',
                    'user_id'             => $userId,
                    'wp_id'               => $postId,
                    'sync_status'         => true,
                    'sync_at'             => now(),
                    'updated_at'          => $updatedAt,
                ];

                if ($postStatus == 'trash') {
                    $articleData['deleted_at'] = $updatedAt ?? now();
                }

                $checkArticle = Article::withTrashed()->where('id', $postId)->first();
                if (! $checkArticle) {
                    $articleData['created_at'] = $createdAt;
                    $checkArticle = Article::create($articleData);
                } else {
                    $checkArticle->update($articleData);

                    // Khôi phục bài viết nếu đã bị soft delete và status không phải trash
                    if ($checkArticle->trashed() && $postStatus !== 'trash') {
                        $checkArticle->restore();
                    }
                }

                ArticleCategory::where('article_id', $checkArticle->id)->forceDelete();
                foreach ($categories as $categoryId) {
                    ArticleCategory::updateOrCreate([
                        'article_id'  => $checkArticle->id,
                        'category_id' => $categoryId,
                    ]);
                }

                ArticleTag::where('article_id', $checkArticle->id)->forceDelete();
                foreach ($tags as $tagId) {
                    ArticleTag::updateOrCreate([
                        'article_id' => $checkArticle->id,
                        'tag_id'     => $tagId,
                    ]);
                }

                $this->info("Importing post {$key}: ID: " . $checkArticle->id . ', Title: ' . $postTitle . ', Slug: ' . $postSlug . ', Status: ' . $postStatus);
            }
        });

        $this->info('Posts imported successfully!');
    }

    /**
     * Convert Japanese text to Romaji.
     *
     * @param string $string
     * @return string
     */
    private function convertJapaneseToRomaji($string, $default = 'article')
    {
        $romaji = null;
        $default = $default . '-' . uniqid();

        try {
            $kakasiOutput = shell_exec('echo ' . escapeshellarg($string) . ' | kakasi -i utf8 -Ha -Ka -Ja -Ea -ka 2>/dev/null');
            $romaji = trim($kakasiOutput);
        } catch (\Exception $e) {
            $this->error('Failed to convert Japanese to Romaji: ' . $e->getMessage());
        }

        if (empty($romaji) || $romaji === ' ') {
            $romaji = transliterator_transliterate(
                'Any-Latin; NFKD; [:Nonspacing Mark:] Remove; Lower; NFC',
                $string
            );
        }

        if (empty($romaji)) {
            $romaji = $default;
        }

        if ($romaji === $default) {
            $this->warn('Failed to convert Japanese to Romaji for string: ' . $string);
        }

        return $romaji;
    }
}