baron-sso/backend/internal/repository/data_integrity_repository.go

package repository

import (
	"baron-sso-backend/internal/domain"
	"context"
	"slices"
	"strings"
	"time"

	"gorm.io/gorm"
)

type DataIntegrityChecker interface {
	CheckDataIntegrity(ctx context.Context) (domain.DataIntegrityReport, error)
	ListOrphanUserLoginIDs(ctx context.Context) ([]domain.OrphanUserLoginID, error)
	DeleteOrphanUserLoginIDs(ctx context.Context, ids []string) (domain.DeleteOrphanUserLoginIDsResult, error)
}

type dataIntegrityChecker struct {
	db *gorm.DB
}

func NewDataIntegrityChecker(db *gorm.DB) DataIntegrityChecker {
	return &dataIntegrityChecker{db: db}
}

func (c *dataIntegrityChecker) CheckDataIntegrity(ctx context.Context) (domain.DataIntegrityReport, error) {
	return CheckDataIntegrity(ctx, c.db)
}

func (c *dataIntegrityChecker) ListOrphanUserLoginIDs(ctx context.Context) ([]domain.OrphanUserLoginID, error) {
	return ListOrphanUserLoginIDs(ctx, c.db, nil)
}

func (c *dataIntegrityChecker) DeleteOrphanUserLoginIDs(ctx context.Context, ids []string) (domain.DeleteOrphanUserLoginIDsResult, error) {
	return DeleteOrphanUserLoginIDs(ctx, c.db, ids)
}

func CheckDataIntegrity(ctx context.Context, db *gorm.DB) (domain.DataIntegrityReport, error) {
	tenantChecks := []domain.DataIntegrityCheck{
		{
			Key:         "duplicate_tenant_slugs",
			Label:       "중복 테넌트 slug",
			Description: "삭제되지 않은 tenant의 slug를 대소문자 무시 기준으로 검사합니다.",
			Severity:    "error",
			Count:       0,
		},
		{
			Key:         "orphan_tenant_parents",
			Label:       "유령 상위 테넌트 참조",
			Description: "tenant.parent_id가 없거나 삭제된 tenant를 참조하는지 검사합니다.",
			Severity:    "error",
			Count:       0,
		},
	}
	userChecks := []domain.DataIntegrityCheck{
		{
			Key:         "orphan_user_tenant_memberships",
			Label:       "유령 테넌트 사용자 소속",
			Description: "users.tenant_id가 없거나 삭제된 tenant를 참조하는지 검사합니다.",
			Severity:    "error",
			Count:       0,
		},
		{
			Key:         "orphan_user_login_id_tenants",
			Label:       "유령 테넌트 로그인 ID",
			Description: "user_login_ids.tenant_id가 없거나 삭제된 tenant를 참조하는지 검사합니다.",
			Severity:    "error",
			Count:       0,
		},
		{
			Key:         "orphan_user_login_id_users",
			Label:       "유령 사용자 로그인 ID",
			Description: "user_login_ids.user_id가 없거나 삭제된 user를 참조하는지 검사합니다.",
			Severity:    "error",
			Count:       0,
		},
	}

	counts := []struct {
		target *int64
		query  string
	}{
		{
			target: &tenantChecks[0].Count,
			query: `
SELECT COUNT(*)
FROM (
	SELECT LOWER(TRIM(slug)) AS normalized_slug
	FROM tenants
	WHERE deleted_at IS NULL
	  AND status <> 'deleted'
	  AND TRIM(slug) <> ''
	GROUP BY LOWER(TRIM(slug))
	HAVING COUNT(*) > 1
) AS duplicate_slugs
`,
		},
		{
			target: &tenantChecks[1].Count,
			query: `
SELECT COUNT(*)
FROM tenants AS child
WHERE child.deleted_at IS NULL
  AND child.parent_id IS NOT NULL
  AND NOT EXISTS (
	SELECT 1
	FROM tenants AS parent
	WHERE parent.id = child.parent_id
	  AND parent.deleted_at IS NULL
  )
`,
		},
		{
			target: &userChecks[0].Count,
			query: `
SELECT COUNT(*)
FROM users AS u
WHERE u.deleted_at IS NULL
  AND u.tenant_id IS NOT NULL
  AND NOT EXISTS (
	SELECT 1
	FROM tenants AS t
	WHERE t.id = u.tenant_id
	  AND t.deleted_at IS NULL
  )
`,
		},
		{
			target: &userChecks[1].Count,
			query: `
SELECT COUNT(*)
FROM user_login_ids AS uli
WHERE NOT EXISTS (
	SELECT 1
	FROM tenants AS t
	WHERE t.id = uli.tenant_id
	  AND t.deleted_at IS NULL
)
`,
		},
		{
			target: &userChecks[2].Count,
			query: `
SELECT COUNT(*)
FROM user_login_ids AS uli
WHERE NOT EXISTS (
	SELECT 1
	FROM users AS u
	WHERE u.id = uli.user_id
	  AND u.deleted_at IS NULL
)
`,
		},
	}

	for _, item := range counts {
		if err := db.WithContext(ctx).Raw(item.query).Scan(item.target).Error; err != nil {
			return domain.DataIntegrityReport{}, err
		}
	}

	tenantChecks = applyIntegrityStatuses(tenantChecks)
	userChecks = applyIntegrityStatuses(userChecks)
	sections := []domain.DataIntegritySection{
		{
			Key:    "tenant_integrity",
			Label:  "테넌트 정합성",
			Status: summarizeIntegrityStatus(tenantChecks),
			Checks: tenantChecks,
		},
		{
			Key:    "user_integrity",
			Label:  "사용자 정합성",
			Status: summarizeIntegrityStatus(userChecks),
			Checks: userChecks,
		},
	}

	summary := domain.DataIntegritySummary{}
	for _, section := range sections {
		for _, check := range section.Checks {
			summary.TotalChecks++
			switch check.Status {
			case domain.DataIntegrityStatusFail:
				summary.Failures += check.Count
			case domain.DataIntegrityStatusWarning:
				summary.Warnings++
			default:
				summary.Passed++
			}
		}
	}

	return domain.DataIntegrityReport{
		Status:    summarizeSectionStatus(sections),
		CheckedAt: time.Now().UTC(),
		Summary:   summary,
		Sections:  sections,
	}, nil
}

