package review

import (
	"context"
	"errors"
	"fmt"
	"strings"
	"time"

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

// reviewContextRow mirrors the single-table review_contexts (see migration 00240).
type reviewContextRow struct {
	ID              string    `db:"id"`
	WorkspaceID     string    `db:"workspace_id"`
	TargetHash      string    `db:"target_hash"`
	ThreadID        *string   `db:"thread_id"`
	TaskScopeID     *string   `db:"task_scope_id"`
	SourceAgentID   *string   `db:"source_agent_id"`
	MachineID       string    `db:"machine_id"`
	RootPath        string    `db:"root_path"`
	BaseRef         string    `db:"base_ref"`
	HeadRef         string    `db:"head_ref"`
	DisplayName     string    `db:"display_name"`
	PRURL           string    `db:"pr_url"`
	ProjectID       *string   `db:"project_id"`
	CreatedByMember *string   `db:"created_by_member_id"`
	UpdatedByMember *string   `db:"updated_by_member_id"`
	CreatedAt       time.Time `db:"created_at"`
	UpdatedAt       time.Time `db:"updated_at"`
}

func (row reviewContextRow) toModel() agent.ReviewContext {
	out := agent.ReviewContext{
		ID:          row.ID,
		WorkspaceID: row.WorkspaceID,
		MachineID:   row.MachineID,
		RootPath:    row.RootPath,
		BaseRef:     row.BaseRef,
		HeadRef:     row.HeadRef,
		DisplayName: row.DisplayName,
		PRURL:       row.PRURL,
		CreatedAt:   row.CreatedAt,
		UpdatedAt:   row.UpdatedAt,
	}
	if row.ThreadID != nil {
		out.ThreadID = *row.ThreadID
	}
	if row.TaskScopeID != nil {
		out.TaskScopeID = *row.TaskScopeID
	}
	if row.SourceAgentID != nil {
		out.SourceAgentID = *row.SourceAgentID
	}
	if row.ProjectID != nil {
		out.ProjectID = *row.ProjectID
	}
	if row.CreatedByMember != nil {
		out.CreatedByMember = *row.CreatedByMember
	}
	if row.UpdatedByMember != nil {
		out.UpdatedByMember = *row.UpdatedByMember
	}
	return out
}

func normalizeRef(ref string) string { return strings.TrimSpace(ref) }

const reviewContextColumns = "id, workspace_id, target_hash, thread_id, task_scope_id, source_agent_id, machine_id, root_path, " +
	"base_ref, head_ref, display_name, pr_url, project_id, created_by_member_id, updated_by_member_id, created_at, updated_at"

// --- Create ---

// CreateReviewContextForUser creates a review context on behalf of a user. When
// thread-bound, the user must be able to read that thread. The review is bound
// to a Project via get-or-create on its (workspace, machine, root) target tuple
// — callers cannot forge a project_id.
func (store *Store) CreateReviewContextForUser(ctx context.Context, userID string, params agent.UpsertReviewContextParams) (agent.ReviewContext, error) {
	if taskScopeID := strings.TrimSpace(params.TaskScopeID); taskScopeID != "" {
		if err := store.EnsureUserTaskAccess(ctx, userID, params.WorkspaceID, taskScopeID); err != nil {
			return agent.ReviewContext{}, err
		}
	}
	if strings.TrimSpace(params.ThreadID) != "" {
		if _, err := store.loadAuthorizedHumanThreadAccess(ctx, store.db, userID, params.WorkspaceID, params.ThreadID, threadReadAction); err != nil {
			return agent.ReviewContext{}, err
		}
	}
	params.UpdatedByMember = userID
	if params.CreatedByMember == "" {
		params.CreatedByMember = userID
	}
	resolved, err := store.resolveReviewProjectForUser(ctx, userID, params)
	if err != nil {
		return agent.ReviewContext{}, err
	}
	params.ProjectID = resolved
	return store.createReviewContext(ctx, params)
}

// CreateReviewContextForAgent creates a review context on behalf of an
// authenticated agent (the producer). The agent is recorded as source_agent_id.
// The review is bound to a Project via get-or-create on its target tuple —
// callers cannot forge a project_id.
func (store *Store) CreateReviewContextForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, params agent.UpsertReviewContextParams) (agent.ReviewContext, error) {
	params.WorkspaceID = authenticatedAgent.WorkspaceID
	if taskScopeID := strings.TrimSpace(params.TaskScopeID); taskScopeID != "" {
		if err := store.EnsureAgentTaskAccess(ctx, authenticatedAgent, taskScopeID); err != nil {
			return agent.ReviewContext{}, err
		}
	}
	if threadID := strings.TrimSpace(params.ThreadID); threadID != "" {
		if err := store.EnsureAgentThreadAccess(ctx, authenticatedAgent.WorkspaceID, authenticatedAgent.AgentID, threadID); err != nil {
			return agent.ReviewContext{}, err
		}
	}
	if strings.TrimSpace(params.SourceAgentID) == "" {
		params.SourceAgentID = authenticatedAgent.AgentID
	}
	if strings.TrimSpace(params.MachineID) == "" {
		params.MachineID = authenticatedAgent.MachineID
	}
	params.UpdatedByMember = authenticatedAgent.MemberID
	if params.CreatedByMember == "" {
		params.CreatedByMember = authenticatedAgent.MemberID
	}
	resolved, err := store.resolveReviewProjectForAgent(ctx, authenticatedAgent, params)
	if err != nil {
		return agent.ReviewContext{}, err
	}
	params.ProjectID = resolved
	return store.createReviewContext(ctx, params)
}

