package mysql

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	"time"

	"github.com/jmoiron/sqlx"
	"github.com/tidbcloud/pov-service/internal/common/options"
	"github.com/tidbcloud/pov-service/internal/model"
	"github.com/tidbcloud/pov-service/internal/repo"
)

type scriptExecRepo struct {
	db *sqlx.DB
	TxnProvider
}

func NewScriptExecRepo(db *sqlx.DB) repo.ScriptExec {
	return &scriptExecRepo{db: db,
		TxnProvider: TxnProvider{
			db: db,
		},
	}
}

type scriptExecPhaseDB struct {
	ID               int64          `db:"id"`
	ScriptExecID     string         `db:"script_exec_id"`
	Name             string         `db:"name"`
	Type             string         `db:"type"`
	Status           string         `db:"status"`
	Message          sql.NullString `db:"message"`
	StartDurationSec int64          `db:"start_duration_sec"`
	DetailJSON       sql.NullString `db:"detail"`
}

type scriptExecDB struct {
	ID               string         `db:"id"`
	TestID           string         `db:"test_id"`
	ScriptID         int64          `db:"script_id"`
	Name             string         `db:"name"`
	Description      sql.NullString `db:"description"`
	TotalDurationSec int64          `db:"total_duration_sec"`
	Status           string         `db:"status"`
	CreatedAt        time.Time      `db:"created_at"`
	UpdatedAt        time.Time      `db:"updated_at"`
	StartedAt        *time.Time     `db:"started_at"`
	FinishedAt       *time.Time     `db:"finished_at"`
	Message          sql.NullString `db:"message"`
	CreatorEmail     sql.NullString `db:"creator_email"`
}

const (
	scriptExecColumns = `
        id, test_id, script_id, name, description, total_duration_sec, status,
        created_at, updated_at, started_at, finished_at, message, creator_email
    `
	scriptExecPhaseColumns = `
        id, script_exec_id, name, type, status, message, start_duration_sec, detail
    `
)

func (r *scriptExecRepo) List(
	ctx context.Context,
	opts options.ListOption[options.ScriptExecOptions],
) ([]model.ScriptExec, error) {
	condition, args := buildQueries(opts.TableOption)
	query := fmt.Sprintf("SELECT %s FROM script_execs %s", scriptExecColumns, condition)

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

	return r.bindPhases(ctx, records)
}

func (r *scriptExecRepo) GetByID(ctx context.Context, id string) (model.ScriptExec, error) {
	query := fmt.Sprintf("SELECT %s FROM script_execs WHERE id = ?", scriptExecColumns)

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

	phaseMap, err := r.fetchPhases(ctx, []string{id})
	if err != nil {
		return model.ScriptExec{}, err
	}

	return record.toModel(phaseMap[id])
}

func (r *scriptExecRepo) GetAndLockForUpdateInTx(
	ctx context.Context,
	tx repo.Tx,
	id string,
	opts ...options.TableStatement) (model.ScriptExec, error) {
	mysqlTx, ok := tx.(*mysqlTx)
	if !ok {
		return model.ScriptExec{}, fmt.Errorf("invalid transaction type")
	}
	tblOptions := options.NewTableOption(opts...)
	waitCmd := "NOWAIT"
	if tblOptions.ShouldWait {
		waitCmd = ""
	}

	query := fmt.Sprintf("SELECT %s FROM script_execs WHERE id = ? FOR UPDATE %s", scriptExecColumns, waitCmd)

	var record scriptExecDB
	if err := mysqlTx.tx.GetContext(ctx, &record, query, id); err != nil {
		if err == sql.ErrNoRows {
			return model.ScriptExec{}, fmt.Errorf("script exec %s not found", id)
		}
		return model.ScriptExec{}, fmt.Errorf("failed to get script exec %s: %w", id, err)
	}

	phaseMap, err := r.fetchPhases(ctx, []string{id})
	if err != nil {
		return model.ScriptExec{}, err
	}

	return record.toModel(phaseMap[id])
}

