// Copyright 2024 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"

	"github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/types"
)

func TestCreateTable(t *testing.T) {
	RunScripts(t, []ScriptTest{
		{
			// https://github.com/dolthub/doltgresql/issues/2580
			Name: "create table with UTF8 identifiers",
			Assertions: []ScriptTestAssertion{
				{
					Query:    `CREATE TABLE foo😏(data🍆 TEXT);`,
					Expected: []sql.Row{},
				},
				{
					Query:    `CREATE INDEX idx🍤 ON foo😏(data🍆);`,
					Expected: []sql.Row{},
				},
				{
					Query:    `Insert into foo😏 (data🍆) VALUES ('foo');`,
					Expected: []sql.Row{},
				},
				{
					Query:    `SELECT data🍆 FROM foo😏;`,
					Expected: []sql.Row{{"foo"}},
				},
			},
		},
		{
			Name: "create table with primary key",
			Assertions: []ScriptTestAssertion{
				{
					// TODO: we don't currently have a way to check for warnings in these tests, but this query was incorrectly
					//  producing a warning. Would be nice to assert no warnings on most queries.
					Query: "create table employees (" +
						"    id int8," +
						"    last_name text," +
						"    first_name text," +
						"    primary key(id));",
				},
				{
					Query: "insert into employees (id, last_name, first_name) values (1, 'Doe', 'John');",
				},
				{
					Query: "select * from employees;",
					Expected: []sql.Row{
						{1, "Doe", "John"},
					},
				},
				{
					// Test that the PK constraint shows up in the information schema
					Query:    "SELECT conname FROM pg_constraint WHERE conrelid = 'employees'::regclass AND contype = 'p';",
					Expected: []sql.Row{{"employees_pkey"}},
				},
				{
					Query:    "ALTER TABLE employees DROP CONSTRAINT employees_pkey;",
					Expected: []sql.Row{},
				},
			},
		},
		{
			// TODO: We don't currently support storing a custom name for a primary key constraint.
			Skip: true,
			Name: "create table with primary key, using custom constraint name",
			SetUpScript: []string{
				"CREATE TABLE users (id SERIAL, name TEXT, CONSTRAINT users_primary_key PRIMARY KEY (id));",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "SELECT conname FROM pg_constraint WHERE conrelid = 'users'::regclass AND contype = 'p';",
					Expected: []sql.Row{{"users_primary_key"}},
				},
				{
					Query:    "ALTER TABLE users DROP CONSTRAINT users_primary_key;",
					Expected: []sql.Row{{types.NewOkResult(0)}},
				},
			},
		},
		{
			Name: "Create table with column default expression using function",
			Assertions: []ScriptTestAssertion{
				{
					// Test with a function in the column default expression
					Query:    "create table t1 (pk int primary key, c1 TEXT default length('Hello World!'));",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into t1(pk) values (1);",
					Expected: []sql.Row{},
				},
				{
					Query:    "select * from t1;",
					Expected: []sql.Row{{1, "12"}},
				},
			},
		},
		{
			Name: "Create table with table check constraint",
			Assertions: []ScriptTestAssertion{
				{
					Query:    `CREATE TABLE products (name text, price numeric, discounted_price numeric, CHECK (price > discounted_price));`,
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into products values ('apple', 1.20, 0.80);",
					Expected: []sql.Row{},
				},
				{
					// TODO: the correct error message: `new row for relation "products" violates check constraint "products_chk_rqcthh8j"`
					Query:       "insert into products values ('peach', 1.20, 1.80);",
					ExpectedErr: `Check constraint "products_chk_`,
				},
				{
					Query:    "select * from products;",
					Expected: []sql.Row{{"apple", Numeric("1.20"), Numeric("0.80")}},
				},
			},
		},
		{
			Name: "Create table with column check constraint",
			Assertions: []ScriptTestAssertion{
				{
					Query:    "create table mytbl (pk int, v1 int constraint v1constraint check (v1 < 100));",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into mytbl values (1, 20);",
					Expected: []sql.Row{},
				},
				{
					Query:       "insert into mytbl values (2, 200);",
					ExpectedErr: `Check constraint "v1constraint" violated`,
				},
				{
					Query:    "select * from mytbl;",
					Expected: []sql.Row{{1, 20}},
				},
			},
		},
		{
			Name: "check constraint with a function",
			Assertions: []ScriptTestAssertion{
				{
					Query:    "CREATE TABLE mytbl (a text CHECK (length(a) > 2) PRIMARY KEY, b text);",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into mytbl values ('abc', 'def');",
					Expected: []sql.Row{},
				},
				{
					Query:       "insert into mytbl values ('de', 'abc');",
					ExpectedErr: `Check constraint "mytbl_chk_`,
				},
				{
					Query:    "select * from mytbl;",
					Expected: []sql.Row{{"abc", "def"}},
				},
			},
		},
		{
			Skip: true, // TODO: vitess does not support multiple check constraint on a single column
			Name: "Create table with multiple check constraints on a single column",
			Assertions: []ScriptTestAssertion{
				{
					Query:    "create table mytbl (pk int, v1 int constraint v1constraint check (v1 < 100) check (v1 > 10));",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into mytbl values (1, 20);",
					Expected: []sql.Row{},
				},
				{
					Query:       "insert into mytbl values (2, 200);",
					ExpectedErr: `Check constraint "v1constraint" violated`,
				},
				{
					Query:       "insert into mytbl values (3, 5);",
					ExpectedErr: `Check constraint "mytbl_chk_`,
				},
				{
					Query:    "select * from mytbl;",
					Expected: []sql.Row{{1, 20}},
				},
			},
		},
		{
			Name: "Create table with a check constraints on a single column and a table check constraint",
			Assertions: []ScriptTestAssertion{
				{
					Query:    "create table mytbl (pk int, v1 int constraint v1constraint check (v1 < 100), check (v1 > 10));",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into mytbl values (1, 20);",
					Expected: []sql.Row{},
				},
				{
					Query:       "insert into mytbl values (2, 200);",
					ExpectedErr: `Check constraint "v1constraint" violated`,
				},
				{
					Query:       "insert into mytbl values (3, 5);",
					ExpectedErr: `Check constraint "mytbl_chk_`,
				},
				{
					Query:    "select * from mytbl;",
					Expected: []sql.Row{{1, 20}},
				},
			},
		},
		{
			Name: "create table with generated column",
			SetUpScript: []string{
				"create table t1 (a int primary key, b int, c int generated always as (a + b) stored);",
				"insert into t1 (a, b) values (1, 2);",
				"create table t2 (a int primary key, b int, c int generated always as (b * 10) stored);",
				"insert into t2 (a, b) values (1, 2);",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "select * from t1;",
					Expected: []sql.Row{{1, 2, 3}},
				},
				{
					Query:    "select * from t2;",
					Expected: []sql.Row{{1, 2, 20}},
				},
			},
		},
		{
			Name: "create table with function in generated column",
			SetUpScript: []string{
				"create table t1 (a varchar(10) primary key, b varchar(10), c varchar(20) generated always as (concat(a,b)) stored);",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query: "insert into t1 (a, b) values ('foo', 'bar');",
				},
				{
					Query:    "select * from t1;",
					Expected: []sql.Row{{"foo", "bar", "foobar"}},
				},
			},
		},
		{
			Name: "generated column with complex expression",
			SetUpScript: []string{
				`create table t1 (a varchar(10) primary key,
				b varchar(20) generated always as 
				    ((
				        ("substring"(TRIM(BOTH FROM a), '([^ ]+)$'::text) || ' '::text)
				          || "substring"(TRIM(BOTH FROM a), '^([^ ]+)'::text)
				    )) stored
				);`,
			},
			Assertions: []ScriptTestAssertion{
				{
					Query: "insert into t1 (a) values (' foo ');",
				},
				{
					Query:    "select * from t1;",
					Expected: []sql.Row{{" foo ", "foo foo"}},
				},
			},
		},
		{
			Name: "generated column with reference to another column",
			SetUpScript: []string{
				`create table t1 (
    			a varchar(10) primary key,
    			b varchar(20),
				  b_not_null bool generated always as ((b is not null)) stored
				);`,
				"insert into t1 (a, b) values ('foo', 'bar');",
				"insert into t1 (a) values ('foo2');",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query: "select * from t1 order by a;",
					Expected: []sql.Row{
						{"foo", "bar", "t"},
						{"foo2", nil, "f"},
					},
				},
			},
		},
		{
			Name: "generated column with space in column name",
			SetUpScript: []string{
				`create table t1 (
    			a varchar(10) primary key,
    			"b 2" varchar(20),
				  b_not_null bool generated always as (("b 2" is not null)) stored
				);`,
				`insert into t1 (a, "b 2") values ('foo', 'bar');`,
				"insert into t1 (a) values ('foo2');",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query: "select * from t1 order by a;",
					Expected: []sql.Row{
						{"foo", "bar", "t"},
						{"foo2", nil, "f"},
					},
				},
			},
		},
		{
			Name: "primary key GENERATED ALWAYS AS IDENTITY",
			SetUpScript: []string{
				`create table t1 (
    			a BIGINT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
				  b varchar(100)
				);`,
			},
			Assertions: []ScriptTestAssertion{
				{
					Query: "insert into t1 (b) values ('foo') returning a;",
					Expected: []sql.Row{
						{1},
					},
				},
				{
					Query:       "insert into t1 (a, b) values (2, 'foo') returning a;",
					ExpectedErr: "The value specified for generated column \"a\" in table \"t1\" is not allowed",
				},
			},
		},
		{
			Name: "create table with default value",
			SetUpScript: []string{
				"create table t1 (a varchar(10) primary key, b varchar(10) default (concat('foo', 'bar')));",
				"insert into t1 (a) values ('abc');",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "select * from t1;",
					Expected: []sql.Row{{"abc", "foobar"}},
				},
			},
		},
		{
			Name: "create table with collation",
			SetUpScript: []string{
				`CREATE TABLE collate_test1 (
    a int,
        b text COLLATE "en-x-icu" NOT NULL
        )`,
				"insert into collate_test1 (a, b) values (1, 'foo');",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "select * from collate_test1;",
					Expected: []sql.Row{{1, "foo"}},
				},
			},
		},
		{
			Name: "inline comments",
			Assertions: []ScriptTestAssertion{
				{
					Query: `CREATE TABLE inline_comments (
	a int,
	b int,
	c int, -- comment on end of line
	CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
	CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
);`,
				},
				{
					Query: `CREATE TABLE block_comments (
	a int,
	b /* block comment */ /* one more thing */ int, -- comment on end of line
	c int, -- comment on end of line /* block comment */
	CONSTRAINT check_b CHECK (b IS NULL OR b = 'a'),
	CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
);`,
				},
			},
		},
		{
			Name: "create temporary table with serial column",
			Assertions: []ScriptTestAssertion{
				{
					Query:    "CREATE TEMP TABLE temp (id serial primary key)",
					Expected: []sql.Row{},
				},
			},
		},
		{
			Name: "table with check constraint with ANY expression",
			SetUpScript: []string{
				`CREATE TABLE location (
    id integer NOT NULL,
    name character varying(100) NOT NULL,
    type character varying(100),
    CONSTRAINT location_type_check CHECK (((type)::text = ANY ((ARRAY['Внутренни'::character varying, 'Покупатель'::character varying, 'Поставщик'::character varying])::text[])))
);`,
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "insert into location values (1, 'Склад Москва', 'Внутренни'), (2, 'Склад Спб', null);",
					Expected: []sql.Row{},
				},
				{
					Query: "SELECT * FROM location;",
					Expected: []sql.Row{
						{1, "Склад Москва", "Внутренни"},
						{2, "Склад Спб", nil},
					},
				},
			},
		},
		{
			Name: "Table names must be unique across all relation types",
			SetUpScript: []string{
				"CREATE TABLE existing_tbl (pk int PRIMARY KEY, v1 int);",
				"CREATE SEQUENCE seq1;",
				"CREATE VIEW view1 AS SELECT pk FROM existing_tbl;",
				"CREATE INDEX idx1 ON existing_tbl (v1);",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:       "CREATE TABLE existing_tbl (c1 int);",
					ExpectedErr: `relation "existing_tbl" already exists`,
				},
				{
					Query:    "CREATE TABLE IF NOT EXISTS existing_tbl (c1 int);",
					Expected: []sql.Row{},
				},
				{
					Query:       "CREATE TABLE seq1 (c1 int);",
					ExpectedErr: `relation "seq1" already exists`,
				},
				{
					Query:    "CREATE TABLE IF NOT EXISTS seq1 (c1 int);",
					Expected: []sql.Row{},
				},
				{
					Query:       "CREATE TABLE view1 (c1 int);",
					ExpectedErr: `relation "view1" already exists`,
				},
				{
					Query:    "CREATE TABLE IF NOT EXISTS view1 (c1 int);",
					Expected: []sql.Row{},
				},
				{
					Query:       "CREATE TABLE idx1 (c1 int);",
					ExpectedErr: `relation "idx1" already exists`,
				},
				{
					Query:    "CREATE TABLE IF NOT EXISTS idx1 (c1 int);",
					Expected: []sql.Row{},
				},
			},
		},
	})
}

