package region

import (
	"context"
	"errors"
	"time"

	"github.com/samber/lo"
	"github.com/tidbcloud/pkgv2/db"
	"github.com/tidbcloud/tiinf/monitor/tilog"
	"go.opentelemetry.io/otel"
	"go.uber.org/zap"
	"gorm.io/gorm"

	globalcommonv1 "github.com/tidbcloud/tidb-management-service/api/spec/global/common/v1"
	"github.com/tidbcloud/tidb-management-service/nextgen-global-service/domain/entity"
	regionrepo "github.com/tidbcloud/tidb-management-service/nextgen-global-service/domain/repository/region"
	"github.com/tidbcloud/tidb-management-service/nextgen-global-service/infra/repoimpl/schema"
	"github.com/tidbcloud/tidb-management-service/utilities/bizerr"
)

var tracer = otel.Tracer("region_repo_impl")

func NewRegionRepo(provider db.Provider) regionrepo.Repo {
	return &regionRepoImpl{
		provider: provider,
	}
}

type regionRepoImpl struct {
	provider db.Provider
}

func (i *regionRepoImpl) SelectRegion(
	ctx context.Context,
	filter regionrepo.Filter, //nolint:gocritic // hugeParam: filter is a query struct
) (*entity.Region, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.SelectRegion")
	defer span.End()

	if filter.RegionId == 0 {
		return nil, bizerr.New(bizerr.ErrCodeParams, "region id is required")
	}

	rdb := i.provider.ReadDB(ctx)

	// list all regions by region name to get provider
	var schemaRegions []schema.Region
	rdb = rdb.Model(&schema.Region{}).
		InnerJoins("Provider").
		Preload("RegionTenants").
		Preload("RegionServicePlans").
		Preload("RegionOpsAuditLogSetting")
	rdb = rdb.
		Where("`regions`.`region_name` = ?", filter.RegionId.String())
	if filter.LogicalName != "" {
		rdb = rdb.Where("`regions`.`name` = ?", filter.LogicalName)
	}
	if filter.ServicePlan != globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		rdb = filterByServicePlan(rdb, filter.ServicePlan)
	}
	rdb = rdb.Where("`regions`.`status` = ?", globalcommonv1.Region_ACTIVE.String())
	if err := rdb.Order("`regions`.`priority` DESC").Order("`regions`.`created_at` DESC").Find(&schemaRegions).Error; err != nil {
		return nil, bizerr.WrapGormError(err, "select region by region id %s service plan %s",
			filter.RegionId.String(), filter.ServicePlan.String())
	}

	// check all same region name regions
	for i := range schemaRegions {
		if filter.CloudProvider != globalcommonv1.CloudProvider_NAME_UNSPECIFIED {
			if schemaRegions[i].Provider.Name != filter.CloudProvider.String() {
				tilog.Warn(ctx, "region is not in target cloud provider %s",
					zap.String("cloud_provider", filter.CloudProvider.String()),
					zap.String("name", schemaRegions[i].Name),
				)
				continue
			}
			if schemaRegions[i].Provider.Status != globalcommonv1.CloudProvider_ACTIVE.String() {
				tilog.Warn(ctx, "region's cloud provider is not active",
					zap.String("cloud_provider", schemaRegions[i].Provider.Name),
					zap.String("name", schemaRegions[i].Name),
				)
				continue
			}
		}
		if !regionVisibleToTenant(&schemaRegions[i], filter.ServicePlan, filter.TenantId) {
			tilog.Warn(ctx, "tenant is not allowed to access region",
				zap.Uint64("tenant_id", filter.TenantId),
				zap.String("name", schemaRegions[i].Name),
			)
			continue
		}
		regionEntity, err := convertRegionModelToEntity(&schemaRegions[i])
		if err != nil {
			return nil, err
		}
		return restrictRegionForTenant(regionEntity, filter.ServicePlan, filter.TenantId), nil
	}
	return nil, bizerr.New(bizerr.ErrCodeRecordNotFound, "region %s not found",
		filter.RegionId.String())
}

