<?php
/**
 * Activity Guard – Real Time Notifier to Slack Rest Route
 *
 * This class is used to response and all rest route works
 *
 * @package WPNTS\Inc
 */

namespace WPNTS\Inc\Database;

use WPNTS\Inc\wpnts_nts_fs;

defined('ABSPATH') || die('Hey, what are you doing here? You silly human!');
/**
 * Update used to rest route created
 *
 * @since 1.0.0
 */
class DB {

	/**
	 * get_all use to get alls data together.
	 */
	public function get_all() {
		$response = [];
		$site_status_transient = get_transient( 'health-check-site-status-result' );

		$site_status = json_decode( $site_status_transient, true );

		if ( $site_status ) {
			$status_data = [
				'good' => $site_status['good'],
				'recommended' => $site_status['recommended'],
				'critical' => $site_status['critical'],
			];
		} else {
			$status_data = [
				'good' => '',
				'recommended' => '',
				'critical' => '',
			];
		}

		// Get the total plugin update count from the option table.
		$total_plugin_updates = get_option( 'wpnts_total_plugin_updates', 0 );
		$activated_plugins_data = get_option( 'wpnts_activated_plugins', [] );
		$deactivated_plugins_data = get_option( 'wpnts_deactivated_plugins', [] );
		$wpnts_user_login_info = get_option( 'wpnts_user_login_info', [] );

		$wpnts_user_daily_login_info = get_option( 'wpnts_user_daily_login_info', [] );
		$wpnts_user_track_failed_login = get_option( 'wpnts_user_track_failed_login', [] );

		// Get current date for filtering
		$current_date = gmdate('Y-m-d');

		// You can add more data to the response array if needed.
		$response['total_plugin_updates'] = $total_plugin_updates;

		// Check if the activated plugins list was updated in the last 24 hours.
		$current_time = time();

		// Activated plugin list
		if ( isset( $activated_plugins_data['last_updated'] ) && ( $current_time - $activated_plugins_data['last_updated'] ) < 86400 ) {
			$activated_plugins_list = isset( $activated_plugins_data['plugins'] ) ? $activated_plugins_data['plugins'] : [];
			$response['wpnts_activated_plugins'] = $activated_plugins_list;
		} else {
			// If more than 24 hours have passed, reset the list.
			$response['wpnts_activated_plugins'] = [];
		}

		// Deactivated plugin list
		if ( isset( $deactivated_plugins_data['last_updated'] ) && ( $current_time - $deactivated_plugins_data['last_updated'] ) < 86400 ) {
			$deactivated_plugins_list = isset( $deactivated_plugins_data['plugins'] ) ? $deactivated_plugins_data['plugins'] : [];
			$response['wpnts_deactivated_plugins'] = $deactivated_plugins_list;
		} else {
			// If more than 24 hours have passed, reset the list.
			$response['wpnts_deactivated_plugins'] = [];
		}

		// Clean up old login data (older than 24 hours)
		foreach ( $wpnts_user_login_info as $key => $login ) {
			if ( ( $current_time - $login['timestamp'] ) >= 86400 ) {
				unset( $wpnts_user_login_info[ $key ] );
			}
		}
		$wpnts_user_login_info = array_values( $wpnts_user_login_info );

		// Clean up old failed login data (older than 24 hours)
		foreach ( $wpnts_user_track_failed_login as $key => $login ) {
			if ( ( $current_time - $login['timestamp'] ) >= 86400 ) {
				unset( $wpnts_user_track_failed_login[ $key ] );
			}
		}
		$wpnts_user_track_failed_login = array_values( $wpnts_user_track_failed_login );

		// Filter daily login info to only include today's data
		$today_daily_login_info = array_filter($wpnts_user_daily_login_info, function($login) use ($current_date) {
			return isset($login['last_reset_day']) && $login['last_reset_day'] === $current_date;
		});

		// Prepare 7-day statistics for activity comparison
		$seven_day_stats = $this->get_seven_day_stats();

		// Logged in and logout in last 24
		$response['wpnts_user_login_info'] = $wpnts_user_login_info;
		$response['wpnts_user_daily_login_info'] = array_values($today_daily_login_info);
		$response['wpnts_user_track_failed_login'] = $wpnts_user_track_failed_login;
		$response['wpnts_site_health'] = $status_data;
		$response['seven_day_stats'] = $seven_day_stats;

		// Return the response as JSON.
		return $response;
	}

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

	/**
	 * Check if the pro version is active
	 *
	 * @return bool
	 */
	public function is_pro_active() {
		$is_pro_installed = class_exists('Notifierpro') && $this->check_pro_plugin_exists();
		
		// Get the correct Freemius instance based on which plugin is handling it
		$freemius_instance = wpnts_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;
	}

	/**
	 * Get IP blocking statistics
	 *
	 * @return array
	 */
	public function get_ip_blocking_stats() {
		$response = [
			'is_pro_active' => $this->is_pro_active(),
			'total_blocked_ips' => 0,
			'today_blocked_ips' => 0,
			'manual_blocked_ips' => 0,
			'auto_blocked_ips' => 0,
			'whitelisted_ips' => 0,
			'uptime_percentage' => 99.9, // Default uptime
		];

		// Get IP blocking settings
		$settings = get_option('wpnts_webhook_site_settings', '{}');
		$settings_array = json_decode($settings, true);

		if ($this->is_pro_active() && is_array($settings_array)) {
			// Count manual blocked IPs
			if (isset($settings_array['listofIP']) && !empty($settings_array['listofIP'])) {
				$manual_ips = array_filter(array_map('trim', explode("\n", $settings_array['listofIP'])));
				$response['manual_blocked_ips'] = count($manual_ips);
			}

			// Count auto blocked IPs
			if (isset($settings_array['autoaddedIP']) && !empty($settings_array['autoaddedIP'])) {
				$auto_ips = array_filter(array_map('trim', explode("\n", $settings_array['autoaddedIP'])));
				$response['auto_blocked_ips'] = count($auto_ips);
			}

			// Count whitelisted IPs
			if (isset($settings_array['whitelistIP']) && !empty($settings_array['whitelistIP'])) {
				$whitelist_ips = array_filter(array_map('trim', explode("\n", $settings_array['whitelistIP'])));
				$response['whitelisted_ips'] = count($whitelist_ips);
			}

			// Total blocked IPs
			$response['total_blocked_ips'] = $response['manual_blocked_ips'] + $response['auto_blocked_ips'];

			// Count today's blocked IPs from activity log
			global $wpdb;
			$table_name = $wpdb->prefix . 'wpnts_activity_log';
			$today = gmdate('Y-m-d');
			
			if ($wpdb->get_var("SHOW TABLES LIKE '$table_name'") == $table_name) {
				$today_blocks = $wpdb->get_var($wpdb->prepare(
					"SELECT COUNT(*) FROM `{$table_name}` WHERE event_type IN ('Restricted', 'Blocked') AND DATE(date) = %s",
					$today
				));
				$response['today_blocked_ips'] = intval($today_blocks);
			}
		} else {
			// Free version - show dummy data with pro indicators
			$response['total_blocked_ips'] = 0;
			$response['today_blocked_ips'] = 0;
			$response['is_pro_feature'] = true;
		}

		// Calculate uptime based on site availability
		$response['uptime_percentage'] = $this->calculate_uptime();

		return $response;
	}

	/**
	 * Calculate uptime percentage
	 *
	 * @return float
	 */
	private function calculate_uptime() {
		// Get site health data
		$site_status_transient = get_transient('health-check-site-status-result');
		$site_status = json_decode($site_status_transient, true);

		if ($site_status && isset($site_status['critical'])) {
			$critical_issues = intval($site_status['critical']);
			// Base uptime calculation on critical issues
			// 0 critical issues = 99.9% uptime
			// Each critical issue reduces uptime by 0.1%
			$uptime = max(95.0, 99.9 - ($critical_issues * 0.1));
			return round($uptime, 1);
		}

		// Default uptime if no health data available
		return 99.9;
	}


	/**
	 * Activity log
	 */
	public function get_all_activity_logs() {
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_activity_log';
		// Since there are no user inputs, we don't need prepare() for this query.
		$query = "SELECT * FROM `{$table_name}` ORDER BY date DESC";
		// Execute the query and get results.
		$results = $wpdb->get_results($query);
		return $results;
	}

	/**
	 * FormFlow
	 */
	public function formflow_status() {
		if ( is_plugin_active('simple-form/simple-form.php') ) {
			return 'active';
		} elseif ( file_exists(WP_PLUGIN_DIR . '/simple-form/simple-form.php') ) {
				// 'Installed but not activated'.
			return 'inactive';
		} else {
			// 'Not installed'.
			return 'notinstalled';
		}
	}