// resolveReviewProjectForUser resolves the Project id for a review target on
// behalf of a human caller. The ProjectStore and ReviewStore share the same
// *Store, so get-or-create runs in its own transaction and commits before the
// review_context INSERT begins. A failure between the two leaves an orphan
// project row — acceptable: orphan projects are harmless rows with no
// review_context pointing at them, acked only in the project list of their
// owner, who can delete them. Wrapping both in one tx would require threading
// a queryer through getOrCreateProjectForTarget and is deferred.
func (store *Store) resolveReviewProjectForUser(ctx context.Context, userID string, params agent.UpsertReviewContextParams) (string, error) {
	project, err := store.GetOrCreateProjectForUser(ctx, userID, agent.GetOrCreateProjectParams{
		WorkspaceID: params.WorkspaceID,
		MachineID:   params.MachineID,
		RootPath:    params.RootPath,
		Name:        params.DisplayName,
	})
	if err != nil {
		return "", err
	}
	return project.ID, nil
}

// resolveReviewProjectForAgent mirrors resolveReviewProjectForUser for the
// agent-runtime path.
func (store *Store) resolveReviewProjectForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, params agent.UpsertReviewContextParams) (string, error) {
	project, err := store.GetOrCreateProjectForAgent(ctx, authenticatedAgent, agent.GetOrCreateProjectParams{
		WorkspaceID: params.WorkspaceID,
		MachineID:   params.MachineID,
		RootPath:    params.RootPath,
		Name:        params.DisplayName,
	})
	if err != nil {
		return "", err
	}
	return project.ID, nil
}

func (store *Store) createReviewContext(ctx context.Context, params agent.UpsertReviewContextParams) (agent.ReviewContext, error) {
	// Normalize + validate at the workspace boundary. The caller (ForUser /
	// ForAgent entrypoints) has already filled in defaults (workspace / source
	// agent / machine / member IDs), so ValidateReviewParams only needs the
	// raw RootPath + control fields. It rejects non-absolute roots, bad refs,
	// and surfaces typed storeerr.BadRequest for HTTP 400 mapping — NOT a
	// generic fmt.Errorf. The previous inline checks duplicated this logic
	// but emitted untyped errors that the HTTP layer could only answer as 500.
	normalized, err := agent.ValidateReviewParams(params)
	if err != nil {
		return agent.ReviewContext{}, err
	}
	params = normalized
	if strings.TrimSpace(params.ThreadID) != "" && strings.TrimSpace(params.TaskScopeID) != "" {
		return agent.ReviewContext{}, storeerr.BadRequest("review context cannot target both a thread and a task")
	}
	if strings.TrimSpace(params.TaskScopeID) != "" && strings.TrimSpace(params.SourceAgentID) == "" {
		return agent.ReviewContext{}, storeerr.BadRequest("task review context requires sourceAgentId")
	}

	targetHash := reviewContextTargetHashForScope(params.TaskScopeID, params.MachineID, params.RootPath, params.BaseRef, params.HeadRef)
	// The slot and target locks are taken inside the same transaction as the
	// INSERT/UPDATE. A duplicate-key retry is only used for the narrow race in
	// which two transactions both insert a previously absent identity; the
	// retry re-enters the lock path rather than doing a non-atomic reload.
	for attempt := 0; attempt < 3; attempt++ {
		rc, retry, err := store.upsertReviewContextTx(ctx, params, targetHash)
		if retry {
			continue
		}
		if err != nil {
			return agent.ReviewContext{}, err
		}
		return rc, nil
	}
	return agent.ReviewContext{}, storeerr.Conflict("review context identity changed concurrently; retry the request")
}

