package bootstrap

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

	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/shared/pkg/constants"
)

// NormalizeObjectIds rewrites every `_System_Object` row whose `Id` does not
// match `schema.GenerateObjectID(ApiName)` to the canonical (lowercase-
// normalized hash) Id, and cascades the rewrite to every FK that references
// `_System_Object.Id` — `_System_Field.ObjectId` and
// `_System_RecordType.ObjectId`.
//
// This is the one-time migration that retires the legacy `disc_<table>` and
// mixed-case `obj_<ApiName>_<hash>` schemes. After it runs, every metadata
// write path produces the same canonical Id for the same logical object,
// eliminating the orphan-field-row class of bug.
//
// Idempotent: rows where `Id == GenerateObjectID(ApiName)` are skipped, so
// a second run is a near-no-op (one SELECT, no writes).
//
// Safety:
//   - FK_CHECKS=0 inside a single connection for the duration of each row's
//     rewrite, so the children-then-parent UPDATE order doesn't trip
//     fk_SystemField_ObjectId / fk_1's NO-ACTION rule mid-flight.
//   - If a canonical-Id row already exists for the same ApiName (concurrent
//     prior write), the legacy row is skipped with a warning — no data is
//     overwritten.
//   - The CASCADE on DELETE is irrelevant here; we never DELETE _System_Object.
//
// Wired into `RunRepair` BEFORE `ReconcileOrphanFieldObjectIds`. Order
// matters: normalization rewrites legitimate rows; orphan reconcile cleans
// up leftover field rows that point to nothing.
func NormalizeObjectIds(ctx context.Context, db *sql.DB) error {
	rows, err := db.QueryContext(ctx, fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE `%s` = 0",
		constants.ColID, constants.FieldAPIName, constants.TableObject, constants.ColIsDeleted,
	))
	if err != nil {
		return fmt.Errorf("list _System_Object for normalization: %w", err)
	}
	defer rows.Close()

	type row struct{ id, apiName string }
	var all []row
	for rows.Next() {
		var r row
		if err := rows.Scan(&r.id, &r.apiName); err != nil {
			return fmt.Errorf("scan normalize row: %w", err)
		}
		all = append(all, r)
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate normalize rows: %w", err)
	}

	rewritten := 0
	skippedConflict := 0
	for _, r := range all {
		canonical := schema.GenerateObjectID(r.apiName)
		if r.id == canonical {
			continue
		}
		didRewrite, err := rewriteObjectID(ctx, db, r.id, canonical, r.apiName)
		if err != nil {
			return fmt.Errorf("rewrite %q (apiName=%q) → %q: %w", r.id, r.apiName, canonical, err)
		}
		if didRewrite {
			rewritten++
		} else {
			skippedConflict++
		}
	}
	if rewritten == 0 && skippedConflict == 0 {
		log.Printf("   ✅ NormalizeObjectIds: no legacy IDs found (all %d _System_Object rows already canonical)", len(all))
	} else {
		log.Printf("   ✅ NormalizeObjectIds: rewrote %d, skipped-conflict %d, examined %d _System_Object row(s)",
			rewritten, skippedConflict, len(all))
	}
	return nil
}

// rewriteObjectID swaps oldID → newID across `_System_Object`,
// `_System_Field`, and `_System_RecordType`. Returns (didRewrite=false, nil)
// when a row at newID already exists for the same ApiName (skipped).
//
// The FK constraints on _System_Field/_System_RecordType have UPDATE_RULE=
// NO ACTION, so naive children-first UPDATEs trip on the missing parent
// (children would briefly reference a non-existent newID). FK_CHECKS=0 for
// the duration sidesteps that, and the operation is wrapped in a single
// connection so the FK_CHECKS toggle is session-local and cannot leak to
// other concurrent connections in the pool.
func rewriteObjectID(ctx context.Context, db *sql.DB, oldID, newID, apiName string) (bool, error) {
	conn, err := db.Conn(ctx)
	if err != nil {
		return false, fmt.Errorf("acquire conn: %w", err)
	}
	defer conn.Close()

	var existing string
	err = conn.QueryRowContext(ctx, fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 LIMIT 1",
		constants.ColID, constants.TableObject, constants.ColID, constants.ColIsDeleted,
	), newID).Scan(&existing)
	switch err {
	case nil:
		log.Printf("   ⚠️  NormalizeObjectIds: canonical Id %q already exists for apiName=%q; legacy row %q skipped (manual reconciliation required)",
			newID, apiName, oldID)
		return false, nil
	case sql.ErrNoRows:
		// Continue to rewrite.
	default:
		return false, fmt.Errorf("check canonical row exists: %w", err)
	}

	if _, err := conn.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=0"); err != nil {
		return false, fmt.Errorf("disable FK checks: %w", err)
	}
	defer func() {
		_, _ = conn.ExecContext(ctx, "SET FOREIGN_KEY_CHECKS=1")
	}()

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

	updates := []struct{ table, col string }{
		{constants.TableField, constants.FieldObjectID},
		{"_System_RecordType", constants.FieldObjectID},
	}
	for _, u := range updates {
		if _, err := tx.ExecContext(ctx, fmt.Sprintf(
			"UPDATE `%s` SET `%s` = ? WHERE `%s` = ?",
			u.table, u.col, u.col,
		), newID, oldID); err != nil {
			return false, fmt.Errorf("update %s.%s: %w", u.table, u.col, err)
		}
	}
	if _, err := tx.ExecContext(ctx, fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ? WHERE `%s` = ?",
		constants.TableObject, constants.ColID, constants.ColID,
	), newID, oldID); err != nil {
		return false, fmt.Errorf("update _System_Object.Id: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return false, fmt.Errorf("commit: %w", err)
	}
	commit = true
	log.Printf("   🔧 NormalizeObjectIds: %s → %s (apiName=%s)", oldID, newID, apiName)
	return true, nil
}