-- Deleted video hook records from RDS
-- This table stores soft-deleted video hooks for historical tracking and potential recovery
-- Populated by the same Glue job (rds_to_s3_video_hook_hourly_insert) that processes active records
-- Clustered by partition date and hour for optimal query performance
create TRANSIENT TABLE DELETED_RDS_VIDEO_HOOK (
	-- Primary identifier
	ID VARCHAR(255),  -- Unique video hook identifier
	
	-- Timestamps
	CREATED_AT TIMESTAMP_TZ(9),  -- When the video hook was originally created
	UPDATED_AT TIMESTAMP_TZ(9),  -- Last update timestamp (typically when it was deleted)
	
	-- Video source
	ORIGINAL_CLIP VARCHAR(255),  -- Original clip identifier that this video hook is based on
	
	-- Status and metadata
	STATUS TEXT,  -- Status of video processing (e.g., 'complete', 'processing', 'failed')
	TITLE TEXT,  -- Video hook title/name
	START_CLIP_TIMESTAMP FLOAT,  -- Start time in seconds for the clip
	END_CLIP_TIMESTAMP FLOAT,  -- End time in seconds for the clip
	
	-- S3 storage identifiers
	RENDERED_VIDEO_S3_ID TEXT,  -- S3 identifier for the final rendered video
	RENDERED_VIDEO_PREVIEW_S3_ID TEXT,  -- S3 identifier for video preview/thumbnail video
	THUMBNAIL_IMAGE_S3_ID TEXT,  -- S3 identifier for the thumbnail image
	RAW_VIDEO_UPLOAD_ID VARCHAR(255),  -- Identifier for raw uploaded video
	RENDERED_WATERMARKED_VIDEO_S3_ID TEXT,  -- S3 identifier for watermarked version
	
	-- User and ownership
	USER_ID INTEGER,  -- User who created the video hook
	IS_TEST BOOLEAN,  -- Flag indicating if this is a test video hook
	
	-- Display settings
	ALLOW_COMMENTS BOOLEAN,  -- Whether comments are allowed on this video hook
	CAPTION TEXT,  -- User-provided caption/description
	SHOW_LYRICS BOOLEAN,  -- Whether to display lyrics overlay on the video
	
	-- Content moderation
	CONTENT_RATING TEXT,  -- Content rating (e.g., 'G', 'PG', 'R')
	CONTENT_RATING_TAGS ARRAY,  -- Array of content tags for filtering/moderation
	
	-- Video specifications
	VIDEO_DURATION FLOAT,  -- Total duration of the video in seconds
	VIDEO_STREAMING_RESOLUTIONS ARRAY,  -- Array of available streaming resolutions
	
	-- Status flags
	IS_DELETED BOOLEAN,  -- Soft delete flag (should be TRUE for all records in this table)
	STAFF_REVIEW_STATUS INTEGER,  -- Status code for staff review (0=pending, 1=approved, 2=rejected, etc.)
	
	-- Additional metadata
	VIDEO_UPLOAD_IDS ARRAY,  -- Array of associated video upload identifiers
	CREATION_SOURCE TEXT,  -- Source of creation (e.g., 'web', 'mobile', 'api')
	METADATA_ID VARCHAR(255),  -- Reference to additional metadata record
	
	-- Partitioning columns (for performance optimization)
	P_DATE DATE,  -- Partition date based on updated_at timestamp
	P_HOUR NUMBER(38,0)  -- Partition hour (0-23) based on updated_at timestamp
); 