<?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
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$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:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		// 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() ) {
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$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:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// 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 publisher taxonomy terms for products in one query
		$publisher_map = array();
		$pub_query = "
			SELECT tr.object_id, t.name as publisher_name
			FROM {$wpdb->term_relationships} tr
			JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy LIKE '%publisher%'
			JOIN {$wpdb->terms} t ON t.term_id = tt.term_id
		";
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
		$pub_results = $wpdb->get_results( $pub_query, ARRAY_A );
		if ( ! empty( $pub_results ) ) {
			foreach ( $pub_results as $pr ) {
				$obj_id = (int) $pr['object_id'];
				if ( ! isset( $publisher_map[ $obj_id ] ) ) {
					$publisher_map[ $obj_id ] = array();
				}
				$publisher_map[ $obj_id ][] = $pr['publisher_name'];
			}
		}

		// 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'];
			}

			// Resolve Publisher
			$parent_id = 'product_variation' === $row['post_type'] ? (int) wp_get_post_parent_id( $pid ) : 0;
			$pub_names = $publisher_map[ $pid ] ?? ( $parent_id ? ( $publisher_map[ $parent_id ] ?? array() ) : array() );

			$individual_publishers = array();
			if ( ! empty( $pub_names ) ) {
				foreach ( $pub_names as $pname ) {
					$parts = explode( ',', $pname );
					foreach ( $parts as $part ) {
						$part = trim( $part );
						if ( '' !== $part ) {
							$individual_publishers[] = $part;
						}
					}
				}
				$individual_publishers = array_values( array_unique( $individual_publishers ) );
			}

			if ( empty( $individual_publishers ) ) {
				$individual_publishers = array( __( 'Unassigned', '360-analytics-for-woocommerce' ) );
			}

			$publisher = implode( ', ', $individual_publishers );

			$formatted_results[] = array(
				'id'                    => $pid,
				'name'                  => $display_name,
				'sku'                   => ! empty( $row['sku'] ) ? $row['sku'] : '-',
				'publisher'             => $publisher,
				'individual_publishers' => $individual_publishers,
				'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'],
			);
		}

		// Tally publishers individually
		$publishers_summary = array();
		foreach ( $formatted_results as $row ) {
			$pub_list = ! empty( $row['individual_publishers'] ) ? $row['individual_publishers'] : array( $row['publisher'] );
			foreach ( $pub_list as $pub ) {
				if ( ! isset( $publishers_summary[ $pub ] ) ) {
					$publishers_summary[ $pub ] = 0;
				}
				$publishers_summary[ $pub ]++;
			}
		}
		ksort( $publishers_summary );

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