<?php

/**
 * Class ETM_String_Translation_Helper
 *
 * This class provides helper functions for regular and gettext string translation.
 */
class ETM_String_Translation_Helper {
	/**
	 * The ETM_Query object for database queries.
	 *
	 * @var ETM_Query $etm_query
	 */
	protected $etm_query;
	/**
	 * The ETM_String_Translation object for string translation.
	 *
	 * @var ETM_String_Translation $string_translation
	 */
	protected $string_translation;
	/**
	 * The settings array.
	 *
	 * @var array $settings.
	 */
	protected $settings;

	/**
	 * Check if the request is an AJAX request and verify the security nonce.
	 *
	 * @param string $type The type of string translation (regular, gettext, slugs).
	 * @param string $get_or_save The operation type (get or save).
	 *
	 * @return bool True if the request is valid, false otherwise.
	 */
	public function check_ajax( $type, $get_or_save ) {
		if ( defined( 'DOING_AJAX' ) && DOING_AJAX ) {
			check_ajax_referer( 'string_translation_' . $get_or_save . '_strings_' . $type, 'security' );

			$action = ( $get_or_save === 'save' ) ? 'etm_save_translations_' : 'etm_string_translation_get_strings_';
			if ( isset( $_POST['action'] ) && $_POST['action'] === $action . $type ) {
				return true;
			}
		}
		wp_die();
	}

	/**
	 * Get sanitized query arguments for string translation.
	 *
	 * @param string $string_type The type of string translation (regular, gettext, slugs).
	 *
	 * @return array An array of sanitized query arguments.
	 */
	public function get_sanitized_query_args( $string_type ) {
		$etm = ETM_eTranslation_Multilingual::get_etm_instance();
		if ( ! $this->string_translation ) {
			$this->string_translation = $etm->get_component( 'string_translation' );
		}
		if ( ! $this->etm_query ) {
			$this->etm_query = $etm->get_component( 'query' );
		}
		if ( ! $this->settings ) {
			$etm_settings   = $etm->get_component( 'settings' );
			$this->settings = $etm_settings->get_settings();
		}
		$query_args   = array();
		$posted_query = ( empty( $_POST['query'] ) ) ? array() : json_decode( wp_kses_post( wp_unslash( $_POST['query'] ) ), true );

		// Translation status
		$translation_status_filters = $this->string_translation->get_translation_status_filters();
		$query_args['status']       = array();
		foreach ( $translation_status_filters['translation_status'] as $translation_status_key => $value ) {
			if ( ! empty( $posted_query[ $translation_status_key ] ) && ( $posted_query[ $translation_status_key ] === true || $posted_query[ $translation_status_key ] === 'true' ) ) {
				$constant_func_name     = 'get_constant_' . $translation_status_key;
				$query_args['status'][] = $this->etm_query->$constant_func_name();
			}
		}
		if ( count( $query_args['status'] ) === 3 ) {
			// If all 3 states are true, then consider the query as if no special translation status requirement was requested.
			$query_args['status'] = array();
		}

		// Search string
		$query_args['s'] = ( empty( $posted_query['s'] ) ) ? '' : trim( esc_sql( sanitize_text_field( $posted_query['s'] ) ) );

		// Page
		$query_args['page'] = ( empty( $posted_query['page'] ) ? 1 : ( ( intval( $posted_query['page'] ) < 1 ) ? 1 : intval( $posted_query['page'] ) ) );

		// Language
		$query_args['language'] = ( ! empty( $posted_query['language'] ) && in_array( $posted_query['language'], $this->settings['translation-languages'] ) ) ?
			$posted_query['language'] : '';

		// Order
		$query_args['order']   = ( empty( $posted_query['order'] ) || ! in_array(
			$posted_query['order'],
			array(
				'asc',
				'desc',
			)
		) ) ? '' : sanitize_text_field( $posted_query['order'] );
		$query_args['orderby'] = ( empty( $posted_query['orderby'] ) ) ? '' : sanitize_text_field( $posted_query['orderby'] );

		// Specific filters for each string type
		$string_types                = $this->string_translation->get_string_types();
		$specific_string_type_config = $string_types[ $string_type ];
		foreach ( $specific_string_type_config['filters'] as $specific_filter_key => $specific_filter_values ) {
			$query_args[ $specific_filter_key ] =
				( ! empty( $posted_query[ $specific_filter_key ] ) && isset( $specific_filter_values[ $posted_query[ $specific_filter_key ] ] ) ) ?
					$posted_query[ $specific_filter_key ] : '';
		}

		return apply_filters( 'etm_sanitized_query_args', $query_args, $string_type, $string_types );
	}

