// Package tenantoverlay applies tenant metadata from an explicit runtime
// directory. The package contains generic overlay machinery only; tenant facts
// live in files selected by the operator when apply-tenant runs.
package tenantoverlay

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/nexuscrm/backend/internal/bootstrap"
	"github.com/nexuscrm/backend/internal/controlplane/packs"
	"github.com/nexuscrm/backend/internal/controlplane/packspec"
	"github.com/nexuscrm/backend/internal/controlplane/specdrift"
	domainschema "github.com/nexuscrm/backend/internal/domain/schema"
	fileops "github.com/nexuscrm/backend/internal/features/files"
	"github.com/nexuscrm/backend/internal/features/metadata"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

const (
	ObjectsSpecFile           = "tenant_objects_spec.json"
	RecordTypesFile           = "record_types.json"
	AutoNumberFormatsFile     = "autonumber_formats.json"
	PicklistTagsFile          = "picklist_tags.json"
	ActionsFile               = "actions.json"
	PageLayoutOverlaysFile    = "page_layout_overlays.json"
	ObjectFieldMappingsFile   = "object_field_mappings.json"
	ObjectChildCloneRulesFile = "object_child_clone_rules.json"
	HrefFieldsFile            = "href_fields.json"
	RelatedListOverlaysFile   = "related_list_overlays.json"
	FlowsDir                  = "flows"
)

// Pack layers tenant metadata from Dir on top of the platform baseline.
// Platform Core skips any (object, field) declared in
// tenant_objects_spec.json so this package is the sole writer for
// those rows; running mid-apply leaves no half-state a restart can't
// recover from via idempotent upserts. The directory must contain
// tenant_objects_spec.json; every other overlay file is optional and
// is applied only when present.
type Pack struct {
	Dir string
}

func New(dir string) *Pack {
	return &Pack{Dir: dir}
}

func (p *Pack) Name() string { return "tenant-overlay" }

func (p *Pack) Category() packs.Category { return packs.PlatformTenant }

func (p *Pack) path(name string) string {
	return filepath.Join(p.Dir, name)
}

func (p *Pack) read(name string) ([]byte, error) {
	if p == nil || p.Dir == "" {
		return nil, fmt.Errorf("tenant overlay directory is not configured")
	}
	data, err := os.ReadFile(p.path(name))
	if err != nil {
		return nil, fmt.Errorf("read %s: %w", name, err)
	}
	return data, nil
}

func (p *Pack) readOptional(name string) ([]byte, bool, error) {
	if p == nil || p.Dir == "" {
		return nil, false, fmt.Errorf("tenant overlay directory is not configured")
	}
	data, err := os.ReadFile(p.path(name))
	if err != nil {
		if os.IsNotExist(err) {
			return nil, false, nil
		}
		return nil, false, fmt.Errorf("read %s: %w", name, err)
	}
	return data, true, nil
}

