package common

import (
	"bytes"
	"dbweb/core"
	"dbweb/lib/safe"
	"dbweb/lib/strfun"
	"dbweb/modules/common/recordview"
	"encoding/csv"
	"encoding/gob"
	"encoding/json"
	"fmt"
	"net/url"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/linlexing/dbx/ddb"

	"github.com/linlexing/dbx/data"
	"github.com/linlexing/dbx/pageselect"
	"github.com/linlexing/dbx/scan"
	"github.com/linlexing/dbx/schema"
	"github.com/linlexing/mapfun"
)

const (
	defaultLimit       = 50
	showSQL            = true
	CtrlRecordViewName = "recordview"
)

func init() {
	gob.Register(new(recordview.ProcessParam))
	core.RegisterFun(CtrlRecordViewName, new(RecordView))
	core.RegisterElementType(CtrlRecordViewName, ModelRecordViewElement{})

}

//RecordView 业务
type RecordView struct{}

//ElementAjaxProcess 用于工作元素定义时自动调用
func (m *RecordView) ElementAjaxProcess(p *core.BillGetHandleArgs) {
	query := p.Req.URL.Query()
	switch query.Get("action") {
	case "getTableList":
		db := core.LoadOuterDB(p.DB, query.Get("db"))
		names, err := schema.Find(db.DriverName()).TableNames(db)
		if err != nil {
			core.LOG.Panic(err)
		}
		p.Render.JSON(200, names)
	case "checkSql":
		db := core.LoadOuterDB(p.DB, query.Get("db"))
		manualPage := safe.Bool(query.Get("manualPage"))
		table := query.Get("table")
		strSQL := query.Get("sql")
		result := map[string]interface{}{}
		var typeCols []*scan.ColumnType
		if len(table) > 0 {
			typeTable, err := schema.Find(db.DriverName()).OpenTable(db, table)
			if err != nil {
				core.LOG.Panic(err)
			}
			typeCols = []*scan.ColumnType{}
			for _, col := range typeTable.Columns {
				typeCols = append(typeCols,
					&scan.ColumnType{Name: col.Name, Type: col.Type})
			}
		}
		sel := recordview.NewRecordSelect(p.ElementHandleArgs)
		sel.TableName = table
		sel.DBName = query.Get("db")
		sel.SQL = strSQL
		sel.ColumnTypes = typeCols
		sel.ManualPage = manualPage
		sel.Limit = 1
		_, list, err := sel.QueryRows()
		if err != nil {
			result["error"] = err.Error() + "\nsql:" + strSQL
			p.Render.JSON(200, result)
			return
		}
		result["columns"] = list
		p.Render.JSON(200, result)
	default:
		core.LOG.Panic("not impl")
	}
}

//ElementParamRender 定义工作元素时调用
func (m *RecordView) ElementParamRender(p *core.BillGetHandleArgs) {
	jsonParams := recordview.RecordViewParam{}
	if bys := safe.Bytea(p.Record.Main["PARAMS"]); bys != nil {
		if err := json.Unmarshal(bys, &jsonParams); err != nil {
			core.LOG.Println(string(bys))
			core.LOG.Panic(err)
		}
	}
	//增加一个空项
	dbNames := core.OuterDBNames(p.DB)
	newList := []string{""}
	dbNames = append(newList, dbNames...)
	p.More["OuterdbNames"] = dbNames

	if core.OuterDBExists(p.DB, safe.String(jsonParams.DB)) {
		tabledb := core.LoadOuterDB(p.DB, safe.String(jsonParams.DB))
		names, err := schema.Find(tabledb.DriverName()).TableNames(tabledb)
		if err != nil {
			core.LOG.Panic(err)
		}
		p.More["TableNames"] = names
	} else {
		core.LOG.Println("not found outerdb:", safe.String(jsonParams.DB))
	}
	p.More["JSONParams"] = jsonParams
	elementNames, err := ddb.ScanStrings(p.DB, "select name from element order by name")
	if err != nil {
		core.LOG.Panic(err)
	}
	p.More["ElementNames"] = elementNames
}

