-- Query the API directly and flatten the nested JSON structure
WITH raw_data AS (
  SELECT * FROM read_json_auto('https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed?actor=did:plc:edglm4muiyzty2snc55ysuqx&limit=10')
),
unnested_feed AS (
  SELECT unnest(feed) as post_data FROM raw_data
)
SELECT 
  -- Post basics
  post_data.post.uri as post_uri,
  post_data.post.author.handle as author_handle,
  post_data.post.author.displayName as display_name,
  
  -- Post content
  post_data.post.record.text as post_text,
  post_data.post.record.createdAt as created_at,
  
  -- Engagement metrics
  post_data.post.replyCount as replies,
  post_data.post.repostCount as reposts,
  post_data.post.likeCount as likes,
  post_data.post.quoteCount as quotes,
  
  -- Embedded content (if available)
  CASE 
    WHEN post_data.post.embed IS NOT NULL 
    AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view' 
    THEN post_data.post.embed.external.uri
    ELSE NULL
  END as embedded_link,
  
  CASE 
    WHEN post_data.post.embed IS NOT NULL 
    AND post_data.post.embed['$type'] = 'app.bsky.embed.external#view' 
    THEN post_data.post.embed.external.title
    ELSE NULL
  END as embedded_title,
  
  -- Total engagement score
  (post_data.post.replyCount + 
   post_data.post.repostCount + 
   post_data.post.likeCount + 
   post_data.post.quoteCount) as total_engagement
FROM unnested_feed
ORDER BY created_at DESC;