package store

import (
	"context"
	"errors"
	"fmt"
	"net/http"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/creasty/defaults"
	"github.com/doug-martin/goqu/v9"
	"github.com/doug-martin/goqu/v9/exp"
	"github.com/jmoiron/sqlx"
	"golang.org/x/sync/errgroup"

	"github.com/pingcap/test-infra/common/model"
	reqM "github.com/pingcap/test-infra/common/model/tcms/request"
	"github.com/pingcap/test-infra/services/tcms/service/common"
	serror "github.com/pingcap/test-infra/services/tcms/service/common/errors"
	"github.com/pingcap/test-infra/services/tcms/service/store/history"
	"github.com/pingcap/test-infra/services/tcms/utils"
	"github.com/pingcap/test-infra/services/tcms/utils/sql"
	"github.com/pingcap/test-infra/services/tcms/utils/timing"
)

type CaseLabelType int8

func (c CaseLabelType) String() string {
	return strconv.Itoa(int(c))
}

// CaseLabel stores the case's components/features/tags.
type CaseLabel struct {
	CaseID model.ID      `db:"case_id"`
	Type   CaseLabelType `db:"type"`
	Name   string        `db:"name"`
}

const (
	// TABLE_CASES_COLUMNS is all the columns of `cases` table.
	TABLE_CASES_COLUMNS = "cases.id,cases.name,cases.deleted_at,cases.summary,cases.preconditions,cases.steps,cases.expected_result,cases.category,cases.priority,cases.supported_versions,cases.created_at,cases.updated_at,cases.designer,cases.automation,cases.framework,cases.parameters,cases.spec"

	// Values of `CaseLabel.Type`
	CASE_LABEL_TYPE_TAG CaseLabelType = 2
)

func (s *caseService) init(db *sql.Database) {
	s.db = db
	s.history = history.NewSimpleHistoryService[model.TestCase](db, "case")
}

type caseService struct {
	db      *sql.Database
	history history.HistoryService[model.TestCase]
}

func (s *caseService) ListBasicInfo(ctx utils.Context, ids []model.ID, names []string, excludeDeleted ...bool) ([]*model.TestCase, error) {
	if len(ids) == 0 && len(names) == 0 {
		return nil, errors.New("expect one of ids and names")
	}

	cases := []*model.TestCase{}
	stmt := strings.Builder{}
	stmt.WriteString("SELECT id, name, category, parameters, created_at, updated_at, deleted_at FROM cases WHERE ")
	if len(ids) != 0 {
		stmt.WriteString(" id IN (:ids)")
	} else if len(names) != 0 {
		stmt.WriteString(" name IN (:names)")
	}
	if len(excludeDeleted) > 0 && excludeDeleted[0] {
		stmt.WriteString(" AND deleted_at=0")
	}
	query, args, _ := sqlx.Named(stmt.String(), map[string]interface{}{
		"ids":   ids,
		"names": names,
	})
	query, args, _ = sqlx.In(query, args...)

	err := s.db.Select(&cases, query, args...)
	if err != nil {
		ctx.Log().Errorf(err, "select cases failed")
		return nil, err
	}

	return cases, nil
}

func (s *caseService) AutoComplete(ctx utils.Context, name string, count int) ([]*model.TestCase, error) {
	cases := []*model.TestCase{}
	err := s.db.Select(&cases, "SELECT id, name, category, created_at, updated_at FROM cases WHERE name LIKE ? AND deleted_at=0 ORDER BY name LIMIT ?",
		sql.SanitizeLike(name)+"%",
		count,
	)
	if err != nil {
		ctx.Log().With("caseName", name).Errorf(err, "AutoCompleteCase failed")
		return nil, err
	}

	return cases, nil
}

