-- Hook Recommendation Quality Score Daily Table
-- Contains daily calculated quality scores for music clips/hooks
CREATE OR REPLACE TRANSIENT TABLE clip_rec_quality_score_daily (
    -- Primary identifier
    clip_id VARCHAR(255) NOT NULL COMMENT 'Unique identifier for the clip',
    
    -- Quality Scores (0-100 scale)
    overall_score INTEGER COMMENT 'Overall weighted quality score (0-100)',
    overall_score_with_multiplier INTEGER COMMENT 'Overall score adjusted by quality multiplier (0-100)',
    
    -- Component Scores (0-100 scale)
    lifetime_quality INTEGER COMMENT 'Percentage of plays >5 seconds over entire lifetime',
    recent_momentum INTEGER COMMENT 'Recent play activity relative to lifetime average',
    like_rate INTEGER COMMENT 'Recent likes per play ratio score',
    listener_diversity INTEGER COMMENT 'Unique listener reach score',
    volume_performance INTEGER COMMENT 'Popularity with age adjustment score',
    content_retention INTEGER COMMENT 'How much of the song people actually listen to',
    
    -- Detailed Metrics
    lifetime_completion_rate FLOAT COMMENT 'Lifetime completion rate as decimal (0.0-1.0)',
    recent_like_rate FLOAT COMMENT 'Recent like rate as decimal (0.0-1.0)',
    avg_retention_rate FLOAT COMMENT 'Average retention rate as decimal (0.0-1.0)',
    plays_per_day FLOAT COMMENT 'Average plays per day as numeric value',
    recent_unique_ratio FLOAT COMMENT 'Recent unique listener ratio as decimal (0.0-1.0)',
    quality_multiplier FLOAT COMMENT 'Quality multiplier value as decimal',
    
    -- Raw Metrics
    total_plays_lifetime INTEGER COMMENT 'Total number of plays over lifetime',
    quality_plays_lifetime INTEGER COMMENT 'Number of plays >5 seconds over lifetime',
    likes_recent INTEGER COMMENT 'Number of recent likes',
    plays_recent INTEGER COMMENT 'Number of recent plays',
    unique_listeners_recent INTEGER COMMENT 'Number of unique recent listeners',
    song_length FLOAT COMMENT 'Duration of the song in seconds',
    days_since_creation INTEGER COMMENT 'Number of days since clip creation',
    
    -- Clip Metadata
    create_mode VARCHAR(50) COMMENT 'Creation mode (custom, simple, advanced, etc.)',
    type VARCHAR(50) COMMENT 'Clip type (gen, edit, upsample, etc.)',
    task VARCHAR(100) COMMENT 'Specific task type (cover, extend, infill, etc.)',
    model_name VARCHAR(100) COMMENT 'AI model used for generation',
    p_date DATE COMMENT 'Processing date for partitioning'
)
COMMENT = 'Daily quality scores and metrics for music clips/hooks used in recommendations'; 