<?php

/**
 * Class eTranslation_Query
 * Handles eTranslation queries and database operations.
 */
class eTranslation_Query {

	/**
	 * The WordPress database object.
	 *
	 * @var wpdb
	 */
	private $db;

	/**
	 * The table name for eTranslation jobs.
	 *
	 * @var string
	 */
	private $jobs_table;

	/**
	 * Constructor.
	 * Initializes the database object and table name.
	 */
	function __construct() {
		global $wpdb;
		$this->db         = $wpdb;
		$this->jobs_table = $wpdb->prefix . 'etm_etranslation_jobs';
		$this->create_etranslation_database_table();
	}

	/**
	 * Handle the translation document destination.
	 *
	 * @param WP_REST_Request $request The REST request object.
	 * @return array The result of the operation.
	 */
	function translation_document_destination( WP_REST_Request $request ): array {
		$id     = $request['id'];
		$db_row = $this->get_incomplete_db_entry( $id );

		if ( $db_row != null ) {
			$translation = base64_decode( $request->get_body() );

			if ( $db_row->status == 'TIMEOUT' ) {
				// timeout has been reached, update translation manually & delete row from job table.
				$this->update_translation_manually( $db_row, $translation );
				$deletion = $this->db->delete(
					$this->jobs_table,
					array(
						'id' => $db_row->id,
					)
				);
				if ( ! $deletion ) {
					$error = $this->db->last_error;
					error_log( "Could not delete $this->jobs_table row after updating translation manually [ID=$db_row->id, error=$error]. Please check if this table exists and contains entry with such ID." );
				}
			} else {
				// translation completed in time.
				$update_result = $this->db->update(
					$this->jobs_table,
					array(
						'status' => 'DONE',
						'body'   => $translation,
					),
					array(
						'id' => $id,
					)
				);

				if ( ! $update_result ) {
					$error = $this->db->last_error;
					error_log( "Error updating eTranslation DB entry [ID=$id, error=$error]. Please check if table '" . $this->jobs_table . "' exists and contains entry with such ID." );
				}
			}
		} else {
			error_log( "Translation received has no entry in database [ID=$id]" );
		}

		return array( $id, $request->get_body() );
	}

	/**
	 * Handle the callback for translation errors.
	 *
	 * @param WP_REST_Request $request The REST request object.
	 * @return array The result of the operation.
	 */
	function translation_error_callback( WP_REST_Request $request ): array {
		$id       = $request['id'];
		$db_entry = $this->get_incomplete_db_entry( $id );

		if ( $db_entry != null ) {
			$error_code = $request->get_param( 'error_code' );

			$update_result = $this->db->update(
				$this->jobs_table,
				array(
					'status' => 'ERROR',
					'body'   => $error_code,
				),
				array(
					'id' => $id,
				)
			);

			if ( ! $update_result ) {
				$error = $this->db->last_error;
				error_log( "Error updating eTranslation DB entry [ID=$id, error=$error]. Please check if the table '" . $this->jobs_table . "' exists and contains an entry with such ID." );
			}
		} else {
			error_log( "Translation error received has no entry in the database [ID=$id]" );
		}

		$error_message = $request->get_param( 'error-message' );
		error_log( "Error translating document with eTranslation: $error_message [code: $error_code] ID=$id" );

		return array( $id, $error_code );
	}

	/**
	 * Search for a saved translation in the database.
	 *
	 * @param string $id The ID of the translation entry.
	 * @return string|null The saved translation or null if not found.
	 */
	function search_saved_translation( $id ) {
		$row = $this->db->get_row( "SELECT * FROM $this->jobs_table WHERE id = '$id' AND status != 'TRANSLATING'" );
		if ( $row != null ) {
			if ( $row->status == 'ERROR' ) {
				error_log( "Translation entry [ID=$id] has status 'ERROR', using original strings" );
				return '';
			}
			$deletion = $this->db->delete(
				$this->jobs_table,
				array(
					'id' => $id,
				)
			);
			if ( ! $deletion ) {
				$error = $this->db->last_error;
				error_log( "Could not delete row from $this->jobs_table [ID=$id, error=$error]. Please check if this table exists and contains an entry with such ID." );
			}
			return $row->body;
		}
		return null;
	}

	/**
	 * Update the translation status in the database.
	 *
	 * @param string $id The ID of the translation entry.
	 * @param string $status The new status to be set.
	 */
	function update_translation_status( $id, $status ) {
		$result = $this->db->update(
			$this->jobs_table,
			array(
				'status' => $status,
			),
			array(
				'id' => $id,
			)
		);

		if ( ! $result ) {
			$error = $this->db->last_error;
			error_log( "Error updating eTranslation DB entry status [ID=$id, error=$error]. Please check if the table '" . $this->jobs_table . "' exists and contains an entry with such ID." );
		}
	}

	/**
	 * Insert a new translation entry into the eTranslation jobs table.
	 *
	 * @param string $id The ID of the translation entry.
	 * @param string $lang_from The source language code.
	 * @param string $lang_to The target language code.
	 * @param string $original The original content to be translated.
	 * @return bool True on success, false on failure.
	 */
	function insert_translation_entry( $id, $lang_from, $lang_to, $original ) {
		$result = $this->db->insert(
			$this->jobs_table,
			array(
				'id'       => $id,
				'status'   => 'TRANSLATING',
				'from'     => $lang_from,
				'to'       => $lang_to,
				'original' => $original,
			)
		);
		if ( ! $result ) {
			$error = $this->db->last_error;
			error_log( "Error inserting translation entry in eTranslation DB [ID=$id, error=$error]. Please check if the table '" . $this->jobs_table . "' exists or reinstall WEB-T – eTranslation Multilingual!" );
		}
		return $result;
	}

