<?php
/**
 * Custom field discovery service.
 *
 * @package RawTypeOE
 */

namespace RawTypeOE\Free;

// Prevent direct access.
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Scans product/order/customer meta tables for exportable custom fields.
 */
class Custom_Field_Scanner {

	/** Cache TTL in seconds (default 6h — meta keys rarely change). */
	const CACHE_TTL = 21600;

	/** Max number of distinct keys to scan. */
	const SCAN_LIMIT = 200;

	/** Cap on rows we sample per meta table when discovering keys. */
	const SAMPLE_ROW_LIMIT = 5000;

	/** TTL of the per-entity stampede lock (seconds). */
	const SCAN_LOCK_TTL = 60;

	/** Minimum seconds between forced re-scans of the same entity. */
	const REFRESH_COOLDOWN = 30;

	/** Prefix used for reversible custom-field column identifiers. */
	const COLUMN_KEY_PREFIX = 'meta__hex__';

	/**
	 * WordPress database connection.
	 *
	 * @var \wpdb|null
	 */
	private $wpdb;

	/**
	 * Constructor.
	 *
	 * @param \wpdb|null $wpdb Optional database connection.
	 */
	public function __construct( $wpdb = null ) {
		if ( null !== $wpdb ) {
			$this->wpdb = $wpdb;
			return;
		}

		global $wpdb;

		if ( isset( $wpdb ) ) {
			$this->wpdb = $wpdb;
		}
	}

	/**
	 * Get discovered fields for an entity.
	 *
	 * @param string $entity        Entity slug.
	 * @param bool   $show_internal Include private/internal keys.
	 * @param bool   $refresh       Force re-scan.
	 * @return array
	 */
	public function get_fields( $entity, $show_internal = false, $refresh = false ) {
		$entity = sanitize_key( $entity );
		if ( ! in_array( $entity, array( 'orders', 'products', 'customers' ), true ) ) {
			return array();
		}

		if ( ! $this->is_db_ready() ) {
			return array();
		}

		$cache_key = $this->get_cache_key( $entity );
		$lock_key  = $cache_key . '_lock';

		if ( $refresh ) {
			// Throttle forced re-scans: repeated refresh=true requests would
			// otherwise bypass the cache and hammer the meta tables.
			$cooldown_key = $cache_key . '_cooldown';

			if ( get_transient( $cooldown_key ) ) {
				$refresh = false;
			} else {
				set_transient( $cooldown_key, 1, self::REFRESH_COOLDOWN );
				delete_transient( $cache_key );
				delete_transient( $lock_key );
			}
		}

		$cached = get_transient( $cache_key );
		$fields = is_array( $cached ) ? $cached : null;

		if ( null === $fields ) {
			// Stampede guard: while another request is scanning, return an
			// empty list rather than queuing a second slow scan that would
			// pile on the DB. The scanner's transient will populate the cache
			// for subsequent requests within seconds.
			if ( get_transient( $lock_key ) ) {
				return array();
			}

			set_transient( $lock_key, 1, self::SCAN_LOCK_TTL );

			try {
				switch ( $entity ) {
					case 'products':
						$fields = $this->scan_product_fields();
						break;
					case 'orders':
						$fields = $this->scan_order_fields();
						break;
					case 'customers':
						$fields = $this->scan_customer_fields();
						break;
					default:
						$fields = array();
				}

				set_transient( $cache_key, $fields, self::CACHE_TTL );
			} finally {
				delete_transient( $lock_key );
			}
		}

		if ( ! $show_internal ) {
			$fields = array_values(
				array_filter(
					$fields,
					function ( $field ) {
						return empty( $field['is_internal'] );
					}
				)
			);
		}

		return $fields;
	}

