package migration

import (
	"errors"
	"fmt"
	"time"

	"github.com/samber/lo"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	globaldedicatedv1 "github.com/tidbcloud/tidb-management-service/api/spec/global/dedicated/v1"
	"github.com/tidbcloud/tidb-management-service/dedicated-global-service/infra/repoimpl/schema"
)

var _ = AddDBMigrateRecord(
	"release_20250923",
	func(db *gorm.DB) error {
		// Add enum values.
		if err := db.Exec(
			"alter table `storage_templates` modify COLUMN `volume_type` enum('gp3','io2','pd-ssd','local-storage','gp2','premiumv2','ultrassd','hyperdisk-balanced','hyperdisk-extreme') DEFAULT NULL;",
		).Error; err != nil {
			return err
		}
		if err := db.Exec(
			"alter table `cluster_component_storages` modify COLUMN `volume_type` enum('gp3','io2','pd-ssd','local-storage','gp2','premiumv2','ultrassd','hyperdisk-balanced','hyperdisk-extreme') DEFAULT NULL;",
		).Error; err != nil {
			return err
		}

		// [The Full SPU Set When PROD Use](https://pingcap.feishu.cn/wiki/GKsVwPduOikbTUkozYAc7WEFnsc#share-ObYpdo611oh6jLxqh1Fcpofyn1c).
		return db.Transaction(func(tx *gorm.DB) error {
			timestamp := time.Now()

			// Fetch the GCP provider.
			var provider *schema.Provider
			if err := tx.Where("name = ?", "gcp").First(&provider).Error; err != nil {
				return err
			}

			if provider == nil || provider.ID == 0 {
				return errors.New("invalid GCP provider data")
			}

			// Add instance types.
			instanceTypes := []*schema.InstanceType{
				{
					InstanceType: "n4-standard-2",
					ProviderId:   uint64(provider.ID),
					CPUNum:       2,
					MemorySizeGi: 8,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-2",
					ProviderId:   uint64(provider.ID),
					CPUNum:       2,
					MemorySizeGi: 8,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-standard-4",
					ProviderId:   uint64(provider.ID),
					CPUNum:       4,
					MemorySizeGi: 16,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-4",
					ProviderId:   uint64(provider.ID),
					CPUNum:       4,
					MemorySizeGi: 16,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-standard-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 32,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 32,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-standard-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-standard-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-standard-64",
					ProviderId:   uint64(provider.ID),
					CPUNum:       64,
					MemorySizeGi: 256,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-standard-64",
					ProviderId:   uint64(provider.ID),
					CPUNum:       64,
					MemorySizeGi: 256,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highcpu-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 16,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highcpu-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 16,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highcpu-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 32,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highcpu-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 32,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highcpu-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highcpu-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highcpu-64",
					ProviderId:   uint64(provider.ID),
					CPUNum:       64,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highcpu-64",
					ProviderId:   uint64(provider.ID),
					CPUNum:       64,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highmem-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highmem-8",
					ProviderId:   uint64(provider.ID),
					CPUNum:       8,
					MemorySizeGi: 64,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highmem-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highmem-16",
					ProviderId:   uint64(provider.ID),
					CPUNum:       16,
					MemorySizeGi: 128,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "n4-highmem-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 256,
					CpuArch:      "amd64",
				},
				{
					InstanceType: "c4a-highmem-32",
					ProviderId:   uint64(provider.ID),
					CPUNum:       32,
					MemorySizeGi: 256,
					CpuArch:      "amd64",
				},
			}
			for _, instanceType := range instanceTypes {
				instanceType.CreatedAt = timestamp
				instanceType.UpdatedAt = timestamp
				if err := tx.Clauses(clause.OnConflict{
					Columns:   []clause.Column{{Name: "instance_type"}},
					UpdateAll: true,
				}).Create(instanceType).Error; err != nil {
					return fmt.Errorf("failed to create instance type %s: %w", instanceType.InstanceType, err)
				}
			}

			// Verify all instance types were created and have valid IDs
			for _, instanceType := range instanceTypes {
				if instanceType.ID == 0 {
					return fmt.Errorf("instance type %s was not assigned a valid ID", instanceType.InstanceType)
				}
			}

			instanceTypeMap := lo.SliceToMap(
				instanceTypes,
				func(i *schema.InstanceType) (string, *schema.InstanceType) {
					return i.InstanceType, i
				},
			)

			// Add Hyperdisk Balanced volumes
			var hyperdiskBalancedStorage *schema.StorageTemplate
			{
				hyperdiskBalancedStorage = &schema.StorageTemplate{
					VolumeType: "hyperdisk-balanced",
					ProviderId: uint64(provider.ID),
				}

				hyperdiskBalancedStorage.CreatedAt = timestamp
				hyperdiskBalancedStorage.UpdatedAt = timestamp
				if err := tx.Clauses(clause.OnConflict{
					Columns:   []clause.Column{{Name: "volume_type"}, {Name: "provider_id"}},
					UpdateAll: true,
				}).Create(hyperdiskBalancedStorage).Error; err != nil {
					return fmt.Errorf("failed to create hyperdisk-balanced storage template: %w", err)
				}

				// Verify storage template was created successfully
				if hyperdiskBalancedStorage.ID == 0 {
					return errors.New("hyperdisk-balanced storage template was not assigned a valid ID")
				}
			}

			var (
				// pd tikv skus need extra mappings.
				pdSku2c8g, tikvSku4c16g                                           *schema.ComponentTierUnit
				pdSku4c16g, tikvSku8c32g, tikvSku8c64g, tikvSku16c64g             *schema.ComponentTierUnit
				pdSku16c32g, tikvSku32c128g                                       *schema.ComponentTierUnit
				pdSku2c8gC4a, tikvSku4c16gC4a                                     *schema.ComponentTierUnit
				pdSku4c16gC4a, tikvSku8c32gC4a, tikvSku8c64gC4a, tikvSku16c64gC4a *schema.ComponentTierUnit
				pdSku16c32gC4a, tikvSku32c128gC4a                                 *schema.ComponentTierUnit

				// other skus.
				tidbSku4c16gN4, tidbSku4c16gC4a           *schema.ComponentTierUnit
				tidbSku8c16gN4, tidbSku8c16gC4a           *schema.ComponentTierUnit
				tidbSku8c32gN4, tidbSku8c32gC4a           *schema.ComponentTierUnit
				tidbSku8c64gN4, tidbSku8c64gC4a           *schema.ComponentTierUnit
				tidbSku16c32gN4, tidbSku16c32gC4a         *schema.ComponentTierUnit
				tidbSku16c64gN4, tidbSku16c64gC4a         *schema.ComponentTierUnit
				tidbSku16c128gN4, tidbSku16c128gC4a       *schema.ComponentTierUnit
				tidbSku32c64gN4, tidbSku32c64gC4a         *schema.ComponentTierUnit
				tidbSku32c128gN4, tidbSku32c128gC4a       *schema.ComponentTierUnit
				tidbSku64c256gN4, tidbSku64c256gC4a       *schema.ComponentTierUnit
				tiflashSku8c64gN4, tiflashSku8c64gC4a     *schema.ComponentTierUnit
				tiflashSku16c128gN4, tiflashSku16c128gC4a *schema.ComponentTierUnit
				tiflashSku32c128gN4, tiflashSku32c128gC4a *schema.ComponentTierUnit
				tiflashSku32c256gN4, tiflashSku32c256gC4a *schema.ComponentTierUnit
				pdSku32c64gN4, pdSku32c64gC4a             *schema.ComponentTierUnit
				pdSku64c128gN4, pdSku64c128gC4a           *schema.ComponentTierUnit
			)

			// Add 2c8g skus.
			{
				// PD
				tier, err := createComponentTier(tx, "2C8G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_PD", "2C8G", "pd", "2 vCPU, 8 GiB", 2, 8, "V1")
				if err != nil {
					return err
				}
				if pdSku2c8g, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-2"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if pdSku2c8gC4a, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-2"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 2c8g pd
				if err := upsertTierUnitMapping(tx, tier, pdSku2c8g.Key, pdSku2c8gC4a.Key); err != nil {
					return err
				}
			}

			// Add 4c16g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"4C16G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"4C16G",
					"tidb",
					"4 vCPU, 16 GiB",
					4,
					16,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku4c16gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-4"]); err != nil {
					return err
				}
				if tidbSku4c16gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-4"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 4c16g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku4c16gN4.Key, tidbSku4c16gC4a.Key); err != nil {
					return err
				}

				// TiKV
				tier, err = createComponentTier(tx, "4C16G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIKV", "4C16G", "tikv", "4 vCPU, 16 GiB", 4, 16, "V1")
				if err != nil {
					return err
				}
				if tikvSku4c16g, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-4"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tikvSku4c16gC4a, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-4"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 4c16g tikv
				if err := upsertTierUnitMapping(tx, tier, tikvSku4c16g.Key, tikvSku4c16gC4a.Key); err != nil {
					return err
				}

				// PD
				tier, err = createComponentTier(tx, "4C16G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_PD", "4C16G", "pd", "4 vCPU, 16 GiB", 4, 16, "V1")
				if err != nil {
					return err
				}
				if pdSku4c16g, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-4"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if pdSku4c16gC4a, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-4"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 4c16g pd
				if err := upsertTierUnitMapping(tx, tier, pdSku4c16g.Key, pdSku4c16gC4a.Key); err != nil {
					return err
				}
			}

			// Add 8c16g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"8C16G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"8C16G",
					"tidb",
					"8 vCPU, 16 GiB",
					8,
					16,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku8c16gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highcpu-8"]); err != nil {
					return err
				}
				if tidbSku8c16gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highcpu-8"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c16g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku8c16gN4.Key, tidbSku8c16gC4a.Key); err != nil {
					return err
				}
			}

			// Add 8c32g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"8C32G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"8C32G",
					"tidb",
					"8 vCPU, 32 GiB",
					8,
					32,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku8c32gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-8"]); err != nil {
					return err
				}
				if tidbSku8c32gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-8"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c32g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku8c32gN4.Key, tidbSku8c32gC4a.Key); err != nil {
					return err
				}

				// TiKV
				tier, err = createComponentTier(tx, "8C32G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIKV", "8C32G", "tikv", "8 vCPU, 32 GiB", 8, 32, "V1")
				if err != nil {
					return err
				}
				if tikvSku8c32g, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tikvSku8c32gC4a, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c32g tikv
				if err := upsertTierUnitMapping(tx, tier, tikvSku8c32g.Key, tikvSku8c32gC4a.Key); err != nil {
					return err
				}
			}

			// Add 8c64g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"8C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"8C64G",
					"tidb",
					"8 vCPU, 64 GiB",
					8,
					64,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku8c64gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highmem-8"]); err != nil {
					return err
				}
				if tidbSku8c64gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highmem-8"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c64g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku8c64gN4.Key, tidbSku8c64gC4a.Key); err != nil {
					return err
				}

				// TiKV
				tier, err = createComponentTier(tx, "8C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIKV", "8C64G", "tikv", "8 vCPU, 64 GiB", 8, 64, "V1")
				if err != nil {
					return err
				}
				if tikvSku8c64g, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highmem-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tikvSku8c64gC4a, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highmem-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c64g tikv
				if err := upsertTierUnitMapping(tx, tier, tikvSku8c64g.Key, tikvSku8c64gC4a.Key); err != nil {
					return err
				}

				// TiFlash
				tier, err = createComponentTier(
					tx,
					"8C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIFLASH",
					"8C64G",
					"tiflash",
					"8 vCPU, 64 GiB",
					8,
					64,
					"V1",
				)
				if err != nil {
					return err
				}
				if tiflashSku8c64gN4, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highmem-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tiflashSku8c64gC4a, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highmem-8"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 8c64g tiflash
				if err := upsertTierUnitMapping(tx, tier, tiflashSku8c64gN4.Key, tiflashSku8c64gC4a.Key); err != nil {
					return err
				}
			}

			// Add 16c32g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"16C32G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"16C32G",
					"tidb",
					"16 vCPU, 32 GiB",
					16,
					32,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku16c32gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highcpu-16"]); err != nil {
					return err
				}
				if tidbSku16c32gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highcpu-16"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 16c32g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku16c32gN4.Key, tidbSku16c32gC4a.Key); err != nil {
					return err
				}

				// PD
				tier, err = createComponentTier(tx, "16C32G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_PD", "16C32G", "pd", "16 vCPU, 32 GiB", 16, 32, "V1")
				if err != nil {
					return err
				}
				pdSku16c32g, err = createPdSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highcpu-16"], hyperdiskBalancedStorage)
				if err != nil {
					return err
				}
				pdSku16c32gC4a, err = createPdSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highcpu-16"], hyperdiskBalancedStorage)
				if err != nil {
					return err
				}
				// Overriding the spu mapping for 16c32g pd
				if err := upsertTierUnitMapping(tx, tier, pdSku16c32g.Key, pdSku16c32gC4a.Key); err != nil {
					return err
				}
			}

			// Add 16c64g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"16C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"16C64G",
					"tidb",
					"16 vCPU, 64 GiB",
					16,
					64,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku16c64gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-16"]); err != nil {
					return err
				}
				if tidbSku16c64gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-16"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 16c64g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku16c64gN4.Key, tidbSku16c64gC4a.Key); err != nil {
					return err
				}

				// TiKV
				tier, err = createComponentTier(
					tx,
					"16C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIKV",
					"16C64G",
					"tikv",
					"16 vCPU, 64 GiB",
					16,
					64,
					"V1",
				)
				if err != nil {
					return err
				}
				if tikvSku16c64g, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-16"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tikvSku16c64gC4a, err = createTikvSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-16"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 16c64g tikv
				if err := upsertTierUnitMapping(tx, tier, tikvSku16c64g.Key, tikvSku16c64gC4a.Key); err != nil {
					return err
				}
			}

			// Add 16c128g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"16C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"16C128G",
					"tidb",
					"16 vCPU, 128 GiB",
					16,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku16c128gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highmem-16"]); err != nil {
					return err
				}
				if tidbSku16c128gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highmem-16"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 16c128g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku16c128gN4.Key, tidbSku16c128gC4a.Key); err != nil {
					return err
				}

				// TiFlash
				tier, err = createComponentTier(
					tx,
					"16C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIFLASH",
					"16C128G",
					"tiflash",
					"16 vCPU, 128 GiB",
					16,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				if tiflashSku16c128gN4, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highmem-16"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tiflashSku16c128gC4a, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highmem-16"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 16c128g tiflash
				if err := upsertTierUnitMapping(tx, tier, tiflashSku16c128gN4.Key, tiflashSku16c128gC4a.Key); err != nil {
					return err
				}
			}

			// Add 32c64g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"32C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"32C64G",
					"tidb",
					"32 vCPU, 64 GiB",
					32,
					64,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku32c64gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-highcpu-32"]); err != nil {
					return err
				}
				if tidbSku32c64gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-highcpu-32"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 32c64g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku32c64gN4.Key, tidbSku32c64gC4a.Key); err != nil {
					return err
				}

				// PD
				tier, err = createComponentTier(tx, "32C64G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_PD", "32C64G", "pd", "32 vCPU, 64 GiB", 32, 64, "V1")
				if err != nil {
					return err
				}
				if pdSku32c64gN4, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highcpu-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if pdSku32c64gC4a, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highcpu-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 32c64g pd
				if err := upsertTierUnitMapping(tx, tier, pdSku32c64gN4.Key, pdSku32c64gC4a.Key); err != nil {
					return err
				}
			}

			// Add 32c128g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"32C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"32C128G",
					"tidb",
					"32 vCPU, 128 GiB",
					32,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku32c128gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-32"]); err != nil {
					return err
				}
				if tidbSku32c128gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-32"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 32c128g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku32c128gN4.Key, tidbSku32c128gC4a.Key); err != nil {
					return err
				}

				// TiKV
				tier, err = createComponentTier(
					tx,
					"32C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIKV",
					"32C128G",
					"tikv",
					"32 vCPU, 128 GiB",
					32,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				tikvSku32c128g, err = createTikvSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-32"], hyperdiskBalancedStorage)
				if err != nil {
					return err
				}
				tikvSku32c128gC4a, err = createTikvSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-32"], hyperdiskBalancedStorage)
				if err != nil {
					return err
				}
				// Overriding the spu mapping for 32c128g tikv
				if err := upsertTierUnitMapping(tx, tier, tikvSku32c128g.Key, tikvSku32c128gC4a.Key); err != nil {
					return err
				}

				// TiFlash
				tier, err = createComponentTier(
					tx,
					"32C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIFLASH",
					"32C128G",
					"tiflash",
					"32 vCPU, 128 GiB",
					32,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				if tiflashSku32c128gN4, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-standard-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tiflashSku32c128gC4a, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-standard-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 32c128g tiflash
				if err := upsertTierUnitMapping(tx, tier, tiflashSku32c128gN4.Key, tiflashSku32c128gC4a.Key); err != nil {
					return err
				}
			}

			// Add 32c256g skus.
			{
				// TiFlash
				tier, err := createComponentTier(
					tx,
					"32C256G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIFLASH",
					"32C256G",
					"tiflash",
					"32 vCPU, 256 GiB",
					32,
					256,
					"V1",
				)
				if err != nil {
					return err
				}
				if tiflashSku32c256gN4, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highmem-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if tiflashSku32c256gC4a, err = createTiflashSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highmem-32"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 32c256g tiflash
				if err := upsertTierUnitMapping(tx, tier, tiflashSku32c256gN4.Key, tiflashSku32c256gC4a.Key); err != nil {
					return err
				}
			}

			// Add 64c128g skus.
			{
				// PD
				tier, err := createComponentTier(
					tx,
					"64C128G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_PD",
					"64C128G",
					"pd",
					"64 vCPU, 128 GiB",
					64,
					128,
					"V1",
				)
				if err != nil {
					return err
				}
				if pdSku64c128gN4, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["n4-highcpu-64"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				if pdSku64c128gC4a, err = createPdSku(
					tx,
					timestamp,
					provider,
					tier,
					instanceTypeMap["c4a-highcpu-64"],
					hyperdiskBalancedStorage,
				); err != nil {
					return err
				}
				// Overriding the spu mapping for 64c128g pd
				if err := upsertTierUnitMapping(tx, tier, pdSku64c128gN4.Key, pdSku64c128gC4a.Key); err != nil {
					return err
				}
			}

			// Add 64c256g skus.
			{
				// TiDB
				tier, err := createComponentTier(
					tx,
					"64C256G:DEPLOY_TYPE_DEDICATED:COMPONENT_TYPE_TIDB",
					"64C256G",
					"tidb",
					"64 vCPU, 256 GiB",
					64,
					256,
					"V1",
				)
				if err != nil {
					return err
				}
				if tidbSku64c256gN4, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["n4-standard-64"]); err != nil {
					return err
				}
				if tidbSku64c256gC4a, err = createTidbSku(tx, timestamp, provider, tier, instanceTypeMap["c4a-standard-64"]); err != nil {
					return err
				}
				// Overriding the spu mapping for 64c256g tidb
				if err := upsertTierUnitMapping(tx, tier, tidbSku64c256gN4.Key, tidbSku64c256gC4a.Key); err != nil {
					return err
				}
			}

			// Add pd tikv mappings
			pdTikvMappings := []*schema.TierUnitCondition{
				{
					ComponentTierUnitId: uint64(pdSku2c8g.ID), // 2c8g pd for 4c16g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q]`, tikvSku4c16g.Key),
				},
				{
					ComponentTierUnitId: uint64(pdSku2c8gC4a.ID), // 2c8g pd for 4c16g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q]`, tikvSku4c16gC4a.Key),
				},
				{
					ComponentTierUnitId: uint64(pdSku4c16g.ID), // 4c16g pd for 8c32g, 8c64g and 16c64g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q,%q,%q]`, tikvSku8c32g.Key, tikvSku8c64g.Key, tikvSku16c64g.Key),
				},
				{
					ComponentTierUnitId: uint64(pdSku4c16gC4a.ID), // 4c16g pd for 8c32g, 8c64g and 16c64g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q,%q,%q]`, tikvSku8c32gC4a.Key, tikvSku8c64gC4a.Key, tikvSku16c64gC4a.Key),
				},
				{
					ComponentTierUnitId: uint64(pdSku16c32g.ID), // 16c32g pd for 32c128g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q]`, tikvSku32c128g.Key),
				},
				{
					ComponentTierUnitId: uint64(pdSku16c32gC4a.ID), // 16c32g pd for 32c128g tikv
					Key:                 globaldedicatedv1.Sku_Condition_ATTACH_TIERUNIT.String(),
					Operator:            globaldedicatedv1.Sku_Condition_IN.String(),
					Values:              fmt.Sprintf(`[%q]`, tikvSku32c128gC4a.Key),
				},
			}

			for _, pdTikvMapping := range pdTikvMappings {
				pdTikvMapping.CreatedAt = timestamp
				pdTikvMapping.UpdatedAt = timestamp
				if err := tx.Clauses(clause.OnConflict{
					Columns:   []clause.Column{{Name: "key"}, {Name: "component_tier_unit_id"}},
					UpdateAll: true,
				}).Create(pdTikvMapping).Error; err != nil {
					return err
				}
			}

			return nil
		})
	},
)

