package mysql

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"slices"
	"strconv"
	"strings"
	"time"

	"github.com/PingCAP-QE/tiworkload/pkg/log"
	"github.com/jmoiron/sqlx"
	"github.com/tidbcloud/pov-service/internal/common/constant"
	"github.com/tidbcloud/pov-service/internal/common/options"
	"github.com/tidbcloud/pov-service/internal/model"
	"github.com/tidbcloud/pov-service/internal/repo"
)

type scriptRepo struct {
	db *sqlx.DB
}

func NewScriptRepo(db *sqlx.DB) repo.Script {
	return &scriptRepo{
		db: db,
	}
}

type scriptTemplateDB struct {
	ID int64 `db:"id"`
	// Version          int64          `db:"version"`
	CreatorEmail     sql.NullString `db:"creator_email"`
	Name             string         `db:"name"`
	Description      sql.NullString `db:"description"`
	TotalDurationSec int64          `db:"total_duration_sec"`
	PhasesJSON       sql.NullString `db:"phases"`
	CreatedAt        time.Time      `db:"created_at"`
	UpdatedAt        time.Time      `db:"updated_at"`
}

type scriptDB struct {
	ID               int64          `db:"id"`
	TemplateID       int64          `db:"template_id"`
	WorkloadID       sql.NullInt64  `db:"workload_id"`
	ClusterType      string         `db:"cluster_type"`
	CreatorEmail     sql.NullString `db:"creator_email"`
	Name             string         `db:"name"`
	Description      sql.NullString `db:"description"`
	TotalDurationSec int64          `db:"total_duration_sec"`
	PhasesJSON       sql.NullString `db:"phases"`
	CreatedAt        time.Time      `db:"created_at"`
	UpdatedAt        time.Time      `db:"updated_at"`
}

const (
	scriptTemplateColumns = `
		id,  creator_email, name, description, total_duration_sec, phases, created_at, updated_at
	`
	scriptColumns = `
		id, template_id, workload_id, cluster_type, creator_email, name, description, total_duration_sec, phases, created_at, updated_at
	`
)

func (r *scriptRepo) ListTemplates(ctx context.Context) ([]model.ScriptTemplate, error) {
	query := fmt.Sprintf("SELECT %s FROM script_templates ORDER BY created_at DESC", scriptTemplateColumns)

	var templates []scriptTemplateDB
	if err := r.db.SelectContext(ctx, &templates, query); err != nil {
		return nil, fmt.Errorf("failed to list script templates: %w", err)
	}

	result := make([]model.ScriptTemplate, 0, len(templates))
	for _, tmpl := range templates {
		modelTemplate, err := tmpl.toModel()
		if err != nil {
			return nil, fmt.Errorf("failed to convert script template: %w", err)
		}
		result = append(result, modelTemplate)
	}

	return result, nil
}

func (r *scriptRepo) GetTemplate(ctx context.Context, id int64) (model.ScriptTemplate, error) {
	query := fmt.Sprintf("SELECT %s FROM script_templates WHERE id = ?", scriptTemplateColumns)

	var tmpl scriptTemplateDB
	if err := r.db.GetContext(ctx, &tmpl, query, id); err != nil {
		if err == sql.ErrNoRows {
			return model.ScriptTemplate{}, fmt.Errorf("script template %d not found", id)
		}
		return model.ScriptTemplate{}, fmt.Errorf("failed to get script template %d: %w", id, err)
	}

	return tmpl.toModel()
}

func (r *scriptRepo) CreateTemplate(
	ctx context.Context,
	tmpl model.ScriptTemplate) (model.ScriptTemplate, error) {
	phasesJSON, err := encodeScriptPhases(tmpl.Phases)
	if err != nil {
		return model.ScriptTemplate{}, fmt.Errorf("failed to encode script template phases: %w", err)
	}

	query := `
		INSERT INTO script_templates (
			 creator_email, name, description, total_duration_sec, phases
		) VALUES ( ?, ?, ?, ?, ?)`

	result, err := r.db.ExecContext(ctx, query,
		toNullString(tmpl.CreatorEmail),
		tmpl.Name,
		toNullString(tmpl.Description),
		durationToSeconds(tmpl.TotalDuration),
		phasesJSON,
	)
	if err != nil {
		return model.ScriptTemplate{}, fmt.Errorf("failed to insert script template: %w", err)
	}

	id := tmpl.ID
	if id == 0 {
		lastID, err := result.LastInsertId()
		if err != nil {
			return model.ScriptTemplate{}, fmt.Errorf("failed to fetch inserted template id: %w", err)
		}
		id = lastID
	}

	return r.GetTemplate(ctx, id)
}

