postgresql

CREATE OR REPLACE FUNCTION update_chat_groups()
RETURNS VOID AS $$
DECLARE
    raw_config_text TEXT;
    cleaned_config_text TEXT;
    chat_group_rec RECORD;
    current_chat_configs JSONB;  -- 重命名变量以避免歧义
    config_length INT;
    first_chat_id uuid;
    first_app_rec apps%ROWTYPE;
    first_model_config app_model_configs%ROWTYPE;
    new_app_id uuid;
    model_config_id uuid;
    replus INT;
    i INTEGER := 0;
BEGIN
    -- 遍历前两条未删除的ChatGroup记录
    FOR chat_group_rec IN
        SELECT * FROM chat_groups
        WHERE is_deleted = false
         AND EXTRACT(YEAR FROM created_at) = 2025
--          AND created_at >= '2024-05-01 00:00:00' AND created_at < '2024-12-30 00:00:00'
        ORDER BY created_at ASC  --  DESC 关键字表示降序排列，即从最近日期开始
--         LIMIT 10
    LOOP
        RAISE NOTICE '当前记录：%', chat_group_rec.id;

        -- 获取原始配置文本
        raw_config_text := chat_group_rec.chat_configs;
        RAISE NOTICE '原始值：%', raw_config_text;

        -- 特殊处理：替换转义的双引号
        cleaned_config_text := replace(raw_config_text, '\"', '"');

        -- 去掉外层可能存在的引号
        IF cleaned_config_text LIKE '"%"' THEN
            cleaned_config_text := substring(cleaned_config_text from 2 for length(cleaned_config_text)-2);
        END IF;

        -- 转换为JSONB
        BEGIN
            current_chat_configs := cleaned_config_text::jsonb;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '无法解析chat_configs，重置为空数组。错误：%', SQLERRM;
            current_chat_configs := '[]'::JSONB;
        END;

--         RAISE NOTICE '处理后值：%', current_chat_configs;
--         RAISE NOTICE '数据类型：%', pg_typeof(current_chat_configs);

        -- 检查是否为有效数组
        IF current_chat_configs IS NULL OR jsonb_typeof(current_chat_configs) <> 'array' THEN
            current_chat_configs := '[]'::JSONB;
        END IF;

        config_length := jsonb_array_length(current_chat_configs);
        RAISE NOTICE '处理ChatGroup %，当前配置数量：% ', chat_group_rec.id, config_length;

        -- 检查配置长度是否在1到3之间
--         IF config_length >= 1 AND config_length < 4 THEN
        IF config_length >= 1 AND config_length < 4 THEN
            replus := 4 - config_length;
            RAISE NOTICE '需要生成 %条记录', replus;

            -- 获取第一个chat_id对应的App记录（添加显式类型转换）
            first_chat_id := (current_chat_configs->0->>'chat_id')::uuid;
            SELECT * INTO first_app_rec FROM "apps" WHERE id = first_chat_id;
            SELECT * INTO first_model_config FROM "app_model_configs" WHERE app_id = first_chat_id;

            IF FOUND THEN
                -- 循环生成所需数量的新记录
                FOR i IN 1..replus LOOP
                    -- 插入新App记录
                    INSERT INTO "apps" (
                        tenant_id,
                        name,
                        description,
                        mode,
                        type,
                        icon,
                        icon_background,
                        workflow_id,
                        status,
                        enable_site,
                        enable_api,
                        api_rpm,
                        api_rph,
                        is_demo,
                        is_public,
                        is_universal,
                        tracing,
                        max_active_requests
                    )
                    VALUES (
                        first_app_rec.tenant_id,
                        first_app_rec.name,
                        first_app_rec.description,
                        first_app_rec.mode,
                        first_app_rec.type,
                        first_app_rec.icon,
                        first_app_rec.icon_background,
                        first_app_rec.workflow_id,
                        first_app_rec.status,
                        first_app_rec.enable_site,
                        first_app_rec.enable_api,
                        first_app_rec.api_rpm,
                        first_app_rec.api_rph,
                        first_app_rec.is_demo,
                        first_app_rec.is_public,
                        first_app_rec.is_universal,
                        first_app_rec.tracing,
                        first_app_rec.max_active_requests
                    )
                    RETURNING id INTO new_app_id;

                    -- 插入对应的AppModelConfig记录
                    INSERT INTO "app_model_configs" (
                                                     app_id,
                                                     model,
                                                    prompt_type,
                                                     file_upload,
                                                     rewrite_config,
                                                     keyword_extraction_config,
                                                     websearch_config
                                                     )
                    VALUES (new_app_id,
                        first_model_config.model,
                            first_model_config.prompt_type,
                            first_model_config.file_upload,
                            first_model_config.rewrite_config,
                            first_model_config.keyword_extraction_config,
                            first_model_config.websearch_config
                            )
                    RETURNING id INTO model_config_id;

                    -- 更新App的app_model_config_id字段
                    UPDATE "apps"
                    SET app_model_config_id = model_config_id
                    WHERE id = new_app_id;

                    -- 构建新配置项并追加到current_chat_configs数组
                    current_chat_configs := current_chat_configs || jsonb_build_object(
                        'chat_id', new_app_id::text,
                        'conversation_id', '',
                        'created_at', EXTRACT(EPOCH FROM NOW())::INT
                    );
                END LOOP;

                -- 更新ChatGroup的chat_configs字段（使用重命名的变量）
                UPDATE "chat_groups"
                SET chat_configs = ('"' || replace(current_chat_configs::text, '"', '\"') || '"')::json
--                 SET chat_configs = current_chat_configs
                WHERE id = chat_group_rec.id;
                RAISE NOTICE '生成记录：%', current_chat_configs;
                i := i + 1;
                RAISE NOTICE '更新记录数：%', i;
            END IF;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT update_chat_groups();