// Copyright 2023 Dolthub, Inc.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package _go

import (
	"testing"
	"time"

	"github.com/dolthub/go-mysql-server/sql"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"
)

func TestPreparedStatements(t *testing.T) {
	RunScripts(t, preparedStatementTests)
}

func TestPreparedPgCatalog(t *testing.T) {
	RunScripts(t, pgCatalogTests)
}

var preparedStatementTests = []ScriptTest{
	{
		Name: "Expressions without tables",
		Assertions: []ScriptTestAssertion{
			{
				Query:    "SELECT CONCAT($1::text, $2::text)",
				BindVars: []any{"hello", "world"},
				Expected: []sql.Row{
					{"helloworld"},
				},
			},
			{
				Query:    "SELECT $1::integer + $2::integer",
				BindVars: []any{1, 2},
				Expected: []sql.Row{
					{3},
				},
			},
			{
				Query:    "select $1 as test",
				BindVars: []any{"hello"},
				Expected: []sql.Row{
					{"hello"},
				},
			},
		},
	},
	{
		Name: "Expressions with tables",
		Assertions: []ScriptTestAssertion{
			{
				Query:    "SELECT EXISTS(SELECT 1 FROM pg_namespace WHERE nspname = $1);",
				BindVars: []any{"public"},
				Expected: []sql.Row{{"t"}},
			},
			{
				Query:    "SELECT nspname FROM pg_namespace LIMIT $1;",
				BindVars: []any{1},
				Expected: []sql.Row{{"dolt"}},
			},
			{
				Query:    "SELECT nspname FROM pg_namespace OFFSET $1;",
				BindVars: []any{1},
				Expected: []sql.Row{
					{"information_schema"},
					{"pg_catalog"},
					{"public"},
				},
			},
		},
	},
	{
		Name: "Integer insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "2", "3", "4"},
			},
			{
				Query: "SELECT * FROM test order by pk;",
				Expected: []sql.Row{
					{1, 2},
					{3, 4},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{"2"},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{"3"},
				Expected: []sql.Row{},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 + $1 = $2;",
				BindVars: []any{"1", "3"},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE pk + v1 = $1;",
				BindVars: []any{"3"},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1::integer + $2::integer;",
				BindVars: []any{"1", "3"},
				Expected: []sql.Row{
					{3, 4},
				},
			},
		},
	},
	{
		Name: "Integer insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 2, 3, 4},
			},
			{
				Query: "SELECT * FROM test order by pk;",
				Expected: []sql.Row{
					{1, 2},
					{3, 4},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{2},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{3},
				Expected: []sql.Row{},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 + $1 = $2;",
				BindVars: []any{1, 3},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE pk + v1 = $1;",
				BindVars: []any{3},
				Expected: []sql.Row{
					{1, 2},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1::integer + $2::integer;",
				BindVars: []any{1, 3},
				Expected: []sql.Row{
					{3, 4},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *",
				BindVars: []any{2, nil},
				Expected: []sql.Row{
					{2, nil},
				},
			},
		},
	},
	{
		Name: "Integer types",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v2 SMALLINT, v4 INTEGER, v5 BIGINT);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2, $3, $4) returning *",
				BindVars: []any{1, 10, 100, 1000},
				Expected: []sql.Row{
					{1, 10, 100, 1000},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2, $3, $4) returning *",
				BindVars: []any{2, nil, nil, nil},
				Expected: []sql.Row{
					{2, nil, nil, nil},
				},
			},
		},
	},
	{
		Name: "Integer update",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 2, 3, 4},
			},
			{
				Query:    "UPDATE test set v1 = $1 WHERE pk = $2;",
				BindVars: []any{5, 1},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{5},
				Expected: []sql.Row{
					{1, 5},
				},
			},
		},
	},
	{
		Name: "Integer delete",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 2, 3, 4},
			},
			{
				Query:    "DELETE FROM test WHERE pk = $1;",
				BindVars: []any{1},
			},
			{
				Query: "SELECT * FROM test order by 1;",
				Expected: []sql.Row{
					{3, 4},
				},
			},
		},
	},
	{
		Name: "String insert",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, s character varying(20));",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, "hello", 3, "goodbye"},
			},
			{
				Query: "SELECT * FROM test order by pk;",
				Expected: []sql.Row{
					{1, "hello"},
					{3, "goodbye"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE s = $1;",
				BindVars: []any{"hello"},
				Expected: []sql.Row{
					{1, "hello"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE s = concat($1::text, $2::text);",
				BindVars: []any{"he", "llo"},
				Expected: []sql.Row{
					{1, "hello"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE concat(s, '!') = $1",
				BindVars: []any{"hello!"},
				Expected: []sql.Row{
					{1, "hello"},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *",
				BindVars: []any{2, nil},
				Expected: []sql.Row{
					{2, nil},
				},
			},
		},
	},
	{
		Name: "String update",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, s character varying(20));",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, "hello", 3, "goodbye"},
			},
			{
				Query:    "UPDATE test set s = $1 WHERE pk = $2;",
				BindVars: []any{"new value", 1},
			},
			{
				Query:    "SELECT * FROM test WHERE s = $1;",
				BindVars: []any{"new value"},
				Expected: []sql.Row{
					{1, "new value"},
				},
			},
		},
	},
	{
		Name: "String delete",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, s character varying(20));",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, "hello", 3, "goodbye"},
			},
			{
				Query:    "DELETE FROM test WHERE s = $1;",
				BindVars: []any{"hello"},
			},
			{
				Query: "SELECT * FROM test ORDER BY 1;",
				Expected: []sql.Row{
					{3, "goodbye"},
				},
			},
		},
	},
	{
		Name: "Float insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, f1 DOUBLE PRECISION);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "1.1", "3", "3.3"},
			},
			{
				Query: "SELECT * FROM test ORDER BY 1;",
				Expected: []sql.Row{
					{1, 1.1},
					{3, 3.3},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 = $1;",
				BindVars: []any{"1.1"},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 + $1 = $2;",
				BindVars: []any{"1.0", "2.1"},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 = $1::decimal + $2::decimal;",
				BindVars: []any{"1.0", "0.1"},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
		},
	},
	{
		Name: "Float insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, f1 DOUBLE PRECISION);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 1.1, 3, 3.3},
			},
			{
				Query: "SELECT * FROM test ORDER BY 1;",
				Expected: []sql.Row{
					{1, 1.1},
					{3, 3.3},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 = $1;",
				BindVars: []any{1.1},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 + $1 = $2;",
				BindVars: []any{1.0, 2.1},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 = $1::decimal + $2::decimal;",
				BindVars: []any{1.0, 0.1},
				Expected: []sql.Row{
					{1, 1.1},
				},
			},
		},
	},
	{
		Name: "Float update",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, f1 DOUBLE PRECISION);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 1.1, 3, 3.3},
			},
			{
				Query:    "UPDATE test set f1 = $1 WHERE f1 = $2;",
				BindVars: []any{2.2, 1.1},
			},
			{
				Query:    "SELECT * FROM test WHERE f1 = $1;",
				BindVars: []any{2.2},
				Expected: []sql.Row{
					{1, 2.2},
				},
			},
		},
	},
	{
		Name: "Float delete",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, f1 DOUBLE PRECISION);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, 1.1, 3, 3.3},
			},
			{
				Query:    "DELETE FROM test WHERE f1 = $1;",
				BindVars: []any{1.1},
			},
			{
				Query: "SELECT * FROM test order by 1;",
				Expected: []sql.Row{
					{3, 3.3},
				},
			},
		},
	},
	{
		Name: "Date insert, update, delete with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 DATE);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "2022-02-02", "3", "2024-04-01 -07"},
			},
			{
				Query: "SELECT * FROM test order by pk;",
				Expected: []sql.Row{
					{1, "2022-02-02"},
					{3, "2024-04-01"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{"2022-02-02"},
				Expected: []sql.Row{
					{1, "2022-02-02"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{"2022-02-03"},
				Expected: []sql.Row{},
			},
			{
				Query:    "UPDATE test set v1 = $1 WHERE pk = $2;",
				BindVars: []any{"2022-02-03", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{"2022-02-03"},
				Expected: []sql.Row{
					{1, "2022-02-03"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE pk = $1;",
				BindVars: []any{"1"},
			},
			{
				Query: "SELECT * FROM test order by 1;",
				Expected: []sql.Row{
					{3, "2024-04-01"},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *;",
				BindVars: []any{"5", nil},
				Expected: []sql.Row{
					{5, nil},
				},
			},
		},
	},
	{
		Name: "Date insert, update, delete with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 DATE);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, Date("2022-02-02"), 3, Date("2024-04-01")},
			},
			{
				Query: "SELECT * FROM test order by pk;",
				Expected: []sql.Row{
					{1, "2022-02-02"},
					{3, "2024-04-01"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{Date("2022-02-02")},
				Expected: []sql.Row{
					{1, "2022-02-02"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{Date("2022-02-03")},
				Expected: []sql.Row{},
			},
			{
				Query:    "UPDATE test set v1 = $1 WHERE pk = $2;",
				BindVars: []any{Date("2022-02-03"), 1},
			},
			{
				Query:    "SELECT * FROM test WHERE v1 = $1;",
				BindVars: []any{Date("2022-02-03")},
				Expected: []sql.Row{
					{1, "2022-02-03"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE pk = $1;",
				BindVars: []any{1},
			},
			{
				Query: "SELECT * FROM test order by 1;",
				Expected: []sql.Row{
					{3, "2024-04-01"},
				},
			},
		},
	},
	{
		Name: "Timestamp insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIMESTAMP);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{
					"1", "2023-01-15 14:30",
					"2", "2024-12-25 09:15:30",
				},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "2023-01-15 14:30:00"},
					{2, "2024-12-25 09:15:30"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{"2023-01-15 14:30"},
				Expected: []sql.Row{
					{1, "2023-01-15 14:30:00"},
				},
			},
			{
				Query:    "UPDATE test SET t1 = $1 WHERE pk = $2;",
				BindVars: []any{"2023-01-15 16:45", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{"2023-01-15 16:45"},
				Expected: []sql.Row{
					{1, "2023-01-15 16:45:00"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE t1 = $1;",
				BindVars: []any{"2023-01-15 16:45"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "2024-12-25 09:15:30"},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *;",
				BindVars: []any{"3", nil},
				Expected: []sql.Row{
					{3, nil},
				},
			},
		},
	},
	{
		Name: "Timestamp insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIMESTAMP);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{
					1, Timestamp("2023-01-15 14:30:00"),
					2, Timestamp("2024-12-25 09:15:30"),
				},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "2023-01-15 14:30:00"},
					{2, "2024-12-25 09:15:30"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{Timestamp("2023-01-15 14:30:00")},
				Expected: []sql.Row{
					{1, "2023-01-15 14:30:00"},
				},
			},
			{
				Query:    "UPDATE test SET t1 = $1 WHERE pk = $2;",
				BindVars: []any{Timestamp("2023-01-15 16:45:00"), 1},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{Timestamp("2023-01-15 16:45:00")},
				Expected: []sql.Row{
					{1, "2023-01-15 16:45:00"},
				},
			},
			{
				Query: "DELETE FROM test WHERE t1 = $1;",
				BindVars: []any{
					Timestamp("2023-01-15 16:45:00")},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "2024-12-25 09:15:30"},
				},
			},
		},
	},
	{
		Name: "Timestamp with timezone insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIMESTAMPTZ);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "2023-01-15 14:30:00+00", "2", "2024-12-25 09:15:30-05"},
			},
			{
				Query: "SELECT pk FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1},
					{2},
				},
			},
			{
				Query:    "SELECT pk FROM test WHERE t1 = $1;",
				BindVars: []any{"2023-01-15 14:30:00+00"},
				Expected: []sql.Row{
					{1},
				},
			},
		},
	},
	{
		Name: "Timestamp with timezone insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIMESTAMPTZ);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{
					1, time.Date(2023, 1, 15, 14, 30, 0, 0, time.UTC), // +00 timezone
					2, time.Date(2024, 12, 25, 9, 15, 30, 0, time.FixedZone("EST", -5*60*60)), // -05 timezone
				},
			},
			{
				Query: "SELECT pk FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1},
					{2},
				},
			},
			{
				Query:    "SELECT pk FROM test WHERE t1 = $1;",
				BindVars: []any{time.Date(2023, 1, 15, 14, 30, 0, 0, time.UTC)},
				Expected: []sql.Row{
					{1},
				},
			},
		},
	},
	{
		Name: "Time insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIME);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "14:30:00", "2", "09:15:30"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "14:30:00"},
					{2, "09:15:30"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{"14:30:00"},
				Expected: []sql.Row{
					{1, "14:30:00"},
				},
			},
			{
				Query:    "UPDATE test SET t1 = $1 WHERE pk = $2;",
				BindVars: []any{"16:45:00", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{"16:45:00"},
				Expected: []sql.Row{
					{1, "16:45:00"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE t1 = $1;",
				BindVars: []any{"16:45:00"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "09:15:30"},
				},
			},
		},
	},
	{
		Name: "Time insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, t1 TIME);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{1, time.Date(0, 1, 1, 14, 30, 0, 0, time.UTC), 2, time.Date(0, 1, 1, 9, 15, 30, 0, time.UTC)},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "14:30:00"},
					{2, "09:15:30"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{time.Date(0, 1, 1, 14, 30, 0, 0, time.UTC)},
				Expected: []sql.Row{
					{1, "14:30:00"},
				},
			},
			{
				Query:    "UPDATE test SET t1 = $1 WHERE pk = $2;",
				BindVars: []any{time.Date(0, 1, 1, 16, 45, 0, 0, time.UTC), 1},
			},
			{
				Query:    "SELECT * FROM test WHERE t1 = $1;",
				BindVars: []any{time.Date(0, 1, 1, 16, 45, 0, 0, time.UTC)},
				Expected: []sql.Row{
					{1, "16:45:00"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE t1 = $1;",
				BindVars: []any{time.Date(0, 1, 1, 16, 45, 0, 0, time.UTC)},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "09:15:30"},
				},
			},
		},
	},
	{
		Name: "UUID insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, u1 UUID);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{"1", "550e8400-e29b-41d4-a716-446655440000", "2", "6ba7b810-9dad-11d1-80b4-00c04fd430c8"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "550e8400-e29b-41d4-a716-446655440000"},
					{2, "6ba7b810-9dad-11d1-80b4-00c04fd430c8"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE u1 = $1;",
				BindVars: []any{"550e8400-e29b-41d4-a716-446655440000"},
				Expected: []sql.Row{
					{1, "550e8400-e29b-41d4-a716-446655440000"},
				},
			},
			{
				Query:    "UPDATE test SET u1 = $1 WHERE pk = $2;",
				BindVars: []any{"123e4567-e89b-12d3-a456-426614174000", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE u1 = $1;",
				BindVars: []any{"123e4567-e89b-12d3-a456-426614174000"},
				Expected: []sql.Row{
					{1, "123e4567-e89b-12d3-a456-426614174000"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE u1 = $1;",
				BindVars: []any{"123e4567-e89b-12d3-a456-426614174000"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "6ba7b810-9dad-11d1-80b4-00c04fd430c8"},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *;",
				BindVars: []any{"3", nil},
				Expected: []sql.Row{
					{3, nil},
				},
			},
		},
	},
	{
		Name: "UUID insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, u1 UUID);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "INSERT INTO test VALUES ($1, $2), ($3, $4);",
				BindVars: []any{
					1, UUID("550e8400-e29b-41d4-a716-446655440000"),
					2, UUID("6ba7b810-9dad-11d1-80b4-00c04fd430c8")},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "550e8400-e29b-41d4-a716-446655440000"},
					{2, "6ba7b810-9dad-11d1-80b4-00c04fd430c8"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE u1 = $1;",
				BindVars: []any{UUID("550e8400-e29b-41d4-a716-446655440000")},
				Expected: []sql.Row{
					{1, "550e8400-e29b-41d4-a716-446655440000"},
				},
			},
			{
				Query:    "UPDATE test SET u1 = $1 WHERE pk = $2;",
				BindVars: []any{UUID("123e4567-e89b-12d3-a456-426614174000"), 1},
			},
			{
				Query:    "SELECT * FROM test WHERE u1 = $1;",
				BindVars: []any{UUID("123e4567-e89b-12d3-a456-426614174000")},
				Expected: []sql.Row{
					{1, "123e4567-e89b-12d3-a456-426614174000"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE u1 = $1;",
				BindVars: []any{UUID("123e4567-e89b-12d3-a456-426614174000")},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, "6ba7b810-9dad-11d1-80b4-00c04fd430c8"},
				},
			},
		},
	},
	{
		Name: "Numeric/Decimal insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, n1 NUMERIC(10,2), n2 DECIMAL(8,3));",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2, $3), ($4, $5, $6);",
				BindVars: []any{"1", "123.45", "67.890", "2", "999.99", "12.345"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, Numeric("123.45"), Numeric("67.890")},
					{2, Numeric("999.99"), Numeric("12.345")},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE n1 = $1;",
				BindVars: []any{"123.45"},
				Expected: []sql.Row{
					{1, Numeric("123.45"), Numeric("67.890")},
				},
			},
			{
				Query:    "UPDATE test SET n1 = $1, n2 = $2 WHERE pk = $3;",
				BindVars: []any{"456.78", "98.765", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE n1 = $1;",
				BindVars: []any{"456.78"},
				Expected: []sql.Row{
					{1, Numeric("456.78"), Numeric("98.765")},
				},
			},
			{
				Query:    "DELETE FROM test WHERE n2 = $1;",
				BindVars: []any{"98.765"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, Numeric("999.99"), Numeric("12.345")},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2, $3) returning *;",
				BindVars: []any{"3", nil, nil},
				Expected: []sql.Row{
					{3, nil, nil},
				},
			},
		},
	},
	{
		Name: "Numeric/Decimal insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, n1 NUMERIC(10,2), n2 DECIMAL(8,3));",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "INSERT INTO test VALUES ($1, $2, $3), ($4, $5, $6);",
				BindVars: []any{
					1, Numeric("123.45"), Numeric("67.890"),
					2, Numeric("999.99"), Numeric("12.345")},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, Numeric("123.45"), Numeric("67.890")},
					{2, Numeric("999.99"), Numeric("12.345")},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE n1 = $1;",
				BindVars: []any{Numeric("123.45")},
				Expected: []sql.Row{
					{1, Numeric("123.45"), Numeric("67.890")},
				},
			},
			{
				Query:    "UPDATE test SET n1 = $1, n2 = $2 WHERE pk = $3;",
				BindVars: []any{Numeric("456.78"), Numeric("98.765"), 1},
			},
			{
				Query:    "SELECT * FROM test WHERE n1 = $1;",
				BindVars: []any{Numeric("456.78")},
				Expected: []sql.Row{
					{1, Numeric("456.78"), Numeric("98.765")},
				},
			},
			{
				Query:    "DELETE FROM test WHERE n2 = $1;",
				BindVars: []any{Numeric("98.765")},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{2, Numeric("999.99"), Numeric("12.345")},
				},
			},
		},
	},
	{
		Name: "Boolean insert with string bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, b1 BOOLEAN);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4), ($5, $6);",
				BindVars: []any{"1", "true", "2", "false", "3", "true"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "t"},
					{2, "f"},
					{3, "t"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1;",
				BindVars: []any{"true"},
				Expected: []sql.Row{
					{1, "t"},
					{3, "t"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1;",
				BindVars: []any{"false"},
				Expected: []sql.Row{
					{2, "f"},
				},
			},
			{
				Query:    "UPDATE test SET b1 = $1 WHERE pk = $2;",
				BindVars: []any{"false", "1"},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1 ORDER BY pk;",
				BindVars: []any{"false"},
				Expected: []sql.Row{
					{1, "f"},
					{2, "f"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE b1 = $1;",
				BindVars: []any{"false"},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{3, "t"},
				},
			},
			{
				Query:    "INSERT INTO test VALUES ($1, $2) returning *;",
				BindVars: []any{"4", nil},
				Expected: []sql.Row{
					{4, nil},
				},
			},
		},
	},
	{
		Name: "Boolean insert with binary bindvars",
		SetUpScript: []string{
			"drop table if exists test",
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, b1 BOOLEAN);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "INSERT INTO test VALUES ($1, $2), ($3, $4), ($5, $6);",
				BindVars: []any{1, true, 2, false, 3, true},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{1, "t"},
					{2, "f"},
					{3, "t"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1;",
				BindVars: []any{true},
				Expected: []sql.Row{
					{1, "t"},
					{3, "t"},
				},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1;",
				BindVars: []any{false},
				Expected: []sql.Row{
					{2, "f"},
				},
			},
			{
				Query:    "UPDATE test SET b1 = $1 WHERE pk = $2;",
				BindVars: []any{false, 1},
			},
			{
				Query:    "SELECT * FROM test WHERE b1 = $1 ORDER BY pk;",
				BindVars: []any{false},
				Expected: []sql.Row{
					{1, "f"},
					{2, "f"},
				},
			},
			{
				Query:    "DELETE FROM test WHERE b1 = $1;",
				BindVars: []any{false},
			},
			{
				Query: "SELECT * FROM test ORDER BY pk;",
				Expected: []sql.Row{
					{3, "t"},
				},
			},
		},
	},
	{
		Name: "pg_get_viewdef function",
		SetUpScript: []string{
			"CREATE TABLE test (id int, name text)",
			"INSERT INTO test VALUES (1,'desk'), (2,'chair')",
			"CREATE VIEW test_view AS SELECT name FROM test",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    `select pg_get_viewdef($1::regclass);`,
				BindVars: []any{"test_view"},
				Expected: []sql.Row{{"SELECT name FROM test"}},
			},
		},
	},
	{
		Name: "insert returning",
		SetUpScript: []string{
			"CREATE TABLE test (id serial, name text)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    `INSERT INTO test (name) VALUES ($1) RETURNING id;`,
				BindVars: []any{"test_name"},
				Expected: []sql.Row{{1}},
			},
		},
	},
	{
		Name: "define placeholder unordered",
		Assertions: []ScriptTestAssertion{
			{
				Query:    "SELECT $3::text, $1::integer + $2::integer",
				BindVars: []any{1, 3, "hi"},
				Expected: []sql.Row{
					{"hi", 4},
				},
			},
		},
	},
	{
		Name: "Bytea with binary bindvars",
		SetUpScript: []string{
			"CREATE TABLE t_bytea (id INTEGER primary key, v1 BYTEA);",
			"INSERT INTO t_bytea VALUES (1, E'\\\\xDEADBEEF'), (2, '\\xC0FFEE'), (3, ''), (4, NULL);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query: "SELECT * FROM t_bytea ORDER BY id;",
				Expected: []sql.Row{
					{1, []byte{0xDE, 0xAD, 0xBE, 0xEF}},
					{2, []byte{0xC0, 0xFF, 0xEE}},
					{3, []byte{}},
					{4, nil},
				},
			},
			{
				Query:    "SELECT * FROM t_bytea WHERE v1 = $1 ORDER BY id;",
				BindVars: []any{[]byte{0xC0, 0xFF, 0xEE}},
				Expected: []sql.Row{
					{2, []byte{0xC0, 0xFF, 0xEE}},
				},
			},
			{
				Query:    "UPDATE t_bytea SET v1 = $1 WHERE id = $2;",
				BindVars: []any{`\xC0FFEE`, 4},
			},
			{
				Query:    "SELECT * FROM t_bytea WHERE v1 = $1 ORDER BY id;",
				BindVars: []any{[]byte{0xC0, 0xFF, 0xEE}},
				Expected: []sql.Row{
					{2, []byte{0xC0, 0xFF, 0xEE}},
					{4, []byte{0xC0, 0xFF, 0xEE}},
				},
			},
			{
				Query:    "DELETE FROM t_bytea WHERE v1 = $1;",
				BindVars: []any{[]byte{0xDE, 0xAD, 0xBE, 0xEF}},
			},
			{
				Query: "SELECT * FROM t_bytea ORDER BY id;",
				Expected: []sql.Row{
					{2, []byte{0xC0, 0xFF, 0xEE}},
					{3, []byte{}},
					{4, []byte{0xC0, 0xFF, 0xEE}},
				},
			},
			{
				Query:    "INSERT INTO t_bytea VALUES ($1, $2) returning *;",
				BindVars: []any{5, nil},
				Expected: []sql.Row{
					{5, nil},
				},
			},
		},
	},
	{
		Name: "Bind parameter to compatible different types",
		SetUpScript: []string{
			"CREATE TABLE text_test (id text, code varchar(10))",
			"CREATE TABLE num_test(small int2, large int8, other float4)",
			"INSERT INTO text_test values ('foo', 'bar'), ('bar', 'foo')",
			"INSERT INTO num_test values (0,0,0), (1, 2, 1.5)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "SELECT * FROM text_test where id = any($1) and code = any($1)",
				BindVars: []any{[]string{"foo", "bar"}},
				Expected: []sql.Row{
					{"foo", "bar"},
					{"bar", "foo"},
				},
			},
			{
				Query:    "SELECT * from num_test where small = $1 and large = $1 and other = $1",
				BindVars: []any{0},
				Expected: []sql.Row{
					{0, 0, float64(0)},
				},
			},
			{
				Query:    "SELECT * from num_test where small = $1::INTEGER and large = $1::INTEGER and other = $1::INTEGER",
				BindVars: []any{0},
				Expected: []sql.Row{
					{0, 0, float64(0)},
				},
			},
			{
				Query:    "SELECT * FROM num_test where small = $1 or other = $1",
				BindVars: []any{1.5},
				Expected: []sql.Row{
					{1, 2, 1.5},
				},
			},
		},
	},
	{
		Name: "Cannot bind parameter to column with incompatible type",
		SetUpScript: []string{
			"CREATE TABLE text_test (fullname text, id int)",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:       "SELECT * FROM text_test where fullname = $1 and id = $1",
				BindVars:    []any{1},
				ExpectedErr: "parameter v1 is used for incompatible types: text and integer",
			},
		},
	},
	{
		Name: "merge join planning with bindvar equality filter on indexed join column",
		SetUpScript: []string{
			"CREATE TABLE t1 (pk INT PRIMARY KEY, fk INT, val INT);",
			"CREATE INDEX t1_fk_idx ON t1(fk);",
			"CREATE TABLE t2 (pk INT PRIMARY KEY, val INT);",
			"INSERT INTO t1 VALUES (1, 1, 100), (2, 2, 200), (3, 3, 300);",
			"INSERT INTO t2 VALUES (1, 10), (2, 20), (3, 30);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "SELECT t1.val, t2.val FROM t1 JOIN t2 ON t1.fk = t2.pk WHERE t1.fk = $1;",
				BindVars: []any{1},
				Expected: []sql.Row{
					{100, 10},
				},
			},
		},
	},
}

