package app

import (
	"context"
	"fmt"
	"strings"

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

// deleteAgentDMInboxProjections removes member-local Inbox rows for DMs that
// include a deleted Agent member. The DM transcript and stream replay rows stay
// intact; this cleanup only removes the current Inbox materialization.
func deleteAgentDMInboxProjections(ctx context.Context, execer coredb.QueryExecer, workspaceID string, agentMemberID string) error {
	trimmedWorkspaceID := strings.TrimSpace(workspaceID)
	trimmedAgentMemberID := strings.TrimSpace(agentMemberID)
	if trimmedWorkspaceID == "" || trimmedAgentMemberID == "" {
		return nil
	}
	if _, err := execer.Exec(ctx, `
DELETE FROM im_conversation_index
WHERE workspace_id = $1
  AND scope_id IN (
    SELECT DISTINCT sm.scope_id
    FROM scope_memberships sm
    JOIN scopes s
      ON s.id = sm.scope_id
     AND s.workspace_id = $1
     AND s.type = 'dm'
    WHERE sm.member_id = $2
  )
`, trimmedWorkspaceID, trimmedAgentMemberID); err != nil {
		return fmt.Errorf("delete agent dm inbox projections: %w", err)
	}
	return nil
}