package packs

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

	"github.com/nexuscrm/analytics"
	domainschema "github.com/nexuscrm/kernel/pkg/schema"
	"github.com/nexuscrm/shared/pkg/formulastorage"
)

const objectSpecFormulaStorageTransitionBatchRows = 4096

type objectSpecFormulaStorageTransition struct {
	objectAPIName string
	fieldAPIName  string
	viewName      string
}

func planObjectSpecFormulaStorageTransitions(
	ctx context.Context,
	db *sql.DB,
	defs []domainschema.TableDefinition,
) ([]objectSpecFormulaStorageTransition, error) {
	if db == nil {
		return nil, fmt.Errorf("database is unavailable")
	}

	var transitions []objectSpecFormulaStorageTransition
	for _, def := range defs {
		objectAPIName := strings.TrimSpace(def.TableName)
		if objectAPIName == "" {
			continue
		}
		for _, col := range def.Columns {
			desired, err := formulastorage.Resolve(formulastorage.SpecFromColumn(col))
			if err != nil {
				return nil, fmt.Errorf("resolve desired formula storage for %s.%s: %w", objectAPIName, col.Name, err)
			}
			if !desired.IsWriteMaterialized() {
				continue
			}

			var liveType, liveFormulaMode, liveStorageMode, liveFormula, liveReturnType, liveProjectionConfig string
			err = db.QueryRowContext(ctx, `
				SELECT f.Type,
				       COALESCE(f.FormulaMode, ''),
				       COALESCE(f.FormulaStorageMode, ''),
				       COALESCE(f.Formula, ''),
				       COALESCE(f.ReturnType, ''),
				       COALESCE(f.ProjectionConfig, '')
				  FROM _System_Field f
				  JOIN _System_Object o ON o.Id = f.ObjectId
				 WHERE o.ApiName = ?
				   AND f.ApiName = ?
				   AND COALESCE(o.IsDeleted, 0) = 0
				   AND COALESCE(f.IsDeleted, 0) = 0
				 LIMIT 1`, objectAPIName, col.Name).Scan(
				&liveType,
				&liveFormulaMode,
				&liveStorageMode,
				&liveFormula,
				&liveReturnType,
				&liveProjectionConfig,
			)
			if err == sql.ErrNoRows {
				continue
			}
			if err != nil {
				return nil, fmt.Errorf("read live formula storage for %s.%s: %w", objectAPIName, col.Name, err)
			}

			live, err := formulastorage.Resolve(formulastorage.Spec{
				FieldType:          strings.TrimSpace(liveType),
				Formula:            liveFormula,
				FormulaMode:        liveFormulaMode,
				FormulaStorageMode: liveStorageMode,
				HasFormula:         strings.TrimSpace(liveFormula) != "",
				HasReturnType:      strings.TrimSpace(liveReturnType) != "",
				HasProjectionConfig: strings.TrimSpace(liveProjectionConfig) != "" &&
					!strings.EqualFold(strings.TrimSpace(liveProjectionConfig), "null"),
			})
			if err != nil {
				return nil, fmt.Errorf("resolve live formula storage for %s.%s: %w", objectAPIName, col.Name, err)
			}
			if !live.RequiresFormulaView() {
				continue
			}
			if !sameFormulaStorageTransitionSemantics(col, liveType, liveFormulaMode, liveFormula, liveReturnType, liveProjectionConfig) {
				return nil, fmt.Errorf(
					"formula storage transition for %s.%s must preserve type, active mode, formula, return type, and projection config; split semantic and storage changes into separate releases",
					objectAPIName,
					col.Name,
				)
			}

			transitions = append(transitions, objectSpecFormulaStorageTransition{
				objectAPIName: objectAPIName,
				fieldAPIName:  strings.TrimSpace(col.Name),
				viewName:      analytics.ViewName(objectAPIName),
			})
		}
	}

	sort.Slice(transitions, func(i, j int) bool {
		if transitions[i].objectAPIName != transitions[j].objectAPIName {
			return transitions[i].objectAPIName < transitions[j].objectAPIName
		}
		return transitions[i].fieldAPIName < transitions[j].fieldAPIName
	})
	return transitions, nil
}

