package approval

import (
	"bytes"
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	"reflect"
	"sort"
	"strings"
	"time"

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

type Store struct{ db coredb.DB }

func New(db coredb.DB) *Store { return &Store{db: db} }

// TxStore binds Approval authority operations to a caller-owned transaction.
// Business domains use this surface when their immutable request and Approval
// subject must either both commit or both roll back.
type TxStore struct{ tx coredb.Tx }

// InTx binds Approval operations to an existing transaction.
func (store *Store) InTx(tx coredb.Tx) *TxStore { return &TxStore{tx: tx} }

func (store *Store) Validate() error {
	if store == nil || store.db == nil {
		return fmt.Errorf("approval store is not configured")
	}
	return nil
}

func (store *Store) Create(ctx context.Context, params CreateParams) (Instance, []Assignment, bool, error) {
	if err := store.Validate(); err != nil {
		return Instance{}, nil, false, err
	}
	normalized, err := normalizeCreate(params)
	if err != nil {
		return Instance{}, nil, false, err
	}
	var instance Instance
	var assignments []Assignment
	created := false
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var txErr error
		instance, assignments, created, txErr = createInTx(ctx, tx, normalized)
		return txErr
	})
	return instance, assignments, created, err
}

// Create inserts or replays an Approval subject in the bound transaction.
func (store *TxStore) Create(ctx context.Context, params CreateParams) (Instance, []Assignment, bool, error) {
	if store == nil || store.tx == nil {
		return Instance{}, nil, false, fmt.Errorf("approval tx store is not configured")
	}
	normalized, err := normalizeCreate(params)
	if err != nil {
		return Instance{}, nil, false, err
	}
	return createInTx(ctx, store.tx, normalized)
}

func createInTx(ctx context.Context, tx coredb.Tx, normalized CreateParams) (Instance, []Assignment, bool, error) {
	if normalized.IdempotencyKey != "" {
		existing, found, err := loadInstanceByIdempotencyKey(ctx, tx, normalized.WorkspaceID, normalized.ApplicantMemberID, normalized.IdempotencyKey, true)
		if err != nil {
			return Instance{}, nil, false, err
		}
		if found {
			return replayCreate(ctx, tx, existing, normalized)
		}
	}
	existing, found, err := loadInstanceBySubject(ctx, tx, normalized.WorkspaceID, normalized.SubjectType, normalized.SubjectRef, normalized.SubjectVersion, true)
	if err != nil {
		return Instance{}, nil, false, err
	}
	if found {
		return replayCreate(ctx, tx, existing, normalized)
	}
	if err := validateMembers(
		ctx,
		tx,
		normalized.WorkspaceID,
		normalized.ApplicantMemberID,
		normalized.ApproverMemberIDs,
		normalized.AllowAgentApplicant,
	); err != nil {
		return Instance{}, nil, false, err
	}
	now := normalized.Now
	if _, err := tx.Exec(ctx, `
INSERT INTO approval_instances (
    id, workspace_id, subject_type, subject_ref, subject_version,
    payload, payload_digest, idempotency_key, pending_key,
    applicant_member_id, state, decision, decision_reason,
    decided_by_member_id, version, projected_version, correlation_id, created_at, updated_at, decided_at, expires_at
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,'pending','','',NULL,1,0,$11,$12,$12,NULL,$13)
`, normalized.ID, normalized.WorkspaceID, normalized.SubjectType, normalized.SubjectRef,
		normalized.SubjectVersion, nullableJSON(normalized.Payload), normalized.PayloadDigest,
		nullableString(normalized.IdempotencyKey), nullableString(normalized.PendingKey),
		normalized.ApplicantMemberID, normalized.CorrelationID, now, nullableTime(normalized.ExpiresAt)); err != nil {
		if coredb.IsUniqueViolation(err) {
			return reconcileCreateUniqueViolation(ctx, tx, normalized)
		}
		return Instance{}, nil, false, fmt.Errorf("insert approval instance: %w", err)
	}
	for _, approverID := range normalized.ApproverMemberIDs {
		assignmentID, err := identifiers.NewEntityID("apasg")
		if err != nil {
			return Instance{}, nil, false, fmt.Errorf("generate approval assignment id: %w", err)
		}
		if _, err := tx.Exec(ctx, `
INSERT INTO approval_assignments (
    id, workspace_id, approval_instance_id, approver_member_id,
    status, version, created_at, updated_at, closed_at
) VALUES ($1,$2,$3,$4,'pending',1,$5,$5,NULL)
`, assignmentID, normalized.WorkspaceID, normalized.ID, approverID, now); err != nil {
			return Instance{}, nil, false, fmt.Errorf("insert approval assignment: %w", err)
		}
	}
	instance, err := loadInstance(ctx, tx, normalized.WorkspaceID, normalized.ID, false)
	if err != nil {
		return Instance{}, nil, false, err
	}
	assignments, err := listAssignments(ctx, tx, normalized.WorkspaceID, normalized.ID, false)
	if err != nil {
		return Instance{}, nil, false, err
	}
	return instance, assignments, true, nil
}

