//go:build sqlite || sqliteonly

package sqlitestore

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log/slog"
	"strings"
	"time"

	"github.com/google/uuid"

	"github.com/nextlevelbuilder/goclaw/internal/crypto"
	"github.com/nextlevelbuilder/goclaw/internal/store"
)

// SQLiteSecureCLIStore implements store.SecureCLIStore backed by SQLite.
// encKey is required for AES-256-GCM encryption of encrypted_env columns.
type SQLiteSecureCLIStore struct {
	db     *sql.DB
	encKey string
}

// NewSQLiteSecureCLIStore creates a new SQLiteSecureCLIStore.
func NewSQLiteSecureCLIStore(db *sql.DB, encKey string) *SQLiteSecureCLIStore {
	return &SQLiteSecureCLIStore{db: db, encKey: encKey}
}

const secureCLISelectCols = `id, binary_name, binary_path, description, encrypted_env,
 deny_args, deny_verbose, timeout_seconds, tips, is_global, enabled, created_by,
 adapter_name, created_at, updated_at`

const secureCLISelectColsAliased = `b.id, b.binary_name, b.binary_path, b.description, b.encrypted_env,
 b.deny_args, b.deny_verbose, b.timeout_seconds, b.tips, b.is_global, b.enabled, b.created_by,
 b.adapter_name, b.created_at, b.updated_at`

