package message

import (
	"fmt"
	"strings"
)

// BuildVisibleThreadRefsSQLCTE returns a database-side visible-thread scope.
//
// The returned CTE keeps the same visibility semantics as access.ListVisibleThreads
// plus the owner-private peer filter, while keeping query text stable and
// parameter count fixed.
func BuildVisibleThreadRefsSQLCTE(name string, workspaceParam int, memberParam int, userParam int, requestedChannelParam int) string {
	return buildVisibleThreadRefsSQLCTEForConversationKinds(
		name,
		workspaceParam,
		memberParam,
		userParam,
		requestedChannelParam,
		conversationKindSelection{channel: true, dm: true, thread: true, serviceAccount: true},
	)
}

func buildVisibleThreadRefsSQLCTEForConversationKinds(name string, workspaceParam int, memberParam int, userParam int, requestedChannelParam int, selection conversationKindSelection) string {
	workspacePlaceholder := dollarParam(workspaceParam)
	memberPlaceholder := dollarParam(memberParam)
	userPlaceholder := dollarParam(userParam)
	requestedChannelPlaceholder := dollarParam(requestedChannelParam)

	branches := make([]string, 0, 4)
	if selection.channel {
		branches = append(branches, visibleChannelRefsBranchSQL(workspacePlaceholder, memberPlaceholder))
	}
	if selection.dm {
		branches = append(branches, visibleDMRefsBranchSQL(workspacePlaceholder, memberPlaceholder))
	}
	if selection.thread {
		branches = append(branches, visibleThreadRefsBranchSQL(workspacePlaceholder, memberPlaceholder))
	}
	if selection.serviceAccount {
		branches = append(branches, visibleServiceAccountRefsBranchSQL(workspacePlaceholder, memberPlaceholder))
	}
	if len(branches) == 0 {
		return fmt.Sprintf(`%s AS (
    SELECT
        '' AS thread_id,
        '' AS channel_id,
        '' AS parent_scope_id
    WHERE 1 = 0
)`, name)
	}

	return fmt.Sprintf(`%s AS (
    SELECT DISTINCT
        visible_threads.thread_id,
        visible_threads.channel_id,
        visible_threads.parent_scope_id
    FROM (
%s
    ) visible_threads
    WHERE 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 = %s
          AND a.member_id = visible_threads.peer_member_id
          AND mac.created_by_user_id <> %s
    )
      AND (%s = '' OR visible_threads.channel_id = %s OR visible_threads.parent_scope_id = %s)
)`,
		name,
		strings.Join(branches, "\n\n        UNION ALL\n\n"),
		workspacePlaceholder,
		userPlaceholder,
		requestedChannelPlaceholder,
		requestedChannelPlaceholder,
		requestedChannelPlaceholder,
	)
}

func visibleChannelRefsBranchSQL(workspacePlaceholder string, memberPlaceholder string) string {
	return fmt.Sprintf(`        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            '' AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
         AND sm.visibility = 'public'
        WHERE s.workspace_id = %s
          AND s.type = 'channel'
          AND s.status = 'active'`, memberPlaceholder, workspacePlaceholder)
}

func visibleDMRefsBranchSQL(workspacePlaceholder string, memberPlaceholder string) string {
	return fmt.Sprintf(`        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            CASE WHEN peer.member_id IS NULL THEN '' ELSE peer.member_id END AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
        LEFT JOIN scope_memberships peer
          ON peer.scope_id = s.id
         AND peer.member_id <> %s
         AND peer.removed_at IS NULL
         AND peer.archived_at IS NULL
         AND peer.effective_to_seq IS NULL
        WHERE s.workspace_id = %s
          AND s.type = 'dm'
          AND s.status = 'active'`, memberPlaceholder, memberPlaceholder, workspacePlaceholder)
}

func visibleServiceAccountRefsBranchSQL(workspacePlaceholder string, memberPlaceholder string) string {
	return fmt.Sprintf(`        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            '' AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
         AND sm.visibility = 'public'
        WHERE s.workspace_id = %s
          AND s.type = 'service_account'
          AND s.status = 'active'`, memberPlaceholder, workspacePlaceholder)
}

func visibleThreadRefsBranchSQL(workspacePlaceholder string, memberPlaceholder string) string {
	return fmt.Sprintf(`        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            parent.id AS parent_scope_id,
            CASE
                WHEN parent.type = 'dm' AND parent_peer.member_id IS NOT NULL THEN parent_peer.member_id
                ELSE ''
            END AS peer_member_id
        FROM scopes s
        JOIN scopes parent
          ON parent.id = s.parent_scope_id
         AND parent.workspace_id = %s
         AND parent.status = 'active'
        LEFT JOIN scope_memberships child_sm
          ON child_sm.scope_id = s.id
         AND child_sm.member_id = %s
         AND child_sm.removed_at IS NULL
         AND child_sm.archived_at IS NULL
         AND child_sm.effective_to_seq IS NULL
        LEFT JOIN scope_memberships parent_sm
          ON parent_sm.scope_id = parent.id
         AND parent_sm.member_id = %s
         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 parent_peer
          ON parent_peer.scope_id = parent.id
         AND parent_peer.member_id <> %s
         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 s.workspace_id = %s
          AND s.type = 'thread'
          AND s.status = 'active'
          AND (
              (parent.type = 'channel' AND parent_sm.member_id IS NOT NULL)
              OR (parent.type = 'dm' AND (child_sm.member_id IS NOT NULL OR parent_sm.member_id IS NOT NULL))
          )`, workspacePlaceholder, memberPlaceholder, memberPlaceholder, memberPlaceholder, workspacePlaceholder)
}