	/**
	 * WooCommerce
	 */
	public function woocommercewoocommerce_status() {
		if ( is_plugin_active('woocommerce/woocommerce.php') ) {
			return 'active';
		} elseif ( file_exists(WP_PLUGIN_DIR . '/woocommerce/woocommerce.php') ) {
				// 'Installed but not activated'.
			return 'inactive';
		} else {
			// 'Not installed'.
			return 'notinstalled';
		}
	}


	/**
	 * Contact Form 7
	 */
	public function cf7_status() {
		if ( is_plugin_active('contact-form-7/wp-contact-form-7.php') ) {
			return 'active';
		} elseif ( file_exists(WP_PLUGIN_DIR . '/contact-form-7/wp-contact-form-7.php') ) {
				// 'Installed but not activated'.
			return 'inactive';
		} else {
			// 'Not installed'.
			return 'notinstalled';
		}
	}


	public function debug_mode_status() {
		$response = [];
		$wp_debug = ( defined('WP_DEBUG') && WP_DEBUG === true );
		$mode = $wp_debug ? 'active' : 'inactive';
		$output = '';

		if ( $wp_debug ) {
			$debug_log = WP_CONTENT_DIR . '/debug.log';

			if ( file_exists($debug_log) ) {
				$debug_log_entries = file($debug_log, FILE_IGNORE_NEW_LINES);
				if ( empty($debug_log_entries) ) {
					$output = 'No errors found in the debug log.';
				} else {
					$output = implode("\n", $debug_log_entries);
				}
			} else {
				$output = 'Debug log file not found. No errors found yet.';
			}
		} else {
			$output = 'Debug mode not activated.';
		}

		$response['status'] = $mode;
		$response['log'] = $output;

		return $response;
	}


	public function maintenannotice_mode_status() {
		$response = [];
		$schedules_int = get_option('wpnts_schedules_maintenannotice_settings');
		$schedules_interval = json_decode($schedules_int);
		$maintenance_mode = $schedules_interval->maintenance_mode ?? 'false';

		$response['status'] = $maintenance_mode;

		return $response;
	}
	/**
	 * Global settings
	 */
	public function global_settings() {
		$response = [];
		$schedules_int = get_option('wpnts_global_api_settings');
		$schedules_interval = json_decode($schedules_int);
		$api_active = $schedules_interval->api_active ?? 'false';

		$global_interval = isset($schedules_interval->global_interval) ? (int) $schedules_interval->global_interval : 300;

		$global_webhook = $schedules_interval->global_webhook ?? '';

		$response['api_active'] = $api_active;
		$response['global_interval'] = $global_interval;
		$response['global_webhook'] = $global_webhook;

		return $response;
	}

	public function get_current_user_id() {
		$current_user_id = get_current_user_id();
		return $current_user_id;
	}

	/**
	 * All notice default and update settings
	 */
	public function notice_settings() {
		$response = [];
		// List of option names to fetch
		$option_names = array(
			'wpnts_plugin_list',
			'wpnts_default_interval',
			'wpnts_global_api_settings',
			'wpnts_schedules_interval_site_settings',
			'wpnts_schedules_interval_woocommerce_settings',
			'wpnts_schedules_interval_debuglog_settings',
			'maintenance_mode',
			'wpnts_schedules_maintenannotice_settings',
			'wpnts_webhook_site_settings',
			'wpntswebhook_pagenpost_settings',
			'wpntswebhook_media_settings',
			'wpntswebhook_theme_settings',
			'wpntswebhook_emengency_shutdown',
			'maintenance_mode_queries',
			'wpntswebhook_menu_settings',
			'wpntswebhook_widget_settings',
			'wpnts_schedules_interval_comment_settings',
			'wpnts_analytics_settings', // Added analytics settings
		);

		foreach ( $option_names as $option_name ) {
			$option_value = get_option($option_name);

			// Initialize analytics settings with defaults if they don't exist
			if ( $option_name === 'wpnts_analytics_settings' && ( $option_value === false || is_null($option_value) ) ) {
				$default_analytics_settings = array(
					'webhook' => '',
					'authorPaneltoMail' => false,
					'system_overview' => true,
					'activity_analytics' => true,
					'security_monitoring' => true,
					'user_activity' => true,
					'system_information' => true,
					'visitors_traffic_list' => true,
					'advance_system_info_panel' => false,
					'seven_day_rolling_analytics' => false
				);
				update_option('wpnts_analytics_settings', wp_json_encode($default_analytics_settings));
				$option_value = wp_json_encode($default_analytics_settings);
			}

			// Skip if the option is not found or is null (except for analytics which we just initialized)
			if ( $option_value === false || is_null($option_value) ) {
				continue;
			}

			// Initialize the response for this option
			$response[ $option_name ] = $option_value;

			// Only attempt to decode if the option value is a string
			if ( is_string($option_value) ) {
				// Attempt to decode the JSON string
				$decoded_value = json_decode($option_value, true);

				// If decoding was successful, replace the original value with the decoded value
				if ( json_last_error() === JSON_ERROR_NONE ) {
					$response[ $option_name ] = $decoded_value;
				}
			}
		}

		return $response;
	}


	/**
	 * Save visitor data to database
	 *
	 * @param array $visitor_data Array of visitor data to save.
	 * @return bool|int False on failure, number of rows affected on success.
	 */
	public function save_visitor_data( array $visitor_data ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_visitor_data';

		// Validate required fields
		if ( empty( $visitor_data['session_id'] ) || empty( $visitor_data['ip'] ) ) {
			return false;
		}

		// Prepare data for insertion
		$data = [
			'session_id'        => isset( $visitor_data['session_id'] ) ? $visitor_data['session_id'] : '',
			'ip'                => isset( $visitor_data['ip'] ) ? $visitor_data['ip'] : '',
			'ip_source'         => isset( $visitor_data['ip_source'] ) ? $visitor_data['ip_source'] : '',
			'frontend_ip'       => isset( $visitor_data['frontend_ip'] ) ? $visitor_data['frontend_ip'] : '',
			'backend_ip'        => isset( $visitor_data['backend_ip'] ) ? $visitor_data['backend_ip'] : '',
			'country'           => isset( $visitor_data['country'] ) ? $visitor_data['country'] : '',
			'city'              => isset( $visitor_data['city'] ) ? $visitor_data['city'] : '',
			'page_url'          => isset( $visitor_data['page_url'] ) ? $visitor_data['page_url'] : '',
			'referrer'          => isset( $visitor_data['referrer'] ) ? $visitor_data['referrer'] : '',
			'user_agent'        => isset( $visitor_data['user_agent'] ) ? $visitor_data['user_agent'] : '',
			'screen_resolution' => isset( $visitor_data['screen_resolution'] ) ? $visitor_data['screen_resolution'] : '',
			'language'          => isset( $visitor_data['language'] ) ? $visitor_data['language'] : '',
			'visiting_time'     => current_time( 'mysql' ),
		];

		// Define data format for wpdb
		$format = [
			'%s', // session_id
			'%s', // ip
			'%s', // ip_source
			'%s', // frontend_ip
			'%s', // backend_ip
			'%s', // country
			'%s', // city
			'%s', // page_url
			'%s', // referrer
			'%s', // user_agent
			'%s', // screen_resolution
			'%s', // language
			'%s', // visiting_time
		];

		// Insert data into database
		$result = $wpdb->insert( $table_name, $data, $format );

		// Return the result (number of rows inserted or false on failure)
		return $result;
	}



	/**
	 * Get all visitor data and count
	 */
	public function visitor_data() {
		$response = [];
		// Fetch data from the custom database table
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_visitor_data';
		// Since there are no user inputs, we don't need prepare() for this query.
		$query = "SELECT * FROM `{$table_name}` ORDER BY visiting_time DESC";
		$results = $wpdb->get_results($query, ARRAY_A);

		// Check if any results were returned
		if ( $results ) {
			// Process the data as needed
			$response['visitor_details'] = $results;
		} else {
			// No results found
			$response['message'] = 'No leads found.';
		}
		return $response;
	}

	/**
	 * Get visitor data by ID
	 */
	public function visitor_data_delete( int $id ) {
		global $wpdb;
		$table = $wpdb->prefix . 'wpnts_visitor_data';

		return $wpdb->delete( $table, [ 'id' => $id ], [ '%d' ] );
	}