func (i *regionRepoImpl) Get(ctx context.Context, id uint64) (*entity.Region, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.Get")
	defer span.End()

	if id == 0 {
		return nil, bizerr.New(bizerr.ErrCodeParams, "region id is required")
	}

	rdb := i.provider.ReadDB(ctx)
	var schemaRegion *schema.Region
	if err := rdb.Model(&schema.Region{}).
		InnerJoins("Provider").
		Preload("RegionTenants").
		Preload("RegionServicePlans").
		Preload("RegionOpsAuditLogSetting").
		Where("`regions`.`id` = ?", id).
		First(&schemaRegion).
		Error; err != nil {
		return nil, bizerr.WrapGormError(err, "get region by id %d", id)
	}

	return convertRegionModelToEntity(schemaRegion)
}

func (i *regionRepoImpl) GetByLogicalName(ctx context.Context, logicalName string) (*entity.Region, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.GetByLogicalName")
	defer span.End()

	if logicalName == "" {
		return nil, bizerr.New(bizerr.ErrCodeParams, "logical name is required")
	}

	rdb := i.provider.ReadDB(ctx)
	var schemaRegion *schema.Region
	if err := rdb.Model(&schema.Region{}).
		InnerJoins("Provider").
		Preload("RegionTenants").
		Preload("RegionServicePlans").
		Preload("RegionOpsAuditLogSetting").
		Where("`regions`.`name` = ?", logicalName).
		First(&schemaRegion).
		Error; err != nil {
		return nil, bizerr.WrapGormError(err, "get region by logical name %s", logicalName)
	}

	return convertRegionModelToEntity(schemaRegion)
}

func (i *regionRepoImpl) ListRegions(
	ctx context.Context,
	filter regionrepo.Filter, //nolint:gocritic // hugeParam: filter is a query struct, pointer would hurt readability
) ([]*entity.Region, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.ListRegions")
	defer span.End()

	rdb := i.provider.ReadDB(ctx)

	var schemaRegions []*schema.Region
	rdb = rdb.Model(&schema.Region{}).
		InnerJoins("Provider").
		Preload("RegionTenants").
		Preload("RegionServicePlans").
		Preload("RegionOpsAuditLogSetting")

	if filter.ShowDeleted {
		rdb = rdb.Unscoped()
	}

	if filter.State != "" && filter.State != globalcommonv1.Region_STATE_UNSPECIFIED.String() {
		rdb = rdb.Where("`regions`.`status` = ?", filter.State)
	}

	if filter.ServicePlan != globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		rdb = filterByServicePlan(rdb, filter.ServicePlan)
	}

	if filter.CloudProvider != globalcommonv1.CloudProvider_NAME_UNSPECIFIED {
		rdb = rdb.Where("`Provider`.`name` = ?", filter.CloudProvider.String())
	}

	if len(filter.DBRegionIds) > 0 {
		rdb = rdb.Where("`regions`.`id` IN ?", filter.DBRegionIds)
	}

	if err := rdb.Find(&schemaRegions).Error; err != nil {
		return nil, bizerr.WrapGormError(err, "list regions")
	}

	out := make([]*entity.Region, 0, len(schemaRegions))
	for _, schemaRegion := range schemaRegions {
		// filter by tenant id if tenant id is not 0
		// if tenant id is 0, return all regions
		if filter.TenantId != 0 && !regionVisibleToTenant(schemaRegion, filter.ServicePlan, filter.TenantId) {
			continue
		}
		region, err := convertRegionModelToEntity(schemaRegion)
		if err != nil {
			return nil, err
		}
		region = restrictRegionForTenant(region, filter.ServicePlan, filter.TenantId)
		out = append(out, region)
	}

	return out, nil
}

