package onboarding

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

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

var ErrMockExchangeUnavailable = errors.New("desktop onboarding mock exchange is unavailable")

type MockAgentTarget struct {
	AgentID             string
	AgentMemberID       string
	AgentMachineID      string
	AgentName           string
	AgentDisplayName    string
	TutorialChannelID   string
	TutorialChannelSlug string
}

type MockReplyTarget struct {
	MockAgentTarget
	ThreadScopeID          string
	ThreadTargetRef        string
	RequestMessageID       string
	ExistingReplyMessageID string
}

// ResolveMockAgentTarget validates the exact active onboarding state, tutorial
// channel, and selected Agent for one user-scoped start request.
func (store *Store) ResolveMockAgentTarget(ctx context.Context, userID string, workspaceID string, tutorialChannelID string, agentID string) (MockAgentTarget, error) {
	if store == nil || store.db == nil {
		return MockAgentTarget{}, fmt.Errorf("onboarding store is not configured")
	}
	var target MockAgentTarget
	err := store.db.QueryRow(ctx, `
SELECT a.id, a.member_id, a.machine_id, a.name,
       COALESCE(NULLIF(agent_member.display_name, ''), a.name),
       state.tutorial_channel_id, profile.slug
FROM desktop_onboarding_states state
JOIN scopes tutorial_scope
  ON tutorial_scope.id = state.tutorial_channel_id
 AND tutorial_scope.workspace_id = state.workspace_id
 AND tutorial_scope.type = 'channel'
 AND tutorial_scope.status = 'active'
 AND tutorial_scope.archived_at IS NULL
JOIN scope_channel_profiles profile
  ON profile.scope_id = tutorial_scope.id
 AND profile.workspace_id = tutorial_scope.workspace_id
 AND profile.subsystem = 'tutorial'
JOIN agents a
  ON a.id = $5
 AND a.workspace_id = state.workspace_id
 AND a.status <> 'archived'
JOIN members agent_member
  ON agent_member.id = a.member_id
 AND agent_member.workspace_id = a.workspace_id
 AND agent_member.type = 'agent'
 AND agent_member.status = 'active'
JOIN machines machine
  ON machine.id = a.machine_id
 AND machine.workspace_id = a.workspace_id
 AND machine.status = 'active'
JOIN scope_memberships membership
  ON membership.scope_id = tutorial_scope.id
 AND membership.member_id = a.member_id
 AND membership.removed_at IS NULL
 AND membership.archived_at IS NULL
 AND membership.effective_to_seq IS NULL
WHERE state.user_id = $1
  AND state.workspace_id = $2
  AND state.flow_version = $3
  AND state.status = 'active'
  AND state.tutorial_channel_id = $4
LIMIT 1
`, strings.TrimSpace(userID), strings.TrimSpace(workspaceID), FlowVersionV1, strings.TrimSpace(tutorialChannelID), strings.TrimSpace(agentID)).Scan(
		&target.AgentID,
		&target.AgentMemberID,
		&target.AgentMachineID,
		&target.AgentName,
		&target.AgentDisplayName,
		&target.TutorialChannelID,
		&target.TutorialChannelSlug,
	)
	if err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return MockAgentTarget{}, ErrMockExchangeUnavailable
		}
		return MockAgentTarget{}, fmt.Errorf("resolve onboarding mock agent target: %w", err)
	}
	return target, nil
}

