<?php

namespace XZD\AI\V5\Database;

/**
 * 数据库迁移管理 — 18张表的建表 + 版本控制
 */
class Schema
{
    private string $versionOption = 'xzd_ai_db_version';
    private string $currentVersion = '3.1.8';

    /**
     * 执行所有建表迁移
     */
    public function migrate(): void
    {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $charset = $wpdb->get_charset_collate();

        $installed = get_option($this->versionOption, '0.0.0');
        $needsCreate = version_compare($installed, $this->currentVersion, '<');

        if ($needsCreate) {
            if (!function_exists('dbDelta')) {
                require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            }

        // 1. AI对话会话
        $this->create("{$prefix}xzd_ai_chat_sessions", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            session_key VARCHAR(64) NOT NULL UNIQUE,
            user_id BIGINT NOT NULL,
            title VARCHAR(255) DEFAULT '',
            model VARCHAR(100) DEFAULT '',
            provider VARCHAR(50) DEFAULT '',
            status VARCHAR(20) DEFAULT 'active',
            total_tokens INT DEFAULT 0,
            total_cost DECIMAL(10,6) DEFAULT 0,
            message_count INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_user (user_id),
            KEY idx_session_key (session_key)
        ", $charset);

        // 2. AI对话消息
        $this->create("{$prefix}xzd_ai_chat_messages", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            session_key VARCHAR(64) NOT NULL,
            role VARCHAR(20) NOT NULL COMMENT 'user/assistant/system/tool',
            content LONGTEXT COMMENT '消息文本',
            tool_calls LONGTEXT DEFAULT NULL COMMENT '工具调用记录',
            tool_call_id VARCHAR(100) DEFAULT NULL,
            model VARCHAR(100) DEFAULT '',
            input_tokens INT DEFAULT 0,
            output_tokens INT DEFAULT 0,
            duration_ms INT DEFAULT 0,
            rating TINYINT DEFAULT 0 COMMENT '1-5星评价',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_session (session_key),
            KEY idx_created (created_at)
        ", $charset);

        // 3. SSE流式分块 — MySQL降级方案
        $this->create("{$prefix}xzd_ai_chat_stream_chunks", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            stream_id VARCHAR(64) NOT NULL,
            chunk_index INT NOT NULL,
            content TEXT NOT NULL,
            tool_call_chunk LONGTEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_stream (stream_id, chunk_index)
        ", $charset);

        // 4. RAG知识库 — FULLTEXT 预检兜底
        $this->createFulltext("{$prefix}xzd_ai_knowledge", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(500) NOT NULL,
            content LONGTEXT NOT NULL,
            source_type VARCHAR(50) DEFAULT 'manual',
            source_url VARCHAR(1000) DEFAULT '',
            status VARCHAR(20) DEFAULT 'active',
            chunk_count INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            {FULLTEXT} idx_content (title, content)
        ", $charset, 'idx_content');

        // 5. 知识库分块
        $this->create("{$prefix}xzd_ai_knowledge_chunks", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            knowledge_id BIGINT NOT NULL,
            chunk_index INT NOT NULL,
            content TEXT NOT NULL,
            embedding LONGTEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_knowledge (knowledge_id),
            KEY idx_chunk (knowledge_id, chunk_index)
        ", $charset);

        // 6. FAQ问答对 — FULLTEXT 预检兜底
        $this->createFulltext("{$prefix}xzd_ai_faq", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            question VARCHAR(500) NOT NULL,
            answer TEXT NOT NULL,
            category VARCHAR(100) DEFAULT '',
            sort_order INT DEFAULT 0,
            status VARCHAR(20) DEFAULT 'active',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            {FULLTEXT} idx_question (question, answer)
        ", $charset, 'idx_question');

        // 7. 意图案例库
        $this->create("{$prefix}xzd_ai_intent_cases", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            pattern VARCHAR(500) NOT NULL COMMENT '用户消息模式',
            intent VARCHAR(50) NOT NULL COMMENT '意图分类',
            agent VARCHAR(50) NOT NULL COMMENT '目标Agent',
            confidence DECIMAL(3,2) DEFAULT 0.00,
            example_count INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_intent (intent)
        ", $charset);

        // 8. 自动化规则
        $this->create("{$prefix}xzd_ai_automation_rules", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(200) NOT NULL,
            description TEXT,
            event VARCHAR(100) NOT NULL COMMENT '触发事件标识',
            conditions LONGTEXT DEFAULT NULL COMMENT '触发条件',
            actions LONGTEXT NOT NULL COMMENT '执行动作列表',
            enabled TINYINT(1) DEFAULT 0,
            created_by VARCHAR(50) DEFAULT 'manual' COMMENT 'manual/ai',
            operator_id BIGINT DEFAULT 0 COMMENT 'AI执行身份',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_event (event),
            KEY idx_enabled (enabled)
        ", $charset);

        // 9. 自动化执行日志
        $this->create("{$prefix}xzd_ai_automation_logs", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            rule_id BIGINT NOT NULL,
            rule_name VARCHAR(200) NOT NULL,
            event VARCHAR(100) NOT NULL,
            event_data LONGTEXT DEFAULT NULL,
            status VARCHAR(20) DEFAULT 'success',
            result LONGTEXT DEFAULT NULL,
            error_message TEXT,
            duration_ms INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_rule (rule_id),
            KEY idx_created (created_at)
        ", $charset);

        // 10. 统一操作日志
        $this->create("{$prefix}xzd_ai_operation_log", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            tool_id VARCHAR(100) NOT NULL COMMENT '工具ID',
            tool_name VARCHAR(200) NOT NULL COMMENT '工具名称',
            category VARCHAR(50) NOT NULL COMMENT '工具分类',
            target_type VARCHAR(50) COMMENT '目标类型',
            target_id BIGINT COMMENT '目标ID',
            params LONGTEXT COMMENT '请求参数(脱敏)',
            result LONGTEXT COMMENT '返回结果',
            status VARCHAR(20) DEFAULT 'success',
            error_message TEXT COMMENT '失败原因',
            undo_data LONGTEXT COMMENT '撤销所需数据',
            undo_window INT DEFAULT 300,
            is_undone TINYINT(1) DEFAULT 0,
            undone_at DATETIME,
            user_id BIGINT NOT NULL,
            operator_id BIGINT,
            session_key VARCHAR(64),
            ip VARCHAR(45),
            cost_tokens INT DEFAULT 0,
            cost_rmb DECIMAL(10,6) DEFAULT 0,
            duration_ms INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_tool (tool_id),
            KEY idx_status (status),
            KEY idx_user (user_id),
            KEY idx_target (target_type, target_id),
            KEY idx_created (created_at)
        ", $charset);

        // 11. 成本追踪
        $this->create("{$prefix}xzd_ai_cost_log", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            provider VARCHAR(50) NOT NULL,
            model VARCHAR(100) NOT NULL,
            tool_id VARCHAR(100),
            operation_id BIGINT,
            input_tokens INT DEFAULT 0,
            output_tokens INT DEFAULT 0,
            cache_read_tokens INT DEFAULT 0,
            cost_rmb DECIMAL(10,6) DEFAULT 0,
            duration_ms INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_date (created_at),
            KEY idx_provider (provider),
            KEY idx_tool (tool_id)
        ", $charset);

        // 以下表从旧版保留，若不存在则创建

        // 12. 任务队列
        $this->create("{$prefix}xzd_ai_queue", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            post_id BIGINT NOT NULL,
            task_type VARCHAR(50) NOT NULL,
            status VARCHAR(20) DEFAULT 'pending',
            priority INT DEFAULT 0,
            attempts INT DEFAULT 0,
            max_attempts INT DEFAULT 3,
            error_message TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            processed_at DATETIME,
            KEY idx_post (post_id),
            KEY idx_status (status, priority)
        ", $charset);

        // 13. 标签索引
        $this->create("{$prefix}xzd_ai_tag_index", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            tag_name VARCHAR(200) NOT NULL,
            tag_slug VARCHAR(200) NOT NULL,
            post_count INT DEFAULT 0,
            confidence INT DEFAULT 0,
            source VARCHAR(50) DEFAULT 'ai',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uk_slug (tag_slug),
            KEY idx_name (tag_name)
        ", $charset);

        // 14. 日志过渡
        $this->create("{$prefix}xzd_ai_logs", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            post_id BIGINT DEFAULT 0,
            action VARCHAR(100) NOT NULL,
            status VARCHAR(20) DEFAULT 'success',
            message TEXT,
            context LONGTEXT DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_post (post_id),
            KEY idx_action (action)
        ", $charset);

        // 15. AI写文章计划
        $this->create("{$prefix}xzd_ai_write_plans", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            category_id BIGINT DEFAULT 0,
            keywords TEXT,
            schedule VARCHAR(50) DEFAULT 'daily',
            post_count INT DEFAULT 1,
            post_status VARCHAR(20) DEFAULT 'draft',
            enabled TINYINT(1) DEFAULT 1,
            last_run DATETIME,
            next_run DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ", $charset);

        // 16. AI写文章追踪
        $this->create("{$prefix}xzd_ai_write_articles", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            plan_id BIGINT NOT NULL,
            post_id BIGINT DEFAULT 0,
            source_keyword VARCHAR(500),
            status VARCHAR(20) DEFAULT 'pending',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            KEY idx_plan (plan_id)
        ", $charset);

        // 17. AI 模板/配方
        $this->create("{$prefix}xzd_ai_recipes", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(200) NOT NULL,
            description TEXT,
            category VARCHAR(50) DEFAULT 'general',
            steps LONGTEXT NOT NULL,
            created_by BIGINT DEFAULT 0,
            is_public TINYINT(1) DEFAULT 0,
            usage_count INT DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_category (category),
            KEY idx_created_by (created_by)
        ", $charset);

        // 18. 知识库学习日志
        $this->create("{$prefix}xzd_ai_learning_log", "
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            mode VARCHAR(20) NOT NULL COMMENT 'incremental/full/manual',
            site_type VARCHAR(30) NOT NULL COMMENT 'primary site type',
            strategy VARCHAR(30) NOT NULL COMMENT 'blog/shop/forum/...',
            items_processed INT DEFAULT 0,
            items_skipped INT DEFAULT 0,
            knowledge_added INT DEFAULT 0,
            chunks_added INT DEFAULT 0,
            tokens_used INT DEFAULT 0,
            cost_rmb DECIMAL(10,4) DEFAULT 0,
            duration_seconds INT DEFAULT 0,
            status VARCHAR(20) DEFAULT 'running' COMMENT 'running/completed/failed',
            error_message TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            completed_at DATETIME NULL,
            KEY idx_status (status),
            KEY idx_created (created_at)
        ", $charset);

        // 验证迁移是否成功，失败则回退到直接 SQL
        $verifyTable = $prefix . 'xzd_ai_operation_log';
        $tableExists = $wpdb->get_var(
            $wpdb->prepare('SHOW TABLES LIKE %s', $verifyTable)
        ) === $verifyTable;

        if (!$tableExists) {
            error_log('[XZD-AI] dbDelta() 建表未生效，使用直接 SQL 回退');
            $this->fallbackMigrate();
            $tableExists = $wpdb->get_var(
                $wpdb->prepare('SHOW TABLES LIKE %s', $verifyTable)
            ) === $verifyTable;
        }

        if (!$tableExists) {
            error_log('[XZD-AI] 数据库迁移失败 — dbDelta 和直接 SQL 均未能建表');
            return;
        }

            // 列迁移 — 仅在版本变更时执行
            $this->migrateColumns($prefix);

            update_option($this->versionOption, $this->currentVersion, false);
        } // end if ($needsCreate)
    }

    /**
     * 增量列迁移 — 每次 admin_init 均执行，确保旧表与新代码兼容
     */
    private function migrateColumns(string $prefix): void
    {
        // 自动化规则表扩展字段
        $this->addColumnIfMissing("{$prefix}xzd_ai_automation_rules", 'rule_type', "VARCHAR(20) DEFAULT 'permanent' COMMENT 'permanent/oneshot/expiring/limited'");
        $this->addColumnIfMissing("{$prefix}xzd_ai_automation_rules", 'expires_at', "DATETIME DEFAULT NULL");
        $this->addColumnIfMissing("{$prefix}xzd_ai_automation_rules", 'max_executions', "INT DEFAULT 0 COMMENT '0=无限'");
        $this->addColumnIfMissing("{$prefix}xzd_ai_automation_rules", 'execution_count', "INT DEFAULT 0");
        $this->addColumnIfMissing("{$prefix}xzd_ai_automation_rules", 'last_executed_at', "DATETIME DEFAULT NULL");

        // 对话会话表扩展字段 — 兼容旧表结构
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'title', "VARCHAR(255) DEFAULT ''");
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'model', "VARCHAR(100) DEFAULT ''");
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'provider', "VARCHAR(50) DEFAULT ''");
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'total_tokens', "INT DEFAULT 0");
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'total_cost', "DECIMAL(10,6) DEFAULT 0");
        $this->addColumnIfMissing("{$prefix}xzd_ai_chat_sessions", 'updated_at', "DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP");

        // AI学习全站化 — 权限感知知识标记
        $this->addColumnIfMissing("{$prefix}xzd_ai_knowledge", 'access_level', "VARCHAR(30) NOT NULL DEFAULT 'public'");

        // 性能索引迁移
        $this->migrateIndexes($prefix);
    }

    /**
     * 性能索引迁移 — 为高频查询添加复合索引
     */
    private function migrateIndexes(string $prefix): void
    {
        // 操作日志: 按分类+状态+时间查询（Dashboard面板高频查询）
        $this->addIndexIfMissing("{$prefix}xzd_ai_operation_log", 'idx_category_status_created', '(category, status, created_at)');

        // 成本日志: 按创建时间倒序查询（成本分析面板）
        $this->addIndexIfMissing("{$prefix}xzd_ai_cost_log", 'idx_created_provider', '(created_at, provider)');

        // postmeta 流水线状态: 加速 _xzd_ai_pipeline_status 查询
        $this->addIndexIfMissing("{$prefix}postmeta", 'idx_xzd_pipeline_status', '(meta_key, meta_value(50))');
    }

    /**
     * 安全添加索引（如果不存在）
     */
    private function addIndexIfMissing(string $table, string $indexName, string $columns): void
    {
        global $wpdb;
        // 先确认表存在
        $tableExists = $wpdb->get_var($wpdb->prepare(
            'SHOW TABLES LIKE %s', $table
        )) === $table;
        if (!$tableExists) {
            return;
        }
        // 检查索引是否已存在
        $indexExists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND INDEX_NAME = %s",
            $table, $indexName
        )) > 0;
        if (!$indexExists) {
            $wpdb->query("ALTER TABLE {$table} ADD INDEX {$indexName} {$columns}");
            error_log("[XZD-AI] 索引创建: {$table}.{$indexName} ({$columns})");
        }
    }

    /**
     * 安全添加列（如果不存在）
     */
    private function addColumnIfMissing(string $table, string $column, string $definition): void
    {
        global $wpdb;
        // 先确认表存在，避免对不存在的表执行 ALTER TABLE
        $tableExists = $wpdb->get_var($wpdb->prepare(
            'SHOW TABLES LIKE %s', $table
        )) === $table;
        if (!$tableExists) {
            return;
        }
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s",
            $table, $column
        ));
        if (empty($row)) {
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN {$column} {$definition}");
        }
    }

    /**
     * 回退迁移 — 使用直接 $wpdb->query() 代替 dbDelta
     */
    private function fallbackMigrate(): void
    {
        global $wpdb;
        $prefix = $wpdb->prefix;
        $charset = $wpdb->get_charset_collate();

        $tables = $this->getAllTableSchemas($prefix);
        foreach ($tables as $tableName => $schema) {
            // FULLTEXT 表特殊处理 — 自动降级兜底
            if (str_contains($schema, '{FULLTEXT}')) {
                $indexName = str_contains($tableName, 'knowledge') ? 'idx_content' : 'idx_question';
                $this->createFulltext($tableName, $schema, $charset, $indexName);
            } else {
                $this->create($tableName, $schema, $charset);
            }
        }
    }

    /**
     * 返回所有表的 DDL（与 migrate() 中保持一致）
     */
    private function getAllTableSchemas(string $prefix): array
    {
        return [
            "{$prefix}xzd_ai_chat_sessions" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                session_key VARCHAR(64) NOT NULL UNIQUE,
                user_id BIGINT NOT NULL,
                title VARCHAR(255) DEFAULT '',
                model VARCHAR(100) DEFAULT '',
                provider VARCHAR(50) DEFAULT '',
                status VARCHAR(20) DEFAULT 'active',
                total_tokens INT DEFAULT 0,
                total_cost DECIMAL(10,6) DEFAULT 0,
                message_count INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_user (user_id),
                KEY idx_session_key (session_key)
            ",
            "{$prefix}xzd_ai_chat_messages" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                session_key VARCHAR(64) NOT NULL,
                role VARCHAR(20) NOT NULL COMMENT 'user/assistant/system/tool',
                content LONGTEXT COMMENT '消息文本',
                tool_calls LONGTEXT DEFAULT NULL COMMENT '工具调用记录',
                tool_call_id VARCHAR(100) DEFAULT NULL,
                model VARCHAR(100) DEFAULT '',
                input_tokens INT DEFAULT 0,
                output_tokens INT DEFAULT 0,
                duration_ms INT DEFAULT 0,
                rating TINYINT DEFAULT 0 COMMENT '1-5星评价',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_session (session_key),
                KEY idx_created (created_at)
            ",
            "{$prefix}xzd_ai_chat_stream_chunks" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                stream_id VARCHAR(64) NOT NULL,
                chunk_index INT NOT NULL,
                content TEXT NOT NULL,
                tool_call_chunk LONGTEXT DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_stream (stream_id, chunk_index)
            ",
            "{$prefix}xzd_ai_knowledge" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(500) NOT NULL,
                content LONGTEXT NOT NULL,
                source_type VARCHAR(50) DEFAULT 'manual',
                source_url VARCHAR(1000) DEFAULT '',
                status VARCHAR(20) DEFAULT 'active',
                access_level VARCHAR(30) NOT NULL DEFAULT 'public',
                chunk_count INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                {FULLTEXT} idx_content (title, content)
            ",
            "{$prefix}xzd_ai_knowledge_chunks" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                knowledge_id BIGINT NOT NULL,
                chunk_index INT NOT NULL,
                content TEXT NOT NULL,
                embedding LONGTEXT DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_knowledge (knowledge_id),
                KEY idx_chunk (knowledge_id, chunk_index)
            ",
            "{$prefix}xzd_ai_faq" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                question VARCHAR(500) NOT NULL,
                answer TEXT NOT NULL,
                category VARCHAR(100) DEFAULT '',
                sort_order INT DEFAULT 0,
                status VARCHAR(20) DEFAULT 'active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                {FULLTEXT} idx_question (question, answer)
            ",
            "{$prefix}xzd_ai_intent_cases" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                pattern VARCHAR(500) NOT NULL COMMENT '用户消息模式',
                intent VARCHAR(50) NOT NULL COMMENT '意图分类',
                agent VARCHAR(50) NOT NULL COMMENT '目标Agent',
                confidence DECIMAL(3,2) DEFAULT 0.00,
                example_count INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_intent (intent)
            ",
            "{$prefix}xzd_ai_automation_rules" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                description TEXT,
                event VARCHAR(100) NOT NULL COMMENT '触发事件标识',
                conditions LONGTEXT DEFAULT NULL COMMENT '触发条件',
                actions LONGTEXT NOT NULL COMMENT '执行动作列表',
                enabled TINYINT(1) DEFAULT 0,
                created_by VARCHAR(50) DEFAULT 'manual' COMMENT 'manual/ai',
                operator_id BIGINT DEFAULT 0 COMMENT 'AI执行身份',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_event (event),
                KEY idx_enabled (enabled)
            ",
            "{$prefix}xzd_ai_automation_logs" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                rule_id BIGINT NOT NULL,
                rule_name VARCHAR(200) NOT NULL,
                event VARCHAR(100) NOT NULL,
                event_data LONGTEXT DEFAULT NULL,
                status VARCHAR(20) DEFAULT 'success',
                result LONGTEXT DEFAULT NULL,
                error_message TEXT,
                duration_ms INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_rule (rule_id),
                KEY idx_created (created_at)
            ",
            "{$prefix}xzd_ai_operation_log" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                tool_id VARCHAR(100) NOT NULL COMMENT '工具ID',
                tool_name VARCHAR(200) NOT NULL COMMENT '工具名称',
                category VARCHAR(50) NOT NULL COMMENT '工具分类',
                target_type VARCHAR(50) COMMENT '目标类型',
                target_id BIGINT COMMENT '目标ID',
                params LONGTEXT COMMENT '请求参数(脱敏)',
                result LONGTEXT COMMENT '返回结果',
                status VARCHAR(20) DEFAULT 'success',
                error_message TEXT COMMENT '失败原因',
                undo_data LONGTEXT COMMENT '撤销所需数据',
                undo_window INT DEFAULT 300,
                is_undone TINYINT(1) DEFAULT 0,
                undone_at DATETIME,
                user_id BIGINT NOT NULL,
                operator_id BIGINT,
                session_key VARCHAR(64),
                ip VARCHAR(45),
                cost_tokens INT DEFAULT 0,
                cost_rmb DECIMAL(10,6) DEFAULT 0,
                duration_ms INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_tool (tool_id),
                KEY idx_status (status),
                KEY idx_user (user_id),
                KEY idx_target (target_type, target_id),
                KEY idx_created (created_at)
            ",
            "{$prefix}xzd_ai_cost_log" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                provider VARCHAR(50) NOT NULL,
                model VARCHAR(100) NOT NULL,
                tool_id VARCHAR(100),
                operation_id BIGINT,
                input_tokens INT DEFAULT 0,
                output_tokens INT DEFAULT 0,
                cache_read_tokens INT DEFAULT 0,
                cost_rmb DECIMAL(10,6) DEFAULT 0,
                duration_ms INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_date (created_at),
                KEY idx_provider (provider),
                KEY idx_tool (tool_id)
            ",
            "{$prefix}xzd_ai_queue" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                post_id BIGINT NOT NULL,
                task_type VARCHAR(50) NOT NULL,
                status VARCHAR(20) DEFAULT 'pending',
                priority INT DEFAULT 0,
                attempts INT DEFAULT 0,
                max_attempts INT DEFAULT 3,
                error_message TEXT,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                processed_at DATETIME,
                KEY idx_post (post_id),
                KEY idx_status (status, priority)
            ",
            "{$prefix}xzd_ai_tag_index" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                tag_name VARCHAR(200) NOT NULL,
                tag_slug VARCHAR(200) NOT NULL,
                post_count INT DEFAULT 0,
                confidence INT DEFAULT 0,
                source VARCHAR(50) DEFAULT 'ai',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uk_slug (tag_slug),
                KEY idx_name (tag_name)
            ",
            "{$prefix}xzd_ai_logs" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                post_id BIGINT DEFAULT 0,
                action VARCHAR(100) NOT NULL,
                status VARCHAR(20) DEFAULT 'success',
                message TEXT,
                context LONGTEXT DEFAULT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_post (post_id),
                KEY idx_action (action)
            ",
            "{$prefix}xzd_ai_write_plans" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                category_id BIGINT DEFAULT 0,
                keywords TEXT,
                schedule VARCHAR(50) DEFAULT 'daily',
                post_count INT DEFAULT 1,
                post_status VARCHAR(20) DEFAULT 'draft',
                enabled TINYINT(1) DEFAULT 1,
                last_run DATETIME,
                next_run DATETIME,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ",
            "{$prefix}xzd_ai_write_articles" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                plan_id BIGINT NOT NULL,
                post_id BIGINT DEFAULT 0,
                source_keyword VARCHAR(500),
                status VARCHAR(20) DEFAULT 'pending',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                KEY idx_plan (plan_id)
            ",
            "{$prefix}xzd_ai_recipes" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                description TEXT,
                category VARCHAR(50) DEFAULT 'general',
                steps LONGTEXT NOT NULL,
                created_by BIGINT DEFAULT 0,
                is_public TINYINT(1) DEFAULT 0,
                usage_count INT DEFAULT 0,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                KEY idx_category (category),
                KEY idx_created_by (created_by)
            ",
            "{$prefix}xzd_ai_learning_log" => "
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                mode VARCHAR(20) NOT NULL COMMENT 'incremental/full/manual',
                site_type VARCHAR(30) NOT NULL COMMENT 'primary site type',
                strategy VARCHAR(30) NOT NULL COMMENT 'blog/shop/forum/...',
                items_processed INT DEFAULT 0,
                items_skipped INT DEFAULT 0,
                knowledge_added INT DEFAULT 0,
                chunks_added INT DEFAULT 0,
                tokens_used INT DEFAULT 0,
                cost_rmb DECIMAL(10,4) DEFAULT 0,
                duration_seconds INT DEFAULT 0,
                status VARCHAR(20) DEFAULT 'running' COMMENT 'running/completed/failed',
                error_message TEXT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                completed_at DATETIME NULL,
                KEY idx_status (status),
                KEY idx_created (created_at)
            ",
        ];
    }

    /**
     * 卸载 — 删除所有表
     */
    public function uninstall(): void
    {
        global $wpdb;
        $prefix = $wpdb->prefix;

        $v5Tables = [
            'xzd_ai_chat_sessions',
            'xzd_ai_chat_messages',
            'xzd_ai_chat_stream_chunks',
            'xzd_ai_knowledge',
            'xzd_ai_knowledge_chunks',
            'xzd_ai_faq',
            'xzd_ai_intent_cases',
            'xzd_ai_automation_rules',
            'xzd_ai_automation_logs',
            'xzd_ai_operation_log',
            'xzd_ai_cost_log',
            'xzd_ai_recipes',
            'xzd_ai_learning_log',
        ];

        $v2Tables = [
            'xzd_ai_queue',
            'xzd_ai_tag_index',
            'xzd_ai_logs',
            'xzd_ai_write_plans',
            'xzd_ai_write_articles',
        ];

        foreach (array_merge($v5Tables, $v2Tables) as $table) {
            $wpdb->query("DROP TABLE IF EXISTS {$prefix}{$table}");
        }

        delete_option($this->versionOption);
        delete_option('xzd_ai_settings');
    }

    /**
     * 创建含 FULLTEXT 索引的表 — 自动降级兜底
     *
     * {FULLTEXT} 占位符在支持时替换为 FULLTEXT，否则降级为普通 KEY。
     */
    private function createFulltext(string $table, string $schema, string $charset, string $indexName): bool
    {
        // 尝试1: 使用 FULLTEXT
        $sql = str_replace('{FULLTEXT}', 'FULLTEXT', $schema);
        $ok = $this->create($table, $sql, $charset);
        if ($ok) {
            return true;
        }
        // 尝试2: 降级为普通 KEY（无 FULLTEXT 修饰符）
        $sql = str_replace('{FULLTEXT}', 'KEY', $schema);
        error_log("[XZD-AI] FULLTEXT 不支持, 降级为普通索引: {$table}.{$indexName}");
        set_transient('xzd_ai_fulltext_degraded', true, MONTH_IN_SECONDS);
        return $this->create($table, $sql, $charset);
    }

    /**
     * 创建表（仅在不存在时）— 使用直接 SQL 而非 dbDelta()，避免格式偏差导致建表静默失败
     */
    private function create(string $table, string $schema, string $charset): bool
    {
        global $wpdb;
        $sql = "CREATE TABLE IF NOT EXISTS {$table} ({$schema}) {$charset}";
        $result = $wpdb->query($sql);
        if ($result === false) {
            error_log("[XZD-AI] 建表失败: {$table} — " . $wpdb->last_error);
        }
        return $result !== false;
    }
}