// BuildVisibleThreadRefsSQLCTEForRequest returns a narrow visible-thread CTE
// when the caller already knows the requested channel/thread scope. Broad
// workspace scans should continue using BuildVisibleThreadRefsSQLCTE.
func BuildVisibleThreadRefsSQLCTEForRequest(name string, workspaceParam int, memberParam int, userParam int, requestedChannelParam int, requestedChannelID string) string {
	if strings.TrimSpace(requestedChannelID) == "" {
		return BuildVisibleThreadRefsSQLCTE(name, workspaceParam, memberParam, userParam, requestedChannelParam)
	}

	workspacePlaceholder := dollarParam(workspaceParam)
	memberPlaceholder := dollarParam(memberParam)
	userPlaceholder := dollarParam(userParam)
	requestedChannelPlaceholder := dollarParam(requestedChannelParam)

	return fmt.Sprintf(`%s AS (
    SELECT DISTINCT
        visible_threads.thread_id,
        visible_threads.channel_id,
        visible_threads.parent_scope_id
    FROM (
        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            '' AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
         AND sm.visibility = 'public'
        WHERE s.workspace_id = %s
          AND s.id = %s
          AND s.type = 'channel'
          AND s.status = 'active'

        UNION ALL

        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            CASE WHEN peer.member_id IS NULL THEN '' ELSE peer.member_id END AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
        LEFT JOIN scope_memberships peer
          ON peer.scope_id = s.id
         AND peer.member_id <> %s
         AND peer.removed_at IS NULL
         AND peer.archived_at IS NULL
         AND peer.effective_to_seq IS NULL
        WHERE s.workspace_id = %s
          AND s.id = %s
          AND s.type = 'dm'
          AND s.status = 'active'

        UNION ALL

        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            s.id AS parent_scope_id,
            '' AS peer_member_id
        FROM scopes s
        JOIN scope_memberships sm
          ON sm.scope_id = s.id
         AND sm.member_id = %s
         AND sm.removed_at IS NULL
         AND sm.archived_at IS NULL
         AND sm.effective_to_seq IS NULL
         AND sm.visibility = 'public'
        WHERE s.workspace_id = %s
          AND s.id = %s
          AND s.type = 'service_account'
          AND s.status = 'active'

        UNION ALL

        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            parent.id AS parent_scope_id,
            CASE
                WHEN parent.type = 'dm' AND parent_peer.member_id IS NOT NULL THEN parent_peer.member_id
                ELSE ''
            END AS peer_member_id
        FROM scopes s
        JOIN scopes parent
          ON parent.id = s.parent_scope_id
         AND parent.workspace_id = %s
         AND parent.status = 'active'
        LEFT JOIN scope_memberships child_sm
          ON child_sm.scope_id = s.id
         AND child_sm.member_id = %s
         AND child_sm.removed_at IS NULL
         AND child_sm.archived_at IS NULL
         AND child_sm.effective_to_seq IS NULL
        LEFT JOIN scope_memberships parent_sm
          ON parent_sm.scope_id = parent.id
         AND parent_sm.member_id = %s
         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 parent_peer
          ON parent_peer.scope_id = parent.id
         AND parent_peer.member_id <> %s
         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 s.workspace_id = %s
          AND s.id = %s
          AND s.type = 'thread'
          AND s.status = 'active'
          AND (
              (parent.type = 'channel' AND parent_sm.member_id IS NOT NULL)
              OR (parent.type = 'dm' AND (child_sm.member_id IS NOT NULL OR parent_sm.member_id IS NOT NULL))
          )

        UNION ALL

        SELECT
            s.id AS thread_id,
            s.id AS channel_id,
            parent.id AS parent_scope_id,
            CASE
                WHEN parent.type = 'dm' AND parent_peer.member_id IS NOT NULL THEN parent_peer.member_id
                ELSE ''
            END AS peer_member_id
        FROM scopes s
        JOIN scopes parent
          ON parent.id = s.parent_scope_id
         AND parent.workspace_id = %s
         AND parent.status = 'active'
        LEFT JOIN scope_memberships child_sm
          ON child_sm.scope_id = s.id
         AND child_sm.member_id = %s
         AND child_sm.removed_at IS NULL
         AND child_sm.archived_at IS NULL
         AND child_sm.effective_to_seq IS NULL
        LEFT JOIN scope_memberships parent_sm
          ON parent_sm.scope_id = parent.id
         AND parent_sm.member_id = %s
         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 parent_peer
          ON parent_peer.scope_id = parent.id
         AND parent_peer.member_id <> %s
         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 s.workspace_id = %s
          AND s.parent_scope_id = %s
          AND s.type = 'thread'
          AND s.status = 'active'
          AND (
              (parent.type = 'channel' AND parent_sm.member_id IS NOT NULL)
              OR (parent.type = 'dm' AND (child_sm.member_id IS NOT NULL OR parent_sm.member_id IS NOT NULL))
          )
    ) visible_threads
    WHERE 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 = %s
          AND a.member_id = visible_threads.peer_member_id
          AND mac.created_by_user_id <> %s
    )
)`,
		name,
		memberPlaceholder,
		workspacePlaceholder,
		requestedChannelPlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		workspacePlaceholder,
		requestedChannelPlaceholder,
		memberPlaceholder,
		workspacePlaceholder,
		requestedChannelPlaceholder,
		workspacePlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		workspacePlaceholder,
		requestedChannelPlaceholder,
		workspacePlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		memberPlaceholder,
		workspacePlaceholder,
		requestedChannelPlaceholder,
		workspacePlaceholder,
		userPlaceholder,
	)
}