package mysql

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"runtime/debug"
	"strconv"
	"strings"
	"sync"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

type Db struct {
	DbConn *DbConnection
	// DbQuery  DbQuery
	Dbconfig *map[string]interface{}
}

// 创建连接器
type DbConnection struct {
	DB       *sql.DB
	DbPrefix string
	DbName   string
	DbConf   map[string]string
}

// 构建查询器
type DbQuery struct {
	DB       *sql.DB
	Prefix   string
	Executor *ExQuery
	sync.RWMutex
}

// 执行器
type ExQuery struct {
	Wher   string
	args   []interface{}
	Joinn  string
	order  string
	limit  string
	alias  string
	table  string
	group  string
	having string
	in     []interface{}
	field  string
	Rows   interface{}
	Row    interface{}
	Prefix string
	DB     *sql.DB
	Ctx    context.Context
	Tx     *sql.Tx
	sync.RWMutex
}

// 新增分页结果结构体
type PageResult struct {
	Data        []map[string]interface{} `json:"data"`
	Total       int                      `json:"total"`
	CurrentPage int                      `json:"current_page"`
	PageSize    int                      `json:"page_size"`
	TotalPages  int                      `json:"total_pages"`
}

// 构建连接
func (DbConnection *DbConnection) Connt(cnt *map[string]string) error {
	var build strings.Builder
	build.WriteString((*cnt)["username"])
	build.WriteString(":")
	build.WriteString((*cnt)["password"])
	build.WriteString("@")
	build.WriteString((*cnt)["network"])
	build.WriteString("(")
	build.WriteString((*cnt)["server"])
	build.WriteString(":")
	build.WriteString((*cnt)["port"])
	build.WriteString(")/")
	build.WriteString((*cnt)["database"])
	db, err := sql.Open("mysql", build.String())
	if err != nil {
		log.Printf("Open mysql failed,err:%v\n", err)
		return err
	}
	db.SetConnMaxLifetime(100 * time.Second) // 生命周期
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(16)
	DbConnection.DB = db
	DbConnection.DbPrefix = (*cnt)["prefix"]
	DbConnection.DbName = (*cnt)["database"]
	DbConnection.DbConf = (*cnt)

	return nil
}

func (db *Db) Db(table string) *ExQuery {
	var q = new(DbQuery)
	q.Executor = new(ExQuery)
	q.Builder(db.DbConn)
	if table != "" {
		return q.Executor.Table(table)
	} else {
		return q.Executor
	}
}

// dbConnection.DB.PingContext(ctx)
// 防止ping阻塞
func (q *DbQuery) Builder(dbConnection *DbConnection) {
	ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
	defer cancel()
	connerr := dbConnection.DB.PingContext(ctx)
	if connerr != nil {
		dbconf := dbConnection.DbConf
		dbConnection.Connt(&dbconf)
		log.Printf("Mysql连接不可用 <%v>", connerr)
	}
	q.Executor.DB = dbConnection.DB
	q.Executor.Prefix = dbConnection.DbPrefix
}

func (q *ExQuery) Table(table string) *ExQuery {
	var b strings.Builder
	b.WriteString(q.Prefix)
	b.WriteString(table)
	// 实例新的执行器
	newq := new(ExQuery)
	newq.DB = q.DB
	// db.Db创建事务后需要复用
	if q.Ctx != nil && q.Tx != nil {
		newq.Ctx = q.Ctx
		newq.Tx = q.Tx
	}
	newq.table = b.String()
	newq.Prefix = q.Prefix
	return newq
}
func (q *ExQuery) Alias(alias string) *ExQuery {
	q.alias = alias
	return q
}
// ( )查询
func (q *ExQuery) WhereOr(f func(query *ExQuery) *ExQuery) *ExQuery {
	if q.Wher != "" {
		q.Wher += " AND ( "
	} else {
		q.Wher = " ( "
	}
	oldWher := q.Wher
	q.Wher = ""
	f(q)
	q.Wher = oldWher + q.Wher + " ) "
	return q
}
func (q *ExQuery) In(in string, v []interface{}) *ExQuery {
	l := len(v)
	if l > 0 {
		q.in = v
		var b strings.Builder
		b.WriteString(in)
		b.WriteString(" IN (")
		for k, _ := range v {
			b.WriteString("?")
			if k < (l - 1) {
				b.WriteString(",")
			}
		}
		b.WriteString(") ")
		if q.Wher != "" {
			q.Wher += " AND " + b.String()
			q.args = append(q.args, v...)
		} else {
			q.Wher = b.String()
			q.args = v
		}
	}
	return q
}

