<?php

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
defined( 'ABSPATH' ) || exit;

/**
 * Migration: Add Language to Bundles
 * Adds language column to the existing boostbot_bundles table.
 */
class BoostBot_Migration_Add_Language_To_Bundles {

	/**
	 * Run the migration.
	 */
	public static function run() {
		$instance = new self();
		$instance->up();
	}

	/**
	 * Add the column.
	 */
	public function up() {
		global $wpdb;

		$table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bundles' );

		// Check if table exists
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) !== $table_name ) {
			return;
		}

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

		if ( empty( $column ) ) {
			// 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 language VARCHAR(10) NOT NULL DEFAULT 'he' AFTER status" );
			
			// 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( "CREATE INDEX idx_bundles_language ON $table_name (language)" );
		}
	}

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