<?php
/**
 * Core functions
 */

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

/**
 * Check if a database table exists, with per-request static cache.
 *
 * Avoids running SHOW TABLES LIKE on every call. Once confirmed to exist
 * within a request it will not be checked again.
 *
 * @param string $table Full table name including prefix.
 * @return bool
 */
function spar_table_exists( $table ) {
	static $cache = [];
	if ( isset( $cache[ $table ] ) ) {
		return $cache[ $table ];
	}

	global $wpdb;
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$exists = ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) === $table );
	$cache[ $table ] = $exists;
	return $exists;
}

/**
 * Register account endpoint early in plugin load.
 */
// Always register endpoint; visibility handled by menu filter and template
add_action( 'init', 'spar_register_account_endpoint', 1 );
function spar_register_account_endpoint() {
	add_rewrite_endpoint( 'rewards', EP_PAGES );
}

/**
 * Get user points
 */
function spar_get_user_points( $user_id = 0 ) {
	$user_id = $user_id ? $user_id : get_current_user_id();
	$points = (int) get_user_meta( $user_id, '_spar_points', true );
	/**
	 * Filter the returned points balance for a user.
	 *
	 * Useful for virtual balances, preview modes, or external adjustments.
	 * Should only affect display/derived behavior; storage remains canonical.
	 *
	 * @param int $points  Current stored balance.
	 * @param int $user_id User ID.
	 */
	return (int) apply_filters( 'spar_user_points', $points, $user_id );
}

if ( ! function_exists( 'spar_format_voucher_value_display' ) ) {
    /**
     * Build a human-friendly voucher value label that combines amount/percentage
     * discounts with optional free-shipping benefits.
     *
     * @param string $discount_type  WooCommerce discount type (percent/fixed_cart/etc).
     * @param float  $amount         Discount amount associated with the voucher/reward.
     * @param bool   $free_shipping  Whether the voucher also grants free shipping.
     * @return string Sanitized HTML/text describing the voucher value.
     */
	function spar_format_voucher_value_display( $discount_type = '', $amount = 0, $free_shipping = false ) {
		$parts         = array();
		$amount        = is_numeric( $amount ) ? (float) $amount : 0.0;
		$discount_type = is_string( $discount_type ) ? strtolower( $discount_type ) : '';
		$free_shipping = (bool) $free_shipping;

		if ( $amount > 0 ) {
			if ( 'percent' === $discount_type ) {
				$parts[] = sprintf(
					'%1$s%% %2$s',
					esc_html( number_format_i18n( $amount ) ),
					esc_html__( 'Off', 'simple-points-and-rewards' )
				);
			} else {
				$value_display = function_exists( 'wc_price' )
					? wc_price( $amount )
					: esc_html( spar_format_currency_amount( $amount ) );
				$parts[] = sprintf(
					'%1$s %2$s',
					$value_display,
					esc_html__( 'Off', 'simple-points-and-rewards' )
				);
			}
		}

		if ( $free_shipping ) {
			$parts[] = esc_html__( 'Free Shipping', 'simple-points-and-rewards' );
		}

		if ( empty( $parts ) ) {
			return '';
		}

		$display = implode( ' + ', $parts );

		return apply_filters( 'spar_voucher_value_display', $display, $discount_type, $amount, $free_shipping, $parts );
	}
}

