<?php
/**
 * Migration: Add FULLTEXT Index to Knowledge Chunks
 *
 * Adds a FULLTEXT index on chunk_text for fast BM25-style keyword search.
 * This enables hybrid retrieval (semantic + keyword) for improved accuracy.
 *
 * @package BoostBot
 * @since   1.15.0
 */

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

/**
 * Class BoostBot_Migration_Add_Fulltext_Index
 *
 * @since 1.15.0
 */
class BoostBot_Migration_Add_Fulltext_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_chunks' );

        // Check if table exists
        // 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 ) {
            BoostBot_Logger::warning( '[Migration] FULLTEXT index skipped: chunks table does not exist' );
            return false;
        }

        // Check if FULLTEXT index already exists
        // 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_index = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                "SHOW INDEX FROM {$table_name} WHERE Index_type = %s AND Column_name = %s",
                'FULLTEXT',
                'chunk_text'
            ),
            ARRAY_A
        );

        if ( ! empty( $existing_index ) ) {
            BoostBot_Logger::info( '[Migration] FULLTEXT index already exists on chunk_text' );
            return true;
        }

        // Check MySQL version for InnoDB FULLTEXT support (requires MySQL 5.6.4+)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $mysql_version = $wpdb->get_var( 'SELECT VERSION()' );
        $version_parts = explode( '.', $mysql_version );
        $major = (int) ( $version_parts[0] ?? 0 );
        $minor = (int) ( $version_parts[1] ?? 0 );
        
        // MySQL 5.6.4+ supports FULLTEXT on InnoDB
        // MariaDB 10.0.5+ supports FULLTEXT on InnoDB
        $supports_innodb_fulltext = ( $major > 5 ) || ( $major === 5 && $minor >= 6 );
        
        // Check if using MariaDB (supports FULLTEXT on InnoDB from 10.0.5)
        if ( stripos( $mysql_version, 'mariadb' ) !== false ) {
            $supports_innodb_fulltext = true; // Modern MariaDB supports it
        }

        if ( ! $supports_innodb_fulltext ) {
            BoostBot_Logger::warning( "[Migration] FULLTEXT index skipped: MySQL version {$mysql_version} may not support InnoDB FULLTEXT" );
            return false;
        }

        // Add FULLTEXT index
        // 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.
        $result = $wpdb->query( "ALTER TABLE {$table_name} ADD FULLTEXT INDEX idx_chunk_fulltext (chunk_text)" );

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

        BoostBot_Logger::info( '[Migration] Successfully added FULLTEXT index on chunk_text' );
        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 );
    }
}
