package review

import (
	"context"
	"crypto/sha256"
	"encoding/hex"
	"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/storeerr"
)

// projectRow mirrors the projects table introduced by migration 00243.
type projectRow struct {
	ID              string    `db:"id"`
	WorkspaceID     string    `db:"workspace_id"`
	MachineID       string    `db:"machine_id"`
	RootPath        string    `db:"root_path"`
	TargetHash      string    `db:"target_hash"`
	Name            string    `db:"name"`
	Description     string    `db:"description"`
	Visibility      string    `db:"visibility"`
	OwnerUserID     string    `db:"owner_user_id"`
	CreatedByUserID string    `db:"created_by_user_id"`
	UpdatedByUserID string    `db:"updated_by_user_id"`
	CreatedAt       time.Time `db:"created_at"`
	UpdatedAt       time.Time `db:"updated_at"`
}

func (row projectRow) toModel() agent.Project {
	return agent.Project{
		ID:              row.ID,
		WorkspaceID:     row.WorkspaceID,
		MachineID:       row.MachineID,
		RootPath:        row.RootPath,
		Name:            row.Name,
		Description:     row.Description,
		Visibility:      row.Visibility,
		OwnerUserID:     row.OwnerUserID,
		CreatedByUserID: row.CreatedByUserID,
		UpdatedByUserID: row.UpdatedByUserID,
		CreatedAt:       row.CreatedAt,
		UpdatedAt:       row.UpdatedAt,
	}
}

// projectGrantRow mirrors the project_grants table.
type projectGrantRow struct {
	ProjectID string    `db:"project_id"`
	MemberID  string    `db:"member_id"`
	Role      string    `db:"role"`
	CreatedAt time.Time `db:"created_at"`
	UpdatedAt time.Time `db:"updated_at"`
}

func (row projectGrantRow) toModel() agent.ProjectGrant {
	return agent.ProjectGrant{
		ProjectID: row.ProjectID,
		MemberID:  row.MemberID,
		Role:      row.Role,
		CreatedAt: row.CreatedAt,
		UpdatedAt: row.UpdatedAt,
	}
}

const projectColumns = "id, workspace_id, machine_id, root_path, target_hash, name, description, visibility, " +
	"owner_user_id, created_by_user_id, updated_by_user_id, created_at, updated_at"

const projectGrantColumns = "project_id, member_id, role, created_at, updated_at"

func qualifiedProjectColumns(alias string) string {
	return alias + "." + strings.ReplaceAll(projectColumns, ", ", ", "+alias+".")
}

func scanProjectWithRole(scanner rowScanner) (agent.Project, error) {
	var row projectRow
	var role string
	if err := scanner.Scan(
		&row.ID, &row.WorkspaceID, &row.MachineID, &row.RootPath, &row.TargetHash, &row.Name,
		&row.Description, &row.Visibility, &row.OwnerUserID,
		&row.CreatedByUserID, &row.UpdatedByUserID, &row.CreatedAt, &row.UpdatedAt,
		&role,
	); err != nil {
		return agent.Project{}, err
	}
	project := row.toModel()
	project.CurrentRole = role
	return project, nil
}

type projectListQueryBuilder struct {
	args  []any
	where []string
}

func newProjectListQueryBuilder(
	workspaceID string,
	userID string,
	memberID string,
) *projectListQueryBuilder {
	return &projectListQueryBuilder{
		args: []any{
			workspaceID,
			userID,
			memberID,
			agent.ProjectVisibilityWorkspace,
			agent.ProjectRoleOwner,
			agent.ProjectRoleView,
		},
		where: []string{`
projects.workspace_id = $1
  AND (
    projects.owner_user_id = $2
    OR project_grants.role IS NOT NULL
    OR projects.visibility = $4
  )`},
	}
}

func (builder *projectListQueryBuilder) placeholder(value any) string {
	builder.args = append(builder.args, value)
	return fmt.Sprintf("$%d", len(builder.args))
}

func (builder *projectListQueryBuilder) appendMachineFilter(
	machineIDs []string,
	exclude bool,
) {
	values := make([]string, 0, len(machineIDs))
	for _, machineID := range machineIDs {
		if machineID = strings.TrimSpace(machineID); machineID != "" {
			values = append(values, builder.placeholder(machineID))
		}
	}
	if len(values) == 0 {
		if !exclude {
			builder.where = append(builder.where, "1 = 0")
		}
		return
	}
	operator := "IN"
	if exclude {
		operator = "NOT IN"
	}
	builder.where = append(
		builder.where,
		"projects.machine_id "+operator+" ("+strings.Join(values, ", ")+")",
	)
}

