package migrate

import (
	"context"
	"fmt"

	apprmod "github.com/tidbcloud/devops/models/approval"
	"github.com/tidbcloud/pkgv2/db"

	"gorm.io/gorm"
)

// migration for approval table to add a column `level`
var _ = AddMigrateRecord(
	"202601081400",
	func(raw *gorm.DB) error {
		err := raw.AutoMigrate(&apprmod.ApprovalNode{}, &apprmod.ApprovalTask{})
		if err != nil {
			return err
		}

		dbp := db.NewProvider(raw)
		return dbp.Transaction(context.Background(), func(ctx context.Context) error {
			wdb := dbp.WriteDB(ctx)

			nodeTable := apprmod.ApprovalNode{}.TableName()
			taskTable := apprmod.ApprovalTask{}.TableName()

			sqls := []string{
				fmt.Sprintf("UPDATE %s SET level = `order`;", nodeTable),
				fmt.Sprintf("UPDATE %s SET level = `order`;", taskTable),
				fmt.Sprintf("UPDATE %s SET name = CONCAT('Node ', `order`, '-0') WHERE name = '';", nodeTable),
				fmt.Sprintf("UPDATE %s t INNER JOIN %s n ON t.approval_id = n.approval_id AND t.`order` = n.`order` SET t.node_name = n.name;", taskTable, nodeTable),
				fmt.Sprintf("ALTER TABLE %s DROP COLUMN `order`", nodeTable),
				fmt.Sprintf("ALTER TABLE %s DROP COLUMN `order`", taskTable),
			}

			for _, sql := range sqls {
				err := wdb.Exec(sql).Error
				if err != nil {
					return fmt.Errorf("execute sql [%s] failed: %w", sql, err)
				}
			}

			return nil
		})
	},
)