-- 004: add full body text storage for formal candidate items.

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

UPDATE creation_knowledge.candidate_items
SET body_text = LEFT(NULLIF(source_payload #>> '{detail,data,data,body_text}', ''), 50000)
WHERE body_text IS NULL
  AND NULLIF(source_payload #>> '{detail,data,data,body_text}', '') IS NOT NULL;

INSERT INTO creation_knowledge.schema_migrations(version, description)
VALUES ('004_candidate_items_body_text', 'Add candidate_items.body_text for full post text')
ON CONFLICT (version) DO UPDATE SET
    description = EXCLUDED.description,
    applied_at = now();