
SET @db = DATABASE();

SET @sql = IF (
    EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @db
          AND TABLE_NAME = 'shop_products'
          AND COLUMN_NAME = 'display_position'
    ),
    'SELECT 1',
    'ALTER TABLE shop_products ADD COLUMN display_position INT NOT NULL DEFAULT 0 AFTER is_visible'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF (
    EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = @db
          AND TABLE_NAME = 'shop_products'
          AND INDEX_NAME = 'idx_shop_products_display_order'
    ),
    'SELECT 1',
    'CREATE INDEX idx_shop_products_display_order ON shop_products (shop_id, display_position, id)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