// projectIDPrefix is the identifiers.NewEntityID prefix for projects. Matches
// the e2e test pin (prj_ + 14 base36 chars).
const projectIDPrefix = "prj"

// projectTargetHash is the deterministic dedup key for a Project target. The
// hash is sha256 over the NUL-separated (workspaceID, machineID, rootPath)
// tuple. workspaceID and machineID are control IDs and are trimmed before
// hashing — matches the ref/ID discipline used by reviewContextTargetHash.
// rootPath is the canonical filesystem path and is hashed RAW — NOT trimmed —
// so two targets whose canonical roots differ only by trailing whitespace
// ("/repo" vs "/repo ") get distinct hashes. Trimming would silently collapse
// them into one identity on the next insert.
//
// UNIQUE(workspace_id, target_hash) backs the constraint; the workspace_id is
// also part of the unique key (belt-and-suspenders, since the hash already
// includes workspace_id) so the index can shard by workspace.
func projectTargetHash(workspaceID, machineID, rootPath string) string {
	h := sha256.Sum256([]byte(strings.Join([]string{
		strings.TrimSpace(workspaceID),
		strings.TrimSpace(machineID),
		rootPath,
	}, "\x00")))
	return hex.EncodeToString(h[:])
}

// --- Create ---

// CreateProjectForUser creates a Project on behalf of a human user. The owner
// is resolved from the authenticated user via access.LoadHumanWorkspaceMember
// — callers cannot forge ownership. MachineID and RootPath are immutable after
// create. No project_grants row is written: owner access is implicit via
// projects.owner_user_id (planner seq=515).
//
// Two concurrent CreateProjectForUser calls on the same (workspace, machine,
// root) tuple race on the UNIQUE(workspace_id, target_hash) index; the loser
// surfaces a typed ErrConflict so the HTTP layer maps to 409. Callers that
// want race-safe first-create-wins semantics should use GetOrCreateProjectForUser.
func (store *Store) CreateProjectForUser(ctx context.Context, userID string, params agent.CreateProjectParams) (agent.Project, error) {
	normalized, err := agent.ValidateCreateProjectParams(params)
	if err != nil {
		return agent.Project{}, err
	}
	if _, err := store.loadHumanWorkspaceMember(ctx, store.db, userID, normalized.WorkspaceID); err != nil {
		return agent.Project{}, err
	}
	now := time.Now().UTC()
	id, err := identifiers.NewEntityID(projectIDPrefix)
	if err != nil {
		return agent.Project{}, fmt.Errorf("generate project id: %w", err)
	}
	row := projectRow{
		ID:              id,
		WorkspaceID:     normalized.WorkspaceID,
		MachineID:       normalized.MachineID,
		RootPath:        normalized.RootPath,
		TargetHash:      projectTargetHash(normalized.WorkspaceID, normalized.MachineID, normalized.RootPath),
		Name:            normalized.Name,
		Description:     normalized.Description,
		Visibility:      normalized.Visibility,
		OwnerUserID:     userID,
		CreatedByUserID: userID,
		UpdatedByUserID: userID,
		CreatedAt:       now,
		UpdatedAt:       now,
	}
	if _, err := store.db.Exec(ctx, `
INSERT INTO projects (`+projectColumns+`)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
`, row.ID, row.WorkspaceID, row.MachineID, row.RootPath, row.TargetHash,
		row.Name, row.Description, row.Visibility, row.OwnerUserID,
		row.CreatedByUserID, row.UpdatedByUserID, row.CreatedAt, row.UpdatedAt); err != nil {
		if coredb.IsUniqueViolation(err) {
			return agent.Project{}, storeerr.Conflict("project target already exists")
		}
		return agent.Project{}, fmt.Errorf("create project: %w", err)
	}
	project := row.toModel()
	project.CurrentRole = agent.ProjectRoleOwner
	return project, nil
}

// --- Get-or-create ---

