create or replace procedure SUNO_PROD.PROD.DIM_CLIP_INSERT_PROC(
    stage_path string
)
returns number
language sql
execute as caller
as
$$
declare
    sql string;
begin   
    sql := 'INSERT INTO PROD.DIM_CLIP(
        CLIP_ID,
        P_DATE,
        P_HOUR,
        CREATED_AT,
        USER_ID,
        REQUEST_ID,
        S3_ID,
        STATUS,
        PLATFORM,
        DISPLAY_INDEX,
        INITIAL_TITLE,
        TIME_USED_SEC,
        TYPE,
        TASK,
        PRIORITY,
        DURATION_SEC,
        IS_REFUND_CREDITS,
        IS_BOT,
        CREDIT_COST,
        LYRICS_AT_GEN,
        SIMPLE_PROMPT,
        LYRICS_FINAL,
        IS_SIMPLE,
        IS_MAKE_INSTRUMENTAL,
        STYLE_TAGS,
        NEGATIVE_STYLE_TAGS,
        REQUEST_MODELS,
        MODEL_NAME,
        EXPERIMENT_INF_PARAM,
        EXPERIMENT_VERSION,
        HISTORY,
        CONCAT_HISTORY,
        ERROR_TYPE,
        ERROR_MESSAGE,
        UPLOAD_HAS_VOCAL,
        EDIT_SESSION_ID,
        EDITED_CLIP_ID,
        TASK_DETAILS,
        CREATION_SOURCE,
        AUGMENTED_TAGS,
        FREE_QUOTA_CATEGORY,
        LYRICS_LANGUAGE,
        SIMPLE_LYRICS_MODEL,
        CREATED_SESSION_ID,
        IS_AUDIO_WATERMARKED,
        USER_TIER,
        IS_DELETED,
        CREATE_MODE,
        COMBINED_FROM_CLIP_IDS
    )
    with a as (
        select
            *,
            PARSE_JSON($1:metadata) as data,
        from '|| :stage_path || ' (file_format => ''BASIC_PARQUET_LOADER'', pattern => (''.*.parquet''))
    )
    select
        $1:id as CLIP_ID,
        Date($1:created_at::TIMESTAMP) as P_DATE,
        HOUR($1:created_at::TIMESTAMP) as P_HOUR,
        $1:created_at as CREATED_AT,
        $1:user_id as USER_ID,
        $1:request_id as REQUEST_ID,
        $1:s3_id as S3_ID,
        $1:status as STATUS,
        IFF(data:source is null, ''web'', data:source) as PLATFORM,
        $1:batch_index as DISPLAY_INDEX,
        $1:title as INITIAL_TITLE,
        $1:time_used as TIME_USED_SEC,
        data:type as TYPE,
        data:task as TASK,
        data:priority as PRIORITY,
        data:duration as DURATION_SEC,
        data:refund_credits as IS_REFUND_CREDITS,
        data:is_bot as IS_BOT,
        data:credit_cost as CREDIT_COST,
        $1:prompt_text as LYRICS_AT_GEN,
        data:gpt_description_prompt as SIMPLE_PROMPT,
        data:prompt as LYRICS_FINAL,
        iff(data:gpt_description_prompt is not null and data:gpt_description_prompt != ''null'', true, false) as IS_SIMPLE,
        data:make_instrumental as IS_MAKE_INSTRUMENTAL,
        data:tags as STYLE_TAGS,
        data:negative_tags as NEGATIVE_STYLE_TAGS,
        data:experiment as REQUEST_MODELS,
        $1:model_name as MODEL_NAME,
        data:param_experiment as EXPERIMENT_INF_PARAM,
        data:experiment_version as EXPERIMENT_VERSION,
        data:history as HISTORY,
        data:concat_history as CONCAT_HISTORY,
        data:error_type as ERROR_TYPE,
        data:error_message as ERROR_MESSAGE,
        data:has_vocal as UPLOAD_HAS_VOCAL,
        data:edit_session_id as EDIT_SESSION_ID,
        data:edited_clip_id as EDITED_CLIP_ID,
        OBJECT_CONSTRUCT(
            ''stem'',
            data:stem_from_id,
            ''extend'',
            OBJECT_CONSTRUCT(
                ''continue_at'', data:continue_at,
                ''continued_from_prompt'', data:continued_from_prompt,
                ''continued_aligned_prompt'', data:continued_aligned_prompt
            ),
            ''infill'',
            OBJECT_CONSTRUCT(
                ''infill_context_start_s'', data:infill_context_start_s,
                ''infill_context_end_s'', data:infill_context_end_s,
                ''infill_start_s'', data:infill_start_s,
                ''infill_end_s'', data:infill_end_s,
                ''include_future_s'', data:include_future_s
            ),
            ''trim'',
            OBJECT_CONSTRUCT(
                ''crop_start_time'', data:crop_start_time,
                ''crop_end_time'', data:crop_end_time
            ),
            ''artist_consistency'',
            data:persona_id,
            ''covers'',
            data:cover_clip_id,
            ''video_upload'',
            OBJECT_CONSTRUCT(
                ''video_to_song_description'', data:video_to_song_description,
                ''video_to_song_video_upload_id'', data:video_to_song_video_upload_id
            )
        ) as task_details,
        data:creation_source as CREATION_SOURCE,
        data:augmented_tags as AUGMENTED_TAGS,
        data:free_quota_category as FREE_QUOTA_CATEGORY,
        data:lang as LYRICS_LANGUAGE,
        data:lyrics_model as SIMPLE_LYRICS_MODEL,
        data:created_session_id as CREATED_SESSION_ID,
        data:is_audio_watermarked as IS_AUDIO_WATERMARKED,
        data:user_tier as USER_TIER,
        false as IS_DELETED,
        data:create_mode as CREATE_MODE,
        data:combined_from_clip_ids as COMBINED_FROM_CLIP_IDS
    from a;
    ';
    
    execute immediate :sql;
    rows_inserted := SQLROWCOUNT;
    RETURN rows_inserted

    -- adjusted_timestamp := DATEADD(HOUR, -1, CURRENT_TIMESTAMP());
    -- p_date := DATE(adjusted_timestamp);
    -- p_hour := HOUR(adjusted_timestamp);

    -- MERGE INTO dim_clip AS t
    -- USING (
    --     select * from ddb_clip_meta_heavy 
    --     where p_date = :p_date and p_hour = :p_hour
    -- ) AS s
    -- ON t.clip_id = s.id        
    -- WHEN MATCHED THEN
    -- UPDATE SET
    --     t.LYRICS_AT_GEN = s.prompt_text,
    --     t.LYRICS_FINAL = s.prompt;
    
    -- rows_merged := SQLROWCOUNT;
    
    -- RETURN rows_inserted + rows_merged;
end;
$$;
