package repository

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"net"
	"reflect"
	"sort"
	"strings"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	coremodel "security-center-backend/internal/model"
	caasmmodel "security-center-backend/internal/modules/caasm/model"
)

type AssetFilter struct {
	Type            caasmmodel.AssetType
	CloudAcctID     uint64
	OwnerEmployeeID *uint64
	OwnerEmail      string
	Environment     string
	Status          caasmmodel.AssetStatus
	Criticality     string
	Provider        caasmmodel.CloudProvider
	Search          string
	Service         string
}

type IPAssetFilter struct {
	Query          string
	IsPublic       *bool
	Provider       caasmmodel.CloudProvider
	Service        string
	CloudAccountID uint64
	Region         string
}

type AssetDetail struct {
	Asset         caasmmodel.Asset
	IPAddresses   []caasmmodel.IPAsset
	DomainAssets  []caasmmodel.DomainAsset
	Relationships []caasmmodel.AssetRelationship
}

type AssetMinimal struct {
	ID       uint64
	NativeID string
}

type AssetRepository interface {
	List(ctx context.Context, filter AssetFilter, page, pageSize int) ([]caasmmodel.Asset, int64, error)
	ListIPAssets(ctx context.Context, filter IPAssetFilter, page, pageSize int) ([]caasmmodel.IPAsset, int64, error)
	FindByID(ctx context.Context, id uint64) (*caasmmodel.Asset, error)
	FindByNativeID(ctx context.Context, cloudAcctID uint64, nativeID string) (*caasmmodel.Asset, error)
	FindMinimalByNativeIDs(ctx context.Context, cloudAcctID uint64, nativeIDs []string) ([]AssetMinimal, error)
	Detail(ctx context.Context, id uint64) (*AssetDetail, error)
	Create(ctx context.Context, asset *caasmmodel.Asset) error
	Update(ctx context.Context, asset *caasmmodel.Asset) error
	// Manual owner fields are updated only through these field-scoped methods.
	UpdateAssetOwner(ctx context.Context, id uint64, ownerEmployeeID *uint64, departmentID *string) error
	UpdateDomainOwner(ctx context.Context, id uint64, ownerEmployeeID *uint64, departmentID *string) error
	Delete(ctx context.Context, id uint64) error
	Save(ctx context.Context, asset *caasmmodel.Asset) error
	// Snapshot writes are sync-only and must never persist manual owner fields.
	SaveAssetSnapshot(ctx context.Context, asset *caasmmodel.Asset) error
	SaveIPAsset(ctx context.Context, ipAsset *caasmmodel.IPAsset) error
	SaveDomainAsset(ctx context.Context, domainAsset *caasmmodel.DomainAsset) error
	// Snapshot writes are sync-only and must never persist manual owner fields.
	SaveDomainAssetSnapshot(ctx context.Context, domainAsset *caasmmodel.DomainAsset) error
	DeletePrivateIPAssetsForCloudAccount(ctx context.Context, cloudAcctID uint64) error
	DeletePrivateDomainAssetsForCloudAccount(ctx context.Context, cloudAcctID uint64) error
	FindIPAsset(ctx context.Context, assetID uint64, ip string) (*caasmmodel.IPAsset, error)
	FindDomainAsset(ctx context.Context, assetID uint64, domain string) (*caasmmodel.DomainAsset, error)
	ListByIDs(ctx context.Context, ids []uint64) ([]caasmmodel.Asset, error)
	ListRelationships(ctx context.Context, filter RelationshipFilter) ([]caasmmodel.AssetRelationship, error)
	SoftDeleteStale(ctx context.Context, cloudAcctID uint64, cutoff time.Time) error
	GetDistinctServices(ctx context.Context) ([]string, error)
	GetDistinctEnvironments(ctx context.Context) ([]string, error)
}

