package message

import (
	"fmt"
	"strconv"
	"strings"
	"time"
)

func BuildThreadTimelineReadQuery(scopeID string, _ string, limit int, beforeSeq int64) (string, []any) {
	query := fmt.Sprintf(`
WITH timeline_page AS (
    SELECT id, seq, author_member_id, author_type, client_message_id, message_type, content, metadata, created_at,
           edited_at, edited_by_member_id, edit_revision
    FROM scope_messages
    WHERE scope_id = $1
      AND seq < $3
      AND %s
    ORDER BY seq DESC
    LIMIT $2
)
SELECT
    timeline_page.id,
    timeline_page.seq,
    $1 AS channel_id,
    timeline_page.author_type,
    author.id,
    u.id,
    a.id,
    a.name,
    u.display_name,
    author.display_name,
    u.name,
    u.avatar_url,
    a.avatar_url,
    timeline_page.client_message_id,
    timeline_page.message_type,
    timeline_page.content,
    timeline_page.metadata,
    timeline_page.created_at,
    timeline_page.edited_at,
    COALESCE(timeline_page.edited_by_member_id, ''),
    timeline_page.edit_revision
FROM timeline_page
JOIN members author ON author.id = timeline_page.author_member_id
LEFT JOIN users u ON timeline_page.author_type = 'human' AND u.id = author.user_id
LEFT JOIN agents a ON timeline_page.author_type = 'agent' AND a.member_id = author.id
ORDER BY timeline_page.seq ASC
`, HumanVisibleMessageTypeSQL("scope_messages"))
	return query, []any{scopeID, limit, beforeSeq}
}

func BuildUnreadCountsForUserScopedLocalSeqQuery(workspaceID string, memberID string, userID string) (string, []any, error) {
	visibleThreadRefsCTE := BuildVisibleThreadRefsSQLCTE("visible_thread_refs", 1, 2, 3, 4)

	query := fmt.Sprintf(`
WITH %s
SELECT
    visible_thread_refs.channel_id,
    COUNT(m.id) AS unread
FROM visible_thread_refs
JOIN scope_memberships sm ON sm.scope_id = visible_thread_refs.channel_id
    AND sm.member_id = $2
    AND sm.removed_at IS NULL
    AND sm.archived_at IS NULL
    AND sm.effective_to_seq IS NULL
LEFT JOIN scope_notification_settings sns ON sns.scope_id = visible_thread_refs.channel_id
    AND sns.member_id = $2
JOIN scopes unread_scope ON unread_scope.id = visible_thread_refs.channel_id
    AND unread_scope.workspace_id = $1
    AND unread_scope.latest_seq > sm.last_read_seq
JOIN scope_messages m ON m.scope_id = visible_thread_refs.channel_id
    AND m.workspace_id = $1
    AND m.seq > sm.last_read_seq
    AND m.seq >= sm.effective_from_seq
    AND COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id) <> $2
    AND %s
WHERE (sns.state IS NULL OR sns.state IN ('active', 'quiet'))
GROUP BY visible_thread_refs.channel_id
`, visibleThreadRefsCTE, HumanVisibleMessageTypeSQL("m"))
	return query, []any{strings.TrimSpace(workspaceID), strings.TrimSpace(memberID), strings.TrimSpace(userID), ""}, nil
}

