<?php

namespace BetterLinks\Traits;
if ( ! defined( 'ABSPATH' ) ) { exit; }

use BetterLinks\Admin\Cache;

// phpcs:disable PluginCheck.Security.DirectDB, WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, WordPress.DB.SlowDBQuery

trait Query {


	public static function insert_link( $item, $is_update = false ) {
		global $wpdb;
		if ( $is_update ) {
			// An update without an ID cannot do anything — bail before we
			// generate PHP warnings by reading a missing 'ID' key four times.
			$id = isset( $item['ID'] ) ? $item['ID'] : null;
			if ( null === $id || '' === $id ) {
				return;
			}
			$item['ID'] = $id;
			// get_link_by_ID() returns an empty array for an ID that is no longer
			// in the table (migrations, stale caches), and current( array() ) is
			// false — wp_parse_args( $item, false ) is deprecated on PHP 8.1+ and
			// becomes a TypeError later. Fall back to the incoming item instead.
			$defaults              = self::get_link_by_ID( $id );
			$defaults              = is_array( $defaults ) && ! empty( $defaults ) ? current( $defaults ) : array();
			$item                  = is_array( $defaults ) ? wp_parse_args( $item, $defaults ) : $item;
			$link_data_array       = array(
				'link_author'       => $item['link_author'] ?? '',
				'link_date'         => $item['link_date'] ?? '',
				'link_date_gmt'     => $item['link_date_gmt'] ?? '',
				'link_title'        => $item['link_title'] ?? '',
				'link_slug'         => $item['link_slug'] ?? '',
				'link_note'         => $item['link_note'] ?? '',
				'link_status'       => $item['link_status'] ?? '',
				'nofollow'          => $item['nofollow'] ?? '',
				'sponsored'         => $item['sponsored'] ?? '',
				'track_me'          => $item['track_me'] ?? '',
				'param_forwarding'  => $item['param_forwarding'] ?? '',
				'param_struct'      => $item['param_struct'] ?? '',
				'redirect_type'     => $item['redirect_type'] ?? '',
				'target_url'        => $item['target_url'] ?? '',
				'short_url'         => $item['short_url'] ?? '',
				'link_order'        => $item['link_order'] ?? '',
				'link_modified'     => $item['link_modified'] ?? '',
				'link_modified_gmt' => $item['link_modified_gmt'] ?? '',
				'wildcards'         => $item['wildcards'] ?? '',
				'expire'            => $item['expire'] ?? '',
				'dynamic_redirect'  => $item['dynamic_redirect'] ?? '',
			);
			$link_data_place_array = array(
				'%d',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%s',
				'%d',
				'%s',
				'%s',
			);
			if ( isset( $item['favorite'] ) ) {
				$link_data_array['favorite'] = $item['favorite'];
				$link_data_place_array[]     = '%s';
			}
			if ( isset( $item['uncloaked'] ) ) {
				$link_data_array['uncloaked'] = $item['uncloaked'];
				$link_data_place_array[]      = '%s';
			}
			$wpdb->update(
				"{$wpdb->prefix}betterlinks",
				$link_data_array,
				array( 'ID' => $item['ID'] ),
				$link_data_place_array,
				array( '%d' )
			);
			do_action( 'betterlinks/after_update_link', $item['ID'], $item );
			return $item['ID'];
		} else {
			$betterlinks = self::get_link_by_short_url( $item['short_url'] );
			if ( count( $betterlinks ) === 0 ) {
				$initial_defaults_arr = array(
					'link_author'       => get_current_user_id(),
					'link_date'         => current_time( 'mysql' ),
					'link_date_gmt'     => current_time( 'mysql', 1 ),
					'link_title'        => '',
					'link_slug'         => '',
					'link_note'         => '',
					'link_status'       => 'publish',
					'nofollow'          => '',
					'sponsored'         => '',
					'track_me'          => '',
					'param_forwarding'  => '',
					'param_struct'      => '',
					'redirect_type'     => '',
					'target_url'        => '',
					'short_url'         => '',
					'link_order'        => '',
					'link_modified'     => current_time( 'mysql' ),
					'link_modified_gmt' => current_time( 'mysql', 1 ),
					'wildcards'         => '',
					'expire'            => '',
					'dynamic_redirect'  => '',
				);
				if ( isset( $item['favorite'] ) ) {
					$initial_defaults_arr['favorite'] = '';
				}
				$defaults            = apply_filters( 'betterlinks/insert_link_default_args', $initial_defaults_arr );
				$item                = wp_parse_args( $item, $defaults );
				$column_names        = 'link_author,link_date,link_date_gmt,link_title,link_slug,link_note,link_status,nofollow,sponsored,track_me,param_forwarding,param_struct,redirect_type,target_url,short_url,link_order,link_modified,link_modified_gmt,wildcards,expire,dynamic_redirect';
				$column_placeholders = '%d, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s';
				$query_value_array   = array(
					$item['link_author'],
					$item['link_date'],
					$item['link_date_gmt'],
					$item['link_title'],
					$item['link_slug'],
					$item['link_note'],
					$item['link_status'],
					$item['nofollow'],
					$item['sponsored'],
					$item['track_me'],
					$item['param_forwarding'],
					$item['param_struct'],
					$item['redirect_type'],
					$item['target_url'],
					$item['short_url'],
					$item['link_order'],
					$item['link_modified'],
					$item['link_modified_gmt'],
					$item['wildcards'],
					$item['expire'],
					$item['dynamic_redirect'],
				);
				if ( isset( $item['favorite'] ) ) {
					$column_names        .= ',favorite';
					$column_placeholders .= ', %s';
					$query_value_array[]  = $item['favorite'];
				}
				if ( isset( $item['uncloaked'] ) ) {
					$column_names        .= ',uncloaked';
					$column_placeholders .= ', %s';
					$query_value_array[]  = $item['uncloaked'];
				}
				$query_string = "INSERT INTO {$wpdb->prefix}betterlinks ( {$column_names} ) VALUES ( {$column_placeholders} )";
				$wpdb->query( $wpdb->prepare( $query_string, $query_value_array ) );
				do_action( 'betterlinks/after_insert_link', $wpdb->insert_id, $item );
				return $wpdb->insert_id;
			}
		}
		return;
	}
	public static function delete_link( $ID ) {
		global $wpdb;
		$wpdb->delete( "{$wpdb->prefix}betterlinks", array( 'ID' => $ID ), array( '%d' ) );
		$wpdb->delete( "{$wpdb->prefix}betterlinks_clicks", array( 'link_id' => $ID ), array( '%d' ) );
		$wpdb->delete( "{$wpdb->prefix}betterlinks_terms_relationships", array( 'link_id' => $ID ), array( '%d' ) );

		/**
		 * Fires after a link and its owned rows are removed.
		 *
		 * Extensions that store their own references to a link id clean them up here —
		 * Pro's Promo Cards use it in place of the FOREIGN KEY constraints its tables
		 * used to declare.
		 *
		 * @param int $ID Deleted link ID.
		 */
		do_action( 'betterlinks/link/after_delete', $ID );
	}
	public static function remove_terms_relationships_by_link_ID( $ID ) {
		global $wpdb;
		$wpdb->delete( "{$wpdb->prefix}betterlinks_terms_relationships", array( 'link_id' => $ID ), array( '%d' ) );
	}
	public static function get_prepare_all_links() {
		global $wpdb;
		$prefix   = $wpdb->prefix;
		$analytic = get_option( 'betterlinks_analytics_data' );
		$analytic = $analytic ? json_decode( $analytic, true ) : array();

		// pull all broken links logs
		$broken_links = get_option( 'betterlinkspro_broken_links_logs' );
		$broken_links = $broken_links ? json_decode( $broken_links, true ) : array();

		$settings = Cache::get_json_settings();

		// Categories a feature owns but does not want on the dashboard (Fluent
		// Boards' task category, the bio pages' "Link in Bio" category). Each
		// feature contributes its own term IDs and they are combined into one
		// exclusion, so adding a second one no longer overwrites the first.
		$hidden_term_ids = apply_filters( 'betterlinks/dashboard_hidden_term_ids', array(), $settings );
		$hidden_term_ids = array_unique( array_filter( array_map( 'intval', (array) $hidden_term_ids ) ) );

		// Back-compat: the original Fluent Boards filter returns a whole WHERE
		// clause rather than IDs. Keep honouring it and AND the ID list onto it.
		$fbs_category_query = apply_filters( 'betterlinks__intlfbs_filter_category_from_dashboard', '', $settings );

		if ( ! empty( $hidden_term_ids ) ) {
			$hidden_clause      = sprintf( 'bt.ID NOT IN (%s)', implode( ',', $hidden_term_ids ) );
			$fbs_category_query = empty( $fbs_category_query )
				? 'WHERE ' . $hidden_clause
				: $fbs_category_query . ' AND ' . $hidden_clause;
		}

		$query = "SELECT
            bt.ID as cat_id,
            bt.term_name,
            bt.term_slug,
            bt.term_type,
            bl.ID,
            bl.link_title,
            bl.link_slug,
            bl.link_note,
            bl.link_status,
            bl.nofollow,
            bl.sponsored,
            bl.track_me,
            bl.param_forwarding,
            bl.param_struct,
            bl.redirect_type,
            bl.target_url,
            bl.short_url,
            bl.link_date,
            bl.wildcards,
            bl.expire,
            bl.favorite,
            bl.dynamic_redirect,
            bl.uncloaked
            FROM {$prefix}betterlinks_terms as bt
            LEFT JOIN  {$prefix}betterlinks_terms_relationships as btr ON bt.ID = btr.term_id
            LEFT JOIN  {$prefix}betterlinks as bl ON bl.ID = btr.link_id
            -- WHERE bt.term_type = 'category'
			{$fbs_category_query}
            ORDER BY bl.link_order ASC;";

		$results = $wpdb->get_results(
			$query,
			OBJECT
		);
		$results = \BetterLinks\Helper::parse_link_response( $results, $analytic, $broken_links );
		return $results;
	}
	public static function get_link_by_short_url( $short_url, $is_case_sensitive = false ) {
		global $wpdb;
		$link = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE short_url=%s", $short_url ),
			ARRAY_A
		);
		if ( isset( $link[0]['short_url'] ) && $is_case_sensitive && $link[0]['short_url'] != $short_url ) {
			return array();
		}
		return $link;
	}
	public static function get_link_by_permalink( $target_url, $fields = '*' ) {
		global $wpdb;
		$link = $wpdb->get_row(
			$wpdb->prepare( "SELECT {$fields} FROM {$wpdb->prefix}betterlinks WHERE target_url=%s", $target_url ),
			ARRAY_A
		);
		return ! empty( $link ) ? $link : array();
	}
	public static function get_link_by_wildcards( $wildcards ) {
		global $wpdb;
		$link = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE wildcards=%d", $wildcards ),
			ARRAY_A
		);
		return $link;
	}
	public static function get_link_by_ID( $ID ) {
		global $wpdb;
		$link = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks WHERE ID=%d", $ID ),
			ARRAY_A
		);
		return $link;
	}
	public static function get_link_data_with_cat_id_by_link_id( $ID ) {
		global $wpdb;
		$link = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT 
            bt.ID as cat_id,
            bl.ID,
            bl.target_url,
            bl.short_url,
            bl.uncloaked
            FROM {$wpdb->prefix}betterlinks as bl
            INNER JOIN {$wpdb->prefix}betterlinks_terms_relationships as btr ON bl.ID = btr.link_id AND bl.ID=%d
            INNER JOIN {$wpdb->prefix}betterlinks_terms as bt ON bt.ID = btr.term_id AND bt.term_type = 'category'
            ",
				$ID
			),
			ARRAY_A
		);
		return $link;
	}

	/**
	 * Get All BetterLinks Uploads Links JSON File
	 *
	 * @return array
	 */
	public static function get_links_for_json() {
		global $wpdb;
		$prefix                                    = $wpdb->prefix;
		$formattedArray                            = array();
		// Changed from INNER JOIN to LEFT JOIN to include links without category assignments
		// This prevents links from disappearing when category relationships are delayed
		$items                                     = $wpdb->get_results(
			"SELECT
            bl.ID,
            bl.redirect_type,
            bl.short_url,
            bl.link_slug,
            bl.link_status,
            bl.target_url,
            bl.nofollow,
            bl.sponsored,
            bl.param_forwarding,
            bl.track_me,
            bl.wildcards,
            bl.expire,
            bl.dynamic_redirect,
            bl.uncloaked,
            br.term_id as cat_id
            FROM {$prefix}betterlinks as bl
            LEFT JOIN {$prefix}betterlinks_terms_relationships as br ON bl.ID = br.link_id
            LEFT JOIN {$prefix}betterlinks_terms as bt ON br.term_id = bt.ID AND bt.term_type = 'category'
            GROUP BY bl.ID
            ORDER BY bl.ID DESC
        "
		);
		$options                                   = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true );
		$formattedArray['is_case_sensitive']       = isset( $options['is_case_sensitive'] ) ? $options['is_case_sensitive'] : false;
		$formattedArray['is_disable_analytics_ip'] = isset( $options['is_disable_analytics_ip'] ) ? $options['is_disable_analytics_ip'] : false;
		$formattedArray['excluded_ips']            = isset( $options['excluded_ips'] ) ? $options['excluded_ips'] : array();
		$is_links_case_sensitive                   = $formattedArray['is_case_sensitive'];
		if ( ! empty( $options ) ) {
			$formattedArray['wildcards_is_active']         = isset( $options['wildcards'] ) ? $options['wildcards'] : false;
			$formattedArray['disablebotclicks']            = isset( $options['disablebotclicks'] ) ? $options['disablebotclicks'] : false;
			$formattedArray['force_https']                 = isset( $options['force_https'] ) ? $options['force_https'] : false;
			$formattedArray['autolink_disable_post_types'] = isset( $options['autolink_disable_post_types'] ) ? $options['autolink_disable_post_types'] : array();
			$formattedArray['is_autolink_icon']            = isset( $options['is_autolink_icon'] ) ? $options['is_autolink_icon'] : false;
			$formattedArray['is_autolink_headings']        = isset( $options['is_autolink_headings'] ) ? $options['is_autolink_headings'] : false;
			$formattedArray['uncloaked_categories']        = isset( $options['uncloaked_categories'] ) ? $options['uncloaked_categories'] : array();
		}
		if ( is_array( $items ) && count( $items ) > 0 ) {
			foreach ( $items as $item ) {
				$short_url = $is_links_case_sensitive ? $item->short_url : strtolower( $item->short_url );
				if ( $item->wildcards == true ) {
					$formattedArray['wildcards'][ $short_url ] = $item;
				} else {
					$formattedArray['links'][ $short_url ] = $item;
				}
			}
		}
		if ( defined( 'BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME' ) && BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME ) {
			$analytic_data = get_option( BETTERLINKS_PRO_EXTERNAL_ANALYTICS_OPTION_NAME, array() );
			if ( is_array( $analytic_data ) ) {
				$formattedArray = array_merge( $analytic_data, $formattedArray );
			} else {
				$analytic_data  = is_string( $analytic_data ) ? json_decode( $analytic_data, true ) : array();
				$formattedArray = array_merge( $analytic_data, $formattedArray );
			}
		}
		return $formattedArray;
	}

	public static function insert_term( $item, $is_update = false ) {
		global $wpdb;
		if ( $is_update ) {
			$wpdb->update(
				"{$wpdb->prefix}betterlinks_terms",
				array(
					'term_name' => $item['term_name'],
					'term_slug' => $item['term_slug'],
					'term_type' => $item['term_type'],
				),
				array( 'ID' => $item['ID'] ),
				array(
					'%s',
					'%s',
					'%s',
				),
				array( '%d' )
			);
			return $item['ID'];
		} else {
			$terms = self::get_term_by_slug( $item['term_slug'], $item['term_type'] );
			if ( count( $terms ) === 0 ) {
				$wpdb->query(
					$wpdb->prepare(
						"INSERT INTO {$wpdb->prefix}betterlinks_terms ( term_name, term_slug, term_type ) VALUES ( %s, %s, %s )",
						array( $item['term_name'], $item['term_slug'], $item['term_type'] )
					)
				);
				return $wpdb->insert_id;
			} elseif ( isset( current( $terms )['ID'] ) ) {
				return current( $terms )['ID'];
			}
		}
		return;
	}
	public static function insert_tags_terms( $tags ) {
		$terms_ids = array();
		if ( is_array( $tags ) && count( $tags ) > 0 ) {
			foreach ( $tags as $tag ) {
				$insert_id = self::insert_term(
					array(
						'term_name' => $tag,
						'term_slug' => \BetterLinks\Helper::make_slug( $tag ),
						'term_type' => 'tags',
					)
				);
				if ( $insert_id ) {
					$terms_ids[] = $insert_id;
				}
			}
		}
		return $terms_ids;
	}

	public static function insert_category_terms( $categories ) {
		$terms_ids = array();
		if ( is_array( $categories ) && count( $categories ) > 0 ) {
			foreach ( $categories as $category ) {
				$insert_id = self::insert_term(
					array(
						'term_name' => $category,
						'term_slug' => \BetterLinks\Helper::make_slug( $category ),
						'term_type' => 'category',
					)
				);
				if ( $insert_id ) {
					$terms_ids[] = $insert_id;
				}
			}
		}
		return $terms_ids;
	}
	public static function insert_terms_relationships( $term_id, $link_id ) {
		global $wpdb;
		$wpdb->query(
			$wpdb->prepare(
				"INSERT INTO {$wpdb->prefix}betterlinks_terms_relationships ( term_id, link_id ) VALUES ( %d, %d )",
				array( $term_id, $link_id )
			)
		);
		return $wpdb->insert_id;
	}

	/**
	 * Delete term and update Term relationship to uncategorized
	 *
	 * @param term_id
	 * @return boolean
	 */
	public static function delete_term_and_update_term_relationships( $term_id ) {
		global $wpdb;
		$wpdb->query( 'START TRANSACTION' );
		$is_delete = $wpdb->delete( $wpdb->prefix . 'betterlinks_terms', array( 'ID' => $term_id ), array( '%d' ) );
		if ( $is_delete ) {
			$term = self::get_term_by_slug( 'uncategorized' );
			if ( count( $term ) > 0 ) {
				$wpdb->update(
					"{$wpdb->prefix}betterlinks_terms_relationships",
					array(
						'term_id' => current( $term )['ID'],
					),
					array( 'term_id' => $term_id ),
					array(
						'%d',
					),
					array( '%d' )
				);
			}
		}
		$wpdb->query( 'COMMIT' );
		return $is_delete;
	}

	public static function insert_terms_and_terms_relationship( $link_id, $request ) {
		global $wpdb;
		$term_data   = array();
		$newTermList = array();
		
		// If no category is provided, check for default category setting.
		//
		// The stored value must be a real term ID. A non-numeric value falls
		// through to the "new category" branch below, which treats cat_id as a
		// NAME and creates a term called after it — so a boolean `true` in
		// settings (which is what some saves write) minted a junk category
		// literally named "1" on every link created without a category.
		if ( empty( $request['cat_id'] ) ) {
			$settings = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true );
			$default  = isset( $settings['default_category'] ) ? $settings['default_category'] : null;

			if ( is_numeric( $default ) && (int) $default > 0 ) {
				$request['cat_id'] = (int) $default;
			} else {
				// Fallback to Uncategorized category (ID 1)
				$request['cat_id'] = 1;
			}
		}
		
		// store tags relation data
		if ( ! empty( $request['cat_id'] ) ) {
			$is_new_cat = true;
			if ( is_numeric( $request['cat_id'] ) ) {
				$query  = $wpdb->prepare(
					"SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE id = %d ",
					$request['cat_id']
				);
				$result = $wpdb->get_row( $query, 'ARRAY_A' );
				if ( isset( $result['term_slug'] ) ) {
					$is_new_cat  = false;
					$term_data[] = array(
						'term_id'   => $request['cat_id'],
						'link_id'   => $link_id,
						'term_slug' => $result['term_slug'],
						'term_name' => $result['term_name'],
						'term_type' => 'category',
					);
				}
			}
			// A NUMERIC cat_id is an ID, never a name. If it did not resolve above
			// the term is gone (a stale default_category, say) — fall back to
			// Uncategorized rather than minting a category literally named "42".
			// Non-numeric values are genuine "user typed a new category" input and
			// still create a term.
			if ( $is_new_cat && is_numeric( $request['cat_id'] ) ) {
				$is_new_cat = false;
				$fallback   = self::get_term_by_slug( 'uncategorized' );
				if ( count( $fallback ) > 0 ) {
					$fallback    = current( $fallback );
					$term_data[] = array(
						'term_id'   => $fallback['ID'],
						'link_id'   => $link_id,
						'term_slug' => $fallback['term_slug'],
						'term_name' => $fallback['term_name'],
						'term_type' => 'category',
					);
				}
			}
			if ( $is_new_cat ) {
				$newTermList[] = array(
					'term_name' => $request['cat_id'],
					'term_slug' => isset( $request['cat_slug'] ) ? $request['cat_slug'] : $request['cat_id'],
					'term_type' => 'category',
				);
			}
		}
		if ( isset( $request['tags_id'] ) && is_array( $request['tags_id'] ) ) {
			foreach ( $request['tags_id'] as $key => $value ) {
				$is_new_tag = true;
				if ( is_numeric( $value ) ) {
					$query  = $wpdb->prepare(
						"SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE id = %d ",
						$value
					);
					$result = $wpdb->get_row( $query, 'ARRAY_A' );
					if ( isset( $result['term_slug'] ) ) {
						$term_data[] = array(
							'link_id'   => $link_id,
							'term_id'   => $value,
							'term_slug' => $result['term_slug'],
							'term_name' => $result['term_name'],
							'term_type' => 'tags',
						);
						$is_new_tag  = false;
					}
				}
				if ( $is_new_tag ) {
					$newTermList[] = array(
						'term_name' => $value,
						'term_slug' => $value,
						'term_type' => 'tags',
					);
				}
			}
		}

		// insert new tags or category
		if ( count( $newTermList ) > 0 ) {
			foreach ( $newTermList as $item ) {
				$term_id     = \BetterLinks\Helper::insert_term( $item );
				$term_data[] = array(
					'link_id'          => $link_id,
					'term_id'          => $term_id,
					'term_type'        => $item['term_type'],
					'term_name'        => $item['term_name'],
					'term_slug'        => $item['term_slug'],
					'is_newly_created' => true,
				);
			}
		}
		// make term and link relation
		if ( count( $term_data ) > 0 ) {
			$is_delete = $wpdb->delete( $wpdb->prefix . 'betterlinks_terms_relationships', array( 'link_id' => $link_id ), array( '%d' ) );
			if ( $is_delete || $is_delete === 0 ) {
				foreach ( $term_data as $term ) {
					\BetterLinks\Helper::insert_terms_relationships( $term['term_id'], $term['link_id'] );
				}
			}
		}
		return $term_data;
	}

	public static function is_term_exists( $term_id, $type = 'category' ) {
		global $wpdb;
		$result = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE ID=%s AND term_type=%s", $term_id, $type ),
			ARRAY_A
		);
		return count( $result ) === 1;
	}

	public static function get_term_by_slug( $slug, $type = 'category' ) {
		global $wpdb;
		$result = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE term_slug=%s AND term_type=%s", $slug, $type ),
			ARRAY_A
		);
		return $result;
	}

	// Get term by ID for AI
	public static function get_term_by_id( $term_id, $type = 'category' ) {
		global $wpdb;
		$result = $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$wpdb->prefix}betterlinks_terms WHERE ID=%d AND term_type=%s", $term_id, $type ),
			ARRAY_A
		);
		return $result;
	}

	public static function get_terms_by_link_ID_and_term_type( $link_ID, $term_type = 'categroy' ) {
		global $wpdb;
		$prefix = $wpdb->prefix;
		$link   = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT
            {$prefix}betterlinks_terms.ID as term_id,
            {$prefix}betterlinks_terms.term_name,
            {$prefix}betterlinks_terms.term_slug,
            {$prefix}betterlinks_terms.term_type
            FROM {$prefix}betterlinks_terms
            LEFT JOIN  {$prefix}betterlinks_terms_relationships ON {$prefix}betterlinks_terms.ID = {$prefix}betterlinks_terms_relationships.term_id
            LEFT JOIN  {$prefix}betterlinks ON {$prefix}betterlinks.ID = {$prefix}betterlinks_terms_relationships.link_id
            WHERE {$prefix}betterlinks_terms_relationships.link_id = %d
            AND {$prefix}betterlinks_terms.term_type = %s",
				$link_ID,
				$term_type
			),
			ARRAY_A
		);
		return $link;
	}

	public static function get_terms_all_data() {
		global $wpdb;
		$query = "SELECT t.ID, t.term_name, t.term_slug, t.term_type, t.term_order, COALESCE(tr.link_count, 0) as link_count FROM {$wpdb->prefix}betterlinks_terms AS t LEFT JOIN (SELECT term_id, COUNT(term_id) AS link_count FROM {$wpdb->prefix}betterlinks_terms_relationships GROUP BY term_id) AS tr ON t.ID=tr.term_id ORDER BY t.term_order ASC, t.term_name ASC";
		$link = $wpdb->get_results( $query, ARRAY_A );
		return $link;
	}

	public static function insert_click( $item ) {
		global $wpdb;
		$betterlinks                       = array();
		$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false );
		if ( isset( $item['short_url'] ) ) {
			$betterlinks = self::get_link_by_short_url( $item['short_url'] );
		} elseif ( isset( $item['link_id'] ) ) {
			$betterlinks = self::get_link_by_ID( $item['link_id'] );
		}
		if( empty( $betterlinks ) ){
			return;
		}
		$is_analytics_ip_enabled = isset( $item['ip'] ) && isset( $item['host'] );
		$is_country_enabled = isset( $item['country_code'] ) && isset( $item['country_name'] );

		$addedPlaceholderString  = $is_analytics_ip_enabled ? ' created_at_gmt, rotation_target_url, ip, host ' : ' created_at_gmt, rotation_target_url ';
		$addedDbColumnsString    = $is_analytics_ip_enabled ? ' %s, %s, %s, %s ' : ' %s, %s ';

		// Use country_id for normalized schema
		$country_id = null;
		if ( $is_country_enabled ) {
			// Get or create country record
			$country_id = \BetterLinks\Services\CountryDetectionService::get_or_create_country_id(
				$item['country_code'],
				$item['country_name']
			);

			if ( $country_id ) {
				$addedPlaceholderString .= ', country_id';
				$addedDbColumnsString   .= ', %d';
			}
		}
	// Check if user agent tracking is enabled AND column exists
	$settings = get_option( BETTERLINKS_LINKS_OPTION_NAME, '[]' );
	if ( is_string( $settings ) ) {
		$settings = json_decode( $settings, true );
	}
	$is_user_agent_tracking_enabled = ! empty( $settings['enable_user_agent_tracking'] );
	
	// Cache the column existence check to avoid querying information_schema on every redirect
	$user_agent_column_exists = self::get_user_agent_column_exists();
	
	// Handle user agent if tracking is enabled, column exists, and user agent is provided
	$user_agent_id = null;
	$should_include_user_agent = $is_user_agent_tracking_enabled && $user_agent_column_exists && isset( $item['user_agent'] );
	if ( $should_include_user_agent ) {
		$user_agent_id = self::get_or_insert_user_agent_id( $item['user_agent'] );
	}		// Pro's extra-data tracking already carries bot_name in its column block;
		// on free, write it on its own so the human-vs-bot split has data there too.
		$should_include_bot_name = ! $is_extra_data_tracking_compatible && \BetterLinks\Helper::has_bot_name_column();

		if ( $is_extra_data_tracking_compatible ) {
			$addedPlaceholderString .= ', brand_name, model, bot_name, browser_type, os_version, browser_version, language, query_params';
			$addedDbColumnsString   .= ', %s, %s, %s, %s, %s, %s, %s, %s';
		} elseif ( $should_include_bot_name ) {
			$addedPlaceholderString .= ', bot_name';
			$addedDbColumnsString   .= ', %s';
		}

		// Add user agent ID to the query only if column exists
		if ( $should_include_user_agent ) {
			$addedPlaceholderString .= ', user_agent_id';
			$addedDbColumnsString   .= ', %s';
		}
		
		if( empty($betterlinks) || empty( current( $betterlinks )['ID'] ) ) return;
		$query         = "INSERT INTO {$wpdb->prefix}betterlinks_clicks ( link_id, browser, os,device, referer, uri, click_count, visitor_id, click_order, created_at,  $addedPlaceholderString ) VALUES ( %d, %s, %s, %s, %s, %s, %d, %s, %d, %s,  $addedDbColumnsString )";
		$db_data_array = array(
			current( $betterlinks )['ID'],
			isset( $item['browser'] ) ? $item['browser'] : '',
			isset( $item['os'] ) ? $item['os'] : '',
			isset( $item['device'] ) ? $item['device'] : '',
			isset( $item['referer'] ) ? $item['referer'] : '',
			isset( $item['uri'] ) ? $item['uri'] : '',
			isset( $item['click_count'] ) ? $item['click_count'] : 0,
			isset( $item['visitor_id'] ) ? $item['visitor_id'] : '',
			isset( $item['click_order'] ) ? $item['click_order'] : '',
			isset( $item['created_at']) ? $item['created_at'] : '',
			isset( $item['created_at_gmt']) ? $item['created_at_gmt'] : '',
			isset( $item['rotation_target_url']) ? $item['rotation_target_url'] : '',
		);
		if ( $is_analytics_ip_enabled ) {
			$db_data_array[] = isset( $item['ip'] ) ? $item['ip'] : '';
			$db_data_array[] = isset( $item['host'] ) ? $item['host'] : '';
		}

		if ( $is_country_enabled && $country_id ) {
			$db_data_array[] = $country_id;
		}
		// $db_data_array[] = isset($item['device']) ? $item['device'] : '';
		if ( $is_extra_data_tracking_compatible ) {
			$db_data_array[] = isset( $item['brand_name'] ) ? $item['brand_name'] : '';
			$db_data_array[] = isset( $item['model'] ) ? $item['model'] : '';
			$db_data_array[] = isset( $item['bot_name'] ) ? $item['bot_name'] : '';
			$db_data_array[] = isset( $item['browser_type'] ) ? $item['browser_type'] : '';
			$db_data_array[] = isset( $item['os_version'] ) ? $item['os_version'] : '';
			$db_data_array[] = isset( $item['browser_version'] ) ? $item['browser_version'] : '';
			$db_data_array[] = isset( $item['language'] ) ? $item['language'] : '';
			$db_data_array[] = isset( $item['query_params'] ) ? $item['query_params'] : '';
		} elseif ( $should_include_bot_name ) {
			$db_data_array[] = isset( $item['bot_name'] ) ? $item['bot_name'] : '';
		}

		// Add user agent ID to data array only if column exists
		if ( $should_include_user_agent ) {
			$db_data_array[] = $user_agent_id;
		}
		
		if ( isset( current( $betterlinks )['ID'] ) ) {
			$wpdb->query(
				$wpdb->prepare( $query, $db_data_array )
			);
			return $wpdb->insert_id;
		}
		return;
	}

	public static function get_or_insert_user_agent_id( $user_agent ) {
		global $wpdb;
		
		if ( empty( $user_agent ) ) {
			return null;
		}
		
		// Check if user_agents table exists first
		$user_agents_table_exists = $wpdb->get_var( 
			$wpdb->prepare( 
				"SHOW TABLES LIKE %s", 
				$wpdb->prefix . 'betterlinks_user_agents' 
			)
		);
		
		if ( ! $user_agents_table_exists ) {
			return null; // Table doesn't exist, return null gracefully
		}
		
		// First try to get existing user agent ID
		$existing_id = $wpdb->get_var( 
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}betterlinks_user_agents WHERE user_agent = %s LIMIT 1",
				$user_agent
			)
		);
		
		if ( $existing_id ) {
			return (int) $existing_id;
		}
		
		// Insert new user agent if not exists
		$result = $wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO {$wpdb->prefix}betterlinks_user_agents (user_agent) VALUES (%s)",
				$user_agent
			)
		);
		
		if ( $result ) {
			return (int) $wpdb->insert_id;
		}
		
		// If INSERT IGNORE failed due to race condition, try to get ID again
		$existing_id = $wpdb->get_var( 
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}betterlinks_user_agents WHERE user_agent = %s LIMIT 1",
				$user_agent
			)
		);
		
		return $existing_id ? (int) $existing_id : null;
	}

	public static function get_linksNips_count() {
		global $wpdb;

		$query = "select link_id, ip, ipc, t2.lidc from ( select ip, link_id, count(ip) as ipc from {$wpdb->prefix}betterlinks_clicks group by ip, link_id ) as t1
        left join ( select link_id as lid, sum(ipc) as lidc from ( select ip, link_id, count(uri) as ipc from {$wpdb->prefix}betterlinks_clicks group by ip,uri, link_id ) as t3 group by link_id ) as t2
        on t1.link_id = t2.lid";

		$results = $wpdb->get_results( $query, ARRAY_A );
		return $results;
	}

	public static function get_clicks_count($from = '', $to = '') {
		global $wpdb;
		
		// Get excluded IPs and build condition safely
		$options      = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true );
		$excluded_ips = isset( $options['excluded_ips'] ) && is_array( $options['excluded_ips'] ) ? $options['excluded_ips'] : array();
		
		$where_conditions = array();
		$query_params = array();
		
		// Add date range condition
		if ( '' !== $from && '' !== $to ) {
			$where_conditions[] = 'created_at BETWEEN %s AND %s';
			$query_params[] = $from . ' 00:00:00';
			$query_params[] = $to . ' 23:59:59';
		}
		
		// Add excluded IPs condition
		if ( ! empty( $excluded_ips ) ) {
			$placeholders = implode( ', ', array_fill( 0, count( $excluded_ips ), '%s' ) );
			$where_conditions[] = "ip NOT IN ({$placeholders})";
			$query_params = array_merge( $query_params, $excluded_ips );
		}
		
		$where_clause = ! empty( $where_conditions ) ? 'WHERE ' . implode( ' AND ', $where_conditions ) : '';

		// Total clicks query. ORDER BY keeps this aligned with the unique query below;
		// callers must still merge the two sets on link_id, never on row position.
		$total_query = "SELECT link_id, count(id) as total_clicks from {$wpdb->prefix}betterlinks_clicks {$where_clause} group by link_id ORDER BY link_id";
		$total_clicks = ! empty( $query_params ) ? $wpdb->get_results( $wpdb->prepare( $total_query, $query_params ), ARRAY_A ) : $wpdb->get_results( $total_query, ARRAY_A );

		// Unique clicks query  
		$unique_query = "SELECT T1.link_id, count(ip) as unique_clicks from ( SELECT ip, link_id FROM {$wpdb->prefix}betterlinks_clicks {$where_clause} GROUP BY `ip`, `link_id` ) as T1 GROUP BY T1.link_id ORDER BY T1.link_id";
		$unique_clicks = ! empty( $query_params ) ? $wpdb->get_results( $wpdb->prepare( $unique_query, $query_params ), ARRAY_A ) : $wpdb->get_results( $unique_query, ARRAY_A );

		return array(
			'total_clicks'  => $total_clicks,
			'unique_clicks' => $unique_clicks,
		);
	}

	public static function get_links_analytics() {
		global $wpdb;
		$prefix  = $wpdb->prefix;
		$results = $wpdb->get_results(
			"SELECT DISTINCT link_id, ip,
			(select count(ip) from {$prefix}betterlinks_clicks WHERE CLICKS.ip = {$prefix}betterlinks_clicks.ip  group by ip) as IPCOUNT,
			(select count(link_id) from {$prefix}betterlinks_clicks WHERE CLICKS.link_id = {$prefix}betterlinks_clicks.link_id group by link_id) as LINKCOUNT
			from {$prefix}betterlinks_clicks as CLICKS group by CLICKS.id",
			ARRAY_A
		);
		return $results;
	}

	public static function clear_analytics_cache() {
		global $wpdb;
		$prefix                          = $wpdb->prefix;
		$individual_analytics_cache_keys = 'btl_individual_analytics_clicks_|btl_individual_graph_data_';
		// Every btl_analytics_* transient belongs here — one left out keeps serving
		// figures from before the clicks changed until its own 30-minute TTL runs
		// out, which reads as "the report is broken".
		$all_analytics_cache_keys        = 'betterlinks_analytics_data|btl_analytics_unique_list_|btl_analytics_unique_list_by_tag_|btl_analytics_graph_|btl_analytics_graph_by_tag_|btl_analytics_audience_|btl_analytics_timing_|btl_top_referer_|btl_click_stats_|btl_top_os_|btl_top_browser_|btl_all_referer_|btl_tags_analytics|btl_categories_analytics|btl_analytics_data_|btl_unique_clicks_count_';
		$query                           = "DELETE FROM {$prefix}options WHERE option_name regexp '{$individual_analytics_cache_keys}|{$all_analytics_cache_keys}'";

		$result = $wpdb->query( $query );
		return $result;
	}

	public static function search_clicks_data( $keyword ) {
		global $wpdb;
		$prefix                            = $wpdb->prefix;
		$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false );
		$extra_data_tracking_columns       = $is_extra_data_tracking_compatible ? 'os, device, brand_name, ' : '';
		$results                           = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT CLICKS.ID as
            click_ID, link_id, browser, {$extra_data_tracking_columns} created_at, referer, SUBSTRING_INDEX(SUBSTRING_INDEX(referer, '/', 3), '/', -1) AS domain, short_url, target_url, ip, {$prefix}betterlinks.link_title,
            (select count(id) from {$prefix}betterlinks_clicks where CLICKS.ip = {$prefix}betterlinks_clicks.ip group by ip) as IPCOUNT
            from {$prefix}betterlinks_clicks as CLICKS left join {$prefix}betterlinks on {$prefix}betterlinks.id = CLICKS.link_id WHERE {$prefix}betterlinks.link_title LIKE %s
            or {$prefix}betterlinks.short_url like %s
            or {$prefix}betterlinks.target_url like %s
            or CLICKS.browser like %s
            or CLICKS.ip like %s
            or CLICKS.referer like %s
            group by CLICKS.id ORDER BY CLICKS.created_at DESC",
				'%' . $keyword . '%',
				'%' . $keyword . '%',
				'%' . $keyword . '%',
				'%' . $keyword . '%',
				'%' . $keyword . '%',
				'%' . $keyword . '%'
			),
			ARRAY_A
		);
		return $results;
	}

	public static function get_clicks_by_date( $from, $to ) {
		global $wpdb;
		$prefix                            = $wpdb->prefix;
		$is_extra_data_tracking_compatible = apply_filters( 'betterlinks/is_extra_data_tracking_compatible', false );
		$extra_data_tracking_columns       = $is_extra_data_tracking_compatible ? 'CLICKS.os, CLICKS.device, CLICKS.brand_name, ' : '';
		
		// Get excluded IPs and build condition safely
		$options      = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME ), true );
		$excluded_ips = isset( $options['excluded_ips'] ) && is_array( $options['excluded_ips'] ) ? $options['excluded_ips'] : array();
		
		$query_params = array( $from . ' 00:00:00', $to . ' 23:59:00' );
		$where_conditions = array( 'CLICKS.created_at BETWEEN %s AND %s' );
		
		if ( ! empty( $excluded_ips ) ) {
			$placeholders = implode( ', ', array_fill( 0, count( $excluded_ips ), '%s' ) );
			$where_conditions[] = "CLICKS.ip NOT IN ({$placeholders})";
			$query_params = array_merge( $query_params, $excluded_ips );
		}
		
		$where_clause = implode( ' AND ', $where_conditions );
		
		$query = "SELECT 
                CLICKS.ID AS click_ID, 
                CLICKS.link_id, 
                CLICKS.browser, 
                {$extra_data_tracking_columns}
                CLICKS.created_at, 
                CLICKS.referer,
                SUBSTRING_INDEX(SUBSTRING_INDEX(CLICKS.referer, '/', 3), '/', -1) AS domain,
                {$prefix}betterlinks.short_url, 
                {$prefix}betterlinks.target_url, 
                CLICKS.ip, 
                {$prefix}betterlinks.link_title
            FROM 
                {$prefix}betterlinks_clicks AS CLICKS 
                LEFT JOIN {$prefix}betterlinks ON {$prefix}betterlinks.id = CLICKS.link_id 
            WHERE 
                {$where_clause}
            GROUP BY 
                CLICKS.id 
            ORDER BY 
                CLICKS.created_at DESC limit 100000";
		
		$results = $wpdb->get_results( $wpdb->prepare( $query, $query_params ), ARRAY_A );
		return $results;
	}


	public static function get_thirstyaffiliates_links() {
		$thirstylinks      = get_posts(
			array(
				'posts_per_page' => -1,
				'post_type'      => 'thirstylink',
				'post_status'    => 'publish',
			)
		);
		$response          = array();
		$betterlinks_links = json_decode( get_option( 'betterlinks_links', '{}' ), true );
		foreach ( $thirstylinks as $thirstylink ) {
			$term              = wp_get_post_terms( $thirstylink->ID, 'thirstylink-category', array( 'fields' => 'names' ) );
			$nofollow          = get_post_meta( $thirstylink->ID, '_ta_no_follow', true );
			$nofollow          = ( $nofollow == 'global' ? get_option( 'ta_no_follow', true ) : $nofollow );
			$redirect_type     = get_post_meta( $thirstylink->ID, '_ta_redirect_type', true );
			$redirect_type     = ( $redirect_type == 'global' ? get_option( 'ta_link_redirect_type', true ) : $redirect_type );
			$param_forwarding  = get_post_meta( $thirstylink->ID, '_ta_pass_query_str', true );
			$param_forwarding  = ( $param_forwarding == 'global' ? get_option( 'ta_pass_query_str', true ) : $param_forwarding );
			$dynamic_redirect  = array();
			$geolocation_links = get_post_meta( $thirstylink->ID, '_ta_geolocation_links', true );
			if ( $geolocation_links && is_array( $geolocation_links ) ) {
				$dynamic_redirect_value = array();
				foreach ( $geolocation_links as $key => $geolocation_link ) {
					$dynamic_redirect_value[] = array(
						'link'    => $geolocation_link,
						'country' => explode( ',', $key ),
					);
				}
				$dynamic_redirect = array(
					'type'  => 'geographic',
					'value' => $dynamic_redirect_value,
					'extra' => array(),
				);
			}
			$link_date = get_post_meta( $thirstylink->ID, '_ta_link_start_date', true );
			// expire
			$expire              = array();
			$expire_date         = get_post_meta( $thirstylink->ID, '_ta_link_expire_date', true );
			$expire_redirect_url = get_post_meta( $thirstylink->ID, '_ta_after_expire_redirect', true );
			if ( ! empty( $expire_date ) ) {
				$expire = array(
					'status' => 1,
					'type'   => 'date',
					'date'   => $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() {
		// Pull free settings (betterlinks_links holds force_https, affiliate_link_disclosure,
		// excluded_ips, custom_domain.*) and Pro option blobs in one batch — all reads are
		// cheap option lookups. Pro options return defaults on free-only installs.
		$links_options       = json_decode( get_option( BETTERLINKS_LINKS_OPTION_NAME, '{}' ), true );
		$links_options       = is_array( $links_options ) ? $links_options : array();
		$pro_external_anal   = get_option( 'betterlinkspro_ga', array() );
		$pro_external_anal   = is_array( $pro_external_anal ) ? $pro_external_anal : array();
		$pro_auto_link_raw   = get_option( 'betterlinkspro_auto_link_create', '' );
		$pro_auto_link       = is_string( $pro_auto_link_raw ) && '' !== $pro_auto_link_raw ? json_decode( $pro_auto_link_raw, true ) : array();
		$pro_auto_link       = is_array( $pro_auto_link ) ? $pro_auto_link : array();
		$pro_reporting_raw   = get_option( 'betterlinkspro_reporting', '' );
		$pro_reporting       = is_string( $pro_reporting_raw ) && '' !== $pro_reporting_raw ? json_decode( $pro_reporting_raw, true ) : array();
		$pro_reporting       = is_array( $pro_reporting ) ? $pro_reporting : array();
		$pro_broken_cfg_raw  = get_option( 'betterlinkspro_broken_link', '' );
		$pro_broken_cfg      = is_string( $pro_broken_cfg_raw ) && '' !== $pro_broken_cfg_raw ? json_decode( $pro_broken_cfg_raw, true ) : array();
		$pro_broken_cfg      = is_array( $pro_broken_cfg ) ? $pro_broken_cfg : array();

		// AI Link Assistant ships in Pro 2.8.0+ and its toggles (in betterlinks_links)
		// default ON, so a missing key looks "enabled" on every install. Gate on the
		// stored Pro version to avoid over-reporting on free / older-Pro sites.
		$pro_version            = get_option( 'betterlinks_pro_version', '' );
		$is_link_assistant_live = ! empty( $pro_version ) && version_compare( $pro_version, '2.8.0', '>=' );

		return array(
			// existing — kept for backward compatibility with WPInsights dashboards
			'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 ) ),
			// new — settings toggles (Pro feature adoption)
			'is_ga_enabled'                   => ! empty( $pro_external_anal['is_enable_ga'] ),
			'is_pixel_enabled'                => ! empty( $pro_external_anal['is_enable_pixel'] ),
			'is_custom_scripts_enabled'       => ! empty( $pro_external_anal['is_enable_custom_scripts'] ),
			'is_custom_domain_configured'     => ! empty( $links_options['custom_domain']['enable_shortlink_custom_domain'] ) && ! empty( $links_options['custom_domain']['shortlink_custom_domain'] ),
			'is_force_https_enabled'          => ! empty( $links_options['force_https'] ),
			'is_affiliate_disclosure_enabled' => ! empty( $links_options['affiliate_link_disclosure'] ),
			'is_exclude_ips_configured'       => isset( $links_options['excluded_ips'] ) && is_array( $links_options['excluded_ips'] ) && ! empty( $links_options['excluded_ips'] ),
			'is_auto_create_links_enabled'    => ! empty( $pro_auto_link['post_shortlinks'] ) || ! empty( $pro_auto_link['page_shortlinks'] ),
			'is_email_reports_enabled'        => ! empty( $pro_reporting['enable_reporting'] ),
			'is_broken_link_scan_enabled'     => ! empty( $pro_broken_cfg['enable_scan'] ),
			// AI Link Assistant — actual usage markers (set by Pro when the feature runs),
			// the reliable signal for adoption since the settings toggles default ON.
			'ai_link_assistant_used'                => !empty( get_option( 'betterlinks_link_genius_used', false ) ),
			'link_suggestion_used'            => !empty( get_option( 'betterlinks_raw_link_rescue_used', false ) ),
		);
	}

	/**
	 * Per-redirect-type adoption rollup, sent to WPInsights so product can see
	 * which redirect modes users actually create.
	 *
	 * Counts:
	 *   - `cloak_redirect_count`               rows where redirect_type='cloak' (Pro)
	 *   - `dynamic_redirect_rotation_count`    dynamic_redirect.type='rotation' (split test)
	 *   - `dynamic_redirect_geographic_count`  dynamic_redirect.type='geographic'
	 *   - `dynamic_redirect_device_count`      dynamic_redirect.type='device'
	 *
	 * LIKE patterns are used instead of MySQL JSON functions for compatibility
	 * with MySQL 5.6 / MariaDB 10.1.
	 */
	public static function get_redirect_type_breakdown() {
		global $wpdb;
		// COALESCE wraps each SUM so a links table with zero matching rows
		// reports 0 instead of NULL in the WPInsights payload.
		$query = "SELECT
				COALESCE(SUM(redirect_type='cloak'), 0) AS cloak_redirect_count,
				COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"rotation\"%'), 0) AS dynamic_redirect_rotation_count,
				COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"geographic\"%'), 0) AS dynamic_redirect_geographic_count,
				COALESCE(SUM(dynamic_redirect LIKE '%\"type\":\"device\"%'), 0) AS dynamic_redirect_device_count
			FROM {$wpdb->prefix}betterlinks;";

		$count = $wpdb->get_row( $query, ARRAY_A );
		return is_array( $count ) ? $count : array();
	}

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