package controlplane

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

	"github.com/nexuscrm/backend/internal/bootstrap"
	"github.com/nexuscrm/backend/internal/controlplane/packs"
	"github.com/nexuscrm/backend/internal/controlplane/tenantoverlay"
	"github.com/nexuscrm/backend/internal/features/automation/autonumber"
	fileops "github.com/nexuscrm/backend/internal/features/files"
	"github.com/nexuscrm/backend/internal/infrastructure/database"
)

const TenantOverlayDirEnv = "NEXUSCRM_TENANT_OVERLAY_DIR"

// ApplyMetadata runs substrate DDL and managed metadata reconciliation.
// This is NOT a pack — it is the prerequisite for all packs.
// Synchronous and deterministic: returns only when all writes are complete.
// Returns a potentially-refreshed DB connection (TiDB requires reconnect after DDL).
//
// Does NOT include repair/backfill operations — those belong to RunRepair.
func ApplyMetadata(ctx context.Context, db *database.TiDBConnection) (*database.TiDBConnection, error) {
	job := &JobResult{Operation: "apply-metadata"}
	start := time.Now()
	log.Println("═══ ApplyMetadata: substrate DDL + managed metadata reconciliation ═══")

	// Schema DDL
	job.trackStep("InitializeSchema", func() error {
		return bootstrap.InitializeSchema(db)
	})
	if job.Steps[0].Error != nil {
		job.Status = "failed"
		job.Duration = time.Since(start)
		return nil, fmt.Errorf("InitializeSchema: %w", job.Steps[0].Error)
	}

	// Reconnect after DDL (TiDB schema cache requires fresh connection)
	if err := db.Close(); err != nil {
		log.Printf("   ⚠️  Failed to close old DB connection: %v", err)
	}
	newDB, err := database.NewConnection()
	if err != nil {
		return nil, fmt.Errorf("re-create database connection after DDL: %w", err)
	}
	log.Println("   🔄 Database connection refreshed after DDL")
	db = newDB

	// Managed metadata reconciliation (persists correct values to DB)
	job.trackStep("ReconcileManagedStandardObjectAccessPolicies", func() error {
		return bootstrap.ReconcileManagedStandardObjectAccessPolicies(db.DB())
	})
	job.trackStep("ReconcileSystemObjectTableTypes", func() error {
		bootstrap.ReconcileSystemObjectTableTypes(ctx, db.DB())
		return nil
	})
	job.trackStep("ReconcileManagedSystemObjectVisibility", func() error {
		return bootstrap.ReconcileManagedSystemObjectVisibility(db.DB(), bootstrap.GetManagedTableDefinitions())
	})
	job.trackStep("EnsureFolderSharingModels", func() error {
		bootstrap.EnsureFolderSharingModels(ctx, db.DB())
		return nil
	})
	// NOTE: ReconcileManagedRelationshipSemantics moved to PlatformCore pack
	// (it needs standard object table defs which only exist after PlatformCore runs)

	job.Status = "completed"
	job.Duration = time.Since(start)
	log.Printf("═══ ApplyMetadata completed (%s) ═══", job.Summary())
	return db, nil
}