	/**
	 * Truncate activity log table
	 */
	public function activity_log_truncate( $confirm ) {
		if ( $confirm === true ) {
			global $wpdb;
			$table_name = $wpdb->prefix . 'wpnts_activity_log';
			// Since there are no user inputs, we don't need prepare() for this query
			$query = "TRUNCATE TABLE `{$table_name}`";
			$result = $wpdb->query($query);

			// Check if the query was successful
			if ( $result === false ) {
				return false; // Truncate failed
			}
			// Return true indicating success
			return true;
		}
		return false; // Confirmation was not true
	}

	/**
	 * Delete individual activity log entry
	 */
	public function delete_activity_log( $log_id ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_activity_log';
		
		$result = $wpdb->delete(
			$table_name,
			array( 'id' => $log_id ),
			array( '%d' )
		);

		return $result !== false;
	}

	/**
	 * Bulk delete activity log entries
	 */
	public function bulk_delete_activity_logs( $log_ids ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_activity_log';
		
		// Sanitize IDs and create placeholders
		$log_ids = array_map('absint', $log_ids);
		$placeholders = implode(',', array_fill(0, count($log_ids), '%d'));
		
		$query = $wpdb->prepare(
			"DELETE FROM `{$table_name}` WHERE id IN ($placeholders)",
			...$log_ids
		);
		
		$result = $wpdb->query($query);
		
		return $result;
	}


	public function admin_list() {
		// Get all users with admin role.
		$admins = get_users(array(
			'role'    => 'administrator',
			'orderby' => 'user_login',
		));

		// Initialize an empty array to store admin usernames and names.
		$admin_list = array();

		// Loop through each admin.
		foreach ( $admins as $admin ) {
			// Add admin username and name to the list.
			$admin_list[] = array(
				'value' => $admin->user_login,
				'label' => $admin->display_name, // Use display name as the label.
			);
		}

		// Return the admin list as JSON.
		return wp_json_encode($admin_list);
	}

	/**
	 * Bulk delete visitor data
	 *
	 * @param array $id Array of IDs to delete.
	 * @return bool|int False on failure, number of rows deleted on success.
	 */
	public function visitor_data_bulk_delete( array $id ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'wpnts_visitor_data';

		// Ensure that $ids is not empty to avoid unnecessary queries.
		if ( empty($id) ) {
			return false;
		}

		// First check if any of these IDs exist.
		$placeholders = implode(', ', array_fill(0, count($id), '%d'));
		$check_query = $wpdb->prepare("SELECT COUNT(*) FROM `{$table_name}` WHERE id IN ($placeholders)", ...$id);
		$count = $wpdb->get_var($check_query);

		if ( $count === 0 ) {
			return false; // None of the IDs exist.
		}

		// Used IN clause to delete multiple rows with specified IDs.
		$query = $wpdb->prepare("DELETE FROM `{$table_name}` WHERE id IN ($placeholders)", ...$id);
		return $wpdb->query($query);
	}


	/**
	 * Retrieve the system information.
	 */
	public function system_info() {
		global $wpdb;
		$home_url = esc_url_raw(home_url());
		$site_url = esc_url_raw(site_url());
		$wp_content_path = defined('WP_CONTENT_DIR') ? esc_html(WP_CONTENT_DIR) : esc_html__('N/A', 'notifier-to-slack');
		$wp_path = defined('ABSPATH') ? esc_html(ABSPATH) : esc_html__('N/A', 'notifier-to-slack');
		$wp_version = get_bloginfo('version');
		$multisite = is_multisite() ? 'Yes' : 'No';
		$memory = ini_get('memory_limit');
		$memory = ! $memory || -1 === $memory ? wp_convert_hr_to_bytes(WP_MEMORY_LIMIT) : wp_convert_hr_to_bytes($memory);
		$memory = is_numeric($memory) ? size_format($memory) : 'N/A';
		$wp_debug = defined('WP_DEBUG') && WP_DEBUG ? 'Active' : 'Inactive';
		$language = get_locale();
		$os = defined('PHP_OS') ? esc_html(PHP_OS) : esc_html__('N/A', 'notifier-to-slack');
		$server_info = isset($_SERVER['SERVER_SOFTWARE']) ? esc_html(sanitize_text_field(wp_unslash($_SERVER['SERVER_SOFTWARE']))) : esc_html__('Unknown', 'notifier-to-slack');
		$php_version = phpversion();
		$post_max_size = size_format(wp_convert_hr_to_bytes(ini_get('post_max_size')));
		$time_limit = ini_get('max_execution_time');
		$mysql_version = $wpdb->db_version();
		$max_upload_size = size_format(wp_max_upload_size());
		$mbstring = extension_loaded('mbstring') ? 'Installed' : 'Not installed';
		$xml = extension_loaded('xml') ? 'Installed' : 'Not installed';
		$dom = extension_loaded('dom') ? 'Installed' : 'Not installed';

		$libxml = extension_loaded('libxml') ? ( defined('LIBXML_VERSION') && LIBXML_VERSION > 20760 ? 'Installed - Version: ' . LIBXML_DOTTED_VERSION : 'Lower version than required' ) : 'Not installed';
		$pdo = extension_loaded('pdo') ? 'Installed - PDO Drivers: ' . implode(', ', pdo_drivers()) : 'Not installed';
		$zip = class_exists('ZipArchive') ? 'Installed' : 'Not installed';
		$curl = extension_loaded('curl') ? 'Installed - Version: ' . curl_version()['version'] : 'Not installed';

		// Theme information.
		$themeObject = wp_get_theme();
		$theme_info = array(
			'Name' => esc_html($themeObject->get('Name')),
			'Version' => esc_html($themeObject->get('Version')),
			'Author' => esc_html($themeObject->get('Author')),
			'AuthorURI' => esc_html($themeObject->get('AuthorURI')),
		);

		// Active plugins information.
		$active_plugins = (array) get_option('active_plugins', array());
		if ( is_multisite() ) {
			$active_plugins = array_merge($active_plugins, array_keys(get_site_option('active_sitewide_plugins', array())));
		}
		$plugins_info = array();
		foreach ( $active_plugins as $plugin ) {
			$plugin_data = @get_plugin_data(WP_PLUGIN_DIR . '/' . $plugin);
			if ( ! empty($plugin_data['Name']) ) {
				$plugins_info[] = array(
					'Name' => ! empty($plugin_data['PluginURI']) ? '<a href="' . esc_url($plugin_data['PluginURI']) . '" title="' . esc_attr__('Visit plugin homepage', 'notifier-to-slack') . '" target="_blank">' . esc_html($plugin_data['Name']) . '</a>' : esc_html($plugin_data['Name']),
					'Author' => esc_html($plugin_data['AuthorName']),
					'Version' => esc_html($plugin_data['Version']),
					'Description' => esc_html($plugin_data['Description']),
				);
			}
		}

		// Return the information as an associative array.
		return array(
			'home_url' => $home_url,
			'site_url' => $site_url,
			'wp_content_path' => $wp_content_path,
			'wp_path' => $wp_path,
			'wp_version' => $wp_version,
			'multisite' => $multisite,
			'memory_limit' => $memory,
			'wp_debug' => $wp_debug,
			'language' => $language,
			'os' => $os,
			'server_info' => $server_info,
			'php_version' => $php_version,
			'post_max_size' => $post_max_size,
			'time_limit' => $time_limit,
			'mysql_version' => $mysql_version,
			'max_upload_size' => $max_upload_size,
			'mbstring' => $mbstring,
			'xml' => $xml,
			'dom' => $dom,
			'libxml' => $libxml,
			'pdo' => $pdo,
			'zip' => $zip,
			'curl' => $curl,
			'theme_info' => $theme_info,
			'plugins_info' => $plugins_info,

		);
	}

	/**
	 * Get core file directories
	 */
	public function get_core_file_directories() {
		return [
			'core',
			'.htaccess',
			'wp-config.php',
			'wp-includes',
			'wp-admin',
			'mu-plugins',
			'uploads',
			'plugins',
			'themes',
		];
	}

