-- host_rank_metrics dashboard and operations queries

-- A) Stability histogram (0-9, 10-19, ... 100)
SELECT
  CONCAT(LPAD(FLOOR(stability/10)*10,2,'0'), '-', LPAD(FLOOR(stability/10)*10 + 9,2,'0')) AS bucket,
  COUNT(*) AS n
FROM host_rank_metrics
GROUP BY FLOOR(stability/10)
ORDER BY FLOOR(stability/10);

-- B) 30-day trend: volume + average stability/confidence
SELECT
  DATE(updated_at) AS day,
  COUNT(*) AS n,
  ROUND(AVG(stability),1) AS avg_stability,
  ROUND(AVG(confidence),3) AS avg_conf
FROM host_rank_metrics
WHERE updated_at >= NOW() - INTERVAL 30 DAY
GROUP BY DATE(updated_at)
ORDER BY day;

-- C) Day-over-day delta stability (hosts updated today vs yesterday)
SELECT
  t.host_id,
  t.profile,
  t.selector,
  ROUND(t.stability - y.stability, 1) AS delta_stability,
  ROUND(t.stability,1) AS stability_now,
  ROUND(y.stability,1) AS stability_yesterday,
  t.updated_at
FROM host_rank_metrics t
JOIN host_rank_metrics y
  ON y.host_id = t.host_id
 AND DATE(y.updated_at) = DATE(t.updated_at) - INTERVAL 1 DAY
WHERE DATE(t.updated_at) = CURDATE()
ORDER BY delta_stability DESC
LIMIT 50;

-- D) Profile quality overview
SELECT
  profile,
  COUNT(*) AS n,
  ROUND(AVG(stability),1) AS avg_stability,
  ROUND(AVG(avg_std),4) AS avg_std,
  ROUND(AVG(confidence),3) AS avg_conf
FROM host_rank_metrics
GROUP BY profile
ORDER BY n DESC;

-- Suggested indexes for dashboard speed
ALTER TABLE host_rank_metrics ADD INDEX idx_updated_at (updated_at);
ALTER TABLE host_rank_metrics ADD INDEX idx_profile (profile);
ALTER TABLE host_rank_metrics ADD INDEX idx_profile_updated (profile, updated_at);