// RunRepair executes historical compatibility fixes, discovery, and backfills.
// These are not normal startup operations — they handle drift, legacy data,
// and one-time migrations. Separately invokable and observable.
func RunRepair(ctx context.Context, db *database.TiDBConnection) error {
	job := &JobResult{Operation: "run-repair"}
	start := time.Now()
	log.Println("═══ RunRepair: discovery + backfills + compatibility fixes ═══")

	job.trackStep("DiscoverAndRegisterPhysicalTables", func() error {
		return bootstrap.DiscoverAndRegisterPhysicalTables(db)
	})
	// Backfill legacy PermissionSet bitmap columns (PermissionsX=1) into the SSoT
	// junction table _System_PermissionSetPermission. This repairs SF-migrated
	// profiles where the boolean columns were loaded but the SSoT rows were never
	// populated, causing /permissions/effective to under-report grants.
	//
	// Run early so operators can restore access quickly even if later repair steps
	// are slow on production-sized snapshots.
	job.trackStep("BackfillPermissionSetPermissionSSoT", func() error {
		return packs.BackfillPermissionSetPermissionSSoTFromBooleanColumns(ctx, db.DB())
	})
	// Backfill missing ObjectPermissions / FieldPermissions rows for setup objects
	// (User/Profile/UserRole/Group/etc.) from metadata-declared write gates and the
	// now-restored system-permission SSoT grants. This repairs migrated profiles
	// that regained ManageUsers/ManageRoles/ManageSharing but still lack the
	// setup-object OP/FP rows required to render/edit those forms.
	job.trackStep("BackfillSetupObjectPermissions", func() error {
		return packs.BackfillSetupObjectPermissionsFromSystemPerms(ctx, db.DB())
	})
	// Re-run profile permission convergence in headless control-plane mode so
	// standalone `setup repair` can also heal partially seeded profile-owned
	// business-object permissions. This mirrors the pack-time InitializePermissions
	// logic instead of forcing operators to run the full `setup apply` sequence
	// just to converge historical permission drift.
	job.trackStep("ReconcileProfileBusinessPermissions", func() error {
		r, err := NewRunner(db)
		if err != nil {
			return fmt.Errorf("new runner: %w", err)
		}
		if err := r.RefreshCache(ctx); err != nil {
			return fmt.Errorf("refresh metadata cache: %w", err)
		}
		if err := packs.InitializePermissions(r.SvcMgr.Permissions, r.SvcMgr.Metadata); err != nil {
			return fmt.Errorf("initialize permissions: %w", err)
		}
		return nil
	})
	// Strip DDL single-quote wrapping on legacy `_System_Field.DefaultValue`
	// rows. Idempotent. Issue #1582 RFC v1.5.
	job.trackStep("NormalizeLegacyDDLDefaults", func() error {
		return bootstrap.NormalizeLegacyDDLDefaults(db)
	})
	// ComputeIntrinsic's Layer 1d/1e consult fieldprovenance.ClassifySource
	// / ClassifyCompound at request time (RFC v1.6); the stored Createable
	// column is a refresh-cache of those live computations. BackfillSource
	// and AssertSourceIntegrity were removed when their purpose (maintain
	// and validate a stored Source column) was subsumed by derived-on-read
	// classification — Issue #1582 RFC v1.6.
	job.trackStep("BackfillFieldCapabilities", func() error {
		return bootstrap.BackfillFieldCapabilities(db)
	})
	job.trackStep("BackfillLookupReferences", func() error {
		return bootstrap.BackfillLookupReferences(db)
	})
	job.trackStep("BackfillContentCompatSystemFiles", func() error {
		return bootstrap.BackfillContentCompatSystemFiles(ctx, db)
	})
	// Normalize legacy _System_Object.Id values (disc_<table>, mixed-case obj_<ApiName>_<hash>,
	// and bare UUIDs) to the canonical schema.GenerateObjectID(ApiName) scheme.
	// Must run BEFORE ReconcileOrphanFieldObjectIds so the normalized children
	// remain attached to a valid parent — otherwise the orphan reconciler would
	// see briefly-orphaned rows mid-migration.
	job.trackStep("NormalizeObjectIds", func() error {
		return bootstrap.NormalizeObjectIds(ctx, db.DB())
	})
	job.trackStep("ReconcileOrphanFieldObjectIds", func() error {
		return bootstrap.ReconcileOrphanFieldObjectIds(ctx, db.DB())
	})
	// Heal FlowDefinition rows whose ActiveVersionId points at a non-existent
	// Flow row. Such drift causes every record save that should dispatch the
	// flow to return HTTP 500 ("Flow '<name>' not found or not active") because
	// the runtime dispatcher reads ActiveVersionId verbatim. Issue #2227.
	job.trackStep("ReconcileOrphanFlowDefinitionActiveVersionIds", func() error {
		return bootstrap.ReconcileOrphanFlowDefinitionActiveVersionIds(ctx, db.DB())
	})
	job.trackStep("CleanupExpiredUploadOperations", func() error {
		deleted, err := fileops.CleanupExpiredUploadOperations(ctx, db.DB(), time.Now().UTC())
		if err == nil && deleted > 0 {
			log.Printf("   🧹 CleanupExpiredUploadOperations: deleted %d expired row(s)", deleted)
		}
		return err
	})

	job.Status = "completed"
	job.Duration = time.Since(start)
	log.Printf("═══ RunRepair completed (%s) ═══", job.Summary())
	return nil
}

// EnsureIndexes creates standard indexes on business tables (~900 indexes).
// This is a separate tracked operation — not hidden inside ApplyMetadata.
// Synchronous: returns when all indexes are created. Can be slow.
func EnsureIndexes(_ context.Context, db *database.TiDBConnection) error {
	log.Println("═══ EnsureIndexes: creating standard indexes ═══")
	start := time.Now()
	if err := bootstrap.EnsureStandardIndexes(db); err != nil {
		log.Printf("═══ EnsureIndexes failed: %v (%.1fs) ═══", err, time.Since(start).Seconds())
		return fmt.Errorf("EnsureStandardIndexes: %w", err)
	}
	log.Printf("═══ EnsureIndexes completed (%.1fs) ═══", time.Since(start).Seconds())
	return nil
}

