package bootstrap

import (
	"fmt"
	"log"

	"github.com/nexuscrm/backend/internal/infrastructure/database"
	"github.com/nexuscrm/shared/pkg/constants"
)

// NormalizeLegacyDDLDefaults strips DDL single-quote wrapping from legacy
// `_System_Field.DefaultValue` rows and un-escapes doubled-single-quotes
// ('It''s' → It's).
//
// Runs on every RunRepair. Idempotent — the WHERE guard ensures clean rows
// are never touched, and the REPLACE is a no-op on already-clean values.
//
// Why this exists as runtime (not a one-shot migration):
//   - New writes are already clean (schema_field_metadata_builder.go:73
//     normalizes via fieldnormalize at the registerField boundary).
//   - Legacy rows written by older builds carry the raw DDL form `'Draft'`.
//   - A one-shot migration adds a separate deployment step and a version
//     gate on assertions; a runtime self-heal converges every install to
//     clean state without those seams. Mirrors BackfillSource's posture.
//
// Issue #1582 RFC v1.5 — replaces the data-specs/2026-04-22 migration.
func NormalizeLegacyDDLDefaults(dbConn *database.TiDBConnection) error {
	db := dbConn.DB()

	// Regex anchors on the full string: ^'(.*)'$. TiDB (MySQL) REGEXP without
	// anchors would also match `foo'bar'baz` which we MUST NOT mutate.
	stripSQL := fmt.Sprintf(
		"UPDATE `%s` "+
			"SET DefaultValue = REPLACE(SUBSTRING(DefaultValue, 2, CHAR_LENGTH(DefaultValue)-2), '''''', ''''), "+
			"    LastModifiedDate = NOW() "+
			"WHERE IsDeleted = 0 "+
			"AND DefaultValue IS NOT NULL "+
			"AND CHAR_LENGTH(DefaultValue) >= 2 "+
			"AND LEFT(DefaultValue, 1) = '''' "+
			"AND RIGHT(DefaultValue, 1) = ''''",
		constants.TableField)

	res, err := db.Exec(stripSQL)
	if err != nil {
		return fmt.Errorf("NormalizeLegacyDDLDefaults: %w", err)
	}
	if n, _ := res.RowsAffected(); n > 0 {
		log.Printf("   🧼 NormalizeLegacyDDLDefaults: stripped DDL quotes from %d row(s)", n)
	}
	return nil
}