<?php

defined( 'ABSPATH' ) || exit;

class AIEO_SEO_Content_Gaps {

	
	const UNHAPPY_BELOW = 40;
	
	const MIN_OCCURRENCES = 2;
	
	const MAPPER_VERSION = 2;
	const MAPPER_OPTION  = 'aieo_content_gaps_mapper';

	
	private static $catalogue_words = array();

	public static function table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_chat_content_gaps';
	}

	private static function history_table() {
		global $wpdb;
		return $wpdb->prefix . 'aieo_chat_history';
	}

	private static function table_exists( $t ) {
		global $wpdb;
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $t ) ) === $t;
	}

	
	public static function rebuild( $max_rows = 40000 ) {
		global $wpdb;
		$gaps = self::table();
		$hist = self::history_table();
		
		
		if ( ! self::table_exists( $gaps ) && class_exists( 'AI_eShop_Chat_Database_Manager' ) ) {
			AI_eShop_Chat_Database_Manager::get_instance()->create_tables();
		}
		if ( ! self::table_exists( $gaps ) || ! self::table_exists( $hist ) ) {
			return array( 'gaps' => 0, 'turns_scanned' => 0 );
		}

		
		
		$days   = (int) apply_filters( 'aieo_chat_retention_days', 30 );
		$days   = $days > 0 ? $days : 30;
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - $days * DAY_IN_SECONDS );

		$buckets = array(); 
		$scanned = 0;
		$last_id = 0;
		$below   = self::UNHAPPY_BELOW;

		
		
		while ( $scanned < $max_rows ) {
			$rows = $wpdb->get_results( $wpdb->prepare(
				"SELECT id, user_message, user_rating, products_found
				   FROM `{$hist}`
				  WHERE id > %d
				    AND created_at >= %s
				    AND (
				          products_found IS NULL OR products_found = '' OR products_found = '[]'
				          OR ( user_rating IS NOT NULL AND user_rating < %d )
				        )
				  ORDER BY id ASC
				  LIMIT 1000",
				$last_id, $cutoff, $below
			), ARRAY_A );
			if ( empty( $rows ) ) {
				break;
			}
			foreach ( $rows as $r ) {
				$last_id = (int) $r['id'];
				$scanned++;
				$q = trim( (string) $r['user_message'] );
				if ( '' === $q || self::is_small_talk( $q ) ) {
					
					
					continue;
				}
				list( $norm, $key ) = self::normalize( $q );
				if ( '' === $key ) {
					continue;
				}
				if ( ! isset( $buckets[ $key ] ) ) {
					$buckets[ $key ] = array( 'sample' => $q, 'occ' => 0, 'zero' => 0, 'unhappy' => 0, 'rsum' => 0, 'rcnt' => 0 );
				}
				$b = &$buckets[ $key ];
				$b['occ']++;
				$pf = trim( (string) $r['products_found'] );
				if ( '' === $pf || '[]' === $pf ) {
					$b['zero']++;
				}
				if ( null !== $r['user_rating'] && '' !== (string) $r['user_rating'] ) {
					$b['rsum'] += (int) $r['user_rating'];
					$b['rcnt']++;
					if ( (int) $r['user_rating'] < $below ) {
						$b['unhappy']++;
					}
				}
				unset( $b );
			}
			if ( count( $rows ) < 1000 ) {
				break;
			}
		}

		$now = current_time( 'mysql', true );
		foreach ( $buckets as $key => $b ) {
			$occ           = (int) $b['occ'];
			$zero_share    = $occ ? $b['zero'] / $occ : 0.0;
			$unhappy_share = $occ ? $b['unhappy'] / $occ : 0.0;
			$freq          = 1.0 - exp( -$occ / 5.0 ); 
			$weight        = (float) min( 100, round( $freq * 70 + $zero_share * 20 + $unhappy_share * 10 ) );
			$avg_sql       = $b['rcnt'] > 0 ? (float) round( $b['rsum'] / $b['rcnt'], 1 ) : 'NULL';

			
			
			
			$wpdb->query( $wpdb->prepare(
				"INSERT INTO `{$gaps}`
				   (gap_key, sample_question, occurrences, zero_product_turns, unhappy_turns, avg_rating, demand_weight, status, first_seen, last_seen, updated_at)
				 VALUES (%s, %s, %d, %d, %d, {$avg_sql}, %f, 'open', %s, %s, %s)
				 ON DUPLICATE KEY UPDATE
				   sample_question = VALUES(sample_question),
				   occurrences     = VALUES(occurrences),
				   zero_product_turns = VALUES(zero_product_turns),
				   unhappy_turns   = VALUES(unhappy_turns),
				   avg_rating      = VALUES(avg_rating),
				   demand_weight   = VALUES(demand_weight),
				   last_seen       = VALUES(last_seen),
				   updated_at      = VALUES(updated_at)",
				$key, mb_substr( $b['sample'], 0, 500 ), $occ, (int) $b['zero'], (int) $b['unhappy'], $weight, $now, $now, $now
			) );
		}

		
		
		$wpdb->query( $wpdb->prepare(
			"DELETE FROM `{$gaps}` WHERE status = 'open' AND last_seen < %s",
			$cutoff
		) );

		return array( 'gaps' => count( $buckets ), 'turns_scanned' => $scanned );
	}

	
	public static function top( $limit = 20, array $args = array() ) {
		global $wpdb;
		$gaps = self::table();
		if ( ! self::table_exists( $gaps ) ) {
			return array();
		}
		$limit         = min( 100, max( 1, (int) $limit ) );
		$min           = max( 1, (int) ( $args['min_occurrences'] ?? self::MIN_OCCURRENCES ) );
		$status        = isset( $args['status'] ) ? sanitize_key( (string) $args['status'] ) : '';
		$only_unmapped = ! empty( $args['only_unmapped'] );

		$where = array( 'occurrences >= %d' );
		$bind  = array( $min );
		if ( in_array( $status, array( 'open', 'dismissed', 'mapped' ), true ) ) {
			$where[] = 'status = %s';
			$bind[]  = $status;
		} else {
			$where[] = "status <> 'dismissed'";
		}
		if ( $only_unmapped ) {
			$where[] = 'mapped_object_id IS NULL';
		}
		$bind[] = $limit;
		$sql    = "SELECT gap_key, sample_question, occurrences, zero_product_turns, unhappy_turns, avg_rating,
		                  demand_weight, status, mapped_object_type, mapped_object_id, first_seen, last_seen
		             FROM `{$gaps}` WHERE " . implode( ' AND ', $where ) . '
		            ORDER BY demand_weight DESC, occurrences DESC LIMIT %d';
		
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $bind ), ARRAY_A );
		return is_array( $rows ) ? $rows : array();
	}

	
	public static function resolve_mappings( $limit = 50 ) {
		global $wpdb;
		$gaps = self::table();
		if ( ! self::table_exists( $gaps ) ) {
			return array( 'scanned' => 0, 'mapped' => 0, 'unmapped' => 0 );
		}
		$limit = min( 200, max( 1, (int) $limit ) );
		self::repair();

		
		$rows = $wpdb->get_results( $wpdb->prepare(
			"SELECT gap_key, sample_question FROM `{$gaps}`
			  WHERE mapped_object_id IS NULL AND status <> 'dismissed'
			  ORDER BY demand_weight DESC, occurrences DESC LIMIT %d",
			$limit
		), ARRAY_A );
		if ( empty( $rows ) ) {
			return array( 'scanned' => 0, 'mapped' => 0, 'unmapped' => 0 );
		}

		$mapped = 0;
		foreach ( $rows as $r ) {
			$hit = self::find_destination( (string) $r['sample_question'] );
			if ( empty( $hit ) ) {
				continue;
			}
			$wpdb->update(
				$gaps,
				array(
					'mapped_object_type' => $hit['type'],
					'mapped_object_id'   => $hit['id'],
					'status'             => 'mapped',
					'updated_at'         => current_time( 'mysql', true ),
				),
				array( 'gap_key' => (string) $r['gap_key'] ),
				array( '%s', '%d', '%s', '%s' ),
				array( '%s' )
			);
			$mapped++;
		}

		return array(
			'scanned'  => count( $rows ),
			'mapped'   => $mapped,
			'unmapped' => count( $rows ) - $mapped,
		);
	}

	
	public static function repair( $force = false ) {
		global $wpdb;
		$done = array( 'removed' => 0, 'reopened' => 0, 'remapped' => 0 );
		if ( ! $force && (int) get_option( self::MAPPER_OPTION, 1 ) >= self::MAPPER_VERSION ) {
			return $done;
		}
		$gaps = self::table();
		if ( ! self::table_exists( $gaps ) ) {
			return $done;
		}
		
		$rows = (array) $wpdb->get_results(
			"SELECT gap_key, sample_question, status, mapped_object_type, mapped_object_id FROM `{$gaps}` WHERE status <> 'dismissed'",
			ARRAY_A
		);
		foreach ( $rows as $r ) {
			$key = (string) $r['gap_key'];
			if ( self::is_small_talk( (string) $r['sample_question'] ) ) {
				$wpdb->delete( $gaps, array( 'gap_key' => $key ), array( '%s' ) );
				$done['removed']++;
				continue;
			}
			if ( empty( $r['mapped_object_id'] ) ) {
				continue;
			}
			$hit = self::find_destination( (string) $r['sample_question'] );
			if ( empty( $hit ) ) {
				$wpdb->query( $wpdb->prepare( 
					"UPDATE %i SET status = 'open', mapped_object_type = NULL, mapped_object_id = NULL, updated_at = %s WHERE gap_key = %s",
					$gaps,
					current_time( 'mysql', true ),
					$key
				) );
				$done['reopened']++;
			} elseif ( $hit['type'] !== (string) $r['mapped_object_type'] || (int) $hit['id'] !== (int) $r['mapped_object_id'] ) {
				$wpdb->update(
					$gaps,
					array( 'mapped_object_type' => $hit['type'], 'mapped_object_id' => $hit['id'], 'updated_at' => current_time( 'mysql', true ) ),
					array( 'gap_key' => $key ),
					array( '%s', '%d', '%s' ),
					array( '%s' )
				);
				$done['remapped']++;
			}
		}
		update_option( self::MAPPER_OPTION, self::MAPPER_VERSION, false );
		return $done;
	}

	
	private static function find_destination( $question ) {
		list( $norm ) = self::normalize( $question );
		if ( '' === $norm ) {
			return array();
		}
		$taxes = array_values( array_filter(
			array( 'product_cat', 'product_tag', 'category', 'post_tag' ),
			'taxonomy_exists'
		) );
		$types = array_values( array_filter(
			array( 'page', 'post', 'product' ),
			'post_type_exists'
		) );
		$words = self::content_words( $norm, $taxes, array_values( array_intersect( $types, array( 'product' ) ) ) );
		if ( empty( $words ) ) {
			return array();
		}

		$best = null;
		if ( ! empty( $taxes ) ) {
			$trank = array_flip( $taxes ); 
			$seen  = array();
			foreach ( $words as $w ) {
				$terms = get_terms( array(
					'taxonomy'   => $taxes,
					'name__like' => $w,
					'hide_empty' => true,
					'number'     => 20,
				) );
				if ( is_wp_error( $terms ) || empty( $terms ) ) {
					continue;
				}
				foreach ( $terms as $t ) {
					if ( isset( $seen[ $t->term_id ] ) ) {
						continue;
					}
					$seen[ $t->term_id ] = true;
					$sc = self::match_score( $t->name, $words );
					$nw = self::name_words( $t->name );
					if ( ! self::score_is_confident( $sc, count( $words ), $nw ) ) {
						continue;
					}
					
					
					
					
					$extra = $nw - $sc['score'];
					$tr    = isset( $trank[ $t->taxonomy ] ) ? (int) $trank[ $t->taxonomy ] : 99;
					$cand  = array( -$sc['score'], $extra, $tr, (int) $t->count );
					if ( null === $best || $cand < $best['rank'] ) {
						$best = array(
							'rank' => $cand,
							'type' => 'term',
							'id'   => (int) $t->term_id,
						);
					}
				}
			}
		}
		if ( null !== $best ) {
			return array( 'type' => 'term', 'id' => $best['id'] );
		}

		if ( empty( $types ) ) {
			return array();
		}
		$posts = get_posts( array(
			's'                => $norm,
			'post_type'        => $types,
			'post_status'      => 'publish',
			'posts_per_page'   => 10,
			'suppress_filters' => false,
			'no_found_rows'    => true,
		) );
		if ( empty( $posts ) ) {
			return array();
		}

		
		
		
		
		
		$rank = array_flip( $types ); 
		$pick = null;
		foreach ( $posts as $p ) {
			$sc = self::match_score( $p->post_title, $words );
			$nw = self::name_words( $p->post_title );
			if ( ! self::score_is_confident( $sc, count( $words ), $nw ) ) {
				continue;
			}
			$r     = isset( $rank[ $p->post_type ] ) ? (int) $rank[ $p->post_type ] : 99;
			$extra = $nw - $sc['score'];
			
			
			if ( null === $pick
				|| $sc['score'] > $pick['score']
				|| ( $sc['score'] === $pick['score'] && $extra < $pick['extra'] )
				|| ( $sc['score'] === $pick['score'] && $extra === $pick['extra'] && $r < $pick['rank'] ) ) {
				$pick = array( 'score' => $sc['score'], 'extra' => $extra, 'rank' => $r, 'type' => $p->post_type, 'id' => (int) $p->ID );
			}
		}
		if ( null === $pick ) {
			return array();
		}
		return array( 'type' => $pick['type'], 'id' => $pick['id'] );
	}

	
	public static function briefs( $limit = 20 ) {
		
		
		
		
		
		
		return self::top( $limit, array(
			'only_unmapped' => true,
			'status'        => 'open',
		) );
	}

	
	private static function wlen( $w ) {
		return function_exists( 'mb_strlen' ) ? mb_strlen( $w, 'UTF-8' ) : strlen( $w );
	}

	
	private static function fold( $s ) {
		$s = function_exists( 'mb_strtolower' ) ? mb_strtolower( (string) $s, 'UTF-8' ) : strtolower( (string) $s );
		return strtr( $s, array(
			'ά' => 'α', 'έ' => 'ε', 'ή' => 'η', 'ί' => 'ι', 'ό' => 'ο', 'ύ' => 'υ', 'ώ' => 'ω',
			'ϊ' => 'ι', 'ϋ' => 'υ', 'ΐ' => 'ι', 'ΰ' => 'υ', 'ς' => 'σ',
			'á' => 'a', 'é' => 'e', 'í' => 'i', 'ó' => 'o', 'ú' => 'u', 'ü' => 'u', 'ñ' => 'n',
		) );
	}

	
	private static function match_score( $title, array $words ) {
		$hay     = self::fold( $title );
		$score   = 0;
		$longest = 0;
		foreach ( $words as $w ) {
			$f = self::fold( $w );
			if ( '' === $f ) {
				continue;
			}
			if ( preg_match( '/(?<![\p{L}\p{N}])' . preg_quote( $f, '/' ) . '(?![\p{L}\p{N}])/u', $hay ) ) {
				$score++;
				$longest = max( $longest, self::wlen( $f ) );
			}
		}
		return array( 'score' => $score, 'longest' => $longest );
	}

	
	private static function score_is_confident( array $sc, $available, $name_words ) {
		if ( $sc['score'] >= 2 ) {
			return 2 * $sc['score'] > (int) $available;
		}
		return 1 === $sc['score'] && (int) $available <= 1 && (int) $name_words <= 1;
	}

	
	private static function content_words( $norm, array $taxes, array $types ) {
		$out = array();
		foreach ( array_unique( array_filter( explode( ' ', (string) $norm ) ) ) as $w ) {
			$len = self::wlen( $w );
			if ( $len >= 4 || ( 3 === $len && self::is_catalogue_word( $w, $taxes, $types ) ) ) {
				$out[] = $w;
			}
		}
		return $out;
	}

	
	private static function is_catalogue_word( $w, array $taxes, array $types ) {
		$memo = &self::$catalogue_words;
		$f    = self::fold( $w );
		if ( isset( $memo[ $f ] ) ) {
			return $memo[ $f ];
		}
		$memo[ $f ] = false;
		if ( ! empty( $taxes ) ) {
			$names = get_terms( array(
				'taxonomy'   => $taxes,
				'name__like' => $w,
				'hide_empty' => true,
				'number'     => 50,
				'fields'     => 'names',
			) );
			if ( ! is_wp_error( $names ) ) {
				foreach ( (array) $names as $name ) {
					if ( self::match_score( (string) $name, array( $w ) )['score'] > 0 ) {
						return $memo[ $f ] = true;
					}
				}
			}
		}
		if ( ! empty( $types ) ) {
			global $wpdb;
			$in     = implode( ', ', array_fill( 0, count( $types ), '%s' ) );
			$titles = (array) $wpdb->get_col( $wpdb->prepare( 
				"SELECT post_title FROM {$wpdb->posts} WHERE post_status = 'publish' AND post_type IN ({$in}) AND post_title LIKE %s LIMIT 100",
				array_merge( $types, array( '%' . $wpdb->esc_like( $w ) . '%' ) )
			) );
			foreach ( $titles as $title ) {
				if ( self::match_score( (string) $title, array( $w ) )['score'] > 0 ) {
					return $memo[ $f ] = true;
				}
			}
		}
		return false;
	}

	
	private static function name_words( $name ) {
		list( $norm ) = self::normalize( (string) $name );
		return count( array_unique( array_filter( explode( ' ', $norm ) ) ) );
	}

	
	public static function is_small_talk( $text ) {
		$t     = self::fold( wp_strip_all_tags( (string) $text ) );
		$t     = (string) preg_replace( '/[^\p{L}\p{N}\s]+/u', ' ', $t );
		$words = preg_split( '/\s+/u', trim( $t ), -1, PREG_SPLIT_NO_EMPTY );
		if ( empty( $words ) ) {
			return true;
		}
		$lex = self::small_talk_words();
		foreach ( $words as $w ) {
			if ( ! isset( $lex[ $w ] ) ) {
				return false;
			}
		}
		return true;
	}

	
	private static function small_talk_words() {
		static $s = null;
		if ( null !== $s ) {
			return $s;
		}
		$list = array(
			
			'thanks', 'thank', 'thankyou', 'thx', 'ty', 'tnx', 'cheers', 'great', 'perfect', 'awesome', 'excellent',
			'wonderful', 'cool', 'lovely', 'brilliant', 'amazing', 'fantastic', 'super', 'nice', 'good', 'ok', 'okay',
			'okey', 'oki', 'k', 'kk', 'alright', 'allright', 'sure', 'fine', 'yes', 'yeah', 'yea', 'yep', 'yup', 'ya',
			'no', 'nope', 'nah', 'hello', 'hi', 'hey', 'bye', 'goodbye', 'byebye', 'cya', 'later', 'morning',
			'afternoon', 'evening', 'goodnight', 'day', 'welcome', 'glad', 'happy', 'appreciate', 'appreciated', 'help',
			'helping', 'helpful', 'love', 'well', 'oh', 'ah', 'haha', 'lol', 'please', 'pls', 'plz',
			
			'a', 'an', 'the', 'i', 'im', 'm', 'll', 's', 'd', 've', 're', 't', 'it', 'its', 'that', 'thats', 'this',
			'these', 'those', 'them', 'one', 'will', 'would', 'shall', 'can', 'could', 'going', 'gonna', 'to', 'now',
			'then', 'soon', 'today', 'tomorrow', 'right', 'away', 'just', 'so', 'very', 'much', 'many', 'lot', 'lots',
			'of', 'for', 'your', 'you', 'u', 'ur', 'all', 'again', 'too', 'also', 'and', 'order', 'ordering', 'buy',
			'purchase', 'get', 'take', 'place', 'go', 'ahead', 'got', 'understood', 'understand', 'see', 'think',
			'guess', 'maybe', 'do', 'did', 'done', 'be', 'is', 'was', 'am', 'are', 'we', 'me', 'my', 'our', 'us',
			'really', 'indeed', 'want', 'wanted', 'need', 'anything', 'else', 'nothing', 'don', 'dont', 'already',
			'have', 'having', 'with', 'in', 'on', 'at', 'by', 'kind', 'kindly', 'bit',
			
			'ευχαριστω', 'ευχαριστουμε', 'ευχαριστιεσ', 'ευχαριστα', 'τελεια', 'τελειο', 'ωραια', 'ωραιο', 'υπεροχα',
			'υπεροχο', 'εξαιρετικα', 'αψογα', 'αψογο', 'μπραβο', 'καλα', 'καλο', 'καλη', 'καλοσ', 'οκ', 'οκει', 'ok',
			'ενταξει', 'σωστα', 'ναι', 'οχι', 'μαλιστα', 'βεβαια', 'βεβαιωσ', 'φυσικα', 'σιγουρα', 'γεια', 'χαιρετω',
			'χαιρετε', 'αντιο', 'καλημερα', 'καλησπερα', 'καληνυχτα', 'μερα', 'συνεχεια', 'χαρα', 'χαιρομαι', 'χαρηκα',
			'παρακαλω', 'ευγενικο', 'ευγενικη', 'ευγενικοσ', 'βοηθεια', 'βοηθησατε', 'βοηθησεσ',
			
			'για', 'σασ', 'σου', 'μου', 'μασ', 'θα', 'να', 'το', 'τα', 'τη', 'την', 'τον', 'ο', 'η', 'οι', 'και', 'κι',
			'πολυ', 'παρα', 'τωρα', 'αμεσα', 'μετα', 'αργοτερα', 'σημερα', 'αυριο', 'ξανα', 'ηδη', 'παραγγειλω',
			'παραγγελω', 'παραγγελνω', 'παραγγειλουμε', 'αγορασω', 'αγοραζω', 'παρω', 'παιρνω', 'κανω', 'κανουμε',
			'εκανα', 'ειναι', 'ειμαι', 'εχω', 'ολα', 'ολο', 'αυτο', 'αυτα', 'αυτη', 'απλα', 'εντελωσ', 'αλλο',
			'τιποτα', 'τιποτε', 'δεν', 'χρειαζομαι', 'θελω', 'ηθελα', 'σε', 'με', 'απο', 'στο', 'στη', 'στην', 'ενα',
			'μια', 'λοιπον', 'α', 'ε', 'αα',
		);
		$s = array_fill_keys( $list, true );
		return $s;
	}

	
	private static function normalize( $text ) {
		$t = wp_strip_all_tags( (string) $text );
		$t = function_exists( 'mb_strtolower' ) ? mb_strtolower( $t, 'UTF-8' ) : strtolower( $t );
		$t = preg_replace( '/[^\p{L}\p{N}\s]+/u', ' ', $t ); 
		$t = trim( preg_replace( '/\s+/u', ' ', (string) $t ) );
		if ( '' === $t ) {
			return array( '', '' );
		}
		$stop  = self::stopwords();
		$words = array();
		foreach ( explode( ' ', $t ) as $w ) {
			if ( '' === $w ) {
				continue;
			}
			$len = function_exists( 'mb_strlen' ) ? mb_strlen( $w ) : strlen( $w );
			if ( $len > 2 && ! isset( $stop[ $w ] ) ) {
				$words[] = $w;
			}
		}
		sort( $words ); 
		$norm = implode( ' ', $words );
		if ( '' === $norm ) {
			$norm = $t;
		}
		return array( $norm, substr( md5( $norm ), 0, 16 ) );
	}

	
	private static function stopwords() {
		static $s = null;
		if ( null !== $s ) {
			return $s;
		}
		$list = array(
			
			'και', 'την', 'τον', 'της', 'του', 'των', 'για', 'στο', 'στη', 'στην', 'στον', 'σας', 'μας',
			'που', 'δεν', 'θα', 'να', 'ένα', 'μια', 'μία', 'είναι', 'έχω', 'έχει', 'θέλω', 'ποιο', 'ποια',
			'πως', 'πώς', 'τι', 'πόσο', 'μπορώ', 'μπορείτε', 'κάνω', 'από', 'με', 'σε', 'το', 'τα', 'οι',
			
			'the', 'and', 'for', 'you', 'are', 'can', 'how', 'what', 'with', 'this', 'that', 'have', 'has',
			'does', 'your', 'from', 'about', 'want', 'need', 'any', 'all', 'get', 'was', 'were', 'they',
			'there', 'which', 'when', 'where', 'would', 'could', 'should', 'please', 'looking', 'find',
		);
		$s = array_fill_keys( $list, true );
		return $s;
	}
}