func BuildWorkspaceHasUnreadForUserScopedQuery(userID string, workspaceIDs []string) (string, []any, error) {
	trimmedUserID := strings.TrimSpace(userID)
	if trimmedUserID == "" {
		return "", nil, fmt.Errorf("user id is required")
	}
	workspaceIDs = compactUniqueStrings(workspaceIDs)
	if len(workspaceIDs) == 0 {
		return "", []any{}, nil
	}

	requestedWorkspacesCTE, workspaceArgs := buildRequestedWorkspaceIDsCTE("requested_workspaces", workspaceIDs, 2)
	query := fmt.Sprintf(`
WITH %s,
workspace_members AS (
    SELECT
        rw.workspace_id,
        member.id AS member_id
    FROM requested_workspaces rw
    JOIN members member
      ON member.workspace_id = rw.workspace_id
      AND member.user_id = $1
      AND member.type = 'human'
      AND member.status = 'active'
)
SELECT wm.workspace_id
FROM workspace_members wm
WHERE EXISTS (
    SELECT 1
    FROM scope_memberships sm
    JOIN scopes unread_scope
      ON unread_scope.id = sm.scope_id
     AND unread_scope.workspace_id = wm.workspace_id
     AND unread_scope.status = 'active'
     AND unread_scope.type IN ('channel', 'dm', 'thread', 'service_account')
     AND unread_scope.latest_seq > sm.last_read_seq
    LEFT JOIN scope_notification_settings sns
      ON sns.scope_id = unread_scope.id
     AND sns.member_id = wm.member_id
    LEFT JOIN scopes parent
      ON parent.id = unread_scope.parent_scope_id
     AND parent.workspace_id = wm.workspace_id
     AND parent.status = 'active'
     AND unread_scope.type = 'thread'
    LEFT JOIN scope_memberships parent_sm
      ON parent_sm.scope_id = parent.id
     AND parent_sm.member_id = wm.member_id
     AND parent_sm.removed_at IS NULL
     AND parent_sm.archived_at IS NULL
     AND parent_sm.effective_to_seq IS NULL
     AND (parent.type = 'dm' OR parent_sm.visibility = 'public')
    LEFT JOIN scope_memberships peer
      ON peer.scope_id = unread_scope.id
     AND peer.member_id <> wm.member_id
     AND peer.removed_at IS NULL
     AND peer.archived_at IS NULL
     AND peer.effective_to_seq IS NULL
     AND unread_scope.type = 'dm'
    LEFT JOIN scope_memberships parent_peer
      ON parent_peer.scope_id = parent.id
     AND parent_peer.member_id <> wm.member_id
     AND parent_peer.removed_at IS NULL
     AND parent_peer.archived_at IS NULL
     AND parent_peer.effective_to_seq IS NULL
     AND parent.type = 'dm'
    WHERE sm.member_id = wm.member_id
      AND sm.removed_at IS NULL
      AND sm.archived_at IS NULL
      AND sm.effective_to_seq IS NULL
      AND (sns.state IS NULL OR sns.state IN ('active', 'quiet'))
      AND (
          (unread_scope.type IN ('channel', 'service_account') AND sm.visibility = 'public')
          OR unread_scope.type = 'dm'
          OR (
              unread_scope.type = 'thread'
              AND (
                  (parent.type = 'channel' AND parent_sm.member_id IS NOT NULL)
                  OR parent.type = 'dm'
              )
          )
      )
      AND NOT EXISTS (
          SELECT 1
          FROM agents a
          JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
          WHERE a.workspace_id = wm.workspace_id
            AND a.member_id = CASE
                WHEN unread_scope.type = 'dm' THEN COALESCE(peer.member_id, '')
                WHEN unread_scope.type = 'thread' AND parent.type = 'dm' THEN COALESCE(parent_peer.member_id, '')
                ELSE ''
            END
            AND mac.created_by_user_id <> $1
      )
      AND EXISTS (
          SELECT 1
          FROM scope_messages m
          WHERE m.scope_id = unread_scope.id
            AND m.workspace_id = wm.workspace_id
            AND m.seq > sm.last_read_seq
            AND m.seq >= sm.effective_from_seq
            AND COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id) <> wm.member_id
            AND %s
          LIMIT 1
      )
    LIMIT 1
)
`, requestedWorkspacesCTE, HumanVisibleMessageTypeSQL("m"))

	args := []any{trimmedUserID}
	args = append(args, workspaceArgs...)
	return query, args, nil
}

func compactUniqueStrings(values []string) []string {
	seen := make(map[string]struct{}, len(values))
	result := make([]string, 0, len(values))
	for _, value := range values {
		trimmed := strings.TrimSpace(value)
		if trimmed == "" {
			continue
		}
		if _, ok := seen[trimmed]; ok {
			continue
		}
		seen[trimmed] = struct{}{}
		result = append(result, trimmed)
	}
	return result
}