func (i *regionRepoImpl) ListCloudProviders(
	ctx context.Context,
	filter regionrepo.Filter, //nolint:gocritic // hugeParam: filter is a query struct, pointer would hurt readability
) ([]*entity.CloudProvider, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.ListCloudProviders")
	defer span.End()

	rdb := i.provider.ReadDB(ctx)
	rdb = rdb.Model(&schema.Provider{})

	if filter.State != "" {
		rdb = rdb.Where("`providers`.`status` = ?", filter.State)
	}

	if filter.CloudProvider != globalcommonv1.CloudProvider_NAME_UNSPECIFIED {
		rdb = rdb.Where("`providers`.`name` = ?", filter.CloudProvider.String())
	}

	// When a service plan filter is provided, filter providers at the providers table level.
	// This way each supported (vendor, plan) combination is returned as a separate entry.
	// When no service plan filter is provided, dedup by name (keep the first/original registration per vendor).
	if filter.ServicePlan != globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		rdb = rdb.Where("`providers`.`service_plan` = ?", filter.ServicePlan.String())
	}

	var schemaCloudProviders []*schema.Provider
	if err := rdb.Order("`providers`.`id` ASC").Find(&schemaCloudProviders).Error; err != nil {
		return nil, bizerr.WrapGormError(err, "list cloud providers")
	}
	if filter.ServicePlan == globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		schemaCloudProviders = uniqCloudProvidersByName(schemaCloudProviders)
	}

	output := make([]*entity.CloudProvider, 0, len(schemaCloudProviders))
	for _, schemaCloudProvider := range schemaCloudProviders {
		cloudProvider, err := convertCloudProviderModelToEntity(schemaCloudProvider)
		if err != nil {
			return nil, err
		}
		regions, err := i.ListRegions(ctx, regionrepo.Filter{
			ServicePlan:   filter.ServicePlan,
			CloudProvider: cloudProvider.Id,
			TenantId:      filter.TenantId,
		})
		if err != nil {
			return nil, err
		}
		cloudProvider.Regions = lo.Map(regions, func(region *entity.Region, _ int) *globalcommonv1.Region {
			return region.Region
		})
		cloudProvider.ServicePlans = collectServicePlansFromRegions(regions, cloudProvider.ServicePlan)
		output = append(output, cloudProvider)
	}

	return output, nil
}

func (i *regionRepoImpl) GetCloudProviderById(
	ctx context.Context,
	cloudProviderId entity.CloudProviderName,
	servicePlan entity.ServicePlan,
) (*entity.CloudProvider, bizerr.BizError) {
	ctx, span := tracer.Start(ctx, "regionRepo.GetCloudProviderById")
	defer span.End()

	rdb := i.provider.ReadDB(ctx)
	var provider *schema.Provider

	// Map SERVICE_PLAN_UNSPECIFIED to Premium as fallback before filtering.
	lookupPlan := servicePlan
	if lookupPlan == globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		lookupPlan = globalcommonv1.ServicePlan_Premium
	}

	q := rdb.Model(&schema.Provider{}).Preload("Regions").Preload("Regions.RegionTenants").Preload("Regions.RegionServicePlans").
		Where("`providers`.`name` = ?", cloudProviderId.String()).
		Where("`providers`.`service_plan` = ?", lookupPlan.String()).
		Order("`providers`.`id` ASC").
		Take(&provider)
	if q.Error != nil {
		return nil, bizerr.WrapGormError(q.Error, "load cloud provider %s", cloudProviderId.String())
	}
	cloudProvider, err := convertCloudProviderModelToEntity(provider)
	if err != nil {
		return nil, err
	}

	regions, err := i.ListRegions(ctx, regionrepo.Filter{
		ServicePlan:   servicePlan,
		CloudProvider: cloudProvider.Id,
	})
	if err != nil {
		return nil, err
	}
	cloudProvider.Regions = lo.Map(regions, func(region *entity.Region, _ int) *globalcommonv1.Region {
		return region.Region
	})
	cloudProvider.ServicePlans = collectServicePlansFromRegions(regions, cloudProvider.ServicePlan)
	return cloudProvider, nil
}