func (p *Pack) Apply(ctx context.Context, deps packs.ApplyDeps) error {
	log.Println("📦 Applying tenant overlay...")
	start := time.Now()

	specs, err := p.parseTenantObjectsSpec()
	if err != nil {
		return fmt.Errorf("parse tenant objects spec: %w", err)
	}

	customSpecs := tenantCustomObjectSpecs(specs)
	customDefs := buildTenantColumnDefs(customSpecs, deps.Schema)
	if len(customDefs) > 0 {
		if err := deps.Schema.BatchCreatePhysicalTables(ctx, customDefs); err != nil {
			return fmt.Errorf("create tenant custom object tables: %w", err)
		}
		if objects := buildTenantObjectMetadata(customSpecs); len(objects) > 0 {
			if err := deps.Schema.BatchSaveObjectMetadata(ctx, objects, nil); err != nil {
				return fmt.Errorf("save tenant custom object metadata: %w", err)
			}
		}
	}

	defs := buildTenantColumnDefs(specs, deps.Schema)
	if len(defs) > 0 {
		if err := bootstrap.ReconcileTableColumns(deps.DB, defs); err != nil {
			return fmt.Errorf("reconcile tenant columns: %w", err)
		}
	}

	// Targeted UPDATE (Label only) rather than BatchSaveObjectMetadata
	// so Core's SharingModel / Icon / Description stay intact.
	if err := applyLabelOverrides(ctx, deps.DB, specs); err != nil {
		return fmt.Errorf("apply tenant label overrides: %w", err)
	}

	// Merge tenant picklist tag classifications into the
	// in-memory specs BEFORE BatchSaveFieldMetadata so the tags land
	// on _System_Field.Options via the normal write path. The
	// authoring source (picklist_tags.json) lives under this tenant
	// directory — never in the shared packspec tree — which keeps
	// the platform boundary intact.
	if err := p.applyPicklistTagOverlay(specs); err != nil {
		return fmt.Errorf("apply tenant picklist tag overlay: %w", err)
	}

	if fields := buildTenantFieldMetadata(specs, deps.Schema); len(fields) > 0 {
		if err := deps.Schema.BatchSaveFieldMetadata(ctx, fields, nil); err != nil {
			return fmt.Errorf("save tenant field metadata: %w", err)
		}
	}

	if recordTypesJSON, ok, err := p.readOptional(RecordTypesFile); err != nil {
		return err
	} else if ok {
		if err := packs.ApplyRecordTypes(deps.DB, recordTypesJSON); err != nil {
			return fmt.Errorf("tenant record types: %w", err)
		}
	}

	if err := reconcileTenantManagedRelationships(ctx, deps.DB, specs, defs); err != nil {
		return fmt.Errorf("tenant managed relationships: %w", err)
	}

	if err := p.applyAutoNumberFormats(ctx, deps.DB); err != nil {
		return err
	}

	// Overlays patch _System_Action / _System_PageLayout rows that
	// PlatformUX has already written; they must run after the platform
	// apply pass, not alongside.
	if err := p.applyActionOverlays(ctx, deps.DB); err != nil {
		return err
	}
	if err := p.applyPageLayoutOverlays(ctx, deps.DB); err != nil {
		return fmt.Errorf("tenant page layout overlays: %w", err)
	}
	if err := p.applyRelatedListOverlays(ctx, deps.DB, specs); err != nil {
		return fmt.Errorf("tenant related-list overlays: %w", err)
	}

	// Insert-inheritance + child-clone metadata surfaces. Runs after tenant
	// fields are present on _System_Field so the orchestrator can run
	// cross-schema validators post-cache-refresh.
	//
	// Validators themselves live in controlplane.ApplyTenant, invoked
	// AFTER r.RefreshCache(ctx) — keeping pack.Apply as pure writes +
	// avoiding the stale-cache read that would otherwise treat newly
	// seeded tenant fields as missing (reviewer finding #2).
	if err := p.assertTenantInsertRulesSurfacesLoadable(); err != nil {
		return fmt.Errorf("tenant insert-rule files: %w", err)
	}
	if err := p.applyInsertRules(ctx, deps.DB); err != nil {
		return err
	}

	// Record-triggered flows that express tenant-specific side effects are
	// seeded last, after the schema and field metadata they reference are in
	// place. Definition ownership is guarded by FlowDefinition.IsCustomized.
	if err := p.seedTenantFlows(ctx, deps); err != nil {
		return fmt.Errorf("seed tenant flows: %w", err)
	}

	log.Printf("✅ tenant overlay applied: %d objects (%.1fs)",
		len(specs), time.Since(start).Seconds())
	return nil
}

type autoNumberOverlay struct {
	AutoNumberFormatSeeds []autoNumberFormatSeed `json:"autoNumberFormatSeeds"`
	RecordTypeCodeSeeds   []recordTypeCodeSeed   `json:"recordTypeCodeSeeds"`
}

type autoNumberFormatSeed struct {
	ObjectName    string `json:"objectName"`
	FieldApiName  string `json:"fieldApiName"`
	Format        string `json:"format"`
	Scope         string `json:"scope"`
	Start         int64  `json:"start"`
	LegacySource  string `json:"legacySource"`
	TokenRefsJSON string `json:"tokenRefsJSON,omitempty"`
}

type recordTypeCodeSeed struct {
	Name string `json:"name"`
	Code string `json:"code"`
}