func buildRequestedWorkspaceIDsCTE(name string, workspaceIDs []string, startParam int) (string, []any) {
	var builder strings.Builder
	builder.WriteString(name)
	builder.WriteString(" AS (\n")

	args := make([]any, 0, len(workspaceIDs))
	param := startParam
	for i, workspaceID := range workspaceIDs {
		if i > 0 {
			builder.WriteString("\n    UNION ALL\n")
		}
		builder.WriteString("    SELECT $")
		builder.WriteString(strconv.Itoa(param))
		builder.WriteString(" AS workspace_id")
		args = append(args, workspaceID)
		param++
	}

	builder.WriteString("\n)")
	return builder.String(), args
}

// BuildMessageDiagnosticsScopeQuery starts from currently readable
// scope_messages on purpose: delivery diagnostics and diagnostics preview must
// not reconstruct a rewound suffix from historical outbox, activity, runtime,
// or recovery evidence after the transcript row has been tombstoned.
func BuildMessageDiagnosticsScopeQuery(workspaceID string, messageID string, memberID string, userID string) (string, []any, error) {
	query := `
SELECT
    m.id,
    message_scope.id AS thread_id,
    message_scope.id AS channel_id,
    message_scope.type AS channel_type,
    CASE WHEN message_scope.type = 'thread' THEN COALESCE(parent_scope.id, '') ELSE '' END AS parent_channel_id,
    CASE WHEN message_scope.type = 'thread' THEN COALESCE(parent_scope.type, '') ELSE '' END AS parent_channel_type,
    CASE WHEN message_scope.type = 'thread' THEN COALESCE(message_scope.root_message_id, '') ELSE '' END AS parent_message_id,
    m.author_member_id,
    COALESCE(author_member.user_id, '') AS author_user_id,
    m.author_type,
    m.message_type,
    m.content,
    m.created_at
FROM scope_messages m
JOIN scopes message_scope
  ON message_scope.id = m.scope_id
 AND message_scope.workspace_id = $1
 AND message_scope.status = 'active'
` + scopeReadableMembershipJoinSQL("$3") + `
LEFT JOIN scopes parent_scope
  ON parent_scope.id = message_scope.parent_scope_id
 AND parent_scope.workspace_id = $1
 AND parent_scope.status = 'active'
 AND message_scope.type = 'thread'
LEFT JOIN scope_memberships peer
  ON peer.scope_id = message_scope.id
 AND peer.member_id <> $3
 AND peer.removed_at IS NULL
 AND peer.archived_at IS NULL
 AND peer.effective_to_seq IS NULL
 AND message_scope.type = 'dm'
LEFT JOIN scope_memberships parent_peer
  ON parent_peer.scope_id = parent_scope.id
 AND parent_peer.member_id <> $3
 AND parent_peer.removed_at IS NULL
 AND parent_peer.archived_at IS NULL
 AND parent_peer.effective_to_seq IS NULL
 AND parent_scope.type = 'dm'
` + scopePrivateAuthorityGrantJoinsSQL + `
LEFT JOIN members author_member ON author_member.id = m.author_member_id AND author_member.workspace_id = $1
WHERE m.workspace_id = $1
  AND m.id = $2
  AND (message_scope.type <> 'thread' OR parent_scope.id IS NOT NULL)
  AND NOT EXISTS (
      SELECT 1
      FROM agents a
      JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
      WHERE a.workspace_id = $1
        AND a.member_id = CASE
            WHEN message_scope.type = 'dm' THEN COALESCE(peer.member_id, '')
            WHEN message_scope.type = 'thread' AND parent_scope.type = 'dm' THEN COALESCE(parent_peer.member_id, '')
            ELSE ''
        END
        AND mac.created_by_user_id <> $4
  )
  AND ` + scopeReadableMessagePredicateSQL + `
`
	return query, []any{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(messageID),
		strings.TrimSpace(memberID),
		strings.TrimSpace(userID),
	}, nil
}

func BuildListSubscribedThreadsForUserScopedQuery(workspaceID string, memberID string, userID string, limit int) (string, []any, error) {
	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(memberID), strings.TrimSpace(userID)}
	limitClause := ""
	if limit > 0 {
		limitClause = listLimitClause(limit, 4)
		args = append(args, limit)
	}

	query := buildListSubscribedThreadsForUserSQL(buildSubscribedThreadPageSQL("", limitClause))
	return query, args, nil
}