func (r *scriptRepo) UpdateTemplate(ctx context.Context, tmpl model.ScriptTemplate) (model.ScriptTemplate, error) {
	if tmpl.ID == 0 {
		return model.ScriptTemplate{}, fmt.Errorf("template id is required")
	}

	phasesJSON, err := encodeScriptPhases(tmpl.Phases)
	if err != nil {
		return model.ScriptTemplate{}, fmt.Errorf("failed to encode script template phases: %w", err)
	}

	query := `
		UPDATE script_templates SET
			 creator_email = ?, name = ?, description = ?, total_duration_sec = ?, phases = ?
		WHERE id = ?`

	_, err = r.db.ExecContext(ctx, query,
		toNullString(tmpl.CreatorEmail),
		tmpl.Name,
		toNullString(tmpl.Description),
		durationToSeconds(tmpl.TotalDuration),
		phasesJSON,
		tmpl.ID,
	)
	if err != nil {
		return model.ScriptTemplate{}, fmt.Errorf("failed to update script template %d: %w", tmpl.ID, err)
	}

	return r.GetTemplate(ctx, tmpl.ID)
}

func (r *scriptRepo) DeleteTemplate(ctx context.Context, id int64) error {
	if id == 0 {
		return fmt.Errorf("template id is required")
	}

	query := `DELETE FROM script_templates WHERE id = ?`
	if _, err := r.db.ExecContext(ctx, query, id); err != nil {
		return fmt.Errorf("failed to delete script template %d: %w", id, err)
	}
	return nil
}

func (r *scriptRepo) ListScripts(
	ctx context.Context,
	opts options.ListOption[options.ScriptOptions],
) ([]model.Script, error) {
	condition, args := buildQueries(opts.TableOption)

	query := fmt.Sprintf(
		"SELECT %s FROM scripts %s", scriptColumns, condition)

	var scripts []scriptDB
	if err := r.db.SelectContext(ctx, &scripts, query, args...); err != nil {
		return nil, fmt.Errorf("failed to list scripts: %w", err)
	}

	result := make([]model.Script, 0, len(scripts))
	for _, sc := range scripts {
		modelScript, err := sc.toModel()
		if err != nil {
			return nil, fmt.Errorf("failed to convert script: %w", err)
		}
		result = append(result, modelScript)
	}

	return result, nil
}

func (r *scriptRepo) GetScript(ctx context.Context, id int64) (model.Script, error) {
	query := fmt.Sprintf("SELECT %s FROM scripts WHERE id = ?", scriptColumns)

	var sc scriptDB
	if err := r.db.GetContext(ctx, &sc, query, id); err != nil {
		if err == sql.ErrNoRows {
			return model.Script{}, fmt.Errorf("script %d not found", id)
		}
		return model.Script{}, fmt.Errorf("failed to get script %d: %w", id, err)
	}

	return sc.toModel()
}

func (r *scriptRepo) CreateScript(ctx context.Context, script model.Script) (model.Script, error) {
	if script.TemplateID == 0 {
		return model.Script{}, fmt.Errorf("template id is required")
	}

	phasesJSON, err := encodeScriptPhases(script.Phases)
	if err != nil {
		return model.Script{}, fmt.Errorf("failed to encode script phases: %w", err)
	}

	query := `
		INSERT INTO scripts (
			template_id, workload_id, cluster_type, creator_email, name, description, total_duration_sec, phases
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`

	result, err := r.db.ExecContext(ctx, query,
		script.TemplateID,
		toNullInt64(script.WorkloadID),
		toNullString(string(script.ClusterType)),
		toNullString(script.CreatorEmail),
		script.Name,
		toNullString(script.Description),
		durationToSeconds(script.TotalDuration),
		phasesJSON,
	)
	if err != nil {
		return model.Script{}, fmt.Errorf("failed to insert script: %w", err)
	}

	id := script.ID
	if id == 0 {
		lastID, err := result.LastInsertId()
		if err != nil {
			return model.Script{}, fmt.Errorf("failed to fetch inserted script id: %w", err)
		}
		id = lastID
	}

	return r.GetScript(ctx, id)
}

func (r *scriptRepo) UpdateScript(ctx context.Context, script model.Script) (model.Script, error) {
	if script.ID == 0 {
		return model.Script{}, fmt.Errorf("script id is required")
	}
	if script.TemplateID == 0 {
		return model.Script{}, fmt.Errorf("template id is required")
	}

	phasesJSON, err := encodeScriptPhases(script.Phases)
	if err != nil {
		return model.Script{}, fmt.Errorf("failed to encode script phases: %w", err)
	}

	query := `
		UPDATE scripts SET
			template_id = ?, workload_id = ?, cluster_type = ? , creator_email = ?, name = ?, description = ?, total_duration_sec = ?, phases = ?
		WHERE id = ?`

	_, err = r.db.ExecContext(ctx, query,
		script.TemplateID,
		toNullInt64(script.WorkloadID),
		string(script.ClusterType),
		toNullString(script.CreatorEmail),
		script.Name,
		toNullString(script.Description),
		durationToSeconds(script.TotalDuration),
		phasesJSON,
		script.ID,
	)
	if err != nil {
		return model.Script{}, fmt.Errorf("failed to update script %d: %w", script.ID, err)
	}

	return r.GetScript(ctx, script.ID)
}

