-- Migration: Pre-test (general-link) Value Card session column
-- Description: Adds a pretest_value_card_details JSONB column to program_session holding the last
--              Value Card sent for the session to its GENERAL-LINK recipients — its description
--              and the uploaded file URLs (re-hosted under
--              pretestvaluecard/sessions/<sessionId>/):
--                { "description": "...", "documentUrls": ["https://.../card.pptx"] }
--
--              Deliberately a SEPARATE column from value_card_details rather than a reuse of it:
--              the two audiences for one session receive different material. value_card_details
--              belongs to the seeker send (registered seekers, hdb_program_registration), this one
--              to the general-link send (zoom_generated_registrant_link recipients, who hold no
--              registration). Sharing one column would make either send overwrite the other's
--              stored content, and the single/top-up send of each reads its own column back as the
--              source of truth for what to attach.
--
--              Nullable; null = no general-link value card sent yet. Idempotent.

ALTER TABLE program_session
    ADD COLUMN IF NOT EXISTS pretest_value_card_details JSONB NULL;

COMMENT ON COLUMN program_session.pretest_value_card_details IS
    'Last general-link ("pre-test") Value Card sent for this session: { description, documentUrls[] } (files re-hosted under pretestvaluecard/sessions/<sessionId>/). Separate from value_card_details, which is the seeker send. Null = none sent.';
