package bootstrap

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

	"github.com/nexuscrm/backend/internal/controlplane/packspec"
	domainschema "github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/backend/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/rs/zerolog/log"
)

type managedRelationshipFieldMetadata struct {
	relationshipName string
	relationshipType string
}

func ReconcileManagedRelationshipSemantics(ctx context.Context, db *sql.DB) error {
	// Merge pack spec relationships + substrate relationships (DashboardComponent)
	defs := packspec.StandardRelationshipSemantics()
	defs = append(defs, domainschema.ManagedStandardRelationshipSemanticsList()...)
	return reconcileManagedRelationshipSemantics(ctx, db, defs, GetManagedTableDefinitions())
}

// ReconcileManagedRelationshipSemanticsWithDefs is like ReconcileManagedRelationshipSemantics
// but accepts explicit relationship definitions and table definitions. Used by PlatformCore pack
// which has standard object table defs that GetManagedTableDefinitions() no longer includes.
func ReconcileManagedRelationshipSemanticsWithDefs(ctx context.Context, db *sql.DB, rels []domainschema.ManagedRelationshipSemantics, tableDefs []domainschema.TableDefinition) error {
	return reconcileManagedRelationshipSemantics(ctx, db, rels, tableDefs)
}

func reconcileManagedRelationshipSemantics(
	ctx context.Context,
	db *sql.DB,
	defs []domainschema.ManagedRelationshipSemantics,
	tableDefs []domainschema.TableDefinition,
) error {
	if len(defs) == 0 {
		return nil
	}

	fieldMetadata := buildManagedRelationshipFieldMetadata(tableDefs)
	updated := 0
	inserted := 0

	for _, def := range defs {
		fieldKey := managedRelationshipKey(def.ChildObjectAPIName, def.ParentObjectAPIName, def.FieldAPIName)
		meta, ok := fieldMetadata[fieldKey]
		if !ok {
			return fmt.Errorf("managed relationship metadata missing field definition for %s.%s -> %s", def.ChildObjectAPIName, def.FieldAPIName, def.ParentObjectAPIName)
		}

		exists, err := managedRelationshipExists(ctx, db, def)
		if err != nil {
			return err
		}
		if exists {
			n, err := updateManagedRelationship(ctx, db, def)
			if err != nil {
				return err
			}
			updated += n
			continue
		}

		if err := insertManagedRelationship(ctx, db, def, meta); err != nil {
			return err
		}
		inserted++
	}

	if updated > 0 || inserted > 0 {
		log.Info().
			Int("updated", updated).
			Int("inserted", inserted).
			Msg("✅ Reconciled managed relationship semantics")
	}

	return nil
}

func buildManagedRelationshipFieldMetadata(tableDefs []domainschema.TableDefinition) map[string]managedRelationshipFieldMetadata {
	metadata := make(map[string]managedRelationshipFieldMetadata)
	for _, def := range tableDefs {
		for _, col := range def.Columns {
			if len(col.ReferenceTo) == 0 {
				continue
			}
			for _, parent := range col.ReferenceTo {
				key := managedRelationshipKey(def.TableName, parent, col.Name)
				relationshipName := col.RelationshipName
				if relationshipName == "" && strings.HasSuffix(col.Name, "Id") {
					relationshipName = strings.TrimSuffix(col.Name, "Id")
				}
				relationshipType := string(constants.FieldTypeReference)
				if col.IsMasterDetail || strings.EqualFold(col.LogicalType, string(constants.FieldTypeMasterDetail)) {
					relationshipType = string(constants.FieldTypeMasterDetail)
				}
				metadata[key] = managedRelationshipFieldMetadata{
					relationshipName: relationshipName,
					relationshipType: relationshipType,
				}
			}
		}
	}
	return metadata
}

func managedRelationshipExists(ctx context.Context, db *sql.DB, def domainschema.ManagedRelationshipSemantics) (bool, error) {
	var count int
	err := db.QueryRowContext(ctx, `
		SELECT COUNT(*)
		FROM _System_Relationship
		WHERE LOWER(ChildObjectApiName) = ?
			AND LOWER(ParentObjectApiName) = ?
			AND LOWER(FieldApiName) = ?
	`,
		strings.ToLower(def.ChildObjectAPIName),
		strings.ToLower(def.ParentObjectAPIName),
		strings.ToLower(def.FieldAPIName),
	).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("check managed relationship %s.%s -> %s: %w", def.ChildObjectAPIName, def.FieldAPIName, def.ParentObjectAPIName, err)
	}
	return count > 0, nil
}

