package storage

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math"
	"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"
)

// BoxConditions conducts the storage query
type BoxConditions struct {
	BoxType       []string
	Names         []string
	ExpStage      []string
	ExpStatus     []string
	ExpDestroyed  []string
	LimitEveryBox int64
	Limit         int64
	Offset        int64
}

/* ---------------- mysql storage ------------------*/
func (m *mysqlClient) createBox(box *types.Box) error {
	t := time.Now().Format(util.TimeFormat)
	box.Ctime, box.Utime = t, t

	tx, err := m.db.Beginx()
	if err != nil {
		return errors.Trace(err)
	}
	result, err := tx.NamedExec(boxInsert, boxToParams(box))
	if err != nil {
		tx.Rollback()
		return errors.Trace(err)
	}
	box.ID, err = result.LastInsertId()
	if err != nil {
		tx.Rollback()
		return errors.Trace(err)
	}

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

func (m *mysqlClient) updateBox(box *types.Box) error {
	t := time.Now().Format(util.TimeFormat)
	box.Utime = t

	if _, err := m.db.NamedExec(boxUpdate, boxToParams(box)); err != nil {
		return errors.Trace(err)
	}
	return nil
}

func (m *mysqlClient) listBox(conditions *BoxConditions) ([]*types.Box, int64, error) {
	var total int64

	tx, err := m.db.Beginx()
	if err != nil {
		return nil, 0, errors.Trace(err)
	}

	err = tx.Get(&total, genBoxCountStmt(conditions))
	if err != nil {
		tx.Rollback()
		return nil, 0, errors.Trace(err)
	}

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

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

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

	return boxes, total, nil
}

func scanBoxRow(scanner Scanner, dest *types.Box) error {
	dest.Tests = &types.TestsConfig{}
	dest.Misc = &types.MiscConfig{}

	slackForSuccess := sql.NullString{}
	updateNodeJSON := stypes.JSONText{}

	err := scanner.Scan(
		&dest.ID,
		&dest.Name,
		&dest.Creator,
		&dest.Desc,
		&dest.State,
		&dest.Ctime,
		&dest.Utime,
		&dest.ExperimentCount,
		&updateNodeJSON,
		&dest.Tags,
		&dest.WithoutCat,
		&dest.Tests.InOrder,
		&dest.Tests.Tests,
		&dest.Tests.LabelStr,
		&dest.Misc.SlackChannelForError,
		&slackForSuccess,
		&dest.Misc.DestroyTidbCluster,
		&dest.Misc.Timeout,
		&dest.Misc.Type,
		&dest.Cat,
		&dest.Rules,
	)

	json.Unmarshal(updateNodeJSON, &dest.NodeLabelsForUpdate)
	dest.Misc.SlackChannelForSuccess = slackForSuccess.String

	return errors.Trace(err)
}

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

	boxes := []*types.Box{}

	for rows.Next() {
		box := &types.Box{}
		if err := scanBoxRow(rows, box); err != nil {
			return nil, errors.Trace(err)
		}

		boxes = append(boxes, box)
	}

	return boxes, nil
}

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

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

	box := &types.Box{}

	if err := scanBoxRow(row, box); err != nil {
		if strings.Contains(err.Error(), "no rows in result set") {
			return nil, errors.NotFoundf("box id: %d", ID)
		}
		return nil, errors.Trace(err)
	}

	return box, nil
}

func (m *mysqlClient) findBoxByName(name string) (*types.Box, error) {
	query, args, err := m.db.BindNamed(boxSelectByName, map[string]interface{}{"name": name})
	if err != nil {
		return nil, errors.Trace(err)
	}

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

	box := &types.Box{
		Tests: &types.TestsConfig{},
		Misc:  &types.MiscConfig{},
	}

	if err := scanBoxRow(row, box); err != nil {
		if strings.Contains(err.Error(), "no rows in result set") {
			return nil, errors.NotFoundf("box name: %s", name)
		}
		return nil, errors.Trace(err)
	}

	return box, nil
}

func (m *mysqlClient) findBoxByTag(tag string) ([]*types.Box, error) {
	rows, err := m.db.Queryx(boxSelectByTag, fmt.Sprintf("%%%s%%", tag))
	if err != nil {
		return nil, errors.Trace(err)
	}

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

	return boxes, nil
}

func boxToParams(box *types.Box) map[string]interface{} {
	return map[string]interface{}{
		"id":                        box.ID,
		"name":                      box.Name,
		"creator":                   box.Creator,
		"description":               box.Desc,
		"status":                    box.State,
		"create_time":               box.Ctime,
		"update_time":               box.Utime,
		"experiment_count":          box.ExperimentCount,
		"update_node":               box.NodeLabelsForUpdate,
		"tags":                      box.Tags,
		"without_cat":               box.WithoutCat,
		"cat":                       box.Cat,
		"rules":                     box.Rules,
		"test_in_order":             box.Tests.InOrder,
		"test_tests":                box.Tests.Tests,
		"test_labels":               box.Tests.LabelStr,
		"slack_channel_for_error":   box.Misc.SlackChannelForError,
		"slack_channel_for_success": box.Misc.SlackChannelForSuccess,
		"destroy_tidb_cluster":      box.Misc.DestroyTidbCluster,
		"timeout":                   box.Misc.Timeout,
		"box_type":                  box.Misc.Type,
	}
}

