package controlplane

import (
	"database/sql"
	"fmt"
	"log"
	"sort"

	"github.com/nexuscrm/shared/pkg/constants"
)

// VerifyOrphanSystemFields returns a non-nil error when any `_System_Field`
// rows reference an `ObjectId` that has no matching `_System_Object.Id`.
//
// Unlike VerifyStandardFieldSpecDrift (which is non-fatal because drift can
// represent legitimate admin customization), orphan field rows are strictly
// broken state: no legitimate path produces them, and nothing reads them.
// Returning an error here causes `cmd/setup verify` to exit non-zero, which
// triggers the existing `infra/entrypoint.sh` fallback to `cmd/setup apply`,
// which runs `bootstrap.ReconcileOrphanFieldObjectIds` to redirect or delete
// the orphans.
//
// Per-object counts are logged even when the error is returned so operators
// can see the shape of the damage before apply runs.
func VerifyOrphanSystemFields(rawDB *sql.DB) error {
	query := fmt.Sprintf(
		"SELECT f.`%s`, COUNT(*) FROM `%s` f LEFT JOIN `%s` o ON f.`%s` = o.`%s` "+
			"WHERE f.`%s` = 0 AND o.`%s` IS NULL GROUP BY f.`%s`",
		constants.FieldObjectID, constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.ColIsDeleted, constants.ColID,
		constants.FieldObjectID,
	)
	rows, err := rawDB.Query(query)
	if err != nil {
		return fmt.Errorf("query _System_Field for orphans: %w", err)
	}
	defer rows.Close()

	perObject := make(map[string]int)
	totalRows := 0
	for rows.Next() {
		var objectID string
		var n int
		if err := rows.Scan(&objectID, &n); err != nil {
			return fmt.Errorf("scan orphan row: %w", err)
		}
		perObject[objectID] = n
		totalRows += n
	}
	if err := rows.Err(); err != nil {
		return fmt.Errorf("iterate orphan rows: %w", err)
	}
	if len(perObject) == 0 {
		log.Println("✅ no orphan _System_Field rows")
		return nil
	}

	// Log a deterministic ordering so operators get a stable diff between runs.
	objectIDs := make([]string, 0, len(perObject))
	for id := range perObject {
		objectIDs = append(objectIDs, id)
	}
	sort.Strings(objectIDs)
	for _, id := range objectIDs {
		log.Printf("⚠️  orphan field rows: ObjectId=%s count=%d", id, perObject[id])
	}
	return fmt.Errorf("orphan _System_Field rows detected on %d object(s) (%d rows total) — run 'cmd/setup apply' to reconcile", len(perObject), totalRows)
}