func replayCreate(ctx context.Context, tx coredb.Tx, existing Instance, normalized CreateParams) (Instance, []Assignment, bool, error) {
	if existing.WorkspaceID != normalized.WorkspaceID ||
		existing.SubjectType != normalized.SubjectType ||
		existing.SubjectRef != normalized.SubjectRef ||
		existing.SubjectVersion != normalized.SubjectVersion ||
		existing.PayloadDigest != normalized.PayloadDigest ||
		!equalBytes(existing.Payload, normalized.Payload) ||
		existing.IdempotencyKey != normalized.IdempotencyKey ||
		existing.PendingKey != normalized.PendingKey ||
		existing.ApplicantMemberID != normalized.ApplicantMemberID ||
		!sameTimePointer(existing.ExpiresAt, normalized.ExpiresAt) {
		return Instance{}, nil, false, storeerr.Conflict("approval replay key is already bound to another immutable request")
	}
	assignments, err := listAssignments(ctx, tx, existing.WorkspaceID, existing.ID, true)
	if err != nil {
		return Instance{}, nil, false, err
	}
	if !sameApproverSet(assignments, normalized.ApproverMemberIDs) {
		return Instance{}, nil, false, storeerr.Conflict("approval request is already bound to another approver set")
	}
	return existing, assignments, false, nil
}

func reconcileCreateUniqueViolation(ctx context.Context, tx coredb.Tx, normalized CreateParams) (Instance, []Assignment, bool, error) {
	if normalized.IdempotencyKey != "" {
		if existing, found, err := loadInstanceByIdempotencyKey(ctx, tx, normalized.WorkspaceID, normalized.ApplicantMemberID, normalized.IdempotencyKey, true); err != nil {
			return Instance{}, nil, false, err
		} else if found {
			return replayCreate(ctx, tx, existing, normalized)
		}
	}
	if existing, found, err := loadInstanceBySubject(ctx, tx, normalized.WorkspaceID, normalized.SubjectType, normalized.SubjectRef, normalized.SubjectVersion, true); err != nil {
		return Instance{}, nil, false, err
	} else if found {
		return replayCreate(ctx, tx, existing, normalized)
	}
	return Instance{}, nil, false, storeerr.Conflict("approval pending key is already bound to another request")
}

func (store *Store) Load(ctx context.Context, workspaceID, approvalID string) (Instance, error) {
	if err := store.Validate(); err != nil {
		return Instance{}, err
	}
	return loadInstance(ctx, store.db, workspaceID, approvalID, false)
}

// LoadByIdempotencyKey returns an Approval request by its caller-scoped
// idempotency key.
func (store *Store) LoadByIdempotencyKey(ctx context.Context, workspaceID, applicantMemberID, idempotencyKey string) (Instance, error) {
	if err := store.Validate(); err != nil {
		return Instance{}, err
	}
	value, found, err := loadInstanceByIdempotencyKey(ctx, store.db, strings.TrimSpace(workspaceID), strings.TrimSpace(applicantMemberID), strings.TrimSpace(idempotencyKey), false)
	if err != nil {
		return Instance{}, err
	}
	if !found {
		return Instance{}, storeerr.NotFound("approval submission not found")
	}
	return value, nil
}

// Load locks and returns an Approval instance in the bound transaction.
func (store *TxStore) Load(ctx context.Context, workspaceID, approvalID string) (Instance, error) {
	if store == nil || store.tx == nil {
		return Instance{}, fmt.Errorf("approval tx store is not configured")
	}
	return loadInstance(ctx, store.tx, workspaceID, approvalID, true)
}

// LoadByIdempotencyKey locks and returns an Approval request by its
// caller-scoped idempotency key in the bound transaction.
func (store *TxStore) LoadByIdempotencyKey(ctx context.Context, workspaceID, applicantMemberID, idempotencyKey string) (Instance, error) {
	if store == nil || store.tx == nil {
		return Instance{}, fmt.Errorf("approval tx store is not configured")
	}
	value, found, err := loadInstanceByIdempotencyKey(ctx, store.tx, strings.TrimSpace(workspaceID), strings.TrimSpace(applicantMemberID), strings.TrimSpace(idempotencyKey), true)
	if err != nil {
		return Instance{}, err
	}
	if !found {
		return Instance{}, storeerr.NotFound("approval submission not found")
	}
	return value, nil
}

func (store *Store) ListAssignments(ctx context.Context, workspaceID, approvalID string) ([]Assignment, error) {
	if err := store.Validate(); err != nil {
		return nil, err
	}
	return listAssignments(ctx, store.db, workspaceID, approvalID, false)
}

// ListAssignments returns the assignment set in the bound transaction.
func (store *TxStore) ListAssignments(ctx context.Context, workspaceID, approvalID string) ([]Assignment, error) {
	if store == nil || store.tx == nil {
		return nil, fmt.Errorf("approval tx store is not configured")
	}
	return listAssignments(ctx, store.tx, workspaceID, approvalID, true)
}

func (store *Store) LoadAssignment(ctx context.Context, workspaceID, assignmentID string) (Assignment, Instance, error) {
	if err := store.Validate(); err != nil {
		return Assignment{}, Instance{}, err
	}
	assignment, err := loadAssignment(ctx, store.db, workspaceID, assignmentID, false)
	if err != nil {
		return Assignment{}, Instance{}, err
	}
	instance, err := loadInstance(ctx, store.db, workspaceID, assignment.ApprovalInstanceID, false)
	return assignment, instance, err
}