func (r *GormAssetRepository) ListIPAssets(ctx context.Context, filter IPAssetFilter, page, pageSize int) ([]caasmmodel.IPAsset, int64, error) {
	var (
		items []caasmmodel.IPAsset
		total int64
	)
	query := r.db.WithContext(ctx).
		Model(&caasmmodel.IPAsset{}).
		Joins("JOIN assets ON assets.id = ip_assets.asset_id").
		Joins("LEFT JOIN cloud_accounts ON cloud_accounts.id = assets.cloud_account_id").
		Where("ip_assets.deleted_at IS NULL").
		Where("assets.deleted_at IS NULL")
	if filter.Query != "" {
		like := "%" + filter.Query + "%"
		query = query.Where("ip_assets.ip_address LIKE ? OR ip_assets.hostname LIKE ? OR assets.name LIKE ? OR assets.native_id LIKE ?", like, like, like, like)
	}
	if filter.IsPublic != nil {
		query = query.Where("ip_assets.is_public = ?", *filter.IsPublic)
	}
	if filter.Provider != "" {
		query = query.Where("cloud_accounts.provider = ?", filter.Provider)
	}
	if filter.Service != "" {
		query = query.Where("assets.service = ?", filter.Service)
	}
	if filter.CloudAccountID != 0 {
		query = query.Where("assets.cloud_account_id = ?", filter.CloudAccountID)
	}
	if filter.Region != "" {
		query = query.Where("assets.region = ?", filter.Region)
	}
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}
	if err := query.
		Preload("Asset", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "cloud_account_id", "native_id", "name", "asset_type", "service", "status", "environment", "region", "provider", "last_seen_at")
		}).
		Preload("Asset.CloudAccount", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "provider", "status")
		}).
		Order("ip_assets.last_seen_at DESC, ip_assets.updated_at DESC, ip_assets.id DESC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&items).Error; err != nil {
		return nil, 0, err
	}
	return items, total, nil
}

type RelationshipFilter struct {
	SourceAssetID uint64
	TargetAssetID uint64
	Type          string
	// Limit 为 0 表示不限制；>0 时按 id 升序取前 Limit 条（调用方用于控制图规模）。
	Limit int
}

type GormAssetRepository struct {
	db *gorm.DB
}

var (
	ErrAssetCloudAccountIDRequired = errors.New("asset cloud_account_id is required")
	ErrAssetNativeIDRequired       = errors.New("asset native_id is required")
)

const assetMinimalLookupChunkSize = 500

func NewAssetRepository(db *gorm.DB) AssetRepository {
	return &GormAssetRepository{db: db}
}

func normalizeAssetNativeID(nativeID string) (string, error) {
	nativeID = strings.TrimSpace(nativeID)
	if nativeID == "" {
		return "", ErrAssetNativeIDRequired
	}
	return nativeID, nil
}

func requiredAssetCloudAccountID(asset *caasmmodel.Asset) (uint64, error) {
	if asset == nil || asset.CloudAccountID == nil || *asset.CloudAccountID == 0 {
		return 0, ErrAssetCloudAccountIDRequired
	}
	return *asset.CloudAccountID, nil
}

func (r *GormAssetRepository) List(ctx context.Context, filter AssetFilter, page, pageSize int) ([]caasmmodel.Asset, int64, error) {
	var (
		assets []caasmmodel.Asset
		total  int64
	)
	query := r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Where("assets.deleted_at IS NULL").
		Select([]string{
			"assets.id",
			"assets.cloud_account_id",
			"assets.native_id",
			"assets.asset_type",
			"assets.service",
			"assets.name",
			"assets.criticality",
			"assets.status",
			"assets.owner_employee_id",
			"assets.department_id",
			"assets.environment",
			"assets.region",
			"assets.provider",
			"assets.first_seen_at",
			"assets.last_seen_at",
			"assets.created_at",
			"assets.updated_at",
		})
	if filter.Type != "" {
		query = query.Where("assets.asset_type = ?", filter.Type)
	}
	if filter.CloudAcctID != 0 {
		query = query.Where("assets.cloud_account_id = ?", filter.CloudAcctID)
	}
	joinedEmployees := false
	joinEmployees := func() {
		if joinedEmployees {
			return
		}
		query = query.Joins("LEFT JOIN employees ON employees.id = assets.owner_employee_id")
		joinedEmployees = true
	}

	if filter.OwnerEmployeeID != nil {
		query = query.Where("assets.owner_employee_id = ?", *filter.OwnerEmployeeID)
	}
	if filter.OwnerEmail != "" {
		joinEmployees()
		query = query.Where("employees.email = ?", filter.OwnerEmail)
	}
	if filter.Environment != "" {
		// Special handling: "other" means NULL or empty environment
		if filter.Environment == "other" {
			query = query.Where("(assets.environment IS NULL OR assets.environment = '')")
		} else {
			query = query.Where("assets.environment = ?", filter.Environment)
		}
	}
	if filter.Status != "" {
		query = query.Where("assets.status = ?", filter.Status)
	}
	if filter.Criticality != "" {
		query = query.Where("assets.criticality = ?", filter.Criticality)
	}
	if filter.Provider != "" {
		query = query.Joins("LEFT JOIN cloud_accounts ON cloud_accounts.id = assets.cloud_account_id").
			Where("cloud_accounts.provider = ?", filter.Provider)
	}
	if filter.Service != "" {
		query = query.Where("assets.service = ?", filter.Service)
	}
	if filter.Search != "" {
		like := "%" + filter.Search + "%"
		if net.ParseIP(filter.Search) != nil {
			query = query.Where(
				`assets.id IN (
					SELECT ip_assets.asset_id
					FROM ip_assets
					WHERE ip_assets.deleted_at IS NULL
						AND ip_assets.ip_address = ?
				)`,
				filter.Search,
			)
		} else {
			joinEmployees()
			query = query.Where("assets.name LIKE ? OR employees.email LIKE ? OR employees.name LIKE ?", like, like, like)
		}
	}
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, err
	}
	if page <= 0 {
		page = 1
	}
	if pageSize <= 0 {
		pageSize = 20
	}
	if err := query.
		Preload("CloudAccount", func(tx *gorm.DB) *gorm.DB {
			return tx.Select("id", "name", "provider", "status")
		}).
		Preload("OwnerEmployee").
		Preload("Department").
		Preload("IPAssets", func(tx *gorm.DB) *gorm.DB {
			return tx.
				Select("id", "asset_id", "ip_address", "is_public", "last_seen_at").
				Where("deleted_at IS NULL").
				Order("is_public DESC, last_seen_at DESC, id ASC")
		}).
		Order("assets.id ASC").
		Limit(pageSize).
		Offset((page - 1) * pageSize).
		Find(&assets).Error; err != nil {
		return nil, 0, err
	}
	return assets, total, nil
}