// 新增LIKE条件查询
func (q *ExQuery) Like(column string, value string) *ExQuery {
	if value != "" {
		// q.Where(column+" LIKE ?", "%"+value+"%")
		q.Where(column+" LIKE ?", "%"+value+"%")
	}
	return q
}

// 新增OR LIKE条件查询
func (q *ExQuery) OrLike(column string, value string) *ExQuery {
	if value != "" {
		q.Or(column+" LIKE ?", "%"+value+"%")
	}
	return q
}
func (q *ExQuery) OrIn(in string, v []interface{}) *ExQuery {
	l := len(v)
	if l > 0 {
		q.in = v
		var b strings.Builder
		b.WriteString(in)
		b.WriteString(" IN (")
		for k, _ := range v {
			b.WriteString("?")
			if k < (l - 1) {
				b.WriteString(",")
			}
		}
		b.WriteString(") ")
		if q.Wher != "" {
			q.Wher += " OR " + b.String()
			q.args = append(q.args, v...)
		} else {
			q.Wher = b.String()
			q.args = v
		}
	}
	return q
}
func (q *ExQuery) Where(wher string, v ...interface{}) *ExQuery {
	if wher != "" {
		if q.Wher != "" {
			q.Wher += " AND " + wher
			q.args = append(q.args, v...)
		} else {
			q.Wher = wher
			q.args = append(q.args, v...)
		}
	}
	return q
}
func (q *ExQuery) Or(or string, v ...interface{}) *ExQuery {
	if or != "" {
		if q.Wher != "" {
			q.Wher += " OR " + or
			q.args = append(q.args, v...)
		} else {
			q.Wher = or
			q.args = append(q.args, v...)
		}
	}
	return q
}
func (q *ExQuery) Order(order string) *ExQuery {
	if order != "" {
		q.order = " ORDER BY " + order
	}
	return q
}

// 涉及线程安全，改用字符串
func (q *ExQuery) Join(jType string, table string, on string) *ExQuery {
	join := ""
	if q.Joinn != "" {
		join = q.Joinn
	}
	var b strings.Builder
	b.WriteString(join)
	joinStr := ""
	switch jType {
	case "left":
		joinStr = " LEFT JOIN "
	case "right":
		joinStr = " RIGHT JOIN "
	case "inner":
		joinStr = " INNER JOIN "
	}
	b.WriteString(joinStr)
	b.WriteString(q.Prefix)
	b.WriteString(table)
	b.WriteString(" ON ")
	b.WriteString(on)
	b.WriteString(" ")
	q.Joinn = b.String()
	return q
}

func (q *ExQuery) Count() (int, error) {
	defer q.Clearquery()
	q.Field("count(*) as count")
	query := q.SelectSql()
	log.Println(query, q.args)
	rows, err := q.DB.Query(query, q.args...)
	if err != nil {
		log.Println("Query error", err)
	}
	ret := q.GetRow(rows)["count"]
	switch ret.(type) {
	case int64:
		return int(ret.(int64)), nil
	case string:
		res, _ := strconv.Atoi(ret.(string))
		return res, nil
	}
	return 0, nil
}

func (q *ExQuery) Field(field string) *ExQuery {
	if field != "" {
		q.field = field
	}
	return q
}

func (q *ExQuery) SelectSql() string {
	var b strings.Builder
	b.WriteString("SELECT ")
	if q.field == "" {
		b.WriteString("*")
	} else {
		b.WriteString(q.field)
	}
	b.WriteString(" FROM ")
	b.WriteString(q.table)
	b.WriteString(" ")
	b.WriteString(q.alias)
	b.WriteString(" ")
	b.WriteString(q.Joinn)
	b.WriteString(" ")
	// b.WriteString(" WHERE isDel=0")
	if q.Wher != "" {
		b.WriteString(" WHERE ")
		b.WriteString(q.Wher)
		b.WriteString(" ")
	}
	b.WriteString(q.group)
	b.WriteString(" ")
	b.WriteString(q.having)
	b.WriteString(" ")
	b.WriteString(q.order)
	b.WriteString(" ")
	b.WriteString(q.limit)
	return b.String()
}
func (q *ExQuery) Query(sql string, args ...interface{}) ([]map[string]interface{}, error) {
	defer q.Clearquery()
	log.Println(sql, args)
	ctx := context.Background()
	conn, e := q.DB.Conn(ctx)
	if e != nil {
		conn.Close()
		log.Println("Query error", e)
		return nil, e
	}
	rows, err := conn.QueryContext(ctx, sql, args...)
	if err != nil {
		conn.Close()
		log.Println("Query error", err)
		return nil, err
	}
	defer func() {
		conn.Close()
	}()
	return q.GetRows(rows), nil
}