func BuildListSubscribedThreadInboxPageForUserScopedQuery(workspaceID string, memberID string, userID string, limit int, cursor ThreadInboxCursor) (string, []any, error) {
	if limit <= 0 {
		return "", nil, fmt.Errorf("invalid limit")
	}

	args := []any{strings.TrimSpace(workspaceID), strings.TrimSpace(memberID), strings.TrimSpace(userID)}

	cursorClause := ""
	if cursor != (ThreadInboxCursor{}) {
		sortAtArg := len(args) + 1
		threadChannelIDArg := sortAtArg + 1
		cursorClause = fmt.Sprintf(`
WHERE (sort_at < $%d OR (sort_at = $%d AND thread_channel_id > $%d))`, sortAtArg, sortAtArg, threadChannelIDArg)
		args = append(args, cursor.SortAt, cursor.ThreadChannelID)
	}

	limitArg := len(args) + 1
	args = append(args, limit+1)
	query := fmt.Sprintf(`
WITH %s
SELECT
    thread_channel_id,
    parent_channel_id,
    parent_channel_type,
    parent_label,
    parent_message_id,
    parent_message_short_id,
    parent_message_preview,
    parent_message_metadata,
    parent_message_author,
    subscription_state,
    reply_count,
    last_reply_at,
    last_activity_at,
    sort_at
FROM subscribed_thread_inbox_rows
ORDER BY sort_at DESC, thread_channel_id ASC
`, buildSubscribedThreadInboxRowsSQL(buildSubscribedThreadPageSQL(cursorClause, "LIMIT "+dollarParam(limitArg))))
	return query, args, nil
}

func BuildCountSubscribedThreadsForUserScopedQuery(workspaceID string, memberID string, userID string) (string, []any, error) {
	query := fmt.Sprintf(`
WITH %s
SELECT COUNT(*)
FROM subscribed_thread_authorized_candidates
`, buildSubscribedThreadAuthorizedCandidatesSQL())
	return query, []any{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(memberID),
		strings.TrimSpace(userID),
	}, nil
}

func buildListSubscribedThreadsForUserSQL(pageCTE string) string {
	return fmt.Sprintf(`
WITH %s
SELECT
    thread_channel_id,
    parent_channel_id,
    parent_channel_type,
    parent_label,
    parent_message_id,
    parent_message_short_id,
    parent_message_preview,
    parent_message_metadata,
    parent_message_author,
    subscription_state,
    reply_count,
    last_reply_at,
    last_activity_at
FROM subscribed_thread_inbox_rows
ORDER BY sort_at DESC, thread_channel_id ASC
`, buildSubscribedThreadInboxRowsSQL(pageCTE))
}

func buildSubscribedThreadPageSQL(cursorClause string, limitClause string) string {
	return fmt.Sprintf(`%s,
subscribed_thread_page AS (
    SELECT
        thread_channel_id,
        sort_at
    FROM subscribed_thread_authorized_candidates
    %s
    ORDER BY sort_at DESC, thread_channel_id ASC
    %s
)`, buildSubscribedThreadAuthorizedCandidatesSQL(), cursorClause, limitClause)
}