	/**
	 * Scan product custom fields from postmeta.
	 *
	 * Previously this used `WHERE post_id IN (SELECT ID FROM posts WHERE post_type='product')`,
	 * which on stores with hundreds of thousands of products materialized the
	 * entire product ID set before the DISTINCT pass on postmeta — frequently
	 * timing out the REST request. We now sample a bounded slice of postmeta
	 * filtered by post_type via a JOIN with LIMIT so the planner can stop
	 * after a few thousand rows.
	 *
	 * @return array
	 */
	public function scan_product_fields() {
		if ( ! $this->is_db_ready() ) {
			return array();
		}

		$tables         = $this->get_tables();
		$postmeta_table = $this->sql_identifier( $tables['postmeta'] );
		$posts_table    = $this->sql_identifier( $tables['posts'] );
		if ( '' === $postmeta_table || '' === $posts_table ) {
			return array();
		}

		$sample_limit = $this->get_sample_row_limit();
		$scan_limit   = self::SCAN_LIMIT;

		$sql = $this->prepare_sql(
			'SELECT DISTINCT meta_key FROM ('
			. ' SELECT pm.meta_key FROM ' . $postmeta_table . ' pm'
			. ' INNER JOIN ' . $posts_table . ' p ON p.ID = pm.post_id'
			. ' WHERE p.post_type = %s'
			. ' LIMIT %d'
			. ') AS sample LIMIT %d',
			array( 'product', $sample_limit, $scan_limit )
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared via prepare_sql().
		$keys = $this->wpdb->get_col( $sql );

		return $this->build_fields_from_keys( 'products', $keys, $tables['postmeta'], 'post_id' );
	}

	/**
	 * Resolve the sample slice size used while discovering meta keys.
	 *
	 * Large enough to cover real-world key diversity, small enough to keep
	 * the scan well under a second on stores with millions of meta rows.
	 *
	 * @return int
	 */
	private function get_sample_row_limit() {
		$limit = (int) apply_filters( 'rawtype_oe_custom_field_sample_rows', self::SAMPLE_ROW_LIMIT );

		if ( $limit < 100 ) {
			return self::SAMPLE_ROW_LIMIT;
		}

		return $limit;
	}

	/**
	 * Resolve the slice size used to compute approximate counts/samples for
	 * the discovered meta keys. The aggregate runs over a bounded subquery so
	 * we never block the REST request on a full table scan.
	 *
	 * @return int
	 */
	private function get_aggregate_row_limit() {
		$limit = (int) apply_filters( 'rawtype_oe_custom_field_aggregate_rows', 10000 );

		if ( $limit < 500 ) {
			return 10000;
		}

		return $limit;
	}

	/**
	 * Scan order custom fields from wc_orders_meta (HPOS).
	 *
	 * @return array
	 */
	public function scan_order_fields() {
		if ( ! $this->is_db_ready() ) {
			return array();
		}

		$tables          = $this->get_tables();
		$order_table     = $tables['order_meta'];
		$order_table_sql = $this->sql_identifier( $order_table );
		if ( '' === $order_table_sql ) {
			return array();
		}

		// Sampling avoids a full DISTINCT scan over the meta_key index when the
		// table holds millions of rows.
		$sql = $this->prepare_sql(
			'SELECT DISTINCT meta_key FROM ('
			. ' SELECT meta_key FROM ' . $order_table_sql . ' LIMIT %d'
			. ') AS sample LIMIT %d',
			array( $this->get_sample_row_limit(), self::SCAN_LIMIT )
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared via prepare_sql().
		$keys = $this->wpdb->get_col( $sql );

		return $this->build_fields_from_keys( 'orders', $keys, $order_table, 'order_id' );
	}

	/**
	 * Scan customer custom fields from usermeta.
	 *
	 * @return array
	 */
	public function scan_customer_fields() {
		if ( ! $this->is_db_ready() ) {
			return array();
		}

		$tables         = $this->get_tables();
		$usermeta_table = $this->sql_identifier( $tables['usermeta'] );
		if ( '' === $usermeta_table ) {
			return array();
		}

		$sql = $this->prepare_sql(
			'SELECT DISTINCT meta_key FROM ('
			. ' SELECT meta_key FROM ' . $usermeta_table . ' LIMIT %d'
			. ') AS sample LIMIT %d',
			array( $this->get_sample_row_limit(), self::SCAN_LIMIT )
		);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared via prepare_sql().
		$keys = $this->wpdb->get_col( $sql );

		return $this->build_fields_from_keys( 'customers', $keys, $tables['usermeta'], 'user_id' );
	}

	/**
	 * Convert discovered fields into column definitions.
	 *
	 * @param string $entity        Entity slug.
	 * @param bool   $show_internal Include private/internal keys.
	 * @param bool   $refresh       Force re-scan.
	 * @return array
	 */
	public function get_columns_for_entity( $entity, $show_internal = false, $refresh = false ) {
		$fields  = $this->get_fields( $entity, $show_internal, $refresh );
		$columns = array();

		foreach ( $fields as $field ) {
			$columns[] = array(
				'key'         => $this->build_column_key( $field['key'] ),
				'label'       => $field['label'],
				'group'       => 'custom_fields',
				'default'     => false,
				'source'      => $field['source'],
				'type'        => $field['type'],
				'count'       => $field['count'],
				'is_internal' => ! empty( $field['is_internal'] ),
				'serialized'  => ! empty( $field['serialized'] ),
			);
		}

		return $columns;
	}

	/**
	 * Build field descriptors from raw meta keys.
	 *
	 * @param string $entity     Entity slug.
	 * @param array  $keys       Meta keys.
	 * @param string $table      Meta table.
	 * @param string $owner_col  Owner ID column.
	 * @return array
	 */
	private function build_fields_from_keys( $entity, $keys, $table, $owner_col ) {
		if ( ! is_array( $keys ) ) {
			return array();
		}

		$allowed_owner_cols = array( 'post_id', 'order_id', 'user_id' );
		if ( ! in_array( $owner_col, $allowed_owner_cols, true ) ) {
			return array();
		}

		$allowed_tables = array_values( $this->get_tables() );
		if ( ! in_array( $table, $allowed_tables, true ) ) {
			return array();
		}

		// Batch query: get count + sample for all keys in a single query instead of 2 per key.
		$sanitized_keys = array();
		foreach ( $keys as $meta_key ) {
			$sanitized = sanitize_text_field( (string) $meta_key );
			if ( '' !== $sanitized ) {
				$sanitized_keys[] = $sanitized;
			}
		}

		$table_sql = $this->sql_identifier( $table );
		if ( '' === $table_sql ) {
			return array();
		}

		$counts_map  = array();
		$samples_map = array();

		if ( ! empty( $sanitized_keys ) ) {
			// We only need an approximate count + a sample value; an accurate
			// COUNT(*) over the entire meta table per request would scan
			// millions of rows on large HPOS/usermeta installs. We scan a
			// bounded slice (default 10k rows) and aggregate inside that.
			$placeholders   = implode( ', ', array_fill( 0, count( $sanitized_keys ), '%s' ) );
			$aggregate_rows = $this->get_aggregate_row_limit();

			$args   = $sanitized_keys;
			$args[] = $aggregate_rows;

			$scan_sql = $this->prepare_sql(
				'SELECT meta_key, COUNT(*) AS cnt, MIN(NULLIF(meta_value, \'\')) AS sample FROM ('
				. ' SELECT meta_key, meta_value FROM ' . $table_sql
				. ' WHERE meta_key IN (' . $placeholders . ')'
				. ' LIMIT %d'
				. ') AS bounded GROUP BY meta_key',
				$args
			);

			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Prepared via prepare_sql().
			$scan_rows = $this->wpdb->get_results( $scan_sql );
			if ( is_array( $scan_rows ) ) {
				foreach ( $scan_rows as $row ) {
					$counts_map[ $row->meta_key ]  = (int) $row->cnt;
					$samples_map[ $row->meta_key ] = $row->sample;
				}
			}
		}

		$fields = array();

		foreach ( $sanitized_keys as $meta_key ) {
			$source    = $this->detect_source( $meta_key );
			$is_acf    = 'acf' === $source;
			$label     = $this->humanize_meta_key( $meta_key );
			$acf_label = $is_acf ? $this->resolve_acf_label( $meta_key ) : '';

			if ( '' !== $acf_label ) {
				$label = $acf_label;
			}

			$count      = isset( $counts_map[ $meta_key ] ) ? $counts_map[ $meta_key ] : 0;
			$sample     = isset( $samples_map[ $meta_key ] ) ? $samples_map[ $meta_key ] : null;
			$type       = $this->detect_type( $sample );
			$serialized = 'serialized' === $type;

			$fields[] = array(
				'key'         => $meta_key,
				'column_key'  => self::build_column_key( $meta_key ),
				'label'       => sanitize_text_field( $label ),
				'source'      => $source,
				'type'        => $type,
				'count'       => max( 0, $count ),
				'is_internal' => $this->is_internal_key( $meta_key ),
				'serialized'  => $serialized,
				'entity'      => $entity,
			);
		}

		usort(
			$fields,
			function ( $a, $b ) {
				return strcasecmp( $a['label'], $b['label'] );
			}
		);

		return $fields;
	}

	/**
	 * Resolve ACF label when available.
	 *
	 * @param string $meta_key Meta key.
	 * @return string
	 */
	private function resolve_acf_label( $meta_key ) {
		if ( ! function_exists( 'acf_get_field' ) ) {
			return '';
		}

		$field_key = ltrim( $meta_key, '_' );
		$field     = acf_get_field( $field_key );

		if ( is_array( $field ) && ! empty( $field['label'] ) ) {
			return sanitize_text_field( $field['label'] );
		}

		return '';
	}

	/**
	 * Determine source type for a meta key.
	 *
	 * @param string $meta_key Meta key.
	 * @return string
	 */
	private function detect_source( $meta_key ) {
		if ( 0 === strpos( $meta_key, '_field_' ) ) {
			return 'acf';
		}

		if ( $this->is_internal_key( $meta_key ) ) {
			return 'woocommerce';
		}

		return 'meta';
	}

	/**
	 * Detect field value type from sample.
	 *
	 * @param mixed $sample Sample value.
	 * @return string
	 */
	private function detect_type( $sample ) {
		if ( null === $sample || '' === $sample ) {
			return 'string';
		}

		if ( is_string( $sample ) && $this->is_serialized_value( $sample ) ) {
			return 'serialized';
		}

		if ( is_numeric( $sample ) ) {
			return 'number';
		}

		if ( is_string( $sample ) && false !== strtotime( $sample ) ) {
			return 'date';
		}

		return 'string';
	}

	/**
	 * Determine whether a meta key is private/internal.
	 *
	 * @param string $meta_key Meta key.
	 * @return bool
	 */
	private function is_internal_key( $meta_key ) {
		return 0 === strpos( $meta_key, '_' );
	}

	/**
	 * Humanize a raw meta key.
	 *
	 * @param string $meta_key Meta key.
	 * @return string
	 */
	private function humanize_meta_key( $meta_key ) {
		$key = ltrim( $meta_key, '_' );
		$key = str_replace( array( '_', '-' ), ' ', $key );
		$key = trim( preg_replace( '/\s+/', ' ', $key ) );

		if ( '' === $key ) {
			return $meta_key;
		}

		return ucwords( $key );
	}

	/**
	 * Check serialized values.
	 *
	 * @param string $value Candidate value.
	 * @return bool
	 */
	private function is_serialized_value( $value ) {
		return is_serialized( $value, false );
	}

	/**
	 * Build cache key for entity.
	 *
	 * @param string $entity Entity slug.
	 * @return string
	 */
	private function get_cache_key( $entity ) {
		return 'rawtype_oe_custom_fields_' . sanitize_key( $entity );
	}

	/**
	 * Build column key from raw meta key.
	 *
	 * @param string $meta_key Meta key.
	 * @return string
	 */
	public static function build_column_key( $meta_key ) {
		$meta_key = (string) $meta_key;

		if ( '' === $meta_key ) {
			return '';
		}

		return self::COLUMN_KEY_PREFIX . bin2hex( $meta_key );
	}

	/**
	 * Convert a column key back to raw-ish meta key.
	 *
	 * @param string $column_key Column key.
	 * @return string
	 */
	public static function parse_meta_key_from_column( $column_key ) {
		$column_key = (string) $column_key;
		if ( '' === $column_key ) {
			return '';
		}

		if ( 0 === strpos( $column_key, self::COLUMN_KEY_PREFIX ) ) {
			$encoded = substr( $column_key, strlen( self::COLUMN_KEY_PREFIX ) );

			if ( '' === $encoded || 1 === ( strlen( $encoded ) % 2 ) || ! ctype_xdigit( $encoded ) ) {
				return '';
			}

			$decoded = pack( 'H*', strtolower( $encoded ) );

			return is_string( $decoded ) ? $decoded : '';
		}

		return '';
	}

	/**
	 * Prepare SQL safely.
	 *
	 * @param string $sql  SQL template.
	 * @param array  $args Placeholder values.
	 * @return string
	 */
	private function prepare_sql( $sql, $args ) {
		if ( empty( $args ) ) {
			return $sql;
		}
		return $this->wpdb->prepare( $sql, ...$args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Wrapper method; callers pass validated SQL templates.
	}

	/**
	 * Quote a trusted SQL identifier.
	 *
	 * @param string $identifier Table or column name.
	 * @return string
	 */
	private function sql_identifier( $identifier ) {
		$identifier = (string) $identifier;

		if ( '' === $identifier || ! preg_match( '/^[A-Za-z0-9_]+$/', $identifier ) ) {
			return '';
		}

		return '`' . $identifier . '`';
	}

	/**
	 * Check DB object readiness.
	 *
	 * @return bool
	 */
	private function is_db_ready() {
		return is_object( $this->wpdb );
	}

	/**
	 * Get table names.
	 *
	 * @return array
	 */
	private function get_tables() {
		$prefix = isset( $this->wpdb->prefix ) ? $this->wpdb->prefix : '';

		return array(
			'posts'      => isset( $this->wpdb->posts ) ? $this->wpdb->posts : $prefix . 'posts',
			'postmeta'   => isset( $this->wpdb->postmeta ) ? $this->wpdb->postmeta : $prefix . 'postmeta',
			'usermeta'   => isset( $this->wpdb->usermeta ) ? $this->wpdb->usermeta : $prefix . 'usermeta',
			'order_meta' => $prefix . 'wc_orders_meta',
		);
	}
}
