package packs

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

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

// repairManagedSystemFieldColumnsFromLiveMetadata rebuilds missing physical
// columns for still-declared pack-owned managed system fields from live
// _System_Field metadata. It iterates the spec's field declarations,
// identifies managed system fields (non-createable, non-updateable, nullable),
// checks whether their physical columns exist in INFORMATION_SCHEMA, and
// rebuilds any missing ones using BuildManagedFieldColumnDefinition +
// bootstrap.ReconcileTableColumns.
//
// This is the live-metadata physical-column repair path: the authoritative
// source for column definitions is the live _System_Field row, not the pack
// spec-derived table definition. The spec still gates which fields are
// "still declared" (fields removed from the spec are NOT repaired here;
// they remain the stale-field error's responsibility).
//
// Pass 2 is scoped to the pack's ownership boundary: it only queries
// _System_Field rows with OwnerKind IN ('pack', 'pack_retained',
// 'pack_removing') AND OwnerPack = packName, so one pack's apply path
// does not recreate columns for admin-owned or peer-pack fields.
//
// Errors are returned but callers should treat them as non-fatal hints:
// the full apply path's ReconcileTableColumns is the backstop.
func repairManagedSystemFieldColumnsFromLiveMetadata(ctx context.Context, db *sql.DB, packName string, specs []ObjectSpec) error {
	if db == nil {
		return fmt.Errorf("db is unavailable for managed system field column repair")
	}

	existingCols, err := schemaProvisioner.LoadPhysicalColumnShapes(db)
	if err != nil {
		return fmt.Errorf("load physical column shapes for managed field repair: %w", err)
	}

	var repaired []string
	// Pass 1: Check spec-declared managed system fields
	for _, spec := range specs {
		tableCols := existingCols[strings.ToLower(spec.APIName)]
		if len(tableCols) == 0 {
			continue // table doesn't exist yet — full apply path will create it
		}
		for _, field := range spec.Fields {
			if !isManagedSystemFieldSpec(field) {
				continue
			}
			if _, exists := tableCols[strings.ToLower(field.APIName)]; exists {
				continue // physical column already exists
			}
			// Physical column is missing for a still-declared managed system
			// field — rebuild from live _System_Field metadata.
			colDef, err := persistence.BuildManagedFieldColumnDefinition(ctx, db, spec.APIName, field.APIName)
			if err != nil {
				log.Printf("   ⚠️  BuildManagedFieldColumnDefinition for %s.%s: %v", spec.APIName, field.APIName, err)
				continue
			}
			// Add the column via ReconcileTableColumns with a single-column
			// table definition scoped to this object.
			tableDef := domainschema.TableDefinition{
				TableName: spec.APIName,
				Columns:   []domainschema.ColumnDefinition{colDef},
			}
			if err := schemaProvisioner.ReconcileTableColumns(db, []domainschema.TableDefinition{tableDef}); err != nil {
				log.Printf("   ⚠️  ReconcileTableColumns for %s.%s: %v", spec.APIName, field.APIName, err)
				continue
			}
			repaired = append(repaired, fmt.Sprintf("%s.%s", spec.APIName, field.APIName))
		}
	}

	// Pass 2: Check pack-owned managed system fields in _System_Field for
	// these objects, including fields that were in a previous pack version
	// (and thus have _System_Field rows) but are no longer in the current
	// spec. Issue #1392: any pack-owned field in _System_Field with
	// IsDeleted=0 should have a physical column.
	//
	// Scoped to fields owned by this pack (OwnerKind IN pack-family AND
	// OwnerPack = packName) so that one pack's apply path does not repair
	// admin-owned or peer-pack fields on the same object.
	for _, spec := range specs {
		tableCols := existingCols[strings.ToLower(spec.APIName)]
		if len(tableCols) == 0 {
			continue // table doesn't exist yet
		}
		// Query _System_Field for pack-owned managed system fields on this
		// object that belong to the current pack.
		managedFields, err := queryManagedSystemFieldsFromLiveMetadata(db, spec.APIName, packName)
		if err != nil {
			log.Printf("   ⚠️  queryManagedSystemFieldsFromLiveMetadata for %s: %v", spec.APIName, err)
			continue
		}
		for _, fieldAPIName := range managedFields {
			if _, exists := tableCols[strings.ToLower(fieldAPIName)]; exists {
				continue // physical column already exists
			}
			// Check if we already repaired this field in Pass 1
			alreadyRepaired := false
			for _, r := range repaired {
				if r == fmt.Sprintf("%s.%s", spec.APIName, fieldAPIName) {
					alreadyRepaired = true
					break
				}
			}
			if alreadyRepaired {
				continue
			}
			// Physical column is missing for a managed system field in _System_Field
			// — rebuild from live metadata.
			colDef, err := persistence.BuildManagedFieldColumnDefinition(ctx, db, spec.APIName, fieldAPIName)
			if err != nil {
				log.Printf("   ⚠️  BuildManagedFieldColumnDefinition for %s.%s (from _System_Field): %v", spec.APIName, fieldAPIName, err)
				continue
			}
			tableDef := domainschema.TableDefinition{
				TableName: spec.APIName,
				Columns:   []domainschema.ColumnDefinition{colDef},
			}
			if err := schemaProvisioner.ReconcileTableColumns(db, []domainschema.TableDefinition{tableDef}); err != nil {
				log.Printf("   ⚠️  ReconcileTableColumns for %s.%s (from _System_Field): %v", spec.APIName, fieldAPIName, err)
				continue
			}
			repaired = append(repaired, fmt.Sprintf("%s.%s", spec.APIName, fieldAPIName))
		}
	}

	if len(repaired) > 0 {
		log.Printf("   🩹 Rebuilt %d managed system field column(s) from live metadata: %s", len(repaired), strings.Join(repaired, ", "))
	}
	return nil
}

