package core

import (
	"bytes"
	"database/sql"
	"fmt"
	"strings"
	"text/template"
	"time"

	"github.com/linlexing/dbx/ddb"

	"github.com/linlexing/dbx/common"
	"github.com/linlexing/dbx/render"
	"github.com/linlexing/dbx/schema"
	"github.com/linlexing/mapfun"

	"github.com/linlexing/dbx/data"
	"github.com/patrickmn/go-cache"
)

var checkCache = cache.New(time.Second*20, time.Second*10)

type TableCheck struct {
	detailTmpl  *template.Template
	ID          string
	Label       string
	TableName   string
	Must        int
	Condition   sql.NullString
	ReferFields string
	DetailTmpl  sql.NullString
	Dept        string
}

//记录一个实际的审核结果
type TableCheckResult struct {
	ID    string
	Label string
	Must  int

	ReferFields []string
	Detail      string //实际的提示信息
	Remark      string //用户填写的备注信息
}

func loadTableCheckes(db ddb.DB, tableName string, dept *Dept) (result []*TableCheck) {
	chks := []*TableCheck{}
	if r, ok := checkCache.Get(tableName); !ok {
		chks = []*TableCheck{}
		rows, err := db.Query("select id,label,tablename,must,condition,referfields,detailtmpl,dept "+
			"from tablecheck where tablename=?", tableName)
		if err != nil {
			LOG.Panic(err)
		}
		defer rows.Close()
		for rows.Next() {
			chk := &TableCheck{}
			if err = rows.Scan(&chk.ID, &chk.Label, &chk.TableName, &chk.Must, &chk.Condition, &chk.ReferFields, &chk.DetailTmpl, &chk.Dept); err != nil {
				LOG.Panic(err)
			}
			chk.loadDetailTmpl()
			chks = append(chks, chk)
		}
		checkCache.Set(tableName, chks, 0)
	} else {
		chks = r.([]*TableCheck)
	}
	result = []*TableCheck{}
	//再根据dept过滤
	for _, v := range chks {
		if dept.IsParentOrSelf(v.Dept) {
			result = append(result, v)
		}
	}
	return
}
func (t *TableCheck) loadDetailTmpl() {
	if len(t.DetailTmpl.String) > 0 {
		var err error
		t.detailTmpl, err = template.New("detail").Funcs(template.FuncMap{
			"exists": func(db ddb.DB, strSql string, param ...interface{}) bool {
				s := data.Bind(db.DriverName(), strSql)
				rows, err := db.Query(strSql, param...)
				if err != nil {
					err = common.NewSQLError(err, s, param...)
					LOG.Println("exists err")
					LOG.Panic(err)
				}
				defer rows.Close()
				return rows.Next()
			},
			"select": func(db ddb.DB, strSql string, param ...interface{}) (result []map[string]interface{}) {
				s := data.Bind(db.DriverName(), strSql)
				rows, err := db.Query(strSql, param...)
				if err != nil {
					err = common.NewSQLError(err, s, param...)
					LOG.Println("select err")
					LOG.Panic(err)
				}
				defer rows.Close()
				cols, err := rows.Columns()
				if err != nil {
					LOG.Panic(err)
				}
				result = []map[string]interface{}{}
				for rows.Next() {
					vals := []interface{}{}
					for range cols {
						vals = append(vals, new(interface{}))
					}
					if err = rows.Scan(vals...); err != nil {
						LOG.Panic(err)
					}
					//将所有的[]byte转换成string
					for i, v := range vals {
						switch tv := v.(type) {
						case []byte:
							vals[i] = string(tv)
						}
					}
					result = append(result, mapfun.Object(cols, vals))
				}
				return result
			},
			"pluck": mapfun.Pluck,
		}).Parse(t.DetailTmpl.String)
		if err != nil {
			LOG.Panic(err)
		}
	}
}
func (t *TableCheck) renderDetail(row map[string]interface{}, user *User, db ddb.DB) string {
	if len(t.DetailTmpl.String) == 0 {
		return ""
	}

	out := bytes.NewBuffer(nil)
	if err := t.detailTmpl.Execute(out, map[string]interface{}{
		"DB":   db,
		"User": user,
		"Row":  row,
	}); err != nil {
		LOG.Panic(err)
	}
	return out.String()
}

//删除一个审核结果
func deleteCheckResult(db ddb.DB, table *schema.Table, checkID string, keyValues []interface{}) error {
	keyWhere := []string{}
	for _, v := range table.PrimaryKeys {
		keyWhere = append(keyWhere, fmt.Sprintf("%s=?", v))
	}

	//删除审核结果
	strSql := data.Bind(db.DriverName(),
		fmt.Sprintf("delete from CR_%s where checkid=? and %s",
			table.Name, strings.Join(keyWhere, " and\n")))
	sqlParam := append([]interface{}{checkID}, keyValues...)
	if _, err := db.Exec(strSql, sqlParam...); err != nil {
		err = common.NewSQLError(err, strSql, sqlParam...)
		LOG.Println(err)
		return err
	}
	return nil
}

