package app

import (
	"context"
	"errors"
	"fmt"
	"strings"

	"github.com/go-sql-driver/mysql"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

// deleteMemoryAgentBindingsForAgentIDs clears MeshX-owned Memory bindings for
// the given agents when an agent is deleted or archived.
//
// The table is co-located in the shared AgentTeam database (the same schema that owns
// knowledge_base_grants), but it is created and migrated by MeshX. Current
// deployments store the binding on memories.agent_id; legacy deployments used
// memory_agent_bindings. Missing MeshX tables or pre-migration columns are
// tolerated, mirroring the knowledge-base grants cleanup so agent deletion never
// fails because MeshX state is absent or not yet migrated.
func deleteMemoryAgentBindingsForAgentIDs(ctx context.Context, execer coredb.QueryExecer, workspaceID string, agentIDs []string) error {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	if trimmedWorkspaceID == "" || len(agentIDs) == 0 {
		return nil
	}
	seen := make(map[string]struct{}, len(agentIDs))
	for _, agentID := range agentIDs {
		trimmedAgentID := strings.TrimSpace(agentID)
		if trimmedAgentID == "" {
			continue
		}
		if _, ok := seen[trimmedAgentID]; ok {
			continue
		}
		seen[trimmedAgentID] = struct{}{}
		if _, err := execer.Exec(ctx, `
UPDATE memories
SET agent_id = NULL,
    branch = 'main',
    import_existing_local_content = FALSE,
    replace_local_content_on_switch = FALSE,
    updated_at = NOW()
WHERE agent_id = $1
  AND deleted_at IS NULL
`, trimmedAgentID); err != nil {
			if !isMissingCurrentMemoryBindingStorage(err) {
				return fmt.Errorf("clear memory agent ownership: %w", err)
			}
		}
		if _, err := execer.Exec(ctx, `
DELETE FROM memory_agent_bindings
WHERE workspace_id = $1
  AND agent_id = $2
`, trimmedWorkspaceID, trimmedAgentID); err != nil {
			if !isMissingLegacyMemoryAgentBindingsTable(err) {
				return fmt.Errorf("delete legacy memory agent bindings: %w", err)
			}
		}
	}
	return nil
}

// isMissingCurrentMemoryBindingStorage reports whether err indicates the
// current memories.agent_id storage is unavailable. MySQL signals missing table
// as 1146 (ER_NO_SUCH_TABLE) and missing column as 1054 (ER_BAD_FIELD_ERROR);
// substring fallbacks cover wrapped or non-typed errors.
func isMissingCurrentMemoryBindingStorage(err error) bool {
	var mysqlErr *mysql.MySQLError
	if errors.As(err, &mysqlErr) && (mysqlErr.Number == 1146 || mysqlErr.Number == 1054) {
		return true
	}
	message := strings.ToLower(err.Error())
	if strings.Contains(message, "memories") && strings.Contains(message, "doesn't exist") {
		return true
	}
	return strings.Contains(message, "unknown column") && strings.Contains(message, "agent_id")
}

func isMissingLegacyMemoryAgentBindingsTable(err error) bool {
	var mysqlErr *mysql.MySQLError
	if errors.As(err, &mysqlErr) && mysqlErr.Number == 1146 {
		return true
	}
	message := strings.ToLower(err.Error())
	return strings.Contains(message, "memory_agent_bindings") && strings.Contains(message, "doesn't exist")
}