func (p *Pack) applyAutoNumberFormats(ctx context.Context, db *sql.DB) error {
	raw, ok, err := p.readOptional(AutoNumberFormatsFile)
	if err != nil {
		return err
	}
	if !ok {
		return nil
	}
	var overlay autoNumberOverlay
	if err := json.Unmarshal(raw, &overlay); err != nil {
		return fmt.Errorf("parse %s: %w", AutoNumberFormatsFile, err)
	}
	formatSeeds := make([]packs.AutoNumberFormatSeed, 0, len(overlay.AutoNumberFormatSeeds))
	for _, s := range overlay.AutoNumberFormatSeeds {
		formatSeeds = append(formatSeeds, packs.AutoNumberFormatSeed{
			ObjectName:    s.ObjectName,
			FieldApiName:  s.FieldApiName,
			Format:        s.Format,
			Scope:         s.Scope,
			Start:         s.Start,
			LegacySource:  s.LegacySource,
			TokenRefsJSON: s.TokenRefsJSON,
		})
	}
	rtCodeSeeds := make([]packs.RecordTypeCodeSeed, 0, len(overlay.RecordTypeCodeSeeds))
	for _, s := range overlay.RecordTypeCodeSeeds {
		rtCodeSeeds = append(rtCodeSeeds, packs.RecordTypeCodeSeed{Name: s.Name, Code: s.Code})
	}
	if err := packs.ApplyAutoNumberFormats(db, formatSeeds, rtCodeSeeds); err != nil {
		return fmt.Errorf("tenant auto-number formats: %w", err)
	}
	tenantSeeds := make([]bootstrap.AutoNumberFormatSeed, 0, len(formatSeeds))
	for _, s := range formatSeeds {
		tenantSeeds = append(tenantSeeds, bootstrap.AutoNumberFormatSeed{
			ObjectName:   s.ObjectName,
			FieldApiName: s.FieldApiName,
			Format:       s.Format,
			Scope:        s.Scope,
			Start:        s.Start,
			LegacySource: s.LegacySource,
		})
	}
	scopeMap := bootstrap.BuildScopeSourceMapFromSeeds(tenantSeeds)
	if err := bootstrap.SeedAutoNumberCounters(ctx, db, tenantSeeds, scopeMap); err != nil {
		return fmt.Errorf("seed tenant autonumber counters: %w", err)
	}
	return nil
}

// RegisterFieldManifest publishes tenant-owned (object, field) pairs after an
// operator explicitly selected this overlay directory. Default platform paths
// never call this, so they do not learn tenant field identities at init time.
func (p *Pack) RegisterFieldManifest() error {
	specs, err := p.parseTenantObjectsSpec()
	if err != nil {
		return err
	}
	type manifestField struct {
		APIName      string   `json:"apiName"`
		IsCompound   bool     `json:"isCompound,omitempty"`
		ComposedFrom []string `json:"composedFrom,omitempty"`
	}
	type manifestObject struct {
		APIName string          `json:"apiName"`
		Fields  []manifestField `json:"fields"`
	}
	manifest := make([]manifestObject, 0, len(specs))
	for _, spec := range specs {
		obj := manifestObject{APIName: spec.APIName}
		for _, f := range spec.Fields {
			obj.Fields = append(obj.Fields, manifestField{
				APIName:      f.APIName,
				IsCompound:   f.IsCompound,
				ComposedFrom: f.ComposedFrom,
			})
		}
		manifest = append(manifest, obj)
	}
	data, err := json.Marshal(manifest)
	if err != nil {
		return fmt.Errorf("marshal tenant field manifest: %w", err)
	}
	packspec.RegisterTenantFieldManifest(data)
	return nil
}

func (p *Pack) RegisterHrefFields() error {
	raw, ok, err := p.readOptional(HrefFieldsFile)
	if err != nil {
		return err
	}
	if !ok {
		return nil
	}
	var fields []fileops.HrefRewriteField
	if err := json.Unmarshal(raw, &fields); err != nil {
		return fmt.Errorf("parse %s: %w", HrefFieldsFile, err)
	}
	fileops.RegisterHrefFields(fields...)
	return nil
}

