<?php
/**
 * Review Repository
 *
 * @package Ugcify\Database
 */

namespace Ugcify\Database;

use Ugcify\Review\Review;
use WP_Error;

/**
 * Class ReviewRepository
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- All queries use $wpdb->prepare(); table names are trusted internal constants.
 */
class ReviewRepository {
	/**
	 * Social review types without star ratings.
	 *
	 * @var array<int,string>
	 */
	private const UNRATED_SOCIAL_TYPES = array( 'instagram', 'youtube', 'tiktok' );

	/**
	 * Table name
	 *
	 * @var string
	 */
	private $table;

	/**
	 * Product tags table existence cache.
	 *
	 * @var bool|null
	 */
	private $has_product_tags_table = null;

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->table = $wpdb->prefix . 'ugcify_reviews';
	}

	/**
	 * Create a new review
	 *
	 * @param Review $review Review object.
	 * @return int|WP_Error Review ID on success, WP_Error on failure.
	 */
	public function create( Review $review ) {
		global $wpdb;

		$data = array(
			'user_id'     => $review->user_id,
			'guest_name'  => $review->guest_name,
			'guest_email' => $review->guest_email,
			'product_id'  => $review->product_id,
			'rating'      => $review->rating,
			'content'     => $review->content,
			'status'      => $review->status,
			'is_featured' => $review->is_featured,
			'is_pinned'   => $review->is_pinned,
			'type'        => $review->type,
			'points'      => $review->points,
			'created_at'  => current_time( 'mysql' ),
			'updated_at'  => current_time( 'mysql' ),
		);

		// Remove null values to let DB defaults apply
		foreach ( $data as $k => $v ) {
			if ( null === $v ) {
				unset( $data[ $k ] );
			}
		}

		// Build format array matching the remaining data, to keep types explicit.
		$format_map = array(
			'user_id'     => '%d',
			'guest_name'  => '%s',
			'guest_email' => '%s',
			'product_id'  => '%d',
			'rating'      => '%d',
			'content'     => '%s',
			'status'      => '%s',
			'is_featured' => '%d',
			'is_pinned'   => '%d',
			'type'        => '%s',
			'points'      => '%d',
			'created_at'  => '%s',
			'updated_at'  => '%s',
		);
		$format = array();
		foreach ( $data as $key => $_value ) {
			if ( isset( $format_map[ $key ] ) ) {
				$format[] = $format_map[ $key ];
			}
		}

		$result = $wpdb->insert(
			$this->table,
			$data,
			$format
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Failed to create review.', 'ugcify' )
			);
		}

		return $wpdb->insert_id;
	}

	/**
	 * Find review by ID
	 *
	 * @param int $id Review ID.
	 * @return Review|null Review object or null if not found.
	 */
	public function find( $id ) {
		if ( function_exists( 'apply_filters' ) ) {
			$pre = apply_filters( 'ugcify_review_repository_pre_find', null, (int) $id );
			if ( null !== $pre ) {
				return $pre;
			}
		}

		global $wpdb;

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

		if ( ! $row ) {
			return null;
		}

		return new Review( $row );
	}

	/**
	 * Update review
	 *
	 * @param int   $id Review ID.
	 * @param array $data Data to update.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public function update( $id, array $data ) {
		global $wpdb;

		// Add updated timestamp.
		$data['updated_at'] = current_time( 'mysql' );

		// Build format array based on data types.
		$format = array();
		foreach ( $data as $key => $value ) {
			if ( in_array( $key, array( 'user_id', 'product_id', 'rating', 'points', 'is_featured', 'is_pinned' ), true ) ) {
				$format[] = '%d';
			} else {
				$format[] = '%s';
			}
		}

		$result = $wpdb->update(
			$this->table,
			$data,
			array( 'id' => $id ),
			$format,
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_update_error',
				__( 'Failed to update review.', 'ugcify' )
			);
		}

		return true;
	}

	/**
	 * Delete review
	 *
	 * @param int $id Review ID.
	 * @return bool|WP_Error True on success, WP_Error on failure.
	 */
	public function delete( $id ) {
		global $wpdb;

		$result = $wpdb->delete(
			$this->table,
			array( 'id' => $id ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_delete_error',
				__( 'Failed to delete review.', 'ugcify' )
			);
		}

		// Also delete review meta.
		$meta_table = $wpdb->prefix . 'ugcify_review_meta';
		$wpdb->delete(
			$meta_table,
			array( 'review_id' => $id ),
			array( '%d' )
		);

		return true;
	}

	/**
	 * Query reviews with filters
	 *
	 * @param array $filters Query filters.
	 * @return array Array of Review objects.
	 */
	public function query( array $filters = array() ) {
		global $wpdb;

		$where_clauses = array();
		$join_clauses  = array();
		$values        = array();

		// Filter by ID.
		if ( ! empty( $filters['id'] ) ) {
			$where_clauses[] = 'r.id = %d';
			$values[]        = $filters['id'];
		}

		// Filter by product_id (includes reviews where product_id matches OR has hotspot tags for this product).
		if ( ! empty( $filters['product_id'] ) ) {
			if ( $this->has_product_tags_table() ) {
				$product_tags_table = $wpdb->prefix . 'ugcify_product_tags';
				$join_clauses[]     = "LEFT JOIN {$product_tags_table} AS pt ON FLOOR(pt.media_id / 1000) = r.id AND pt.product_id = %d";
				$values[]           = $filters['product_id'];
				$where_clauses[]    = '(r.product_id = %d OR pt.id IS NOT NULL)';
				$values[]           = $filters['product_id'];
			} else {
				$where_clauses[] = 'r.product_id = %d';
				$values[]        = $filters['product_id'];
			}
		}

		// Filter by product tags (slug CSV/array; OR within values).
		$tag_slugs = $this->normalize_term_slug_filter( isset( $filters['tags'] ) ? $filters['tags'] : array() );
		if ( ! empty( $tag_slugs ) ) {
			$join_clauses[] = "INNER JOIN {$wpdb->term_relationships} AS tr_tag ON tr_tag.object_id = r.product_id";
			$join_clauses[] = "INNER JOIN {$wpdb->term_taxonomy} AS tt_tag ON tt_tag.term_taxonomy_id = tr_tag.term_taxonomy_id AND tt_tag.taxonomy = 'product_tag'";
			$join_clauses[] = "INNER JOIN {$wpdb->terms} AS t_tag ON t_tag.term_id = tt_tag.term_id";
			$placeholders   = implode( ',', array_fill( 0, count( $tag_slugs ), '%s' ) );
			$where_clauses[] = "t_tag.slug IN ({$placeholders})";
			foreach ( $tag_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		// Filter by product categories (slug CSV/array; OR within values).
		$category_slugs = $this->normalize_term_slug_filter( isset( $filters['categories'] ) ? $filters['categories'] : array() );
		if ( ! empty( $category_slugs ) ) {
			$join_clauses[] = "INNER JOIN {$wpdb->term_relationships} AS tr_cat ON tr_cat.object_id = r.product_id";
			$join_clauses[] = "INNER JOIN {$wpdb->term_taxonomy} AS tt_cat ON tt_cat.term_taxonomy_id = tr_cat.term_taxonomy_id AND tt_cat.taxonomy = 'product_cat'";
			$join_clauses[] = "INNER JOIN {$wpdb->terms} AS t_cat ON t_cat.term_id = tt_cat.term_id";
			$placeholders   = implode( ',', array_fill( 0, count( $category_slugs ), '%s' ) );
			$where_clauses[] = "t_cat.slug IN ({$placeholders})";
			foreach ( $category_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		// Filter by user_id.
		if ( ! empty( $filters['user_id'] ) ) {
			$where_clauses[] = 'r.user_id = %d';
			$values[]        = $filters['user_id'];
		}

		// Filter by status.
		if ( ! empty( $filters['status'] ) ) {
			$where_clauses[] = 'r.status = %s';
			$values[]        = $filters['status'];
		}

		if ( ! empty( $filters['contributor_type'] ) ) {
			$meta_table = $wpdb->prefix . 'ugcify_review_meta';
			$filter     = sanitize_key( (string) $filters['contributor_type'] );

			$join_clauses[] = "LEFT JOIN {$meta_table} AS rm_contributor ON r.id = rm_contributor.review_id AND rm_contributor.meta_key = '_ugcify_contributor_type'";

			if ( 'influencer' === $filter ) {
				$where_clauses[] = "rm_contributor.meta_value = 'influencer'";
			} elseif ( in_array( $filter, array( 'direct', 'ugcify', 'non_influencer' ), true ) ) {
				$where_clauses[] = "(rm_contributor.meta_id IS NULL OR rm_contributor.meta_value <> 'influencer')";
			}
		}

		// Filter by featured flag.
		if ( isset( $filters['is_featured'] ) && '' !== $filters['is_featured'] ) {
			$where_clauses[] = 'r.is_featured = %d';
			$values[]        = (int) $filters['is_featured'];
		}

		// Filter by pinned flag.
		if ( isset( $filters['is_pinned'] ) && '' !== $filters['is_pinned'] ) {
			$where_clauses[] = 'r.is_pinned = %d';
			$values[]        = (int) $filters['is_pinned'];
		}

		// Filter by type(s).
		// When priority flags are active, pinned/featured reviews must always show
		// regardless of the type filter — they are exempt from the type restriction.
		if ( ! empty( $filters['type'] ) ) {
			$types = $filters['type'];
			if ( is_string( $types ) && strpos( $types, ',' ) !== false ) {
				$types = explode( ',', $types );
			}

			$priority_exempt = isset( $filters['priority_exempt_type'] )
				? filter_var( $filters['priority_exempt_type'], FILTER_VALIDATE_BOOLEAN )
				: ( ! empty( $filters['prioritize_pinned'] ) || ! empty( $filters['prioritize_featured'] ) );

			if ( is_array( $types ) ) {
				// If filtering for image or video, include mixed type.
				if ( in_array( 'image', $types, true ) || in_array( 'video', $types, true ) ) {
					$types[] = 'mixed';
					$types   = array_unique( $types );
				}

				$placeholders = implode( ',', array_fill( 0, count( $types ), '%s' ) );
				if ( $priority_exempt ) {
					$where_clauses[] = "(r.type IN ($placeholders) OR r.is_pinned = 1 OR r.is_featured = 1)";
				} else {
					$where_clauses[] = "r.type IN ($placeholders)";
				}
				foreach ( $types as $t ) {
					$values[] = $t;
				}
			} else {
				if ( $types === 'image' || $types === 'video' ) {
					if ( $priority_exempt ) {
						$where_clauses[] = "(r.type IN ('%s', 'mixed') OR r.is_pinned = 1 OR r.is_featured = 1)";
					} else {
						$where_clauses[] = "r.type IN ('%s', 'mixed')";
					}
					$values[] = $types;
				} else {
					if ( $priority_exempt ) {
						$where_clauses[] = '(r.type = %s OR r.is_pinned = 1 OR r.is_featured = 1)';
					} else {
						$where_clauses[] = 'r.type = %s';
					}
					$values[] = $types;
				}
			}
		}

		// Filter by rating(s).
		if ( ! empty( $filters['rating'] ) ) {
			$ratings = $filters['rating'];
			if ( is_string( $ratings ) && strpos( $ratings, ',' ) !== false ) {
				$ratings = explode( ',', $ratings );
			}

			if ( is_array( $ratings ) ) {
				$ratings         = array_map( 'intval', $ratings );
				$placeholders    = implode( ',', array_fill( 0, count( $ratings ), '%d' ) );
				$where_clauses[] = "r.rating IN ($placeholders)";
				foreach ( $ratings as $r ) {
					$values[] = $r;
				}
			} elseif ( is_numeric( $ratings ) ) {
				$where_clauses[] = 'r.rating = %d';
				$values[]        = (int) $ratings;
			}
		}

		// Filter by minimum rating.
		if ( ! empty( $filters['min_rating'] ) ) {
			$where_clauses[] = 'r.rating >= %d';
			$values[]        = $filters['min_rating'];
		}

		// Filter by maximum rating.
		if ( ! empty( $filters['max_rating'] ) ) {
			$where_clauses[] = 'r.rating <= %d';
			$values[]        = $filters['max_rating'];
		}

		// Filter by date range.
		if ( ! empty( $filters['date_from'] ) ) {
			$where_clauses[] = 'r.created_at >= %s';
			$values[]        = $filters['date_from'];
		}

		if ( ! empty( $filters['date_to'] ) ) {
			$where_clauses[] = 'r.created_at <= %s';
			$values[]        = $filters['date_to'];
		}

		// Filter by has_image (meta check).
		if ( isset( $filters['has_image'] ) ) {
			$meta_table      = $wpdb->prefix . 'ugcify_review_meta';
			$join_clauses[]  = "LEFT JOIN {$meta_table} AS rm_image ON r.id = rm_image.review_id AND rm_image.meta_key = 'images'";
			$where_clauses[] = $filters['has_image'] ? 'rm_image.meta_id IS NOT NULL' : 'rm_image.meta_id IS NULL';
		}

		// Filter by has_video (meta check).
		if ( isset( $filters['has_video'] ) ) {
			$meta_table      = $wpdb->prefix . 'ugcify_review_meta';
			$join_clauses[]  = "LEFT JOIN {$meta_table} AS rm_video ON r.id = rm_video.review_id AND rm_video.meta_key = 'videos'";
			$where_clauses[] = $filters['has_video'] ? 'rm_video.meta_id IS NOT NULL' : 'rm_video.meta_id IS NULL';
		}

		// Search across multiple fields: content, author name, author email, product name, product ID.
		if ( ! empty( $filters['search_content'] ) ) {
			// Add necessary joins for user and product tables.
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			if ( ! in_array( "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID";
			}

			$search_term = '%' . $wpdb->esc_like( $filters['search_content'] ) . '%';
			
			// Build OR conditions for all searchable fields.
			$search_conditions = array(
				'r.content LIKE %s',
				'u.display_name LIKE %s',
				'u.user_email LIKE %s',
				'r.guest_name LIKE %s',
				'r.guest_email LIKE %s',
				'p.post_title LIKE %s',
			);

			// Add product ID search if numeric.
			if ( is_numeric( $filters['search_content'] ) ) {
				$search_conditions[] = 'r.product_id = %d';
				$where_clauses[]     = '(' . implode( ' OR ', $search_conditions ) . ')';
				// Add values for LIKE searches.
				for ( $i = 0; $i < 6; $i++ ) {
					$values[] = $search_term;
				}
				// Add value for product ID.
				$values[] = (int) $filters['search_content'];
			} else {
				$where_clauses[] = '(' . implode( ' OR ', $search_conditions ) . ')';
				// Add values for LIKE searches.
				for ( $i = 0; $i < 6; $i++ ) {
					$values[] = $search_term;
				}
			}
		}

		// Search by user email.
		if ( ! empty( $filters['search_user_email'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			$where_clauses[] = 'u.user_email LIKE %s';
			$values[]        = '%' . $wpdb->esc_like( $filters['search_user_email'] ) . '%';
		}

		// Search by guest email.
		if ( ! empty( $filters['search_guest_email'] ) ) {
			$where_clauses[] = 'r.guest_email LIKE %s';
			$values[]        = '%' . $wpdb->esc_like( $filters['search_guest_email'] ) . '%';
		}

		// Search by author (user display name or guest name).
		if ( ! empty( $filters['search_author'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			$where_clauses[] = '(u.display_name LIKE %s OR u.user_email LIKE %s OR r.guest_name LIKE %s OR r.guest_email LIKE %s)';
			$search_term     = '%' . $wpdb->esc_like( $filters['search_author'] ) . '%';
			$values[]        = $search_term;
			$values[]        = $search_term;
			$values[]        = $search_term;
			$values[]        = $search_term;
		}

		// Search by product name (WooCommerce).
		if ( ! empty( $filters['search_product_name'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID";
			}
			$where_clauses[] = "p.post_title LIKE %s AND p.post_type = 'product'";
			$values[]        = '%' . $wpdb->esc_like( $filters['search_product_name'] ) . '%';
		}

		// Search by product ID.
		if ( ! empty( $filters['search_product_id'] ) && is_numeric( $filters['search_product_id'] ) ) {
			$where_clauses[] = 'r.product_id = %d';
			$values[]        = (int) $filters['search_product_id'];
		}

		// Build the query.
		$sql = "SELECT DISTINCT r.* FROM {$this->table} AS r";

		// Add joins.
		if ( ! empty( $join_clauses ) ) {
			$sql .= ' ' . implode( ' ', array_unique( $join_clauses ) );
		}

		// Add where clauses.
		if ( ! empty( $where_clauses ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where_clauses );
		}

		// Add ordering.
		$allowed_order_by = array( 'id', 'product_id', 'rating', 'created_at', 'points', 'is_featured', 'is_pinned' );
		$order_by         = ! empty( $filters['order_by'] ) ? $filters['order_by'] : 'created_at';
		if ( ! in_array( $order_by, $allowed_order_by, true ) ) {
			$order_by = 'created_at';
		}
		$order = ! empty( $filters['order'] ) && strtoupper( $filters['order'] ) === 'ASC' ? 'ASC' : 'DESC';
		$prioritize_pinned   = ! empty( $filters['prioritize_pinned'] );
		$prioritize_featured = ! empty( $filters['prioritize_featured'] );

		if ( $prioritize_pinned && $prioritize_featured ) {
			$sql .= ' ORDER BY CASE WHEN r.is_pinned = 1 THEN 0 WHEN r.is_featured = 1 THEN 1 ELSE 2 END ASC, ';
		} elseif ( $prioritize_pinned ) {
			$sql .= ' ORDER BY r.is_pinned DESC, ';
		} elseif ( $prioritize_featured ) {
			$sql .= ' ORDER BY r.is_featured DESC, ';
		} else {
			$sql .= ' ORDER BY ';
		}

		$sql .= "r.{$order_by} {$order}";

		// Add pagination.
		$per_page = isset( $filters['per_page'] ) ? (int) $filters['per_page'] : 20;
		$page     = isset( $filters['page'] ) ? (int) $filters['page'] : 1;
		$offset   = ( $page - 1 ) * $per_page;

		$sql .= ' LIMIT %d OFFSET %d';
		array_push( $values, $per_page, $offset );

		$results = $wpdb->get_results(
			$wpdb->prepare( $sql, $values ),
			ARRAY_A
		);

		if ( ! $results ) {
			return array();
		}

		// Convert to Review objects.
		return array_map(
			function ( $row ) {
				return new Review( $row );
			},
			$results
		);
	}

	/**
	 * Get total count of reviews matching filters
	 *
	 * @param array $filters Query filters.
	 * @return int
	 */
	public function count( array $filters = array() ) {
		global $wpdb;

		$where_clauses = array();
		$join_clauses  = array();
		$values        = array();

		// Apply same filters as query method.
		if ( ! empty( $filters['id'] ) ) {
			$where_clauses[] = 'r.id = %d';
			$values[]        = $filters['id'];
		}

		if ( ! empty( $filters['product_id'] ) ) {
			if ( $this->has_product_tags_table() ) {
				$product_tags_table = $wpdb->prefix . 'ugcify_product_tags';
				$join_clauses[]     = "LEFT JOIN {$product_tags_table} AS pt ON FLOOR(pt.media_id / 1000) = r.id AND pt.product_id = %d";
				$values[]           = $filters['product_id'];
				$where_clauses[]    = '(r.product_id = %d OR pt.id IS NOT NULL)';
				$values[]           = $filters['product_id'];
			} else {
				$where_clauses[] = 'r.product_id = %d';
				$values[]        = $filters['product_id'];
			}
		}

		// Filter by product tags (slug CSV/array; OR within values).
		$tag_slugs = $this->normalize_term_slug_filter( isset( $filters['tags'] ) ? $filters['tags'] : array() );
		if ( ! empty( $tag_slugs ) ) {
			$join_clauses[] = "INNER JOIN {$wpdb->term_relationships} AS tr_tag ON tr_tag.object_id = r.product_id";
			$join_clauses[] = "INNER JOIN {$wpdb->term_taxonomy} AS tt_tag ON tt_tag.term_taxonomy_id = tr_tag.term_taxonomy_id AND tt_tag.taxonomy = 'product_tag'";
			$join_clauses[] = "INNER JOIN {$wpdb->terms} AS t_tag ON t_tag.term_id = tt_tag.term_id";
			$placeholders   = implode( ',', array_fill( 0, count( $tag_slugs ), '%s' ) );
			$where_clauses[] = "t_tag.slug IN ({$placeholders})";
			foreach ( $tag_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		// Filter by product categories (slug CSV/array; OR within values).
		$category_slugs = $this->normalize_term_slug_filter( isset( $filters['categories'] ) ? $filters['categories'] : array() );
		if ( ! empty( $category_slugs ) ) {
			$join_clauses[] = "INNER JOIN {$wpdb->term_relationships} AS tr_cat ON tr_cat.object_id = r.product_id";
			$join_clauses[] = "INNER JOIN {$wpdb->term_taxonomy} AS tt_cat ON tt_cat.term_taxonomy_id = tr_cat.term_taxonomy_id AND tt_cat.taxonomy = 'product_cat'";
			$join_clauses[] = "INNER JOIN {$wpdb->terms} AS t_cat ON t_cat.term_id = tt_cat.term_id";
			$placeholders   = implode( ',', array_fill( 0, count( $category_slugs ), '%s' ) );
			$where_clauses[] = "t_cat.slug IN ({$placeholders})";
			foreach ( $category_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		if ( ! empty( $filters['user_id'] ) ) {
			$where_clauses[] = 'r.user_id = %d';
			$values[]        = $filters['user_id'];
		}

		if ( ! empty( $filters['status'] ) ) {
			$where_clauses[] = 'r.status = %s';
			$values[]        = $filters['status'];
		}

		if ( ! empty( $filters['contributor_type'] ) ) {
			$meta_table = $wpdb->prefix . 'ugcify_review_meta';
			$filter     = sanitize_key( (string) $filters['contributor_type'] );

			$join_clauses[] = "LEFT JOIN {$meta_table} AS rm_contributor ON r.id = rm_contributor.review_id AND rm_contributor.meta_key = '_ugcify_contributor_type'";

			if ( 'influencer' === $filter ) {
				$where_clauses[] = "rm_contributor.meta_value = 'influencer'";
			} elseif ( in_array( $filter, array( 'direct', 'ugcify', 'non_influencer' ), true ) ) {
				$where_clauses[] = "(rm_contributor.meta_id IS NULL OR rm_contributor.meta_value <> 'influencer')";
			}
		}

		if ( isset( $filters['is_featured'] ) && '' !== $filters['is_featured'] ) {
			$where_clauses[] = 'r.is_featured = %d';
			$values[]        = (int) $filters['is_featured'];
		}

		if ( isset( $filters['is_pinned'] ) && '' !== $filters['is_pinned'] ) {
			$where_clauses[] = 'r.is_pinned = %d';
			$values[]        = (int) $filters['is_pinned'];
		}

		// Filter by type(s).
		// When priority flags are active, pinned/featured reviews must always show
		// regardless of the type filter — they are exempt from the type restriction.
		if ( ! empty( $filters['type'] ) ) {
			$types = $filters['type'];
			if ( is_string( $types ) && strpos( $types, ',' ) !== false ) {
				$types = explode( ',', $types );
			}

			$priority_exempt = isset( $filters['priority_exempt_type'] )
				? filter_var( $filters['priority_exempt_type'], FILTER_VALIDATE_BOOLEAN )
				: ( ! empty( $filters['prioritize_pinned'] ) || ! empty( $filters['prioritize_featured'] ) );

			if ( is_array( $types ) ) {
				// If filtering for image or video, include mixed type.
				if ( in_array( 'image', $types, true ) || in_array( 'video', $types, true ) ) {
					$types[] = 'mixed';
					$types   = array_unique( $types );
				}

				$placeholders = implode( ',', array_fill( 0, count( $types ), '%s' ) );
				if ( $priority_exempt ) {
					$where_clauses[] = "(r.type IN ($placeholders) OR r.is_pinned = 1 OR r.is_featured = 1)";
				} else {
					$where_clauses[] = "r.type IN ($placeholders)";
				}
				foreach ( $types as $t ) {
					$values[] = $t;
				}
			} else {
				if ( $types === 'image' || $types === 'video' ) {
					if ( $priority_exempt ) {
						$where_clauses[] = "(r.type IN ('%s', 'mixed') OR r.is_pinned = 1 OR r.is_featured = 1)";
					} else {
						$where_clauses[] = "r.type IN ('%s', 'mixed')";
					}
					$values[] = $types;
				} else {
					if ( $priority_exempt ) {
						$where_clauses[] = '(r.type = %s OR r.is_pinned = 1 OR r.is_featured = 1)';
					} else {
						$where_clauses[] = 'r.type = %s';
					}
					$values[] = $types;
				}
			}
		}

		// Filter by rating(s).
		if ( ! empty( $filters['rating'] ) ) {
			$ratings = $filters['rating'];
			if ( is_string( $ratings ) && strpos( $ratings, ',' ) !== false ) {
				$ratings = explode( ',', $ratings );
			}

			if ( is_array( $ratings ) ) {
				$ratings         = array_map( 'intval', $ratings );
				$placeholders    = implode( ',', array_fill( 0, count( $ratings ), '%d' ) );
				$where_clauses[] = "r.rating IN ($placeholders)";
				foreach ( $ratings as $r ) {
					$values[] = $r;
				}
			} elseif ( is_numeric( $ratings ) ) {
				$where_clauses[] = 'r.rating = %d';
				$values[]        = (int) $ratings;
			}
		}

		if ( ! empty( $filters['min_rating'] ) ) {
			$where_clauses[] = 'r.rating >= %d';
			$values[]        = $filters['min_rating'];
		}

		if ( ! empty( $filters['max_rating'] ) ) {
			$where_clauses[] = 'r.rating <= %d';
			$values[]        = $filters['max_rating'];
		}

		if ( ! empty( $filters['date_from'] ) ) {
			$where_clauses[] = 'r.created_at >= %s';
			$values[]        = $filters['date_from'];
		}

		if ( ! empty( $filters['date_to'] ) ) {
			$where_clauses[] = 'r.created_at <= %s';
			$values[]        = $filters['date_to'];
		}

		if ( isset( $filters['has_image'] ) ) {
			$meta_table      = $wpdb->prefix . 'ugcify_review_meta';
			$join_clauses[]  = "LEFT JOIN {$meta_table} AS rm_image ON r.id = rm_image.review_id AND rm_image.meta_key = 'images'";
			$where_clauses[] = $filters['has_image'] ? 'rm_image.meta_id IS NOT NULL' : 'rm_image.meta_id IS NULL';
		}

		if ( isset( $filters['has_video'] ) ) {
			$meta_table      = $wpdb->prefix . 'ugcify_review_meta';
			$join_clauses[]  = "LEFT JOIN {$meta_table} AS rm_video ON r.id = rm_video.review_id AND rm_video.meta_key = 'videos'";
			$where_clauses[] = $filters['has_video'] ? 'rm_video.meta_id IS NOT NULL' : 'rm_video.meta_id IS NULL';
		}

		// Search across multiple fields: content, author name, author email, product name, product ID.
		if ( ! empty( $filters['search_content'] ) ) {
			// Add necessary joins for user and product tables.
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			if ( ! in_array( "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID";
			}

			$search_term = '%' . $wpdb->esc_like( $filters['search_content'] ) . '%';
			
			// Build OR conditions for all searchable fields.
			$search_conditions = array(
				'r.content LIKE %s',
				'u.display_name LIKE %s',
				'u.user_email LIKE %s',
				'r.guest_name LIKE %s',
				'r.guest_email LIKE %s',
				'p.post_title LIKE %s',
			);

			// Add product ID search if numeric.
			if ( is_numeric( $filters['search_content'] ) ) {
				$search_conditions[] = 'r.product_id = %d';
				$where_clauses[]     = '(' . implode( ' OR ', $search_conditions ) . ')';
				// Add values for LIKE searches.
				for ( $i = 0; $i < 6; $i++ ) {
					$values[] = $search_term;
				}
				// Add value for product ID.
				$values[] = (int) $filters['search_content'];
			} else {
				$where_clauses[] = '(' . implode( ' OR ', $search_conditions ) . ')';
				// Add values for LIKE searches.
				for ( $i = 0; $i < 6; $i++ ) {
					$values[] = $search_term;
				}
			}
		}

		if ( ! empty( $filters['search_user_email'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			$where_clauses[] = 'u.user_email LIKE %s';
			$values[]        = '%' . $wpdb->esc_like( $filters['search_user_email'] ) . '%';
		}

		if ( ! empty( $filters['search_guest_email'] ) ) {
			$where_clauses[] = 'r.guest_email LIKE %s';
			$values[]        = '%' . $wpdb->esc_like( $filters['search_guest_email'] ) . '%';
		}

		// Search by author (user display name or guest name).
		if ( ! empty( $filters['search_author'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->users} AS u ON r.user_id = u.ID";
			}
			$where_clauses[] = '(u.display_name LIKE %s OR u.user_email LIKE %s OR r.guest_name LIKE %s OR r.guest_email LIKE %s)';
			$search_term     = '%' . $wpdb->esc_like( $filters['search_author'] ) . '%';
			$values[]        = $search_term;
			$values[]        = $search_term;
			$values[]        = $search_term;
			$values[]        = $search_term;
		}

		if ( ! empty( $filters['search_product_name'] ) ) {
			if ( ! in_array( "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID", $join_clauses, true ) ) {
				$join_clauses[] = "LEFT JOIN {$wpdb->posts} AS p ON r.product_id = p.ID";
			}
			$where_clauses[] = "p.post_title LIKE %s AND p.post_type = 'product'";
			$values[]        = '%' . $wpdb->esc_like( $filters['search_product_name'] ) . '%';
		}

		// Search by product ID.
		if ( ! empty( $filters['search_product_id'] ) && is_numeric( $filters['search_product_id'] ) ) {
			$where_clauses[] = 'r.product_id = %d';
			$values[]        = (int) $filters['search_product_id'];
		}

		// Build the query.
		$sql = "SELECT COUNT(DISTINCT r.id) FROM {$this->table} AS r";

		if ( ! empty( $join_clauses ) ) {
			$sql .= ' ' . implode( ' ', array_unique( $join_clauses ) );
		}

		if ( ! empty( $where_clauses ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where_clauses );
		}

		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Query creator-submitted reviews for a specific user.
	 *
	 * @param int                 $user_id Creator user ID.
	 * @param array<string,mixed> $filters Optional filters.
	 * @return array<int, Review>
	 */
	public function query_creator_submissions( int $user_id, array $filters = array() ): array {
		global $wpdb;

		if ( $user_id < 1 ) {
			return array();
		}

		$where_clauses = array(
			'r.user_id = %d',
			"(rm_creator.meta_id IS NULL OR rm_creator.meta_value = 'influencer')",
		);
		$values        = array( $user_id );

		if ( ! empty( $filters['id'] ) ) {
			$where_clauses[] = 'r.id = %d';
			$values[]        = absint( $filters['id'] );
		}

		if ( isset( $filters['status'] ) && '' !== (string) $filters['status'] ) {
			$where_clauses[] = 'r.status = %s';
			$values[]        = sanitize_key( (string) $filters['status'] );
		}

		if ( isset( $filters['product_id'] ) && absint( $filters['product_id'] ) > 0 ) {
			$where_clauses[] = 'r.product_id = %d';
			$values[]        = absint( $filters['product_id'] );
		}

		$allowed_order_by = array( 'id', 'created_at', 'updated_at', 'status', 'product_id' );
		$order_by         = isset( $filters['order_by'] ) ? sanitize_key( (string) $filters['order_by'] ) : 'created_at';
		$order_by         = in_array( $order_by, $allowed_order_by, true ) ? $order_by : 'created_at';
		$order            = isset( $filters['order'] ) && 'ASC' === strtoupper( (string) $filters['order'] ) ? 'ASC' : 'DESC';
		$per_page         = max( 1, min( 100, isset( $filters['per_page'] ) ? absint( $filters['per_page'] ) : 20 ) );
		$page             = max( 1, isset( $filters['page'] ) ? absint( $filters['page'] ) : 1 );
		$offset           = ( $page - 1 ) * $per_page;

		$sql = "SELECT DISTINCT r.* FROM {$this->table} AS r LEFT JOIN {$wpdb->prefix}ugcify_review_meta AS rm_creator ON rm_creator.review_id = r.id AND rm_creator.meta_key = '_ugcify_contributor_type' WHERE " . implode( ' AND ', $where_clauses );
		$sql .= " ORDER BY r.{$order_by} {$order} LIMIT %d OFFSET %d";
		$values[] = $per_page;
		$values[] = $offset;

		$results = $wpdb->get_results(
			$wpdb->prepare( $sql, $values ),
			ARRAY_A
		);

		if ( ! is_array( $results ) || empty( $results ) ) {
			return array();
		}

		return array_map(
			static function ( array $row ) {
				return new Review( $row );
			},
			$results
		);
	}

	/**
	 * Count creator-submitted reviews for a specific user.
	 *
	 * @param int                 $user_id Creator user ID.
	 * @param array<string,mixed> $filters Optional filters.
	 * @return int
	 */
	public function count_creator_submissions( int $user_id, array $filters = array() ): int {
		global $wpdb;

		if ( $user_id < 1 ) {
			return 0;
		}

		$where_clauses = array(
			'r.user_id = %d',
			"(rm_creator.meta_id IS NULL OR rm_creator.meta_value = 'influencer')",
		);
		$values        = array( $user_id );

		if ( ! empty( $filters['id'] ) ) {
			$where_clauses[] = 'r.id = %d';
			$values[]        = absint( $filters['id'] );
		}

		if ( isset( $filters['status'] ) && '' !== (string) $filters['status'] ) {
			$where_clauses[] = 'r.status = %s';
			$values[]        = sanitize_key( (string) $filters['status'] );
		}

		if ( isset( $filters['product_id'] ) && absint( $filters['product_id'] ) > 0 ) {
			$where_clauses[] = 'r.product_id = %d';
			$values[]        = absint( $filters['product_id'] );
		}

		$sql = "SELECT COUNT(DISTINCT r.id) FROM {$this->table} AS r LEFT JOIN {$wpdb->prefix}ugcify_review_meta AS rm_creator ON rm_creator.review_id = r.id AND rm_creator.meta_key = '_ugcify_contributor_type' WHERE " . implode( ' AND ', $where_clauses );

		return (int) $wpdb->get_var( $wpdb->prepare( $sql, $values ) );
	}

	/**
	 * Get paginated results
	 *
	 * @param array $filters Query filters.
	 * @return array {
	 *    @type array $items Array of Review objects.
	 *    @type int   $total Total number of items.
	 *    @type int   $pages Total number of pages.
	 * }
	 */
	public function get_paginated( array $filters = array() ) {
		$per_page = isset( $filters['per_page'] ) ? (int) $filters['per_page'] : 10;
		$total    = $this->count( $filters );
		$pages    = ceil( $total / $per_page );
		$items    = $this->query( $filters );

		return array(
			'items' => $items,
			'total' => $total,
			'pages' => $pages,
		);
	}

	/**
	 * Normalize term slug filters from CSV string or array.
	 *
	 * @param mixed $raw Raw filter value.
	 * @return array
	 */
	private function normalize_term_slug_filter( $raw ) {
		if ( is_string( $raw ) ) {
			$raw = explode( ',', $raw );
		}

		if ( ! is_array( $raw ) ) {
			return array();
		}

		$normalized = array_map(
			function ( $value ) {
				return sanitize_title( trim( (string) $value ) );
			},
			$raw
		);

		return array_values( array_unique( array_filter( $normalized ) ) );
	}

	/**
	 * Normalize review type filters from CSV string or array.
	 *
	 * @param mixed $raw Raw type filter value.
	 * @return array<int,string>
	 */
	private function normalize_type_filter( $raw ) {
		if ( is_string( $raw ) ) {
			$raw = explode( ',', $raw );
		}

		if ( ! is_array( $raw ) ) {
			$raw = array( $raw );
		}

		$types = array_map(
			function ( $value ) {
				return sanitize_key( trim( (string) $value ) );
			},
			$raw
		);
		$types = array_values( array_unique( array_filter( $types ) ) );

		// Keep parity with query()/count(): image/video filters include mixed.
		if ( in_array( 'image', $types, true ) || in_array( 'video', $types, true ) ) {
			$types[] = 'mixed';
			$types   = array_values( array_unique( $types ) );
		}

		return $types;
	}

	/**
	 * Check if hotspot product tags table exists.
	 *
	 * @return bool
	 */
	private function has_product_tags_table() {
		if ( null !== $this->has_product_tags_table ) {
			return $this->has_product_tags_table;
		}

		global $wpdb;

		$table_name = $wpdb->prefix . 'ugcify_product_tags';
		$found      = $wpdb->get_var(
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
		);

		$this->has_product_tags_table = ( $found === $table_name );
		return $this->has_product_tags_table;
	}

	/**
	 * Get average rating for a product
	 *
	 * @param int    $product_id Product ID.
	 * @param string $status     Optional. Status to filter by. Default null (all status).
	 * @param array  $filters    Optional extra filters (tags/categories).
	 * @return float
	 */
	public function get_average_rating( $product_id, $status = null, array $filters = array() ) {
		global $wpdb;

		$join_parts  = array();
		$where_parts = array();
		$values      = array();
		if ( $product_id ) {
			if ( $this->has_product_tags_table() ) {
				$product_tags_table = $wpdb->prefix . 'ugcify_product_tags';
				$join_parts[]       = "LEFT JOIN {$product_tags_table} AS pt ON FLOOR(pt.media_id / 1000) = r.id AND pt.product_id = %d";
				$values[]           = $product_id;
				$where_parts[]      = '(r.product_id = %d OR pt.id IS NOT NULL)';
				$values[]           = $product_id;
			} else {
				$where_parts[] = 'r.product_id = %d';
				$values[]      = $product_id;
			}
		}

		if ( $status ) {
			$where_parts[] = 'r.status = %s';
			$values[]      = $status;
		}

		$tag_slugs = $this->normalize_term_slug_filter( isset( $filters['tags'] ) ? $filters['tags'] : array() );
		if ( ! empty( $tag_slugs ) ) {
			$join_parts[]  = "INNER JOIN {$wpdb->term_relationships} AS tr_tag ON tr_tag.object_id = r.product_id";
			$join_parts[]  = "INNER JOIN {$wpdb->term_taxonomy} AS tt_tag ON tt_tag.term_taxonomy_id = tr_tag.term_taxonomy_id AND tt_tag.taxonomy = 'product_tag'";
			$join_parts[]  = "INNER JOIN {$wpdb->terms} AS t_tag ON t_tag.term_id = tt_tag.term_id";
			$placeholders  = implode( ',', array_fill( 0, count( $tag_slugs ), '%s' ) );
			$where_parts[] = "t_tag.slug IN ({$placeholders})";
			foreach ( $tag_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		$category_slugs = $this->normalize_term_slug_filter( isset( $filters['categories'] ) ? $filters['categories'] : array() );
		if ( ! empty( $category_slugs ) ) {
			$join_parts[]  = "INNER JOIN {$wpdb->term_relationships} AS tr_cat ON tr_cat.object_id = r.product_id";
			$join_parts[]  = "INNER JOIN {$wpdb->term_taxonomy} AS tt_cat ON tt_cat.term_taxonomy_id = tr_cat.term_taxonomy_id AND tt_cat.taxonomy = 'product_cat'";
			$join_parts[]  = "INNER JOIN {$wpdb->terms} AS t_cat ON t_cat.term_id = tt_cat.term_id";
			$placeholders  = implode( ',', array_fill( 0, count( $category_slugs ), '%s' ) );
			$where_parts[] = "t_cat.slug IN ({$placeholders})";
			foreach ( $category_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		$type_filters = $this->normalize_type_filter( isset( $filters['type'] ) ? $filters['type'] : array() );
		if ( ! empty( $type_filters ) ) {
			$placeholders  = implode( ',', array_fill( 0, count( $type_filters ), '%s' ) );
			$where_parts[] = "r.type IN ({$placeholders})";
			foreach ( $type_filters as $type_value ) {
				$values[] = $type_value;
			}
		}

		$where_parts[] = 'rating IS NOT NULL';
		$where_parts[] = 'rating >= 1';

		$social_placeholders = implode( ',', array_fill( 0, count( self::UNRATED_SOCIAL_TYPES ), '%s' ) );
		$where_parts[]       = "r.type NOT IN ({$social_placeholders})";
		$values              = array_merge( $values, self::UNRATED_SOCIAL_TYPES );

		$where = ! empty( $where_parts ) ? 'WHERE ' . implode( ' AND ', $where_parts ) : '';
		$joins = ! empty( $join_parts ) ? ' ' . implode( ' ', array_unique( $join_parts ) ) : '';

		$sql = "SELECT AVG(filtered.rating) FROM (SELECT DISTINCT r.id, r.rating FROM {$this->table} AS r{$joins} {$where}) AS filtered";
		$avg = empty( $values ) ? $wpdb->get_var( $sql ) : $wpdb->get_var( $wpdb->prepare( $sql, $values ) );

		return $avg ? round( (float) $avg, 1 ) : 0.0;
	}

	/**
	 * Count rated reviews (excludes unrated social review types).
	 *
	 * @param int         $product_id Product ID.
	 * @param string|null $status Optional. Status to filter by. Default null (all status).
	 * @return int
	 */
	public function count_rated_reviews( $product_id = 0, $status = null ) {
		global $wpdb;

		$where_parts = array( 'r.rating IS NOT NULL', 'r.rating >= 1' );
		$values      = array();
		$join_parts  = array();

		if ( $product_id ) {
			if ( $this->has_product_tags_table() ) {
				$product_tags_table = $wpdb->prefix . 'ugcify_product_tags';
				$join_parts[]       = "LEFT JOIN {$product_tags_table} AS pt ON FLOOR(pt.media_id / 1000) = r.id AND pt.product_id = %d";
				$values[]           = (int) $product_id;
				$where_parts[]      = '(r.product_id = %d OR pt.id IS NOT NULL)';
				$values[]           = (int) $product_id;
			} else {
				$where_parts[] = 'r.product_id = %d';
				$values[]      = (int) $product_id;
			}
		}

		if ( $status ) {
			$where_parts[] = 'r.status = %s';
			$values[]      = $status;
		}

		$social_placeholders = implode( ',', array_fill( 0, count( self::UNRATED_SOCIAL_TYPES ), '%s' ) );
		$where_parts[]       = "r.type NOT IN ({$social_placeholders})";
		$values              = array_merge( $values, self::UNRATED_SOCIAL_TYPES );

		$join  = ! empty( $join_parts ) ? implode( ' ', $join_parts ) : '';
		$where = 'WHERE ' . implode( ' AND ', $where_parts );
		$sql   = "SELECT COUNT(DISTINCT r.id) FROM {$this->table} AS r {$join} {$where}";
		$sql   = $wpdb->prepare( $sql, $values );

		return (int) $wpdb->get_var( $sql );
	}

	/**
	 * Get rating distribution for a product
	 *
	 * @param int         $product_id Product ID.
	 * @param string|null $status     Optional. Status to filter by. Default null (all status).
	 * @param array       $filters    Optional extra filters (tags/categories).
	 * @return array Array with keys 1-5 and counts as values.
	 */
	public function get_rating_distribution( $product_id, $status = null, array $filters = array() ) {
		global $wpdb;

		$join_parts  = array();
		$where_parts = array();
		$values      = array();
		if ( $product_id ) {
			if ( $this->has_product_tags_table() ) {
				$product_tags_table = $wpdb->prefix . 'ugcify_product_tags';
				$join_parts[]       = "LEFT JOIN {$product_tags_table} AS pt ON FLOOR(pt.media_id / 1000) = r.id AND pt.product_id = %d";
				$values[]           = $product_id;
				$where_parts[]      = '(r.product_id = %d OR pt.id IS NOT NULL)';
				$values[]           = $product_id;
			} else {
				$where_parts[] = 'r.product_id = %d';
				$values[]      = $product_id;
			}
		}
		if ( $status ) {
			$where_parts[] = 'r.status = %s';
			$values[]      = $status;
		}

		$tag_slugs = $this->normalize_term_slug_filter( isset( $filters['tags'] ) ? $filters['tags'] : array() );
		if ( ! empty( $tag_slugs ) ) {
			$join_parts[]  = "INNER JOIN {$wpdb->term_relationships} AS tr_tag ON tr_tag.object_id = r.product_id";
			$join_parts[]  = "INNER JOIN {$wpdb->term_taxonomy} AS tt_tag ON tt_tag.term_taxonomy_id = tr_tag.term_taxonomy_id AND tt_tag.taxonomy = 'product_tag'";
			$join_parts[]  = "INNER JOIN {$wpdb->terms} AS t_tag ON t_tag.term_id = tt_tag.term_id";
			$placeholders  = implode( ',', array_fill( 0, count( $tag_slugs ), '%s' ) );
			$where_parts[] = "t_tag.slug IN ({$placeholders})";
			foreach ( $tag_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		$category_slugs = $this->normalize_term_slug_filter( isset( $filters['categories'] ) ? $filters['categories'] : array() );
		if ( ! empty( $category_slugs ) ) {
			$join_parts[]  = "INNER JOIN {$wpdb->term_relationships} AS tr_cat ON tr_cat.object_id = r.product_id";
			$join_parts[]  = "INNER JOIN {$wpdb->term_taxonomy} AS tt_cat ON tt_cat.term_taxonomy_id = tr_cat.term_taxonomy_id AND tt_cat.taxonomy = 'product_cat'";
			$join_parts[]  = "INNER JOIN {$wpdb->terms} AS t_cat ON t_cat.term_id = tt_cat.term_id";
			$placeholders  = implode( ',', array_fill( 0, count( $category_slugs ), '%s' ) );
			$where_parts[] = "t_cat.slug IN ({$placeholders})";
			foreach ( $category_slugs as $slug ) {
				$values[] = $slug;
			}
		}

		$type_filters = $this->normalize_type_filter( isset( $filters['type'] ) ? $filters['type'] : array() );
		if ( ! empty( $type_filters ) ) {
			$placeholders  = implode( ',', array_fill( 0, count( $type_filters ), '%s' ) );
			$where_parts[] = "r.type IN ({$placeholders})";
			foreach ( $type_filters as $type_value ) {
				$values[] = $type_value;
			}
		}

		$where_parts[] = 'rating IS NOT NULL';
		$where_parts[] = 'rating >= 1';

		$social_placeholders = implode( ',', array_fill( 0, count( self::UNRATED_SOCIAL_TYPES ), '%s' ) );
		$where_parts[]       = "r.type NOT IN ({$social_placeholders})";
		$values              = array_merge( $values, self::UNRATED_SOCIAL_TYPES );

		$where = ! empty( $where_parts ) ? 'WHERE ' . implode( ' AND ', $where_parts ) : '';
		$joins = ! empty( $join_parts ) ? ' ' . implode( ' ', array_unique( $join_parts ) ) : '';

		$sql = "SELECT r.rating, COUNT(DISTINCT r.id) as count FROM {$this->table} AS r{$joins} {$where} GROUP BY r.rating";

		$results = empty( $values )
			? $wpdb->get_results( $sql, OBJECT_K )
			: $wpdb->get_results( $wpdb->prepare( $sql, $values ), OBJECT_K );
		$distribution = array(
			1 => 0,
			2 => 0,
			3 => 0,
			4 => 0,
			5 => 0,
		);

		foreach ( $results as $rating => $row ) {
			$r = intval( $rating );
			if ( $r >= 1 && $r <= 5 ) {
				$distribution[ $r ] = intval( $row->count );
			}
		}

		return $distribution;
	}

	/**
	 * Create social share token meta row.
	 *
	 * @param int    $review_id Review ID.
	 * @param string $token Share token.
	 * @param string $channel Channel key.
	 * @param int    $author_id Share creator user ID.
	 * @param int    $product_id Product ID.
	 * @return bool
	 */
	public function create_share_token( $review_id, $token, $channel, $author_id, $product_id = 0 ) {
		global $wpdb;

		$review_id = absint( $review_id );
		$author_id = absint( $author_id );
		$product_id = absint( $product_id );
		$token     = sanitize_key( (string) $token );
		$channel   = sanitize_key( (string) $channel );

		if ( $review_id < 1 || '' === $token || '' === $channel ) {
			return false;
		}

		$meta_key   = '_ugcify_share_token_' . $token;
		$meta_table = $wpdb->prefix . 'ugcify_review_meta';
		$created_at = current_time( 'mysql', true );
		$expires_at = gmdate( 'Y-m-d H:i:s', strtotime( '+30 days', strtotime( $created_at ) ) );

		$meta_payload = array(
			'token'         => $token,
			'review_id'     => $review_id,
			'author_id'     => $author_id,
			'product_id'    => $product_id,
			'channel'       => $channel,
			'created_at'    => $created_at,
			'expires_at'    => $expires_at,
			'clicks'        => 0,
			'last_click_at' => '',
			'awarded'       => array(
				'initiate'    => false,
				'click'       => false,
				'add_to_cart' => false,
				'conversion'  => false,
			),
		);

		return false !== $wpdb->insert(
			$meta_table,
			array(
				'review_id'  => $review_id,
				'meta_key'   => $meta_key,
				'meta_value' => wp_json_encode( $meta_payload ),
			),
			array( '%d', '%s', '%s' )
		);
	}

	/**
	 * Find share token meta payload.
	 *
	 * @param string $token Share token.
	 * @return array|null
	 */
	public function find_by_share_token( $token ) {
		global $wpdb;

		$token = sanitize_key( (string) $token );
		if ( '' === $token ) {
			return null;
		}

		$meta_key   = '_ugcify_share_token_' . $token;
		$meta_table = $wpdb->prefix . 'ugcify_review_meta';
		$row        = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT meta_value FROM {$meta_table} WHERE meta_key = %s ORDER BY meta_id DESC LIMIT 1",
				$meta_key
			),
			ARRAY_A
		);

		if ( empty( $row['meta_value'] ) ) {
			return null;
		}

		$decoded = json_decode( (string) $row['meta_value'], true );
		return is_array( $decoded ) ? $decoded : null;
	}

	/**
	 * Increment share click counter.
	 *
	 * @param string $token Share token.
	 * @return bool
	 */
	public function increment_share_click( $token ) {
		$meta = $this->find_by_share_token( $token );
		if ( ! is_array( $meta ) ) {
			return false;
		}

		$meta['clicks']        = absint( $meta['clicks'] ?? 0 ) + 1;
		$meta['last_click_at'] = current_time( 'mysql', true );

		return $this->update_share_token_meta( $token, $meta );
	}

	/**
	 * Update share token payload.
	 *
	 * @param string $token Share token.
	 * @param array  $meta Payload.
	 * @return bool
	 */
	public function update_share_token_meta( $token, array $meta ) {
		global $wpdb;

		$token = sanitize_key( (string) $token );
		if ( '' === $token ) {
			return false;
		}

		$meta_key   = '_ugcify_share_token_' . $token;
		$meta_table = $wpdb->prefix . 'ugcify_review_meta';

		return false !== $wpdb->update(
			$meta_table,
			array(
				'meta_value' => wp_json_encode( $meta ),
			),
			array(
				'meta_key' => $meta_key,
			),
			array( '%s' ),
			array( '%s' )
		);
	}

	/**
	 * Delete expired share token rows.
	 *
	 * @return int
	 */
	public function delete_expired_share_tokens() {
		global $wpdb;

		$meta_table = $wpdb->prefix . 'ugcify_review_meta';
		$rows       = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT meta_id, meta_value FROM {$meta_table} WHERE meta_key LIKE %s",
				'_ugcify_share_token_%'
			),
			ARRAY_A
		);

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

		$deleted = 0;
		$now     = current_time( 'mysql', true );

		foreach ( $rows as $row ) {
			$meta = json_decode( (string) ( $row['meta_value'] ?? '' ), true );
			if ( ! is_array( $meta ) || empty( $meta['expires_at'] ) || ! is_string( $meta['expires_at'] ) ) {
				continue;
			}

			if ( strtotime( $meta['expires_at'] ) >= strtotime( $now ) ) {
				continue;
			}

			$result = $wpdb->delete(
				$meta_table,
				array( 'meta_id' => absint( $row['meta_id'] ?? 0 ) ),
				array( '%d' )
			);
			if ( false !== $result ) {
				++$deleted;
			}
		}

		return $deleted;
	}
}
