将多模态业务数据整理成带来源引用的可检索记录

-- Adapted excerpt: the tutorial registers transcribe_audio in prepare.py.
WITH recordings AS (
    SELECT regexp_extract(f.url, '([^/]+)[.]wav$', 1) AS ticket_id,
           audio_file(file(f.url, NULL, NULL, NULL, NULL)) AS audio
    FROM list_files('./recordings/*.wav') AS f
), transcripts AS (
    SELECT ticket_id, audio.url AS source_uri,
           transcribe_audio(audio) AS segments_json
    FROM recordings
), segments AS (
    SELECT ticket_id, source_uri,
           unnest(from_json(segments_json,
               '[{"segment_id":"INTEGER","text":"VARCHAR","start_ms":"BIGINT","end_ms":"BIGINT"}]'
           )) AS segment
    FROM transcripts
)
SELECT ticket_id, segment.segment_id AS segment_id,
       segment.text AS text, source_uri,
       segment.start_ms AS start_ms, segment.end_ms AS end_ms,
       ai_embed(
           segment.text, provider := 'transformers',
           model := 'sentence-transformers/paraphrase-multilingual-MiniLM-L12-v2',
           dimensions := 384, on_error := 'raise',
           options := struct_pack(normalize := true, actor_number := 1, batch_size := 16)
       ) AS embedding
FROM segments
WHERE length(trim(segment.text)) > 0;