//Post 业务
func (m *RecordView) Post(p *core.ElementHandleArgs) {
	query := p.Req.URL.Query()
	action := query.Get("action")
	switch action {
	case "addPublic":
		decoder := json.NewDecoder(p.Req.Body)
		data := &EleView{}
		if err := decoder.Decode(data); err != nil {
			core.LOG.Panic(err)
		}
		data.Public = 1
		data.Owner = p.User.Dept.Code
		data.EleName = p.Element.Name
		err := InsertEleView(p.DB, data)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	case "addPrivate":
		decoder := json.NewDecoder(p.Req.Body)
		data := &EleView{}
		if err := decoder.Decode(data); err != nil {
			core.LOG.Panic(err)
		}
		data.Public = 0
		data.Owner = p.User.Name
		data.EleName = p.Element.Name
		err := InsertEleView(p.DB, data)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	case "editPublic":
		oldName := query.Get("old")
		oldOwner := strings.Split(oldName, ".")[0]
		oldName = oldName[len(oldOwner)+1:]

		decoder := json.NewDecoder(p.Req.Body)
		data := &EleView{}

		if err := decoder.Decode(data); err != nil {
			core.LOG.Panic(err)
		}
		data.Owner = oldOwner
		data.Public = 1
		data.EleName = p.Element.Name
		err := UpdateEleView(p.DB, oldName, data)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	case "editPrivate":
		oldName := query.Get("old")
		decoder := json.NewDecoder(p.Req.Body)
		data := &EleView{}
		if err := decoder.Decode(data); err != nil {
			core.LOG.Panic(err)
		}
		data.Owner = p.User.Name
		data.Public = 0
		data.EleName = p.Element.Name
		err := UpdateEleView(p.DB, oldName, data)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	case "delPublic":
		name := query.Get("name")
		owner := strings.Split(name, ".")[0]
		name = name[len(owner)+1:]
		err := DeleteEleView(p.DB, p.Element.Name, 1, owner, name)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	case "delPrivate":
		name := query.Get("name")
		err := DeleteEleView(p.DB, p.Element.Name, 0, p.User.Name, name)
		if err == nil {
			p.Res.Write([]byte(`{"ok":true}`))
			return
		}
		bys, _ := json.Marshal(map[string]interface{}{
			"ok":    false,
			"error": err.Error(),
		})
		p.Res.Write(bys)

	default:
		core.LOG.Panic("not impl")
	}
}
func splitAndGroup(val string) []string {
	rd := csv.NewReader(bytes.NewBufferString(val))
	rs, err := rd.Read()
	if err != nil {
		core.LOG.Panic(err)
	}
	//排重
	rs = mapfun.GroupStr(rs)
	return rs
}

//将csv字符串解开，并保存到临时表中
func saveInList(db ddb.DB, sid string, val []string) error {
	if _, err := db.Exec("delete from TMPINTABLE where sid=?", sid); err != nil {
		return err
	}
	if len(val) > 0 {
		tab, err := data.OpenTable(db.DriverName(), db, "TMPINTABLE")
		if err != nil {
			return err
		}
		rows := []map[string]interface{}{}
		for _, one := range val {
			rows = append(rows, map[string]interface{}{
				"SID": sid,
				"VAL": one,
			})
		}
		if e := tab.Insert(rows); e != nil {
			return e
		}

	}

	return nil
}

