<?php
namespace AIPT\Api\BulkGenerator\SeoMeta;

use AIPT\Api\BulkGenerator\SeoMeta\Core\SeoMetaWriter;
use AIPT\Api\BulkGenerator\SeoMeta\Core\SeoPluginDetector;

/**
 * Manages SEO Meta history records with CRUD operations, caching, and rollback functionality.
 *
 * @package AIPT\Api\BulkGenerator\SeoMeta
 */
class SeoMetaHistoryManager {

	/**
	 * Cache prefix for transients.
	 *
	 * @var string
	 */
	private const CACHE_PREFIX = 'aipt_seo_meta_history_';

	/**
	 * Cache TTL in seconds.
	 *
	 * @var int
	 */
	private const CACHE_TTL = 60;

	/**
	 * Mapping for ORDER BY columns to prevent SQL injection.
	 *
	 * @var array
	 */
	private const ORDER_BY_MAP = [
		'id'         => 'h.id',
		'created_at' => 'h.created_at',
		'product_id' => 'h.product_id',
		'status'     => 'h.status',
		'meta_type'  => 'h.meta_type',
		'updated_at' => 'h.updated_at',
	];

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

	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table_name;

	/**
	 * Constructor.
	 *
	 * @param \wpdb $wpdb Optional WordPress database object. Defaults to global $wpdb.
	 */
	public function __construct( $wpdb = null ) {
		$this->wpdb       = $wpdb ?? $GLOBALS['wpdb'];
		$this->table_name = $this->wpdb->prefix . 'aipt_seo_meta_history';
	}

	/**
	 * Get cache key for a given identifier.
	 *
	 * @param string $identifier The cache identifier.
	 * @return string The cache key.
	 */
	private function get_cache_key( $identifier ) {
		$version = $this->get_cache_version();
		return self::CACHE_PREFIX . $version . ':' . $identifier;
	}

	/**
	 * Get cache version for invalidation.
	 *
	 * @return int The cache version.
	 */
	private function get_cache_version() {
		$cache_key = self::CACHE_PREFIX . 'version';
		$version   = wp_cache_get( $cache_key );
		if ( false === $version ) {

			$added = wp_cache_add( $cache_key, 1 );
			if ( $added ) {
				$version = 1;
			} else {

				$version = wp_cache_get( $cache_key );
				if ( false === $version ) {
					$version = 1;
				}
			}
		}
		return $version;
	}

	/**
	 * Invalidate all caches.
	 *
	 * @return void
	 */
	private function invalidate_cache() {
		$version = $this->get_cache_version();
		wp_cache_set( self::CACHE_PREFIX . 'version', $version + 1 );
	}

	/**
	 * Insert a new SEO meta history record.
	 *
	 * @param int    $product_id      The product ID.
	 * @param string $meta_type       The meta type (e.g., 'title', 'description').
	 * @param string $previous_value  The previous meta value.
	 * @param string $generated_value The generated meta value.
	 * @param string $status          The status (default: 'pending').
	 * @param int    $variant_index   The variant index (default: 0).
	 * @param string $plugin_slug     The SEO plugin slug used at creation time (default: '').
	 * @return int|false The inserted record ID or false on failure.
	 */
	public function insert( $product_id, $meta_type, $previous_value, $generated_value, $status = 'pending', $variant_index = 0, $plugin_slug = '' ) {
		$product_id      = absint( $product_id );
		$meta_type       = sanitize_text_field( $meta_type );
		$previous_value  = sanitize_textarea_field( $previous_value );
		$generated_value = sanitize_textarea_field( $generated_value );
		$status          = sanitize_text_field( $status );
		$variant_index   = absint( $variant_index );
		$plugin_slug     = sanitize_text_field( $plugin_slug );

		$data = [
			'product_id'      => $product_id,
			'meta_type'       => $meta_type,
			'previous_value'  => $previous_value,
			'generated_value' => $generated_value,
			'status'          => $status,
			'variant_index'   => $variant_index,
			'plugin_slug'     => $plugin_slug,
			'created_at'      => current_time( 'mysql' ),
			'updated_at'      => current_time( 'mysql' ),
		];

		$formats = [
			'%d',
			'%s',
			'%s',
			'%s',
			'%s',
			'%d',
			'%s',
			'%s',
			'%s',
		];

		$result = $this->wpdb->insert( $this->table_name, $data, $formats );

		if ( $result ) {
			$this->invalidate_cache();

			return $this->wpdb->insert_id;
		}

		return false;
	}