func applyIntegrityStatuses(checks []domain.DataIntegrityCheck) []domain.DataIntegrityCheck {
	for i := range checks {
		if checks[i].Count > 0 {
			checks[i].Status = domain.DataIntegrityStatusFail
		} else {
			checks[i].Status = domain.DataIntegrityStatusPass
		}
	}
	return checks
}

func summarizeIntegrityStatus(checks []domain.DataIntegrityCheck) domain.DataIntegrityStatus {
	status := domain.DataIntegrityStatusPass
	for _, check := range checks {
		if check.Status == domain.DataIntegrityStatusFail {
			return domain.DataIntegrityStatusFail
		}
		if check.Status == domain.DataIntegrityStatusWarning {
			status = domain.DataIntegrityStatusWarning
		}
	}
	return status
}

func summarizeSectionStatus(sections []domain.DataIntegritySection) domain.DataIntegrityStatus {
	status := domain.DataIntegrityStatusPass
	for _, section := range sections {
		if section.Status == domain.DataIntegrityStatusFail {
			return domain.DataIntegrityStatusFail
		}
		if section.Status == domain.DataIntegrityStatusWarning {
			status = domain.DataIntegrityStatusWarning
		}
	}
	return status
}

func ListOrphanUserLoginIDs(ctx context.Context, db *gorm.DB, ids []string) ([]domain.OrphanUserLoginID, error) {
	type orphanRow struct {
		ID              string
		UserID          string
		UserEmail       string
		UserDeletedAt   *time.Time
		TenantID        string
		TenantSlug      string
		TenantDeletedAt *time.Time
		FieldKey        string
		LoginID         string
		MissingUser     bool
		DeletedUser     bool
		MissingTenant   bool
		DeletedTenant   bool
	}

	query := `
SELECT
	uli.id,
	uli.user_id,
	COALESCE(u.email, '') AS user_email,
	u.deleted_at AS user_deleted_at,
	uli.tenant_id,
	COALESCE(t.slug, '') AS tenant_slug,
	t.deleted_at AS tenant_deleted_at,
	uli.field_key,
	uli.login_id,
	(u.id IS NULL) AS missing_user,
	(u.id IS NOT NULL AND u.deleted_at IS NOT NULL) AS deleted_user,
	(t.id IS NULL) AS missing_tenant,
	(t.id IS NOT NULL AND t.deleted_at IS NOT NULL) AS deleted_tenant
FROM user_login_ids AS uli
LEFT JOIN users AS u ON u.id = uli.user_id
LEFT JOIN tenants AS t ON t.id = uli.tenant_id
WHERE (
	u.id IS NULL
	OR u.deleted_at IS NOT NULL
	OR t.id IS NULL
	OR t.deleted_at IS NOT NULL
)
`
	args := []any{}
	if len(ids) > 0 {
		query += " AND uli.id IN ?\n"
		args = append(args, ids)
	}
	query += "ORDER BY uli.login_id, uli.id"

	var rows []orphanRow
	if err := db.WithContext(ctx).Raw(query, args...).Scan(&rows).Error; err != nil {
		return nil, err
	}

	items := make([]domain.OrphanUserLoginID, 0, len(rows))
	for _, row := range rows {
		reasons := make([]string, 0, 4)
		if row.MissingUser {
			reasons = append(reasons, "missing_user")
		}
		if row.DeletedUser {
			reasons = append(reasons, "deleted_user")
		}
		if row.MissingTenant {
			reasons = append(reasons, "missing_tenant")
		}
		if row.DeletedTenant {
			reasons = append(reasons, "deleted_tenant")
		}
		items = append(items, domain.OrphanUserLoginID{
			ID:              row.ID,
			UserID:          row.UserID,
			UserEmail:       row.UserEmail,
			UserDeletedAt:   row.UserDeletedAt,
			TenantID:        row.TenantID,
			TenantSlug:      row.TenantSlug,
			TenantDeletedAt: row.TenantDeletedAt,
			FieldKey:        row.FieldKey,
			LoginID:         row.LoginID,
			Reasons:         reasons,
		})
	}
	return items, nil
}