//Get 业务
func (m *RecordView) Get(p *core.ElementHandleArgs) {
	startTime := time.Now()
	query := p.Req.URL.Query()
	action := query.Get("action")
	if action == "gv" { //获取定义
		evname := query.Get("evname")
		pub := 0
		owner := p.User.Name

		if query.Get("pub") == "1" {
			pub = 1
			owner = strings.Split(evname, ".")[0]
			evname = evname[len(owner)+1:]
		}
		p.Render.JSON(200, LoadEleView(p.DB, p.Element.Name, pub, owner, evname))
		return
	}
	divide := safe.Split(query.Get("divide"), ",")
	order := safe.Split(query.Get("order"), ",")
	//如果有more参数，则需要向下传递
	moreStr := query.Get("_more")
	field := query.Get("field")
	opt := query.Get("opt")
	val := query.Get("val")
	//如果val是缩写，则取出
	if strings.HasPrefix(val, "$$:") {
		val = GetSessionText(p.LSession, val[3:])
	}
	stype := query.Get("stype")
	_tqTitle := p.User.DecodeQueryValue(query.Get("_tq_title"))
	_tqCondition := p.User.DecodeQueryValue(query.Get("_tq_condition"))
	selKeys := query["s"]
	if len(selKeys) > 1 {
		sort.Strings(selKeys)
	}
	limit, err := strconv.ParseInt(p.Req.URL.Query().Get("limit"), 10, 64)
	if err != nil || limit == 0 {
		limit = defaultLimit
	}
	divideRowNum, err := strconv.ParseInt(p.Req.URL.Query().Get("rn"), 10, 64)
	if err != nil {
		divideRowNum = 0
	}
	rowCount, err := strconv.ParseInt(p.Req.URL.Query().Get("rc"), 10, 64)
	if err != nil {
		rowCount = -1
	}

	var pubView *EleView
	var priView *EleView
	var usedDspCol []*recordview.DisplayColumn //最终采用的显示列的定义
	var hideCols []string

	if len(p.Element.Params) == 0 {
		core.LOG.Panic(fmt.Errorf("the element:%s must define param", p.Element.Name))
	}
	param := recordview.ParseParam(p.Element.Params, p.User)
	//传递More参数
	for _, v := range param.Processes {
		v.MoreStr = moreStr
	}

	pubList, err := GetPubEleViewList(p.DB, p.Element.Name, p.User)
	if err != nil {
		core.LOG.Panic(err)
	}
	priList, err := GetPriEleViewList(p.DB, p.Element.Name, p.User)
	if err != nil {
		core.LOG.Panic(err)
	}

	pub := p.Req.URL.Query().Get("pub")
	if len(pub) > 0 {
		pubOwner := strings.Split(pub, ".")[0]
		pub = pub[len(pubOwner)+1:]
		pubView = LoadEleView(p.DB, p.Element.Name, 1, pubOwner, pub)
	}
	pri := p.Req.URL.Query().Get("pri")
	if len(pri) > 0 {
		priView = LoadEleView(p.DB, p.Element.Name, 0, p.User.Name, pri)
	}

	if len(param.Columns) > 0 {
		usedDspCol = param.Columns
	}
	sqlSelect := recordview.NewRecordSelect(p)
	sqlSelect.DBName = param.DB
	sqlSelect.TableName = param.TableName
	sqlSelect.ManualPage = param.ManualPage
	sqlSelect.ColumnTypes = param.AllColumns
	sqlSelect.More = func() []string {
		moreParam := []string{}
		//如果有more字符串数组传入，则解包，该参数主要用于主键传递
		if len(moreStr) > 0 {
			bys := bytes.NewBufferString(p.User.DecodeQueryValue(moreStr))
			if err := gob.NewDecoder(bys).Decode(&moreParam); err != nil {
				core.LOG.Panic(err)
			}
		}
		return moreParam
	}()
	sqlSelect.PKS = param.UniqueField
	sqlSelect.SQL = param.Sql

	if pubView != nil {
		sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
			Name:      "pub",
			Lines:     pubView.Conts,
			PlainText: pubView.PlainText,
		})
		if len(pubView.DspCols) > 0 {
			usedDspCol = pubView.DspCols
		}
	}
	if priView != nil {
		sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
			Name:      "pri",
			Lines:     priView.Conts,
			PlainText: priView.PlainText,
		})
		if len(priView.DspCols) > 0 {
			usedDspCol = priView.DspCols
		}
	}
	if len(field) > 0 && len(opt) > 0 {
		processed := false
		//如果是在列表、不在列表，则转为临时表的关联查询
		//并且超出了1000行
		if opt == "in" || opt == "!in" {
			valList := splitAndGroup(val)
			if len(valList) > 1000 {

				if err := saveInList(core.LoadOuterDB(p.DB, param.DB), p.LSession.SessionIDString(),
					valList); err != nil {
					fmt.Println("save in list error", err)
					p.RenderError(err.Error())
					return
				}
				ptxt := ""
				if opt == "in" {
					ptxt = fmt.Sprintf(
						"exists(select 1 from TMPINTABLE where TMPINTABLE.SID=%s and wholesql.%s=TMPINTABLE.VAL)",
						safe.SignStringUseBySqlxNamed(p.LSession.SessionIDString()), field)
				} else {
					ptxt = fmt.Sprintf(
						"not exists(select 1 from TMPINTABLE where TMPINTABLE.SID=%s and wholesql.%s=TMPINTABLE.VAL)",
						safe.SignStringUseBySqlxNamed(p.LSession.SessionIDString()), field)
				}
				sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
					Name:      "one",
					PlainText: ptxt,
				})
				processed = true
			}
		}
		if !processed {
			popt, err := pageselect.ParseString(query.Get("opt"))
			if err != nil {
				core.LOG.Panic(err)
			}

			sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
				Name: "one",
				Lines: []*pageselect.ConditionLine{
					&pageselect.ConditionLine{
						ColumnName: field,
						Operators:  popt,
						Value:      val,
					},
				},
				PlainText: "",
			})
		}
	}
	//如果没有选中，又选择了仅显示选中，则是空
	if stype == "s" && len(selKeys) == 0 {
		sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
			Name:      "sel",
			PlainText: "1=2",
		})
	} else if len(stype) > 0 && len(selKeys) > 0 {
		sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
			Name:      "sel",
			Lines:     buildSelCondition(param.UniqueField, stype, selKeys),
			PlainText: "",
		})
	}
	//如果有传入的附加条件，则作为单独的高级条件传入sql构建中
	if len(_tqCondition) > 0 {
		sqlSelect.Conditions = append(sqlSelect.Conditions, &pageselect.SQLCondition{
			Name:      "tq_cnd",
			PlainText: string(_tqCondition),
		})
	}
	sqlSelect.Limit = int(limit) //多取一行是为了判断是否刚好是limit个记录
	sqlSelect.Columns = usedDspCol
	sqlSelect.Order = order
	sqlSelect.Divide = divide
	switch action {
	case "rc": //汇总记录总数
		if rcount, err := sqlSelect.RowCount(); err != nil {
			core.LOG.Panic(err)
		} else {
			p.Render.JSON(200, map[string]interface{}{"RowCount": rcount})
			return
		}
	case "total": //汇总数值字段
		//找出数值字段
		totalColumns := query["tcols"]
		row, err := sqlSelect.Total(totalColumns)
		var errStr string
		if err != nil {
			errStr = err.Error()
		}
		p.Render.JSON(200, map[string]interface{}{"Data": row, "Error": errStr})
		return

	}
	divideRowNum++
	renderResult, err := param.QueryRender(sqlSelect, selKeys, divideRowNum)
	if err != nil {
		p.RenderError(err.Error())
		return
	}
	if showSQL {
		p.More["ShowSql"] = true
		p.More["SelectSql"] = sqlSelect.BuildSQL()
	}
	if renderResult.RowCount > 0 {
		rowCount = renderResult.RowCount
	}
	var queryErr error
	//如果有错误，则去掉一个条件，防止错误的条件引起无法删除模板
	for err != nil && len(sqlSelect.Conditions) > 0 {
		println("found error:", err.Error(), "cons count:", len(sqlSelect.Conditions))
		queryErr = err
		sqlSelect.Conditions = sqlSelect.Conditions[1:]
		sqlSelect.Columns = nil
		sqlSelect.Order = nil
		renderResult, err = param.QueryRender(sqlSelect, selKeys, divideRowNum)
	}
	timesum := time.Since(startTime)
	if err != nil {
		p.RenderError(err.Error())
		return
	}
	param.BuildProcessParamToSession(p, sqlSelect, selKeys, hideCols)

	//查出在列表找不到的项目
	if len(field) > 0 && opt == "in" {
		//考虑到性能问题，不用表查询，直接读取查询后的结果中该字段的值，然后程序得出差异
		dataSQL := fmt.Sprintf(`select %s from(%s) ob group by %[1]s`, field, sqlSelect.DataSQL())
		ls, err := ddb.ScanStrings(core.LoadOuterDB(p.DB, param.DB), dataSQL)
		if err != nil {
			fmt.Println("ScanStrings error", err)
			p.RenderError(err.Error())
			return
		}
		valList, err := csv.NewReader(bytes.NewBufferString(val)).Read()
		if err != nil {
			fmt.Println("csv reader error", err, "val:", val)
			p.RenderError(err.Error())
			return
		}
		notinlist := mapfun.WithoutStr(valList, ls...)
		p.More["NotInList"] = strings.Join(notinlist, "\n")
		p.More["NotInListCount"] = len(notinlist)
	}
	if queryErr != nil {
		p.More["QueryError"] = queryErr.Error()
	}
	//如果有传入的标题，则传递至模板中
	if len(_tqTitle) > 0 {
		p.More["TransQueryTitle"] = string(_tqTitle)
	}
	p.More["UniqueField"] = param.UniqueField
	p.More["Processes"] = param.Processes
	p.More["DefaultDspCols"] = param.Columns
	p.More["AllColumns"] = param.AllColumns
	p.More["Order"] = order
	p.More["PubView"] = pubView
	if pubView != nil && pubView.Owner == p.User.Dept.Code {
		p.More["PubViewCanModified"] = true
	}
	p.More["PriView"] = priView
	p.More["Rows"] = renderResult.Rows
	p.More["PubList"] = pubList
	p.More["PriList"] = priList
	p.More["Columns"] = renderResult.DisplayColumns
	p.More["RowCount"] = rowCount
	p.More["UpRowNum"] = p.Req.URL.Query()["uprn"]
	p.More["UpDivide"] = p.Req.URL.Query()["updivide"]
	p.More["DownDivide"] = renderResult.DownDivide
	p.More["DownRowNum"] = renderResult.DownRowNum
	p.More["TimeSum"] = fmt.Sprintf("%s", timesum)
	p.More["SearchColumns"] = renderResult.Columns
	p.More["SearchOperate"] = recordview.GetOftenOperates()
	//判断是否有数值字段，如有，则需要添加合计按钮
	if len(renderResult.Rows) > 0 {
		canTotal := false
		totalURL, _ := url.Parse(p.Req.URL.String())
		totalQuery := totalURL.Query()
		for _, v := range renderResult.DisplayColumns {
			switch v.Type {
			case schema.TypeInt, schema.TypeFloat:
				canTotal = true
				totalQuery.Add("tcols", v.Name)
			}
		}
		p.More["CanTotal"] = canTotal
		if canTotal {
			totalQuery.Set("action", "total")
			totalURL.RawQuery = totalQuery.Encode()
			p.More["TotalUrl"] = totalURL.String()
		}
	}
	p.HTML()
}

