<?php
/**
 * AnyWareX Clone Translate – Mapping Manager
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class AWXCT_Clone_Translate_Mapping {

	/** @var string */
	protected $table;

	/** @var string */
	protected $cache_group = 'awxct';

	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'awxct_translations';
	}

	/**
	 * Validate a DB identifier (table/column) to alnum/underscore only.
	 *
	 * @param string $ident Identifier.
	 * @return string
	 */
	protected function validate_ident( $ident ) {
		$ident = (string) $ident;
		if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $ident ) ) {
			return '';
		}
		return $ident;
	}

	/**
	 * Get validated table identifier (WP < 6.2 has no %i).
	 *
	 * @return string
	 */
	protected function table_ident() {
		return $this->validate_ident( $this->table );
	}

	/**
	 * Get validated posts table identifier (WP < 6.2 has no %i).
	 *
	 * @return string
	 */
	protected function posts_ident() {
		global $wpdb;
		return $this->validate_ident( $wpdb->posts );
	}

	/**
	 * Build a cache key (blog-aware).
	 *
	 * @param string $suffix
	 * @return string
	 */
	protected function cache_key( $suffix ) {
		$blog_id = function_exists( 'get_current_blog_id' ) ? (int) get_current_blog_id() : 0;
		return 'awxct_map_' . $blog_id . '_' . (string) $suffix;
	}

	/**
	 * Invalidate mapping-related caches.
	 *
	 * @return void
	 */
	protected function invalidate_cache() {
		wp_cache_delete( $this->cache_key( 'all' ), $this->cache_group );
		// by_post / exists are dynamic; we rely on short TTL + deleting known keys where possible.
	}

	/**
	 * Retrieves the mappings for a specific post.
	 *
	 * @param int $post_id
	 * @return array
	 */
	public function get_by_post( $post_id ) {
		global $wpdb;

		$post_id = (int) $post_id;
		if ( $post_id <= 0 ) {
			return array();
		}

		$ck = $this->cache_key( 'by_post_' . $post_id );
		$cv = wp_cache_get( $ck, $this->cache_group );
		if ( false !== $cv && is_array( $cv ) ) {
			return $cv;
		}

		$table_ident = $this->table_ident();
		if ( '' === $table_ident ) {
			return array();
		}

		// Cached read-query (TTL=30s). Identifier is validated; WP<6.2 has no %i.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT * FROM `' . esc_sql( $table_ident ) . '` WHERE original_post_id = %d OR translated_post_id = %d',
				$post_id,
				$post_id
			)
		);

		$rows = is_array( $rows ) ? $rows : array();
		wp_cache_set( $ck, $rows, $this->cache_group, 30 );

		return $rows;
	}

	/**
	 * Checks if a mapping exists.
	 *
	 * @param int    $original_id
	 * @param int    $translated_id
	 * @param string $language_code
	 * @return bool
	 */
	public function exists( $original_id, $translated_id, $language_code ) {
		global $wpdb;

		$original_id   = (int) $original_id;
		$translated_id = (int) $translated_id;
		$language_code = (string) $language_code;

		if ( $original_id <= 0 || $translated_id <= 0 || '' === $language_code ) {
			return false;
		}

		$hash = md5( $original_id . '|' . $translated_id . '|' . strtolower( $language_code ) );
		$ck   = $this->cache_key( 'exists_' . $hash );
		$cv   = wp_cache_get( $ck, $this->cache_group );
		if ( false !== $cv ) {
			return (bool) $cv;
		}

		$table_ident = $this->table_ident();
		if ( '' === $table_ident ) {
			return false;
		}

		// Cached read-query (TTL=30s). Identifier is validated; WP<6.2 has no %i.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(*) FROM `' . esc_sql( $table_ident ) . '` WHERE original_post_id = %d AND translated_post_id = %d AND language_code = %s',
				$original_id,
				$translated_id,
				$language_code
			)
		);

		$exists = ( $count > 0 );
		wp_cache_set( $ck, $exists, $this->cache_group, 30 );

		return $exists;
	}

	/**
	 * Creates or updates a mapping.
	 *
	 * @param int    $original_id
	 * @param int    $translated_id
	 * @param string $language_code
	 * @return void
	 */
	public function add_or_update( $original_id, $translated_id, $language_code ) {
		global $wpdb;

		$original_id   = (int) $original_id;
		$translated_id = (int) $translated_id;
		$language_code = sanitize_text_field( (string) $language_code );

		if ( $original_id <= 0 || $translated_id <= 0 ) {
			return;
		}

		if ( $this->exists( $original_id, $translated_id, $language_code ) ) {
			// WRITE query => NoCaching ignore is fine.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->update(
				$this->table,
				array( 'language_code' => $language_code ),
				array(
					'original_post_id'   => $original_id,
					'translated_post_id' => $translated_id,
				),
				array( '%s' ),
				array( '%d', '%d' )
			);
		} else {
			// WRITE query => NoCaching ignore is fine.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->insert(
				$this->table,
				array(
					'original_post_id'   => $original_id,
					'translated_post_id' => $translated_id,
					'language_code'      => $language_code,
				),
				array( '%d', '%d', '%s' )
			);
		}

		// Invalidate relevant caches.
		wp_cache_delete( $this->cache_key( 'by_post_' . $original_id ), $this->cache_group );
		wp_cache_delete( $this->cache_key( 'by_post_' . $translated_id ), $this->cache_group );
		$this->invalidate_cache();
	}

	/**
	 * Deletes a mapping.
	 *
	 * @param int $id
	 * @return int|false
	 */
	public function delete( $id ) {
		global $wpdb;

		$id = (int) $id;
		if ( $id <= 0 ) {
			return false;
		}

		// WRITE query => NoCaching ignore is fine.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$res = $wpdb->delete(
			$this->table,
			array( 'id' => $id ),
			array( '%d' )
		);

		$this->invalidate_cache();
		return $res;
	}

	/**
	 * Deletes all mappings that reference a given post (root/original/translated).
	 *
	 * @param int $post_id
	 * @return void
	 */
	public function delete_by_post( $post_id ) {
		global $wpdb;

		$post_id = (int) $post_id;
		if ( $post_id <= 0 ) {
			return;
		}

		$table_ident = $this->table_ident();
		if ( '' === $table_ident ) {
			return;
		}

		// WRITE query => NoCaching ignore is fine. Identifier is validated; WP<6.2 has no %i.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				'DELETE FROM `' . esc_sql( $table_ident ) . '` WHERE root_post_id = %d OR original_post_id = %d OR translated_post_id = %d',
				$post_id,
				$post_id,
				$post_id
			)
		);

		wp_cache_delete( $this->cache_key( 'by_post_' . $post_id ), $this->cache_group );
		$this->invalidate_cache();
	}

	/**
	 * Retrieves all mappings.
	 *
	 * @return array
	 */
	public function get_all() {
		global $wpdb;

		$ck = $this->cache_key( 'all' );
		$cv = wp_cache_get( $ck, $this->cache_group );
		if ( false !== $cv && is_array( $cv ) ) {
			return $cv;
		}

		$table_ident = $this->table_ident();
		$posts_ident = $this->posts_ident();
		if ( '' === $table_ident || '' === $posts_ident ) {
			return array();
		}

		/*
		 * WP<6.2: no %i placeholder for identifiers.
		 * We keep identifiers validated and pass a real placeholder (WHERE 1=%d) so prepare() is effective.
		 * We do NOT use $table_sql / $posts_sql variables (those trigger NotPrepared on variable identifiers).
		 */
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT m.*, p1.post_title AS original_title, p2.post_title AS translated_title ' .
				'FROM ' . esc_sql( $table_ident ) . ' m ' .
				'LEFT JOIN ' . esc_sql( $posts_ident ) . ' p1 ON m.original_post_id = p1.ID ' .
				'LEFT JOIN ' . esc_sql( $posts_ident ) . ' p2 ON m.translated_post_id = p2.ID ' .
				'WHERE 1 = %d ' .
				'ORDER BY m.id DESC',
				1
			)
		);

		$rows = is_array( $rows ) ? $rows : array();
		wp_cache_set( $ck, $rows, $this->cache_group, 30 );

		return $rows;
	}
}
