<?php
/**
 * Database Manager Class
 *
 * @package ShopExplorer\Database
 * @since 1.0.0
 */

namespace SHOPEXPLORER\Database;

use SHOPEXPLORER\Exceptions\ShopExplorerException;

if ( ! defined( 'ABSPATH' ) ) {
	exit; // Exit if accessed directly.
}

/**
 * Database Manager class
 *
 * Handles database operations with WooCommerce integration and HPOS compatibility
 *
 * @since 1.0.0
 */
class DatabaseManager {

	/**
	 * WordPress database instance
	 *
	 * @var \wpdb
	 */
	private $wpdb;

	/**
	 * Cache for query results
	 *
	 * @var array
	 */
	private $cache = [];

	/**
	 * Cache expiration time (in seconds)
	 *
	 * @var int
	 */
	private $cache_expiration = 300; // 5 minutes

	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->wpdb = $wpdb;
	}



	/**
	 * Save incomplete order data
	 *
	 * @param array $data Incomplete order data
	 * @return int|false Insert ID on success, false on failure
	 */
	public function save_incomplete_order( $data ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		// Check if order already exists for this session
		$existing_order = $wpdb->get_row( $wpdb->prepare(
			"SELECT id FROM {$table_name} WHERE session_key = %s AND status = 'incomplete'",
			$data['session_key']
		) );

		$order_data = [
			'session_key' => $data['session_key'],
			'user_id' => $data['user_id'] ?? 0,
			'email' => $data['email'],
			'name' => $data['name'],
			'phone' => $data['phone'],
			'address' => $data['address'],
			'product_ids' => $data['product_ids'],
			'product_names' => $data['product_names'],
			'product_links' => $data['product_links'],
			'cart_data' => $data['cart_data'],
			'total_amount' => $data['total_amount'],
			'cart_interactions' => $data['cart_interactions'] ?? '[]',
			'is_abandonment' => $data['is_abandonment'] ?? false,
			'time_on_checkout' => $data['time_on_checkout'] ?? 0,
			'ip' => $data['ip'],
			'user_agent' => $data['user_agent'],
			'status' => 'incomplete', // This was missing!
		];

		if ( $existing_order ) {
			// Update existing order
		
			$result = $wpdb->update(
				$table_name,
				$order_data,
				[ 'id' => $existing_order->id ],
				[
					'%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%f', '%s', '%d', '%d', '%s', '%s', '%s'
				],
				[ '%d' ]
			);
			
			if ( $result !== false ) {
				return $existing_order->id;
			} else {
				return false;
			}
		} else {
			// Insert new order
			$result = $wpdb->insert(
				$table_name,
				$order_data,
				[
					'%s', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%f', '%s', '%d', '%d', '%s', '%s', '%s'
				]
			);
			
			if ( $result ) {
				$insert_id = $wpdb->insert_id;
				$this->schedule_incomplete_order_notification( $insert_id );
				
				return $insert_id;
			} else {
				
				return false;
			}
		}
	}

	/**
	 * Get incomplete orders
	 *
	 * @param array $filters Optional filters
	 * @return array
	 */
	public function get_incomplete_orders( $filters = [] ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		$where_clauses = [ "status = 'incomplete'" ];
		$where_values = [];

		// Add filters
		if ( ! empty( $filters['email'] ) ) {
			$where_clauses[] = "email LIKE %s";
			$where_values[] = '%' . $wpdb->esc_like( $filters['email'] ) . '%';
		}

		if ( ! empty( $filters['date_from'] ) ) {
			$where_clauses[] = "DATE(created_at) >= %s";
			$where_values[] = $filters['date_from'];
		}

		if ( ! empty( $filters['date_to'] ) ) {
			$where_clauses[] = "DATE(created_at) <= %s";
			$where_values[] = $filters['date_to'];
		}

		$where_sql = implode( ' AND ', $where_clauses );
		
		$query = "SELECT * FROM {$table_name} WHERE {$where_sql} ORDER BY created_at DESC LIMIT 100";
		
		if ( ! empty( $where_values ) ) {
			$query = $wpdb->prepare( $query, $where_values );
		}

		$results = $wpdb->get_results( $query );
		
		// Process results to decode JSON fields
		foreach ( $results as &$result ) {
			$result->product_ids = json_decode( $result->product_ids, true ) ?: [];
			$result->product_names = json_decode( $result->product_names, true ) ?: [];
			$result->product_links = json_decode( $result->product_links, true ) ?: [];
			$result->cart_data = json_decode( $result->cart_data, true ) ?: [];
		}

		return $results;
	}

	/**
	 * Mark incomplete order as complete
	 *
	 * @param WC_Order $order WooCommerce order object
	 * @return bool
	 */
	public function mark_incomplete_order_complete( $order ) {
		global $wpdb;
		
		try {
			
			$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
			
			// Check if table exists
			if ( $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" ) !== $table_name ) {
			
				return false;
			}
			
			$email = $order->get_billing_email();
			$phone = $order->get_billing_phone();
			$user_id = $order->get_user_id();
			
		
			$where_clauses = [];
			$where_values = [];
			
			if ( $email ) {
				$where_clauses[] = "email = %s";
				$where_values[] = $email;
			}
			
			if ( $phone ) {
				$where_clauses[] = "phone = %s";
				$where_values[] = $phone;
			}
			
			// Also check by user_id if available
			if ( $user_id > 0 ) {
				// Check if user_id column exists
				$columns = $wpdb->get_results( "SHOW COLUMNS FROM $table_name LIKE 'user_id'" );
				if ( ! empty( $columns ) ) {
					$where_clauses[] = "user_id = %d";
					$where_values[] = $user_id;
				}
			}
			
			if ( empty( $where_clauses ) ) {
				
				return false;
			}
			
			$where_sql = '(' . implode( ' OR ', $where_clauses ) . ") AND status = 'incomplete'";
			
			// Check if order_id column exists before using it
			$columns = $wpdb->get_results( "SHOW COLUMNS FROM $table_name LIKE 'order_id'" );
			if ( ! empty( $columns ) ) {
				$query = "UPDATE {$table_name} SET status = 'completed', order_id = %d WHERE {$where_sql}";
				$where_values = array_merge( [ $order->get_id() ], $where_values );
			} else {
				$query = "UPDATE {$table_name} SET status = 'completed' WHERE {$where_sql}";
			}
			
			$result = $wpdb->query( $wpdb->prepare( $query, $where_values ) );

			return $result !== false;
			
		} catch ( Exception $e ) {
		
			return false;
		}
	}

	/**
	 * Get incomplete orders analytics
	 *
	 * @return array
	 */
	public function get_incomplete_orders_analytics() {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		// Get date ranges
		$today = date( 'Y-m-d' );
		$week_start = date( 'Y-m-d', strtotime( '-7 days' ) );
		$month_start = date( 'Y-m-d', strtotime( '-30 days' ) );
		
		// Basic stats
		$stats = [
			'total_incomplete' => 0,
			'total_value' => 0,
			'today_incomplete' => 0,
			'week_incomplete' => 0,
			'month_incomplete' => 0,
			'recovery_rate' => 0,
		];
		
		// Get total incomplete orders
		$stats['total_incomplete'] = $wpdb->get_var(
			"SELECT COUNT(*) FROM {$table_name} WHERE status = 'incomplete'"
		);
		
		// Get total value of incomplete orders
		$stats['total_value'] = $wpdb->get_var(
			"SELECT SUM(total_amount) FROM {$table_name} WHERE status = 'incomplete'"
		) ?: 0;
		
		// Get today's incomplete orders
		$stats['today_incomplete'] = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table_name} WHERE status = 'incomplete' AND DATE(created_at) = %s",
			$today
		) );
		
		// Get week's incomplete orders
		$stats['week_incomplete'] = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table_name} WHERE status = 'incomplete' AND DATE(created_at) >= %s",
			$week_start
		) );
		
		// Get month's incomplete orders
		$stats['month_incomplete'] = $wpdb->get_var( $wpdb->prepare(
			"SELECT COUNT(*) FROM {$table_name} WHERE status = 'incomplete' AND DATE(created_at) >= %s",
			$month_start
		) );
		
		// Calculate recovery rate
		$total_orders = $wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" );
		$completed_orders = $wpdb->get_var( "SELECT COUNT(*) FROM {$table_name} WHERE status = 'completed'" );
		
		if ( $total_orders > 0 ) {
			$stats['recovery_rate'] = round( ( $completed_orders / $total_orders ) * 100, 2 );
		}
		
		// Get most abandoned products
		$most_abandoned = $this->get_most_abandoned_products();
		
		// Get daily trends
		$daily_trends = $this->get_incomplete_orders_daily_trends();
		
		return [
			'stats' => $stats,
			'most_abandoned_products' => $most_abandoned,
			'daily_trends' => $daily_trends,
		];
	}

	/**
	 * Get most abandoned products
	 *
	 * @param int $limit Number of products to return
	 * @return array
	 */
	public function get_most_abandoned_products( $limit = 10 ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		// Get all incomplete orders from last 30 days
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT product_names, product_ids, total_amount FROM {$table_name} 
			WHERE status = 'incomplete' AND DATE(created_at) >= %s",
			date( 'Y-m-d', strtotime( '-30 days' ) )
		) );
		
		$product_stats = [];
		
		foreach ( $results as $result ) {
			$product_names = json_decode( $result->product_names, true ) ?: [];
			$product_ids = json_decode( $result->product_ids, true ) ?: [];
			
			foreach ( $product_names as $index => $product_name ) {
				$product_id = isset( $product_ids[$index] ) ? $product_ids[$index] : 0;
				
				if ( ! isset( $product_stats[$product_name] ) ) {
					$product_stats[$product_name] = [
						'name' => $product_name,
						'id' => $product_id,
						'abandonment_count' => 0,
						'total_value' => 0,
					];
				}
				
				$product_stats[$product_name]['abandonment_count']++;
				$product_stats[$product_name]['total_value'] += floatval( $result->total_amount );
			}
		}
		
		// Sort by abandonment count
		uasort( $product_stats, function( $a, $b ) {
			return $b['abandonment_count'] - $a['abandonment_count'];
		} );
		
		return array_slice( array_values( $product_stats ), 0, $limit );
	}

	/**
	 * Get incomplete orders daily trends
	 *
	 * @param int $days Number of days to get trends for
	 * @return array
	 */
	public function get_incomplete_orders_daily_trends( $days = 14 ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		$results = $wpdb->get_results( $wpdb->prepare(
			"SELECT 
				DATE(created_at) as date,
				COUNT(*) as incomplete_count,
				SUM(total_amount) as total_value
			FROM {$table_name} 
			WHERE DATE(created_at) >= %s
			GROUP BY DATE(created_at)
			ORDER BY date ASC",
			date( 'Y-m-d', strtotime( "-{$days} days" ) )
		) );
		
		// Fill in missing dates with zero values
		$trends = [];
		for ( $i = $days - 1; $i >= 0; $i-- ) {
			$date = date( 'Y-m-d', strtotime( "-{$i} days" ) );
			$trends[$date] = [
				'date' => $date,
				'incomplete_count' => 0,
				'total_value' => 0,
			];
		}
		
		// Fill in actual data
		foreach ( $results as $result ) {
			if ( isset( $trends[$result->date] ) ) {
				$trends[$result->date]['incomplete_count'] = intval( $result->incomplete_count );
				$trends[$result->date]['total_value'] = floatval( $result->total_value );
			}
		}
		
		return array_values( $trends );
	}

	/**
	 * Mark incomplete order as contacted
	 *
	 * @param int $order_id Incomplete order ID
	 * @return bool
	 */
	public function mark_incomplete_order_contacted( $order_id ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		$result = $wpdb->update(
			$table_name,
			[ 'status' => 'contacted' ],
			[ 'id' => $order_id ],
			[ '%s' ],
			[ '%d' ]
		);
		
		return $result !== false;
	}

	/**
	 * Schedule incomplete order notification
	 *
	 * @param int $order_id Incomplete order ID
	 */
	private function schedule_incomplete_order_notification( $order_id ) {
		// Get delay from settings
		$schedules_int = get_option( 'wpnts_schedules_interval_woocommerce_settings' );
		$schedules_interval = json_decode( $schedules_int );
		$delay_minutes = $schedules_interval->abandonment_delay_minutes ?? 5;
		
		// Schedule the notification
		wp_schedule_single_event(
			time() + ( $delay_minutes * 60 ),
			'wpnts_send_incomplete_order_notification',
			[ $order_id ]
		);
	}

	/**
	 * Save cart interaction data
	 *
	 * @param string $session_key Session key
	 * @param array $interaction Interaction data
	 * @return int|false Insert ID on success, false on failure
	 */
	public function save_cart_interaction( $session_key, $interaction ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_cart_interactions';
		
		// Create table if it doesn't exist
		$this->create_cart_interactions_table();
		
		$result = $wpdb->insert(
			$table_name,
			[
				'session_key' => $session_key,
				'interaction_type' => $interaction['type'] ?? '',
				'product_name' => $interaction['product'] ?? '',
				'quantity' => $interaction['quantity'] ?? 0,
				'value' => $interaction['value'] ?? 0,
				'interaction_data' => json_encode( $interaction ),
				'created_at' => current_time( 'mysql' ),
			],
			[
				'%s', // session_key
				'%s', // interaction_type
				'%s', // product_name
				'%d', // quantity
				'%f', // value
				'%s', // interaction_data
				'%s', // created_at
			]
		);
		
		return $result ? $wpdb->insert_id : false;
	}

	/**
	 * Create cart interactions table
	 */
	private function create_cart_interactions_table() {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_cart_interactions';
		
		$charset_collate = $wpdb->get_charset_collate();
		
		$sql = "CREATE TABLE IF NOT EXISTS $table_name (
			id int(11) NOT NULL AUTO_INCREMENT,
			session_key varchar(255) NOT NULL,
			interaction_type varchar(50) NOT NULL,
			product_name varchar(255) DEFAULT '',
			quantity int(11) DEFAULT 0,
			value decimal(10,2) DEFAULT 0.00,
			interaction_data longtext,
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			PRIMARY KEY (id),
			KEY session_key (session_key),
			KEY interaction_type (interaction_type),
			KEY created_at (created_at)
		) $charset_collate;";
		
		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $sql );
	}

	/**
	 * Get cart interactions analytics
	 *
	 * @param array $filters Optional filters
	 * @return array
	 */
	public function get_cart_interactions_analytics( $filters = [] ) {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_cart_interactions';
		
		// Get interaction type counts
		$interaction_counts = $wpdb->get_results(
			"SELECT interaction_type, COUNT(*) as count 
			 FROM $table_name 
			 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
			 GROUP BY interaction_type
			 ORDER BY count DESC"
		);
		
		// Get most interacted products
		$product_interactions = $wpdb->get_results(
			"SELECT product_name, interaction_type, COUNT(*) as count
			 FROM $table_name 
			 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
			 AND product_name != ''
			 GROUP BY product_name, interaction_type
			 ORDER BY count DESC
			 LIMIT 20"
		);
		
		// Get daily interaction trends
		$daily_trends = $wpdb->get_results(
			"SELECT DATE(created_at) as date, interaction_type, COUNT(*) as count
			 FROM $table_name 
			 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
			 GROUP BY DATE(created_at), interaction_type
			 ORDER BY date DESC"
		);
		
		return [
			'interaction_counts' => $interaction_counts,
			'product_interactions' => $product_interactions,
			'daily_trends' => $daily_trends,
		];
	}

	/**
	 * Clean up old incomplete orders
	 */
	public function cleanup_old_incomplete_orders() {
		global $wpdb;
		
		$table_name = $wpdb->prefix . 'wpnts_incomplete_orders';
		
		// Get cleanup days from settings
		$schedules_int = get_option( 'wpnts_schedules_interval_woocommerce_settings' );
		$schedules_interval = json_decode( $schedules_int );
		$cleanup_days = $schedules_interval->cleanup_incomplete_orders_days ?? 30;
		
		$cleanup_date = date( 'Y-m-d', strtotime( "-{$cleanup_days} days" ) );
		
		$wpdb->query( $wpdb->prepare(
			"DELETE FROM {$table_name} WHERE DATE(created_at) < %s AND status = 'incomplete'",
			$cleanup_date
		) );
	}



	/**
	 * Check if HPOS (High-Performance Order Storage) is enabled
	 *
	 * @return bool
	 */
	public function is_hpos_enabled() {
		return class_exists( 'Automattic\WooCommerce\Utilities\OrderUtil' ) && 
			   \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
	}

	/**
	 * Get orders with HPOS compatibility
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	public function get_orders( $args = [] ) {
		$defaults = [
			'limit' => 20,
			'offset' => 0,
			'status' => 'any',
			'orderby' => 'date',
			'order' => 'DESC',
			'meta_query' => [],
			'date_query' => [],
			'search' => '',
		];

		$args = wp_parse_args( $args, $defaults );

		// Use WooCommerce's order query for better compatibility
		if ( function_exists( 'wc_get_orders' ) ) {
			return $this->get_orders_via_wc( $args );
		}

		// Fallback to direct database query
		return $this->get_orders_via_db( $args );
	}

	/**
	 * Get orders using WooCommerce functions
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_orders_via_wc( $args ) {
		$wc_args = [
			'limit' => $args['limit'],
			'offset' => $args['offset'],
			'orderby' => $args['orderby'],
			'order' => $args['order'],
			'return' => 'objects',
		];

		// Handle status
		if ( $args['status'] !== 'any' ) {
			$wc_args['status'] = $args['status'];
		}

		// Handle search
		if ( ! empty( $args['search'] ) ) {
			$wc_args['search'] = $args['search'];
		}

		// Handle date query
		if ( ! empty( $args['date_query'] ) ) {
			$wc_args['date_query'] = $args['date_query'];
		}

		// Handle meta query
		if ( ! empty( $args['meta_query'] ) ) {
			$wc_args['meta_query'] = $args['meta_query'];
		}

		$orders = wc_get_orders( $wc_args );
		$formatted_orders = [];

		foreach ( $orders as $order ) {
			$formatted_orders[] = $this->format_order_data( $order );
		}

		return $formatted_orders;
	}

	/**
	 * Get orders via direct database query
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_orders_via_db( $args ) {
		if ( $this->is_hpos_enabled() ) {
			return $this->get_orders_hpos( $args );
		} else {
			return $this->get_orders_legacy( $args );
		}
	}

	/**
	 * Get orders from HPOS tables
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_orders_hpos( $args ) {
		$cache_key = 'orders_hpos_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$orders_table = $this->wpdb->prefix . 'wc_orders';
		$meta_table = $this->wpdb->prefix . 'wc_orders_meta';

		$sql = "SELECT o.*, 
				GROUP_CONCAT(CONCAT(om.meta_key, ':', om.meta_value) SEPARATOR '||') as order_meta
				FROM {$orders_table} o
				LEFT JOIN {$meta_table} om ON o.id = om.order_id";

		$where_conditions = [ "o.type = 'shop_order'" ];
		$sql_params = [];

		// Add status condition
		if ( $args['status'] !== 'any' ) {
			$where_conditions[] = "o.status = %s";
			$sql_params[] = 'wc-' . $args['status'];
		}

		// Add search condition
		if ( ! empty( $args['search'] ) ) {
			$where_conditions[] = "(o.id LIKE %s OR om.meta_value LIKE %s)";
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
		}

		// Add date conditions
		if ( ! empty( $args['date_query'] ) ) {
			$date_conditions = $this->build_date_query( $args['date_query'], 'o.date_created_gmt' );
			if ( $date_conditions ) {
				$where_conditions[] = $date_conditions;
			}
		}

		if ( ! empty( $where_conditions ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where_conditions );
		}

		$sql .= ' GROUP BY o.id';
		$sql .= ' ORDER BY o.' . $args['orderby'] . ' ' . $args['order'];
		$sql .= ' LIMIT %d OFFSET %d';

		$sql_params[] = $args['limit'];
		$sql_params[] = $args['offset'];

		$prepared_sql = $this->wpdb->prepare( $sql, $sql_params );
		$results = $this->wpdb->get_results( $prepared_sql, ARRAY_A );

		$orders = [];
		foreach ( $results as $row ) {
			$orders[] = $this->format_hpos_order( $row );
		}

		$this->set_cache( $cache_key, $orders );
		return $orders;
	}

	/**
	 * Get orders from legacy post tables
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_orders_legacy( $args ) {
		$cache_key = 'orders_legacy_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$posts_table = $this->wpdb->posts;
		$meta_table = $this->wpdb->postmeta;

		$sql = "SELECT p.*, 
				GROUP_CONCAT(CONCAT(pm.meta_key, ':', pm.meta_value) SEPARATOR '||') as post_meta
				FROM {$posts_table} p
				LEFT JOIN {$meta_table} pm ON p.ID = pm.post_id";

		$where_conditions = [ "p.post_type = 'shop_order'" ];
		$sql_params = [];

		// Add status condition
		if ( $args['status'] !== 'any' ) {
			$where_conditions[] = "p.post_status = %s";
			$sql_params[] = 'wc-' . $args['status'];
		}

		// Add search condition
		if ( ! empty( $args['search'] ) ) {
			$where_conditions[] = "(p.ID LIKE %s OR p.post_title LIKE %s OR pm.meta_value LIKE %s)";
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
		}

		// Add date conditions
		if ( ! empty( $args['date_query'] ) ) {
			$date_conditions = $this->build_date_query( $args['date_query'], 'p.post_date' );
			if ( $date_conditions ) {
				$where_conditions[] = $date_conditions;
			}
		}

		if ( ! empty( $where_conditions ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where_conditions );
		}

		$sql .= ' GROUP BY p.ID';
		$sql .= ' ORDER BY p.post_' . $args['orderby'] . ' ' . $args['order'];
		$sql .= ' LIMIT %d OFFSET %d';

		$sql_params[] = $args['limit'];
		$sql_params[] = $args['offset'];

		$prepared_sql = $this->wpdb->prepare( $sql, $sql_params );
		$results = $this->wpdb->get_results( $prepared_sql, ARRAY_A );

		$orders = [];
		foreach ( $results as $row ) {
			$orders[] = $this->format_legacy_order( $row );
		}

		$this->set_cache( $cache_key, $orders );
		return $orders;
	}

	/**
	 * Get products with proper indexing
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	public function get_products( $args = [] ) {
		$defaults = [
			'limit' => 20,
			'offset' => 0,
			'status' => 'publish',
			'orderby' => 'date',
			'order' => 'DESC',
			'meta_query' => [],
			'search' => '',
			'include_variations' => false,
		];

		$args = wp_parse_args( $args, $defaults );

		// Use WooCommerce's product query for better compatibility
		if ( function_exists( 'wc_get_products' ) ) {
			return $this->get_products_via_wc( $args );
		}

		// Fallback to direct database query
		return $this->get_products_via_db( $args );
	}

	/**
	 * Get products using WooCommerce functions
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_products_via_wc( $args ) {
		$wc_args = [
			'limit' => $args['limit'],
			'offset' => $args['offset'],
			'orderby' => $args['orderby'],
			'order' => $args['order'],
			'status' => $args['status'],
			'return' => 'objects',
		];

		// Handle search
		if ( ! empty( $args['search'] ) ) {
			$wc_args['search'] = $args['search'];
		}

		// Handle variations
		if ( ! $args['include_variations'] ) {
			$wc_args['type'] = [ 'simple', 'grouped', 'external', 'variable' ];
		}

		// Handle meta query
		if ( ! empty( $args['meta_query'] ) ) {
			$wc_args['meta_query'] = $args['meta_query'];
		}

		$products = wc_get_products( $wc_args );
		$formatted_products = [];

		foreach ( $products as $product ) {
			$formatted_products[] = $this->format_product_data( $product );
		}

		return $formatted_products;
	}

	/**
	 * Get products via direct database query
	 *
	 * @param array $args Query arguments
	 * @return array
	 */
	private function get_products_via_db( $args ) {
		$cache_key = 'products_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$posts_table = $this->wpdb->posts;
		$meta_table = $this->wpdb->postmeta;

		$sql = "SELECT p.*, 
				GROUP_CONCAT(CONCAT(pm.meta_key, ':', pm.meta_value) SEPARATOR '||') as post_meta
				FROM {$posts_table} p
				LEFT JOIN {$meta_table} pm ON p.ID = pm.post_id";

		$where_conditions = [ "p.post_type IN ('product', 'product_variation')" ];
		$sql_params = [];

		// Add status condition
		if ( $args['status'] !== 'any' ) {
			$where_conditions[] = "p.post_status = %s";
			$sql_params[] = $args['status'];
		}

		// Handle variations
		if ( ! $args['include_variations'] ) {
			$where_conditions[] = "p.post_type = 'product'";
		}

		// Add search condition
		if ( ! empty( $args['search'] ) ) {
			$where_conditions[] = "(p.post_title LIKE %s OR p.post_content LIKE %s OR pm.meta_value LIKE %s)";
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
			$sql_params[] = '%' . $this->wpdb->esc_like( $args['search'] ) . '%';
		}

		if ( ! empty( $where_conditions ) ) {
			$sql .= ' WHERE ' . implode( ' AND ', $where_conditions );
		}

		$sql .= ' GROUP BY p.ID';
		$sql .= ' ORDER BY p.post_' . $args['orderby'] . ' ' . $args['order'];
		$sql .= ' LIMIT %d OFFSET %d';

		$sql_params[] = $args['limit'];
		$sql_params[] = $args['offset'];

		$prepared_sql = $this->wpdb->prepare( $sql, $sql_params );
		$results = $this->wpdb->get_results( $prepared_sql, ARRAY_A );

		$products = [];
		foreach ( $results as $row ) {
			$products[] = $this->format_product_row( $row );
		}

		$this->set_cache( $cache_key, $products );
		return $products;
	}

	/**
	 * Get order count
	 *
	 * @param array $args Query arguments
	 * @return int
	 */
	public function get_order_count( $args = [] ) {
		if ( $this->is_hpos_enabled() ) {
			return $this->get_order_count_hpos( $args );
		} else {
			return $this->get_order_count_legacy( $args );
		}
	}

	/**
	 * Get product count
	 *
	 * @param array $args Query arguments
	 * @return int
	 */
	public function get_product_count( $args = [] ) {
		$cache_key = 'product_count_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$where_conditions = [ "post_type = 'product'" ];
		$sql_params = [];

		if ( ! empty( $args['status'] ) && $args['status'] !== 'any' ) {
			$where_conditions[] = "post_status = %s";
			$sql_params[] = $args['status'];
		}

		if ( ! empty( $where_conditions ) ) {
			$where_clause = implode( ' AND ', $where_conditions );
			$sql = $this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->wpdb->posts} WHERE {$where_clause}",
				$sql_params
			);
		} else {
			$sql = "SELECT COUNT(*) FROM {$this->wpdb->posts}";
		}

		$count = (int) $this->wpdb->get_var( $sql );
		$this->set_cache( $cache_key, $count );
		
		return $count;
	}

	/**
	 * Update order status
	 *
	 * @param int    $order_id Order ID
	 * @param string $status New status
	 * @return bool
	 */
	public function update_order_status( $order_id, $status ) {
		if ( $this->is_hpos_enabled() ) {
			return $this->update_order_status_hpos( $order_id, $status );
		} else {
			return $this->update_order_status_legacy( $order_id, $status );
		}
	}

	/**
	 * Update product stock
	 *
	 * @param int $product_id Product ID
	 * @param int $stock_quantity Stock quantity
	 * @return bool
	 */
	public function update_product_stock( $product_id, $stock_quantity ) {
		$result = update_post_meta( $product_id, '_stock', $stock_quantity );
		
		// Clear cache
		$this->clear_cache_by_pattern( 'products_' );
		
		return $result !== false;
	}

	/**
	 * Format order data from WooCommerce object
	 *
	 * @param \WC_Order $order Order object
	 * @return array
	 */
	private function format_order_data( $order ) {
		return [
			'id' => $order->get_id(),
			'status' => $order->get_status(),
			'date_created' => $order->get_date_created()->date( 'Y-m-d H:i:s' ),
			'total' => $order->get_total(),
			'currency' => $order->get_currency(),
			'customer_id' => $order->get_customer_id(),
			'billing_email' => $order->get_billing_email(),
			'billing_first_name' => $order->get_billing_first_name(),
			'billing_last_name' => $order->get_billing_last_name(),
			'items' => $this->get_order_items( $order ),
			'meta_data' => $this->get_order_meta( $order ),
		];
	}

	/**
	 * Format product data from WooCommerce object
	 *
	 * @param \WC_Product $product Product object
	 * @return array
	 */
	private function format_product_data( $product ) {
		return [
			'id' => $product->get_id(),
			'name' => $product->get_name(),
			'sku' => $product->get_sku(),
			'price' => $product->get_price(),
			'regular_price' => $product->get_regular_price(),
			'sale_price' => $product->get_sale_price(),
			'stock_quantity' => $product->get_stock_quantity(),
			'stock_status' => $product->get_stock_status(),
			'manage_stock' => $product->get_manage_stock(),
			'type' => $product->get_type(),
			'status' => $product->get_status(),
			'date_created' => $product->get_date_created()->date( 'Y-m-d H:i:s' ),
			'meta_data' => $this->get_product_meta( $product ),
		];
	}

	/**
	 * Get order items
	 *
	 * @param \WC_Order $order Order object
	 * @return array
	 */
	private function get_order_items( $order ) {
		$items = [];
		foreach ( $order->get_items() as $item ) {
			$items[] = [
				'id' => $item->get_id(),
				'name' => $item->get_name(),
				'product_id' => $item->get_product_id(),
				'variation_id' => $item->get_variation_id(),
				'quantity' => $item->get_quantity(),
				'total' => $item->get_total(),
			];
		}
		return $items;
	}

	/**
	 * Get order meta data
	 *
	 * @param \WC_Order $order Order object
	 * @return array
	 */
	private function get_order_meta( $order ) {
		$meta = [];
		foreach ( $order->get_meta_data() as $meta_data ) {
			$data = $meta_data->get_data();
			$meta[ $data['key'] ] = $data['value'];
		}
		return $meta;
	}

	/**
	 * Get product meta data
	 *
	 * @param \WC_Product $product Product object
	 * @return array
	 */
	private function get_product_meta( $product ) {
		$meta = [];
		foreach ( $product->get_meta_data() as $meta_data ) {
			$data = $meta_data->get_data();
			$meta[ $data['key'] ] = $data['value'];
		}
		return $meta;
	}

	/**
	 * Build date query conditions
	 *
	 * @param array  $date_query Date query parameters
	 * @param string $column Column name
	 * @return string
	 */
	private function build_date_query( $date_query, $column ) {
		$conditions = [];

		if ( isset( $date_query['after'] ) ) {
			$conditions[] = $this->wpdb->prepare( "$column >= %s", $date_query['after'] );
		}

		if ( isset( $date_query['before'] ) ) {
			$conditions[] = $this->wpdb->prepare( "$column <= %s", $date_query['before'] );
		}

		return implode( ' AND ', $conditions );
	}

	/**
	 * Format HPOS order data
	 *
	 * @param array $row Database row
	 * @return array
	 */
	private function format_hpos_order( $row ) {
		$meta = $this->parse_meta_string( $row['order_meta'] ?? '' );
		
		return [
			'id' => $row['id'],
			'status' => str_replace( 'wc-', '', $row['status'] ),
			'date_created' => $row['date_created_gmt'],
			'total' => $row['total_amount'] ?? 0,
			'currency' => $row['currency'] ?? 'USD',
			'customer_id' => $row['customer_id'] ?? 0,
			'billing_email' => $meta['_billing_email'] ?? '',
			'billing_first_name' => $meta['_billing_first_name'] ?? '',
			'billing_last_name' => $meta['_billing_last_name'] ?? '',
			'meta_data' => $meta,
		];
	}

	/**
	 * Format legacy order data
	 *
	 * @param array $row Database row
	 * @return array
	 */
	private function format_legacy_order( $row ) {
		$meta = $this->parse_meta_string( $row['post_meta'] ?? '' );
		
		return [
			'id' => $row['ID'],
			'status' => str_replace( 'wc-', '', $row['post_status'] ),
			'date_created' => $row['post_date'],
			'total' => $meta['_order_total'] ?? 0,
			'currency' => $meta['_order_currency'] ?? 'USD',
			'customer_id' => $meta['_customer_user'] ?? 0,
			'billing_email' => $meta['_billing_email'] ?? '',
			'billing_first_name' => $meta['_billing_first_name'] ?? '',
			'billing_last_name' => $meta['_billing_last_name'] ?? '',
			'meta_data' => $meta,
		];
	}

	/**
	 * Format product row data
	 *
	 * @param array $row Database row
	 * @return array
	 */
	private function format_product_row( $row ) {
		$meta = $this->parse_meta_string( $row['post_meta'] ?? '' );
		
		return [
			'id' => $row['ID'],
			'name' => $row['post_title'],
			'sku' => $meta['_sku'] ?? '',
			'price' => $meta['_price'] ?? 0,
			'regular_price' => $meta['_regular_price'] ?? 0,
			'sale_price' => $meta['_sale_price'] ?? '',
			'stock_quantity' => $meta['_stock'] ?? null,
			'stock_status' => $meta['_stock_status'] ?? 'instock',
			'manage_stock' => $meta['_manage_stock'] ?? 'no',
			'type' => $meta['_product_type'] ?? 'simple',
			'status' => $row['post_status'],
			'date_created' => $row['post_date'],
			'meta_data' => $meta,
		];
	}

	/**
	 * Parse meta string into array
	 *
	 * @param string $meta_string Meta string
	 * @return array
	 */
	private function parse_meta_string( $meta_string ) {
		$meta = [];
		if ( empty( $meta_string ) ) {
			return $meta;
		}

		$pairs = explode( '||', $meta_string );
		foreach ( $pairs as $pair ) {
			if ( strpos( $pair, ':' ) !== false ) {
				list( $key, $value ) = explode( ':', $pair, 2 );
				$meta[ $key ] = $value;
			}
		}

		return $meta;
	}

	/**
	 * Get order count for HPOS
	 *
	 * @param array $args Query arguments
	 * @return int
	 */
	private function get_order_count_hpos( $args ) {
		$cache_key = 'order_count_hpos_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$where_conditions = [ "type = 'shop_order'" ];
		$sql_params = [];

		if ( ! empty( $args['status'] ) && $args['status'] !== 'any' ) {
			$where_conditions[] = "status = %s";
			$sql_params[] = 'wc-' . $args['status'];
		}

		if ( ! empty( $where_conditions ) ) {
			$where_clause = implode( ' AND ', $where_conditions );
			$sql = $this->wpdb->prepare(
				"SELECT COUNT(*) FROM {$this->wpdb->prefix}wc_orders WHERE {$where_clause}",
				$sql_params
			);
		} else {
			$sql = "SELECT COUNT(*) FROM {$this->wpdb->prefix}wc_orders";
		}

		$count = (int) $this->wpdb->get_var( $sql );
		$this->set_cache( $cache_key, $count );
		
		return $count;
	}

	/**
	 * Get order count for legacy
	 *
	 * @param array $args Query arguments
	 * @return int
	 */
	private function get_order_count_legacy( $args ) {
		$cache_key = 'order_count_legacy_' . md5( serialize( $args ) );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$where_conditions = [ "post_type = 'shop_order'" ];
		$sql_params = [];

		if ( ! empty( $args['status'] ) && $args['status'] !== 'any' ) {
			$where_conditions[] = "post_status = %s";
			$sql_params[] = 'wc-' . $args['status'];
		}

		$sql = "SELECT COUNT(*) FROM {$this->wpdb->posts} WHERE " . implode( ' AND ', $where_conditions );
		
		if ( ! empty( $sql_params ) ) {
			$sql = $this->wpdb->prepare( $sql, $sql_params );
		}

		$count = (int) $this->wpdb->get_var( $sql );
		$this->set_cache( $cache_key, $count );
		
		return $count;
	}

	/**
	 * Update order status for HPOS
	 *
	 * @param int    $order_id Order ID
	 * @param string $status New status
	 * @return bool
	 */
	private function update_order_status_hpos( $order_id, $status ) {
		$result = $this->wpdb->update(
			$this->wpdb->prefix . 'wc_orders',
			[ 'status' => 'wc-' . $status ],
			[ 'id' => $order_id ],
			[ '%s' ],
			[ '%d' ]
		);

		// Clear cache
		$this->clear_cache_by_pattern( 'orders_' );
		
		return $result !== false;
	}

	/**
	 * Update order status for legacy
	 *
	 * @param int    $order_id Order ID
	 * @param string $status New status
	 * @return bool
	 */
	private function update_order_status_legacy( $order_id, $status ) {
		$result = $this->wpdb->update(
			$this->wpdb->posts,
			[ 'post_status' => 'wc-' . $status ],
			[ 'ID' => $order_id ],
			[ '%s' ],
			[ '%d' ]
		);

		// Clear cache
		$this->clear_cache_by_pattern( 'orders_' );
		
		return $result !== false;
	}

	/**
	 * Get cache value
	 *
	 * @param string $key Cache key
	 * @return mixed
	 */
	private function get_cache( $key ) {
		if ( isset( $this->cache[ $key ] ) ) {
			$data = $this->cache[ $key ];
			if ( $data['expires'] > time() ) {
				return $data['value'];
			} else {
				unset( $this->cache[ $key ] );
			}
		}
		return false;
	}

	/**
	 * Set cache value
	 *
	 * @param string $key Cache key
	 * @param mixed  $value Cache value
	 */
	private function set_cache( $key, $value ) {
		$this->cache[ $key ] = [
			'value' => $value,
			'expires' => time() + $this->cache_expiration,
		];
	}

	/**
	 * Clear cache by pattern
	 *
	 * @param string $pattern Cache key pattern
	 */
	private function clear_cache_by_pattern( $pattern ) {
		foreach ( array_keys( $this->cache ) as $key ) {
			if ( strpos( $key, $pattern ) === 0 ) {
				unset( $this->cache[ $key ] );
			}
		}
	}

	/**
	 * Clear all cache
	 */
	public function clear_cache() {
		$this->cache = [];
	}

	/**
	 * Get WooCommerce analytics data
	 *
	 * @return array
	 */
	public function get_woo_analytics_data() {
		// Check if WooCommerce is active
		if ( ! class_exists( 'WooCommerce' ) ) {
			return [
				'error' => 'WooCommerce is not active',
				'has_data' => false
			];
		}

		try {
			$analytics_data = [
				'has_data' => true,
				'last_updated' => current_time( 'mysql' ),
				'order_analytics' => $this->get_order_analytics(),
				'revenue_analytics' => $this->get_revenue_analytics(),
				'customer_analytics' => $this->get_customer_analytics(),
				'top_products' => $this->get_top_products(),
				'order_trends' => $this->get_order_trends()
			];

			// Check if we have any meaningful data
			$has_meaningful_data = (
				$analytics_data['order_analytics']['today']['total'] > 0 ||
				$analytics_data['order_analytics']['week']['total'] > 0 ||
				count( $analytics_data['top_products'] ) > 0
			);

			$analytics_data['has_data'] = $has_meaningful_data;

			return $analytics_data;

		} catch ( Exception $e ) {
			error_log( 'WooAnalytics Error: ' . $e->getMessage() );
			return [
				'error' => 'Failed to load analytics data: ' . $e->getMessage(),
				'has_data' => false
			];
		}
	}

	/**
	 * Get order analytics data
	 *
	 * @return array
	 */
	private function get_order_analytics() {
		$cache_key = 'order_analytics_' . date( 'Y-m-d-H' );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$today = date( 'Y-m-d' );
		$week_start = date( 'Y-m-d', strtotime( '-7 days' ) );
		$fifteen_days_start = date( 'Y-m-d', strtotime( '-15 days' ) );

		$analytics = [
			'today' => $this->get_orders_by_date_range( $today, $today ),
			'week' => $this->get_orders_by_date_range( $week_start, $today ),
			'fifteen_days' => $this->get_orders_by_date_range( $fifteen_days_start, $today )
		];

		$this->set_cache( $cache_key, $analytics );
		return $analytics;
	}

	/**
	 * Get revenue analytics data
	 *
	 * @return array
	 */
	private function get_revenue_analytics() {
		$cache_key = 'revenue_analytics_' . date( 'Y-m-d-H' );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$today = date( 'Y-m-d' );
		$week_start = date( 'Y-m-d', strtotime( '-7 days' ) );
		$fifteen_days_start = date( 'Y-m-d', strtotime( '-15 days' ) );

		$analytics = [
			'currency' => get_woocommerce_currency_symbol(),
			'today' => $this->get_revenue_by_date_range( $today, $today ),
			'week' => $this->get_revenue_by_date_range( $week_start, $today ),
			'fifteen_days' => $this->get_revenue_by_date_range( $fifteen_days_start, $today )
		];

		$this->set_cache( $cache_key, $analytics );
		return $analytics;
	}

	/**
	 * Get customer analytics data
	 *
	 * @return array
	 */
	private function get_customer_analytics() {
		$cache_key = 'customer_analytics_' . date( 'Y-m-d-H' );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$today = date( 'Y-m-d' );
		$week_start = date( 'Y-m-d', strtotime( '-7 days' ) );
		$fifteen_days_start = date( 'Y-m-d', strtotime( '-15 days' ) );

		$analytics = [
			'new_customers_today' => $this->get_new_customers_count( $today, $today ),
			'new_customers_week' => $this->get_new_customers_count( $week_start, $today ),
			'new_customers_fifteen_days' => $this->get_new_customers_count( $fifteen_days_start, $today ),
			'returning_customers_today' => $this->get_returning_customers_count( $today, $today ),
			'returning_customers_week' => $this->get_returning_customers_count( $week_start, $today ),
			'returning_customers_fifteen_days' => $this->get_returning_customers_count( $fifteen_days_start, $today )
		];

		$this->set_cache( $cache_key, $analytics );
		return $analytics;
	}

	/**
	 * Get top products data
	 *
	 * @return array
	 */
	private function get_top_products() {
		$cache_key = 'top_products_' . date( 'Y-m-d' );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$thirty_days_start = date( 'Y-m-d', strtotime( '-30 days' ) );
		$today = date( 'Y-m-d' );

		if ( $this->is_hpos_enabled() ) {
			$products = $this->get_top_products_hpos( $thirty_days_start, $today );
		} else {
			$products = $this->get_top_products_legacy( $thirty_days_start, $today );
		}

		$this->set_cache( $cache_key, $products );
		return $products;
	}

	/**
	 * Get order trends data
	 *
	 * @return array
	 */
	private function get_order_trends() {
		$cache_key = 'order_trends_' . date( 'Y-m-d' );
		$cached = $this->get_cache( $cache_key );
		if ( $cached !== false ) {
			return $cached;
		}

		$trends = [];
		for ( $i = 14; $i >= 0; $i-- ) {
			$date = date( 'Y-m-d', strtotime( "-{$i} days" ) );
			$day_data = $this->get_orders_by_date_range( $date, $date );
			$revenue = $this->get_revenue_by_date_range( $date, $date );

			$trends[] = [
				'date' => $date,
				'total_orders' => $day_data['total'],
				'completed' => $day_data['completed'],
				'processing' => $day_data['processing'],
				'pending' => $day_data['pending'],
				'cancelled' => $day_data['cancelled'],
				'refunded' => $day_data['refunded'],
				'total_revenue' => $revenue
			];
		}

		$this->set_cache( $cache_key, $trends );
		return $trends;
	}

	/**
	 * Get orders by date range
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return array
	 */
	private function get_orders_by_date_range( $start_date, $end_date ) {
		if ( $this->is_hpos_enabled() ) {
			return $this->get_orders_by_date_range_hpos( $start_date, $end_date );
		} else {
			return $this->get_orders_by_date_range_legacy( $start_date, $end_date );
		}
	}

	/**
	 * Get orders by date range (HPOS)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return array
	 */
	private function get_orders_by_date_range_hpos( $start_date, $end_date ) {
		$orders_table = $this->wpdb->prefix . 'wc_orders';

		$results = $this->wpdb->get_results( $this->wpdb->prepare(
			"SELECT status, COUNT(*) as count 
			 FROM {$orders_table} 
			 WHERE type = 'shop_order' 
			 AND DATE(date_created_gmt) BETWEEN %s AND %s 
			 GROUP BY status",
			$start_date,
			$end_date
		) );

		$analytics = [
			'total' => 0,
			'completed' => 0,
			'processing' => 0,
			'pending' => 0,
			'cancelled' => 0,
			'refunded' => 0,
			'on_hold' => 0
		];

		foreach ( $results as $result ) {
			$status = str_replace( 'wc-', '', $result->status );
			$count = intval( $result->count );
			
			$analytics['total'] += $count;
			
			if ( isset( $analytics[$status] ) ) {
				$analytics[$status] = $count;
			}
		}

		return $analytics;
	}

	/**
	 * Get orders by date range (Legacy)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return array
	 */
	private function get_orders_by_date_range_legacy( $start_date, $end_date ) {
		$results = $this->wpdb->get_results( $this->wpdb->prepare(
			"SELECT post_status, COUNT(*) as count 
			 FROM {$this->wpdb->posts} 
			 WHERE post_type = 'shop_order' 
			 AND DATE(post_date) BETWEEN %s AND %s 
			 GROUP BY post_status",
			$start_date,
			$end_date
		) );

		$analytics = [
			'total' => 0,
			'completed' => 0,
			'processing' => 0,
			'pending' => 0,
			'cancelled' => 0,
			'refunded' => 0,
			'on_hold' => 0
		];

		foreach ( $results as $result ) {
			$status = str_replace( 'wc-', '', $result->post_status );
			$count = intval( $result->count );
			
			$analytics['total'] += $count;
			
			if ( isset( $analytics[$status] ) ) {
				$analytics[$status] = $count;
			}
		}

		return $analytics;
	}

	/**
	 * Get revenue by date range
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return float
	 */
	private function get_revenue_by_date_range( $start_date, $end_date ) {
		if ( $this->is_hpos_enabled() ) {
			return $this->get_revenue_by_date_range_hpos( $start_date, $end_date );
		} else {
			return $this->get_revenue_by_date_range_legacy( $start_date, $end_date );
		}
	}

	/**
	 * Get revenue by date range (HPOS)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return float
	 */
	private function get_revenue_by_date_range_hpos( $start_date, $end_date ) {
		$orders_table = $this->wpdb->prefix . 'wc_orders';

		$result = $this->wpdb->get_var( $this->wpdb->prepare(
			"SELECT SUM(total_amount) 
			 FROM {$orders_table} 
			 WHERE type = 'shop_order' 
			 AND status IN ('wc-completed', 'wc-processing') 
			 AND DATE(date_created_gmt) BETWEEN %s AND %s",
			$start_date,
			$end_date
		) );

		return floatval( $result );
	}

	/**
	 * Get revenue by date range (Legacy)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return float
	 */
	private function get_revenue_by_date_range_legacy( $start_date, $end_date ) {
		$result = $this->wpdb->get_var( $this->wpdb->prepare(
			"SELECT SUM(pm.meta_value) 
			 FROM {$this->wpdb->posts} p
			 INNER JOIN {$this->wpdb->postmeta} pm ON p.ID = pm.post_id
			 WHERE p.post_type = 'shop_order' 
			 AND p.post_status IN ('wc-completed', 'wc-processing') 
			 AND pm.meta_key = '_order_total'
			 AND DATE(p.post_date) BETWEEN %s AND %s",
			$start_date,
			$end_date
		) );

		return floatval( $result );
	}

	/**
	 * Get new customers count
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return int
	 */
	private function get_new_customers_count( $start_date, $end_date ) {
		$result = $this->wpdb->get_var( $this->wpdb->prepare(
			"SELECT COUNT(DISTINCT u.ID) 
			 FROM {$this->wpdb->users} u
			 WHERE DATE(u.user_registered) BETWEEN %s AND %s
			 AND EXISTS (
				 SELECT 1 FROM {$this->wpdb->usermeta} um 
				 WHERE um.user_id = u.ID 
				 AND um.meta_key = 'wp_capabilities' 
				 AND um.meta_value LIKE %s
			 )",
			$start_date,
			$end_date,
			'%customer%'
		) );

		return intval( $result );
	}

	/**
	 * Get returning customers count
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return int
	 */
	private function get_returning_customers_count( $start_date, $end_date ) {
		if ( $this->is_hpos_enabled() ) {
			$orders_table = $this->wpdb->prefix . 'wc_orders';
			$result = $this->wpdb->get_var( $this->wpdb->prepare(
				"SELECT COUNT(DISTINCT o1.customer_id) 
				 FROM {$orders_table} o1
				 WHERE o1.type = 'shop_order' 
				 AND o1.customer_id > 0
				 AND DATE(o1.date_created_gmt) BETWEEN %s AND %s
				 AND EXISTS (
					 SELECT 1 FROM {$orders_table} o2 
					 WHERE o2.customer_id = o1.customer_id 
					 AND o2.type = 'shop_order'
					 AND o2.date_created_gmt < o1.date_created_gmt
				 )",
				$start_date,
				$end_date
			) );
		} else {
			$result = $this->wpdb->get_var( $this->wpdb->prepare(
				"SELECT COUNT(DISTINCT pm1.meta_value) 
				 FROM {$this->wpdb->posts} p1
				 INNER JOIN {$this->wpdb->postmeta} pm1 ON p1.ID = pm1.post_id
				 WHERE p1.post_type = 'shop_order' 
				 AND pm1.meta_key = '_customer_user'
				 AND pm1.meta_value > 0
				 AND DATE(p1.post_date) BETWEEN %s AND %s
				 AND EXISTS (
					 SELECT 1 FROM {$this->wpdb->posts} p2
					 INNER JOIN {$this->wpdb->postmeta} pm2 ON p2.ID = pm2.post_id
					 WHERE p2.post_type = 'shop_order'
					 AND pm2.meta_key = '_customer_user'
					 AND pm2.meta_value = pm1.meta_value
					 AND p2.post_date < p1.post_date
				 )",
				$start_date,
				$end_date
			) );
		}

		return intval( $result );
	}

	/**
	 * Get top products (HPOS)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return array
	 */
	private function get_top_products_hpos( $start_date, $end_date ) {
		$orders_table = $this->wpdb->prefix . 'wc_orders';
		$order_items_table = $this->wpdb->prefix . 'woocommerce_order_items';
		$order_itemmeta_table = $this->wpdb->prefix . 'woocommerce_order_itemmeta';

		$results = $this->wpdb->get_results( $this->wpdb->prepare(
			"SELECT 
				oim_product.meta_value as product_id,
				oi.order_item_name as product_name,
				SUM(oim_qty.meta_value) as total_quantity,
				COUNT(DISTINCT oi.order_id) as order_count,
				SUM(oim_total.meta_value) as total_revenue,
				AVG(oim_total.meta_value / oim_qty.meta_value) as avg_price
			 FROM {$orders_table} o
			 INNER JOIN {$order_items_table} oi ON o.id = oi.order_id
			 INNER JOIN {$order_itemmeta_table} oim_product ON oi.order_item_id = oim_product.order_item_id
			 INNER JOIN {$order_itemmeta_table} oim_qty ON oi.order_item_id = oim_qty.order_item_id
			 INNER JOIN {$order_itemmeta_table} oim_total ON oi.order_item_id = oim_total.order_item_id
			 WHERE o.type = 'shop_order'
			 AND o.status IN ('wc-completed', 'wc-processing')
			 AND DATE(o.date_created_gmt) BETWEEN %s AND %s
			 AND oi.order_item_type = 'line_item'
			 AND oim_product.meta_key = '_product_id'
			 AND oim_qty.meta_key = '_qty'
			 AND oim_total.meta_key = '_line_total'
			 GROUP BY oim_product.meta_value, oi.order_item_name
			 ORDER BY total_revenue DESC
			 LIMIT 50",
			$start_date,
			$end_date
		) );

		return $this->format_top_products_results( $results );
	}

	/**
	 * Get top products (Legacy)
	 *
	 * @param string $start_date Start date
	 * @param string $end_date End date
	 * @return array
	 */
	private function get_top_products_legacy( $start_date, $end_date ) {
		$order_items_table = $this->wpdb->prefix . 'woocommerce_order_items';
		$order_itemmeta_table = $this->wpdb->prefix . 'woocommerce_order_itemmeta';

		$results = $this->wpdb->get_results( $this->wpdb->prepare(
			"SELECT 
				oim_product.meta_value as product_id,
				oi.order_item_name as product_name,
				SUM(oim_qty.meta_value) as total_quantity,
				COUNT(DISTINCT oi.order_id) as order_count,
				SUM(oim_total.meta_value) as total_revenue,
				AVG(oim_total.meta_value / oim_qty.meta_value) as avg_price
			 FROM {$this->wpdb->posts} p
			 INNER JOIN {$order_items_table} oi ON p.ID = oi.order_id
			 INNER JOIN {$order_itemmeta_table} oim_product ON oi.order_item_id = oim_product.order_item_id
			 INNER JOIN {$order_itemmeta_table} oim_qty ON oi.order_item_id = oim_qty.order_item_id
			 INNER JOIN {$order_itemmeta_table} oim_total ON oi.order_item_id = oim_total.order_item_id
			 WHERE p.post_type = 'shop_order'
			 AND p.post_status IN ('wc-completed', 'wc-processing')
			 AND DATE(p.post_date) BETWEEN %s AND %s
			 AND oi.order_item_type = 'line_item'
			 AND oim_product.meta_key = '_product_id'
			 AND oim_qty.meta_key = '_qty'
			 AND oim_total.meta_key = '_line_total'
			 GROUP BY oim_product.meta_value, oi.order_item_name
			 ORDER BY total_revenue DESC
			 LIMIT 50",
			$start_date,
			$end_date
		) );

		return $this->format_top_products_results( $results );
	}

	/**
	 * Format top products results
	 *
	 * @param array $results Raw database results
	 * @return array
	 */
	private function format_top_products_results( $results ) {
		$products = [];

		foreach ( $results as $result ) {
			$products[] = [
				'id' => intval( $result->product_id ),
				'name' => $result->product_name,
				'total_quantity' => intval( $result->total_quantity ),
				'order_count' => intval( $result->order_count ),
				'total_revenue' => floatval( $result->total_revenue ),
				'avg_price' => floatval( $result->avg_price )
			];
		}

		return $products;
	}

	/**
	 * Check if the pro version is installed
	 *
	 * @return bool
	 */
	public function check_pro_plugin_exists(): bool {
		return file_exists( WP_PLUGIN_DIR . '/shop-explorer-pro/shop-explorer-pro.php' );
	}

	/**
	 * Check if the pro version is active
	 *
	 * @return bool
	 */
	public function is_pro_active() {
		$is_pro_installed = class_exists('ShopExplorerPro') && $this->check_pro_plugin_exists();
		
		// Get the correct Freemius instance based on which plugin is handling it
		$freemius_instance = shopex_get_freemius_instance();
		
		// If no Freemius instance available, return false
		if ( ! $freemius_instance ) {
			return false;
		}
		
		return $freemius_instance->can_use_premium_code__premium_only() && $is_pro_installed;
	}

	
}