// upsertReviewContextTx is the linearization point for review-context create
// and Task replacement. Task contexts lock their (workspace, task, source
// agent) slot before the target hash; thread contexts lock the target hash,
// preserving the legacy target-dedup behavior. Both the authoritative target
// tuple and project_id are written in one UPDATE, so a failed statement leaves
// the previous row intact.
func (store *Store) upsertReviewContextTx(ctx context.Context, params agent.UpsertReviewContextParams, targetHash string) (agent.ReviewContext, bool, error) {
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		if coredb.IsRetryableTransactionError(err) {
			return agent.ReviewContext{}, true, nil
		}
		return agent.ReviewContext{}, false, fmt.Errorf("begin review context upsert: %w", err)
	}
	defer tx.Rollback(ctx)

	var existing *reviewContextRow
	var found bool
	if params.TaskScopeID != "" {
		existing, found, err = lockReviewContextWhere(ctx, tx, `workspace_id = $1 AND task_scope_id = $2 AND source_agent_id = $3`, params.WorkspaceID, params.TaskScopeID, params.SourceAgentID)
	} else {
		existing, found, err = lockReviewContextWhere(ctx, tx, `workspace_id = $1 AND target_hash = $2`, params.WorkspaceID, targetHash)
	}
	if err != nil {
		if coredb.IsRetryableTransactionError(err) {
			return agent.ReviewContext{}, true, nil
		}
		return agent.ReviewContext{}, false, err
	}

	// Lock the target owner after the slot so concurrent replacements of one
	// Task serialize in slot order. For an existing same-target row this is a
	// harmless second lock on the same row.
	targetOwner, targetFound, err := lockReviewContextWhere(ctx, tx, `workspace_id = $1 AND target_hash = $2`, params.WorkspaceID, targetHash)
	if err != nil {
		if coredb.IsRetryableTransactionError(err) {
			return agent.ReviewContext{}, true, nil
		}
		return agent.ReviewContext{}, false, err
	}
	if targetFound && (!found || targetOwner.ID != existing.ID) {
		return agent.ReviewContext{}, false, storeerr.Conflict("review target already bound to another review context")
	}

	if params.TaskScopeID != "" && found && existing.TargetHash != targetHash {
		// This is the atomic Task/source-agent replacement path. The target
		// owner check above guarantees that changing target_hash cannot steal a
		// target from another context.
		if err := updateReviewContextTx(ctx, tx, existing.ID, params, targetHash); err != nil {
			if coredb.IsUniqueViolation(err) {
				return agent.ReviewContext{}, false, storeerr.Conflict("review target already bound to another review context")
			}
			if coredb.IsRetryableTransactionError(err) {
				return agent.ReviewContext{}, true, nil
			}
			return agent.ReviewContext{}, false, fmt.Errorf("replace review context: %w", err)
		}
	} else if found {
		// Same-target refresh, including legacy Thread refresh, preserves the
		// stable context ID while replacing all request-owned metadata.
		if err := updateReviewContextTx(ctx, tx, existing.ID, params, targetHash); err != nil {
			if coredb.IsUniqueViolation(err) {
				return agent.ReviewContext{}, false, storeerr.Conflict("review target already bound to another review context")
			}
			if coredb.IsRetryableTransactionError(err) {
				return agent.ReviewContext{}, true, nil
			}
			return agent.ReviewContext{}, false, fmt.Errorf("refresh review context: %w", err)
		}
	} else {
		id, idErr := identifiers.NewEntityID("rcx")
		if idErr != nil {
			return agent.ReviewContext{}, false, fmt.Errorf("generate review context id: %w", idErr)
		}
		if err := insertReviewContextTx(ctx, tx, id, params, targetHash); err != nil {
			if coredb.IsUniqueViolation(err) || coredb.IsRetryableTransactionError(err) {
				// A concurrent first insert won one of the unique identities, or
				// TiDB aborted this transaction on a write conflict. Roll back and
				// re-enter the lock path so the winner is either refreshed (same
				// slot/target) or reported as a typed conflict.
				return agent.ReviewContext{}, true, nil
			}
			return agent.ReviewContext{}, false, fmt.Errorf("insert review context: %w", err)
		}
		existing = &reviewContextRow{ID: id}
	}

	row, err := lockReviewContextByID(ctx, tx, params.WorkspaceID, existing.ID)
	if err != nil {
		if coredb.IsRetryableTransactionError(err) {
			return agent.ReviewContext{}, true, nil
		}
		return agent.ReviewContext{}, false, fmt.Errorf("load review context in upsert: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		if coredb.IsRetryableTransactionError(err) {
			return agent.ReviewContext{}, true, nil
		}
		return agent.ReviewContext{}, false, fmt.Errorf("commit review context upsert: %w", err)
	}
	return row.toModel(), false, nil
}

func insertReviewContextTx(ctx context.Context, tx coredb.Tx, id string, params agent.UpsertReviewContextParams, targetHash string) error {
	now := time.Now().UTC()
	_, err := tx.Exec(ctx, `
INSERT INTO review_contexts (id, workspace_id, target_hash, thread_id, task_scope_id, source_agent_id, machine_id, root_path,
  base_ref, head_ref, display_name, pr_url, project_id, created_by_member_id, updated_by_member_id, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
`, id, params.WorkspaceID, targetHash, nilIfEmpty(params.ThreadID), nilIfEmpty(params.TaskScopeID), nilIfEmpty(params.SourceAgentID),
		params.MachineID, params.RootPath, params.BaseRef, params.HeadRef, params.DisplayName, params.PRURL, nilIfEmpty(params.ProjectID),
		nilIfEmpty(params.CreatedByMember), nilIfEmpty(params.UpdatedByMember), now, now)
	return err
}

func updateReviewContextTx(ctx context.Context, tx coredb.Tx, id string, params agent.UpsertReviewContextParams, targetHash string) error {
	_, err := tx.Exec(ctx, `
UPDATE review_contexts
SET target_hash = $3, thread_id = $4, task_scope_id = $5, source_agent_id = $6,
    machine_id = $7, root_path = $8, base_ref = $9, head_ref = $10,
    display_name = $11, pr_url = $12, project_id = COALESCE($13, project_id),
    updated_by_member_id = $14, updated_at = $15
WHERE workspace_id = $1 AND id = $2
`, params.WorkspaceID, id, targetHash, nilIfEmpty(params.ThreadID), nilIfEmpty(params.TaskScopeID), nilIfEmpty(params.SourceAgentID),
		params.MachineID, params.RootPath, params.BaseRef, params.HeadRef, params.DisplayName, params.PRURL, nilIfEmpty(params.ProjectID),
		nilIfEmpty(params.UpdatedByMember), time.Now().UTC())
	return err
}

func lockReviewContextByID(ctx context.Context, queryer coredb.QueryRower, workspaceID, id string) (*reviewContextRow, error) {
	row, found, err := lockReviewContextWhere(ctx, queryer, `workspace_id = $1 AND id = $2`, workspaceID, id)
	if err != nil {
		return nil, err
	}
	if !found {
		return nil, ErrNotFound
	}
	return row, nil
}

func lockReviewContextWhere(ctx context.Context, queryer coredb.QueryRower, predicate string, args ...any) (*reviewContextRow, bool, error) {
	row := &reviewContextRow{}
	if err := queryer.QueryRow(ctx, `SELECT `+reviewContextColumns+` FROM review_contexts WHERE `+predicate+` FOR UPDATE`, args...).Scan(
		&row.ID, &row.WorkspaceID, &row.TargetHash, &row.ThreadID, &row.TaskScopeID, &row.SourceAgentID, &row.MachineID, &row.RootPath,
		&row.BaseRef, &row.HeadRef, &row.DisplayName, &row.PRURL, &row.ProjectID, &row.CreatedByMember, &row.UpdatedByMember,
		&row.CreatedAt, &row.UpdatedAt,
	); err != nil {
		if coredb.IsNotFound(err) {
			return nil, false, nil
		}
		return nil, false, fmt.Errorf("lock review context: %w", err)
	}
	return row, true, nil
}

