package taskboard

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

	"github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/require"

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

type scriptedTaskboardExecResult struct {
	tag coredb.Result
	err error
}

type scriptedTaskboardQueryResult struct {
	rows [][]any
	err  error
}

type scriptedTaskboardRowResult struct {
	values []any
	err    error
}

type scriptedTaskboardQueryExecer struct {
	queries      []string
	args         [][]any
	execResults  []scriptedTaskboardExecResult
	queryResults []scriptedTaskboardQueryResult
	rowResults   []scriptedTaskboardRowResult
}

func (queryer *scriptedTaskboardQueryExecer) Exec(_ context.Context, query string, args ...any) (coredb.Result, error) {
	queryer.queries = append(queryer.queries, query)
	queryer.args = append(queryer.args, append([]any(nil), args...))
	if len(queryer.execResults) == 0 {
		return testExecResult{}, nil
	}
	result := queryer.execResults[0]
	queryer.execResults = queryer.execResults[1:]
	return result.tag, result.err
}

func (queryer *scriptedTaskboardQueryExecer) 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 len(queryer.rowResults) == 0 {
		return scriptedTaskboardRow{}
	}
	result := queryer.rowResults[0]
	queryer.rowResults = queryer.rowResults[1:]
	return scriptedTaskboardRow(result)
}

func (queryer *scriptedTaskboardQueryExecer) Query(_ context.Context, query string, args ...any) (coredb.Rows, error) {
	queryer.queries = append(queryer.queries, query)
	queryer.args = append(queryer.args, append([]any(nil), args...))
	if len(queryer.queryResults) == 0 {
		return nil, errors.New("unexpected query")
	}
	result := queryer.queryResults[0]
	queryer.queryResults = queryer.queryResults[1:]
	if result.err != nil {
		return nil, result.err
	}
	return &scriptedTaskboardRows{rows: result.rows}, nil
}

type scriptedTaskboardRow struct {
	values []any
	err    error
}

func (row scriptedTaskboardRow) Scan(dest ...any) error {
	if row.err != nil {
		return row.err
	}
	for index, value := range row.values {
		reflect.ValueOf(dest[index]).Elem().Set(reflect.ValueOf(value))
	}
	return nil
}

type scriptedTaskboardRows struct {
	rows  [][]any
	index int
}

func (rows *scriptedTaskboardRows) Close() {}

func (rows *scriptedTaskboardRows) Err() error {
	return nil
}

func (rows *scriptedTaskboardRows) Next() bool {
	if rows.index >= len(rows.rows) {
		return false
	}
	rows.index++
	return true
}