// ResolveMockReplyTarget validates one exact Human mock request in an active
// tutorial reply thread and detects an idempotently persisted Agent reply.
func (store *Store) ResolveMockReplyTarget(ctx context.Context, userID string, workspaceID string, threadScopeID string, agentID string, requestMessageID string, replyClientMessageID string) (MockReplyTarget, error) {
	if store == nil || store.db == nil {
		return MockReplyTarget{}, fmt.Errorf("onboarding store is not configured")
	}
	var target MockReplyTarget
	var metadataJSON []byte
	var existingReplyMessageID sql.NullString
	err := store.db.QueryRow(ctx, `
SELECT a.id, a.member_id, a.machine_id, a.name,
       COALESCE(NULLIF(agent_member.display_name, ''), a.name),
       state.tutorial_channel_id, profile.slug,
       thread_scope.id, thread_scope.root_message_short_id,
       request_message.id, request_message.metadata,
       existing_reply.id
FROM desktop_onboarding_states state
JOIN scopes tutorial_scope
  ON tutorial_scope.id = state.tutorial_channel_id
 AND tutorial_scope.workspace_id = state.workspace_id
 AND tutorial_scope.type = 'channel'
 AND tutorial_scope.status = 'active'
 AND tutorial_scope.archived_at IS NULL
JOIN scope_channel_profiles profile
  ON profile.scope_id = tutorial_scope.id
 AND profile.workspace_id = tutorial_scope.workspace_id
 AND profile.subsystem = 'tutorial'
JOIN scopes thread_scope
  ON thread_scope.id = $4
 AND thread_scope.workspace_id = state.workspace_id
 AND thread_scope.parent_scope_id = tutorial_scope.id
 AND thread_scope.type = 'thread'
 AND thread_scope.status = 'active'
 AND thread_scope.archived_at IS NULL
JOIN scope_messages root_message
  ON root_message.id = thread_scope.root_message_id
 AND root_message.workspace_id = state.workspace_id
 AND root_message.scope_id = tutorial_scope.id
 AND root_message.author_member_id = state.owner_member_id
 AND root_message.author_type = 'human'
 AND root_message.client_message_id IS NOT NULL
JOIN agents a
  ON a.id = $5
 AND a.workspace_id = state.workspace_id
 AND a.status <> 'archived'
JOIN members agent_member
  ON agent_member.id = a.member_id
 AND agent_member.workspace_id = a.workspace_id
 AND agent_member.type = 'agent'
 AND agent_member.status = 'active'
JOIN machines machine
  ON machine.id = a.machine_id
 AND machine.workspace_id = a.workspace_id
 AND machine.status = 'active'
JOIN scope_memberships tutorial_membership
  ON tutorial_membership.scope_id = tutorial_scope.id
 AND tutorial_membership.member_id = a.member_id
 AND tutorial_membership.removed_at IS NULL
 AND tutorial_membership.archived_at IS NULL
 AND tutorial_membership.effective_to_seq IS NULL
JOIN scope_messages request_message
  ON request_message.id = $6
 AND request_message.workspace_id = state.workspace_id
 AND request_message.scope_id = thread_scope.id
 AND request_message.author_member_id = state.owner_member_id
 AND request_message.author_type = 'human'
 AND request_message.client_message_id = CONCAT(root_message.client_message_id, ':request')
LEFT JOIN scope_messages existing_reply
  ON existing_reply.workspace_id = state.workspace_id
 AND existing_reply.scope_id = thread_scope.id
 AND existing_reply.author_member_id = a.member_id
 AND existing_reply.author_type = 'agent'
 AND existing_reply.client_message_id = $7
WHERE state.user_id = $1
  AND state.workspace_id = $2
  AND state.flow_version = $3
  AND (
    state.status = 'active'
    OR (state.status = 'completed' AND existing_reply.id IS NOT NULL)
  )
  AND $7 = CONCAT(root_message.client_message_id, ':reply')
LIMIT 1
`, strings.TrimSpace(userID), strings.TrimSpace(workspaceID), FlowVersionV1, strings.TrimSpace(threadScopeID), strings.TrimSpace(agentID), strings.TrimSpace(requestMessageID), strings.TrimSpace(replyClientMessageID)).Scan(
		&target.AgentID,
		&target.AgentMemberID,
		&target.AgentMachineID,
		&target.AgentName,
		&target.AgentDisplayName,
		&target.TutorialChannelID,
		&target.TutorialChannelSlug,
		&target.ThreadScopeID,
		&target.ThreadTargetRef,
		&target.RequestMessageID,
		&metadataJSON,
		&existingReplyMessageID,
	)
	if err != nil {
		if coredb.IsNotFound(err) || err == sql.ErrNoRows {
			return MockReplyTarget{}, ErrMockExchangeUnavailable
		}
		return MockReplyTarget{}, fmt.Errorf("resolve onboarding mock reply target: %w", err)
	}
	var metadata map[string]any
	if len(metadataJSON) == 0 || model.UnmarshalJSONObject("onboarding mock request metadata", metadataJSON, &metadata) != nil || !metadataMentionsAgent(metadata, target.AgentID) {
		return MockReplyTarget{}, ErrMockExchangeUnavailable
	}
	target.ThreadTargetRef = "#" + strings.TrimSpace(target.TutorialChannelSlug) + ":" + strings.TrimSpace(target.ThreadTargetRef)
	target.ExistingReplyMessageID = strings.TrimSpace(existingReplyMessageID.String)
	return target, nil
}