func (r *GormAssetRepository) FindByID(ctx context.Context, id uint64) (*caasmmodel.Asset, error) {
	var asset caasmmodel.Asset
	if err := r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Preload("CloudAccount").
		Preload("OwnerEmployee").
		Preload("Department").
		Where("assets.id = ? AND assets.deleted_at IS NULL", id).
		First(&asset).Error; err != nil {
		return nil, err
	}
	return &asset, nil
}

func (r *GormAssetRepository) FindByNativeID(ctx context.Context, cloudAcctID uint64, nativeID string) (*caasmmodel.Asset, error) {
	nativeID, err := normalizeAssetNativeID(nativeID)
	if err != nil {
		return nil, err
	}
	if cloudAcctID == 0 {
		return nil, ErrAssetCloudAccountIDRequired
	}
	var asset caasmmodel.Asset
	if err := r.db.WithContext(ctx).
		Where("cloud_account_id = ? AND native_id = ? AND deleted_at IS NULL", cloudAcctID, nativeID).
		Preload("CloudAccount").
		Preload("OwnerEmployee").
		Preload("Department").
		First(&asset).Error; err != nil {
		return nil, err
	}
	return &asset, nil
}

func (r *GormAssetRepository) FindMinimalByNativeIDs(ctx context.Context, cloudAcctID uint64, nativeIDs []string) ([]AssetMinimal, error) {
	if cloudAcctID == 0 {
		return nil, ErrAssetCloudAccountIDRequired
	}

	normalized := normalizeAssetNativeIDs(nativeIDs)
	if len(normalized) == 0 {
		return []AssetMinimal{}, nil
	}

	results := make([]AssetMinimal, 0, len(normalized))
	for start := 0; start < len(normalized); start += assetMinimalLookupChunkSize {
		end := start + assetMinimalLookupChunkSize
		if end > len(normalized) {
			end = len(normalized)
		}
		var chunk []AssetMinimal
		if err := r.db.WithContext(ctx).
			Model(&caasmmodel.Asset{}).
			Select("id", "native_id").
			Where("cloud_account_id = ? AND native_id IN ?", cloudAcctID, normalized[start:end]).
			Where("deleted_at IS NULL").
			Find(&chunk).Error; err != nil {
			return nil, err
		}
		results = append(results, chunk...)
	}

	return results, nil
}