// GetOrCreateProjectForUser resolves the Project for a workspace+machine+root
// target tuple on behalf of a human user, minting one when no match exists.
// Backs the C4 review-create path: every review points at exactly one project,
// and the project identity is the target tuple.
//
// Owner resolution: a freshly minted project is owned by the authenticating
// human workspace member (same path as CreateProjectForUser). The owner of a
// pre-existing project is NOT mutated — get-or-create never rewrites ownership
// on the way in, even when the caller is a different member than the original
// owner. The caller still gets the row back; visibility rules apply on read.
//
// Race safety: identity is enforced by UNIQUE(workspace_id, target_hash) on
// the projects table. The resolver INSERTs; on duplicate-key violation (the
// other tx won the race), it reloads the canonical row by target_hash. TiDB
// does not gap-lock missing rows under SELECT ... FOR UPDATE, so a
// select-then-insert sequence would let two concurrent first-creates both
// commit — the unique index is the load-bearing guard. See migration 00243.
func (store *Store) GetOrCreateProjectForUser(ctx context.Context, userID string, params agent.GetOrCreateProjectParams) (agent.Project, error) {
	normalized, err := agent.ValidateCreateProjectParams(agent.CreateProjectParams{
		WorkspaceID: params.WorkspaceID,
		MachineID:   params.MachineID,
		RootPath:    params.RootPath,
		Name:        params.Name,
		Description: params.Description,
	})
	if err != nil {
		return agent.Project{}, err
	}
	if _, err := store.loadHumanWorkspaceMember(ctx, store.db, userID, normalized.WorkspaceID); err != nil {
		return agent.Project{}, err
	}
	return store.getOrCreateProjectForTarget(ctx, normalized, userID)
}

// GetOrCreateProjectForAgent mirrors GetOrCreateProjectForUser for an
// authenticated agent. The owner of a freshly minted project is the user that
// owns the Agent's trusted machine. Used by the agent-runtime review-add path.
func (store *Store) GetOrCreateProjectForAgent(ctx context.Context, authenticatedAgent agent.AuthenticatedAgent, params agent.GetOrCreateProjectParams) (agent.Project, error) {
	userID, err := store.projectUserForAgent(ctx, authenticatedAgent)
	if err != nil {
		return agent.Project{}, err
	}
	machineID := strings.TrimSpace(authenticatedAgent.MachineID)
	normalized, err := agent.ValidateCreateProjectParams(agent.CreateProjectParams{
		WorkspaceID: authenticatedAgent.WorkspaceID,
		MachineID:   machineID,
		RootPath:    params.RootPath,
		Name:        params.Name,
		Description: params.Description,
	})
	if err != nil {
		return agent.Project{}, err
	}
	return store.getOrCreateProjectForTarget(ctx, normalized, userID)
}

// getOrCreateProjectForTarget is the shared resolver. The caller has already
// validated the params and resolved the owner user id; this function owns
// the race-safe INSERT-then-reload.
//
// Atomicity: the INSERT itself is the linearization point. The
// UNIQUE(workspace_id, target_hash) index guarantees that across N concurrent
// callers exactly one INSERT succeeds; the others surface a unique-key
// violation and reload the canonical row by target_hash. The reloaded row is
// returned WITHOUT rewriting its owner — get-or-create never rewrites
// ownership on the way in.
//
// We deliberately do NOT pre-SELECT by target_hash before the INSERT. A
// select-then-insert sequence would race on TiDB (no gap lock on missing
// rows) and could let two callers both see "no row" then both insert, which
// is exactly the bug C4 revise fixes. Always-INSERT with conflict-reload is
// cheaper than SELECT FOR UPDATE and works under all isolation levels.
func (store *Store) getOrCreateProjectForTarget(ctx context.Context, normalized agent.CreateProjectParams, ownerUserID string) (agent.Project, error) {
	targetHash := projectTargetHash(normalized.WorkspaceID, normalized.MachineID, normalized.RootPath)
	id, err := identifiers.NewEntityID(projectIDPrefix)
	if err != nil {
		return agent.Project{}, fmt.Errorf("generate project id: %w", err)
	}
	now := time.Now().UTC()
	row := projectRow{
		ID:              id,
		WorkspaceID:     normalized.WorkspaceID,
		MachineID:       normalized.MachineID,
		RootPath:        normalized.RootPath,
		TargetHash:      targetHash,
		Name:            normalized.Name,
		Description:     normalized.Description,
		Visibility:      normalized.Visibility,
		OwnerUserID:     ownerUserID,
		CreatedByUserID: ownerUserID,
		UpdatedByUserID: ownerUserID,
		CreatedAt:       now,
		UpdatedAt:       now,
	}
	if _, err := store.db.Exec(ctx, `
INSERT INTO projects (`+projectColumns+`)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
`, row.ID, row.WorkspaceID, row.MachineID, row.RootPath, row.TargetHash,
		row.Name, row.Description, row.Visibility, row.OwnerUserID,
		row.CreatedByUserID, row.UpdatedByUserID, row.CreatedAt, row.UpdatedAt); err != nil {
		if coredb.IsUniqueViolation(err) {
			return store.getProjectByTargetHash(ctx, normalized.WorkspaceID, targetHash)
		}
		return agent.Project{}, fmt.Errorf("create project: %w", err)
	}
	project := row.toModel()
	project.CurrentRole = agent.ProjectRoleOwner
	return project, nil
}