func (r *scriptExecRepo) Create(
	ctx context.Context,
	exec model.ScriptExec) (model.ScriptExec, error) {
	if exec.ID == "" {
		return model.ScriptExec{}, fmt.Errorf("script exec id is required")
	}
	if exec.TestID == "" {
		return model.ScriptExec{}, fmt.Errorf("test id is required")
	}
	if exec.ScriptID == 0 {
		return model.ScriptExec{}, fmt.Errorf("script id is required")
	}

	tx, err := r.db.BeginTxx(ctx, nil)
	if err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	insertExecQuery := `
        INSERT INTO script_execs (
            id, test_id, script_id, name, description, total_duration_sec,
            status, started_at, finished_at, message, creator_email
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`

	if _, err := tx.ExecContext(ctx, insertExecQuery,
		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),
	); err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to insert script exec: %w", err)
	}

	if err := r.insertPhasesInTx(ctx, tx, exec.ID, exec.Phases); err != nil {
		return model.ScriptExec{}, err
	}

	if err := tx.Commit(); err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to commit script exec creation: %w", err)
	}

	return r.GetByID(ctx, exec.ID)
}

func (r *scriptExecRepo) Update(ctx context.Context, exec model.ScriptExec) (model.ScriptExec, error) {
	if exec.ID == "" {
		return model.ScriptExec{}, fmt.Errorf("script exec id is required")
	}

	tx, err := r.db.BeginTxx(ctx, nil)
	if err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer tx.Rollback()

	updateQuery := `
        UPDATE script_execs SET
            test_id = ?, script_id = ?, name = ?, description = ?,
            total_duration_sec = ?, status = ?, started_at = ?, finished_at = ?,
            message = ?, creator_email = ?
        WHERE id = ?`

	if _, err := tx.ExecContext(ctx, updateQuery,
		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,
	); err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to update script exec %s: %w", exec.ID, err)
	}

	if err := r.replacePhasesInTx(ctx, tx, exec.ID, exec.Phases); err != nil {
		return model.ScriptExec{}, err
	}

	if err := tx.Commit(); err != nil {
		return model.ScriptExec{}, fmt.Errorf("failed to commit script exec update: %w", err)
	}

	return r.GetByID(ctx, exec.ID)
}

func (r *scriptExecRepo) UpdateInTx(
	ctx context.Context, tx repo.Tx, exec model.ScriptExec) error {
	if exec.ID == "" {
		return fmt.Errorf("script exec id is required")
	}

	mysqlTx, ok := tx.(*mysqlTx)
	if !ok {
		return fmt.Errorf("invalid transaction type")
	}

	updateQuery := `
        UPDATE script_execs SET
            test_id = ?, script_id = ?, name = ?, description = ?,
            total_duration_sec = ?, status = ?, started_at = ?, finished_at = ?,
            message = ?, creator_email = ?
        WHERE id = ?`

	if _, err := mysqlTx.tx.ExecContext(ctx, updateQuery,
		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,
	); err != nil {
		return fmt.Errorf("failed to update script exec %s: %w", exec.ID, err)
	}

	if err := r.replacePhasesInTx(ctx, mysqlTx.tx, exec.ID, exec.Phases); err != nil {
		return err
	}

	return nil
}

func (r *scriptExecRepo) bindPhases(
	ctx context.Context,
	records []scriptExecDB,
) ([]model.ScriptExec, error) {
	if len(records) == 0 {
		return []model.ScriptExec{}, nil
	}

	ids := make([]string, 0, len(records))
	for _, record := range records {
		ids = append(ids, record.ID)
	}

	phaseMap, err := r.fetchPhases(ctx, ids)
	if err != nil {
		return nil, err
	}

	result := make([]model.ScriptExec, 0, len(records))
	for _, record := range records {
		phases := phaseMap[record.ID]
		exec, err := record.toModel(phases)
		if err != nil {
			return nil, err
		}
		result = append(result, exec)
	}

	return result, nil
}

func (r *scriptExecRepo) fetchPhases(
	ctx context.Context,
	execIDs []string,
) (map[string][]scriptExecPhaseDB, error) {
	result := make(map[string][]scriptExecPhaseDB, len(execIDs))
	if len(execIDs) == 0 {
		return result, nil
	}

	placeholders := strings.Repeat("?,", len(execIDs))
	placeholders = placeholders[:len(placeholders)-1]
	query := fmt.Sprintf("SELECT %s FROM script_exec_phases WHERE script_exec_id IN (%s) ORDER BY id ASC",
		scriptExecPhaseColumns, placeholders)

	args := make([]any, len(execIDs))
	for i, id := range execIDs {
		args[i] = id
	}

	var phases []scriptExecPhaseDB
	if err := r.db.SelectContext(ctx, &phases, query, args...); err != nil {
		return nil, fmt.Errorf("failed to fetch script exec phases: %w", err)
	}

	for _, phase := range phases {
		result[phase.ScriptExecID] = append(result[phase.ScriptExecID], phase)
	}
	return result, nil
}