	/**
	 * Add WHERE clauses to the query based on the provided array of clauses.
	 *
	 * @param string $query The initial query string.
	 * @param array  $where_clauses An array of WHERE clauses to be added to the query.
	 *
	 * @return string The updated query string with added WHERE clauses.
	 */
	public function add_where_clauses_to_query( $query, $where_clauses ) {
		if ( count( $where_clauses ) > 0 ) {
			$query .= 'WHERE ';
			foreach ( $where_clauses as $where_clause ) {
				$query .= $where_clause . ' AND ';
			}
			$query = rtrim( $query, ' AND' ) . ' ';
		}

		return $query;
	}

	/**
	 * Get a language table column-based query for the provided filters and sanitized arguments.
	 *
	 * @param array $filters An array of column filters for the query.
	 * @param array $translation_languages An array of translation languages.
	 * @param array $sanitized_args An array of sanitized query arguments.
	 *
	 * @return array An array of WHERE clauses based on the provided filters and arguments.
	 */
	public function get_language_table_column_based_query_for_filters( $filters, $translation_languages, $sanitized_args ) {
		$where_clauses = array();
		foreach ( $filters as $column_name => $filter_name ) {
			if ( ! empty( $sanitized_args[ $filter_name ] ) ) {
				$column_query = '( ';
				foreach ( $translation_languages as $language ) {
					$column_query .= $this->get_column_query( $column_name, $sanitized_args[ $filter_name ], esc_sql( sanitize_text_field( $language ) ) ) . ' OR ';
				}
				$column_query    = rtrim( $column_query, ' OR ' ) . ' ) ';
				$where_clauses[] = $column_query;
			}
		}

		return $where_clauses;
	}

	/**
	 * Get a column-based query for the provided column name, column values, and language.
	 *
	 * @param string $column_name The name of the column.
	 * @param mixed  $column_values The values of the column (can be an array or a single value).
	 * @param string $language The translation language.
	 *
	 * @return string The column-based query.
	 */
	public function get_column_query( $column_name, $column_values, $language ) {
		$query = '';

		if ( is_array( $column_values ) ) {
			foreach ( $column_values as $value ) {
				$query .= $language . '.' . $column_name . ' = ' . $value . ' OR ';
			}
		} else {
			$query .= $language . '.' . $column_name . ' = ' . $column_values . ' OR ';
		}
		$query = rtrim( $query, ' OR ' );

		return $query;
	}

	/**
	 * Get the SQL JOIN query for joining the language table with the given table name and language.
	 *
	 * @param string $table_name The name of the language table.
	 * @param string $language The translation language.
	 *
	 * @return string The SQL JOIN query.
	 */
	public function get_join_language_table_sql( $table_name, $language ) {
		return 'LEFT JOIN ' . $table_name . ' AS ' . $language . ' ON ' . $language . '.original_id = original_strings.id ';
	}

	/**
	 * Get the SQL JOIN query for joining the meta table with the given table name.
	 *
	 * @param string $table_name The name of the meta table.
	 *
	 * @return string The SQL JOIN query.
	 */
	public function get_join_meta_table_sql( $table_name ) {
		return 'LEFT JOIN ' . $table_name . ' AS original_meta ON original_meta.original_id = original_strings.id ';
	}