func DeleteOrphanUserLoginIDs(ctx context.Context, db *gorm.DB, ids []string) (domain.DeleteOrphanUserLoginIDsResult, error) {
	ids = normalizeIDList(ids)
	result := domain.DeleteOrphanUserLoginIDsResult{
		Deleted:    []domain.OrphanUserLoginID{},
		SkippedIDs: []string{},
	}
	if len(ids) == 0 {
		return result, nil
	}

	err := db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		items, err := ListOrphanUserLoginIDs(ctx, tx, ids)
		if err != nil {
			return err
		}

		deletableIDs := make([]string, 0, len(items))
		deletableSet := make(map[string]bool, len(items))
		for _, item := range items {
			deletableIDs = append(deletableIDs, item.ID)
			deletableSet[item.ID] = true
		}
		for _, id := range ids {
			if !deletableSet[id] {
				result.SkippedIDs = append(result.SkippedIDs, id)
			}
		}
		if len(deletableIDs) == 0 {
			return nil
		}

		deleteResult := tx.Exec("DELETE FROM user_login_ids WHERE id IN ?", deletableIDs)
		if deleteResult.Error != nil {
			return deleteResult.Error
		}
		result.Deleted = items
		result.DeletedCount = deleteResult.RowsAffected
		return nil
	})
	return result, err
}

func normalizeIDList(ids []string) []string {
	normalized := make([]string, 0, len(ids))
	seen := map[string]bool{}
	for _, id := range ids {
		id = strings.TrimSpace(id)
		if id == "" || seen[id] {
			continue
		}
		seen[id] = true
		normalized = append(normalized, id)
	}
	slices.Sort(normalized)
	return normalized
}