func (i *regionRepoImpl) SaveCloudProvider(ctx context.Context, cloudProvider *entity.CloudProvider) bizerr.BizError {
	ctx, span := tracer.Start(ctx, "regionRepo.SaveCloudProvider")
	defer span.End()
	if cloudProvider == nil {
		return bizerr.New(bizerr.ErrCodeParams, "cloud provider is nil")
	}

	wdb := i.provider.WriteDB(ctx)
	provider, err := convertCloudProviderEntityToModel(cloudProvider)
	if err != nil {
		return err
	}
	if provider.ID == 0 {
		var existing schema.Provider
		q := wdb.Model(&schema.Provider{}).Where("`providers`.`name` = ?", provider.Name)
		if provider.ServicePlan != "" {
			q = q.Where("`providers`.`service_plan` = ?", provider.ServicePlan)
		}
		if err := q.Take(&existing).Error; err == nil {
			provider.ID = existing.ID
			provider.CreatedAt = existing.CreatedAt
		}
		if err := wdb.Save(&provider).Error; err != nil {
			return bizerr.WrapGormError(err, "create cloud provider %s", cloudProvider.Id.String())
		}
	} else {
		if err := wdb.Omit("created_at").Save(&provider).Error; err != nil {
			return bizerr.WrapGormError(err, "save cloud provider %s", cloudProvider.Id.String())
		}
	}
	cloudProvider.ProviderID = uint64(provider.ID)

	return nil
}

func uniqCloudProvidersByName(providers []*schema.Provider) []*schema.Provider {
	out := make([]*schema.Provider, 0, len(providers))
	seen := make(map[string]struct{}, len(providers))
	for _, provider := range providers {
		if _, ok := seen[provider.Name]; ok {
			continue
		}
		seen[provider.Name] = struct{}{}
		out = append(out, provider)
	}
	return out
}

func (i *regionRepoImpl) SaveRegion(ctx context.Context, region *entity.Region) bizerr.BizError {
	ctx, span := tracer.Start(ctx, "regionRepo.SaveRegion")
	defer span.End()
	if region == nil {
		return bizerr.New(bizerr.ErrCodeParams, "region is nil")
	}

	servicePlans := normalizeRegionServicePlans(region)
	if len(servicePlans) == 0 {
		return bizerr.New(bizerr.ErrCodeParams, "service plan is required")
	}
	if region.ServicePlan == globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		region.ServicePlan = servicePlans[0]
	}

	wdb := i.provider.WriteDB(ctx)
	var provider *schema.Provider
	if err := wdb.Model(&schema.Provider{}).
		Where("`providers`.`name` = ?", region.CloudProviderId.String()).
		Take(&provider).
		Error; err != nil {
		return bizerr.WrapGormError(err, "load cloud provider %s", region.CloudProviderId.String())
	}

	if region.GetLogicalName() == "" {
		region.LogicalName = region.Id.String() // use region id as logic name default
	}
	cloudRegion, err := convertCloudRegionEntityToModel(region)
	if err != nil {
		return err
	}

	var existingRegion *schema.Region
	if err := wdb.Model(&schema.Region{}).
		Where("`regions`.`name` = ? and `regions`.`region_name` = ?", region.GetLogicalName(), region.Id.String()).
		Take(&existingRegion).
		Error; err != nil {
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return bizerr.WrapGormError(err, "load region %s", region.Id.String())
		}
		cloudRegion.ID = uint(GenRegionModelId())
		cloudRegion.CreatedAt = time.Now()
	} else {
		cloudRegion.ID = existingRegion.ID
		cloudRegion.CreatedAt = existingRegion.CreatedAt
	}

	cloudRegion.ProviderId = uint64(provider.ID)
	if err := wdb.Save(&cloudRegion).Error; err != nil {
		return bizerr.WrapGormError(err, "save region %s", region.Id.String())
	}

	region.RegionID = uint64(cloudRegion.ID)
	region.ProviderID = uint64(provider.ID)
	region.RegionLogicalName = region.GetLogicalName()
	regionWhiteLists := normalizeRegionWhiteListsForSave(region, servicePlans)
	if err := i.saveRegionServicePlans(wdb, uint64(cloudRegion.ID), regionWhiteLists); err != nil {
		return err
	}
	regionServicePlanMap, err := i.loadRegionServicePlanMap(wdb, uint64(cloudRegion.ID))
	if err != nil {
		return err
	}
	regionServicePlanIds := pickRegionServicePlanIdsFromWhiteLists(regionServicePlanMap, regionWhiteLists)
	region.RegionWhiteLists = regionWhiteLists

	if err := i.syncRegionTenants(wdb, uint64(cloudRegion.ID), regionWhiteLists, regionServicePlanMap, regionServicePlanIds, region); err != nil {
		return err
	}

	return nil
}