func TestCreateTableInherit(t *testing.T) {
	RunScripts(t, []ScriptTest{
		{
			Name: "Create table with inheritance",
			SetUpScript: []string{
				"create table t1 (a int);",
				"create table t2 (b int);",
				"create table t3 (c int);",
				"create table t11 (a int);",
			},
			Assertions: []ScriptTestAssertion{
				{
					Query:    "create table t4 (d int) inherits (t1, t2, t3);",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into t4(a, b, c, d) values (1, 2, 3, 4);",
					Expected: []sql.Row{},
				},
				{
					Query: "select * from t4;",
					Expected: []sql.Row{
						{1, 2, 3, 4},
					},
				},
				{
					Query:    "create table t111 () inherits (t1, t11);",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into t111(a) values (1);",
					Expected: []sql.Row{},
				},
				{
					Query: "select * from t111;",
					Expected: []sql.Row{
						{1},
					},
				},
				{
					Query:    "create table t1t1 (a int) inherits (t1);",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into t1t1(a) values (1);",
					Expected: []sql.Row{},
				},
				{
					Query: "select * from t1t1;",
					Expected: []sql.Row{
						{1},
					},
				},
				{
					Query:    "create table TT1t1 (A int) inherits (t1);",
					Expected: []sql.Row{},
				},
				{
					Query:    "insert into TT1t1(a) values (1);",
					Expected: []sql.Row{},
				},
				{
					Query: "select * from TT1t1;",
					Expected: []sql.Row{
						{1},
					},
				},
			},
		},
	})
}