package mysql

import (
	"context"
	"database/sql"
	"fmt"
	"regexp"
	"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 TestDurationConversionHelpers(t *testing.T) {
	t.Parallel()

	duration := 3*time.Minute + 5*time.Second
	seconds := durationToSeconds(duration)
	assert.Equal(t, int64(185), seconds)
	assert.Equal(t, duration, secondsToDuration(seconds))
}

func TestScriptTemplateDBToModelConvertsDurationAndPhases(t *testing.T) {
	t.Parallel()

	phases := []model.ScriptPhase{
		&model.ScriptProceedPhase{
			ID:          1,
			Name:        "warmup",
			Description: "prepare for workload",
		},
		&model.ScriptMutatePhase{
			ID:            2,
			Name:          "scale",
			Description:   "scale up threads",
			Message:       "apply extra load",
			StartDuration: 45 * time.Second,
			Mutations: []model.ScriptMutation{{
				Param:   constant.Thread,
				ToValue: "32",
			}},
		},
	}

	phasesJSON, err := encodeScriptPhases(phases)
	require.NoError(t, err)

	dbTemplate := scriptTemplateDB{
		ID:               42,
		CreatorEmail:     sql.NullString{String: "tester@example.com", Valid: true},
		Name:             "load-test",
		Description:      sql.NullString{String: "sample script", Valid: true},
		TotalDurationSec: 120,
		PhasesJSON:       sql.NullString{String: phasesJSON, Valid: true},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	}

	modelTemplate, err := dbTemplate.toModel()
	require.NoError(t, err)

	assert.Equal(t, 120*time.Second, modelTemplate.TotalDuration)
	require.Len(t, modelTemplate.Phases, 2)

	mutatePhase, ok := modelTemplate.Phases[1].(*model.ScriptMutatePhase)
	require.True(t, ok, "second phase should be mutate phase")
	assert.Equal(t, 45*time.Second, mutatePhase.StartDuration)
	assert.Equal(t, "apply extra load", mutatePhase.Message)
	require.Len(t, mutatePhase.Mutations, 1)
	assert.Equal(t, "32", mutatePhase.Mutations[0].ToValue)
}