	/**
	 * Get the results of original strings matching the provided filters.
	 *
	 * @param string $type The type of string translation (regular, gettext, slugs).
	 * @param string $original_table The name of the original strings table.
	 * @param string $original_meta_table The name of the original meta table.
	 * @param string $get_table_name_func The function name to get the language table name.
	 * @param array  $filters An array of filters for the query.
	 *
	 * @return array An array containing 'original_ids', 'originals', and 'total_item_count'.
	 */
	public function get_originals_results( $type, $original_table, $original_meta_table, $get_table_name_func, $filters ) {
		$this->check_ajax( $type, 'get' );

		$etm                = ETM_eTranslation_Multilingual::get_etm_instance();
		$string_translation = $etm->get_component( 'string_translation' );
		$etm_query          = $etm->get_component( 'query' );
		$etm_settings       = $etm->get_component( 'settings' );
		$settings           = $etm_settings->get_settings();
		$config             = $string_translation->get_configuration_options();
		$sanitized_args     = $this->get_sanitized_query_args( $type );
		$where_clauses      = array();

		if ( ! empty( $sanitized_args['translation-block-type'] ) ) {
			$mapping_array                            = array(
				'individual_string' => 0,
				'translation_block' => 1,
			);
			$sanitized_args['translation-block-type'] = $mapping_array[ $sanitized_args['translation-block-type'] ];
		}

		// Language filter
		if ( empty( $sanitized_args['language'] ) ) {
			// All language tables are needed for table joining.
			$translation_languages = array();
			foreach ( $settings['translation-languages'] as $language ) {
				// Regular strings don't have the default language table.
				// English language does not react to "Not translated/Manually/Automatically" if no specific language is selected.
				if ( $language === $settings['default-language'] && $type === 'regular' || ( $type === 'gettext' && $this->string_starts_with( $language, 'en' ) ) ) {
					continue;
				}
				$translation_languages[] = $language;
			}
		} else {
			// Only the current language is needed for table joining.
			$translation_languages = array( $sanitized_args['language'] );
		}

		$counting_query = 'SELECT COUNT(*) ';
		$results_query  = 'SELECT DISTINCT original_strings.id, original_strings.original ';
		$results_query .= ( $type === 'gettext' ) ? ', original_strings.domain, original_strings.context, original_strings.original_plural ' : '';
		$query          = 'FROM `' . sanitize_text_field( $original_table ) . '` AS original_strings ';

		if ( ! empty( $sanitized_args['status'] ) || ! empty( $sanitized_args['translation-block-type'] ) ) {
			// Joining translation tables is needed only when we have filter for translation status or for translation block type.
			foreach ( $translation_languages as $language ) {
				$query .= $this->get_join_language_table_sql( sanitize_text_field( $etm_query->$get_table_name_func( $language ) ), esc_sql( sanitize_text_field( $language ) ) );
			}

			// Translation status and block type
			$where_clauses = array_merge(
				$where_clauses,
				$this->get_language_table_column_based_query_for_filters( $filters, $translation_languages, $sanitized_args )
			);
		}

		// Original meta table only needed when the filter by type is set.
		if ( ! empty( $sanitized_args['type'] ) && $sanitized_args['type'] !== 'etm_default' ) {
			$query .= $this->get_join_meta_table_sql( $original_meta_table );
		}

		// Filter by type (email)
		if ( ! empty( $sanitized_args['type'] ) && $sanitized_args['type'] !== 'etm_default' ) {
			if ( $sanitized_args['type'] === 'email' ) {
				$where_clauses[] = "original_meta.meta_key='in_email' and original_meta.meta_value = 'yes' ";
			}
		}

		// Search
		if ( ! empty( $sanitized_args['s'] ) ) {
			$where_clauses[] = 'original_strings.original LIKE \'%' . $sanitized_args['s'] . '%\' ';
		}

		if ( ! empty( $sanitized_args['domain'] ) ) {
			$where_clauses[] = 'original_strings.domain LIKE \'%' . $sanitized_args['domain'] . '%\' ';
		}

		$query = $this->add_where_clauses_to_query( $query, $where_clauses );

		$counting_query .= $query;

		// Order by
		if ( ! empty( $sanitized_args['orderby'] ) ) {
			if ( $sanitized_args['orderby'] === 'original' ) {
				$query .= 'ORDER BY original_strings.' . $sanitized_args['orderby'] . ' ' . $sanitized_args['order'] . ' ';
			}
		}

		// Pagination
		$query .= 'LIMIT ' . ( $sanitized_args['page'] - 1 ) * $config['items_per_page'] . ', ' . $config['items_per_page'];

		global $wpdb;
		$total_item_count = $wpdb->get_var( $counting_query );
		$original_ids     = array();
		$originals        = array();
		if ( $total_item_count > 0 ) {
			// Query search to retrieve IDs of original strings needed.
			$results_query .= $query;
			$originals      = $wpdb->get_results( $results_query, OBJECT_K );
			$original_ids   = array_keys( $originals );
		}

		return array(
			'original_ids'     => $original_ids,
			'originals'        => $originals,
			'total_item_count' => $total_item_count,
		);
	}

	/**
	 * Check if a string starts with a given prefix.
	 *
	 * @param string $haystack The string to check.
	 * @param string $needle The prefix to search for.
	 *
	 * @return bool True if the string starts with the prefix, false otherwise.
	 */
	private function string_starts_with( $haystack, $needle ) {
		return (string) $needle !== '' && strncmp( $haystack, $needle, strlen( $needle ) ) === 0;
	}
}