func createComponentTier(
	tx *gorm.DB,
	key, name, componentType, displayName string,
	cpuNum, memorySizeGi uint32,
	version string, //nolint:unparam // version is reserved for future use
) (*schema.ComponentTier, error) {
	tier := &schema.ComponentTier{
		Key:           key,
		Name:          name,
		ComponentType: componentType,
		DisplayName:   displayName,
		CPUNum:        cpuNum,
		MemorySizeGi:  memorySizeGi,
		Version:       version,
	}
	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "key"}},
		UpdateAll: true,
	}).Create(tier).Error; err != nil {
		return nil, err
	}
	return tier, nil
}

func createTidbSku(
	tx *gorm.DB,
	timestamp time.Time,
	provider *schema.Provider,
	tier *schema.ComponentTier,
	instanceType *schema.InstanceType,
) (*schema.ComponentTierUnit, error) {
	sku := &schema.ComponentTierUnit{
		Key:             fmt.Sprintf("gcp:tidb:%s", instanceType.InstanceType),
		Priority:        DEFAULT_PRIORITY,
		ComponentType:   "tidb",
		ProviderId:      uint64(provider.ID),
		ComponentTierId: uint64(tier.ID),
		InstanceTypeId:  uint64(instanceType.ID),
		DefaultNodeNum:  DEFAULT_TIDB_NODE_NUM,
	}
	sku.CreatedAt = timestamp
	sku.UpdatedAt = timestamp
	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "key"}},
		UpdateAll: true,
	}).Create(sku).Error; err != nil {
		return nil, err
	}
	return sku, nil
}

