unis_crm/sql/upgrade_dashboard_analytics_card_display_config_pg17.sql

begin;

set search_path to public;

-- 经营分析卡片历史展示配置修正脚本
-- 说明：
-- 1. 归一化旧版 chartPreset 中文值，统一转成当前内部预设值。
-- 2. 为老的 pie/ring/funnel 卡片补齐 showDataLabels=true，避免升级后标签丢失。
-- 3. 对非堆叠 line/bar 卡片，如果误存 dataLabelMode=total，则回退为 value。
-- 4. 对 display_text_config 为空、非法 JSON、或非对象结构的卡片，按安全对象配置修正。
-- 5. 可重复执行。

do $$
declare
  rec record;
  parsed_config jsonb;
  next_config jsonb;
  preset_text text;
  normalized_preset text;
  parse_failed boolean;
begin
  if to_regclass('public.dashboard_analytics_card_config') is null then
    raise notice 'skip upgrade_dashboard_analytics_card_display_config_pg17: table dashboard_analytics_card_config does not exist';
    return;
  end if;

  for rec in
    select
      id,
      lower(coalesce(render_type, '')) as render_type,
      display_text_config
    from dashboard_analytics_card_config
  loop
    parse_failed := false;
    parsed_config := '{}'::jsonb;

    begin
      if rec.display_text_config is null or btrim(rec.display_text_config) = '' then
        parsed_config := '{}'::jsonb;
      else
        parsed_config := rec.display_text_config::jsonb;
        if jsonb_typeof(parsed_config) <> 'object' then
          parsed_config := '{}'::jsonb;
          parse_failed := true;
        end if;
      end if;
    exception
      when others then
        parsed_config := '{}'::jsonb;
        parse_failed := true;
    end;

    next_config := parsed_config;
    preset_text := nullif(btrim(coalesce(parsed_config ->> 'chartPreset', '')), '');

    normalized_preset := case preset_text
      when '基础折线图' then 'line-basic'
      when '平滑曲线图' then 'line-smooth'
      when '面积折线图' then 'line-area'
      when '阶梯折线图（起点）' then 'line-step-start'
      when '阶梯折线图（中点）' then 'line-step-middle'
      when '阶梯折线图（终点）' then 'line-step-end'
      when '堆叠折线图' then 'line-stacked'
      when '堆积折线图' then 'line-stacked'
      when '折线图' then 'line-basic'
      when '平滑折线图' then 'line-smooth'
      when '面积图' then 'line-area'
      when '基础柱状图' then 'bar-basic'
      when '柱状图' then 'bar-basic'
      when '横向柱状图' then 'bar-horizontal'
      when '条形图' then 'bar-horizontal'
      when '堆叠柱状图' then 'bar-stacked'
      when '堆积柱状图' then 'bar-stacked'
      when '横向堆叠柱状图' then 'bar-horizontal-stacked'
      when '横向堆积柱状图' then 'bar-horizontal-stacked'
      when '堆积条形图' then 'bar-horizontal-stacked'
      when '瀑布图' then 'bar-waterfall'
      when '动态排序柱状图（单帧）' then 'bar-race'
      when '动态排序柱状图（时间帧）' then 'bar-race-timeline'
      when '基础饼图' then 'pie-basic'
      when '饼图' then 'pie-basic'
      when '圆环图' then 'pie-doughnut'
      when '环形图' then case rec.render_type
        when 'ring' then 'ring-basic'
        else 'pie-doughnut'
      end
      when '南丁格尔玫瑰图' then 'pie-rose'
      when '玫瑰图' then case rec.render_type
        when 'ring' then 'ring-rose'
        else 'pie-rose'
      end
      when '嵌套饼图' then 'pie-nested'
      when '基础圆环图' then 'ring-basic'
      when '玫瑰圆环图' then 'ring-rose'
      when '嵌套圆环图' then 'ring-nested'
      when '基础漏斗图' then 'funnel-basic'
      when '漏斗图' then 'funnel-basic'
      when '对比漏斗图' then 'funnel-contrast'
      when '多系列漏斗图' then 'funnel-multiple'
      else preset_text
    end;

    if normalized_preset is distinct from preset_text and normalized_preset is not null then
      next_config := jsonb_set(next_config, '{chartPreset}', to_jsonb(normalized_preset), true);
    end if;

    if jsonb_typeof(next_config -> 'showDataLabels') = 'string' then
      if lower(coalesce(next_config ->> 'showDataLabels', '')) in ('true', '1', 'yes', 'y', 'on', '是') then
        next_config := jsonb_set(next_config, '{showDataLabels}', 'true'::jsonb, true);
      elsif lower(coalesce(next_config ->> 'showDataLabels', '')) in ('false', '0', 'no', 'n', 'off', '否') then
        next_config := jsonb_set(next_config, '{showDataLabels}', 'false'::jsonb, true);
      end if;
    end if;

    if jsonb_typeof(next_config -> 'showLegend') = 'string' then
      if lower(coalesce(next_config ->> 'showLegend', '')) in ('true', '1', 'yes', 'y', 'on', '是') then
        next_config := jsonb_set(next_config, '{showLegend}', 'true'::jsonb, true);
      elsif lower(coalesce(next_config ->> 'showLegend', '')) in ('false', '0', 'no', 'n', 'off', '否') then
        next_config := jsonb_set(next_config, '{showLegend}', 'false'::jsonb, true);
      end if;
    end if;

    if rec.render_type in ('pie', 'ring', 'funnel') and not (next_config ? 'showDataLabels') then
      next_config := jsonb_set(next_config, '{showDataLabels}', 'true'::jsonb, true);
    end if;

    if coalesce(next_config ->> 'dataLabelMode', '') = '只显示总数' then
      next_config := jsonb_set(next_config, '{dataLabelMode}', to_jsonb('total'::text), true);
    elsif coalesce(next_config ->> 'dataLabelMode', '') = '显示每段数值' then
      next_config := jsonb_set(next_config, '{dataLabelMode}', to_jsonb('value'::text), true);
    end if;

    if rec.render_type in ('line', 'bar')
      and coalesce(next_config ->> 'dataLabelMode', '') = 'total'
      and position('stacked' in coalesce(next_config ->> 'chartPreset', '')) = 0 then
      next_config := jsonb_set(next_config, '{dataLabelMode}', to_jsonb('value'::text), true);
    end if;

    if parse_failed or next_config is distinct from parsed_config then
      update dashboard_analytics_card_config
      set display_text_config = next_config::text,
          updated_at = now()
      where id = rec.id;
    end if;
  end loop;
end
$$;

commit;