<?php
namespace FleekDash\Database\Migrations;

use FleekDash\Database\Core\BaseMigration;

/**
 * Create Search Index Table
 *
 * Advanced search with FULLTEXT indexes
 * Converted to new format
 */
return new class extends BaseMigration {

    public function up(): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'fleekdash_search_index';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            object_id BIGINT(20) UNSIGNED NOT NULL,
            object_type VARCHAR(50) NOT NULL,
            title TEXT NOT NULL,
            content LONGTEXT,
            excerpt TEXT,
            keywords TEXT,
            metadata JSON,
            search_vector TEXT,
            popularity_score INT(11) DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_object (object_type, object_id),
            KEY idx_type_title (object_type, title(100)),
            KEY idx_popularity (popularity_score DESC),
            KEY idx_updated (updated_at DESC),
            FULLTEXT KEY idx_search_content (title, content, keywords),
            FULLTEXT KEY idx_search_vector (search_vector)
        ) {$charset_collate};";

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

        // Initialize options
        add_option('fleekdash_search_index_version', '1.0.0');
        add_option('fleekdash_search_last_indexed', 0);
        add_option('fleekdash_search_indexing_in_progress', false);
        add_option('fleekdash_search_index_stats', json_encode([
            'total_indexed' => 0,
            'last_full_index' => 0,
            'index_errors' => []
        ]));

        // Schedule background indexing
        if (!wp_next_scheduled('fleekdash_search_background_index')) {
            wp_schedule_event(time() + 60, 'hourly', 'fleekdash_search_background_index');
        }
    }

    public function down(): void
    {
        $this->dropTable('fleekdash_search_index');

        delete_option('fleekdash_search_index_version');
        delete_option('fleekdash_search_last_indexed');
        delete_option('fleekdash_search_indexing_in_progress');
        delete_option('fleekdash_search_index_stats');

        wp_clear_scheduled_hook('fleekdash_search_background_index');
        wp_clear_scheduled_hook('fleekdash_search_incremental_index');
    }
};