	/**
	 * Create the eTranslation response database table if it does not exist.
	 */
	function create_etranslation_database_table() {
		// Check to see if the table exists already, if not, then create it
		if ( $this->db->get_var( "show tables like '$this->jobs_table'" ) != $this->jobs_table ) {
			$sql  = 'CREATE TABLE `' . $this->jobs_table . '` ( ';
			$sql .= '  `id`  VARCHAR(15) NOT NULL, ';
			$sql .= "  `status`  ENUM('TRANSLATING','DONE','ERROR','TIMEOUT') NOT NULL DEFAULT 'TRANSLATING', ";
			$sql .= '  `body`  LONGTEXT NULL DEFAULT NULL, ';
			$sql .= ' `from` VARCHAR(5) NULL, ';
			$sql .= ' `to` VARCHAR(200) NULL, ';
			$sql .= ' `original` LONGTEXT NULL, ';
			$sql .= ' `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, ';
			$sql .= '  PRIMARY KEY (`id`) ';
			$sql .= ") COLLATE='utf8mb4_unicode_520_ci'; ";
			require_once ABSPATH . '/wp-admin/includes/upgrade.php';
			$result = dbDelta( $sql );

			if ( ! $result ) {
				$error = $this->db->last_error;
				error_log( 'Error creating eTranslation jobs DB table. Error: ' . $error );
			}
		}
	}

	/**
	 * Update the translation manually in the database.
	 *
	 * @param object $details_row The details of the translation entry.
	 * @param string $response_body The response body containing the translated content.
	 */
	private function update_translation_manually( $details_row, $response_body ) {
		$etm                = ETM_eTranslation_Multilingual::get_etm_instance();
		$etm_query          = $etm->get_component( 'query' );
		$machine_translator = $etm->get_component( 'machine_translator' );

		if ( $details_row->from && $details_row->to && $details_row->from != $details_row->to ) {
			$is_gettext                   = str_starts_with( $details_row->id, 'g-' );
			$original_strings             = explode( $machine_translator->translation_delimiter, $details_row->original );
			$translation_prepare_response = $machine_translator->prepare_strings_for_translation( $original_strings );

			$xml_translations    = $machine_translator->xml_document_to_string_array( $response_body );
			$translation_strings = $machine_translator->process_xml_translations( $translation_prepare_response, $xml_translations );

			if ( $is_gettext ) {
				$dict_table = $etm_query->get_gettext_table_name( $details_row->to );
				// domain is unknown, only retrieve existing rows.
				$original_inserts = $etm_query->get_all_gettext_untranslated_strings( $details_row->to );
			} else {
				$dict_table = $etm_query->get_table_name( $details_row->to, $details_row->from );
				// insert original strings in table if they don't exist.
				$original_inserts = $etm_query->original_strings_sync( $details_row->to, $original_strings );
			}

			$max_id  = $this->db->get_row( "SELECT MAX(id) as id FROM $dict_table" )->id;
			$next_id = intval( $max_id ) + 1;

			$update_strings        = array();
			$original_string_count = count( $original_strings );
			for ( $i = 0; $i < $original_string_count; $i++ ) {
				$string = $original_strings[ $i ];
				$row    = array(
					'id'         => $next_id + $i,
					'original'   => $string,
					'translated' => etm_sanitize_string( $translation_strings[ $i ] ),
					'status'     => $etm_query->get_constant_machine_translated(),
				);
				if ( $is_gettext ) {
					// fallback values.
					$row['original_id'] = 0;
					$row['plural_form'] = 0;
					$row['domain']      = 'default';

					foreach ( $original_inserts as $key => $value ) {
						if ( $value['original'] === $string ) {
							$row['id']          = $value['id'];
							$row['original_id'] = $value['original_id'];
							$row['plural_form'] = $value['plural_form'];
							$row['domain']      = $value['domain'];
							break;
						}
					}
				} else {
					$row['original_id'] = $original_inserts[ $string ]->id;
				}
				array_push( $update_strings, $row );
			}

			$updated_rows = 0;
			// insert translations.
			if ( $is_gettext ) {
				$gettext_insert_update = $etm_query->get_query_component( 'gettext_insert_update' );
				$updated_rows          = $gettext_insert_update->update_gettext_strings( $update_strings, $details_row->to, array( 'id', 'original', 'translated', 'domain', 'status', 'original_id', 'plural_form' ) ) / 2; // divided by 2 as ON DUPLICATE KEY UPDATE operation is: insert + update.
			} else {
				$updated_rows = $etm_query->update_strings( $update_strings, $details_row->to, array( 'id', 'original', 'translated', 'status', 'original_id' ) );
				// delete previously inserted untranslated rows.
				$etm_query->remove_possible_duplicates( $update_strings, $details_row->to, 'regular' );
			}

			if ( count( $update_strings ) !== $updated_rows ) {
				error_log( 'Translation list size differs from updated row count (' . count( $update_strings ) . ' vs ' . $updated_rows . '). Please contact technical support.' );
			}
		} else {
			error_log( "Cannot update translations manually - invalid language pair '$details_row->from -> $details_row->to'. Please contact technical support." );
		}
	}

	/**
	 * Get the incomplete translation entry from the eTranslation database.
	 *
	 * @param string $id The ID of the translation entry.
	 * @return object|null The incomplete translation entry if found, null otherwise.
	 */
	private function get_incomplete_db_entry( $id ) {
		return $this->db->get_row( "SELECT * FROM $this->jobs_table WHERE id = '$id' AND status IN ('TRANSLATING', 'TIMEOUT')" );
	}

}
