package message

import (
	"strings"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
)

var (
	benchmarkQuerySink string
	benchmarkArgsSink  []any
)

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

	query, args, err := BuildWorkspaceHasUnreadForUserScopedQuery("usr-1", []string{"wsp-1", "wsp-2", "wsp-1"})
	require.NoError(t, err)
	require.Equal(t, []any{"usr-1", "wsp-1", "wsp-2"}, args)
	require.Contains(t, query, "requested_workspaces AS (")
	require.Contains(t, query, "workspace_members AS (")
	require.Contains(t, query, "FROM workspace_members wm")
	require.Contains(t, query, "WHERE EXISTS (")
	require.Contains(t, query, "unread_scope.type IN ('channel', 'dm', 'thread', 'service_account')")
	require.Contains(t, query, "unread_scope.type IN ('channel', 'service_account')")
	require.Contains(t, query, "AND EXISTS (")
	require.Contains(t, query, "FROM scope_messages m")
	require.Contains(t, query, "m.seq > sm.last_read_seq")
	require.Contains(t, query, "JOIN scopes unread_scope")
	require.Contains(t, query, "unread_scope.latest_seq > sm.last_read_seq")
	require.Contains(t, query, "m.seq >= sm.effective_from_seq")
	require.Contains(t, query, "COALESCE(NULLIF(m.notify_principal_member_id, ''), m.author_member_id) <> wm.member_id")
	require.Contains(t, query, "(sns.state IS NULL OR sns.state IN ('active', 'quiet'))")
	require.Contains(t, query, "(parent.type = 'channel' AND parent_sm.member_id IS NOT NULL)")
	require.Contains(t, query, "mac.created_by_user_id <> $1")
	require.Contains(t, query, "LIMIT 1")
	require.NotContains(t, query, "visible_thread_refs AS (")
	require.NotContains(t, query, "SELECT DISTINCT")
	require.NotContains(t, query, "visible_thread_refs.")
	require.NotContains(t, query, "JOIN scope_messages m")
	require.NotContains(t, query, "COUNT(")
	require.NotContains(t, query, "GROUP BY")
	require.NotContains(t, query, "last_read_global_seq")
	require.NotContains(t, query, "workspace_global_seq >")
}

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

	query, args, err := BuildMessageSyncDirtyScopeCandidatesQuery("wsp-1", "mem-1", []string{"cur-1", "cur-0"}, 25, 200)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", "cur-1", "cur-0", 25, 200}, args)
	require.Contains(t, query, "scope_dirty_window AS (")
	require.Contains(t, query, "FROM scope_sync_dirty dirty")
	require.Contains(t, query, "member_dirty_window AS (")
	require.Contains(t, query, "FROM member_scope_sync_dirty dirty")
	require.Contains(t, query, "dirty_candidate_window AS (")
	require.Contains(t, query, "dirty_candidate_scopes AS (")
	require.Contains(t, query, "visible_dirty_scopes AS (")
	require.Contains(t, query, "ORDER BY dirty.updated_at ASC, dirty.scope_id ASC")
	require.Contains(t, query, "LIMIT $6")
	require.Contains(t, query, "JOIN scope_memberships sm")
	require.Contains(t, query, "LEFT JOIN scope_notification_settings sns")
	require.Contains(t, query, "(sns.state IS NULL OR sns.state IN ('active', 'quiet'))")
	require.Contains(t, query, "cursor_frontier_ids AS (")
	require.Contains(t, query, "SELECT $3 AS cursor_id")
	require.Contains(t, query, "SELECT $4 AS cursor_id")
	require.Contains(t, query, "JOIN cursor_frontier_ids cursor_row")
	require.Contains(t, query, "FROM message_sync_cursor_items item")
	require.Contains(t, query, "MAX(item.frontier_seq) AS frontier_seq")
	require.Contains(t, query, "GROUP BY item.scope_id")
	require.Contains(t, query, "eligible_dirty_scopes AS (")
	require.Contains(t, query, "sm.effective_from_seq")
	require.Contains(t, query, "GREATEST(COALESCE(frontier_versions.frontier_seq, 0), GREATEST(dirty.effective_from_seq - 1, 0)) AS frontier_seq")
	require.Contains(t, query, "dirty.latest_message_seq > GREATEST(COALESCE(frontier_versions.frontier_seq, 0), GREATEST(dirty.effective_from_seq - 1, 0))")
	scanLimitOffset := strings.Index(query, "LIMIT $6")
	visibleOffset := strings.Index(query, "visible_dirty_scopes AS (")
	filterOffset := strings.Index(query, "dirty.latest_message_seq > GREATEST(COALESCE(frontier_versions.frontier_seq, 0), GREATEST(dirty.effective_from_seq - 1, 0))")
	limitOffset := strings.Index(query, "LIMIT $5")
	require.NotEqual(t, -1, scanLimitOffset)
	require.NotEqual(t, -1, visibleOffset)
	require.NotEqual(t, -1, filterOffset)
	require.NotEqual(t, -1, limitOffset)
	require.Less(t, scanLimitOffset, visibleOffset, "dirty scope candidates must apply the raw scan budget before visibility and frontier joins")
	require.Less(t, filterOffset, limitOffset, "dirty scope candidates must filter already-covered frontiers before applying the page limit")
	require.NotContains(t, query, "\nscope_dirty AS (")
	require.NotContains(t, query, "\nmember_dirty AS (")
	require.NotContains(t, query, "\ndirty_scopes AS (")
	require.NotContains(t, query, "WITH RECURSIVE")
	require.NotContains(t, query, "message_sync_cursors")
	require.NotContains(t, query, "cursor_chain")
	require.NotContains(t, query, "WITH RECURSIVE dirty_page AS")
	require.NotContains(t, query, "ROW_NUMBER() OVER")
	require.NotContains(t, query, "workspace_global_seq")
	require.NotContains(t, query, "DELETE FROM member_scope_sync_dirty")
}

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

	query, _, err := BuildMessageSyncDirtyScopeCandidatesQuery("wsp-1", "mem-1", []string{"cur-1"}, 25, 200)
	require.NoError(t, err)
	require.Contains(t, query, "dirty.latest_activity_seq")
	require.Contains(t, query, "MAX(dirty.latest_activity_seq) AS latest_activity_seq")
	require.Contains(t, query, "dirty.latest_message_seq > 0")
	require.Contains(t, query, "dirty.latest_message_seq > GREATEST(COALESCE(frontier_versions.frontier_seq, 0), GREATEST(dirty.effective_from_seq - 1, 0))")
	require.NotContains(t, query, "dirty.latest_activity_seq >")
	require.NotContains(t, query, "frontier_activity_seq")

	eligibleOffset := strings.Index(query, "eligible_dirty_scopes AS (")
	require.NotEqual(t, -1, eligibleOffset)
	eligibleAndPageSQL := query[eligibleOffset:]
	require.NotContains(t, eligibleAndPageSQL, "latest_activity_seq", "browser message sync candidate eligibility and payload remain message-seq only")
}

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

	query, args, err := BuildMessageSyncInitialCursorItemsQuery("wsp-1", "mem-1", "cur-1", time.Date(2026, 5, 21, 14, 0, 0, 0, time.UTC))
	require.NoError(t, err)
	require.Len(t, args, 4)
	require.Contains(t, query, "frontier_seq, frontier_activity_seq")
	require.Contains(t, query, "GREATEST(s.latest_seq, sm.effective_from_seq - 1, 0) AS frontier_seq")
	require.Contains(t, query, "sm.last_activity_global_seq AS frontier_activity_seq")
}

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

	query, args, err := BuildMessageSyncMessagesForScopesQuery("wsp-1", []MessageSyncDirtyScopeCandidate{{
		ScopeID:          "scp-1",
		FrontierSeq:      4,
		LatestMessageSeq: 9,
	}}, 20)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "scp-1", int64(4), int64(9), 20}, args)
	require.Contains(t, query, "candidate_scopes AS (")
	require.Contains(t, query, "m.seq > candidate.frontier_seq")
	require.Contains(t, query, "m.seq <= candidate.latest_message_seq")
	require.Contains(t, query, "ROW_NUMBER() OVER (PARTITION BY m.scope_id ORDER BY m.seq ASC")
	require.Contains(t, query, "WHERE scope_row_number <=")
	require.Contains(t, query, "user_avatar_url")
	require.Contains(t, query, "agent_avatar_url")
	require.Contains(t, query, "m.edited_at")
	require.Contains(t, query, "COALESCE(m.edited_by_member_id, '') AS edited_by_member_id")
	require.Contains(t, query, "m.edit_revision")
	require.NotContains(t, query, "workspace_global_seq")
	require.NotContains(t, query, "member_scope_sync_dirty")
}

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

	query, _, err := BuildMessageSyncMessagesForScopesQuery("wsp-1", []MessageSyncDirtyScopeCandidate{{
		ScopeID:          "scp-1",
		FrontierSeq:      4,
		LatestMessageSeq: 9,
	}}, 20)
	require.NoError(t, err)
	require.Contains(t, query, "JOIN scope_messages m")
	require.Contains(t, query, HumanVisibleMessageTypeSQL("m"))
	require.NotContains(t, query, "scope_activity_events")
	require.NotContains(t, query, "scope_message_reactions")
	require.NotContains(t, query, "scope_fanout_outbox")
	require.NotContains(t, query, "message_delivery")
}

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

	query, args, err := BuildMessageDiagnosticsScopeQuery("wsp-1", "msg-1", "mem-1", "usr-1")
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "msg-1", "mem-1", "usr-1"}, args)

	require.Contains(t, query, "JOIN scopes message_scope")
	require.Contains(t, query, "message_scope.id = m.scope_id")
	require.Contains(t, query, "message_scope.status = 'active'")
	require.Contains(t, query, "JOIN scope_memberships sm")
	require.Contains(t, query, "sm.member_id = $3")
	require.Contains(t, query, scopeReadableMessagePredicateSQL)
	require.Contains(t, query, "scope_private_grants spg_scope")
	require.Contains(t, query, "LEFT JOIN scopes parent_scope")
	require.Contains(t, query, "LEFT JOIN scope_memberships peer")
	require.Contains(t, query, "LEFT JOIN scope_memberships parent_peer")
	require.Contains(t, query, "mac.created_by_user_id <> $4")
	require.Contains(t, query, "LEFT JOIN members author_member")
	require.Contains(t, query, "COALESCE(author_member.user_id, '') AS author_user_id")
	require.Contains(t, query, "message_scope.root_message_id")
	require.NotContains(t, query, "visible_thread_refs AS (")
	require.NotContains(t, query, "JOIN visible_thread_refs")
	require.NotContains(t, query, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("LEFT JOIN", "channels"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("LEFT JOIN", "reply", "threads"))
}

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

	query, _, err := BuildMessageDiagnosticsScopeQuery("wsp-1", "msg-1", "mem-1", "usr-1")
	require.NoError(t, err)
	require.Contains(t, query, "FROM scope_messages m")
	require.Contains(t, query, "WHERE m.workspace_id = $1")
	require.Contains(t, query, "AND m.id = $2")
	require.NotContains(t, query, "scope_fanout_outbox")
	require.NotContains(t, query, "scope_activity_events")
	require.NotContains(t, query, "message_recovery_points")
	require.NotContains(t, query, "runtime_events")
	require.NotContains(t, query, "delivery historical")
}

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

	query, args, err := BuildAgentUnreadThreadsQuery("wsp-1", "mem-1", time.Date(2026, 4, 5, 14, 10, 0, 0, time.UTC))
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "mem-1", MessageTypeChannelShareCard, MessageTypeOwnerQuote, MessageTypeSystem, MessageTypeTaskDescriptionAttachment, MessageTypeTaskDispatch, MessageTypeThreadShareCard, time.Date(2026, 4, 5, 14, 10, 0, 0, time.UTC)}, args)
	require.Contains(t, query, "agent_dense_scope_labels AS (")
	require.Contains(t, query, "FROM scope_memberships sm")
	require.Contains(t, query, "JOIN scopes s")
	require.Contains(t, query, "s.id AS thread_id")
	require.Contains(t, query, "s.id AS scope_id")
	require.Contains(t, query, "CONCAT('thread-', NULLIF(s.root_message_short_id, ''))")
	require.Contains(t, query, "sm.archived_at IS NULL")
	require.NotContains(t, query, legacyTableNameForMessageTest("channel", "agent", "entries"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("FROM", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("JOIN", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("LEFT JOIN", "channels"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("JOIN", "channels"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("LEFT JOIN", "dm", "threads"))
	require.NotContains(t, query, legacySQLTableRefForMessageTest("JOIN", "dm", "threads"))
	require.NotContains(t, query, legacyTableNameForMessageTest("reply", "threads"))
	require.NotContains(t, query, "thread_read_states")
	require.Contains(t, query, "JOIN scope_messages sm")
	require.Contains(t, query, "sm.seq > agent_dense_scope_labels.last_read_seq")
	require.Contains(t, query, "COALESCE(NULLIF(sm.notify_principal_member_id, ''), sm.author_member_id) <> $2")
	require.NotContains(t, query, legacyTableNameForMessageTest("targeted", "delivery", "events"))
	require.Contains(t, query, "sm.message_type NOT IN ($3, $4, $5, $6, $7, $8)")
}

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

	query, args := BuildThreadTimelineReadQuery("thr-1", "chn-1", 20, 99)
	require.Len(t, args, 3)
	require.Contains(t, query, "timeline_page AS (")
	require.Contains(t, query, "FROM timeline_page")
	require.Contains(t, query, "u.avatar_url")
	require.Contains(t, query, "a.avatar_url")
}

func BenchmarkBuildThreadTimelineReadQuery(b *testing.B) {
	b.ReportAllocs()
	for i := 0; i < b.N; i++ {
		query, args := BuildThreadTimelineReadQuery("thr-1", "mem-1", 200, 4096)
		benchmarkQuerySink = query
		benchmarkArgsSink = args
	}
}

func BenchmarkBuildAgentUnreadThreadsQuery(b *testing.B) {
	ownerQuoteCutoff := time.Date(2026, 4, 5, 14, 10, 0, 0, time.UTC)
	b.ReportAllocs()
	for i := 0; i < b.N; i++ {
		query, args, err := BuildAgentUnreadThreadsQuery("wsp-1", "mem-1", ownerQuoteCutoff)
		if err != nil {
			b.Fatal(err)
		}
		benchmarkQuerySink = query
		benchmarkArgsSink = args
	}
}