func (q *ExQuery) Exec(sql string, args ...interface{}) (sql.Result, error) {
	defer q.Clearquery()
	log.Println(sql, args)
	ctx := context.Background()
	conn, e := q.DB.Conn(ctx)
	if e != nil {
		conn.Close()
		log.Println("Query error", e)
		return nil, e
	}
	res, err := conn.ExecContext(ctx, sql, args...)
	if err != nil {
		conn.Close()
		log.Println("Query error", err)
		return nil, err
	}
	defer func() {
		conn.Close()
	}()
	return res, nil
}

func (q *ExQuery) Select() ([]map[string]interface{}, error) {
	defer q.Clearquery()
	query := q.SelectSql()
	log.Println(query, q.args)
	ctx := context.Background()
	conn, e := q.DB.Conn(ctx)
	if e != nil {
		conn.Close()
		log.Println("Query error", e)
		return nil, e
	}
	rows, err := conn.QueryContext(ctx, query, q.args...)
	if err != nil {
		conn.Close()
		log.Println("Query error", err)
		return nil, err
	}
	defer func() {
		conn.Close()
	}()
	return q.GetRows(rows), nil
}
func (q *ExQuery) Find() (map[string]interface{}, error) {
	defer q.Clearquery()
	query := q.SelectSql()
	log.Println(query, q.args)
	//使用DB查询
	var build strings.Builder
	build.WriteString(query)
	build.WriteString(" LIMIT 1")
	rows, err := q.DB.Query(build.String(), q.args...)
	if err != nil {
		log.Println("Query error", err)
		return nil, err
	}

	return q.GetRow(rows), nil
}
func (q *ExQuery) Value(key string) (interface{}, error) {
	defer q.Clearquery()
	query := q.SelectSql()
	log.Println(query, q.args)
	//使用DB查询
	var build strings.Builder
	build.WriteString(query)
	build.WriteString(" LIMIT 1")
	rows, err := q.DB.Query(build.String(), q.args...)
	if err != nil {
		log.Println("Query error", err)
		return nil, err
	}
	r := q.GetRow(rows)
	if r != nil {
		return r[key], nil
	}
	return nil, nil
}
func (q *ExQuery) Update(update map[string]interface{}) (sql.Result, error) {
	defer q.Clearquery()
	defer func() {
		if err := recover(); err != nil {
			stack := debug.Stack()
			log.Println(err, string(stack))
		}
	}()
	query, value := buileUpdateParams(q, update, q.args...)
	if query == "" {
		return nil, nil
	}
	fmt.Println(query, update, value, q.args)
	ctx, cancel := context.WithCancel(context.Background())
	conn, e := q.DB.Conn(ctx)
	if e != nil {
		conn.Close()
		cancel()
		log.Println("Query error", e)
		return nil, e
	}
	res, err := conn.ExecContext(ctx, query, value...)
	if err != nil {
		conn.Close()
		cancel()
		log.Println("Query error", err)
		return nil, err
	}
	defer func() {
		conn.Close()
		cancel()
	}()
	return res, nil
}

func (q *ExQuery) Save(save map[string]interface{}) (sql.Result, error) {
	defer q.Clearquery()
	query, value := buileSaveParams(q, save)
	if query == "" {
		return nil, nil
	}
	log.Println(query, save)
	ctx := context.Background()
	conn, e := q.DB.Conn(ctx)
	if e != nil {
		log.Println("Query error", e)
		return nil, e
	}
	res, err := conn.ExecContext(ctx, query, value...)
	if err != nil {
		conn.Close()
		log.Println("Query error", err)
		return nil, err
	}
	conn.Close()
	return res, nil
}