// getProjectByTargetHash loads the canonical row for a (workspace, target_hash)
// pair. The UNIQUE(workspace_id, target_hash) index guarantees at most one row;
// callers use this to reload after a get-or-create race loss. Returns
// storeerr.ErrNotFound if no row matches — the caller is responsible for
// surfacing that as the appropriate typed error.
func (store *Store) getProjectByTargetHash(ctx context.Context, workspaceID string, targetHash string) (agent.Project, error) {
	row := projectRow{}
	err := store.db.QueryRow(ctx, `
SELECT `+projectColumns+`
FROM projects
WHERE workspace_id = $1 AND target_hash = $2
LIMIT 1
`, workspaceID, targetHash).Scan(
		&row.ID, &row.WorkspaceID, &row.MachineID, &row.RootPath, &row.TargetHash, &row.Name,
		&row.Description, &row.Visibility, &row.OwnerUserID,
		&row.CreatedByUserID, &row.UpdatedByUserID, &row.CreatedAt, &row.UpdatedAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return agent.Project{}, fmt.Errorf("load project by target hash after unique violation: %w", err)
		}
		return agent.Project{}, fmt.Errorf("load project by target hash: %w", err)
	}
	return row.toModel(), nil
}

// --- Read ---

// GetProjectForUser loads a single Project, enforcing visibility. Returns
// storeerr.ErrNotFound when the project does not exist OR the user has no
// access — the two cases are indistinguishable to the caller to avoid leaking
// the existence of private projects.
func (store *Store) GetProjectForUser(ctx context.Context, userID string, workspaceID string, projectID string) (agent.Project, error) {
	row, err := store.loadProjectRow(ctx, store.db, workspaceID, projectID)
	if err != nil {
		return agent.Project{}, err
	}
	role, err := store.resolveProjectRole(ctx, store.db, userID, workspaceID, row)
	if err != nil {
		return agent.Project{}, err
	}
	if !roleSatisfies(role, agent.ProjectRoleView) {
		return agent.Project{}, storeerr.ErrForbidden
	}
	project := row.toModel()
	project.CurrentRole = role
	return project, nil
}

// ListProjectsForUser returns the Projects in a workspace visible to the user:
// owned, granted (manage/view), or workspace-visibility projects visible to
// any active workspace member. Order is most-recently-updated first.
func (store *Store) ListProjectsForUser(ctx context.Context, userID string, workspaceID string) ([]agent.Project, error) {
	membership, err := store.loadHumanWorkspaceMember(ctx, store.db, userID, workspaceID)
	if err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, `
SELECT `+qualifiedProjectColumns("projects")+`,
  CASE
    WHEN projects.owner_user_id = $2 THEN $5
    WHEN project_grants.role IS NOT NULL THEN project_grants.role
    ELSE $6
  END
FROM projects
LEFT JOIN project_grants
  ON project_grants.project_id = projects.id AND project_grants.member_id = $3
WHERE projects.workspace_id = $1
  AND (
    projects.owner_user_id = $2
    OR project_grants.role IS NOT NULL
    OR projects.visibility = $4
  )
ORDER BY projects.updated_at DESC, projects.id ASC
`, workspaceID, userID, membership.Member.ID, agent.ProjectVisibilityWorkspace, agent.ProjectRoleOwner, agent.ProjectRoleView)
	if err != nil {
		return nil, fmt.Errorf("list projects: %w", err)
	}
	defer rows.Close()
	var out []agent.Project
	for rows.Next() {
		project, err := scanProjectWithRole(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, project)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate projects: %w", err)
	}
	return out, nil
}