func (r *GormAssetRepository) Detail(ctx context.Context, id uint64) (*AssetDetail, error) {
	var asset caasmmodel.Asset
	if err := r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Preload("CloudAccount").
		Preload("OwnerEmployee").
		Preload("Department").
		Where("assets.id = ? AND assets.deleted_at IS NULL", id).
		First(&asset).Error; err != nil {
		return nil, err
	}

	var ips []caasmmodel.IPAsset
	if err := r.db.WithContext(ctx).
		Where("asset_id = ?", id).
		Where("deleted_at IS NULL").
		Find(&ips).Error; err != nil {
		return nil, err
	}

	var domains []caasmmodel.DomainAsset
	if err := r.db.WithContext(ctx).
		Preload("Asset").
		Preload("Asset.OwnerEmployee").
		Preload("Asset.Department").
		Preload("OwnerEmployee").
		Preload("Department").
		Where("asset_id = ?", id).
		Where("deleted_at IS NULL").
		Find(&domains).Error; err != nil {
		return nil, err
	}

	var relationships []caasmmodel.AssetRelationship
	if err := r.db.WithContext(ctx).
		Where("source_asset_id = ? OR target_asset_id = ?", id, id).
		Where("deleted_at IS NULL").
		Find(&relationships).Error; err != nil {
		return nil, err
	}

	return &AssetDetail{
		Asset:         asset,
		IPAddresses:   ips,
		DomainAssets:  domains,
		Relationships: relationships,
	}, nil
}

func (r *GormAssetRepository) Create(ctx context.Context, asset *caasmmodel.Asset) error {
	return r.db.WithContext(ctx).Create(asset).Error
}

func (r *GormAssetRepository) Update(ctx context.Context, asset *caasmmodel.Asset) error {
	return r.db.WithContext(ctx).Save(asset).Error
}

func (r *GormAssetRepository) UpdateAssetOwner(ctx context.Context, id uint64, ownerEmployeeID *uint64, departmentID *string) error {
	return r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Where("id = ?", id).
		Updates(map[string]interface{}{
			"owner_employee_id": ownerEmployeeID,
			"department_id":     departmentID,
		}).Error
}

func (r *GormAssetRepository) UpdateDomainOwner(ctx context.Context, id uint64, ownerEmployeeID *uint64, departmentID *string) error {
	return r.db.WithContext(ctx).
		Model(&caasmmodel.DomainAsset{}).
		Where("id = ?", id).
		Updates(map[string]interface{}{
			"owner_employee_id": ownerEmployeeID,
			"department_id":     departmentID,
		}).Error
}

func (r *GormAssetRepository) Delete(ctx context.Context, id uint64) error {
	return r.db.WithContext(ctx).Delete(&caasmmodel.Asset{}, id).Error
}

func (r *GormAssetRepository) Save(ctx context.Context, asset *caasmmodel.Asset) error {
	return r.SaveAssetSnapshot(ctx, asset)
}

func (r *GormAssetRepository) SaveAssetSnapshot(ctx context.Context, asset *caasmmodel.Asset) error {
	if asset == nil {
		return errors.New("asset is nil")
	}

	snapshot := *asset
	snapshot.OwnerEmployeeID = nil
	snapshot.DepartmentID = nil

	cloudAcctID, err := requiredAssetCloudAccountID(&snapshot)
	if err != nil {
		return err
	}

	nativeID, err := normalizeAssetNativeID(snapshot.NativeID)
	if err != nil {
		return err
	}
	snapshot.NativeID = nativeID
	snapshot.ID = 0

	now := time.Now()
	if snapshot.FirstSeenAt == nil {
		snapshot.FirstSeenAt = &now
	}
	snapshot.LastSeenAt = &now
	if snapshot.CreatedAt.IsZero() {
		snapshot.CreatedAt = now
	}
	snapshot.UpdatedAt = now
	snapshot.DeletedAt = nil

	err = r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var existing caasmmodel.Asset
		if err := tx.
			Where("cloud_account_id = ? AND native_id = ? AND deleted_at IS NULL", cloudAcctID, nativeID).
			First(&existing).Error; err == nil {
			if existing.FirstSeenAt != nil {
				snapshot.FirstSeenAt = existing.FirstSeenAt
			}
		} else if errors.Is(err, gorm.ErrRecordNotFound) {
			if err := tombstoneSoftDeletedAssetIdentities(tx, cloudAcctID, nativeID); err != nil {
				return err
			}
		} else {
			return err
		}

		return tx.
			Omit("CloudAccount", "OwnerEmployee", "Department").
			Clauses(clause.OnConflict{
				Columns: []clause.Column{
					{Name: "cloud_account_id"},
					{Name: "native_id"},
				},
				DoUpdates: clause.AssignmentColumns([]string{
					"cloud_account_id",
					"native_id",
					"name",
					"asset_type",
					"service",
					"criticality",
					"status",
					"environment",
					"region",
					"provider",
					"metadata",
					"first_seen_at",
					"last_seen_at",
					"deleted_at",
					"updated_at",
				}),
			}).
			Create(&snapshot).Error
	})
	if err != nil {
		return err
	}

	saved, err := r.findByIdentity(ctx, cloudAcctID, nativeID)
	if err != nil {
		return err
	}
	*asset = *saved
	return nil
}

