<?php
/**
 * WooCommerce order lookup repository.
 *
 * @package PRECO
 */

namespace PRECO\Includes\Frontend\Engine\Sorting\Preferred;

defined( 'ABSPATH' ) || exit;

/**
 * WooCommerce order lookup repository.
 */
class WcOrderLookupRepository {
	/**
	 * Check if a DB table exists.
	 *
	 * @param string $table Fully qualified table name.
	 * @return bool
	 */
	public function table_exists( $table ) {
		global $wpdb;
		$table = (string) $table;
		if ( ! $table ) {
			return false;
		}
		$like  = $wpdb->esc_like( $table );
		$found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $like ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (string) $found === (string) $table;
	}

	/**
	 * Get co-purchased product IDs from the wc_order_product_lookup table.
	 *
	 * @param int $product_id Product ID.
	 * @param int $limit Limit.
	 * @param int $since Only consider order lines created on/after this unix timestamp. 0 = all time.
	 * @param int $order_limit Safety cap on how many matching orders to scan.
	 * @return int[]
	 */
	public function get_co_purchased_product_ids( $product_id, $limit, $since = 0, $order_limit = 500 ) {
		$product_id  = absint( $product_id );
		$limit       = (int) $limit;
		$since       = (int) $since;
		$order_limit = (int) $order_limit;
		if ( $product_id <= 0 || $limit <= 0 ) {
			return array();
		}

		global $wpdb;
		$table = $wpdb->prefix . 'wc_order_product_lookup';
		if ( ! $this->table_exists( $table ) ) {
			return array();
		}

		$order_limit  = max( 10, min( 500, $order_limit ) );
		$orders_table = $wpdb->prefix . 'wc_orders';
		$table_sql    = esc_sql( $table );
		$orders_sql   = esc_sql( $orders_table );

		// Only count orders with a valid payment status — matching WooCommerce Analytics behaviour.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( $since > 0 ) {
			$order_ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT opl.order_id FROM {$table_sql} opl
					INNER JOIN {$orders_sql} o ON o.id = opl.order_id
					WHERE opl.product_id = %d
					AND opl.date_created >= %s
					AND o.status IN ('wc-completed','wc-processing','wc-on-hold')
					ORDER BY opl.order_id DESC LIMIT %d",
					$product_id,
					\gmdate( 'Y-m-d H:i:s', $since ),
					$order_limit
				)
			);
		} else {
			$order_ids = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT opl.order_id FROM {$table_sql} opl
					INNER JOIN {$orders_sql} o ON o.id = opl.order_id
					WHERE opl.product_id = %d
					AND o.status IN ('wc-completed','wc-processing','wc-on-hold')
					ORDER BY opl.order_id DESC LIMIT %d",
					$product_id,
					$order_limit
				)
			);
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		if ( empty( $order_ids ) || ! is_array( $order_ids ) ) {
			return array();
		}
		$order_ids = array_values( array_unique( array_filter( array_map( 'absint', $order_ids ) ) ) );
		if ( empty( $order_ids ) ) {
			return array();
		}

		$in       = implode( ',', array_fill( 0, count( $order_ids ), '%d' ) );
		$params   = $order_ids;
		$params[] = $product_id;
		$params[] = max( 50, (int) $limit );

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT product_id, SUM(product_qty) AS qty
				 FROM {$table_sql}
				 WHERE order_id IN ({$in})
				   AND product_id != %d
				 GROUP BY product_id
				 ORDER BY qty DESC
				 LIMIT %d",
				$params
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare,WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		$ids = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( isset( $row['product_id'] ) ) {
					$ids[] = absint( $row['product_id'] );
				}
			}
		}
		return array_values( array_unique( array_filter( $ids ) ) );
	}

	/**
	 * Get top-selling products all-time.
	 *
	 * @param int $limit Limit.
	 * @return int[]
	 */
	public function get_top_selling_product_ids_all_time( $limit ) {
		$limit = (int) $limit;
		if ( $limit <= 0 ) {
			return array();
		}

		global $wpdb;
		$table = $wpdb->prefix . 'wc_order_product_lookup';
		if ( ! $this->table_exists( $table ) ) {
			return array();
		}

		// Join wc_orders to exclude invalid statuses (cancelled, refunded, failed, pending),
		// matching WooCommerce Analytics "Top Products" behaviour.
		$orders_table = $wpdb->prefix . 'wc_orders';
		$table_sql    = esc_sql( $table );
		$orders_sql   = esc_sql( $orders_table );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT opl.product_id, SUM(opl.product_qty) AS qty
				 FROM {$table_sql} opl
				 INNER JOIN {$orders_sql} o ON o.id = opl.order_id
				 WHERE o.status IN ('wc-completed','wc-processing','wc-on-hold')
				 GROUP BY opl.product_id
				 ORDER BY qty DESC
				 LIMIT %d",
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( isset( $row['product_id'] ) ) {
					$ids[] = absint( $row['product_id'] );
				}
			}
		}
		return array_values( array_unique( array_filter( $ids ) ) );
	}

	/**
	 * Get top-selling products since a timestamp.
	 *
	 * @param int $since Unix timestamp.
	 * @param int $limit Limit.
	 * @return int[]
	 */
	public function get_top_selling_product_ids_since( $since, $limit ) {
		$since = (int) $since;
		$limit = (int) $limit;
		if ( $since <= 0 || $limit <= 0 ) {
			return array();
		}

		global $wpdb;
		$table = $wpdb->prefix . 'wc_order_product_lookup';
		if ( ! $this->table_exists( $table ) ) {
			return array();
		}
		$after        = \gmdate( 'Y-m-d H:i:s', $since );
		$orders_table = $wpdb->prefix . 'wc_orders';
		$table_sql    = esc_sql( $table );
		$orders_sql   = esc_sql( $orders_table );
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT opl.product_id, SUM(opl.product_qty) AS qty
				 FROM {$table_sql} opl
				 INNER JOIN {$orders_sql} o ON o.id = opl.order_id
				 WHERE opl.date_created >= %s
				   AND o.status IN ('wc-completed','wc-processing','wc-on-hold')
				 GROUP BY opl.product_id
				 ORDER BY qty DESC
				 LIMIT %d",
				$after,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$ids = array();
		if ( is_array( $rows ) ) {
			foreach ( $rows as $row ) {
				if ( isset( $row['product_id'] ) ) {
					$ids[] = absint( $row['product_id'] );
				}
			}
		}
		return array_values( array_unique( array_filter( $ids ) ) );
	}
}