// ListProjectPageForUser returns one stable, most-recently-updated page of
// Projects visible to the user. Search matches Project name and root path.
func (store *Store) ListProjectPageForUser(ctx context.Context, userID string, workspaceID string, options agent.ProjectListOptions) (agent.ProjectPage, error) {
	membership, err := store.loadHumanWorkspaceMember(ctx, store.db, userID, workspaceID)
	if err != nil {
		return agent.ProjectPage{}, err
	}
	limit := options.Limit
	if limit <= 0 {
		limit = 20
	}
	if limit > 100 {
		limit = 100
	}
	builder := newProjectListQueryBuilder(
		workspaceID,
		userID,
		membership.Member.ID,
	)
	query := strings.TrimSpace(options.Query)
	if query != "" {
		queryArg := builder.placeholder(strings.ToLower(query))
		builder.where = append(
			builder.where,
			"(LOCATE("+queryArg+", LOWER(projects.name)) > 0 OR LOCATE("+queryArg+", LOWER(projects.root_path)) > 0)",
		)
	}
	if len(options.MachineIDs) > 0 {
		builder.appendMachineFilter(options.MachineIDs, false)
	}
	if len(options.ExcludeMachineIDs) > 0 {
		builder.appendMachineFilter(options.ExcludeMachineIDs, true)
	}
	if !options.CursorUpdatedAt.IsZero() && strings.TrimSpace(options.CursorID) != "" {
		updatedAtArg := builder.placeholder(options.CursorUpdatedAt)
		idArg := builder.placeholder(strings.TrimSpace(options.CursorID))
		builder.where = append(builder.where, "(projects.updated_at < "+updatedAtArg+" OR (projects.updated_at = "+updatedAtArg+" AND projects.id > "+idArg+"))")
	}
	limitArg := builder.placeholder(limit + 1)
	rows, err := store.db.Query(ctx, `
SELECT `+qualifiedProjectColumns("projects")+`,
  CASE
    WHEN projects.owner_user_id = $2 THEN $5
    WHEN project_grants.role IS NOT NULL THEN project_grants.role
    ELSE $6
  END
FROM projects
LEFT JOIN project_grants
  ON project_grants.project_id = projects.id AND project_grants.member_id = $3
WHERE `+strings.Join(builder.where, " AND ")+`
ORDER BY projects.updated_at DESC, projects.id ASC
LIMIT `+limitArg, builder.args...)
	if err != nil {
		return agent.ProjectPage{}, fmt.Errorf("list project page: %w", err)
	}
	defer rows.Close()
	projects := make([]agent.Project, 0, limit+1)
	for rows.Next() {
		project, err := scanProjectWithRole(rows)
		if err != nil {
			return agent.ProjectPage{}, err
		}
		projects = append(projects, project)
	}
	if err := rows.Err(); err != nil {
		return agent.ProjectPage{}, fmt.Errorf("iterate project page: %w", err)
	}
	hasMore := len(projects) > limit
	if hasMore {
		projects = projects[:limit]
	}
	return agent.ProjectPage{Projects: projects, HasMore: hasMore}, nil
}

// --- Update ---

// UpdateProjectForUser mutates only Name, Description, and Visibility on the
// Project. MachineID and RootPath are immutable; owner transfer is out of
// scope. Requires manage+ role. Nil pointer fields mean "preserve existing".
func (store *Store) UpdateProjectForUser(ctx context.Context, userID string, workspaceID string, projectID string, params agent.UpdateProjectParams) (agent.Project, error) {
	normalized, err := agent.ValidateUpdateProjectParams(params)
	if err != nil {
		return agent.Project{}, err
	}
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return agent.Project{}, fmt.Errorf("begin tx for project update: %w", err)
	}
	defer tx.Rollback(ctx)
	row, err := store.loadProjectRowForUpdate(ctx, tx, workspaceID, projectID)
	if err != nil {
		return agent.Project{}, err
	}
	role, err := store.resolveProjectRole(ctx, tx, userID, workspaceID, row)
	if err != nil {
		return agent.Project{}, err
	}
	if !roleSatisfies(role, agent.ProjectRoleManage) {
		return agent.Project{}, storeerr.ErrForbidden
	}
	if normalized.Name != nil {
		row.Name = *normalized.Name
	}
	if normalized.Description != nil {
		row.Description = *normalized.Description
	}
	if normalized.Visibility != nil {
		row.Visibility = *normalized.Visibility
	}
	row.UpdatedByUserID = userID
	row.UpdatedAt = time.Now().UTC()
	if _, err := tx.Exec(ctx, `
UPDATE projects
SET name = $1, description = $2, visibility = $3, updated_by_user_id = $4, updated_at = $5
WHERE id = $6 AND workspace_id = $7
`, row.Name, row.Description, row.Visibility, row.UpdatedByUserID, row.UpdatedAt, row.ID, row.WorkspaceID); err != nil {
		return agent.Project{}, fmt.Errorf("update project: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return agent.Project{}, fmt.Errorf("commit project update: %w", err)
	}
	project := row.toModel()
	project.CurrentRole = role
	return project, nil
}

// --- Delete ---

