package controlplane

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

	database "github.com/nexuscrm/db"
	bootstate "github.com/nexuscrm/db/pkg/bootstate"
	"github.com/nexuscrm/db/pkg/schemashape"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/persistence"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/pingcap-inc/nexuscrm-controlplane/internal/persistencewiring"
)

const migrationIdBackfillMissingNameFieldMetadata = "20260528_backfill_missing_name_field_metadata_for_custom_settings"

type missingNameFieldMetadataCandidate struct {
	objectID string
	apiName  string
}

// BackfillMissingNameFieldMetadata repairs warm-upgrade custom-setting objects
// whose live table already has a physical Name column but whose persisted
// _System_Field metadata omitted the authoritative Name row entirely.
func BackfillMissingNameFieldMetadata(ctx context.Context, db *sql.DB) (int, error) {
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdBackfillMissingNameFieldMetadata)
	if err != nil {
		return 0, err
	}
	if applied {
		log.Printf("   ⏭  BackfillMissingNameFieldMetadata: already applied; skipping")
		return 0, nil
	}

	candidates, err := loadMissingNameFieldMetadataCandidates(ctx, db)
	if err != nil {
		return 0, err
	}
	physical, err := schemaProvisioner.LoadPhysicalColumnShapes(db)
	if err != nil {
		return 0, fmt.Errorf("BackfillMissingNameFieldMetadata: load physical column shapes: %w", err)
	}

	repo := persistence.NewSchemaRepository(db, nil)
	directWriters, err := persistencewiring.NewPermissionStateDirectWriters(
		database.NewConnectionFromDB(db),
	)
	if err != nil {
		return 0, err
	}
	if err := directWriters.BindSchema(repo); err != nil {
		return 0, err
	}
	fields := make([]domainschema.FieldWithContext, 0, len(candidates))
	blocked := make([]string, 0)
	for _, candidate := range candidates {
		nameCol, ok := physicalColumnShape(physical, candidate.apiName, constants.FieldName)
		if !ok {
			blocked = append(blocked, candidate.apiName)
			continue
		}

		field := repo.PrepareFieldForBatch(candidate.apiName, domainschema.ColumnDefinition{
			Name:          constants.FieldName,
			Label:         constants.FieldName,
			Type:          nameCol.ColumnType,
			Nullable:      nameCol.Nullable,
			CollationName: nullStringValue(nameCol.CollationName),
			IsNameField:   true,
		})
		field.ObjectID = candidate.objectID
		fields = append(fields, field)
	}

	if len(blocked) > 0 {
		sort.Strings(blocked)
		return 0, fmt.Errorf(
			"BackfillMissingNameFieldMetadata: custom setting object(s) missing authoritative Name metadata but lacking physical Name column: %s",
			strings.Join(blocked, ", "),
		)
	}

	if len(fields) > 0 {
		if err := repo.BatchSaveFieldMetadata(ctx, fields, nil); err != nil {
			return 0, fmt.Errorf("BackfillMissingNameFieldMetadata: persist Name fields: %w", err)
		}
	}

	remaining, err := loadMissingNameFieldMetadataCandidates(ctx, db)
	if err != nil {
		return 0, fmt.Errorf("BackfillMissingNameFieldMetadata: closure check query: %w", err)
	}
	var unresolved []string
	for _, candidate := range remaining {
		if _, ok := physicalColumnShape(physical, candidate.apiName, constants.FieldName); ok {
			unresolved = append(unresolved, candidate.apiName)
		}
	}
	if len(unresolved) > 0 {
		sort.Strings(unresolved)
		return len(fields), fmt.Errorf(
			"BackfillMissingNameFieldMetadata: %d custom setting object(s) still miss persisted Name metadata despite physical Name column: %s",
			len(unresolved), strings.Join(unresolved, ", "),
		)
	}

	if err := bootstate.RecordMigrationApplied(
		ctx, db,
		migrationIdBackfillMissingNameFieldMetadata,
		1,
		"Backfill missing _System_Field.Name rows for custom-setting objects that already have a physical Name column and no authoritative name field, using canonical _System_Object.CustomSetting authority instead of broad custom_object scanning.",
	); err != nil {
		return len(fields), err
	}
	if len(fields) > 0 {
		log.Printf("   🧼 BackfillMissingNameFieldMetadata: inserted %d Name field row(s)", len(fields))
	}
	return len(fields), nil
}

func loadMissingNameFieldMetadataCandidates(ctx context.Context, db *sql.DB) ([]missingNameFieldMetadataCandidate, error) {
	rows, err := db.QueryContext(ctx, `
SELECT o.Id, o.ApiName
FROM _System_Object o
WHERE COALESCE(o.IsDeleted, 0) = 0
  AND COALESCE(o.CustomSetting, 0) = 1
  AND NOT EXISTS (
    SELECT 1
    FROM _System_Field f_name
    WHERE f_name.ObjectId = o.Id
      AND COALESCE(f_name.IsDeleted, 0) = 0
      AND LOWER(f_name.ApiName) = LOWER(?)
  )
  AND NOT EXISTS (
    SELECT 1
    FROM _System_Field f_marked
    WHERE f_marked.ObjectId = o.Id
      AND COALESCE(f_marked.IsDeleted, 0) = 0
      AND COALESCE(f_marked.IsNameField, 0) = 1
  )
ORDER BY o.ApiName`, constants.FieldName)
	if err != nil {
		return nil, fmt.Errorf("BackfillMissingNameFieldMetadata query rows: %w", err)
	}
	defer rows.Close()

	var candidates []missingNameFieldMetadataCandidate
	for rows.Next() {
		var candidate missingNameFieldMetadataCandidate
		if err := rows.Scan(&candidate.objectID, &candidate.apiName); err != nil {
			return nil, fmt.Errorf("BackfillMissingNameFieldMetadata scan row: %w", err)
		}
		candidate.apiName = strings.TrimSpace(candidate.apiName)
		candidate.objectID = strings.TrimSpace(candidate.objectID)
		if candidate.apiName == "" || candidate.objectID == "" {
			return nil, fmt.Errorf("BackfillMissingNameFieldMetadata: blank object identity in candidate row")
		}
		candidates = append(candidates, candidate)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("BackfillMissingNameFieldMetadata rows: %w", err)
	}
	return candidates, nil
}

func physicalColumnShape(all map[string]map[string]schemashape.PhysicalColumnShape, tableName string, columnName string) (schemashape.PhysicalColumnShape, bool) {
	tableCols := all[strings.ToLower(strings.TrimSpace(tableName))]
	if len(tableCols) == 0 {
		return schemashape.PhysicalColumnShape{}, false
	}
	col, ok := tableCols[strings.ToLower(strings.TrimSpace(columnName))]
	return col, ok
}

func nullStringValue(v sql.NullString) string {
	if !v.Valid {
		return ""
	}
	return v.String
}