<?php
/**
 * Migration: Add Deep Indexing Incremental Columns to Knowledge Index (Week 24.5)
 *
 * Adds change tracking columns:
 * - content_hash: sha256 of canonical extracted text (for deep_incremental)
 * - content_length: canonical text length (chars)
 * - indexed_mode: last indexing mode used (basic/deep/deep_incremental)
 * - indexed_version: internal version for future hashing/indexing logic changes
 *
 * @package BoostBot
 * @since   1.8.0
 */

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

class BoostBot_Migration_Add_Deep_Indexing_Incremental_Columns {

    /**
     * Run the migration.
     *
     * @return bool
     */
    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;
        }

        // 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 = array();
        foreach ( $columns as $col ) {
            $existing[] = $col->Field;
        }

        // Column definitions are hard-coded literals.
        $columns_to_add = array(
            'content_hash'   => 'varchar(64) DEFAULT NULL AFTER raw_content',
            'content_length' => 'int(11) NOT NULL DEFAULT 0 AFTER content_hash',
            'indexed_mode'   => "varchar(32) NOT NULL DEFAULT 'basic' AFTER content_length",
            'indexed_version'=> 'int(11) NOT NULL DEFAULT 1 AFTER indexed_mode',
        );

        foreach ( $columns_to_add as $name => $definition ) {
            if ( in_array( $name, $existing, true ) ) {
                continue;
            }
            $safe_col = self::sanitize_identifier( $name );
            // 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_col}` {$definition}" );
            if ( $wpdb->last_error ) {
                if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
                    BoostBot_Logger::error( '[Migration] Deep incremental columns: ' . $wpdb->last_error );
                }
                return false;
            }
        }

        $indexes_to_add = array(
            'idx_content_hash' => 'content_hash',
            'idx_indexed_mode' => 'indexed_mode',
        );

        foreach ( $indexes_to_add as $index_name => $col_name ) {
            $safe_index = self::sanitize_identifier( $index_name );
            $safe_col   = self::sanitize_identifier( $col_name );

            // 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 ) {
                continue;
            }
            // 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_col}`)" );
        }

        return true;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * @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 );
    }
}