func createPdSku(
	tx *gorm.DB,
	timestamp time.Time,
	provider *schema.Provider,
	tier *schema.ComponentTier,
	instanceType *schema.InstanceType,
	storage *schema.StorageTemplate,
) (*schema.ComponentTierUnit, error) {
	sku := &schema.ComponentTierUnit{
		Key:               fmt.Sprintf("gcp:pd:%s:%s:%d:%d", instanceType.InstanceType, storage.VolumeType, PD_DEFAULT_IOPS, PD_DEFAULT_THROUGHPUT),
		Priority:          DEFAULT_PRIORITY,
		ComponentType:     "pd",
		ProviderId:        uint64(provider.ID),
		ComponentTierId:   uint64(tier.ID),
		InstanceTypeId:    uint64(instanceType.ID),
		DefaultNodeNum:    DEFAULT_PD_NODE_NUM,
		StorageTemplateId: lo.ToPtr(uint64(storage.ID)),
		MinStorageGi:      PD_STORAGE_GI,
		MaxStorageGi:      HYPERDISK_MAX_STORAGE_GI,
		DefaultStorageGi:  PD_STORAGE_GI,
		DefaultIops:       PD_DEFAULT_IOPS,
		DefaultThroughPut: lo.ToPtr(uint32(PD_DEFAULT_THROUGHPUT)),
	}
	sku.CreatedAt = timestamp
	sku.UpdatedAt = timestamp
	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "key"}},
		UpdateAll: true,
	}).Create(sku).Error; err != nil {
		return nil, err
	}
	return sku, nil
}