// ApplyPacks applies platform packs in dependency order:
// PlatformPolicy → PlatformConfig → (cache refresh) → PlatformUX → DevSample (if enabled).
func ApplyPacks(ctx context.Context, r *Runner, includeDevSample bool) error {
	log.Println("═══ ApplyPacks ═══")
	start := time.Now()

	for _, pack := range packs.AllPacks(includeDevSample) {
		if err := pack.Apply(ctx, r.PackDeps()); err != nil {
			return fmt.Errorf("pack %s: %w", pack.Name(), err)
		}
		// Inter-pack cache refresh at causal boundaries:
		// - After PlatformCore: standard objects must be visible to PlatformTenant
		// - After PlatformTenant: tenant-overridden picklists/labels must be
		//   visible to PlatformPolicy (which reads field metadata)
		// - After PlatformConfig: layouts/listfields must be visible to PlatformUX
		if pack.Category() == packs.PlatformCore || pack.Category() == packs.PlatformTenant || pack.Category() == packs.PlatformConfig {
			if err := r.RefreshCache(ctx); err != nil {
				return fmt.Errorf("cache refresh after %s failed (downstream packs depend on it): %w", pack.Name(), err)
			}
			// Post-refresh validators for the Quote/Order cutover's new
			// metadata surfaces. No-op when the shipped binary carries
			// zero rules (standard greenfield today); active the moment
			// a future standard pack seeds anything on either surface.
			if pack.Category() == packs.PlatformConfig {
				if err := runInsertRulesValidators(ctx, r); err != nil {
					return fmt.Errorf("insert-rules validation after %s: %w", pack.Name(), err)
				}
			}
		}
	}

	log.Printf("═══ ApplyPacks completed (%.1fs) ═══", time.Since(start).Seconds())
	return nil
}

// ApplyPacksWithLock serializes standalone pack application with the same
// advisory lock used by ApplyAll. This covers cmd/setup apply-packs invocations
// that otherwise bypass the full control-plane lock.
func ApplyPacksWithLock(ctx context.Context, r *Runner, includeDevSample bool) error {
	return withControlPlaneApplyLock(ctx, r.DB.DB(), "ApplyPacks", func() error {
		return ApplyPacks(ctx, r, includeDevSample)
	})
}

// ApplyTenant layers tenant-specific metadata on top of an already-
// applied platform baseline. It is the entry point for
// `cmd/setup apply-tenant` — invoked by deployment tooling (e.g.
// infra/entrypoint.sh) only on deployments that opt in via
// NEXUSCRM_APPLY_TENANT_OVERLAY=1. Greenfield orgs never call this.
func ApplyTenant(ctx context.Context, r *Runner) error {
	return withControlPlaneApplyLock(ctx, r.DB.DB(), "ApplyTenant", func() error {
		log.Println("═══ ApplyTenant: tenant overlay ═══")
		start := time.Now()

		overlayDir := strings.TrimSpace(os.Getenv(TenantOverlayDirEnv))
		if overlayDir == "" {
			return fmt.Errorf("%s must point at the tenant overlay directory", TenantOverlayDirEnv)
		}
		pack := tenantoverlay.New(overlayDir)
		if err := pack.RegisterFieldManifest(); err != nil {
			return fmt.Errorf("tenant field manifest: %w", err)
		}
		if err := pack.RegisterRelatedListRules(); err != nil {
			return fmt.Errorf("tenant related-list rules: %w", err)
		}
		if err := bootstrap.InitFieldProvenance(bootstrap.DefaultPlatformStandardFields()); err != nil {
			return fmt.Errorf("tenant field provenance: %w", err)
		}
		if err := pack.Apply(ctx, r.PackDeps()); err != nil {
			return fmt.Errorf("tenant overlay apply: %w", err)
		}

		if err := r.RefreshCache(ctx); err != nil {
			return fmt.Errorf("tenant post-apply cache refresh: %w", err)
		}

		// Cross-schema validators for the Quote/Order cutover's new
		// metadata surfaces. Runs AFTER RefreshCache so validators
		// see authoritative post-apply state (not a stale cache). A
		// bad tenant rule aborts apply-tenant; the operator fixes the
		// JSON and retries. Plan §6.5 — no warn-only escape hatch on
		// these new surfaces.
		if err := runInsertRulesValidators(ctx, r); err != nil {
			return fmt.Errorf("tenant insert-rules validation: %w", err)
		}

		// Declared-vs-DB closure for the two tenant seed surfaces.
		// Lives HERE (not in runInsertRulesValidators) because its
		// inputs are the selected tenant overlay files —
		// platform boundary: the generic ApplyPacks path must stay
		// tenant-agnostic. Round-7 reviewer finding #1: putting this
		// in a shared validator function leaked tenant-specific reads
		// into greenfield `cmd/setup apply`, which then failed because
		// DB couldn't match tenant overlay seeds that were never
		// applied. This call is reachable only via ApplyTenant, so
		// greenfield never hits it.
		if err := pack.VerifyInsertRulesLanded(ctx, r.DB.DB()); err != nil {
			return fmt.Errorf("tenant insert-rules closure: %w", err)
		}

		// Symmetric to ApplyAll's step-7 verify: the tenant overlay
		// owns its own drift check, so greenfield boots (which never
		// call ApplyTenant) never hit it. strictMissing=true here is
		// correct — on a tenant-enabled deployment every spec-declared
		// field must have landed.
		if err := pack.Verify(ctx, packs.VerifyDeps{DB: r.DB.DB(), DeepChecks: false}); err != nil {
			return fmt.Errorf("tenant overlay verify: %w", err)
		}

		log.Printf("═══ ApplyTenant completed (%.1fs) ═══", time.Since(start).Seconds())
		return nil
	})
}