if ( ! function_exists( 'spar_get_coupon_limitations' ) ) {
	/**
	 * Build an array of human-readable limitation notes for a WooCommerce coupon.
	 *
	 * @param int $coupon_id Post ID of the shop_coupon.
	 * @return array List of plain-text limitation strings.
	 */
	function spar_get_coupon_limitations( $coupon_id ) {
		$notes = [];

		// Detect product vouchers: SPAR reward vouchers that grant a specific free product.
		// For these, product_ids is the free item itself, not a purchase restriction.
		$is_product_voucher = '1' === get_post_meta( $coupon_id, '_spar_reward_voucher', true )
			&& ! empty( get_post_meta( $coupon_id, 'product_ids', true ) );

		$minimum_amount = get_post_meta( $coupon_id, 'minimum_amount', true );
		if ( is_numeric( $minimum_amount ) && (float) $minimum_amount > 0 ) {
			/* translators: %s: formatted minimum spend amount */
			$notes[] = sprintf( wp_kses_post( __( 'Min. spend: %s', 'simple-points-and-rewards' ) ), html_entity_decode( wp_strip_all_tags( wc_price( (float) $minimum_amount ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ) );
		}

		$maximum_amount = get_post_meta( $coupon_id, 'maximum_amount', true );
		if ( is_numeric( $maximum_amount ) && (float) $maximum_amount > 0 ) {
			/* translators: %s: formatted maximum spend amount */
			$notes[] = sprintf( wp_kses_post( __( 'Max. spend: %s', 'simple-points-and-rewards' ) ), html_entity_decode( wp_strip_all_tags( wc_price( (float) $maximum_amount ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ) );
		}

		// Only show product restriction note for non-product-vouchers; for product vouchers
		// the product_ids field identifies the free item, not a purchase restriction.
		if ( ! $is_product_voucher ) {
			$product_ids = get_post_meta( $coupon_id, 'product_ids', true );
			if ( ! empty( $product_ids ) ) {
				$ids   = array_filter( array_map( 'intval', explode( ',', $product_ids ) ) );
				$names = [];
				foreach ( array_slice( $ids, 0, 3 ) as $pid ) {
					$p = function_exists( 'wc_get_product' ) ? wc_get_product( $pid ) : null;
					if ( $p ) {
						$names[] = $p->get_name();
					}
				}
				if ( ! empty( $names ) ) {
					$label = implode( ', ', $names );
					if ( count( $ids ) > 3 ) {
						/* translators: %d: number of additional products */
						$label .= sprintf( esc_html__( ' +%d more', 'simple-points-and-rewards' ), count( $ids ) - 3 );
					}
					/* translators: %s: list of product names */
					$notes[] = sprintf( esc_html__( 'Products only: %s', 'simple-points-and-rewards' ), $label );
				}
			}
		}

		$exclude_product_ids = get_post_meta( $coupon_id, 'exclude_product_ids', true );
		if ( ! empty( $exclude_product_ids ) ) {
			$notes[] = esc_html__( 'Some products excluded', 'simple-points-and-rewards' );
		}

		$product_categories = get_post_meta( $coupon_id, 'product_categories', true );
		if ( ! empty( $product_categories ) && is_array( $product_categories ) ) {
			$cat_names = [];
			foreach ( array_slice( $product_categories, 0, 3 ) as $cat_id ) {
				$term = get_term( (int) $cat_id, 'product_cat' );
				if ( $term && ! is_wp_error( $term ) ) {
					$cat_names[] = $term->name;
				}
			}
			if ( ! empty( $cat_names ) ) {
				/* translators: %s: list of category names */
				$notes[] = sprintf( esc_html__( 'Categories: %s', 'simple-points-and-rewards' ), implode( ', ', $cat_names ) );
			}
		}

		$exclude_product_categories = get_post_meta( $coupon_id, 'exclude_product_categories', true );
		if ( ! empty( $exclude_product_categories ) && is_array( $exclude_product_categories ) ) {
			$notes[] = esc_html__( 'Some categories excluded', 'simple-points-and-rewards' );
		}

		$exclude_sale_items = get_post_meta( $coupon_id, 'exclude_sale_items', true );
		if ( 'yes' === $exclude_sale_items ) {
			$notes[] = esc_html__( 'Excludes sale items', 'simple-points-and-rewards' );
		}

		/**
		 * Filter the coupon limitation notes displayed in the voucher list.
		 *
		 * @param array $notes     List of limitation strings.
		 * @param int   $coupon_id Post ID of the coupon.
		 */
		return apply_filters( 'spar_coupon_limitation_notes', $notes, $coupon_id );
	}
}

if ( ! function_exists( 'spar_format_currency_amount' ) ) {
	/**
	 * Format a numeric amount using WooCommerce price settings when available.
	 *
	 * @param float|int|string $amount   Numeric amount to format.
	 * @param int|null         $decimals Optional override for decimal places.
	 * @return string
	 */
	function spar_format_currency_amount( $amount, $decimals = null ) {
		$amount = is_numeric( $amount ) ? (float) $amount : 0.0;
		if ( function_exists( 'wc_get_price_decimals' ) && function_exists( 'wc_get_price_decimal_separator' ) && function_exists( 'wc_get_price_thousand_separator' ) ) {
			$price_decimals = is_numeric( $decimals ) ? (int) $decimals : (int) wc_get_price_decimals();
			$decimal_sep    = (string) wc_get_price_decimal_separator();
			$thousand_sep   = (string) wc_get_price_thousand_separator();
			return number_format( $amount, $price_decimals, $decimal_sep, $thousand_sep );
		}
		$price_decimals = is_numeric( $decimals ) ? (int) $decimals : 2;
		return number_format_i18n( $amount, $price_decimals );
	}
}

if ( ! function_exists( 'spar_format_multiplier' ) ) {
	/**
	 * Format a points multiplier for display (e.g. 1.5, 1.75, 2).
	 *
	 * Shows up to 2 decimal places but strips unnecessary trailing zeros,
	 * so 2.0 displays as "2", 1.5 as "1.5", and 1.75 as "1.75".
	 *
	 * @param float $multiplier The multiplier value.
	 * @return string Formatted multiplier string.
	 */
	function spar_format_multiplier( $multiplier ) {
		$multiplier = (float) $multiplier;

		if ( $multiplier === floor( $multiplier ) ) {
			return number_format_i18n( $multiplier, 0 );
		}

		$one_decimal  = round( $multiplier, 1 );
		$two_decimals = round( $multiplier, 2 );

		if ( abs( $one_decimal - $two_decimals ) < 0.001 ) {
			return number_format_i18n( $multiplier, 1 );
		}

		return number_format_i18n( $multiplier, 2 );
	}
}

/**
 * Add or remove points
 *
 * Parameters:
 * - $user_id   (int)    User ID.
 * - $points    (int)    Number of points to add/remove.
 * - $action    (string) 'add' or 'remove'.
 * - $note      (string) Human-friendly context shown in logs/UI.
 * - $action_id (string) Machine-friendly action identifier for analytics/filtering.
 *
 * Common $action_id values used across the plugin:
 * - 'signup'          – signup bonus
 * - 'order'           – points earned for an order
 * - 'first_order'     – first order bonus
 * - 'nth_order'       – n-th order bonus
 * - 'order_refund'    – deduction for refunded order
 * - 'order_cancelled' – deduction for cancelled order
 * - 'order_failed'    – deduction for failed order
 * - 'referral'        – referral rewards
 * - 'review'          – product review rewards (PRO)
 * - 'redeem'          – points spent on rewards redemption
 * - 'redeem_refund'   – refund of points after failed redemption
 * - 'admin_adjustment'– manual admin points change
 */
function spar_update_user_points( $user_id, $points, $action = 'add', $note = '', $action_id = '' ) {
	// Allow developers to adjust the update arguments before applying.
	$args = apply_filters( 'spar_update_user_points_args', [
		'user_id'   => (int) $user_id,
		'points'    => (int) $points,
		'action'    => (string) $action,
		'note'      => (string) $note,
		'action_id' => is_string( $action_id ) ? sanitize_key( $action_id ) : '',
	] );

	$user_id   = (int) ( $args['user_id'] ?? $user_id );
	$points    = (int) ( $args['points'] ?? $points );
	$action    = (string) ( $args['action'] ?? $action );
	// Constrain action to expected values
	$action    = ( 'remove' === $action ) ? 'remove' : 'add';
	// Sanitize note for storage/logging and downstream hooks
	$note      = sanitize_text_field( (string) ( $args['note'] ?? $note ) );
	$action_id = (string) ( $args['action_id'] ?? $action_id );

	// Prevent earning points for banned users (do not block deductions)
	$user_status = get_user_meta( $user_id, 'spar_user_status', true );
	if ( 'banned' === $user_status && 'add' === $action && $points > 0 ) {
		/**
		 * Fires when a banned user attempts to earn points (blocked).
		 *
		 * @param int    $user_id User ID.
		 * @param int    $points  Points that were attempted.
		 * @param string $note    Context note.
		 * @param string $action_id Action identifier.
		 */
		do_action( 'spar_banned_user_points_blocked', $user_id, (int) $points, $note, $action_id );
		return; // Bail early – no changes applied.
	}

	/**
	 * Fires right before a user's points balance is updated.
	 *
	 * @param int    $user_id   User ID.
	 * @param int    $points    Positive integer amount being added/removed.
	 * @param string $action    'add' or 'remove'.
	 * @param string $note      Human-friendly note.
	 * @param string $action_id Machine-friendly short identifier.
	 */
	do_action( 'spar_before_points_update', $user_id, $points, $action, $note, $action_id );

	$current = spar_get_user_points( $user_id );

	// Track level before change (if feature available) to detect transitions.
	$prev_level = null;
	if ( function_exists( 'spar_get_user_level' ) ) {
		$prev_level = spar_get_user_level( $user_id );
	}

	$new = ( 'add' === $action ) ? $current + $points : $current - $points;
	$new = max( 0, $new );
	update_user_meta( $user_id, '_spar_points', $new );

	spar_log_points_event( $user_id, $note, $action, $points, $action_id );

	// Maintain lifetime "Total Earned" as user meta for fast reads.
	// By definition, lifetime total tracks only positive earnings (previously sum of 'add' logs).
	// We increment on 'add' and ensure the meta key exists for other actions.
	// For order-related deductions (refunds/cancellations), we also deduct from total earned.
	$meta_key = '_spar_total_earned_points';
	if ( 'add' === $action && $points > 0 ) {
		$lifetotal = (int) get_user_meta( $user_id, $meta_key, true );
		$lifetotal = $lifetotal + (int) $points;
		update_user_meta( $user_id, $meta_key, $lifetotal );
	} elseif ( 'remove' === $action && $points > 0 ) {
		// Deduct from total earned for order-related refunds/cancellations/failures
		$order_deduction_actions = [ 'order_refund', 'order_cancelled', 'order_failed' ];
		if ( in_array( $action_id, $order_deduction_actions, true ) ) {
			$lifetotal = (int) get_user_meta( $user_id, $meta_key, true );
			$lifetotal = max( 0, $lifetotal - (int) $points );
			update_user_meta( $user_id, $meta_key, $lifetotal );
		}
		// Initialize meta if missing so subsequent reads don't backfill repeatedly
		$exists = get_user_meta( $user_id, $meta_key, true );
		if ( '' === $exists || null === $exists ) {
			// If helper is available, backfill via existing function (which persists), otherwise set to 0.
			if ( function_exists( 'spar_get_user_total_points_earned' ) ) {
				spar_get_user_total_points_earned( $user_id );
			} else {
				update_user_meta( $user_id, $meta_key, 0 );
			}
		}
	} else {
		// Initialize meta if missing so subsequent reads don't backfill repeatedly
		$exists = get_user_meta( $user_id, $meta_key, true );
		if ( '' === $exists || null === $exists ) {
			// If helper is available, backfill via existing function (which persists), otherwise set to 0.
			if ( function_exists( 'spar_get_user_total_points_earned' ) ) {
				spar_get_user_total_points_earned( $user_id );
			} else {
				update_user_meta( $user_id, $meta_key, 0 );
			}
		}
	}
	
	// Trigger notifications/actions for this update
	if ( $action === 'add' && $points > 0 ) {
		do_action( 'spar_points_added', $user_id, $points, $note, $action_id );
	} elseif ( $action === 'remove' && $points > 0 ) {
		/**
		 * Fires when points are removed from a user.
		 *
		 * @param int    $user_id   User ID.
		 * @param int    $points    Amount removed.
		 * @param string $note      Context note.
		 * @param string $action_id Short action identifier.
		 */
		do_action( 'spar_points_removed', $user_id, $points, $note, $action_id );
	}

	/**
	 * Fires after a user's points balance is updated.
	 *
	 * @param int    $user_id     User ID.
	 * @param int    $new_balance New points balance.
	 * @param int    $delta       Positive integer amount that was added/removed.
	 * @param string $action      'add' or 'remove'.
	 * @param string $note        Context note.
	 * @param string $action_id   Short action identifier.
	 */
	do_action( 'spar_after_points_update', $user_id, (int) $new, (int) $points, $action, $note, $action_id );

	// Detect and announce level changes if levels feature is present.
	if ( function_exists( 'spar_get_user_level' ) ) {
		$new_level = spar_get_user_level( $user_id );
		$prev_id   = is_array( $prev_level ) && isset( $prev_level['id'] ) ? $prev_level['id'] : '';
		$new_id    = is_array( $new_level ) && isset( $new_level['id'] ) ? $new_level['id'] : '';
		if ( $new_id && $new_id !== $prev_id ) {
			/**
			 * Fires when a user's level changes.
			 *
			 * @param int   $user_id     User ID.
			 * @param array $new_level   New level array.
			 * @param array $previous    Previous level array (or null).
			 * @param int   $total_earned Lifetime total earned points after update.
			 */
			$total_earned = function_exists( 'spar_get_user_total_points_earned' ) ? spar_get_user_total_points_earned( $user_id ) : 0;
			do_action( 'spar_user_level_changed', $user_id, $new_level, $prev_level, (int) $total_earned );
		}
	}
}

/*
 * Create points log table on plugin activation
 * Note: The activation hook is registered from the main plugin file to comply with WP.org guidelines.
 */
function spar_create_points_log_table() {
	global $wpdb;

	$table_name = $wpdb->prefix . 'spar_points_logs';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE $table_name (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		user_id BIGINT UNSIGNED NOT NULL,
		action TEXT NOT NULL,
		type VARCHAR(100) NOT NULL,
		action_id VARCHAR(64) NULL,
		points INT NOT NULL,
		date DATETIME NOT NULL,
		PRIMARY KEY  (id),
		KEY user_id (user_id),
		KEY type (type),
		KEY action_id (action_id),
		KEY date (date)
	) $charset_collate;";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

/**
 * Log history
 *
 * $action_id is an optional short identifier describing the origin of the change
 * (e.g., 'order', 'referral', 'redeem'). It is sanitized with sanitize_key()
 * and stored in the spar_points_logs table for filtering/analytics.
 */
function spar_log_points_event( $user_id, $note, $type, $points, $action_id = '' ) {
	global $wpdb;
	$table = $wpdb->prefix . 'spar_points_logs';

	// Create table if it doesn't exist (cached check avoids SHOW TABLES on every insert).
	if ( ! spar_table_exists( $table ) ) {
		spar_create_points_log_table();
	}

	// $action_id is optional string identifier for the action source (e.g., 'referral', 'order')
	$action_id = is_string( $action_id ) ? sanitize_key( $action_id ) : '';

	$row = [
		'user_id'   => (int) $user_id,
		'action'    => sanitize_text_field( $note ),
		'type'      => sanitize_text_field( $type ),
		'action_id' => $action_id,
		'points'    => (int) $points,
		'date'      => current_time( 'mysql' ),
	];

	/**
	 * Filter the row to be inserted into the points log table.
	 *
	 * @param array $row Row array with keys: user_id, action, type, action_id, points, date.
	 */
	$row = apply_filters( 'spar_points_log_row', $row );
	// Re-sanitize defensively in case filters modified values
	$row = [
		'user_id'   => isset( $row['user_id'] ) ? (int) $row['user_id'] : (int) $user_id,
		'action'    => isset( $row['action'] ) ? sanitize_text_field( $row['action'] ) : sanitize_text_field( $note ),
		'type'      => ( isset( $row['type'] ) && 'remove' === $row['type'] ) ? 'remove' : 'add',
		'action_id' => isset( $row['action_id'] ) ? sanitize_key( (string) $row['action_id'] ) : $action_id,
		'points'    => isset( $row['points'] ) ? (int) $row['points'] : (int) $points,
		'date'      => isset( $row['date'] ) ? sanitize_text_field( $row['date'] ) : current_time( 'mysql' ),
	];

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Insert uses proper placeholders; WPDB handles sanitization by formats.
	$wpdb->insert( $table, $row, [ '%d', '%s', '%s', '%s', '%d', '%s' ] );

	/**
	 * Fires after a points log row was inserted.
	 *
	 * @param int   $insert_id Insert ID.
	 * @param array $row       The row data inserted.
	 */
	do_action( 'spar_points_log_inserted', (int) $wpdb->insert_id, $row );
}

/**
 * Maybe upgrade points log table to include new columns/indexes.
 * - Adds action_id column if missing.
 *
 * Uses a stored DB version option to avoid running expensive
 * SHOW TABLES / SHOW COLUMNS queries on every page load.
 */
function spar_maybe_upgrade_points_log_table() {

	// The current DB schema version. Bump this when adding new upgrade steps.
	$current_db_version = '1.1.0';

	// Bail early if the DB is already up to date — no queries needed.
	$installed_db_version = get_option( 'spar_db_version', '0' );
	if ( version_compare( $installed_db_version, $current_db_version, '>=' ) ) {
		return;
	}

	global $wpdb;
	$table = $wpdb->prefix . 'spar_points_logs';

	// Bail if table doesn't exist yet
	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
		return;
	}

	// v1.1.0: Add action_id column if missing.
	if ( version_compare( $installed_db_version, '1.1.0', '<' ) ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$has_action_id = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'action_id' ) );
		if ( ! $has_action_id ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN action_id VARCHAR(64) NULL AFTER type', $table ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD INDEX action_id (action_id)', $table ) );
			/**
			 * Fires after the points log table structure has been upgraded.
			 *
			 * @param string $change Identifier of the change performed (e.g., 'action_id').
			 */
			do_action( 'spar_points_log_table_upgraded', 'action_id' );
		}
	}

	// All upgrades applied — store the current version so we skip next time.
	update_option( 'spar_db_version', $current_db_version, false );
}

