{{
    config(
        materialized='external',
        location="@" ~ var('external_s3_env') ~ ".SUNO_DATABASE_EVENTS/bots_generatedclip_insert",
        file_format=var('external_s3_env') ~ ".BASIC_PARQUET_LOADER",
        pattern='bots_generatedclip_insert/pdate=.*/phour=.*/.*.parquet'
    )
}}

-- External table reading from S3 stage for bots_generatedclip_insert parquet files
-- This replicates the source data that the DIM_CLIP_INSERT_PROC procedure reads from
-- The stage path format is: @SUNO_DATABASE_EVENTS/bots_generatedclip_insert/pdate=YYYY-MM-DD/phour=HH

SELECT
    -- Top-level fields from parquet (types inferred from parquet file)
    $1:id AS id,
    $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,
    $1:batch_index AS batch_index,
    $1:title AS title,
    $1:time_used AS time_used,
    $1:prompt_text AS prompt_text,
    $1:model_name AS model_name,
    
    -- Metadata JSON (parsed as VARIANT for later transformation)
    PARSE_JSON($1:metadata) AS metadata_json,
    
    -- Parse pdate and phour from file path using METADATA$FILENAME
    TRY_TO_DATE(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'pdate=', 2), '/', 1), 'YYYY-MM-DD') AS p_date,
    TRY_TO_NUMBER(SPLIT_PART(SPLIT_PART(METADATA$FILENAME, 'phour=', 2), '/', 1)) AS p_hour

FROM {{ source('s3', 'bots_generatedclip_insert') }}