	/**
	 * Get a record by ID.
	 *
	 * @param int $id The record ID.
	 * @return array|null The record or null if not found.
	 */
	public function get_by_id( $id ) {
		$id = absint( $id );

		$cache_key = $this->get_cache_key( 'id_' . $id );
		$cached     = wp_cache_get( $cache_key );

		if ( false !== $cached ) {

			return 'NOT_FOUND' === $cached ? null : $cached;
		}

		$result = $this->wpdb->get_row(
			$this->wpdb->prepare(
				"SELECT * FROM {$this->table_name} WHERE id = %d",
				$id
			),
			ARRAY_A
		);

		$cache_value = ( null !== $result ) ? $result : 'NOT_FOUND';
		wp_cache_set( $cache_key, $cache_value, '', self::CACHE_TTL );

		return $result;
	}

	/**
	 * Get records by product ID.
	 *
	 * @param int    $product_id The product ID.
	 * @param string $status     Optional status filter.
	 * @param int    $limit      The limit (default: 50).
	 * @return array Array of records.
	 */
	public function get_by_product( $product_id, $status = null, $limit = 50 ) {
		$product_id = absint( $product_id );
		$limit      = absint( $limit );

		$cache_key = $this->get_cache_key( 'product_' . $product_id . '_' . ( $status ?? 'all' ) . '_' . $limit );
		$cached     = wp_cache_get( $cache_key );

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

		$query = "SELECT * FROM {$this->table_name} WHERE product_id = %d";
		$args  = [ $product_id ];

		if ( null !== $status ) {
			$status = sanitize_text_field( $status );
			$query .= ' AND status = %s';
			$args[] = $status;
		}

		$query .= ' ORDER BY created_at DESC LIMIT %d';
		$args[] = $limit;

		$result = $this->wpdb->get_results(

			$this->wpdb->prepare( $query, $args ),
			ARRAY_A
		);

		wp_cache_set( $cache_key, $result ?? array(), '', self::CACHE_TTL );

		return $result ?? [];
	}

	/**
	 * Get all records with optional filtering and pagination.
	 *
	 * @param array $args {
	 *     Optional arguments.
	 *
	 *     @type string $status     Filter by status.
	 *     @type string $meta_type  Filter by meta type.
	 *     @type string $search     Search term for product name.
	 *     @type int    $product_id Filter by product ID.
	 *     @type string $orderby    Column to order by (default: 'created_at').
	 *     @type string $order      Order direction (default: 'DESC').
	 *     @type int    $page       Page number (default: 1).
	 *     @type int    $per_page   Records per page (default: 20).
	 * }
	 * @return array {
	 *     @type array $items       Array of history records with product info.
	 *     @type int   $total       Total record count.
	 *     @type int   $total_pages Total number of pages.
	 * }
	 */
	public function get_all( $args = [] ) {
		$defaults = [
			'status'     => null,
			'meta_type'  => null,
			'search'     => null,
			'product_id' => 0,
			'orderby'    => 'created_at',
			'order'      => 'DESC',
			'page'       => 1,
			'per_page'   => 20,
		];

		$args = wp_parse_args( $args, $defaults );

		$status     = $args['status'] ? sanitize_text_field( $args['status'] ) : null;
		$meta_type  = $args['meta_type'] ? sanitize_text_field( $args['meta_type'] ) : null;
		$search     = $args['search'] ? sanitize_text_field( $args['search'] ) : null;
		$product_id = absint( $args['product_id'] );
		$safe_orderby = self::ORDER_BY_MAP[ $args['orderby'] ] ?? 'h.created_at';
		$safe_order   = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';
		$page       = max( 1, absint( $args['page'] ) );
		$per_page   = max( 1, min( 100, absint( $args['per_page'] ) ) );
		$offset     = ( $page - 1 ) * $per_page;

		$cache_key = $this->get_cache_key(
			'all_v2_' . md5(
				wp_json_encode(
					[
						'status'     => $status,
						'meta_type'  => $meta_type,
						'search'     => $search,
						'product_id' => $product_id,
						'orderby'    => $safe_orderby,
						'order'      => $safe_order,
						'page'       => $page,
						'per_page'   => $per_page,
					]
				)
			)
		);

		$cached = wp_cache_get( $cache_key );
		if ( false !== $cached ) {
			return $cached;
		}

		$where_clauses = [ '1=1' ];
		$params        = [];

		if ( null !== $status ) {
			$where_clauses[] = 'h.status = %s';
			$params[]        = $status;
		}

		if ( null !== $meta_type ) {
			$where_clauses[] = 'h.meta_type = %s';
			$params[]        = $meta_type;
		}

		if ( $product_id > 0 ) {
			$where_clauses[] = 'h.product_id = %d';
			$params[]        = $product_id;
		}

		if ( null !== $search ) {
			$where_clauses[] = 'p.post_title LIKE %s';
			$params[]        = '%' . $this->wpdb->esc_like( $search ) . '%';
		}

		$where_sql = implode( ' AND ', $where_clauses );

		$count_query = "SELECT COUNT(*) FROM {$this->table_name} h
			LEFT JOIN {$this->wpdb->posts} p ON h.product_id = p.ID
			WHERE {$where_sql}";

		if ( empty( $params ) ) {

			$total = (int) $this->wpdb->get_var( $count_query );
		} else {

			$total = (int) $this->wpdb->get_var( $this->wpdb->prepare( $count_query, $params ) );
		}

		$select_query = "SELECT h.*, p.post_title as product_name
			FROM {$this->table_name} h
			LEFT JOIN {$this->wpdb->posts} p ON h.product_id = p.ID
			WHERE {$where_sql}
			ORDER BY {$safe_orderby} {$safe_order}
			LIMIT %d OFFSET %d";

		$params[] = $per_page;
		$params[] = $offset;

		$items = $this->wpdb->get_results(

			$this->wpdb->prepare( $select_query, $params ),
			ARRAY_A
		);

		if ( ! empty( $items ) ) {
			foreach ( $items as &$item ) {
				$item['product_image'] = \get_the_post_thumbnail_url( $item['product_id'], 'thumbnail' ) ?: null;
			}
			unset( $item );
		}

		$result = [
			'items'       => $items ?: [],
			'total'       => $total,
			'total_pages' => (int) ceil( $total / $per_page ),
		];

		wp_cache_set( $cache_key, $result, '', self::CACHE_TTL );

		return $result;
	}