func (r *scriptRepo) DeleteScript(ctx context.Context, id int64) error {
	if id == 0 {
		return fmt.Errorf("script id is required")
	}

	query := `DELETE FROM scripts WHERE id = ?`
	if _, err := r.db.ExecContext(ctx, query, id); err != nil {
		return fmt.Errorf("failed to delete script %d: %w", id, err)
	}
	return nil
}

type scriptPhaseRecord struct {
	ID            int64                  `json:"id,omitempty"`
	Type          string                 `json:"type"`
	Name          string                 `json:"name"`
	State         string                 `json:"state"`
	Description   string                 `json:"description,omitempty"`
	Message       string                 `json:"message,omitempty"`
	StartDuration int64                  `json:"startDuration,omitempty"`
	Mutations     []model.ScriptMutation `json:"mutations,omitempty"`
}

func encodeScriptPhases(phases []model.ScriptPhase) (string, error) {
	if len(phases) == 0 {
		return "[]", nil
	}

	calMutateDirect := func() {
		paramMap := make(map[constant.WorkloadParamName]string)

		for i, phase := range phases {
			if phase.GetType() != model.ScriptPhaseTypeMutate {
				continue
			}

			mutatePhase := phase.(*model.ScriptMutatePhase)
			if len(mutatePhase.Mutations) == 0 {
				continue
			}
			for j, mutation := range mutatePhase.Mutations {
				// hard coded direction for inject & rollback
				switch mutation.Param {
				case constant.InjectFailure:
					mutatePhase.Mutations[j].Direction = "down"
				case constant.RollbackFailure:
					mutatePhase.Mutations[j].Direction = "up"
				}

				// otherwise, just calculate direction by comparing param values of the same param
				if !slices.Contains(constant.NumericWorkloadParams, mutation.Param) {
					continue
				}
				thisValue := mutation.ToValue
				thisFloat, err := strconv.ParseFloat(thisValue, 64)
				if err != nil {
					log.Errorf(err, "failed to parse float value %s", thisValue)
					continue
				}

				if lastValue, ok := paramMap[mutation.Param]; ok {
					lastFloat, err := strconv.ParseFloat(lastValue, 64)
					if err != nil {
						log.Errorf(err, "failed to parse float value %s", lastValue)
						continue
					}
					if lastFloat > thisFloat {
						mutatePhase.Mutations[j].Direction = "down"
					} else if lastFloat < thisFloat {
						mutatePhase.Mutations[j].Direction = "up"
					}
				}

				paramMap[mutation.Param] = mutation.ToValue
			}

			phases[i] = mutatePhase
		}
	}
	calMutateDirect()

	calProceedStartDur := func() {
		lastDuration := time.Duration(0)
		for i, phase := range phases {
			if phase.GetStartDuration() != 0 || phase.GetType() == model.ScriptPhaseTypeMutate {
				lastDuration = phase.GetStartDuration()
				continue
			}

			proceed := phase.(*model.ScriptProceedPhase)
			if i == 0 {
				proceed.StartDuration = lastDuration
			} else if i == len(phases)-1 {
				proceed.StartDuration = lastDuration + time.Minute
			} else {
				// find next phase with non-zero start duration
				j := i + 1
				for ; j < len(phases); j++ {
					if phases[j].GetStartDuration() != 0 {
						break
					}
				}

				if j == len(phases) {
					proceed.StartDuration = lastDuration + time.Minute
				} else {
					secondInt := (lastDuration.Seconds() + phases[j].GetStartDuration().Seconds()) / float64(j-i+1)
					proceed.StartDuration = time.Second * time.Duration(secondInt)
					lastDuration = proceed.StartDuration
				}
			}

			phases[i] = proceed
		}
	}
	calProceedStartDur()

	records := make([]scriptPhaseRecord, 0, len(phases))
	for _, phase := range phases {
		record, err := phaseToRecord(phase)
		if err != nil {
			return "", err
		}
		records = append(records, record)
	}

	data, err := json.Marshal(records)
	if err != nil {
		return "", fmt.Errorf("failed to marshal script phases: %w", err)
	}
	return string(data), nil
}