// LoadAssignment locks an assignment and its Approval instance in the bound
// transaction.
func (store *TxStore) LoadAssignment(ctx context.Context, workspaceID, assignmentID string) (Assignment, Instance, error) {
	if store == nil || store.tx == nil {
		return Assignment{}, Instance{}, fmt.Errorf("approval tx store is not configured")
	}
	assignment, err := loadAssignment(ctx, store.tx, workspaceID, assignmentID, true)
	if err != nil {
		return Assignment{}, Instance{}, err
	}
	instance, err := loadInstance(ctx, store.tx, workspaceID, assignment.ApprovalInstanceID, true)
	return assignment, instance, err
}

func (store *Store) Decide(ctx context.Context, params DecideParams) (DecisionResult, error) {
	return store.DecideWithEffect(ctx, params, nil)
}

// DecideWithEffect runs a subject effect in the same transaction as the
// terminal generic Approval facts.
func (store *Store) DecideWithEffect(ctx context.Context, params DecideParams, effect DecisionEffect) (DecisionResult, error) {
	if err := store.Validate(); err != nil {
		return DecisionResult{}, err
	}
	normalized, requestDigest, err := normalizeDecision(params)
	if err != nil {
		return DecisionResult{}, err
	}
	var result DecisionResult
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var txErr error
		result, txErr = decideInTx(ctx, tx, normalized, requestDigest, effect)
		return txErr
	})
	return result, err
}

// Decide applies an Approval decision in the bound transaction. The caller
// owns commit or rollback and can atomically settle its domain state with the
// Approval decision.
func (store *TxStore) Decide(ctx context.Context, params DecideParams) (DecisionResult, error) {
	return store.DecideWithEffect(ctx, params, nil)
}

// DecideWithEffect runs a subject effect in the bound transaction after
// generic authorization and idempotency checks and before terminal writes.
func (store *TxStore) DecideWithEffect(ctx context.Context, params DecideParams, effect DecisionEffect) (DecisionResult, error) {
	if store == nil || store.tx == nil {
		return DecisionResult{}, fmt.Errorf("approval tx store is not configured")
	}
	normalized, requestDigest, err := normalizeDecision(params)
	if err != nil {
		return DecisionResult{}, err
	}
	return decideInTx(ctx, store.tx, normalized, requestDigest, effect)
}

func (store *Store) CancelByApplicant(ctx context.Context, params ApplicantCancelParams) (DecisionResult, error) {
	if err := store.Validate(); err != nil {
		return DecisionResult{}, err
	}
	normalized, requestDigest, err := normalizeApplicantCancel(params)
	if err != nil {
		return DecisionResult{}, err
	}
	var result DecisionResult
	err = coredb.WithTx(ctx, store.db, func(tx coredb.Tx) error {
		var txErr error
		result, txErr = cancelInTx(ctx, tx, normalized, requestDigest, true)
		return txErr
	})
	return result, err
}

func (store *TxStore) CancelByApplicant(ctx context.Context, params ApplicantCancelParams) (DecisionResult, error) {
	if store == nil || store.tx == nil {
		return DecisionResult{}, fmt.Errorf("approval tx store is not configured")
	}
	normalized, requestDigest, err := normalizeApplicantCancel(params)
	if err != nil {
		return DecisionResult{}, err
	}
	return cancelInTx(ctx, store.tx, normalized, requestDigest, true)
}

// CancelByDomain cancels a pending Approval after the owning domain has
// authorized the actor. Keeping this operation on TxStore only prevents it
// from becoming a stand-alone authorization bypass and lets the caller commit
// its own tombstone/state change atomically with the cancellation.
func (store *TxStore) CancelByDomain(ctx context.Context, params DomainCancelParams) (DecisionResult, error) {
	if store == nil || store.tx == nil {
		return DecisionResult{}, fmt.Errorf("approval tx store is not configured")
	}
	normalized, requestDigest, err := normalizeApplicantCancel(ApplicantCancelParams{
		WorkspaceID:          params.WorkspaceID,
		ApprovalID:           params.ApprovalID,
		ActorMemberID:        params.ActorMemberID,
		ExpectedVersion:      params.ExpectedVersion,
		ClientCancellationID: params.ClientCancellationID,
		Reason:               params.Reason,
		Now:                  params.Now,
	})
	if err != nil {
		return DecisionResult{}, err
	}
	return cancelInTx(ctx, store.tx, normalized, requestDigest, false)
}