func (r *GormAssetRepository) findByIdentity(ctx context.Context, cloudAcctID uint64, nativeID string) (*caasmmodel.Asset, error) {
	var asset caasmmodel.Asset
	if err := r.db.WithContext(ctx).
		Where("cloud_account_id = ? AND native_id = ? AND deleted_at IS NULL", cloudAcctID, nativeID).
		First(&asset).Error; err != nil {
		return nil, err
	}
	return &asset, nil
}

func tombstoneSoftDeletedAssetIdentities(tx *gorm.DB, cloudAcctID uint64, nativeID string) error {
	var deleted []caasmmodel.Asset
	if err := tx.Unscoped().
		Where("cloud_account_id = ? AND native_id = ? AND deleted_at IS NOT NULL", cloudAcctID, nativeID).
		Find(&deleted).Error; err != nil {
		return err
	}
	for _, asset := range deleted {
		tombstonedNativeID := assetTombstoneNativeID(nativeID, asset.ID)
		if err := tx.Unscoped().
			Model(&caasmmodel.Asset{}).
			Where("id = ?", asset.ID).
			Update("native_id", tombstonedNativeID).Error; err != nil {
			return err
		}
	}
	return nil
}

func assetTombstoneNativeID(nativeID string, assetID uint64) string {
	suffix := fmt.Sprintf("#deleted#%d", assetID)
	const maxNativeIDLength = 255
	if len(nativeID)+len(suffix) <= maxNativeIDLength {
		return nativeID + suffix
	}
	keep := maxNativeIDLength - len(suffix)
	if keep < 0 {
		keep = 0
	}
	return nativeID[:keep] + suffix
}

func normalizeAssetNativeIDs(nativeIDs []string) []string {
	if len(nativeIDs) == 0 {
		return nil
	}

	seen := make(map[string]struct{}, len(nativeIDs))
	normalized := make([]string, 0, len(nativeIDs))
	for _, nativeID := range nativeIDs {
		trimmed := strings.TrimSpace(nativeID)
		if trimmed == "" {
			continue
		}
		if _, exists := seen[trimmed]; exists {
			continue
		}
		seen[trimmed] = struct{}{}
		normalized = append(normalized, trimmed)
	}
	sort.Strings(normalized)
	return normalized
}

func (r *GormAssetRepository) SaveIPAsset(ctx context.Context, ipAsset *caasmmodel.IPAsset) error {
	if ipAsset == nil {
		return errors.New("caasm repository: nil ip asset")
	}
	if ipAsset.AssetID == 0 {
		return errors.New("caasm repository: ip asset asset_id is required")
	}
	ipAsset.IPAddress = strings.TrimSpace(ipAsset.IPAddress)
	if ipAsset.IPAddress == "" {
		return errors.New("caasm repository: ip asset ip_address is required")
	}
	if ipAsset.FirstSeenAt == nil {
		now := time.Now()
		ipAsset.FirstSeenAt = &now
	}
	if ipAsset.LastSeenAt == nil {
		now := time.Now()
		ipAsset.LastSeenAt = &now
	}
	return r.db.WithContext(ctx).
		Clauses(clause.OnConflict{
			Columns: []clause.Column{{Name: "asset_id"}, {Name: "ip_address"}},
			DoUpdates: clause.Assignments(map[string]interface{}{
				"hostname":      ipAsset.Hostname,
				"geo_location":  ipAsset.GeoLocation,
				"is_public":     ipAsset.IsPublic,
				"first_seen_at": gorm.Expr("COALESCE(first_seen_at, ?)", ipAsset.FirstSeenAt),
				"last_seen_at":  ipAsset.LastSeenAt,
				"deleted_at":    nil,
				"updated_at":    time.Now(),
			}),
		}).
		Omit("Asset").
		Create(ipAsset).Error
}