func (s *SQLiteSecureCLIStore) Create(ctx context.Context, b *store.SecureCLIBinary) error {
	if err := store.ValidateUserID(b.CreatedBy); err != nil {
		return err
	}
	if b.ID == uuid.Nil {
		b.ID = store.GenNewID()
	}

	// Normalize binary_name to lowercase so IsRegisteredBinary (which lowercases
	// the candidate) can match. Admin entering "Gh" becomes "gh".
	b.BinaryName = strings.ToLower(strings.TrimSpace(b.BinaryName))

	var envBytes []byte
	if len(b.EncryptedEnv) > 0 && s.encKey != "" {
		encrypted, err := crypto.Encrypt(string(b.EncryptedEnv), s.encKey)
		if err != nil {
			return fmt.Errorf("encrypt env: %w", err)
		}
		envBytes = []byte(encrypted)
	} else {
		envBytes = b.EncryptedEnv
	}

	now := time.Now().UTC()
	b.CreatedAt = now
	b.UpdatedAt = now
	nowStr := now.Format(time.RFC3339Nano)

	tenantID := store.TenantIDFromContext(ctx)
	if tenantID == uuid.Nil {
		tenantID = store.MasterTenantID
	}

	_, err := s.db.ExecContext(ctx,
		`INSERT INTO secure_cli_binaries (id, binary_name, binary_path, description, encrypted_env,
		 deny_args, deny_verbose, timeout_seconds, tips, is_global, enabled, created_by, adapter_name, created_at, updated_at, tenant_id)
		 VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
		b.ID, b.BinaryName, nilStr(derefStr(b.BinaryPath)), b.Description,
		envBytes,
		jsonOrEmptyArray(b.DenyArgs), jsonOrEmptyArray(b.DenyVerbose),
		b.TimeoutSeconds, b.Tips,
		b.IsGlobal, b.Enabled,
		b.CreatedBy, b.AdapterName, nowStr, nowStr, tenantID,
	)
	return err
}

func (s *SQLiteSecureCLIStore) Get(ctx context.Context, id uuid.UUID) (*store.SecureCLIBinary, error) {
	if store.IsCrossTenant(ctx) {
		row := s.db.QueryRowContext(ctx,
			`SELECT `+secureCLISelectCols+` FROM secure_cli_binaries WHERE id = ?`, id)
		return s.scanRow(row)
	}
	tenantID := store.TenantIDFromContext(ctx)
	if tenantID == uuid.Nil {
		return nil, sql.ErrNoRows
	}
	row := s.db.QueryRowContext(ctx,
		`SELECT `+secureCLISelectCols+` FROM secure_cli_binaries WHERE id = ? AND tenant_id = ?`, id, tenantID)
	return s.scanRow(row)
}

func (s *SQLiteSecureCLIStore) scanRow(row *sql.Row) (*store.SecureCLIBinary, error) {
	var b store.SecureCLIBinary
	var binaryPath *string
	var denyArgs, denyVerbose []byte
	var env []byte
	var createdAt, updatedAt sqliteTime

	err := row.Scan(
		&b.ID, &b.BinaryName, &binaryPath, &b.Description, &env,
		&denyArgs, &denyVerbose,
		&b.TimeoutSeconds, &b.Tips, &b.IsGlobal,
		&b.Enabled, &b.CreatedBy, &b.AdapterName, &createdAt, &updatedAt,
	)
	if err != nil {
		return nil, err
	}

	b.BinaryPath = binaryPath
	if len(denyArgs) > 0 {
		b.DenyArgs = json.RawMessage(denyArgs)
	}
	if len(denyVerbose) > 0 {
		b.DenyVerbose = json.RawMessage(denyVerbose)
	}
	b.CreatedAt = createdAt.Time
	b.UpdatedAt = updatedAt.Time

	// Decrypt env
	if len(env) > 0 && s.encKey != "" {
		decrypted, err := crypto.Decrypt(string(env), s.encKey)
		if err != nil {
			slog.Warn("secure_cli: failed to decrypt env", "binary", b.BinaryName, "error", err)
		} else {
			b.EncryptedEnv = []byte(decrypted)
		}
	} else {
		b.EncryptedEnv = env
	}

	return &b, nil
}

func (s *SQLiteSecureCLIStore) scanRows(rows *sql.Rows) ([]store.SecureCLIBinary, error) {
	defer rows.Close()
	var result []store.SecureCLIBinary
	for rows.Next() {
		var b store.SecureCLIBinary
		var binaryPath *string
		var denyArgs, denyVerbose []byte
		var env []byte
		var createdAt, updatedAt sqliteTime

		if err := rows.Scan(
			&b.ID, &b.BinaryName, &binaryPath, &b.Description, &env,
			&denyArgs, &denyVerbose,
			&b.TimeoutSeconds, &b.Tips, &b.IsGlobal,
			&b.Enabled, &b.CreatedBy, &b.AdapterName, &createdAt, &updatedAt,
		); err != nil {
			return nil, fmt.Errorf("scan secure_cli_binaries row: %w", err)
		}

		b.BinaryPath = binaryPath
		if len(denyArgs) > 0 {
			b.DenyArgs = json.RawMessage(denyArgs)
		}
		if len(denyVerbose) > 0 {
			b.DenyVerbose = json.RawMessage(denyVerbose)
		}
		b.CreatedAt = createdAt.Time
		b.UpdatedAt = updatedAt.Time

		if len(env) > 0 && s.encKey != "" {
			if decrypted, err := crypto.Decrypt(string(env), s.encKey); err == nil {
				b.EncryptedEnv = []byte(decrypted)
			}
		} else {
			b.EncryptedEnv = env
		}

		result = append(result, b)
	}
	return result, nil
}

var secureCLIAllowedFields = map[string]bool{
	"binary_name": true, "binary_path": true, "description": true,
	"encrypted_env": true, "deny_args": true, "deny_verbose": true,
	"timeout_seconds": true, "tips": true, "is_global": true, "enabled": true,
	"adapter_name": true,
	"updated_at":   true,
}

func (s *SQLiteSecureCLIStore) Update(ctx context.Context, id uuid.UUID, updates map[string]any) error {
	for k := range updates {
		if !secureCLIAllowedFields[k] {
			delete(updates, k)
		}
	}

	// Normalize binary_name to lowercase if updated (parity with Create).
	if nameVal, ok := updates["binary_name"]; ok {
		if nameStr, isStr := nameVal.(string); isStr {
			updates["binary_name"] = strings.ToLower(strings.TrimSpace(nameStr))
		}
	}

	// Encrypt env if present in updates
	if envVal, ok := updates["encrypted_env"]; ok {
		if envStr, isStr := envVal.(string); isStr && envStr != "" && s.encKey != "" {
			encrypted, err := crypto.Encrypt(envStr, s.encKey)
			if err != nil {
				return fmt.Errorf("encrypt env: %w", err)
			}
			updates["encrypted_env"] = []byte(encrypted)
		}
	}
	updates["updated_at"] = time.Now().UTC().Format(time.RFC3339Nano)
	if store.IsCrossTenant(ctx) {
		return execMapUpdate(ctx, s.db, "secure_cli_binaries", id, updates)
	}
	tid := store.TenantIDFromContext(ctx)
	if tid == uuid.Nil {
		return fmt.Errorf("tenant_id required for update")
	}
	return execMapUpdateWhereTenant(ctx, s.db, "secure_cli_binaries", updates, id, tid)
}

func (s *SQLiteSecureCLIStore) Delete(ctx context.Context, id uuid.UUID) error {
	if store.IsCrossTenant(ctx) {
		_, err := s.db.ExecContext(ctx, "DELETE FROM secure_cli_binaries WHERE id = ?", id)
		return err
	}
	tid := store.TenantIDFromContext(ctx)
	if tid == uuid.Nil {
		return fmt.Errorf("tenant_id required")
	}
	_, err := s.db.ExecContext(ctx, "DELETE FROM secure_cli_binaries WHERE id = ? AND tenant_id = ?", id, tid)
	return err
}

func (s *SQLiteSecureCLIStore) List(ctx context.Context) ([]store.SecureCLIBinary, error) {
	// caller_tenant_id scopes the grants subquery to the requesting tenant (C3 isolation).
	// Master-scope binaries have b.tenant_id = MasterTenantID but grants belong to caller's tenant.
	callerTenantID := store.TenantIDFromContext(ctx)

	// H4: SQLite json_group_array has no inline ORDER BY.
	// Use a FROM-subquery so ORDER BY applies before aggregation.
	// encrypted_env IS NOT NULL projects as 0/1 integer (SQLite booleans) — never the blob.
	agentGrantsSubquery := `(SELECT json_group_array(json_object(
			'grant_id', g.id,
			'agent_id', g.agent_id,
			'agent_key', a.agent_key,
			'name',      a.display_name,
			'enabled',   g.enabled,
			'env_set',   (g.encrypted_env IS NOT NULL)
		))
		FROM (SELECT g.id, g.agent_id, g.enabled, g.encrypted_env, g.created_at, a.agent_key, a.display_name
		      FROM secure_cli_agent_grants g
		      JOIN agents a ON a.id = g.agent_id AND a.tenant_id = g.tenant_id
		      WHERE g.binary_id = b.id AND g.tenant_id = ?
		      ORDER BY g.created_at
		      LIMIT 20) g) AS grants`

	var query string
	var qArgs []any

	if store.IsCrossTenant(ctx) {
		effectiveTenant := callerTenantID
		if effectiveTenant == uuid.Nil {
			effectiveTenant = store.MasterTenantID
		}
		qArgs = append(qArgs, effectiveTenant)
		query = `SELECT ` + secureCLISelectColsAliased + `, ` + agentGrantsSubquery +
			` FROM secure_cli_binaries b ORDER BY b.binary_name`
	} else {
		if callerTenantID == uuid.Nil {
			return nil, nil
		}
		qArgs = append(qArgs, callerTenantID, callerTenantID)
		query = `SELECT ` + secureCLISelectColsAliased + `, ` + agentGrantsSubquery +
			` FROM secure_cli_binaries b WHERE b.tenant_id = ? ORDER BY b.binary_name`
	}

	rows, err := s.db.QueryContext(ctx, query, qArgs...)
	if err != nil {
		return nil, err
	}
	return s.scanRowsWithGrants(rows)
}

// scanRowsWithGrants scans the extended List query (includes grants JSON column).
func (s *SQLiteSecureCLIStore) scanRowsWithGrants(rows *sql.Rows) ([]store.SecureCLIBinary, error) {
	defer rows.Close()
	var result []store.SecureCLIBinary
	for rows.Next() {
		var b store.SecureCLIBinary
		var binaryPath *string
		var denyArgs, denyVerbose []byte
		var env []byte
		var grantsJSON []byte
		var createdAt, updatedAt sqliteTime

		if err := rows.Scan(
			&b.ID, &b.BinaryName, &binaryPath, &b.Description, &env,
			&denyArgs, &denyVerbose,
			&b.TimeoutSeconds, &b.Tips, &b.IsGlobal,
			&b.Enabled, &b.CreatedBy, &b.AdapterName, &createdAt, &updatedAt,
			&grantsJSON,
		); err != nil {
			return nil, fmt.Errorf("scan secure_cli_binaries row: %w", err)
		}

		b.BinaryPath = binaryPath
		if len(denyArgs) > 0 {
			b.DenyArgs = json.RawMessage(denyArgs)
		}
		if len(denyVerbose) > 0 {
			b.DenyVerbose = json.RawMessage(denyVerbose)
		}
		b.CreatedAt = createdAt.Time
		b.UpdatedAt = updatedAt.Time

		if len(env) > 0 && s.encKey != "" {
			if decrypted, err := crypto.Decrypt(string(env), s.encKey); err == nil {
				b.EncryptedEnv = []byte(decrypted)
			}
		} else {
			b.EncryptedEnv = env
		}

		// Unmarshal grants JSON → slice; default to empty slice (never nil).
		b.AgentGrantsSummary = []store.AgentGrantSummary{}
		if len(grantsJSON) > 0 {
			// SQLite returns integer 0/1 for boolean columns in json_object;
			// we decode into a raw intermediate type to handle that.
			var raw []sqliteGrantRaw
			if err := json.Unmarshal(grantsJSON, &raw); err == nil {
				b.AgentGrantsSummary = make([]store.AgentGrantSummary, len(raw))
				for i, r := range raw {
					b.AgentGrantsSummary[i] = store.AgentGrantSummary{
						GrantID:  r.GrantID,
						AgentID:  r.AgentID,
						AgentKey: r.AgentKey,
						Name:     r.Name,
						Enabled:  r.Enabled != 0,
						EnvSet:   r.EnvSet != 0,
					}
				}
			}
		}

		result = append(result, b)
	}
	return result, nil
}

// sqliteGrantRaw is used to decode json_group_array output where SQLite encodes
// booleans as integers (0/1) instead of JSON true/false.
type sqliteGrantRaw struct {
	GrantID  uuid.UUID `json:"grant_id"`
	AgentID  uuid.UUID `json:"agent_id"`
	AgentKey string    `json:"agent_key"`
	Name     string    `json:"name"`
	Enabled  int       `json:"enabled"`
	EnvSet   int       `json:"env_set"`
}

// LookupByBinary finds the credential config for a binary name.
// LEFT JOINs grant overrides and per-user credentials.
func (s *SQLiteSecureCLIStore) LookupByBinary(ctx context.Context, binaryName string, agentID *uuid.UUID, userID string) (*store.SecureCLIBinary, error) {
	tid := store.TenantIDFromContext(ctx)
	isCross := store.IsCrossTenant(ctx)
	if !isCross && tid == uuid.Nil {
		return nil, nil
	}

	selectCols := secureCLISelectColsAliased
	selectCols += `, g.deny_args AS grant_deny_args, g.deny_verbose AS grant_deny_verbose, g.timeout_seconds AS grant_timeout, g.tips AS grant_tips, g.enabled AS grant_enabled, g.id AS grant_id, g.encrypted_env AS grant_enc_env`

	var args []any

	// Project user-credential columns (encrypted_env + credential_type + host_scope).
	// When userID is empty we cannot reference uc_user.* — emit NULL placeholders
	// so the scan column count stays stable.
	hasUserJoin := userID != "" && agentID != nil
	hasUserJoinNoAgent := userID != "" && agentID == nil

	if hasUserJoin || hasUserJoinNoAgent {
		selectCols += `, uc_user.encrypted_env AS user_env, uc_user.credential_type AS user_cred_type, uc_user.host_scope AS user_host_scope`
	} else {
		selectCols += `, NULL AS user_env, NULL AS user_cred_type, NULL AS user_host_scope`
	}
	if agentID != nil {
		selectCols += `, ac.encrypted_env AS agent_env, ac.credential_type AS agent_cred_type, ac.host_scope AS agent_host_scope`
	} else {
		selectCols += `, NULL AS agent_env, NULL AS agent_cred_type, NULL AS agent_host_scope`
	}

	query := `SELECT ` + selectCols + ` FROM secure_cli_binaries b`

	// LEFT JOIN agent grant
	if agentID != nil {
		query += ` LEFT JOIN secure_cli_agent_grants g ON g.binary_id = b.id AND g.agent_id = ?`
		args = append(args, *agentID)
	} else {
		query += ` LEFT JOIN secure_cli_agent_grants g ON 0`
	}
	if agentID != nil {
		query += ` LEFT JOIN secure_cli_agent_credentials ac ON ac.binary_id = b.id AND ac.agent_id = ?`
		args = append(args, *agentID)
		if !isCross {
			query += ` AND ac.tenant_id = ?`
			args = append(args, tid)
		}
	}

	// LEFT JOIN user credentials (only when we project uc_user.*)
	if hasUserJoin || hasUserJoinNoAgent {
		if isCross {
			query += ` LEFT JOIN secure_cli_user_credentials uc_user ON uc_user.binary_id = b.id AND uc_user.user_id = ?`
			args = append(args, userID)
		} else {
			query += ` LEFT JOIN secure_cli_user_credentials uc_user ON uc_user.binary_id = b.id AND uc_user.user_id = ? AND uc_user.tenant_id = ?`
			args = append(args, userID, tid)
		}
	}

	// WHERE
	query += ` WHERE b.binary_name = ? AND b.enabled = 1`
	args = append(args, binaryName)

	if !isCross {
		query += ` AND b.tenant_id = ?`
		args = append(args, tid)
	}

	// Authorization
	if agentID != nil {
		query += ` AND (
			(b.is_global = 1 AND (g.id IS NULL OR g.enabled = 1))
			OR
			(b.is_global = 0 AND g.id IS NOT NULL AND g.enabled = 1)
		)`
	} else {
		query += ` AND b.is_global = 1`
	}

	query += ` LIMIT 1`

	row := s.db.QueryRowContext(ctx, query, args...)
	b, err := s.scanRowWithGrantAndUserEnv(row)
	if err != nil || b == nil {
		return b, err
	}
	return s.applyContextSecureCLI(ctx, b)
}

func (s *SQLiteSecureCLIStore) scanRowWithGrantAndUserEnv(row *sql.Row) (*store.SecureCLIBinary, error) {
	var b store.SecureCLIBinary
	var binaryPath *string
	var denyArgs, denyVerbose []byte
	var env []byte
	var grantDenyArgs, grantDenyVerbose []byte
	var grantTimeout *int
	var grantTips *string
	var grantEnabled *bool
	var grantID *uuid.UUID
	var grantEncEnv []byte
	var userEnv []byte
	var userCredType, userHostScope *string
	var agentEnv []byte
	var agentCredType, agentHostScope *string
	var createdAt, updatedAt sqliteTime

	err := row.Scan(
		&b.ID, &b.BinaryName, &binaryPath, &b.Description, &env,
		&denyArgs, &denyVerbose,
		&b.TimeoutSeconds, &b.Tips, &b.IsGlobal,
		&b.Enabled, &b.CreatedBy, &b.AdapterName, &createdAt, &updatedAt,
		&grantDenyArgs, &grantDenyVerbose, &grantTimeout, &grantTips, &grantEnabled, &grantID, &grantEncEnv,
		&userEnv, &userCredType, &userHostScope,
		&agentEnv, &agentCredType, &agentHostScope,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}

	b.BinaryPath = binaryPath
	if len(denyArgs) > 0 {
		b.DenyArgs = json.RawMessage(denyArgs)
	}
	if len(denyVerbose) > 0 {
		b.DenyVerbose = json.RawMessage(denyVerbose)
	}
	b.CreatedAt = createdAt.Time
	b.UpdatedAt = updatedAt.Time

	// Decrypt base env
	if len(env) > 0 && s.encKey != "" {
		if decrypted, err := crypto.Decrypt(string(env), s.encKey); err == nil {
			b.EncryptedEnv = []byte(decrypted)
		}
	} else {
		b.EncryptedEnv = env
	}

	// Apply grant overrides
	if grantID != nil {
		grant := &store.SecureCLIAgentGrant{}
		if len(grantDenyArgs) > 0 {
			raw := json.RawMessage(grantDenyArgs)
			grant.DenyArgs = &raw
		}
		if len(grantDenyVerbose) > 0 {
			raw := json.RawMessage(grantDenyVerbose)
			grant.DenyVerbose = &raw
		}
		grant.TimeoutSeconds = grantTimeout
		grant.Tips = grantTips
		if len(grantEncEnv) > 0 {
			if s.encKey != "" {
				if decrypted, err := crypto.Decrypt(string(grantEncEnv), s.encKey); err == nil {
					grant.EncryptedEnv = []byte(decrypted)
				}
			} else {
				grant.EncryptedEnv = grantEncEnv
			}
		}
		b.MergeGrantOverrides(grant)
	}

	// Decrypt per-user env
	if len(userEnv) > 0 {
		if s.encKey != "" {
			if decrypted, err := crypto.Decrypt(string(userEnv), s.encKey); err == nil {
				b.UserEnv = []byte(decrypted)
			}
		} else {
			b.UserEnv = userEnv
		}
	}

	// Typed-credential metadata from the joined user-credential row.
	b.UserCredentialType = userCredType
	b.UserHostScope = userHostScope
	if len(b.UserEnv) > 0 || userCredType != nil || userHostScope != nil {
		b.SetEffectiveCredential(b.UserEnv, userCredType, userHostScope, "user", "")
	}
	if len(agentEnv) > 0 || agentCredType != nil || agentHostScope != nil {
		decrypted := agentEnv
		if len(agentEnv) > 0 && s.encKey != "" {
			if d, err := crypto.Decrypt(string(agentEnv), s.encKey); err == nil {
				decrypted = []byte(d)
			}
		}
		if b.CredentialSource == "" {
			b.SetEffectiveCredential(decrypted, agentCredType, agentHostScope, "agent", "")
		}
	}

	return &b, nil
}

func (s *SQLiteSecureCLIStore) applyContextSecureCLI(ctx context.Context, b *store.SecureCLIBinary) (*store.SecureCLIBinary, error) {
	scopes := store.ChannelContextScopeChainFromContext(ctx)
	if len(scopes) == 0 || b == nil {
		return b, nil
	}
	hasUserCredential := b.CredentialSource == "user" || len(b.UserEnv) > 0 || b.UserCredentialType != nil || b.UserHostScope != nil
	disabledByContext := false
	for _, scope := range scopes {
		grants, err := s.ListContextGrantsForScope(ctx, scope)
		if err != nil {
			return nil, err
		}
		for _, contextGrant := range grants {
			if contextGrant.BinaryID != b.ID {
				continue
			}
			if !contextGrant.Enabled {
				disabledByContext = true
				break
			}
			grant := &store.SecureCLIAgentGrant{
				DenyArgs:       contextGrant.DenyArgs,
				DenyVerbose:    contextGrant.DenyVerbose,
				TimeoutSeconds: contextGrant.TimeoutSeconds,
				Tips:           contextGrant.Tips,
				EncryptedEnv:   contextGrant.EncryptedEnv,
			}
			b.MergeGrantOverrides(grant)
			disabledByContext = false
			break
		}
	}
	if disabledByContext {
		return nil, nil
	}
	for _, scope := range scopes {
		creds, err := s.GetContextCredentialsForScope(ctx, scope, b.ID)
		if err != nil {
			return nil, err
		}
		if creds == nil || len(creds.EncryptedEnv) == 0 {
			continue
		}
		if !hasUserCredential {
			b.SetEffectiveCredential(creds.EncryptedEnv, creds.CredentialType, creds.HostScope, "context", scope.ScopeType+":"+scope.ScopeKey)
		} else {
			b.EncryptedEnv = creds.EncryptedEnv
		}
	}
	return b, nil
}

// ListEnabled returns all enabled configs.
func (s *SQLiteSecureCLIStore) ListEnabled(ctx context.Context) ([]store.SecureCLIBinary, error) {
	query := `SELECT ` + secureCLISelectCols + ` FROM secure_cli_binaries WHERE enabled = 1`
	var qArgs []any
	if !store.IsCrossTenant(ctx) {
		tenantID := store.TenantIDFromContext(ctx)
		if tenantID == uuid.Nil {
			return nil, nil
		}
		query += ` AND tenant_id = ?`
		qArgs = append(qArgs, tenantID)
	}
	query += ` ORDER BY binary_name`
	rows, err := s.db.QueryContext(ctx, query, qArgs...)
	if err != nil {
		return nil, err
	}
	return s.scanRows(rows)
}

// IsRegisteredBinary reports whether a binary requires a grant (is_global=0)
// and is enabled for the current tenant. See interface godoc for rationale.
func (s *SQLiteSecureCLIStore) IsRegisteredBinary(ctx context.Context, binaryName string) (bool, error) {
	name := strings.ToLower(strings.TrimSpace(binaryName))
	if name == "" {
		return false, nil
	}
	query := `SELECT EXISTS(
		SELECT 1 FROM secure_cli_binaries
		WHERE LOWER(binary_name) = ?
		  AND enabled = 1
		  AND is_global = 0`
	args := []any{name}
	if !store.IsCrossTenant(ctx) {
		tid := store.TenantIDFromContext(ctx)
		if tid == uuid.Nil {
			return false, nil
		}
		query += ` AND tenant_id = ?`
		args = append(args, tid)
	}
	query += `)`
	var exists bool
	if err := s.db.QueryRowContext(ctx, query, args...).Scan(&exists); err != nil {
		return false, err
	}
	return exists, nil
}

// ListForAgent returns all CLIs accessible by an agent (global + granted),
// with grant overrides merged into the returned configs.
func (s *SQLiteSecureCLIStore) ListForAgent(ctx context.Context, agentID uuid.UUID) ([]store.SecureCLIBinary, error) {
	tid := store.TenantIDFromContext(ctx)
	isCross := store.IsCrossTenant(ctx)
	if !isCross && tid == uuid.Nil {
		return nil, nil
	}

	selectCols := secureCLISelectColsAliased +
		`, g.deny_args AS grant_deny_args, g.deny_verbose AS grant_deny_verbose,
		   g.timeout_seconds AS grant_timeout, g.tips AS grant_tips, g.id AS grant_id,
		   g.encrypted_env AS grant_enc_env`

	query := `SELECT ` + selectCols + ` FROM secure_cli_binaries b
		LEFT JOIN secure_cli_agent_grants g ON g.binary_id = b.id AND g.agent_id = ?
		WHERE b.enabled = 1
		  AND (
		    b.is_global = 1
		    OR (b.id IN (SELECT binary_id FROM secure_cli_agent_grants WHERE agent_id = ? AND enabled = 1))
		  )`

	args := []any{agentID, agentID}
	if !isCross {
		query += ` AND b.tenant_id = ?`
		args = append(args, tid)
	}
	query += ` ORDER BY b.binary_name`

	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var result []store.SecureCLIBinary
	for rows.Next() {
		var b store.SecureCLIBinary
		var binaryPath *string
		var denyArgs, denyVerbose []byte
		var env []byte
		var grantDenyArgs, grantDenyVerbose []byte
		var grantTimeout *int
		var grantTips *string
		var grantID *uuid.UUID
		var grantEncEnv []byte
		var createdAt, updatedAt sqliteTime

		if err := rows.Scan(
			&b.ID, &b.BinaryName, &binaryPath, &b.Description, &env,
			&denyArgs, &denyVerbose,
			&b.TimeoutSeconds, &b.Tips, &b.IsGlobal,
			&b.Enabled, &b.CreatedBy, &b.AdapterName, &createdAt, &updatedAt,
			&grantDenyArgs, &grantDenyVerbose, &grantTimeout, &grantTips, &grantID, &grantEncEnv,
		); err != nil {
			return nil, fmt.Errorf("scan secure_cli_binaries row: %w", err)
		}

		b.BinaryPath = binaryPath
		if len(denyArgs) > 0 {
			b.DenyArgs = json.RawMessage(denyArgs)
		}
		if len(denyVerbose) > 0 {
			b.DenyVerbose = json.RawMessage(denyVerbose)
		}
		b.CreatedAt = createdAt.Time
		b.UpdatedAt = updatedAt.Time

		if len(env) > 0 && s.encKey != "" {
			if decrypted, err := crypto.Decrypt(string(env), s.encKey); err == nil {
				b.EncryptedEnv = []byte(decrypted)
			}
		} else {
			b.EncryptedEnv = env
		}

		if grantID != nil {
			grant := &store.SecureCLIAgentGrant{}
			if len(grantDenyArgs) > 0 {
				raw := json.RawMessage(grantDenyArgs)
				grant.DenyArgs = &raw
			}
			if len(grantDenyVerbose) > 0 {
				raw := json.RawMessage(grantDenyVerbose)
				grant.DenyVerbose = &raw
			}
			grant.TimeoutSeconds = grantTimeout
			grant.Tips = grantTips
			if len(grantEncEnv) > 0 && s.encKey != "" {
				if decrypted, err := crypto.Decrypt(string(grantEncEnv), s.encKey); err == nil {
					grant.EncryptedEnv = []byte(decrypted)
				}
			}
			b.MergeGrantOverrides(grant)
		}

		result = append(result, b)
	}
	return result, nil
}