<?php

/**
 * Class ETM_Query
 *
 * Queries for translations in custom etm tables.
 */
class ETM_Query {

	/**
	 * @var string The name of the custom database table used for storing translations.
	 */
	protected $table_name;

	/**
	 * @var object The WordPress database object for database operations.
	 */
	public $db;

	/**
	 * @var array Settings.
	 */
	protected $settings;

	/**
	 * @var ETM_URL_Converter Handles URL conversion.
	 */
	protected $url_converter;

	/**
	 * @var ETM_Translation_Render Handles rendering of translations.
	 */
	protected $translation_render;

	/**
	 * @var ETM_Error_Manager Handles error management for translation operations.
	 */
	protected $error_manager;

	/**
	 * @var ETM_Check_Invalid_Text Checks for invalid or unsupported characters in translations.
	 */
	protected $check_invalid_text;

	/**
	 * @var array Keeps track of the existing plugin tables.
	 */
	protected $tables_exist = array();

	/**
	 * @var string|null Holds the database SQL version.
	 */
	protected $db_sql_version = null;

	/**
	 * @var bool|null Should gettext be normalized
	 */
	protected $gettext_normalized = null;

	/* gettext query components */

	/**
	 * @var ETM_Gettext_Normalization
	 */
	protected $gettext_table_creation;

	/**
	 * @var ETM_Gettext_Table_Normalization
	 */
	protected $gettext_normalization;

	/**
	 * @var ETM_Gettext_Insert_Update
	 */
	protected $gettext_insert_update;

	/**
	 * Constants representing translation status.
	 */
	const NOT_TRANSLATED     = 0;
	const MACHINE_TRANSLATED = 1;
	const HUMAN_REVIEWED     = 2;

	/**
	 * Constants representing block types.
	 */
	const BLOCK_TYPE_REGULAR_STRING = 0;
	const BLOCK_TYPE_ACTIVE         = 1;
	const BLOCK_TYPE_DEPRECATED     = 2;

	/**
	 * ETM_Query constructor.
	 *
	 * @param $settings
	 */
	public function __construct( $settings ) {
		global $wpdb;
		$this->db       = $wpdb;
		$this->settings = $settings;

		$this->gettext_normalization  = new ETM_Gettext_Normalization( $settings );
		$this->gettext_table_creation = new ETM_Gettext_Table_Creation( $settings );
		$this->gettext_insert_update  = new ETM_Gettext_Insert_Update( $settings );
	}

	/**
	 * Retrieves specified component from this class
	 *
	 * @param string $component Name of component to retrieve.
	 * @return object
	 */
	public function get_query_component( $component ) {
		return $this->$component;
	}


	/**
	 * Return an array of all the active translation blocks
	 *
	 * @param $language_code
	 *
	 * @return array|null|object
	 */
	public function get_all_translation_blocks( $language_code ) {
		$query      = 'SELECT original, id, block_type, status FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE block_type = ' . self::BLOCK_TYPE_ACTIVE . ' OR block_type = ' . self::BLOCK_TYPE_DEPRECATED;
		$dictionary = $this->db->get_results( $query, OBJECT_K );
		return $dictionary;
	}

	/**
	 * Returns the translations for the provided strings.
	 *
	 * Only returns results where there actually is a translation ( != NOT_TRANSLATED )
	 *
	 * @param array  $strings_array      Array of original strings to search for.
	 * @param string $language_code     Language code to query for.
	 * @return object                   Associative Array of objects with translations where key is original string.
	 */
	public function get_existing_translations( $strings_array, $language_code, $block_type = null ) {
		if ( !is_array( $strings_array ) || count ( $strings_array ) == 0 || !in_array( $language_code, $this->settings['translation-languages'] ) || $language_code === $this->settings['default-language'] ){
			return array();
		}
		if ( $block_type == null ) {
			$and_block_type = '';
		} else {
			$and_block_type = ' AND block_type = ' . $block_type;
		}
		$query = 'SELECT original,translated, status,id FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE status != ' . self::NOT_TRANSLATED . $and_block_type . " AND translated <>'' AND original IN ";

		$placeholders = array();
		$values       = array();
		foreach ( $strings_array as $string ) {
			$placeholders[] = '%s';
			$values[]       = $string;
		}

		$query         .= '( ' . implode( ', ', $placeholders ) . ' )';
		$prepared_query = $this->db->prepare( $query, $values );
		$dictionary     = $this->db->get_results( $prepared_query, OBJECT_K );

		if ( ! $this->check_invalid_text ) {
			$etm                      = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->check_invalid_text = $etm->get_component( 'check_invalid_text' );
		}
		$dictionary = $this->check_invalid_text->get_existing_translations_without_invalid_text( $dictionary, $prepared_query, $strings_array, $language_code, $block_type );

		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_existing_translations()' ) );

		if ( $this->db->last_error !== '' && ! $this->check_invalid_text->is_invalid_data_error() ) {
			$dictionary = false;
		}

		$dictionary = apply_filters( 'etm_get_existing_translations', $dictionary, $prepared_query, $strings_array, $language_code, $block_type );
		if ( is_array( $dictionary ) && count( $dictionary ) === 0 && ! $this->table_exists( $this->get_table_name( $language_code ) ) && ! $this->check_invalid_text->is_invalid_data_error() ) {
			// if table is missing then last_error is empty for the select query
			$this->maybe_record_automatic_translation_error( array( 'details' => 'Missing table ' . $this->get_table_name( $language_code ) . ' . To regenerate tables, try going to Settings->WEB-T – eTranslation Multilingual->General tab and Save Settings.' ), true );
		}

		return $dictionary;
	}

	/**
	 * Return constant used for entries without translations.
	 *
	 * @return int
	 */
	public function get_constant_not_translated() {
		return self::NOT_TRANSLATED;
	}

	/**
	 * Return constant used for entries with machine translation.
	 *
	 * @return int
	 */
	public function get_constant_machine_translated() {
		return self::MACHINE_TRANSLATED;
	}

	/**
	 * Return constant used for entries edited by humans.
	 *
	 * @return int
	 */
	public function get_constant_human_reviewed() {
		return self::HUMAN_REVIEWED;
	}

	/**
	 * Return constant used for individual strings, not part of a translation block
	 *
	 * @return int
	 */
	public function get_constant_block_type_regular_string() {
		return self::BLOCK_TYPE_REGULAR_STRING;
	}

	/**
	 * Return constant used for a translation block
	 *
	 * @return int
	 */
	public function get_constant_block_type_active() {
		return self::BLOCK_TYPE_ACTIVE;
	}

	/**
	 * Return constant used for a translation block, no longer in use (i.e. after being split )
	 *
	 * @return int
	 */
	public function get_constant_block_type_deprecated() {
		return self::BLOCK_TYPE_DEPRECATED;
	}

	/**
	 * Check if table for specific language exists.
	 *
	 * If the table does not exists it is created.
	 *
	 * @param string $language_code
	 */
	public function check_table( $default_language, $language_code ) {
		$table_name = sanitize_text_field( $this->get_table_name( $language_code, $default_language ) );
		if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
			// table not in database. Create new table
			$charset_collate = $this->db->get_charset_collate();

			$sql = 'CREATE TABLE `' . $table_name . '`(
                                    id bigint(20) AUTO_INCREMENT NOT NULL PRIMARY KEY,
                                    original  longtext NOT NULL,
                                    translated  longtext,
                                    status int(20) DEFAULT ' . $this::NOT_TRANSLATED . ',
                                    block_type int(20) DEFAULT ' . $this::BLOCK_TYPE_REGULAR_STRING . ",
                                    original_id bigint(20) DEFAULT NULL,
                                    UNIQUE KEY id (id) )
                                     $charset_collate;";
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			$sql_index = 'CREATE INDEX index_name ON `' . $table_name . '` (original(100));';
			$this->db->query( $sql_index );

			$this->maybe_record_automatic_translation_error( array( 'details' => 'Error creating regular tables' ) );

