package controlplane

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"strings"

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

// migrationIdIsPolymorphicMultiTargetBackfill is the stable
// `_System_Migration.Id` for this migration.
const migrationIdIsPolymorphicMultiTargetBackfill = "is_polymorphic_multi_target_backfill_v1"

// MigrateIsPolymorphicMultiTargetBackfill stamps IsPolymorphic=true on
// persisted _System_Field rows that have multi-target ReferenceTo but lack
// the explicit flag. This ensures legacy pre-cutover rows round-trip as
// polymorphic after schema reload/restart, so <field>_type discriminator
// ownership is not silently lost.
//
// The migration is idempotent: already-stamped rows are skipped.
//
// Fail-closed on ambiguous persisted metadata: malformed ReferenceTo JSON,
// non-array ReferenceTo values, or blank entries inside a multi-target array
// are treated as fatal diagnostics. The migration does NOT stamp
// _System_Migration completion until every active in-scope row has been
// classified unambiguously. Multi-target masterdetail rows are out of scope
// (masterdetail is not a polymorphic relationship kind) and are excluded
// from the candidate query entirely.
func MigrateIsPolymorphicMultiTargetBackfill(ctx context.Context, db *sql.DB) (int64, error) {
	// Check if migration already completed via the stamp helper.
	applied, err := bootstate.MigrationApplied(ctx, db, migrationIdIsPolymorphicMultiTargetBackfill)
	if err != nil {
		return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: check stamp: %w", err)
	}
	if applied {
		log.Printf("   ✅ MigrateIsPolymorphicMultiTargetBackfill: already stamped, skipping")
		return 0, nil
	}

	log.Println("   🔧 MigrateIsPolymorphicMultiTargetBackfill: scanning for multi-target reference/lookup rows...")

	// Find all active reference/lookup fields with non-empty ReferenceTo that
	// are not yet stamped IsPolymorphic=true. masterdetail is excluded: it is
	// not a polymorphic relationship kind and stamping it would be wrong.
	// We filter in Go rather than SQL because ReferenceTo is JSON and we need
	// to count array elements.
	query := `
		SELECT f.Id, o.ApiName, f.ApiName, f.Type, f.ReferenceTo, f.IsPolymorphic
		FROM _System_Field f
		JOIN _System_Object o ON f.ObjectId = o.Id
		WHERE f.Type IN ('reference', 'lookup')
		  AND COALESCE(f.IsDeleted, 0) = 0
		  AND COALESCE(o.IsDeleted, 0) = 0
		  AND f.ReferenceTo IS NOT NULL
		  AND TRIM(f.ReferenceTo) != ''
		  AND TRIM(f.ReferenceTo) != '[]'
		  AND COALESCE(f.IsPolymorphic, 0) = 0
	`

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: query: %w", err)
	}
	defer rows.Close()

	type candidate struct {
		ID            string
		ObjectAPIName string
		FieldAPIName  string
		Type          string
	}
	var candidates []candidate

	for rows.Next() {
		var id, objectAPIName, fieldAPIName, fieldType string
		var referenceToJSON sql.NullString
		var isPolymorphic sql.NullBool
		if err := rows.Scan(&id, &objectAPIName, &fieldAPIName, &fieldType, &referenceToJSON, &isPolymorphic); err != nil {
			return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: scan: %w", err)
		}
		// Already stamped (defensive; SQL should have excluded these).
		if isPolymorphic.Valid && isPolymorphic.Bool {
			continue
		}
		// Non-empty ReferenceTo must parse as a JSON string array. Anything
		// else is ambiguous persisted metadata — fail closed so the operator
		// can repair the row; do not record migration completion.
		if !referenceToJSON.Valid || referenceToJSON.String == "" {
			return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: %s.%s (%s) has NULL/empty ReferenceTo despite non-empty filter",
				objectAPIName, fieldAPIName, id)
		}
		var targets []string
		if err := json.Unmarshal([]byte(referenceToJSON.String), &targets); err != nil {
			return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: %s.%s (%s) has malformed ReferenceTo JSON %q: %w",
				objectAPIName, fieldAPIName, id, referenceToJSON.String, err)
		}
		if len(targets) <= 1 {
			continue
		}
		// Blank targets inside a multi-target array signal ambiguous
		// metadata; fail closed rather than stamping polymorphic ownership
		// on a row with invalid target shape.
		for i, target := range targets {
			if strings.TrimSpace(target) == "" {
				return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: %s.%s (%s) has blank ReferenceTo[%d] in multi-target array %q",
					objectAPIName, fieldAPIName, id, i, referenceToJSON.String)
			}
		}
		candidates = append(candidates, candidate{
			ID:            id,
			ObjectAPIName: objectAPIName,
			FieldAPIName:  fieldAPIName,
			Type:          fieldType,
		})
	}
	if err := rows.Err(); err != nil {
		return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: rows: %w", err)
	}

	if len(candidates) == 0 {
		log.Println("   ✅ MigrateIsPolymorphicMultiTargetBackfill: no rows need stamping")
	} else {
		log.Printf("   🔧 MigrateIsPolymorphicMultiTargetBackfill: stamping %d multi-target row(s)", len(candidates))

		updateStmt, err := db.PrepareContext(ctx,
			`UPDATE _System_Field SET IsPolymorphic = 1 WHERE Id = ?`)
		if err != nil {
			return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: prepare: %w", err)
		}
		defer updateStmt.Close()

		for _, c := range candidates {
			result, err := updateStmt.ExecContext(ctx, c.ID)
			if err != nil {
				return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: update %s.%s (%s): %w",
					c.ObjectAPIName, c.FieldAPIName, c.ID, err)
			}
			affected, _ := result.RowsAffected()
			if affected > 0 {
				log.Printf("      stamped IsPolymorphic=1 on %s.%s (type=%s)", c.ObjectAPIName, c.FieldAPIName, c.Type)
			}
		}
	}

	// Stamp migration completion only after every in-scope row has been
	// classified unambiguously and updated or proven not to need the flag.
	if err := bootstate.RecordMigrationApplied(ctx, db, migrationIdIsPolymorphicMultiTargetBackfill, 1,
		"Stamp IsPolymorphic=true on legacy multi-target reference/lookup rows"); err != nil {
		return 0, fmt.Errorf("MigrateIsPolymorphicMultiTargetBackfill: stamp migration: %w", err)
	}

	return int64(len(candidates)), nil
}