<?php
/**
 * Migration: Add personality behavior columns to bots table.
 *
 * Adds system_prompt, tone, ask_questions, suggest_products, proactive_engagement,
 * and personality_overrides columns that allow bots to store their own personality
 * settings (previously these only lived on the personalities table).
 *
 * @package BoostBot
 * @since   1.0.12
 */

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

/**
 * Class BoostBot_Migration_Add_Bot_Personality_Columns
 *
 * Idempotent — checks column existence before ALTER.
 *
 * @since 1.0.12
 */
class BoostBot_Migration_Add_Bot_Personality_Columns {

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

		$bots_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );
		$success    = true;

		// Column definitions are hard-coded literals controlled by the plugin.
		$columns = array(
			'system_prompt'        => 'text DEFAULT NULL AFTER personality_id',
			'tone'                 => "varchar(50) DEFAULT NULL AFTER system_prompt",
			'ask_questions'        => 'tinyint(1) DEFAULT 1 AFTER tone',
			'suggest_products'     => 'tinyint(1) DEFAULT 1 AFTER ask_questions',
			'proactive_engagement' => 'tinyint(1) DEFAULT 0 AFTER suggest_products',
			'personality_overrides'=> 'longtext DEFAULT NULL AFTER proactive_engagement',
		);

		foreach ( $columns as $col => $definition ) {
			$safe_col = self::sanitize_identifier( $col );

			// 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 `{$bots_table}` LIKE %s",
					$safe_col
				)
			);

			if ( ! empty( $exists ) ) {
				continue; // Already exists — idempotent.
			}

			// 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 `{$bots_table}` ADD COLUMN `{$safe_col}` {$definition}" );

			if ( false === $result ) {
				if ( class_exists( 'BoostBot_Logger' ) ) {
					BoostBot_Logger::error( "Migration failed: Could not add {$col} to bots table", array(
						'error' => $wpdb->last_error,
					) );
				}
				$success = false;
			} else {
				if ( class_exists( 'BoostBot_Logger' ) ) {
					BoostBot_Logger::info( "Migration: Added {$col} to bots table" );
				}
			}
		}

		return $success;
	}

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

		$bots_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );
		$success    = true;
		$columns    = array( 'system_prompt', 'tone', 'ask_questions', 'suggest_products', 'proactive_engagement', 'personality_overrides' );

		foreach ( $columns as $col ) {
			$safe_col = self::sanitize_identifier( $col );

			// 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 `{$bots_table}` LIKE %s",
					$safe_col
				)
			);

			if ( empty( $exists ) ) {
				continue;
			}

			// 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 `{$bots_table}` DROP COLUMN `{$safe_col}`" );

			if ( false === $result ) {
				$success = false;
			}
		}

		return $success;
	}

	/**
	 * 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 passed through
	 * this allow-list first.
	 *
	 * @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 );
	}
}
