







ALTER TABLE videos ADD COLUMN performer_name VARCHAR(100) DEFAULT NULL AFTER video_type;
ALTER TABLE videos ADD COLUMN favorites_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER dislikes;


UPDATE videos v
LEFT JOIN (
    SELECT vp.video_id, MIN(p.name) as pname
    FROM video_performers vp
    JOIN performers p ON vp.performer_id = p.id
    GROUP BY vp.video_id
) pn ON pn.video_id = v.id
SET v.performer_name = pn.pname;


UPDATE videos v
LEFT JOIN (
    SELECT video_id, COUNT(*) as cnt
    FROM user_favorites
    GROUP BY video_id
) fc ON fc.video_id = v.id
SET v.favorites_count = COALESCE(fc.cnt, 0);





ALTER TABLE categories ADD COLUMN avg_ctr_score DOUBLE NOT NULL DEFAULT 0 AFTER total_views;
ALTER TABLE performers ADD COLUMN avg_ctr_score DOUBLE NOT NULL DEFAULT 0 AFTER total_views;
ALTER TABLE studios ADD COLUMN avg_ctr_score DOUBLE NOT NULL DEFAULT 0 AFTER total_views;


UPDATE categories c
LEFT JOIN (
    SELECT vc.category_id, AVG(v.ctr_score) as avg_ctr
    FROM video_categories vc
    JOIN videos v ON vc.video_id = v.id AND v.status = 'published'
    GROUP BY vc.category_id
) agg ON c.id = agg.category_id
SET c.avg_ctr_score = COALESCE(agg.avg_ctr, 0);


UPDATE performers p
LEFT JOIN (
    SELECT vp.performer_id, AVG(v.ctr_score) as avg_ctr
    FROM video_performers vp
    JOIN videos v ON vp.video_id = v.id AND v.status = 'published'
    GROUP BY vp.performer_id
) agg ON p.id = agg.performer_id
SET p.avg_ctr_score = COALESCE(agg.avg_ctr, 0);


UPDATE studios s
LEFT JOIN (
    SELECT vs.studio_id, AVG(v.ctr_score) as avg_ctr
    FROM video_studios vs
    JOIN videos v ON vs.video_id = v.id AND v.status = 'published'
    GROUP BY vs.studio_id
) agg ON s.id = agg.studio_id
SET s.avg_ctr_score = COALESCE(agg.avg_ctr, 0);





ALTER TABLE videos ADD INDEX idx_type_status_published (video_type, status, published_at DESC);
ALTER TABLE videos ADD INDEX idx_type_status_views (video_type, status, views DESC);
ALTER TABLE videos ADD INDEX idx_type_status_ctr (video_type, status, ctr_score DESC);
ALTER TABLE videos ADD INDEX idx_type_status_likes (video_type, status, likes DESC);
ALTER TABLE videos ADD INDEX idx_type_status_favorites (video_type, status, favorites_count DESC);






ALTER TABLE users DROP INDEX idx_username_unique;
ALTER TABLE users DROP INDEX idx_email_unique;


ALTER TABLE pages DROP INDEX idx_slug;


ALTER TABLE rate_limits DROP INDEX idx_window_start;


ALTER TABLE translation_overrides DROP INDEX idx_lang_key_value;


ALTER TABLE comments DROP INDEX idx_video;
ALTER TABLE comments DROP INDEX idx_video_status;




ALTER TABLE reports ADD INDEX idx_status_created (status, created_at DESC);
ALTER TABLE contact_messages ADD INDEX idx_read_created (is_read, created_at DESC);





ALTER TABLE user_history
    DROP PRIMARY KEY,
    DROP COLUMN id,
    ADD PRIMARY KEY (user_id, video_id);
