package message

import (
	"context"
	"fmt"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

func TestBuildVisibleThreadRefsSQLCTEAvoidsPerThreadUnionParams(t *testing.T) {
	t.Parallel()

	cte := BuildVisibleThreadRefsSQLCTE("visible_thread_refs", 1, 2, 3, 4)
	require.Contains(t, cte, "visible_thread_refs AS (")
	require.Contains(t, cte, "FROM scopes s")
	require.Contains(t, cte, "JOIN scope_memberships sm")
	require.Contains(t, cte, "JOIN scopes parent")
	require.Contains(t, cte, "NOT EXISTS")
	require.Contains(t, cte, "mac.created_by_user_id <> $3")
	require.Contains(t, cte, "($4 = '' OR visible_threads.channel_id = $4 OR visible_threads.parent_scope_id = $4)")
	require.NotContains(t, cte, legacyTableNameForMessageTest("space", "participants"))
	require.NotContains(t, cte, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, cte, "SELECT $")
}

func TestBuildVisibleThreadRefsSQLCTEForRequestUsesNarrowScopePredicates(t *testing.T) {
	t.Parallel()

	cte := BuildVisibleThreadRefsSQLCTEForRequest("visible_thread_refs", 1, 2, 3, 4, "chn-filter")
	require.Contains(t, cte, "visible_thread_refs AS (")
	require.Contains(t, cte, "s.id = $4")
	require.Contains(t, cte, "s.parent_scope_id = $4")
	require.Contains(t, cte, "JOIN scopes parent")
	require.Contains(t, cte, "NOT EXISTS")
	require.Contains(t, cte, "mac.created_by_user_id <> $3")
	require.NotContains(t, cte, "($4 = '' OR visible_threads.channel_id = $4 OR visible_threads.parent_scope_id = $4)")
}

func TestBuildVisibleThreadRefsSQLCTEForRequestMatchesBroadRequestedFilter(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)

	now := time.Date(2026, 5, 28, 10, 30, 0, 0, time.UTC)
	const (
		workspaceID      = "wsp_visible_refs_narrow"
		ownerUserID      = "usr_visible_refs_narrow_owner"
		peerUserID       = "usr_visible_refs_narrow_peer"
		ownerMemberID    = "mem_visible_refs_narrow_owner"
		peerMemberID     = "mem_visible_refs_narrow_peer"
		channelID        = "chn_visible_refs_narrow"
		channelThreadID  = "chn_visible_refs_narrow_thread"
		channelMessageID = "msg_visible_refs_narrow_channel"
		dmID             = "dm_visible_refs_narrow"
		dmThreadID       = "chn_visible_refs_narrow_dm_thread"
		dmMessageID      = "msg_visible_refs_narrow_dm"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "visible-refs-narrow-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, peerUserID, "visible-refs-narrow-peer@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, peerMemberID, workspaceID, peerUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "visible-refs-narrow", ownerMemberID, "", now)
	insertMessagingHumanScopeMessage(t, ctx, pool, channelMessageID, workspaceID, channelID, "", ownerMemberID, "parent channel message", 1, 1, now)
	insertMessagingReplyThread(t, ctx, pool, channelThreadID, workspaceID, channelID, channelMessageID, "narchn", "", now)
	insertMessagingDMThread(t, ctx, pool, dmID, workspaceID, ownerMemberID, peerMemberID, "", now)
	insertMessagingHumanScopeMessage(t, ctx, pool, dmMessageID, workspaceID, dmID, "", peerMemberID, "parent dm message", 1, 2, now)
	insertMessagingReplyThread(t, ctx, pool, dmThreadID, workspaceID, dmID, dmMessageID, "nardm", "", now)

	for _, requestedScopeID := range []string{channelID, channelThreadID, dmID, dmThreadID, "missing_visible_refs_narrow"} {
		broad := visibleThreadChannelsForTest(t, ctx, pool, BuildVisibleThreadRefsSQLCTE("visible_thread_refs", 1, 2, 3, 4), workspaceID, ownerMemberID, ownerUserID, requestedScopeID)
		narrow := visibleThreadChannelsForTest(t, ctx, pool, BuildVisibleThreadRefsSQLCTEForRequest("visible_thread_refs", 1, 2, 3, 4, requestedScopeID), workspaceID, ownerMemberID, ownerUserID, requestedScopeID)
		require.Equal(t, broad, narrow, "requested scope %q", requestedScopeID)
	}
}