func TestScriptDBToModelHandlesWorkloadID(t *testing.T) {
	t.Parallel()

	dbScript := scriptDB{
		ID:               7,
		TemplateID:       42,
		WorkloadID:       sql.NullInt64{}, // not set
		CreatorEmail:     sql.NullString{String: "tester@example.com", Valid: true},
		Name:             "load-test-run",
		Description:      sql.NullString{String: "run", Valid: true},
		TotalDurationSec: 300,
		PhasesJSON:       sql.NullString{},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	}

	modelScript, err := dbScript.toModel()
	require.NoError(t, err)

	assert.Equal(t, int64(0), modelScript.WorkloadID, "workload ID should be zero when NULL in DB")
	assert.Equal(t, 300*time.Second, modelScript.TotalDuration)
	assert.Empty(t, modelScript.Phases)
}

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

	ctx := context.Background()
	phases := []model.ScriptPhase{
		&model.ScriptProceedPhase{Name: "prepare"},
	}
	phasesJSON := mustEncodePhases(t, phases)

	tmpl := model.ScriptTemplate{
		CreatorEmail: "creator@example.com",
		ScriptSpec: model.ScriptSpec{
			Name:          "template-A",
			Description:   "desc",
			TotalDuration: 150 * time.Second,
			Phases:        phases,
		},
	}

	mock.ExpectExec(insertTemplateQuery()).
		WithArgs(
			toNullString(tmpl.CreatorEmail),
			tmpl.Name,
			toNullString(tmpl.Description),
			int64(150),
			phasesJSON,
		).
		WillReturnResult(sqlmock.NewResult(10, 1))

	expectTemplateSelect(mock, 10, scriptTemplateDB{
		ID:               10,
		CreatorEmail:     sql.NullString{String: tmpl.CreatorEmail, Valid: true},
		Name:             tmpl.Name,
		Description:      sql.NullString{String: tmpl.Description, Valid: true},
		TotalDurationSec: int64(150),
		PhasesJSON:       sql.NullString{String: phasesJSON, Valid: true},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	})

	created, err := repo.CreateTemplate(ctx, tmpl)
	require.NoError(t, err)
	assert.Equal(t, int64(10), created.ID)
	assert.Equal(t, tmpl.TotalDuration, created.TotalDuration)
	require.Len(t, created.Phases, 1)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	now := time.Now()
	phasesJSON := "[]"
	rows := sqlmock.NewRows([]string{
		"id", "creator_email", "name", "description", "total_duration_sec", "phases", "created_at", "updated_at",
	}).AddRow(1, "creator@example.com", "tmpl1", "desc1", 60, phasesJSON, now, now).
		AddRow(2, "creator@example.com", "tmpl2", "desc2", 120, phasesJSON, now, now)

	mock.ExpectQuery(listTemplatesQuery()).
		WillReturnRows(rows)

	result, err := repo.ListTemplates(context.Background())
	require.NoError(t, err)
	require.Len(t, result, 2)
	assert.Equal(t, 60*time.Second, result[0].TotalDuration)
	assert.Equal(t, 120*time.Second, result[1].TotalDuration)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	ctx := context.Background()
	phases := []model.ScriptPhase{&model.ScriptProceedPhase{Name: "prepare"}}
	phasesJSON := mustEncodePhases(t, phases)
	tmpl := model.ScriptTemplate{
		ID:           11,
		CreatorEmail: "creator@example.com",
		ScriptSpec: model.ScriptSpec{
			Name:          "template-A",
			Description:   "updated",
			TotalDuration: 200 * time.Second,
			Phases:        phases,
		},
	}

	mock.ExpectExec(updateTemplateQuery()).
		WithArgs(
			toNullString(tmpl.CreatorEmail),
			tmpl.Name,
			toNullString(tmpl.Description),
			durationToSeconds(tmpl.TotalDuration),
			phasesJSON,
			tmpl.ID,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	expectTemplateSelect(mock, tmpl.ID, scriptTemplateDB{
		ID:               tmpl.ID,
		CreatorEmail:     sql.NullString{String: tmpl.CreatorEmail, Valid: true},
		Name:             tmpl.Name,
		Description:      sql.NullString{String: tmpl.Description, Valid: true},
		TotalDurationSec: durationToSeconds(tmpl.TotalDuration),
		PhasesJSON:       sql.NullString{String: phasesJSON, Valid: true},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	})

	updated, err := repo.UpdateTemplate(ctx, tmpl)
	require.NoError(t, err)
	assert.Equal(t, tmpl.ID, updated.ID)
	assert.Equal(t, tmpl.TotalDuration, updated.TotalDuration)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	mock.ExpectExec(regexp.QuoteMeta("DELETE FROM script_templates WHERE id = ?")).
		WithArgs(int64(15)).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err := repo.DeleteTemplate(context.Background(), 15)
	require.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	ctx := context.Background()
	phases := []model.ScriptPhase{&model.ScriptProceedPhase{Name: "start"}}
	phasesJSON := mustEncodePhases(t, phases)

	script := model.Script{
		TemplateID:   7,
		CreatorEmail: "runner@example.com",
		ScriptSelector: model.ScriptSelector{
			WorkloadID: 9,
		},
		ScriptSpec: model.ScriptSpec{
			Name:          "run-1",
			Description:   "desc",
			TotalDuration: 300 * time.Second,
			Phases:        phases,
		},
	}

	mock.ExpectExec(insertScriptsQuery()).
		WithArgs(
			script.TemplateID,
			toNullInt64(script.WorkloadID),
			toNullString(script.CreatorEmail),
			script.Name,
			toNullString(script.Description),
			durationToSeconds(script.TotalDuration),
			phasesJSON,
		).
		WillReturnResult(sqlmock.NewResult(20, 1))

	expectScriptSelect(mock, 20, scriptDB{
		ID:               20,
		TemplateID:       script.TemplateID,
		WorkloadID:       sql.NullInt64{Int64: script.WorkloadID, Valid: true},
		CreatorEmail:     sql.NullString{String: script.CreatorEmail, Valid: true},
		Name:             script.Name,
		Description:      sql.NullString{String: script.Description, Valid: true},
		TotalDurationSec: durationToSeconds(script.TotalDuration),
		PhasesJSON:       sql.NullString{String: phasesJSON, Valid: true},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	})

	created, err := repo.CreateScript(ctx, script)
	require.NoError(t, err)
	assert.Equal(t, int64(20), created.ID)
	assert.Equal(t, script.WorkloadID, created.WorkloadID)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	now := time.Now()
	rows := sqlmock.NewRows([]string{
		"id", "template_id", "workload_id", "creator_email", "name", "description", "total_duration_sec", "phases", "created_at", "updated_at",
	}).AddRow(1, 7, 9, "runner@example.com", "script1", "desc", 90, "[]", now, now).
		AddRow(2, 7, nil, "runner@example.com", "script2", "desc2", 120, "[]", now, now)

	mock.ExpectQuery(listScriptsQuery()).
		WillReturnRows(rows)

	result, err := repo.ListScripts(context.Background(), options.NewListScriptOption())
	require.NoError(t, err)
	require.Len(t, result, 2)
	assert.Equal(t, int64(9), result[0].WorkloadID)
	assert.Equal(t, int64(0), result[1].WorkloadID)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	phases := []model.ScriptPhase{&model.ScriptProceedPhase{Name: "start"}}
	phasesJSON := mustEncodePhases(t, phases)

	script := model.Script{
		ID:           30,
		TemplateID:   7,
		CreatorEmail: "runner@example.com",
		ScriptSelector: model.ScriptSelector{
			WorkloadID: 11,
		},
		ScriptSpec: model.ScriptSpec{
			Name:          "script-update",
			Description:   "desc",
			TotalDuration: 400 * time.Second,
			Phases:        phases,
		},
	}

	mock.ExpectExec(updateScriptsQuery()).
		WithArgs(
			script.TemplateID,
			toNullInt64(script.WorkloadID),
			toNullString(script.CreatorEmail),
			script.Name,
			toNullString(script.Description),
			durationToSeconds(script.TotalDuration),
			phasesJSON,
			script.ID,
		).
		WillReturnResult(sqlmock.NewResult(0, 1))

	expectScriptSelect(mock, script.ID, scriptDB{
		ID:               script.ID,
		TemplateID:       script.TemplateID,
		WorkloadID:       sql.NullInt64{Int64: script.WorkloadID, Valid: true},
		CreatorEmail:     sql.NullString{String: script.CreatorEmail, Valid: true},
		Name:             script.Name,
		Description:      sql.NullString{String: script.Description, Valid: true},
		TotalDurationSec: durationToSeconds(script.TotalDuration),
		PhasesJSON:       sql.NullString{String: phasesJSON, Valid: true},
		CreatedAt:        time.Now(),
		UpdatedAt:        time.Now(),
	})

	updated, err := repo.UpdateScript(context.Background(), script)
	require.NoError(t, err)
	assert.Equal(t, script.ID, updated.ID)
	assert.Equal(t, script.TotalDuration, updated.TotalDuration)
	assert.NoError(t, mock.ExpectationsWereMet())
}

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

	mock.ExpectExec(regexp.QuoteMeta("DELETE FROM scripts WHERE id = ?")).
		WithArgs(int64(33)).
		WillReturnResult(sqlmock.NewResult(0, 1))

	err := repo.DeleteScript(context.Background(), 33)
	require.NoError(t, err)
	assert.NoError(t, mock.ExpectationsWereMet())
}

