CREATE TRANSIENT TABLE IF NOT EXISTS {{ dim_hook_table_name }} (
    -- Core Identifiers
    ID VARCHAR(65535) NOT NULL COMMENT 'Unique identifier for the hook',
    PLATFORM VARCHAR(65535) COMMENT 'Platform where hook was created (web, ios, android)',
    LYRICS_LANGUAGE VARCHAR(65535) COMMENT 'Language of the hook',

    -- Timestamps
    CREATED_AT TIMESTAMP_TZ NOT NULL COMMENT 'When the hook was created',
    UPDATED_AT TIMESTAMP_TZ COMMENT 'When the hook was last updated',

    -- Clip Relationships
    ORIGINAL_CLIP VARCHAR(65535) COMMENT 'Reference to original clip if this is a derivative hook',

    -- Status and Content
    STATUS VARCHAR(65535) NOT NULL COMMENT 'Hook status (complete, processing, failed, rendered_passed_moderation, etc.)',
    TITLE VARCHAR(65535) COMMENT 'User-provided title for the hook',

    -- Video Timing
    START_CLIP_TIMESTAMP FLOAT COMMENT 'Start timestamp in seconds within the source',
    END_CLIP_TIMESTAMP FLOAT COMMENT 'End timestamp in seconds within the source',

    -- Media Storage References
    RENDERED_VIDEO_S3_ID VARCHAR(65535) COMMENT 'S3 ID for the final rendered video file',
    RENDERED_VIDEO_PREVIEW_S3_ID VARCHAR(65535) COMMENT 'S3 ID for video preview/thumbnail',
    THUMBNAIL_IMAGE_S3_ID VARCHAR(65535) COMMENT 'S3 ID for thumbnail image',
    RAW_VIDEO_UPLOAD_ID VARCHAR(65535) COMMENT 'S3 ID for original uploaded video file',
    RENDERED_WATERMARKED_VIDEO_S3_ID VARCHAR(65535) COMMENT 'S3 ID for watermarked version',

    -- User and Ownership
    USER_ID VARCHAR(65535) NOT NULL COMMENT 'ID of the user who created the hook',

    -- Flags and Settings
    IS_TEST BOOLEAN DEFAULT FALSE COMMENT 'Whether this is a test hook',
    ALLOW_COMMENTS BOOLEAN DEFAULT TRUE COMMENT 'Whether comments are enabled',
    IS_DELETED BOOLEAN DEFAULT FALSE COMMENT 'Soft delete flag',

    -- Content and Metadata
    CAPTION TEXT COMMENT 'User-provided caption or description',
    SHOW_LYRICS BOOLEAN DEFAULT FALSE COMMENT 'Whether to display lyrics overlay',
    CONTENT_RATING VARCHAR(65535) COMMENT 'Content rating (G, PG, R, etc.)',
    CONTENT_RATING_TAGS VARIANT COMMENT 'JSON array of content warning tags',

    -- Video Properties
    VIDEO_DURATION FLOAT COMMENT 'Duration of the video in seconds',
    VIDEO_STREAMING_RESOLUTIONS VARIANT COMMENT 'JSON array of available streaming resolutions',

    -- Review and Moderation
    STAFF_REVIEW_STATUS VARCHAR(65535) COMMENT 'Staff moderation status (approved, pending, rejected)',

    -- AI/ML Features (Gemini Extracted)
    IS_AI_GENERATED BOOLEAN COMMENT 'Whether the hook content is AI-generated (from Gemini analysis)',
    IS_UGC BOOLEAN COMMENT 'Whether the hook content is user-generated content (from Gemini analysis)',
    HAS_UGC_FACE BOOLEAN COMMENT 'Whether the hook contains user-generated faces (from Gemini analysis)',
    HAS_UGC_FACE_IN_FIRST_FRAME BOOLEAN COMMENT 'Whether the first frame contains user-generated faces (from Gemini analysis)',
    HOOK_CREATION_TYPE_JSON VARIANT COMMENT 'Hook creation type metadata as JSON (from Gemini analysis)',
    GEMINI_RATING VARCHAR(65535) COMMENT 'Content rating assigned by Gemini AI model',

    -- Technical Metadata
    VIDEO_UPLOAD_IDS VARIANT COMMENT 'JSON array of associated video upload IDs',
    CREATION_SOURCE VARCHAR(65535) COMMENT 'Source of creation (app, web, api, etc.)',
    METADATA_ID VARCHAR(65535) COMMENT 'Reference to additional metadata record',

    -- Partitioning Columns
    P_DATE DATE NOT NULL COMMENT 'Partition date (derived from created_at)',
    P_HOUR INTEGER NOT NULL COMMENT 'Partition hour 0-23 (derived from created_at)'
) CLUSTER BY (p_date, p_hour)
COMMENT = 'Dimension table containing hook/video metadata and properties';