func (store *Store) getReviewContextByTarget(ctx context.Context, workspaceID string, targetHash string) (agent.ReviewContext, error) {
	row := store.db.QueryRow(ctx, `
SELECT `+reviewContextColumns+`
FROM review_contexts
WHERE workspace_id = $1 AND target_hash = $2
`, workspaceID, targetHash)
	var r reviewContextRow
	if err := scanReviewContextRow(row, &r); err != nil {
		return agent.ReviewContext{}, fmt.Errorf("get review context by target: %w", err)
	}
	return r.toModel(), nil
}

// --- Read ---

// GetReviewContext loads a review context by id (no access check).
func (store *Store) GetReviewContext(ctx context.Context, workspaceID string, id string) (*agent.ReviewContext, error) {
	row := store.db.QueryRow(ctx, `
SELECT `+reviewContextColumns+`
FROM review_contexts
WHERE workspace_id = $1 AND id = $2
`, workspaceID, id)
	var r reviewContextRow
	if err := scanReviewContextRow(row, &r); err != nil {
		return nil, fmt.Errorf("get review context: %w", err)
	}
	rc := r.toModel()
	return &rc, nil
}

// GetReviewContextForUser loads a review context by id and authorizes the user
// against it: visible if the user can view its Project, read its thread, or
// operate its source agent. Returns ErrNotFound if absent or not visible, so a
// caller cannot probe an arbitrary id within the workspace.
func (store *Store) GetReviewContextForUser(ctx context.Context, userID string, workspaceID string, id string) (*agent.ReviewContext, error) {
	rc, err := store.GetReviewContext(ctx, workspaceID, id)
	if err != nil {
		return nil, err
	}
	if rc == nil {
		return nil, ErrNotFound
	}
	if taskScopeID := strings.TrimSpace(rc.TaskScopeID); taskScopeID != "" {
		if err := store.EnsureUserTaskAccess(ctx, userID, workspaceID, taskScopeID); err != nil {
			if errors.Is(err, ErrNotFound) {
				return nil, ErrNotFound
			}
			return nil, err
		}
		return rc, nil
	}
	projectVisible, err := store.canViewReviewProject(ctx, userID, workspaceID, rc.ProjectID)
	if err != nil {
		return nil, err
	}
	if projectVisible {
		return rc, nil
	}
	if strings.TrimSpace(rc.ThreadID) != "" {
		if _, err := store.loadAuthorizedHumanThreadAccess(ctx, store.db, userID, workspaceID, rc.ThreadID, threadReadAction); err == nil {
			return rc, nil
		}
	}
	if strings.TrimSpace(rc.SourceAgentID) != "" {
		if _, err := store.requireAgentOperation(ctx, userID, workspaceID, rc.SourceAgentID); err == nil {
			return rc, nil
		}
	}
	return nil, ErrNotFound
}

func (store *Store) canViewReviewProject(ctx context.Context, userID string, workspaceID string, projectID string) (bool, error) {
	projectID = strings.TrimSpace(projectID)
	if projectID == "" {
		return false, nil
	}
	row, err := store.loadProjectRow(ctx, store.db, workspaceID, projectID)
	if err != nil {
		if errors.Is(err, storeerr.ErrNotFound) {
			return false, nil
		}
		return false, err
	}
	role, err := store.resolveProjectRole(ctx, store.db, userID, workspaceID, row)
	if err != nil {
		return false, err
	}
	return roleSatisfies(role, agent.ProjectRoleView), nil
}

// ListReviewContextsForThread returns the review contexts bound to a thread,
// after authorizing the user can read that thread. Backs the Thread Detail tab.
func (store *Store) ListReviewContextsForThread(ctx context.Context, userID string, workspaceID string, threadID string) ([]agent.ReviewContext, error) {
	if _, err := store.loadAuthorizedHumanThreadAccess(ctx, store.db, userID, workspaceID, threadID, threadReadAction); err != nil {
		return nil, err
	}
	return store.listReviewContexts(ctx, workspaceID, "thread_id = $2", threadID)
}

// ListReviewContextsForTask returns task-bound contexts after checking the
// caller can read the canonical Task. The task scope is resolved through the
// board's active Channel authorization; no workspace-wide context enumeration
// is used.
func (store *Store) ListReviewContextsForTask(ctx context.Context, userID string, workspaceID string, taskScopeID string) ([]agent.ReviewContext, error) {
	if err := store.EnsureUserTaskAccess(ctx, userID, workspaceID, taskScopeID); err != nil {
		return nil, err
	}
	return store.listReviewContexts(ctx, workspaceID, "task_scope_id = $2", taskScopeID)
}

func (store *Store) ListReviewContextsForTaskID(ctx context.Context, userID string, workspaceID string, taskID string) ([]agent.ReviewContext, error) {
	var taskScopeID string
	if err := store.db.QueryRow(ctx, `
SELECT task.task_scope_id
FROM tasks task
JOIN task_boards board ON board.id = task.board_id
JOIN scopes task_scope ON task_scope.id = task.task_scope_id AND task_scope.type = 'task'
WHERE board.workspace_id = $1 AND task.id = $2
LIMIT 1
`, workspaceID, strings.TrimSpace(taskID)).Scan(&taskScopeID); err != nil {
		if coredb.IsNotFound(err) {
			return nil, ErrNotFound
		}
		return nil, fmt.Errorf("resolve review task: %w", err)
	}
	return store.ListReviewContextsForTask(ctx, userID, workspaceID, taskScopeID)
}

