<?php
/**
 * Database & Data Retrieval Handler for Affiliate Cards.
 *
 * @package AffiStyle
 */

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

/**
 * Class AffiStyle_Database
 *
 * Handles database operations and metadata retrieval for Affiliate Cards.
 */
class AffiStyle_Database {


	/**
	 * Ensure custom analytics database table exists with fail-safe auto-creation.
	 */
	public static function ensure_analytics_table_exists() {
		global $wpdb;
		$table_name = esc_sql( $wpdb->prefix . 'affistyle_clicks' );

		$installed_ver = get_option( 'affistyle_clicks_db_version', '1.0' );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( version_compare( $installed_ver, '1.2.0', '<' ) || $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) !== $table_name ) {
			if ( file_exists( ABSPATH . 'wp-admin/includes/upgrade.php' ) ) {
				require_once ABSPATH . 'wp-admin/includes/upgrade.php';
			}
			$charset_collate = $wpdb->get_charset_collate();

			$sql = "CREATE TABLE {$table_name} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				post_id bigint(20) unsigned NOT NULL DEFAULT 0,
				box_id bigint(20) unsigned NOT NULL DEFAULT 0,
				product_index tinyint(2) unsigned NOT NULL DEFAULT 1,
				variant varchar(10) NOT NULL DEFAULT 'A',
				country_code varchar(10) NOT NULL DEFAULT 'ZZ',
				device_type varchar(20) NOT NULL DEFAULT 'desktop',
				referrer_url varchar(255) NOT NULL DEFAULT '',
				referrer varchar(255) NOT NULL DEFAULT '',
				ip_address varchar(45) NOT NULL DEFAULT '',
				user_agent text,
				created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
				PRIMARY KEY  (id),
				KEY post_id (post_id),
				KEY box_id (box_id),
				KEY variant (variant),
				KEY country_code (country_code),
				KEY created_at (created_at)
			) {$charset_collate};";