func buildSubscribedThreadAuthorizedCandidatesSQL() string {
	return `subscribed_thread_authorized_candidates AS (
    SELECT
        ss.scope_id AS thread_channel_id,
        COALESCE(reply_scope.latest_message_at, ss.updated_at) AS sort_at
    FROM scope_notification_settings ss
    JOIN scopes reply_scope
      ON reply_scope.id = ss.scope_id
     AND reply_scope.workspace_id = $1
     AND reply_scope.type = 'thread'
     AND reply_scope.status = 'active'
     AND reply_scope.archived_at IS NULL
    JOIN scopes parent_scope
      ON parent_scope.id = reply_scope.parent_scope_id
     AND parent_scope.workspace_id = $1
     AND parent_scope.status = 'active'
     AND parent_scope.archived_at IS NULL
    LEFT JOIN scope_channel_profiles parent_cp
      ON parent_cp.scope_id = parent_scope.id
     AND parent_cp.workspace_id = $1
    WHERE ss.member_id = $2
      AND ss.state IN ('active', 'quiet', 'mute')
      AND (parent_scope.type <> 'channel' OR parent_cp.subsystem IN ('im', 'team', 'tutorial'))
      AND (
          (parent_scope.type = 'channel' AND EXISTS (
              SELECT 1
              FROM scope_memberships parent_sm
              WHERE parent_sm.scope_id = parent_scope.id
                AND parent_sm.member_id = $2
                AND parent_sm.removed_at IS NULL
                AND parent_sm.archived_at IS NULL
                AND parent_sm.effective_to_seq IS NULL
                AND parent_sm.visibility = 'public'
          ))
          OR (parent_scope.type = 'dm' AND (
              EXISTS (
                  SELECT 1
                  FROM scope_memberships child_sm
                  WHERE child_sm.scope_id = reply_scope.id
                    AND child_sm.member_id = $2
                    AND child_sm.removed_at IS NULL
                    AND child_sm.archived_at IS NULL
                    AND child_sm.effective_to_seq IS NULL
              )
              OR EXISTS (
                  SELECT 1
                  FROM scope_memberships parent_sm
                  WHERE parent_sm.scope_id = parent_scope.id
                    AND parent_sm.member_id = $2
                    AND parent_sm.removed_at IS NULL
                    AND parent_sm.archived_at IS NULL
                    AND parent_sm.effective_to_seq IS NULL
              )
          ))
      )
      AND (parent_scope.type <> 'dm' OR NOT EXISTS (
          SELECT 1
          FROM scope_memberships parent_peer_membership
          JOIN agents a
            ON a.workspace_id = $1
           AND a.member_id = parent_peer_membership.member_id
          JOIN machines mac ON mac.workspace_id = a.workspace_id AND mac.id = a.machine_id
          WHERE parent_peer_membership.scope_id = parent_scope.id
            AND parent_peer_membership.member_id <> $2
            AND parent_peer_membership.removed_at IS NULL
            AND parent_peer_membership.archived_at IS NULL
            AND parent_peer_membership.effective_to_seq IS NULL
            AND mac.created_by_user_id <> $3
      ))
)`
}

func buildSubscribedThreadInboxRowsSQL(pageCTE string) string {
	return fmt.Sprintf(`%s,
subscribed_thread_inbox_rows AS (
    SELECT
        page.thread_channel_id,
        parent_scope.id AS parent_channel_id,
        parent_scope.type AS parent_channel_type,
        CASE
            WHEN parent_scope.type = 'channel' THEN COALESCE(NULLIF(parent_cp.display_name, ''), NULLIF(parent_cp.slug, ''), parent_scope.id)
            WHEN parent_peer.type = 'agent' THEN COALESCE(NULLIF(parent_peer.display_name, ''), NULLIF(parent_peer_agent.name, ''), '')
            WHEN parent_peer.type = 'human' THEN COALESCE(NULLIF(parent_peer_user.display_name, ''), NULLIF(parent_peer.display_name, ''), parent_peer_user.name, '')
            ELSE ''
        END AS parent_label,
        COALESCE(reply_scope.root_message_id, '') AS parent_message_id,
        COALESCE(reply_scope.root_message_short_id, '') AS parent_message_short_id,
        COALESCE(pm.content, '') AS parent_message_preview,
        COALESCE(pm.metadata, '{}') AS parent_message_metadata,
        CASE
            WHEN pm.author_type = 'agent' THEN COALESCE(NULLIF(author.display_name, ''), NULLIF(author_agent.name, ''), '')
            WHEN pm.author_type = 'human' THEN COALESCE(NULLIF(author_user.display_name, ''), NULLIF(author.display_name, ''), author_user.name, '')
            WHEN pm.author_type = 'service' THEN COALESCE(NULLIF(author.display_name, ''), author.id)
            ELSE ''
        END AS parent_message_author,
        ss.state AS subscription_state,
        COALESCE(reply_scope.latest_seq, 0) AS reply_count,
        reply_scope.latest_message_at AS last_reply_at,
        COALESCE(reply_scope.latest_message_at, ss.updated_at) AS last_activity_at,
        page.sort_at
    FROM subscribed_thread_page page
    JOIN scope_notification_settings ss
      ON ss.scope_id = page.thread_channel_id
     AND ss.member_id = $2
     AND ss.state IN ('active', 'quiet', 'mute')
    JOIN scopes reply_scope
      ON reply_scope.id = page.thread_channel_id
     AND reply_scope.workspace_id = $1
     AND reply_scope.type = 'thread'
     AND reply_scope.status = 'active'
     AND reply_scope.archived_at IS NULL
    JOIN scopes parent_scope
      ON parent_scope.id = reply_scope.parent_scope_id
     AND parent_scope.workspace_id = $1
     AND parent_scope.status = 'active'
     AND parent_scope.archived_at IS NULL
    LEFT JOIN scope_channel_profiles parent_cp ON parent_cp.scope_id = parent_scope.id
    LEFT JOIN scope_memberships parent_peer_membership
      ON parent_peer_membership.scope_id = parent_scope.id
     AND parent_peer_membership.member_id <> $2
     AND parent_peer_membership.removed_at IS NULL
     AND parent_peer_membership.archived_at IS NULL
     AND parent_peer_membership.effective_to_seq IS NULL
     AND parent_scope.type = 'dm'
    LEFT JOIN members parent_peer ON parent_peer.id = parent_peer_membership.member_id
    LEFT JOIN users parent_peer_user ON parent_peer.type = 'human' AND parent_peer_user.id = parent_peer.user_id
    LEFT JOIN agents parent_peer_agent ON parent_peer.type = 'agent' AND parent_peer_agent.member_id = parent_peer.id
    LEFT JOIN scope_messages pm
      ON pm.id = reply_scope.root_message_id
     AND pm.scope_id = reply_scope.parent_scope_id
    LEFT JOIN members author ON author.id = pm.author_member_id
    LEFT JOIN users author_user ON pm.author_type = 'human' AND author_user.id = author.user_id
    LEFT JOIN agents author_agent ON pm.author_type = 'agent' AND author_agent.member_id = author.id
)`, pageCTE)
}