// ListReviewContextsForTaskForAgent is the agent-runtime equivalent of the
// human task read path.
func (store *Store) ListReviewContextsForTaskForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, taskScopeID string) ([]agent.ReviewContext, error) {
	if err := store.EnsureAgentTaskAccess(ctx, authenticatedAgent, taskScopeID); err != nil {
		return nil, err
	}
	return store.listReviewContexts(ctx, authenticatedAgent.WorkspaceID, "task_scope_id = $2", taskScopeID)
}

// EnsureAgentTaskAccess authorizes a canonical Task scope without enumerating
// tasks or falling back to a prior conversation target.
func (store *Store) EnsureAgentTaskAccess(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, taskScopeID string) error {
	if strings.TrimSpace(taskScopeID) == "" {
		return ErrNotFound
	}
	_, err := access.AuthorizeActiveAgentTaskBoardChannel(ctx, store.db, authenticatedAgent.WorkspaceID, authenticatedAgent.MemberID, strings.TrimSpace(taskScopeID))
	if errors.Is(err, access.ErrForbidden) || errors.Is(err, access.ErrNotFound) {
		return ErrNotFound
	}
	return err
}

// EnsureUserTaskAccess authorizes a human against the canonical Task's active
// board channel. Invalid, cross-workspace, and inaccessible scopes all use the
// same not-found result at the review boundary.
func (store *Store) EnsureUserTaskAccess(ctx context.Context, userID string, workspaceID string, taskScopeID string) error {
	if strings.TrimSpace(taskScopeID) == "" {
		return ErrNotFound
	}
	resolved, err := access.ResolveActiveTaskBoardChannel(ctx, store.db, workspaceID, strings.TrimSpace(taskScopeID))
	if errors.Is(err, access.ErrForbidden) || errors.Is(err, access.ErrNotFound) {
		return ErrNotFound
	}
	if err != nil {
		return fmt.Errorf("resolve review task scope: %w", err)
	}
	_, err = access.LoadAuthorizedHumanChannelAccess(ctx, store.db, userID, workspaceID, resolved.ChannelID, access.HumanAction{Resource: access.ResourceChannel, Action: access.ActionRead})
	if errors.Is(err, access.ErrForbidden) || errors.Is(err, access.ErrNotFound) {
		return ErrNotFound
	}
	return err
}

// EnsureAgentThreadAccess gates agent-runtime review-context reads and writes
// through the canonical visible-scope relation. Agent membership on the reply
// scope remains participation state only; it must not authorize content or
// Review Context access when the parent Channel authority is absent.
func (store *Store) EnsureAgentThreadAccess(ctx context.Context, workspaceID string, agentID string, threadID string) error {
	workspaceID = strings.TrimSpace(workspaceID)
	agentID = strings.TrimSpace(agentID)
	threadID = strings.TrimSpace(threadID)
	if workspaceID == "" || agentID == "" || threadID == "" {
		return ErrNotFound
	}
	var memberID string
	if err := store.db.QueryRow(ctx, `
SELECT member_id
FROM agents
WHERE workspace_id = $1
  AND id = $2
  AND status <> 'archived'
`, workspaceID, agentID).Scan(&memberID); err != nil {
		if coredb.IsNotFound(err) {
			return ErrNotFound
		}
		return fmt.Errorf("load agent member for review context access: %w", err)
	}
	visible, err := message.NewSQLVisibleScopeAuthorizer(store.db).CanReadVisibleScopesForAgent(
		ctx,
		workspaceID,
		strings.TrimSpace(memberID),
		agentID,
		[]string{threadID},
	)
	if err != nil {
		return fmt.Errorf("authorize agent thread review context access: %w", err)
	}
	if access, ok := visible[threadID]; ok && access.Readable {
		return nil
	}
	return ErrNotFound
}

// ListReviewContextsForThreadForAgent returns the review contexts bound to a
// thread for an authenticated agent. Callers must check agent thread access
// before listing.
func (store *Store) ListReviewContextsForThreadForAgent(ctx context.Context, workspaceID string, threadID string) ([]agent.ReviewContext, error) {
	return store.listReviewContexts(ctx, workspaceID, "thread_id = $2", threadID)
}

// ListReviewContextsForThreadForAgentPage returns one bounded, stable page for
// an already-authorized Agent thread. The thread membership check remains in
// the API handler before this method is called.
func (store *Store) ListReviewContextsForThreadForAgentPage(ctx context.Context, workspaceID string, threadID string, options agent.ReviewContextListOptions) (agent.ReviewContextPage, error) {
	return store.listReviewContextPage(ctx, workspaceID, "thread_id = $2", options, threadID)
}

// ListReviewContextsForProject returns the review contexts attached to a
// Project. Callers MUST first authorize the caller against the Project via
// ProjectStore.GetProjectForUser (view+ role) — this method intentionally
// does no access check on its own, mirroring the ListReviewContextsForThread
// contract where the caller is responsible for thread access.
//
// Returns contexts ordered by updated_at DESC, matching ListVisibleReviewContextsForUser.
func (store *Store) ListReviewContextsForProject(ctx context.Context, workspaceID string, projectID string) ([]agent.ReviewContext, error) {
	return store.listReviewContexts(ctx, workspaceID, "project_id = $2", projectID)
}