// DeleteProjectForUser implements Danger Zone semantics (planner seq=517,
// reviewer2 seq=516): only the owner can delete; the operation is
// metadata-only. Within a single transaction:
//  1. DELETE project_grants rows for this project.
//  2. UPDATE review_contexts SET project_id = NULL WHERE project_id = this.
//  3. DELETE the projects row.
//
// Machine files and Review rows are intentionally untouched — the nullable
// review_contexts.project_id contract exists for this unlink path.
func (store *Store) DeleteProjectForUser(ctx context.Context, userID string, workspaceID string, projectID string) error {
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return fmt.Errorf("begin tx for project delete: %w", err)
	}
	defer tx.Rollback(ctx)
	row, err := store.loadProjectRowForUpdate(ctx, tx, workspaceID, projectID)
	if err != nil {
		return err
	}
	if _, err := store.requireProjectRole(ctx, tx, userID, workspaceID, row, agent.ProjectRoleOwner); err != nil {
		return err
	}
	if _, err := tx.Exec(ctx, `DELETE FROM project_grants WHERE project_id = $1`, projectID); err != nil {
		return fmt.Errorf("delete project grants: %w", err)
	}
	if _, err := tx.Exec(ctx, `UPDATE review_contexts SET project_id = NULL WHERE project_id = $1`, projectID); err != nil {
		return fmt.Errorf("unlink project reviews: %w", err)
	}
	if _, err := tx.Exec(ctx, `DELETE FROM projects WHERE id = $1 AND workspace_id = $2`, projectID, workspaceID); err != nil {
		return fmt.Errorf("delete project: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("commit project delete: %w", err)
	}
	return nil
}

// --- Access (settings) ---

// ListProjectAccessForUser returns the persisted grants for a Project. Owner
// is NOT included — owner access is implicit. Settings/Access is the
// management plane, so this requires manage+ role. view-only callers cannot
// enumerate grants (Reviewer2 seq=537 point 5).
func (store *Store) ListProjectAccessForUser(ctx context.Context, userID string, workspaceID string, projectID string) ([]agent.ProjectGrant, error) {
	row, err := store.loadProjectRow(ctx, store.db, workspaceID, projectID)
	if err != nil {
		return nil, err
	}
	if _, err := store.requireProjectRole(ctx, store.db, userID, workspaceID, row, agent.ProjectRoleManage); err != nil {
		return nil, err
	}
	rows, err := store.db.Query(ctx, `
SELECT `+projectGrantColumns+`
FROM project_grants
WHERE project_id = $1
ORDER BY member_id ASC
`, projectID)
	if err != nil {
		return nil, fmt.Errorf("list project grants: %w", err)
	}
	defer rows.Close()
	var out []agent.ProjectGrant
	for rows.Next() {
		var grant projectGrantRow
		if err := rows.Scan(&grant.ProjectID, &grant.MemberID, &grant.Role, &grant.CreatedAt, &grant.UpdatedAt); err != nil {
			return nil, fmt.Errorf("scan project grant: %w", err)
		}
		out = append(out, grant.toModel())
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate project grants: %w", err)
	}
	return out, nil
}

// UpsertProjectGrantForUser adds or updates one non-owner grant. The target
// member MUST be an active human workspace member (caller supplies memberID
// — the store verifies it). role must be manage or view; role=owner is
// rejected. The Project owner can never become a grant target. Requires
// manage+ role on the Project.
func (store *Store) UpsertProjectGrantForUser(ctx context.Context, userID string, workspaceID string, projectID string, targetMemberID string, role string) (agent.ProjectGrant, error) {
	if strings.TrimSpace(targetMemberID) == "" {
		return agent.ProjectGrant{}, storeerr.BadRequest("memberId is required")
	}
	if err := agent.ValidateProjectGrantRole(role); err != nil {
		return agent.ProjectGrant{}, err
	}
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return agent.ProjectGrant{}, fmt.Errorf("begin tx for project grant upsert: %w", err)
	}
	defer tx.Rollback(ctx)
	row, err := store.loadProjectRowForUpdate(ctx, tx, workspaceID, projectID)
	if err != nil {
		return agent.ProjectGrant{}, err
	}
	if _, err := store.requireProjectRole(ctx, tx, userID, workspaceID, row, agent.ProjectRoleManage); err != nil {
		return agent.ProjectGrant{}, err
	}
	targetUserID, err := store.activeHumanMemberUserID(ctx, tx, workspaceID, targetMemberID)
	if err != nil {
		return agent.ProjectGrant{}, err
	}
	if targetUserID == row.OwnerUserID {
		return agent.ProjectGrant{}, storeerr.BadRequest("owner cannot be a grant target")
	}
	now := time.Now().UTC()
	grant := projectGrantRow{
		ProjectID: projectID,
		MemberID:  targetMemberID,
		Role:      role,
		CreatedAt: now,
		UpdatedAt: now,
	}
	if _, err := tx.Exec(ctx, `
INSERT INTO project_grants (`+projectGrantColumns+`)
VALUES ($1, $2, $3, $4, $5)
ON DUPLICATE KEY UPDATE role = VALUES(role), updated_at = VALUES(updated_at)
`, grant.ProjectID, grant.MemberID, grant.Role, grant.CreatedAt, grant.UpdatedAt); err != nil {
		return agent.ProjectGrant{}, fmt.Errorf("upsert project grant: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return agent.ProjectGrant{}, fmt.Errorf("commit project grant upsert: %w", err)
	}
	return grant.toModel(), nil
}