func (r *GormAssetRepository) DeletePrivateIPAssetsForCloudAccount(ctx context.Context, cloudAcctID uint64) error {
	subquery := r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Select("id").
		Where("cloud_account_id = ?", cloudAcctID)

	return r.db.WithContext(ctx).
		Where("asset_id IN (?) AND is_public = ?", subquery, false).
		Delete(&caasmmodel.IPAsset{}).Error
}

func (r *GormAssetRepository) DeletePrivateDomainAssetsForCloudAccount(ctx context.Context, cloudAcctID uint64) error {
	subquery := r.db.WithContext(ctx).
		Model(&caasmmodel.Asset{}).
		Select("id").
		Where("cloud_account_id = ?", cloudAcctID)

	return r.db.WithContext(ctx).
		Where("asset_id IN (?) AND LOWER(COALESCE(zone_type, '')) NOT IN ?", subquery, []string{"public", "public-and-private"}).
		Delete(&caasmmodel.DomainAsset{}).Error
}

func (r *GormAssetRepository) SaveDomainAsset(ctx context.Context, domainAsset *caasmmodel.DomainAsset) error {
	return r.SaveDomainAssetSnapshot(ctx, domainAsset)
}

func (r *GormAssetRepository) SaveDomainAssetSnapshot(ctx context.Context, domainAsset *caasmmodel.DomainAsset) error {
	if domainAsset == nil {
		return errors.New("domain asset is nil")
	}

	snapshot := *domainAsset
	snapshot.OwnerEmployeeID = nil
	snapshot.DepartmentID = nil
	snapshot.DomainName = strings.TrimSuffix(
		strings.ToLower(strings.TrimSpace(snapshot.DomainName)),
		".",
	)

	now := time.Now()
	if snapshot.FirstSeenAt == nil {
		snapshot.FirstSeenAt = &now
	}
	snapshot.LastSeenAt = &now
	if snapshot.LastSyncedAt == nil {
		snapshot.LastSyncedAt = &now
	}
	if snapshot.CreatedAt.IsZero() {
		snapshot.CreatedAt = now
	}
	snapshot.UpdatedAt = now

	normalizeNames := func(names coremodel.StringArray) []string {
		normalized := make([]string, 0, len(names))
		for _, name := range names {
			if name == "" {
				continue
			}
			normalized = append(normalized, strings.ToLower(strings.TrimSpace(name)))
		}
		sort.Strings(normalized)
		return normalized
	}

	applyNameServersChangeTracking := func(existing *caasmmodel.DomainAsset) error {
		if existing == nil {
			return nil
		}
		nameServersChanged := !reflect.DeepEqual(normalizeNames(existing.NameServers), normalizeNames(snapshot.NameServers))
		if !nameServersChanged {
			snapshot.NameServersLastChangeAt = existing.NameServersLastChangeAt
			snapshot.NameServersLastChangeFrom = existing.NameServersLastChangeFrom
			snapshot.NameServersLastChangeTo = existing.NameServersLastChangeTo
			return nil
		}

		nowUTC := time.Now().UTC()
		snapshot.NameServersLastChangeAt = &nowUTC
		snapshot.NameServersLastChangeFrom = coremodel.RawJSON(`{"name_servers":[]}`)
		snapshot.NameServersLastChangeTo = coremodel.RawJSON(`{"name_servers":[]}`)

		if payload, err := json.Marshal(coremodel.JSONB{"name_servers": existing.NameServers}); err == nil {
			snapshot.NameServersLastChangeFrom = coremodel.RawJSON(payload)
		} else {
			return fmt.Errorf("failed to marshal name_servers_last_change_from payload: %w", err)
		}
		if payload, err := json.Marshal(coremodel.JSONB{"name_servers": snapshot.NameServers}); err == nil {
			snapshot.NameServersLastChangeTo = coremodel.RawJSON(payload)
		} else {
			return fmt.Errorf("failed to marshal name_servers_last_change_to payload: %w", err)
		}
		return nil
	}

	updateExisting := func(tx *gorm.DB, existing *caasmmodel.DomainAsset) error {
		if existing == nil {
			return errors.New("existing domain asset is nil")
		}
		if err := applyNameServersChangeTracking(existing); err != nil {
			return err
		}

		snapshot.ID = existing.ID
		snapshot.CreatedAt = existing.CreatedAt
		if existing.FirstSeenAt != nil {
			snapshot.FirstSeenAt = existing.FirstSeenAt
		}
		snapshot.DeletedAt = nil
		snapshot.UpdatedAt = now

		updates := map[string]interface{}{
			"asset_id":                      snapshot.AssetID,
			"domain_name":                   snapshot.DomainName,
			"domain_class":                  snapshot.DomainClass,
			"registrar":                     snapshot.Registrar,
			"expires_at":                    snapshot.ExpiresAt,
			"zone_id":                       snapshot.ZoneID,
			"zone_type":                     snapshot.ZoneType,
			"status":                        snapshot.Status,
			"auto_renew":                    snapshot.AutoRenew,
			"ttl_default":                   snapshot.TTLDefault,
			"name_servers":                  snapshot.NameServers,
			"name_servers_last_change_at":   snapshot.NameServersLastChangeAt,
			"name_servers_last_change_from": snapshot.NameServersLastChangeFrom,
			"name_servers_last_change_to":   snapshot.NameServersLastChangeTo,
			"forwarding_cfg":                snapshot.ForwardingCfg,
			"peering_cfg":                   snapshot.PeeringCfg,
			"routing_policy":                snapshot.RoutingPolicy,
			"registrant_contact":            snapshot.RegistrantContact,
			"dns_health":                    snapshot.DNSHealth,
			"dns_records":                   snapshot.DNSRecords,
			"last_synced_at":                snapshot.LastSyncedAt,
			"first_seen_at":                 snapshot.FirstSeenAt,
			"last_seen_at":                  snapshot.LastSeenAt,
			"deleted_at":                    snapshot.DeletedAt,
			"updated_at":                    snapshot.UpdatedAt,
		}

		return tx.Unscoped().
			Model(&caasmmodel.DomainAsset{}).
			Where("id = ?", existing.ID).
			Updates(updates).Error
	}

	err := r.db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var existing caasmmodel.DomainAsset
		err := tx.Unscoped().
			Clauses(clause.Locking{Strength: "UPDATE"}).
			Where("asset_id = ? AND domain_name = ? AND domain_class = ?", snapshot.AssetID, snapshot.DomainName, snapshot.DomainClass).
			First(&existing).Error
		if err == nil {
			return updateExisting(tx, &existing)
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}

		snapshot.DeletedAt = nil
		if err := tx.Create(&snapshot).Error; err != nil {
			if !isUniqueConstraintError(err) {
				return fmt.Errorf("failed to create domain asset %s: %w", snapshot.DomainName, err)
			}

			if err := tx.Unscoped().
				Clauses(clause.Locking{Strength: "UPDATE"}).
				Where("asset_id = ? AND domain_name = ? AND domain_class = ?", snapshot.AssetID, snapshot.DomainName, snapshot.DomainClass).
				First(&existing).Error; err != nil {
				return fmt.Errorf("failed to reload concurrent domain asset %s: %w", snapshot.DomainName, err)
			}
			return updateExisting(tx, &existing)
		}

		return nil
	})
	if err != nil {
		return err
	}
	*domainAsset = snapshot
	return nil
}

