package storage

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

	"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"
)

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

func (m *mysqlClient) createTestTemplate(ct *types.TestTemplate) error {
	t := time.Now().Format(util.TimeFormat)
	ct.Ctime, ct.Utime = t, t

	res, err := m.db.NamedExec(stmtTestTemplateInsert, testTemplateToParams(ct))
	if err != nil {
		return errors.Trace(err)
	}

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

	return nil
}

func (m *mysqlClient) updateTestTemplate(ct *types.TestTemplate) error {
	ct.Utime = time.Now().Format(util.TimeFormat)
	_, err := m.db.NamedExec(stmtTestTemplateUpdate, testTemplateToParams(ct))
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

func (m *mysqlClient) loadTestTemplate() ([]*types.TestTemplate, error) {
	return nil, nil
}

func (m *mysqlClient) listTestTemplate(limit, offset int64) ([]*types.TestTemplate, error) {
	params := map[string]interface{}{
		"limit":  limit,
		"offset": offset,
	}

	rows, err := m.db.NamedQuery(stmtTestTemplateList, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	testTemplates, err := scanTestTemplateRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}

	return testTemplates, nil
}

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

	row := m.db.QueryRowx(query, args...)
	if row == nil {
		return nil, errors.NotFoundf("test template %d", id)
	}

	out := new(types.TestTemplate)

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

	return out, nil
}

func (m *mysqlClient) listTestTemplateByName(name string) ([]*types.TestTemplate, error) {
	rows, err := m.db.NamedQuery(stmtTestTemplateQueryByName, map[string]interface{}{"name": name})
	if err != nil {
		return nil, errors.Trace(err)
	}

	testTemplates, err := scanTestTemplateRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}
	return testTemplates, nil
}

func (m *mysqlClient) findTestTemplateByNameAndLatest(name string) (*types.TestTemplate, error) {
	rows, err := m.db.NamedQuery(stmtTestTemplateQueryByName, map[string]interface{}{"name": name})
	if err != nil {
		return nil, errors.Trace(err)
	}
	testTemplates, err := scanTestTemplateRows(rows)

	for _, testTemplate := range testTemplates {
		if testTemplate.Source.Type == types.Latest {
			return testTemplate, nil
		}
	}

	return nil, errors.NotFoundf("test template %s/latest", name)
}

func (m *mysqlClient) findTestTemplateByNameAndPR(name, pr string) (*types.TestTemplate, error) {
	rows, err := m.db.NamedQuery(stmtTestTemplateQueryByName, map[string]interface{}{"name": name})
	if err != nil {
		return nil, errors.Trace(err)
	}
	testTemplates, err := scanTestTemplateRows(rows)

	for _, testTemplate := range testTemplates {
		if testTemplate.Source.Type == types.GitSource &&
			testTemplate.Source.GitValue.GitType == types.GitTypePr &&
			testTemplate.Source.GitValue.Value == pr {
			return testTemplate, nil
		}
	}

	return nil, errors.NotFoundf("test template %s/pr/%s", name, pr)
}

func (m *mysqlClient) findTestTemplateByTag(tag string, limit, offset int64) ([]*types.TestTemplate, error) {
	params := map[string]interface{}{
		"tag":    "%" + tag + "%",
		"limit":  limit,
		"offset": offset,
	}

	rows, err := m.db.NamedQuery(stmtTestTemplateQueryByTag, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	testTemplates, err := scanTestTemplateRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}

	return testTemplates, nil
}

// If name is not empty, deleteTestTemplate will exec the delete operation according to name,
// otherwise according to id
func (m *mysqlClient) deleteTestTemplate(id int64) error {
	_, err := m.db.NamedExec(stmtTestTemplateDeleteByID, map[string]interface{}{
		"id": id,
	})
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

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

	var testTemplates []*types.TestTemplate
	for rows.Next() {
		testTemplate := new(types.TestTemplate)
		if err := scanTestTemplateRow(rows, testTemplate); err != nil {
			return nil, errors.Trace(err)
		}
		testTemplates = append(testTemplates, testTemplate)
	}

	return testTemplates, nil
}

// A Scanner represents an object that can be scanned for values.
type Scanner interface {
	Scan(dest ...interface{}) error
}

