package storage

import (
	"encoding/json"
	"fmt"
	"math"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
	stypes "github.com/jmoiron/sqlx/types"
	"github.com/juju/errors"
	"github.com/pingcap/schrodinger/pkg/types"
	"github.com/pingcap/schrodinger/util"
)

const (
	// split every $batchCount experiments a group, and execute queries in batches
	batchCount = 100
)

// ExperimentProcessConditions conducts the storage query
type ExperimentProcessConditions struct {
	Box           []int64
	Stage         []string
	Status        []string
	Destroyed     []string
	Names         []string
	Limit         int64
	Offset        int64
	LimitEveryBox int64
}

/* ---------------- mysql storage ------------------*/

func (m *mysqlClient) createExperimentProcess(ep *types.ExperimentProcess) error {
	t := time.Now().Format(util.TimeFormat)
	ep.Ctime, ep.Utime = t, t

	res, err := m.db.NamedExec(stmtExperimentInsert, experimentToParams(ep))
	if err != nil {
		return errors.Trace(err)
	}

	ep.ID, err = res.LastInsertId()
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

func experimentToParams(ep *types.ExperimentProcess) map[string]interface{} {
	return map[string]interface{}{
		"id":          ep.ID,
		"labels":      encodeJSONParams(ep.Labels),
		"name":        ep.Name,
		"namespace":   ep.Namespace,
		"box":         ep.BoxID,
		"box_name":    ep.BoxName,
		"creator":     ep.Creator,
		"box_index":   ep.BoxIndex,
		"destroyed":   ep.Destroyed,
		"create_time": ep.Ctime,
		"update_time": ep.Utime,
		"start_time":  getDatetime(ep.StartTime),
		"end_time":    getDatetime(ep.EndTime),
		"fail_time":   getDatetime(ep.Ftime),
		"state":       ep.State,
		"stage":       ep.Stage,
		"author":      ep.Author,
		"annotation":  ep.Annotation,
		"cat_count":   ep.CatCount,
		"config":      encodeJSONParams(ep.Config),
		"report":      encodeJSONParams(ep.Report),
	}
}

func getDatetime(src string) interface{} {
	if util.IsEmpty(src) {
		return mysql.NullTime{}
	}

	return src
}

func scanExperimentRows(rows *sqlx.Rows) ([]*types.ExperimentProcess, error) {
	defer rows.Close()

	eps := []*types.ExperimentProcess{}

	for rows.Next() {
		ep := &types.ExperimentProcess{
			Report: &types.ExperimentReport{},
			Meta:   &types.Meta{Labels: make(map[string]string)},
			Config: &types.ExperimentConfig{},
			Tests:  make(map[string]*types.TestProcess),
		}

		if err := scanExperimentRow(rows, ep); err != nil {
			return nil, errors.Trace(err)
		}

		eps = append(eps, ep)
	}

	return eps, nil
}

func scanExperimentRow(scanner Scanner, dest *types.ExperimentProcess) error {
	reportJSON := stypes.JSONText{}
	labelsJSON := stypes.JSONText{}
	configJSON := stypes.JSONText{}
	startTime := mysql.NullTime{}
	endTime := mysql.NullTime{}
	failTime := mysql.NullTime{}
	err := scanner.Scan(
		&dest.ID,
		&labelsJSON,
		&dest.Name,
		&dest.Namespace,
		&dest.BoxID,
		&dest.BoxName,
		&dest.Creator,
		&dest.BoxIndex,
		&dest.Destroyed,
		&dest.Ctime,
		&dest.Utime,
		&startTime,
		&endTime,
		&failTime,
		&dest.State,
		&dest.Stage,
		&dest.Author,
		&dest.Annotation,
		&dest.CatCount,
		&configJSON,
		&reportJSON,
	)

	json.Unmarshal(reportJSON, &dest.Report)
	json.Unmarshal(labelsJSON, &dest.Labels)
	json.Unmarshal(configJSON, &dest.Config)
	if startTime.Valid {
		dest.StartTime = startTime.Time.Format(util.TimeFormat)
	}

	if endTime.Valid {
		dest.EndTime = endTime.Time.Format(util.TimeFormat)
	}

	if failTime.Valid {
		dest.Ftime = failTime.Time.Format(util.TimeFormat)
	}

	return errors.Trace(err)
}

func (m *mysqlClient) updateExperimentProcess(ep *types.ExperimentProcess) error {
	ep.Utime = time.Now().Format(util.TimeFormat)

	_, err := m.db.NamedExec(stmtExperimentUpdate, experimentToParams(ep))
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

func (m *mysqlClient) listExperimentProcess(conditions *ExperimentProcessConditions) ([]*types.ExperimentProcess, int64, error) {
	tx, err := m.db.Beginx()
	if err != nil {
		return nil, 0, errors.Trace(err)
	}

	var total int64
	if err := tx.Get(&total, genExperimentCountStmt(conditions)); err != nil {
		tx.Rollback()
		return nil, 0, errors.Trace(err)
	}

	rows, err := tx.Queryx(genExperimentSelectStmt(conditions))
	if err != nil {
		tx.Rollback()
		return nil, 0, errors.Trace(err)
	}

	eps, err := scanExperimentRows(rows)
	if err != nil {
		tx.Rollback()
		return nil, 0, errors.Trace(err)
	}

	// decrease query times by splitting experiments into groups
	experimentGroups := splitExperimentsByBatch(eps)
	for _, experimentGroup := range experimentGroups {
		var ids []int64
		var names []string
		idToEpMap := make(map[int64]*types.ExperimentProcess)
		nameToEpMap := make(map[string]*types.ExperimentProcess)

		for _, ep := range experimentGroup {
			ids = append(ids, ep.ID)
			names = append(names, ep.Name)
			idToEpMap[ep.ID] = ep
			nameToEpMap[ep.Name] = ep
		}

		// query test processes in batches
		tps, err := m.findTestProcessByExperiments(ids)
		if err != nil {
			tx.Rollback()
			return nil, 0, errors.Trace(err)
		}
		for _, tp := range tps {
			if ep, ok := idToEpMap[tp.ExperimentID]; ok {
				ep.Tests[tp.Name] = tp
			}
		}

		// query cat processes in batches
		cps, err := m.findCatProcessByExperiments(names)
		if err != nil {
			return nil, 0, errors.Trace(err)
		}
		for _, cp := range cps {
			if ep, ok := nameToEpMap[cp.ExperimentName]; ok && ep.Cat == nil {
				ep.Cat = cp
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return nil, 0, errors.Trace(err)
	}

	return eps, total, nil
}

func (m *mysqlClient) findExperimentProcess(id int64) (*types.ExperimentProcess, error) {
	query, args, err := m.db.BindNamed(stmtExperimentQuery, map[string]interface{}{"id": id})
	if err != nil {
		return nil, errors.Trace(err)
	}

	row := m.db.QueryRowx(query, args...)

	out := &types.ExperimentProcess{
		Report: &types.ExperimentReport{},
		Meta:   &types.Meta{Labels: make(map[string]string)},
		Config: &types.ExperimentConfig{},
		Tests:  make(map[string]*types.TestProcess),
	}

	if err := scanExperimentRow(row, out); err != nil {
		return nil, errors.Trace(err)
	}

	tps, err := m.findTestProcessByExperiment(out.ID, math.MaxInt64, 0)
	if err != nil && !strings.Contains(err.Error(), "no rows") {
		return nil, errors.Trace(err)
	}

	if tps != nil {
		for _, tp := range tps {
			out.Tests[tp.Name] = tp
		}
	}

	cps, err := m.findCatProcessByExperiment(out.Name)
	if err != nil && !strings.Contains(err.Error(), "no rows") {
		return nil, errors.Trace(err)
	}

	if len(cps) != 0 {
		out.Cat = cps[0]
	}

	return out, nil
}

func (m *mysqlClient) deleteExperimentProcess(id int64) error {
	_, err := m.db.NamedExec(stmtExperimentDelete, map[string]interface{}{"id": id})
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

const stmtExperimentInsert = `
INSERT INTO experiment (
 labels
,name
,namespace
,box
,box_name
,creator
,box_index
,destroyed
,create_time
,update_time
,start_time
,end_time
,fail_time
,state
,stage
,author
,annotation
,cat_count
,config
,report
) VALUES (
 :labels
,:name
,:namespace
,:box
,:box_name
,:creator
,:box_index
,:destroyed
,:create_time
,:update_time
,:start_time
,:end_time
,:fail_time
,:state
,:stage
,:author
,:annotation
,:cat_count
,:config
,:report
)
`

const stmtExperimentUpdate = `
UPDATE experiment SET
 labels = :labels
,name = :name
,namespace = :namespace
,box = :box
,box_name = :box_name
,creator = :creator
,box_index = :box_index
,destroyed = :destroyed
,update_time = :update_time
,start_time = :start_time
,end_time = :end_time
,fail_time = :fail_time
,state = :state
,stage = :stage
,author = :author
,annotation = :annotation
,cat_count = :cat_count
,config = :config
,report = :report
WHERE id = :id
`

const stmtExperimentCount = `
SELECT COUNT(*) 
FROM experiment
`

const stmtExperimentQueryBase = `
SELECT 
 id
,labels
,name
,namespace
,box
,box_name
,creator
,box_index
,destroyed
,create_time
,update_time
,start_time
,end_time
,fail_time
,state
,stage
,author
,annotation
,cat_count
,config
,report
FROM experiment
`

const stmtExperimentQuery = stmtExperimentQueryBase + `
WHERE id = :id
`

func genExperimentSelectStmt(conditions *ExperimentProcessConditions) string {
	var experimentSelectTemplate = stmtExperimentQueryBase + `WHERE %s
ORDER BY create_time DESC
`
	var conds = "1 = 1"

	if conditions != nil {
		conds = conds + genInClause("box", intSlice2StringSlice(conditions.Box))
		conds = conds + genInClause("stage", conditions.Stage)
		conds = conds + genInClause("state", conditions.Status)
		conds = conds + genInClause("destroyed", conditions.Destroyed)
		conds = conds + genLikeClause("name", conditions.Names)
		if conditions.LimitEveryBox != 0 {
			conds = conds + fmt.Sprintf(" AND (SELECT COUNT(*) FROM experiment AS f WHERE f.box = experiment.box AND f.create_time >= experiment.create_time) <= %d", conditions.LimitEveryBox)
		}
	}

	result := fmt.Sprintf(experimentSelectTemplate, conds)
	if conditions != nil && conditions.Limit != 0 {
		result = result + fmt.Sprintf("LIMIT %d OFFSET %d\n", conditions.Limit, conditions.Offset)
	}
	return result
}

func genExperimentCountStmt(conditions *ExperimentProcessConditions) string {
	var experimentCountTemplate = stmtExperimentCount + `WHERE %s
ORDER BY create_time DESC
`
	var conds = "1 = 1"

	if conditions != nil {
		conds = conds + genInClause("box", intSlice2StringSlice(conditions.Box))
		conds = conds + genInClause("stage", conditions.Stage)
		conds = conds + genInClause("state", conditions.Status)
		conds = conds + genInClause("destroyed", conditions.Destroyed)
		conds = conds + genLikeClause("name", conditions.Names)

		if conditions.LimitEveryBox != 0 {
			conds = conds + fmt.Sprintf(" AND (SELECT COUNT(*) FROM experiment AS f WHERE f.box = experiment.box AND f.create_time >= experiment.create_time) <= %d", conditions.LimitEveryBox)
		}
	}

	result := fmt.Sprintf(experimentCountTemplate, conds)
	return result
}

const stmtExperimentDelete = `
DELETE FROM experiment
WHERE id = :id
`

// splitExperimentsByBatch split every batchCount experiments in a group
func splitExperimentsByBatch(eps []*types.ExperimentProcess) [][]*types.ExperimentProcess {
	var groups [][]*types.ExperimentProcess
	var group []*types.ExperimentProcess

	for _, ep := range eps {
		group = append(group, ep)
		if len(group) == batchCount {
			groups = append(groups, group)
			group = nil
		}
	}
	if len(group) > 0 {
		groups = append(groups, group)
	}

	return groups
}