func (r *GormAssetRepository) FindIPAsset(ctx context.Context, assetID uint64, ip string) (*caasmmodel.IPAsset, error) {
	var ipAsset caasmmodel.IPAsset
	if err := r.db.WithContext(ctx).
		Where("asset_id = ? AND ip_address = ?", assetID, ip).
		First(&ipAsset).Error; err != nil {
		return nil, err
	}
	return &ipAsset, nil
}

func (r *GormAssetRepository) FindDomainAsset(ctx context.Context, assetID uint64, domain string) (*caasmmodel.DomainAsset, error) {
	var domainAsset caasmmodel.DomainAsset
	domain = strings.TrimSuffix(strings.ToLower(strings.TrimSpace(domain)), ".")
	if err := r.db.WithContext(ctx).
		Where("asset_id = ? AND domain_name = ?", assetID, domain).
		First(&domainAsset).Error; err != nil {
		return nil, err
	}
	return &domainAsset, nil
}

func columnNames(cols []clause.Column) []string {
	names := make([]string, 0, len(cols))
	for _, c := range cols {
		names = append(names, c.Name)
	}
	return names
}

func isUniqueConstraintError(err error) bool {
	if err == nil {
		return false
	}
	if errors.Is(err, gorm.ErrDuplicatedKey) {
		return true
	}
	msg := strings.ToLower(err.Error())
	return strings.Contains(msg, "duplicate key") ||
		strings.Contains(msg, "unique constraint failed") ||
		strings.Contains(msg, "duplicate entry")
}