// Run DB upgrade check only on admin requests.
add_action( 'admin_init', 'spar_maybe_upgrade_points_log_table' );

/**
 * Utility function to safely declare functions
 * Prevents redeclaration errors
 */
if ( ! function_exists( 'spar_function_exists' ) ) {
	function spar_function_exists( $function_name ) {
		return function_exists( $function_name );
	}
}

/**
 * Get user points history with pagination
 */
function spar_get_user_points_history( $user_id, $page = 1, $per_page = 10 ) {
	global $wpdb;
	$table = $wpdb->prefix . 'spar_points_logs';
	
	// Calculate offset
	$per_page = (int) apply_filters( 'spar_points_history_per_page', (int) $per_page, (int) $user_id );
	$offset   = ( $page - 1 ) * $per_page;
	
	// Get total count with short cache
	$cache_group        = 'spar';
	$count_cache_key    = 'spar_points_history_count_' . absint( $user_id );
	$total_count        = wp_cache_get( $count_cache_key, $cache_group );
	if ( false === $total_count ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_count = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE user_id = %d AND ( action_id IS NULL OR action_id != 'guest_migration' )", $table, $user_id ) );
		wp_cache_set( $count_cache_key, $total_count, $cache_group, 60 );
	}
	
	// Get paginated results with short cache
	$logs_cache_key    = 'spar_points_history_logs_' . absint( $user_id ) . '_' . absint( $page ) . '_' . absint( $per_page );
	$logs              = wp_cache_get( $logs_cache_key, $cache_group );
	if ( false === $logs ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$logs = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM %i WHERE user_id = %d AND ( action_id IS NULL OR action_id != 'guest_migration' ) ORDER BY date DESC LIMIT %d OFFSET %d", $table, $user_id, $per_page, $offset ), ARRAY_A );
		wp_cache_set( $logs_cache_key, $logs, $cache_group, 60 );
	}
	
	// Calculate pagination info
	$total_pages = ceil( $total_count / $per_page );

	$result = [
		'logs' => $logs,
		'pagination' => [
			'current_page' => $page,
			'per_page' => $per_page,
			'total_count' => $total_count,
			'total_pages' => $total_pages,
			'has_previous' => $page > 1,
			'has_next' => $page < $total_pages
		]
	];

	/**
	 * Filter the assembled points history response for a user.
	 *
	 * @param array $result  Array with 'logs' and 'pagination'.
	 * @param int   $user_id User ID.
	 */
	return apply_filters( 'spar_user_points_history', $result, (int) $user_id );
}

