<?php
/**
 * Admin Reports Class
 *
 * @package WarrantyCart
 */

namespace WarrantyCart\Admin;

use Automattic\WooCommerce\Enums\OrderInternalStatus;
use WC_Order;
use WC_Order_Item_Product;
use WC_Product;

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

/**
 * Admin Reports Class
 */
class Reports {

	/**
	 * Constructor
	 */
	public function __construct() {
		// Hook into WooCommerce product query to handle warranty meta queries
		add_filter( 'woocommerce_product_data_store_cpt_get_products_query', array( $this, 'modify_warranty_product_query' ), 10, 2 );
	}

	/**
	 * Modify warranty product query
	 *
	 * @param array $query      WP_Query arguments.
	 * @param array $query_vars Query variables.
	 *
	 * @return array Modified query arguments.
	 */
	public function modify_warranty_product_query( $query, $query_vars ) {
		// Check if this is a warranty query
		if ( isset( $query_vars['_warranty_query'] ) && $query_vars['_warranty_query'] ) {
			// Build meta query for warranty products
			$meta_query = array(
				'relation' => 'AND',
				array(
					'key'     => '_warranty_product_type',
					'compare' => 'EXISTS',
				),
			);

			// Add type filter if specified
			if ( isset( $query_vars['_warranty_meta_query'] ) && \is_array( $query_vars['_warranty_meta_query'] ) ) {
				foreach ( $query_vars['_warranty_meta_query'] as $meta_condition ) {
					if ( isset( $meta_condition['key'] ) && '_warranty_product_type' === $meta_condition['key'] && isset( $meta_condition['value'] ) ) {
						$meta_query[] = $meta_condition;
					}
				}
			}

			$query['meta_query'] = $meta_query;
		}

		return $query;
	}

	/**
	 * Get warranty product IDs
	 *
	 * Retrieves warranty product IDs with optional filtering and arguments.
	 *
	 * @param array<string, mixed> $args Additional arguments to merge with default query.
	 *
	 * @return array<int, int> Array of warranty product IDs.
	 */
	public function get_warranty_product_ids( array $args = array() ): array {
		$default_args = array(
			'limit'      => - 1,
			'return'     => 'ids',
			'meta_query' => array(
				array(
					'key'     => '_warranty_product_type',
					'compare' => 'EXISTS',
				),
			),
		);

		// Merge arguments properly - args override defaults for scalar values
		$query_args          = $default_args;
		$warranty_meta_query = array();

		foreach ( $args as $key => $value ) {
			if ( 'meta_query' === $key ) {
				// Store meta_query for the hook to process
				$warranty_meta_query = $value;
			} else {
				// For other keys, use the provided value
				$query_args[ $key ] = $value;
			}
		}

		// Store warranty query context and meta query for the hook
		$query_args['_warranty_query']      = true;
		$query_args['_warranty_meta_query'] = $warranty_meta_query;

		/**
		 * Filter warranty product IDs query arguments
		 *
		 * @param array $query_args Query arguments for wc_get_products.
		 * @param array $args       Additional arguments passed to the method.
		 */
		$query_args = \apply_filters( 'warranty_cart_warranty_product_ids_query_args', $query_args, $args );

		$products = \wc_get_products( $query_args );

		return is_array( $products ) ? $products : array();
	}

	/**
	 * Get total warranty orders count
	 *
	 * Retrieves the total count of orders containing warranty products.
	 *
	 * @return int Total number of warranty orders.
	 */
	public function get_total_warranty_orders(): int {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->get_row( "SELECT COUNT(DISTINCT order_id) as total FROM {$table_name}" );

		return (int) ( $result->total ?? 0 );
	}

	/**
	 * Get warranty products
	 *
	 * Retrieves warranty products as objects with optional filtering and arguments.
	 *
	 * @param array<string, mixed> $args Additional arguments to merge with default query.
	 *
	 * @return array<int, WC_Product> Array of warranty product objects.
	 */
	public function get_warranty_products( array $args = array() ): array {
		$default_args = array(
			'limit'      => - 1,
			'return'     => 'objects',
			'meta_query' => array(
				array(
					'key'     => '_warranty_product_type',
					'compare' => 'EXISTS',
				),
			),
		);

		// Merge arguments properly - args override defaults for scalar values
		$query_args          = $default_args;
		$warranty_meta_query = array();
		foreach ( $args as $key => $value ) {
			if ( isset( $default_args['meta_query'] ) && 'meta_query' === $key ) {
				// Store meta_query for the hook to process
				$warranty_meta_query = $value;
			} else {
				// For other keys, use the provided value
				$query_args[ $key ] = $value;
			}
		}

		// Store warranty query context and meta query for the hook
		$query_args['_warranty_query']      = true;
		$query_args['_warranty_meta_query'] = $warranty_meta_query;

		/**
		 * Filter warranty products query arguments
		 *
		 * @param array $query_args Query arguments for wc_get_products.
		 * @param array $args       Additional arguments passed to the method.
		 */
		$query_args = \apply_filters( 'warranty_cart_warranty_products_query_args', $query_args, $args );

		$products = \wc_get_products( $query_args );

		return is_array( $products ) ? $products : array();
	}