func buildSelCondition(uniqueField []string, stype string, selKeys []string) []*pageselect.ConditionLine {
	lines := []*pageselect.ConditionLine{}
	switch stype {
	case "s":
		for _, comboKey := range selKeys {
			//解开主键值为一个数组
			keys := strfun.DecodeCSV(comboKey)
			keyTerms := []*pageselect.ConditionLine{}
			for i, key := range keys {
				keyTerms = append(keyTerms, &pageselect.ConditionLine{
					ColumnName: uniqueField[i],
					Operators:  pageselect.OperatorEqu,
					Value:      key,
					Logic:      "AND",
				})
			}
			//每个主键之间用or
			if len(uniqueField) == 1 {
				keyTerms[0].Logic = "OR"
			} else if len(uniqueField) > 1 {
				keyTerms[0].LeftBrackets = "("
				keyTerms[len(keyTerms)-1].RightBrackets = ")"
				keyTerms[len(keyTerms)-1].Logic = "OR"
			}
			lines = append(lines, keyTerms...)
		}
	case "ns":
		for _, comboKey := range selKeys {
			//解开主键值为一个数组
			keys := strfun.DecodeCSV(comboKey)
			keyTerms := []*pageselect.ConditionLine{}
			for i, key := range keys {
				keyTerms = append(keyTerms, &pageselect.ConditionLine{
					ColumnName: uniqueField[i],
					Operators:  pageselect.OperatorNotEqu,
					Value:      key,
					Logic:      "OR",
				})
			}
			//每个主键之间用and
			if len(uniqueField) == 1 {
				keyTerms[0].Logic = "AND"
			} else if len(uniqueField) > 1 {
				keyTerms[0].LeftBrackets = "("
				keyTerms[len(keyTerms)-1].RightBrackets = ")"
				keyTerms[len(keyTerms)-1].Logic = "AND"
			}
			lines = append(lines, keyTerms...)
		}
	default:
		core.LOG.Panic("not impl")
	}
	return lines
}

//without is array dec values
func without(src []*scan.ColumnType, vals ...string) []*scan.ColumnType {
	if src == nil {
		return nil
	}
	if len(vals) == 0 {
		return src
	}
	r := []*scan.ColumnType{}
	for _, v := range src {
		b := false
		for _, sv := range vals {
			if v.Name == sv {
				b = true
				break
			}
		}
		if !b {
			r = append(r, v)
		}
	}
	return r
}

//reverse the array
func reverse(src []map[string]interface{}) []map[string]interface{} {
	if len(src) == 0 {
		return src
	}
	r := make([]map[string]interface{}, len(src))
	for i, v := range src {
		r[len(r)-i-1] = v
	}
	return r
}