<?php
/**
 * WooCommerce Migration Service for BoostBot.
 *
 * Handles migration from widget-based WooCommerce settings to preset-based system.
 *
 * @package BoostBot
 * @since   1.3.0 (Week 25)
 */

// Prevent direct access.
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Migration owns custom-table DDL. Table names pass through sanitize_identifier(); DDL cannot use prepare() placeholders for identifiers; caching is irrelevant for one-shot migrations.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_WooCommerce_Migration
 *
 * Migrates existing Bundle Builder widget settings to new WooCommerce presets.
 *
 * @since 1.3.0
 */
class BoostBot_WooCommerce_Migration {

    /**
     * Run the migration.
     *
     * @since 1.3.0
     * @return array Migration results.
     */
    public function run(): array {
        $results = array(
            'presets_created'  => 0,
            'coupons_migrated' => 0,
            'bots_updated'     => 0,
            'errors'           => array(),
        );

        // Idempotency: if the migration already completed, do nothing. Prevents
        // re-running from creating duplicate presets/coupons.
        if ( get_option( 'boostbot_woocommerce_migration_complete', false ) ) {
            return $results;
        }

        // Concurrency lock: add_option() is an atomic insert, so a competing
        // invocation that finds the lock already present bails out instead of
        // both proceeding and duplicating presets.
        if ( ! add_option( 'boostbot_woocommerce_migration_lock', current_time( 'mysql' ), '', 'no' ) ) {
            $results['errors'][] = __( 'מיגרציית WooCommerce כבר רצה כעת', 'boostbot' );
            return $results;
        }

        // Step 1: Create default WooCommerce preset if none exists.
        $default_preset_id = $this->ensure_default_preset();
        if ( is_wp_error( $default_preset_id ) ) {
            $results['errors'][] = $default_preset_id->get_error_message();
        } else {
            ++$results['presets_created'];
        }

        // Step 2: Migrate Bundle Builder widget settings to presets.
        $widget_migration = $this->migrate_widget_settings();
        $results['presets_created']  += $widget_migration['created'];
        $results['coupons_migrated'] += $widget_migration['coupons'];
        $results['errors']            = array_merge( $results['errors'], $widget_migration['errors'] );

        // Step 3: Link bots without preset to default.
        $bots_updated = $this->link_bots_to_default( $default_preset_id );
        $results['bots_updated'] = $bots_updated;

        // Mark migration as complete and release the concurrency lock.
        update_option( 'boostbot_woocommerce_migration_complete', true );
        update_option( 'boostbot_woocommerce_migration_date', current_time( 'mysql' ) );
        delete_option( 'boostbot_woocommerce_migration_lock' );

        return $results;
    }

    /**
     * Check if migration is needed.
     *
     * @since 1.3.0
     * @return bool True if migration needed.
     */
    public function needs_migration(): bool {
        return ! get_option( 'boostbot_woocommerce_migration_complete', false );
    }

    /**
     * Ensure default WooCommerce preset exists.
     *
     * @since 1.3.0
     * @return int|WP_Error Preset ID or error.
     */
    private function ensure_default_preset() {
        $preset_model = new BoostBot_WooCommerce_Preset_Model();
        $default      = $preset_model->get_default();

        if ( $default ) {
            return $default->id;
        }

        // Create default preset.
        $id = $preset_model->create(
            array(
                'name'                  => __( 'פריסט ברירת מחדל', 'boostbot' ),
                'description'           => __( 'הגדרות WooCommerce ברירת מחדל', 'boostbot' ),
                'slug'                  => 'default',
                'bundle_enabled'        => 1,
                'bundle_min_products'   => 2,
                'bundle_max_products'   => 5,
                'bundle_discount_type'  => 'percent',
                'bundle_discount_value' => 10,
                'coupon_enabled'        => 1,
                'coupon_auto_apply'     => 0,
                'coupon_max_per_order'  => 1,
                'product_mode'          => 'all',
                'include_on_sale'       => 1,
                'include_variable'      => 1,
                'show_savings'          => 1,
                'success_message'       => __( 'הנה החבילה שהכנו עבורך! 🎁', 'boostbot' ),
                'empty_message'         => __( 'לא נמצאו מוצרים מתאימים.', 'boostbot' ),
                'is_default'            => 1,
                'is_system'             => 1,
                'is_active'             => 1,
            )
        );

        if ( ! $id ) {
            return new WP_Error( 'preset_creation_failed', __( 'שגיאה ביצירת פריסט ברירת מחדל', 'boostbot' ) );
        }

        return $id;
    }

    /**
     * Migrate Bundle Builder widget settings.
     *
     * @since 1.3.0
     * @return array Migration stats.
     */
    private function migrate_widget_settings(): array {
        global $wpdb;

        $result = array(
            'created' => 0,
            'coupons' => 0,
            'errors'  => array(),
        );

        // Get all Bundle Builder widgets.
        $widget_model = new BoostBot_Widget_Model();
        $widgets      = $widget_model->get_by_type( 'bundle_builder' );

        if ( empty( $widgets ) ) {
            return $result;
        }

        $preset_model  = new BoostBot_WooCommerce_Preset_Model();
        $coupon_model  = new BoostBot_WooCommerce_Coupon_Model();
        $presets_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_presets' );

        foreach ( $widgets as $widget ) {
            // Parse widget config. The widgets table stores this in the `config` column
            // (there is no `settings` column), so reading ->settings always yielded null
            // and the migration silently skipped every widget.
            $settings = is_string( $widget->config ) ? json_decode( $widget->config, true ) : (array) $widget->config;

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

            // Extract bundle settings.
            $product_settings = $settings['product_settings'] ?? array();
            $pricing_settings = $settings['pricing_settings'] ?? array();
            $coupon_settings  = $settings['coupon_settings'] ?? array();

            // Create a preset from widget settings.
            /* translators: %s: widget name */
            $preset_name = sprintf( __( 'מיובא מ-%s', 'boostbot' ), $widget->name ?: "Widget #{$widget->id}" );

            // Idempotency: skip widgets already imported (detected by matching the
            // imported preset name) so re-running does not create duplicate presets
            // or coupon links. Still re-link this widget's bots (harmless, sets the
            // same preset id).
            // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $existing_id = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT id FROM {$presets_table} WHERE name = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $preset_name
                )
            );
            if ( $existing_id ) {
                $this->link_widget_bots_to_preset( $widget->id, (int) $existing_id );
                continue;
            }

