package run

import (
	"context"
	"fmt"
	"strings"

	"github.com/tidbcloud/AgentTeam/internal/coredb"
	"github.com/tidbcloud/AgentTeam/internal/store/storeerr"
)

// LoadCallbackSnapshotForUpdate locks one callback's Run, activation, and
// effect in the same order used by ResolveAwaitingEffect. It exists for
// external mutations whose authorization must remain valid through their own
// transaction commit.
func (store *Store) LoadCallbackSnapshotForUpdate(
	ctx context.Context,
	tx coredb.Tx,
	workspaceID,
	runID,
	effectID string,
) (Run, Activation, Effect, error) {
	if err := store.Validate(); err != nil {
		return Run{}, Activation{}, Effect{}, err
	}
	if tx == nil {
		return Run{}, Activation{}, Effect{}, storeerr.BadRequest("transaction is required")
	}
	workspaceID, runID, effectID = strings.TrimSpace(workspaceID), strings.TrimSpace(runID), strings.TrimSpace(effectID)
	if workspaceID == "" || runID == "" || effectID == "" {
		return Run{}, Activation{}, Effect{}, storeerr.BadRequest("workspaceId, runId, and effectId are required")
	}
	var activationID string
	if err := tx.QueryRow(ctx, `
SELECT activation_id
FROM workflow_run_effects
WHERE workspace_id=$1 AND run_id=$2 AND id=$3
`, workspaceID, runID, effectID).Scan(&activationID); err != nil {
		return Run{}, Activation{}, Effect{}, mapReadError("load workflow Run callback activation identity", err)
	}
	run, err := scanRun(tx.QueryRow(
		ctx,
		runSelectSQL()+` WHERE r.workspace_id=$1 AND r.id=$2 FOR UPDATE`,
		workspaceID,
		runID,
	))
	if err != nil {
		return Run{}, Activation{}, Effect{}, mapReadError("lock workflow Run callback", err)
	}
	activation, err := scanActivation(tx.QueryRow(
		ctx,
		activationSelectSQL()+` WHERE workspace_id=$1 AND run_id=$2 AND id=$3 FOR UPDATE`,
		workspaceID,
		runID,
		strings.TrimSpace(activationID),
	))
	if err != nil {
		return Run{}, Activation{}, Effect{}, mapReadError("lock workflow Run callback activation", err)
	}
	effect, err := scanEffect(tx.QueryRow(
		ctx,
		effectSelectSQL()+` WHERE workspace_id=$1 AND run_id=$2 AND id=$3 FOR UPDATE`,
		workspaceID,
		runID,
		effectID,
	))
	if err != nil {
		return Run{}, Activation{}, Effect{}, mapReadError("lock workflow Run callback effect", err)
	}
	if strings.TrimSpace(effect.ActivationID) != strings.TrimSpace(activation.ID) {
		return Run{}, Activation{}, Effect{}, storeerr.Conflict("workflow Run callback effect belongs to a different activation")
	}
	return run, activation, effect, nil
}

func (store *Store) LoadRun(ctx context.Context, workspaceID, runID string) (Run, error) {
	if err := store.Validate(); err != nil {
		return Run{}, err
	}
	workspaceID, runID = strings.TrimSpace(workspaceID), strings.TrimSpace(runID)
	if workspaceID == "" || runID == "" {
		return Run{}, storeerr.BadRequest("workspaceId and runId are required")
	}
	value, err := scanRun(store.db.QueryRow(ctx, runSelectSQL()+` WHERE r.workspace_id=$1 AND r.id=$2`, workspaceID, runID))
	if err != nil {
		return Run{}, mapReadError("load workflow Run", err)
	}
	return value, nil
}