/**
 * Get user referral clicks history with pagination
 */
function spar_get_user_referral_clicks_history( $user_id, $page = 1, $per_page = 5 ) {
	global $wpdb;
	$table = $wpdb->prefix . 'spar_referral_clicks';

	$per_page = (int) apply_filters( 'spar_referral_clicks_per_page', (int) $per_page, (int) $user_id );
	$offset   = ( $page - 1 ) * $per_page;

	// Ensure table exists (cached check).
	if ( ! spar_table_exists( $table ) ) {
		return [
			'logs' => [],
			'pagination' => [
				'current_page' => 1,
				'per_page' => $per_page,
				'total_count' => 0,
				'total_pages' => 0,
				'has_previous' => false,
				'has_next' => false,
			],
		];
	}

	$cache_group     = 'spar';
	$count_cache_key = 'spar_referral_clicks_count_' . absint( $user_id );
	$total_count     = wp_cache_get( $count_cache_key, $cache_group );
	if ( false === $total_count ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE referrer_user_id = %d', $table, $user_id ) );
		wp_cache_set( $count_cache_key, $total_count, $cache_group, 60 );
	}

	$logs_cache_key = 'spar_referral_clicks_logs_' . absint( $user_id ) . '_' . absint( $page ) . '_' . absint( $per_page );
	$logs           = wp_cache_get( $logs_cache_key, $cache_group );
	if ( false === $logs ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$logs = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, created_at, landing_url, referring_domain, converted FROM %i WHERE referrer_user_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d',
				$table,
				$user_id,
				$per_page,
				$offset
			),
			ARRAY_A
		);
		wp_cache_set( $logs_cache_key, $logs, $cache_group, 60 );
	}

	$total_pages = $per_page > 0 ? (int) ceil( $total_count / $per_page ) : 0;

	$result = [
		'logs' => $logs,
		'pagination' => [
			'current_page' => $page,
			'per_page' => $per_page,
			'total_count' => $total_count,
			'total_pages' => $total_pages,
			'has_previous' => $page > 1,
			'has_next' => $page < $total_pages,
		],
	];

	return apply_filters( 'spar_user_referral_clicks_history', $result, (int) $user_id );
}