func BuildAgentUnreadThreadsQuery(workspaceID string, memberID string, ownerQuoteCutoff time.Time) (string, []any, error) {
	excludedMessageTypes := AgentUnreadExcludedMessageTypes()
	args := []any{workspaceID, memberID}
	for _, messageType := range excludedMessageTypes {
		args = append(args, messageType)
	}
	ownerQuoteCutoffParam := 3 + len(excludedMessageTypes)
	args = append(args, ownerQuoteCutoff)
	messageTypePredicate := ""
	if len(excludedMessageTypes) > 0 {
		messageTypePredicate = fmt.Sprintf(" AND sm.message_type NOT IN (%s)", dollarParamList(3, len(excludedMessageTypes)))
	}
	rewoundPredicate := " AND " + MessageNotRewoundSQL("sm")
	query := fmt.Sprintf(`
WITH %s
SELECT
    agent_dense_scope_labels.thread_id,
    agent_dense_scope_labels.channel_type,
    agent_dense_scope_labels.channel_name,
    agent_dense_scope_labels.parent_channel_type,
    agent_dense_scope_labels.parent_channel_name,
    agent_dense_scope_labels.last_read_seq,
    COUNT(*) AS unread_count
FROM agent_dense_scope_labels
JOIN scope_messages sm
  ON sm.scope_id = agent_dense_scope_labels.scope_id
 AND sm.workspace_id = $1
 AND sm.seq > agent_dense_scope_labels.last_read_seq
 AND COALESCE(NULLIF(sm.notify_principal_member_id, ''), sm.author_member_id) <> $2
%s%s
GROUP BY
    agent_dense_scope_labels.thread_id,
    agent_dense_scope_labels.channel_type,
    agent_dense_scope_labels.channel_name,
    agent_dense_scope_labels.parent_channel_type,
    agent_dense_scope_labels.parent_channel_name,
    agent_dense_scope_labels.last_read_seq
ORDER BY agent_dense_scope_labels.thread_id ASC
`, buildAgentDenseScopeLabelsCTE("agent_dense_scope_labels", 1, 2, ownerQuoteCutoffParam), rewoundPredicate, messageTypePredicate)
	return query, args, nil
}

func BuildAgentUnreadThreadLabelsCTE(name string, workspaceParam int, memberParam int, ownerQuoteCutoffParam int) string {
	return buildAgentDenseScopeLabelsCTE(name, workspaceParam, memberParam, ownerQuoteCutoffParam)
}

