package controlplane

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"
	"sync"
	"time"

	"github.com/nexuscrm/analytics"
	"github.com/nexuscrm/db"
	"github.com/nexuscrm/shared/pkg/formulastorage"
	"github.com/pingcap-inc/nexuscrm-controlplane/packs"
)

// deferReplacement mirrors InstallPackOptions.DeferFormulaViewRebuild for the
// pack-scoped materialization path: existing views are left untouched (their
// replacement belongs to the deployment's activation window), and a MISSING
// view fails closed — a cutover release must not introduce pack-owned
// active-formula objects (ship them in a prior regular release), and a
// pre-existing view lost to drift must be healed by a standard apply, never
// recreated with the new compiler while old-semantics serving may exist.
func materializeSourceBackedPackDerived(ctx context.Context, r *Runner, pack *packs.SourceBackedPack, deferReplacement bool) error {
	return materializeSourceBackedPackDerivedWithVerification(ctx, r, pack, deferReplacement, true)
}

func materializeSourceBackedPackDerivedApplyOnly(ctx context.Context, r *Runner, pack *packs.SourceBackedPack, deferReplacement bool) error {
	return materializeSourceBackedPackDerivedWithVerification(ctx, r, pack, deferReplacement, false)
}

func materializeSourceBackedPackDerivedWithVerification(ctx context.Context, r *Runner, pack *packs.SourceBackedPack, deferReplacement bool, verify bool) error {
	if pack == nil {
		return nil
	}
	if r == nil || r.DB == nil || r.SvcMgr == nil || r.SvcMgr.Metadata() == nil {
		return fmt.Errorf("formula view materializer unavailable")
	}
	if err := r.RefreshCache(ctx); err != nil {
		return fmt.Errorf("refresh metadata before formula view materialization: %w", err)
	}
	touchedObjects := pack.OwnedObjectAPINames()
	predicate, args := inPredicate("o.ApiName", touchedObjects)
	formulaRows, err := queryFormulaStorageRows(ctx, r.DB.DB(), predicate, args...)
	if err != nil {
		return fmt.Errorf("load touched active-formula objects for pack %s: %w", pack.Name(), err)
	}
	seen := make(map[string]bool)
	var objects []string
	for _, row := range formulaRows {
		resolved, err := formulastorage.Resolve(formulastorage.Spec{
			FormulaMode:         row.formulaMode,
			FormulaStorageMode:  row.formulaStorageMode,
			Formula:             row.formulaExpr,
			HasProjectionConfig: strings.TrimSpace(row.projectionConfig) != "",
		})
		if err != nil {
			return fmt.Errorf("resolve touched active-formula object %s: %w", row.objectAPIName, err)
		}
		if !resolved.RequiresFormulaView() || seen[row.objectAPIName] {
			continue
		}
		seen[row.objectAPIName] = true
		objects = append(objects, row.objectAPIName)
	}
	if len(objects) == 0 {
		return nil
	}
	// Compile-only/sqlmock callers may provide formula metadata without a schema
	// writer. Real control-plane runners always wire Schema and take the repair
	// path; nil Schema keeps hermetic rebuild tests on the pre-repair contract.
	if verify && r.SvcMgr.Schema() != nil {
		if err := pack.RepairObjectSpecSubstrate(ctx, r.PackDeps(), objects); err != nil {
			return fmt.Errorf("repair object-spec substrate for pack %s before formula view materialization: %w", pack.Name(), err)
		}
		if err := r.RefreshCache(ctx); err != nil {
			return fmt.Errorf("refresh metadata after object-spec substrate repair: %w", err)
		}
	}
	if r.SvcMgr.FormulaView() == nil {
		return fmt.Errorf("formula view materializer unavailable for objects: %s", strings.Join(objects, ", "))
	}
	if err := r.SvcMgr.FormulaView().RefreshRuntimeFunctionRegistry(); err != nil {
		return fmt.Errorf("refresh formula view function registry for pack %s: %w", pack.Name(), err)
	}
	// Order by live-metadata dependency graph. Replaces pack-payload merge logic:
	// pack-payload ordering missed L2-admin-added active formulas that introduce
	// cross-pack-object edges invisible to the pack manifest (R9 BLOCK case).
	// Fails closed on cycles or missing metadata; no lexical / pack-payload
	// fallback per design Forbidden Move #5/#11.
	ordered, err := r.SvcMgr.FormulaView().OrderForBuild(ctx, objects)
	if err != nil {
		return fmt.Errorf("compute formula view build order for pack %s: %w", pack.Name(), err)
	}
	for _, objectAPIName := range ordered {
		if deferReplacement {
			viewName := analytics.ViewName(objectAPIName)
			exists, existsErr := formulaViewExists(ctx, r.DB.DB(), viewName)
			if existsErr != nil {
				return fmt.Errorf("probe formula view %s under deferred replacement: %w", viewName, existsErr)
			}
			if !exists {
				// Fail closed: a missing view on the pack path is either a
				// pack-introduced formula object (a cutover release must not
				// introduce those — ship them in a prior regular release) or
				// a pre-existing view lost to drift/restore; creating either
				// with the new compiler here would hand old-semantics serving
				// a new-semantics view.
				return fmt.Errorf(
					"formula view %s for %s is missing under DeferFormulaViewRebuild; cutover releases must not introduce pack-owned active-formula objects — ship the object in a prior regular release or heal drift with a standard apply",
					viewName, objectAPIName)
			}
			log.Printf("      ⏭️  formula view replacement deferred for %s (DeferFormulaViewRebuild)", objectAPIName)
			continue
		}
		if err := r.SvcMgr.FormulaView().RebuildView(ctx, objectAPIName); err != nil {
			return fmt.Errorf("rebuild formula view for %s: %w", objectAPIName, err)
		}
	}
	if err := r.SvcMgr.FormulaView().RefreshViewCache(ctx); err != nil {
		return fmt.Errorf("refresh formula view cache: %w", err)
	}
	if !verify {
		return nil
	}
	return verifySourceBackedDerivedArtifacts(ctx, r.DB.DB(), pack)
}