func (s *caseService) List(ctx utils.Context, opts common.CaseQueryOpts) ([]*model.TestCase, int, error) {
	c := ctx.Request().Context()
	defer timing.DB(c, "case:list").End()

	// filter out deleted cases
	stmt := sql.Builder.From("cases").Prepared(true)
	if !opts.IncludeDeleted {
		stmt = stmt.Where(goqu.L("cases.deleted_at=0"))
	}
	if len(opts.IDs) > 0 {
		stmt = stmt.Where(goqu.I("cases.id").In(opts.IDs))
	}
	if len(opts.Names) > 0 {
		stmt = stmt.Where(goqu.I("cases.name").In(opts.Names))
	}
	if opts.FromUpdatedAt != nil {
		stmt = stmt.Where(goqu.I("cases.updated_at").Gt(opts.FromUpdatedAt))
	}
	if opts.DesignerEmail != "" {
		stmt = stmt.Where(goqu.I("users.email").Eq(opts.DesignerEmail))
	}

	// filter by tags
	tagCount := len(opts.Tags)
	if tagCount > 0 {
		// Search in lowercase
		lowercaseTags := make([]string, tagCount)
		for i, tag := range opts.Tags {
			lowercaseTags[i] = strings.ToLower(tag)
		}
		// join table `case_labels`
		tagFilter := sql.Builder.
			Select(goqu.COUNT("*").Gte(tagCount)).
			From("case_labels").
			Where(goqu.L("cases.id=case_labels.case_id"), goqu.I("case_labels.lowercase_name").In(lowercaseTags)).
			Prepared(true)
		stmt = stmt.Where(tagFilter)
	}
	stmt = stmt.LeftJoin(goqu.T("users"), goqu.On(goqu.I("users.id").Eq(goqu.I("cases.designer"))))

	g := errgroup.Group{}

	var total int
	stmt_ := stmt
	g.Go(func() (err error) {
		query, args, _ := stmt_.Select(goqu.COUNT("*")).ToSQL()
		if err := s.db.GetContext(c, &total, query, args...); err != nil {
			return err
		}
		return nil
	})

	cases := []*model.TestCase{}
	// The cols that will be returned to client.
	cols := "cases.id,cases.name,cases.summary,cases.designer,cases.automation,cases.created_at,cases.updated_at,cases.deleted_at"
	if opts.AllFields {
		cols = TABLE_CASES_COLUMNS
	}

	// Order by
	orderBy := []exp.OrderedExpression{goqu.I("cases.updated_at").Asc(), goqu.I("cases.id").Asc()}
	if opts.SortedDescending {
		orderBy = []exp.OrderedExpression{goqu.I("cases.updated_at").Desc(), goqu.I("cases.id").Desc()}
	}

	// perform query
	query, args, _ := stmt.Select(
		goqu.L(cols),
		goqu.L("COALESCE(users.email, '') AS designer_email"),
		goqu.L("(SELECT GROUP_CONCAT(case_labels.name SEPARATOR ',') FROM case_labels WHERE case_labels.case_id=cases.id) AS tags"),
	).
		Order(orderBy...).
		Limit(uint(opts.Count)).
		Offset(uint(opts.Offset)).
		ToSQL()
	if err := s.db.SelectContext(c, &cases, query, args...); err != nil {
		ctx.Log().Errorf(err, "list cases failed")
		return nil, 0, err
	}
	if err := g.Wait(); err != nil {
		return nil, 0, err
	}
	return cases, total, nil
}

func (s *caseService) Get(ctx utils.Context, id model.ID, historyOpts *common.HistoryGetOpts) (c *model.TestCase, err error) {
	log := ctx.Log().With("caseID", id)

	if historyOpts != nil {
		// Get from `case_histories` table
		return s.history.Get(ctx.Request().Context(), id, historyOpts)
	}

	// Get directly from `cases` table
	c = &model.TestCase{}
	err = s.db.GetContext(ctx.Request().Context(), c, `
		SELECT
			`+TABLE_CASES_COLUMNS+`,
			COALESCE((SELECT email FROM users WHERE users.id=cases.designer), '') AS designer_email,
			(SELECT GROUP_CONCAT(case_labels.name SEPARATOR ',') FROM case_labels WHERE case_labels.case_id=cases.id) AS tags
		FROM cases WHERE id=?`, id)
	if err != nil {
		if sql.IsErrNoRows(err) {
			return nil, nil
		}
		log.Errorf(err, "GetCase")
		return nil, err
	}
	return c, nil
}

func (s *caseService) ListHistories(ctx utils.Context, id model.ID, opts common.HistoryListOpts) ([]model.HistoryRecord, error) {
	return s.history.List(ctx.Request().Context(), id, opts)
}