func cancelInTx(ctx context.Context, tx coredb.Tx, normalized ApplicantCancelParams, requestDigest string, requireApplicant bool) (DecisionResult, error) {
	instance, err := loadInstance(ctx, tx, normalized.WorkspaceID, normalized.ApprovalID, true)
	if err != nil {
		return DecisionResult{}, err
	}
	if requireApplicant && instance.ApplicantMemberID != normalized.ActorMemberID {
		return DecisionResult{}, storeerr.Forbidden("only the Approval applicant may cancel this request")
	}
	receipt, found, err := loadDecisionReceipt(ctx, tx, instance.ID, normalized.ActorMemberID, normalized.ClientCancellationID)
	if err != nil {
		return DecisionResult{}, err
	}
	if found {
		if receipt.RequestDigest != requestDigest {
			return DecisionResult{}, storeerr.Conflict("approval cancellation id was already used with another payload")
		}
		replayed, err := loadInstance(ctx, tx, normalized.WorkspaceID, instance.ID, false)
		if err != nil {
			return DecisionResult{}, err
		}
		assignment, err := loadAssignment(ctx, tx, normalized.WorkspaceID, receipt.AssignmentID, false)
		if err != nil {
			return DecisionResult{}, err
		}
		return DecisionResult{Instance: replayed, Assignment: assignment, Receipt: receipt, AlreadyApplied: true}, nil
	}
	if instance.State != StatePending {
		return DecisionResult{}, storeerr.Conflict("approval has already reached a terminal decision")
	}
	if instance.Version != normalized.ExpectedVersion {
		return DecisionResult{}, storeerr.Conflict("approval version changed")
	}
	assignments, err := listAssignments(ctx, tx, normalized.WorkspaceID, instance.ID, true)
	if err != nil {
		return DecisionResult{}, err
	}
	if len(assignments) == 0 {
		return DecisionResult{}, storeerr.Conflict("approval has no assignment to close")
	}
	now := normalized.Now
	// Expiry wins over a late cancellation: an expired request is terminalized
	// as expired, never cancelled.
	expired, err := ensureNotExpired(ctx, tx, instance, now)
	if err != nil {
		return DecisionResult{}, err
	}
	if expired {
		return DecisionResult{}, storeerr.Conflict("approval has expired before cancellation")
	}
	update, err := tx.Exec(ctx, `
UPDATE approval_instances
SET state='cancelled', decision='cancelled', decision_reason=$4,
    decided_by_member_id=$5, pending_key=NULL,
    version=version+1, updated_at=$6, decided_at=$6
WHERE workspace_id=$1 AND id=$2 AND version=$3 AND state='pending'
`, normalized.WorkspaceID, instance.ID, instance.Version, normalized.Reason, normalized.ActorMemberID, now)
	if err != nil {
		return DecisionResult{}, fmt.Errorf("cancel approval instance: %w", err)
	}
	if update.RowsAffected() != 1 {
		return DecisionResult{}, storeerr.Conflict("approval version changed")
	}
	if _, err := tx.Exec(ctx, `
UPDATE approval_assignments
SET status='cancelled', version=version+1, updated_at=$3, closed_at=$3
WHERE workspace_id=$1 AND approval_instance_id=$2 AND status='pending'
`, normalized.WorkspaceID, instance.ID, now); err != nil {
		return DecisionResult{}, fmt.Errorf("cancel approval assignments: %w", err)
	}
	receiptID, err := identifiers.NewEntityID("apdec")
	if err != nil {
		return DecisionResult{}, fmt.Errorf("generate approval cancellation receipt id: %w", err)
	}
	receiptAssignment := assignments[0]
	if _, err := tx.Exec(ctx, `
INSERT INTO approval_decision_receipts (
    id, workspace_id, approval_instance_id, assignment_id, actor_member_id,
    client_decision_id, request_digest, decision, reason, result_version, created_at
) VALUES ($1,$2,$3,$4,$5,$6,$7,'cancelled',$8,$9,$10)
`, receiptID, normalized.WorkspaceID, instance.ID, receiptAssignment.ID, normalized.ActorMemberID,
		normalized.ClientCancellationID, requestDigest, normalized.Reason, instance.Version+1, now); err != nil {
		return DecisionResult{}, fmt.Errorf("insert approval cancellation receipt: %w", err)
	}
	var result DecisionResult
	result.Instance, err = loadInstance(ctx, tx, normalized.WorkspaceID, instance.ID, false)
	if err != nil {
		return DecisionResult{}, err
	}
	result.Assignment, err = loadAssignment(ctx, tx, normalized.WorkspaceID, receiptAssignment.ID, false)
	if err != nil {
		return DecisionResult{}, err
	}
	result.Receipt, _, err = loadDecisionReceipt(ctx, tx, instance.ID, normalized.ActorMemberID, normalized.ClientCancellationID)
	return result, err
}

