<?php
/**
 * Migration: Add product tier columns to WooCommerce presets table.
 *
 * Adds product_card_enabled toggle and bundle-specific product scope
 * columns to support the two-tier product model (global scope vs
 * bundle-specific scope).
 *
 * @package BoostBot
 * @since   1.3.0
 */

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

/**
 * Class BoostBot_Migration_Add_Preset_Product_Tiers
 */
class BoostBot_Migration_Add_Preset_Product_Tiers {

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

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

		$table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_presets' );

		// Column definitions are hard-coded literals controlled by the plugin,
		// and column names are restricted to [A-Za-z0-9_] before interpolation.
		$columns = array(
			'product_card_enabled'      => "TINYINT(1) NOT NULL DEFAULT 1",
			'bundle_product_mode'       => "VARCHAR(20) DEFAULT 'all'",
			'bundle_allowed_categories' => "LONGTEXT DEFAULT NULL",
			'bundle_excluded_categories' => "LONGTEXT DEFAULT NULL",
			'bundle_specific_product_ids' => "LONGTEXT DEFAULT NULL",
			'bundle_excluded_product_ids' => "LONGTEXT DEFAULT NULL",
		);

		foreach ( $columns as $col_name => $col_def ) {
			$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().
			$exists = $wpdb->get_results(
				$wpdb->prepare(
					"SHOW COLUMNS FROM `{$table}` LIKE %s",
					$safe_col
				)
			);

			if ( empty( $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.
				$result = $wpdb->query( "ALTER TABLE `{$table}` ADD COLUMN `{$safe_col}` {$col_def}" );

				if ( false === $result ) {
					if ( class_exists( 'BoostBot_Logger' ) ) {
						BoostBot_Logger::error( "Migration failed: Could not add {$col_name} to presets", array(
							'error' => $wpdb->last_error,
						) );
					}
					return false;
				}
			}
		}

		if ( class_exists( 'BoostBot_Logger' ) ) {
			BoostBot_Logger::info( 'Migration: Added product tier columns to woocommerce_presets table' );
		}

		return true;
	}

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

		$table   = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_presets' );
		$columns = array(
			'product_card_enabled',
			'bundle_product_mode',
			'bundle_allowed_categories',
			'bundle_excluded_categories',
			'bundle_specific_product_ids',
			'bundle_excluded_product_ids',
		);

		foreach ( $columns as $col_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().
			$exists = $wpdb->get_results(
				$wpdb->prepare(
					"SHOW COLUMNS FROM `{$table}` LIKE %s",
					$safe_col
				)
			);

			if ( ! empty( $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}` DROP COLUMN `{$safe_col}`" );
			}
		}

		return true;
	}

	/**
	 * Restrict a SQL identifier to [A-Za-z0-9_] characters.
	 *
	 * DDL cannot use $wpdb->prepare() for table/column/index names, so every
	 * identifier that ends up interpolated into a query is first passed
	 * through this allow-list so the SQL surface cannot be coerced even if a
	 * future change widens the source of these values.
	 *
	 * @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 );
	}
}