func decodeScriptPhases(data sql.NullString) ([]model.ScriptPhase, error) {
	if !data.Valid || strings.TrimSpace(data.String) == "" {
		return nil, nil
	}

	var records []scriptPhaseRecord
	if err := json.Unmarshal([]byte(data.String), &records); err != nil {
		return nil, fmt.Errorf("failed to unmarshal script phases: %w", err)
	}

	result := make([]model.ScriptPhase, 0, len(records))
	for _, record := range records {
		phase, err := record.toModel()
		if err != nil {
			return nil, err
		}
		result = append(result, phase)
	}
	return result, nil
}

func phaseToRecord(phase model.ScriptPhase) (scriptPhaseRecord, error) {
	switch v := phase.(type) {
	case *model.ScriptMutatePhase:
		return scriptPhaseRecord{
			ID:            v.ID,
			Type:          "mutate",
			Name:          v.Name,
			State:         v.State,
			Description:   v.Description,
			Message:       v.Message,
			StartDuration: int64(v.StartDuration),
			Mutations:     append([]model.ScriptMutation(nil), v.Mutations...),
		}, nil
	case *model.ScriptProceedPhase:
		return scriptPhaseRecord{
			ID:            v.ID,
			Type:          "proceed",
			Name:          v.Name,
			State:         v.State,
			Description:   v.Description,
			Message:       v.Message,
			StartDuration: int64(v.StartDuration),
		}, nil
	default:
		return scriptPhaseRecord{}, fmt.Errorf("unsupported script phase type %T", phase)
	}
}

func (rec scriptPhaseRecord) toModel() (model.ScriptPhase, error) {
	switch strings.ToLower(rec.Type) {
	case "", "proceed":
		return &model.ScriptProceedPhase{
			ID:            rec.ID,
			Name:          rec.Name,
			State:         rec.State,
			Description:   rec.Description,
			Message:       rec.Message,
			StartDuration: time.Duration(rec.StartDuration),
		}, nil
	case "mutate":
		return &model.ScriptMutatePhase{
			ID:            rec.ID,
			Name:          rec.Name,
			State:         rec.State,
			Description:   rec.Description,
			Message:       rec.Message,
			StartDuration: time.Duration(rec.StartDuration),
			Mutations:     append([]model.ScriptMutation(nil), rec.Mutations...),
		}, nil
	default:
		return nil, fmt.Errorf("unsupported script phase type %s", rec.Type)
	}
}

func (tmpl scriptTemplateDB) toModel() (model.ScriptTemplate, error) {
	phases, err := decodeScriptPhases(tmpl.PhasesJSON)
	if err != nil {
		return model.ScriptTemplate{}, err
	}

	spec := model.ScriptSpec{
		Name:          tmpl.Name,
		Description:   tmpl.Description.String,
		TotalDuration: secondsToDuration(tmpl.TotalDurationSec),
		Phases:        phases,
	}

	return model.ScriptTemplate{
		ID: tmpl.ID,
		// Version:      tmpl.Version,
		CreatorEmail: tmpl.CreatorEmail.String,
		CreatedAt:    tmpl.CreatedAt,
		UpdatedAt:    tmpl.UpdatedAt,
		ScriptSpec:   spec,
	}, nil
}

func (sc scriptDB) toModel() (model.Script, error) {
	phases, err := decodeScriptPhases(sc.PhasesJSON)
	if err != nil {
		return model.Script{}, err
	}

	spec := model.ScriptSpec{
		Name:          sc.Name,
		Description:   sc.Description.String,
		TotalDuration: secondsToDuration(sc.TotalDurationSec),
		Phases:        phases,
	}

	script := model.Script{
		ID:           sc.ID,
		TemplateID:   sc.TemplateID,
		CreatorEmail: sc.CreatorEmail.String,
		CreatedAt:    sc.CreatedAt,
		UpdatedAt:    sc.UpdatedAt,
		ScriptSelector: model.ScriptSelector{
			WorkloadID:  sc.WorkloadID.Int64,
			ClusterType: model.ClusterType(sc.ClusterType),
		},
		ScriptSpec: spec,
	}

	if !sc.WorkloadID.Valid {
		script.WorkloadID = 0
	}

	return script, nil
}

func toNullString(value string) sql.NullString {
	if strings.TrimSpace(value) == "" {
		return sql.NullString{}
	}
	return sql.NullString{
		String: value,
		Valid:  true,
	}
}

func toNullInt64(value int64) sql.NullInt64 {
	if value == 0 {
		return sql.NullInt64{}
	}
	return sql.NullInt64{
		Int64: value,
		Valid: true,
	}
}

func durationToSeconds(d time.Duration) int64 {
	return int64(d / time.Second)
}

func secondsToDuration(seconds int64) time.Duration {
	return time.Duration(seconds) * time.Second
}