Create-knowledge-find-decode/db/migrations/002_candidate_items_unique_key.sql

-- 002: add formal candidate item unique_key.
-- Applied to database: creation_knowledge_prod

ALTER TABLE creation_knowledge.candidate_items
ADD COLUMN IF NOT EXISTS unique_key text;

WITH extracted AS (
    SELECT
        id,
        platform,
        COALESCE(
            NULLIF(platform_item_id, ''),
            NULLIF(source_payload #>> '{candidate,source_id}', ''),
            NULLIF(source_payload #>> '{detail,data,data,channel_content_id}', ''),
            NULLIF(substring(canonical_url from '/explore/([^/?#]+)'), '')
        ) AS xhs_id,
        COALESCE(
            NULLIF(platform_item_id, ''),
            NULLIF(source_payload #>> '{candidate,source_id}', ''),
            NULLIF(source_payload #>> '{candidate,raw,id}', ''),
            NULLIF(source_payload #>> '{detail,data,data,channel_content_id}', ''),
            NULLIF(substring(canonical_url from '/video/([^/?#]+)'), '')
        ) AS dy_id,
        substring(canonical_url from '[?&]__biz=([^&#]+)') AS wx_biz,
        substring(canonical_url from '[?&]mid=([^&#]+)') AS wx_mid,
        substring(canonical_url from '[?&]idx=([^&#]+)') AS wx_idx,
        substring(canonical_url from '[?&]sn=([^&#]+)') AS wx_sn
    FROM creation_knowledge.candidate_items
    WHERE unique_key IS NULL
)
UPDATE creation_knowledge.candidate_items ci
SET unique_key = CASE
    WHEN e.platform = 'xiaohongshu' AND e.xhs_id IS NOT NULL THEN 'xhs:' || e.xhs_id
    WHEN e.platform = 'douyin' AND e.dy_id IS NOT NULL THEN 'dy:' || e.dy_id
    WHEN e.platform = 'weixin'
        AND e.wx_biz IS NOT NULL
        AND e.wx_mid IS NOT NULL
        AND e.wx_idx IS NOT NULL
        AND e.wx_sn IS NOT NULL
        THEN 'wx:' || e.wx_biz || ':' || e.wx_mid || ':' || e.wx_idx || ':' || e.wx_sn
    ELSE NULL
END
FROM extracted e
WHERE ci.id = e.id;

CREATE UNIQUE INDEX IF NOT EXISTS idx_candidate_items_unique_key
ON creation_knowledge.candidate_items(unique_key)
WHERE unique_key IS NOT NULL;

INSERT INTO creation_knowledge.schema_migrations(version, description)
VALUES ('002_candidate_items_unique_key', 'Add candidate_items.unique_key and backfill existing candidate identities')
ON CONFLICT (version) DO UPDATE
SET description = EXCLUDED.description,
    applied_at = now();