Raw DDL

-- ============================================================================
-- Lab Adhoc Lists - Table DDL
-- ============================================================================
-- Purpose: Adhoc lists for recipients
-- Primary Key: recipientListID (compound key: CRMID_ListID)
-- ============================================================================

CREATE TABLE lab_adhoc_lists (
    recipientListID VARCHAR(50) NOT NULL,
    recipientId VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    listId VARCHAR(50),
    modified TIMESTAMP NOT NULL,
    enrollDate TIMESTAMP,
    clickActivity BOOLEAN,
    msisdn VARCHAR(50),
    channel_preference INTEGER,
    PRIMARY KEY (recipientListID)
);

-- ============================================================================
-- Column Descriptions
-- ============================================================================
-- recipientListID:    Unique identifier for each adhoc list entry (PK); compound key (CRMID_ListID)
-- recipientId:       CRM identifier linking to recipient/customer
-- email:             Recipient email address
-- listId:            Brand/list identifier
-- modified:          Last modification timestamp (required)
-- enrollDate:        Date/time when recipient enrolled in list
-- clickActivity:     Boolean flag for click engagement
-- msisdn:            Mobile phone number (MSISDN format)
-- channel_preference: Channel preference (maps to "channel preference" 1= email, 2=sms, 3=DM)
-- ============================================================================