func verifySourceBackedDerivedArtifacts(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack) error {
	return verifySourceBackedDerivedArtifactsWithCompileProof(ctx, db, pack, nil)
}

type formulaCompileProofFunc func(context.Context, string) error

func verifySourceBackedDerivedArtifactsWithCompileProof(ctx context.Context, db *sql.DB, pack *packs.SourceBackedPack, compileProof formulaCompileProofFunc) error {
	if err := packs.VerifySourceBackedPackWriteMaterializedFormulaPayload(pack); err != nil {
		return fmt.Errorf("verify write-materialized formulas for pack %s: %w", pack.Name(), err)
	}
	touchedObjects := pack.OwnedObjectAPINames()
	predicate, args := inPredicate("o.ApiName", touchedObjects)
	formulaRows, err := queryFormulaStorageRows(ctx, db, predicate, args...)
	if err != nil {
		return err
	}
	seen := make(map[string]bool)
	var objects []string
	for _, row := range formulaRows {
		resolved, err := formulastorage.Resolve(formulastorage.Spec{
			FormulaMode:         row.formulaMode,
			FormulaStorageMode:  row.formulaStorageMode,
			Formula:             row.formulaExpr,
			HasProjectionConfig: strings.TrimSpace(row.projectionConfig) != "",
		})
		if err != nil {
			return fmt.Errorf("resolve touched active-formula object %s: %w", row.objectAPIName, err)
		}
		if !resolved.RequiresFormulaView() || seen[row.objectAPIName] {
			continue
		}
		seen[row.objectAPIName] = true
		objects = append(objects, row.objectAPIName)
	}
	log.Printf("   ▶ source-backed derived-artifact verify start: pack=%s objects=%d", pack.Name(), len(objects))
	for _, objectAPIName := range objects {
		objectStart := time.Now()
		log.Printf("      ▶ derived-artifact object start: pack=%s object=%s", pack.Name(), objectAPIName)
		viewName := analytics.ViewName(objectAPIName)
		exists, err := formulaViewExists(ctx, db, viewName)
		if err != nil {
			return err
		}
		if !exists {
			return fmt.Errorf("formula view %s for object %s is missing", viewName, objectAPIName)
		}
		// Fail closed when the view exists in the catalog but is not actually
		// readable, such as a SQL SECURITY DEFINER view whose stamped definer no
		// longer exists after a restore or user rotation.
		if err := verifyFormulaViewUsable(ctx, db, viewName); err != nil {
			return fmt.Errorf("formula view %s for object %s exists but is not readable: %w; run 'setup apply' to rebuild views", viewName, objectAPIName, err)
		}
		if compileProof != nil {
			if err := compileProof(ctx, objectAPIName); err != nil {
				return fmt.Errorf("compile active formulas for %s: %w", objectAPIName, err)
			}
		}
		log.Printf("      ✅ derived-artifact object done: pack=%s object=%s (%.1fs)", pack.Name(), objectAPIName, time.Since(objectStart).Seconds())
	}
	log.Printf("   ✅ source-backed derived-artifact verify done: pack=%s objects=%d", pack.Name(), len(objects))
	return nil
}

