<?php
/**
 * Migration: Fix WooCommerce widget sharing and mark as system-managed.
 *
 * Finds WooCommerce widgets shared across multiple bots (unsafe) and
 * creates per-bot copies. Then marks all WooCommerce widgets as
 * system-managed so they are controlled by the preset system.
 *
 * @package BoostBot
 * @since   1.2.0
 */

// Prevent direct access.
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Migration DB writes; table names sanitized via sanitize_identifier(); dynamic %s placeholder lists bound via prepare().
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Migration_WooCommerce_Widget_Sharing
 */
class BoostBot_Migration_WooCommerce_Widget_Sharing {

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

        $widgets_table  = self::sanitize_identifier( $wpdb->prefix . 'boostbot_widgets' );
        $junction_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_chatbot_widgets' );
        $woo_types      = array( 'product_card', 'coupon', 'bundle_builder' );

        // Check if is_system_managed column exists (depends on prior migration).
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $col_check = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "SHOW COLUMNS FROM {$widgets_table} LIKE 'is_system_managed'"
        );
        if ( empty( $col_check ) ) {
            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::error( 'WooCommerce widget sharing migration: is_system_managed column missing' );
            }
            return false;
        }

        // Step 1: Find shared WooCommerce widgets.
        $type_placeholders = implode( ',', array_fill( 0, count( $woo_types ), '%s' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $shared = $wpdb->get_results( $wpdb->prepare(
            "SELECT w.id, w.type, w.name, w.description, w.config, w.is_active, w.language,
                    COUNT(DISTINCT cw.chatbot_id) as bot_count
             FROM {$widgets_table} w
             JOIN {$junction_table} cw ON w.id = cw.widget_id
             WHERE w.type IN ({$type_placeholders})
             GROUP BY w.id
             HAVING bot_count > 1",
            ...$woo_types
        ) );

        // Step 2: Split shared widgets into per-bot copies.
        if ( ! empty( $shared ) ) {
            $widget_model = new BoostBot_Widget_Model();

            foreach ( $shared as $sw ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$assignments = $wpdb->get_results( $wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					"SELECT chatbot_id, custom_config FROM {$junction_table}
					 WHERE widget_id = %d ORDER BY chatbot_id ASC",
                    $sw->id
                ) );

                if ( count( $assignments ) <= 1 ) {
                    continue;
                }

                // Keep the original widget for the first bot, create copies for the rest.
                $first = true;
                foreach ( $assignments as $assignment ) {
                    if ( $first ) {
                        $first = false;
                        continue;
                    }

                    // Create a cloned widget.
                    $new_id = $widget_model->create( array(
                        'name'              => $sw->name,
                        'type'              => $sw->type,
                        'description'       => $sw->description ?? '',
                        'config'            => $sw->config ?? '',
                        'is_active'         => $sw->is_active ?? 1,
                        'is_system_managed' => 1,
                        'language'          => $sw->language ?? 'he',
                    ) );

                    if ( $new_id ) {
                        // Update the junction entry to point to the new widget.
                        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                        $wpdb->update(
                            $junction_table,
                            array( 'widget_id' => $new_id ),
                            array(
                                'widget_id'  => $sw->id,
                                'chatbot_id' => $assignment->chatbot_id,
                            ),
                            array( '%d' ),
                            array( '%d', '%d' )
                        );

                        if ( class_exists( 'BoostBot_Logger' ) ) {
                            BoostBot_Logger::info( 'WooCommerce widget sharing migration: cloned widget', array(
                                'original_id'  => $sw->id,
                                'new_id'       => $new_id,
                                'type'         => $sw->type,
                                'for_bot'      => $assignment->chatbot_id,
                            ) );
                        }
                    }
                }
            }
        }

        // Step 3: Mark ALL existing WooCommerce widgets as system-managed.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $wpdb->query( $wpdb->prepare(
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            "UPDATE {$widgets_table}
             SET is_system_managed = 1
             WHERE type IN ({$type_placeholders}) AND is_system_managed = 0",
            ...$woo_types
        ) );

        if ( class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( 'WooCommerce widget sharing migration completed' );
        }

        return true;
    }

    /**
     * Rollback the migration.
     *
     * @return bool True on success.
     */
    public static function down() {
        // Cannot automatically undo widget splitting; this is a one-way migration.
        return true;
    }

    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * @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 );
    }
}