            $preset_id = $preset_model->create(
                array(
                    'name'                  => $preset_name,
                    /* translators: %d: widget ID */
                    'description'           => sprintf( __( 'הגדרות מיובאות מווידג\'ט %d', 'boostbot' ), $widget->id ),
                    'bundle_enabled'        => 1,
                    'bundle_min_products'   => (int) ( $product_settings['min_products'] ?? 2 ),
                    'bundle_max_products'   => (int) ( $product_settings['max_products'] ?? 5 ),
                    'bundle_discount_type'  => $pricing_settings['discount_type'] ?? 'percent',
                    'bundle_discount_value' => (float) ( $pricing_settings['discount_percent'] ?? 10 ),
                    'coupon_enabled'        => ! empty( $coupon_settings['enabled_coupons'] ) ? 1 : 0,
                    'product_mode'          => $product_settings['product_mode'] ?? 'all',
                    'allowed_categories'    => $product_settings['allowed_categories'] ?? array(),
                    'excluded_categories'   => $product_settings['excluded_categories'] ?? array(),
                    'include_on_sale'       => (int) ( $product_settings['include_on_sale'] ?? 1 ),
                    'include_variable'      => (int) ( $product_settings['include_variable'] ?? 1 ),
                    'show_savings'          => (int) ( $pricing_settings['show_savings'] ?? 1 ),
                    'is_active'             => 1,
                )
            );

            if ( $preset_id ) {
                ++$result['created'];

                // Migrate coupons.
                $enabled_coupons = $coupon_settings['enabled_coupons'] ?? array();
                foreach ( $enabled_coupons as $wc_coupon_id ) {
                    if ( ! is_numeric( $wc_coupon_id ) ) {
                        continue;
                    }

                    $coupon_id = $coupon_model->create(
                        array(
                            'preset_id'    => $preset_id,
                            'wc_coupon_id' => (int) $wc_coupon_id,
                            'priority'     => 0,
                            'is_active'    => 1,
                        )
                    );

                    if ( $coupon_id ) {
                        ++$result['coupons'];
                    }
                }

                // Link widget's associated bots to this preset.
                $this->link_widget_bots_to_preset( $widget->id, $preset_id );
            } else {
                /* translators: %d: widget ID */
                $result['errors'][] = sprintf( __( 'שגיאה ביצירת פריסט מווידג\'ט %d', 'boostbot' ), $widget->id );
            }
        }

        return $result;
    }

    /**
     * Link bots that used a widget to the new preset.
     *
     * @since 1.3.0
     * @param int $widget_id Widget ID.
     * @param int $preset_id Preset ID.
     * @return void
     */
    private function link_widget_bots_to_preset( int $widget_id, int $preset_id ): void {
        global $wpdb;

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

        // Find bots that have this widget enabled.
        // 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().
        $bots = $wpdb->get_results(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "SELECT id, enabled_widgets FROM {$bots_table} WHERE enabled_widgets LIKE %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                '%' . $wpdb->esc_like( '"' . $widget_id . '"' ) . '%'
            )
        );

        foreach ( $bots as $bot ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update(
                $bots_table,
                array( 'woocommerce_preset_id' => $preset_id ),
                array( 'id' => $bot->id ),
                array( '%d' ),
                array( '%d' )
            );
        }
    }

    /**
     * Link bots without preset to default.
     *
     * @since 1.3.0
     * @param int $default_preset_id Default preset ID.
     * @return int Number of bots updated.
     */
    private function link_bots_to_default( int $default_preset_id ): int {
        global $wpdb;

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

        // 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().
        $updated = $wpdb->query(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $wpdb->prepare(
                "UPDATE {$bots_table} SET woocommerce_preset_id = %d WHERE woocommerce_preset_id IS NULL", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $default_preset_id
            )
        );

        return (int) $updated;
    }

    /**
     * Rollback migration.
     *
     * @since 1.3.0
     * @return bool True if rolled back.
     */
    public function rollback(): bool {
        global $wpdb;

        // Delete all WooCommerce presets and coupons.
        $presets_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_presets' );
        $coupons_table = self::sanitize_identifier( $wpdb->prefix . 'boostbot_woocommerce_coupons' );
        $bots_table    = self::sanitize_identifier( $wpdb->prefix . 'boostbot_bots' );

        // 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( "TRUNCATE TABLE {$coupons_table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // 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( "TRUNCATE TABLE {$presets_table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Reset bots.
        // 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( "UPDATE {$bots_table} SET woocommerce_preset_id = NULL, woocommerce_custom_config = NULL" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        // Remove migration flags (including any stale concurrency lock).
        delete_option( 'boostbot_woocommerce_migration_complete' );
        delete_option( 'boostbot_woocommerce_migration_date' );
        delete_option( 'boostbot_woocommerce_migration_lock' );

        return true;
    }

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







