CREATE OR REPLACE PROCEDURE clip_rec_quality_score_daily_insert_proc(
    pdate date
)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
INSERT INTO clip_rec_quality_score_daily
WITH clips_base AS (
            -- Step 1: Get base clips data
            SELECT
                original_clip AS clip_id,
                id as hook_id
            FROM rds_video_hook
            where is_test = FALSE
                AND is_deleted = FALSE
        ),

        clip_data AS (
            -- Step 2: Get clip metadata and parse JSON
            SELECT
                c.id as clip_id,
                c.created_at,
                c.user_id,
                c.model_name,
                c.is_public,
                -- Parse metadata JSON fields
                COALESCE(TRY_PARSE_JSON(c.metadata):task::string, '') as task,
                COALESCE(TRY_PARSE_JSON(c.metadata):duration::float, 30) as song_length,
                COALESCE(TRY_PARSE_JSON(c.metadata):type::string, '') as type,
                COALESCE(TRY_PARSE_JSON(c.metadata):create_mode::string, '') as create_mode
            FROM clip c
            INNER JOIN clips_base cb ON c.id = cb.clip_id
        ),
        reaction_data AS (
            -- Step 3: Aggregate recent (last 7 days) reaction data
            SELECT
                r.clip_id,
                SUM(r.play_count) as plays_recent,
                SUM(CASE WHEN r.reaction_type = 'L' THEN 1 ELSE 0 END) as likes_recent,
                COUNT(DISTINCT r.user_id) as unique_listeners_recent
            FROM rds_user_reaction r
            INNER JOIN clips_base cb ON r.clip_id = cb.clip_id
            WHERE r.play_count > 1
                AND (r.updated_at >= CURRENT_DATE() - INTERVAL '7 days')
            GROUP BY r.clip_id
        ),

        session_data AS (
            -- Step 4: Get latest session summary data
            SELECT
                song_id as clip_id,
                sum_total_play_cnt_0 as total_plays_lifetime,
                sum_total_play_cnt_5 as quality_plays_lifetime,
                sum_total_play_duration_0 as total_duration_with_creator,
                sum_total_play_duration_5 as quality_duration_with_creator,
                sum_non_creator_listen_duration_0 as total_duration_without_creator,
                sum_non_creator_listen_duration_5 as quality_duration_without_creator,
                p_date
            FROM (
                SELECT *,
                    ROW_NUMBER() OVER (PARTITION BY song_id ORDER BY p_date DESC, p_hour DESC) as rn
                FROM ML_SONG_SUMMARY_INFO
                WHERE p_date >= DATEADD(hour, -3, CURRENT_TIMESTAMP())::date
                    AND p_hour >= HOUR(DATEADD(hour, -3, CURRENT_TIMESTAMP()))
                    AND song_id IN (SELECT clip_id FROM clips_base)
            )
            WHERE rn = 1
        ),

        merged_data AS (
            -- Step 5: Combine all data sources
            SELECT
                cd.*,
                COALESCE(rd.plays_recent, 0) as plays_recent,
                COALESCE(rd.likes_recent, 0) as likes_recent,
                COALESCE(rd.unique_listeners_recent, 0) as unique_listeners_recent,
                COALESCE(sd.total_plays_lifetime, 0) as total_plays_lifetime,
                COALESCE(sd.quality_plays_lifetime, 0) as quality_plays_lifetime,
                COALESCE(sd.total_duration_with_creator, 0) as total_duration_with_creator,
                COALESCE(sd.quality_duration_with_creator, 0) as quality_duration_with_creator,
                COALESCE(sd.total_duration_without_creator, 0) as total_duration_without_creator,
                COALESCE(sd.quality_duration_without_creator, 0) as quality_duration_without_creator,
                GREATEST(0, DATEDIFF('day', cd.created_at, COALESCE(sd.p_date, CURRENT_DATE()))) as days_since_creation
            FROM clip_data cd
            LEFT JOIN reaction_data rd ON cd.clip_id = rd.clip_id
            LEFT JOIN session_data sd ON cd.clip_id = sd.clip_id
        ),

        model_categories AS (
            -- Step 6: Categorize models and clip types
            SELECT *,
                -- Model categorization
                CASE
                    WHEN model_name IS NULL OR model_name = '' THEN 'no_model'
                    WHEN LOWER(model_name) LIKE '%v3p5%' OR LOWER(model_name) LIKE '%v3-5%' THEN 'v3.5'
                    WHEN LOWER(model_name) LIKE '%v4%' OR LOWER(model_name) LIKE '%chirp-up%' THEN 'v4'
                    WHEN LOWER(model_name) LIKE '%auk%' OR LOWER(model_name) LIKE '%ahi%' THEN 'v4.5'
                    WHEN LOWER(model_name) LIKE '%bluejay%' OR LOWER(model_name) LIKE '%bass%' THEN 'v4.5+'
                    WHEN LOWER(model_name) LIKE '%crow%' OR LOWER(model_name) LIKE '%carp%' THEN 'v5'
                    WHEN LOWER(model_name) LIKE '%stem%' THEN 'stem'
                    WHEN LOWER(model_name) LIKE '%v3%' OR LOWER(model_name) LIKE '%v2%' THEN 'older_model'
                    ELSE model_name
                END as model_category,

                -- Clip type and task categorization
                CASE
                    WHEN type = 'gen' AND (task IS NULL OR task = '') THEN 'normal_gen'
                    WHEN task IN ('cover') THEN 'cover_gen'
                    WHEN task IN ('extend', 'upload_extend') THEN 'extend_gen'
                    WHEN type = 'edit_speed' THEN 'remix_edit'
                    WHEN task = 'infill' THEN 'infill_gen'
                    WHEN task IN ('artist_consistency', 'artist_cover', 'artist_extend') THEN 'persona_related_gen'
                    WHEN task = 'gen_stem' THEN 'stem_gen'
                    WHEN type LIKE 'edit%' THEN 'edit'
                    WHEN task IN ('image_to_song', 'video_to_song') THEN 'iv2s_gen'
                    WHEN type = 'upsample' THEN 'upsample'
                    WHEN type LIKE 'concat%' THEN 'concat'
                    WHEN task IN ('overpainting', 'underpainting') THEN 'over_under_painting'
                    WHEN task = 'playlist_condition' THEN 'playlist_condition'
                    ELSE COALESCE(type, '') || COALESCE(task, '')
                END as clip_category
            FROM merged_data
        ),

        quality_multipliers AS (
            -- Step 7: Calculate quality multipliers
            SELECT *,
                -- Category weights
                CASE clip_category
                    WHEN 'edit' THEN 4
                    WHEN 'concat' THEN 4
                    WHEN 'persona_related_gen' THEN 3
                    WHEN 'cover_gen' THEN 3
                    WHEN 'upsample' THEN 2
                    WHEN 'extend_gen' THEN 2
                    WHEN 'remix_edit' THEN 2
                    WHEN 'upload' THEN 1
                    WHEN 'normal_gen' THEN 1
                    WHEN 'over_under_painting' THEN 1
                    WHEN 'playlist_condition' THEN 1
                    WHEN 'iv2s_gen' THEN 0
                    WHEN 'infill_gen' THEN 0
                    WHEN 'stem_gen' THEN 0
                    ELSE 0
                END as category_weight,

                -- Model weights
                CASE model_category
                    WHEN 'v5' THEN 4
                    WHEN 'v4.5' THEN 3
                    WHEN 'v4' THEN 2
                    WHEN 'older_model' THEN 1
                    WHEN 'no_model' THEN 3
                    ELSE 0
                END as model_weight,

                -- Custom weights
                CASE create_mode
                    WHEN 'custom' THEN 3
                    WHEN 'advanced' THEN 3
                    WHEN 'simple' THEN 1
                    ELSE 1
                END as custom_weight
            FROM model_categories
        ),

        calculated_scores AS (
            -- Step 8: Calculate all quality score components
            SELECT *,
                -- Quality multiplier calculation (weighted average: create_mode=50%, model_name=30%, clip_category=20%)
                (0.3 * (0.5 + (model_weight / 4.0) * 0.5) +
                0.2 * (0.5 + (category_weight / 4.0) * 0.5) +
                0.5 * (0.5 + (custom_weight / 3.0) * 0.5)) as quality_multiplier_calc,

                -- Lifetime Quality: Percentage of plays >5 seconds (0-100)
                CASE
                    WHEN total_plays_lifetime = 0 THEN 0
                    ELSE LEAST(100, (quality_plays_lifetime::float / total_plays_lifetime) * 100)
                END as lifetime_quality_score,

                -- Recent Momentum: Recent plays vs lifetime average (0-100)
                CASE
                    WHEN days_since_creation = 0 THEN 100
                    WHEN total_plays_lifetime = 0 THEN CASE WHEN plays_recent > 0 THEN 100 ELSE 0 END
                    -- Plays recent is the number of plays in the last 7 days
                    ELSE LEAST(100, 25 + (plays_recent::float / (total_plays_lifetime::float / (days_since_creation::float / 7))) * 25)
                END as recent_momentum_score,

                -- Like Rate: Recent likes per play (0-100)
                CASE
                    WHEN plays_recent = 0 THEN 0
                    WHEN plays_recent < 10 THEN
                        LEAST(100, (likes_recent::float / plays_recent) * 500) * (plays_recent::float / 10)
                    ELSE LEAST(100, (likes_recent::float / plays_recent) * 500)
                END as like_rate_score,

                -- Listener Diversity: Unique listeners ratio (0-100)
                CASE
                    WHEN plays_recent = 0 THEN 0
                    WHEN plays_recent < 5 THEN
                        LEAST(100, (unique_listeners_recent::float / plays_recent) * 100) * (plays_recent::float / 5)
                    ELSE LEAST(100, (unique_listeners_recent::float / plays_recent) * 100)
                END as listener_diversity_score,

                -- Volume Performance: Quality plays per day, log scale (0-100)
                CASE
                    WHEN days_since_creation = 0 THEN days_since_creation + 1
                    ELSE days_since_creation
                END as adj_days_since_creation
            FROM quality_multipliers
        ),

        final_scores AS (
            -- Step 9: Complete volume and retention calculations
            SELECT *,
                -- Volume Performance continued
                CASE
                    WHEN quality_plays_lifetime::float / adj_days_since_creation <= 1 THEN 0
                    ELSE LEAST(100, LOG(10, quality_plays_lifetime::float / adj_days_since_creation) * 50)
                END as volume_performance_score,

                -- Content Retention: How much of song people listen to (0-100)
                CASE
                    WHEN quality_plays_lifetime = 0 OR song_length = 0 THEN 0
                    ELSE LEAST(100, (quality_duration_without_creator::float / quality_plays_lifetime / song_length) * 100)
                END as content_retention_score
            FROM calculated_scores
        ),

        weighted_scores AS (
            -- Step 10: Apply component weights and calculate final scores
            SELECT *,
                -- Component weights: lifetime_quality=0.15, recent_momentum=0.25, like_rate=0.15,
                -- listener_diversity=0.15, volume_performance=0.20, content_retention=0.10
                ROUND(
                    lifetime_quality_score * 0.15 +
                    recent_momentum_score * 0.25 +
                    like_rate_score * 0.15 +
                    listener_diversity_score * 0.15 +
                    volume_performance_score * 0.20 +
                    content_retention_score * 0.10
                ) as overall_score_calc,

                -- Calculate detailed metrics for display
                CASE
                    WHEN total_plays_lifetime = 0 THEN 0
                    ELSE ROUND((quality_plays_lifetime::float / total_plays_lifetime) * 100, 1)
                END as lifetime_completion_rate,

                CASE
                    WHEN plays_recent = 0 THEN 0
                    ELSE ROUND((likes_recent::float / plays_recent) * 100, 1)
                END as recent_like_rate_display,

                CASE
                    WHEN quality_plays_lifetime = 0 OR song_length = 0 THEN 0
                    ELSE ROUND((quality_duration_without_creator::float / quality_plays_lifetime / song_length) * 100, 1)
                END as avg_retention_rate_display,

                ROUND(total_plays_lifetime::float / GREATEST(1, days_since_creation), 1) as plays_per_day_display,

                CASE
                    WHEN plays_recent = 0 THEN 0
                    ELSE ROUND((unique_listeners_recent::float / plays_recent) * 100, 1)
                END as recent_unique_ratio_display,

                ROUND(quality_multiplier_calc, 1) as quality_multiplier_display
            FROM final_scores
        )

        -- Final SELECT with all required columns
        SELECT
            clip_id,
            GREATEST(0, LEAST(100, overall_score_calc))::INTEGER as overall_score,
            GREATEST(0, LEAST(100, ROUND(quality_multiplier_calc * overall_score_calc)))::INTEGER as overall_score_with_multiplier,
            ROUND(lifetime_quality_score)::INTEGER as lifetime_quality,
            ROUND(recent_momentum_score)::INTEGER as recent_momentum,
            ROUND(like_rate_score)::INTEGER as like_rate,
            ROUND(listener_diversity_score)::INTEGER as listener_diversity,
            ROUND(volume_performance_score)::INTEGER as volume_performance,
            ROUND(content_retention_score)::INTEGER as content_retention,
            lifetime_completion_rate,
            recent_like_rate_display as recent_like_rate,
            avg_retention_rate_display as avg_retention_rate,
            plays_per_day_display as plays_per_day,
            recent_unique_ratio_display as recent_unique_ratio,
            quality_multiplier_display as quality_multiplier,
            total_plays_lifetime::INTEGER,
            quality_plays_lifetime::INTEGER,
            likes_recent::INTEGER,
            plays_recent::INTEGER,
            unique_listeners_recent::INTEGER,
            song_length,
            days_since_creation::INTEGER,
            create_mode,
            type,
            task,
            model_name,
            :pdate as p_date
        FROM weighted_scores
        ORDER BY overall_score DESC;

RETURN 'Quality scores inserted successfully for date: ' || :pdate;
END;
$$;