func (store *Store) ListRuns(ctx context.Context, params ListRunsParams) ([]Run, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID, params.PlanID, params.Status, params.BeforeID =
		strings.TrimSpace(params.WorkspaceID), strings.TrimSpace(params.PlanID),
		strings.TrimSpace(params.Status), strings.TrimSpace(params.BeforeID)
	if params.WorkspaceID == "" {
		return nil, storeerr.BadRequest("workspaceId is required")
	}
	if params.Status != "" && !validRunStatus(params.Status) {
		return nil, storeerr.BadRequest("invalid workflow Run status")
	}
	if params.BeforeStartedAt != nil && params.BeforeID == "" {
		return nil, storeerr.BadRequest("beforeId is required with beforeStartedAt")
	}
	limit, err := boundedLimit(params.Limit)
	if err != nil {
		return nil, err
	}
	query := runSelectSQL() + `
WHERE r.workspace_id=$1`
	args := []any{params.WorkspaceID}
	if params.PlanID != "" {
		args = append(args, params.PlanID)
		query += fmt.Sprintf(" AND r.plan_id=$%d", len(args))
	}
	if params.Status != "" {
		args = append(args, params.Status)
		query += fmt.Sprintf(" AND r.status=$%d", len(args))
	}
	if params.BeforeID != "" {
		if params.BeforeStartedAt == nil {
			args = append(args, params.BeforeID)
			query += fmt.Sprintf(" AND r.started_at IS NULL AND r.id<$%d", len(args))
		} else {
			args = append(args, params.BeforeStartedAt.UTC(), params.BeforeID)
			query += fmt.Sprintf(" AND (r.started_at<$%d OR r.started_at IS NULL OR (r.started_at=$%d AND r.id<$%d))", len(args)-1, len(args)-1, len(args))
		}
	}
	args = append(args, limit)
	query += fmt.Sprintf("\nORDER BY r.started_at DESC, r.id DESC LIMIT $%d", len(args))
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list workflow Runs: %w", err)
	}
	defer rows.Close()
	values := make([]Run, 0, limit)
	for rows.Next() {
		value, err := scanRun(rows)
		if err != nil {
			return nil, fmt.Errorf("scan workflow Run: %w", err)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workflow Runs: %w", err)
	}
	return values, nil
}

func (store *Store) LoadActivation(ctx context.Context, workspaceID, runID, activationID string) (Activation, error) {
	if err := store.Validate(); err != nil {
		return Activation{}, err
	}
	workspaceID, runID, activationID = strings.TrimSpace(workspaceID), strings.TrimSpace(runID), strings.TrimSpace(activationID)
	if workspaceID == "" || runID == "" || activationID == "" {
		return Activation{}, storeerr.BadRequest("workspaceId, runId, and activationId are required")
	}
	value, err := scanActivation(store.db.QueryRow(ctx, activationSelectSQL()+` WHERE workspace_id=$1 AND run_id=$2 AND id=$3`, workspaceID, runID, activationID))
	if err != nil {
		return Activation{}, mapReadError("load workflow Run activation", err)
	}
	return value, nil
}

func (store *Store) ListActivations(ctx context.Context, params ListChildrenParams) ([]Activation, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID, params.RunID, params.Status, params.AfterID = strings.TrimSpace(params.WorkspaceID), strings.TrimSpace(params.RunID), strings.TrimSpace(params.Status), strings.TrimSpace(params.AfterID)
	if params.WorkspaceID == "" || params.RunID == "" {
		return nil, storeerr.BadRequest("workspaceId and runId are required")
	}
	if params.Status != "" && !validActivationStatus(params.Status) {
		return nil, storeerr.BadRequest("invalid workflow Run activation status")
	}
	limit, err := boundedLimit(params.Limit)
	if err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, activationSelectSQL()+`
WHERE workspace_id=$1 AND run_id=$2 AND id>$3 AND ($4='' OR status=$4)
ORDER BY id ASC LIMIT $5`, params.WorkspaceID, params.RunID, params.AfterID, params.Status, limit)
	if err != nil {
		return nil, fmt.Errorf("list workflow Run activations: %w", err)
	}
	defer rows.Close()
	values := make([]Activation, 0, limit)
	for rows.Next() {
		value, err := scanActivation(rows)
		if err != nil {
			return nil, fmt.Errorf("scan workflow Run activation: %w", err)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workflow Run activations: %w", err)
	}
	return values, nil
}

