<?php

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Metrics queries read fixed WooCommerce order tables with prepared value placeholders.
class AVIN_WooCommerce_Metrics {
	public function sales_for_products( $product_ids, $days = 30 ) {
		global $wpdb;

		$product_ids = array_values( array_unique( array_filter( array_map( 'absint', (array) $product_ids ) ) ) );
		$empty       = array();
		foreach ( $product_ids as $product_id ) {
			$empty[ $product_id ] = array(
				'units'   => 0,
				'revenue' => 0.0,
			);
		}

		if ( empty( $product_ids ) || ! class_exists( 'WooCommerce' ) ) {
			return $empty;
		}

		$days         = max( 1, absint( $days ) );
		$since        = gmdate( 'Y-m-d H:i:s', strtotime( current_time( 'mysql' ) . ' -' . ( $days - 1 ) . ' days' ) );
		$placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );
		$params       = array_merge( array( '_product_id', '_qty', '_line_total' ), $product_ids, array( $since ) );

		if ( $this->table_exists( $wpdb->prefix . 'wc_orders' ) ) {
			$sql = "SELECT product_meta.meta_value AS product_id,
					SUM(CAST(qty_meta.meta_value AS DECIMAL(18,4))) AS units,
					SUM(CAST(total_meta.meta_value AS DECIMAL(18,4))) AS revenue
				FROM {$wpdb->prefix}woocommerce_order_items items
				INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta product_meta ON product_meta.order_item_id = items.order_item_id AND product_meta.meta_key = %s
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta qty_meta ON qty_meta.order_item_id = items.order_item_id AND qty_meta.meta_key = %s
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta total_meta ON total_meta.order_item_id = items.order_item_id AND total_meta.meta_key = %s
				INNER JOIN {$wpdb->prefix}wc_orders orders ON orders.id = items.order_id
				WHERE items.order_item_type = 'line_item'
					AND product_meta.meta_value IN ({$placeholders})
					AND orders.status IN ('wc-processing', 'wc-completed')
					AND orders.date_created_gmt >= %s
				GROUP BY product_meta.meta_value";
		} else {
			$sql = "SELECT product_meta.meta_value AS product_id,
					SUM(CAST(qty_meta.meta_value AS DECIMAL(18,4))) AS units,
					SUM(CAST(total_meta.meta_value AS DECIMAL(18,4))) AS revenue
				FROM {$wpdb->prefix}woocommerce_order_items items
				INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta product_meta ON product_meta.order_item_id = items.order_item_id AND product_meta.meta_key = %s
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta qty_meta ON qty_meta.order_item_id = items.order_item_id AND qty_meta.meta_key = %s
				LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta total_meta ON total_meta.order_item_id = items.order_item_id AND total_meta.meta_key = %s
				INNER JOIN {$wpdb->posts} orders ON orders.ID = items.order_id
				WHERE items.order_item_type = 'line_item'
					AND product_meta.meta_value IN ({$placeholders})
					AND orders.post_type = 'shop_order'
					AND orders.post_status IN ('wc-processing', 'wc-completed')
					AND orders.post_date_gmt >= %s
				GROUP BY product_meta.meta_value";
		}

		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		foreach ( $rows as $row ) {
			$product_id = absint( $row['product_id'] );
			if ( isset( $empty[ $product_id ] ) ) {
				$empty[ $product_id ] = array(
					'units'   => (int) $row['units'],
					'revenue' => (float) $row['revenue'],
				);
			}
		}

		return apply_filters( 'avin_sales_for_products', $empty, $product_ids, $days );
	}

	public function table_exists( $table ) {
		global $wpdb;

		return $table === $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
	}
}
