create or replace procedure SUNO_PROD.PROD.DIM_CLIP_LYRICS_UPSERT_PROC(
    p_date date
)
returns number
language sql
execute as caller
as
$$
declare
    rows_merged number;
begin
    merge into PROD.DIM_CLIP as t
    using (
        select
            id as CLIP_ID,
            prompt_text,
            prompt
        from PROD.DDB_CLIP_META_HEAVY
        where p_date = :p_date
    ) as s
    on t.CLIP_ID = s.CLIP_ID
    when matched then update set
        t.LYRICS_AT_GEN = s.prompt_text,
        t.LYRICS_FINAL   = s.prompt;

    rows_merged := SQLROWCOUNT;
    return rows_merged;
end;
$$;


