<?php

namespace App\Console\Commands;

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

class ImportWordpressPostsLost extends Command
{
	/**
	 * The name and signature of the console command.
	 *
	 * @var string
	 */
	protected $signature = 'import:wordpress-posts-updated';
	
	/**
	 * 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();
		
		$count = 0;
		$ids = [];
		collect($posts)->chunk(50)->each(function ($chunk) use (&$count, &$ids) {
			foreach ($chunk as $key => $post) {
				$postId = $post->ID;
				$postTitle = trim(html_entity_decode($post->post_title, ENT_QUOTES | ENT_HTML5, 'UTF-8'));
				$postSlug = $post->post_name; //urldecode($post->post_name);
				$postStatus = $post->post_status;
				
				// Post status: publish, draft, pending, private, future, trash
				if (
					$postStatus == '' ||
					$postStatus == null ||
					$postStatus == "auto-draft" ||
					$postStatus == "trash" ||
					$postTitle == "Test post with meta information" ||
					$postTitle == "testこれはテスト" ||
					$postTitle == "test category post" ||
					$postTitle == "" ||
					$postTitle == null ||
					(($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));
					$postSlug = rtrim(substr($postSlug, 0, 200), '-_');
					
					$existingSlug = Article::where('slug', $postSlug)->exists();
					if ($existingSlug) {
						$postSlug = $postSlug . '-' . uniqid();
					}
				}
				
				//$check = Article::where('slug', $postSlug)->exists();
				$check = Article::where('wp_id', $postId)->exists();
				
				if (!$check) {
					$count++;
					$ids[] = $postId;
					
					// 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();
					
					$checkUser = User::where('id', $userId)->exists();
					if (!$checkUser) {
						$wpUser = DB::connection('wordpress')
							->table('wp_users')
							->leftJoin('wp_usermeta', function ($join) {
								$join->on('wp_users.ID', '=', 'wp_usermeta.user_id')
									->where('wp_usermeta.meta_key', '=', 'wp_capabilities');
							})
							->select('wp_users.*', 'wp_usermeta.meta_value as roles')
							->where('wp_users.ID', $userId)
							->first();
						
						
						$checkEmail = User::where('email', $wpUser->user_email)->first();
						if ($checkEmail) {
							$userId = $checkEmail->id;
						} else {
							$roles = unserialize($wpUser->roles) ?: [];
							$roleName = 'subscriber';
							if (isset($roles)) {
								foreach ($roles as $keyRole => $role) {
									$roleName = $keyRole;
									break;
								}
							}
							
							$rawPassword = $this->generateRandomPassword(16);
							
							$newUser = User::create([
								'id'                => $wpUser->ID,
								'name'              => $wpUser->user_login,
								'email'             => $wpUser->user_email,
								'password'          => bcrypt($rawPassword),
								'email_verified_at' => now(),
								'created_at'        => $wpUser->user_registered,
								'updated_at'        => $wpUser->user_registered,
							]);
							
							$newUser->syncRoles([]);
							$newUser->assignRole($roleName);
							$userId = $newUser->id;
							
							\Log::info('[Users] CSV created: ' . $wpUser->user_email . ',' . $rawPassword . ', ' . $roleName);
							\Log::info('Thiếu thông tin tác giả từ WordPress: ', [
								'role_name'    => $roleName,
								'wp_id'        => $wpUser->ID,
								'user_login'   => $wpUser->user_login,
								'user_email'   => $wpUser->user_email,
								'display_name' => $wpUser->display_name,
							]);
						}
					}
					
					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('Missing ' . $count . ' posts: [' . implode(',', $ids) . ']');
	}
	
	/**
	 * 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;
	}
	
	private function generateRandomPassword($length = 10)
	{
		$characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
		$charactersLength = strlen($characters);
		$randomString = '';
		
		// Generate secure random bytes
		$bytes = random_bytes($length);
		
		// Map random bytes to characters
		for ($i = 0; $i < $length; $i++) {
			$randomString .= $characters[ord($bytes[$i]) % $charactersLength];
		}
		
		return $randomString;
	}
}