package bootstrapimpl

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/nexuscrm/db"
	"github.com/nexuscrm/shared/pkg/constants"
)

const migrationIdNormalizeLegacyDefaults = "20260516_normalize_legacy_defaults"

// NormalizeLegacyDDLDefaults strips DDL single-quote wrapping from legacy
// `_System_Field.DefaultValue` rows and un-escapes doubled-single-quotes
// ('It”s' -> It's).
//
// Removal criterion: this file can be deleted after every supported deployment
// version has run the 2026-05-16 migration at least once and the predicate
// below is known to return zero rows:
//
//	SELECT COUNT(*) FROM _System_Field
//	WHERE IsDeleted = 0 AND DefaultValue IS NOT NULL
//	  AND CHAR_LENGTH(DefaultValue) >= 2
//	  AND LEFT(DefaultValue, 1) = '''' AND RIGHT(DefaultValue, 1) = ''''
//
// Why this is a stamped 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'`.
//   - Re-running on every boot would rewrite any new customized default whose
//     authored value intentionally starts and ends with a single quote.
//
// Issue #1582 RFC v1.5 — replaces the data-specs/2026-04-22 migration.
func NormalizeLegacyDDLDefaults(ctx context.Context, dbConn *database.TiDBConnection) error {
	return normalizeLegacyDDLDefaults(ctx, dbConn.DB())
}

func normalizeLegacyDDLDefaults(ctx context.Context, db *sql.DB) error {
	applied, err := MigrationApplied(ctx, db, migrationIdNormalizeLegacyDefaults)
	if err != nil {
		return err
	}
	if applied {
		log.Println("   ⏭  NormalizeLegacyDDLDefaults: already applied; skipping")
		return nil
	}

	// 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)

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("NormalizeLegacyDDLDefaults begin transaction: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	res, err := tx.ExecContext(ctx, 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)
	}
	if err := recordMigrationApplied(
		ctx,
		tx,
		migrationIdNormalizeLegacyDefaults,
		1,
		"Strip DDL single-quote wrapping from legacy _System_Field.DefaultValue rows.",
	); err != nil {
		return err
	}
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("NormalizeLegacyDDLDefaults commit: %w", err)
	}
	committed = true
	return nil
}