-- crm_channel_expansion.channel_code data fix:
-- normalize all existing channel codes to the current system rule
--
-- Rule:
-- QD-YYYYMMDD-序号(按记录创建日期、创建时间、ID排序生成)
--
-- Notes:
-- 1) all rows are recalculated using current rule
-- 2) rows are ordered by created_at asc, id asc within each day
-- 3) script is idempotent: rerun yields the same result

begin;

with ranked_all as (
    select
        id,
        'QD-' || to_char(created_at::date, 'YYYYMMDD') || '-' ||
        lpad(seq_no::text, 3, '0') as generated_code
    from (
        select
            id,
            created_at,
            row_number() over (
            partition by created_at::date
            order by created_at asc, id asc
            ) as seq_no,
            channel_code
        from crm_channel_expansion
    ) ranked_all
)
update crm_channel_expansion c
set channel_code = ra.generated_code
from ranked_all ra
where c.id = ra.id;

commit;