<?php
/**
 * Bought Together Rule Helper.
 *
 * @package PRECO
 */

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

defined( 'ABSPATH' ) || exit;

/**
 * Helper for filtering products based on co-purchase order data across all customers.
 */
class BoughtTogetherRuleHelper {

	/**
	 * Add bought together rule.
	 *
	 * Finds products that were frequently ordered in the same transaction as the current
	 * product (product page) or any product in the current cart (cart page).
	 *
	 * @param array|null &$include_ids   Include IDs array.
	 * @param bool       &$enforce_empty Enforce empty flag.
	 * @param string     $relation       Relation (in/not_in).
	 * @param mixed      $value          Rule value (period string or array with 'period' key).
	 * @param int        $product_id     Current product ID (0 when not on a product page).
	 * @return void
	 */
	public function add_bought_together_rule( &$include_ids, &$enforce_empty, $relation, $value, $product_id ) {
		$target_ids = $this->resolve_target_product_ids( $product_id );

		if ( empty( $target_ids ) ) {
			if ( 'in' === $relation ) {
				$enforce_empty = true;
			}
			return;
		}

		$raw_period  = is_array( $value ) ? ( $value['period'] ?? 'last_30d' ) : (string) $value;
		$period      = $this->parse_period( sanitize_text_field( $raw_period ) );
		$product_ids = $this->get_bought_together_products( $target_ids, $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;
		}
	}

	/**
	 * Resolve the source product IDs for the co-purchase lookup.
	 *
	 * On a product page the context product ID is used. On a cart page the IDs of
	 * all products currently in the WooCommerce cart are used.
	 *
	 * @param int $product_id Context product ID (0 when not on a single product page).
	 * @return array
	 */
	private function resolve_target_product_ids( $product_id ) {
		if ( $product_id > 0 ) {
			return array( (int) $product_id );
		}

		// Fallback: try to resolve the current product from the global query (product page).
		if ( function_exists( 'is_product' ) && \is_product() ) {
			$queried_id = absint( \get_queried_object_id() );
			if ( $queried_id > 0 ) {
				return array( $queried_id );
			}
		}

		if ( function_exists( 'WC' ) && WC()->cart ) {
			$ids = array();
			foreach ( WC()->cart->get_cart() as $item ) {
				$pid = absint( $item['product_id'] ?? 0 );
				if ( $pid ) {
					$ids[] = $pid;
				}
			}
			return array_unique( $ids );
		}

		return array();
	}

	/**
	 * Query products that were most frequently ordered alongside the given source products.
	 *
	 * Uses the WooCommerce HPOS/legacy analytics lookup tables.
	 *
	 * @param int[]  $target_ids Source product IDs to find co-purchases for.
	 * @param string $period     Parsed period string (e.g. '30 days') or 'alltime'.
	 * @return array Product IDs ordered by co-purchase frequency, descending.
	 */
	private function get_bought_together_products( array $target_ids, $period ) {
		global $wpdb;

		// Guard: wc_order_stats must exist (Analytics may be disabled or table not yet created).
		$stats_table_name = $wpdb->prefix . 'wc_order_stats';
		$stats_exists     = (bool) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $stats_table_name ) )
		);
		if ( ! $stats_exists ) {
			return array();
		}

		$count        = count( $target_ids );
		$placeholders = implode( ', ', array_fill( 0, $count, '%d' ) );
		$opl_table    = esc_sql( $wpdb->prefix . 'wc_order_product_lookup' );
		$stats_table  = esc_sql( $stats_table_name );

		// Subquery first finds the relevant order IDs, then collects co-purchased products.
		// This avoids a self-join that can produce duplicate rows and skewed counts.
		// Two code paths to avoid interpolating a pre-prepared SQL fragment.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		if ( 'alltime' !== $period ) {
			$date_from = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $period ) );
			$results   = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT opl2.product_id, COUNT(DISTINCT opl2.order_id) AS co_count
					FROM {$opl_table} AS opl2
					INNER JOIN {$stats_table} AS os
						ON opl2.order_id = os.order_id
						AND os.status IN ('wc-completed','wc-processing')
						AND os.date_created_gmt >= %s
					WHERE opl2.order_id IN (
						SELECT DISTINCT opl1.order_id
						FROM {$opl_table} AS opl1
						WHERE opl1.product_id IN ( {$placeholders} )
					)
					AND opl2.product_id NOT IN ( {$placeholders} )
					GROUP BY opl2.product_id
					ORDER BY co_count DESC",
					array_merge( array( $date_from ), $target_ids, $target_ids )
				),
				ARRAY_A
			);
		} else {
			$results = $wpdb->get_results(
				$wpdb->prepare(
					"SELECT opl2.product_id, COUNT(DISTINCT opl2.order_id) AS co_count
					FROM {$opl_table} AS opl2
					INNER JOIN {$stats_table} AS os
						ON opl2.order_id = os.order_id
						AND os.status IN ('wc-completed','wc-processing')
					WHERE opl2.order_id IN (
						SELECT DISTINCT opl1.order_id
						FROM {$opl_table} AS opl1
						WHERE opl1.product_id IN ( {$placeholders} )
					)
					AND opl2.product_id NOT IN ( {$placeholders} )
					GROUP BY opl2.product_id
					ORDER BY co_count DESC",
					array_merge( $target_ids, $target_ids )
				),
				ARRAY_A
			);
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare

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

	/**
	 * Parse a period slug to a strtotime-compatible interval string.
	 *
	 * @param string $period Period slug (e.g. 'last_7d').
	 * @return string Interval string or 'alltime'.
	 */
	private function parse_period( $period ) {
		$map = array(
			'last_7d'  => '7 days',
			'last_14d' => '14 days',
			'last_30d' => '30 days',
			'last_60d' => '60 days',
		);
		return isset( $map[ $period ] ) ? $map[ $period ] : '30 days';
	}
}