	/**
	 * Get all exclusion data.
	 */
	public function get_exclusion_option() {
		$scan_exclusions = get_option('wps_security_scan_exclusions');
		$security_scan_exclusions = maybe_unserialize($scan_exclusions);

		// Initialize default structure
		if ( ! is_array($security_scan_exclusions) ) {
			$security_scan_exclusions = [
				'paths'         => [],
				'file_patterns' => [],
				'php_functions' => [],
				'core_files'    => [],
				'plugins'       => [],
				'themes'        => [],
			];
		} else {
			foreach ( [ 'paths', 'file_patterns', 'php_functions', 'core_files', 'plugins', 'themes' ] as $key ) {
				if ( ! isset($security_scan_exclusions[ $key ]) || ! is_array($security_scan_exclusions[ $key ]) ) {
					$security_scan_exclusions[ $key ] = [];
				}
			}
		}

		// Add default plugin exclusions
		$default_plugin_exclusions = [
			'notifier-to-slack/notifier-to-slack.php',
			'notifier-to-slack-pro/notifier-to-slack-pro.php',
		];
		foreach ( $default_plugin_exclusions as $plugin ) {
			if ( ! in_array($plugin, $security_scan_exclusions['plugins']) ) {
				$security_scan_exclusions['plugins'][] = $plugin;
			}
		}

		// Handle inactive plugins with prefix
		$all_plugins = array_keys(get_plugins());
		$active_plugins = get_option('active_plugins', []);
		$inactive_plugins = array_diff($all_plugins, $active_plugins);
		foreach ( $inactive_plugins as $plugin ) {
			$prefixed_plugin = 'prefix-' . $plugin;
			if ( ! in_array($prefixed_plugin, $security_scan_exclusions['plugins']) ) {
				$security_scan_exclusions['plugins'][] = $prefixed_plugin;
			}
		}

		// Handle inactive themes with prefix
		$all_themes = array_keys(wp_get_themes());
		$active_theme = wp_get_theme()->get_stylesheet();
		$inactive_themes = array_filter($all_themes, function ( $theme ) use ( $active_theme ) {
			return $theme !== $active_theme;
		});
		foreach ( $inactive_themes as $theme ) {
			$prefixed_theme = 'prefix-' . $theme;
			if ( ! in_array($prefixed_theme, $security_scan_exclusions['themes']) ) {
				$security_scan_exclusions['themes'][] = $prefixed_theme;
			}
		}

		return $security_scan_exclusions;
	}

	// Modified helper functions to return prefixed inactive items
	public function get_installed_plugins() {
		$plugins = get_plugins();
		$active_plugins = get_option('active_plugins', []);

		return array_map(function ( $plugin ) use ( $active_plugins ) {
			return in_array($plugin, $active_plugins) ? $plugin : 'prefix-' . $plugin;
		}, array_keys($plugins));
	}

	public function get_installed_themes() {
		$themes = wp_get_themes();
		$active_theme = wp_get_theme()->get_stylesheet();

		return array_map(function ( $theme ) use ( $active_theme ) {
			return $theme === $active_theme ? $theme : 'prefix-' . $theme;
		}, array_keys($themes));
	}

	/**
	 * Generate 7-day statistics for login activities
	 *
	 * @return array
	 */
	public function get_seven_day_stats() {
		// Get all login data
		$all_login_data = get_option('wpnts_user_login_info', []);
		$all_failed_login_data = get_option('wpnts_user_track_failed_login', []);

		// Initialize stats for the last 7 days
		$stats = [];
		for ($i = 6; $i >= 0; $i--) {
			$date = date('Y-m-d', strtotime("-$i days"));
			$stats[$date] = [
				'date' => $date,
				'successful_logins' => 0,
				'failed_logins' => 0
			];
		}

		// Option 1: Count total login attempts (matches your original expectation of 14)
		// Uncomment this if you want to count all login attempts
		/*
		foreach ($all_login_data as $login) {
			if (isset($login['action']) && $login['action'] === 'login') {
				$login_date = date('Y-m-d', $login['timestamp']);
				if (isset($stats[$login_date])) {
					$stats[$login_date]['successful_logins']++;
				}
			}
		}
		*/

		// Option 2: Use wpnts_user_daily_login_info for more accurate counting
		$daily_login_data = get_option('wpnts_user_daily_login_info', []);
		$today = date('Y-m-d');
		
		foreach ($daily_login_data as $daily_login) {
			$user_date = $daily_login['last_reset_day'];
			if (isset($stats[$user_date])) {
				// Add the daily login count for this user to the stats
				$stats[$user_date]['successful_logins'] += $daily_login['daily_login_count'];
			}
		}

		// If wpnts_user_daily_login_info doesn't have data for all days, 
		// fall back to counting from wpnts_user_login_info
		foreach ($stats as $date => $stat) {
			if ($stat['successful_logins'] == 0) {
				// Count login actions for this specific date
				foreach ($all_login_data as $login) {
					if (isset($login['action']) && $login['action'] === 'login') {
						$login_date = date('Y-m-d', $login['timestamp']);
						if ($login_date === $date) {
							$stats[$date]['successful_logins']++;
						}
					}
				}
			}
		}

		// Count failed logins per day - aggregate by user and day to avoid duplicates
		$failed_logins_by_day = [];
		foreach ($all_failed_login_data as $failed_login) {
			$failed_date = $failed_login['last_reset_day']; // Use last_reset_day instead of timestamp date
			$user = $failed_login['user'];
			$key = $failed_date . '_' . $user;
			
			if (!isset($failed_logins_by_day[$key])) {
				$failed_logins_by_day[$key] = [
					'date' => $failed_date,
					'count' => $failed_login['daily_failed_count']
				];
			}
			// If there's already an entry for this user on this day, keep the higher count
			// This handles cases where the same user has multiple entries for the same day
			else {
				$failed_logins_by_day[$key]['count'] = max(
					$failed_logins_by_day[$key]['count'], 
					$failed_login['daily_failed_count']
				);
			}
		}

		// Sum up the failed login counts by day
		foreach ($failed_logins_by_day as $failed_login) {
			$date = $failed_login['date'];
			if (isset($stats[$date])) {
				$stats[$date]['failed_logins'] += $failed_login['count'];
			}
		}

		return array_values($stats);
	}

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

		global $wpdb;
		
