package controlplane

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

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

// ReconcileOrphanFieldObjectIds heals `_System_Field` rows whose `ObjectId`
// has no matching `_System_Object.Id`. Such rows are the lingering residue
// of an earlier bug where pack apply generated `obj_<ApiName>_<hash>` IDs
// that collided with discovery's `disc_<table>` rows on the case-insensitive
// UNIQUE(ApiName) and left the field FK pointing at a never-materialized Id.
//
// For each orphan row we:
//  1. Recover the intended APIName from the orphan ObjectId
//     (format: `obj_<ApiName>_<32-char hex hash>`).
//  2. Look up the canonical `_System_Object` row by LOWER(ApiName).
//  3. If a canonical field row already exists for the same ApiName, DELETE
//     the orphan (canonical wins; orphan was never readable).
//     Otherwise UPDATE the orphan's ObjectId to the canonical Id (redirect).
//
// When recovery fails (the ObjectId doesn't match the obj_<...>_<hash>
// pattern, e.g. legacy UUID-style Ids) OR the canonical _System_Object row
// no longer exists (object was hard-deleted), the orphan field rows are
// definitionally dead — nothing reads them and there is no way to attribute
// them to a live owner. They are HARD-DELETED so readiness verify stops
// failing after this explicit repair path runs.
//
// Idempotent: a second run finds no orphans and is a no-op.
//
// Intended to run inside `RunRepair` (before pack apply). Once
// `GetOrCreateObjectID` is adopted on every pack/bootstrap write path,
// the loop will find zero rows on healthy tenants.
func ReconcileOrphanFieldObjectIds(ctx context.Context, db *sql.DB) error {
	orphanObjectIDs, err := listOrphanFieldObjectIDs(ctx, db)
	if err != nil {
		return fmt.Errorf("list orphan field ObjectIds: %w", err)
	}
	if len(orphanObjectIDs) == 0 {
		log.Println("   ✅ ReconcileOrphanFieldObjectIds: no orphan field rows found")
		return nil
	}

	totalRedirected := 0
	totalDeleted := 0
	totalDeadDropped := 0
	for _, orphanID := range orphanObjectIDs {
		apiName, ok := recoverAPINameFromOrphanID(orphanID)
		var canonicalID string
		if ok {
			id, err := lookupCanonicalObjectID(ctx, db, apiName)
			if err != nil {
				return fmt.Errorf("lookup canonical for orphan %q (apiName=%q): %w", orphanID, apiName, err)
			}
			canonicalID = id
		}
		if canonicalID == "" {
			// No canonical owner — either the ID format is unrecoverable or the
			// object was hard-deleted. Either way the field rows are dead. Drop
			// them so readiness verify stops failing after the operator-driven
			// reconcile completes.
			n, err := deleteAllFieldRowsForObject(ctx, db, orphanID)
			if err != nil {
				return fmt.Errorf("hard-delete dead orphan rows for %q: %w", orphanID, err)
			}
			totalDeadDropped += n
			log.Printf("   🗑  orphan %s: no canonical owner — hard-deleted %d dead field row(s)", orphanID, n)
			continue
		}
		redirected, deleted, err := reconcileFieldRowsForOrphan(ctx, db, orphanID, canonicalID)
		if err != nil {
			return fmt.Errorf("reconcile orphan %q → canonical %q: %w", orphanID, canonicalID, err)
		}
		totalRedirected += redirected
		totalDeleted += deleted
		log.Printf("   🔧 orphan %s → %s: redirected=%d deleted=%d", orphanID, canonicalID, redirected, deleted)
	}
	log.Printf("   ✅ ReconcileOrphanFieldObjectIds: %d redirected, %d deleted, %d dead-dropped across %d orphan object(s)",
		totalRedirected, totalDeleted, totalDeadDropped, len(orphanObjectIDs))
	return nil
}

// deleteAllFieldRowsForObject hard-deletes every _System_Field row keyed to
// the given (dead) ObjectId. Returns the number of rows deleted.
func deleteAllFieldRowsForObject(ctx context.Context, db *sql.DB, objectID string) (int, error) {
	deleteSQL := fmt.Sprintf(
		"DELETE FROM `%s` WHERE `%s` = ?",
		constants.TableField, constants.FieldObjectID,
	)
	res, err := db.ExecContext(ctx, deleteSQL, objectID)
	if err != nil {
		return 0, err
	}
	n, _ := res.RowsAffected()
	return int(n), nil
}