func scanTestTemplateRow(scanner Scanner, dest *types.TestTemplate) error {
	resourceJSON := stypes.JSONText{}

	err := scanner.Scan(
		&dest.ID,
		&dest.Name,
		&dest.Creator,
		&dest.Ctime,
		&dest.Utime,
		&dest.Status,
		&dest.Type,
		&dest.Args,
		&dest.ErrorInfo,
		&dest.Manual.Description,
		&dest.Manual.Parameters,
		&dest.Manual.Notes,
		&resourceJSON,
		&dest.Tags,
		&dest.Source.BinaryName,
		&dest.Source.Image,
		&dest.Source.Type,
		&dest.Source.URL,
		&dest.Source.GitValue.GitType,
		&dest.Source.GitRepo,
		&dest.Source.GitValue.Value,
	)

	json.Unmarshal(resourceJSON, &dest.ResourceConfig)
	return errors.Trace(err)
}

// helper function converts the testTemplate to a set of named query parameters.
func testTemplateToParams(ct *types.TestTemplate) map[string]interface{} {
	return map[string]interface{}{
		"id":                 ct.ID,
		"name":               ct.Name,
		"creator":            ct.Creator,
		"create_time":        ct.Ctime,
		"update_time":        ct.Utime,
		"status":             ct.Status,
		"type":               ct.Type,
		"args":               ct.Args,
		"error_info":         ct.ErrorInfo,
		"description":        ct.Manual.Description,
		"parameters":         ct.Manual.Parameters,
		"notes":              ct.Manual.Notes,
		"resource_config":    encodeJSONParams(ct.ResourceConfig),
		"tags":               ct.Tags,
		"source_binary_name": ct.Source.BinaryName,
		"source_image":       ct.Source.Image,
		"source_type":        ct.Source.Type,
		"source_url":         ct.Source.URL,
		"source_git_type":    ct.Source.GitValue.GitType,
		"source_git_repo":    ct.Source.GitRepo,
		"source_git_value":   ct.Source.GitValue.Value,
	}
}

func (m *mysqlClient) createTestProcess(cp *types.TestProcess) error {
	t := time.Now().Format(util.TimeFormat)
	cp.Ctime, cp.Utime = t, t

	res, err := m.db.NamedExec(stmtTestProcessInsert, testProcessToParams(cp))
	if err != nil {
		return errors.Trace(err)
	}

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

	return nil
}

func testProcessToParams(cp *types.TestProcess) map[string]interface{} {
	return map[string]interface{}{
		"id":            cp.ID,
		"name":          cp.Name,
		"labels":        encodeJSONParams(cp.Meta.Labels),
		"box":           cp.BoxID,
		"experiment":    cp.ExperimentID,
		"create_time":   cp.Ctime,
		"update_time":   cp.Utime,
		"report":        encodeJSONParams(cp.Report),
		"test_template": cp.Template.ID,
		"override_args": cp.OverrideArgs,
		"status":        cp.Status,
		"namespace":     cp.Namespace,
		"mark":          cp.Mark,
	}
}