// rebuildObjectSpecFormulaStorageTransitionSources makes the FormulaView owner
// regenerate every copy source from the current live metadata before any
// physical column is created. A readable pre-existing view is not authority:
// it may have survived a deferred or failed rebuild with an obsolete SQL
// expression. The metadata row plus the active compiler are the source of
// truth; the view is a replaceable derived artifact.
func rebuildObjectSpecFormulaStorageTransitionSources(
	ctx context.Context,
	deps ApplyDeps,
	transitions []objectSpecFormulaStorageTransition,
) error {
	if len(transitions) == 0 {
		return nil
	}
	if deps.Options.DeferFormulaViewRebuild {
		return fmt.Errorf("formula storage transition cannot defer formula-view rebuild because the copy source must be regenerated from current metadata")
	}
	if deps.Metadata == nil {
		return fmt.Errorf("formula storage transition metadata authority is unavailable")
	}
	if deps.FormulaView == nil {
		return fmt.Errorf("formula storage transition view compiler is unavailable")
	}
	if err := deps.Metadata.RefreshCache(ctx); err != nil {
		return fmt.Errorf("refresh current metadata before formula storage transition: %w", err)
	}
	if err := deps.FormulaView.RefreshRuntimeFunctionRegistry(); err != nil {
		return fmt.Errorf("refresh formula compiler before formula storage transition: %w", err)
	}
	// A transition view may read another formula view owned by a different
	// pack or by admin-authored metadata. Rebuilding only the transition
	// objects, even in their own topological order, leaves out-of-set
	// dependencies stale. Reuse the FormulaView owner's complete active graph
	// rebuild so every dependency is current before any value is copied into a
	// physical column.
	if err := deps.FormulaView.RebuildAllViews(ctx); err != nil {
		return fmt.Errorf("rebuild authoritative formula view dependency graph before storage transition: %w", err)
	}
	if err := deps.FormulaView.RefreshViewCache(ctx); err != nil {
		return fmt.Errorf("refresh formula view cache before storage transition: %w", err)
	}
	return nil
}

func sameFormulaStorageTransitionSemantics(
	col domainschema.ColumnDefinition,
	liveType string,
	liveFormulaMode string,
	liveFormula string,
	liveReturnType string,
	liveProjectionConfig string,
) bool {
	return strings.EqualFold(strings.TrimSpace(liveType), strings.TrimSpace(col.LogicalType)) &&
		strings.TrimSpace(liveFormulaMode) == "active" &&
		strings.TrimSpace(col.FormulaMode) == "active" &&
		strings.TrimSpace(liveFormula) == strings.TrimSpace(col.Formula) &&
		strings.EqualFold(strings.TrimSpace(liveReturnType), strings.TrimSpace(col.ReturnType)) &&
		sameFormulaStorageTransitionProjectionConfig(col, liveProjectionConfig)
}

func sameFormulaStorageTransitionProjectionConfig(col domainschema.ColumnDefinition, live string) bool {
	desiredCanonical, err := canonicalProjectionJSON(col.ProjectionConfig)
	if err != nil {
		return false
	}
	live = strings.TrimSpace(live)
	if live == "" {
		live = "null"
	}
	liveCanonical, err := canonicalProjectionJSON(json.RawMessage(live))
	if err != nil {
		return false
	}
	return bytes.Equal(desiredCanonical, liveCanonical)
}

func applyObjectSpecFormulaStorageTransitions(
	ctx context.Context,
	db *sql.DB,
	transitions []objectSpecFormulaStorageTransition,
) error {
	if len(transitions) == 0 {
		return nil
	}
	if db == nil {
		return fmt.Errorf("database is unavailable")
	}

	for start := 0; start < len(transitions); {
		end := start + 1
		for end < len(transitions) && transitions[end].objectAPIName == transitions[start].objectAPIName {
			end++
		}
		group := transitions[start:end]
		if err := applyObjectSpecFormulaStorageTransitionGroup(ctx, db, group); err != nil {
			return err
		}
		start = end
	}
	return nil
}

