package advance

import (
	"github.com/stretchr/testify/assert"
	"sort"
	"testing"
)

var exprNumberTable = [][]Expr{
	[]Expr{
		{OpChar: Eq, Value: []interface{}{100, 200, 600}},
		//{OpChar: Eq, Value: []interface{}{500}},
		{OpChar: Range, Value: []interface{}{50, 200}, LeftOp: GreaterThan, RightOp: LessThan},
		{OpChar: LessThanEqual, Value: []interface{}{Infinity, 50}},
		{OpChar: LessThan, Value: []interface{}{Infinity, 250}},
		{OpChar: Range, Value: []interface{}{60, 100}},
		{OpChar: Range, Value: []interface{}{60, 70}},
		{OpChar: NotEqual, Value: []interface{}{60, 61, 62}},
	},
	[]Expr{
		{OpChar: Range, Value: []interface{}{100, 200}, LeftOp: GreaterThan, RightOp: LessThanEqual},
		{OpChar: Range, Value: []interface{}{Infinity, 50}, LeftOp: GreaterThan, RightOp: LessThanEqual},
		{OpChar: Range, Value: []interface{}{50, 90}, LeftOp: GreaterThan, RightOp: LessThanEqual},
		{OpChar: Range, Value: []interface{}{150, 300}, LeftOp: GreaterThan, RightOp: LessThanEqual},
	},
}

var exprDateTable = [][]Expr{
	[]Expr{
		{OpChar: Range, Value: []interface{}{1611731000, 1611735000}},
		{OpChar: LessThanEqual, Value: []interface{}{Infinity, 1611721000}},
		{OpChar: Range, Value: []interface{}{1611734000, 1611737000}},
	},
	[]Expr{
		{OpChar: Range, Value: []interface{}{1611731000, 1611735000}},
		{OpChar: LessThanEqual, Value: []interface{}{Infinity, 1611721000}},
		{OpChar: Range, Value: []interface{}{1611734000, 1611737000}},
		{OpChar: Recent, Value: []interface{}{5}},
	},
}

var exprCharsTable = [][]Expr{
	[]Expr{
		{OpChar: In, Value: []interface{}{"abc", "abd"}},
		{OpChar: Eq, Value: []interface{}{"abc"}},
	},
	[]Expr{
		{OpChar: In, Value: []interface{}{"华南", "华北"}},
		{OpChar: Eq, Value: []interface{}{"华北"}},
		{OpChar: Like, Value: []interface{}{"华"}},
		{OpChar: Like, Value: []interface{}{"中"}},
	},
}

var columnChar = Column{
	Column:    "userName",
	Name:      "姓名",
	DbAlias:   "ext->>'userName'",
	ValueType: ValueChars,
}

var columnNumber = Column{
	Column:    "age",
	Name:      "年龄",
	DbAlias:   "age",
	ValueType: ValueNumber,
}

// 数字表达式合并
func TestJoinNumberColumnExpr(t *testing.T) {
	for i := range exprNumberTable {
		out := JoinColumnExprNumber(exprNumberTable[i])
		t.Log(out)
	}
}

// 时间表达式合并
func TestJoinDateColumnExpr(t *testing.T) {
	for i := range exprDateTable {
		out := JoinColumnExprDate(exprDateTable[i])
		t.Log(out)
	}
}

// 字符串表达式合并
func TestJoinCharsColumnExpr(t *testing.T) {
	for i := range exprCharsTable {
		out := JoinColumnExprChars(exprCharsTable[i])
		t.Log(out)
	}
}

// 排序测试
func TestSortExprList(t *testing.T) {
	rec := RangeNumberExprCompute{valueType: ValueNumber}
	expr := exprNumberTable[0]
	for i := range expr {
		if expr[i].OpChar == Range || expr[i].OpChar == LessThanEqual || expr[i].OpChar == GreaterThanEqual {
			rec.expr = append(rec.expr, expr[i])
		}
	}
	var exprSort = exprSortable(rec.expr)
	//log.Info("before:", exprSort)
	sort.Sort(exprSort)
	//log.Info("after:", exprSort)
	rec.expr = exprSort
}

func TestAdvancedQuerySqlNormal(t *testing.T) {
	inputs := []AdvancedQuery{
		{
			Column: columnChar,
			Exprs:  exprCharsTable[1],
		},
		{
			Column: columnNumber,
			Exprs:  exprNumberTable[0],
		},
	}
	sql, _ := AdvancedQuerySql(inputs)
	t.Log(sql)
}

func TestAdvancedQuerySqlMerge(t *testing.T) {
	inputs := []AdvancedQuery{
		{
			Column: columnChar,
			Exprs:  exprCharsTable[1],
		},
		{
			Column: columnNumber,
			Exprs:  exprCharsTable[1],
		},
	}
	sql, _ := AdvancedQuerySql(inputs)
	t.Log(sql)
}

func TestAdvancedQuerySqlNumber(t *testing.T) {
	inputs := []AdvancedQuery{
		{
			Column: columnChar,
			Exprs:  exprCharsTable[1],
		},
		{
			Column: columnNumber,
			Exprs:  exprNumberTable[1],
		},
	}
	sql, _ := AdvancedQuerySql(inputs)
	t.Log(sql)
}