// ListVisibleReviewContextsForUser returns the review contexts visible to a
// human in the Explorer: the union of Project visibility, readable threads,
// and operable source Agents.
func (store *Store) ListVisibleReviewContextsForUser(ctx context.Context, userID string, workspaceID string) ([]agent.ReviewContext, error) {
	all, err := store.listReviewContexts(ctx, workspaceID, "TRUE")
	if err != nil {
		return nil, err
	}
	projects, err := store.ListProjectsForUser(ctx, userID, workspaceID)
	if err != nil {
		return nil, err
	}
	visibleProjectIDs := make(map[string]struct{}, len(projects))
	for _, project := range projects {
		visibleProjectIDs[project.ID] = struct{}{}
	}
	type threadKey struct{ id string }
	threadAccess := make(map[threadKey]bool)
	canReadThread := func(threadID string) bool {
		k := threadKey{threadID}
		if v, ok := threadAccess[k]; ok {
			return v
		}
		_, accessErr := store.loadAuthorizedHumanThreadAccess(ctx, store.db, userID, workspaceID, threadID, threadReadAction)
		ok := accessErr == nil
		threadAccess[k] = ok
		return ok
	}
	agentOperable := make(map[string]bool)
	canOperateAgent := func(agentID string) bool {
		if agentID == "" {
			return false
		}
		if v, ok := agentOperable[agentID]; ok {
			return v
		}
		_, opErr := store.requireAgentOperation(ctx, userID, workspaceID, agentID)
		ok := opErr == nil
		agentOperable[agentID] = ok
		return ok
	}
	visible := make([]agent.ReviewContext, 0, len(all))
	for _, rc := range all {
		// Task contexts are authorized only by the canonical active Task
		// authority. Do this before the Project union so a user who can see
		// the repository Project cannot inherit access to a Task context.
		if rc.TaskScopeID != "" {
			if err := store.EnsureUserTaskAccess(ctx, userID, workspaceID, rc.TaskScopeID); err == nil {
				visible = append(visible, rc)
			} else if !errors.Is(err, ErrNotFound) {
				return nil, err
			}
			continue
		}
		if _, ok := visibleProjectIDs[rc.ProjectID]; ok {
			visible = append(visible, rc)
			continue
		}
		if rc.ThreadID != "" && canReadThread(rc.ThreadID) {
			visible = append(visible, rc)
			continue
		}
		if canOperateAgent(rc.SourceAgentID) {
			visible = append(visible, rc)
		}
	}
	return visible, nil
}

// ListVisibleReviewContextsForAgent returns the review contexts visible to an
// authenticated agent: source_agent_id == self (the agent's own output). TODO:
// also include thread-bound contexts where the agent is a thread participant —
// that needs an agent-thread-participation check (see privateagent.
// publicAgentParticipatesInThread); wiring it requires resolving a
// review->privateagent dependency, deferred for review.
func (store *Store) ListVisibleReviewContextsForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent) ([]agent.ReviewContext, error) {
	return store.listReviewContexts(ctx, authenticatedAgent.WorkspaceID, "source_agent_id = $2", authenticatedAgent.AgentID)
}

func (store *Store) listReviewContexts(ctx context.Context, workspaceID string, condition string, args ...any) ([]agent.ReviewContext, error) {
	rows, err := store.db.Query(ctx, `
SELECT `+reviewContextColumns+`
FROM review_contexts
WHERE workspace_id = $1 AND `+condition+`
ORDER BY updated_at DESC, id DESC
`, append([]any{workspaceID}, args...)...)
	if err != nil {
		return nil, fmt.Errorf("list review contexts: %w", err)
	}
	defer rows.Close()
	var result []agent.ReviewContext
	for rows.Next() {
		var row reviewContextRow
		if err := scanReviewContextRow(rows, &row); err != nil {
			return nil, fmt.Errorf("scan review context: %w", err)
		}
		result = append(result, row.toModel())
	}
	return result, rows.Err()
}

func (store *Store) listReviewContextPage(ctx context.Context, workspaceID string, condition string, options agent.ReviewContextListOptions, args ...any) (agent.ReviewContextPage, error) {
	limit := options.Limit
	if limit <= 0 {
		limit = 20
	}
	if limit > 100 {
		limit = 100
	}
	workspaceID = strings.TrimSpace(workspaceID)
	queryArgs := append([]any{workspaceID}, args...)
	where := "workspace_id = $1 AND " + condition
	cursorID := strings.TrimSpace(options.CursorID)
	if !options.CursorUpdatedAt.IsZero() || cursorID != "" {
		if options.CursorUpdatedAt.IsZero() || cursorID == "" {
			return agent.ReviewContextPage{}, storeerr.BadRequest("invalid review context cursor")
		}
		updatedAtArg := len(queryArgs) + 1
		queryArgs = append(queryArgs, options.CursorUpdatedAt.UTC())
		idArg := len(queryArgs) + 1
		queryArgs = append(queryArgs, cursorID)
		where += fmt.Sprintf(" AND (updated_at < $%d OR (updated_at = $%d AND id < $%d))", updatedAtArg, updatedAtArg, idArg)
	}
	limitArg := len(queryArgs) + 1
	queryArgs = append(queryArgs, limit+1)
	rows, err := store.db.Query(ctx, `
SELECT `+reviewContextColumns+`
FROM review_contexts
WHERE `+where+`
ORDER BY updated_at DESC, id DESC
LIMIT `+fmt.Sprintf("$%d", limitArg), queryArgs...)
	if err != nil {
		return agent.ReviewContextPage{}, fmt.Errorf("list review contexts: %w", err)
	}
	defer rows.Close()
	result := make([]agent.ReviewContext, 0, limit+1)
	for rows.Next() {
		var row reviewContextRow
		if err := scanReviewContextRow(rows, &row); err != nil {
			return agent.ReviewContextPage{}, fmt.Errorf("scan review context: %w", err)
		}
		result = append(result, row.toModel())
	}
	if err := rows.Err(); err != nil {
		return agent.ReviewContextPage{}, fmt.Errorf("iterate review contexts: %w", err)
	}
	hasMore := len(result) > limit
	if hasMore {
		result = result[:limit]
	}
	return agent.ReviewContextPage{Contexts: result, HasMore: hasMore}, nil
}

