-- =============================================
-- 查询名称: 真实用户评论样本（近30天）
-- 创建时间: 2026-03-18
-- 口径说明:
--   - 仅根评论: parent_type='collection' AND level=1
--   - 仅可见评论: status='PUBLISHED' AND review_status='NORMAL'
--   - 排除疑似AI评论: llm_conversation_uuid IS NULL
-- =============================================
SELECT
  c.id,
  c.user_id AS commenter_user_id,
  c.root_entity_uuid AS collection_uuid,
  c.content,
  CHAR_LENGTH(TRIM(c.content)) AS content_len,
  c.like_count,
  c.ctime AS comment_time,
  co.user_id AS post_author_user_id,
  co.title AS post_title,
  LEFT(COALESCE(co.description, ''), 120) AS post_desc_120,
  co.picture_count,
  co.has_video,
  co.is_interactive,
  co.comment_count AS post_comment_count,
  co.like_count AS post_like_count,
  co.ctime AS post_time,
  TIMESTAMPDIFF(MINUTE, co.ctime, c.ctime) AS lag_minutes
FROM comment c
JOIN collection co
  ON co.uuid = c.root_entity_uuid
WHERE c.status = 'PUBLISHED'
  AND c.review_status = 'NORMAL'
  AND c.parent_type = 'collection'
  AND c.level = 1
  AND c.llm_conversation_uuid IS NULL
  AND c.ctime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND co.status = 'PUBLISHED'
  AND co.review_status = 'PASS'
  AND c.content IS NOT NULL
  AND TRIM(c.content) <> ''
ORDER BY c.ctime DESC
LIMIT 3000;
