



ALTER TABLE videos ADD COLUMN impressions INT UNSIGNED NOT NULL DEFAULT 0 AFTER views;
ALTER TABLE videos ADD COLUMN ctr_score DOUBLE NOT NULL DEFAULT 0 AFTER impressions;


CREATE INDEX idx_status_ctr ON videos (status, ctr_score DESC);


INSERT INTO settings (setting_key, setting_value, autoload)
VALUES ('ctr_enabled', '1', 1) ON DUPLICATE KEY UPDATE setting_key = setting_key;


UPDATE videos SET ctr_score = (views / (views * 10 + 50))
    * EXP(-0.01 * GREATEST(DATEDIFF(NOW(), COALESCE(published_at, created_at)), 0))
WHERE status = 'published' AND views > 0;