/**
 * Get user referral clicks history with full fields (admin detail view).
 */
function spar_get_user_referral_clicks_history_full( $user_id, $page = 1, $per_page = 10 ) {
	global $wpdb;
	$table = $wpdb->prefix . 'spar_referral_clicks';

	$per_page = (int) apply_filters( 'spar_referral_clicks_per_page', (int) $per_page, (int) $user_id );
	$offset   = ( $page - 1 ) * $per_page;

	// Ensure table exists (cached check).
	if ( ! spar_table_exists( $table ) ) {
		return [
			'logs' => [],
			'pagination' => [
				'current_page' => 1,
				'per_page' => $per_page,
				'total_count' => 0,
				'total_pages' => 0,
				'has_previous' => false,
				'has_next' => false,
			],
		];
	}

	$cache_group     = 'spar';
	$count_cache_key = 'spar_referral_clicks_count_' . absint( $user_id );
	$total_count     = wp_cache_get( $count_cache_key, $cache_group );
	if ( false === $total_count ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$total_count = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE referrer_user_id = %d', $table, $user_id ) );
		wp_cache_set( $count_cache_key, $total_count, $cache_group, 60 );
	}

	$logs_cache_key = 'spar_referral_clicks_logs_full_' . absint( $user_id ) . '_' . absint( $page ) . '_' . absint( $per_page );
	$logs           = wp_cache_get( $logs_cache_key, $cache_group );
	if ( false === $logs ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$logs = $wpdb->get_results(
			$wpdb->prepare(
				'SELECT id, created_at, landing_url, referring_domain, converted, referree_user_id, order_id FROM %i WHERE referrer_user_id = %d ORDER BY created_at DESC LIMIT %d OFFSET %d',
				$table,
				$user_id,
				$per_page,
				$offset
			),
			ARRAY_A
		);
		wp_cache_set( $logs_cache_key, $logs, $cache_group, 60 );
	}

	$total_pages = $per_page > 0 ? (int) ceil( $total_count / $per_page ) : 0;

	$result = [
		'logs' => $logs,
		'pagination' => [
			'current_page' => $page,
			'per_page' => $per_page,
			'total_count' => $total_count,
			'total_pages' => $total_pages,
			'has_previous' => $page > 1,
			'has_next' => $page < $total_pages,
		],
	];

	return apply_filters( 'spar_user_referral_clicks_history_full', $result, (int) $user_id );
}

