<?php
/**
 * Sales Analytics Rule Helper.
 *
 * @package PRECO
 */

namespace PRECO\Includes\Frontend\Engine\Filters\Query;

use PRECO\Includes\Frontend\UserEvents\UserEventsTable;

defined( 'ABSPATH' ) || exit;

/**
 * Helper for filtering products based on sales analytics and performance metrics.
 */
class SalesAnalyticsRuleHelper {

	/**
	 * Cached result of the wc_order_stats table-existence check.
	 *
	 * @var bool|null null = not yet checked.
	 */
	private static $order_stats_available = null;

	/**
	 * Check whether the wc_order_stats analytics table is present.
	 *
	 * Result is cached per-request so SHOW TABLES is only run once.
	 *
	 * @return bool
	 */
	private function is_order_stats_available() {
		if ( null !== self::$order_stats_available ) {
			return self::$order_stats_available;
		}
		global $wpdb;
		$table                       = $wpdb->prefix . 'wc_order_stats';
		self::$order_stats_available = (bool) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) )
		);
		return self::$order_stats_available;
	}

	/**
	 * Add best-selling products rule.
	 *
	 * @param array|null &$include_ids Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation Relation (in/not_in).
	 * @param array      $value Rule value.
	 * @return void
	 */
	public function add_best_selling_rule( &$include_ids, &$enforce_empty, $relation, $value ) {
		// Value may be a plain period string or an array with a 'period' key.
		$period = is_array( $value ) ? ( isset( $value['period'] ) ? sanitize_text_field( $value['period'] ) : 'last_30d' ) : sanitize_text_field( (string) $value );
		if ( ! $period ) {
			$period = 'last_30d';
		}

		$product_ids = $this->get_best_selling_products( $period );

		if ( 'not_in' === $relation ) {
			if ( null === $include_ids ) {
				return;
			}
			$include_ids = array_diff( $include_ids, $product_ids );
		} else {
			$include_ids = ( null === $include_ids ) ? $product_ids : array_intersect( $include_ids, $product_ids );
		}

		if ( empty( $include_ids ) && 'in' === $relation ) {
			$enforce_empty = true;
		}
	}

	/**
	 * Add high-revenue products rule.
	 *
	 * @param array|null &$include_ids Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation Relation (greater/less).
	 * @param array      $value Rule value.
	 * @return void
	 */
	public function add_high_revenue_rule( &$include_ids, &$enforce_empty, $relation, $value ) {
		// Value may be a plain number or an array with an 'amount' key.
		$threshold = is_array( $value ) ? floatval( $value['amount'] ?? 0 ) : floatval( $value );

		if ( $threshold <= 0 ) {
			return;
		}

		// Relation: 'greater' means revenue >= threshold; 'less' means revenue < threshold.
		$compare = ( 'less' === $relation ) ? 'less' : 'greater';

		$product_ids = $this->get_revenue_filtered_products( $threshold, $compare );

		$include_ids = ( null === $include_ids ) ? $product_ids : array_intersect( $include_ids, $product_ids );

		if ( empty( $include_ids ) ) {
			$enforce_empty = true;
		}
	}

	/**
	 * Add low-selling products rule.
	 *
	 * @param array|null &$include_ids Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation Relation (in/not_in).
	 * @param array      $value Rule value.
	 * @return void
	 */
	public function add_low_selling_rule( &$include_ids, &$enforce_empty, $relation, $value ) {
		$period = is_array( $value ) ? ( isset( $value['period'] ) ? sanitize_text_field( $value['period'] ) : 'last_30d' ) : sanitize_text_field( (string) $value );
		if ( ! $period ) {
			$period = 'last_30d';
		}

		$product_ids = $this->get_low_selling_products( $period );

		if ( 'not_in' === $relation ) {
			if ( null === $include_ids ) {
				return;
			}
			$include_ids = array_diff( $include_ids, $product_ids );
		} else {
			$include_ids = ( null === $include_ids ) ? $product_ids : array_intersect( $include_ids, $product_ids );
		}

		if ( empty( $include_ids ) && 'in' === $relation ) {
			$enforce_empty = true;
		}
	}

	/**
	 * Add high conversion products rule.
	 *
	 * @param array|null &$include_ids Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation Relation (in/not_in).
	 * @param array      $value Rule value.
	 * @return void
	 */
	public function add_high_conversion_rule( &$include_ids, &$enforce_empty, $relation, $value ) {
		$period = is_array( $value ) ? ( isset( $value['period'] ) ? sanitize_text_field( $value['period'] ) : 'last_30d' ) : sanitize_text_field( (string) $value );
		if ( ! $period ) {
			$period = 'last_30d';
		}

		$product_ids = $this->get_high_conversion_products( $period );

		if ( 'not_in' === $relation ) {
			if ( null === $include_ids ) {
				return;
			}
			$include_ids = array_diff( $include_ids, $product_ids );
		} else {
			$include_ids = ( null === $include_ids ) ? $product_ids : array_intersect( $include_ids, $product_ids );
		}

		if ( empty( $include_ids ) && 'in' === $relation ) {
			$enforce_empty = true;
		}
	}

	/**
	 * Add high click-through products rule.
	 *
	 * @param array|null &$include_ids Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation Relation (in/not_in).
	 * @param array      $value Rule value.
	 * @return void
	 */
	public function add_high_click_through_rule( &$include_ids, &$enforce_empty, $relation, $value ) {
		$period = is_array( $value ) ? ( isset( $value['period'] ) ? sanitize_text_field( $value['period'] ) : 'last_30d' ) : sanitize_text_field( (string) $value );
		if ( ! $period ) {
			$period = 'last_30d';
		}

		$product_ids = $this->get_high_click_through_products( $period );

		if ( 'not_in' === $relation ) {
			if ( null === $include_ids ) {
				return;
			}
			$include_ids = array_diff( $include_ids, $product_ids );
		} else {
			$include_ids = ( null === $include_ids ) ? $product_ids : array_intersect( $include_ids, $product_ids );
		}

		if ( empty( $include_ids ) && 'in' === $relation ) {
			$enforce_empty = true;
		}
	}

	/**
	 * Get best-selling products by sales volume.
	 *
	 * @param string $period Time period.
	 * @return array Product IDs.
	 */
	private function get_best_selling_products( $period ) {
		if ( ! $this->is_order_stats_available() ) {
			return array();
		}
		global $wpdb;

		$period_map = $this->get_period_map();
		$days       = isset( $period_map[ $period ] ) ? $period_map[ $period ] : 30;
		$date_from  = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT opl.product_id, SUM( opl.product_qty ) AS total_qty
				FROM {$wpdb->prefix}wc_order_product_lookup AS opl
				INNER JOIN {$wpdb->prefix}wc_order_stats AS os
					ON opl.order_id = os.order_id
					AND os.status IN ('wc-completed','wc-processing')
					AND os.date_created_gmt >= %s
				GROUP BY opl.product_id
				ORDER BY total_qty DESC",
				$date_from
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

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

		$sum      = array_sum( array_column( $results, 'total_qty' ) );
		$average  = (int) floor( $sum / count( $results ) );
		$filtered = array_filter(
			$results,
			function ( $row ) use ( $average ) {
				return (int) $row['total_qty'] >= $average;
			}
		);

		return array_map( 'intval', array_column( $filtered, 'product_id' ) );
	}

	/**
	 * Get products filtered by revenue threshold.
	 *
	 * @param float  $threshold Revenue threshold.
	 * @param string $relation Comparison relation.
	 * @return array Product IDs.
	 */
	private function get_revenue_filtered_products( $threshold, $relation ) {
		if ( ! $this->is_order_stats_available() ) {
			return array();
		}
		global $wpdb;

		// Use two explicit code paths to avoid interpolating a comparison operator into SQL.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( 'less' === $relation ) {
			$results = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT opl.product_id
					FROM {$wpdb->prefix}wc_order_product_lookup AS opl
					INNER JOIN {$wpdb->prefix}wc_order_stats AS os
						ON opl.order_id = os.order_id
						AND os.status IN ('wc-completed','wc-processing')
					GROUP BY opl.product_id
					HAVING SUM( opl.product_net_revenue ) < %f",
					$threshold
				)
			);
		} else {
			$results = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT opl.product_id
					FROM {$wpdb->prefix}wc_order_product_lookup AS opl
					INNER JOIN {$wpdb->prefix}wc_order_stats AS os
						ON opl.order_id = os.order_id
						AND os.status IN ('wc-completed','wc-processing')
					GROUP BY opl.product_id
					HAVING SUM( opl.product_net_revenue ) >= %f",
					$threshold
				)
			);
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return $results ? array_map( 'intval', $results ) : array();
	}

	/**
	 * Get low-selling products.
	 *
	 * @param string $period Time period.
	 * @return array Product IDs.
	 */
	private function get_low_selling_products( $period ) {
		if ( ! $this->is_order_stats_available() ) {
			return array();
		}
		global $wpdb;

		$period_map = $this->get_period_map();
		$days       = isset( $period_map[ $period ] ) ? $period_map[ $period ] : 30;
		$date_from  = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT opl.product_id, SUM( opl.product_qty ) AS total_qty
				FROM {$wpdb->prefix}wc_order_product_lookup AS opl
				INNER JOIN {$wpdb->prefix}wc_order_stats AS os
					ON opl.order_id = os.order_id
					AND os.status IN ('wc-completed','wc-processing')
					AND os.date_created_gmt >= %s
				GROUP BY opl.product_id
				ORDER BY total_qty ASC",
				$date_from
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

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

		$sum      = array_sum( array_column( $results, 'total_qty' ) );
		$average  = (int) floor( $sum / count( $results ) );
		$filtered = array_filter(
			$results,
			function ( $row ) use ( $average ) {
				return (int) $row['total_qty'] < $average;
			}
		);

		return array_map( 'intval', array_column( $filtered, 'product_id' ) );
	}

	/**
	 * Get high conversion products (views to sales ratio).
	 *
	 * @param string $period Time period.
	 * @return array Product IDs.
	 */
	private function get_high_conversion_products( $period ) {
		if ( ! $this->is_order_stats_available() ) {
			return array();
		}
		global $wpdb;

		$period_map = $this->get_period_map();
		$days       = isset( $period_map[ $period ] ) ? $period_map[ $period ] : 30;
		$date_from  = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		$events_table = esc_sql( UserEventsTable::get_table_name() );

		// Get view counts (all-time, no date filter).
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$view_counts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, SUM(count) as view_count
				FROM {$events_table}
				WHERE event_type = %d
				GROUP BY product_id",
				UserEventsTable::EVENT_VIEW
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( empty( $view_counts ) ) {
			return array();
		}

		// Get sales counts.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sales_counts = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT opl.product_id, SUM( opl.product_qty ) AS sales_count
				FROM {$wpdb->prefix}wc_order_product_lookup AS opl
				INNER JOIN {$wpdb->prefix}wc_order_stats AS os
					ON opl.order_id = os.order_id
					AND os.status IN ('wc-completed','wc-processing')
					AND os.date_created_gmt >= %s
				GROUP BY opl.product_id",
				$date_from
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( empty( $sales_counts ) ) {
			return array();
		}

		// Calculate conversion rates.
		$conversion_rates = array();
		$sales_by_id      = wp_list_pluck( $sales_counts, 'sales_count', 'product_id' );

		foreach ( $view_counts as $view ) {
			$product_id  = intval( $view['product_id'] );
			$view_count  = intval( $view['view_count'] );
			$sales_count = isset( $sales_by_id[ $product_id ] ) ? intval( $sales_by_id[ $product_id ] ) : 0;

			if ( $view_count > 0 && $sales_count > 0 ) {
				$conversion_rates[ $product_id ] = ( $sales_count / $view_count ) * 100;
			}
		}

		if ( empty( $conversion_rates ) ) {
			return array();
		}

		$average  = floor( array_sum( $conversion_rates ) / count( $conversion_rates ) );
		$filtered = array_filter(
			$conversion_rates,
			function ( $rate ) use ( $average ) {
				return $rate >= $average;
			}
		);

		arsort( $filtered );

		return array_map( 'intval', array_keys( $filtered ) );
	}

	/**
	 * Get high click-through products (views).
	 *
	 * @param string $period Time period.
	 * @return array Product IDs.
	 */
	private function get_high_click_through_products( $period ) {
		global $wpdb;

		$period_map = $this->get_period_map();
		$days       = isset( $period_map[ $period ] ) ? $period_map[ $period ] : 30;
		$date_from  = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		$events_table = esc_sql( UserEventsTable::get_table_name() );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, SUM(count) as total_views
				FROM {$events_table}
				WHERE event_type = %d AND updated_at >= %s
				GROUP BY product_id
				ORDER BY total_views DESC",
				UserEventsTable::EVENT_VIEW,
				$date_from
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

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

		$sum      = array_sum( array_column( $results, 'total_views' ) );
		$average  = (int) floor( $sum / count( $results ) );
		$filtered = array_filter(
			$results,
			function ( $row ) use ( $average ) {
				return (int) $row['total_views'] >= $average;
			}
		);

		return array_map( 'intval', array_column( $filtered, 'product_id' ) );
	}

	/**
	 * Get period map.
	 *
	 * @return array Period mapping.
	 */
	private function get_period_map() {
		return array(
			'last_24h' => 1,
			'last_7d'  => 7,
			'last_30d' => 30,
			'last_60d' => 60,
		);
	}
}
