package coredb

import (
	"testing"

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

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

	cte, args, nextParam, err := BuildStringValuesCTE("requested_ids", "id", []string{"agt-1", "agt-2"}, 4)
	require.NoError(t, err)
	require.Equal(t, []any{"agt-1", "agt-2"}, args)
	require.Equal(t, 6, nextParam)
	require.Contains(t, cte, "requested_ids AS (")
	require.Contains(t, cte, "SELECT $4 AS id")
	require.Contains(t, cte, "UNION ALL")
	require.Contains(t, cte, "SELECT $5 AS id")
	require.NotContains(t, cte, "UNNEST(")
}

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

	cte, args, nextParam, err := BuildStringPairsCTE("routed", "agent_member_id", "delivery_reason", []string{"mem-1", "mem-2"}, []string{"mention", "dm_peer"}, 2)
	require.NoError(t, err)
	require.Equal(t, []any{"mem-1", "mention", "mem-2", "dm_peer"}, args)
	require.Equal(t, 6, nextParam)
	require.Contains(t, cte, "routed AS (")
	require.Contains(t, cte, "SELECT $2 AS agent_member_id, $3 AS delivery_reason")
	require.Contains(t, cte, "SELECT $4 AS agent_member_id, $5 AS delivery_reason")
	require.NotContains(t, cte, "UNNEST(")
}

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

	query, args, nextParam, err := BuildStringPairsUnionSelect("agent_member_id", "delivery_reason", []string{"mem-1", "mem-2"}, []string{"mention", "dm_peer"}, 2)
	require.NoError(t, err)
	require.Equal(t, []any{"mem-1", "mention", "mem-2", "dm_peer"}, args)
	require.Equal(t, 6, nextParam)
	require.Contains(t, query, "SELECT $2 AS agent_member_id, $3 AS delivery_reason")
	require.Contains(t, query, "UNION ALL")
	require.Contains(t, query, "SELECT $4 AS agent_member_id, $5 AS delivery_reason")
	require.NotContains(t, query, "WITH ")
	require.NotContains(t, query, "UNNEST(")
}

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

	cte, args, nextParam, err := BuildIndexedStringValuesCTE("requested_ids", "message_id", "ordinality", []string{"msg-1", "msg-2"}, 7)
	require.NoError(t, err)
	require.Equal(t, []any{"msg-1", "msg-2"}, args)
	require.Equal(t, 9, nextParam)
	require.Contains(t, cte, "requested_ids AS (")
	require.Contains(t, cte, "SELECT $7 AS message_id, 1 AS ordinality")
	require.Contains(t, cte, "SELECT $8 AS message_id, 2 AS ordinality")
	require.NotContains(t, cte, "WITH ORDINALITY")
}

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

	cte, args, nextParam, err := BuildInt64ValuesCTE("requested_numbers", "task_number", []int64{7, 9}, 3)
	require.NoError(t, err)
	require.Equal(t, []any{int64(7), int64(9)}, args)
	require.Equal(t, 5, nextParam)
	require.Contains(t, cte, "requested_numbers AS (")
	require.Contains(t, cte, "SELECT CAST($3 AS DECIMAL(20,0)) AS task_number")
	require.Contains(t, cte, "SELECT CAST($4 AS DECIMAL(20,0)) AS task_number")
	require.NotContains(t, cte, "UNNEST(")
}

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

	_, _, _, err := BuildStringPairsCTE("routed", "agent_member_id", "delivery_reason", []string{"mem-1"}, []string{"mention", "dm_peer"}, 1)
	require.EqualError(t, err, "value pairs are inconsistent")
}

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

	_, _, _, err := BuildInt64ValuesCTE("requested_numbers", "task_number", nil, 1)
	require.EqualError(t, err, "values are empty")
}

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

	tests := []struct {
		name  string
		build func(int) error
	}{
		{
			name: "string values cte",
			build: func(startParam int) error {
				_, _, _, err := BuildStringValuesCTE("requested_ids", "id", []string{"id-1"}, startParam)
				return err
			},
		},
		{
			name: "string pairs cte",
			build: func(startParam int) error {
				_, _, _, err := BuildStringPairsCTE("routed", "agent_member_id", "delivery_reason", []string{"mem-1"}, []string{"mention"}, startParam)
				return err
			},
		},
		{
			name: "string pairs union select",
			build: func(startParam int) error {
				_, _, _, err := BuildStringPairsUnionSelect("agent_member_id", "delivery_reason", []string{"mem-1"}, []string{"mention"}, startParam)
				return err
			},
		},
		{
			name: "indexed string values cte",
			build: func(startParam int) error {
				_, _, _, err := BuildIndexedStringValuesCTE("requested_ids", "message_id", "ordinality", []string{"msg-1"}, startParam)
				return err
			},
		},
		{
			name: "int64 values cte",
			build: func(startParam int) error {
				_, _, _, err := BuildInt64ValuesCTE("requested_numbers", "task_number", []int64{7}, startParam)
				return err
			},
		},
	}

	for _, tt := range tests {
		tt := tt
		t.Run(tt.name, func(t *testing.T) {
			t.Parallel()

			require.EqualError(t, tt.build(0), "startParam must be at least 1")
			require.EqualError(t, tt.build(-1), "startParam must be at least 1")
		})
	}
}