package organization

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

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

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

type portableExecCall struct {
	query string
	args  []any
}

type portableExecResponse struct {
	rowsAffected int64
	err          error
}

type recordingPortableExecer struct {
	queryRowResponses []portableQueryRowResponse
	queryRowCalls     []portableExecCall
	execResponses     []portableExecResponse
	execCalls         []portableExecCall
}

type portableQueryRowResponse struct {
	err error
}

func (execer *recordingPortableExecer) QueryRow(_ context.Context, query string, args ...any) coredb.Row {
	execer.queryRowCalls = append(execer.queryRowCalls, portableExecCall{
		query: query,
		args:  append([]any(nil), args...),
	})
	if len(execer.queryRowResponses) == 0 {
		return portableRow{err: sql.ErrNoRows}
	}
	response := execer.queryRowResponses[0]
	execer.queryRowResponses = execer.queryRowResponses[1:]
	return portableRow(response)
}

func (execer *recordingPortableExecer) Exec(_ context.Context, query string, args ...any) (coredb.Result, error) {
	execer.execCalls = append(execer.execCalls, portableExecCall{
		query: query,
		args:  append([]any(nil), args...),
	})
	if len(execer.execResponses) == 0 {
		return portableResult{rowsAffected: 0}, nil
	}
	response := execer.execResponses[0]
	execer.execResponses = execer.execResponses[1:]
	return portableResult{rowsAffected: response.rowsAffected}, response.err
}

type portableResult struct {
	rowsAffected int64
}

func (result portableResult) RowsAffected() int64 {
	return result.rowsAffected
}

type portableRow struct {
	err error
}

func (row portableRow) Scan(dest ...any) error {
	if row.err != nil {
		return row.err
	}
	target, ok := dest[0].(*int)
	if !ok {
		return errors.New("expected *int destination")
	}
	*target = 1
	return nil
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{{}},
		execResponses:     []portableExecResponse{{rowsAffected: 1}},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id) VALUES ($1)",
		[]any{"mem-1"},
	)
	require.NoError(t, err)
	require.Len(t, execer.execCalls, 1)
	require.Contains(t, execer.execCalls[0].query, "UPDATE memberships")
	require.Equal(t, []any{"mem-1"}, execer.execCalls[0].args)
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{{err: sql.ErrNoRows}},
		execResponses: []portableExecResponse{
			{rowsAffected: 1},
		},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id, status) VALUES ($1, 'active')",
		[]any{"mem-1"},
	)
	require.NoError(t, err)
	require.Len(t, execer.queryRowCalls, 1)
	require.Len(t, execer.execCalls, 1)
	require.Contains(t, execer.execCalls[0].query, "INSERT INTO memberships")
	require.NotContains(t, execer.execCalls[0].query, "ON CONFLICT")
	require.NotContains(t, execer.execCalls[0].query, "ON DUPLICATE")
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{
			{err: sql.ErrNoRows},
			{},
		},
		execResponses: []portableExecResponse{
			{err: &mysql.MySQLError{Number: 1062, Message: "Duplicate entry 'mem-1' for key 'PRIMARY'"}},
			{rowsAffected: 1},
		},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id, status) VALUES ($1, 'active')",
		[]any{"mem-1"},
	)
	require.NoError(t, err)
	require.Len(t, execer.queryRowCalls, 2)
	require.Len(t, execer.execCalls, 2)
	require.Contains(t, execer.execCalls[1].query, "UPDATE memberships")
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{{}},
		execResponses:     []portableExecResponse{{err: errors.New("boom")}},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id) VALUES ($1)",
		[]any{"mem-1"},
	)
	require.EqualError(t, err, "boom")
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{
			{},
			{err: sql.ErrNoRows},
		},
		execResponses: []portableExecResponse{
			{rowsAffected: 0},
			{rowsAffected: 1},
		},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id, status) VALUES ($1, 'active')",
		[]any{"mem-1"},
	)
	require.NoError(t, err)
	require.Len(t, execer.queryRowCalls, 2)
	require.Len(t, execer.execCalls, 2)
	require.Contains(t, execer.execCalls[0].query, "UPDATE memberships")
	require.Contains(t, execer.execCalls[1].query, "INSERT INTO memberships")
}

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

	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{
			{},
			{},
		},
		execResponses: []portableExecResponse{
			{rowsAffected: 0},
		},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id, status) VALUES ($1, 'active')",
		[]any{"mem-1"},
	)
	require.NoError(t, err)
	require.Len(t, execer.queryRowCalls, 2)
	require.Len(t, execer.execCalls, 1)
	require.Contains(t, execer.execCalls[0].query, "UPDATE memberships")
}

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

	duplicateErr := &mysql.MySQLError{Number: 1062, Message: "Duplicate entry 'mem-1' for key 'PRIMARY'"}
	execer := &recordingPortableExecer{
		queryRowResponses: []portableQueryRowResponse{
			{err: sql.ErrNoRows},
			{err: sql.ErrNoRows},
		},
		execResponses: []portableExecResponse{
			{err: duplicateErr},
		},
	}

	err := execPortableUpdateThenInsert(
		context.Background(),
		execer,
		"SELECT 1 FROM memberships WHERE id = $1",
		[]any{"mem-1"},
		"UPDATE memberships SET status = 'active' WHERE id = $1",
		[]any{"mem-1"},
		"INSERT INTO memberships (id, status) VALUES ($1, 'active')",
		[]any{"mem-1"},
	)
	require.ErrorIs(t, err, duplicateErr)
	require.Len(t, execer.execCalls, 1)
}