func visibleThreadChannelsForTest(t *testing.T, ctx context.Context, pool coredb.Queryer, cte string, workspaceID string, memberID string, userID string, requestedScopeID string) []string {
	t.Helper()

	query := fmt.Sprintf("WITH %s SELECT channel_id FROM visible_thread_refs ORDER BY channel_id", cte)
	rows, err := pool.Query(ctx, query, workspaceID, memberID, userID, requestedScopeID)
	require.NoError(t, err)
	defer rows.Close()

	var visible []string
	for rows.Next() {
		var channelID string
		require.NoError(t, rows.Scan(&channelID))
		visible = append(visible, channelID)
	}
	require.NoError(t, rows.Err())
	return visible
}

func TestBuildVisibleThreadRefsSQLCTEFiltersOwnerPrivatePeers(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)

	now := time.Date(2026, 5, 13, 21, 45, 0, 0, time.UTC)
	workspaceID := "wsp_visible_refs_block_scope"
	ownerUserID := "usr_visible_refs_block_owner"
	otherUserID := "usr_visible_refs_block_other"
	ownerMemberID := "mem_visible_refs_block_owner"
	otherAgentMemberID := "mem_visible_refs_block_other_agent"
	ownedAgentMemberID := "mem_visible_refs_block_owned_agent"
	otherDMID := "dm_visible_refs_block_other"
	ownedDMID := "dm_visible_refs_block_owned"

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "visible-refs-block-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, otherUserID, "visible-refs-block-other@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingAgentMember(t, ctx, pool, otherAgentMemberID, workspaceID, now)
	insertMessagingAgentMember(t, ctx, pool, ownedAgentMemberID, workspaceID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO machines (id, workspace_id, created_by_user_id, name, api_key, created_at, updated_at)
VALUES
($1, $3, $4, $1, 'key-visible-refs-block-other', $5, $5),
($2, $3, $6, $2, 'key-visible-refs-block-owned', $5, $5)
`, "mac_visible_refs_block_other", "mac_visible_refs_block_owned", workspaceID, otherUserID, now, ownerUserID)
	insertMessagingAgent(t, ctx, pool, "agt_visible_refs_block_other", workspaceID, "mac_visible_refs_block_other", otherAgentMemberID, "other-agent", now)
	insertMessagingAgent(t, ctx, pool, "agt_visible_refs_block_owned", workspaceID, "mac_visible_refs_block_owned", ownedAgentMemberID, "owned-agent", now)
	insertMessagingDMThread(t, ctx, pool, otherDMID, workspaceID, ownerMemberID, otherAgentMemberID, "thr_visible_refs_block_other_legacy", now)
	insertMessagingDMThread(t, ctx, pool, ownedDMID, workspaceID, ownerMemberID, ownedAgentMemberID, "thr_visible_refs_block_owned_legacy", now)

	query := fmt.Sprintf("WITH %s SELECT channel_id FROM visible_thread_refs ORDER BY channel_id", BuildVisibleThreadRefsSQLCTE("visible_thread_refs", 1, 2, 3, 4))
	rows, err := pool.Query(ctx, query, workspaceID, ownerMemberID, ownerUserID, "")
	require.NoError(t, err)
	defer rows.Close()

	var visible []string
	for rows.Next() {
		var channelID string
		require.NoError(t, rows.Scan(&channelID))
		visible = append(visible, channelID)
	}
	require.NoError(t, rows.Err())
	require.Equal(t, []string{ownedDMID}, visible)
}

func TestBuildVisibleThreadRefsSQLCTEIgnoresClosedChildMembership(t *testing.T) {
	t.Parallel()

	ctx := context.Background()
	pool := startMessagingTestPool(t, ctx)

	now := time.Date(2026, 5, 14, 7, 15, 0, 0, time.UTC)
	const (
		workspaceID       = "wsp_visible_refs_closed_child"
		ownerUserID       = "usr_visible_refs_closed_owner"
		closedUserID      = "usr_visible_refs_closed_child"
		childOnlyUserID   = "usr_visible_refs_child_only"
		ownerMemberID     = "mem_visible_refs_closed_owner"
		closedMemberID    = "mem_visible_refs_closed_child"
		childOnlyMemberID = "mem_visible_refs_child_only"
		channelID         = "chn_visible_refs_closed_parent"
		channelThreadID   = "thr_visible_refs_closed_parent"
		replyChannelID    = "chn_visible_refs_closed_reply"
		replyThreadID     = "thr_visible_refs_closed_reply"
		parentMessageID   = "msg_visible_refs_closed_parent"
		parentMessageSID  = "visclose"
	)

	insertMessagingWorkspace(t, ctx, pool, workspaceID, now)
	insertMessagingUser(t, ctx, pool, ownerUserID, "visible-refs-closed-owner@example.com", now)
	insertMessagingUser(t, ctx, pool, closedUserID, "visible-refs-closed-child@example.com", now)
	insertMessagingUser(t, ctx, pool, childOnlyUserID, "visible-refs-child-only@example.com", now)
	insertMessagingHumanMember(t, ctx, pool, ownerMemberID, workspaceID, ownerUserID, now)
	insertMessagingHumanMember(t, ctx, pool, closedMemberID, workspaceID, closedUserID, now)
	insertMessagingHumanMember(t, ctx, pool, childOnlyMemberID, workspaceID, childOnlyUserID, now)
	insertMessagingChannel(t, ctx, pool, channelID, workspaceID, "visible-refs-closed-parent", ownerMemberID, channelThreadID, now)
	insertMessagingReplyThread(t, ctx, pool, replyChannelID, workspaceID, channelID, parentMessageID, parentMessageSID, replyThreadID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 7, 7, $3, $3, NULL, NULL)
`, replyChannelID, closedMemberID, now)
	mustExecMessaging(t, ctx, pool, `
INSERT INTO scope_memberships (
    scope_id, member_id, visibility, owner_member_id, last_read_seq,
    last_read_global_seq, last_activity_global_seq, owner_context_dismissed_through_global_seq,
    effective_from_seq, effective_to_seq, created_at, updated_at, removed_at, archived_at
)
VALUES ($1, $2, 'public', NULL, 0, 0, 0, 0, 7, NULL, $3, $3, NULL, NULL)
`, replyChannelID, childOnlyMemberID, now)

	query := fmt.Sprintf("WITH %s SELECT channel_id FROM visible_thread_refs ORDER BY channel_id", BuildVisibleThreadRefsSQLCTE("visible_thread_refs", 1, 2, 3, 4))
	rows, err := pool.Query(ctx, query, workspaceID, closedMemberID, closedUserID, "")
	require.NoError(t, err)
	defer rows.Close()

	var visible []string
	for rows.Next() {
		var channelID string
		require.NoError(t, rows.Scan(&channelID))
		visible = append(visible, channelID)
	}
	require.NoError(t, rows.Err())
	require.Empty(t, visible)

	rows, err = pool.Query(ctx, query, workspaceID, childOnlyMemberID, childOnlyUserID, "")
	require.NoError(t, err)
	defer rows.Close()

	visible = nil
	for rows.Next() {
		var channelID string
		require.NoError(t, rows.Scan(&channelID))
		visible = append(visible, channelID)
	}
	require.NoError(t, rows.Err())
	require.Empty(t, visible)
}