func buildAgentDenseScopeLabelsCTE(name string, workspaceParam int, memberParam int, ownerQuoteCutoffParam int) string {
	workspacePlaceholder := dollarParam(workspaceParam)
	memberPlaceholder := dollarParam(memberParam)
	ownerQuoteCutoffPlaceholder := dollarParam(ownerQuoteCutoffParam)

	return fmt.Sprintf(`%s AS (
    SELECT
        s.id AS thread_id,
        s.id AS scope_id,
        s.type AS channel_type,
        CASE
            WHEN s.type = 'dm' THEN COALESCE(peer_membership.member_id, '')
            WHEN s.type = 'thread' THEN COALESCE(NULLIF(cp.slug, ''), CONCAT('thread-', NULLIF(s.root_message_short_id, '')), s.id)
            ELSE COALESCE(NULLIF(cp.slug, ''), s.id)
        END AS channel_name,
        CASE
            WHEN s.type = 'dm' THEN ''
            WHEN s.type = 'thread' THEN COALESCE(NULLIF(cp.display_name, ''), NULLIF(cp.slug, ''), CONCAT('thread-', NULLIF(s.root_message_short_id, '')), s.id)
            ELSE COALESCE(NULLIF(cp.display_name, ''), NULLIF(cp.slug, ''), s.id)
        END AS channel_display_name,
        CASE WHEN s.type = 'thread' THEN COALESCE(parent.type, '') ELSE '' END AS parent_channel_type,
        CASE
            WHEN s.type = 'thread' AND parent.type = 'channel' THEN COALESCE(NULLIF(parent_cp.slug, ''), parent.id)
            WHEN s.type = 'thread' AND parent.type = 'dm' THEN COALESCE(parent_peer_membership.member_id, '')
            ELSE ''
        END AS parent_channel_name,
        CASE
            WHEN s.type = 'thread' AND parent.type = 'channel' THEN COALESCE(NULLIF(parent_cp.display_name, ''), NULLIF(parent_cp.slug, ''), parent.id)
            ELSE ''
        END AS parent_channel_display_name,
        COALESCE(sm.last_read_seq, 0) AS last_read_seq,
        COALESCE(sm.updated_at, %s) AS owner_quote_cutoff
    FROM scope_memberships sm
    JOIN scopes s
      ON s.id = sm.scope_id
     AND s.workspace_id = %s
     AND s.status = 'active'
    LEFT JOIN scope_channel_profiles cp ON cp.scope_id = s.id
    LEFT JOIN scopes parent
      ON parent.id = s.parent_scope_id
     AND parent.workspace_id = %s
    LEFT JOIN scope_channel_profiles parent_cp ON parent_cp.scope_id = parent.id
    LEFT JOIN scope_memberships peer_membership
      ON peer_membership.scope_id = s.id
     AND peer_membership.member_id <> %s
     AND peer_membership.removed_at IS NULL
     AND peer_membership.archived_at IS NULL
     AND peer_membership.effective_to_seq IS NULL
     AND s.type = 'dm'
    LEFT JOIN scope_memberships parent_peer_membership
      ON parent_peer_membership.scope_id = parent.id
     AND parent_peer_membership.member_id <> %s
     AND parent_peer_membership.removed_at IS NULL
     AND parent_peer_membership.archived_at IS NULL
     AND parent_peer_membership.effective_to_seq IS NULL
     AND parent.type = 'dm'
    WHERE sm.member_id = %s
      AND s.type IN ('channel', 'dm', 'thread', 'task', 'service_account')
      AND sm.removed_at IS NULL
      AND sm.archived_at IS NULL
      AND sm.effective_to_seq IS NULL
)`,
		name,
		ownerQuoteCutoffPlaceholder,
		workspacePlaceholder,
		workspacePlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		memberPlaceholder,
	)
}

func listLimitClause(limit int, index int) string {
	if limit <= 0 {
		return ""
	}
	return "LIMIT " + dollarParam(index)
}

func dollarParam(index int) string {
	return "$" + strconv.Itoa(index)
}

func dollarParamList(start int, count int) string {
	params := make([]string, 0, count)
	for i := 0; i < count; i++ {
		params = append(params, dollarParam(start+i))
	}
	return strings.Join(params, ", ")
}