func (r *scriptExecRepo) insertPhasesInTx(
	ctx context.Context,
	tx *sqlx.Tx,
	execID string,
	phases []model.ScriptPhaseExec,
) error {
	if len(phases) == 0 {
		return nil
	}

	insertQuery := `
        INSERT INTO script_exec_phases (
            script_exec_id, name, type, status, message, start_duration_sec, detail
        ) VALUES (?, ?, ?, ?, ?, ?, ?)`

	for _, phase := range phases {
		phaseDB, err := newScriptExecPhaseDB(execID, phase)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, insertQuery,
			phaseDB.ScriptExecID,
			phaseDB.Name,
			phaseDB.Type,
			phaseDB.Status,
			phaseDB.Message,
			phaseDB.StartDurationSec,
			phaseDB.DetailJSON,
		); err != nil {
			return fmt.Errorf("failed to insert script exec phase: %w", err)
		}
	}
	return nil
}

func (r *scriptExecRepo) replacePhasesInTx(
	ctx context.Context,
	tx *sqlx.Tx,
	execID string,
	phases []model.ScriptPhaseExec,
) error {
	if _, err := tx.ExecContext(ctx,
		"DELETE FROM script_exec_phases WHERE script_exec_id = ?", execID); err != nil {
		return fmt.Errorf("failed to clear script exec phases for %s: %w", execID, err)
	}
	return r.insertPhasesInTx(ctx, tx, execID, phases)
}

func (db scriptExecDB) toModel(phases []scriptExecPhaseDB) (model.ScriptExec, error) {
	var updatedAt *time.Time
	if !db.UpdatedAt.IsZero() {
		tmp := db.UpdatedAt
		updatedAt = &tmp
	}

	exec := model.ScriptExec{
		ID:            db.ID,
		TestID:        db.TestID,
		ScriptID:      db.ScriptID,
		Name:          db.Name,
		Description:   db.Description.String,
		CreatorEmail:  db.CreatorEmail.String,
		TotalDuration: secondsToDuration(db.TotalDurationSec),
		ExecBase: model.ExecBase{
			CreatedAt:  db.CreatedAt,
			UpdatedAt:  updatedAt,
			StartedAt:  db.StartedAt,
			FinishedAt: db.FinishedAt,
			Status:     model.ExecStatus(db.Status),
			Message:    db.Message.String,
		},
	}

	exec.Phases = make([]model.ScriptPhaseExec, 0, len(phases))
	for _, phase := range phases {
		modelPhase, err := phase.toModel()
		if err != nil {
			return model.ScriptExec{}, err
		}
		exec.Phases = append(exec.Phases, modelPhase)
	}

	return exec, nil
}

func (db scriptExecPhaseDB) toModel() (model.ScriptPhaseExec, error) {
	base := model.ScriptPhaseExecBase{
		Name:          db.Name,
		Type:          model.ScriptPhaseType(db.Type),
		Status:        model.ExecStatus(db.Status),
		Message:       db.Message.String,
		StartDuration: secondsToDuration(db.StartDurationSec),
	}

	return model.BindPhaseExecDetail(base, []byte(db.DetailJSON.String))
}

func newScriptExecPhaseDB(
	execID string,
	phase model.ScriptPhaseExec,
) (scriptExecPhaseDB, error) {
	dbPhase := scriptExecPhaseDB{
		ScriptExecID:     execID,
		Name:             phase.GetName(),
		Type:             string(phase.GetType()),
		Status:           string(phase.GetStatus()),
		Message:          toNullString(getPhaseMessage(phase)),
		StartDurationSec: durationToSeconds(phase.GetStartDuration()),
	}

	detail, err := phase.GetDetailJSON()
	if err != nil {
		return scriptExecPhaseDB{}, err
	}
	dbPhase.DetailJSON = toNullString(string(detail))

	return dbPhase, nil
}

func getPhaseMessage(phase model.ScriptPhaseExec) string {
	type messageGetter interface {
		GetMessage() string
	}
	if getter, ok := phase.(messageGetter); ok {
		return getter.GetMessage()
	}
	return ""
}