// listOrphanFieldObjectIDs returns the distinct ObjectIds in _System_Field
// that have no matching _System_Object.Id (active rows only).
func listOrphanFieldObjectIDs(ctx context.Context, db *sql.DB) ([]string, error) {
	query := fmt.Sprintf(
		"SELECT DISTINCT f.`%s` FROM `%s` f LEFT JOIN `%s` o ON f.`%s` = o.`%s` "+
			"WHERE f.`%s` = 0 AND o.`%s` IS NULL",
		constants.FieldObjectID, constants.TableField, constants.TableObject,
		constants.FieldObjectID, constants.ColID,
		constants.ColIsDeleted, constants.ColID,
	)
	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids = append(ids, id)
	}
	return ids, rows.Err()
}

// recoverAPINameFromOrphanID extracts the APIName embedded in an orphan
// ObjectId of the form `obj_<ApiName>_<32-hex>`. Returns false for any
// other shape (e.g. a UUID that was hand-inserted, or a `disc_*` prefix
// that shouldn't be orphaned in practice).
func recoverAPINameFromOrphanID(id string) (string, bool) {
	if !strings.HasPrefix(id, constants.PrefixObject) {
		return "", false
	}
	remainder := strings.TrimPrefix(id, constants.PrefixObject)
	// GenerateObjectID appends `_<32 hex chars>`. The ApiName itself may
	// contain underscores (rare for standard SF objects but custom objects
	// can), so we cut from the RIGHT: the suffix is always exactly one
	// leading underscore + 32 hex chars.
	if len(remainder) < 33 || remainder[len(remainder)-33] != '_' {
		return "", false
	}
	apiName := remainder[:len(remainder)-33]
	if apiName == "" {
		return "", false
	}
	return apiName, true
}

// lookupCanonicalObjectID returns the canonical _System_Object.Id for the
// given APIName using a case-insensitive match (_System_Object.ApiName's
// UNIQUE is utf8mb4_unicode_ci, so `Order`/`order` are the same row).
func lookupCanonicalObjectID(ctx context.Context, db *sql.DB, apiName string) (string, error) {
	query := fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE LOWER(`%s`) = LOWER(?) AND `%s` = 0 LIMIT 1",
		constants.ColID, constants.TableObject, constants.FieldAPIName, constants.ColIsDeleted,
	)
	var id string
	err := db.QueryRowContext(ctx, query, apiName).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

// reconcileFieldRowsForOrphan walks every active _System_Field row with
// ObjectId = orphanID. For each one, if a canonical row already exists at
// (canonicalID, ApiName) the orphan is deleted; otherwise the orphan is
// redirected via UPDATE ObjectId. Returns (redirected, deleted).
func reconcileFieldRowsForOrphan(ctx context.Context, db *sql.DB, orphanID, canonicalID string) (int, int, error) {
	selectSQL := fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0",
		constants.ColID, constants.FieldAPIName, constants.TableField,
		constants.FieldObjectID, constants.ColIsDeleted,
	)
	rows, err := db.QueryContext(ctx, selectSQL, orphanID)
	if err != nil {
		return 0, 0, err
	}
	type orphanFieldRow struct{ id, apiName string }
	var orphanRows []orphanFieldRow
	for rows.Next() {
		var r orphanFieldRow
		if err := rows.Scan(&r.id, &r.apiName); err != nil {
			rows.Close()
			return 0, 0, err
		}
		orphanRows = append(orphanRows, r)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return 0, 0, err
	}

	existsSQL := fmt.Sprintf(
		"SELECT 1 FROM `%s` WHERE `%s` = ? AND `%s` = ? AND `%s` = 0 LIMIT 1",
		constants.TableField, constants.FieldObjectID, constants.FieldAPIName, constants.ColIsDeleted,
	)
	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = ? WHERE `%s` = ?",
		constants.TableField, constants.FieldObjectID, constants.ColID,
	)
	deleteSQL := fmt.Sprintf(
		"DELETE FROM `%s` WHERE `%s` = ?",
		constants.TableField, constants.ColID,
	)

	var redirected, deleted int
	for _, orphan := range orphanRows {
		var one int
		err := db.QueryRowContext(ctx, existsSQL, canonicalID, orphan.apiName).Scan(&one)
		switch err {
		case nil:
			if _, err := db.ExecContext(ctx, deleteSQL, orphan.id); err != nil {
				return redirected, deleted, fmt.Errorf("delete orphan field %q: %w", orphan.id, err)
			}
			deleted++
		case sql.ErrNoRows:
			if _, err := db.ExecContext(ctx, updateSQL, canonicalID, orphan.id); err != nil {
				return redirected, deleted, fmt.Errorf("redirect orphan field %q to canonical %q: %w", orphan.id, canonicalID, err)
			}
			redirected++
		default:
			return redirected, deleted, fmt.Errorf("check existing canonical field row for %q.%q: %w", canonicalID, orphan.apiName, err)
		}
	}
	return redirected, deleted, nil
}