<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class ANFOWO_Stock_Query {

	/**
	 * Get current stock report.
	 *
	 * @param array $dates Date range filters.
	 * @return array
	 */
	public static function get_report( $dates = array() ) {
		global $wpdb;

		if ( empty( $dates ) ) {
			if ( class_exists( 'ANFOWO_Date_Resolver' ) ) {
				$dates = ANFOWO_Date_Resolver::resolve( 'last_30_days' );
			} else {
				$dates = array( 'start' => gmdate( 'Y-m-d 00:00:00', strtotime( '-30 days' ) ), 'end' => gmdate( 'Y-m-d 23:59:59' ) );
			}
		}

		$global_threshold = (int) get_option( 'woocommerce_notify_low_stock_amount', 2 );
		$paid_statuses    = ANFOWO_Database::get_paid_statuses_sql();

		// 1. Fetch Sold Quantities
		$sold_during = array();
		$sold_after  = array();

		// Sold during period
		$sold_during_query = $wpdb->prepare(
			"SELECT COALESCE(NULLIF(opl.variation_id, 0), opl.product_id) as id, SUM(opl.product_qty) as qty_sold
			 FROM {$wpdb->prefix}wc_order_product_lookup opl
			 JOIN {$wpdb->prefix}wc_order_stats os ON os.order_id = opl.order_id
			 WHERE os.status IN ($paid_statuses) AND os.date_created >= %s AND os.date_created <= %s
			 GROUP BY id",
			$dates['start'],
			$dates['end']
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sold_during_results = $wpdb->get_results( $sold_during_query, ARRAY_A );
		foreach ( $sold_during_results as $r ) {
			$sold_during[ (int) $r['id'] ] = (int) $r['qty_sold'];
		}

		// Sold after period (since end of period to now)
		$sold_after_results = array();
		if ( strtotime( $dates['end'] ) < time() ) {
			$sold_after_query = $wpdb->prepare(
				"SELECT COALESCE(NULLIF(opl.variation_id, 0), opl.product_id) as id, SUM(opl.product_qty) as qty_sold
				 FROM {$wpdb->prefix}wc_order_product_lookup opl
				 JOIN {$wpdb->prefix}wc_order_stats os ON os.order_id = opl.order_id
				 WHERE os.status IN ($paid_statuses) AND os.date_created > %s
				 GROUP BY id",
				$dates['end']
			);
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$sold_after_results = $wpdb->get_results( $sold_after_query, ARRAY_A );
		}
		foreach ( $sold_after_results as $r ) {
			$sold_after[ (int) $r['id'] ] = (int) $r['qty_sold'];
		}

		// 2. Fetch Refunded Quantities
		$ref_during = array();
		$ref_after  = array();

		// Refunded during period
		$ref_during_query = $wpdb->prepare(
			"SELECT COALESCE(NULLIF(itemmeta_variation.meta_value, '0'), itemmeta_product.meta_value) as id, SUM(ABS(CAST(itemmeta.meta_value AS SIGNED))) as qty_refunded
			 FROM {$wpdb->prefix}woocommerce_order_items items
			 JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta ON items.order_item_id = itemmeta.order_item_id AND itemmeta.meta_key = '_qty'
			 JOIN {$wpdb->posts} p_ref ON p_ref.ID = items.order_id AND p_ref.post_type = 'shop_order_refund'
			 JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_product ON items.order_item_id = itemmeta_product.order_item_id AND itemmeta_product.meta_key = '_product_id'
			 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_variation ON items.order_item_id = itemmeta_variation.order_item_id AND itemmeta_variation.meta_key = '_variation_id'
			 WHERE p_ref.post_date >= %s AND p_ref.post_date <= %s
			 GROUP BY id",
			$dates['start'],
			$dates['end']
		);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$ref_during_results = $wpdb->get_results( $ref_during_query, ARRAY_A );
		foreach ( $ref_during_results as $r ) {
			$ref_during[ (int) $r['id'] ] = (int) $r['qty_refunded'];
		}

		// Refunded after period
		$ref_after_results = array();
		if ( strtotime( $dates['end'] ) < time() ) {
			$ref_after_query = $wpdb->prepare(
				"SELECT COALESCE(NULLIF(itemmeta_variation.meta_value, '0'), itemmeta_product.meta_value) as id, SUM(ABS(CAST(itemmeta.meta_value AS SIGNED))) as qty_refunded
				 FROM {$wpdb->prefix}woocommerce_order_items items
				 JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta ON items.order_item_id = itemmeta.order_item_id AND itemmeta.meta_key = '_qty'
				 JOIN {$wpdb->posts} p_ref ON p_ref.ID = items.order_id AND p_ref.post_type = 'shop_order_refund'
				 JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_product ON items.order_item_id = itemmeta_product.order_item_id AND itemmeta_product.meta_key = '_product_id'
				 LEFT JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_variation ON items.order_item_id = itemmeta_variation.order_item_id AND itemmeta_variation.meta_key = '_variation_id'
				 WHERE p_ref.post_date > %s
				 GROUP BY id",
				$dates['end']
			);
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$ref_after_results = $wpdb->get_results( $ref_after_query, ARRAY_A );
		}
		foreach ( $ref_after_results as $r ) {
			$ref_after[ (int) $r['id'] ] = (int) $r['qty_refunded'];
		}

		// Fetch ALL published products/variations in one query (no LIMIT).
		$query = "SELECT 
			pml.product_id,
			p.post_title as name,
			p.post_type,
			pml.sku,
			pml.stock_quantity,
			pml.stock_status,
			pml.min_price as price,
			p.post_modified as last_updated,
			COALESCE(parent.post_title, '') as parent_name,
			COALESCE(pm_low.meta_value, '') as low_stock_amount
			FROM {$wpdb->prefix}wc_product_meta_lookup pml
			JOIN {$wpdb->posts} p ON p.ID = pml.product_id
			LEFT JOIN {$wpdb->posts} parent ON parent.ID = p.post_parent AND p.post_type = 'product_variation'
			LEFT JOIN {$wpdb->postmeta} pm_low ON pm_low.post_id = pml.product_id AND pm_low.meta_key = '_low_stock_amount'
			WHERE p.post_status = 'publish'
			  AND p.post_type IN ('product', 'product_variation')
			ORDER BY pml.stock_status DESC, pml.stock_quantity ASC";

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$results = $wpdb->get_results( $query, ARRAY_A );

		$formatted_results = array();
		$instock_count    = 0;
		$outofstock_count = 0;
		$lowstock_count   = 0;

		foreach ( $results as $row ) {
			$pid       = (int) $row['product_id'];
			$curr_qty  = $row['stock_quantity'] !== null ? (int) $row['stock_quantity'] : null;
			$threshold = $row['low_stock_amount'] !== '' ? (int) $row['low_stock_amount'] : $global_threshold;

			$s_during = $sold_during[$pid] ?? 0;
			$s_after  = $sold_after[$pid] ?? 0;
			$r_during = $ref_during[$pid] ?? 0;
			$r_after  = $ref_after[$pid] ?? 0;

			if ( $curr_qty !== null ) {
				$ending_qty  = $curr_qty + $s_after - $r_after;
				$opening_qty = $ending_qty + $s_during - $r_during;
			} else {
				$ending_qty  = null;
				$opening_qty = null;
			}

			// Resolve status based on ending_qty
			if ( $curr_qty !== null ) {
				if ( $ending_qty <= 0 ) {
					$resolved_status = 'outofstock';
				} elseif ( $ending_qty < $threshold ) {
					$resolved_status = 'lowstock';
				} else {
					$resolved_status = 'instock';
				}
			} else {
				$resolved_status = $row['stock_status'];
			}

			// Tally summary counts
			if ( 'outofstock' === $resolved_status ) {
				$outofstock_count++;
			} elseif ( 'lowstock' === $resolved_status ) {
				$lowstock_count++;
			} else {
				$instock_count++;
			}

			// Format Name for variations.
			$display_name = $row['name'];
			if ( 'product_variation' === $row['post_type'] && ! empty( $row['parent_name'] ) ) {
				$display_name = $row['parent_name'] . ' — ' . $row['name'];
			}

			$formatted_results[] = array(
				'id'              => $pid,
				'name'            => $display_name,
				'sku'             => ! empty( $row['sku'] ) ? $row['sku'] : '-',
				'opening_qty'     => $opening_qty,
				'sold_qty'        => $s_during,
				'returned_qty'    => $r_during,
				'qty'             => $ending_qty,
				'status'          => $resolved_status,
				'price'           => (float) $row['price'],
				'type'            => 'product_variation' === $row['post_type'] ? __( 'Variation', '360-analytics-for-woocommerce' ) : __( 'Simple', '360-analytics-for-woocommerce' ),
				'edit_url'        => get_edit_post_link( $row['product_id'] ),
				'parent_edit_url' => 'product_variation' === $row['post_type'] ? get_edit_post_link( wp_get_post_parent_id( $row['product_id'] ) ) : '',
				'last_updated'    => $row['last_updated'],
			);
		}

		return array(
			'summary' => array(
				'instock'    => $instock_count,
				'outofstock' => $outofstock_count,
				'lowstock'   => $lowstock_count,
			),
			'table'   => $formatted_results,
		);
	}
}