type queryStringRows struct {
	values []string
	index  int
}

func (rows *queryStringRows) Close() {}
func (rows *queryStringRows) Err() error {
	return nil
}

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

func (rows *queryStringRows) Scan(dest ...any) error {
	target, ok := dest[0].(*string)
	if !ok {
		return errors.New("expected *string destination")
	}
	*target = rows.values[rows.index-1]
	return nil
}

type recordingQueryExecer struct {
	recordingPortableExecer
	rows      coredb.Rows
	queryCall portableExecCall
	queryErr  error
}

func (queryer *recordingQueryExecer) Query(_ context.Context, query string, args ...any) (coredb.Rows, error) {
	queryer.queryCall = portableExecCall{
		query: query,
		args:  append([]any(nil), args...),
	}
	return queryer.rows, queryer.queryErr
}

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

	queryer := &recordingQueryExecer{
		rows: &queryStringRows{},
	}

	values, err := queryStringSetThenExecUpdate(
		context.Background(),
		queryer,
		"SELECT membership_id FROM memberships WHERE account_id = $1",
		[]any{"acct-1"},
		"UPDATE memberships SET status = 'inactive' WHERE account_id = $1",
		[]any{"acct-1"},
	)
	require.NoError(t, err)
	require.Empty(t, values)
	require.Empty(t, queryer.execCalls)
}

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

	queryer := &recordingQueryExecer{
		recordingPortableExecer: recordingPortableExecer{
			execResponses: []portableExecResponse{
				{rowsAffected: 2},
			},
		},
		rows: &queryStringRows{values: []string{"unit-1", "unit-2", "unit-1"}},
	}

	values, err := queryStringSetThenExecUpdate(
		context.Background(),
		queryer,
		"SELECT organization_unit_id FROM memberships WHERE account_id = $1",
		[]any{"acct-1"},
		"UPDATE memberships SET status = 'inactive' WHERE account_id = $1",
		[]any{"acct-1"},
	)
	require.NoError(t, err)
	require.Equal(t, map[string]struct{}{
		"unit-1": {},
		"unit-2": {},
	}, values)
	require.Len(t, queryer.execCalls, 1)
	require.Contains(t, queryer.execCalls[0].query, "UPDATE memberships")
	require.NotContains(t, queryer.execCalls[0].query, "RETURNING")
}