// queryManagedSystemFieldsFromLiveMetadata queries _System_Field for all
// pack-owned managed system fields on the given object that belong to the
// specified pack. A managed system field is one that is:
// - Not deleted (IsDeleted=0)
// - Not createable (Createable=0)
// - Not updateable (Updateable=0)
// - Not required (Required=0, i.e., nullable)
// - Not an active formula backed by a view (FormulaMode != 'active' OR FormulaStorageMode = 'write_materialized')
// - Owned by this pack (OwnerKind IN ('pack', 'pack_retained', 'pack_removing') AND OwnerPack = packName)
//
// The OwnerKind/OwnerPack filter ensures that one pack's repair path does
// not recreate columns for admin-owned or peer-pack fields on the same object.
//
// This is used by the repair path to find fields that were in a previous pack
// version (and thus have _System_Field rows) but are no longer in the current spec.
func queryManagedSystemFieldsFromLiveMetadata(db *sql.DB, objectAPIName string, packName string) ([]string, error) {
	// Query _System_Field joined with _System_Object to find pack-owned managed
	// system fields. The predicate matches persistence.IsManagedSystemField
	// classification:
	// - Createable=0, Updateable=0, Required=0 (nullable)
	// - Not an active formula backed by a view (FormulaMode != 'active' OR has no formula)
	// - OwnerKind IN pack-family AND OwnerPack = packName (pack ownership boundary)
	query := fmt.Sprintf(
		`SELECT f.%s FROM %s f
		 JOIN %s o ON o.%s = f.%s
		 WHERE COALESCE(o.%s, 0) = 0
		   AND COALESCE(f.%s, 0) = 0
		   AND o.%s = ?
		   AND COALESCE(f.%s, 1) = 0
		   AND COALESCE(f.%s, 1) = 0
		   AND COALESCE(f.%s, 0) = 0
		   AND NOT (LOWER(TRIM(COALESCE(f.%s, ''))) = 'active'
		            AND TRIM(COALESCE(f.%s, '')) != ''
		            AND TRIM(COALESCE(f.%s, '')) != 'write_materialized')
		   AND TRIM(COALESCE(f.%s, '')) IN (?, ?, ?)
		   AND TRIM(COALESCE(f.%s, '')) = ?`,
		constants.FieldSysField_APIName, constants.TableField,
		constants.TableObject, constants.FieldSysObject_ID, constants.FieldSysField_ObjectID,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysField_IsDeleted,
		constants.FieldSysObject_APIName,
		constants.FieldSysField_Createable,
		constants.FieldSysField_Updateable,
		constants.FieldSysField_Required,
		constants.FieldSysField_FormulaMode,
		constants.FieldSysField_Formula,
		constants.FieldSysField_FormulaStorageMode,
		constants.FieldSysField_OwnerKind,
		constants.FieldSysField_OwnerPack,
	)

	rows, err := db.Query(query, objectAPIName,
		constants.FieldOwnerKindPack,
		constants.FieldOwnerKindPackRetained,
		constants.FieldOwnerKindPackRemoving,
		packName,
	)
	if err != nil {
		return nil, fmt.Errorf("query managed system fields: %w", err)
	}
	defer rows.Close()

	var fields []string
	for rows.Next() {
		var apiName string
		if err := rows.Scan(&apiName); err != nil {
			continue
		}
		fields = append(fields, apiName)
	}
	return fields, rows.Err()
}

