package migration

import (
	"gorm.io/gorm"

	"github.com/tidbcloud/tidb-management-service/nextgen-global-service/infra/repoimpl/schema"
)

// Migration 202605010001:
// 1. Add region_service_plans to support multiple service plans per region.
// 2. Add region_service_plan_id to region_tenants for plan-scoped allowlist.
// 3. Backfill relation data from legacy region-level model.
var _ = AddDBMigrateRecord(
	"202605010001",
	func(db *gorm.DB) error {
		if err := db.AutoMigrate(&schema.RegionServicePlan{}, &schema.RegionTenant{}); err != nil {
			return err
		}
		if err := db.Exec(`
			INSERT INTO region_service_plans (created_at, updated_at, region_id, service_plan, is_public)
			SELECT NOW(), NOW(), id, service_plan, COALESCE(is_public, FALSE)
			FROM regions
			WHERE deleted_at IS NULL
			  AND service_plan <> ''
			  AND NOT EXISTS (
				  SELECT 1 FROM region_service_plans
				  WHERE region_service_plans.region_id = regions.id
				    AND region_service_plans.service_plan = regions.service_plan
				    AND region_service_plans.deleted_at IS NULL
			  )
		`).Error; err != nil {
			return err
		}

		return db.Exec(`
			UPDATE region_tenants rt
			JOIN regions r ON r.id = rt.region_id AND r.deleted_at IS NULL
			JOIN region_service_plans rsp ON rsp.region_id = r.id AND rsp.service_plan = r.service_plan AND rsp.deleted_at IS NULL
			SET rt.region_service_plan_id = rsp.id
			WHERE rt.deleted_at IS NULL
			  AND rt.region_service_plan_id IS NULL
		`).Error
	},
)