



ALTER TABLE categories ADD COLUMN video_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER sort_order;
ALTER TABLE categories ADD COLUMN total_views BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER video_count;


ALTER TABLE performers ADD COLUMN total_views BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER video_count;


ALTER TABLE studios ADD COLUMN total_views BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER video_count;


ALTER TABLE tags ADD COLUMN video_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER video_type;


UPDATE categories c SET
    video_count = COALESCE((
        SELECT COUNT(*) FROM video_categories vc
        JOIN videos v ON vc.video_id = v.id
        WHERE vc.category_id = c.id AND v.status = 'published'
    ), 0),
    total_views = COALESCE((
        SELECT SUM(v.views) FROM video_categories vc
        JOIN videos v ON vc.video_id = v.id
        WHERE vc.category_id = c.id AND v.status = 'published'
    ), 0);


UPDATE performers p SET
    video_count = COALESCE((
        SELECT COUNT(*) FROM video_performers vp
        JOIN videos v ON vp.video_id = v.id
        WHERE vp.performer_id = p.id AND v.status = 'published'
    ), 0),
    total_views = COALESCE((
        SELECT SUM(v.views) FROM video_performers vp
        JOIN videos v ON vp.video_id = v.id
        WHERE vp.performer_id = p.id AND v.status = 'published'
    ), 0);


UPDATE studios s SET
    video_count = COALESCE((
        SELECT COUNT(*) FROM video_studios vs
        JOIN videos v ON vs.video_id = v.id
        WHERE vs.studio_id = s.id AND v.status = 'published'
    ), 0),
    total_views = COALESCE((
        SELECT SUM(v.views) FROM video_studios vs
        JOIN videos v ON vs.video_id = v.id
        WHERE vs.studio_id = s.id AND v.status = 'published'
    ), 0);


UPDATE tags t SET
    video_count = COALESCE((
        SELECT COUNT(*) FROM video_tags vt
        JOIN videos v ON vt.video_id = v.id
        WHERE vt.tag_id = t.id AND v.status = 'published'
    ), 0);


DROP INDEX idx_slug ON videos;
DROP INDEX idx_status ON videos;
DROP INDEX idx_views ON videos;
DROP INDEX idx_likes ON videos;
DROP INDEX idx_published ON videos;
DROP INDEX idx_created ON videos;