// 在ExQuery结构体新增方法
func (q *ExQuery) Paginate(page int, size int) (*PageResult, error) {
	// 保存当前查询条件
	originalLimit := q.limit
	originaAlias := q.alias
	originalOrder := q.order
	originalField := q.field
	originalTable := q.table
	originalWhere := q.Wher
	originalArgs := append([]interface{}{}, q.args...)
	originalGroup := q.group
	originalHaving := q.having
	originalJoinn := q.Joinn

	// 获取总数
	total, err := q.Count()
	if err != nil {
		return nil, err
	}

	// 恢复查询条件并设置分页
	q.limit = originalLimit
	q.alias = originaAlias
	q.order = originalOrder
	q.field = originalField
	q.table = originalTable
	q.Wher = originalWhere
	q.args = originalArgs
	q.group = originalGroup
	q.having = originalHaving
	q.Joinn = originalJoinn
	if page <= 0 {
		page = 1
	}
	q.Size(page, size)
	data, err := q.Select()
	if err != nil {
		return nil, err
	}

	// 计算总页数
	totalPages := total / size
	if total%size > 0 {
		totalPages++
	}

	return &PageResult{
		Data:        data,
		Total:       total,
		CurrentPage: page,
		PageSize:    size,
		TotalPages:  totalPages,
	}, nil
}
func buileUpdateParams(q *ExQuery, update map[string]interface{}, where ...interface{}) (string, []interface{}) {
	into := ""
	var value []interface{}
	var binto strings.Builder
	var b strings.Builder
	if update != nil {
		i := len(update)
		for key := range update {
			i--
			binto.WriteString(key)
			binto.WriteString(" = ?")
			if len(update) > 0 && i > 0 {
				binto.WriteString(",")
			}
			value = append(value, update[key])
		}
		into = binto.String()
		b.WriteString("UPDATE ")
		b.WriteString(q.table)
		b.WriteString(" SET ")
		b.WriteString(into)
		b.WriteString(" WHERE ")
		b.WriteString(q.Wher)
	}
	if len(where) > 0 {
		for _, v := range where {
			value = append(value, v)
		}
	}
	query := b.String()
	return query, value
}
func buileSaveParams(q *ExQuery, save map[string]interface{}) (string, []interface{}) {
	query, into := "", ""
	var value []interface{}
	var buildPlace strings.Builder
	var buildInto strings.Builder
	var b strings.Builder

	if save != nil {
		buildInto.WriteString(" (")
		buildPlace.WriteString(")VALUES( ")
		i := len(save)
		for key := range save {
			i--
			buildInto.WriteString(key)
			buildPlace.WriteString("?")
			if len(save) > 0 && i > 0 {
				buildInto.WriteString(",")
				buildPlace.WriteString(",")
			}
			value = append(value, save[key])
		}
		place := buildPlace.String()
		into = buildInto.String()
		b.WriteString("INSERT INTO ")
		b.WriteString(q.table)
		b.WriteString(into)
		b.WriteString(place)
		b.WriteString(")")
		query = b.String()
		return query, value
	} else {
		return query, nil
	}
}

// 删除
func buileDeleteParams(q *ExQuery) string {
	var b strings.Builder
	b.WriteString("DELETE FROM ")
	b.WriteString(q.table)
	b.WriteString(" ")
	b.WriteString(q.Wher)
	query := b.String()
	return query
}

// 创建事务
// 注意，不能用q.DB.Conn(ctx) 否则会创建 新的连接并不会释放
func (q *ExQuery) CreateDBTx() (*ExQuery, error) {
	ctx := context.Background()
	q.Ctx = ctx
	// conn, e := q.DB.Conn(ctx)
	// if e == nil {
	opts := new(sql.TxOptions)
	tx, err := q.DB.BeginTx(ctx, opts)
	if err == nil {
		q.Tx = tx
		return q, nil
	} else {
		return nil, err
	}
	// }
}

// 事务写入
func (q *ExQuery) TxSave(save map[string]interface{}) (sql.Result, error) {
	defer q.Clearquery()
	query, value := buileSaveParams(q, save)
	if query == "" {
		return nil, nil
	}
	log.Println(query, value)
	var result sql.Result
	res, err := q.Tx.ExecContext(q.Ctx, query, value...)
	if err != nil {
		log.Println("Query error", err)
		e := q.Tx.Rollback()
		if e != nil {
			log.Println("Query error", e)
		}
	}
	result = res
	return result, err
}

// 事务更新
func (q *ExQuery) TxUpdate(update map[string]interface{}, where ...interface{}) (sql.Result, error) {
	defer q.Clearquery()
	query, value := buileUpdateParams(q, update, q.args...)
	if query == "" {
		return nil, nil
	}
	log.Println(query, value)
	var result sql.Result

	res, err := q.Tx.ExecContext(q.Ctx, query, value...)
	if err != nil {
		log.Println("Query error", err)
		e := q.Tx.Rollback()
		if e != nil {
			log.Println("Query error", e)
		}
	}
	result = res
	return result, err
}

