<?php

namespace AC_SYNC\Includes\Classes\Sql\Version {

    if (!defined('ABSPATH')) exit;

    use AC_SYNC\Includes\Classes\Logger\Action_Wear_Log as Log;

    class Action_Wear_Sql_Version_1_1_0 extends Action_Wear_Sql_Version_Abstract implements Action_Wear_Sql_Version_Interface
    {
        public static function versioningScript()
        {
            global $wpdb;
            $charset_collate = $wpdb->get_charset_collate();
            $table_name = $wpdb->prefix . 'actionwear_jobs';

            // 1. Create the actionwear_jobs table
            $sql = "CREATE TABLE IF NOT EXISTS {$table_name} (
                id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                job_type       VARCHAR(64) NOT NULL,
                payload        LONGTEXT NULL,
                priority       TINYINT NOT NULL DEFAULT 50,
                conflict_group VARCHAR(64) NULL,
                status         ENUM('pending','claimed','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
                attempts       TINYINT UNSIGNED NOT NULL DEFAULT 0,
                max_attempts   TINYINT UNSIGNED NOT NULL DEFAULT 3,
                available_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                claimed_at     DATETIME NULL,
                claimed_by     VARCHAR(128) NULL,
                created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                INDEX idx_runner      (status, available_at, priority, id),
                INDEX idx_conflict    (conflict_group, status),
                INDEX idx_type_status (job_type, status),
                INDEX idx_claimed_by  (claimed_by, status)
            ) ENGINE=InnoDB {$charset_collate};";

            require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
            dbDelta($sql);

            // 2. Migrate existing batch rows from wp_options to job rows
            self::migrateBatchesToJobs();

            // 3. Cleanup old wp_options
            self::cleanupOldOptions();

            // 4. Seed recurring differential jobs
            self::seedRecurringJobs();

            // 5. Cleanup old cron events and schedule new ones
            self::migrateCronEvents();

            Log::write("DB Migration 1.1.0: Unified job queue created and data migrated", Log::INFO, Log::CONTEXT_GENERAL);
        }

        /**
         * Convert existing batch rows in wp_options to actionwear_jobs rows.
         */
        private static function migrateBatchesToJobs()
        {
            global $wpdb;
            $table = $wpdb->prefix . 'actionwear_jobs';
            $now = current_time('mysql', true);

            // Mapping of old batch prefixes to new job types
            $batchPrefixes = [
                'actionwear_product_import_batch_'           => ['type' => 'product_import',  'priority' => 10, 'conflict_group' => 'product_import'],
                'actionwear_images_update_batch_'            => ['type' => 'images_update',   'priority' => 20, 'conflict_group' => 'images_update'],
                'actionwear_bulk_price_sync_batch_'          => ['type' => 'bulk_price_batch', 'priority' => 40, 'conflict_group' => 'price_sync'],
                'actionwear_bulk_quantity_sync_batch_'       => ['type' => 'bulk_quantity_batch', 'priority' => 40, 'conflict_group' => 'quantity_sync'],
                'actionwear_differential_product_task_batch_' => ['type' => 'catalog_generate_categories', 'priority' => 80, 'conflict_group' => 'catalog_sync'],
                'actionwear_prices_update_batch_'            => ['type' => 'bulk_price_fetch', 'priority' => 30, 'conflict_group' => 'price_sync'],
            ];

            foreach ($batchPrefixes as $prefix => $config) {
                $batches = $wpdb->get_results($wpdb->prepare(
                    "SELECT option_name, option_value FROM {$wpdb->options} WHERE option_name LIKE %s",
                    $wpdb->esc_like($prefix) . '%'
                ));

                foreach ($batches as $batch) {
                    $items = maybe_unserialize($batch->option_value);
                    if (!is_array($items) || empty($items)) continue;

                    // For product imports, create individual jobs per SKU
                    if ($config['type'] === 'product_import') {
                        foreach ($items as $item) {
                            $sku = self::extractSkuFromItem($item);
                            if ($sku) {
                                $wpdb->insert($table, [
                                    'job_type'       => 'product_import',
                                    'payload'        => wp_json_encode(['sku' => $sku]),
                                    'priority'       => $config['priority'],
                                    'conflict_group' => $config['conflict_group'],
                                    'status'         => 'pending',
                                    'max_attempts'   => 3,
                                    'available_at'   => $now,
                                    'created_at'     => $now,
                                ]);
                            }
                        }
                    }
                    // For images updates, create individual jobs per SKU
                    elseif ($config['type'] === 'images_update') {
                        foreach ($items as $item) {
                            $sku = is_string($item) ? $item : (is_object($item) && isset($item->sku) ? $item->sku : null);
                            if ($sku) {
                                $wpdb->insert($table, [
                                    'job_type'       => 'images_update',
                                    'payload'        => wp_json_encode(['sku' => $sku]),
                                    'priority'       => $config['priority'],
                                    'conflict_group' => $config['conflict_group'],
                                    'status'         => 'pending',
                                    'max_attempts'   => 3,
                                    'available_at'   => $now,
                                    'created_at'     => $now,
                                ]);
                            }
                        }
                    }
                    // For bulk sync batches, keep the batch as a single job
                    elseif (in_array($config['type'], ['bulk_price_batch', 'bulk_quantity_batch'])) {
                        $wpdb->insert($table, [
                            'job_type'       => $config['type'],
                            'payload'        => wp_json_encode(['items' => $items]),
                            'priority'       => $config['priority'],
                            'conflict_group' => $config['conflict_group'],
                            'status'         => 'pending',
                            'max_attempts'   => 3,
                            'available_at'   => $now,
                            'created_at'     => $now,
                        ]);
                    }
                    // For differential product tasks (generateCategories etc.), create individual jobs
                    elseif ($config['type'] === 'catalog_generate_categories') {
                        $methodMap = [
                            'generateCategories' => 'catalog_generate_categories',
                            'generateProducts'   => 'catalog_generate_products',
                            'generateImages'     => 'catalog_generate_images',
                        ];
                        foreach ($items as $method) {
                            $jobType = $methodMap[$method] ?? null;
                            if ($jobType) {
                                $wpdb->insert($table, [
                                    'job_type'       => $jobType,
                                    'payload'        => null,
                                    'priority'       => 80,
                                    'conflict_group' => 'catalog_sync',
                                    'status'         => 'pending',
                                    'max_attempts'   => 3,
                                    'available_at'   => $now,
                                    'created_at'     => $now,
                                ]);
                            }
                        }
                    }
                    // For legacy prices_update, convert to single bulk_price_fetch job
                    elseif ($config['type'] === 'bulk_price_fetch') {
                        // Only insert one bulk_price_fetch job (not one per SKU)
                        $wpdb->insert($table, [
                            'job_type'       => 'bulk_price_fetch',
                            'payload'        => null,
                            'priority'       => 30,
                            'conflict_group' => 'price_sync',
                            'status'         => 'pending',
                            'max_attempts'   => 3,
                            'available_at'   => $now,
                            'created_at'     => $now,
                        ]);
                        break; // Only one fetch job needed regardless of batch count
                    }
                }
            }
        }

