
SET @schema_name = DATABASE();

SET @sql = IF(
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'network_users'
          AND COLUMN_NAME = 'sso_subject'
    ),
    'SELECT 1',
    'ALTER TABLE network_users ADD COLUMN sso_subject VARCHAR(190) NULL AFTER external_id'
);
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;

SET @sql = IF(
    EXISTS (
        SELECT 1
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'network_users'
          AND COLUMN_NAME = 'sso_provider_code'
    ),
    'SELECT 1',
    'ALTER TABLE network_users ADD COLUMN sso_provider_code VARCHAR(100) NULL AFTER sso_subject'
);
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;

SET @sql = IF(
    EXISTS (
        SELECT 1
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = @schema_name
          AND TABLE_NAME = 'network_users'
          AND INDEX_NAME = 'idx_network_users_sso_subject'
    ),
    'SELECT 1',
    'ALTER TABLE network_users ADD INDEX idx_network_users_sso_subject (network_id, sso_subject)'
);
PREPARE statement FROM @sql;
EXECUTE statement;
DEALLOCATE PREPARE statement;