func (rows *scriptedTaskboardRows) Scan(dest ...any) error {
	current := rows.rows[rows.index-1]
	for index, value := range current {
		reflect.ValueOf(dest[index]).Elem().Set(reflect.ValueOf(value))
	}
	return nil
}

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

	query, args, err := buildListTasksByIDsQuery("wsp-1", []string{"tsk-1", "tsk-2"})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "tsk-1", "tsk-2"}, args)
	require.Contains(t, query, "requested_task_ids AS (")
	require.Contains(t, query, "task_view_rows AS (")
	require.Contains(t, query, "SELECT $2 AS task_id")
	require.Contains(t, query, "SELECT $3 AS task_id")
	require.Contains(t, query, "EXISTS (")
	require.Contains(t, query, "FROM task_view_rows")
	require.Contains(t, query, "WHERE tb.workspace_id = $1")
	require.Contains(t, query, "requested.task_id = t.id")
	require.NotContains(t, query, "ANY(")
}

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

	query, args, err := buildListTasksByNumbersQuery("wsp-1", "chn-1", []int64{2, 7})
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "chn-1", int64(2), int64(7)}, args)
	require.Contains(t, query, "requested_task_numbers AS (")
	require.Contains(t, query, "task_view_rows AS (")
	require.Contains(t, query, "SELECT CAST($3 AS DECIMAL(20,0)) AS task_number")
	require.Contains(t, query, "SELECT CAST($4 AS DECIMAL(20,0)) AS task_number")
	require.Contains(t, query, "EXISTS (")
	require.Contains(t, query, "FROM task_view_rows")
	require.Contains(t, query, "WHERE tb.workspace_id = $1")
	require.Contains(t, query, "tb.channel_id = $2")
	require.Contains(t, query, "requested.task_number = t.task_number")
	require.NotContains(t, query, "ANY(")
}

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

	byIDQuery, byIDArgs := buildTaskByIDQuery("wsp-1", "tsk-1")
	require.Equal(t, []any{"wsp-1", "tsk-1"}, byIDArgs)
	require.Contains(t, byIDQuery, "WHERE tb.workspace_id = $1")
	require.Contains(t, byIDQuery, "t.id = $2")
	require.NotContains(t, byIDQuery, "WHERE id = $2")

	byNumberQuery, byNumberArgs := buildTaskByNumberQuery("wsp-1", "chn-1", 3)
	require.Equal(t, []any{"wsp-1", "chn-1", int64(3)}, byNumberArgs)
	require.Contains(t, byNumberQuery, "WHERE tb.workspace_id = $1")
	require.Contains(t, byNumberQuery, "tb.channel_id = $2")
	require.Contains(t, byNumberQuery, "t.task_number = $3")
	require.NotContains(t, byNumberQuery, "WHERE channel_id = $2")
}

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

	scope := taskBrowseScope{
		WorkspaceID: "wsp-1",
		MemberID:    "mem-1",
		ChannelID:   "chn-1",
		Query:       normalizeTaskBrowseQuery(TaskBrowseQuery{}),
	}

	countQuery, _, err := buildTaskBrowseCountQuery(scope)
	require.NoError(t, err)
	require.NotContains(t, countQuery, "task_artifacts")
	require.NotContains(t, countQuery, "task_checklist_items")
	require.NotContains(t, countQuery, "subtask_counts")

	boardCountsQuery, _, err := buildTaskBrowseBoardCountsQuery(scope, defaultTaskBoardLayout())
	require.NoError(t, err)
	require.NotContains(t, boardCountsQuery, "task_artifacts")
	require.NotContains(t, boardCountsQuery, "task_checklist_items")
	require.NotContains(t, boardCountsQuery, "subtask_counts")
}

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

	scope := taskBrowseScope{
		WorkspaceID: "wsp-1",
		MemberID:    "mem-1",
		ChannelID:   "chn-1",
		Query:       normalizeTaskBoardBrowseQuery(TaskBrowseQuery{SortMode: "recently_updated"}),
	}
	layout := taskBoardLayout{
		ColumnOrder: []string{"todo", "in_progress"},
	}

	query, args, err := buildTaskBrowseBoardRowsQuery(scope, layout, "in_progress", TaskBrowseCursor{})
	require.NoError(t, err)
	require.Contains(t, query, "COALESCE(t.status, '') = $1")
	require.NotContains(t, query, "'in_progress'")
	require.Equal(t, []any{"in_progress", "wsp-1", "chn-1", scope.Query.Limit + 1}, args)
}

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

	tests := []struct {
		name      string
		status    string
		condition string
		arg       any
	}{
		{
			name:      "todo",
			status:    string(StatusTodo),
			condition: "t.status = $1",
			arg:       string(StatusTodo),
		},
		{
			name:      "active",
			status:    "active",
			condition: "t.status <> $1",
			arg:       string(StatusDone),
		},
	}
	for _, test := range tests {
		test := test
		t.Run(test.name, func(t *testing.T) {
			t.Parallel()
			builder := &taskBrowseSQLBuilder{}
			condition, err := buildTaskBrowseStatusCondition(builder, test.status)
			require.NoError(t, err)
			require.Equal(t, test.condition, condition)
			require.Equal(t, []any{test.arg}, builder.args)
		})
	}

	builder := &taskBrowseSQLBuilder{}
	condition, err := buildTaskBrowseStatusCondition(builder, "unexpected")
	require.Error(t, err)
	require.ErrorContains(t, err, "invalid status")
	require.Empty(t, condition)
	require.Empty(t, builder.args)
}

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

	scope := taskBrowseScope{
		WorkspaceID:   "wsp-1",
		MemberID:      "mem-1",
		ChannelID:     "chn-1",
		Query:         normalizeTaskBrowseQuery(TaskBrowseQuery{SearchQuery: " Alpha  Profile "}),
		SearchApplied: true,
		SearchTaskIDs: []string{"tsk-alpha-2", "tsk-alpha-1"},
	}

	query, args, err := buildTaskBrowseCountQuery(scope)
	require.NoError(t, err)
	require.Equal(t, []any{"wsp-1", "chn-1", "tsk-alpha-2", "tsk-alpha-1"}, args)
	require.Contains(t, query, "t.id IN ($3, $4)")
	require.NotContains(t, query, "CONCAT_WS")
	require.NotContains(t, query, "BrowseSearch")
	require.NotContains(t, query, " LIKE ")
}

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

	scope := taskBrowseScope{
		WorkspaceID: "wsp-1",
		MemberID:    "mem-1",
		ChannelID:   "chn-1",
		Query:       normalizeTaskBrowseQuery(TaskBrowseQuery{SearchQuery: "alpha"}),
	}

	_, _, err := buildTaskBrowseCountQuery(scope)
	require.Error(t, err)
	require.ErrorContains(t, err, "task search plan is required")
}

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

	query := buildTaskViewRowsCTE("$1")

	require.Contains(t, query, "FROM task_artifacts ta")
	require.Contains(t, query, "ta.task_id = t.id")
	require.Contains(t, query, "FROM task_checklist_items tci")
	require.Contains(t, query, "tci.task_id = t.id")
	require.Contains(t, query, "subtask.parent_task_ref = t.id")
	require.NotContains(t, query, "FROM task_artifacts\n    GROUP BY task_id")
	require.NotContains(t, query, "FROM task_checklist_items\n    WHERE list_kind = 'checkpoint'\n    GROUP BY task_id")
	require.NotContains(t, query, "subtask_counts")
}

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

	query, args := buildListWorkspaceTasksQuery("wsp-1", "mem-1", false)

	require.Equal(t, []any{"wsp-1", "mem-1"}, args)
	require.Contains(t, query, "WHERE tb.workspace_id = $1\n  AND t.parent_task_ref = ''")
	require.NotContains(t, query, "task_view_rows.parent_task_ref = ''")
}

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

	builder := &taskBrowseSQLBuilder{}
	conditions, err := buildTaskBrowseQuickFilterConditions(builder, " wsp-1 ", " mem-1 ", []string{"related_to_me", "blocked", "needs_clarification"})
	require.NoError(t, err)
	relatedToMeCondition := `(` + taskBrowseAssigneeRefExpr + ` = $1 OR COALESCE(t.created_by_ref, t.created_by_member_id, '') = $1 OR EXISTS (
  SELECT 1
  FROM agents owned_agent
  JOIN machines owned_machine
    ON owned_machine.workspace_id = owned_agent.workspace_id
   AND owned_machine.id = owned_agent.machine_id
   AND owned_machine.status = 'active'
  JOIN members owner_member
    ON owner_member.workspace_id = owned_agent.workspace_id
   AND owner_member.user_id = owned_machine.created_by_user_id
   AND owner_member.type = 'human'
   AND owner_member.status = 'active'
  WHERE owned_agent.workspace_id = $2
    AND owned_agent.status <> 'archived'
    AND owner_member.id = $1
    AND owned_agent.member_id IN (NULLIF(` + taskBrowseAssigneeRefExpr + `, ''), NULLIF(COALESCE(t.created_by_ref, t.created_by_member_id, ''), ''))
))`
	require.Equal(t, []string{
		relatedToMeCondition,
		"TRIM(t.blocked_reason) <> ''",
		"t.status = 'todo' AND t.discussion_count > 0 AND " + taskBrowseAssigneeRefExpr + " = '' AND TRIM(t.blocked_reason) = ''",
	}, conditions)
	require.Equal(t, []any{"mem-1", "wsp-1"}, builder.args)
}

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

	statusBuilder := &taskBrowseSQLBuilder{}
	statusCondition, err := buildTaskBrowseStructuredFilterCondition(statusBuilder, "status", []string{" todo ", "done"})
	require.NoError(t, err)
	require.Equal(t, "t.status IN ($1, $2)", statusCondition)
	require.Equal(t, []any{"todo", "done"}, statusBuilder.args)

	channelBuilder := &taskBrowseSQLBuilder{}
	channelCondition, err := buildTaskBrowseStructuredFilterCondition(channelBuilder, "channel", []string{" chn-1 ", "chn-2"})
	require.NoError(t, err)
	require.Equal(t, "tb.channel_id IN ($1, $2)", channelCondition)
	require.Equal(t, []any{"chn-1", "chn-2"}, channelBuilder.args)

	assigneeBuilder := &taskBrowseSQLBuilder{}
	assigneeCondition, err := buildTaskBrowseStructuredFilterCondition(assigneeBuilder, "assignee", []string{"usr-1", "agt-1"})
	require.NoError(t, err)
	require.Equal(t, "(COALESCE(NULLIF(assignee_agent.id, ''), NULLIF(assignee_user.id, '')) IN ($1, $2))", assigneeCondition)
	require.Equal(t, []any{"usr-1", "agt-1"}, assigneeBuilder.args)

	kindBuilder := &taskBrowseSQLBuilder{}
	kindCondition, err := buildTaskBrowseStructuredFilterCondition(kindBuilder, "kind", []string{"feature"})
	require.NoError(t, err)
	require.Equal(t, "(kind_option.option_key IN ($1))", kindCondition)
	require.Equal(t, []any{"feature"}, kindBuilder.args)

	priorityBuilder := &taskBrowseSQLBuilder{}
	priorityCondition, err := buildTaskBrowseStructuredFilterCondition(priorityBuilder, "priority", []string{"p1"})
	require.NoError(t, err)
	require.Equal(t, "(priority_option.option_key IN ($1))", priorityCondition)
	require.Equal(t, []any{"p1"}, priorityBuilder.args)
}

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

	now := time.Date(2026, 4, 4, 20, 0, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		execResults: []scriptedTaskboardExecResult{
			{err: &mysql.MySQLError{Number: 1062, Message: "Duplicate entry 'chn-1' for key 'task_boards_channel_id_key'"}},
		},
	}

	err := insertTaskBoardIfMissing(context.Background(), queryer, "brd-1", "wsp-1", "chn-1", 1, now)
	require.Error(t, err)
	require.ErrorContains(t, err, "insert task board")
	require.Len(t, queryer.queries, 1)
	require.NotContains(t, queryer.queries[0], "ON CONFLICT")
}

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

	now := time.Date(2026, 4, 4, 20, 5, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		rowResults: []scriptedTaskboardRowResult{
			{values: []any{"brd-1"}},
		},
	}

	err := insertTaskCounterIfMissing(context.Background(), queryer, "brd-1", 4, now)
	require.NoError(t, err)
	require.Len(t, queryer.queries, 1)
	require.Contains(t, queryer.queries[0], "FROM task_counters")
	require.Empty(t, queryer.execResults)
}

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

	now := time.Date(2026, 4, 4, 20, 6, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		rowResults: []scriptedTaskboardRowResult{
			{err: sql.ErrNoRows},
		},
		execResults: []scriptedTaskboardExecResult{
			{err: &mysql.MySQLError{Number: 1062, Message: "Duplicate entry 'brd-1' for key 'PRIMARY'"}},
		},
	}

	err := insertTaskCounterIfMissing(context.Background(), queryer, "brd-1", 4, now)
	require.Error(t, err)
	require.ErrorContains(t, err, "insert task counter")
	require.Len(t, queryer.queries, 2)
	require.Contains(t, queryer.queries[0], "FROM task_counters")
	require.Contains(t, queryer.queries[1], "WHERE NOT EXISTS")
}

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

	now := time.Date(2026, 4, 14, 1, 35, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{}

	err := insertTaskBoardIfMissing(context.Background(), queryer, "brd-1", "wsp-1", "chn-1", 1, now)
	require.NoError(t, err)
	require.Len(t, queryer.queries, 1)
	require.Contains(t, queryer.queries[0], "WHERE NOT EXISTS")
	require.NotContains(t, queryer.queries[0], "ON CONFLICT")
}

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

	now := time.Date(2026, 4, 14, 1, 36, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		rowResults: []scriptedTaskboardRowResult{
			{err: sql.ErrNoRows},
		},
	}

	err := insertTaskCounterIfMissing(context.Background(), queryer, "brd-1", 4, now)
	require.NoError(t, err)
	require.Len(t, queryer.queries, 2)
	require.Contains(t, queryer.queries[0], "FROM task_counters")
	require.Contains(t, queryer.queries[1], "WHERE NOT EXISTS")
	require.Contains(t, queryer.queries[1], "MAX(t.task_number) + 1")
	require.NotContains(t, queryer.queries[0], "ON CONFLICT")
	require.NotContains(t, queryer.queries[1], "ON CONFLICT")
}

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

	now := time.Date(2026, 4, 4, 20, 10, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		execResults: []scriptedTaskboardExecResult{
			{tag: testExecResult{rowsAffected: 1}},
		},
		rowResults: []scriptedTaskboardRowResult{
			{values: []any{int64(9)}},
		},
	}

	startingTaskNumber, err := allocateTaskNumbersWithExecer(context.Background(), queryer, taskBoardRecord{ID: "brd-1"}, 3, now)
	require.NoError(t, err)
	require.Equal(t, int64(6), startingTaskNumber)
	require.Len(t, queryer.queries, 2)
	require.NotContains(t, queryer.queries[0], "RETURNING")
	require.NotContains(t, queryer.queries[1], "RETURNING")
}

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

	now := time.Date(2026, 4, 4, 20, 11, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		execResults: []scriptedTaskboardExecResult{
			{tag: testExecResult{rowsAffected: 1}},
			{tag: testExecResult{rowsAffected: 1}},
		},
		rowResults: []scriptedTaskboardRowResult{
			{values: []any{int64(12)}},
		},
	}

	_, err := allocateTaskNumbersWithExecer(context.Background(), queryer, taskBoardRecord{ID: "brd-1"}, 2, now)
	require.NoError(t, err)
	for _, query := range queryer.queries {
		require.NotContains(t, query, "UPDATE task_boards")
	}
}

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

	now := time.Date(2026, 4, 4, 20, 15, 0, 0, time.UTC)
	queryer := &scriptedTaskboardQueryExecer{
		execResults: []scriptedTaskboardExecResult{
			{tag: testExecResult{}},
		},
	}

	_, err := allocateTaskNumbersWithExecer(context.Background(), queryer, taskBoardRecord{ID: "brd-1"}, 2, now)
	require.ErrorIs(t, err, ErrNotFound)
	require.Len(t, queryer.queries, 1)
	require.NotContains(t, queryer.queries[0], "RETURNING")
}