=> $expire_date,
				);
			}
			if ( ! empty( $expire_redirect_url ) ) {
				$expire['redirect_status'] = 1;
				$expire['redirect_url']    = $expire_redirect_url;
			}
			// link status
			$link_status = 'publish';
			$now         = time();
			if ( ! empty( $link_date ) && $now < strtotime( $link_date ) ) {
				$link_status = 'scheduled';
			}
			if ( ! empty( $expire_date ) && $now > strtotime( $expire_date ) ) {
				$link_status = 'draft';
			}
			// keywords
			$keywords   = get_post_meta( $thirstylink->ID, '_ta_autolink_keyword_list', true );
			$limit      = get_post_meta( $thirstylink->ID, '_ta_autolink_keyword_limit', true );
			$response[] = array(
				'link_title'        => $thirstylink->post_title,
				'link_slug'         => $thirstylink->post_name,
				'link_date'         => $link_date ? $link_date : '',
				'link_date_gmt'     => $link_date ? $link_date : '',
				'link_status'       => $link_status,
				'short_url'         => trim( \BetterLinks\Helper::force_relative_url( get_the_permalink( $thirstylink->ID ) ), '/' ),
				'link_author'       => $thirstylink->post_author,
				'link_date'         => $thirstylink->post_date,
				'link_date_gmt'     => $thirstylink->post_date_gmt,
				'nofollow'          => ( $nofollow == 'yes' ? 1 : 0 ),
				'sponsored'         => $betterlinks_links['sponsored'],
				'track_me'          => $betterlinks_links['track_me'],
				'redirect_type'     => $redirect_type,
				'param_forwarding'  => ( $param_forwarding == 'yes' ? 1 : 0 ),
				'target_url'        => get_post_meta( $thirstylink->ID, '_ta_destination_url', true ),
				'link_modified'     => $thirstylink->post_modified,
				'link_modified_gmt' => $thirstylink->post_modified_gmt,
				'terms'             => $term,
				'expire'            => json_encode( $expire ),
				'dynamic_redirect'  => json_encode( $dynamic_redirect ),
				'keywords'          => $keywords,
				'limit'             => $limit,
			);
		}
		return $response;
	}

	public static function get_prettylinks_links_count() {
		global $wpdb;
		$links = $wpdb->get_var( "SELECT COUNT(id) FROM {$wpdb->prefix}prli_links" );
		return $links;
	}
	public static function get_prettylinks_clicks_count() {
		global $wpdb;
		$clicks = $wpdb->get_var( "SELECT COUNT(id) FROM {$wpdb->prefix}prli_clicks" );
		return $clicks;
	}

	public static function get_link_meta( $link_id, $meta_key ) {
		global $wpdb;
		$table = $wpdb->prefix . 'betterlinkmeta';
		if ( empty( $link_id ) || empty( $meta_key ) ) {
			return false;
		}
		$query   = $wpdb->prepare( "SELECT meta_value FROM $table WHERE meta_key=%s AND link_id=%d", $meta_key, $link_id );
		$results = $wpdb->get_results( $query );
		if ( ! empty( $results ) ) {
			if ( is_serialized( current( $results )->meta_value, true ) ) {
				return current( $results )->meta_value;
			}
			if ( is_string( current( $results )->meta_value ) ) {
				return json_decode( current( $results )->meta_value );
			}

			return json_decode( current( $results )->meta_value );
		}
		return false;
	}

	public static function add_link_meta( $link_id, $meta_key, $meta_value ) {
		global $wpdb;
		$meta_key   = wp_unslash( $meta_key );
		$meta_value = wp_unslash( $meta_value );

		if ( isset( $meta_value['keywords'] ) ) {
			$meta_value['keywords'] = preg_replace( '/\’|\'|\‘/', "'", $meta_value['keywords'] );
		}
		$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false );
		if ( empty( $link_id ) || empty( $meta_key ) ) {
			return false;
		}
		$result = $wpdb->insert(
			$wpdb->prefix . 'betterlinkmeta',
			array(
				'link_id'    => $link_id,
				'meta_key'   => $meta_key,
				'meta_value' => $meta_value,
			)  // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
		);  // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
		if ( ! $result ) {
			return false;
		}
		return (int) $wpdb->insert_id;
	}
	public static function update_link_meta( $link_id, $meta_key, $meta_value, $old_keywords = false, $old_link_id = false ) {
		global $wpdb;
		$table      = $wpdb->prefix . 'betterlinkmeta';
		$link_id    = absint( $link_id );
		$meta_key   = wp_unslash( $meta_key );
		$meta_value = wp_unslash( $meta_value );
		if ( isset( $meta_value['keywords'] ) ) {
			$meta_value['keywords'] = preg_replace( '/\’|\'|\‘/', "'", $meta_value['keywords'] );
		}
		$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false );
		if ( empty( $link_id ) || empty( $meta_key ) ) {
			return false;
		}
		$result = false;
		if ( $old_keywords && $old_link_id ) {
			$keywordPattern = wp_slash( '%"keywords":' . wp_json_encode( wp_unslash( $old_keywords ) ) . ',"link_id":%' );
			$result         = $wpdb->query(
				$wpdb->prepare(
					"UPDATE $table
                SET meta_value = %s, link_id = %d
                WHERE link_id = %d AND meta_key=%s AND meta_value LIKE %s LIMIT 1",
					$meta_value,
					$link_id,
					$old_link_id,
					$meta_key,
					$keywordPattern
				)
			);
		} else {
			$result = $wpdb->query(
				$wpdb->prepare(
					"UPDATE $table
                SET meta_value = %s
                WHERE link_id = %d AND meta_key=%s",
					$meta_value,
					$link_id,
					$meta_key
				)
			);
		}
		return ! ! $result;
	}

	public static function delete_link_meta( $link_id, $meta_key, $meta_value = '', $keywords = false ) {
		global $wpdb;
		$table = $wpdb->prefix . 'betterlinkmeta';
		if ( empty( $link_id ) || empty( $meta_key ) ) {
			return false;
		}
		$query = $wpdb->prepare( "SELECT link_id FROM $table WHERE meta_key = %s AND link_id = %d", $meta_key, $link_id );
		if ( ! empty( $keywords ) ) {
			$keywordPattern = wp_slash( '%"keywords":' . wp_json_encode( wp_unslash( $keywords ) ) . ',"link_id":%' );
			$query          = $wpdb->prepare(
				"SELECT meta_id FROM $table WHERE meta_key = %s AND link_id = %d AND meta_value LIKE %s LIMIT 1",
				$meta_key,
				$link_id,
				$keywordPattern
			);
		}
		if ( ! empty( $meta_value ) ) {
			$query .= $wpdb->prepare( ' AND meta_value = %s', $meta_value );
		}
		$meta_ids = $wpdb->get_col( $query );
		if ( ! count( $meta_ids ) ) {
			return false;
		}
		$query = "DELETE FROM $table WHERE meta_id IN( " . implode( ',', $meta_ids ) . ' )';
		$count = $wpdb->query( $query );
		return ! ! $count;
	}

	public static function get_keywords() {
		global $wpdb;
		$results = $wpdb->get_results(
			$wpdb->prepare( "SELECT meta_value FROM {$wpdb->prefix}betterlinkmeta WHERE meta_key=%s ORDER BY meta_id DESC", 'keywords' ),
			ARRAY_A
		);
		$results = array_column( $results, 'meta_value' );
		return $results;
	}

	public static function get_keywords_for_export() {
		global $wpdb;
		$results = $wpdb->get_results(
			$wpdb->prepare( "SELECT meta_id, meta_value FROM {$wpdb->prefix}betterlinkmeta WHERE meta_key=%s ORDER BY meta_id DESC", 'keywords' ),
			ARRAY_A
		);
		return $results;
	}

	public static function update_link_meta_by_meta_id( $meta_id, $link_id, $meta_key, $meta_value ) {
		global $wpdb;
		$table      = $wpdb->prefix . 'betterlinkmeta';
		$meta_id    = absint( $meta_id );
		$link_id    = absint( $link_id );
		$meta_key   = wp_unslash( $meta_key );
		$meta_value = wp_unslash( $meta_value );
		if ( isset( $meta_value['keywords'] ) ) {
			$meta_value['keywords'] = preg_replace( '/\'|\'|\'/', "'", $meta_value['keywords'] );
		}
		$meta_value = \BetterLinks\Helper::maybe_json( $meta_value, false );
		if ( empty( $meta_id ) || empty( $link_id ) || empty( $meta_key ) ) {
			return false;
		}
		$result = $wpdb->update(
			$table,
			array(
				'meta_value' => $meta_value,
				'link_id'    => $link_id,
			),  // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
			array(
				'meta_id'  => $meta_id,
				'meta_key' => $meta_key,
			)
		);  // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
		return $result !== false;
	}

	public static function get_link_data_by_id( $id, $fields ) {
		global $wpdb;
		$query  = $wpdb->prepare( "SELECT `{$fields}` from {$wpdb->prefix}betterlinks WHERE id=%d", array( $id ) );
		$result = $wpdb->get_var( $query );
		return $result;
	}

	public static function get_link_count(){
		global $wpdb;
		$query = "SELECT COUNT(*) AS total_link, 
				SUM(wildcards) AS wildcards,
				SUM(expire != '' and expire != '{}') AS link_expire,
				SUM(dynamic_redirect != '' and dynamic_redirect != '{}') AS dynamic_redirect,
				SUM(id=link_id and meta_key='keywords') as auto_link_keyword
			FROM {$wpdb->prefix}betterlinks as links left join {$wpdb->prefix}betterlinkmeta as meta on links.id=meta.link_id;";

		$count = $wpdb->get_row( $query, ARRAY_A );
		return is_array( $count ) ? $count : [];
	}
	public static function get_password_protected_link_count(){
		if( ! apply_filters('betterlinks/pro_enabled', false) ){
			return [
				'password_protected' => 0,
				'active_password_protected' => 0
			];
		}
		global $wpdb;
		$query = "SELECT COUNT(*) AS password_protected, SUM(status) AS active_password_protected FROM {$wpdb->prefix}betterlinks_password;";
		$count = $wpdb->get_row( $query, ARRAY_A );
		return is_array( $count ) ? $count : [];
	}

	public static function get_prettylinks_data() {
		$links_count  = self::get_prettylinks_links_count();
		$clicks_count = self::get_prettylinks_clicks_count();
		set_transient(
			'betterlinks_migration_data_prettylinks',
			array(
				'links_count'  => $links_count,
				'clicks_count' => $clicks_count,
			),
			60 * 5
		);
		return array(
			'links_count'  => $links_count,
			'clicks_count' => $clicks_count,
		);
	}

	public static function used_features_by_client() {
		return [
			'betterlinks_broken_link_scanner' => !empty( get_option( 'betterlinkspro_broken_links_logs', [] ) ),
			'fullsite_link_scanner' => !empty( get_option('betterlinkspro_fullsite_broken_links_logs_cleared', 0) ) || !empty( get_option( 'betterlinkspro_fullsite_broken_links_logs', [] ) ),
			'ai_link_generator' => !empty( get_option( 'betterlinks_ai_generator_used', false ) ),
			'utm_builder' => !empty( get_option( 'betterlinks_utm_builder_used', false ) ),
		];
	}

	/**
	 * Cached check for user_agent_id column existence to avoid hitting information_schema on every redirect
	 * 
	 * @return bool
	 */
	private static function get_user_agent_column_exists() {
		global $wpdb;
		
		$transient_key = 'betterlinks_user_agent_column_exists';
		$column_exists = get_transient( $transient_key );
		
		if ( $column_exists === false ) {
			// Only hit information_schema when not cached
			$column_exists = $wpdb->get_var( 
				$wpdb->prepare( 
					'SELECT `column_name` FROM information_schema.columns WHERE table_schema=%s AND table_name=%s AND column_name="user_agent_id"',
					DB_NAME,
					$wpdb->prefix . 'betterlinks_clicks'
				)
			);
			
			// Cache for 1 hour - column structure doesn't change often
			set_transient( $transient_key, $column_exists ? 'yes' : 'no', HOUR_IN_SECONDS );
		}
		
		return $column_exists === 'yes';
	}
}