<?php
/**
 * Migration: Add opening_buttons column to opening_screen_presets table
 *
 * @package BoostBot
 * @since   1.5.1
 */

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

/**
 * Class BoostBot_Migration_Add_Opening_Buttons
 *
 * Adds the opening_buttons column for quick action buttons on the opening screen.
 */
class BoostBot_Migration_Add_Opening_Buttons {

    /**
     * Run the migration.
     *
     * @return void
     */
    public static function up() {
        global $wpdb;
        
        $table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_opening_screen_presets' );
        
        // Check if column already exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $column_exists = $wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.columns 
                WHERE table_schema = %s 
                AND table_name = %s 
                AND column_name = 'opening_buttons'",
                DB_NAME,
                $table_name
            )
        );
        
        if ( ! $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().
            $wpdb->query(
                "ALTER TABLE {$table_name} ADD COLUMN opening_buttons longtext DEFAULT NULL AFTER attached_files" // 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.
            );
            
            // Log migration
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG && class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Migration: Added opening_buttons column to opening_screen_presets table' );
            }
        }
    }

    /**
     * Rollback the migration.
     *
     * @return void
     */
    public static function down() {
        global $wpdb;
        
        $table_name = self::sanitize_identifier( $wpdb->prefix . 'boostbot_opening_screen_presets' );
        
        // 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} DROP COLUMN IF EXISTS opening_buttons" // 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.
        );
    }

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

