<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * AnyWareX Clone Translate – Active Translation Mapping
 *
 * Goals for PluginCheck:
 * - No %i placeholders (WP < 6.2)
 * - No dynamic column identifiers in SQL (use fixed branches)
 * - Values always via $wpdb->prepare()
 * - Table identifiers are validated (alnum/_), then esc_sql()’d
 * - For validated table interpolation, we use targeted phpcs ignores (PluginCheck limitation)
 */

global $wpdb;

/* =========================================================
 * Identifier helpers (validated)
 * ========================================================= */

/**
 * Validate a table identifier (wpdb prefix + fixed suffix).
 *
 * @param string $table Table identifier.
 * @return string Validated identifier or empty string.
 */
function awxct_map_validate_table_ident( $table ) {
	$table = (string) $table;
	if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
		return '';
	}
	return $table;
}

$tbl_trans = awxct_map_validate_table_ident( $wpdb->prefix . 'awxct_translations' );
$tbl_lang  = awxct_map_validate_table_ident( $wpdb->prefix . 'awxct_languages' );
$wp_posts  = awxct_map_validate_table_ident( $wpdb->posts );

if ( '' === $tbl_trans || '' === $tbl_lang || '' === $wp_posts ) {
	echo '<div class="notice notice-error"><p>'
		. esc_html__( 'AnyWareX Clone Translate: Required database tables could not be validated.', 'anywarex-clone-translate' )
		. '</p></div>';
	return;
}

/**
 * Scanner-friendly aliases (still validated).
 */
$tbl_trans_sql = esc_sql( $tbl_trans );
$tbl_lang_sql  = esc_sql( $tbl_lang );
$wp_posts_sql  = esc_sql( $wp_posts );

/* =========================================================
 * Cache helpers (view-level)
 * ========================================================= */

if ( ! function_exists( 'awxct_map_cache_key' ) ) {
	function awxct_map_cache_key( $suffix ) {
		$blog_id = function_exists( 'get_current_blog_id' ) ? (int) get_current_blog_id() : 0;
		return 'awxct_' . $blog_id . '_mapping_' . (string) $suffix;
	}
}

if ( ! function_exists( 'awxct_map_cache_bump_ver' ) ) {
	function awxct_map_cache_bump_ver() {
		$ver = (int) wp_cache_get( awxct_map_cache_key( 'ver' ), 'awxct' );
		if ( $ver < 0 ) {
			$ver = 0;
		}
		wp_cache_set( awxct_map_cache_key( 'ver' ), $ver + 1, 'awxct', HOUR_IN_SECONDS );
	}
}

function awxct_map_cached( $cache_suffix, $ttl, $callback ) {
	$key = awxct_map_cache_key( 'c_' . (string) $cache_suffix );
	$val = wp_cache_get( $key, 'awxct' );

	if ( false !== $val ) {
		return $val;
	}

	$val = is_callable( $callback ) ? call_user_func( $callback ) : null;

	wp_cache_set( $key, $val, 'awxct', (int) $ttl );
	return $val;
}

$awxct_map_ver = (int) wp_cache_get( awxct_map_cache_key( 'ver' ), 'awxct' );
if ( $awxct_map_ver < 0 ) {
	$awxct_map_ver = 0;
}

/* =========================================================
 * Auto-cleanup: remove orphaned mapping entries
 * ========================================================= */

$awxct_cleaned = 0;

$orphan_ids = (array) awxct_map_cached(
	'orphans_' . $awxct_map_ver,
	30,
	static function () use ( $tbl_trans_sql, $wp_posts_sql ) {
		global $wpdb;

		// Validated identifiers are interpolated; PluginCheck still flags it -> targeted ignore.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		return (array) $wpdb->get_col(
			"SELECT m.id
			   FROM `{$tbl_trans_sql}` m
		  LEFT JOIN `{$wp_posts_sql}` p_root ON p_root.ID = m.root_post_id
		  LEFT JOIN `{$wp_posts_sql}` p_orig ON p_orig.ID = m.original_post_id
		  LEFT JOIN `{$wp_posts_sql}` p_tr   ON p_tr.ID   = m.translated_post_id
			  WHERE (m.root_post_id <> 0 AND p_root.ID IS NULL)
			     OR (m.original_post_id <> 0 AND p_orig.ID IS NULL)
			     OR (m.translated_post_id <> 0 AND p_tr.ID IS NULL)"
		);
		// phpcs:enable
	}
);

