<?php
/**
 * Migration: Add RAG Enhancement Columns to Knowledge Index
 *
 * Adds new columns for Week 24 Global Context Awareness:
 * - raw_content: Full unprocessed content for detailed queries
 * - service_category: Category classification for service pages
 * - is_service_page: Flag indicating if this is a service/product page
 *
 * @package BoostBot
 * @since   1.6.0
 */

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

/**
 * Class BoostBot_Migration_Add_RAG_Columns
 *
 * @since 1.6.0
 */
class BoostBot_Migration_Add_RAG_Columns {

    /**
     * Run the migration.
     *
     * @return bool Success or failure.
     */
    public static function run() {
        global $wpdb;

        // $wpdb->prefix is a trusted constant produced by WordPress; the table
        // stem is a hard-coded literal. We still enforce the identifier
        // charset [A-Za-z0-9_] before any interpolation so this cannot be
        // coerced into executing attacker-controlled DDL even if a future
        // change widens the inputs.
        $table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_knowledge_index' );

        // 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 ) {
            return false;
        }

        // Check which columns already exist. Identifier is sanitized above;
        // $wpdb->prepare() does not support %i placeholders for identifiers
        // on all supported WP versions, so we interpolate the already-vetted
        // table name and suppress the sniff with a justified ignore.
        // 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.
        $columns = $wpdb->get_results( "SHOW COLUMNS FROM `{$table_name}`" );

        $existing_columns = array();
        foreach ( $columns as $column ) {
            $existing_columns[] = $column->Field;
        }

        // All identifiers and column definitions below are hard-coded literals.
        $columns_to_add = array(
            'raw_content'      => 'longtext DEFAULT NULL AFTER full_content',
            'service_category' => 'varchar(100) DEFAULT NULL AFTER keywords',
            'is_service_page'  => 'tinyint(1) DEFAULT 0 AFTER service_category',
        );

        foreach ( $columns_to_add as $column_name => $column_definition ) {
            if ( in_array( $column_name, $existing_columns, true ) ) {
                continue;
            }
            $safe_column = self::sanitize_identifier( $column_name );
            // $column_definition is a hard-coded literal from the array above;
            // $safe_column is restricted to [A-Za-z0-9_]. DDL cannot use
            // placeholders for identifiers, so interpolation is necessary.
            // 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.
            $wpdb->query( "ALTER TABLE `{$table_name}` ADD COLUMN `{$safe_column}` {$column_definition}" );

            if ( $wpdb->last_error ) {
                if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                    BoostBot_Logger::error( '[Migration] RAG columns: ' . $wpdb->last_error );
                }
                return false;
            }
        }

        // Add indexes for the new columns
        $indexes_to_add = array(
            'idx_service_page'     => 'is_service_page',
            'idx_service_category' => 'service_category',
        );

        foreach ( $indexes_to_add as $index_name => $column_name ) {
            $safe_index  = self::sanitize_identifier( $index_name );
            $safe_column = self::sanitize_identifier( $column_name );

            // Key_name is a user-supplied identifier elsewhere in MySQL, but
            // here it's a hard-coded literal that we still sanitize before
            // quoting. We use %s for the quoted-string comparison value.
            // 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().
            $index_exists = $wpdb->get_var(
                $wpdb->prepare(
                    "SHOW INDEX FROM `{$table_name}` WHERE Key_name = %s",
                    $safe_index
                )
            );

            if ( ! $index_exists ) {
                // 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.
                $wpdb->query( "ALTER TABLE `{$table_name}` ADD INDEX `{$safe_index}` (`{$safe_column}`)" );
            }
        }

        return true;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL statements cannot use $wpdb->prepare() for table/column/index names.
     * Any interpolation of an identifier must be preceded by a strict allow-list
     * sanitization pass so the SQL surface cannot carry backticks, spaces, or
     * quote characters even if the source value is later changed.
     *
     * @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 );
    }
}







