<?php
class XC_Queries {

	// Total of Posts
	public static function get_posts_total() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		
		$query_posts_total = $wpdb->prepare("
			SELECT COUNT(ID) 
			FROM {$wpdb->posts}
			WHERE post_type = %s 
			AND post_status = %s
		", $post_type, $post_status);
		
		$cache_key   = 'posts_total_cache_' . md5($query_posts_total);
		$cache_group = 'my_cache_group';
	
		$total_posts = wp_cache_get($cache_key, $cache_group);
		if ($total_posts === false) {
			$total_posts = (int) $wpdb->get_var($query_posts_total);
			wp_cache_set($cache_key, $total_posts, $cache_group, 3600);
		}
	
		return $total_posts;
	}

	// Posts by Type
	public static function get_posts_by_type() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		
		$query_posts_type = $wpdb->prepare("
			SELECT 
				(SELECT 
					COUNT(*) 
					FROM {$wpdb->posts} 
					WHERE {$wpdb->posts}.post_type = %s 
					AND {$wpdb->posts}.post_status = %s
				) AS total, 
				COUNT(*) AS posts, 
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 'F (- 400)' 
					WHEN words BETWEEN 401 AND 800 THEN 'E (401 - 800)' 
					WHEN words BETWEEN 801 AND 1200 THEN 'D (801 - 1200)' 
					WHEN words BETWEEN 1201 AND 1600 THEN 'C (1201 - 1600)' 
					WHEN words BETWEEN 1601 AND 2000 THEN 'B (1601 - 2000)' 
					ELSE 'A (2001 +)' 
				END AS type,
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 6 
					WHEN words BETWEEN 401 AND 800 THEN 5 
					WHEN words BETWEEN 801 AND 1200 THEN 4 
					WHEN words BETWEEN 1201 AND 1600 THEN 3 
					WHEN words BETWEEN 1601 AND 2000 THEN 2 
					ELSE 1 
				END AS type_order
			FROM 
				(SELECT 
					LENGTH({$wpdb->posts}.post_content) - LENGTH(REPLACE({$wpdb->posts}.post_content, ' ', '')) + 1 AS words 
				FROM {$wpdb->posts} 
				WHERE {$wpdb->posts}.post_type = %s 
				AND {$wpdb->posts}.post_status = %s
				) AS temp 
			GROUP BY type, type_order
			ORDER BY posts DESC, type_order ASC;
		", $post_type, $post_status, $post_type, $post_status);
	
		$cache_key   = 'posts_type_cache_' . md5($query_posts_type);
		$cache_group = 'my_cache_group';
	
		$type_by_posts = wp_cache_get($cache_key, $cache_group);
		if ($type_by_posts === false) {
			$type_by_posts = $wpdb->get_results($query_posts_type, OBJECT);
			wp_cache_set($cache_key, $type_by_posts, $cache_group, 3600);
		}
	
		return $type_by_posts;
	}

	// Posts by Month
	public static function get_posts_by_month() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		$limit       = 12;
		
		$query_posts_month = $wpdb->prepare("
			SELECT 
				YEAR(post_date) AS year,
				MONTH(post_date) AS nmonth,
				COUNT(*) AS published
			FROM {$wpdb->posts}
			WHERE post_type = %s AND post_status = %s
			GROUP BY year, nmonth
			ORDER BY year DESC, nmonth DESC
			LIMIT %d;
		", $post_type, $post_status, $limit);
	
		$cache_key   = 'posts_month_cache_' . md5($query_posts_month);
		$cache_group = 'my_cache_group';
		
		$month_by_posts = wp_cache_get($cache_key, $cache_group);
		if ($month_by_posts === false) {
			$month_by_posts = $wpdb->get_results($query_posts_month, OBJECT);
			wp_cache_set($cache_key, $month_by_posts, $cache_group, 3600);
		}
		
		return $month_by_posts;
	}

	// Posts by Author
	public static function get_posts_by_author() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		$limit       = 12;
		
		$query_posts_author = $wpdb->prepare("
			SELECT 
				u.user_nicename AS login,
				u.display_name AS name,
				YEAR(p.post_date) AS year,
				MONTH(p.post_date) AS nmonth,
				COUNT(p.ID) AS posts
			FROM {$wpdb->posts} p
			INNER JOIN {$wpdb->users} u ON u.ID = p.post_author
			WHERE p.post_type = %s
			AND p.post_status = %s
			GROUP BY p.post_author, year, nmonth
			ORDER BY year DESC, nmonth DESC
			LIMIT %d;
		", $post_type, $post_status, $limit);
	
		$cache_key   = 'posts_author_cache_' . md5($query_posts_author);
		$cache_group = 'my_cache_group';
	
		$author_by_posts = wp_cache_get($cache_key, $cache_group);
		if ($author_by_posts === false) {
			$author_by_posts = $wpdb->get_results($query_posts_author, OBJECT);
			wp_cache_set($cache_key, $author_by_posts, $cache_group, 3600);
		}
	
		return $author_by_posts;
	}

	// Top Posts
	public static function get_posts_top() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		$limit       = 12;
		