	/**
	 * Get sales data overview
	 *
	 * Retrieves basic warranty sales overview data with optional date range filtering.
	 *
	 * @param int $date_range Date range in days (0 for all time, or positive number for days).
	 *
	 * @return array<string, mixed> Sales data including total_sales, total_orders, average_order, total_warranties, and active_warranties.
	 */
	public function get_sales_data( int $date_range = 0 ): array {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		// Build date filter
		$date_filter = '';
		$date_params = array();
		if ( $date_range > 0 ) {
			$start_date  = \gmdate( 'Y-m-d H:i:s', \strtotime( "-{$date_range} days" ) );
			$date_filter = 'AND purchase_date >= %s';
			$date_params = array( $start_date );
		}

		/**
		 * Filter sales data query arguments
		 *
		 * @param string $date_filter Date filter SQL.
		 * @param array  $date_params Date filter parameters.
		 * @param int    $date_range  Date range in days.
		 */
		$date_filter = \apply_filters( 'warranty_cart_sales_data_date_filter', $date_filter, $date_params, $date_range );

		// Get sales data from warranty table
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		$query      = $wpdb->prepare(
			"SELECT
				SUM(purchase_price) as total_sales,
				COUNT(*) as total_warranties,
				SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) as active_warranties
			FROM {$table_name}
			WHERE 1=1 " . $date_filter,
			$date_params
		);
		$sales_data = $wpdb->get_row( $query );

		$total_sales       = (float) ( $sales_data->total_sales ?? 0 );
		$total_orders      = (int) ( $sales_data->total_orders ?? 0 );
		$total_warranties  = (int) ( $sales_data->total_warranties ?? 0 );
		$active_warranties = (int) ( $sales_data->active_warranties ?? 0 );

		$average_order = number_format( $total_orders > 0 ? $total_sales / $total_orders : 0, 2 );

		// Get order totals for detailed breakdown
		$query        = $wpdb->prepare(
			"SELECT order_id, SUM(purchase_price) as total
			FROM {$table_name}
			WHERE 1=1 " . $date_filter . '
			GROUP BY order_id',
			$date_params
		);
		$order_totals = $wpdb->get_results( $query, OBJECT_K );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared

		$order_totals_array = array();
		foreach ( $order_totals as $order_id => $data ) {
			$order_totals_array[ $order_id ] = (float) $data->total;
		}

		$sales_data = array(
			'total_sales'       => $total_sales,
			'total_orders'      => $total_orders,
			'average_order'     => $average_order,
			'order_totals'      => $order_totals_array,
			'total_warranties'  => $total_warranties,
			'active_warranties' => $active_warranties,
		);

