Create-knowledge-find-decode/db/migrations/003_candidate_items_content_mode.sql

-- 003: add formal candidate item content_mode.
-- Applied to database: creation_knowledge_prod

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

UPDATE creation_knowledge.candidate_items ci
SET content_mode = CASE
    WHEN ci.platform = 'weixin' THEN 'article'
    WHEN EXISTS (
        SELECT 1 FROM creation_knowledge.media_assets ma
        WHERE ma.item_id = ci.id AND ma.media_type = 'video'
    ) THEN 'video_post'
    WHEN EXISTS (
        SELECT 1 FROM creation_knowledge.media_assets ma
        WHERE ma.item_id = ci.id AND ma.media_type IN ('image', 'cover', 'frame')
    ) OR NULLIF(ci.raw_summary, '') IS NOT NULL THEN 'image_post'
    ELSE 'unsupported'
END
WHERE ci.content_mode IS NULL;

CREATE INDEX IF NOT EXISTS idx_candidate_items_content_mode
ON creation_knowledge.candidate_items(content_mode);

INSERT INTO creation_knowledge.schema_migrations(version, description)
VALUES ('003_candidate_items_content_mode', 'Add candidate_items.content_mode and backfill business media modes')
ON CONFLICT (version) DO UPDATE
SET description = EXCLUDED.description,
    applied_at = now();