	/**
	 * Update the status of a record.
	 *
	 * @param int    $id        The record ID.
	 * @param string $new_status The new status.
	 * @return bool True on success, false on failure.
	 */
	public function update_status( $id, $new_status ) {
		$id         = absint( $id );
		$new_status = sanitize_text_field( $new_status );

		$data    = [
			'status'     => $new_status,
			'updated_at' => current_time( 'mysql' ),
		];
		$formats = [ '%s', '%s' ];

		$result = $this->wpdb->update(
			$this->table_name,
			$data,
			[ 'id' => $id ],
			$formats,
			[ '%d' ]
		);

		if ( $result === false ) {
			return false;
		}

		if ( $result > 0 ) {
			$this->invalidate_cache();
		}

		return true;
	}

	/**
	 * Delete a record by ID.
	 *
	 * @param int $id The record ID.
	 * @return bool True on success, false on failure.
	 */
	public function delete( $id ) {
		$id = absint( $id );

		$result = $this->wpdb->delete(
			$this->table_name,
			[ 'id' => $id ],
			[ '%d' ]
		);

		if ( false !== $result && $result > 0 ) {
			$this->invalidate_cache();
			return true;
		}

		return false;
	}

	/**
	 * Delete all records for a product.
	 *
	 * @param int $product_id The product ID.
	 * @return bool True on success, false on failure.
	 */
	public function delete_by_product( $product_id ) {
		$product_id = absint( $product_id );

		$result = $this->wpdb->delete(
			$this->table_name,
			[ 'product_id' => $product_id ],
			[ '%d' ]
		);

		if ( false !== $result && $result > 0 ) {
			$this->invalidate_cache();
			return true;
		}

		return false;
	}

	/**
	 * Bulk delete records by IDs.
	 *
	 * @param array $ids Array of record IDs.
	 * @return int Number of deleted records.
	 */
	public function bulk_delete( array $ids ): int {
		if ( empty( $ids ) ) {
			return 0;
		}

		$ids = array_map( 'absint', $ids );
		$ids = array_filter( $ids );

		if ( empty( $ids ) ) {
			return 0;
		}

		$placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

		$result = $this->wpdb->query(
			$this->wpdb->prepare(
				"DELETE FROM {$this->table_name} WHERE id IN ({$placeholders})",
				...$ids
			)
		);

		if ( false !== $result && $result > 0 ) {
			$this->invalidate_cache();
			return (int) $result;
		}

		return 0;
	}