func (m *mysqlClient) updateTestProcess(cp *types.TestProcess) error {
	cp.Utime = time.Now().Format(util.TimeFormat)

	_, err := m.db.NamedExec(stmtTestProcessUpdate, testProcessToParams(cp))
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

func (m *mysqlClient) loadTestProcess() ([]*types.TestProcess, error) {
	return nil, nil
}

func (m *mysqlClient) listTestProcess(limit, offset int64) ([]*types.TestProcess, error) {
	params := map[string]interface{}{
		"limit":  limit,
		"offset": offset,
	}

	rows, err := m.db.NamedQuery(stmtTestProcessList, params)
	if err != nil {
		return nil, errors.Trace(err)
	}
	var tps []*types.TestProcess

	for rows.Next() {
		var testProcess types.TestProcess
		if err := rows.StructScan(&testProcess); err != nil {
			return nil, errors.Trace(err)
		}
		tps = append(tps, &testProcess)
	}

	return tps, nil
}

func (m *mysqlClient) findTestProcess(id int64) (*types.TestProcess, error) {
	var testProcess types.TestProcess

	if err := m.db.Get(&testProcess, stmtTestProcessQuery, id); err != nil {
		return nil, errors.Trace(err)
	}
	return &testProcess, nil
}

func (m *mysqlClient) findTestProcessByExperiments(experimentIDs []int64) ([]*types.TestProcess, error) {
	var exps []string
	for _, experimentID := range experimentIDs {
		exps = append(exps, fmt.Sprintf("%d", experimentID))
	}

	stmtTestProcessQueryByExperiments := fmt.Sprintf("%s AND test_process.experiment IN (%s) ORDER BY test_process.update_time DESC", stmtTestProcessQueryBase, strings.Join(exps, ","))

	rows, err := m.db.Queryx(stmtTestProcessQueryByExperiments)
	if err != nil {
		return nil, errors.Trace(err)
	}

	var tps []*types.TestProcess

	for rows.Next() {
		var testProcess types.TestProcess
		if err := rows.StructScan(&testProcess); err != nil {
			return nil, errors.Trace(err)
		}
		tps = append(tps, &testProcess)
	}

	return tps, nil
}

func (m *mysqlClient) findTestProcessByExperiment(experimentID int64, limit, offset int64) ([]*types.TestProcess, error) {
	params := map[string]interface{}{
		"limit":      limit,
		"offset":     offset,
		"experiment": experimentID,
	}

	rows, err := m.db.NamedQuery(stmtTestProcessQueryByExperiment, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	var tps []*types.TestProcess

	for rows.Next() {
		var testProcess types.TestProcess
		if err := rows.StructScan(&testProcess); err != nil {
			return nil, errors.Trace(err)
		}
		tps = append(tps, &testProcess)
	}

	return tps, nil
}

func (m *mysqlClient) createTestResult(tr *types.TestResult) error {
	t := time.Now().Format(util.TimeFormat)
	tr.Ctime, tr.Utime = t, t

	res, err := m.db.NamedExec(stmtTestResultInsert, testResultToParams(tr))
	if err != nil {
		return errors.Trace(err)
	}

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

	return nil
}

func testResultToParams(tr *types.TestResult) map[string]interface{} {
	return map[string]interface{}{
		"id":               tr.ID,
		"create_time":      tr.Ctime,
		"update_time":      tr.Utime,
		"experiment":       tr.ExperimentID,
		"box":              tr.Box,
		"test_name":        tr.TestName,
		"last_test_result": tr.LastResultID,
		"tag":              tr.Tag,
		"eps":              tr.EPS,
		"qps":              tr.QPS,
		"avgms":            tr.AvgMs,
		"pct_90":           tr.Pct90,
		"pct_99":           tr.Pct99,
		"pct_95":           tr.Pct95,
		"spend":            tr.Spend,
		"qps_diff":         tr.QPSDiff,
		"eps_diff":         tr.EPSDiff,
		"avgms_diff":       tr.AvgMsDiff,
		"pct_90_diff":      tr.Pct90Diff,
		"pct_99_diff":      tr.Pct99Diff,
		"pct_95_diff":      tr.Pct95Diff,
		"query_results":    encodeJSONParams(tr.QueryResults),
		"others":           encodeJSONParams(tr.Others),
	}
}

func (m *mysqlClient) updateTestResult(tr *types.TestResult) error {
	tr.Utime = time.Now().Format(util.TimeFormat)

	_, err := m.db.NamedExec(stmtTestResultUpdate, testResultToParams(tr))
	if err != nil {
		return errors.Trace(err)
	}

	return nil
}

func (m *mysqlClient) loadTestResult() ([]*types.TestResult, error) {
	return nil, nil
}

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

	row := m.db.QueryRowx(query, args...)
	if row == nil {
		return nil, errors.NotFoundf("test result %d", id)
	}

	out := new(types.TestResult)

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

	return out, nil
}

func (m *mysqlClient) findTestResultByBoxAndName(box, name string, limit, offset int64) ([]*types.TestResult, error) {
	params := map[string]interface{}{
		"box":       box,
		"test_name": name,
		"limit":     limit,
		"offset":    offset,
	}

	rows, err := m.db.NamedQuery(stmtTestResultQueryByBoxAndName, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	trs, err := scanTestResultRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}

	return trs, nil
}

func (m *mysqlClient) findTestResultByExperimentAndName(experimentID int64, name string) (*types.TestResult, error) {
	params := map[string]interface{}{
		"experiment": experimentID,
		"test_name":  name,
	}

	query, args, err := m.db.BindNamed(stmtTestResultQueryByExperimentAndName, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	row := m.db.QueryRowx(query, args...)
	if row == nil {
		return nil, errors.NotFoundf("test result experiment ID %d name %s", experimentID, name)
	}

	out := new(types.TestResult)

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

	return out, nil
}

func (m *mysqlClient) findTestResultByExperiment(experimentID int64, limit, offset int64) ([]*types.TestResult, error) {
	params := map[string]interface{}{
		"experiment": experimentID,
		"limit":      limit,
		"offset":     offset,
	}

	rows, err := m.db.NamedQuery(stmtTestResultQueryByExperiment, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	trs, err := scanTestResultRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}

	return trs, nil
}

func (m *mysqlClient) listTestResult(limit, offset int64) ([]*types.TestResult, error) {
	params := map[string]interface{}{
		"limit":  limit,
		"offset": offset,
	}

	rows, err := m.db.NamedQuery(stmtTestResultList, params)
	if err != nil {
		return nil, errors.Trace(err)
	}

	trs, err := scanTestResultRows(rows)
	if err != nil {
		return nil, errors.Trace(err)
	}

	return trs, nil
}

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

	trs := []*types.TestResult{}

	for rows.Next() {
		tr := &types.TestResult{}

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

		trs = append(trs, tr)
	}

	return trs, nil
}

func scanTestResultRow(scanner Scanner, dest *types.TestResult) error {
	queryResultsJSON := stypes.JSONText{}
	otherResultsJSON := stypes.JSONText{}

	err := scanner.Scan(
		&dest.ID,
		&dest.Ctime,
		&dest.Utime,
		&dest.ExperimentID,
		&dest.Box,
		&dest.TestName,
		&dest.LastResultID,
		&dest.Tag,
		&dest.EPS,
		&dest.QPS,
		&dest.AvgMs,
		&dest.Pct90,
		&dest.Pct99,
		&dest.Pct95,
		&dest.Spend,
		&dest.QPSDiff,
		&dest.EPSDiff,
		&dest.AvgMsDiff,
		&dest.Pct90Diff,
		&dest.Pct99Diff,
		&dest.Pct95Diff,
		&queryResultsJSON,
		&otherResultsJSON,
	)

	json.Unmarshal(queryResultsJSON, &dest.QueryResults)
	json.Unmarshal(otherResultsJSON, &dest.Others)
	return errors.Trace(err)
}

const stmtTestTemplateInsert = `
INSERT INTO test_template (
 name
,creator
,create_time
,update_time
,status
,type
,args
,error_info
,description
,parameters
,notes
,resource_config
,tags
,source_binary_name
,source_image
,source_type
,source_url
,source_git_type
,source_git_repo
,source_git_value
) VALUES (
 :name
,:creator
,:create_time
,:update_time
,:status
,:type
,:args
,:error_info
,:description
,:parameters
,:notes
,:resource_config
,:tags
,:source_binary_name
,:source_image
,:source_type
,:source_url
,:source_git_type
,:source_git_repo
,:source_git_value
)
`

const stmtTestTemplateUpdate = `
UPDATE test_template SET
 name = :name
,creator = :creator
,update_time = :update_time
,status = :status
,type = :type
,args = :args
,error_info = :error_info
,description = :description
,parameters = :parameters
,notes = :notes
,resource_config = :resource_config
,tags = :tags
,source_binary_name = :source_binary_name
,source_image = :source_image
,source_type = :source_type
,source_url = :source_url
,source_git_type = :source_git_type
,source_git_repo = :source_git_repo
,source_git_value = :source_git_value
WHERE id = :id
`

const stmtTestTemplateQueryBase = `
SELECT
 id
,name
,creator
,create_time
,update_time
,status
,type
,args
,error_info
,description
,parameters
,notes
,resource_config
,tags
,source_binary_name
,source_image
,source_type
,source_url
,source_git_type
,source_git_repo
,source_git_value
FROM test_template
`

const stmtTestTemplateQuery = stmtTestTemplateQueryBase + `
WHERE id = :id
`
const stmtTestTemplateQueryByName = stmtTestTemplateQueryBase + `
WHERE name = :name
ORDER BY create_time DESC
`

const stmtTestTemplateList = stmtTestTemplateQueryBase + `
ORDER BY update_time DESC
LIMIT :limit OFFSET :offset
`

const stmtTestTemplateDeleteByID = `
DELETE FROM test_template
WHERE id = :id
`

const stmtTestTemplateDeleteByName = `
DELETE FROM test_template
WHERE name = :name
`

const stmtTestTemplateQueryByTag = stmtTestTemplateQueryBase + `
WHERE tags LIKE :tag
ORDER BY update_time DESC
LIMIT :limit OFFSET :offset
`

const stmtTestProcessInsert = `
INSERT INTO test_process (
 name
,labels
,box
,experiment
,create_time
,update_time
,report
,test_template
,override_args
,status
,namespace
,mark
) VALUES (
 :name
,:labels
,:box
,:experiment
,:create_time
,:update_time
,:report
,:test_template
,:override_args
,:status
,:namespace
,:mark
)
`

const stmtTestProcessUpdate = `
UPDATE test_process SET
 name = :name
,labels = :labels
,box = :box
,experiment = :experiment
,override_args = :override_args
,update_time = :update_time
,report = :report
,test_template = :test_template
,status = :status
,namespace = :namespace
,mark = :mark
WHERE id = :id
`

const stmtTestProcessQueryBase = `
SELECT
 test_process.id
,test_process.name
,test_process.labels
,test_process.box "box_id"
,test_process.experiment "experiment_id"
,test_process.override_args
,test_process.create_time
,test_process.update_time
,test_process.report
,test_process.status
,test_process.namespace
,test_process.mark
,template.id "template.id"
,template.name "template.name"
,template.creator "template.creator"
,template.create_time "template.create_time"
,template.update_time "template.update_time"
,template.status "template.status"
,template.type "template.type"
,template.args "template.args"
,template.error_info "template.error_info"
,template.description "template.manual.description"
,template.parameters "template.manual.parameters"
,template.notes "template.manual.notes"
,template.resource_config "template.resource_config"
,template.tags "template.tags"
,template.source_binary_name "template.source.binary_name"
,template.source_image "template.source.image"
,template.source_type "template.source.type"
,template.source_url "template.source.url"
,template.source_git_type "template.source.git_value.git_type"
,template.source_git_repo "template.source.git_repo"
,template.source_git_value "template.source.git_value.value"
FROM test_process
JOIN test_template as template
WHERE template.id = test_process.test_template
`

const stmtTestProcessQuery = stmtTestProcessQueryBase + `
AND test_process.id = ?
`

const stmtTestProcessList = stmtTestProcessQueryBase + `
ORDER BY test_process.update_time DESC
LIMIT :limit OFFSET :offset
`

const stmtTestProcessQueryByExperiment = stmtTestProcessQueryBase + `
AND test_process.experiment = :experiment
ORDER BY test_process.update_time DESC
LIMIT :limit OFFSET :offset
`

const stmtTestResultInsert = `
INSERT INTO test_result ( 
 create_time
,update_time
,experiment
,box
,test_name
,last_test_result
,tag
,eps
,qps
,avgms
,pct_90
,pct_99
,pct_95
,spend
,qps_diff
,eps_diff
,avgms_diff
,pct_90_diff
,pct_99_diff
,pct_95_diff
,query_results
,others
) VALUES (
 :create_time
,:update_time
,:experiment
,:box
,:test_name
,:last_test_result
,:tag
,:eps
,:qps
,:avgms
,:pct_90
,:pct_99
,:pct_95
,:spend
,:qps_diff
,:eps_diff
,:avgms_diff
,:pct_90_diff
,:pct_99_diff
,:pct_95_diff
,:query_results
,:others
)
`
const stmtTestResultUpdate = `
UPDATE test_result SET
 create_time = :create_time
,update_time = :update_time
,experiment = :experiment
,box = :box
,test_name = :test_name
,last_test_result = :last_test_result
,tag = :tag
,eps = :eps
,qps = :qps
,avgms = :avgms
,pct_90 = :pct_90
,pct_99 = :pct_99
,pct_95 = :pct_95
,spend = :spend
,qps_diff = :qps_diff
,eps_diff = :eps_diff
,avgms_diff = :avgms_diff
,pct_90_diff = :pct_90_diff
,pct_99_diff = :pct_99_diff
,pct_95_diff = :pct_95_diff
,query_results = :query_results
,others = others
WHERE id = :id
`
const stmtTestResultQueryBase = `
SELECT
 id
,create_time
,update_time
,experiment
,box
,test_name
,last_test_result
,tag
,eps
,qps
,avgms
,pct_90
,pct_99
,pct_95
,spend
,qps_diff
,eps_diff
,avgms_diff
,pct_90_diff
,pct_99_diff
,pct_95_diff
,query_results
,others
FROM test_result
`

const stmtTestResultQuery = stmtTestResultQueryBase + `
WHERE id = :id
`
const stmtTestResultList = stmtTestResultQueryBase + `
ORDER BY update_time DESC
LIMIT :limit OFFSET :offset
`
const stmtTestResultQueryByBoxAndName = stmtTestResultQueryBase + `
WHERE box = :box AND test_name = :test_name
ORDER BY update_time DESC
LIMIT :limit OFFSET :offset
`
const stmtTestResultQueryByExperimentAndName = stmtTestResultQueryBase + `
WHERE experiment = :experiment AND test_name = :test_name
`
const stmtTestResultQueryByExperiment = stmtTestResultQueryBase + `
WHERE experiment = :experiment
ORDER BY update_time DESC
LIMIT :limit OFFSET :offset
`