// isManagedSystemFieldSpec classifies a FieldSpec as a managed system field
// using the same source of truth as persistence.IsManagedSystemField:
//
//  1. Resolve the spec-declared Createable/Updateable with the same defaults
//     as buildObjectMetadataWithResolver (nil → true for both).
//  2. Apply the provenance-independent subset of fieldcapabilities.ComputeIntrinsic
//     to match what the persistence path would clamp for this field at
//     _System_Field write time. Pack-declared fields inherently have pack
//     provenance, so the provenance-based rules in ComputeIntrinsic
//     (discovered-source, compound-from-manifest) cannot apply here and are
//     intentionally omitted. The same goes for the platform-field denylist
//     (User-only fields that are not pack-declared).
//  3. Classify via persistence.IsManagedSystemField, the single structural
//     classifier (no object-name or field-name branching in that helper).
//
// This path therefore matches the live-metadata row for pack-declared fields
// whose capabilities were defaulted at authoring time but clamped by
// ComputeIntrinsic at persist time (e.g. LastActivityDate, LastViewedDate).
func isManagedSystemFieldSpec(field FieldSpec) bool {
	// Resolve spec-declared capabilities with the same defaults as
	// buildObjectMetadataWithResolver: nil → true.
	createable := true
	if field.Createable != nil {
		createable = *field.Createable
	}
	updateable := true
	if field.Updateable != nil {
		updateable = *field.Updateable
	}

	// Apply the provenance-independent intrinsic bounds. This is the subset
	// of fieldcapabilities.ComputeIntrinsic that does not consult
	// fieldprovenance (which requires L1 manifests to be installed and
	// would incorrectly classify pack-declared fields as "discovered"
	// when the classifier is not yet initialized). Pack-declared fields
	// have pack provenance by definition, so discovered-source and
	// manifest-compound rules cannot apply.
	intrinsicCreateable, intrinsicUpdateable := packFieldIntrinsicBounds(field)
	createable = createable && intrinsicCreateable
	updateable = updateable && intrinsicUpdateable

	if createable || updateable {
		return false
	}
	if field.Required {
		return false
	}
	// Check formula storage — view-only formulas have no physical column.
	resolved, err := formulastorage.Resolve(formulastorage.Spec{
		FieldType:           field.Type,
		Formula:             field.Formula,
		FormulaMode:         field.FormulaMode,
		FormulaStorageMode:  field.FormulaStorageMode,
		HasFormula:          strings.TrimSpace(field.Formula) != "",
		HasProjectionConfig: field.ProjectionConfig != nil,
	})
	if err == nil && resolved.FormulaBacked && !resolved.RequiresPhysicalColumn() {
		return false
	}
	return true
}

// packFieldIntrinsicBounds applies the provenance-independent subset of
// fieldcapabilities.ComputeIntrinsic to a FieldSpec. Pack-declared fields
// have pack provenance by definition, so the discovered-source and
// manifest-compound rules in ComputeIntrinsic cannot apply here. The
// platform-field denylist only covers User object fields, which are not
// pack-declared, so it is also omitted.
//
// This helper exists so that isManagedSystemFieldSpec and the persistence
// path agree on which fields are intrinsically read-only, without coupling
// to the fieldprovenance lifecycle.
func packFieldIntrinsicBounds(field FieldSpec) (createable bool, updateable bool) {
	// Audit/system fields that are always non-editable (same name set as
	// ComputeIntrinsic Layer 1a).
	switch field.APIName {
	case constants.FieldID,
		constants.FieldCreatedDate, constants.FieldCreatedByID,
		constants.FieldLastModifiedDate, constants.FieldLastModifiedByID,
		constants.FieldSystemModstamp, constants.FieldIsDeleted:
		return false, false
	}

	// Type-based read-only (same set as ComputeIntrinsic Layer 1b).
	normalizedType := constants.NormalizeFieldType(field.Type)
	if strings.TrimSpace(field.Formula) != "" ||
		field.ProjectionConfig != nil ||
		normalizedType == constants.FieldTypeAutonumber ||
		normalizedType == constants.FieldTypeRollupSummary {
		return false, false
	}

	// WriteProtectionPolicy (same as ComputeIntrinsic Layer 1c).
	if strings.TrimSpace(field.WriteProtectionPolicy) != "" {
		return false, false
	}

	// System-maintained date fields (same name set as ComputeIntrinsic Layer 1d).
	switch field.APIName {
	case constants.FieldLastActivityDate, constants.FieldLastViewedDate, constants.FieldLastReferencedDate:
		return false, false
	}

	// Compound fields declared in the pack spec (same predicate as
	// ComputeIntrinsic Layer 1e, but using the spec rather than the
	// manifest-based fieldprovenance.ClassifyCompound).
	if field.IsCompound {
		return false, false
	}

	return true, true
}