// 事务回滚
func (q *ExQuery) TxRollback() error {
	q.Tx.Rollback()
	q.Tx = nil
	q.Ctx = nil
	return nil
}

// 事务提交
func (q *ExQuery) TxCommit() error {
	q.Tx.Commit()
	q.Tx = nil
	q.Ctx = nil
	return nil
}
func (q *ExQuery) Del() (sql.Result, error) {
	defer q.Clearquery()
	query := buileDeleteParams(q)
	if query == "" {
		return nil, nil
	}
	log.Println(query, q.args)
	ctx := context.Background()
	conn, e := q.DB.Conn(ctx)
	var result sql.Result
	if e == nil {
		res, err := conn.ExecContext(ctx, query, q.args...)
		if err != nil {
			log.Println("Query error", err)
			return nil, err
		}
		result = res

	} else {
		log.Println("Query error", e)
	}
	defer func() {
		conn.Close()
	}()
	return result, nil
}

func (q *ExQuery) Limit(limit string) *ExQuery {
	if limit != "" {
		q.limit = " LIMIT " + limit + ""
	}
	return q
}

func (q *ExQuery) Size(page int, size int) *ExQuery {
	start := (page - 1) * size
	startToStr := strconv.Itoa(start)
	sizeToStr := strconv.Itoa(size)
	var b strings.Builder
	b.WriteString(" LIMIT ")
	b.WriteString(startToStr)
	b.WriteString(",")
	b.WriteString(sizeToStr)
	limitStr := b.String()
	q.limit = limitStr
	return q
}

func (q *ExQuery) Group(group string) *ExQuery {
	if group != "" {
		q.group = " GROUP BY " + group
	}
	return q
}
func (q *ExQuery) Having(h string, v interface{}) *ExQuery {
	if h != "" {
		q.having = " HAVING " + h
		q.args = append(q.args, v)
	}
	return q
}
func (q *ExQuery) GetRows(query *sql.Rows) []map[string]interface{} {
	defer func() {
		query.Close()
	}()
	columns, _ := query.Columns()
	field := make([]interface{}, len(columns))
	//让每一行数据都填充到[][]byte里面
	for i := range field {
		var v interface{}
		field[i] = &v
	}
	var results []map[string]interface{}
	for query.Next() {
		if err := query.Scan(field...); err != nil {
			fmt.Println(err)
		}
		row := make(map[string]interface{})
		for k, val := range field {
			v := *(val.(*interface{}))
			key := columns[k]
			row[key] = assertion(v)
		}
		results = append(results, row)
	}
	return results
}
func (q *ExQuery) GetRow(query *sql.Rows) map[string]interface{} {
	defer func() {
		query.Close()
	}()
	columns, _ := query.Columns()
	field := make([]interface{}, len(columns))
	for i := range field {
		var v interface{}
		field[i] = &v
	}
	row := map[string]interface{}{}
	for query.Next() {
		if err := query.Scan(field...); err != nil {
			fmt.Println(err)
		}
		for k, val := range field {
			v := *(val.(*interface{}))
			key := columns[k]
			row[key] = assertion(v)
		}
		break
	}
	return row
}
func assertion(val interface{}) interface{} {
	var t interface{}
	switch v := val.(type) {
	case int64:
		t = int64(v)
	case int32:
		t = int32(v)
	case int16:
		t = int16(v)
	case int8:
		t = int8(v)
	case int:
		t = int(v)
	case []uint8:
		t = string(v)
	case float32:
		t = float32(v)
	case float64:
		t = float64(v)
	case uint8:
		t = uint8(v)
	case uint16:
		t = uint16(v)
	case uint32:
		t = uint32(v)
	case uint64:
		t = uint64(v)
	case nil:
		t = nil
	default:
		t = v
	}
	return t
}

// 闭包查询
func (q *ExQuery) Fun(f func(query *ExQuery) *ExQuery) *ExQuery {
	return f(q)
}
func (sqlstruct *ExQuery) Clearquery() {
	sqlstruct.Joinn = ""
	sqlstruct.limit = ""
	sqlstruct.alias = ""
	sqlstruct.Wher = ""
	sqlstruct.field = ""
	sqlstruct.group = ""
	sqlstruct.order = ""
	sqlstruct.table = ""
	sqlstruct.having = ""
	sqlstruct.in = []interface{}{}
	sqlstruct.Rows = nil
	sqlstruct.Row = nil
	sqlstruct.args = []interface{}{}
}