Fix NormalizedName for Multi-Tenant Subdomain #8662

To help anyone else looking. I wanted to share how we fixed the normalized name for multi-tenant subdomain.

Please refund the ticket :)

using System.Text.RegularExpressions;
using Volo.Abp.DependencyInjection;
using Volo.Abp.MultiTenancy;

namespace MyProject.Blazor.Services
{
    [Dependency(ReplaceServices = true)]
    [ExposeServices(typeof(ITenantNormalizer))]
    public class CustomUpperInvariantTenantNormalizer : ITenantNormalizer, ITransientDependency
    {
        public virtual string? NormalizeName(string? name)
        {
            if (string.IsNullOrEmpty(name))
            {
                return name;
            }

            // Normalize, convert to lower case, replace spaces and special characters with hyphens
            string subdomain = name.Normalize()
                                    .ToLowerInvariant()
                                    .Replace(" ", "-")
                                    .Replace("_", "-");

            // Remove any characters that are not letters, numbers, or hyphens
            subdomain = Regex.Replace(subdomain, @"[^a-z0-9-]", "");

            // Ensure the subdomain does not start or end with a hyphen
            subdomain = subdomain.Trim('-');

            return subdomain;
        }
    }
}

-- Update NormalizedName field on Existing Tenants
DROP TABLE IF EXISTS #TenantsNormalizedNames;
CREATE TABLE #TenantsNormalizedNames
(
    Id UNIQUEIDENTIFIER NOT NULL,
    NormalizedName NVARCHAR(255) NULL
);

INSERT INTO #TenantsNormalizedNames
    (Id, NormalizedName)
SELECT Id,
    UPPER(
        TRIM('-' FROM 
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(
                                                REPLACE(
                                                    REPLACE(
                                                        REPLACE(
                                                            REPLACE(
                                                                NormalizedName, ' ', '-'
                                                            ), '_', '-'
                                                        ), '.', '-'
                                                    ), '!', ''
                                                ), '@', ''
                                            ), '#', ''
                                        ), '$', ''
                                    ), '%', ''
                                ), '&', ''
                            ), '*', ''
                        ), '(', ''
                    ), ')', ''
                ), '''', ''  -- Handling the single quote
            )
        )
    ) AS NormalizedName
FROM
    [dbo].[SaasTenants];


SELECT t.NormalizedName, s.NormalizedName
FROM
    [dbo].[SaasTenants] t
JOIN #TenantsNormalizedNames s
    ON t.Id = s.Id
WHERE
    t.NormalizedName <> s.NormalizedName;


UPDATE t
SET  t.NormalizedName = s.NormalizedName
FROM
    [dbo].[SaasTenants] t
JOIN #TenantsNormalizedNames s
    ON t.Id = s.Id
WHERE
    t.NormalizedName <> s.NormalizedName;    


SELECT Name, NormalizedName 
FROM 
    [dbo].[SaasTenants]