// Verify runs drift detection against tenant_objects_spec.json. It is
// invoked by controlplane.ApplyTenant after a successful Apply, and by
// the apply-tenant CLI path — never by platform Verify(). That way
// greenfield boots (which deliberately skip tenant apply) never see a
// "tenant field missing" fatal.
//
// strictMissing is true: on a tenant-enabled deployment, every
// spec-declared field must have a live _System_Field row. A missing
// row means the apply pass failed or was partial and the operator
// must re-run apply-tenant.
//
// The spec bytes passed to specdrift.Verify are NOT the raw
// tenant_objects_spec.json; they are the overlay-merged view. Apply
// runs applyPicklistTagOverlay before writing _System_Field.Options,
// so Verify must run the same merge before reading, or every tenant
// picklist tag would show up as a phantom "live has tags, spec
// doesn't" drift. Keeping Verify symmetric to Apply is the
// R10 shared-state discipline the permission-SSoT incident warned
// against breaking.
func (p *Pack) Verify(_ context.Context, deps packs.VerifyDeps) error {
	mergedSpec, err := p.overlayMergedTenantSpecJSON()
	if err != nil {
		return fmt.Errorf("build overlay-merged tenant spec for verify: %w", err)
	}
	return specdrift.VerifyForTableTypes(
		deps.DB,
		mergedSpec,
		"tenant_objects_spec.json",
		nil,
		true,
		constants.TableTypeStandardObject,
		constants.TableTypeCustomObject,
	)
}

// overlayMergedTenantSpecJSON parses the raw tenant_objects_spec.json,
// applies every tenant authoring overlay (currently just the picklist
// tag overlay), and returns the re-marshalled bytes. specdrift.Verify
// then operates on the same conceptual state Apply landed in
// _System_Field, closing the read/write symmetry.
func (p *Pack) overlayMergedTenantSpecJSON() ([]byte, error) {
	tenantObjectsSpecJSON, err := p.read(ObjectsSpecFile)
	if err != nil {
		return nil, err
	}
	var specs []packs.ObjectSpec
	if err := json.Unmarshal(tenantObjectsSpecJSON, &specs); err != nil {
		return nil, err
	}
	if err := p.applyPicklistTagOverlay(specs); err != nil {
		return nil, err
	}
	return json.Marshal(specs)
}

// ---- Spec Parsing ----

func (p *Pack) parseTenantObjectsSpec() ([]packs.ObjectSpec, error) {
	tenantObjectsSpecJSON, err := p.read(ObjectsSpecFile)
	if err != nil {
		return nil, err
	}
	var specs []packs.ObjectSpec
	if err := json.Unmarshal(tenantObjectsSpecJSON, &specs); err != nil {
		return nil, apperrors.NewInternalError("failed to parse tenant_objects_spec.json", err)
	}
	return specs, nil
}

// ---- Builders (tenant semantics, narrower than Core) ----

func tenantCustomObjectSpecs(specs []packs.ObjectSpec) []packs.ObjectSpec {
	out := make([]packs.ObjectSpec, 0)
	for _, spec := range specs {
		if isTenantCustomObjectSpec(spec) {
			out = append(out, spec)
		}
	}
	return out
}

func isTenantCustomObjectSpec(spec packs.ObjectSpec) bool {
	return strings.EqualFold(spec.TableType, string(constants.TableTypeCustomObject))
}