func createTikvSku(
	tx *gorm.DB,
	timestamp time.Time,
	provider *schema.Provider,
	tier *schema.ComponentTier,
	instanceType *schema.InstanceType,
	storage *schema.StorageTemplate,
) (*schema.ComponentTierUnit, error) {
	// derive default IOPS/Throughput from tier.Name
	defaultIops, defaultThroughput := uint32(0), uint32(0)
	switch tier.Name {
	case "2C8G":
		defaultIops, defaultThroughput = 3000, 140
	case "4C16G":
		defaultIops, defaultThroughput = 3000, 149
	case "8C32G", "8C64G":
		defaultIops, defaultThroughput = 4000, 297
	case "16C64G", "16C128G":
		defaultIops, defaultThroughput = 8000, 594
	case "32C128G", "64C256G":
		defaultIops, defaultThroughput = 16000, 1000
	}

	sku := &schema.ComponentTierUnit{
		Key: fmt.Sprintf(
			"gcp:tikv:%s:%s:%d:%d",
			instanceType.InstanceType,
			storage.VolumeType,
			lo.Ternary(defaultIops == 0, HYPERDISK_MAX_IOPS, defaultIops),
			lo.Ternary(defaultThroughput == 0, HYPERDISK_MAX_THROUGHPUT, defaultThroughput),
		),
		Priority:          DEFAULT_PRIORITY,
		ComponentType:     "tikv",
		ProviderId:        uint64(provider.ID),
		ComponentTierId:   uint64(tier.ID),
		InstanceTypeId:    uint64(instanceType.ID),
		DefaultNodeNum:    DEFAULT_TIKV_NODE_NUM,
		StorageTemplateId: lo.ToPtr(uint64(storage.ID)),
		MinStorageGi:      HYPERDISK_MIN_STORAGE_GI,
		MaxStorageGi:      HYPERDISK_MAX_STORAGE_GI,
		DefaultStorageGi:  HYPERDISK_DEFAULT_STORAGE_GI,
		DefaultIops:       defaultIops,
		DefaultThroughPut: lo.ToPtr(defaultThroughput),
		MaxIops:           HYPERDISK_MAX_IOPS,
		MinIops:           HYPERDISK_MIN_IOPS,
		MaxThroughPut:     HYPERDISK_MAX_THROUGHPUT,
		MinThroughPut:     HYPERDISK_MIN_THROUGHPUT,
	}
	sku.CreatedAt = timestamp
	sku.UpdatedAt = timestamp
	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "key"}},
		UpdateAll: true,
	}).Create(sku).Error; err != nil {
		return nil, err
	}
	return sku, nil
}