        /**
         * Extract SKU from various queue item formats.
         */
        private static function extractSkuFromItem($item): ?string
        {
            if (is_string($item)) return $item;
            if (is_object($item) && isset($item->sku)) return $item->sku;
            if (is_array($item)) {
                if (isset($item['_sku'])) return $item['_sku'];
                if (isset($item['sku'])) return $item['sku'];
                if (isset($item['_original'])) return self::extractSkuFromItem($item['_original']);
            }
            return null;
        }

        /**
         * Cleanup old wp_options entries from the old queue system.
         */
        private static function cleanupOldOptions()
        {
            global $wpdb;

            // Delete batch options
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_product_import_batch_%'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_images_update_batch_%'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_differential_product_task_batch_%'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_prices_update_batch_%'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_bulk_price_sync_batch_%'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_bulk_quantity_sync_batch_%'");

            // Delete status options
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'actionwear_%_status'");

            // Delete lock options
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE '_actionwear_lock_actionwear_%'");

            // Delete old heartbeats (replaced by single runner heartbeat)
            delete_option('_ACTIONWEAR_BG_HEARTBEATS');

            // Delete old delete_ flags
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE 'delete_actionwear_%'");

            // Delete old transients
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_actionwear_%_process_lock'");
            $wpdb->query("DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_actionwear_%_process_lock'");
        }

        /**
         * Seed initial recurring differential jobs.
         */
        private static function seedRecurringJobs()
        {
            global $wpdb;
            $table = $wpdb->prefix . 'actionwear_jobs';

            $recurringTypes = [
                'diff_price'    => ['priority' => 50, 'conflict_group' => 'price_sync',    'option' => '_ACTIONWEAR_LAST_PRICE_UPDATE',    'timing' => 3600],
                'diff_quantity' => ['priority' => 60, 'conflict_group' => 'quantity_sync', 'option' => '_ACTIONWEAR_LAST_QUANTITY_UPDATE', 'timing' => 300],
                'diff_catalog'  => ['priority' => 70, 'conflict_group' => 'catalog_sync',  'option' => '_ACTIONWEAR_LAST_PRODUCT_DIFFERENTIAL_UPDATE', 'timing' => 3600 * 36],
            ];

            foreach ($recurringTypes as $jobType => $config) {
                // Check if already exists (from migration)
                $exists = (int) $wpdb->get_var($wpdb->prepare(
                    "SELECT COUNT(*) FROM {$table} WHERE job_type = %s AND status IN ('pending', 'claimed')",
                    $jobType
                ));

                if ($exists > 0) continue;

                // Calculate available_at from last execution timestamp
                $lastExec = (int) get_option($config['option'], 0);
                $nextRun = $lastExec > 0 ? ($lastExec + $config['timing']) : time();

                if ($nextRun < time()) {
                    $nextRun = time();
                }

                $wpdb->insert($table, [
                    'job_type'       => $jobType,
                    'payload'        => null,
                    'priority'       => $config['priority'],
                    'conflict_group' => $config['conflict_group'],
                    'status'         => 'pending',
                    'max_attempts'   => 5,
                    'available_at'   => gmdate('Y-m-d H:i:s', $nextRun),
                    'created_at'     => current_time('mysql', true),
                ]);
            }
        }

        /**
         * Remove old cron events and schedule new ones.
         */
        private static function migrateCronEvents()
        {
            // Unschedule old events
            $oldHooks = [
                'camac_cron_hook',
                'actionwear_product_import_cron',
                'actionwear_differential_product_task_cron',
                'actionwear_prices_update_cron',
                'actionwear_images_update_cron',
                'actionwear_bulk_price_sync_cron',
                'actionwear_bulk_quantity_sync_cron',
            ];

            foreach ($oldHooks as $hook) {
                $timestamp = wp_next_scheduled($hook);
                if ($timestamp) {
                    wp_unschedule_event($timestamp, $hook);
                }
                // Clear all instances
                wp_clear_scheduled_hook($hook);
            }

            // Schedule new events (will be registered by Core if not already)
            if (!wp_next_scheduled('actionwear_runner_hook')) {
                wp_schedule_event(time(), 'actionwear_two_minutes', 'actionwear_runner_hook');
            }

            if (!wp_next_scheduled('actionwear_scheduler_check')) {
                wp_schedule_event(time(), 'weekly', 'actionwear_scheduler_check');
            }
        }
    }
}