	/**
	 * Delete all records.
	 *
	 * @param bool $confirm Explicit confirmation required to execute destructive operation.
	 * @return int Number of deleted records, or 0 if not confirmed.
	 */
	public function delete_all( bool $confirm = false ): int {
		if ( ! $confirm ) {
			return 0;
		}

		$current_user = \wp_get_current_user();
		$user_info    = $current_user->ID > 0 ? 'user_id:' . $current_user->ID : 'Unknown';

		$result = $this->wpdb->query( "DELETE FROM {$this->table_name}" );

		if ( false !== $result && $result > 0 ) {

			error_log( sprintf(
				'[AIPT SEO Meta] History purge: %d records deleted by %s at %s',
				$result,
				$user_info,
				current_time( 'mysql' )
			) );
			$this->invalidate_cache();
			return (int) $result;
		}

		return 0;
	}

	/**
	 * Get the previous value for a specific record.
	 *
	 * @param int $id The record ID.
	 * @return string|null The previous value or null if not found.
	 */
	public function get_previous_value( $id ) {
		$id = absint( $id );

		$cache_key = $this->get_cache_key( 'prev_value_' . $id );
		$cached     = wp_cache_get( $cache_key );

		if ( false !== $cached ) {

			return 'NOT_FOUND' === $cached ? null : $cached;
		}

		$result = $this->wpdb->get_var(
			$this->wpdb->prepare(
				"SELECT previous_value FROM {$this->table_name} WHERE id = %d",
				$id
			)
		);

		$cache_value = ( null !== $result ) ? $result : 'NOT_FOUND';
		wp_cache_set( $cache_key, $cache_value, '', self::CACHE_TTL );

		return $result;
	}

	/**
	 * Rollback a record by restoring the previous value.
	 *
	 * @param int $id The record ID.
	 * @return bool True on success, false on failure.
	 */
	public function rollback( $id ) {
		$record = $this->get_by_id( $id );

		if ( ! $record ) {
			return false;
		}

		if ( 'rolled_back' === ( $record['status'] ?? '' ) ) {
			return false;
		}

		$product_id     = absint( $record['product_id'] );
		$meta_type      = sanitize_text_field( $record['meta_type'] );
		$previous_value = $record['previous_value'];

		if ( null === $previous_value ) {
			return false;
		}

		$stored_plugin_slug = $record['plugin_slug'] ?? '';
		if ( ! empty( $stored_plugin_slug ) ) {
			$plugin_slug = $stored_plugin_slug;
		} else {

			$detected_plugin = SeoPluginDetector::detect();
			$plugin_slug     = $detected_plugin['plugin_slug'] ?? 'standalone';
		}

		try {
			$writer = new SeoMetaWriter();

			if ( '' === $previous_value ) {

				$write_result = $writer->write( $product_id, $meta_type, '', $plugin_slug );
			} else {
				$write_result = $writer->write( $product_id, $meta_type, $previous_value, $plugin_slug );
			}

			if ( ! $write_result ) {

				return false;
			}

			$status_updated = $this->update_status( $id, 'rolled_back' );
			if ( ! $status_updated ) {

				$generated_value = $record['generated_value'] ?? '';
				if ( ! empty( $generated_value ) ) {
					$compensate_result = $writer->write( $product_id, $meta_type, $generated_value, $plugin_slug );
					if ( ! $compensate_result ) {

						error_log( sprintf(
							'[AIPT SEO Meta] Compensating write FAILED for history ID %d, product %d, meta_type %s, plugin %s. Value: %s',
							$id,
							$product_id,
							$meta_type,
							$plugin_slug,
							mb_substr( $generated_value, 0, 100 )
						) );

						$this->wpdb->update(
							$this->table_name,
							[ 'status' => 'rollback_failed' ],
							[ 'id' => $id ],
							[ '%s' ],
							[ '%d' ]
						);
						$this->invalidate_cache();

						error_log( sprintf(
							'[AIPT SEO Meta] ROLLBACK INCONSISTENT: history_id=%d, product_id=%d, meta_type=%s - status update and compensating write both failed',
							$id,
							$product_id,
							$meta_type
						) );
						return false;
					}
				} else {

					$this->wpdb->update(
						$this->table_name,
						[ 'status' => 'rollback_failed' ],
						[ 'id' => $id ],
						[ '%s' ],
						[ '%d' ]
					);
					$this->invalidate_cache();
					return false;
				}

				$this->wpdb->update(
					$this->table_name,
					[ 'status' => 'rollback_reverted' ],
					[ 'id' => $id ],
					[ '%s' ],
					[ '%d' ]
				);
				$this->invalidate_cache();
				return false;
			}

			return true;
		} catch ( \Exception $e ) {

			return false;
		}
	}
}