func formulaViewCompileProofForRunner(r *Runner) formulaCompileProofFunc {
	if r == nil || r.SvcMgr == nil || r.SvcMgr.FormulaView() == nil {
		return func(context.Context, string) error {
			return fmt.Errorf("formula view compiler unavailable")
		}
	}
	return func(ctx context.Context, objectAPIName string) error {
		_, err := r.SvcMgr.FormulaView().PreviewDDL(ctx, objectAPIName)
		return err
	}
}

func formulaViewCompileProofForDB(db *sql.DB) formulaCompileProofFunc {
	return formulaViewCompileProofForDBWithRunnerOptions(db, RunnerOptions{})
}

func formulaViewCompileProofForDBWithRunnerOptions(db *sql.DB, options RunnerOptions) formulaCompileProofFunc {
	var (
		once    sync.Once
		runner  *Runner
		initErr error
	)
	return func(ctx context.Context, objectAPIName string) error {
		once.Do(func() {
			if initErr = registerInstalledRuntimeMetadataForCompileProof(ctx, db, options.ComponentKindRegistrationSet); initErr != nil {
				return
			}
			runner, initErr = newRunnerForFormulaCompileProof(database.NewConnectionFromDB(db), options)
			if initErr != nil {
				return
			}
			if initErr = refreshRuntimeFunctionsForCompileProof(runner); initErr != nil {
				return
			}
			initErr = refreshRunnerCacheForCompileProof(ctx, runner)
		})
		if initErr != nil {
			return initErr
		}
		return formulaViewCompileProofForRunner(runner)(ctx, objectAPIName)
	}
}

type formulaStorageRow struct {
	objectAPIName      string
	formulaMode        string
	formulaStorageMode string
	formulaExpr        string
	projectionConfig   string
}

