<?php
/**
 * Migration: Add FULLTEXT Index to Knowledge Index table
 *
 * Adds a composite FULLTEXT index on (title, summary, keywords) for fast
 * MySQL-side text search filtering, replacing the PHP-side full-table-scan
 * approach in keyword_search().
 *
 * @package BoostBot
 * @since   2.3.0
 */

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Migration_Add_Fulltext_Index_Knowledge_Index
 *
 * @since 2.3.0
 */
class BoostBot_Migration_Add_Fulltext_Index_Knowledge_Index {

    /**
     * Run the migration.
     *
     * @return bool True on success.
     */
    public static function run(): bool {
        global $wpdb;

        $table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_knowledge_index' );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
        if ( ! $table_exists ) {
            return false;
        }

        // Check if FULLTEXT index already exists on title column (part of composite)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $existing = $wpdb->get_results(
            "SHOW INDEX FROM {$table_name} WHERE Index_type = 'FULLTEXT' AND Column_name = 'title'"
        );

        if ( ! empty( $existing ) ) {
            return true;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $result = $wpdb->query(
            "ALTER TABLE {$table_name} ADD FULLTEXT INDEX ft_knowledge_search (title, summary, keywords)" // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- DDL cannot use placeholders for identifiers; $table is sanitized, definitions are literals.
        );

        if ( false === $result ) {
            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::error( '[Migration] Failed to add FULLTEXT index on knowledge_index: ' . $wpdb->last_error );
            }
            return false;
        }

        if ( class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( '[Migration] Added FULLTEXT index ft_knowledge_search on knowledge_index (title, summary, keywords)' );
        }
        return true;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}