		/**
		 * Filter sales data results
		 *
		 * @param array $sales_data Sales data array.
		 * @param int   $date_range Date range in days.
		 */
		return \apply_filters( 'warranty_cart_sales_data', $sales_data, $date_range );
	}

	/**
	 * Get popular warranties
	 *
	 * Retrieves the most popular warranty products based on sales data.
	 *
	 * @param int $limit Number of products to return.
	 *
	 * @return array<int, array<string, mixed>> Popular products data with sales counts.
	 */
	public function get_popular_warranties( int $limit = 20 ): array {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		// Get popular warranties from database
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$query              = $wpdb->prepare(
			"SELECT
				product_id,
				product_name,
				COUNT(*) as sales,
				SUM(purchase_price) as revenue
			FROM {$table_name}
			GROUP BY product_id, product_name
			ORDER BY sales DESC
			LIMIT %d",
			$limit
		);
		$popular_warranties = $wpdb->get_results( $query );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared

		$result = array();
		foreach ( $popular_warranties as $warranty ) {
			$result[ $warranty->product_id ] = array(
				'name'    => $warranty->product_name,
				'sales'   => (int) $warranty->sales,
				'revenue' => (float) $warranty->revenue,
			);
		}

		return $result;
	}

	/**
	 * Get monthly sales data
	 *
	 * Retrieves warranty sales data grouped by month for the last 12 months.
	 *
	 * @return array<string, float> Monthly sales data with totals per month.
	 */
	public function get_monthly_sales_data(): array {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		// Get monthly sales data from warranty table
		$date_format = '%M, %Y';
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		$query        = $wpdb->prepare(
			"SELECT
				DATE_FORMAT(purchase_date, %s) as month,
				SUM(purchase_price) as sales
			FROM {$table_name}
			WHERE purchase_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
			GROUP BY DATE_FORMAT(purchase_date, %s), YEAR(purchase_date), MONTH(purchase_date)
			ORDER BY YEAR(purchase_date), MONTH(purchase_date)",
			$date_format,
			$date_format
		);
		$monthly_data = $wpdb->get_results( $query );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared

		$result = array();
		foreach ( $monthly_data as $data ) {
			$result[ $data->month ] = (float) $data->sales;
		}

		return $result;
	}

	/**
	 * Get formatted warranty orders
	 *
	 * Retrieves orders that contain warranty products with optional filtering and formatting.
	 *
	 * @param array<string, mixed> $query_args Additional query arguments for wc_get_orders.
	 * @param bool                 $formatted  Whether to return formatted data or raw WC_Order objects.
	 *
	 * @return array<mixed> Array of formatted order data or WC_Order objects containing warranty products.
	 */
	public function get_formatted_warranty_orders( array $query_args = array(), bool $formatted = false ): array {
		// Use OrderQuery utility with specific overrides for this method
		$query_args = $this->build_order_query_args(
			$query_args,
			array(
				'orderby' => 'date',
				'order'   => 'DESC',
				'status'  => OrderInternalStatus::COMPLETED,
			)
		);

		// Handle pagination
		$page     = $query_args['page'] ?? 1;
		$per_page = $query_args['limit'] ?? - 1;
		if ( - 1 !== $per_page ) {
			$query_args['limit'] = $per_page;
			$query_args['page']  = $page;
		}

		// Get all warranty products
		$warranty_products = $this->get_warranty_product_ids();
		if ( empty( $warranty_products ) ) {
			return $formatted ? array(
				'orders'      => array(),
				'total'       => 0,
				'page'        => $page,
				'per_page'    => $per_page,
				'total_pages' => 0,
			) : array();
		}

		// Get orders that contain warranty products
		$orders_query = \wc_get_orders( $query_args );
		if ( ! is_array( $orders_query ) ) {
			$orders_query = array();
		}
		$orders = array();

		foreach ( $orders_query as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );

			// Only include orders with warranty products
			if ( $warranty_data['count'] > 0 ) {
				$warranty_items = array_map(
					static function ( $item ) {
						return array(
							'product_id'   => $item['product_id'],
							'product_name' => $item['product_name'],
							'quantity'     => $item['quantity'],
							'price'        => $item['total'],
						);
					},
					$warranty_data['items']
				);

				if ( $formatted ) {
					$date_created = $order->get_date_created();
					$orders[]     = array(
						'id'            => $order->get_id(),
						'customer_name' => $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
						'email'         => $order->get_billing_email(),
						'warranty_name' => implode( ', ', array_column( $warranty_items, 'product_name' ) ),
						'order_date'    => $date_created ? $date_created->date( 'Y-m-d' ) : '',
						'status'        => $order->get_status(),
						'amount'        => $order->get_total(),
						'items'         => $warranty_items,
					);
				} else {
					$orders[] = $order;
				}
			}
		}

		if ( ! $formatted ) {
			return $orders;
		}

		// Get total count for pagination
		$count_args = $query_args;
		unset( $count_args['limit'], $count_args['page'], $count_args['return'] );
		$count_args['limit'] = - 1;

		$all_orders = \wc_get_orders( $count_args );
		if ( ! is_array( $all_orders ) ) {
			$all_orders = array();
		}
		$total_orders = 0;
		foreach ( $all_orders as $order ) {
			foreach ( $order->get_items( 'line_item' ) as $order_item ) {
				if ( ! $order_item instanceof WC_Order_Item_Product ) {
					continue;
				}

				$product = $order_item->get_product();
				if ( $product instanceof WC_Product && $product->get_meta( '_warranty_product_type', true ) ) {
					++$total_orders;
					break; // Count each order only once
				}
			}
		}

		$total_pages = $per_page > 0 ? ceil( $total_orders / $per_page ) : 1;

		return array(
			'orders'      => $orders,
			'total'       => $total_orders,
			'page'        => $page,
			'per_page'    => $per_page,
			'total_pages' => $total_pages,
		);
	}

	/**
	 * Get comprehensive reports data
	 *
	 * Retrieves warranty sales analytics and reporting data with date range filtering.
	 *
	 * @param int $date_range Date range in days (7, 30, 90, 365).
	 *
	 * @return array<string, mixed> Comprehensive reports data including sales, orders, products, and monthly data.
	 */
	public function get_comprehensive_reports( int $date_range = 30 ): array {
		// Validate date range
		if ( ! in_array( $date_range, array( 7, 30, 90, 365 ), true ) ) {
			$date_range = 30; // Default to 30 days
		}

		$cache_key   = 'warranty_comprehensive_reports_' . $date_range;
		$cached_data = \get_transient( $cache_key );

		if ( false !== $cached_data ) {
			return $cached_data;
		}

		$query_args = $this->build_order_query_with_date_range( $date_range );

		/**
		 * Filter reports query arguments
		 *
		 * @param array $query_args Query arguments.
		 * @param int   $date_range Date range in days.
		 */
		$query_args = \apply_filters( 'warranty_cart_rest_reports_query_args', $query_args, $date_range );

		$orders          = $this->get_warranty_orders( array_merge( $query_args, array( 'return' => 'objects' ) ) );
		$warranty_orders = $orders; // Already filtered to warranty orders
		$product_sales   = array();
		$monthly_data    = array();

		// Initialize monthly data
		for ( $i = $date_range / 30; $i >= 0; $i-- ) {
			$timestamp = strtotime( "-{$i} months" );
			$month_key = $timestamp ? \wp_date( 'M', $timestamp ) : '';
			if ( $month_key ) {
				$monthly_data[ $month_key ] = array(
					'sales'  => 0,
					'orders' => 0,
				);
			}
		}

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );

			if ( $warranty_data['count'] > 0 ) {
				// Track product sales data
				foreach ( $warranty_data['items'] as $item ) {
					$product_id = $item['product_id'];
					if ( ! isset( $product_sales[ $product_id ] ) ) {
						$product_sales[ $product_id ] = array(
							'name'    => $item['product_name'],
							'sales'   => 0,
							'revenue' => 0,
						);
					}
					$product_sales[ $product_id ]['sales']   += $item['quantity'];
					$product_sales[ $product_id ]['revenue'] += $item['total'];
				}

				// Add to monthly data
				$date_created = $order->get_date_created();
				if ( $date_created ) {
					$order_month = $date_created->date( 'M' );
					if ( isset( $monthly_data[ $order_month ] ) ) {
						$monthly_data[ $order_month ]['sales'] += $warranty_data['total'];
						++$monthly_data[ $order_month ]['orders'];
					}
				}
			}
		}

		// Calculate totals
		$total_sales         = array_sum( array_column( $warranty_orders, 'total' ) );
		$total_orders        = count( $warranty_orders );
		$average_order_value = $total_orders > 0 ? $total_sales / $total_orders : 0;

		// Sort products by revenue
		usort(
			$product_sales,
			static function ( $a, $b ) {
				return $b['revenue'] <=> $a['revenue'];
			}
		);
		$top_products = array_slice( (array) $product_sales, 0, 5 );

		// Format monthly data
		$formatted_monthly = array();
		foreach ( $monthly_data as $month => $data ) {
			$formatted_monthly[] = array(
				'month'  => $month,
				'sales'  => $data['sales'],
				'orders' => $data['orders'],
			);
		}

		// Calculate additional metrics
		$total_customers       = count( array_unique( array_column( $warranty_orders, 'customer_id' ) ) );
		$conversion_rate       = 0; // Would need additional data to calculate
		$top_customer_segments = $this->get_top_customer_segments( $orders );

		// Calculate growth metrics (compare with previous period)
		$previous_period_sales = $this->get_previous_period_sales( $date_range );
		$sales_growth          = $previous_period_sales > 0 ? ( ( $total_sales - $previous_period_sales ) / $previous_period_sales ) * 100 : 0;

		// Calculate warranty type distribution
		$warranty_type_distribution = $this->get_warranty_type_distribution( $orders );

		// Calculate average warranty duration
		$average_warranty_duration = $this->get_average_warranty_duration( $orders );

		$reports = array(
			'total_sales'                => $total_sales,
			'total_orders'               => $total_orders,
			'total_customers'            => $total_customers,
			'average_order_value'        => $average_order_value,
			'sales_growth'               => $sales_growth,
			'conversion_rate'            => $conversion_rate,
			'top_products'               => $top_products,
			'top_customer_segments'      => $top_customer_segments,
			'warranty_type_distribution' => $warranty_type_distribution,
			'average_warranty_duration'  => $average_warranty_duration,
			'monthly_data'               => $formatted_monthly,
			'date_range'                 => $date_range,
			'generated_at'               => time(),
		);

		/**
		 * Filter reports response data
		 *
		 * @param array $reports    Reports data.
		 * @param int   $date_range Date range in days.
		 */
		$filtered_reports = \apply_filters( 'warranty_cart_rest_reports_response', $reports, $date_range );

		// Cache for 15 minutes
		\set_transient( $cache_key, $filtered_reports, 15 * MINUTE_IN_SECONDS );

		return $filtered_reports;
	}

	/**
	 * Get single warranty order
	 *
	 * Retrieves detailed information for a specific warranty order.
	 *
	 * @param int $order_id Order ID to retrieve.
	 *
	 * @return array<string, mixed>|null Order data if it's a warranty order, null otherwise.
	 */
	public function get_single_warranty_order( int $order_id ): ?array {
		$order = \wc_get_order( $order_id );
		if ( ! $order instanceof WC_Order ) {
			return null;
		}

		$warranty_data = $this->get_order_warranty_items( $order );

		// Only return data if order contains warranty products
		if ( 0 === $warranty_data['count'] ) {
			return null;
		}

		$warranty_items = array_map(
			static function ( $item ) {
				return array(
					'product_id'   => $item['product_id'],
					'product_name' => $item['product_name'],
					'quantity'     => $item['quantity'],
					'price'        => $item['total'],
				);
			},
			$warranty_data['items']
		);

		$date_created = $order->get_date_created();

		return array(
			'id'                   => $order->get_id(),
			'customer_name'        => $order->get_billing_first_name() . ' ' . $order->get_billing_last_name(),
			'email'                => $order->get_billing_email(),
			'warranty_name'        => implode( ', ', array_column( $warranty_items, 'product_name' ) ),
			'order_date'           => $date_created ? $date_created->date( 'Y-m-d' ) : '',
			'status'               => $order->get_status(),
			'amount'               => $order->get_total(),
			'items'                => $warranty_items,
			'payment_method'       => $order->get_payment_method(),
			'payment_method_title' => $order->get_payment_method_title(),
			'customer_note'        => $order->get_customer_note(),
			'billing'              => array(
				'first_name' => $order->get_billing_first_name(),
				'last_name'  => $order->get_billing_last_name(),
				'email'      => $order->get_billing_email(),
				'phone'      => $order->get_billing_phone(),
				'address_1'  => $order->get_billing_address_1(),
				'address_2'  => $order->get_billing_address_2(),
				'city'       => $order->get_billing_city(),
				'state'      => $order->get_billing_state(),
				'postcode'   => $order->get_billing_postcode(),
				'country'    => $order->get_billing_country(),
			),
		);
	}

	/**
	 * Get recent orders
	 *
	 * Retrieves the most recent orders containing warranty products.
	 *
	 * @param int $limit Number of orders to retrieve.
	 *
	 * @return array<int, array<string, mixed>> Recent orders data with customer and product information.
	 */
	public function get_recent_orders( int $limit = 10 ): array {
		// Get all warranty products
		$warranty_products = $this->get_warranty_product_ids();

		if ( empty( $warranty_products ) ) {
			return array();
		}

		// Get recent completed orders containing warranty products
		$orders = $this->get_warranty_orders(
			array(
				'limit'   => $limit * 5, // Get more to account for filtering
				'orderby' => 'date',
				'order'   => 'DESC',
			)
		);

		$recent_orders = array();

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );

			if ( $warranty_data['count'] > 0 ) {
				$products     = array_column( $warranty_data['items'], 'product_name' );
				$date_created = $order->get_date_created();

				$recent_orders[] = array(
					'id'       => $order->get_id(),
					'customer' => trim( $order->get_billing_first_name() . ' ' . $order->get_billing_last_name() ),
					'products' => $products,
					'total'    => $order->get_total(),
					'date'     => $date_created ? \date_i18n( \get_option( 'date_format' ), $date_created->getTimestamp() ) : '',
					'edit_url' => \admin_url( 'post.php?post=' . $order->get_id() . '&action=edit' ),
				);
			}
		}

		return $recent_orders;
	}

	/**
	 * Get warranty orders
	 *
	 * Retrieves orders containing warranty products with optional filtering.
	 *
	 * @param array<string, mixed> $args Additional arguments to merge with default query.
	 *
	 * @return array<int, WC_Order> Array of WC_Order objects containing warranty products.
	 */
	public function get_warranty_orders( array $args = array() ): array {
		global $wpdb;

		$table_name = $wpdb->prefix . 'warranty_cart_warranties';

		// Build query to get unique order IDs with warranties
		$limit  = isset( $args['limit'] ) ? absint( $args['limit'] ) : - 1;
		$offset = isset( $args['offset'] ) ? absint( $args['offset'] ) : 0;

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
		if ( $limit > 0 ) {
			$query = $wpdb->prepare( "SELECT DISTINCT order_id FROM {$table_name} ORDER BY order_id DESC LIMIT %d OFFSET %d", $limit, $offset );
		} else {
			$query = $wpdb->prepare( "SELECT DISTINCT order_id FROM {$table_name} ORDER BY order_id DESC" );
		}

		$order_ids = $wpdb->get_col( $query );
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared

		if ( empty( $order_ids ) ) {
			return array();
		}

		// Get the actual order objects
		$warranty_orders = array();
		foreach ( $order_ids as $order_id ) {
			$order = \wc_get_order( $order_id );
			if ( $order instanceof WC_Order ) {
				$warranty_orders[] = $order;
			}
		}

		/**
		 * Filter warranty orders results
		 *
		 * @param array $warranty_orders Array of WC_Order objects.
		 * @param array $args            Additional arguments passed to the method.
		 */
		return \apply_filters( 'warranty_cart_warranty_orders', $warranty_orders, $args );
	}

	/**
	 * Build standard order query arguments
	 *
	 * @param array<string, mixed> $args      Additional arguments to merge.
	 * @param array<string, mixed> $overrides Override default arguments.
	 *
	 * @return array<string, mixed> Complete query arguments for wc_get_orders().
	 */
	private function build_order_query_args( array $args = array(), array $overrides = array() ): array {
		$default_args = array(
			'limit'  => - 1,
			'status' => array( OrderInternalStatus::COMPLETED, OrderInternalStatus::PROCESSING, OrderInternalStatus::ON_HOLD ),
			'return' => 'objects',
		);

		return array_merge( $default_args, $overrides, $args );
	}

	/**
	 * Build order query with date range filtering
	 *
	 * @param int                  $date_range Days to look back (0 = all time).
	 * @param array<string, mixed> $args       Additional arguments.
	 * @param array<string, mixed> $overrides  Override default arguments.
	 *
	 * @return array<string, mixed> Query arguments with date filtering.
	 */
	private function build_order_query_with_date_range( int $date_range = 0, array $args = array(), array $overrides = array() ): array {
		$query_args = $this->build_order_query_args( $args, $overrides );

		// Add date range filtering if specified
		if ( $date_range > 0 ) {
			$end_date   = \current_time( 'Y-m-d H:i:s' );
			$timestamp  = strtotime( "-{$date_range} days" );
			$start_date = $timestamp ? \wp_date( 'Y-m-d H:i:s', $timestamp ) : null;

			if ( $start_date ) {
				$query_args['date_after']  = $start_date;
				$query_args['date_before'] = $end_date;
			}
		}

		return $query_args;
	}

	/**
	 * Build order query for completed orders only
	 *
	 * @param array<string, mixed> $args Additional arguments.
	 *
	 * @return array<string, mixed> Query arguments for completed orders.
	 */
	private function build_completed_orders_query( array $args = array() ): array {
		return $this->build_order_query_args( $args, array( 'status' => OrderInternalStatus::COMPLETED ) );
	}

	/**
	 * Check if a product is a warranty product
	 *
	 * @param WC_Product $product Product to check.
	 *
	 * @return bool True if product is a warranty product.
	 */
	public function is_warranty_product( WC_Product $product ): bool {
		return $product->exists() && $product->get_meta( '_warranty_cart_product_status', true ) === 'enabled' && $product->meta_exists( '_warranty_cart_product_type' );
	}

	/**
	 * Get warranty items from an order
	 *
	 * @param WC_Order $order Order to analyze.
	 *
	 * @return array<string, mixed> Array of warranty items with product and total data.
	 */
	public function get_order_warranty_items( WC_Order $order ): array {
		$warranty_items = array();
		$total          = 0;

		foreach ( $order->get_items( 'line_item' ) as $order_item ) {
			if ( ! $order_item instanceof WC_Order_Item_Product ) {
				continue;
			}

			$product = $order_item->get_product();
			if ( $product instanceof WC_Product && $this->is_warranty_product( $product ) ) {
				$item_total = $order_item->get_total();
				$total     += $item_total;

				$warranty_items[] = array(
					'product'      => $product,
					'order_item'   => $order_item,
					'quantity'     => $order_item->get_quantity(),
					'total'        => $item_total,
					'product_id'   => $product->get_id(),
					'product_name' => $product->get_name(),
				);
			}
		}

		return array(
			'items' => $warranty_items,
			'total' => $total,
			'count' => count( $warranty_items ),
		);
	}

	/**
	 * Get top customer segments
	 *
	 * Analyzes customer purchasing patterns and segments.
	 *
	 * @param array $orders Array of WC_Order objects.
	 *
	 * @return array Customer segment data.
	 */
	private function get_top_customer_segments( array $orders ): array {
		$segments = array(
			'new_customers'       => 0,
			'returning_customers' => 0,
			'high_value'          => 0,
			'bulk_purchasers'     => 0,
		);

		$customer_orders = array();
		$customer_totals = array();

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$customer_id = $order->get_customer_id();
			if ( ! $customer_id ) {
				continue; // Skip guest orders
			}

			$warranty_data = $this->get_order_warranty_items( $order );
			if ( 0 === $warranty_data['count'] ) {
				continue;
			}

			if ( ! isset( $customer_orders[ $customer_id ] ) ) {
				$customer_orders[ $customer_id ] = 0;
				$customer_totals[ $customer_id ] = 0;
			}

			++$customer_orders[ $customer_id ];
			$customer_totals[ $customer_id ] += $warranty_data['total'];
		}

		foreach ( $customer_orders as $customer_id => $order_count ) {
			$total_spent = $customer_totals[ $customer_id ];

			if ( 1 === $order_count ) {
				++$segments['new_customers'];
			} else {
				++$segments['returning_customers'];
			}

			if ( $total_spent > 500 ) {
				++$segments['high_value'];
			}

			if ( $order_count > 3 ) {
				++$segments['bulk_purchasers'];
			}
		}

		return $segments;
	}

	/**
	 * Get previous period sales
	 *
	 * Calculates sales for the previous period of the same length.
	 *
	 * @param int $date_range Current date range in days.
	 *
	 * @return float Previous period sales amount.
	 */
	private function get_previous_period_sales( int $date_range ): float {
		$previous_start = strtotime( "-{$date_range} days", strtotime( "-{$date_range} days" ) );
		$previous_end   = strtotime( "-{$date_range} days" );

		$query_args = $this->build_order_query_args(
			array(
				'date_created' => $previous_start . '...' . $previous_end,
				'status'       => OrderInternalStatus::COMPLETED,
			)
		);

		$orders = \wc_get_orders( $query_args );
		if ( ! is_array( $orders ) ) {
			return 0.0;
		}

		$total_sales = 0.0;
		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );
			$total_sales  += $warranty_data['total'];
		}

		return $total_sales;
	}

	/**
	 * Get warranty type distribution
	 *
	 * Analyzes the distribution of warranty types sold.
	 *
	 * @param array $orders Array of WC_Order objects.
	 *
	 * @return array Warranty type distribution data.
	 */
	private function get_warranty_type_distribution( array $orders ): array {
		$distribution = array(
			'main_warranty'      => 0,
			'additional_service' => 0,
		);

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );

			foreach ( $warranty_data['items'] as $item ) {
				$product = $item['product'];
				if ( $product instanceof WC_Product ) {
					$warranty_type = $product->get_meta( '_warranty_product_type', true );
					if ( isset( $distribution[ $warranty_type ] ) ) {
						$distribution[ $warranty_type ] += $item['quantity'];
					}
				}
			}
		}

		return $distribution;
	}

	/**
	 * Get average warranty duration
	 *
	 * Calculates the average duration of warranties sold.
	 *
	 * @param array $orders Array of WC_Order objects.
	 *
	 * @return array Average duration data by unit.
	 */
	private function get_average_warranty_duration( array $orders ): array {
		$durations = array(
			'days'   => array(),
			'months' => array(),
			'years'  => array(),
		);

		foreach ( $orders as $order ) {
			if ( ! $order instanceof WC_Order ) {
				continue;
			}

			$warranty_data = $this->get_order_warranty_items( $order );

			foreach ( $warranty_data['items'] as $item ) {
				$product = $item['product'];
				if ( $product instanceof WC_Product ) {
					$warranty_type = $product->get_meta( '_warranty_product_type', true );
					if ( 'main_warranty' === $warranty_type ) {
						$duration = (int) $product->get_meta( '_warranty_duration', true );
						$unit     = $product->get_meta( '_warranty_duration_unit', true );

						if ( isset( $durations[ $unit ] ) ) {
							$durations[ $unit ][] = $duration;
						}
					}
				}
			}
		}

		$averages = array();
		foreach ( $durations as $unit => $values ) {
			if ( ! empty( $values ) ) {
				$averages[ $unit ] = array_sum( $values ) / count( $values );
			} else {
				$averages[ $unit ] = 0;
			}
		}

		return $averages;
	}

	/**
	 * Get dashboard data
	 *
	 * Retrieves all data needed for the admin dashboard.
	 *
	 * @param int $date_range Date range in days for sales data.
	 *
	 * @return array<string, mixed> Dashboard data array.
	 */
	public function get_dashboard_data( int $date_range = 30 ): array {
		$cache_key   = 'warranty_dashboard_data_' . $date_range;
		$cached_data = \get_transient( $cache_key );

		if ( false !== $cached_data ) {
			return $cached_data;
		}

		// Get sales overview data
		$sales_data = $this->get_sales_data( $date_range );

		// Get popular warranties (top 5)
		$popular_warranties = $this->get_popular_warranties( 5 );

		// Get recent orders (top 5)
		$recent_orders = $this->get_recent_orders( 5 );

		// Get monthly sales data
		$monthly_sales = $this->get_monthly_sales_data();

		// Get warranty status summary
		$warranty_status_summary = $this->get_warranty_status_summary();

		// Get performance metrics
		$performance_metrics = $this->get_performance_metrics( $date_range );

		// Get alerts and notifications
		$alerts = $this->get_system_alerts();

		$dashboard_data = array(
			'sales_overview'          => $sales_data,
			'popular_warranties'      => $popular_warranties,
			'recent_orders'           => $recent_orders,
			'monthly_sales'           => $monthly_sales,
			'warranty_status_summary' => $warranty_status_summary,
			'performance_metrics'     => $performance_metrics,
			'alerts'                  => $alerts,
			'date_range'              => $date_range,
			'generated_at'            => time(),
		);

		// Cache for 10 minutes
		\set_transient( $cache_key, $dashboard_data, 10 * MINUTE_IN_SECONDS );

		return $dashboard_data;
	}

	/**
	 * Get warranty status summary
	 *
	 * Provides a summary of warranty statuses across all products.
	 *
	 * @return array Warranty status summary data.
	 */
	private function get_warranty_status_summary(): array {
		$warranty_products = $this->get_warranty_products();

		$summary = array(
			'total_warranties'    => 0,
			'active_warranties'   => 0,
			'inactive_warranties' => 0,
			'out_of_stock'        => 0,
			'low_stock'           => 0,
		);

		foreach ( $warranty_products as $product ) {
			if ( ! $product instanceof WC_Product ) {
				continue;
			}

			++$summary['total_warranties'];

			if ( 'publish' === $product->get_status() ) {
				++$summary['active_warranties'];
			} else {
				++$summary['inactive_warranties'];
			}

			if ( ! $product->is_in_stock() ) {
				++$summary['out_of_stock'];
			} elseif ( $product->get_stock_quantity() && $product->get_stock_quantity() < 5 ) {
				++$summary['low_stock'];
			}
		}

		return $summary;
	}

	/**
	 * Get performance metrics
	 *
	 * Calculates various performance metrics for the dashboard.
	 *
	 * @param int $date_range Date range in days.
	 *
	 * @return array Performance metrics data.
	 */
	private function get_performance_metrics( int $date_range ): array {
		$current_sales  = $this->get_sales_data( $date_range );
		$previous_sales = $this->get_sales_data( $date_range * 2 );

		$sales_growth = 0;
		if ( $previous_sales['total_sales'] > 0 ) {
			$sales_growth = ( ( $current_sales['total_sales'] - $previous_sales['total_sales'] ) / $previous_sales['total_sales'] ) * 100;
		}

		$order_growth = 0;
		if ( $previous_sales['total_orders'] > 0 ) {
			$order_growth = ( ( $current_sales['total_orders'] - $previous_sales['total_orders'] ) / $previous_sales['total_orders'] ) * 100;
		}

		return array(
			'sales_growth_percentage' => round( $sales_growth, 2 ),
			'order_growth_percentage' => round( $order_growth, 2 ),
			'current_period_sales'    => $current_sales['total_sales'],
			'previous_period_sales'   => $previous_sales['total_sales'],
			'current_period_orders'   => $current_sales['total_orders'],
			'previous_period_orders'  => $previous_sales['total_orders'],
		);
	}

	/**
	 * Get system alerts
	 *
	 * Retrieves important alerts and notifications for the dashboard.
	 *
	 * @return array System alerts data.
	 */
	private function get_system_alerts(): array {
		$alerts = array();

		// Check for out of stock warranties
		$warranty_products  = $this->get_warranty_products();
		$out_of_stock_count = 0;

		foreach ( $warranty_products as $product ) {
			if ( $product instanceof WC_Product && ! $product->is_in_stock() ) {
				++$out_of_stock_count;
			}
		}

		if ( $out_of_stock_count > 0 ) {
			$alerts[] = array(
				'type'    => 'warning',
				'message' => sprintf(
				/* translators: %d: number of out of stock warranties */
					\_n(
						'%d warranty product is out of stock',
						'%d warranty products are out of stock',
						$out_of_stock_count,
						'warranty-cart'
					),
					$out_of_stock_count
				),
				'action'  => \admin_url( 'edit.php?post_type=product&product_cat=warranties' ),
			);
		}

		// Check for low stock warranties
		$low_stock_count = 0;
		foreach ( $warranty_products as $product ) {
			if ( $product instanceof WC_Product && $product->get_stock_quantity() && $product->get_stock_quantity() < 5 && $product->is_in_stock() ) {
				++$low_stock_count;
			}
		}

		if ( $low_stock_count > 0 ) {
			$alerts[] = array(
				'type'    => 'info',
				'message' => sprintf(
				/* translators: %d: number of low stock warranties */
					\_n(
						'%d warranty product has low stock',
						'%d warranty products have low stock',
						$low_stock_count,
						'warranty-cart'
					),
					$low_stock_count
				),
				'action'  => \admin_url( 'edit.php?post_type=product&product_cat=warranties' ),
			);
		}

		// Check for recent orders without PDFs
		$recent_orders      = $this->get_recent_orders( 10 );
		$orders_without_pdf = 0;

		foreach ( $recent_orders as $order_data ) {
			$order = \wc_get_order( $order_data['id'] );
			if ( $order instanceof WC_Order ) {
				$warranty_data = $this->get_order_warranty_items( $order );
				if ( $warranty_data['count'] > 0 ) {
					// Check if PDF exists
					$upload_dir = \wp_upload_dir();
					$pdf_path   = $upload_dir['basedir'] . '/warranties/warranty-' . $order->get_id() . '.pdf';

					if ( ! warranty_cart()->file_exists( $pdf_path ) ) {
						++$orders_without_pdf;
					}
				}
			}
		}

		if ( $orders_without_pdf > 0 ) {
			$alerts[] = array(
				'type'    => 'info',
				'message' => sprintf(
				/* translators: %d: number of orders without PDFs */
					\_n(
						'%d recent order is missing warranty PDF',
						'%d recent orders are missing warranty PDFs',
						$orders_without_pdf,
						'warranty-cart'
					),
					$orders_without_pdf
				),
				'action'  => \admin_url( 'admin.php?page=warranty-orders' ),
			);
		}

		return $alerts;
	}

	/**
	 * Export reports data to CSV
	 *
	 * Generates and downloads a CSV file with comprehensive warranty reports data.
	 *
	 * @param int $date_range Date range in days for the export.
	 *
	 * @return void Outputs CSV file for download.
	 */
	public function export_reports_csv( int $date_range = 30 ): void {
		// Get comprehensive reports data
		$reports_data = $this->get_comprehensive_reports( $date_range );

		// Set headers for CSV download
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=warranty-reports-' . \wp_date( 'Y-m-d' ) . '.csv' );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		// Open output stream
		$output = fopen( 'php://output', 'w' );

		// Write CSV headers
		fputcsv( $output, array( 'Report Type', 'Metric', 'Value', 'Details' ) );

		// Sales Overview
		fputcsv( $output, array( 'Sales Overview', 'Total Sales', \wc_price( $reports_data['total_sales'] ), '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Total Orders', $reports_data['total_orders'], '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Total Customers', $reports_data['total_customers'], '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Average Order Value', \wc_price( $reports_data['average_order_value'] ), '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Sales Growth', number_format( $reports_data['sales_growth'], 2 ) . '%', '' ) );

		// Customer Segments
		fputcsv( $output, array( 'Customer Segments', 'New Customers', $reports_data['top_customer_segments']['new_customers'], '' ) );
		fputcsv( $output, array( 'Customer Segments', 'Returning Customers', $reports_data['top_customer_segments']['returning_customers'], '' ) );
		fputcsv( $output, array( 'Customer Segments', 'High Value Customers', $reports_data['top_customer_segments']['high_value'], '' ) );
		fputcsv( $output, array( 'Customer Segments', 'Bulk Purchasers', $reports_data['top_customer_segments']['bulk_purchasers'], '' ) );

		// Warranty Type Distribution
		fputcsv( $output, array( 'Warranty Types', 'Main Warranties', $reports_data['warranty_type_distribution']['main_warranty'], '' ) );
		fputcsv( $output, array( 'Warranty Types', 'Additional Services', $reports_data['warranty_type_distribution']['additional_service'], '' ) );

		// Average Warranty Duration
		fputcsv( $output, array( 'Warranty Duration', 'Average Days', number_format( $reports_data['average_warranty_duration']['days'], 1 ), '' ) );
		fputcsv( $output, array( 'Warranty Duration', 'Average Months', number_format( $reports_data['average_warranty_duration']['months'], 1 ), '' ) );
		fputcsv( $output, array( 'Warranty Duration', 'Average Years', number_format( $reports_data['average_warranty_duration']['years'], 1 ), '' ) );

		// Top Products
		fputcsv( $output, array( 'Top Products', '', '', '' ) );
		foreach ( $reports_data['top_products'] as $product ) {
			fputcsv( $output, array( 'Top Products', $product['name'], $product['sales'] . ' sales', \wc_price( $product['revenue'] ) ) );
		}

		// Monthly Data
		fputcsv( $output, array( 'Monthly Trends', '', '', '' ) );
		foreach ( $reports_data['monthly_data'] as $month_data ) {
			fputcsv( $output, array( 'Monthly Trends', $month_data['month'], \wc_price( $month_data['sales'] ), $month_data['orders'] . ' orders' ) );
		}

		// Close output stream
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- fclose() is appropriate for CSV export output stream
		fclose( $output );
		exit;
	}

	/**
	 * Export dashboard data to CSV
	 *
	 * Generates and downloads a CSV file with dashboard summary data.
	 *
	 * @param int $date_range Date range in days for the export.
	 *
	 * @return void Outputs CSV file for download.
	 */
	public function export_dashboard_csv( int $date_range = 30 ): void {
		// Get dashboard data
		$dashboard_data = $this->get_dashboard_data( $date_range );

		// Set headers for CSV download
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=warranty-dashboard-' . \wp_date( 'Y-m-d' ) . '.csv' );
		header( 'Pragma: no-cache' );
		header( 'Expires: 0' );

		// Open output stream
		$output = fopen( 'php://output', 'w' );

		// Write CSV headers
		fputcsv( $output, array( 'Section', 'Metric', 'Value', 'Details' ) );

		// Sales Overview
		$sales_overview = $dashboard_data['sales_overview'];
		fputcsv( $output, array( 'Sales Overview', 'Total Sales', \wc_price( $sales_overview['total_sales'] ), '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Total Orders', $sales_overview['total_orders'], '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Average Order', \wc_price( $sales_overview['average_order'] ), '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Total Warranties', $sales_overview['total_warranties'], '' ) );
		fputcsv( $output, array( 'Sales Overview', 'Active Warranties', $sales_overview['active_warranties'], '' ) );

		// Performance Metrics
		$performance = $dashboard_data['performance_metrics'];
		fputcsv( $output, array( 'Performance', 'Sales Growth', number_format( $performance['sales_growth_percentage'], 2 ) . '%', '' ) );
		fputcsv( $output, array( 'Performance', 'Order Growth', number_format( $performance['order_growth_percentage'], 2 ) . '%', '' ) );
		fputcsv( $output, array( 'Performance', 'Current Period Sales', \wc_price( $performance['current_period_sales'] ), '' ) );
		fputcsv( $output, array( 'Performance', 'Previous Period Sales', \wc_price( $performance['previous_period_sales'] ), '' ) );

		// Warranty Status Summary
		$status_summary = $dashboard_data['warranty_status_summary'];
		fputcsv( $output, array( 'Warranty Status', 'Total Warranties', $status_summary['total_warranties'], '' ) );
		fputcsv( $output, array( 'Warranty Status', 'Active Warranties', $status_summary['active_warranties'], '' ) );
		fputcsv( $output, array( 'Warranty Status', 'Inactive Warranties', $status_summary['inactive_warranties'], '' ) );
		fputcsv( $output, array( 'Warranty Status', 'Out of Stock', $status_summary['out_of_stock'], '' ) );
		fputcsv( $output, array( 'Warranty Status', 'Low Stock', $status_summary['low_stock'], '' ) );

		// Popular Warranties
		fputcsv( $output, array( 'Popular Warranties', '', '', '' ) );
		foreach ( $dashboard_data['popular_warranties'] as $warranty ) {
			fputcsv( $output, array( 'Popular Warranties', $warranty['name'], $warranty['sales'] . ' sales', '' ) );
		}

		// Recent Orders
		fputcsv( $output, array( 'Recent Orders', '', '', '' ) );
		foreach ( $dashboard_data['recent_orders'] as $order ) {
			fputcsv( $output, array( 'Recent Orders', $order['customer'], implode( ', ', $order['products'] ), \wc_price( $order['total'] ) ) );
		}

		// Monthly Sales
		fputcsv( $output, array( 'Monthly Sales', '', '', '' ) );
		foreach ( $dashboard_data['monthly_sales'] as $month => $sales ) {
			fputcsv( $output, array( 'Monthly Sales', $month, \wc_price( $sales ), '' ) );
		}

		// System Alerts
		if ( ! empty( $dashboard_data['alerts'] ) ) {
			fputcsv( $output, array( 'System Alerts', '', '', '' ) );
			foreach ( $dashboard_data['alerts'] as $alert ) {
				fputcsv( $output, array( 'System Alerts', ucfirst( $alert['type'] ), $alert['message'], '' ) );
			}
		}

		// Close output stream
		// phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- fclose() is appropriate for CSV export output stream
		fclose( $output );
		exit;
	}
}