func filterByServicePlan(tx *gorm.DB, servicePlan globalcommonv1.ServicePlan) *gorm.DB {
	return tx.Where(
		"EXISTS (SELECT 1 FROM `region_service_plans` WHERE `region_service_plans`.`region_id` = `regions`.`id` AND `region_service_plans`.`service_plan` = ? AND `region_service_plans`.`deleted_at` IS NULL)",
		servicePlan.String(),
	)
}

func (i *regionRepoImpl) saveRegionServicePlans(
	tx *gorm.DB,
	regionId uint64,
	regionWhiteLists []*globalcommonv1.RegionWhiteList,
) bizerr.BizError {
	if err := tx.Model(&schema.RegionServicePlan{}).
		Where("`region_id` = ?", regionId).
		Delete(&schema.RegionServicePlan{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove service plans of region %d", regionId)
	}

	regionServicePlans := lo.Map(regionWhiteLists, func(wl *globalcommonv1.RegionWhiteList, _ int) *schema.RegionServicePlan {
		return &schema.RegionServicePlan{
			RegionId:    regionId,
			ServicePlan: wl.GetServicePlan().String(),
			IsPublic:    lo.ToPtr(wl.GetIsPublic()),
		}
	})
	if len(regionServicePlans) == 0 {
		return nil
	}
	if err := tx.Save(regionServicePlans).Error; err != nil {
		return bizerr.WrapGormError(err, "save service plans of region %d", regionId)
	}

	return nil
}

func (i *regionRepoImpl) loadRegionServicePlanMap(tx *gorm.DB, regionId uint64) (map[globalcommonv1.ServicePlan]uint64, bizerr.BizError) {
	var regionServicePlans []*schema.RegionServicePlan
	if err := tx.Model(&schema.RegionServicePlan{}).
		Where("`region_id` = ?", regionId).
		Find(&regionServicePlans).Error; err != nil {
		return nil, bizerr.WrapGormError(err, "load service plans of region %d", regionId)
	}
	out := make(map[globalcommonv1.ServicePlan]uint64, len(regionServicePlans))
	for _, regionServicePlan := range regionServicePlans {
		plan := globalcommonv1.ServicePlan(globalcommonv1.ServicePlan_value[regionServicePlan.ServicePlan])
		if plan == globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
			continue
		}
		out[plan] = uint64(regionServicePlan.ID)
	}
	return out, nil
}

func pickRegionServicePlanIdsFromWhiteLists(
	regionServicePlanMap map[globalcommonv1.ServicePlan]uint64,
	regionWhiteLists []*globalcommonv1.RegionWhiteList,
) []uint64 {
	out := make([]uint64, 0, len(regionWhiteLists))
	for _, wl := range regionWhiteLists {
		if regionServicePlanId, ok := regionServicePlanMap[wl.GetServicePlan()]; ok {
			out = append(out, regionServicePlanId)
		}
	}
	return lo.Uniq(out)
}

