<?php
/**
 * Migration: Add language column to appearance_presets table.
 *
 * @package BoostBot
 * @since   1.2.2
 */

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

class BoostBot_Migration_Add_Language_To_Appearance_Presets {

    /**
     * Run the migration.
     *
     * @return void
     */
    public static function up(): void {
        global $wpdb;
        $table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_appearance_presets' );

        // 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.
        $col = $wpdb->get_results( "SHOW COLUMNS FROM `{$table}` LIKE 'language'" );

        if ( empty( $col ) ) {
            // 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}` ADD COLUMN `language` VARCHAR(10) NOT NULL DEFAULT 'he' AFTER `id`" );

            // 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.
            $index_exists = $wpdb->get_results( "SHOW INDEX FROM `{$table}` WHERE Key_name = 'idx_language'" );
            if ( empty( $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}` ADD INDEX `idx_language` (`language`)" );
            }

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Migration: Added language column to appearance_presets table' );
            }
        }

        // Backfill language for existing English presets (slug-based detection).
        // Runs regardless of whether the column was just added or already existed
        // (e.g. from CREATE TABLE in create_tables()).
        // 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( "UPDATE `{$table}` SET `language` = 'en' WHERE `slug` LIKE '%-en' AND `language` = 'he'" );
    }

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