func TestAdvancedQuerySql_PG(t *testing.T) {
	tables := []struct {
		name      string
		col       Column
		ins       []Expr
		except    interface{}
		exceptSql string
		ok        bool
	}{
		// in (equal)
		{
			col:  columnChar,
			name: "in zero item",
			ins: []Expr{
				{OpChar: In, Value: []interface{}{}},
			},
			except:    nil,
			exceptSql: "",
			ok:        false,
		},
		{
			col:  columnChar,
			name: "in one item",
			ins: []Expr{
				{OpChar: In, Value: []interface{}{"foo"}},
			},
			except:    nil,
			exceptSql: "(ext->>'userName' in ('foo'))",
			ok:        true,
		},
		{
			col:  columnChar,
			name: "in many item",
			ins: []Expr{
				{OpChar: In, Value: []interface{}{"bar", "ccc", "foo", "ele"}},
			},
			except:    nil,
			exceptSql: "(ext->>'userName' in ('bar','ccc','ele','foo'))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "in many item (number)",
			ins: []Expr{
				{OpChar: In, Value: []interface{}{1, 2, 3, 4}},
			},
			except:    nil,
			exceptSql: "(age in (1,2,3,4))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "in many item (number)",
			ins: []Expr{
				{OpChar: In, Value: []interface{}{1, 2, 3, 4}},
			},
			except:    nil,
			exceptSql: "(age in (1,2,3,4))",
			ok:        true,
		},

		// not in (not equal)
		{
			col:  columnNumber,
			name: "not equal many item (number)",
			ins: []Expr{
				{OpChar: NotEqual, Value: []interface{}{1, 2, 3, 4}},
				{OpChar: NotEqual, Value: []interface{}{5, 6}},
			},
			except:    nil,
			exceptSql: "(age not in (1,2,3,4,5,6))",
			ok:        true,
		},

		// range
		{
			col:  columnChar,
			name: "range one item",
			ins: []Expr{
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 60}},
			},
			except:    nil,
			exceptSql: "",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range one item",
			ins: []Expr{
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 60}},
			},
			except:    nil,
			exceptSql: "(( age <= 60 ))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range many item (LessThanEqual)",
			ins: []Expr{
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 60}},
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 80}},
			},
			except:    nil,
			exceptSql: "(( age <= 60 ))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range many item (GreaterThanEqual)",
			ins: []Expr{
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 60}},
				{OpChar: GreaterThanEqual, Value: []interface{}{80, Infinity}},
				{OpChar: GreaterThanEqual, Value: []interface{}{200, Infinity}},
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 70}},
			},
			except:    nil,
			exceptSql: "", // 或集  (( age <= 70 ) or ( age >= 80 ))  并集 ""
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range many item (Between $<=n<=$)",
			ins: []Expr{
				{OpChar: GreaterThanEqual, Value: []interface{}{80, Infinity}},
				{OpChar: LessThanEqual, Value: []interface{}{Infinity, 200}},
				{OpChar: LessThan, Value: []interface{}{Infinity, 150}},
			},
			except:    nil,
			exceptSql: "(( age >= 80  and  age < 150 ))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range many item (100<=n<=200)",
			ins: []Expr{
				{OpChar: Range, Value: []interface{}{100, 200}, LeftOp: GreaterThanEqual, RightOp: LessThanEqual},
			},
			except:    nil,
			exceptSql: "(( age >= 100  and  age <= 200 ))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "range many item (80<n<220)",
			ins: []Expr{
				{OpChar: Range, Value: []interface{}{"150", "180"}, LeftOp: GreaterThanEqual, RightOp: LessThanEqual},
				{OpChar: Range, Value: []interface{}{"120", "220"}, LeftOp: GreaterThanEqual, RightOp: LessThan},
				{OpChar: Range, Value: []interface{}{"100", "200"}, LeftOp: GreaterThanEqual, RightOp: LessThanEqual},
				{OpChar: Range, Value: []interface{}{"80", "100"}, LeftOp: GreaterThan, RightOp: LessThanEqual},
				{OpChar: Range, Value: []interface{}{"300", "500"}, LeftOp: GreaterThan, RightOp: LessThanEqual},
			},
			except:    nil,
			exceptSql: "(( age > 80  and  age < 220 ) or ( age > 300  and  age <= 500 ))",
			ok:        true,
		},

		// like
		{
			col:  columnChar,
			name: "like zero item",
			ins: []Expr{
				{OpChar: Like, Value: []interface{}{}},
			},
			except:    nil,
			exceptSql: "",
			ok:        false,
		},
		{
			col:  columnChar,
			name: "like one item",
			ins: []Expr{
				{OpChar: Like, Value: []interface{}{"foo"}},
			},
			except:    nil,
			exceptSql: "((ext->>'userName' like '%foo%'))",
			ok:        true,
		},
		{
			col:  columnChar,
			name: "like many item",
			ins: []Expr{
				{OpChar: Like, Value: []interface{}{"bar", "ccc"}},
			},
			except:    nil,
			exceptSql: "((ext->>'userName' like '%bar%' or ext->>'userName' like '%ccc%'))",
			ok:        true,
		},
		{
			col:  columnNumber,
			name: "like many item (number)",
			ins: []Expr{
				{OpChar: Like, Value: []interface{}{10, 20}},
			},
			except:    nil,
			exceptSql: "((age::text like '%10%' or age::text like '%20%'))",
			ok:        true,
		},

		// error input
		{
			col:  columnChar,
			name: "invalid input",
			ins: []Expr{
				{OpChar: Like, Value: []interface{}{"dsger?*"}},
			},
			except:    nil,
			exceptSql: "",
			ok:        false,
		},
	}

	for i := range tables {
		q := []AdvancedQuery{
			{
				Column: tables[i].col,
				Exprs:  tables[i].ins,
			},
		}
		t.Run(tables[i].name, func(t *testing.T) {
			sql, err := AdvancedQuerySql(q)
			assert.Equal(t, tables[i].exceptSql, sql)
			if tables[i].ok {
				assert.Nil(t, err)
			}
		})
	}
}