// --- Update / Delete ---

// UpdateReviewContextForUser updates a review context's target fields and
// recomputes target_hash atomically. The caller must be able to read the
// context (thread read access when thread-bound, or operate the source agent
// otherwise).
//
// Identity semantics: target_hash is the dedup key. If the new target tuple
// (machine/root/base/head) is currently owned by a DIFFERENT id within the
// same workspace, the update is rejected with storeerr.ErrConflict — the
// existing row is returned unchanged. Silently overwriting or upserting a
// second row would either lose the original binding or split the identity,
// both of which break the "one row per target" contract the review cache
// relies on. If the new tuple collides with THIS row's existing identity
// (no-op ref-only update), the UPDATE proceeds normally.
func (store *Store) UpdateReviewContextForUser(ctx context.Context, userID string, workspaceID string, id string, params agent.UpsertReviewContextParams) (*agent.ReviewContext, error) {
	existing, err := store.GetReviewContext(ctx, workspaceID, id)
	if err != nil {
		return nil, err
	}
	if existing == nil {
		return nil, ErrNotFound
	}
	if err := store.ensureReviewContextAccess(ctx, userID, workspaceID, *existing); err != nil {
		return nil, err
	}
	// Carry forward workspace/id scope from the loaded row; ValidateReviewParams
	// needs the canonical RootPath (raw, untrimmed) but trims the control/enum
	// fields. Defaults for member IDs come from the existing row when the
	// caller didn't supply them.
	if strings.TrimSpace(params.WorkspaceID) == "" {
		params.WorkspaceID = existing.WorkspaceID
	}
	if strings.TrimSpace(params.TaskScopeID) == "" {
		params.TaskScopeID = existing.TaskScopeID
	}
	if strings.TrimSpace(params.UpdatedByMember) == "" {
		params.UpdatedByMember = userID
	}
	// DisplayName omit = preserve existing. MUST run BEFORE ValidateReviewParams:
	// validate fills empty DisplayName with defaultReviewDisplayName(rootPath),
	// so checking "DisplayName == ''" AFTER validate is dead code and the
	// default would always win, silently overwriting the omit=preserve
	// contract. Doing the preserve first means: param supplied → use param;
	// param omitted + existing non-empty → use existing; param omitted +
	// existing empty (legacy row) → validate backfills default from rootPath.
	//
	// The omit check uses TrimSpace so a whitespace-only DisplayName is treated
	// as omit too. The previous contract trimmed before comparing, so a caller
	// that sent "   " would have preserved existing; without TrimSpace here,
	// validate would see the whitespace as non-empty, skip the default path,
	// and store the raw whitespace bytes — diverging from the documented
	// "empty → preserve" semantics.
	if strings.TrimSpace(params.DisplayName) == "" {
		params.DisplayName = existing.DisplayName
	}
	normalized, err := agent.ValidateReviewParams(params)
	if err != nil {
		return nil, err
	}
	params = normalized

	// Recompute target_hash from the normalized tuple. The UNIQUE(workspace_id,
	// target_hash) index is the dedup constraint; we explicitly check it here
	// BEFORE the UPDATE so a collision surfaces as a typed ErrConflict (HTTP
	// 409) rather than as a raw DB duplicate-key error (which would leak
	// through as HTTP 500 with no stable code).
	newTargetHash := reviewContextTargetHashForScope(params.TaskScopeID, params.MachineID, params.RootPath, params.BaseRef, params.HeadRef)
	existingHash := reviewContextTargetHashForScope(existing.TaskScopeID, existing.MachineID, existing.RootPath, existing.BaseRef, existing.HeadRef)
	if newTargetHash != existingHash {
		ownerID, err := store.getReviewContextIDByTarget(ctx, workspaceID, newTargetHash)
		if err != nil {
			return nil, err
		}
		if ownerID != "" && ownerID != id {
			return nil, storeerr.Conflict("review target already bound to another review context")
		}
	}

	// Re-bind Project when the (machine, root) target tuple changes. Project is
	// keyed on (workspace, machine, root) — refs are NOT part of project
	// membership — so a base/head-only edit preserves the existing project_id.
	// When machine or root changes the old project_id would dangle under the
	// wrong target, so we re-resolve via get-or-create (planner seq=531
	// guardrail 1: "不能保留旧 project_id"). The new project row commits in its
	// own tx before the UPDATE below; a failure between the two leaves an orphan
	// project, which is harmless (see resolveReviewProjectForUser doc).
	machineRootChanged := params.MachineID != existing.MachineID || params.RootPath != existing.RootPath
	if machineRootChanged {
		resolved, err := store.resolveReviewProjectForUser(ctx, userID, params)
		if err != nil {
			return nil, err
		}
		params.ProjectID = resolved
	} else {
		params.ProjectID = existing.ProjectID
	}

	// Test-only hook: deterministically forces the SQL 1062 path by letting a
	// test insert a rival owner row on the same connection between the pre-check
	// and the UPDATE. Nil in production. See Store.onUpdatePreCheckPassed doc.
	if store.onUpdatePreCheckPassed != nil {
		store.onUpdatePreCheckPassed(ctx, workspaceID, newTargetHash, id)
	}

	if _, err := store.db.Exec(ctx, `
UPDATE review_contexts
SET machine_id = $2, root_path = $3, base_ref = $4, head_ref = $5, display_name = $6,
    pr_url = $7, target_hash = $8, project_id = $9, updated_by_member_id = $10, updated_at = $11
WHERE workspace_id = $1 AND id = $12
`,
		workspaceID, params.MachineID, params.RootPath,
		params.BaseRef, params.HeadRef, params.DisplayName, params.PRURL,
		newTargetHash, nilIfEmpty(params.ProjectID),
		nilIfEmpty(params.UpdatedByMember), time.Now().UTC(), id,
	); err != nil {
		// The explicit pre-check above is a TOCTOU guard, not an atomic
		// guarantee: two concurrent UPDATEs targeting the same unowned
		// identity can both pass the SELECT owner check and then race on
		// the UNIQUE(workspace_id, target_hash) index. Exactly one UPDATE
		// wins; the loser surfaces a 1062 duplicate-key error. Without
		// this branch, the loser's error wraps as a generic "update review
		// context" failure and the HTTP layer maps it to 500. Mapping the
		// duplicate-key to the SAME typed ErrConflict the pre-check uses
		// surfaces the loser as a stable HTTP 409 instead, with the row
		// left untouched (single-statement UPDATE atomicity guarantees the
		// loser's row is not partially written).
		if coredb.IsUniqueViolation(err) {
			return nil, storeerr.Conflict("review target already bound to another review context")
		}
		return nil, fmt.Errorf("update review context: %w", err)
	}
	return store.GetReviewContext(ctx, workspaceID, id)
}