var pgCatalogTests = []ScriptTest{
	{
		Name: "pg_namespace",
		SetUpScript: []string{
			`CREATE SCHEMA testschema;`,
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    `SELECT * FROM "pg_catalog"."pg_namespace" WHERE nspname=$1;`,
				BindVars: []any{"testschema"},
				Expected: []sql.Row{{2638679668, "testschema", 0, nil}},
			},
			{
				Query:    `SELECT * FROM "pg_catalog"."pg_namespace" WHERE oid=$1;`,
				BindVars: []any{2638679668},
				Expected: []sql.Row{{2638679668, "testschema", 0, nil}},
			},
		},
	},
	{
		Name: "pg_tables",
		SetUpScript: []string{
			`CREATE TABLE testing (pk INT primary key, v1 INT);`,
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    `SELECT * FROM "pg_catalog"."pg_tables" WHERE tablename=$1;`,
				BindVars: []any{"testing"},
				Expected: []sql.Row{{"public", "testing", "postgres", nil, "t", "f", "f", "f"}},
			},
			{
				Query:    `SELECT count(*) FROM "pg_catalog"."pg_tables" WHERE schemaname=$1;`,
				BindVars: []any{"pg_catalog"},
				Expected: []sql.Row{{139}},
			},
		},
	},
	{
		Name: "pg_class",
		SetUpScript: []string{
			`CREATE SCHEMA testschema;`,
			`CREATE TABLE testschema.testtable (id int primary key, v1 text)`,
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:    "select distinct relnamespace from pg_catalog.pg_class c INNER JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname=$1;",
				BindVars: []any{"testschema"},
				Expected: []sql.Row{{2638679668}},
			},
			// TODO: when this test is run in isolation without the query above, the below query returns no rows. This is
			//  because the process of converting an OID to its internal ID can only proceed in one direction: from internal
			//  to OID. When the above query runs, it causes the internal ID for the testschema namespace to be cached,
			//  allowing the reverse lookup to succeed in subsequent queries. For OIDs that have not yet been cached in this
			//  manner, lookups by their OID will fail. This doesn't impact all queries since many of them get an index
			//  lookup on OID, which has the side effect of converting everything to numeric IDs anyway. But for queries
			//  that use a normal comparison function for an OID literal value, the conversion to an internal ID of the
			//  appropriate type (e.g. id.Namespace) cannot happen in the |oidin| function in some cases because the internal
			//  to OID mapping hasn't yet been established for that schema element, so the comparison fails, yielding
			//  incorrect results.
			//  To fix this, we need to correctly seed the internal ID cache with all schema elements in the database.
			{
				Query: `SELECT c.oid,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key 
FROM pg_catalog.pg_class c
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') and c.oid not in (select oid from pg_catalog.pg_class where left(relname, 5) = 'dolt_');`,
				BindVars: []any{2638679668},
				Expected: []sql.Row{{1712283605, nil, ""}},
			},
			{
				Query: `SELECT c.oid,d.description,pg_catalog.pg_get_expr(c.relpartbound, c.oid) as partition_expr,  pg_catalog.pg_get_partkeydef(c.oid) as partition_key 
FROM pg_catalog.pg_class c
LEFT OUTER JOIN pg_catalog.pg_description d ON d.objoid=c.oid AND d.objsubid=0 AND d.classoid='pg_class'::regclass
WHERE c.relnamespace=$1 AND c.relkind not in ('i','I','c') and c.oid not in (select oid from pg_catalog.pg_class where left(relname, 5) = 'dolt_');`,
				BindVars: []any{2638679668},
				Expected: []sql.Row{{1712283605, nil, nil, ""}},
			},
			{
				Query: `SELECT d.description from pg_catalog.pg_description d WHERE d.classoid='pg_class'::regclass`,
				// TODO: add expected values (pg_description not yet implemented)
			},
			{
				Query:    `select c.oid,pg_catalog.pg_total_relation_size(c.oid) as total_rel_size,pg_catalog.pg_relation_size(c.oid) as rel_size FROM pg_class c WHERE c.relnamespace=$1 and c.oid not in (select oid from pg_catalog.pg_class where left(relname, 5) = 'dolt_');`,
				BindVars: []any{2638679668},
				Expected: []sql.Row{{444447634, 0, 0}, {1712283605, 0, 0}},
			},
			{
				Query: `SELECT c.relname, a.attrelid, a.attname, a.atttypid, pg_catalog.pg_get_expr(ad.adbin, ad.adrelid, true) as def_value,dsc.description,dep.objid 
FROM pg_catalog.pg_attribute a 
INNER JOIN pg_catalog.pg_class c ON (a.attrelid=c.oid) 
LEFT OUTER JOIN pg_catalog.pg_attrdef ad ON (a.attrelid=ad.adrelid AND a.attnum = ad.adnum) 
LEFT OUTER JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid) 
LEFT OUTER JOIN pg_depend dep on dep.refobjid = a.attrelid AND dep.deptype = 'i' and dep.refobjsubid = a.attnum and dep.classid = dep.refclassid 
WHERE NOT a.attisdropped AND c.relkind not in ('i','I','c') AND c.oid=$1 ORDER BY a.attnum`,
				BindVars: []any{1712283605},
				Expected: []sql.Row{{"testtable", 1712283605, "id", 23, nil, nil, nil}, {"testtable", 1712283605, "v1", 25, nil, nil, nil}},
			},
		},
	},
}

