<?php
/**
 * Truebe gaps: the record of what visitors asked that had no verified answer.
 *
 * Every miss from Truebe_Query lands here. Near-identical questions are grouped under one
 * row with a running count, so the owner sees "11 people asked something like this" instead
 * of eleven separate lines. This is the to-do list that grows the knowledge base: each open
 * gap is a question worth adding a seed for.
 *
 * Logging happens in real time on each miss. A light nightly cron prunes resolved and
 * dismissed gaps that have gone stale, so the report stays focused on what still matters.
 *
 * Grouping here is exact-after-normalization (lowercased, punctuation stripped). Smarter
 * similarity grouping can layer on later without changing how callers log or read.
 */

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

if ( class_exists( 'Truebe_Gaps' ) ) {
	return;
}

class Truebe_Gaps {

	const STATUS_OPEN      = 'open';
	const STATUS_RESOLVED  = 'resolved';
	const STATUS_DISMISSED = 'dismissed';

	const NIGHTLY_HOOK = 'truebe_gaps_nightly';

	/** Days a resolved/dismissed gap is kept before nightly cleanup removes it. */
	const STALE_DAYS = 90;

	/**
	 * Table name for gaps.
	 *
	 * @return string
	 */
	private static function table() {
		global $wpdb;
		return $wpdb->prefix . 'truebe_gaps';
	}

	/**
	 * Create the gaps table. Called on plugin activation.
	 */
	public static function install() {
		global $wpdb;
		$table   = self::table();
		$collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			question TEXT NOT NULL,
			fingerprint VARCHAR(64) NOT NULL,
			hits INT UNSIGNED NOT NULL DEFAULT 1,
			status VARCHAR(20) NOT NULL DEFAULT 'open',
			first_seen DATETIME NOT NULL,
			last_seen DATETIME NOT NULL,
			PRIMARY KEY (id),
			KEY fingerprint (fingerprint),
			KEY status (status)
		) {$collate};";

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

	/**
	 * Schedule the nightly cleanup and bind its handler. Called at file load.
	 */
	public static function init() {
		add_action( self::NIGHTLY_HOOK, array( __CLASS__, 'nightly_cleanup' ) );
		if ( ! wp_next_scheduled( self::NIGHTLY_HOOK ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, 'daily', self::NIGHTLY_HOOK );
		}
	}

	/**
	 * Record an unanswered question. Increments the count if a matching gap already exists.
	 *
	 * @param string $question
	 */
	public static function log( $question ) {
		global $wpdb;
		$question = is_string( $question ) ? trim( $question ) : '';
		if ( '' === $question ) {
			return;
		}

		$fingerprint = self::fingerprint( $question );
		$table       = self::table();
		$now         = current_time( 'mysql' );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$existing = $wpdb->get_var(
			$wpdb->prepare( "SELECT id FROM {$table} WHERE fingerprint = %s LIMIT 1", $fingerprint )
		);

		if ( $existing ) {
			$wpdb->query(
				$wpdb->prepare(
					"UPDATE {$table} SET hits = hits + 1, last_seen = %s, status = %s WHERE id = %d",
					$now,
					self::STATUS_OPEN,
					(int) $existing
				)
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
			return;
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; $wpdb->insert() is the supported API and there is no object cache for this row.
		$wpdb->insert(
			$table,
			array(
				'question'    => $question,
				'fingerprint' => $fingerprint,
				'hits'        => 1,
				'status'      => self::STATUS_OPEN,
				'first_seen'  => $now,
				'last_seen'   => $now,
			),
			array( '%s', '%s', '%d', '%s', '%s', '%s' )
		);
	}

	/**
	 * Open gaps for the report, most-asked first.
	 *
	 * @param int $limit
	 * @return array[] Rows as associative arrays.
	 */
	public static function get_open( $limit = 100 ) {
		global $wpdb;
		$table = self::table();
		$limit = max( 1, (int) $limit );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		return $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$table} WHERE status = %s ORDER BY hits DESC, last_seen DESC LIMIT %d",
				self::STATUS_OPEN,
				$limit
			),
			ARRAY_A
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Count of open gaps (for the admin badge).
	 *
	 * @return int
	 */
	public static function count_open() {
		global $wpdb;
		$table = self::table();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		return (int) $wpdb->get_var(
			$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE status = %s", self::STATUS_OPEN )
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Mark a gap resolved or dismissed.
	 *
	 * @param int    $id
	 * @param string $status
	 * @return bool
	 */
	public static function set_status( $id, $status ) {
		global $wpdb;
		$allowed = array( self::STATUS_OPEN, self::STATUS_RESOLVED, self::STATUS_DISMISSED );
		if ( ! in_array( $status, $allowed, true ) ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- custom plugin table; $wpdb->update() is the supported API and there is no object cache for this row.
		return (bool) $wpdb->update(
			self::table(),
			array( 'status' => $status ),
			array( 'id' => (int) $id ),
			array( '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Mark any open gap matching this question (by fingerprint) as resolved.
	 * Called when the matcher successfully answers a question that previously missed.
	 *
	 * @param string $question
	 * @return void
	 */
	public static function resolve_by_question( $question ) {
		global $wpdb;
		$question = is_string( $question ) ? trim( $question ) : '';
		if ( '' === $question ) {
			return;
		}
		$fingerprint = self::fingerprint( $question );
		$table       = self::table();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$wpdb->query(
			$wpdb->prepare(
				"UPDATE {$table} SET status = %s WHERE fingerprint = %s AND status = %s",
				self::STATUS_RESOLVED,
				$fingerprint,
				self::STATUS_OPEN
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Nightly: drop resolved/dismissed gaps that have gone stale. Open gaps are never removed.
	 */
	public static function nightly_cleanup() {
		global $wpdb;
		$table  = self::table();
		$cutoff = gmdate( 'Y-m-d H:i:s', time() - ( self::STALE_DAYS * DAY_IN_SECONDS ) );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- {$table} is built from $wpdb->prefix plus a fixed string (no user input); SQL identifiers cannot be passed as prepare() parameters. Custom plugin table, no core API or object cache.
		$wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$table} WHERE status IN ( %s, %s ) AND last_seen < %s",
				self::STATUS_RESOLVED,
				self::STATUS_DISMISSED,
				$cutoff
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}

	/**
	 * Normalize a question to a grouping fingerprint: lowercase, strip non-alphanumerics,
	 * collapse whitespace, then hash. Two questions that differ only by case or punctuation
	 * group together.
	 *
	 * @param string $question
	 * @return string
	 */
	private static function fingerprint( $question ) {
		$norm = strtolower( $question );
		$norm = preg_replace( '/[^a-z0-9\s]/', ' ', $norm );
		$norm = trim( preg_replace( '/\s+/', ' ', $norm ) );
		return md5( $norm );
	}
}

Truebe_Gaps::init();