// DeleteProjectGrantForUser removes one grant. The Project owner cannot be a
// target (defensive — owner should never appear in project_grants to begin
// with, but this guard catches a stale row). Requires manage+ role.
func (store *Store) DeleteProjectGrantForUser(ctx context.Context, userID string, workspaceID string, projectID string, targetMemberID string) error {
	if strings.TrimSpace(targetMemberID) == "" {
		return storeerr.BadRequest("memberId is required")
	}
	tx, err := store.db.BeginTx(ctx, coredb.TxOptions{})
	if err != nil {
		return fmt.Errorf("begin tx for project grant delete: %w", err)
	}
	defer tx.Rollback(ctx)
	row, err := store.loadProjectRowForUpdate(ctx, tx, workspaceID, projectID)
	if err != nil {
		return err
	}
	if _, err := store.requireProjectRole(ctx, tx, userID, workspaceID, row, agent.ProjectRoleManage); err != nil {
		return err
	}
	targetUserID, err := store.activeHumanMemberUserID(ctx, tx, workspaceID, targetMemberID)
	if err != nil {
		return err
	}
	if targetUserID == row.OwnerUserID {
		return storeerr.BadRequest("owner grant cannot be removed")
	}
	if _, err := tx.Exec(ctx, `
DELETE FROM project_grants WHERE project_id = $1 AND member_id = $2
`, projectID, targetMemberID); err != nil {
		return fmt.Errorf("delete project grant: %w", err)
	}
	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("commit project grant delete: %w", err)
	}
	return nil
}

// --- Authorization helpers ---

// resolveProjectRole computes the in-memory role a user holds on a Project.
// The result is one of: ProjectRoleOwner, ProjectRoleManage, ProjectRoleView,
// or "" (no access). Resolution order:
//
//  1. ProjectRoleOwner if projects.owner_user_id == current user id.
//  2. ProjectRoleManage / ProjectRoleView from project_grants row.
//  3. ProjectRoleView if Visibility == workspace AND the user is an active
//     workspace member (the workspace-wide view relaxation).
//  4. "" otherwise (no access).
//
// The "owner" value is intentionally never persisted as a grant — see
// CreateProjectForUser.
func (store *Store) resolveProjectRole(ctx context.Context, queryer Queryer, userID string, workspaceID string, row projectRow) (string, error) {
	membership, err := store.loadHumanWorkspaceMember(ctx, queryer, userID, workspaceID)
	if err != nil {
		if errors.Is(err, access.ErrForbidden) {
			return "", nil
		}
		return "", err
	}
	if row.OwnerUserID == userID {
		return agent.ProjectRoleOwner, nil
	}
	var role string
	err = queryer.QueryRow(ctx, `
SELECT role FROM project_grants WHERE project_id = $1 AND member_id = $2
`, row.ID, membership.Member.ID).Scan(&role)
	if err == nil {
		return role, nil
	}
	if !coredb.IsNotFound(err) {
		return "", fmt.Errorf("resolve project grant: %w", err)
	}
	if row.Visibility == agent.ProjectVisibilityWorkspace {
		return agent.ProjectRoleView, nil
	}
	return "", nil
}