func applyObjectSpecFormulaStorageTransitionGroup(
	ctx context.Context,
	db *sql.DB,
	group []objectSpecFormulaStorageTransition,
) error {
	if len(group) == 0 {
		return nil
	}
	objectAPIName := group[0].objectAPIName
	viewName := group[0].viewName
	setClauses := make([]string, 0, len(group))
	mismatchClauses := make([]string, 0, len(group))
	for _, transition := range group {
		if transition.objectAPIName != objectAPIName || transition.viewName != viewName {
			return fmt.Errorf("formula storage transition group mixes objects or views")
		}
		field := quotePackSQLIdentifier(transition.fieldAPIName)
		setClauses = append(setClauses, fmt.Sprintf("target.%s = computed.%s", field, field))
		mismatchClauses = append(mismatchClauses, fmt.Sprintf("NOT (target.%s <=> computed.%s)", field, field))
	}

	joinTables := fmt.Sprintf(
		"%s AS target JOIN %s AS computed ON target.%s = computed.%s",
		quotePackSQLIdentifier(objectAPIName),
		quotePackSQLIdentifier(viewName),
		quotePackSQLIdentifier("Id"),
		quotePackSQLIdentifier("Id"),
	)
	where := strings.Join(mismatchClauses, " OR ")
	physicalTable := quotePackSQLIdentifier(objectAPIName)
	id := quotePackSQLIdentifier("Id")
	lastID := ""
	var rowsAffected int64
	var batches int
	for {
		boundary, ok, err := nextObjectSpecFormulaStorageTransitionBoundary(
			ctx, db, physicalTable, id, lastID, objectSpecFormulaStorageTransitionBatchRows,
		)
		if err != nil {
			return fmt.Errorf("select formula storage transition boundary for %s: %w", objectAPIName, err)
		}
		if !ok {
			break
		}

		rangeWhere := fmt.Sprintf(
			"target.%s > ? AND target.%s <= ? AND computed.%s > ? AND computed.%s <= ?",
			id, id, id, id,
		)
		updateSQL := "UPDATE " + joinTables + " SET " + strings.Join(setClauses, ", ") +
			" WHERE " + rangeWhere + " AND (" + where + ")"
		result, err := db.ExecContext(ctx, updateSQL, lastID, boundary, lastID, boundary)
		if err != nil {
			return fmt.Errorf("copy authoritative formula projection for %s through %q: %w", objectAPIName, boundary, err)
		}
		changed, err := result.RowsAffected()
		if err == nil {
			rowsAffected += changed
		}

		verifySQL := "SELECT COUNT(*) FROM " + joinTables + " WHERE " + rangeWhere + " AND (" + where + ")"
		var remaining int64
		if err := db.QueryRowContext(
			ctx, verifySQL, lastID, boundary, lastID, boundary,
		).Scan(&remaining); err != nil {
			return fmt.Errorf("verify formula storage transition for %s through %q: %w", objectAPIName, boundary, err)
		}
		if remaining != 0 {
			return fmt.Errorf(
				"formula storage transition for %s through %q left %d mismatching rows",
				objectAPIName, boundary, remaining,
			)
		}
		lastID = boundary
		batches++
	}
	log.Printf(
		"formula storage transition: object=%s fields=%d batches=%d rows_updated=%d",
		objectAPIName, len(group), batches, rowsAffected,
	)
	return nil
}

func nextObjectSpecFormulaStorageTransitionBoundary(
	ctx context.Context,
	db *sql.DB,
	physicalTable string,
	id string,
	after string,
	batchRows int,
) (string, bool, error) {
	if batchRows <= 0 {
		return "", false, fmt.Errorf("batch size must be positive")
	}
	boundarySQL := fmt.Sprintf(
		"SELECT %s FROM %s WHERE %s > ? ORDER BY %s LIMIT ?, 1",
		id, physicalTable, id, id,
	)
	var boundary string
	err := db.QueryRowContext(ctx, boundarySQL, after, batchRows-1).Scan(&boundary)
	if err == nil {
		return boundary, true, nil
	}
	if err != sql.ErrNoRows {
		return "", false, err
	}

	tailSQL := fmt.Sprintf(
		"SELECT %s FROM %s WHERE %s > ? ORDER BY %s DESC LIMIT 1",
		id, physicalTable, id, id,
	)
	err = db.QueryRowContext(ctx, tailSQL, after).Scan(&boundary)
	if err == sql.ErrNoRows {
		return "", false, nil
	}
	if err != nil {
		return "", false, err
	}
	return boundary, true, nil
}