package mysql

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"regexp"
	"strings"
	"testing"
	"time"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/jmoiron/sqlx"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

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

func TestScriptExecRepo_ListFiltersByTestIDAndLoadsPhases(t *testing.T) {
	repo, mock, cleanup := newScriptExecRepoWithMock(t)
	defer cleanup()

	now := time.Now().UTC()
	started := now.Add(-time.Minute)
	ctx := context.Background()

	records := []scriptExecDB{
		{
			ID:               "exec-1",
			TestID:           "test-1",
			ScriptID:         7,
			Name:             "exec-one",
			Description:      sql.NullString{String: "desc1", Valid: true},
			TotalDurationSec: 120,
			Status:           string(model.ExecStatusRunning),
			CreatedAt:        now,
			UpdatedAt:        now,
			StartedAt:        &started,
			FinishedAt:       nil,
			Message:          sql.NullString{String: "running", Valid: true},
			CreatorEmail:     sql.NullString{String: "owner@example.com", Valid: true},
		},
		{
			ID:               "exec-2",
			TestID:           "test-1",
			ScriptID:         8,
			Name:             "exec-two",
			TotalDurationSec: 240,
			Status:           string(model.ExecStatusPending),
			CreatedAt:        now,
			UpdatedAt:        now,
			CreatorEmail:     sql.NullString{String: "runner@example.com", Valid: true},
		},
	}

	mock.ExpectQuery(scriptExecListQuery("WHERE test_id = ?")).
		WithArgs("test-1").
		WillReturnRows(scriptExecRows(records...))

	phaseRecords := []scriptExecPhaseDB{
		{
			ID:               1,
			ScriptExecID:     "exec-1",
			Name:             "prepare",
			Type:             "proceed",
			Status:           "completed",
			Message:          sql.NullString{String: "done", Valid: true},
			StartDurationSec: 0,
		},
		{
			ID:               2,
			ScriptExecID:     "exec-1",
			Name:             "scale",
			Type:             "mutate",
			Status:           "running",
			Message:          sql.NullString{String: "scaling", Valid: true},
			StartDurationSec: 30,
			DetailJSON: mustMarshalPhaseDetail(t, []model.ScriptMutation{{
				Param:   constant.WorkloadParamName("threads"),
				ToValue: "32",
			}}),
		},
		{
			ID:               3,
			ScriptExecID:     "exec-2",
			Name:             "warmup",
			Type:             "proceed",
			Status:           "pending",
			StartDurationSec: 0,
		},
	}

	mock.ExpectQuery(scriptExecPhasesQuery(2)).
		WithArgs("exec-1", "exec-2").
		WillReturnRows(scriptExecPhaseRows(phaseRecords...))

	opts := options.NewListScriptExecOption().
		AddFilter(options.ColumnOption{Column: "test_id", Value: "test-1"})

	result, err := repo.List(ctx, opts)
	require.NoError(t, err)
	require.Len(t, result, 2)

	first := result[0]
	assert.Equal(t, "exec-1", first.ID)
	assert.Equal(t, model.ExecStatus("running"), first.Status)
	assert.NotNil(t, first.StartedAt)
	assert.NotNil(t, first.UpdatedAt)
	require.Len(t, first.Phases, 2)
	mPhase, ok := first.Phases[1].(*model.ScriptMutationPhaseExec)
	require.True(t, ok)
	assert.Equal(t, 30*time.Second, mPhase.StartDuration)
	require.Len(t, mPhase.Mutations, 1)
	assert.Equal(t, "32", mPhase.Mutations[0].ToValue)

	second := result[1]
	assert.Equal(t, "exec-2", second.ID)
	require.Len(t, second.Phases, 1)
	assert.Equal(t, "warmup", second.Phases[0].GetName())

	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestScriptExecRepo_GetByID(t *testing.T) {
	repo, mock, cleanup := newScriptExecRepoWithMock(t)
	defer cleanup()

	now := time.Now().UTC()
	started := now.Add(-2 * time.Minute)
	finished := now.Add(-time.Minute)

	dbRecord := scriptExecDB{
		ID:               "exec-55",
		TestID:           "test-55",
		ScriptID:         11,
		Name:             "exec-single",
		Description:      sql.NullString{String: "single", Valid: true},
		TotalDurationSec: 600,
		Status:           string(model.ExecStatusCompleted),
		CreatedAt:        now,
		UpdatedAt:        now,
		StartedAt:        &started,
		FinishedAt:       &finished,
		Message:          sql.NullString{String: "done", Valid: true},
		CreatorEmail:     sql.NullString{String: "runner@example.com", Valid: true},
	}

	phaseRecords := []scriptExecPhaseDB{
		{
			ID:               7,
			ScriptExecID:     dbRecord.ID,
			Name:             "warmup",
			Type:             "proceed",
			Status:           "completed",
			StartDurationSec: 0,
		},
		{
			ID:               8,
			ScriptExecID:     dbRecord.ID,
			Name:             "traffic",
			Type:             "mutate",
			Status:           "completed",
			Message:          sql.NullString{String: "traffic", Valid: true},
			StartDurationSec: 120,
			DetailJSON: mustMarshalPhaseDetail(t, []model.ScriptMutation{{
				Param:   constant.WorkloadParamName("qps"),
				ToValue: "2400",
			}}),
		},
	}

	expectScriptExecSelect(mock, dbRecord)
	mock.ExpectQuery(scriptExecPhasesQuery(1)).
		WithArgs(dbRecord.ID).
		WillReturnRows(scriptExecPhaseRows(phaseRecords...))

	result, err := repo.GetByID(context.Background(), dbRecord.ID)
	require.NoError(t, err)
	assert.Equal(t, dbRecord.ID, result.ID)
	assert.Equal(t, dbRecord.TestID, result.TestID)
	assert.Equal(t, model.ExecStatus("completed"), result.Status)
	assert.NotNil(t, result.FinishedAt)
	require.Len(t, result.Phases, 2)
	assert.Equal(t, "traffic", result.Phases[1].GetName())

	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestScriptExecRepo_CreatePersistsExecAndPhases(t *testing.T) {
	repo, mock, cleanup := newScriptExecRepoWithMock(t)
	defer cleanup()

	ctx := context.Background()
	now := time.Now().UTC()
	started := now.Add(-30 * time.Second)

	phaseProceed := &model.ScriptProceedPhaseExec{
		ScriptPhaseExecBase: model.ScriptPhaseExecBase{
			Name:    "prepare",
			Type:    "proceed",
			Status:  "running",
			Message: "prep",
		},
	}
	phaseMutate := &model.ScriptMutationPhaseExec{
		ScriptPhaseExecBase: model.ScriptPhaseExecBase{
			Name:          "scale",
			Type:          "mutate",
			Status:        "pending",
			Message:       "scale",
			StartDuration: 45 * time.Second,
		},
		Mutations: []model.ScriptMutation{{
			Param:   constant.WorkloadParamName("threads"),
			ToValue: "64",
		}},
	}

	exec := model.ScriptExec{
		ID:            "exec-create",
		TestID:        "test-create",
		ScriptID:      99,
		Name:          "creation",
		Description:   "desc",
		CreatorEmail:  "owner@example.com",
		TotalDuration: 2 * time.Minute,
		ExecBase: model.ExecBase{
			Status:    model.ExecStatusRunning,
			Message:   "running",
			StartedAt: &started,
		},
		Phases: []model.ScriptPhaseExec{phaseProceed, phaseMutate},
	}

	mock.ExpectBegin()
	mock.ExpectExec(insertScriptExecQuery()).
		WithArgs(
			exec.ID,
			exec.TestID,
			exec.ScriptID,
			exec.Name,
			toNullString(exec.Description),
			durationToSeconds(exec.TotalDuration),
			string(exec.Status),
			exec.StartedAt,
			exec.FinishedAt,
			toNullString(exec.Message),
			toNullString(exec.CreatorEmail),
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mock.ExpectExec(insertScriptExecPhaseQuery()).
		WithArgs(
			exec.ID,
			phaseProceed.Name,
			phaseProceed.Type,
			phaseProceed.Status,
			toNullString(phaseProceed.Message),
			int64(0),
			sql.NullString{},
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mutateDetail := mustMarshalPhaseDetail(t, phaseMutate.Mutations)
	mock.ExpectExec(insertScriptExecPhaseQuery()).
		WithArgs(
			exec.ID,
			phaseMutate.Name,
			phaseMutate.Type,
			phaseMutate.Status,
			toNullString(phaseMutate.Message),
			int64(phaseMutate.StartDuration/time.Second),
			mutateDetail,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mock.ExpectCommit()

	dbRecord := scriptExecDB{
		ID:               exec.ID,
		TestID:           exec.TestID,
		ScriptID:         exec.ScriptID,
		Name:             exec.Name,
		Description:      sql.NullString{String: exec.Description, Valid: true},
		TotalDurationSec: durationToSeconds(exec.TotalDuration),
		Status:           string(exec.Status),
		CreatedAt:        now,
		UpdatedAt:        now,
		StartedAt:        exec.StartedAt,
		FinishedAt:       exec.FinishedAt,
		Message:          sql.NullString{String: exec.Message, Valid: true},
		CreatorEmail:     sql.NullString{String: exec.CreatorEmail, Valid: true},
	}
	phaseRows := []scriptExecPhaseDB{
		{
			ID:               1,
			ScriptExecID:     exec.ID,
			Name:             phaseProceed.Name,
			Type:             string(phaseProceed.Type),
			Status:           string(phaseProceed.Status),
			Message:          toNullString(phaseProceed.Message),
			StartDurationSec: 0,
		},
		{
			ID:               2,
			ScriptExecID:     exec.ID,
			Name:             phaseMutate.Name,
			Type:             string(phaseMutate.Type),
			Status:           string(phaseMutate.Status),
			Message:          toNullString(phaseMutate.Message),
			StartDurationSec: int64(phaseMutate.StartDuration / time.Second),
			DetailJSON:       mutateDetail,
		},
	}

	expectScriptExecSelect(mock, dbRecord)
	mock.ExpectQuery(scriptExecPhasesQuery(1)).
		WithArgs(exec.ID).
		WillReturnRows(scriptExecPhaseRows(phaseRows...))

	created, err := repo.Create(ctx, exec)
	require.NoError(t, err)
	assert.Equal(t, exec.ID, created.ID)
	require.Len(t, created.Phases, 2)
	assert.Equal(t, "scale", created.Phases[1].GetName())
	assert.NoError(t, mock.ExpectationsWereMet())
}

func TestScriptExecRepo_UpdateReplacesPhases(t *testing.T) {
	repo, mock, cleanup := newScriptExecRepoWithMock(t)
	defer cleanup()

	ctx := context.Background()
	now := time.Now().UTC()
	started := now.Add(-2 * time.Minute)
	finished := now

	phase := &model.ScriptMutationPhaseExec{
		ScriptPhaseExecBase: model.ScriptPhaseExecBase{
			Name:          "scale",
			Type:          "mutate",
			Status:        "completed",
			Message:       "scaled",
			StartDuration: 20 * time.Second,
		},
		Mutations: []model.ScriptMutation{{
			Param:   constant.WorkloadParamName("qps"),
			ToValue: "1600",
		}},
	}

	exec := model.ScriptExec{
		ID:            "exec-update",
		TestID:        "test-update",
		ScriptID:      77,
		Name:          "update",
		Description:   "updated",
		CreatorEmail:  "runner@example.com",
		TotalDuration: 5 * time.Minute,
		ExecBase: model.ExecBase{
			Status:     model.ExecStatusCompleted,
			Message:    "finished",
			StartedAt:  &started,
			FinishedAt: &finished,
		},
		Phases: []model.ScriptPhaseExec{phase},
	}

	mock.ExpectBegin()
	mock.ExpectExec(updateScriptExecQuery()).
		WithArgs(
			exec.TestID,
			exec.ScriptID,
			exec.Name,
			toNullString(exec.Description),
			durationToSeconds(exec.TotalDuration),
			string(exec.Status),
			exec.StartedAt,
			exec.FinishedAt,
			toNullString(exec.Message),
			toNullString(exec.CreatorEmail),
			exec.ID,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mock.ExpectExec(deleteScriptExecPhasesQuery()).
		WithArgs(exec.ID).
		WillReturnResult(sqlmock.NewResult(0, 1))

	phaseDetail := mustMarshalPhaseDetail(t, phase.Mutations)
	mock.ExpectExec(insertScriptExecPhaseQuery()).
		WithArgs(
			exec.ID,
			phase.Name,
			phase.Type,
			phase.Status,
			toNullString(phase.Message),
			int64(phase.StartDuration/time.Second),
			phaseDetail,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	mock.ExpectCommit()

	dbRecord := scriptExecDB{
		ID:               exec.ID,
		TestID:           exec.TestID,
		ScriptID:         exec.ScriptID,
		Name:             exec.Name,
		Description:      sql.NullString{String: exec.Description, Valid: true},
		TotalDurationSec: durationToSeconds(exec.TotalDuration),
		Status:           string(exec.Status),
		CreatedAt:        now,
		UpdatedAt:        now,
		StartedAt:        exec.StartedAt,
		FinishedAt:       exec.FinishedAt,
		Message:          sql.NullString{String: exec.Message, Valid: true},
		CreatorEmail:     sql.NullString{String: exec.CreatorEmail, Valid: true},
	}
	phaseDB := scriptExecPhaseDB{
		ID:               5,
		ScriptExecID:     exec.ID,
		Name:             phase.Name,
		Type:             string(phase.Type),
		Status:           string(phase.Status),
		Message:          toNullString(phase.Message),
		StartDurationSec: int64(phase.StartDuration / time.Second),
		DetailJSON:       phaseDetail,
	}

	expectScriptExecSelect(mock, dbRecord)
	mock.ExpectQuery(scriptExecPhasesQuery(1)).
		WithArgs(exec.ID).
		WillReturnRows(scriptExecPhaseRows(phaseDB))

	updated, err := repo.Update(ctx, exec)
	require.NoError(t, err)
	assert.Equal(t, exec.ID, updated.ID)
	require.Len(t, updated.Phases, 1)
	assert.Equal(t, "scale", updated.Phases[0].GetName())
	assert.NoError(t, mock.ExpectationsWereMet())
}

func newScriptExecRepoWithMock(t *testing.T) (repo.ScriptExec, sqlmock.Sqlmock, func()) {
	t.Helper()

	db, mock, err := sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherRegexp))
	require.NoError(t, err)

	sqlxDB := sqlx.NewDb(db, "mysql")
	repo := NewScriptExecRepo(sqlxDB)
	cleanup := func() {
		_ = sqlxDB.Close()
	}
	return repo, mock, cleanup
}

func scriptExecRows(records ...scriptExecDB) *sqlmock.Rows {
	rows := sqlmock.NewRows([]string{
		"id", "test_id", "script_id", "name", "description", "total_duration_sec", "status",
		"created_at", "updated_at", "started_at", "finished_at", "message", "creator_email",
	})
	for _, record := range records {
		rows.AddRow(
			record.ID,
			record.TestID,
			record.ScriptID,
			record.Name,
			nullStringInterface(record.Description),
			record.TotalDurationSec,
			record.Status,
			record.CreatedAt,
			record.UpdatedAt,
			record.StartedAt,
			record.FinishedAt,
			nullStringInterface(record.Message),
			nullStringInterface(record.CreatorEmail),
		)
	}
	return rows
}

func scriptExecPhaseRows(phases ...scriptExecPhaseDB) *sqlmock.Rows {
	rows := sqlmock.NewRows([]string{
		"id", "script_exec_id", "name", "type", "state", "message", "start_duration_sec", "detail",
	})
	for _, phase := range phases {
		rows.AddRow(
			phase.ID,
			phase.ScriptExecID,
			phase.Name,
			phase.Type,
			phase.Status,
			nullStringInterface(phase.Message),
			phase.StartDurationSec,
			nullStringInterface(phase.DetailJSON),
		)
	}
	return rows
}

func expectScriptExecSelect(mock sqlmock.Sqlmock, record scriptExecDB) {
	rows := scriptExecRows(record)
	mock.ExpectQuery(singleScriptExecQuery()).
		WithArgs(record.ID).
		WillReturnRows(rows)
}

func singleScriptExecQuery() string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM script_execs WHERE id = ?", scriptExecColumns))
}

func scriptExecListQuery(condition string) string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM script_execs %s", scriptExecColumns, condition))
}

func scriptExecPhasesQuery(execCount int) string {
	pl := strings.TrimSuffix(strings.Repeat("?,", execCount), ",")
	return regexp.QuoteMeta(fmt.Sprintf(
		"SELECT %s FROM script_exec_phases WHERE script_exec_id IN (%s) ORDER BY id ASC",
		scriptExecPhaseColumns,
		pl,
	))
}

func insertScriptExecQuery() string {
	return regexp.QuoteMeta(`
        INSERT INTO script_execs (
            id, test_id, script_id, name, description, total_duration_sec,
            status, started_at, finished_at, message, creator_email
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
}

func updateScriptExecQuery() string {
	return regexp.QuoteMeta(`
        UPDATE script_execs SET
            test_id = ?, script_id = ?, name = ?, description = ?,
            total_duration_sec = ?, status = ?, started_at = ?, finished_at = ?,
            message = ?, creator_email = ?
        WHERE id = ?`)
}

func insertScriptExecPhaseQuery() string {
	return regexp.QuoteMeta(`
        INSERT INTO script_exec_phases (
            script_exec_id, name, type, state, message, start_duration_sec, detail
        ) VALUES (?, ?, ?, ?, ?, ?, ?)`)
}

func deleteScriptExecPhasesQuery() string {
	return regexp.QuoteMeta("DELETE FROM script_exec_phases WHERE script_exec_id = ?")
}

func nullStringInterface(value sql.NullString) interface{} {
	if value.Valid {
		return value.String
	}
	return nil
}

func mustMarshalPhaseDetail(t *testing.T, detail any) sql.NullString {
	t.Helper()
	data, err := json.Marshal(detail)
	require.NoError(t, err)
	return sql.NullString{String: string(data), Valid: true}
}