func createTiflashSku(
	tx *gorm.DB,
	timestamp time.Time,
	provider *schema.Provider,
	tier *schema.ComponentTier,
	instanceType *schema.InstanceType,
	storage *schema.StorageTemplate,
) (*schema.ComponentTierUnit, error) {
	// derive default IOPS/Throughput from tier.Name
	defaultIops, defaultThroughput := uint32(0), uint32(0)
	switch tier.Name {
	case "8C64G":
		defaultIops, defaultThroughput = 12000, 297
	case "16C128G":
		defaultIops, defaultThroughput = 16000, 594
	case "32C128G", "32C256G":
		defaultIops, defaultThroughput = 16000, 1000
	}

	sku := &schema.ComponentTierUnit{
		Key: fmt.Sprintf(
			"gcp:tiflash:%s:%s:%d:%d",
			instanceType.InstanceType,
			storage.VolumeType,
			lo.Ternary(defaultIops == 0, HYPERDISK_MAX_IOPS, defaultIops),
			lo.Ternary(defaultThroughput == 0, HYPERDISK_MAX_THROUGHPUT, defaultThroughput),
		),
		Priority:          DEFAULT_PRIORITY,
		ComponentType:     "tiflash",
		ProviderId:        uint64(provider.ID),
		ComponentTierId:   uint64(tier.ID),
		InstanceTypeId:    uint64(instanceType.ID),
		DefaultNodeNum:    0,
		StorageTemplateId: lo.ToPtr(uint64(storage.ID)),
		MinStorageGi:      HYPERDISK_MIN_STORAGE_GI,
		MaxStorageGi:      HYPERDISK_MAX_STORAGE_GI,
		DefaultStorageGi:  HYPERDISK_DEFAULT_STORAGE_GI,
		DefaultIops:       defaultIops,
		DefaultThroughPut: lo.ToPtr(defaultThroughput),
		MaxIops:           HYPERDISK_MAX_IOPS,
		MinIops:           HYPERDISK_MIN_IOPS,
		MaxThroughPut:     HYPERDISK_MAX_THROUGHPUT,
		MinThroughPut:     HYPERDISK_MIN_THROUGHPUT,
	}
	sku.CreatedAt = timestamp
	sku.UpdatedAt = timestamp
	if err := tx.Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "key"}},
		UpdateAll: true,
	}).Create(sku).Error; err != nil {
		return nil, err
	}
	return sku, nil
}

// Helper: upsert TierUnitMapping with given SKU keys for a component tier.
func upsertTierUnitMapping(tx *gorm.DB, tier *schema.ComponentTier, skuKeys ...string) error {
	mappings := &schema.TierTierUnit{
		ComponentTierId: uint64(tier.ID),
	}
	if err := tx.Where("component_tier_id = ?", tier.ID).FirstOrCreate(mappings).Error; err != nil {
		return err
	}
	newItems := make([]*schema.TierUnitMappingItem, 0, len(skuKeys))
	for _, key := range skuKeys {
		newItems = append(newItems, &schema.TierUnitMappingItem{
			ProviderName: "gcp",
			RegionNames:  []string{"NAME_UNSPECIFIED"},
			TenantIDs:    []string{},
			SkuKey:       key,
		})
	}
	mappings.TierUnitMapping.Items = append(newItems, mappings.TierUnitMapping.Items...)
	mappings.TierUnitMapping.Items = lo.UniqBy(mappings.TierUnitMapping.Items, func(item *schema.TierUnitMappingItem) string { return item.SkuKey })
	return tx.Save(mappings).Error
}