// runInsertRulesValidators runs the two DB-driven validators for the
// Quote/Order cutover's new metadata surfaces:
//
//   - ValidateInsertInheritFromLookupRules inspects every
//     _System_ObjectFieldMapping row with ActionType='insertInheritFromLookup'
//     and rejects rows whose schema references no longer resolve.
//   - ValidateObjectChildCloneRules walks every ObjectMetadata's
//     ChildCloneRules JSON and rejects rules that point at missing
//     metadata.
//
// Both validators are DB-driven — they read whatever rows currently
// exist and reject the malformed ones. They no-op cleanly on a
// greenfield DB (no rows → no rejections), which is why this function
// is safe to invoke from BOTH the tenant path (ApplyTenant) AND the
// generic platform path (ApplyPacks).
//
// Declared-vs-DB closure (VerifyInsertRulesLanded) is NOT called from
// here. Round-7 reviewer finding #1: that closure reads tenant seed
// files, so invoking it from the shared validator helper leaks tenant-
// specific reads into greenfield `cmd/setup apply`. The platform-boundary
// invariant is that tenant overlay reads live only on the ApplyTenant path.
// The closure call now sits inline in ApplyTenant after this function.
//
// Passes a real engineOwnedByObject function backed by
// sm.AutoNumberMetadata — NOT nil — so the validator's engine-owned check
// at packs/insert_rules_validator.go fires. Shipping nil here would let
// a seed row like Quote.Name → Name pass validation while the runtime
// applier silently drops it (canInheritTarget rejects engine-owned
// targets) — authoring surface inconsistent with runtime, round 2
// reviewer finding #1.
func runInsertRulesValidators(ctx context.Context, r *Runner) error {
	if r == nil || r.SvcMgr == nil || r.SvcMgr.Metadata == nil {
		return nil
	}
	meta := r.SvcMgr.Metadata
	engineOwned := engineOwnedByObjectFromAutoNumber(r.SvcMgr.AutoNumberMetadata)
	if err := packs.ValidateInsertInheritFromLookupRules(ctx, r.DB.DB(), meta, engineOwned); err != nil {
		return err
	}
	if err := packs.ValidateObjectChildCloneRules(ctx, meta, meta.GetSchemas(ctx)); err != nil {
		return err
	}
	return nil
}

// engineOwnedByObjectFromAutoNumber returns a per-object lookup of
// field API names (lower-cased) that the autonumber engine renders —
// exactly the set records.engineOwnedFieldSet produces at insert time.
// Exposed here so the orchestrator's validator call uses the same
// authoritative source as the runtime applier; mismatch would let bad
// metadata pass validation and be silently ignored on insert.
//
// Returns nil when the metadata source is unset (test harnesses) so the
// validator falls back to "no engine-owned fields", which matches the
// runtime applier's nil-guard path.
func engineOwnedByObjectFromAutoNumber(src *autonumber.MetadataSource) func(string) map[string]bool {
	if src == nil {
		return nil
	}
	return func(objectName string) map[string]bool {
		fields := src.LookupByObject(objectName)
		if len(fields) == 0 {
			return nil
		}
		out := make(map[string]bool, len(fields))
		for _, fm := range fields {
			out[strings.ToLower(fm.FieldAPIName)] = true
		}
		return out
	}
}

