package controlplane

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

	bootstate "github.com/nexuscrm/db/pkg/bootstate"
)

// migrationIDMasterDetailRequiredBackfill is the stable _System_Migration ID
// for the NexusCRM#4726 metadata repair.
const migrationIDMasterDetailRequiredBackfill = "master_detail_required_backfill_v1"

// MigrateMasterDetailRequiredBackfill repairs active master-detail field rows
// written before both authoring representations shared the same normalization.
// Those rows already have NOT NULL physical columns, but _System_Field may say
// Required=false and may omit IsMasterDetail when Type=masterdetail.
//
// The migration is idempotent and intentionally repairs metadata only. It does
// not change field ownership/customization or physical schema. The stamp records
// when this convergence rule was introduced, but every invocation still runs the
// guarded UPDATE so later legacy/source-backed writes cannot drift after stamping.
func MigrateMasterDetailRequiredBackfill(ctx context.Context, db *sql.DB) (int64, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIDMasterDetailRequiredBackfill)
	if err != nil {
		return 0, fmt.Errorf("MigrateMasterDetailRequiredBackfill: check stamp: %w", err)
	}

	result, err := db.ExecContext(ctx, `
		UPDATE _System_Field f
		JOIN _System_Object o ON o.Id = f.ObjectId
		SET f.Required = 1,
		    f.IsMasterDetail = 1
		WHERE COALESCE(f.IsDeleted, 0) = 0
		  AND COALESCE(o.IsDeleted, 0) = 0
		  AND (
		      LOWER(TRIM(f.Type)) = 'masterdetail'
		      OR COALESCE(f.IsMasterDetail, 0) = 1
		  )
		  AND (
		      COALESCE(f.Required, 0) = 0
		      OR COALESCE(f.IsMasterDetail, 0) = 0
		  )
	`)
	if err != nil {
		return 0, fmt.Errorf("MigrateMasterDetailRequiredBackfill: update metadata: %w", err)
	}
	updated, err := result.RowsAffected()
	if err != nil {
		return 0, fmt.Errorf("MigrateMasterDetailRequiredBackfill: rows affected: %w", err)
	}

	if !applied {
		if err := bootstate.RecordMigrationApplied(
			ctx,
			db,
			migrationIDMasterDetailRequiredBackfill,
			1,
			"Normalize Required and IsMasterDetail on legacy master-detail field rows",
		); err != nil {
			return 0, fmt.Errorf("MigrateMasterDetailRequiredBackfill: stamp migration: %w", err)
		}
	}

	log.Printf("   ✅ MigrateMasterDetailRequiredBackfill: converged %d field row(s) (previously_stamped=%t)", updated, applied)
	return updated, nil
}