/**
 * Format a referral click landing URL for display.
 */
function spar_format_referral_click_landing_url( $url ) {
	if ( empty( $url ) ) {
		return esc_html__( '—', 'simple-points-and-rewards' );
	}
	$parts = wp_parse_url( $url );
	if ( ! is_array( $parts ) ) {
		$raw = preg_replace( '#^https?://#i', '', $url );
		$raw = rtrim( (string) $raw, '/' );
		return esc_html( $raw );
	}
	$host = isset( $parts['host'] ) ? (string) $parts['host'] : '';
	$path = isset( $parts['path'] ) ? (string) $parts['path'] : '';
	$display = $host . $path;
	$display = function_exists( 'untrailingslashit' ) ? untrailingslashit( $display ) : rtrim( $display, '/' );
	if ( '' === $display ) {
		$display = isset( $parts['path'] ) ? ltrim( (string) $parts['path'], '/' ) : '';
		$display = rtrim( $display, '/' );
	}
	return esc_html( $display );
}

/**
 * Build referral clicks table rows HTML.
 */
function spar_build_referral_clicks_table_rows( $logs, $context = 'dashboard' ) {
	$context = in_array( $context, [ 'dashboard', 'widget' ], true ) ? $context : 'dashboard';
	$columns = ( 'widget' === $context ) ? 2 : 4;
	$table_html = '';

	if ( ! empty( $logs ) ) {
		foreach ( $logs as $index => $log ) {
			$row_class = ( $index % 2 === 0 ) ? 'spar-row-even' : 'spar-row-odd';
			$date = '';
			if ( ! empty( $log['created_at'] ) ) {
				$date_format = ( 'widget' === $context ) ? 'M j, Y \\a\\t g:i A' : 'M j, Y \\a\\t g:i A';
				$date = date_i18n( $date_format, strtotime( $log['created_at'] ) );
			}
			$landing = spar_format_referral_click_landing_url( $log['landing_url'] ?? '' );
			if ( 'widget' === $context ) {
				$converted = ! empty( $log['converted'] ) ? esc_html__( 'Yes', 'simple-points-and-rewards' ) : esc_html__( 'No', 'simple-points-and-rewards' );
				$table_html .= sprintf(
					'<tr class="%s">'
					. '<td data-label="%s">%s</td>'
					. '<td data-label="%s">%s</td>'
					. '</tr>',
					esc_attr( $row_class ),
					esc_attr__( 'Date', 'simple-points-and-rewards' ),
					esc_html( $date ),
					esc_attr__( 'Converted', 'simple-points-and-rewards' ),
					esc_html( $converted )
				);
			} else {
				$converted = ! empty( $log['converted'] ) ? esc_html__( 'Yes', 'simple-points-and-rewards' ) : esc_html__( 'No', 'simple-points-and-rewards' );
				$referring_domain = ! empty( $log['referring_domain'] ) ? esc_html( $log['referring_domain'] ) : '&mdash;';
				$table_html .= sprintf(
					'<tr class="%s">'
					. '<td data-label="%s">%s</td>'
					. '<td data-label="%s">%s</td>'
					. '<td data-label="%s">%s</td>'
					. '<td class="spar-td-center" data-label="%s">%s</td>'
					. '</tr>',
					esc_attr( $row_class ),
					esc_attr__( 'Date', 'simple-points-and-rewards' ),
					esc_html( $date ),
					esc_attr__( 'Landing Page', 'simple-points-and-rewards' ),
					$landing,
					esc_attr__( 'Referring Domain', 'simple-points-and-rewards' ),
					$referring_domain,
					esc_attr__( 'Converted', 'simple-points-and-rewards' ),
					esc_html( $converted )
				);
			}
		}
	} else {
		$table_html = sprintf(
			'<tr class="spar-empty"><td colspan="%d">%s</td></tr>',
			(int) $columns,
			esc_html__( 'No referral clicks yet.', 'simple-points-and-rewards' )
		);
	}

	return $table_html;
}