			if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
				// table still doesn't exist after creation
				$this->maybe_record_automatic_translation_error( array( 'details' => 'Error creating regular strings tables' ), true );
			} else {
				// full text index for original
				$sql_index = 'CREATE FULLTEXT INDEX original_fulltext ON `' . $table_name . '`(original);';
				$this->db->query( $sql_index );

				// syncronize all translation blocks.
				$this->copy_all_translation_blocks_into_table( $default_language, $language_code );
			}
		} else {
			$this->check_for_block_type_column( $language_code, $default_language );
			$this->check_for_original_id_column( $language_code, $default_language );
		}
	}

	/**
	 * Check if table for machine translation logs exists.
	 *
	 * If the table does not exists it is created.
	 *
	 * @param string $language_code
	 */
	public function check_machine_translation_log_table() {
		$table_name = $this->db->prefix . 'etm_machine_translation_log';
		if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
			// table not in database. Create new table
			$charset_collate = $this->db->get_charset_collate();

			$sql = "CREATE TABLE `{$table_name}`(
                                    id bigint(20) AUTO_INCREMENT NOT NULL PRIMARY KEY,
                                    url text,
                                    timestamp datetime DEFAULT '0000-00-00 00:00:00',
                                    strings longtext,
                                    characters text,
                                    response longtext,
                                    lang_source text,
                                    lang_target text,
                                    UNIQUE KEY id (id) )
                                     {$charset_collate};";
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			$this->maybe_record_automatic_translation_error( array( 'details' => 'Error creating machine translation log tables' ) );

			if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
				$this->maybe_record_automatic_translation_error( array( 'details' => 'Error creating machine translation log tables' ), true );
				// something failed. Table still doesn't exist.
				return false;
			}
			// table exists
			return true;
		}
		// table exists
		return true;
	}

	public function copy_all_translation_blocks_into_table( $default_language, $language_code ) {
		$all_table_names = $this->get_all_table_names( $default_language, array( $language_code ) );
		if ( count( $all_table_names ) > 0 ) {
			$source_table_name = $all_table_names[0];

			// copy translation blocks from table name of this language
			$source_language = apply_filters( 'etm_source_language_translation_blocks', '', $default_language, $language_code );
			if ( $source_language != '' ) {
				$source_table_name = $this->get_table_name( $source_language, $default_language );
			}

			$destination_table_name = $this->get_table_name( $language_code, $default_language );

			// get all tb from $source_table_name and copy to $destination_table_name
			$sql = 'INSERT INTO `' . $destination_table_name . '` (id, original, translated, status, block_type) SELECT NULL, original, "", ' . $this::NOT_TRANSLATED . ', block_type FROM `' . $source_table_name . '` WHERE block_type = ' . self::BLOCK_TYPE_ACTIVE . ' OR block_type = ' . self::BLOCK_TYPE_DEPRECATED;
			$this->db->query( $sql );
		}
	}

	/**
	 * Check if the original string table exists
	 *
	 * If the table does not exists it is created.
	 */
	public function check_original_table() {

		$table_name = $this->get_table_name_for_original_strings();
		if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
			// table not in database. Create new table
			$charset_collate = $this->db->get_charset_collate();

			$sql = 'CREATE TABLE `' . $table_name . "`(
                                    id bigint(20) AUTO_INCREMENT NOT NULL PRIMARY KEY,
                                    original TEXT NOT NULL )
                                     $charset_collate;";
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			$sql_index = 'CREATE INDEX index_original ON `' . $table_name . '` (original(100));';
			$this->db->query( $sql_index );
		}
	}

	/**
	 * Function that takes care of inserting  original strings from dictionary to original_strings table
	 */
	public function original_ids_insert( $language_code, $inferior_limit, $batch_size ) {

		// don't do anything for default language
		if ( $this->settings['default-language'] === $language_code ) {
			return 0;
		}

		if ( ! $this->error_manager ) {
			$etm                 = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->error_manager = $etm->get_component( 'error_manager' );
		}

		$originals_table = $this->get_table_name_for_original_strings();
		$table_name      = sanitize_text_field( $this->get_table_name( $language_code, $this->settings['default-language'] ) );

		/*
		*  select all string that are in the dictionary table and are not in the original tables and insert them in the original
		*/
		$insert_records = $this->db->query( $this->db->prepare( "INSERT INTO `$originals_table` (original) SELECT DISTINCT ( BINARY t1.original ) FROM `$table_name` t1 LEFT JOIN `$originals_table` t2 ON ( t2.original = t1.original AND t2.original = BINARY t1.original ) WHERE t2.original IS NULL AND t1.id > %d AND t1.id <= %d AND LENGTH(t1.original) < 20000", $inferior_limit, ( $inferior_limit + $batch_size ) ) );

		if ( ! empty( $this->db->last_error ) ) {
			$this->error_manager->record_error( array( 'last_error_insert_original_strings' => $this->db->last_error ) );
		}

		return $insert_records;

	}

	/**
	 * Function that makes sure we don't have duplicates in original_strings table
	 * It is executed after we inserted all the strings
	 */
	public function original_ids_cleanup() {
		if ( ! $this->error_manager ) {
			$etm                 = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->error_manager = $etm->get_component( 'error_manager' );
		}

		$originals_table = $this->get_table_name_for_original_strings();
		$charset_collate = $this->db->get_charset_collate();
		$charset         = 'utf8mb4';
		if ( strpos( 'latin1', $charset_collate ) === 0 ) {
			$charset = 'latin1';
		}

		$this->db->query( "DELETE t1 FROM `$originals_table` t1 INNER JOIN `$originals_table` t2 WHERE t1.id > t2.id AND t1.original COLLATE " . $charset . '_bin = t2.original' );

		if ( ! empty( $this->db->last_error ) ) {
			$this->error_manager->record_error( array( 'last_error_cleaning_original_strings' => $this->db->last_error ) );
		}

	}

	/**
	 * Function that takes care of synchronizing the dictionaries with the original table by inserting the original ids in the original_id column
	 */
	public function original_ids_reindex( $language_code, $inferior_limit, $batch_size ) {

		// don't do anything for default language
		if ( $this->settings['default-language'] === $language_code ) {
			return 0;
		}

		if ( ! $this->error_manager ) {
			$etm                 = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->error_manager = $etm->get_component( 'error_manager' );
		}

		$originals_table = $this->get_table_name_for_original_strings();
		$table_name      = sanitize_text_field( $this->get_table_name( $language_code, $this->settings['default-language'] ) );
		$charset_collate = $this->db->get_charset_collate();
		$charset         = 'utf8mb4';
		if ( strpos( 'latin1', $charset_collate ) === 0 ) {
			$charset = 'latin1';
		}

		/*
		*  perform a UPDATE JOIN with the original table https://www.mysqltutorial.org/mysql-update-join/
		*/
		$update_records = $this->db->query( $this->db->prepare( "UPDATE $table_name, $originals_table SET $table_name.original_id = $originals_table.id WHERE $table_name.original COLLATE " . $charset . "_bin = $originals_table.original AND $table_name.id > %d AND $table_name.id <= %d", $inferior_limit, ( $inferior_limit + $batch_size ) ) );

		if ( ! empty( $this->db->last_error ) ) {
			$this->error_manager->record_error( array( 'last_error_reindex_original_ids' => $this->db->last_error ) );
		}

		return $update_records;
	}

	/**
	 * Function that makes sure that when new strings are inserted in dictionaries they are also inserted in original_strings table if they don't exist
	 *
	 * @param $language_code
	 * @param $new_strings
	 * @return array|object|null
	 */
	public function original_strings_sync( $language_code, $new_strings ) {
		if ( count( $new_strings ) === 0 ) {
			return array();
		}
		if ( $this->settings['default-language'] != $language_code ) {

			$originals_table = $this->get_table_name_for_original_strings();

			$possible_new_strings = array();
			foreach ( $new_strings as $string ) {
				$possible_new_strings[] = $this->db->prepare( '%s', $string );
			}

			$existing_strings = $this->db->get_results( "SELECT original FROM `$originals_table` WHERE BINARY $originals_table.original IN (" . implode( ',', $possible_new_strings ) . ')', OBJECT_K );

			if ( ! empty( $existing_strings ) ) {
				$existing_strings = array_keys( $existing_strings );
				$insert_strings   = array_diff( $new_strings, $existing_strings );
			} else {
				$insert_strings = $new_strings;
			}

			foreach ( $insert_strings as $k => $string ) {
				$insert_strings[ $k ] = $this->db->prepare( '(%s)', $string );
			}

			if ( ! empty( $insert_strings ) ) {
				// insert the strings that are missing
				$this->db->query( "INSERT INTO `$originals_table` (original) VALUES " . implode( ',', $insert_strings ) );
			}

			// get the ids for all the new strings (new in dictionary)
			$new_strings_in_dictionary_with_original_id = $this->db->get_results( "SELECT original,id FROM `$originals_table` WHERE BINARY $originals_table.original IN (" . implode( ',', $possible_new_strings ) . ')', OBJECT_K );

			if ( count( $new_strings_in_dictionary_with_original_id ) === count( $new_strings ) ) {
				return $new_strings_in_dictionary_with_original_id;
			}
		}

		return array();

	}

	/**
	 * Function that adds post_parent_id meta to  original_meta table
	 *
	 * @param $original_string_ids
	 * @param $post_ids
	 */
	public function set_original_string_meta_post_id( $original_string_ids, $post_ids ) {

		// group the strings in a new array by post_id
		$strings_grouped = array();
		if ( ! empty( $post_ids ) ) {
			foreach ( $post_ids as $i => $post_id ) {
				$strings_grouped[ $post_id ][] = $original_string_ids[ $i ];
			}
		}

		if ( ! empty( $strings_grouped ) ) {
			foreach ( $strings_grouped as $post_id => $original_ids ) {

				// remove all empty values from original_ids just in case
				$original_ids = array_filter( $original_ids );
				if ( ! empty( $original_ids ) ) {

					/*
					 * - select all id's that are in the meta already
					 * - in php compare our $original_ids with the result and leave just the ones that are not in the db
					 * - insert all the remaining ones
					 */

					$existing_entries = $this->db->get_results(
						$this->db->prepare(
							'SELECT original_id FROM ' . $this->get_table_name_for_original_meta() . " WHERE meta_key = '" . $this->get_meta_key_for_post_parent_id() . "' AND meta_value = '%1d' AND original_id IN ( %2s )",
							$post_id,
							implode( ', ', $original_ids )
						),
						OBJECT_K
					);

					$existing_entries = array_keys( $existing_entries );
					$insert_this      = array_unique( array_diff( $original_ids, $existing_entries ) );

					if ( ! empty( $insert_this ) ) {
						$insert_values = array();
						foreach ( $insert_this as $missing_entry ) {
							$insert_values[] = $this->db->prepare( '( %d, %s, %d )', $missing_entry, $this->get_meta_key_for_post_parent_id(), $post_id );
						}

						$this->db->query( 'INSERT INTO ' . $this->get_table_name_for_original_meta() . ' ( original_id, meta_key, meta_value ) VALUES ' . implode( ', ', $insert_values ) );
					}
				}
			}
		}

	}

	/**
	 * Check if the original meta table exists
	 *
	 * If the table does not exists it is created.
	 */
	public function check_original_meta_table() {

		$table_name = $this->get_table_name_for_original_meta();
		if ( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) != $table_name ) {
			// table not in database. Create new table
			$charset_collate = $this->db->get_charset_collate();

			$sql = 'CREATE TABLE `' . $table_name . "`(
                                    meta_id bigint(20) AUTO_INCREMENT NOT NULL PRIMARY KEY,
                                    original_id bigint(20) NOT NULL,
                                    meta_key varchar(255),
                                    meta_value longtext,
                                    UNIQUE KEY meta_id (meta_id) )
                                     $charset_collate;";
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			dbDelta( $sql );

			// create indexes
			$sql_index = 'CREATE INDEX index_original_id ON `' . $table_name . '` (original_id);';
			$this->db->query( $sql_index );
			$sql_index = 'CREATE INDEX meta_key ON `' . $table_name . '`(meta_key);';
			$this->db->query( $sql_index );
		}
	}


	/**
	 * Add block_type column to dictionary tables, if it doesn't exist.
	 *
	 * Affects all existing tables, including deactivated languages
	 *
	 * @param null $language_code
	 * @param null $default_language
	 */
	public function check_for_block_type_column( $language_code = null, $default_language = null ) {
		if ( $default_language == null ) {
			$default_language = $this->settings['default-language'];
		}

		if ( $language_code ) {
			// check only this language
			$array_of_table_names = array( $this->get_table_name( $language_code, $default_language ) );
		} else {
			// check all languages, including deactivated ones
			$array_of_table_names = $this->get_all_table_names( $default_language, array() );
		}

		foreach ( $array_of_table_names as $table_name ) {
			if ( ! $this->table_column_exists( $table_name, 'block_type' ) ) {
				$this->db->query( 'ALTER TABLE ' . $table_name . ' ADD block_type INT(20) DEFAULT ' . $this::BLOCK_TYPE_REGULAR_STRING );
			}
		}
	}


	/**
	 * Add original_id column to dictionary tables, if it doesn't exist.
	 *
	 * Affects all existing tables, including deactivated languages
	 *
	 * @param null $language_code
	 * @param null $default_language
	 */
	public function check_for_original_id_column( $language_code = null, $default_language = null ) {
		if ( $default_language == null ) {
			$default_language = $this->settings['default-language'];
		}

		if ( $language_code ) {
			// check only this language
			$array_of_table_names = array( $this->get_table_name( $language_code, $default_language ) );
		} else {
			// check all languages, including deactivated ones
			$array_of_table_names = $this->get_all_table_names( $default_language, array() );
		}

		foreach ( $array_of_table_names as $table_name ) {
			if ( ! $this->table_column_exists( $table_name, 'original_id' ) ) {
				$this->db->query( 'ALTER TABLE ' . $table_name . ' ADD original_id BIGINT(20) DEFAULT NULL' );
			}
		}
	}

	/**
	 * Returns true if a database table column exists. Otherwise returns false.
	 *
	 * @link http://stackoverflow.com/a/5943905/2489248
	 * @global wpdb $wpdb
	 *
	 * @param string $table_name Name of table we will check for column existence.
	 * @param string $column_name Name of column we are checking for.
	 *
	 * @return boolean True if column exists. Else returns false.
	 */
	public function table_column_exists( $table_name, $column_name ) {
		$column = $this->db->get_results(
			$this->db->prepare(
				'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s ',
				DB_NAME,
				$table_name,
				$column_name
			)
		);
		if ( ! empty( $column ) ) {
			return true;
		}
		return false;
	}

	/**
	 * Update regular (non-gettext) strings in DB
	 *
	 * @param array  $update_strings                 Array of strings to update
	 * @param string $language_code                 Language code
	 * @param array  $columns_to_update              Array with the name of columns to update id, original, translated, status, block_type, original_id
	 */
	public function update_strings( $update_strings, $language_code, $columns_to_update = array( 'id', 'original', 'translated', 'status', 'block_type', 'original_id' ) ) {
		if ( count( $update_strings ) == 0 ) {
			return;
		}

		$placeholder_array_mapping = array(
			'id'          => '%d',
			'original'    => '%s',
			'translated'  => '%s',
			'status'      => '%d',
			'block_type'  => '%d',
			'original_id' => '%d',
		);
		$columns_query_part        = '';
		foreach ( $columns_to_update as $column ) {
			$columns_query_part .= $column . ',';
			$placeholders[]      = $placeholder_array_mapping[ $column ];
		}
		$columns_query_part = rtrim( $columns_query_part, ',' );

		$query = 'INSERT INTO `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` ( ' . $columns_query_part . ' ) VALUES ';

		$values        = array();
		$place_holders = array();

		$placeholders_query_part = '(';
		foreach ( $placeholders as $placeholder ) {
			$placeholders_query_part .= "'" . $placeholder . "',";
		}
		$placeholders_query_part  = rtrim( $placeholders_query_part, ',' );
		$placeholders_query_part .= ')';

		foreach ( $update_strings as $string ) {
			foreach ( $columns_to_update as $column ) {
				array_push( $values, $string[ $column ] );
			}
			$place_holders[] = $placeholders_query_part;
		}

		$on_duplicate    = ' ON DUPLICATE KEY UPDATE ';
		$key_term_values = $this->is_values_accepted() ? 'VALUES' : 'VALUE';
		foreach ( $columns_to_update as $column ) {
			if ( $column == 'id' ) {
				continue;
			}
			$on_duplicate .= $column . '=' . $key_term_values . '(' . $column . '),';
		}
		$query .= implode( ', ', $place_holders );

		$on_duplicate = rtrim( $on_duplicate, ',' );
		$query       .= $on_duplicate;

		// you cannot insert multiple rows at once using insert() method.
		// but by using prepare you cannot insert NULL values.

		$prepared_query = $this->db->prepare( $query . ' ', $values );
		$updated_rows   = $this->db->query( $prepared_query );
		if ( ! $this->check_invalid_text ) {
			$etm                      = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->check_invalid_text = $etm->get_component( 'check_invalid_text' );
		}
		$this->check_invalid_text->update_translations_without_invalid_text( $update_strings, $language_code, $columns_to_update );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running update_strings()' ) );

		return $updated_rows;
	}

	/**
	 * Insert new regular strings in DB.
	 *
	 * @param array  $new_strings Array of strings for which we do not have a translation. Only inserts original.
	 * @param string $language_code Language code of table where it should be inserted.
	 * @param int    $block_type
	 */
	public function insert_strings( $new_strings, $language_code, $block_type = self::BLOCK_TYPE_REGULAR_STRING ) {

		if ( $block_type == null ) {
			$block_type = self::BLOCK_TYPE_REGULAR_STRING;
		}
		if ( count( $new_strings ) == 0 ) {
			return;
		}
		$query = 'INSERT INTO `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` ( original, translated, status, block_type, original_id ) VALUES ';

		$values        = array();
		$place_holders = array();
		$new_strings   = array_unique( $new_strings );

		// make sure we have the same strings in the original table as well
		$original_inserts = $this->original_strings_sync( $language_code, $new_strings );

		foreach ( $new_strings as $string ) {
			array_push( $values, $string, null, self::NOT_TRANSLATED, $block_type, $original_inserts[ $string ]->id );
			$place_holders[] = "('%s','%s','%d','%d', %d)";
		}
		$query .= implode( ', ', $place_holders );

		// you cannot insert multiple rows at once using insert() method.
		// but by using prepare you cannot insert NULL values.
		$this->db->query( $this->db->prepare( $query . ' ', $values ) );
		if ( ! $this->check_invalid_text ) {
			$etm                      = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->check_invalid_text = $etm->get_component( 'check_invalid_text' );
		}
		$this->check_invalid_text->insert_translations_without_invalid_text( $new_strings, $language_code, $block_type );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running insert_strings()' ) );
	}

	/**
	 * Returns the DB ids of the provided original strings
	 *
	 * @param array  $original_strings       Array of original strings to search for.
	 * @param string $language_code         Language code to query for.
	 * @return object                       Associative Array of objects with translations where key is original string.
	 */
	public function get_string_ids( $original_strings, $language_code, $output = OBJECT_K ) {
		if ( ! is_array( $original_strings ) || count( $original_strings ) == 0 ) {
			return array();
		}
		$query = 'SELECT original,id FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE original IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $original_strings as $string ) {
			$placeholders[] = '%s';
			$values[]       = $string;
		}

		$query     .= '( ' . implode( ', ', $placeholders ) . ' )';
		$dictionary = $this->db->get_results( $this->db->prepare( $query, $values ), $output );

		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_string_ids()' ) );
		return $dictionary;
	}

	/**
	 * Returns the DB ids of the provided original strings
	 *
	 * @param array $original_strings       Array of original strings to search for.
	 * @return array                       Associative Array of objects with translations where key is original string.
	 */
	public function get_original_string_ids( $original_strings ) {
		if ( ! is_array( $original_strings ) || count( $original_strings ) == 0 ) {
			return array();
		}
		$query = 'SELECT original,id FROM `' . $this->get_table_name_for_original_strings() . '` WHERE BINARY original IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $original_strings as $string ) {
			$placeholders[] = '%s';
			$values[]       = $string;
		}

		$query  .= '( ' . implode( ', ', $placeholders ) . ' )';
		$results = $this->db->get_results( $this->db->prepare( $query, $values ), OBJECT_K );

		$results_ids = array();
		if ( ! empty( $results ) && ! empty( $original_strings ) ) {
			foreach ( $original_strings as $string ) {
				if ( ! empty( $results[ $string ] ) && ! empty( $results[ $string ]->id ) ) {
					$results_ids[] = $results[ $string ]->id;
				} else {
					$results_ids[] = null; // this should not happen but if it does we need to keep the same number of result ids as original_strings to have a correlation
				}
			}
		}

		return $results_ids;
	}

	/**
	 * Returns the entries for the provided strings.
	 *
	 * Only returns results where there is no translation ( == NOT_TRANSLATED )
	 *
	 * @param array  $strings_array      Array of original strings to search for.
	 * @param string $language_code     Language code to query for.
	 * @return object                   Associative Array of objects with translations where key is original string.
	 */
	public function get_untranslated_strings( $strings_array, $language_code ) {
		if ( ! is_array( $strings_array ) || count( $strings_array ) == 0 ) {
			return array();
		}
		$query = 'SELECT original,id FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE status = ' . self::NOT_TRANSLATED . ' AND original IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $strings_array as $string ) {
			$placeholders[] = '%s';
			$values[]       = $string;
		}

		$query     .= '( ' . implode( ', ', $placeholders ) . ' )';
		$dictionary = $this->db->get_results( $this->db->prepare( $query, $values ), OBJECT_K );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_untranslated_strings()' ) );
		return $dictionary;
	}

	/**
	 * Return custom table name for given language code.
	 *
	 * @param string $language_code         Language code.
	 * @param string $default_language      Default language. Defaults to the one from settings.
	 * @return string                       Table name.
	 */
	public function get_table_name( $language_code, $default_language = null, $only_prefix = false ) {
		if ( $default_language == null ) {
			$default_language = $this->settings['default-language'];
		}
		if ( ( ! etm_is_valid_language_code( $language_code ) && $only_prefix === false ) || ! etm_is_valid_language_code( $default_language ) ) {
			/* there's are other checks that display an admin notice for this kind of errors */
			return 'etm_language_code_is_invalid_error';
		}

		return apply_filters( 'etm_table_name_dictionary', $this->db->prefix . 'etm_dictionary_' . strtolower( $default_language ) . '_' . strtolower( $language_code ), $this->db->prefix, $language_code, $default_language );
	}

	public function get_language_code_from_table_name( $table_name, $default_language = null ) {
		if ( $default_language == null ) {
			$default_language = $this->settings['default-language'];
		}
		$language_code = str_replace( $this->db->prefix . 'etm_dictionary_' . strtolower( $default_language ) . '_', '', $table_name );
		return $language_code;
	}

	/**
	 * Return table name for original strings table
	 *
	 * @return string                       Table name.
	 */
	public function get_table_name_for_original_strings() {
		return apply_filters( 'etm_table_name_original_strings', sanitize_text_field( $this->db->prefix . 'etm_original_strings' ), $this->db->prefix );
	}

	/**
	 * Return table name for original meta table
	 *
	 * @return string                       Table name.
	 */
	public function get_table_name_for_original_meta() {
		return apply_filters( 'etm_table_name_original_meta', sanitize_text_field( $this->db->prefix . 'etm_original_meta' ), $this->db->prefix );
	}

	/**
	 * Return table name for gettext original strings table
	 *
	 * @return string                       Table name.
	 */
	public function get_table_name_for_gettext_original_strings() {
		return sanitize_text_field( $this->db->prefix . 'etm_gettext_original_strings' );
	}

	/**
	 * Return table name for gettext original meta table
	 *
	 * @return string                       Table name.
	 */
	public function get_table_name_for_gettext_original_meta() {
		return sanitize_text_field( $this->db->prefix . 'etm_gettext_original_meta' );
	}

	/**
	 * Return meta_key for post parent id from meta table
	 *
	 * @return string                       key name.
	 */
	public function get_meta_key_for_post_parent_id() {
		return 'post_parent_id';
	}

	public function get_all_gettext_strings( $language_code ) {
		$dictionary = $this->db->get_results( 'SELECT tt.id, CASE WHEN ot.original is NULL THEN tt.original ELSE NULL END as tt_original, tt.translated, tt.domain AS tt_domain, tt.plural_form, ot.original, ot.domain, ot.context FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . '` AS tt LEFT JOIN `' . sanitize_text_field( $this->get_table_name_for_gettext_original_strings() ) . '` AS ot ON tt.original_id = ot.id', ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_all_gettext_strings()' ) );
		if ( is_array( $dictionary ) && count( $dictionary ) === 0 && ! $this->table_exists( $this->get_gettext_table_name( $language_code ) ) ) {
			// if table is missing then last_error is empty
			$this->maybe_record_automatic_translation_error( array( 'details' => 'Missing table ' . $this->get_gettext_table_name( $language_code ) . ' . To regenerate tables, try going to Settings->WEB-T – eTranslation Multilingual->General tab and Save Settings.' ), true );
		}
		return $dictionary;
	}

	public function get_all_gettext_translated_strings( $language_code ) {
		$dictionary = $this->db->get_results( 'SELECT id, original, translated, domain FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . "` WHERE translated <>'' AND status != " . self::NOT_TRANSLATED, ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_all_gettext_translated_strings()' ) );
		return $dictionary;
	}

	public function get_all_gettext_untranslated_strings( $language_code ) {
		$dictionary = $this->db->get_results( 'SELECT id, original, translated, domain, original_id, plural_form FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . "` WHERE translated = ''", ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_all_gettext_untranslated_strings()' ) );
		return $dictionary;
	}

	public function get_gettext_table_name( $language_code ) {
		if ( ! etm_is_valid_language_code( $language_code ) ) {
			/* there's are other checks that display an admin notice for this kind of errors */
			return 'etm_language_code_is_invalid_error';
		}
		return apply_filters( 'etm_table_name_gettext', $this->db->prefix . 'etm_gettext_' . strtolower( $language_code ), $this->db->prefix, $language_code );
	}

	/**
	 * Return entire rows for given ids or original strings.
	 *
	 * @param array  $id_array Int array of db ids.
	 * @param array  $original_array String array of originals.
	 * @param string $language_code Language code of table.
	 * @param string $output Return format
	 * @param bool   $is_original_id_array Whether the id array refers to the id or original_id column
	 * @return object                   Associative Array of objects with translations where key is id.
	 */
	public function get_string_rows( $id_array, $original_array, $language_code, $output = OBJECT_K, $is_original_id_array = false ) {
		$original_id  = ( $is_original_id_array ) ? ' original_id, ' : '';
		$select_query = 'SELECT ' . $original_id . 'id, original, translated, status, block_type FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE ';

		$prepared_query1 = '';
		if ( is_array( $original_array ) && count( $original_array ) > 0 ) {
			$placeholders = array();
			$values       = array();
			foreach ( $original_array as $string ) {
				$placeholders[] = '%s';
				$values[]       = $string;
			}

			$query1          = 'original IN ( ' . implode( ', ', $placeholders ) . ' )';
			$prepared_query1 = $this->db->prepare( $query1, $values );
		}

		$prepared_query2 = '';
		if ( is_array( $id_array ) && count( $id_array ) > 0 ) {
			$placeholders = array();
			$values       = array();
			foreach ( $id_array as $id ) {
				$placeholders[] = '%d';
				$values[]       = intval( $id );
			}
			$original_or_not = ( $is_original_id_array ) ? 'original_' : '';
			$query2          = $original_or_not . 'id IN ( ' . implode( ', ', $placeholders ) . ' )';
			$prepared_query2 = $this->db->prepare( $query2, $values );
		}

		$query = '';
		if ( empty( $prepared_query1 ) && empty( $prepared_query2 ) ) {
			return array();
		}
		if ( empty( $prepared_query1 ) ) {
			$query = $select_query . $prepared_query2;
		}
		if ( empty( $prepared_query2 ) ) {
			$query = $select_query . $prepared_query1;
		}
		if ( ! empty( $prepared_query1 ) && ! empty( $prepared_query2 ) ) {
			$query = $select_query . $prepared_query1 . ' OR ' . $prepared_query2;
		}

		$dictionary = $this->db->get_results( $query, $output );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_string_rows()' ) );
		return $dictionary;
	}

	/**
	 * Retrieve gettext string rows by their IDs for a specific language.
	 *
	 * @param array  $id_array An array containing the IDs of gettext strings.
	 * @param string $language_code The language code for the translation.
	 * @return array An array of gettext string rows.
	 */
	public function get_gettext_string_rows_by_ids( $id_array, $language_code ) {
		if ( ! is_array( $id_array ) || count( $id_array ) == 0 ) {
			return array();
		}

		$query = 'SELECT ot.id as ot_id, tt.id, ot.original, tt.original as tt_original, tt.translated, tt.domain AS tt_domain, tt.plural_form, ot.original, ot.domain, ot.context, ot.original_plural FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . '` AS tt LEFT JOIN `' . sanitize_text_field( $this->get_table_name_for_gettext_original_strings() ) . '` AS ot ON tt.original_id = ot.id WHERE tt.id IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $id_array as $id ) {
			$placeholders[] = '%d';
			$values[]       = intval( $id );
		}

		$query     .= '( ' . implode( ', ', $placeholders ) . ' )';
		$dictionary = $this->db->get_results( $this->db->prepare( $query, $values ), ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_gettext_string_rows_by_ids()' ) );
		return $dictionary;
	}

	/**
	 * Retrieve gettext string rows by their original IDs for a specific language.
	 *
	 * @param array  $original_id_array An array containing the original IDs of gettext strings.
	 * @param string $language_code The language code for the translation.
	 * @return array An array of gettext string rows.
	 */
	public function get_gettext_string_rows_by_original_id( $original_id_array, $language_code ) {
		if ( ! is_array( $original_id_array ) || count( $original_id_array ) == 0 ) {
			return array();
		}
		$query = 'SELECT tt.id, tt.original AS tt_original, tt.translated, tt.status, tt.domain AS tt_domain, tt.plural_form, ot.id AS ot_id, ot.original, ot.domain, ot.context, ot.original_plural FROM `' . sanitize_text_field( $this->get_table_name_for_gettext_original_strings() ) . '` AS ot LEFT JOIN `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . '` AS tt ON tt.original_id = ot.id WHERE ot.id IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $original_id_array as $id ) {
			$placeholders[] = '%d';
			$values[]       = intval( $id );
		}

		$query     .= '( ' . implode( ', ', $placeholders ) . ' )';
		$dictionary = $this->db->get_results( $this->db->prepare( $query, $values ), ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_gettext_string_rows_by_original_id()' ) );
		return $dictionary;
	}

	/**
	 * Retrieve gettext string rows by their original strings for a specific language.
	 *
	 * @param array  $original_array An array containing the original strings of gettext strings.
	 * @param string $language_code The language code for the translation.
	 * @return array An array of gettext string rows.
	 */
	public function get_gettext_string_rows_by_original( $original_array, $language_code ) {
		if ( ! is_array( $original_array ) || count( $original_array ) == 0 ) {
			return array();
		}
		$query = 'SELECT tt.id, tt.original as tt_original, tt.translated, tt.domain AS tt_domain, tt.plural_form, ot.original, ot.domain, ot.context FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . '` AS tt LEFT JOIN `' . sanitize_text_field( $this->get_table_name_for_gettext_original_strings() ) . '` AS ot ON tt.original_id = ot.id WHERE CASE WHEN ot.original is NULL THEN tt.original ELSE NULL END IN ';

		$placeholders = array();
		$values       = array();
		foreach ( $original_array as $string ) {
			$placeholders[] = '%s';
			$values[]       = $string;
		}

		$query     .= '( ' . implode( ', ', $placeholders ) . ' )';
		$dictionary = $this->db->get_results( $this->db->prepare( $query, $values ), ARRAY_A );
		$this->maybe_record_automatic_translation_error( array( 'details' => 'Error running get_gettext_string_rows_by_original()' ) );
		return $dictionary;
	}
	/**
	 * Get all table names for translation languages except for exceptions.
	 *
	 * @param string $original_language The original language.
	 * @param array  $exception_translation_languages An array containing exceptions for translation languages.
	 * @return array An array of table names.
	 */
	public function get_all_table_names( $original_language, $exception_translation_languages = array() ) {
		foreach ( $exception_translation_languages as $key => $language ) {
			$exception_translation_languages[ $key ] = $this->get_table_name( $language, $original_language );
		}
		$return_tables = array();
		$table_name    = $this->get_table_name( '', null, true );
		$table_names   = $this->db->get_results( "SHOW TABLES LIKE '$table_name%'", ARRAY_N );
		foreach ( $table_names as $table_name ) {
			if ( isset( $table_name[0] ) && ! in_array( $table_name[0], $exception_translation_languages ) ) {
				$return_tables[] = $table_name[0];
			}
		}
		return $return_tables;
	}

	/**
	 * Get all gettext table names for translation languages.
	 *
	 * @return array An array of gettext table names.
	 */
	public function get_all_gettext_table_names() {
		global $wpdb;
		$table_name    = $wpdb->get_blog_prefix() . 'etm_gettext_';
		$return_tables = array();

		$table_names = $this->db->get_results( "SHOW TABLES LIKE '$table_name%'", ARRAY_N );
		foreach ( $table_names as $table_name ) {
			if ( isset( $table_name[0] ) &&
				strpos( $table_name[0], 'etm_gettext_original_meta' ) === false &&
				strpos( $table_name[0], 'etm_gettext_original_strings' ) === false ) {
				$return_tables[] = $table_name[0];
			}
		}
		return $return_tables;
	}

	/**
	 * Update translation blocks for a list of table names and original strings.
	 *
	 * @param array  $table_names An array containing the table names for translation.
	 * @param array  $original_array An array containing the original strings of gettext strings.
	 * @param string $block_type The type of block to update.
	 * @return bool|int The number of rows affected on success, or false on failure.
	 */
	public function update_translation_blocks_by_original( $table_names, $original_array, $block_type ) {

		$values = array();
		foreach ( $table_names as $table_name ) {
			$placeholders = array();
			foreach ( $original_array as $string ) {
				$placeholders[] = '%s';
				$values[]       = etm_full_trim( $string );
			}
		}

		$placeholders = '( ' . implode( ', ', $placeholders ) . ' )';
		$query        = 'UPDATE `' . implode( '`, `', $table_names ) . '` SET `' . implode( '`.block_type=' . $block_type . ', `', $table_names ) . '`.block_type=' . $block_type . ' WHERE `' . implode( '`.original IN ' . $placeholders . ' AND `', $table_names ) . '`.original IN ' . $placeholders;

		return $this->db->query( $this->db->prepare( $query, $values ) );
	}

	/**
	 * Removes duplicate rows of regular strings table
	 *
	 * (original, translated, status, block_type) have to be identical.
	 * Only the row with the lowest ID remains
	 *
	 * https://stackoverflow.com/a/25206828
	 *
	 * @param $table
	 */
	public function remove_duplicate_rows_in_dictionary_table( $language_code, $inferior_limit, $batch_size ) {
		$table_name = $this->get_table_name( $language_code );
		if ( $this->table_exists( $table_name ) ) {
			$last_id = $this->get_last_id( $table_name );
			$query   = $this->get_remove_identical_duplicates_query( $table_name, $inferior_limit, 'regular' );
			$this->db->query( $query );
			if ( $inferior_limit > $last_id ) {
				return true;
			}
			return false;
		} else {
			return true;
		}
	}

	/**
	 * Removes duplicate rows of gettext strings table
	 *
	 * (original, translated, domain, status) have to be identical.
	 * Only the row with the lowest ID remains
	 *
	 * @param $table
	 */
	/**
	 * @param $language_code
	 * @param $inferior_limit 1000, 2000
	 * @param $batch_size
	 * @return bool|int
	 */
	public function remove_duplicate_rows_in_gettext_table( $language_code, $inferior_limit, $batch_size ) {
		$table_name = $this->get_gettext_table_name( $language_code );
		if ( $this->table_exists( $table_name ) ) {
			$last_id = $this->get_last_id( $table_name );
			$query   = $this->get_remove_identical_duplicates_query( $table_name, $inferior_limit, 'gettext' );
			$this->db->query( $query );
			if ( $inferior_limit > $last_id ) {
				return true;
			}
			return false;
		} else {
			return true;
		}
	}

	/**
	 * Function that builds the query string for removing identical entries
	 *
	 * @param $table_name
	 * @param $batch
	 * @param $type string possible values are 'regular' or 'gettext'
	 * @return string
	 */
	private function get_remove_identical_duplicates_query( $table_name, $inferior_limit, $type ) {
		$charset_collate = $this->db->get_charset_collate();
		$charset         = 'utf8mb4';
		if ( strpos( 'latin1', $charset_collate ) === 0 ) {
			$charset = 'latin1';
		}

		$query = '	DELETE `b`
					FROM
					    ' . $table_name . ' AS `a`,
					    ' . $table_name . ' AS `b`
					WHERE
					    -- IMPORTANT: Ensures one version remains
					    `a`.ID < ' . $inferior_limit . '
					    AND `b`.ID < ' . $inferior_limit . '
					    AND `a`.`ID` < `b`.`ID`

					    -- Check for all duplicates. Binary ensure case sensitive comparison
					    AND (`a`.`original` COLLATE ' . $charset . '_bin = `b`.`original` OR `a`.`original` IS NULL AND `b`.`original` IS NULL)
					    AND (`a`.`translated` COLLATE ' . $charset . '_bin = `b`.`translated` OR `a`.`translated` IS NULL AND `b`.`translated` IS NULL)
					    AND (`a`.`status` = `b`.`status` OR `a`.`status` IS NULL AND `b`.`status` IS NULL)';
		if ( $type === 'gettext' ) {
			$query .= 'AND (`a`.`domain` = `b`.`domain` OR `a`.`domain` IS NULL AND `b`.`domain` IS NULL)';
		} elseif ( $type === 'regular' ) {
			$query .= 'AND (`a`.`block_type` = `b`.`block_type` OR `a`.`block_type` IS NULL AND `b`.`block_type` IS NULL)';
		}
		$query .= ';';
		return $query;
	}

	/**
	 * Removes a row if translation status 0, if the original exists translated
	 *
	 * Only the original with translation remains
	 */
	public function remove_untranslated_strings_if_translation_available( $language_code, $inferior_limit, $batch_size ) {
		$table_name = $this->get_table_name( $language_code );

		if ( $this->table_exists( $table_name ) ) {
			$query = $this->get_remove_untranslated_duplicates_query( $table_name, 'regular' );
			$this->db->query( $query );
		}
		return true;
	}

	/**
	 * Removes a row if translation status 0, if the original exists translated in gettext tables
	 *
	 * Only the original with translation remains
	 */
	public function remove_untranslated_strings_if_gettext_translation_available( $language_code, $inferior_limit, $batch_size ) {
		$table_name = $this->get_gettext_table_name( $language_code );

		if ( $this->table_exists( $table_name ) ) {
			$query = $this->get_remove_untranslated_duplicates_query( $table_name, 'gettext' );
			$this->db->query( $query );
		}
		return true;
	}

	/**
	 * Function that builds the query string for removing identical entries
	 *
	 * @param $table_name
	 * @param $batch
	 * @param $type string possible values are 'regular' or 'gettext'
	 * @return string
	 */
	private function get_remove_untranslated_duplicates_query( $table_name, $type ) {
		$charset_collate = $this->db->get_charset_collate();
		$charset         = 'utf8mb4';
		if ( strpos( 'latin1', $charset_collate ) === 0 ) {
			$charset = 'latin1';
		}

		$query = '	DELETE `a`
						FROM
						    ' . $table_name . ' AS `a`,
						    ' . $table_name . ' AS `b`
						WHERE
						    (`a`.`original` COLLATE ' . $charset . '_bin = `b`.`original` OR `a`.`original` IS NULL AND `b`.`original` IS NULL)
						    AND (`a`.`status` = 0 )
						    AND (`b`.`status` != 0 )';
		if ( $type === 'gettext' ) {
			$query .= 'AND (`a`.`domain` = `b`.`domain` OR `a`.`domain` IS NULL AND `b`.`domain` IS NULL)';
		} elseif ( $type === 'regular' ) {
			$query .= 'AND (`a`.`block_type` = `b`.`block_type` OR `a`.`block_type` IS NULL AND `b`.`block_type` IS NULL)';
		}
		$query .= ';';
		return $query;
	}


	/**
	 * Removes CDATA from original and dictionary tables.
	 *
	 * @param $language_code
	 * @param $inferior_limit
	 * @param $batch_size
	 * @return bool
	 */
	public function remove_cdata_in_original_and_dictionary_tables( $language_code, $inferior_limit, $batch_size ) {

		if ( $language_code == $this->settings['default-language'] ) {
			$table_name = $this->get_table_name_for_original_strings();
			$query      = $this->get_remove_cdata_query( $table_name, $batch_size );

			$rows_affected = $this->db->query( $query );
			if ( $rows_affected > 0 ) {
				return false;
			} else {
				return true;
			}
		}
		$table_name = $this->get_table_name( $language_code );
		if ( $this->table_exists( $table_name ) ) {
			$query         = $this->get_remove_cdata_query( $table_name, $batch_size );
			$rows_affected = $this->db->query( $query );
			if ( $rows_affected > 0 ) {
				return false;
			} else {
				return true;
			}
		} else {
			return true;
		}
	}

	/**
	 * @param $table_name
	 * @param $batch_size
	 * @return string
	 */
	private function get_remove_cdata_query( $table_name, $batch_size ) {

		$query = 'DELETE FROM ' . $table_name . " WHERE original LIKE '<![CDATA[%' LIMIT " . $batch_size;

		return $query;
	}

	/**
	 * Removes untranslated links from the dictionary table
	 *
	 * @param $language_code
	 * @param $inferior_limit
	 * @param $batch_size
	 * @return bool
	 */
	public function remove_untranslated_links_in_dictionary_table( $language_code, $inferior_limit, $batch_size ) {
		$table_name = $this->get_table_name( $language_code );
		if ( $this->table_exists( $table_name ) ) {
			$query         = $this->get_remove_untranslated_links_query( $table_name, $batch_size );
			$rows_affected = $this->db->query( $query );
			if ( $rows_affected > 0 ) {
				return false;
			} else {
				return true;
			}
		} else {
			return true;
		}
	}

	/**
	 * @param $table_name
	 * @return $query
	 */
	private function get_remove_untranslated_links_query( $table_name, $batch_size ) {

		$query = 'DELETE FROM ' . $table_name . " WHERE original LIKE 'http%' AND (translated = '' OR translated IS NULL) LIMIT " . $batch_size;

		return $query;
	}

	/*
	 * Get last inserted ID for this table
	 *
	 * Useful for optimizing database by removing duplicate rows
	 */
	public function get_last_id( $table_name ) {
		$last_id = $this->db->get_var( 'SELECT MAX(id) FROM ' . $table_name );
		return $last_id;
	}

	/**
	 * Returns a selection of rows from a specific location.
	 * Only id and original are selected.
	 *
	 * Ex. if $inferior_limit = 400 and $batch_size = 10
	 * You will get rows 401 to 411
	 *
	 * @param $language_code
	 * @param $inferior_limit
	 * @param $batch_size
	 *
	 * @return array|null|object
	 */
	public function get_rows_from_location( $language_code, $inferior_limit, $batch_size, $columns_to_retrieve ) {
		$columns_query_part = '';
		foreach ( $columns_to_retrieve as $column ) {
			$columns_query_part .= $column . ',';
		}
		$columns_query_part = rtrim( $columns_query_part, ',' );
		$query              = 'SELECT ' . $columns_query_part . ' FROM `' . sanitize_text_field( $this->get_table_name( $language_code ) ) . '` WHERE status != ' . self::NOT_TRANSLATED . ' ORDER BY id LIMIT ' . $inferior_limit . ', ' . $batch_size;
		$dictionary         = $this->db->get_results( $query, ARRAY_A );
		return $dictionary;
	}

	/**
	 * Used for updating database
	 *
	 * @param string $language_code     Language code of the table
	 * @param string $limit             How many strings to affect at most
	 *
	 * @return bool|int
	 */
	public function delete_empty_gettext_strings( $language_code, $limit ) {
		$limit = (int) $limit;
		$sql   = 'DELETE FROM `' . sanitize_text_field( $this->get_gettext_table_name( $language_code ) ) . "` WHERE (original IS NULL OR original = '') LIMIT " . $limit;
		return $this->db->query( $sql );
	}

	public function maybe_record_automatic_translation_error( $error_details = array(), $ignore_last_error = false ) {
		if ( ! $this->check_invalid_text ) {
			$etm                      = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->check_invalid_text = $etm->get_component( 'check_invalid_text' );
		}

		if ( ( ! empty( $this->db->last_error ) && ! $this->check_invalid_text->is_invalid_data_error() ) || $ignore_last_error ) {
			$etm = ETM_eTranslation_Multilingual::get_etm_instance();
			if ( ! $this->error_manager ) {
				$this->error_manager = $etm->get_component( 'error_manager' );
			}
			if ( ! $this->url_converter ) {
				$this->url_converter = $etm->get_component( 'url_converter' );
			}

			$default_error_details = array(
				'last_error'                     => $this->db->last_error,
				'disable_automatic_translations' => true,
				'url'                            => $this->url_converter->cur_page_url(),
			);
			$error_details         = array_merge( $default_error_details, $error_details );
			$this->error_manager->record_error( $error_details );
		}
	}

	/**
	 * Return true if table exists in db, return false otherwise
	 *
	 * @param $table_name
	 * @param $ignore_cache
	 * @return bool
	 */
	public function table_exists( $table_name, $ignore_cache = false ) {
		if ( ! $ignore_cache && in_array( $table_name, $this->tables_exist ) ) {
			return true;
		}

		$table_name  = sanitize_text_field( $table_name );
		$table_found = strtolower( $this->db->get_var( "SHOW TABLES LIKE '$table_name'" ) ) == strtolower( $table_name );
		if ( $table_found ) {
			$this->tables_exist[] = $table_name;
		}
		return $table_found;
	}

	/**
	 * Removes any other strings from DB that have the same original with the provided array
	 *
	 * Keeps only the rows with the ids specified. Any other rows with the same original (and domain for gettext) are deleted from DB
	 *
	 * @param $update_string_array
	 * @param $language
	 * @param $string_type
	 * @return bool|int|void
	 */
	public function remove_possible_duplicates( $update_string_array, $language, $string_type ) {
		if ( ! is_array( $update_string_array ) || count( $update_string_array ) < 1 ) {
			return;
		}

		$charset_collate = $this->db->get_charset_collate();
		$charset         = ( strpos( 'latin1', $charset_collate ) === 0 ) ? 'latin1' : 'utf8mb4';

		$table_name = ( $string_type === 'gettext' ) ? $this->get_gettext_table_name( $language ) : $this->get_table_name( $language );

		$values        = array();
		$place_holders = array();
		foreach ( $update_string_array as $string ) {
			if ( $string_type === 'gettext' ) {
				array_push( $values, $string['original'], $string['domain'], (int) $string['plural_form'], $string['id'] );
			} else {
				array_push( $values, $string['original'], $string['id'] );
			}
			$domain          = ( $string_type === 'gettext' ) ? 'AND domain COLLATE ' . $charset . "_bin = '%s' AND plural_form = '%d' " : '';
			$place_holders[] = '(original COLLATE ' . $charset . "_bin = '%s' " . $domain . "AND id != '%d'  )";
		}

		$sql   = 'DELETE FROM `' . sanitize_text_field( $table_name ) . '` WHERE ' . implode( ' OR ', $place_holders );
		$query = $this->db->prepare( $sql, $values );
		return $this->db->query( $query );
	}

	public function rename_originals_table() {
		$new_table_name = sanitize_text_field( $this->get_table_name_for_original_strings() . time() );
		$this->db->query( 'ALTER TABLE ' . $this->get_table_name_for_original_strings() . ' RENAME TO ' . $new_table_name );

		$table_to_use_for_recovery = get_option( 'etm_original_strings_table_for_recovery', '' );
		if ( $table_to_use_for_recovery == '' ) {
			// if a previous run of removing original strings duplicates failed, use the old table, not the one created during that failed time
			update_option( 'etm_original_strings_table_for_recovery', $new_table_name );
		}
	}

	public function regenerate_original_meta_table( $inferior_limit, $batch_size ) {

		if ( ! $this->error_manager ) {
			$etm                 = ETM_eTranslation_Multilingual::get_etm_instance();
			$this->error_manager = $etm->get_component( 'error_manager' );
		}

		$originals_table          = $this->get_table_name_for_original_strings();
		$recovery_originals_table = sanitize_text_field( get_option( 'etm_original_strings_table_for_recovery' ) );
		$originals_meta_table     = $this->get_table_name_for_original_meta();
		if ( empty( $recovery_originals_table ) ) {
			$this->error_manager->record_error( array( 'regenerate_original_meta_table' => 'Empty option etm_original_strings_table_for_recovery' ) );
			return;
		}

		$this->db->query( $this->db->prepare( "UPDATE `$originals_meta_table` etm_meta INNER JOIN `$recovery_originals_table` etm_old ON etm_meta.original_id = etm_old.id LEFT JOIN `$originals_table` etm_new ON etm_new.original = etm_old.original set etm_meta.original_id = IF(etm_new.id IS NULL, 0, etm_new.id) WHERE etm_meta.meta_id > %d AND etm_meta.meta_id <= %d AND etm_new.id != etm_old.id", $inferior_limit, ( $inferior_limit + $batch_size ) ) );

		/*
		 UPDATE `wp_etm_original_meta` etm_meta INNER JOIN `wp_etm_original_strings1608214654` as etm_old ON etm_meta.original_id = etm_old.id
		 * LEFT JOIN `wp_etm_original_strings` as etm_new on etm_new.original = etm_old.original  set etm_meta.original_id = IF(etm_new.id IS NULL, 0, etm_new.id)
		 * WHERE etm_meta.meta_id > 10 AND etm_meta.meta_id <= 33 AND etm_new.id != etm_old.id*/

		if ( ! empty( $this->db->last_error ) ) {
			$this->error_manager->record_error( array( 'last_error_regenerate_original_meta_table' => $this->db->last_error ) );
		}
	}

	public function clean_original_meta( $limit ) {
		$limit = (int) $limit;
		$sql   = 'DELETE FROM `' . sanitize_text_field( $this->get_table_name_for_original_meta() ) . '` WHERE original_id = 0 LIMIT ' . $limit;
		return $this->db->query( $sql );
	}

	public function drop_table( $table_name ) {
		$sql = 'DROP TABLE `' . sanitize_text_field( $table_name ) . '`';
		return $this->db->query( $sql );
	}

	/**
	 * Return db sql version
	 *
	 * Using 'select version()' instead of wpdb->db_server_info because
	 * db_server_info returns format 5.5.5-10.3.3-mariadb instead of 10.3.3-mariadb on some setups
	 * https://www.php.net/manual/en/mysqli.get-server-info.php#118822
	 *
	 * @return string|null
	 */
	public function get_db_sql_version() {
		if ( $this->db_sql_version === null ) {
			$this->db_sql_version = $this->db->get_var( 'select version()' );
			$this->db_sql_version = ( $this->db_sql_version === null ) ? '0' : $this->db_sql_version;
		}
		return $this->db_sql_version;
	}

	/**
	 * Whether it is safe to use VALUES() instead of VALUE()
	 *
	 * Starting with 10.3.3 MariaDB recommends using VALUE() instead of VALUES()
	 * Even though they say they still accept the term values for 'on duplicate key update' syntax,
	 * some users still report syntax error.
	 * https://mariadb.com/kb/en/values-value/
	 *
	 * MySQL servers marked the use of VALUES deprecated starting with 8.0.20 but have not removed support for it.
	 * We can't use the MariaDB approach, their alternative is different and not supported by earlier versions.
	 * For now, there is no need to further complicate this SQL query based on DB version and make.
	 *
	 * @return bool
	 */
	public function is_values_accepted() {
		$return         = true;
		$db_sql_version = strtolower( $this->get_db_sql_version() );
		if ( strpos( $db_sql_version, 'mariadb' ) !== false ) {
			$db_server_array = explode( '-', $db_sql_version );
			if ( isset( $db_server_array[1] ) && $db_server_array[1] == 'mariadb' && version_compare( $db_server_array[0], '10.3.3', '>=' ) ) {
				$return = false;
			}
		}
		return apply_filters( 'etm_is_sql_values_accepted', $return );
	}

	/**
	 * Return true if the dictionary table of $language has at least $minimum_rows with $status
	 *
	 * @param $language
	 * @param $minimum_rows
	 * @param $status
	 *
	 * @return bool
	 */
	public function minimum_rows_with_status( $language, $minimum_rows, $status ) {
		$minimum_rows = (int) $minimum_rows;
		$status       = (int) $status;

		$sql = 'SELECT (COUNT(*) > ' . $minimum_rows . ') FROM `' . sanitize_text_field( $this->get_table_name( $language ) ) . '` WHERE status = ' . $status;
		return $this->db->get_var( $sql );
	}

	/**
	 * Check if gettext strings are normalized.
	 *
	 * @return bool Whether gettext strings are normalized or not.
	 */
	public function is_gettext_normalized() {
		if ( $this->gettext_normalized === null ) {
			$this->gettext_normalized = ! ( get_option( 'etm_gettext_normalized', '' ) == 'no' );
		}
		return $this->gettext_normalized;
	}

	/**
	 * Delete all translations for original and gettext strings.
	 * Truncates the translation tables for all languages.
	 */
	public function delete_all_translations() {
		$default_language     = $this->settings['default-language'];
		$tables_to_truncate   = array();
		$tables_to_truncate[] = $this->get_table_name_for_original_strings();
		$tables_to_truncate[] = $this->get_table_name_for_gettext_original_strings();

		foreach ( $this->settings['translation-languages'] as $language_code ) {
			$tables_to_truncate[] = $this->get_table_name( $language_code, $default_language );
			$tables_to_truncate[] = $this->get_gettext_table_name( $language_code );
		}

		foreach ( $tables_to_truncate as $table ) {
			if ( $this->table_exists( $table ) ) {
				$sql = 'TRUNCATE TABLE `' . sanitize_text_field( $table ) . '`';
				$this->db->query( $sql );
			}
		}
	}
}