		try {
			// Detect if HPOS is enabled
			$hpos_enabled = $this->is_hpos_enabled();
			// Get current date ranges
			$today = date('Y-m-d');
			$yesterday = date('Y-m-d', strtotime('-1 day'));
			$week_start = date('Y-m-d', strtotime('-7 days'));
			$fifteen_days_start = date('Y-m-d', strtotime('-15 days'));
			$month_start = date('Y-m-d', strtotime('-90 days')); // Expand to 3 months to capture more orders
			
			// Check if there are any orders at all using the appropriate method
			$total_orders = $this->get_total_orders_count($hpos_enabled);
			
			// Debug: Let's see all orders and their statuses
			if ($hpos_enabled) {
				$all_orders_debug = $wpdb->get_results("
					SELECT id, status, DATE(date_created_gmt) as date, total_amount
					FROM {$wpdb->prefix}wc_orders 
					WHERE type = 'shop_order'
					ORDER BY date_created_gmt DESC
					LIMIT 20
				");
				
			} else {
				$all_orders_debug = $wpdb->get_results("
					SELECT p.ID, p.post_status, DATE(p.post_date) as date, pm.meta_value as total
					FROM {$wpdb->posts} p
					LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_order_total'
					WHERE p.post_type = 'shop_order'
					ORDER BY p.post_date DESC
					LIMIT 20
				");
				
			}

			// If no orders exist, return empty data structure
			if (!$total_orders || $total_orders == 0) {
				return [
					'order_analytics' => [
						'today' => ['total' => 0, 'completed' => 0, 'processing' => 0, 'pending' => 0, 'cancelled' => 0, 'refunded' => 0],
						'week' => ['total' => 0, 'completed' => 0, 'processing' => 0, 'pending' => 0, 'cancelled' => 0, 'refunded' => 0],
						'fifteen_days' => ['total' => 0, 'completed' => 0, 'processing' => 0, 'pending' => 0, 'cancelled' => 0, 'refunded' => 0],
						'month' => ['total' => 0, 'completed' => 0, 'processing' => 0, 'pending' => 0, 'cancelled' => 0, 'refunded' => 0]
					],
					'revenue_analytics' => [
						'today' => 0, 'week' => 0, 'fifteen_days' => 0, 'month' => 0,
						'currency' => function_exists('get_woocommerce_currency_symbol') ? get_woocommerce_currency_symbol() : '$'
					],
					'customer_analytics' => [
						'new_customers_today' => 0, 'new_customers_week' => 0, 'new_customers_fifteen_days' => 0,
						'returning_customers_today' => 0, 'returning_customers_week' => 0, 'returning_customers_fifteen_days' => 0
					],
					'top_products' => [],
					'order_trends' => [],
					'has_data' => false,
					'last_updated' => current_time('mysql')
				];
			}

			// Get comprehensive analytics using the appropriate method
			$order_analytics = $this->get_comprehensive_order_analytics($today, $week_start, $fifteen_days_start, $month_start, $hpos_enabled);
			$revenue_analytics = $this->get_comprehensive_revenue_analytics($today, $week_start, $fifteen_days_start, $month_start, $hpos_enabled);
			$customer_analytics = $this->get_customer_analytics($today, $week_start, $fifteen_days_start, $hpos_enabled);
			$top_products = $this->get_comprehensive_top_products($month_start, $hpos_enabled);
			$order_trends = $this->get_order_trends($fifteen_days_start, $hpos_enabled);

			return [
				'order_analytics' => $order_analytics,
				'revenue_analytics' => $revenue_analytics,
				'customer_analytics' => $customer_analytics,
				'top_products' => $top_products ?: [],
				'order_trends' => $order_trends ?: [],
				'has_data' => true,
				'last_updated' => current_time('mysql')
			];
		} catch (Exception $e) {
			
			return [
				'error' => 'Failed to fetch analytics data: ' . $e->getMessage()
			];
		}
	}

	/**
	 * Check if WooCommerce HPOS (High-Performance Order Storage) is enabled
	 */
	private function is_hpos_enabled() {
		// Check if HPOS is enabled in WooCommerce settings
		if (function_exists('wc_get_container')) {
			try {
				$data_store = wc_get_container()->get(\Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore::class);
				return $data_store && get_option('woocommerce_custom_orders_table_enabled') === 'yes';
			} catch (Exception $e) {
				// Fallback method
			}
		}
		
		// Alternative check
		return get_option('woocommerce_custom_orders_table_enabled') === 'yes' && 
			   class_exists('\Automattic\WooCommerce\Internal\DataStores\Orders\OrdersTableDataStore');
	}

	/**
	 * Get total orders count using appropriate method
	 */
	private function get_total_orders_count($hpos_enabled) {
		global $wpdb;

		if ($hpos_enabled) {
			// HPOS method - use wc_orders table
			return $wpdb->get_var("
				SELECT COUNT(*) 
				FROM {$wpdb->prefix}wc_orders 
				WHERE type = 'shop_order'
			");
		} else {
			// Legacy method - use posts table
			return $wpdb->get_var("
				SELECT COUNT(*) 
				FROM {$wpdb->posts} 
				WHERE post_type = 'shop_order'
			");
		}
	}

	/**
	 * Get comprehensive order analytics by status and time period
	 */
	private function get_comprehensive_order_analytics($today, $week_start, $fifteen_days_start, $month_start, $hpos_enabled) {
		global $wpdb;

		$periods = [
			'today' => $today,
			'week' => $week_start,
			'fifteen_days' => $fifteen_days_start,
			'month' => $month_start
		];

		$analytics = [];

		foreach ($periods as $period_name => $start_date) {
			if ($hpos_enabled) {
				// HPOS method
				$date_condition = $period_name === 'today' 
					? "DATE(date_created_gmt) = '$start_date'"
					: "DATE(date_created_gmt) >= '$start_date'";

				$query = "
					SELECT 
						status,
						COUNT(*) as count
					FROM {$wpdb->prefix}wc_orders
					WHERE type = 'shop_order'
					AND $date_condition
					GROUP BY status
				";
				
				$order_stats = $wpdb->get_results($query);
			} else {
				// Legacy method
				$date_condition = $period_name === 'today' 
					? "DATE(post_date) = '$start_date'"
					: "DATE(post_date) >= '$start_date'";

				$query = "
					SELECT 
						post_status as status,
						COUNT(*) as count
					FROM {$wpdb->posts}
					WHERE post_type = 'shop_order'
					AND $date_condition
					GROUP BY post_status
				";
				
				$order_stats = $wpdb->get_results($query);
				
			}

			// Initialize counts
			$counts = [
				'total' => 0,
				'completed' => 0,
				'processing' => 0,
				'pending' => 0,
				'cancelled' => 0,
				'refunded' => 0,
				'on_hold' => 0,
				'failed' => 0
			];

			// Process results
			foreach ($order_stats as $stat) {
				$count = intval($stat->count); // Ensure integer
				$counts['total'] += $count;
				
				// Normalize status (HPOS uses different format)
				$status = $stat->status;
				
				// Handle both HPOS format (completed) and legacy format (wc-completed)
				$normalized_status = str_replace('wc-', '', $status);
				
				
				switch ($normalized_status) {
					case 'completed':
						$counts['completed'] = $count;
						break;
					case 'processing':
						$counts['processing'] = $count;
						break;
					case 'pending-payment':
					case 'pending':
						$counts['pending'] += $count;
						break;
					case 'cancelled':
						$counts['cancelled'] = $count;
						break;
					case 'refunded':
						$counts['refunded'] = $count;
						break;
					case 'on-hold':
						$counts['on_hold'] = $count;
						break;
					case 'failed':
						$counts['failed'] = $count;
						break;
					default:
						
						break;
				}
			}

			$analytics[$period_name] = $counts;
		}

		return $analytics;
	}

	/**
	 * Get comprehensive revenue analytics
	 */
	private function get_comprehensive_revenue_analytics($today, $week_start, $fifteen_days_start, $month_start, $hpos_enabled) {
		global $wpdb;

		$periods = [
			'today' => $today,
			'week' => $week_start,
			'fifteen_days' => $fifteen_days_start,
			'month' => $month_start
		];

		$revenue = [];

		foreach ($periods as $period_name => $start_date) {
			if ($hpos_enabled) {
				// HPOS method - let's check what fields are available
				$date_condition = $period_name === 'today' 
					? "DATE(o.date_created_gmt) = '$start_date'"
					: "DATE(o.date_created_gmt) >= '$start_date'";

				// First, let's see what fields are available in the orders table
				$sample_order = $wpdb->get_row("
					SELECT * 
					FROM {$wpdb->prefix}wc_orders o
					WHERE o.type = 'shop_order'
					AND o.status IN ('completed', 'processing')
					LIMIT 1
				");
				
				// Try different field names for total amount
				$revenue_query = "
					SELECT SUM(CAST(o.total_amount AS DECIMAL(10,2))) as total_amount_sum,
						   COUNT(*) as order_count
					FROM {$wpdb->prefix}wc_orders o
					WHERE o.type = 'shop_order'
					AND o.status IN ('wc-completed', 'wc-processing')
					AND $date_condition
				";
				
				$revenue_result = $wpdb->get_row($revenue_query);
				
				$total_revenue = $revenue_result->total_amount_sum;
			} else {
				// Legacy method
				$date_condition = $period_name === 'today' 
					? "DATE(p.post_date) = '$start_date'"
					: "DATE(p.post_date) >= '$start_date'";

				$revenue_query = "
					SELECT SUM(CAST(pm.meta_value AS DECIMAL(10,2))) as total_revenue,
						   COUNT(*) as order_count
					FROM {$wpdb->posts} p
					JOIN {$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_condition
				";
				
				$revenue_result = $wpdb->get_row($revenue_query);
				
				$total_revenue = $revenue_result->total_revenue;
			}

			$revenue[$period_name] = floatval($total_revenue ?: 0);
		}

		// Get clean currency symbol without HTML entities
		$currency_symbol = '$'; // Default fallback
		if (function_exists('get_woocommerce_currency')) {
			$currency_code = get_woocommerce_currency();
			$currency_symbol = html_entity_decode(get_woocommerce_currency_symbol($currency_code), ENT_QUOTES, 'UTF-8');
		}
		$revenue['currency'] = $currency_symbol;
		
		return $revenue;
	}

	/**
	 * Get customer analytics (new vs returning customers)
	 */
	private function get_customer_analytics($today, $week_start, $fifteen_days_start, $hpos_enabled) {
		global $wpdb;

		$periods = [
			'today' => $today,
			'week' => $week_start,
			'fifteen_days' => $fifteen_days_start
		];

		$customer_analytics = [];

		foreach ($periods as $period_name => $start_date) {
			if ($hpos_enabled) {
				// HPOS method
				$date_condition = $period_name === 'today' 
					? "DATE(o.date_created_gmt) = '$start_date'"
					: "DATE(o.date_created_gmt) >= '$start_date'";

				$customers_in_period = $wpdb->get_results("
					SELECT DISTINCT o.billing_email as customer_email
					FROM {$wpdb->prefix}wc_orders o
					WHERE o.type = 'shop_order'
					AND o.status IN ('wc-completed', 'wc-processing', 'wc-pending-payment', 'wc-on-hold')
					AND o.billing_email IS NOT NULL
					AND o.billing_email != ''
					AND $date_condition
				");
			} else {
				// Legacy method
				$date_condition = $period_name === 'today' 
					? "DATE(p.post_date) = '$start_date'"
					: "DATE(p.post_date) >= '$start_date'";

				$customers_in_period = $wpdb->get_results("
					SELECT DISTINCT pm_customer.meta_value as customer_email
					FROM {$wpdb->posts} p
					JOIN {$wpdb->postmeta} pm_customer ON p.ID = pm_customer.post_id
					WHERE p.post_type = 'shop_order'
					AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-pending-payment', 'wc-on-hold')
					AND pm_customer.meta_key = '_billing_email'
					AND $date_condition
				");
			}

			$new_customers = 0;
			$returning_customers = 0;

			foreach ($customers_in_period as $customer) {
				if ($hpos_enabled) {
					// HPOS method - check previous orders
					$previous_orders = $wpdb->get_var($wpdb->prepare("
						SELECT COUNT(*)
						FROM {$wpdb->prefix}wc_orders o
						WHERE o.type = 'shop_order'
						AND o.billing_email = %s
						AND DATE(o.date_created_gmt) < %s
					", $customer->customer_email, $start_date));
				} else {
					// Legacy method - check previous orders
					$previous_orders = $wpdb->get_var($wpdb->prepare("
						SELECT COUNT(*)
						FROM {$wpdb->posts} p
						JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
						WHERE p.post_type = 'shop_order'
						AND pm.meta_key = '_billing_email'
						AND pm.meta_value = %s
						AND DATE(p.post_date) < %s
					", $customer->customer_email, $start_date));
				}

				if ($previous_orders > 0) {
					$returning_customers++;
				} else {
					$new_customers++;
				}
			}

			$customer_analytics['new_customers_' . $period_name] = $new_customers;
			$customer_analytics['returning_customers_' . $period_name] = $returning_customers;
		}

		return $customer_analytics;
	}

	/**
	 * Get comprehensive top products
	 */
	private function get_comprehensive_top_products($month_start, $hpos_enabled, $limit = 10) {
		global $wpdb;

		if ($hpos_enabled) {
			// HPOS method
			$results = $wpdb->get_results($wpdb->prepare("
				SELECT 
					p.ID,
					p.post_title as product_name,
					SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as total_quantity,
					COUNT(DISTINCT oi.order_id) as order_count,
					AVG(CAST(pm_price.meta_value AS DECIMAL(10,2))) as avg_price,
					SUM(CAST(oim_total.meta_value AS DECIMAL(10,2))) as total_revenue
				FROM {$wpdb->prefix}woocommerce_order_items oi
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oi.order_item_id = oim_product.order_item_id
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_qty ON oi.order_item_id = oim_qty.order_item_id
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_total ON oi.order_item_id = oim_total.order_item_id
				JOIN {$wpdb->posts} p ON oim_product.meta_value = p.ID
				JOIN {$wpdb->prefix}wc_orders o ON oi.order_id = o.id
				LEFT JOIN {$wpdb->postmeta} pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
				WHERE oim_product.meta_key = '_product_id'
				AND oim_qty.meta_key = '_qty'
				AND oim_total.meta_key = '_line_total'
				AND oi.order_item_type = 'line_item'
				AND o.type = 'shop_order'
				AND o.status IN ('wc-completed', 'wc-processing')
				AND DATE(o.date_created_gmt) >= %s
				GROUP BY p.ID, p.post_title
				ORDER BY total_quantity DESC
				LIMIT %d
			", $month_start, $limit));
		} else {
			// Legacy method
			$results = $wpdb->get_results($wpdb->prepare("
				SELECT 
					p.ID,
					p.post_title as product_name,
					SUM(CAST(oim_qty.meta_value AS UNSIGNED)) as total_quantity,
					COUNT(DISTINCT oi.order_id) as order_count,
					AVG(CAST(pm_price.meta_value AS DECIMAL(10,2))) as avg_price,
					SUM(CAST(oim_total.meta_value AS DECIMAL(10,2))) as total_revenue
				FROM {$wpdb->prefix}woocommerce_order_items oi
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oi.order_item_id = oim_product.order_item_id
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_qty ON oi.order_item_id = oim_qty.order_item_id
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_total ON oi.order_item_id = oim_total.order_item_id
				JOIN {$wpdb->posts} p ON oim_product.meta_value = p.ID
				JOIN {$wpdb->posts} order_post ON oi.order_id = order_post.ID
				LEFT JOIN {$wpdb->postmeta} pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
				WHERE oim_product.meta_key = '_product_id'
				AND oim_qty.meta_key = '_qty'
				AND oim_total.meta_key = '_line_total'
				AND oi.order_item_type = 'line_item'
				AND order_post.post_type = 'shop_order'
				AND order_post.post_status IN ('wc-completed', 'wc-processing')
				AND DATE(order_post.post_date) >= %s
				GROUP BY p.ID, p.post_title
				ORDER BY total_quantity DESC
				LIMIT %d
			", $month_start, $limit));
		}

		if (!$results) {
			return [];
		}

		return array_map(function($item) {
			return [
				'id' => intval($item->ID),
				'name' => sanitize_text_field($item->product_name),
				'total_quantity' => intval($item->total_quantity ?: 0),
				'order_count' => intval($item->order_count ?: 0),
				'avg_price' => floatval($item->avg_price ?: 0),
				'total_revenue' => floatval($item->total_revenue ?: 0)
			];
		}, $results);
	}

	/**
	 * Get order trends for charts
	 */
	private function get_order_trends($fifteen_days_start, $hpos_enabled) {
		global $wpdb;

		if ($hpos_enabled) {
			// HPOS method
			$results = $wpdb->get_results($wpdb->prepare("
				SELECT 
					DATE(date_created_gmt) as date,
					status,
					COUNT(*) as count,
					SUM(CAST(total_amount AS DECIMAL(10,2))) as revenue
				FROM {$wpdb->prefix}wc_orders
				WHERE type = 'shop_order'
				AND DATE(date_created_gmt) >= %s
				GROUP BY DATE(date_created_gmt), status
				ORDER BY date ASC, status
			", $fifteen_days_start));
		} else {
			// Legacy method
			$results = $wpdb->get_results($wpdb->prepare("
				SELECT 
					DATE(post_date) as date,
					post_status as status,
					COUNT(*) as count,
					SUM(CASE WHEN pm.meta_value IS NOT NULL THEN CAST(pm.meta_value AS DECIMAL(10,2)) ELSE 0 END) as revenue
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_order_total'
				WHERE p.post_type = 'shop_order'
				AND DATE(p.post_date) >= %s
				GROUP BY DATE(post_date), post_status
				ORDER BY date ASC, post_status
			", $fifteen_days_start));
		}

		// Group by date
		$trends = [];
		foreach ($results as $result) {
			$date = $result->date;
			if (!isset($trends[$date])) {
				$trends[$date] = [
					'date' => $date,
					'total_orders' => 0,
					'completed' => 0,
					'processing' => 0,
					'pending' => 0,
					'cancelled' => 0,
					'refunded' => 0,
					'total_revenue' => 0
				];
			}

			$trends[$date]['total_orders'] += $result->count;
			
			// Normalize status (remove wc- prefix if present)
			$normalized_status = str_replace('wc-', '', $result->status);
			
			switch ($normalized_status) {
				case 'completed':
					$trends[$date]['completed'] = $result->count;
					$trends[$date]['total_revenue'] += $result->revenue;
					break;
				case 'processing':
					$trends[$date]['processing'] = $result->count;
					$trends[$date]['total_revenue'] += $result->revenue;
					break;
				case 'pending-payment':
				case 'pending':
					$trends[$date]['pending'] += $result->count;
					break;
				case 'cancelled':
					$trends[$date]['cancelled'] = $result->count;
					break;
				case 'refunded':
					$trends[$date]['refunded'] = $result->count;
					break;
			}
		}

		return array_values($trends);
	}

	/**
	 * Legacy method for backward compatibility - now redirects to comprehensive analytics
	 */
	private function get_revenue_analytics($today, $yesterday, $week_start, $month_start, $year_start) {
		global $wpdb;

		try {
			// Debug: Check what orders exist
			$all_orders = $wpdb->get_results("
				SELECT p.ID, p.post_status, p.post_date, pm.meta_value as total
				FROM {$wpdb->posts} p
				LEFT JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id AND pm.meta_key = '_order_total'
				WHERE p.post_type = 'shop_order'
				ORDER BY p.post_date DESC
				LIMIT 10
			");
		
			// Today's revenue (include more order statuses for testing)
			$today_revenue = $wpdb->get_var($wpdb->prepare("
				SELECT SUM(CAST(meta_value AS DECIMAL(10,2))) 
				FROM {$wpdb->postmeta} pm
				JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = '_order_total'
				AND p.post_type = 'shop_order'
				AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
				AND DATE(p.post_date) = %s
			", $today));

		
			// Yesterday's revenue
			$yesterday_revenue = $wpdb->get_var($wpdb->prepare("
				SELECT SUM(CAST(meta_value AS DECIMAL(10,2))) 
				FROM {$wpdb->postmeta} pm
				JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = '_order_total'
				AND p.post_type = 'shop_order'
				AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
				AND DATE(p.post_date) = %s
			", $yesterday));

			// This week's revenue
			$week_revenue = $wpdb->get_var($wpdb->prepare("
				SELECT SUM(CAST(meta_value AS DECIMAL(10,2))) 
				FROM {$wpdb->postmeta} pm
				JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = '_order_total'
				AND p.post_type = 'shop_order'
				AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
				AND DATE(p.post_date) >= %s
			", $week_start));

			// This month's revenue
			$month_revenue = $wpdb->get_var($wpdb->prepare("
				SELECT SUM(CAST(meta_value AS DECIMAL(10,2))) 
				FROM {$wpdb->postmeta} pm
				JOIN {$wpdb->posts} p ON pm.post_id = p.ID
				WHERE pm.meta_key = '_order_total'
				AND p.post_type = 'shop_order'
				AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
				AND DATE(p.post_date) >= %s
			", $month_start));

		
			// Calculate growth percentages
			$daily_growth = $yesterday_revenue > 0 ? (($today_revenue - $yesterday_revenue) / $yesterday_revenue) * 100 : 0;

			return [
				'today' => floatval($today_revenue ?: 0),
				'yesterday' => floatval($yesterday_revenue ?: 0),
				'week' => floatval($week_revenue ?: 0),
				'month' => floatval($month_revenue ?: 0),
				'daily_growth' => round($daily_growth, 2),
				'currency' => function_exists('get_woocommerce_currency_symbol') ? get_woocommerce_currency_symbol() : '$'
			];
		} catch (Exception $e) {
			return [
				'today' => 0,
				'yesterday' => 0,
				'week' => 0,
				'month' => 0,
				'daily_growth' => 0,
				'currency' => '$'
			];
		}
	}

	/**
	 * Get top selling products
	 */
	private function get_top_selling_products($month_start, $limit = 10) {
		global $wpdb;

		try {
			$results = $wpdb->get_results($wpdb->prepare("
				SELECT 
					p.ID,
					p.post_title as product_name,
					SUM(CAST(oim.meta_value AS UNSIGNED)) as total_sales,
					COUNT(oi.order_item_id) as order_count,
					AVG(CAST(pm_price.meta_value AS DECIMAL(10,2))) as avg_price
				FROM {$wpdb->prefix}woocommerce_order_items oi
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oi.order_item_id = oim.order_item_id
				JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim_product ON oi.order_item_id = oim_product.order_item_id
				JOIN {$wpdb->posts} p ON oim_product.meta_value = p.ID
				JOIN {$wpdb->posts} order_post ON oi.order_id = order_post.ID
				LEFT JOIN {$wpdb->postmeta} pm_price ON p.ID = pm_price.post_id AND pm_price.meta_key = '_price'
				WHERE oim.meta_key = '_qty'
				AND oim_product.meta_key = '_product_id'
				AND oi.order_item_type = 'line_item'
				AND order_post.post_type = 'shop_order'
				AND order_post.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
				AND DATE(order_post.post_date) >= %s
				GROUP BY p.ID, p.post_title
				ORDER BY total_sales DESC
				LIMIT %d
			", $month_start, $limit));

			if (!$results) {
				return [];
			}

			return array_map(function($item) {
				return [
					'id' => intval($item->ID),
					'name' => sanitize_text_field($item->product_name),
					'total_sales' => intval($item->total_sales ?: 0),
					'order_count' => intval($item->order_count ?: 0),
					'avg_price' => floatval($item->avg_price ?: 0)
				];
			}, $results);
		} catch (Exception $e) {
			return [];
		}
	}

	/**
	 * Get conversion trends
	 */
	private function get_conversion_trends($week_start) {
		global $wpdb;

		// Get daily data for the last 7 days
		$results = $wpdb->get_results($wpdb->prepare("
			SELECT 
				DATE(post_date) as date,
				COUNT(*) as orders,
				SUM(CAST(pm.meta_value AS DECIMAL(10,2))) as revenue
			FROM {$wpdb->posts} p
			JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
			WHERE p.post_type = 'shop_order'
			AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
			AND pm.meta_key = '_order_total'
			AND DATE(p.post_date) >= %s
			GROUP BY DATE(post_date)
			ORDER BY date ASC
		", $week_start));

		// Calculate conversion rate (assuming 100 visitors per day as baseline)
		// In a real scenario, you'd integrate with Google Analytics or similar
		return array_map(function($item) {
			$visitors = 100; // This should come from actual analytics data
			$conversion_rate = $visitors > 0 ? ($item->orders / $visitors) * 100 : 0;
			
			return [
				'date' => $item->date,
				'orders' => intval($item->orders),
				'revenue' => floatval($item->revenue),
				'conversion_rate' => round($conversion_rate, 2),
				'visitors' => $visitors
			];
		}, $results);
	}

	/**
	 * Get sales by different periods
	 */
	private function get_sales_by_period($month_start) {
		global $wpdb;

		// Weekly sales for the last month
		$weekly_sales = $wpdb->get_results($wpdb->prepare("
			SELECT 
				YEARWEEK(post_date) as week,
				COUNT(*) as orders,
				SUM(CAST(pm.meta_value AS DECIMAL(10,2))) as revenue
			FROM {$wpdb->posts} p
			JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
			WHERE p.post_type = 'shop_order'
			AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
			AND pm.meta_key = '_order_total'
			AND DATE(p.post_date) >= %s
			GROUP BY YEARWEEK(post_date)
			ORDER BY week ASC
		", $month_start));

		// Monthly sales for the last year
		$monthly_sales = $wpdb->get_results("
			SELECT 
				DATE_FORMAT(post_date, '%Y-%m') as month,
				COUNT(*) as orders,
				SUM(CAST(pm.meta_value AS DECIMAL(10,2))) as revenue
			FROM {$wpdb->posts} p
			JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
			WHERE p.post_type = 'shop_order'
			AND p.post_status IN ('wc-completed', 'wc-processing', 'wc-on-hold', 'wc-pending-payment')
			AND pm.meta_key = '_order_total'
			AND post_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
			GROUP BY DATE_FORMAT(post_date, '%Y-%m')
			ORDER BY month ASC
		");

		return [
			'weekly' => array_map(function($item) {
				return [
					'period' => $item->week,
					'orders' => intval($item->orders),
					'revenue' => floatval($item->revenue)
				];
			}, $weekly_sales),
			'monthly' => array_map(function($item) {
				return [
					'period' => $item->month,
					'orders' => intval($item->orders),
					'revenue' => floatval($item->revenue)
				];
			}, $monthly_sales)
		];
	}

	/**
	 * Get 7-day rolling analytics data
	 *
	 * @return array
	 */
	public function get_seven_day_rolling_analytics() {
		// Clean up old data first (older than 7 days)
		$this->cleanup_seven_day_analytics_data();

		// Use the same data source as the regular login tracking for consistency
		$all_login_data = get_option('wpnts_user_login_info', []);
		$all_failed_login_data = get_option('wpnts_user_track_failed_login', []);
		$daily_login_data = get_option('wpnts_user_daily_login_info', []);

		// Initialize stats for the last 7 days
		$analytics_data = [];
		$current_date = current_time('Y-m-d');
		
		for ($i = 6; $i >= 0; $i--) {
			$date = date('Y-m-d', strtotime($current_date . " -$i days"));
			
			// Initialize day data
			$day_data = [
				'date' => $date,
				'successful_logins' => 0,
				'failed_logins' => 0,
				'logouts' => 0,
				'unique_users' => 0,
				'success_rate' => 0
			];

			// Count successful logins using the same logic as get_seven_day_stats
			// First try to use daily login data
			foreach ($daily_login_data as $daily_login) {
				if (isset($daily_login['last_reset_day']) && $daily_login['last_reset_day'] === $date) {
					$login_count = isset($daily_login['daily_login_count']) ? $daily_login['daily_login_count'] : 0;
					$day_data['successful_logins'] += $login_count;
				}
			}

			// If no daily login data for this date, fall back to counting from wpnts_user_login_info
			if ($day_data['successful_logins'] == 0) {
				foreach ($all_login_data as $login) {
					if (isset($login['action']) && $login['action'] === 'login') {
						$login_date = date('Y-m-d', $login['timestamp']);
						if ($login_date === $date) {
							$day_data['successful_logins']++;
						}
					}
				}
			}

			// Count failed logins
			$failed_logins_by_user = [];
			foreach ($all_failed_login_data as $failed_login) {
				if (isset($failed_login['last_reset_day']) && $failed_login['last_reset_day'] === $date) {
					$user = isset($failed_login['user']) ? $failed_login['user'] : 'Unknown';
					$failed_count = isset($failed_login['daily_failed_count']) ? $failed_login['daily_failed_count'] : 1;
					
					if (!isset($failed_logins_by_user[$user])) {
						$failed_logins_by_user[$user] = $failed_count;
					} else {
						$failed_logins_by_user[$user] = max($failed_logins_by_user[$user], $failed_count);
					}
				}
			}
			$day_data['failed_logins'] = array_sum($failed_logins_by_user);

			// Count logouts from login data
			foreach ($all_login_data as $login) {
				if (isset($login['action']) && $login['action'] === 'logout') {
					$login_date = date('Y-m-d', $login['timestamp']);
					if ($login_date === $date) {
						$day_data['logouts']++;
					}
				}
			}

			// Count unique users (from both successful logins and logouts)
			$unique_users = [];
			foreach ($all_login_data as $login) {
				$login_date = date('Y-m-d', $login['timestamp']);
				if ($login_date === $date && isset($login['user'])) {
					$unique_users[$login['user']] = true;
				}
			}
			$day_data['unique_users'] = count($unique_users);

			// Calculate success rate
			$total_attempts = $day_data['successful_logins'] + $day_data['failed_logins'];
			$day_data['success_rate'] = $total_attempts > 0 ? 
				round(($day_data['successful_logins'] / $total_attempts) * 100, 1) : 0;

			$analytics_data[] = $day_data;
		}

		return $analytics_data;
	}

	/**
	 * Get detailed analytics for a specific day
	 *
	 * @param string $date The date in Y-m-d format
	 * @return array
	 */
	public function get_seven_day_detailed_analytics($date) {
		// Validate date format
		if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
			return ['error' => 'Invalid date format'];
		}

		// Use the same data source as the regular login tracking
		$all_login_data = get_option('wpnts_user_login_info', []);
		$all_failed_login_data = get_option('wpnts_user_track_failed_login', []);
		$daily_login_data = get_option('wpnts_user_daily_login_info', []);

		// Process login details for the specific date
		$processed_login_details = [];
		$login_counts_by_user = [];
		
		// First, get login counts from daily login data
		foreach ($daily_login_data as $daily_login) {
			if (isset($daily_login['last_reset_day']) && $daily_login['last_reset_day'] === $date) {
				$user = isset($daily_login['user']) ? $daily_login['user'] : 'Unknown';
				$login_count = isset($daily_login['daily_login_count']) ? $daily_login['daily_login_count'] : 0;
				$login_counts_by_user[$user] = $login_count;
			}
		}

		// Then get individual login events from login info
		foreach ($all_login_data as $login) {
			if (isset($login['action']) && $login['action'] === 'login') {
				$login_date = date('Y-m-d', $login['timestamp']);
				if ($login_date === $date) {
					$processed_login_details[] = [
						'user' => isset($login['user']) ? $login['user'] : 'Unknown',
						'ip' => isset($login['ip']) ? $login['ip'] : 'Not detected',
						'formatted_time' => date('H:i:s', $login['timestamp']),
						'timestamp' => $login['timestamp']
					];
				}
			}
		}

		// Process failed login details
		$processed_failed_details = [];
		foreach ($all_failed_login_data as $failed_login) {
			if (isset($failed_login['last_reset_day']) && $failed_login['last_reset_day'] === $date) {
				$processed_failed_details[] = [
					'user' => isset($failed_login['user']) ? $failed_login['user'] : 'Unknown',
					'ip' => isset($failed_login['ip']) ? $failed_login['ip'] : 'Not detected',
					'formatted_time' => 'Multiple attempts',
					'attempts' => isset($failed_login['daily_failed_count']) ? $failed_login['daily_failed_count'] : 1,
					'timestamp' => strtotime($date)
				];
			}
		}

		// Process logout details
		$processed_logout_details = [];
		foreach ($all_login_data as $login) {
			if (isset($login['action']) && $login['action'] === 'logout') {
				$login_date = date('Y-m-d', $login['timestamp']);
				if ($login_date === $date) {
					$processed_logout_details[] = [
						'user' => isset($login['user']) ? $login['user'] : 'Unknown',
						'ip' => isset($login['ip']) ? $login['ip'] : 'Not detected',
						'formatted_time' => date('H:i:s', $login['timestamp']),
						'timestamp' => $login['timestamp']
					];
				}
			}
		}

		// Calculate summary statistics
		$successful_logins = array_sum($login_counts_by_user) ?: count($processed_login_details);
		$failed_logins = array_sum(array_column($processed_failed_details, 'attempts'));
		$logouts = count($processed_logout_details);
		
		// Get unique users from all activities
		$unique_users = [];
		foreach ($processed_login_details as $login) {
			$unique_users[$login['user']] = true;
		}
		foreach ($processed_logout_details as $logout) {
			$unique_users[$logout['user']] = true;
		}
		$unique_users_count = count($unique_users);

		$success_rate = ($successful_logins + $failed_logins) > 0 ? 
			round(($successful_logins / ($successful_logins + $failed_logins)) * 100, 1) : 0;

		return [
			'date' => $date,
			'successful_logins' => $successful_logins,
			'failed_logins' => $failed_logins,
			'logouts' => $logouts,
			'unique_users' => $unique_users_count,
			'success_rate' => $success_rate,
			'login_details' => $processed_login_details,
			'failed_details' => $processed_failed_details,
			'logout_details' => $processed_logout_details
		];
	}



	/**
	 * Clean up analytics data older than 7 days
	 */
	private function cleanup_seven_day_analytics_data() {
		$seven_days_ago_timestamp = strtotime('-7 days');
		$seven_days_ago_date = date('Y-m-d', $seven_days_ago_timestamp);
		
		// Only clean up if 7-day analytics is enabled
		$security_settings = get_option('wpnts_webhook_site_settings', '{}');
		$settings = json_decode($security_settings, true);
		
		if (isset($settings['seven_day_rolling_analytics']) && $settings['seven_day_rolling_analytics'] === true) {
			// Clean up old login data (older than 7 days)
			$all_login_data = get_option('wpnts_user_login_info', []);
			$cleaned_login_data = [];
			
			foreach ($all_login_data as $login) {
				if (isset($login['timestamp']) && $login['timestamp'] >= $seven_days_ago_timestamp) {
					$cleaned_login_data[] = $login;
				}
			}
			
			if (count($cleaned_login_data) !== count($all_login_data)) {
				update_option('wpnts_user_login_info', $cleaned_login_data);
			}

			// Clean up old failed login data (older than 7 days)
			$all_failed_login_data = get_option('wpnts_user_track_failed_login', []);
			$cleaned_failed_data = [];
			
			foreach ($all_failed_login_data as $failed_login) {
				if (isset($failed_login['last_reset_day']) && $failed_login['last_reset_day'] >= $seven_days_ago_date) {
					$cleaned_failed_data[] = $failed_login;
				}
			}
			
			if (count($cleaned_failed_data) !== count($all_failed_login_data)) {
				update_option('wpnts_user_track_failed_login', $cleaned_failed_data);
			}

			// Clean up old daily login data (older than 7 days)
			$daily_login_data = get_option('wpnts_user_daily_login_info', []);
			$cleaned_daily_data = [];
			
			foreach ($daily_login_data as $daily_login) {
				if (isset($daily_login['last_reset_day']) && $daily_login['last_reset_day'] >= $seven_days_ago_date) {
					$cleaned_daily_data[] = $daily_login;
				}
			}
			
			if (count($cleaned_daily_data) !== count($daily_login_data)) {
				update_option('wpnts_user_daily_login_info', $cleaned_daily_data);
			}
		}
	}

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

		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'
				],
				[ '%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'
				]
			);
			
			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
		) );
	}
}