func normalizeRegionWhiteListsForSave(
	region *entity.Region,
	servicePlans []globalcommonv1.ServicePlan,
) []*globalcommonv1.RegionWhiteList {
	if len(servicePlans) == 0 {
		return nil
	}
	if len(region.GetRegionWhiteLists()) > 0 {
		out := make([]*globalcommonv1.RegionWhiteList, 0, len(servicePlans))
		for _, plan := range servicePlans {
			for _, wl := range region.GetRegionWhiteLists() {
				if wl.GetServicePlan() != plan {
					continue
				}
				out = append(out, &globalcommonv1.RegionWhiteList{
					ServicePlan:     plan,
					IsPublic:        wl.GetIsPublic(),
					OpenedTenantIds: lo.Uniq(append([]uint64(nil), wl.GetOpenedTenantIds()...)),
				})
				break
			}
		}
		if len(out) > 0 {
			return out
		}
	}
	out := make([]*globalcommonv1.RegionWhiteList, 0, len(servicePlans))
	for _, plan := range servicePlans {
		out = append(out, &globalcommonv1.RegionWhiteList{
			ServicePlan:     plan,
			IsPublic:        region.GetIsPublic(),
			OpenedTenantIds: lo.Uniq(append([]uint64(nil), region.GetOpenedTenantIds()...)),
		})
	}
	return out
}

func (i *regionRepoImpl) syncRegionTenants(
	tx *gorm.DB,
	regionId uint64,
	regionWhiteLists []*globalcommonv1.RegionWhiteList,
	regionServicePlanMap map[globalcommonv1.ServicePlan]uint64,
	regionServicePlanIds []uint64,
	region *entity.Region,
) bizerr.BizError {
	if len(regionServicePlanIds) == 0 {
		return nil
	}
	if err := tx.Model(&schema.RegionTenant{}).
		Where("`region_id` = ? AND `region_service_plan_id` IN ?", regionId, regionServicePlanIds).
		Delete(&schema.RegionTenant{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove tenants of region %s(%s)", region.GetLogicalName(), region.Id.String())
	}

	tenantRegions := make([]*schema.RegionTenant, 0)
	for _, wl := range regionWhiteLists {
		if wl.GetIsPublic() {
			continue
		}
		regionServicePlanId, ok := regionServicePlanMap[wl.GetServicePlan()]
		if !ok {
			continue
		}
		for _, tenantId := range wl.GetOpenedTenantIds() {
			tenantRegions = append(tenantRegions, &schema.RegionTenant{
				RegionId:            regionId,
				RegionServicePlanId: regionServicePlanId,
				TenantId:            tenantId,
			})
		}
	}
	if len(tenantRegions) == 0 {
		return nil
	}
	if err := tx.Create(tenantRegions).Error; err != nil {
		return bizerr.WrapGormError(err, "save region %s(%s) tenants", region.GetLogicalName(), region.Id.String())
	}
	return nil
}

func collectServicePlansFromRegions(regions []*entity.Region, _ globalcommonv1.ServicePlan) []globalcommonv1.ServicePlan {
	out := make([]globalcommonv1.ServicePlan, 0, len(regions))
	for _, region := range regions {
		for _, wl := range region.GetRegionWhiteLists() {
			plan := wl.GetServicePlan()
			if plan == globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
				continue
			}
			out = append(out, plan)
		}
	}
	return lo.Uniq(out)
}

func regionVisibleToTenant(region *schema.Region, plan globalcommonv1.ServicePlan, tenantId uint64) bool {
	if plan != globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED {
		for _, regionServicePlan := range region.RegionServicePlans {
			if regionServicePlan.ServicePlan != plan.String() {
				continue
			}
			if regionServicePlan.IsPublic == nil || *regionServicePlan.IsPublic {
				return true
			}
			_, ok := lo.Find(region.RegionTenants, func(t *schema.RegionTenant) bool {
				return t.TenantId == tenantId && t.RegionServicePlanId == uint64(regionServicePlan.ID)
			})
			return ok
		}
		return false
	}

	for _, regionServicePlan := range region.RegionServicePlans {
		if regionServicePlan.IsPublic == nil || *regionServicePlan.IsPublic {
			return true
		}
		_, ok := lo.Find(region.RegionTenants, func(t *schema.RegionTenant) bool {
			return t.TenantId == tenantId && t.RegionServicePlanId == uint64(regionServicePlan.ID)
		})
		if ok {
			return true
		}
	}

	return false
}

