CREATE OR REPLACE PROCEDURE OWNED_CLIP_ANALYSIS_PROC(start_date DATE, end_date DATE)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    -- Delete existing data for the date range
    DELETE FROM SUNO_PROD.PROD.OWNED_CLIP_ANALYSIS 
    WHERE p_date >= :start_date AND p_date <= :end_date;
    
    -- Create temporary table for subscribers
    CREATE OR REPLACE TEMPORARY TABLE temp_subscribers AS
    SELECT DISTINCT user_id
    FROM suno_prod.prod.fact_subscription_period
    WHERE tier != 'Basic';
    
    -- Insert owned clips using their original user_tier
    INSERT INTO SUNO_PROD.PROD.OWNED_CLIP_ANALYSIS
    SELECT
        c.clip_id as backup_clip_id,
        'owned' as ownership_status,
        'subscribed' as ownership_reason,
        c.clip_id,
        c.user_id,
        c.user_tier as user_usage_plan,  -- Direct from clip
        c.created_at as clip_created_at,
        c.created_at as created_at,       
        c.created_at as updated_at,       
        c.p_date                          
    FROM suno_prod.prod.dim_clip c
    LEFT JOIN suno_prod.prod.rds_clip_parent cp
        ON c.clip_id = cp.clip_id
        AND (
            c.p_date = cp.p_date
            OR c.p_date = (cp.p_date::DATE - INTERVAL '1 day')::VARCHAR
        )
    INNER JOIN temp_subscribers ts ON ts.user_id = c.user_id
    WHERE
        c.p_date >= :start_date
        AND c.p_date <= :end_date
        AND (
            (c.user_tier IS NULL AND c.priority IS NULL)  -- Subscriber with no explicit tier
            OR c.user_tier IN (
                '30cc669c-1ee3-4e91-8801-8bc7570060e9',
                '3eaebef3-ef46-446a-931c-3d50cd1514f1',
                'e1235dd7-9f4d-4738-aeb2-1470466cba27',
                '60364dfb-34db-4736-97b5-2987eb5f5a41'
            )
            OR c.priority > 0
        )
        AND cp.non_owned_parent_clip_id IS NULL
        -- Exclude clips with invalid user_tier UUIDs (bot-generated)
        AND NOT (
            c.user_tier IS NOT NULL
            AND c.user_tier NOT IN (
                '30cc669c-1ee3-4e91-8801-8bc7570060e9',
                '3eaebef3-ef46-446a-931c-3d50cd1514f1',
                'e1235dd7-9f4d-4738-aeb2-1470466cba27',
                '60364dfb-34db-4736-97b5-2987eb5f5a41'
            )
        );
    
    -- Get row count for logging
    LET row_count INTEGER := (SELECT COUNT(*) FROM SUNO_PROD.PROD.OWNED_CLIP_ANALYSIS 
                              WHERE p_date >= :start_date AND p_date <= :end_date);
    
    -- Return success message with row count
    RETURN 'Successfully processed ' || :row_count || ' owned clips for date range ' || 
           :start_date || ' to ' || :end_date;
END;
$$;