package sqlm

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

	"github.com/jmoiron/sqlx"
)

// SQLM SQLM
type SQLM struct {
	*sqlx.DB
}

// Conn 连接数据库
func Conn(driverName, dataSourceName string) (sqlm SQLM, err error) {
	sqlm.DB, err = sqlx.Open(driverName, dataSourceName)
	if err != nil {
		err = fmt.Errorf("dsn:%s, err: %s", dataSourceName, err)
		return
	}
	err = sqlm.DB.Ping()
	if err != nil {
		err = fmt.Errorf("dsn:%s, err: %s", dataSourceName, err)
		return
	}
	return
}

// getSQL 获取sql
func (s SQLM) getSQL(pattern string, arg interface{}) (query string, args []interface{}, err error) {
	t := template.New("new template")
	t = t.Funcs(template.FuncMap{"notNil": notNil})
	t, err = t.Parse(pattern)
	if err != nil {
		err = fmt.Errorf("template parse error: %s", err.Error())
		return
	}
	var buf bytes.Buffer
	err = t.Execute(&buf, arg)
	if err != nil {
		err = fmt.Errorf("template execute error: %s", err.Error())
		return
	}
	query, args, err = sqlx.Named(buf.String(), arg)
	if err != nil {
		return
	}
	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return
	}

	query = s.Rebind(query)
	r1 := regexp.MustCompile(`[\s]+`)
	query = r1.ReplaceAllString(query, " ")
	query = strings.TrimSpace(query)
	return
}

// Query 执行一次多行查询
func (s SQLM) Query(pattern string, arg interface{}) (rows *sqlx.Rows, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	rows, err = stmt.Queryx(args...)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
	}
	return
}

// QueryRow 执行一次单行查询
func (s SQLM) QueryRow(pattern string, arg interface{}) (row *sqlx.Row, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	row = stmt.QueryRowx(args...)
	return
}

// Exec 执行一次数据操作
func (s SQLM) Exec(pattern string, arg interface{}) (result sql.Result, err error) {
	query, args, err := s.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := s.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	result, err = stmt.Exec(args...)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
	}
	return
}

// Tx sqlt事务对象
type Tx struct {
	*sqlx.Tx
}

// Begin 开始事务
func (s SQLM) Begin() (tx Tx, err error) {
	tx.Tx, err = s.Beginx()
	if err != nil {
		err = fmt.Errorf("开始事务失败:%v", err)
	}
	return
}

// Rollback 回滚事务
func (tx Tx) Rollback() (err error) {
	err = tx.Tx.Rollback()
	if err != nil {
		err = fmt.Errorf("回滚事务失败:%v", err)
	}
	return
}

// Commit 提交事务
func (tx Tx) Commit() (err error) {
	err = tx.Tx.Commit()
	if err != nil {
		err = fmt.Errorf("回滚事务失败:%v", err)
	}
	return
}

// getSQL 获取sql
func (tx Tx) getSQL(pattern string, arg interface{}) (query string, args []interface{}, err error) {
	t := template.New("new template")
	t, err = t.Parse(pattern)
	if err != nil {
		err = fmt.Errorf("template parse error: %s", err.Error())
		return
	}
	var buf bytes.Buffer
	err = t.Execute(&buf, arg)
	if err != nil {
		err = fmt.Errorf("template execute error: %s", err.Error())
		return
	}
	query, args, err = sqlx.Named(buf.String(), arg)
	if err != nil {
		return
	}
	query, args, err = sqlx.In(query, args...)
	if err != nil {
		return
	}

	query = tx.Rebind(query)
	r1 := regexp.MustCompile(`[\s]+`)
	query = r1.ReplaceAllString(query, " ")
	query = strings.TrimSpace(query)
	return
}

// Query 在事务中执行一次多行查询
func (tx Tx) Query(pattern string, arg interface{}) (rows *sqlx.Rows, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	rows, err = stmt.Queryx(args...)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
	}
	return
}

// QueryRow 在事务中执行一次单行查询
func (tx Tx) QueryRow(pattern string, arg interface{}) (row *sqlx.Row, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, query:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	row = stmt.QueryRowx(args...)
	return
}

// Exec 在事务中执行一次数据操作
func (tx Tx) Exec(pattern string, arg interface{}) (result sql.Result, err error) {
	query, args, err := tx.getSQL(pattern, arg)
	if err != nil {
		return
	}
	stmt, err := tx.Preparex(query)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
		return
	}
	defer stmt.Close()
	result, err = stmt.Exec(args...)
	if err != nil {
		err = fmt.Errorf(`%v, exec:%s, params: %v`, err, query, args)
	}
	return
}

func notNil(t interface{}) bool {
	return t != nil
}