<?php
/**
 * Migration: Add language column to entity tables for multilingual support.
 *
 * Adds a `language VARCHAR(10) DEFAULT 'he' NOT NULL` column to all entity
 * tables that need per-language data separation. Existing rows default to 'he'
 * (Hebrew) since the plugin was originally Hebrew-only.
 *
 * Tables that already have the column (knowledge_index) are skipped.
 * Tables where language is inherited via FK (knowledge_chunks) are skipped.
 *
 * @package BoostBot
 * @since   1.2.0
 */

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

class BoostBot_Migration_Add_Language_Column {

    /**
     * Tables that need a language column and the column to place it AFTER.
     *
     * @var array<string, string>
     */
    private static $tables = array(
        'boostbot_bots'                  => 'bot_slug',
        'boostbot_personalities'         => 'id',
        'boostbot_personality_traits'    => 'id',
        'boostbot_tones'                 => 'id',
        'boostbot_widgets'              => 'id',
        'boostbot_opening_screen_presets' => 'id',
        'boostbot_index_queue'           => 'id',
        'boostbot_leads'                 => 'id',
        'boostbot_conversations'         => 'id',
    );

    /**
     * Run the migration.
     *
     * @return bool True on success.
     */
    public static function run(): bool {
        return self::up();
    }

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

        $success = true;

        foreach ( self::$tables as $table_suffix => $after_column ) {
            $table_name   = self::sanitize_identifier( $wpdb->prefix . $table_suffix );
            $safe_after   = self::sanitize_identifier( $after_column );

            // Idempotent: skip if column 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().
            $column_exists = $wpdb->get_results(
                $wpdb->prepare(
                    "SHOW COLUMNS FROM `{$table_name}` LIKE %s",
                    'language'
                )
            );

            if ( ! empty( $column_exists ) ) {
                continue;
            }

            // 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 COLUMN `language` VARCHAR(10) NOT NULL DEFAULT 'he' AFTER `{$safe_after}`" // 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: Could not add language column to {$table_suffix}", array(
                        'error' => $wpdb->last_error,
                    ) );
                }
                $success = false;
            } else {
                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::info( "Migration: Added language column to {$table_suffix}" );
                }
            }
        }

        $indexed_tables = array(
            'boostbot_bots',
            'boostbot_personalities',
            'boostbot_widgets',
            'boostbot_opening_screen_presets',
            'boostbot_tones',
        );

        foreach ( $indexed_tables as $table_suffix ) {
            $table_name = self::sanitize_identifier( $wpdb->prefix . $table_suffix );
            $index_name = self::sanitize_identifier( 'idx_language' );

            // 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_results(
                $wpdb->prepare(
                    "SHOW INDEX FROM `{$table_name}` WHERE Key_name = %s",
                    $index_name
                )
            );

            if ( empty( $index_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().
                $wpdb->query(
                    "ALTER TABLE `{$table_name}` ADD INDEX `{$index_name}` (`language`)" // 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.
                );
            }
        }

        return $success;
    }

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

        $success = true;

        foreach ( self::$tables as $table_suffix => $after_column ) {
            $table_name = self::sanitize_identifier( $wpdb->prefix . $table_suffix );

            // 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().
            $column_exists = $wpdb->get_results(
                $wpdb->prepare(
                    "SHOW COLUMNS FROM `{$table_name}` LIKE %s",
                    'language'
                )
            );

            if ( ! empty( $column_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().
                $result = $wpdb->query(
                    "ALTER TABLE `{$table_name}` DROP COLUMN `language`" // 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 ) {
                    $success = false;
                }
            }
        }

        return $success;
    }

    /**
     * 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 );
    }
}