func updateManagedRelationship(ctx context.Context, db *sql.DB, def domainschema.ManagedRelationshipSemantics) (int, error) {
	setClauses := make([]string, 0, 3)
	driftClauses := make([]string, 0, 3)
	args := make([]interface{}, 0, 12)
	driftArgs := make([]interface{}, 0, 3)

	if def.IsSharingParent != nil {
		setClauses = append(setClauses, "`IsSharingParent` = ?")
		args = append(args, *def.IsSharingParent)
		driftClauses = append(driftClauses, "COALESCE(`IsSharingParent`, 0) != ?")
		driftArgs = append(driftArgs, *def.IsSharingParent)
	}
	if def.CascadeDelete != nil {
		setClauses = append(setClauses, "`CascadeDelete` = ?")
		args = append(args, *def.CascadeDelete)
		driftClauses = append(driftClauses, "COALESCE(`CascadeDelete`, 0) != ?")
		driftArgs = append(driftArgs, *def.CascadeDelete)
	}
	if def.RestrictedDelete != nil {
		setClauses = append(setClauses, "`RestrictedDelete` = ?")
		args = append(args, *def.RestrictedDelete)
		driftClauses = append(driftClauses, "COALESCE(`RestrictedDelete`, 0) != ?")
		driftArgs = append(driftArgs, *def.RestrictedDelete)
	}
	if len(setClauses) == 0 {
		return 0, nil
	}

	query := fmt.Sprintf(`
		UPDATE _System_Relationship
		SET %s, LastModifiedDate = NOW()
		WHERE LOWER(ChildObjectApiName) = ?
			AND LOWER(ParentObjectApiName) = ?
			AND LOWER(FieldApiName) = ?
			AND (%s)
	`, strings.Join(setClauses, ", "), strings.Join(driftClauses, " OR "))

	args = append(
		args,
		strings.ToLower(def.ChildObjectAPIName),
		strings.ToLower(def.ParentObjectAPIName),
		strings.ToLower(def.FieldAPIName),
	)
	args = append(args, driftArgs...)

	result, err := db.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, fmt.Errorf("update managed relationship %s.%s -> %s: %w", def.ChildObjectAPIName, def.FieldAPIName, def.ParentObjectAPIName, err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, nil
	}
	return int(affected), nil
}

func insertManagedRelationship(
	ctx context.Context,
	db *sql.DB,
	def domainschema.ManagedRelationshipSemantics,
	meta managedRelationshipFieldMetadata,
) error {
	// Derive a default RelatedListLabel from the relationship name
	relatedListLabel := meta.relationshipName
	if relatedListLabel == "" {
		relatedListLabel = def.ChildObjectAPIName
	}

	columns := []string{
		"`Id`",
		"`ChildObjectApiName`",
		"`ParentObjectApiName`",
		"`FieldApiName`",
		"`RelationshipName`",
		"`RelationshipType`",
		"`RelatedListLabel`",
		"`RelatedListFields`",
	}
	values := []interface{}{
		utils.GenerateID(),
		def.ChildObjectAPIName,
		def.ParentObjectAPIName,
		def.FieldAPIName,
		meta.relationshipName,
		meta.relationshipType,
		relatedListLabel,
		"[]", // empty JSON array default
	}

	if def.IsSharingParent != nil {
		columns = append(columns, "`IsSharingParent`")
		values = append(values, *def.IsSharingParent)
	}
	if def.CascadeDelete != nil {
		columns = append(columns, "`CascadeDelete`")
		values = append(values, *def.CascadeDelete)
	}
	if def.RestrictedDelete != nil {
		columns = append(columns, "`RestrictedDelete`")
		values = append(values, *def.RestrictedDelete)
	}

	query := fmt.Sprintf(
		"INSERT INTO `_System_Relationship` (%s, `CreatedDate`, `LastModifiedDate`) VALUES (%s, NOW(), NOW())",
		strings.Join(columns, ", "),
		strings.TrimRight(strings.Repeat("?, ", len(values)), ", "),
	)
	if _, err := db.ExecContext(ctx, query, values...); err != nil {
		return fmt.Errorf("insert managed relationship %s.%s -> %s: %w", def.ChildObjectAPIName, def.FieldAPIName, def.ParentObjectAPIName, err)
	}
	return nil
}

func managedRelationshipKey(childObjectAPIName, parentObjectAPIName, fieldAPIName string) string {
	return strings.ToLower(childObjectAPIName) + "|" + strings.ToLower(parentObjectAPIName) + "|" + strings.ToLower(fieldAPIName)
}