			dbDelta( $sql );
			update_option( 'affistyle_clicks_db_version', '1.2.0' );
		}
	}

	/**
	 * Retrieve compiled analytics metrics & counts.
	 *
	 * @param string $date_range Range preset ('all', '7', '30', '60', '90', 'custom').
	 * @param string $start_date YYYY-MM-DD.
	 * @param string $end_date   YYYY-MM-DD.
	 * @return array
	 */
	public static function get_analytics_summary( $date_range = '7', $start_date = '', $end_date = '' ) {
		self::ensure_analytics_table_exists();

		global $wpdb;
		$table_name = esc_sql( $wpdb->prefix . 'affistyle_clicks' );

		$date_range = sanitize_text_field( $date_range );
		$start_date = sanitize_text_field( $start_date );
		$end_date   = sanitize_text_field( $end_date );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$total_all_time = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$table_name}" );

		if ( in_array( $date_range, array( '7', '30', '60', '90' ), true ) ) {
			$days = absint( $date_range );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_last_range = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)", $days )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$unique_visitors = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT NULLIF(ip_address, '')) FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)", $days )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$active_cards_tracked = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY)", $days )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_countries = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $days, 5 ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_posts = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $days, 5 ),
				ARRAY_A
			);
		} elseif ( 'custom' === $date_range && ! empty( $start_date ) && ! empty( $end_date ) ) {
			$start_datetime = $start_date . ' 00:00:00';
			$end_datetime   = $end_date . ' 23:59:59';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_last_range = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s", $start_datetime, $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$unique_visitors = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT NULLIF(ip_address, '')) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s", $start_datetime, $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$active_cards_tracked = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= %s AND created_at <= %s", $start_datetime, $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_countries = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $start_datetime, $end_datetime, 5 ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_posts = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $start_datetime, $end_datetime, 5 ),
				ARRAY_A
			);
		} elseif ( 'custom' === $date_range && ! empty( $start_date ) ) {
			$start_datetime = $start_date . ' 00:00:00';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_last_range = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at >= %s", $start_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$unique_visitors = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT NULLIF(ip_address, '')) FROM {$table_name} WHERE created_at >= %s", $start_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$active_cards_tracked = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at >= %s", $start_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_countries = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $start_datetime, 5 ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_posts = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at >= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $start_datetime, 5 ),
				ARRAY_A
			);
		} elseif ( 'custom' === $date_range && ! empty( $end_date ) ) {
			$end_datetime = $end_date . ' 23:59:59';

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_last_range = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(id) FROM {$table_name} WHERE created_at <= %s", $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$unique_visitors = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT NULLIF(ip_address, '')) FROM {$table_name} WHERE created_at <= %s", $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$active_cards_tracked = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name} WHERE created_at <= %s", $end_datetime )
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_countries = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} WHERE created_at <= %s GROUP BY country_code ORDER BY click_count DESC LIMIT %d", $end_datetime, 5 ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_posts = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} WHERE created_at <= %s GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", $end_datetime, 5 ),
				ARRAY_A
			);
		} else {
			$total_last_range = $total_all_time;

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$unique_visitors = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT NULLIF(ip_address, '')) FROM {$table_name}" );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$active_cards_tracked = (int) $wpdb->get_var( "SELECT COUNT(DISTINCT COALESCE(NULLIF(box_id, 0), post_id)) FROM {$table_name}" );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_countries = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT country_code, COUNT(id) as click_count FROM {$table_name} GROUP BY country_code ORDER BY click_count DESC LIMIT %d", 5 ),
				ARRAY_A
			);

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$top_posts = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT COALESCE(NULLIF(box_id, 0), post_id) as post_id, COUNT(id) as click_count FROM {$table_name} GROUP BY COALESCE(NULLIF(box_id, 0), post_id) ORDER BY click_count DESC LIMIT %d", 5 ),
				ARRAY_A
			);
		}

		// Ensure unique visitors fallback if clicks exist but IPs were stored as empty.
		if ( 0 === $unique_visitors && $total_last_range > 0 ) {
			$unique_visitors = 1;
		}

		// Count published affistyle_box posts within the active date range so the
		// "Published Boxes" card reflects the same filter as the other metrics.
		$posts_table          = esc_sql( $wpdb->posts );
		$published_boxes_base = "SELECT COUNT(ID) FROM {$posts_table} WHERE post_type = 'affistyle_box' AND post_status = 'publish'";

		if ( in_array( $date_range, array( '7', '30', '60', '90' ), true ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_published_cards = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( $published_boxes_base . ' AND post_date >= DATE_SUB(NOW(), INTERVAL %d DAY)', $days )
			);
		} elseif ( 'custom' === $date_range && ! empty( $start_date ) && ! empty( $end_date ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_published_cards = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( $published_boxes_base . ' AND post_date >= %s AND post_date <= %s', $start_date . ' 00:00:00', $end_date . ' 23:59:59' )
			);
		} elseif ( 'custom' === $date_range && ! empty( $start_date ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_published_cards = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( $published_boxes_base . ' AND post_date >= %s', $start_date . ' 00:00:00' )
			);
		} elseif ( 'custom' === $date_range && ! empty( $end_date ) ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$total_published_cards = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( $published_boxes_base . ' AND post_date <= %s', $end_date . ' 23:59:59' )
			);
		} else {
			// All-time: count all published affistyle_box posts using core helper.
			$published_counts      = wp_count_posts( 'affistyle_box' );
			$total_published_cards = isset( $published_counts->publish ) ? (int) $published_counts->publish : 0;
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
		$sticky_cta_clicks = (int) $wpdb->get_var( "SELECT COUNT(id) FROM {$table_name} WHERE device_type = 'mobile_sticky'" );

		return array(
			'total_all_time'        => $total_all_time,
			'total_last_7_days'     => $total_last_range,
			'total_range_clicks'    => $total_last_range,
			'unique_visitors'       => $unique_visitors,
			'total_published_cards' => $total_published_cards,
			'active_cards_tracked'  => $active_cards_tracked,
			'sticky_cta_clicks'     => $sticky_cta_clicks,
			'top_countries'         => is_array( $top_countries ) ? $top_countries : array(),
			'top_posts'             => is_array( $top_posts ) ? $top_posts : array(),
		);
	}

	/**
	 * Retrieve time-series click performance data for chart visualizations.
	 *
	 * @param string $date_range Range preset ('7', '30', '60', '90', 'all', 'custom').
	 * @param string $start_date YYYY-MM-DD.
	 * @param string $end_date   YYYY-MM-DD.
	 * @return array
	 */
	public static function get_clicks_time_series( $date_range = '7', $start_date = '', $end_date = '' ) {
		self::ensure_analytics_table_exists();

		global $wpdb;
		$table_name = esc_sql( $wpdb->prefix . 'affistyle_clicks' );

		$date_range = sanitize_text_field( $date_range );
		$start_date = sanitize_text_field( $start_date );
		$end_date   = sanitize_text_field( $end_date );

		$labels                 = array();
		$full_labels            = array();
		$data                   = array();
		$revenue_data           = array();
		$interval               = 'daily';
		$has_revenue_configured = class_exists( 'AffiStyle_Click_Analytics' ) && AffiStyle_Click_Analytics::has_revenue_configured();

		if ( in_array( $date_range, array( '7', '30', '60', '90' ), true ) ) {
			$days = absint( $date_range );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
				$wpdb->prepare( "SELECT DATE(created_at) as click_date, COALESCE(NULLIF(box_id, 0), post_id) as card_id, product_index, COUNT(id) as total_clicks FROM {$table_name} WHERE created_at >= DATE_SUB(NOW(), INTERVAL %d DAY) GROUP BY DATE(created_at), COALESCE(NULLIF(box_id, 0), post_id), product_index ORDER BY click_date ASC", $days ),
				ARRAY_A
			);

			$click_map = array();
			$rev_map   = array();
			if ( ! empty( $results ) ) {
				foreach ( $results as $row ) {
					$d_key   = $row['click_date'];
					$c_id    = absint( $row['card_id'] );
					$p_idx   = absint( $row['product_index'] );
					$c_count = (int) $row['total_clicks'];

					$click_map[ $d_key ] = ( isset( $click_map[ $d_key ] ) ? $click_map[ $d_key ] : 0 ) + $c_count;

					if ( $has_revenue_configured ) {
						$c_epc             = AffiStyle_Click_Analytics::get_card_effective_epc( $c_id, $p_idx );
						$rev_map[ $d_key ] = ( isset( $rev_map[ $d_key ] ) ? $rev_map[ $d_key ] : 0.0 ) + round( $c_count * $c_epc, 2 );
					}
				}
			}

			// Build continuous daily sequence from ($days - 1) days ago to today.
			$current_ts = time();
			for ( $i = $days - 1; $i >= 0; $i-- ) {
				$day_ts         = $current_ts - ( $i * DAY_IN_SECONDS );
				$date_key       = gmdate( 'Y-m-d', $day_ts );
				$labels[]       = gmdate( 'M j', $day_ts );
				$full_labels[]  = gmdate( 'M j, Y', $day_ts );
				$data[]         = isset( $click_map[ $date_key ] ) ? $click_map[ $date_key ] : 0;
				$revenue_data[] = ( $has_revenue_configured && isset( $rev_map[ $date_key ] ) ) ? round( $rev_map[ $date_key ], 2 ) : 0.0;
			}
		} elseif ( 'all' === $date_range ) {
			$interval = 'monthly';

			// Determine earliest click date.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$first_date = $wpdb->get_var( "SELECT MIN(created_at) FROM {$table_name}" );

			// Query monthly totals using SUBSTRING to extract YYYY-MM safely.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
			$results = $wpdb->get_results( "SELECT SUBSTRING(created_at, 1, 7) as click_month, COALESCE(NULLIF(box_id, 0), post_id) as card_id, product_index, COUNT(id) as total_clicks FROM {$table_name} GROUP BY click_month, COALESCE(NULLIF(box_id, 0), post_id), product_index ORDER BY click_month ASC", ARRAY_A );

			$click_map = array();
			$rev_map   = array();
			if ( ! empty( $results ) ) {
				foreach ( $results as $row ) {
					$m_key   = $row['click_month'];
					$c_id    = absint( $row['card_id'] );
					$p_idx   = absint( $row['product_index'] );
					$c_count = (int) $row['total_clicks'];

					$click_map[ $m_key ] = ( isset( $click_map[ $m_key ] ) ? $click_map[ $m_key ] : 0 ) + $c_count;

					if ( $has_revenue_configured ) {
						$c_epc             = AffiStyle_Click_Analytics::get_card_effective_epc( $c_id, $p_idx );
						$rev_map[ $m_key ] = ( isset( $rev_map[ $m_key ] ) ? $rev_map[ $m_key ] : 0.0 ) + round( $c_count * $c_epc, 2 );
					}
				}
			}

			$current_ts    = time();
			$current_year  = (int) gmdate( 'Y', $current_ts );
			$current_month = (int) gmdate( 'n', $current_ts );

			// Default: months of the current year up to current month (e.g. Jan to Sep).
			$start_year  = $current_year;
			$start_month = 1;

			if ( ! empty( $first_date ) ) {
				$first_ts         = strtotime( $first_date );
				$first_click_year = (int) gmdate( 'Y', $first_ts );
				if ( $first_click_year < $current_year ) {
					// Show last 12 months up to current month.
					$start_ts    = strtotime( '-11 months', $current_ts );
					$start_year  = (int) gmdate( 'Y', $start_ts );
					$start_month = (int) gmdate( 'n', $start_ts );
				}
			}

			$loop_year  = $start_year;
			$loop_month = $start_month;

			while ( ( $loop_year < $current_year ) || ( $loop_year === $current_year && $loop_month <= $current_month ) ) {
				$month_key      = sprintf( '%04d-%02d', $loop_year, $loop_month );
				$month_ts       = gmmktime( 0, 0, 0, $loop_month, 1, $loop_year );
				$labels[]       = gmdate( 'M', $month_ts );
				$full_labels[]  = gmdate( 'F Y', $month_ts );
				$data[]         = isset( $click_map[ $month_key ] ) ? $click_map[ $month_key ] : 0;
				$revenue_data[] = ( $has_revenue_configured && isset( $rev_map[ $month_key ] ) ) ? round( $rev_map[ $month_key ], 2 ) : 0.0;

				++$loop_month;
				if ( $loop_month > 12 ) {
					$loop_month = 1;
					++$loop_year;
				}
			}
		} elseif ( 'custom' === $date_range ) {
			$start_ts = ! empty( $start_date ) ? strtotime( $start_date ) : ( time() - ( 30 * DAY_IN_SECONDS ) );
			$end_ts   = ! empty( $end_date ) ? strtotime( $end_date ) : time();

			if ( $start_ts > $end_ts ) {
				$temp     = $start_ts;
				$start_ts = $end_ts;
				$end_ts   = $temp;
			}

			$diff_days = round( ( $end_ts - $start_ts ) / DAY_IN_SECONDS );

			if ( $diff_days > 90 ) {
				$interval  = 'monthly';
				$start_str = gmdate( 'Y-m-d 00:00:00', $start_ts );
				$end_str   = gmdate( 'Y-m-d 23:59:59', $end_ts );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					$wpdb->prepare( "SELECT SUBSTRING(created_at, 1, 7) as click_month, COALESCE(NULLIF(box_id, 0), post_id) as card_id, product_index, COUNT(id) as total_clicks FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY click_month, COALESCE(NULLIF(box_id, 0), post_id), product_index ORDER BY click_month ASC", $start_str, $end_str ),
					ARRAY_A
				);

				$click_map = array();
				$rev_map   = array();
				if ( ! empty( $results ) ) {
					foreach ( $results as $row ) {
						$m_key   = $row['click_month'];
						$c_id    = absint( $row['card_id'] );
						$p_idx   = absint( $row['product_index'] );
						$c_count = (int) $row['total_clicks'];

						$click_map[ $m_key ] = ( isset( $click_map[ $m_key ] ) ? $click_map[ $m_key ] : 0 ) + $c_count;

						if ( $has_revenue_configured ) {
							$c_epc             = AffiStyle_Click_Analytics::get_card_effective_epc( $c_id, $p_idx );
							$rev_map[ $m_key ] = ( isset( $rev_map[ $m_key ] ) ? $rev_map[ $m_key ] : 0.0 ) + round( $c_count * $c_epc, 2 );
						}
					}
				}

				$loop_year  = (int) gmdate( 'Y', $start_ts );
				$loop_month = (int) gmdate( 'n', $start_ts );
				$end_year   = (int) gmdate( 'Y', $end_ts );
				$end_month  = (int) gmdate( 'n', $end_ts );

				while ( ( $loop_year < $end_year ) || ( $loop_year === $end_year && $loop_month <= $end_month ) ) {
					$month_key      = sprintf( '%04d-%02d', $loop_year, $loop_month );
					$month_ts       = gmmktime( 0, 0, 0, $loop_month, 1, $loop_year );
					$labels[]       = gmdate( 'M Y', $month_ts );
					$full_labels[]  = gmdate( 'F Y', $month_ts );
					$data[]         = isset( $click_map[ $month_key ] ) ? $click_map[ $month_key ] : 0;
					$revenue_data[] = ( $has_revenue_configured && isset( $rev_map[ $month_key ] ) ) ? round( $rev_map[ $month_key ], 2 ) : 0.0;

					++$loop_month;
					if ( $loop_month > 12 ) {
						$loop_month = 1;
						++$loop_year;
					}
				}
			} else {
				$interval  = 'daily';
				$start_str = gmdate( 'Y-m-d 00:00:00', $start_ts );
				$end_str   = gmdate( 'Y-m-d 23:59:59', $end_ts );

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
				$results = $wpdb->get_results(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
					$wpdb->prepare( "SELECT DATE(created_at) as click_date, COALESCE(NULLIF(box_id, 0), post_id) as card_id, product_index, COUNT(id) as total_clicks FROM {$table_name} WHERE created_at >= %s AND created_at <= %s GROUP BY DATE(created_at), COALESCE(NULLIF(box_id, 0), post_id), product_index ORDER BY click_date ASC", $start_str, $end_str ),
					ARRAY_A
				);

				$click_map = array();
				$rev_map   = array();
				if ( ! empty( $results ) ) {
					foreach ( $results as $row ) {
						$d_key   = $row['click_date'];
						$c_id    = absint( $row['card_id'] );
						$p_idx   = absint( $row['product_index'] );
						$c_count = (int) $row['total_clicks'];

						$click_map[ $d_key ] = ( isset( $click_map[ $d_key ] ) ? $click_map[ $d_key ] : 0 ) + $c_count;

						if ( $has_revenue_configured ) {
							$c_epc             = AffiStyle_Click_Analytics::get_card_effective_epc( $c_id, $p_idx );
							$rev_map[ $d_key ] = ( isset( $rev_map[ $d_key ] ) ? $rev_map[ $d_key ] : 0.0 ) + round( $c_count * $c_epc, 2 );
						}
					}
				}

				for ( $t = $start_ts; $t <= $end_ts; $t += DAY_IN_SECONDS ) {
					$date_key       = gmdate( 'Y-m-d', $t );
					$labels[]       = gmdate( 'M j', $t );
					$full_labels[]  = gmdate( 'M j, Y', $t );
					$data[]         = isset( $click_map[ $date_key ] ) ? $click_map[ $date_key ] : 0;
					$revenue_data[] = ( $has_revenue_configured && isset( $rev_map[ $date_key ] ) ) ? round( $rev_map[ $date_key ], 2 ) : 0.0;
				}
			}
		}

		$total_period_clicks = array_sum( $data );
		$max_value           = ! empty( $data ) ? max( $data ) : 0;

		$fin_settings    = class_exists( 'AffiStyle_Click_Analytics' ) ? AffiStyle_Click_Analytics::get_financial_settings() : array();
		$currency_symbol = isset( $fin_settings['currency_symbol'] ) ? (string) $fin_settings['currency_symbol'] : '$';

		$total_revenue = ( $has_revenue_configured && ! empty( $revenue_data ) ) ? round( array_sum( $revenue_data ), 2 ) : 0.0;
		$max_revenue   = ( $has_revenue_configured && ! empty( $revenue_data ) ) ? max( $revenue_data ) : 0.0;

		if ( ! $has_revenue_configured ) {
			$revenue_data = array();
		}

		return array(
			'labels'                 => $labels,
			'full_labels'            => $full_labels,
			'data'                   => $data,
			'revenue_data'           => $revenue_data,
			'interval'               => $interval,
			'total_clicks'           => $total_period_clicks,
			'total_revenue'          => $total_revenue,
			'max_value'              => $max_value,
			'max_revenue'            => $max_revenue,
			'has_revenue_configured' => ( $has_revenue_configured && $total_revenue > 0 ),
			'epc'                    => ( $total_period_clicks > 0 && $total_revenue > 0 ) ? round( $total_revenue / $total_period_clicks, 2 ) : 0.00,
			'currency_symbol'        => $currency_symbol,
		);
	}

	/**
	 * Helper function for options retrieval to avoid PHP reserved word parameters.
	 *
	 * @param string $key           Option key.
	 * @param mixed  $default_value Fallback value if option not set.
	 * @return mixed
	 */
	public static function get_option( $key, $default_value = false ) {
		$val = get_option( $key );
		return ( false !== $val ) ? $val : $default_value;
	}

	/**
	 * Retrieve all saved affiliate card post items for dropdown selectors.
	 *
	 * @return array
	 */
	public static function get_all_boxes_for_dropdown() {
		$posts = get_posts(
			array(
				'post_type'      => array( 'affistyle_box', 'aff_box' ),
				'posts_per_page' => -1,
				'post_status'    => 'publish',
				'orderby'        => 'title',
				'order'          => 'ASC',
			)
		);

		$options = array(
			array(
				'label' => __( 'Select an Affiliate Box...', 'affistyle' ),
				'value' => '',
			),
		);

		if ( ! empty( $posts ) ) {
			foreach ( $posts as $post ) {
				$options[] = array(
					'label' => $post->post_title,
					'value' => (string) $post->ID,
				);
			}
		}

		return $options;
	}

	/**
	 * Retrieve and compile all manual metadata for a specific affiliate box ID.
	 *
	 * @param int $post_id Post ID.
	 * @return array|false
	 */
	public static function get_box_data( $post_id ) {
		$post_id = absint( $post_id );

		$post_type = get_post_type( $post_id );
		if ( 'affistyle_box' !== $post_type && 'aff_box' !== $post_type ) {
			return false;
		}

		// Fetch all metadata in a single cached database call.
		$all_meta = get_post_meta( $post_id );

		$get_meta = function ( $key, $default_value = '' ) use ( $all_meta ) {
			return isset( $all_meta[ $key ][0] ) ? $all_meta[ $key ][0] : $default_value;
		};

		$count = absint( $get_meta( '_affistyle_rating_count', 0 ) );
		$sum   = absint( $get_meta( '_affistyle_rating_sum', 0 ) );
		$avg   = $count > 0 ? round( $sum / $count, 1 ) : 0.0;

		$score_meta = $get_meta( '_affistyle_rating_score' );
		$score      = ( '' !== $score_meta ) ? floatval( $score_meta ) : $avg;
		$rev_count  = $get_meta( '_affistyle_review_count' );
		if ( empty( $rev_count ) && $count > 0 ) {
			/* translators: %d: Review count number */
			$rev_count = sprintf( _n( '%d review', '%d reviews', $count, 'affistyle' ), $count );
		}
		$show_meta   = $get_meta( '_affistyle_show_rating' );
		$show_rating = ( '' === $show_meta ) ? true : ( '1' === (string) $show_meta );

		$title_1 = $get_meta( '_affistyle_title_1' );
		if ( empty( $title_1 ) ) {
			$title_1 = get_the_title( $post_id );
		}
		$image_1 = $get_meta( '_affistyle_image_1' );
		if ( empty( $image_1 ) ) {
			$image_1 = $get_meta( '_affistyle_image_url' );
		}
		$raw_dest_1 = $get_meta( '_affistyle_destination' );
		if ( empty( $raw_dest_1 ) ) {
			$raw_dest_1 = $get_meta( '_affistyle_url_default' );
		}

		$is_cloaking_enabled = apply_filters( 'affistyle_is_cloaking_enabled', apply_filters( 'affistyle_enable_cloaking', true ), $post_id );

		if ( $is_cloaking_enabled && class_exists( 'AffiStyle_Cloaking' ) ) {
			$dest_1 = AffiStyle_Cloaking::get_cloaked_url( $post_id, 1 );
		} else {
			$dest_1 = $raw_dest_1;
		}

		$link_attributes = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::get_link_attributes_string( $post_id ) : 'target="_blank" rel="nofollow sponsored noopener"';
		$ftc_disclosure  = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::get_ftc_disclosure( $post_id ) : __( 'We may earn an affiliate commission at no extra cost to you when you purchase through links on our site.', 'affistyle' );
		$show_ftc        = class_exists( 'AffiStyle_Compliance' ) ? AffiStyle_Compliance::is_ftc_enabled( $post_id ) : true;

		$price_1 = $get_meta( '_affistyle_price_1' );
		if ( empty( $price_1 ) ) {
			$price_1 = $get_meta( '_affistyle_price' );
		}
		$show_price_meta = $get_meta( '_affistyle_show_price' );
		$show_price      = ( '' === $show_price_meta ) ? true : ( '1' === (string) $show_price_meta );

		$data = array(
			'id'              => $post_id,
			'post_id'         => $post_id,
			'template_style'  => $get_meta( '_affistyle_template_style', 'style-1' ),
			'title'           => get_the_title( $post_id ),
			'title_1'         => $title_1,
			'description'     => $get_meta( '_affistyle_description' ),
			'price'           => $price_1,
			'price_1'         => $price_1,
			'show_price'      => $show_price,
			'destination'     => $dest_1,
			'link_1'          => $dest_1,
			'raw_destination' => $raw_dest_1,
			'raw_link_1'      => $raw_dest_1,
			'button_text'     => $get_meta( '_affistyle_button_text', __( 'Claim Deal', 'affistyle' ) ),
			'badge_text'      => $get_meta( '_affistyle_badge_text' ),
			'image_url'       => $image_1,
			'image_1'         => $image_1,
			'bg_color'        => $get_meta( '_affistyle_bg_color', '#ffffff' ),
			'btn_color'       => $get_meta( '_affistyle_btn_color', '#2563eb' ),
			'pros'            => $get_meta( '_affistyle_pros' ),
			'cons'            => $get_meta( '_affistyle_cons' ),
			'rating_score'    => $score,
			'review_count'    => $rev_count,
			'show_rating'     => $show_rating,
			'rating_count'    => $count,
			'rating_sum'      => $sum,
			'rating_average'  => $avg,

			// Automated Compliance & FTC Disclosure fields.
			'link_attributes' => $link_attributes,
			'ftc_disclosure'  => $ftc_disclosure,
			'show_ftc'        => $show_ftc,
		);

		return apply_filters( 'affistyle_card_data', $data, $post_id );
	}
}

new AffiStyle_Database();