func newScriptRepoWithMock(t *testing.T) (repo.Script, sqlmock.Sqlmock, func()) {
	t.Helper()

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

	sqlxDB := sqlx.NewDb(db, "mysql")
	repo := NewScriptRepo(sqlxDB)

	cleanup := func() {
		_ = sqlxDB.Close()
	}
	return repo, mock, cleanup
}

func mustEncodePhases(t *testing.T, phases []model.ScriptPhase) string {
	t.Helper()

	encoded, err := encodeScriptPhases(phases)
	require.NoError(t, err)
	return encoded
}

func expectTemplateSelect(mock sqlmock.Sqlmock, id int64, tmpl scriptTemplateDB) {
	rows := sqlmock.NewRows([]string{
		"id", "creator_email", "name", "description", "total_duration_sec", "phases", "created_at", "updated_at",
	}).AddRow(
		tmpl.ID,
		tmpl.CreatorEmail.String,
		tmpl.Name,
		tmpl.Description.String,
		tmpl.TotalDurationSec,
		tmpl.PhasesJSON.String,
		tmpl.CreatedAt,
		tmpl.UpdatedAt,
	)

	mock.ExpectQuery(singleTemplateQuery()).
		WithArgs(id).
		WillReturnRows(rows)
}

func expectScriptSelect(mock sqlmock.Sqlmock, id int64, sc scriptDB) {
	rows := sqlmock.NewRows([]string{
		"id", "template_id", "workload_id", "creator_email", "name", "description", "total_duration_sec", "phases", "created_at", "updated_at",
	}).AddRow(
		sc.ID,
		sc.TemplateID,
		nullInt64Interface(sc.WorkloadID),
		sc.CreatorEmail.String,
		sc.Name,
		sc.Description.String,
		sc.TotalDurationSec,
		sc.PhasesJSON.String,
		sc.CreatedAt,
		sc.UpdatedAt,
	)

	mock.ExpectQuery(singleScriptQuery()).
		WithArgs(id).
		WillReturnRows(rows)
}

func nullInt64Interface(value sql.NullInt64) interface{} {
	if value.Valid {
		return value.Int64
	}
	return nil
}

func insertTemplateQuery() string {
	return regexp.QuoteMeta(`
		INSERT INTO script_templates (
			 creator_email, name, description, total_duration_sec, phases
		) VALUES ( ?, ?, ?, ?, ?)`)
}

func updateTemplateQuery() string {
	return regexp.QuoteMeta(`
		UPDATE script_templates SET
			 creator_email = ?, name = ?, description = ?, total_duration_sec = ?, phases = ?
		WHERE id = ?`)
}

func singleTemplateQuery() string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM script_templates WHERE id = ?", scriptTemplateColumns))
}

func listTemplatesQuery() string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM script_templates ORDER BY created_at DESC", scriptTemplateColumns))
}

func insertScriptsQuery() string {
	return regexp.QuoteMeta(`
		INSERT INTO scripts (
			template_id, workload_id, creator_email, name, description, total_duration_sec, phases
		) VALUES (?, ?, ?, ?, ?, ?, ?)`)
}

func updateScriptsQuery() string {
	return regexp.QuoteMeta(`
		UPDATE scripts SET
			template_id = ?, workload_id = ?, creator_email = ?, name = ?, description = ?, total_duration_sec = ?, phases = ?
		WHERE id = ?`)
}

func singleScriptQuery() string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM scripts WHERE id = ?", scriptColumns))
}

func listScriptsQuery() string {
	return regexp.QuoteMeta(fmt.Sprintf("SELECT %s FROM scripts ORDER BY created_at DESC", scriptColumns))
}