// getReviewContextIDByTarget returns the id of the row that currently owns the
// given target_hash within the workspace, or "" when no row owns it. Used by
// UpdateReviewContextForUser to detect identity collision before mutating.
// "No rows" is the legitimate "unowned" outcome and is reported as ("", nil);
// any other error is a real DB failure and propagates as-is.
func (store *Store) getReviewContextIDByTarget(ctx context.Context, workspaceID string, targetHash string) (string, error) {
	var id string
	err := store.db.QueryRow(ctx, `
SELECT id FROM review_contexts
WHERE workspace_id = $1 AND target_hash = $2
`, workspaceID, targetHash).Scan(&id)
	if err == nil {
		return id, nil
	}
	if coredb.IsNotFound(err) {
		return "", nil
	}
	return "", fmt.Errorf("lookup review context by target: %w", err)
}

// DeleteReviewContextForUser deletes a review context by id after authorizing
// the caller against its thread (when thread-bound) or source agent.
func (store *Store) DeleteReviewContextForUser(ctx context.Context, userID string, workspaceID string, id string) error {
	existing, err := store.GetReviewContext(ctx, workspaceID, id)
	if err != nil {
		return err
	}
	if existing == nil {
		return ErrNotFound
	}
	if err := store.ensureReviewContextAccess(ctx, userID, workspaceID, *existing); err != nil {
		return err
	}
	return store.deleteReviewContext(ctx, workspaceID, id)
}

// DeleteReviewContextForAgent deletes a review context by id for an
// authenticated agent. The agent may delete its own output (source_agent_id ==
// self); thread-bound contexts require thread management (deferred — currently
// limited to the agent's own output for the agent-caller path).
func (store *Store) DeleteReviewContextForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, id string) error {
	existing, err := store.GetReviewContext(ctx, authenticatedAgent.WorkspaceID, id)
	if err != nil {
		return err
	}
	if existing == nil {
		return ErrNotFound
	}
	if strings.TrimSpace(existing.SourceAgentID) != authenticatedAgent.AgentID {
		return ErrNotFound
	}
	if taskScopeID := strings.TrimSpace(existing.TaskScopeID); taskScopeID != "" {
		if err := store.EnsureAgentTaskAccess(ctx, authenticatedAgent, taskScopeID); err != nil {
			return ErrNotFound
		}
	}
	return store.deleteReviewContext(ctx, authenticatedAgent.WorkspaceID, id)
}

func (store *Store) deleteReviewContext(ctx context.Context, workspaceID string, id string) error {
	if _, err := store.db.Exec(ctx, `DELETE FROM review_contexts WHERE workspace_id = $1 AND id = $2`, workspaceID, id); err != nil {
		return fmt.Errorf("delete review context: %w", err)
	}
	return nil
}

// --- access ---

// ensureReviewContextAccess authorizes a human user to manage a review context:
// thread read access when it is thread-bound, otherwise the ability to operate
// its source agent.
func (store *Store) ensureReviewContextAccess(ctx context.Context, userID string, workspaceID string, rc agent.ReviewContext) error {
	if taskScopeID := strings.TrimSpace(rc.TaskScopeID); taskScopeID != "" {
		return store.EnsureUserTaskAccess(ctx, userID, workspaceID, taskScopeID)
	}
	if strings.TrimSpace(rc.ThreadID) != "" {
		_, err := store.loadAuthorizedHumanThreadAccess(ctx, store.db, userID, workspaceID, rc.ThreadID, threadReadAction)
		return err
	}
	if strings.TrimSpace(rc.SourceAgentID) != "" {
		_, err := store.requireAgentOperation(ctx, userID, workspaceID, rc.SourceAgentID)
		return err
	}
	return ErrNotFound
}

type reviewScanner interface {
	Scan(dest ...any) error
}

func scanReviewContextRow(scanner reviewScanner, row *reviewContextRow) error {
	return scanner.Scan(
		&row.ID, &row.WorkspaceID, &row.TargetHash, &row.ThreadID, &row.TaskScopeID, &row.SourceAgentID, &row.MachineID, &row.RootPath,
		&row.BaseRef, &row.HeadRef, &row.DisplayName, &row.PRURL, &row.ProjectID,
		&row.CreatedByMember, &row.UpdatedByMember, &row.CreatedAt, &row.UpdatedAt,
	)
}

func nilIfEmpty(s string) any {
	if s == "" {
		return nil
	}
	return s
}