// requireProjectRole wraps resolveProjectRole and returns ErrForbidden when
// the resolved role does not meet the minimum required. The required role is
// interpreted as a rank: owner > manage > view. "" is always rejected.
func (store *Store) requireProjectRole(ctx context.Context, queryer Queryer, userID string, workspaceID string, row projectRow, requiredRole string) (access.HumanWorkspaceMember, error) {
	resolved, err := store.resolveProjectRole(ctx, queryer, userID, workspaceID, row)
	if err != nil {
		return access.HumanWorkspaceMember{}, err
	}
	if !roleSatisfies(resolved, requiredRole) {
		return access.HumanWorkspaceMember{}, storeerr.ErrForbidden
	}
	membership, err := store.loadHumanWorkspaceMember(ctx, queryer, userID, workspaceID)
	if err != nil {
		return access.HumanWorkspaceMember{}, err
	}
	return membership, nil
}

// roleSatisfies returns true when the resolved role meets or exceeds the
// required role by rank. The empty role never satisfies any requirement.
func roleSatisfies(resolved string, required string) bool {
	rank := func(role string) int {
		switch role {
		case agent.ProjectRoleOwner:
			return 3
		case agent.ProjectRoleManage:
			return 2
		case agent.ProjectRoleView:
			return 1
		default:
			return 0
		}
	}
	return rank(resolved) >= rank(required)
}

// requireActiveHumanMember verifies that a memberID is an active human member
// of the workspace. Used as a grant-target precondition.
func (store *Store) requireActiveHumanMember(ctx context.Context, queryer Queryer, workspaceID string, memberID string) error {
	var count int
	if err := queryer.QueryRow(ctx, `
SELECT COUNT(*)
FROM members
WHERE workspace_id = $1 AND id = $2 AND type = 'human' AND status = 'active'
`, workspaceID, memberID).Scan(&count); err != nil {
		return fmt.Errorf("verify grant target membership: %w", err)
	}
	if count == 0 {
		return storeerr.BadRequest("grant target must be an active human workspace member")
	}
	return nil
}

func (store *Store) activeHumanMemberUserID(ctx context.Context, queryer Queryer, workspaceID string, memberID string) (string, error) {
	var userID string
	if err := queryer.QueryRow(ctx, `
SELECT user_id
FROM members
WHERE workspace_id = $1 AND id = $2 AND type = 'human' AND status = 'active'
`, workspaceID, memberID).Scan(&userID); err != nil {
		if coredb.IsNotFound(err) {
			return "", storeerr.BadRequest("grant target must be an active human workspace member")
		}
		return "", fmt.Errorf("resolve grant target user: %w", err)
	}
	return strings.TrimSpace(userID), nil
}

// --- Row helpers ---

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

func scanProjectRow(scanner rowScanner, row *projectRow) error {
	return scanner.Scan(
		&row.ID, &row.WorkspaceID, &row.MachineID, &row.RootPath, &row.TargetHash, &row.Name,
		&row.Description, &row.Visibility, &row.OwnerUserID,
		&row.CreatedByUserID, &row.UpdatedByUserID, &row.CreatedAt, &row.UpdatedAt,
	)
}

func (store *Store) loadProjectRow(ctx context.Context, queryer Queryer, workspaceID string, projectID string) (projectRow, error) {
	row := projectRow{}
	err := queryer.QueryRow(ctx, `
SELECT `+projectColumns+`
FROM projects
WHERE id = $1 AND workspace_id = $2
`, projectID, workspaceID).Scan(
		&row.ID, &row.WorkspaceID, &row.MachineID, &row.RootPath, &row.TargetHash, &row.Name,
		&row.Description, &row.Visibility, &row.OwnerUserID,
		&row.CreatedByUserID, &row.UpdatedByUserID, &row.CreatedAt, &row.UpdatedAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return projectRow{}, storeerr.ErrNotFound
		}
		return projectRow{}, fmt.Errorf("load project: %w", err)
	}
	return row, nil
}

func (store *Store) loadProjectRowForUpdate(ctx context.Context, queryer Queryer, workspaceID string, projectID string) (projectRow, error) {
	row := projectRow{}
	err := queryer.QueryRow(ctx, `
SELECT `+projectColumns+`
FROM projects
WHERE id = $1 AND workspace_id = $2
FOR UPDATE
`, projectID, workspaceID).Scan(
		&row.ID, &row.WorkspaceID, &row.MachineID, &row.RootPath, &row.TargetHash, &row.Name,
		&row.Description, &row.Visibility, &row.OwnerUserID,
		&row.CreatedByUserID, &row.UpdatedByUserID, &row.CreatedAt, &row.UpdatedAt,
	)
	if err != nil {
		if coredb.IsNotFound(err) {
			return projectRow{}, storeerr.ErrNotFound
		}
		return projectRow{}, fmt.Errorf("load project for update: %w", err)
	}
	return row, nil
}