func (s *caseService) Create(ctx utils.Context, c *reqM.CaseCreateRequest) (tc *model.TestCase, err error) {
	defer timing.DB(ctx.Request().Context(), "case:create").End()

	now := time.Now()
	if ctx.DryRun() {
		isExist := false
		if err := s.db.GetContext(ctx.Request().Context(), &isExist, "SELECT count(*)>0 FROM cases WHERE name = ? AND deleted_at = 0", c.Name); err != nil {
			return nil, err
		} else if isExist {
			return nil, serror.New(http.StatusBadRequest, fmt.Errorf("case %s is already exists", c.Name))
		}
		return c.IntoTestCase(0, model.CreatedAtAndUpdatedAt{CreatedAt: now, UpdatedAt: now}), nil
	}

	err = s.db.Transaction(ctx.Request().Context(), func(tx *sqlx.Tx) (err error) {
		ids, err := s.BatchCreate(ctx, tx, []*reqM.CaseCreateRequest{c})
		if err != nil {
			return err
		}

		// record history
		tc = c.IntoTestCase(ids[0], model.CreatedAtAndUpdatedAt{
			CreatedAt: now,
			UpdatedAt: now,
		})
		userID := model.ID(0)
		if ctx.User() != nil {
			userID = ctx.User().ID
		}
		_, err = s.history.Create(context.Background(), tc.ID, tc, tx, &common.HistoryCreateOpts{
			User:   userID,
			Action: model.HistoryActionCreate,
			Ts:     &now,
		})
		if err != nil {
			ctx.Log().Errorf(err, "create case history failed")
			return
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return tc, nil
}

func (s *caseService) BatchCreate(ctx utils.Context, tx *sqlx.Tx, cases []*reqM.CaseCreateRequest) (ids []model.ID, err error) {
	log := ctx.Log()
	if len(cases) == 0 {
		return nil, nil
	}

	b := sql.Builder.
		Insert("cases").
		Prepared(true).
		Cols(
			"name",
			"deleted_at",
			"summary",
			"category",
			"preconditions",
			"steps",
			"expected_result",
			"priority",
			"supported_versions",
			"designer",
			"automation",
			"framework",
			"parameters",
			"spec",
		)
	for _, c := range cases {
		defaults.MustSet(c)
		b = b.Vals(goqu.Vals{
			c.Name,
			c.DeletedAt,
			c.Summary,
			c.Category,
			c.Preconditions,
			c.Steps,
			c.ExpectedResult,
			c.Priority,
			c.SupportedVersions,
			c.DesignerID,
			c.Automation,
			c.Framework.Type(),
			c.Parameters,
			c.Framework,
		})
	}
	query, args, _ := b.ToSQL()
	createF := func(tx *sqlx.Tx) error {
		r, err := tx.Exec(query, args...)
		if err != nil {
			if sql.IsErrDuplicate(err) {
				return serror.NewMsg(http.StatusBadRequest, "case name is already exist")
			}
			log.Errorf(err, "CreateCase")
			return err
		}
		firstRowID, err := r.LastInsertId()
		if err != nil {
			log.Errorf(err, "CreateCase get last insert id")
			return err
		}

		// insert case's tags
		cid2tags := make(map[model.ID][]string, len(cases))
		for i, c := range cases {
			id := model.ID(firstRowID + int64(i))
			ids = append(ids, id)
			if len(c.Tags) == 0 {
				continue
			}
			cid2tags[id] = c.Tags
		}
		if err := s.createTags(tx, cid2tags); err != nil {
			log.Errorf(err, "CreateCase insert labels")
			return err
		}
		return nil
	}
	if tx == nil {
		err = s.db.Transaction(ctx.Request().Context(), createF)
	} else {
		err = createF(tx)
	}
	return ids, err
}

func (s *caseService) Update(ctx utils.Context, id model.ID, c *reqM.CaseUpdateRequest) (*model.TestCase, error) {
	log := ctx.Log().With("caseID", id, "caseName", c.Name)
	defer timing.DB(ctx.Request().Context(), "case:update").End()

	now := time.Now()
	var oldCase struct {
		CreatedAt time.Time       `db:"created_at"`
		DeletedAt int64           `db:"deleted_at"`
		Tags      model.StringCSV `db:"tags"`
	}
	var needUpdateTags bool
	getOldCaseF := func() (tc *model.TestCase, err error) {
		// get case
		if err := s.db.Get(&oldCase, `
		SELECT 
			created_at,
			deleted_at, 
			(SELECT GROUP_CONCAT(case_labels.name SEPARATOR ',') FROM case_labels WHERE case_labels.case_id=cases.id ORDER BY case_labels.name ASC FOR UPDATE) AS tags 
		FROM cases WHERE id=? FOR UPDATE`,
			id,
		); err != nil {
			if sql.IsErrNoRows(err) {
				return nil, serror.New(http.StatusNotFound, fmt.Errorf("case not found: #%d", id))
			}
			return nil, err
		} else if oldCase.DeletedAt > 0 {
			return nil, serror.New(http.StatusNotFound, fmt.Errorf("case #%d has been deleted", id))
		}
		tc = (*reqM.CaseCreateRequest)(c).IntoTestCase(id, model.CreatedAtAndUpdatedAt{
			CreatedAt: oldCase.CreatedAt,
			UpdatedAt: now,
		})
		return tc, nil
	}
	if ctx.DryRun() {
		return getOldCaseF()
	}

	var tc *model.TestCase
	err := s.db.Transaction(ctx.Request().Context(), func(tx *sqlx.Tx) (err error) {
		// get case for update, we need to lock the row
		tc, err = getOldCaseF()
		if err != nil {
			return err
		}
		sort.Strings(c.Tags)
		needUpdateTags = !utils.SlicesEqual(oldCase.Tags, c.Tags)

		// update case
		r, err := tx.Exec(`UPDATE cases SET 
			name=?, 
			summary=?, 
			category=?,
			preconditions=?,
			steps=?, 
			expected_result=?, 
			priority=?, 
			supported_versions=?, 
			designer=?, 
			automation=?, 
			framework=?, 
			parameters=?, 
			spec=?
		WHERE id=?`,
			c.Name,
			c.Summary,
			c.Category,
			c.Preconditions,
			c.Steps,
			c.ExpectedResult,
			c.Priority,
			c.SupportedVersions,
			c.DesignerID,
			c.Automation,
			c.Framework.Type(),
			c.Parameters,
			c.Framework,
			id,
		)
		if err != nil {
			log.Errorf(err, "UpdateCase failed")
			return err
		}
		rowsAffected, err := r.RowsAffected()
		if err != nil {
			return err
		}

		// update case's tags
		if needUpdateTags {
			_, err = tx.Exec(`DELETE FROM case_labels WHERE case_id=?`, id)
			if err != nil {
				log.Errorf(err, "UpdateCase delete old case's labels failed")
				return err
			}

			err = s.createTags(tx, map[model.ID][]string{id: c.Tags})
			if err != nil {
				log.Errorf(err, "UpdateCase insert labels")
				return err
			}
		}

		// record history
		isUpdated := rowsAffected > 0 || needUpdateTags
		userID := model.ID(0)
		if ctx.User() != nil {
			userID = ctx.User().ID
		}
		if isUpdated {
			_, err := s.history.Create(context.Background(), tc.ID, tc, tx, &common.HistoryCreateOpts{
				Ts:     &now,
				User:   userID,
				Action: model.HistoryActionUpdate,
			})
			if err != nil {
				ctx.Log().Errorf(err, "create case history failed")
				return err
			}
		}
		return nil
	})
	if err != nil {
		return nil, err
	}
	return tc, nil
}

func (s *caseService) Delete(ctx utils.Context, id model.ID, hard ...bool) error {
	log := ctx.Log().With("caseID", id)
	if id == 0 {
		return nil
	}

	c, err := s.Get(ctx, id, nil)
	if err != nil {
		return err
	} else if c == nil {
		return serror.NewMsg(http.StatusNotFound, "case #%d not found", id)
	}
	now := time.Now()
	c.DeletedAt = now.Unix()
	var revision model.ID
	err = s.db.Transaction(context.Background(), func(tx *sqlx.Tx) (err error) {
		if len(hard) > 0 && hard[0] {
			// hard delete
			_, err := tx.Exec("DELETE FROM cases WHERE id = ?", id)
			if err != nil {
				log.Errorf(err, "hard delete cases failed")
				return err
			}
		} else {
			// soft delete
			_, err := tx.Exec("UPDATE cases SET deleted_at=? WHERE id = ?", c.DeletedAt, id)
			if err != nil {
				log.Errorf(err, "soft delete cases failed")
				return err
			}
		}

		// delete case's labels
		_, err = tx.Exec("DELETE FROM case_labels WHERE case_id = ?", id)
		if err != nil {
			log.Errorf(err, "delete case labels failed")
			return err
		}

		// record history
		userID := model.ID(0)
		if ctx.User() != nil {
			userID = ctx.User().ID
		}
		revision, err = s.history.Create(context.Background(), c.ID, c, tx, &common.HistoryCreateOpts{
			User:   userID,
			Action: model.HistoryActionDelete,
			Ts:     &now,
		})
		return err
	})
	if err != nil {
		return err
	}
	log.Infof("deleted case #%d, revision= %d", id, revision)
	return nil
}

func (s *caseService) createTags(tx *sqlx.Tx, caseID2Tags map[model.ID][]string) error {
	isEmptyTags := true
	b := sql.Builder.Insert("case_labels").Cols("case_id", "type", "name", "lowercase_name").Prepared(true)
	for cid, tags := range caseID2Tags {
		for _, tag := range tags {
			if isEmptyTags {
				isEmptyTags = false
			}
			// NOTE: The type of label is not used now, because we only support one type of label (CASE_LABEL_TYPE_TAG).
			b = b.Vals(goqu.Vals{cid, CASE_LABEL_TYPE_TAG, tag, strings.ToLower(tag)})
		}
	}
	if isEmptyTags {
		return nil
	}
	query, args, _ := b.ToSQL()
	_, err := tx.Exec(query, args...)
	return err
}