-- =============================================
-- 查询名称: 每日评论触发优先级汇总（P0-P3 + 覆盖补漏）
-- 创建时间: 2026-03-18
-- 口径说明: 与 04_daily_trigger_userlist.sql 完全一致
-- =============================================
WITH params AS (
  SELECT
    toFloat64(0.15) AS target_cover_rate,
    toInt32(900) AS min_target_users,
    toInt32(1800) AS max_target_users
),
max_day AS (
  SELECT max(dt) AS dt
  FROM bigbangdatabase.dws_ssid_allinfo
),
active_raw AS (
  SELECT
    dt,
    user_id AS user_uuid,
    (interact_image_like_cnt + interact_work_like_cnt + interact_comment_like_cnt + interact_comment_cnt + interact_follow_cnt) AS active_interact_total,
    (creation_collection_publish_cnt + creation_oc_publish_cnt + creation_verse_publish_cnt) AS publish_cnt,
    if(active_app_launch_cnt > 0 OR active_duration_s > 0, 1, 0) AS is_active_on_dt
  FROM bigbangdatabase.dws_ssid_allinfo
  WHERE dt BETWEEN addDays((SELECT dt FROM max_day), -29) AND (SELECT dt FROM max_day)
    AND user_id != ''
),
active_ud AS (
  SELECT
    dt,
    user_uuid,
    sum(active_interact_total) AS active_interact_total,
    sum(publish_cnt) AS publish_cnt,
    max(is_active_on_dt) AS is_active_on_dt
  FROM active_raw
  GROUP BY dt, user_uuid
),
passive_ud AS (
  SELECT
    dt,
    dim_user_uuid AS user_uuid,
    (sum(today_likes) + sum(today_comments)) AS passive_interact_total
  FROM bigbangdatabase.dws_collection_allinfo
  WHERE dt BETWEEN addDays((SELECT dt FROM max_day), -29) AND (SELECT dt FROM max_day)
    AND dim_user_uuid != ''
  GROUP BY dt, dim_user_uuid
),
base_today AS (
  SELECT
    a.dt,
    a.user_uuid,
    a.publish_cnt AS publish_cnt_today,
    ifNull(p.passive_interact_total, 0) AS passive_interact_total_today
  FROM active_ud a
  LEFT JOIN passive_ud p
    ON a.dt = p.dt AND a.user_uuid = p.user_uuid
  WHERE a.dt = (SELECT dt FROM max_day)
    AND a.publish_cnt > 0
    AND ifNull(p.passive_interact_total, 0) = 0
),
recent7 AS (
  SELECT
    a.user_uuid,
    sumIf(a.publish_cnt, a.dt >= addDays((SELECT dt FROM max_day), -6)) AS recent7_publish_cnt,
    sumIf(a.active_interact_total, a.dt >= addDays((SELECT dt FROM max_day), -6)) AS recent7_active_interact_total,
    sumIf(a.is_active_on_dt, a.dt >= addDays((SELECT dt FROM max_day), -6)) AS recent7_active_days,
    sumIf(ifNull(p.passive_interact_total, 0), a.dt >= addDays((SELECT dt FROM max_day), -6)) AS recent7_passive_total
  FROM active_ud a
  LEFT JOIN passive_ud p
    ON a.dt = p.dt AND a.user_uuid = p.user_uuid
  GROUP BY a.user_uuid
),
scored AS (
  SELECT
    b.dt,
    b.user_uuid,
    b.publish_cnt_today,
    b.passive_interact_total_today,
    ifNull(r.recent7_publish_cnt, 0) AS recent7_publish_cnt,
    ifNull(r.recent7_passive_total, 0) AS recent7_passive_total,
    ifNull(r.recent7_active_interact_total, 0) AS recent7_active_interact_total,
    ifNull(r.recent7_active_days, 0) AS recent7_active_days,
    round(ifNull(r.recent7_passive_total / nullIf(r.recent7_publish_cnt, 0), 0), 4) AS recent7_passive_per_publish,
    greatest(0.0, 1 - least(ifNull(r.recent7_passive_total / nullIf(r.recent7_publish_cnt, 0), 0), 3) / 3) AS need_gap_score,
    (least(1.0, ifNull(r.recent7_publish_cnt, 0) / 12.0) * 0.5 + least(1.0, ifNull(r.recent7_active_interact_total, 0) / 80.0) * 0.5) AS creator_intent_score,
    least(1.0, ifNull(r.recent7_active_days, 0) / 7.0) AS recency_score,
    round(100 * (
      0.50 * greatest(0.0, 1 - least(ifNull(r.recent7_passive_total / nullIf(r.recent7_publish_cnt, 0), 0), 3) / 3)
      + 0.30 * (least(1.0, ifNull(r.recent7_publish_cnt, 0) / 12.0) * 0.5 + least(1.0, ifNull(r.recent7_active_interact_total, 0) / 80.0) * 0.5)
      + 0.20 * least(1.0, ifNull(r.recent7_active_days, 0) / 7.0)
    ), 2) AS priority_score,
    multiIf(
      ifNull(r.recent7_publish_cnt, 0) >= 8
        AND ifNull(r.recent7_passive_total, 0) = 0
        AND ifNull(r.recent7_active_interact_total, 0) >= 40
        AND ifNull(r.recent7_active_days, 0) >= 4, 'P0',
      ifNull(r.recent7_publish_cnt, 0) >= 4
        AND ifNull(r.recent7_passive_total, 0) <= 1
        AND ifNull(r.recent7_active_interact_total, 0) >= 15
        AND ifNull(r.recent7_active_days, 0) >= 3, 'P1',
      ifNull(r.recent7_publish_cnt, 0) >= 2
        AND ifNull(r.recent7_passive_total, 0) <= 1
        AND ifNull(r.recent7_active_interact_total, 0) >= 5
        AND ifNull(r.recent7_active_days, 0) >= 2, 'P2',
      ifNull(r.recent7_publish_cnt, 0) >= 1
        AND ifNull(r.recent7_passive_total, 0) <= 2
        AND ifNull(r.recent7_active_interact_total, 0) >= 5
        AND ifNull(r.recent7_active_days, 0) >= 2, 'P3',
      'P4'
    ) AS priority_tier
  FROM base_today b
  LEFT JOIN recent7 r
    ON b.user_uuid = r.user_uuid
),
stats AS (
  SELECT
    count() AS base_today_cnt,
    countIf(priority_tier IN ('P0', 'P1', 'P2', 'P3')) AS base_selected_cnt
  FROM scored
),
target AS (
  SELECT
    s.base_today_cnt,
    s.base_selected_cnt,
    toInt32(greatest(toFloat64(p.min_target_users), least(toFloat64(p.max_target_users), round(s.base_today_cnt * p.target_cover_rate)))) AS target_selected_cnt,
    greatest(0, toInt32(greatest(toFloat64(p.min_target_users), least(toFloat64(p.max_target_users), round(s.base_today_cnt * p.target_cover_rate)))) - s.base_selected_cnt) AS supplement_needed
  FROM stats s
  CROSS JOIN params p
),
ranked_supplement AS (
  SELECT
    sc.*,
    row_number() OVER (ORDER BY sc.priority_score DESC, sc.recent7_publish_cnt DESC, sc.recent7_active_interact_total DESC, sc.user_uuid ASC) AS supplement_rank
  FROM scored sc
  WHERE sc.priority_tier = 'P4'
),
selected AS (
  SELECT
    sc.dt AS run_dt,
    sc.priority_tier AS final_tier,
    sc.priority_score
  FROM scored sc
  WHERE sc.priority_tier IN ('P0', 'P1', 'P2', 'P3')

  UNION ALL

  SELECT
    rs.dt AS run_dt,
    'S1' AS final_tier,
    rs.priority_score
  FROM ranked_supplement rs
  CROSS JOIN target t
  WHERE rs.supplement_rank <= t.supplement_needed
)
SELECT
  sel.run_dt AS run_dt,
  sel.final_tier AS priority_tier,
  count() AS tier_user_cnt,
  round(avg(sel.priority_score), 2) AS avg_priority_score,
  t.base_today_cnt,
  t.base_selected_cnt,
  t.target_selected_cnt,
  t.supplement_needed,
  sum(count()) OVER () AS final_selected_cnt,
  round(sum(count()) OVER () / nullIf(t.base_today_cnt, 0) * 100, 2) AS final_coverage_rate_pct,
  round(count() / nullIf(t.base_today_cnt, 0) * 100, 2) AS tier_coverage_rate_pct
FROM selected sel
CROSS JOIN target t
GROUP BY
  sel.run_dt,
  sel.final_tier,
  t.base_today_cnt,
  t.base_selected_cnt,
  t.target_selected_cnt,
  t.supplement_needed
ORDER BY
  multiIf(sel.final_tier = 'P0', 0, sel.final_tier = 'P1', 1, sel.final_tier = 'P2', 2, sel.final_tier = 'P3', 3, sel.final_tier = 'S1', 4, 5);