		$query_posts_top = $wpdb->prepare("
			SELECT 
				ID AS post_id, 
				post_title, 
				LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1 AS post_words, 
				post_modified, 
				post_date 
			FROM {$wpdb->posts} 
			WHERE post_type = %s 
			AND post_status = %s 
			ORDER BY post_words DESC 
			LIMIT %d;
		", $post_type, $post_status, $limit);
	
		$cache_key   = 'posts_top_cache_' . md5($query_posts_top);
		$cache_group = 'my_cache_group';
		
		$top_posts = wp_cache_get($cache_key, $cache_group);
		if ($top_posts === false) {
			$top_posts = $wpdb->get_results($query_posts_top, OBJECT);
			wp_cache_set($cache_key, $top_posts, $cache_group, 3600);
		}
		
		return $top_posts;
	}

	// Posts by Categories
	public static function get_posts_by_categories() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		$taxonomy    = 'category';
		
		$query_posts_categories = $wpdb->prepare("
			SELECT 
				t.name AS category,
				COUNT(tr.object_id) AS posts,
				MAX(p.post_modified) AS modified,
				MAX(p.post_date) AS published,
				(SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = %s AND post_status = %s) AS total
			FROM {$wpdb->term_taxonomy} tt
			JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			JOIN {$wpdb->term_relationships} tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
			JOIN {$wpdb->posts} p ON tr.object_id = p.ID
			WHERE tt.taxonomy = %s AND p.post_type = %s AND p.post_status = %s
			GROUP BY t.term_id
			ORDER BY posts DESC;
		", $post_type, $post_status, $taxonomy, $post_type, $post_status);
	
		$cache_key   = 'posts_categories_cache_' . md5($query_posts_categories);
		$cache_group = 'my_cache_group';
	
		$categories_by_posts = wp_cache_get($cache_key, $cache_group);
		if ($categories_by_posts === false) {
			$categories_by_posts = $wpdb->get_results($query_posts_categories, OBJECT);
			wp_cache_set($cache_key, $categories_by_posts, $cache_group, 3600);
		}
	
		return $categories_by_posts;
	}

	// Posts Score
	public static function get_posts_score() {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
	
		$query_posts_score = $wpdb->prepare("
			SELECT
				COUNT(*) AS total_posts,
				AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) AS avg_words,
				AVG(DATEDIFF(NOW(), post_modified)) AS avg_days,
				CASE 
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
					ELSE 0
				END AS score_words,
				CASE 
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
					ELSE 10
				END AS score_recent,
				CASE 
					WHEN COUNT(*) <= 100 THEN 1
					WHEN COUNT(*) <= 200 THEN 2
					WHEN COUNT(*) <= 300 THEN 3
					WHEN COUNT(*) <= 400 THEN 4
					WHEN COUNT(*) <= 500 THEN 5
					WHEN COUNT(*) <= 600 THEN 6
					WHEN COUNT(*) <= 700 THEN 7
					WHEN COUNT(*) <= 800 THEN 8
					WHEN COUNT(*) <= 900 THEN 9
					ELSE 10
				END AS score_quantity,
				(
					CASE 
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
						ELSE 0
					END
					+
					CASE 
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
						ELSE 10
					END
					+
					CASE 
						WHEN COUNT(*) <= 100 THEN 1
						WHEN COUNT(*) <= 200 THEN 2
						WHEN COUNT(*) <= 300 THEN 3
						WHEN COUNT(*) <= 400 THEN 4
						WHEN COUNT(*) <= 500 THEN 5
						WHEN COUNT(*) <= 600 THEN 6
						WHEN COUNT(*) <= 700 THEN 7
						WHEN COUNT(*) <= 800 THEN 8
						WHEN COUNT(*) <= 900 THEN 9
						ELSE 10
					END
				) AS score_total
			FROM {$wpdb->posts}
			WHERE post_type = %s AND post_status = %s;
		", $post_type, $post_status);
	
		$cache_key   = 'posts_score_cache_' . md5($query_posts_score);
		$cache_group = 'my_cache_group';
	
		$score_posts = wp_cache_get($cache_key, $cache_group);
		if ($score_posts === false) {
			$score_posts = $wpdb->get_row($query_posts_score, OBJECT);
			wp_cache_set($cache_key, $score_posts, $cache_group, 3600);
		}
	
		return $score_posts;
	}