$orphan_ids = array_map( 'absint', (array) $orphan_ids );
$orphan_ids = array_values( array_filter( $orphan_ids ) );

if ( ! empty( $orphan_ids ) ) {

	$awxct_cleaned = 0;

	// Delete one-by-one to avoid dynamic IN() placeholder strings (PluginCheck: UnfinishedPrepare).
	foreach ( $orphan_ids as $oid ) {
		$oid = absint( $oid );
		if ( $oid <= 0 ) {
			continue;
		}

		// $tbl_trans is validated (alnum/_). wpdb::delete() prepares values internally.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$deleted = $wpdb->delete(
			$tbl_trans,
			array( 'id' => $oid ),
			array( '%d' )
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

		if ( false !== $deleted ) {
			$awxct_cleaned += (int) $deleted;
		}
	}

	if ( $awxct_cleaned > 0 ) {
		awxct_map_cache_bump_ver();
		$awxct_map_ver++;
	}
}


/* =========================================================
 * License helper
 * ========================================================= */

if ( ! function_exists( 'awxct_allow_multi_per_lang' ) ) {
	function awxct_allow_multi_per_lang() {
		return false; // Free default.
	}
}

/* =========================================================
 * Detect columns (cached)
 * ========================================================= */

$trans_cols = (array) awxct_map_cached(
	'trans_cols_' . $awxct_map_ver,
	300,
	static function () use ( $tbl_trans_sql ) {
		global $wpdb;
		// SHOW COLUMNS has no values to prepare; validated table interpolation -> targeted ignore.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$out = (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$tbl_trans_sql}`" );
		// phpcs:enable
		return $out;
	}
);

$group_col = "original_post_id"; // forced: root_post_id is history only

$post_col = null;
if ( in_array( 'translated_post_id', $trans_cols, true ) ) {
	$post_col = 'translated_post_id';
} elseif ( in_array( 'translation_post_id', $trans_cols, true ) ) {
	$post_col = 'translation_post_id';
}

/* =========================================================
 * Save (server-side, no AJAX)
 * ========================================================= */

