-- Update profile in consolidated actors table
-- SCHEMA CHANGE: profiles table dropped, data now in actors.profile_* columns
-- Parameters: $1=actor_id, $2=cid(bytea), $3=avatar_cid, $4=banner_cid, $5=display_name, $6=description,
--             $7=pinned_uri, $8=joined_sp_uri, $9=pronouns, $10=website, $11=created_at
-- NOTE: actor_id is provided by dispatcher after ensuring actor exists
-- Note: No JOIN to posts table - we already have the natural key from URI parsing
WITH pinned_lookup AS (
    SELECT tid_to_i64(SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 3)) as rkey
    FROM actors a
    WHERE $7::text IS NOT NULL
      AND a.did = SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 1)
      AND SPLIT_PART(SUBSTRING($7::text FROM 6), '/', 2) = 'app.bsky.feed.post'  -- Only parse post URIs
),
joined_sp_lookup AS (
    SELECT sp.id
    FROM actors a
    INNER JOIN starterpacks sp ON sp.actor_id = a.id
    WHERE $8::text IS NOT NULL
      AND a.did = SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 1)
      AND SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 2) = 'app.bsky.graph.starterpack'  -- Only parse starterpack URIs
      AND sp.rkey = tid_to_i64(SPLIT_PART(SUBSTRING($8::text FROM 6), '/', 3))
)
UPDATE actors SET
    profile_cid = $2::bytea,
    profile_created_at = $11,
    profile_avatar_cid = $3,
    profile_banner_cid = $4,
    profile_display_name = $5,
    profile_description = $6,
    profile_pinned_post_rkey = (SELECT rkey FROM pinned_lookup),
    profile_joined_sp_id = (SELECT id FROM joined_sp_lookup),
    profile_pronouns = $9,
    profile_website = $10
WHERE id = $1