func (store *Store) LoadEffect(ctx context.Context, workspaceID, runID, effectID string) (Effect, error) {
	if err := store.Validate(); err != nil {
		return Effect{}, err
	}
	workspaceID, runID, effectID = strings.TrimSpace(workspaceID), strings.TrimSpace(runID), strings.TrimSpace(effectID)
	if workspaceID == "" || runID == "" || effectID == "" {
		return Effect{}, storeerr.BadRequest("workspaceId, runId, and effectId are required")
	}
	value, err := scanEffect(store.db.QueryRow(ctx, effectSelectSQL()+` WHERE workspace_id=$1 AND run_id=$2 AND id=$3`, workspaceID, runID, effectID))
	if err != nil {
		return Effect{}, mapReadError("load workflow Run effect", err)
	}
	return value, nil
}

// LoadEffectByID resolves an effect without requiring the caller to already
// know its owning Run. The workspace predicate is part of the lookup rather
// than a post-read authorization check, so an effect ID from another
// workspace is indistinguishable from an unknown effect.
func (store *Store) LoadEffectByID(ctx context.Context, workspaceID, effectID string) (Effect, error) {
	if err := store.Validate(); err != nil {
		return Effect{}, err
	}
	workspaceID, effectID = strings.TrimSpace(workspaceID), strings.TrimSpace(effectID)
	if workspaceID == "" || effectID == "" {
		return Effect{}, storeerr.BadRequest("workspaceId and effectId are required")
	}
	value, err := scanEffect(store.db.QueryRow(ctx, effectSelectSQL()+` WHERE workspace_id=$1 AND id=$2`, workspaceID, effectID))
	if err != nil {
		return Effect{}, mapReadError("load workflow Run effect by id", err)
	}
	return value, nil
}

func (store *Store) ListEffects(ctx context.Context, params ListChildrenParams) ([]Effect, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID, params.RunID, params.Status, params.AfterID = strings.TrimSpace(params.WorkspaceID), strings.TrimSpace(params.RunID), strings.TrimSpace(params.Status), strings.TrimSpace(params.AfterID)
	if params.WorkspaceID == "" || params.RunID == "" {
		return nil, storeerr.BadRequest("workspaceId and runId are required")
	}
	if params.Status != "" && !validEffectStatus(params.Status) {
		return nil, storeerr.BadRequest("invalid workflow Run effect status")
	}
	limit, err := boundedLimit(params.Limit)
	if err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, effectSelectSQL()+`
WHERE workspace_id=$1 AND run_id=$2 AND id>$3 AND ($4='' OR status=$4)
ORDER BY id ASC LIMIT $5`, params.WorkspaceID, params.RunID, params.AfterID, params.Status, limit)
	if err != nil {
		return nil, fmt.Errorf("list workflow Run effects: %w", err)
	}
	defer rows.Close()
	values := make([]Effect, 0, limit)
	for rows.Next() {
		value, err := scanEffect(rows)
		if err != nil {
			return nil, fmt.Errorf("scan workflow Run effect: %w", err)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workflow Run effects: %w", err)
	}
	return values, nil
}