func TestPreparedErrorHandling(t *testing.T) {
	tt := ScriptTest{
		Name: "error handling doesn't foul session",
		SetUpScript: []string{
			"CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT);",
			"insert into test values (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7);",
		},
		Assertions: []ScriptTestAssertion{
			{
				Query:       "select v1 from doesNotExist where pk = 1;",
				ExpectedErr: "table not found",
			},
			{
				Query:    "select v1 from test where pk = 1;",
				Expected: []sql.Row{{1}},
			},
			{
				Query:    "select v1 from test where pk = 2;",
				Expected: []sql.Row{{2}},
			},
			{
				Query:    "select v1 from test where pk = 3;",
				Expected: []sql.Row{{3}},
			},
			{
				Query:    "select v1 from test where pk = 4;",
				Expected: []sql.Row{{4}},
			},
			{
				Query:    "select v1 from test where pk = 5;",
				Expected: []sql.Row{{5}},
			},
			{
				Query:    "select v1 from test where pk = 6;",
				Expected: []sql.Row{{6}},
			},
			{
				Query:    "select v1 from test where pk = 7;",
				Expected: []sql.Row{{7}},
			},
		},
	}

	RunScriptN(t, tt, 20)
}

// RunScriptN runs the assertions of the given script n times using the same connection
func RunScriptN(t *testing.T, script ScriptTest, n int) {
	scriptDatabase := script.Database
	if len(scriptDatabase) == 0 {
		scriptDatabase = "postgres"
	}
	ctx, conn, controller := CreateServer(t, scriptDatabase)
	defer func() {
		conn.Close(ctx)
		controller.Stop()
		err := controller.WaitForStop()
		require.NoError(t, err)
	}()

	// Run the setup
	for _, query := range script.SetUpScript {
		rows, err := conn.Query(ctx, query)
		require.NoError(t, err)
		_, _, err = ReadRows(rows, true)
		assert.NoError(t, err)
	}

	for i := 0; i < n; i++ {
		t.Run(script.Name, func(t *testing.T) {
			// Run the assertions
			for _, assertion := range script.Assertions {
				t.Run(assertion.Query, func(t *testing.T) {
					if assertion.Skip {
						t.Skip("Skip has been set in the assertion")
					}
					rows, err := conn.Query(ctx, assertion.Query)
					if err == nil {
						defer rows.Close()
					}

					var errorSeen string

					if assertion.ExpectedErr == "" {
						require.NoError(t, err)
					} else if err != nil {
						errorSeen = err.Error()
					}

					if errorSeen == "" {
						foundRows, foundRawRows, err := ReadRows(rows, true)
						if assertion.ExpectedErr == "" {
							require.NoError(t, err)
							if assertion.ExpectedRaw != nil {
								assert.Equal(t, assertion.ExpectedRaw, foundRawRows)
							} else {
								assert.Equal(t, NormalizeExpectedRow(rows.FieldDescriptions(), assertion.Expected), foundRows)
							}
						} else if err != nil {
							errorSeen = err.Error()
						}
					}

					if assertion.ExpectedErr != "" {
						require.False(t, errorSeen == "", "Expected error but got none")
						assert.Contains(t, errorSeen, assertion.ExpectedErr)
					}
				})
			}
		})
	}
}