package message

import (
	"context"
	"database/sql"
	"testing"
	"time"

	"github.com/stretchr/testify/require"

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

type scriptedQueryRower struct {
	queries []string
	args    [][]any
	row     coredb.Row
}

func (queryer *scriptedQueryRower) QueryRow(_ context.Context, query string, args ...any) coredb.Row {
	queryer.queries = append(queryer.queries, query)
	queryer.args = append(queryer.args, append([]any(nil), args...))
	if queryer.row == nil {
		return scriptedInt64Row{err: sql.ErrNoRows}
	}
	return queryer.row
}

type scriptedInt64Row struct {
	value int64
	err   error
}

func (row scriptedInt64Row) Scan(dest ...any) error {
	if row.err != nil {
		return row.err
	}
	if len(dest) != 1 {
		return sql.ErrNoRows
	}
	ptr, ok := dest[0].(*int64)
	if !ok || ptr == nil {
		return sql.ErrNoRows
	}
	*ptr = row.value
	return nil
}

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

	now := time.Date(2026, 4, 4, 16, 0, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	err := WriteThreadReadStateByGlobalSeq(context.Background(), execer, "mem-1", "scope-1", 4, 9, now)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "UPDATE scope_memberships")
	require.Contains(t, execer.queries[0], "last_read_global_seq = GREATEST(last_read_global_seq, $4)")
	require.Contains(t, execer.queries[0], "last_read_seq < $3")
	require.Contains(t, execer.queries[0], "last_read_global_seq < $4")
	require.NotContains(t, execer.queries[0], "thread_read_states")
	require.Equal(t, []any{"mem-1", "scope-1", int64(4), int64(9), now}, execer.args[0])
}

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

	now := time.Date(2026, 7, 10, 6, 30, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	err := WriteActivityReadStateByGlobalSeq(context.Background(), execer, " mem-1 ", " scope-1 ", 42, now)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "last_activity_global_seq = GREATEST(last_activity_global_seq, $3)")
	require.Contains(t, execer.queries[0], "last_activity_global_seq < $3")
	require.Equal(t, []any{"mem-1", "scope-1", int64(42), now}, execer.args[0])
}

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

	now := time.Date(2026, 5, 13, 9, 20, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	err := WriteThreadReadStateExact(context.Background(), execer, " mem-1 ", " scope-1 ", 6, 60, now)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "last_read_seq = $3")
	require.Contains(t, execer.queries[0], "last_read_global_seq = $4")
	require.Contains(t, execer.queries[0], "AND (last_read_seq <> $3 OR last_read_global_seq <> $4)")
	require.Equal(t, []any{"mem-1", "scope-1", int64(6), int64(60), now}, execer.args[0])
}

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

	execer := &scriptedExecOnly{}

	err := WriteThreadOwnerContextDismissState(context.Background(), execer, " mem-1 ", " scope-1 ", 13)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "UPDATE scope_memberships")
	require.Contains(t, execer.queries[0], "GREATEST(owner_context_dismissed_through_global_seq, $3)")
	require.NotContains(t, execer.queries[0], "thread_read_states")
	require.Equal(t, []any{"mem-1", "scope-1", int64(13)}, execer.args[0])
}

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

	queryer := &scriptedQueryRower{row: scriptedInt64Row{err: sql.ErrNoRows}}

	globalSeq, err := LoadThreadOwnerContextDismissedThroughGlobalSeq(context.Background(), queryer, " mem-1 ", " scope-1 ")
	require.NoError(t, err)
	require.Equal(t, int64(0), globalSeq)
	require.Len(t, queryer.queries, 1)
	require.Contains(t, queryer.queries[0], "FROM scope_memberships")
	require.Contains(t, queryer.queries[0], "owner_context_dismissed_through_global_seq")
	require.Equal(t, []any{"mem-1", "scope-1"}, queryer.args[0])
}

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

	ownerQuoteReadAt := time.Date(2026, 4, 4, 16, 30, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	err := UpsertThreadReadState(context.Background(), execer, "mem-1", "scope-1", 12, &ownerQuoteReadAt)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "UPDATE scope_memberships")
	require.Contains(t, execer.queries[0], "last_read_seq = GREATEST(last_read_seq, $3)")
	require.Contains(t, execer.queries[0], "last_read_seq < $3")
	require.Contains(t, execer.queries[0], "updated_at < $4")
	require.NotContains(t, execer.queries[0], "thread_read_states")
	require.Equal(t, []any{"mem-1", "scope-1", int64(12), ownerQuoteReadAt, true}, execer.args[0])
}

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

	execer := &scriptedExecOnly{}

	err := UpsertThreadReadState(context.Background(), execer, "mem-1", "scope-1", 12, nil)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "UPDATE scope_memberships")
	require.Contains(t, execer.queries[0], "ELSE updated_at")
	require.Contains(t, execer.queries[0], "OR ($5 AND updated_at < $4)")
	require.Equal(t, []any{"mem-1", "scope-1", int64(12), OwnerQuoteReadFloor(), false}, execer.args[0])
}

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

	now := time.Date(2026, 4, 5, 10, 0, 0, 0, time.UTC)
	execer := &scriptedExecOnly{}

	err := InsertThreadSubscriptionIfMissing(context.Background(), execer, "mem-1", "chn-thread-1", now)
	require.NoError(t, err)
	require.Len(t, execer.queries, 1)
	require.Contains(t, execer.queries[0], "INSERT IGNORE INTO scope_notification_settings")
	require.NotContains(t, execer.queries[0], legacyTableNameForMessageTest("thread", "subscriptions"))
	require.NotContains(t, execer.queries[0], legacyTableNameForMessageTest("space", "subscriptions"))
}