func decideInTx(ctx context.Context, tx coredb.Tx, normalized DecideParams, requestDigest string, effect DecisionEffect) (DecisionResult, error) {
	instance, err := loadInstance(ctx, tx, normalized.WorkspaceID, normalized.ApprovalID, true)
	if err != nil {
		return DecisionResult{}, err
	}
	assignment, err := loadAssignment(ctx, tx, normalized.WorkspaceID, normalized.AssignmentID, true)
	if err != nil {
		return DecisionResult{}, err
	}
	if assignment.ApprovalInstanceID != instance.ID || assignment.ApproverMemberID != normalized.ActorMemberID {
		return DecisionResult{}, storeerr.Forbidden("approval assignment does not belong to the current Human")
	}
	receipt, found, err := loadDecisionReceipt(ctx, tx, instance.ID, normalized.ActorMemberID, normalized.ClientDecisionID)
	if err != nil {
		return DecisionResult{}, err
	}
	if found {
		if receipt.RequestDigest != requestDigest {
			return DecisionResult{}, storeerr.Conflict("approval decision id was already used with another payload")
		}
		replayedInstance, err := loadInstance(ctx, tx, normalized.WorkspaceID, instance.ID, false)
		if err != nil {
			return DecisionResult{}, err
		}
		replayedAssignment, err := loadAssignment(ctx, tx, normalized.WorkspaceID, assignment.ID, false)
		if err != nil {
			return DecisionResult{}, err
		}
		return DecisionResult{Instance: replayedInstance, Assignment: replayedAssignment, Receipt: receipt, AlreadyApplied: true}, nil
	}
	if instance.State != StatePending || assignment.Status != AssignmentPending {
		return DecisionResult{}, storeerr.Conflict("approval has already reached a terminal decision")
	}
	if instance.Version != normalized.ExpectedVersion {
		return DecisionResult{}, storeerr.Conflict("approval version changed")
	}
	now := normalized.Now
	// Expiry wins over a delayed/concurrent approval: the authoritative
	// expires_at is compared under the row lock before any effect runs, so a
	// late approve can never grant an expired request.
	expired, err := ensureNotExpired(ctx, tx, instance, now)
	if err != nil {
		return DecisionResult{}, err
	}
	if expired {
		return DecisionResult{}, storeerr.Conflict("approval has expired before a decision")
	}
	nextState := StateApproved
	nextAssignment := AssignmentApproved
	if normalized.Decision == DecisionRejected {
		nextState = StateRejected
		nextAssignment = AssignmentRejected
	}
	decisionReason := normalized.Reason
	if effect != nil {
		effectResult, err := effect(ctx, tx, instance, normalized.Decision)
		if err != nil {
			return DecisionResult{}, err
		}
		if effectResult.TerminalState != "" {
			switch {
			case normalized.Decision == DecisionApproved && effectResult.TerminalState == StateApproved:
			case normalized.Decision == DecisionRejected && effectResult.TerminalState == StateRejected:
			case effectResult.TerminalState == StateCancelled:
				nextAssignment = AssignmentCancelled
			default:
				return DecisionResult{}, storeerr.BadRequest("approval effect returned an invalid terminal state")
			}
			nextState = effectResult.TerminalState
		}
		if effectResult.DecisionReason != "" {
			decisionReason = effectResult.DecisionReason
		}
	}
	update, err := tx.Exec(ctx, `
UPDATE approval_instances
SET state=$4, decision=$4, decision_reason=$5, decided_by_member_id=$6,
    pending_key=NULL, version=version+1, updated_at=$7, decided_at=$7
WHERE workspace_id=$1 AND id=$2 AND version=$3 AND state='pending'
`, normalized.WorkspaceID, instance.ID, instance.Version, nextState, decisionReason, normalized.ActorMemberID, now)
	if err != nil {
		return DecisionResult{}, fmt.Errorf("decide approval instance: %w", err)
	}
	if update.RowsAffected() != 1 {
		return DecisionResult{}, storeerr.Conflict("approval version changed")
	}
	if _, err := tx.Exec(ctx, `
UPDATE approval_assignments
SET status = CASE WHEN id=$3 THEN $4 ELSE 'superseded' END,
    version=version+1, updated_at=$5, closed_at=$5
WHERE workspace_id=$1 AND approval_instance_id=$2 AND status='pending'
`, normalized.WorkspaceID, instance.ID, assignment.ID, nextAssignment, now); err != nil {
		return DecisionResult{}, fmt.Errorf("close approval assignments: %w", err)
	}
	receiptID, err := identifiers.NewEntityID("apdec")
	if err != nil {
		return DecisionResult{}, fmt.Errorf("generate approval decision receipt id: %w", err)
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO approval_decision_receipts (
    id, workspace_id, approval_instance_id, assignment_id, actor_member_id,
    client_decision_id, request_digest, decision, reason, result_version, created_at
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
`, receiptID, normalized.WorkspaceID, instance.ID, assignment.ID, normalized.ActorMemberID,
		normalized.ClientDecisionID, requestDigest, normalized.Decision, normalized.Reason,
		instance.Version+1, now); err != nil {
		return DecisionResult{}, fmt.Errorf("insert approval decision receipt: %w", err)
	}
	var result DecisionResult
	result.Instance, err = loadInstance(ctx, tx, normalized.WorkspaceID, instance.ID, false)
	if err != nil {
		return DecisionResult{}, err
	}
	result.Assignment, err = loadAssignment(ctx, tx, normalized.WorkspaceID, assignment.ID, false)
	if err != nil {
		return DecisionResult{}, err
	}
	result.Receipt, _, err = loadDecisionReceipt(ctx, tx, instance.ID, normalized.ActorMemberID, normalized.ClientDecisionID)
	return result, err
}

func normalizeApplicantCancel(params ApplicantCancelParams) (ApplicantCancelParams, string, error) {
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.ApprovalID = strings.TrimSpace(params.ApprovalID)
	params.ActorMemberID = strings.TrimSpace(params.ActorMemberID)
	params.ClientCancellationID = strings.TrimSpace(params.ClientCancellationID)
	params.Reason = strings.TrimSpace(params.Reason)
	if params.WorkspaceID == "" || params.ApprovalID == "" || params.ActorMemberID == "" ||
		params.ExpectedVersion <= 0 || params.ClientCancellationID == "" || params.Reason == "" {
		return ApplicantCancelParams{}, "", storeerr.BadRequest("approval cancellation identity, actor, expected version, client id, and reason are required")
	}
	if len(params.ClientCancellationID) > 128 || len(params.Reason) > 4096 {
		return ApplicantCancelParams{}, "", storeerr.BadRequest("approval cancellation payload is too large")
	}
	payload, err := json.Marshal(struct {
		ActorMemberID   string `json:"actorMemberId"`
		ExpectedVersion int64  `json:"expectedVersion"`
		Reason          string `json:"reason"`
	}{params.ActorMemberID, params.ExpectedVersion, params.Reason})
	if err != nil {
		return ApplicantCancelParams{}, "", fmt.Errorf("marshal approval cancellation digest: %w", err)
	}
	digest := sha256.Sum256(payload)
	if params.Now.IsZero() {
		params.Now = time.Now().UTC()
	} else {
		params.Now = params.Now.UTC()
	}
	return params, hex.EncodeToString(digest[:]), nil
}

func normalizeCreate(params CreateParams) (CreateParams, error) {
	params.ID = strings.TrimSpace(params.ID)
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.SubjectType = strings.TrimSpace(params.SubjectType)
	params.SubjectRef = strings.TrimSpace(params.SubjectRef)
	params.Payload = bytes.TrimSpace(params.Payload)
	params.PayloadDigest = strings.ToLower(strings.TrimSpace(params.PayloadDigest))
	params.IdempotencyKey = strings.TrimSpace(params.IdempotencyKey)
	params.PendingKey = strings.TrimSpace(params.PendingKey)
	params.ApplicantMemberID = strings.TrimSpace(params.ApplicantMemberID)
	params.CorrelationID = strings.TrimSpace(params.CorrelationID)
	if params.ID == "" || params.WorkspaceID == "" || params.SubjectType == "" || params.SubjectRef == "" || params.SubjectVersion <= 0 || params.ApplicantMemberID == "" || !validDigest(params.PayloadDigest) {
		return CreateParams{}, storeerr.BadRequest("approval identity, subject, applicant, version, and payload digest are required")
	}
	if len(params.ID) > 64 || len(params.SubjectType) > 64 || len(params.SubjectRef) > 255 || len(params.CorrelationID) > 255 {
		return CreateParams{}, storeerr.BadRequest("approval identifier is too long")
	}
	if len(params.IdempotencyKey) > 64 || len(params.PendingKey) > 64 {
		return CreateParams{}, storeerr.BadRequest("approval idempotency or pending key is too long")
	}
	if len(params.Payload) > 0 && !json.Valid(params.Payload) {
		return CreateParams{}, storeerr.BadRequest("approval payload must be valid JSON")
	}
	if len(params.Payload) > 0 {
		canonical, err := deterministicJSON(params.Payload)
		if err != nil {
			return CreateParams{}, storeerr.BadRequest("approval payload must be valid JSON")
		}
		digest := sha256.Sum256(canonical)
		if hex.EncodeToString(digest[:]) != params.PayloadDigest {
			return CreateParams{}, storeerr.BadRequest("approval payload digest does not match the canonical payload")
		}
		params.Payload = canonical
	}
	rawApprovers := append([]string(nil), params.ApproverMemberIDs...)
	seen := map[string]struct{}{}
	params.ApproverMemberIDs = make([]string, 0, len(rawApprovers))
	// Rebuild from the caller slice without retaining whitespace aliases.
	for _, memberID := range rawApprovers {
		memberID = strings.TrimSpace(memberID)
		if memberID == "" {
			continue
		}
		if _, duplicate := seen[memberID]; duplicate {
			continue
		}
		seen[memberID] = struct{}{}
		params.ApproverMemberIDs = append(params.ApproverMemberIDs, memberID)
	}
	if len(params.ApproverMemberIDs) == 0 || len(params.ApproverMemberIDs) > 100 {
		return CreateParams{}, storeerr.BadRequest("approval requires between 1 and 100 approvers")
	}
	sort.Strings(params.ApproverMemberIDs)
	if params.ExpiresAt != nil {
		// The DB stores timestamp(0) second precision; truncate so the stored
		// value and the param are byte-identical for replay comparison.
		t := params.ExpiresAt.UTC().Truncate(time.Second)
		params.ExpiresAt = &t
	}
	if params.Now.IsZero() {
		params.Now = time.Now().UTC()
	} else {
		params.Now = params.Now.UTC()
	}
	return params, nil
}

func deterministicJSON(value []byte) ([]byte, error) {
	decoder := json.NewDecoder(bytes.NewReader(value))
	decoder.UseNumber()
	var decoded any
	if err := decoder.Decode(&decoded); err != nil {
		return nil, err
	}
	return json.Marshal(decoded)
}

func normalizeDecision(params DecideParams) (DecideParams, string, error) {
	params.WorkspaceID = strings.TrimSpace(params.WorkspaceID)
	params.ApprovalID = strings.TrimSpace(params.ApprovalID)
	params.AssignmentID = strings.TrimSpace(params.AssignmentID)
	params.ActorMemberID = strings.TrimSpace(params.ActorMemberID)
	params.ClientDecisionID = strings.TrimSpace(params.ClientDecisionID)
	params.Decision = strings.TrimSpace(params.Decision)
	params.Reason = strings.TrimSpace(params.Reason)
	if params.WorkspaceID == "" || params.ApprovalID == "" || params.AssignmentID == "" || params.ActorMemberID == "" || params.ExpectedVersion <= 0 || params.ClientDecisionID == "" {
		return DecideParams{}, "", storeerr.BadRequest("approval decision identity, actor, expected version, and client id are required")
	}
	if params.Decision != DecisionApproved && params.Decision != DecisionRejected {
		return DecideParams{}, "", storeerr.BadRequest("approval decision must be approved or rejected")
	}
	if len(params.ClientDecisionID) > 128 || len(params.Reason) > 4096 {
		return DecideParams{}, "", storeerr.BadRequest("approval decision payload is too large")
	}
	payload, err := json.Marshal(struct {
		AssignmentID    string `json:"assignmentId"`
		ActorMemberID   string `json:"actorMemberId"`
		ExpectedVersion int64  `json:"expectedVersion"`
		Decision        string `json:"decision"`
		Reason          string `json:"reason"`
	}{params.AssignmentID, params.ActorMemberID, params.ExpectedVersion, params.Decision, params.Reason})
	if err != nil {
		return DecideParams{}, "", fmt.Errorf("marshal approval decision digest: %w", err)
	}
	digest := sha256.Sum256(payload)
	if params.Now.IsZero() {
		params.Now = time.Now().UTC()
	} else {
		params.Now = params.Now.UTC()
	}
	return params, hex.EncodeToString(digest[:]), nil
}

func validDigest(value string) bool {
	if len(value) != sha256.Size*2 {
		return false
	}
	_, err := hex.DecodeString(value)
	return err == nil
}

func validateMembers(
	ctx context.Context,
	queryer coredb.QueryRower,
	workspaceID string,
	applicantID string,
	approvers []string,
	allowAgentApplicant bool,
) error {
	applicantTypes := "'human'"
	if allowAgentApplicant {
		applicantTypes = "'human','agent'"
	}
	var applicantCount int
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM members
WHERE workspace_id=$1 AND status='active' AND type IN (`+applicantTypes+`) AND id=$2
`, workspaceID, applicantID).Scan(&applicantCount); err != nil {
		return fmt.Errorf("validate approval applicant member: %w", err)
	}
	if applicantCount != 1 {
		return storeerr.Conflict("approval applicant is not an allowed active workspace member")
	}

	args := []any{workspaceID}
	placeholders := make([]string, 0, len(approvers))
	for _, id := range approvers {
		args = append(args, id)
		placeholders = append(placeholders, fmt.Sprintf("$%d", len(args)))
	}
	var count int
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(DISTINCT id)
FROM members
WHERE workspace_id=$1 AND type='human' AND status='active'
  AND id IN (`+strings.Join(placeholders, ",")+`)
`, args...).Scan(&count); err != nil {
		return fmt.Errorf("validate approval Human approvers: %w", err)
	}
	unique := map[string]struct{}{}
	for _, id := range approvers {
		unique[id] = struct{}{}
	}
	if count != len(unique) {
		return storeerr.Conflict("approval approver is not an active workspace Human")
	}
	return nil
}

func sameApproverSet(assignments []Assignment, expected []string) bool {
	if len(assignments) != len(expected) {
		return false
	}
	actual := make([]string, 0, len(assignments))
	for _, assignment := range assignments {
		actual = append(actual, assignment.ApproverMemberID)
	}
	sort.Strings(actual)
	return strings.Join(actual, "\x00") == strings.Join(expected, "\x00")
}

func loadInstance(ctx context.Context, queryer coredb.QueryRower, workspaceID, approvalID string, forUpdate bool) (Instance, error) {
	query := instanceSelectSQL + ` WHERE workspace_id=$1 AND id=$2`
	if forUpdate {
		query += ` FOR UPDATE`
	}
	instance, err := scanInstance(queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(approvalID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Instance{}, storeerr.NotFound("approval instance not found")
		}
		return Instance{}, fmt.Errorf("load approval instance: %w", err)
	}
	return instance, nil
}

func loadInstanceBySubject(ctx context.Context, queryer coredb.QueryRower, workspaceID, subjectType, subjectRef string, subjectVersion int64, forUpdate bool) (Instance, bool, error) {
	query := instanceSelectSQL + ` WHERE workspace_id=$1 AND subject_type=$2 AND subject_ref=$3 AND subject_version=$4`
	if forUpdate {
		query += ` FOR UPDATE`
	}
	instance, err := scanInstance(queryer.QueryRow(ctx, query, workspaceID, subjectType, subjectRef, subjectVersion))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Instance{}, false, nil
		}
		return Instance{}, false, fmt.Errorf("load approval instance by subject: %w", err)
	}
	return instance, true, nil
}

func loadInstanceByIdempotencyKey(ctx context.Context, queryer coredb.QueryRower, workspaceID, applicantMemberID, idempotencyKey string, forUpdate bool) (Instance, bool, error) {
	if workspaceID == "" || applicantMemberID == "" || idempotencyKey == "" {
		return Instance{}, false, nil
	}
	query := instanceSelectSQL + ` WHERE workspace_id=$1 AND applicant_member_id=$2 AND idempotency_key=$3`
	if forUpdate {
		query += ` FOR UPDATE`
	}
	instance, err := scanInstance(queryer.QueryRow(ctx, query, workspaceID, applicantMemberID, idempotencyKey))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Instance{}, false, nil
		}
		return Instance{}, false, fmt.Errorf("load approval instance by idempotency key: %w", err)
	}
	return instance, true, nil
}

const instanceSelectSQL = `
SELECT id, workspace_id, subject_type, subject_ref, subject_version,
       payload, payload_digest, COALESCE(idempotency_key,''),
       COALESCE(pending_key,''), applicant_member_id,
       state, decision, decision_reason,
       COALESCE(decided_by_member_id,''), version, projected_version, correlation_id,
       created_at, updated_at, decided_at, expires_at
FROM approval_instances`

func scanInstance(scanner interface{ Scan(...any) error }) (Instance, error) {
	var value Instance
	var payloadSnapshot []byte
	var decidedAt sql.NullTime
	var expiresAt sql.NullTime
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.SubjectType, &value.SubjectRef, &value.SubjectVersion,
		&payloadSnapshot, &value.PayloadDigest, &value.IdempotencyKey, &value.PendingKey,
		&value.ApplicantMemberID, &value.State, &value.Decision, &value.DecisionReason,
		&value.DecidedByMemberID, &value.Version, &value.ProjectedVersion, &value.CorrelationID,
		&value.CreatedAt, &value.UpdatedAt, &decidedAt, &expiresAt,
	); err != nil {
		return Instance{}, err
	}
	value.Payload = append([]byte(nil), payloadSnapshot...)
	if decidedAt.Valid {
		t := decidedAt.Time
		value.DecidedAt = &t
	}
	if expiresAt.Valid {
		t := expiresAt.Time
		value.ExpiresAt = &t
	}
	return value, nil
}

func nullableString(value string) any {
	if value == "" {
		return nil
	}
	return value
}

func nullableJSON(value []byte) any {
	if len(value) == 0 {
		return nil
	}
	return string(value)
}

func nullableTime(value *time.Time) any {
	if value == nil {
		return nil
	}
	return *value
}

func sameTimePointer(left, right *time.Time) bool {
	if left == nil || right == nil {
		return left == nil && right == nil
	}
	return left.Equal(*right)
}

func equalBytes(left, right []byte) bool {
	left = bytes.TrimSpace(left)
	right = bytes.TrimSpace(right)
	if len(left) == 0 || len(right) == 0 {
		return len(left) == len(right)
	}
	var leftValue any
	var rightValue any
	if json.Unmarshal(left, &leftValue) != nil || json.Unmarshal(right, &rightValue) != nil {
		return bytes.Equal(left, right)
	}
	return reflect.DeepEqual(leftValue, rightValue)
}

func loadAssignment(ctx context.Context, queryer coredb.QueryRower, workspaceID, assignmentID string, forUpdate bool) (Assignment, error) {
	query := assignmentSelectSQL + ` WHERE workspace_id=$1 AND id=$2`
	if forUpdate {
		query += ` FOR UPDATE`
	}
	value, err := scanAssignment(queryer.QueryRow(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(assignmentID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return Assignment{}, storeerr.NotFound("approval assignment not found")
		}
		return Assignment{}, fmt.Errorf("load approval assignment: %w", err)
	}
	return value, nil
}

func listAssignments(ctx context.Context, queryer coredb.Queryer, workspaceID, approvalID string, forUpdate bool) ([]Assignment, error) {
	query := assignmentSelectSQL + ` WHERE workspace_id=$1 AND approval_instance_id=$2 ORDER BY approver_member_id ASC, id ASC`
	if forUpdate {
		query += ` FOR UPDATE`
	}
	rows, err := queryer.Query(ctx, query, strings.TrimSpace(workspaceID), strings.TrimSpace(approvalID))
	if err != nil {
		return nil, fmt.Errorf("list approval assignments: %w", err)
	}
	defer rows.Close()
	result := make([]Assignment, 0)
	for rows.Next() {
		value, err := scanAssignment(rows)
		if err != nil {
			return nil, err
		}
		result = append(result, value)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate approval assignments: %w", err)
	}
	return result, nil
}

const assignmentSelectSQL = `
SELECT id, workspace_id, approval_instance_id, approver_member_id,
       status, version, created_at, updated_at, closed_at
FROM approval_assignments`

func scanAssignment(scanner interface{ Scan(...any) error }) (Assignment, error) {
	var value Assignment
	var closedAt sql.NullTime
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.ApprovalInstanceID, &value.ApproverMemberID,
		&value.Status, &value.Version, &value.CreatedAt, &value.UpdatedAt, &closedAt,
	); err != nil {
		return Assignment{}, err
	}
	if closedAt.Valid {
		t := closedAt.Time
		value.ClosedAt = &t
	}
	return value, nil
}

func loadDecisionReceipt(ctx context.Context, queryer coredb.QueryRower, approvalID, actorMemberID, clientDecisionID string) (DecisionReceipt, bool, error) {
	var value DecisionReceipt
	err := queryer.QueryRow(ctx, `
SELECT id, workspace_id, approval_instance_id, assignment_id, actor_member_id,
       client_decision_id, request_digest, decision, reason, result_version, created_at
FROM approval_decision_receipts
WHERE approval_instance_id=$1 AND actor_member_id=$2 AND client_decision_id=$3
LIMIT 1
`, approvalID, actorMemberID, clientDecisionID).Scan(
		&value.ID, &value.WorkspaceID, &value.ApprovalInstanceID, &value.AssignmentID, &value.ActorMemberID,
		&value.ClientDecisionID, &value.RequestDigest, &value.Decision, &value.Reason, &value.ResultVersion, &value.CreatedAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) || coredb.IsNotFound(err) {
			return DecisionReceipt{}, false, nil
		}
		return DecisionReceipt{}, false, fmt.Errorf("load approval decision receipt: %w", err)
	}
	return value, true, nil
}