// buildTenantColumnDefs produces one TableDefinition per object that
// declares at least one field in the tenant spec. Standard-object
// overlays are partial defs (tenant fields only). Tenant-declared
// custom objects are full defs with platform system columns, because
// apply-tenant owns their object/table creation end to end. The
// reconciler remains additive: it creates or adds missing columns, and
// never drops tenant data.
func buildTenantColumnDefs(specs []packs.ObjectSpec, sm *metadata.SchemaManager) []domainschema.TableDefinition {
	defs := make([]domainschema.TableDefinition, 0, len(specs))
	for _, spec := range specs {
		if len(spec.Fields) == 0 {
			continue
		}
		def := domainschema.TableDefinition{
			TableName:              spec.APIName,
			TableType:              spec.TableType,
			Category:               spec.Category,
			Label:                  spec.Label,
			Description:            spec.Description,
			IsManaged:              true,
			SharingModel:           spec.SharingModel,
			EnableHierarchySharing: spec.EnableHierarchySharing,
		}
		if isTenantCustomObjectSpec(spec) {
			def.Columns = append(def.Columns, sm.GetStandardSystemColumns()...)
		}
		for _, f := range spec.Fields {
			sqlType := sm.MapFieldTypeToSQL(f.Type)
			if sqlType == "" || sqlType == "INVALID_TYPE_ERROR" {
				// Skip picklist overlays that reuse an existing string
				// column: MapFieldTypeToSQL("picklist") returns a valid
				// VARCHAR mapping, so this branch only trips for
				// genuinely unknown logical types. Let
				// BatchSaveFieldMetadata surface those as first-class
				// errors.
				continue
			}
			col := domainschema.ColumnDefinition{
				Name:        f.APIName,
				Label:       f.Label,
				Type:        sqlType,
				LogicalType: f.Type,
				Nullable:    !f.Required,
			}
			if len(f.ReferenceTo) > 0 {
				col.ReferenceTo = f.ReferenceTo
			}
			if f.RelationshipName != "" {
				col.RelationshipName = f.RelationshipName
			}
			if f.Default != "" {
				col.Default = "'" + f.Default + "'"
			}
			if f.IsNameField {
				col.IsNameField = true
			}
			if len(f.Options) > 0 {
				col.Options = f.Options
			}
			if f.IsCompound {
				col.IsCompound = true
				col.ComposedFrom = append([]string(nil), f.ComposedFrom...)
			}
			def.Columns = append(def.Columns, col)
		}
		if len(def.Columns) > 0 {
			defs = append(defs, def)
		}
	}
	return defs
}

func buildTenantObjectMetadata(specs []packs.ObjectSpec) []*models.ObjectMetadata {
	objects := make([]*models.ObjectMetadata, 0, len(specs))
	for _, spec := range specs {
		if !isTenantCustomObjectSpec(spec) {
			continue
		}
		label := spec.Label
		if label == "" {
			label = spec.APIName
		}
		pluralLabel := spec.PluralLabel
		if pluralLabel == "" {
			pluralLabel = label + "s"
		}
		desc := spec.Description
		obj := &models.ObjectMetadata{
			ID:                     domainschema.GenerateObjectID(spec.APIName),
			APIName:                spec.APIName,
			Label:                  label,
			PluralLabel:            pluralLabel,
			Description:            &desc,
			TableType:              constants.TableTypeCustomObject,
			IsCustom:               true,
			Searchable:             true,
			SharingModel:           models.SharingModel(spec.SharingModel),
			EnableHierarchySharing: spec.EnableHierarchySharing,
			ListFields:             append([]string(nil), spec.ListFields...),
		}
		if obj.SharingModel == "" {
			obj.SharingModel = models.SharingModel(constants.SharingModelPrivate)
		}
		if spec.PathField != "" {
			pathField := spec.PathField
			obj.PathField = &pathField
		}
		objects = append(objects, obj)
	}
	return objects
}

func reconcileTenantManagedRelationships(ctx context.Context, db *sql.DB, specs []packs.ObjectSpec, defs []domainschema.TableDefinition) error {
	rels := tenantManagedRelationshipSemantics(specs)
	if len(rels) == 0 {
		return nil
	}
	allDefs := make([]domainschema.TableDefinition, 0, len(defs)+len(bootstrap.GetManagedTableDefinitions()))
	allDefs = append(allDefs, defs...)
	allDefs = append(allDefs, bootstrap.GetManagedTableDefinitions()...)
	return bootstrap.ReconcileManagedRelationshipSemanticsWithDefs(ctx, db, rels, allDefs)
}

func tenantManagedRelationshipSemantics(specs []packs.ObjectSpec) []domainschema.ManagedRelationshipSemantics {
	var rels []domainschema.ManagedRelationshipSemantics
	for _, spec := range specs {
		for _, rel := range spec.ManagedRelationships {
			rels = append(rels, domainschema.ManagedRelationshipSemantics{
				ChildObjectAPIName:  spec.APIName,
				ParentObjectAPIName: rel.ParentObject,
				FieldAPIName:        rel.FieldAPIName,
				IsSharingParent:     rel.IsSharingParent,
				CascadeDelete:       rel.CascadeDelete,
			})
		}
	}
	return rels
}