if (
	isset( $_POST['awxct_save_active'] ) &&
	check_admin_referer( 'awxct_set_active', 'awxct_nonce' )
) {
	$original_id    = isset( $_POST['original_id'] ) ? absint( wp_unslash( $_POST['original_id'] ) ) : 0;
	$language_code  = isset( $_POST['language_code'] ) ? sanitize_text_field( wp_unslash( $_POST['language_code'] ) ) : '';
	$translation_id = isset( $_POST['active'] ) ? absint( wp_unslash( $_POST['active'] ) ) : 0;

	if ( $original_id > 0 && '' !== $language_code && $translation_id > 0 ) {

		// 1) Deactivate all entries in the same group and language.
		// IMPORTANT: fixed branches -> no dynamic column names.
		if ( 'root_post_id' === $group_col ) {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$tbl_trans_sql}` SET is_active = 0
					  WHERE root_post_id = %d
					    AND LOWER(REPLACE(language_code, '_', '-')) = %s",
					$original_id,
					strtolower( $language_code )
				)
			);
			// phpcs:enable
		} else {
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE `{$tbl_trans_sql}` SET is_active = 0
					  WHERE original_post_id = %d
					    AND LOWER(REPLACE(language_code, '_', '-')) = %s",
					$original_id,
					strtolower( $language_code )
				)
			);
			// phpcs:enable
		}

		// 2) Mark the selected entry as active (table name validated).
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->update(
			$tbl_trans,
			array( 'is_active' => 1 ),
			array( 'id' => $translation_id ),
			array( '%d' ),
			array( '%d' )
		);

		awxct_map_cache_bump_ver();
		$awxct_map_ver++;

		echo '<div class="updated"><p><strong>'
			. esc_html__( 'Saved: Active translation updated.', 'anywarex-clone-translate' )
			. '</strong></p></div>';
	} else {
		echo '<div class="notice notice-error"><p>'
			. esc_html__( 'Could not save: please check selection.', 'anywarex-clone-translate' )
			. '</p></div>';
	}
}

/* =========================================================
 * Notices
 * ========================================================= */

// phpcs:ignore WordPress.Security.NonceVerification.Recommended
$awxmsg = isset( $_GET['awxmsg'] ) ? sanitize_key( wp_unslash( $_GET['awxmsg'] ) ) : '';
if ( 'unlinked' === $awxmsg ) {
	echo '<div class="updated"><p>'
		. esc_html__( 'Entry was removed from translations.', 'anywarex-clone-translate' )
		. '</p></div>';
}

/* =========================================================
 * Read filters
 * ========================================================= */

// phpcs:ignore WordPress.Security.NonceVerification.Recommended
$selected_original = isset( $_GET['original_id'] ) ? absint( wp_unslash( $_GET['original_id'] ) ) : 0;
// phpcs:ignore WordPress.Security.NonceVerification.Recommended
$selected_lang = isset( $_GET['language_code'] ) ? sanitize_text_field( wp_unslash( $_GET['language_code'] ) ) : '';

/* =========================================================
 * Helper functions (read-only)
 * ========================================================= */

function awxct_original_choices( $group_col, $tbl_trans_sql, $wp_posts_sql, $ver ) {
	$group_col = ( 'root_post_id' === $group_col ) ? 'root_post_id' : 'original_post_id';

	$rows = (array) awxct_map_cached(
		'orig_choices_' . (int) $ver . '_' . $group_col,
		60,
		static function () use ( $group_col, $tbl_trans_sql, $wp_posts_sql ) {
			global $wpdb;

			if ( 'root_post_id' === $group_col ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					"SELECT DISTINCT t.root_post_id AS original_id, p.post_title AS title
					   FROM `{$tbl_trans_sql}` t
				  LEFT JOIN `{$wp_posts_sql}` p ON p.ID = t.root_post_id
				   ORDER BY original_id ASC"
				);
				// phpcs:enable
			}

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return (array) $wpdb->get_results(
				"SELECT DISTINCT t.original_post_id AS original_id, p.post_title AS title
				   FROM `{$tbl_trans_sql}` t
			  LEFT JOIN `{$wp_posts_sql}` p ON p.ID = t.original_post_id
			   ORDER BY original_id ASC"
			);
			// phpcs:enable
		}
	);

	$out = array();
	foreach ( $rows as $r ) {
		$label = ! empty( $r->title ) ? (string) $r->title : '#' . (int) $r->original_id;
		$out[] = (object) array(
			'id'    => (int) $r->original_id,
			'label' => $label,
		);
	}

	return $out;
}

function awxct_language_choices( $tbl_lang_sql, $ver ) {
	$lang_cols = (array) awxct_map_cached(
		'lang_cols_' . (int) $ver,
		300,
		static function () use ( $tbl_lang_sql ) {
			global $wpdb;
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$out = (array) $wpdb->get_col( "SHOW COLUMNS FROM `{$tbl_lang_sql}`" );
			// phpcs:enable
			return $out;
		}
	);

	$has_native  = in_array( 'native_name', $lang_cols, true );
	$has_english = in_array( 'english_name', $lang_cols, true );

	$select_name = $has_native ? 'native_name' : ( $has_english ? 'english_name' : 'code' );
	if ( ! in_array( $select_name, array( 'native_name', 'english_name', 'code' ), true ) ) {
		$select_name = 'code';
	}

	$rows = (array) awxct_map_cached(
		'lang_choices_' . (int) $ver . '_' . $select_name,
		60,
		static function () use ( $tbl_lang_sql, $select_name ) {
			global $wpdb;

			if ( 'native_name' === $select_name ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					"SELECT LOWER(code) AS code,
							COALESCE(NULLIF(native_name,''), code) AS display_name,
							COALESCE(active,1) AS active
					   FROM `{$tbl_lang_sql}`
				   ORDER BY code ASC"
				);
				// phpcs:enable
			}

			if ( 'english_name' === $select_name ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					"SELECT LOWER(code) AS code,
							COALESCE(NULLIF(english_name,''), code) AS display_name,
							COALESCE(active,1) AS active
					   FROM `{$tbl_lang_sql}`
				   ORDER BY code ASC"
				);
				// phpcs:enable
			}

			// code
			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return (array) $wpdb->get_results(
				"SELECT LOWER(code) AS code,
						COALESCE(NULLIF(code,''), code) AS display_name,
						COALESCE(active,1) AS active
				   FROM `{$tbl_lang_sql}`
			   ORDER BY code ASC"
			);
			// phpcs:enable
		}
	);

	$out = array();
	foreach ( $rows as $r ) {
		if ( 1 === (int) ( $r->active ?? 0 ) ) {
			$out[] = (object) array(
				'code' => (string) $r->code,
				'name' => (string) $r->display_name,
			);
		}
	}

	return $out;
}

function awxct_load_translations( $group_col, $post_col, $selected_original, $selected_lang, $tbl_trans_sql, $wp_posts_sql, $ver ) {
	global $wpdb;

	$group_col = ( 'root_post_id' === $group_col ) ? 'root_post_id' : 'original_post_id';

	if ( ! in_array( $post_col, array( 'translated_post_id', 'translation_post_id' ), true ) ) {
		$post_col = null;
	}

	$selected_original = absint( $selected_original );
	$selected_lang_lc  = ( '' !== (string) $selected_lang ) ? strtolower( (string) $selected_lang ) : '';
	$has_filters       = ( $selected_original > 0 ) || ( '' !== $selected_lang_lc );

	/* ==============================
	 * A) WITH join to posts
	 * ============================== */
	if ( $post_col ) {

		$is_translated = ( 'translated_post_id' === $post_col );

		// NO FILTERS
		if ( ! $has_filters ) {
			return (array) awxct_map_cached(
				'rows_all_join_' . (int) $ver . '_' . $group_col . ( $is_translated ? '_translated' : '_translation' ),
				30,
				static function () use ( $tbl_trans_sql, $wp_posts_sql, $group_col, $is_translated ) {
					global $wpdb;

					$join_col = $is_translated ? 'translated_post_id' : 'translation_post_id';

					if ( 'root_post_id' === $group_col ) {
						// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
						return (array) $wpdb->get_results(
							"SELECT t.*,
									tp.post_title AS trans_title,
									tp.post_status AS trans_status,
									t.root_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
						  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
						   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC
						   LIMIT 300"
						);
						// phpcs:enable
					}

					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						"SELECT t.*,
								tp.post_title AS trans_title,
								tp.post_status AS trans_status,
								t.original_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
					  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
					   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC
					   LIMIT 300"
					);
					// phpcs:enable
				}
			);
		}

		// FILTERS (3 cases)
		// 1) original only
		if ( $selected_original > 0 && '' === $selected_lang_lc ) {
			$cache_suffix = 'rows_f_join_o_' . md5( (int) $ver . '|g:' . $group_col . '|c:' . ( $is_translated ? 't' : 'x' ) . '|' . $selected_original );
			return (array) awxct_map_cached(
				$cache_suffix,
				30,
				static function () use ( $tbl_trans_sql, $wp_posts_sql, $group_col, $is_translated, $selected_original ) {
					global $wpdb;

					$join_col = $is_translated ? 'translated_post_id' : 'translation_post_id';

					if ( 'root_post_id' === $group_col ) {
						// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
						return (array) $wpdb->get_results(
							$wpdb->prepare(
								"SELECT t.*,
										tp.post_title AS trans_title,
										tp.post_status AS trans_status,
										t.root_post_id AS _gid
								   FROM `{$tbl_trans_sql}` t
							  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
								  WHERE t.root_post_id = %d
							   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
								$selected_original
							)
						);
						// phpcs:enable
					}

					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						$wpdb->prepare(
							"SELECT t.*,
									tp.post_title AS trans_title,
									tp.post_status AS trans_status,
									t.original_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
						  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
							  WHERE t.original_post_id = %d
						   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
							$selected_original
						)
					);
					// phpcs:enable
				}
			);
		}

		// 2) lang only
		if ( $selected_original <= 0 && '' !== $selected_lang_lc ) {
			$cache_suffix = 'rows_f_join_l_' . md5( (int) $ver . '|g:' . $group_col . '|c:' . ( $is_translated ? 't' : 'x' ) . '|' . $selected_lang_lc );
			return (array) awxct_map_cached(
				$cache_suffix,
				30,
				static function () use ( $tbl_trans_sql, $wp_posts_sql, $group_col, $is_translated, $selected_lang_lc ) {
					global $wpdb;

					$join_col = $is_translated ? 'translated_post_id' : 'translation_post_id';

					if ( 'root_post_id' === $group_col ) {
						// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
						return (array) $wpdb->get_results(
							$wpdb->prepare(
								"SELECT t.*,
										tp.post_title AS trans_title,
										tp.post_status AS trans_status,
										t.root_post_id AS _gid
								   FROM `{$tbl_trans_sql}` t
							  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
								  WHERE LOWER(t.language_code) = %s
							   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
								$selected_lang_lc
							)
						);
						// phpcs:enable
					}

					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						$wpdb->prepare(
							"SELECT t.*,
									tp.post_title AS trans_title,
									tp.post_status AS trans_status,
									t.original_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
						  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
							  WHERE LOWER(t.language_code) = %s
						   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
							$selected_lang_lc
						)
					);
					// phpcs:enable
				}
			);
		}

		// 3) original + lang
		$cache_suffix = 'rows_f_join_ol_' . md5( (int) $ver . '|g:' . $group_col . '|c:' . ( $is_translated ? 't' : 'x' ) . '|' . $selected_original . '|' . $selected_lang_lc );
		return (array) awxct_map_cached(
			$cache_suffix,
			30,
			static function () use ( $tbl_trans_sql, $wp_posts_sql, $group_col, $is_translated, $selected_original, $selected_lang_lc ) {
				global $wpdb;

				$join_col = $is_translated ? 'translated_post_id' : 'translation_post_id';

				if ( 'root_post_id' === $group_col ) {
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						$wpdb->prepare(
							"SELECT t.*,
									tp.post_title AS trans_title,
									tp.post_status AS trans_status,
									t.root_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
						  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
							  WHERE t.root_post_id = %d
							    AND LOWER(t.language_code) = %s
						   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
							$selected_original,
							$selected_lang_lc
						)
					);
					// phpcs:enable
				}

				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					$wpdb->prepare(
						"SELECT t.*,
								tp.post_title AS trans_title,
								tp.post_status AS trans_status,
								t.original_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
					  LEFT JOIN `{$wp_posts_sql}` tp ON tp.ID = t.`{$join_col}`
						  WHERE t.original_post_id = %d
						    AND LOWER(t.language_code) = %s
					   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
						$selected_original,
						$selected_lang_lc
					)
				);
				// phpcs:enable
			}
		);
	}

	/* ==============================
	 * B) WITHOUT join
	 * ============================== */

	// NO FILTERS
	if ( ! $has_filters ) {
		return (array) awxct_map_cached(
			'rows_all_nojoin_' . (int) $ver . '_' . $group_col,
			30,
			static function () use ( $tbl_trans_sql, $group_col ) {
				global $wpdb;

				if ( 'root_post_id' === $group_col ) {
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						"SELECT t.*,
								NULL AS trans_title,
								NULL AS trans_status,
								t.root_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
					   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC
					   LIMIT 300"
					);
					// phpcs:enable
				}

				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					"SELECT t.*,
							NULL AS trans_title,
							NULL AS trans_status,
							t.original_post_id AS _gid
					   FROM `{$tbl_trans_sql}` t
				   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC
				   LIMIT 300"
				);
				// phpcs:enable
			}
		);
	}

	// 1) original only
	if ( $selected_original > 0 && '' === $selected_lang_lc ) {
		$cache_suffix = 'rows_f_nojoin_o_' . md5( (int) $ver . '|g:' . $group_col . '|' . $selected_original );
		return (array) awxct_map_cached(
			$cache_suffix,
			30,
			static function () use ( $tbl_trans_sql, $group_col, $selected_original ) {
				global $wpdb;

				if ( 'root_post_id' === $group_col ) {
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						$wpdb->prepare(
							"SELECT t.*,
									NULL AS trans_title,
									NULL AS trans_status,
									t.root_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
							  WHERE t.root_post_id = %d
						   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
							$selected_original
						)
					);
					// phpcs:enable
				}

				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					$wpdb->prepare(
						"SELECT t.*,
								NULL AS trans_title,
								NULL AS trans_status,
								t.original_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
						  WHERE t.original_post_id = %d
					   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
						$selected_original
					)
				);
				// phpcs:enable
			}
		);
	}

	// 2) lang only
	if ( $selected_original <= 0 && '' !== $selected_lang_lc ) {
		$cache_suffix = 'rows_f_nojoin_l_' . md5( (int) $ver . '|g:' . $group_col . '|' . $selected_lang_lc );
		return (array) awxct_map_cached(
			$cache_suffix,
			30,
			static function () use ( $tbl_trans_sql, $group_col, $selected_lang_lc ) {
				global $wpdb;

				if ( 'root_post_id' === $group_col ) {
					// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					return (array) $wpdb->get_results(
						$wpdb->prepare(
							"SELECT t.*,
									NULL AS trans_title,
									NULL AS trans_status,
									t.root_post_id AS _gid
							   FROM `{$tbl_trans_sql}` t
							  WHERE LOWER(t.language_code) = %s
						   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
							$selected_lang_lc
						)
					);
					// phpcs:enable
				}

				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					$wpdb->prepare(
						"SELECT t.*,
								NULL AS trans_title,
								NULL AS trans_status,
								t.original_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
						  WHERE LOWER(t.language_code) = %s
					   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
						$selected_lang_lc
					)
				);
				// phpcs:enable
			}
		);
	}

	// 3) original + lang
	$cache_suffix = 'rows_f_nojoin_ol_' . md5( (int) $ver . '|g:' . $group_col . '|' . $selected_original . '|' . $selected_lang_lc );
	return (array) awxct_map_cached(
		$cache_suffix,
		30,
		static function () use ( $tbl_trans_sql, $group_col, $selected_original, $selected_lang_lc ) {
			global $wpdb;

			if ( 'root_post_id' === $group_col ) {
				// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				return (array) $wpdb->get_results(
					$wpdb->prepare(
						"SELECT t.*,
								NULL AS trans_title,
								NULL AS trans_status,
								t.root_post_id AS _gid
						   FROM `{$tbl_trans_sql}` t
						  WHERE t.root_post_id = %d
						    AND LOWER(t.language_code) = %s
					   ORDER BY t.root_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
						$selected_original,
						$selected_lang_lc
					)
				);
				// phpcs:enable
			}

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return (array) $wpdb->get_results(
				$wpdb->prepare(
					"SELECT t.*,
							NULL AS trans_title,
							NULL AS trans_status,
							t.original_post_id AS _gid
					   FROM `{$tbl_trans_sql}` t
					  WHERE t.original_post_id = %d
					    AND LOWER(t.language_code) = %s
				   ORDER BY t.original_post_id ASC, LOWER(t.language_code) ASC, t.id ASC",
					$selected_original,
					$selected_lang_lc
				)
			);
			// phpcs:enable
		}
	);
}

/* =========================================================
 * Data
 * ========================================================= */

$original_choices = awxct_original_choices( $group_col, $tbl_trans_sql, $wp_posts_sql, $awxct_map_ver );
$lang_choices     = awxct_language_choices( $tbl_lang_sql, $awxct_map_ver );
$rows             = awxct_load_translations( $group_col, $post_col, $selected_original, $selected_lang, $tbl_trans_sql, $wp_posts_sql, $awxct_map_ver );

/**
 * Title cache per original (cached).
 */
$titles = array();

if ( ! empty( $rows ) ) {
	$ids = array();
	foreach ( $rows as $r ) {
		if ( isset( $r->{$group_col} ) ) {
			$ids[] = (int) $r->{$group_col};
		}
	}
	$ids = array_values( array_unique( array_filter( $ids ) ) );

	if ( ! empty( $ids ) ) {
		$cache_key = 'titles_posts_' . md5( (int) $awxct_map_ver . '|' . wp_json_encode( $ids ) );

		$posts = (array) awxct_map_cached(
			$cache_key,
			60,
			static function () use ( $ids ) {
				return get_posts(
					array(
						'post_type'      => 'any',
						'post_status'    => 'any',
						'posts_per_page' => count( $ids ),
						'post__in'       => $ids,
						'orderby'        => 'post__in',
						'no_found_rows'  => true,
						'fields'         => 'ids',
					)
				);
			}
		);

		if ( ! empty( $posts ) ) {
			foreach ( $posts as $pid ) {
				$pid = (int) $pid;
				$titles[ $pid ] = get_the_title( $pid );
				if ( '' === (string) $titles[ $pid ] ) {
					$titles[ $pid ] = '#' . $pid;
				}
			}
		}
	}
}

if ( $awxct_cleaned > 0 ) {
	echo '<div class="updated"><p>';
	printf(
		/* translators: %d: Number of orphan translation entries that were cleaned up. */
		esc_html__( 'Cleaned %d orphan translation entries (posts were already deleted).', 'anywarex-clone-translate' ),
		(int) $awxct_cleaned
	);
	echo '</p></div>';
}
?>

<div class="wrap">
	<?php if ( function_exists( 'awxct_admin_header' ) ) : ?>
		<?php awxct_admin_header( __( 'Active Translation Mapping', 'anywarex-clone-translate' ) ); ?>
	<?php else : ?>
		<h1><?php echo esc_html__( 'Active Translation Mapping', 'anywarex-clone-translate' ); ?></h1>
	<?php endif; ?>

	<form method="get" action="">
		<input type="hidden" name="page" value="anywarex-clone-translate-mapping" />
		<table class="form-table">
			<tbody>
			<tr>
				<th scope="row">
					<label for="awxct_original_id"><?php echo esc_html__( 'Original page', 'anywarex-clone-translate' ); ?></label>
				</th>
				<td>
					<select name="original_id" id="awxct_original_id">
						<option value="0"><?php echo esc_html__( '— all —', 'anywarex-clone-translate' ); ?></option>
						<?php foreach ( (array) $original_choices as $o ) : ?>
							<option value="<?php echo esc_attr( (string) (int) $o->id ); ?>" <?php selected( $selected_original, (int) $o->id ); ?>>
								<?php
								$opt_label = ! empty( $o->label ) ? $o->label : '#' . (int) $o->id;
								echo esc_html( $opt_label . ' (ID ' . (int) $o->id . ')' );
								?>
							</option>
						<?php endforeach; ?>
					</select>
				</td>

				<th scope="row" style="padding-left:40px;">
					<label for="awxct_language_code"><?php echo esc_html__( 'Language', 'anywarex-clone-translate' ); ?></label>
				</th>
				<td>
					<select name="language_code" id="awxct_language_code">
						<option value=""><?php echo esc_html__( '— all —', 'anywarex-clone-translate' ); ?></option>
						<?php foreach ( (array) $lang_choices as $l ) : ?>
							<option value="<?php echo esc_attr( (string) $l->code ); ?>" <?php selected( strtolower( $selected_lang ), strtolower( (string) $l->code ) ); ?>>
								<?php echo esc_html( strtoupper( (string) $l->code ) . ' – ' . (string) $l->name ); ?>
							</option>
						<?php endforeach; ?>
					</select>
				</td>
			</tr>
			</tbody>
		</table>
		<p class="submit">
			<button class="button button-primary" type="submit">
				<?php echo esc_html__( 'Filter', 'anywarex-clone-translate' ); ?>
			</button>
			<?php if ( $selected_original || '' !== $selected_lang ) : ?>
				<a class="button" href="<?php echo esc_url( admin_url( 'admin.php?page=anywarex-clone-translate-mapping' ) ); ?>">
					<?php echo esc_html__( 'Reset', 'anywarex-clone-translate' ); ?>
				</a>
			<?php endif; ?>
		</p>
	</form>

	<?php
	if ( ! empty( $rows ) ) :
		$by_original = array();
		foreach ( $rows as $r ) {
			$oid = isset( $r->{$group_col} ) ? (int) $r->{$group_col} : 0;
			if ( $oid <= 0 ) {
				continue;
			}
			if ( ! isset( $by_original[ $oid ] ) ) {
				$by_original[ $oid ] = array();
			}
			$by_original[ $oid ][] = $r;
		}

		foreach ( $by_original as $oid => $list ) :
			$title = isset( $titles[ $oid ] ) ? $titles[ $oid ] : '#' . (int) $oid;

			$by_lang = array();
			foreach ( $list as $r ) {
				$lc_raw = isset( $r->language_code ) ? $r->language_code : '';
				$lc     = strtolower( trim( (string) $lc_raw ) );
				if ( ! isset( $by_lang[ $lc ] ) ) {
					$by_lang[ $lc ] = array();
				}
				$by_lang[ $lc ][] = $r;
			}

			foreach ( $by_lang as $lc => $rows_lang ) :
				$badge_text = '';
				if ( '' !== $lc ) {
					$badge_text = strtoupper( $lc );
				} else {
					if ( apply_filters( 'awxct_show_clone_badge', true ) ) {
						$badge_text = __( 'Clone', 'anywarex-clone-translate' );
					}
				}
				?>
				<h2 style="margin-top:25px;">
					<?php echo esc_html( $title ); ?>
					<?php if ( $badge_text ) : ?>
						<small>(<?php echo esc_html( $badge_text ); ?>)</small>
					<?php endif; ?>
				</h2>

				<?php
				if ( ! awxct_allow_multi_per_lang() && count( $rows_lang ) > 1 ) {
					$lic_url = admin_url( 'admin.php?page=awxct-plg-ct-license' );
					echo '<div class="notice notice-warning" style="margin:10px 0;"><p>'
						. esc_html__( 'Free: There are multiple translation sites for this language. New entries are blocked in the free version. Switching is possible.', 'anywarex-clone-translate' )
						. ' <a href="' . esc_url( $lic_url ) . '">' . esc_html__( 'Switch to Pro', 'anywarex-clone-translate' ) . '</a>'
						. '</p></div>';
				}
				?>

				<form method="post">
					<?php wp_nonce_field( 'awxct_set_active', 'awxct_nonce' ); ?>
					<input type="hidden" name="original_id" value="<?php echo esc_attr( (string) (int) $oid ); ?>">
					<input type="hidden" name="language_code" value="<?php echo esc_attr( (string) $lc ); ?>">

					<table class="widefat fixed striped">
						<thead>
						<tr>
							<th style="width:120px;"><?php echo esc_html__( 'Entry ID', 'anywarex-clone-translate' ); ?></th>
							<th><?php echo esc_html__( 'Translated page', 'anywarex-clone-translate' ); ?></th>
							<th style="width:120px;"><?php echo esc_html__( 'Status', 'anywarex-clone-translate' ); ?></th>
							<th style="width:140px;"><?php echo esc_html__( 'Active', 'anywarex-clone-translate' ); ?></th>
							<th style="width:210px;"><?php echo esc_html__( 'Actions', 'anywarex-clone-translate' ); ?></th>
						</tr>
						</thead>
						<tbody>
						<?php foreach ( (array) $rows_lang as $r ) : ?>
							<?php
							$is_active    = isset( $r->is_active ) ? (int) $r->is_active : 0;
							$trans_id     = $post_col ? (int) $r->{$post_col} : 0;
							$trans_title  = isset( $r->trans_title ) ? (string) $r->trans_title : '';
							$trans_status = isset( $r->trans_status ) ? (string) $r->trans_status : '';

							$label = $trans_title ? $trans_title : ( $trans_id ? '#' . (int) $trans_id : '—' );

							if ( ! $trans_id ) {
								$status_label = '—';
							} elseif ( 'trash' === $trans_status ) {
								$status_label = __( 'In Trash', 'anywarex-clone-translate' );
							} elseif ( 'draft' === $trans_status ) {
								$status_label = __( 'Draft', 'anywarex-clone-translate' );
							} elseif ( 'pending' === $trans_status ) {
								$status_label = __( 'Pending', 'anywarex-clone-translate' );
							} else {
								$status_label = __( 'OK', 'anywarex-clone-translate' );
							}

							$in_trash     = ( 'trash' === $trans_status );
							$can_activate = ( $trans_id && ! $in_trash );
							$row_class    = $is_active ? 'awxct-active' : '';
							?>
							<tr class="<?php echo esc_attr( $row_class ); ?>">
								<td><?php echo esc_html( (string) (int) $r->id ); ?></td>
								<td>
									<?php if ( $trans_id ) : ?>
										<?php $edit = $can_activate ? get_edit_post_link( $trans_id, '' ) : ''; ?>
										<?php if ( $edit ) : ?>
											<a href="<?php echo esc_url( $edit ); ?>">
												<?php echo esc_html( $label ); ?>
											</a>
										<?php else : ?>
											<?php echo esc_html( $label ); ?>
										<?php endif; ?>
									<?php else : ?>
										<?php echo esc_html( $label ); ?>
									<?php endif; ?>
								</td>
								<td><?php echo esc_html( $status_label ); ?></td>
								<td>
									<?php if ( $can_activate ) : ?>
										<label>
											<input type="radio"
												name="active"
												value="<?php echo esc_attr( (string) (int) $r->id ); ?>"
												<?php checked( $is_active, 1 ); ?>>
											<?php echo esc_html__( 'Mark as active', 'anywarex-clone-translate' ); ?>
										</label>
									<?php else : ?>
										<span class="description">
											<?php echo esc_html__( 'Not available', 'anywarex-clone-translate' ); ?>
										</span>
									<?php endif; ?>
								</td>
								<td>
									<?php if ( $trans_id ) : ?>
										<?php
										$unlink_url = wp_nonce_url(
											admin_url( 'admin-post.php?action=awxct_unlink_translation&post_id=' . (int) $trans_id ),
											'awxct_unlink_translation',
											'awxct_unlink_nonce'
										);
										?>
										<a href="<?php echo esc_url( $unlink_url ); ?>" class="button button-link-delete">
											<?php echo esc_html__( 'Remove from translations', 'anywarex-clone-translate' ); ?>
										</a>
									<?php else : ?>
										<em><?php echo esc_html__( 'No linked page', 'anywarex-clone-translate' ); ?></em>
									<?php endif; ?>
								</td>
							</tr>
						<?php endforeach; ?>
						</tbody>
					</table>

					<p style="margin-top:10px;">
						<button type="submit" name="awxct_save_active" class="button button-primary">
							<?php echo esc_html__( 'Save', 'anywarex-clone-translate' ); ?>
						</button>
					</p>
				</form>
			<?php endforeach; ?>
		<?php endforeach; ?>
	<?php else : ?>
		<p><em><?php echo esc_html__( 'No entries for the current filter.', 'anywarex-clone-translate' ); ?></em></p>
	<?php endif; ?>
</div>