	// Posts List
	public static function get_posts_list($column = 'post_words', $sort_order = 'DESC', $offset = 0, $limit = 20) {
		global $wpdb;
		
		$post_type   = 'post';
		$post_status = 'publish';
		$taxonomy    = 'category';
	
		$allowed_columns = array('post_words', 'post_modified', 'post_title', 'post_category', 'post_date', 'post_id', 'post_name');
		$column = in_array($column, $allowed_columns) ? $column : 'post_words';
		$column = esc_sql($column);	
		$sort_order = strtoupper($sort_order) === 'ASC' ? 'ASC' : 'DESC';
	
		$query_posts_list = $wpdb->prepare("
			SELECT 
				p.ID AS post_id, 
				p.post_title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS post_words, 
				p.post_modified, 
				p.post_date, 
				GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') AS post_category, 
				p.post_name
			FROM {$wpdb->posts} p
			INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
			INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
			INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			WHERE p.post_type = %s
			AND p.post_status = %s
			AND tt.taxonomy = %s
			GROUP BY p.ID
			ORDER BY {$column} {$sort_order}
			LIMIT %d, %d
		", $post_type, $post_status, $taxonomy, $offset, $limit);
	
		$cache_key   = 'posts_list_cache_' . md5($query_posts_list);
		$cache_group = 'my_cache_group';
	
		$list_posts = wp_cache_get($cache_key, $cache_group);
		if ($list_posts === false) {
			$list_posts = $wpdb->get_results($query_posts_list, OBJECT);
			wp_cache_set($cache_key, $list_posts, $cache_group, 3600);
		}
	
		return $list_posts;
	}

	// Export Posts
	public static function export_posts() {
		global $wpdb;
	
		$post_type   = 'post';
		$post_status = 'publish';
		$taxonomy    = 'category';
		$offset      = 0;
		$limit       = 10000;
	
		$query_export_posts = $wpdb->prepare("
			SELECT 
				p.ID AS Id, 
				p.post_title AS Title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS Words, 
				DATE_FORMAT(p.post_modified, '%%d/%%m/%%Y') AS Modified, 
				DATE_FORMAT(p.post_date, '%%d/%%m/%%Y') AS Published, 
				GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') AS Category, 
				p.post_name AS URL
			FROM {$wpdb->posts} p
			INNER JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
			INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
			INNER JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			WHERE p.post_type = %s 
			AND p.post_status = %s 
			AND tt.taxonomy = %s
			GROUP BY p.ID
			ORDER BY Words DESC
			LIMIT %d, %d
		", $post_type, $post_status, $taxonomy, $offset, $limit);
	
		$cache_key   = 'export_posts_cache_' . md5($query_export_posts);
		$cache_group = 'my_cache_group';
	
		$posts_export = wp_cache_get($cache_key, $cache_group);
		if ($posts_export === false) {
			$posts_export = $wpdb->get_results($query_export_posts, ARRAY_A);
			wp_cache_set($cache_key, $posts_export, $cache_group, 3600);
		}
	
		return $posts_export;
	}

	// Total of Pages
	public static function get_pages_total() {
		global $wpdb;
		
		$post_type   = 'page';
		$post_status = 'publish';
	
		$query_pages_total = $wpdb->prepare("
			SELECT COUNT(ID) 
			FROM {$wpdb->posts}
			WHERE post_type = %s 
			AND post_status = %s
		", $post_type, $post_status);
	
		$cache_key   = 'pages_total_cache_' . md5($query_pages_total);
		$cache_group = 'my_cache_group';
	
		$total_pages = wp_cache_get($cache_key, $cache_group);
		if ($total_pages === false) {
			$total_pages = (int) $wpdb->get_var($query_pages_total);
			wp_cache_set($cache_key, $total_pages, $cache_group, 3600);
		}
	
		return $total_pages;
	}

	// Pages by Type
	public static function get_pages_by_type() {
		global $wpdb;
	
		$post_type = 'page';
		$post_status = 'publish';
	
		$query_pages_type = $wpdb->prepare("
			SELECT 
				(SELECT 
					COUNT(*) 
					FROM {$wpdb->posts} 
					WHERE {$wpdb->posts}.post_type = %s 
					AND {$wpdb->posts}.post_status = %s
				) AS total, 
				COUNT(*) AS posts, 
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 'F (- 400)' 
					WHEN words BETWEEN 401 AND 800 THEN 'E (401 - 800)' 
					WHEN words BETWEEN 801 AND 1200 THEN 'D (801 - 1200)' 
					WHEN words BETWEEN 1201 AND 1600 THEN 'C (1201 - 1600)' 
					WHEN words BETWEEN 1601 AND 2000 THEN 'B (1601 - 2000)' 
					ELSE 'A (2001 +)' 
				END AS type,
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 6 
					WHEN words BETWEEN 401 AND 800 THEN 5 
					WHEN words BETWEEN 801 AND 1200 THEN 4 
					WHEN words BETWEEN 1201 AND 1600 THEN 3 
					WHEN words BETWEEN 1601 AND 2000 THEN 2 
					ELSE 1 
				END AS type_order
			FROM 
				(SELECT 
					LENGTH({$wpdb->posts}.post_content) - LENGTH(REPLACE({$wpdb->posts}.post_content, ' ', '')) + 1 AS words 
				FROM {$wpdb->posts} 
				WHERE {$wpdb->posts}.post_type = %s 
				AND {$wpdb->posts}.post_status = %s
				) AS temp 
			GROUP BY type, type_order
			ORDER BY posts DESC, type_order ASC;
		", $post_type, $post_status, $post_type, $post_status);
	
		$cache_key   = 'pages_type_cache_' . md5($query_pages_type);
		$cache_group = 'my_cache_group';
		
		$type_by_pages = wp_cache_get($cache_key, $cache_group);
		if ($type_by_pages === false) {
			$type_by_pages = $wpdb->get_results($query_pages_type, OBJECT);
			wp_cache_set($cache_key, $type_by_pages, $cache_group, 3600);
		}
	
		return $type_by_pages;
	}

	// Pages Score
	public static function get_pages_score() {
		global $wpdb;
	
		$post_type = 'page';
		$post_status = 'publish';
	
		$query_pages_score = $wpdb->prepare("
			SELECT
				COUNT(*) AS total_posts,
				AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) AS avg_words,
				AVG(DATEDIFF(NOW(), post_modified)) AS avg_days,
				CASE 
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
					ELSE 0
				END AS score_words,
				CASE 
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
					ELSE 10
				END AS score_recent,
				CASE 
					WHEN COUNT(*) <= 100 THEN 1
					WHEN COUNT(*) <= 200 THEN 2
					WHEN COUNT(*) <= 300 THEN 3
					WHEN COUNT(*) <= 400 THEN 4
					WHEN COUNT(*) <= 500 THEN 5
					WHEN COUNT(*) <= 600 THEN 6
					WHEN COUNT(*) <= 700 THEN 7
					WHEN COUNT(*) <= 800 THEN 8
					WHEN COUNT(*) <= 900 THEN 9
					ELSE 10
				END AS score_quantity,
				(
					CASE 
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
						ELSE 0
					END
					+
					CASE 
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
						ELSE 10
					END
					+
					CASE 
						WHEN COUNT(*) <= 100 THEN 1
						WHEN COUNT(*) <= 200 THEN 2
						WHEN COUNT(*) <= 300 THEN 3
						WHEN COUNT(*) <= 400 THEN 4
						WHEN COUNT(*) <= 500 THEN 5
						WHEN COUNT(*) <= 600 THEN 6
						WHEN COUNT(*) <= 700 THEN 7
						WHEN COUNT(*) <= 800 THEN 8
						WHEN COUNT(*) <= 900 THEN 9
						ELSE 10
					END
				) AS score_total
			FROM {$wpdb->posts}
			WHERE post_type = %s AND post_status = %s
		", $post_type, $post_status);
	
		$cache_key   = 'pages_score_cache_' . md5($query_pages_score);
		$cache_group = 'my_cache_group';
	
		$score_pages = wp_cache_get($cache_key, $cache_group);
		if ($score_pages === false) {
			$score_pages = $wpdb->get_row($query_pages_score, OBJECT);
			wp_cache_set($cache_key, $score_pages, $cache_group, 3600);
		}
	
		return $score_pages;
	}

	// Pages List
	public static function get_pages_list($column = 'post_words', $sort_order = 'DESC', $offset = 0, $limit = 20) {
		global $wpdb;
		
		$post_type   = 'page';
		$post_status = 'publish';
	
		$allowed_columns = array('post_words', 'post_modified', 'post_title', 'post_date', 'post_id', 'post_name');
		$column = in_array($column, $allowed_columns) ? $column : 'post_words';
		$column = esc_sql($column);
		$sort_order = strtoupper($sort_order) === 'ASC' ? 'ASC' : 'DESC';
	
		$query_pages_list = $wpdb->prepare("
			SELECT 
				ID AS post_id, 
				post_title, 
				LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1 AS post_words, 
				post_modified, 
				post_date, 
				post_name
			FROM {$wpdb->posts}
			WHERE post_type = %s 
			AND post_status = %s
			ORDER BY {$column} {$sort_order}
			LIMIT %d, %d
		", $post_type, $post_status, $offset, $limit);
	
		$cache_key   = 'pages_list_cache_' . md5($query_pages_list);
		$cache_group = 'my_cache_group';
	
		$list_pages = wp_cache_get($cache_key, $cache_group);
		if ($list_pages === false) {
			$list_pages = $wpdb->get_results($query_pages_list, OBJECT);
			wp_cache_set($cache_key, $list_pages, $cache_group, 3600);
		}
	
		return $list_pages;
	}

	// Export Pages
	public static function export_pages() {
		global $wpdb;
	
		$post_type   = 'page';
		$post_status = 'publish';
		$offset      = 0;
		$limit       = 10000;
	
		$query_export_pages = $wpdb->prepare("
			SELECT 
				ID AS Id, 
				post_title AS Title, 
				LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1 AS Words, 
				DATE_FORMAT(post_modified, '%%d/%%m/%%Y') AS Modified, 
				DATE_FORMAT(post_date, '%%d/%%m/%%Y') AS Published, 
				post_name AS URL 
			FROM {$wpdb->posts}
			WHERE post_type = %s 
			AND post_status = %s
			ORDER BY Words DESC
			LIMIT %d, %d
		", $post_type, $post_status, $offset, $limit);
	
		$cache_key   = 'export_pages_cache_' . md5($query_export_pages);
		$cache_group = 'my_cache_group';
	
		$pages_export = wp_cache_get($cache_key, $cache_group);
		if ($pages_export === false) {
			$pages_export = $wpdb->get_results($query_export_pages, ARRAY_A);
			wp_cache_set($cache_key, $pages_export, $cache_group, 3600);
		}
	
		return $pages_export;
	}

	// Total of Products
	public static function get_products_total() {
		global $wpdb;
		
		$post_type   = 'product';
		$post_status = 'publish';
	
		$query_products_total = $wpdb->prepare("
			SELECT COUNT(ID) 
			FROM {$wpdb->posts}
			WHERE post_type = %s 
			AND post_status = %s
		", $post_type, $post_status);
	
		$cache_key   = 'products_total_cache_' . md5($query_products_total);
		$cache_group = 'my_cache_group';
	
		$total_products = wp_cache_get($cache_key, $cache_group);
		if ($total_products === false) {
			$total_products = (int) $wpdb->get_var($query_products_total);
			wp_cache_set($cache_key, $total_products, $cache_group, 3600);
		}
	
		return $total_products;
	}

	// Products by Type
	public static function get_products_by_type() {
		global $wpdb;
		
		$post_type   = 'product';
		$post_status = 'publish';
	
		$query_products_type = $wpdb->prepare("
			SELECT 
				(SELECT 
					COUNT(*) 
					FROM {$wpdb->posts} 
					WHERE {$wpdb->posts}.post_type = %s 
					AND {$wpdb->posts}.post_status = %s
				) AS total, 
				COUNT(*) AS posts, 
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 'F (- 400)' 
					WHEN words BETWEEN 401 AND 800 THEN 'E (401 - 800)' 
					WHEN words BETWEEN 801 AND 1200 THEN 'D (801 - 1200)' 
					WHEN words BETWEEN 1201 AND 1600 THEN 'C (1201 - 1600)' 
					WHEN words BETWEEN 1601 AND 2000 THEN 'B (1601 - 2000)' 
					ELSE 'A (2001 +)' 
				END AS type,
				CASE 
					WHEN words BETWEEN 0 AND 400 THEN 6 
					WHEN words BETWEEN 401 AND 800 THEN 5 
					WHEN words BETWEEN 801 AND 1200 THEN 4 
					WHEN words BETWEEN 1201 AND 1600 THEN 3 
					WHEN words BETWEEN 1601 AND 2000 THEN 2 
					ELSE 1 
				END AS type_order
			FROM 
				(SELECT 
					LENGTH({$wpdb->posts}.post_content) - LENGTH(REPLACE({$wpdb->posts}.post_content, ' ', '')) + 1 AS words 
				FROM {$wpdb->posts} 
				WHERE {$wpdb->posts}.post_type = %s 
				AND {$wpdb->posts}.post_status = %s
				) AS temp 
			GROUP BY type, type_order
			ORDER BY posts DESC, type_order ASC;
		", $post_type, $post_status, $post_type, $post_status);
	
		$cache_key   = 'products_type_cache_' . md5($query_products_type);
		$cache_group = 'my_cache_group';
	
		$type_by_products = wp_cache_get($cache_key, $cache_group);
		if ($type_by_products === false) {
			$type_by_products = $wpdb->get_results($query_products_type, OBJECT);
			wp_cache_set($cache_key, $type_by_products, $cache_group, 3600);
		}
	
		return $type_by_products;
	}

	// Products Score
	public static function get_products_score() {
		global $wpdb;
		
		$post_type   = 'product';
		$post_status = 'publish';
	
		$query_products_score = $wpdb->prepare("
			SELECT
				COUNT(*) AS total_posts,
				AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) AS avg_words,
				AVG(DATEDIFF(NOW(), post_modified)) AS avg_days,
				CASE 
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
					WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
					ELSE 0
				END AS score_words,
				CASE 
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
					WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
					ELSE 10
				END AS score_recent,
				CASE 
					WHEN COUNT(*) <= 100 THEN 1
					WHEN COUNT(*) <= 200 THEN 2
					WHEN COUNT(*) <= 300 THEN 3
					WHEN COUNT(*) <= 400 THEN 4
					WHEN COUNT(*) <= 500 THEN 5
					WHEN COUNT(*) <= 600 THEN 6
					WHEN COUNT(*) <= 700 THEN 7
					WHEN COUNT(*) <= 800 THEN 8
					WHEN COUNT(*) <= 900 THEN 9
					ELSE 10
				END AS score_quantity,
				(
					CASE 
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) >= 2001 THEN 50
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1601 AND 2000 THEN 40
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 1201 AND 1600 THEN 30
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 801 AND 1200 THEN 20
						WHEN AVG(LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1) BETWEEN 401 AND 800 THEN 10
						ELSE 0
					END
					+
					CASE 
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 60 THEN 40
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 90 THEN 30
						WHEN AVG(DATEDIFF(NOW(), post_modified)) <= 120 THEN 20
						ELSE 10
					END
					+
					CASE 
						WHEN COUNT(*) <= 100 THEN 1
						WHEN COUNT(*) <= 200 THEN 2
						WHEN COUNT(*) <= 300 THEN 3
						WHEN COUNT(*) <= 400 THEN 4
						WHEN COUNT(*) <= 500 THEN 5
						WHEN COUNT(*) <= 600 THEN 6
						WHEN COUNT(*) <= 700 THEN 7
						WHEN COUNT(*) <= 800 THEN 8
						WHEN COUNT(*) <= 900 THEN 9
						ELSE 10
					END
				) AS score_total
			FROM {$wpdb->posts}
			WHERE post_type = %s AND post_status = %s
		", $post_type, $post_status);
	
		$cache_key   = 'products_score_cache_' . md5($query_products_score);
		$cache_group = 'my_cache_group';
	
		$score_products = wp_cache_get($cache_key, $cache_group);
		if ($score_products === false) {
			$score_products = $wpdb->get_row($query_products_score, OBJECT);
			wp_cache_set($cache_key, $score_products, $cache_group, 3600);
		}
	
		return $score_products;
	}

	// Products List
	public static function get_products_list($column = 'post_words', $sort_order = 'DESC', $offset = 0, $limit = 20) {
		global $wpdb;
		
		$post_type   = 'product';
		$post_status = 'publish';
	
		$allowed_columns = array('post_words','post_modified','post_title','post_date','post_id','post_name','product_category');
		$column = in_array($column, $allowed_columns) ? $column : 'post_words';
		$column = esc_sql($column);
		$sort_order = strtoupper($sort_order) === 'ASC' ? 'ASC' : 'DESC';
	
		$query_products_list = $wpdb->prepare("
			SELECT 
				p.ID AS post_id, 
				p.post_title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS post_words, 
				p.post_modified, 
				p.post_date, 
				p.post_name,
				GROUP_CONCAT(t.name SEPARATOR ', ') AS product_category
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->term_relationships} tr ON (p.ID = tr.object_id)
			LEFT JOIN {$wpdb->term_taxonomy} tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'product_cat')
			LEFT JOIN {$wpdb->terms} t ON (tt.term_id = t.term_id)
			WHERE p.post_type = %s 
			AND p.post_status = %s 
			GROUP BY p.ID
			ORDER BY {$column} {$sort_order}
			LIMIT %d, %d
		", $post_type, $post_status, $offset, $limit);
	
		$cache_key   = 'products_list_cache_' . md5($query_products_list);
		$cache_group = 'my_cache_group';
	
		$list_products = wp_cache_get($cache_key, $cache_group);
		if ($list_products === false) {
			$list_products = $wpdb->get_results($query_products_list, OBJECT);
			wp_cache_set($cache_key, $list_products, $cache_group, 3600);
		}
	
		return $list_products;
	}

	// Export Products
	public static function export_products() {
		global $wpdb;
	
		$post_type   = 'product';
		$post_status = 'publish';
		$offset      = 0;
		$limit       = 10000;
	
		$query_export_products = $wpdb->prepare("
			SELECT 
				p.ID AS Id, 
				p.post_title AS Title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS Words, 
				DATE_FORMAT(p.post_modified, '%%d/%%m/%%Y') AS Modified, 
				DATE_FORMAT(p.post_date, '%%d/%%m/%%Y') AS Published, 
				GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') AS Category,
				p.post_name AS URL
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
			LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'product_cat'
			LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			WHERE p.post_type = %s 
			AND p.post_status = %s
			GROUP BY p.ID
			ORDER BY Words DESC
			LIMIT %d, %d
		", $post_type, $post_status, $offset, $limit);
	
		$cache_key   = 'export_products_cache_' . md5($query_export_products);
		$cache_group = 'my_cache_group';
	
		$products_export = wp_cache_get($cache_key, $cache_group);
		if ($products_export === false) {
			$products_export = $wpdb->get_results($query_export_products, ARRAY_A);
			wp_cache_set($cache_key, $products_export, $cache_group, 3600);
		}
	
		return $products_export;
	}

	// Total of Custom Posts
	public static function get_custom_posts_total() {
		global $wpdb;
		
		$post_type_post      = 'post';
		$post_type_page      = 'page';
		$post_type_product   = 'product';
		$post_status         = 'publish';
	
		$query_custom_total = $wpdb->prepare("
			SELECT COUNT(ID)
			FROM {$wpdb->posts}
			WHERE post_type NOT IN (%s, %s, %s)
			AND post_status = %s
		", $post_type_post, $post_type_page, $post_type_product, $post_status);
	
		$cache_key   = 'custom_total_cache_' . md5($query_custom_total);
		$cache_group = 'my_cache_group';
	
		$total_custom_posts = wp_cache_get($cache_key, $cache_group);
		if ($total_custom_posts === false) {
			$total_custom_posts = (int) $wpdb->get_var($query_custom_total);
			wp_cache_set($cache_key, $total_custom_posts, $cache_group, 3600);
		}
	
		return $total_custom_posts;
	}

	// Custom Posts List
	public static function get_custom_posts_list($column = 'post_words', $sort_order = 'DESC', $offset = 0, $limit = 20) {
		global $wpdb;
		
		$post_type_post      = 'post';
		$post_type_page      = 'page';
		$post_type_product   = 'product';
		$post_status         = 'publish';
	
		$allowed_columns = array('post_words', 'post_modified', 'post_title', 'post_date', 'post_id', 'post_type', 'post_name', 'custom_category');
		$column = in_array($column, $allowed_columns) ? $column : 'post_words';
		$column = esc_sql($column);
		$sort_order = strtoupper($sort_order) === 'ASC' ? 'ASC' : 'DESC';
	
		$query_custom_list = $wpdb->prepare("
			SELECT 
				p.ID AS post_id, 
				p.post_type, 
				p.post_title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS post_words, 
				p.post_modified, 
				p.post_date, 
				p.post_name,
				GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') AS custom_category
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
			LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
			LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			WHERE p.post_type NOT IN (%s, %s, %s)
			AND p.post_status = %s
			GROUP BY p.ID
			ORDER BY {$column} {$sort_order}
			LIMIT %d, %d
		", $post_type_post, $post_type_page, $post_type_product, $post_status, $offset, $limit);
	
		$cache_key   = 'custom_list_cache_' . md5($query_custom_list);
		$cache_group = 'my_cache_group';
	
		$list_custom_posts = wp_cache_get($cache_key, $cache_group);
		if ($list_custom_posts === false) {
			$list_custom_posts = $wpdb->get_results($query_custom_list, OBJECT);
			wp_cache_set($cache_key, $list_custom_posts, $cache_group, 3600);
		}
	
		return $list_custom_posts;
	}

	// Export Custom Posts
	public static function export_custom_posts() {
		global $wpdb;
	
		$post_status  = 'publish';
		$offset       = 0;
		$limit        = 10000;
		$excluded_1   = 'post';
		$excluded_2   = 'page';
		$excluded_3   = 'product';
	
		$query_export_custom = $wpdb->prepare("
			SELECT 
				p.ID AS Id, 
				p.post_type AS Type,
				p.post_title AS Title, 
				LENGTH(p.post_content) - LENGTH(REPLACE(p.post_content, ' ', '')) + 1 AS Words, 
				DATE_FORMAT(p.post_modified, '%%d/%%m/%%Y') AS Modified, 
				DATE_FORMAT(p.post_date, '%%d/%%m/%%Y') AS Published, 
				GROUP_CONCAT(DISTINCT t.name SEPARATOR ', ') AS Category,
				p.post_name AS URL
			FROM {$wpdb->posts} p
			LEFT JOIN {$wpdb->term_relationships} tr ON p.ID = tr.object_id
			LEFT JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
			LEFT JOIN {$wpdb->terms} t ON tt.term_id = t.term_id
			WHERE p.post_type NOT IN (%s, %s, %s)
			AND p.post_status = %s
			GROUP BY p.ID
			ORDER BY Words DESC
			LIMIT %d, %d
		", 
			$excluded_1, $excluded_2, $excluded_3,
			$post_status, $offset, $limit
		);
	
		$cache_key   = 'export_custom_cache_' . md5($query_export_custom);
		$cache_group = 'my_cache_group';
	
		$custom_posts_export = wp_cache_get($cache_key, $cache_group);
		if ($custom_posts_export === false) {
			$custom_posts_export = $wpdb->get_results($query_export_custom, ARRAY_A);
			wp_cache_set($cache_key, $custom_posts_export, $cache_group, 3600);
		}
	
		return $custom_posts_export;
	}

	// OnPage Check
	public static function get_onpage($id_wp) {
		global $wpdb;
	
		$id_wp = intval($id_wp);
		if ($id_wp <= 0) {
			return false;
		}
	
		$query_onpage = $wpdb->prepare("
			SELECT 
				ID AS post_id,
				post_title,
				CHAR_LENGTH(post_title) AS title_length,
				post_type,
				post_modified,
				post_date,
				post_name,
				CHAR_LENGTH(post_name) AS url_length,
				LENGTH(post_content) - LENGTH(REPLACE(post_content, ' ', '')) + 1 AS post_words
			FROM {$wpdb->posts}
			WHERE ID = %d
			LIMIT 1
		", $id_wp);
	
		$onpage = $wpdb->get_row($query_onpage, OBJECT);
		
		return $onpage;
	}

	// Meta Descriptions
	public static function get_meta_descriptions($id_wp) {
		global $wpdb;
	
		$id_wp = intval( $id_wp );
		if ( $id_wp <= 0 ) {
			return false;
		}
	
		if ( ! function_exists( 'is_plugin_active' ) ) {
			include_once ABSPATH . 'wp-admin/includes/plugin.php';
		}
	
		$meta_plugins = array();
		
		if ( is_plugin_active( 'wordpress-seo/wp-seo.php' ) ) {
			$meta_plugins['_yoast_wpseo_metadesc'] = 'Yoast SEO';
		}
		if ( is_plugin_active( 'wp-seopress/seopress.php' ) ) {
			$meta_plugins['_seopress_titles_desc'] = 'SEOPress';
		}
		if ( is_plugin_active( 'seo-by-rank-math/rank-math.php' ) ) {
			$meta_plugins['rank_math_description'] = 'Rank Math';
		}
		if ( is_plugin_active( 'all-in-one-seo-pack/all_in_one_seo_pack.php' ) ) {
			$meta_plugins['_aioseo_description'] = 'All in One SEO';
		}
	
		if ( empty( $meta_plugins ) ) {
			return false;
		}
	
		$placeholders = implode( ',', array_fill( 0, count( $meta_plugins ), '%s' ) );
	
		$query_meta_descriptions = $wpdb->prepare("
			SELECT meta_key, meta_value
			FROM {$wpdb->postmeta}
			WHERE post_id = %d
			AND meta_key IN ($placeholders)
			",
			array_merge( array( $id_wp ), array_keys( $meta_plugins ) )
		);
	
		$meta_description = $wpdb->get_results( $query_meta_descriptions, ARRAY_A );
	
		if ( empty( $meta_description ) ) {
			return false;
		}
	
		$descriptions_meta = array();
		foreach ( $meta_description as $row ) {
			$meta_plugin = $row['meta_key'];
			if ( isset( $meta_plugins[$meta_plugin] ) && ! empty( $row['meta_value'] ) ) {
				$descriptions_meta[ $meta_plugins[$meta_plugin] ] = $row['meta_value'];
			}
		}
	
		return $descriptions_meta;
	}

	// Keywords Density
	public static function get_keywords_density($id_wp) {
		global $wpdb;
	
		$id_wp = intval($id_wp);
		if ($id_wp <= 0) {
			return false;
		}
	
		$query_keywords_density = $wpdb->get_var($wpdb->prepare("
			SELECT post_content
			FROM {$wpdb->posts}
			WHERE ID = %d
			LIMIT 1
		", $id_wp));
	
		if (empty($query_keywords_density)) {
			return array();
		}
	
		$text = strtolower(wp_strip_all_tags($query_keywords_density));
		$text = preg_replace('/[^\p{L}\p{N}\s]/u', '', $text);
		$words = preg_split('/\s+/', $text, -1, PREG_SPLIT_NO_EMPTY);
		$total_words = count($words);
	
		if ($total_words === 0) {
			return array();
		}
	
		$frequencies = array_count_values($words);
	
		foreach ($frequencies as $word => $count) {
			if (mb_strlen($word) <= 2) {
				unset($frequencies[$word]);
			}
		}
	
		$densities = array();
		foreach ($frequencies as $word => $count) {
			$percentage = ($count / $total_words) * 100;
			$densities[$word] = round($percentage, 2);
		}
	
		arsort($densities);
		$density_keywords = array_slice( $densities, 0, 10, true );
		
		return $density_keywords;
	}

	// Content Tags
	public static function get_tags($id_wp) {
		global $wpdb;
	
		$id_wp = intval($id_wp);
		if ($id_wp <= 0) {
			return false;
		}
	
		$query_tags = $wpdb->get_var($wpdb->prepare("
			SELECT post_content
			FROM {$wpdb->posts}
			WHERE ID = %d
			LIMIT 1
		", $id_wp));
	
		if (empty($query_tags)) {
			return array('h2_h3' => 0, 'h4_h6' => 0, 'images' => 0);
		}
	
		$h2_h3_count = preg_match_all('/<(h2|h3)[^>]*>.*?<\/\1>/i', $query_tags, $matches);
		$h4_h6_count = preg_match_all('/<(h4|h5|h6)[^>]*>.*?<\/\1>/i', $query_tags, $matches);
		$img_count   = preg_match_all('/<img\s[^>]*>/i', $query_tags, $matches);
	
		$tags = array(
			'h2_h3'  => $h2_h3_count,
			'h4_h6'  => $h4_h6_count,
			'images' => $img_count
		);
		
		return $tags;
	}

	// Content Links
	public static function get_links($id_wp) {
		global $wpdb;
	
		$id_wp = intval($id_wp);
		if ($id_wp <= 0) {
			return array('linker' => 0, 'linked' => 0);
		}
	
		$query_linker = $wpdb->get_row($wpdb->prepare("
			SELECT post_content
			FROM {$wpdb->posts}
			WHERE ID = %d
			LIMIT 1
		", $id_wp));
	
		if (!$query_linker) {
			return array('linker' => 0, 'linked' => 0);
		}
	
		$query_linker_counter = preg_match_all('/<a\s[^>]*href=["\']?([^"\'>\s]+)/i', $query_linker->post_content, $matches);
	
		$permalink = get_permalink($id_wp);
		$url_like  = '%<a%href%' . $wpdb->esc_like($permalink) . '%';
	
		$query_linked_counter = $wpdb->get_var($wpdb->prepare("
			SELECT COUNT(*) FROM {$wpdb->posts}
			WHERE ID != %d
			AND post_status = 'publish'
			AND post_type IN ('post', 'page', 'product')
			AND post_content LIKE %s
		", $id_wp, $url_like));
	
		$links = array(
			'linker' => $query_linker_counter,
			'linked' => intval($query_linked_counter)
		);
		
		return $links;
	}
}
?>