// applyLabelOverrides issues a targeted UPDATE for each tenant-spec
// object that declares a label override. Only Label (and PluralLabel if
// set) is touched — SharingModel / Description / Icon / etc. are
// preserved. The IsCustomized=0 guard ensures admin-renamed objects
// (IsCustomized=1) are not reverted.
func applyLabelOverrides(ctx context.Context, db *sql.DB, specs []packs.ObjectSpec) error {
	for _, spec := range specs {
		if spec.Label == "" {
			continue
		}
		objectID := domainschema.GenerateObjectID(spec.APIName)
		query := fmt.Sprintf(
			"UPDATE `%s` SET `%s` = ?, `%s` = COALESCE(NULLIF(?, ''), `%s`), `%s` = NOW() "+
				"WHERE `%s` = ? AND (`%s` = 0 OR `%s` IS NULL)",
			constants.TableObject,
			constants.FieldSysObject_Label,
			constants.FieldSysObject_PluralLabel, constants.FieldSysObject_PluralLabel,
			constants.FieldLastModifiedDate,
			constants.ColID,
			constants.FieldSysObject_IsCustomized, constants.FieldSysObject_IsCustomized,
		)
		if _, err := db.ExecContext(ctx, query, spec.Label, spec.PluralLabel, objectID); err != nil {
			return fmt.Errorf("update label for %s: %w", spec.APIName, err)
		}
	}
	return nil
}

// buildTenantFieldMetadata constructs FieldWithContext entries for
// every field declared in the tenant spec. For picklist-delta fields
// the upsert overwrites Core's Type/Options/Default/Required on
// non-customized rows; for new fields the upsert inserts a fresh row.
func buildTenantFieldMetadata(specs []packs.ObjectSpec, sm *metadata.SchemaManager) []metadata.FieldWithContext {
	var fields []metadata.FieldWithContext
	stdFields := sm.GetStandardFieldMetadata()
	for _, spec := range specs {
		if len(spec.Fields) == 0 {
			continue
		}
		objectID := domainschema.GenerateObjectID(spec.APIName)
		if isTenantCustomObjectSpec(spec) {
			for _, sf := range stdFields {
				fieldID := domainschema.GenerateFieldID(spec.APIName, sf.APIName)
				sfCopy := sf
				fields = append(fields, metadata.FieldWithContext{
					Field:     &sfCopy,
					TableName: spec.APIName,
					ObjectID:  objectID,
					FieldID:   fieldID,
				})
			}
		}
		for _, fSpec := range spec.Fields {
			field := &models.FieldMetadata{
				APIName:     fSpec.APIName,
				Label:       fSpec.Label,
				Type:        constants.SchemaFieldType(fSpec.Type),
				Required:    fSpec.Required,
				IsNameField: fSpec.IsNameField,
				ReferenceTo: fSpec.ReferenceTo,
				Createable:  true,
				Updateable:  true,
				Options:     fSpec.Options,
			}
			if fSpec.RelationshipName != "" {
				rn := fSpec.RelationshipName
				field.RelationshipName = &rn
			}
			if fSpec.Default != "" {
				def := fSpec.Default
				field.DefaultValue = &def
			}
			if fSpec.IsCompound {
				field.IsCompound = true
				field.ComposedFrom = append([]string(nil), fSpec.ComposedFrom...)
			}

			fieldID := domainschema.GenerateFieldID(spec.APIName, fSpec.APIName)
			sqlType := sm.MapFieldTypeToSQL(fSpec.Type)
			colDef := domainschema.ColumnDefinition{
				Name:        fSpec.APIName,
				Label:       fSpec.Label,
				Type:        sqlType,
				LogicalType: fSpec.Type,
			}
			if len(fSpec.ReferenceTo) > 0 {
				colDef.ReferenceTo = fSpec.ReferenceTo
			}
			if fSpec.RelationshipName != "" {
				colDef.RelationshipName = fSpec.RelationshipName
			}
			if fSpec.IsCompound {
				colDef.IsCompound = true
				colDef.ComposedFrom = append([]string(nil), fSpec.ComposedFrom...)
			}
			fields = append(fields, metadata.FieldWithContext{
				Field:            field,
				TableName:        spec.APIName,
				ObjectID:         objectID,
				FieldID:          fieldID,
				ColumnDefinition: colDef,
			})
		}
	}
	return fields
}