const boxSelect = `
SELECT 
 box.id
,box.name
,box.creator
,box.description
,box.status
,box.create_time
,box.update_time
,box.experiment_count
,box.update_node
,box.tags
,box.without_cat
,box.test_in_order
,box.test_tests
,box.test_labels
,box.slack_channel_for_error
,box.slack_channel_for_success
,box.destroy_tidb_cluster
,box.timeout
,box.box_type
,box.cat
,box.rules
FROM box
`

const boxCount = `
SELECT COUNT(*)
FROM box
`

func genBoxSelectStmt(conditions *BoxConditions) string {
	if conditions == nil {
		return boxSelect + `WHERE status != 'DELETED'
ORDER BY create_time DESC
`
	}

	if len(conditions.ExpStage) == 0 && len(conditions.ExpStatus) == 0 && len(conditions.ExpDestroyed) == 0 {
		return boxSelect + "WHERE 1 = 1" +
			genLikeClause("box.name", conditions.Names) +
			genInClause("box.box_type", conditions.BoxType) + ` AND status != 'DELETED'
ORDER BY create_time DESC
` + genLimitOffsetClause(conditions.Limit, conditions.Offset)
	}

	// query boxes according their experiments status
	var limitEveryBox = conditions.LimitEveryBox
	if limitEveryBox == 0 {
		limitEveryBox = math.MaxInt64
	}

	// select boxes that there exist at lease one in it's last `limitEveryBox` experiments which matches the specified stage, state or destroyed
	return boxSelect + `LEFT JOIN (
    SELECT id, box, stage, state, destroyed
    FROM experiment
    WHERE (
        SELECT COUNT(*)
        FROM experiment
        AS f
        WHERE
            f.box = experiment.box
        AND f.create_time >= experiment.create_time) <= ` + fmt.Sprintf("%d", limitEveryBox) + `
) AS experiment
ON box.id = experiment.box
WHERE 1 = 1` + genInClause("experiment.stage", conditions.ExpStage) +
		genInClause("experiment.state", conditions.ExpStatus) +
		genInClause("experiment.destroyed", conditions.ExpDestroyed) +
		genLikeClause("box.name", conditions.Names) +
		genInClause("box.box_type", conditions.BoxType) + `
AND box.status != 'DELETED'
GROUP BY box.id
HAVING (COUNT(experiment.id)) > 0
` + genLimitOffsetClause(conditions.Limit, conditions.Offset)
}

func genBoxCountStmt(conditions *BoxConditions) string {
	if conditions == nil {
		return boxCount + `WHERE status != 'DELETED'
ORDER BY create_time DESC
`
	}
	if len(conditions.ExpDestroyed) == 0 && len(conditions.ExpStatus) == 0 && len(conditions.ExpStage) == 0 {
		return boxCount + "WHERE 1 = 1" +
			genLikeClause("box.name", conditions.Names) +
			genInClause("box.box_type", conditions.BoxType) + ` AND status != 'DELETED'
ORDER BY create_time DESC
`
	}

	// query boxes according their experiments status
	var limitEveryBox = conditions.LimitEveryBox
	if limitEveryBox == 0 {
		limitEveryBox = math.MaxInt64
	}

	return `SELECT COUNT(*)
FROM (
    SELECT box.*
    FROM box
    LEFT JOIN (
        SELECT id, box, stage, state, destroyed
        FROM experiment
        WHERE (
            SELECT COUNT(*) FROM experiment AS f
            WHERE f.box = experiment.box
                AND f.create_time >= experiment.create_time
            ) <= ` + fmt.Sprintf("%d", limitEveryBox) + `
    ) AS experiment
    ON box.id = experiment.box
    WHERE 1 = 1` + genInClause("experiment.stage", conditions.ExpStage) +
		genInClause("experiment.state", conditions.ExpStatus) +
		genInClause("experiment.destroyed", conditions.ExpDestroyed) +
		genLikeClause("box.name", conditions.Names) +
		genInClause("box.box_type", conditions.BoxType) +
		`
    AND box.status != 'DELETED'
    GROUP BY box.id
    HAVING (count(experiment.id)) > 0
) AS tmp`
}

const boxSelectByID = boxSelect + `
WHERE box.id = :id
`

const boxSelectByName = boxSelect + `
WHERE box.name = :name
`

const boxSelectByTag = boxSelect + `
WHERE box.tags like ?
`

const boxInsert = `
INSERT INTO box (
 name
,creator
,description
,status
,create_time
,update_time
,experiment_count
,update_node
,tags
,without_cat
,cat
,rules
,test_in_order
,test_tests
,test_labels
,slack_channel_for_error
,slack_channel_for_success
,destroy_tidb_cluster
,timeout
,box_type
) VALUES (
 :name
,:creator
,:description
,:status
,:create_time
,:update_time
,:experiment_count
,:update_node
,:tags
,:without_cat
,:cat
,:rules
,:test_in_order
,:test_tests
,:test_labels
,:slack_channel_for_error
,:slack_channel_for_success
,:destroy_tidb_cluster
,:timeout
,:box_type
)
`

const boxUpdate = `
UPDATE box SET
 name = :name
,creator = :creator
,description = :description
,status = :status
,update_time = :update_time
,experiment_count = :experiment_count
,update_node = :update_node
,tags = :tags
,without_cat = :without_cat
,cat = :cat
,rules = :rules
,test_in_order = :test_in_order
,test_tests = :test_tests
,test_labels = :test_labels
,slack_channel_for_error = :slack_channel_for_error
,slack_channel_for_success = :slack_channel_for_success
,destroy_tidb_cluster = :destroy_tidb_cluster
,timeout = :timeout
,box_type = :box_type
WHERE id = :id
`