func (r *GormAssetRepository) SoftDeleteStale(ctx context.Context, cloudAcctID uint64, cutoff time.Time) error {
	// Preservation-first: do not delete historical assets; just mark stale ones inactive.
	now := time.Now()
	tx := r.db.WithContext(ctx).Begin()
	assetIDs := tx.Model(&caasmmodel.Asset{}).
		Select("id").
		Where("cloud_account_id = ?", cloudAcctID)

	if err := tx.Model(&caasmmodel.Asset{}).
		Where("cloud_account_id = ? AND last_seen_at < ? AND status <> ?", cloudAcctID, cutoff, caasmmodel.AssetStatusInactive).
		Updates(map[string]interface{}{
			"status":     caasmmodel.AssetStatusInactive,
			"updated_at": now,
		}).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Model(&caasmmodel.DomainAsset{}).
		Where("asset_id IN (?) AND (last_seen_at IS NULL OR last_seen_at < ?)", assetIDs, cutoff).
		Updates(map[string]interface{}{
			"status":     string(caasmmodel.AssetStatusInactive),
			"updated_at": now,
		}).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Model(&caasmmodel.IPAsset{}).
		Where("asset_id IN (?) AND (last_seen_at IS NULL OR last_seen_at < ?)", assetIDs, cutoff).
		Updates(map[string]interface{}{
			"deleted_at": now,
			"updated_at": now,
		}).Error; err != nil {
		tx.Rollback()
		return err
	}

	if err := tx.Model(&caasmmodel.IPPortIndex{}).
		Where("asset_id IN (?) AND (last_seen_at IS NULL OR last_seen_at < ?)", assetIDs, cutoff).
		Updates(map[string]interface{}{
			"deleted_at": now,
			"updated_at": now,
		}).Error; err != nil {
		tx.Rollback()
		return err
	}

	return tx.Commit().Error
}

func (r *GormAssetRepository) ListByIDs(ctx context.Context, ids []uint64) ([]caasmmodel.Asset, error) {
	items := make([]caasmmodel.Asset, 0, len(ids))
	if len(ids) == 0 {
		return items, nil
	}
	err := r.db.WithContext(ctx).
		Select("id", "cloud_account_id", "native_id", "name", "asset_type", "service", "status", "environment", "region", "provider").
		Where("id IN ?", ids).
		Order("id ASC").
		Find(&items).Error
	return items, err
}

func (r *GormAssetRepository) ListRelationships(ctx context.Context, filter RelationshipFilter) ([]caasmmodel.AssetRelationship, error) {
	var items []caasmmodel.AssetRelationship
	query := r.db.WithContext(ctx).Model(&caasmmodel.AssetRelationship{})

	if filter.SourceAssetID != 0 {
		query = query.Where("source_asset_id = ?", filter.SourceAssetID)
	}
	if filter.TargetAssetID != 0 {
		query = query.Where("target_asset_id = ?", filter.TargetAssetID)
	}
	if filter.Type != "" {
		query = query.Where("relationship_type = ?", filter.Type)
	}
	if filter.Limit > 0 {
		query = query.Order("id ASC").Limit(filter.Limit)
	}

	err := query.Find(&items).Error
	return items, err
}

func (r *GormAssetRepository) GetDistinctServices(ctx context.Context) ([]string, error) {
	rawServices := []string{}
	err := r.db.WithContext(ctx).Model(&caasmmodel.Asset{}).
		Where("service IS NOT NULL AND service != ''").
		Distinct("service").
		Order("service").
		Pluck("service", &rawServices).Error
	if err != nil {
		return nil, err
	}

	seen := make(map[string]struct{}, len(rawServices))
	services := make([]string, 0, len(rawServices))
	for _, service := range rawServices {
		normalized := strings.ToLower(strings.TrimSpace(service))
		if normalized == "" {
			continue
		}
		if _, exists := seen[normalized]; exists {
			continue
		}
		seen[normalized] = struct{}{}
		services = append(services, normalized)
	}
	sort.Strings(services)
	return services, nil
}

func (r *GormAssetRepository) GetDistinctEnvironments(ctx context.Context) ([]string, error) {
	environments := []string{} // Initialize to empty slice instead of nil
	err := r.db.WithContext(ctx).Model(&caasmmodel.Asset{}).
		Where("environment IS NOT NULL AND environment != ''").
		Distinct("environment").
		Order("environment").
		Pluck("environment", &environments).Error
	return environments, err
}