/**
 * Build referral clicks pagination HTML.
 */
function spar_build_referral_clicks_pagination_html( $pagination, $context = 'dashboard' ) {
	$context = in_array( $context, [ 'dashboard', 'widget' ], true ) ? $context : 'dashboard';
	if ( empty( $pagination['total_pages'] ) || $pagination['total_pages'] <= 1 ) {
		return '';
	}

	$pagination_html = '';

	$pagination_html .= '<div class="spar-pagination-controls">';
	$prev_disabled = ! $pagination['has_previous'] ? 'disabled' : '';
	$pagination_html .= sprintf(
		'<button class="spar-pagination-btn spar-pagination-prev" data-page="%d" %s>%s</button>',
		$pagination['current_page'] - 1,
		$prev_disabled,
		esc_html__( '← Previous', 'simple-points-and-rewards' )
	);

	if ( 'widget' === $context ) {
		$current_page = (int) $pagination['current_page'];
		$total_pages  = (int) $pagination['total_pages'];
		$start_page   = max( 1, $current_page - 1 );
		$end_page     = min( $total_pages, $current_page + 1 );
		if ( 1 === $start_page ) {
			$end_page = min( $total_pages, 3 );
		} elseif ( $end_page === $total_pages ) {
			$start_page = max( 1, $total_pages - 2 );
		}
		for ( $i = $start_page; $i <= $end_page; $i++ ) {
			$is_current  = $i === $current_page;
			$btn_class   = $is_current ? 'spar-pagination-btn spar-pagination-current' : 'spar-pagination-btn spar-pagination-page';
			$btn_disabled = $is_current ? 'disabled' : '';
			$pagination_html .= sprintf(
				'<button class="%s" data-page="%d" %s>%d</button>',
				esc_attr( $btn_class ),
				$i,
				$btn_disabled,
				$i
			);
		}
	} else {
		$current_page = (int) $pagination['current_page'];
		$total_pages  = (int) $pagination['total_pages'];
		$start_page   = max( 1, $current_page - 2 );
		$end_page     = min( $total_pages, $current_page + 2 );
		if ( 1 === $start_page ) {
			$end_page = min( $total_pages, 5 );
		} elseif ( $end_page === $total_pages ) {
			$start_page = max( 1, $total_pages - 4 );
		}
		for ( $i = $start_page; $i <= $end_page; $i++ ) {
			$is_current = $i === $current_page;
			$btn_class = $is_current ? 'spar-pagination-btn spar-pagination-current' : 'spar-pagination-btn spar-pagination-page';
			$btn_disabled = $is_current ? 'disabled' : '';
			$pagination_html .= sprintf(
				'<button class="%s" data-page="%d" %s>%d</button>',
				esc_attr( $btn_class ),
				$i,
				$btn_disabled,
				$i
			);
		}
	}

	$next_disabled = ! $pagination['has_next'] ? 'disabled' : '';
	$pagination_html .= sprintf(
		'<button class="spar-pagination-btn spar-pagination-next" data-page="%d" %s>%s</button>',
		$pagination['current_page'] + 1,
		$next_disabled,
		esc_html__( 'Next →', 'simple-points-and-rewards' )
	);
	$pagination_html .= '</div>';

	if ( 'dashboard' === $context ) {
		$pagination_html .= '<div class="spar-pagination-loading"><span>' . esc_html__( 'Loading...', 'simple-points-and-rewards' ) . '</span></div>';
	}

	return $pagination_html;
}