//更新一个审核结果
func updateCheckResult(db ddb.DB, dbTable *schema.Table, checkID string,
	keyValues []interface{}, detail, remark string) error {
	keyWhere := []string{}
	for _, v := range dbTable.PrimaryKeys {
		keyWhere = append(keyWhere, fmt.Sprintf("%s=?", v))
	}

	//保存审核结果至相应的表中,采用先更新，失败再插入的方法
	strSql := fmt.Sprintf("update CR_%s set detail=?,remark=? where checkid=? and %s",
		dbTable.Name, strings.Join(keyWhere, " and\n"))
	sqlParam := append([]interface{}{
		func() interface{} {
			if len(detail) == 0 {
				return nil
			}
			return detail
		}(),
		func() interface{} {
			if len(remark) == 0 {
				return nil
			}
			return remark
		}(), checkID}, keyValues...)
	strSql = data.Bind(db.DriverName(), strSql)
	sr, err := db.Exec(strSql, sqlParam...)
	if err != nil {
		err = common.NewSQLError(err, strSql, sqlParam...)
		LOG.Println(err)
		return err

	}
	i, err := sr.RowsAffected()
	if err != nil {
		return err
	}
	if i > 0 {
		return nil
	}
	//如果没有更新成功，则需要insert
	strSql = fmt.Sprintf("insert into CR_%s(checkid,detail,remark,%s)values(?,?,?,%s)",
		dbTable.Name, strings.Join(dbTable.PrimaryKeys, ","),
		strings.Join(strings.Split(strings.Repeat("?", len(keyValues)), ""), ","))
	sqlParam = append([]interface{}{checkID,
		func() interface{} {
			if len(detail) == 0 {
				return nil
			}
			return detail
		}(),
		func() interface{} {
			if len(remark) == 0 {
				return nil
			}
			return remark
		}()}, keyValues...)
	strSql = data.Bind(db.DriverName(), strSql)
	if _, err := db.Exec(strSql, sqlParam...); err != nil {
		err = common.NewSQLError(err, strSql, sqlParam...)
		LOG.Println(err)
		return err
	}
	return nil

}
func (t *TableCheck) RemoveCheckResult(db ddb.DB, dbTable *schema.Table,
	keyValues []interface{}) (err error) {
	return deleteCheckResult(db, dbTable, t.ID, keyValues)
}
func (t *TableCheck) LoadCheckResult(db ddb.DB, dbTable *schema.Table,
	keyValues []interface{}) (result *TableCheckResult, err error) {
	keyWhere := []string{}
	for _, v := range dbTable.PrimaryKeys {
		keyWhere = append(keyWhere, fmt.Sprintf("%s=?", v))
	}

	//查询审核结果
	strSQL := fmt.Sprintf("select detail,remark from CR_%s where checkid=? and %s",
		dbTable.Name, strings.Join(keyWhere, " and\n"))
	strSQL = data.Bind(db.DriverName(), strSQL)
	sqlParam := append([]interface{}{t.ID}, keyValues...)
	var detail sql.NullString
	var remark sql.NullString
	if err = db.QueryRow(strSQL, sqlParam...).Scan(&detail, &remark); err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		err = common.NewSQLError(err, strSQL, sqlParam...)
		LOG.Println(err)
		return nil, err
	}
	return &TableCheckResult{
		ID:          t.ID,
		Label:       t.Label,
		Must:        t.Must,
		ReferFields: strings.Split(t.ReferFields, ","),
		Detail:      detail.String,
		Remark:      remark.String,
	}, nil
}

//审核一个记录，保存审核结果，如果没有错误的记录，是nil
func (t *TableCheck) CheckRow(dbTable *data.Table, keyValues []interface{},
	remark string, user *User) (result *TableCheckResult, err error) {
	if len(t.Condition.String) == 0 {
		return
	}
	//加入主键条件
	var keyWhere = []string{}
	for _, v := range dbTable.PrimaryKeys {
		keyWhere = append(keyWhere, fmt.Sprintf("%s=?", v))
	}
	where, err := render.RenderSQL(
		fmt.Sprintf("(%s) and (%s)", strings.Join(keyWhere, " and\n"), t.Condition.String),
		map[string]interface{}{"User": user})
	if err != nil {
		return
	}
	//由于加了主键限制，最多返回一行记录
	var rows []map[string]interface{}
	rows, err = dbTable.QueryRows(where, keyValues...)
	if err != nil {
		return
	}
	if len(rows) == 0 {
		result = nil
		err = deleteCheckResult(dbTable.DB.(ddb.DB), dbTable.Table, t.ID, keyValues)
	} else {
		result = &TableCheckResult{
			ID:          t.ID,
			Label:       t.Label,
			Must:        t.Must,
			ReferFields: strings.Split(t.ReferFields, ","),
			Detail:      t.renderDetail(rows[0], user, dbTable.DB.(ddb.DB)),
			Remark:      remark,
		}
		err = updateCheckResult(dbTable.DB.(ddb.DB), dbTable.Table, t.ID,
			keyValues, result.Detail, remark)
	}
	return

}