func queryFormulaStorageRows(ctx context.Context, db *sql.DB, predicate string, args ...interface{}) ([]formulaStorageRow, error) {
	whereExtra := ""
	if strings.TrimSpace(predicate) != "" {
		whereExtra = "\n  AND " + predicate
	}
	rows, err := db.QueryContext(ctx, `
SELECT o.ApiName,
       COALESCE(f.FormulaMode, ''),
       COALESCE(f.FormulaStorageMode, ''),
       COALESCE(f.Formula, ''),
       COALESCE(f.ProjectionConfig, '')
FROM _System_Field f
JOIN _System_Object o ON o.Id = f.ObjectId
WHERE COALESCE(f.IsDeleted, 0) = 0
  AND COALESCE(o.IsDeleted, 0) = 0
`+whereExtra+`
ORDER BY o.ApiName`, args...)
	if err != nil {
		return nil, fmt.Errorf("query formula-storage rows: %w", err)
	}
	defer rows.Close()

	var out []formulaStorageRow
	for rows.Next() {
		var row formulaStorageRow
		if err := rows.Scan(&row.objectAPIName, &row.formulaMode, &row.formulaStorageMode, &row.formulaExpr, &row.projectionConfig); err != nil {
			return nil, fmt.Errorf("scan formula-storage row: %w", err)
		}
		out = append(out, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate formula-storage rows: %w", err)
	}
	return out, nil
}

// verifyActiveFormulaViewsUsable fails closed when ANY object with an active
// formula view - source-backed-pack-owned, admin-created __c, or L2 tenant - has
// a missing or unreadable v_<object>. It is the start-only readiness counterpart
// to apply's RebuildDerived/RebuildAllViews, which CREATE-OR-REPLACEs every such
// view (SQL SECURITY INVOKER) for ALL objects with active computed fields on
// every apply and so heals a dangling-DEFINER view (MySQL 1356) after a restore,
// branch, or DB-user rotation. The per-pack source-backed probe only covers
// pack.OwnedObjectAPINames(); a restart that does not run apply (issue #1590)
// would otherwise pass readiness with a broken non-pack view and then fail at
// query time, because ResolveTableName routes every active-formula object to its
// view and fails closed. This probe is read-only (start must not mutate); the
// recovery is an explicit `setup apply` that rebuilds the views.
func verifyActiveFormulaViewsUsable(ctx context.Context, db *sql.DB) error {
	formulaRows, err := queryFormulaStorageRows(ctx, db, "")
	if err != nil {
		return err
	}
	seen := make(map[string]bool)
	var objects []string
	for _, row := range formulaRows {
		resolved, err := formulastorage.Resolve(formulastorage.Spec{
			FormulaMode:         row.formulaMode,
			FormulaStorageMode:  row.formulaStorageMode,
			Formula:             row.formulaExpr,
			HasProjectionConfig: strings.TrimSpace(row.projectionConfig) != "",
		})
		if err != nil {
			return fmt.Errorf("resolve active-formula object %s: %w", row.objectAPIName, err)
		}
		if !resolved.RequiresFormulaView() || seen[row.objectAPIName] {
			continue
		}
		seen[row.objectAPIName] = true
		objects = append(objects, row.objectAPIName)
	}
	for _, objectAPIName := range objects {
		viewName := analytics.ViewName(objectAPIName)
		exists, err := formulaViewExists(ctx, db, viewName)
		if err != nil {
			return err
		}
		if !exists {
			return fmt.Errorf("formula view %s for object %s is missing; run 'setup apply' to rebuild views", viewName, objectAPIName)
		}
		if err := verifyFormulaViewUsable(ctx, db, viewName); err != nil {
			return fmt.Errorf("formula view %s for object %s exists but is not readable: %w; run 'setup apply' to rebuild views", viewName, objectAPIName, err)
		}
	}
	return nil
}

func formulaViewExists(ctx context.Context, db *sql.DB, viewName string) (bool, error) {
	var count int
	if err := db.QueryRowContext(ctx,
		`SELECT COUNT(*)
		   FROM information_schema.VIEWS
		  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?`,
		viewName,
	).Scan(&count); err != nil {
		return false, fmt.Errorf("query formula view %s: %w", viewName, err)
	}
	return count > 0, nil
}

// verifyFormulaViewUsable fails closed when a formula view exists in the schema
// catalog but cannot actually be read. The canonical case is a SQL SECURITY
// DEFINER view whose stamped definer user no longer exists after a database
// restore or user rotation: existence checks pass, but any SELECT fails with
// MySQL error 1356. The probe is a read-only `SELECT 1 FROM <view> LIMIT 1`, so
// it never mutates (start/restart must not mutate) and is cheap. `setup apply`
// re-creates the view with SQL SECURITY INVOKER (analytics.CreateOrReplaceViewStmt)
// and heals it.
func verifyFormulaViewUsable(ctx context.Context, db *sql.DB, viewName string) error {
	quoted := "`" + strings.ReplaceAll(viewName, "`", "``") + "`"
	rows, err := db.QueryContext(ctx, "SELECT 1 FROM "+quoted+" LIMIT 1")
	if err != nil {
		return err
	}
	return rows.Close()
}