











DELIMITER //

DROP PROCEDURE IF EXISTS AddIndexIfNotExists //

CREATE PROCEDURE AddIndexIfNotExists(
    IN p_table VARCHAR(64),
    IN p_index VARCHAR(64),
    IN p_columns VARCHAR(255)
)
BEGIN
    DECLARE index_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO index_exists
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
    LIMIT 1;

    IF index_exists = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table, '` ADD INDEX `', p_index, '` (', p_columns, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END //

DELIMITER ;








CALL AddIndexIfNotExists('video_categories', 'idx_category_video', 'category_id, video_id');
CALL AddIndexIfNotExists('video_tags',       'idx_tag_video',      'tag_id, video_id');
CALL AddIndexIfNotExists('video_performers', 'idx_performer_video', 'performer_id, video_id');
CALL AddIndexIfNotExists('video_studios',    'idx_studio_video',   'studio_id, video_id');










CALL AddIndexIfNotExists('videos', 'idx_status_published', 'status, published_at DESC');


CALL AddIndexIfNotExists('videos', 'idx_status_views', 'status, views DESC');


CALL AddIndexIfNotExists('videos', 'idx_status_likes', 'status, likes DESC');


CALL AddIndexIfNotExists('videos', 'idx_status_created', 'status, created_at DESC');


CALL AddIndexIfNotExists('videos', 'idx_status_featured', 'status, is_featured');










CALL AddIndexIfNotExists('comments', 'idx_video_status_parent', 'video_id, status, parent_id');


CALL AddIndexIfNotExists('comments', 'idx_status_created', 'status, created_at DESC');








CALL AddIndexIfNotExists('tags',       'idx_name', 'name');
CALL AddIndexIfNotExists('performers', 'idx_name', 'name');













CALL AddIndexIfNotExists('user_favorites', 'idx_user_created', 'user_id, created_at DESC');






DROP PROCEDURE IF EXISTS AddIndexIfNotExists;