// RebuildDerived rebuilds derived state from authoritative metadata:
// formula views and the metadata cache.
func RebuildDerived(ctx context.Context, r *Runner) error {
	log.Println("═══ RebuildDerived ═══")
	start := time.Now()

	if r.SvcMgr.FormulaViewSvc != nil {
		if err := r.SvcMgr.FormulaViewSvc.RefreshViewCache(ctx); err != nil {
			log.Printf("   ⚠️  Failed to refresh formula view cache: %v", err)
		}
		// Always rebuild (not just "create if missing") so that existing-but-broken
		// views — e.g. views carried over from a TiDB Serverless branch whose DEFINER
		// user no longer exists on the new cluster — are healed on every apply.
		// RebuildView uses CREATE OR REPLACE and is idempotent.
		if err := r.SvcMgr.FormulaViewSvc.RebuildAllViews(ctx); err != nil {
			log.Printf("   ⚠️  Formula view rebuild errors: %v", err)
		}
	}

	if err := r.RefreshCache(ctx); err != nil {
		return fmt.Errorf("final cache refresh: %w", err)
	}

	log.Printf("═══ RebuildDerived completed (%.1fs) ═══", time.Since(start).Seconds())
	return nil
}

func withControlPlaneApplyLock(ctx context.Context, rawDB *sql.DB, operation string, fn func() error) error {
	lockConn, err := rawDB.Conn(ctx)
	if err != nil {
		return fmt.Errorf("acquiring connection for apply lock: %w", err)
	}
	defer lockConn.Close()

	var lockResult int
	if err := lockConn.QueryRowContext(ctx,
		"SELECT GET_LOCK('nexuscrm_controlplane_apply', 600)",
	).Scan(&lockResult); err != nil || lockResult != 1 {
		return fmt.Errorf("acquiring control-plane apply lock (result=%d): %w", lockResult, err)
	}
	defer func() { _, _ = lockConn.ExecContext(ctx, "SELECT RELEASE_LOCK('nexuscrm_controlplane_apply')") }()
	log.Printf("🔒 Acquired control-plane apply lock for %s", operation)

	return fn()
}

// ApplyAll runs the complete control-plane sequence:
// metadata → repair → runner init → packs → rebuild → indexes → verify.
// Holds a MySQL advisory lock for the entire duration to prevent concurrent
// multi-pod executions from racing on pack apply.
func ApplyAll(ctx context.Context, db *database.TiDBConnection) error {
	log.Println("══════════════════════════════════════════════════════════════")
	log.Println("  Control Plane: ApplyAll")
	log.Println("══════════════════════════════════════════════════════════════")
	start := time.Now()

	return withControlPlaneApplyLock(ctx, db.DB(), "ApplyAll", func() error {
		// 1. Substrate + managed metadata
		newDB, err := ApplyMetadata(ctx, db)
		if err != nil {
			return fmt.Errorf("apply-metadata: %w", err)
		}

		// 2. Repair: discovery + backfills
		if err := RunRepair(ctx, newDB); err != nil {
			return fmt.Errorf("run-repair: %w", err)
		}

		// 3. Build headless service graph
		r, err := NewRunner(newDB)
		if err != nil {
			return fmt.Errorf("runner init: %w", err)
		}
		if err := r.RefreshCache(ctx); err != nil {
			return fmt.Errorf("initial cache refresh: %w", err)
		}

		// 4. Apply packs (include dev-sample in non-production)
		includeDevSample := !isProduction()
		if err := ApplyPacks(ctx, r, includeDevSample); err != nil {
			return fmt.Errorf("apply-packs: %w", err)
		}

		// 5. Rebuild derived state
		if err := RebuildDerived(ctx, r); err != nil {
			return fmt.Errorf("rebuild-derived: %w", err)
		}

		// 6. Standard indexes (synchronous, fatal — consistent with standalone ensure-indexes command)
		if err := EnsureIndexes(ctx, newDB); err != nil {
			return fmt.Errorf("ensure-indexes: %w", err)
		}

		// 7. Verify
		if err := Verify(ctx, newDB); err != nil {
			return fmt.Errorf("verify: %w", err)
		}

		log.Printf("══════════════════════════════════════════════════════════════")
		log.Printf("  Control Plane: ApplyAll completed (%.1fs)", time.Since(start).Seconds())
		log.Printf("══════════════════════════════════════════════════════════════")
		return nil
	})
}

func isProduction() bool {
	env := strings.ToLower(strings.TrimSpace(os.Getenv("NEXUS_ENV")))
	return env == "production" || env == "prod" || env == "staging"
}