func restrictRegionForTenant(region *entity.Region, plan globalcommonv1.ServicePlan, tenantId uint64) *entity.Region {
	if region == nil || tenantId == 0 || len(region.GetRegionWhiteLists()) == 0 {
		return region
	}

	allowedWhiteLists := make([]*globalcommonv1.RegionWhiteList, 0, len(region.GetRegionWhiteLists()))
	for _, wl := range region.GetRegionWhiteLists() {
		if plan != globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED && wl.GetServicePlan() != plan {
			continue
		}
		if wl.GetIsPublic() {
			allowedWhiteLists = append(allowedWhiteLists, &globalcommonv1.RegionWhiteList{
				ServicePlan:     wl.GetServicePlan(),
				IsPublic:        true,
				OpenedTenantIds: []uint64{},
			})
			continue
		}
		if lo.Contains(wl.GetOpenedTenantIds(), tenantId) {
			allowedWhiteLists = append(allowedWhiteLists, &globalcommonv1.RegionWhiteList{
				ServicePlan:     wl.GetServicePlan(),
				IsPublic:        false,
				OpenedTenantIds: []uint64{tenantId},
			})
		}
	}

	region.RegionWhiteLists = allowedWhiteLists
	if len(allowedWhiteLists) == 0 {
		region.ServicePlan = globalcommonv1.ServicePlan_SERVICE_PLAN_UNSPECIFIED
		region.IsPublic = false
		region.OpenedTenantIds = nil
		return region
	}

	if !lo.ContainsBy(allowedWhiteLists, func(wl *globalcommonv1.RegionWhiteList) bool {
		return wl.GetServicePlan() == region.GetServicePlan()
	}) {
		region.ServicePlan = allowedWhiteLists[0].GetServicePlan()
	}

	selectedWhiteList := allowedWhiteLists[0]
	for _, wl := range allowedWhiteLists {
		if wl.GetServicePlan() == region.GetServicePlan() {
			selectedWhiteList = wl
			break
		}
	}
	region.IsPublic = selectedWhiteList.GetIsPublic()
	region.OpenedTenantIds = lo.Uniq(append([]uint64(nil), selectedWhiteList.GetOpenedTenantIds()...))

	return region
}

func (i *regionRepoImpl) DeleteRegionByLogicalName(ctx context.Context, logicalName string) bizerr.BizError {
	ctx, span := tracer.Start(ctx, "regionRepo.DeleteRegionByLogicalName")
	defer span.End()
	// get region by logical name
	rdb := i.provider.ReadDB(ctx)
	var cloudRegion schema.Region
	if err := rdb.Model(&schema.Region{}).InnerJoins("Provider").
		Where("`regions`.`name` = ?", logicalName).
		First(&cloudRegion).Error; err != nil {
		return bizerr.WrapGormError(err, "load region by logical name %s", logicalName)
	}

	// delete region
	wdb := i.provider.WriteDB(ctx)
	if err := wdb.Model(&schema.RegionTenant{}).
		Where("`region_id` = ?", cloudRegion.ID).
		Delete(&schema.RegionTenant{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove all tenants of region %s", logicalName)
	}

	if err := wdb.Model(&schema.RegionOpsAuditLogSetting{}).
		Where("`region_id` = ?", cloudRegion.ID).
		Delete(&schema.RegionOpsAuditLogSetting{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove ops audit log setting of region %s", logicalName)
	}

	if err := wdb.Model(&schema.ProviderIdentity{}).
		Where("`region_id` = ?", cloudRegion.ID).
		Delete(&schema.ProviderIdentity{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove provider identities of region %s", logicalName)
	}

	if err := wdb.Model(&schema.RegionServicePlan{}).
		Where("`region_id` = ?", cloudRegion.ID).
		Delete(&schema.RegionServicePlan{}).Error; err != nil {
		return bizerr.WrapGormError(err, "remove service plans of region %s", logicalName)
	}

	if err := wdb.Delete(&cloudRegion).Error; err != nil {
		return bizerr.WrapGormError(err, "delete region %s", logicalName)
	}

	return nil
}