/**
 * Create referral clicks log table on plugin activation
 */
function spar_create_referral_clicks_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'spar_referral_clicks';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE $table_name (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		created_at DATETIME NOT NULL,
		referral_code VARCHAR(64) NOT NULL,
		referrer_user_id BIGINT UNSIGNED NULL,
		referree_user_id BIGINT UNSIGNED NULL,
		landing_url TEXT NULL,
		referral_link TEXT NULL,
		referring_domain VARCHAR(191) NULL,
		converted TINYINT(1) NOT NULL DEFAULT 0,
		order_id BIGINT UNSIGNED NULL,
		gift_coupon_code VARCHAR(200) NULL,
		conversion_at DATETIME NULL,
		PRIMARY KEY  (id),
		KEY created_at (created_at),
		KEY referrer_user_id (referrer_user_id),
		KEY converted (converted),
		KEY order_id (order_id),
		KEY referral_code (referral_code)
	) $charset_collate;";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

/**
 * Create spins activity log table on plugin activation.
 */
function spar_create_spins_log_table() {
	global $wpdb;

	$table_name      = $wpdb->prefix . 'spar_spins_log';
	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE $table_name (
		id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
		user_id BIGINT UNSIGNED NOT NULL,
		event_type VARCHAR(50) NOT NULL,
		description TEXT NOT NULL,
		spins INT NOT NULL DEFAULT 1,
		date DATETIME NOT NULL,
		PRIMARY KEY  (id),
		KEY user_id (user_id),
		KEY event_type (event_type),
		KEY date (date)
	) $charset_collate;";

	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
	dbDelta( $sql );
}

/**
 * Log a spins activity event.
 *
 * @param int    $user_id    User ID.
 * @param string $event_type 'spin' or 'bonus_earned'.
 * @param string $description Human-readable description of the event.
 * @param int    $spins      Number of spins involved (default 1).
 */
function spar_log_spin_event( $user_id, $event_type, $description, $spins = 1 ) {
	global $wpdb;

	$table = $wpdb->prefix . 'spar_spins_log';

	// Create table if it doesn't exist (cached check).
	if ( ! spar_table_exists( $table ) ) {
		spar_create_spins_log_table();
	}

	$row = [
		'user_id'    => (int) $user_id,
		'event_type' => in_array( $event_type, [ 'spin', 'bonus_earned' ], true ) ? $event_type : 'spin',
		'description'=> sanitize_text_field( $description ),
		'spins'      => (int) $spins,
		'date'       => current_time( 'mysql' ),
	];

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
	$wpdb->insert( $table, $row, [ '%d', '%s', '%s', '%d', '%s' ] );
	// Flush all cached spins-log queries so pages reflect the new entry immediately.
	wp_cache_flush_group( 'spar' );
}

/**
 * Preview points for an order without awarding them.
 */
function spar_calculate_order_points_preview( $order, $user_id ) {
	if ( ! $order || ! $user_id ) {
		return 0;
	}

	$options = get_option( 'spar_options', [] );
	$has_spend = ! empty( $options['earn']['order']['enabled'] );
	$has_fixed = ! empty( $options['earn']['order_fixed']['enabled'] );
	if ( ! $has_spend && ! $has_fixed ) {
		return 0;
	}

	if ( ! function_exists( 'spar_calculate_order_base_points' ) ) {
		return 0;
	}

	$base_points = spar_calculate_order_base_points( $order );

	$currency     = method_exists( $order, 'get_currency' ) ? $order->get_currency() : '';
	$fixed_total  = function_exists( 'spar_get_points_calculation_total_from_order' ) ? spar_get_points_calculation_total_from_order( $order, 'order_fixed' ) : (float) $order->get_total();
	$fixed_info   = function_exists( 'spar_calculate_fixed_order_points_for_total' ) ? spar_calculate_fixed_order_points_for_total( $fixed_total, $currency ) : [ 'points' => 0 ];
	$fixed_points = isset( $fixed_info['points'] ) ? (int) $fixed_info['points'] : 0;
	$fixed_points = (int) apply_filters( 'spar_order_fixed_points', $fixed_points, $order, $fixed_info );

	$multiplier = 1.0;
	if ( function_exists( 'spar_get_user_points_multiplier' ) ) {
		$multiplier = spar_get_user_points_multiplier( $user_id, 'order' );
	}
	$multiplier = (float) apply_filters( 'spar_order_points_multiplier', $multiplier, (int) $user_id, (int) $order->get_id() );

	$points_raw = ( (int) $base_points + (int) $fixed_points ) * $multiplier;
	$points = (int) apply_filters( 'spar_order_awarded_points', (int) floor( $points_raw ), (int) $order->get_id(), (int) $user_id, (int) $base_points, (int) $fixed_points, (float) $multiplier );

	if ( function_exists( 'sparp_cr_calculate_conditionally_adjusted_points__premium_only' ) ) {
		$calc = sparp_cr_calculate_conditionally_adjusted_points__premium_only( (int) $base_points, (int) $fixed_points, (float) $multiplier, (int) $user_id, 'order', $order );
		if ( is_array( $calc ) && isset( $calc['adjusted'] ) ) {
			$points = max( 0, (int) $calc['adjusted'] );
		}
	}

	return max( 0, (int) $points );
}