// ListAwaitingCallbackEffects discovers only callbacks whose durable frozen
// responder matches the authenticated Agent. Filtering happens in SQL before
// any receipt is decoded, so foreign or malformed callback payloads cannot
// leak Run existence or disrupt another Agent's discovery.
func (store *Store) ListAwaitingCallbackEffects(
	ctx context.Context,
	params ListAwaitingCallbackEffectsParams,
) ([]Effect, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.AgentID = strings.TrimSpace(params.AgentID)
	params.MemberID = strings.TrimSpace(params.MemberID)
	params.AfterID = strings.TrimSpace(params.AfterID)
	if params.WorkspaceID == "" || params.AgentID == "" {
		return nil, storeerr.BadRequest("workspaceId and agentId are required")
	}
	limit, err := boundedLimit(params.Limit)
	if err != nil {
		return nil, err
	}
	query := effectSelectSQL() + `
WHERE workspace_id=$1
  AND callback_responder_agent_id=$2
  AND status='awaiting'
  AND id>$3`
	args := []any{params.WorkspaceID, params.AgentID, params.AfterID}
	if params.MemberID != "" {
		// The production Agent route always supplies MemberID. Require the
		// frozen Agent and its current canonical lifecycle before decoding any
		// receipt. Agent IDs are immutable and are never rebound to another
		// workspace member, so the effect does not duplicate that identity.
		query += `
  AND EXISTS (
      SELECT 1
      FROM workflow_run_instances callback_run
      JOIN agents callback_agent
        ON callback_agent.workspace_id=workflow_run_effects.workspace_id
       AND callback_agent.id=workflow_run_effects.callback_responder_agent_id
       AND callback_agent.id=$2
       AND callback_agent.member_id=$4
       AND callback_agent.status <> 'archived'
      JOIN members callback_agent_member
        ON callback_agent_member.workspace_id=callback_agent.workspace_id
       AND callback_agent_member.id=callback_agent.member_id
       AND callback_agent_member.type='agent'
       AND callback_agent_member.status='active'
      JOIN machines callback_machine
        ON callback_machine.workspace_id=callback_agent.workspace_id
       AND callback_machine.id=callback_agent.machine_id
       AND callback_machine.status='active'
      JOIN members callback_owner
        ON callback_owner.workspace_id=callback_machine.workspace_id
       AND callback_owner.user_id=callback_machine.created_by_user_id
       AND callback_owner.type='human'
       AND callback_owner.status='active'
      WHERE callback_run.workspace_id=workflow_run_effects.workspace_id
        AND callback_run.id=workflow_run_effects.run_id
        AND callback_run.principal_member_id_snapshot=callback_owner.id
  )`
		args = append(args, params.MemberID)
	}
	query += fmt.Sprintf("\nORDER BY id ASC LIMIT $%d", len(args)+1)
	args = append(args, limit)
	rows, err := store.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list awaiting workflow Run callback effects: %w", err)
	}
	defer rows.Close()
	values := make([]Effect, 0, limit)
	for rows.Next() {
		value, err := scanEffect(rows)
		if err != nil {
			return nil, fmt.Errorf("scan awaiting workflow Run callback effect: %w", err)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate awaiting workflow Run callback effects: %w", err)
	}
	return values, nil
}

func (store *Store) LoadEvent(ctx context.Context, workspaceID, runID string, sequence int64) (Event, error) {
	if err := store.Validate(); err != nil {
		return Event{}, err
	}
	workspaceID, runID = strings.TrimSpace(workspaceID), strings.TrimSpace(runID)
	if workspaceID == "" || runID == "" || sequence < 1 {
		return Event{}, storeerr.BadRequest("workspaceId, runId, and positive sequence are required")
	}
	value, err := scanEvent(store.db.QueryRow(ctx, eventSelectSQL()+` WHERE workspace_id=$1 AND run_id=$2 AND seq=$3`, workspaceID, runID, sequence))
	if err != nil {
		return Event{}, mapReadError("load workflow Run event", err)
	}
	return value, nil
}

func (store *Store) ListEvents(ctx context.Context, params ListEventsParams) ([]Event, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	params.WorkspaceID, params.RunID = strings.TrimSpace(params.WorkspaceID), strings.TrimSpace(params.RunID)
	if params.WorkspaceID == "" || params.RunID == "" || params.AfterSeq < 0 {
		return nil, storeerr.BadRequest("workspaceId, runId, and non-negative afterSeq are required")
	}
	limit, err := boundedLimit(params.Limit)
	if err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, eventSelectSQL()+`
WHERE workspace_id=$1 AND run_id=$2 AND seq>$3
ORDER BY seq ASC LIMIT $4`, params.WorkspaceID, params.RunID, params.AfterSeq, limit)
	if err != nil {
		return nil, fmt.Errorf("list workflow Run events: %w", err)
	}
	defer rows.Close()
	values := make([]Event, 0, limit)
	for rows.Next() {
		value, err := scanEvent(rows)
		if err != nil {
			return nil, fmt.Errorf("scan workflow Run event: %w", err)
		}
		values = append(values, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate workflow Run events: %w", err)
	}
	return values, nil
}