package persistence

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"sort"
	"strings"

	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/kernel/pkg/utils"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

var _ ports.PermissionAuthoringTargetStore = (*PermissionRepository)(nil)

// ExtractPermissionGrants pulls system-permission virtual keys out of an
// SObject payload and deletes the matched keys from the record in place.
// Returns the grants map keyed by the catalog APIName (derived from
// FieldMetadata.VirtualSourceAPIName) for staging to the
// _System_PermissionSetPermission junction.
//
// Matching is metadata-driven, not string-driven: only fields that the
// schema marks IsVirtual with VirtualKind == SystemPermission are
// treated as grants. Case-insensitive lookup against schema.FieldsMap
// makes lowercase / mixed-case payload keys resolve correctly.
//
// Callers no longer need to branch on objectName: for every non-
// PermissionSet object the schema has zero SystemPermission virtuals,
// so this returns an empty map and leaves the record untouched. Passing
// a nil or undecorated schema is equivalent to that no-op case.
//
// Coercion contract: the caller MUST have already run schemautil.NormalizeSObject
// on the record. Virtual boolean fields are in schema.Fields with
// Type=Boolean, so Normalize applies the same schema-driven coercion it
// uses for physical boolean fields ("true"/"1" strings, numbers,
// []uint8 bytes → bool; unknown types LEFT AS-IS per schema_utils.go).
// We do NOT re-implement coercion here — doing so silently mapped
// string "true" to bool false in earlier iterations (review #1831 P1).
//
// Fail-loud on non-bool: if Normalize left a value as non-bool
// (unparseable string, map, slice — the garbage case), this function
// returns an error rather than silently treating it as false. Paired
// with strict Normalize, the chain is:
//   - valid bool-like input (bool / "true" / 1 / []uint8) → coerced by
//     Normalize, extracted here as bool
//   - garbage input → left as original type by Normalize, rejected
//     here with a clear error before it becomes a silent revoke
//
// Returning the record unmodified on error is intentional: the caller
// wraps the call and aborts the write. Any keys already extracted
// BEFORE the failing key stay in `grants` (and have been deleted from
// `record`) — the write aborts anyway, so the partial state is never
// committed.
func ExtractPermissionGrants(record models.SObject, schema *models.ObjectMetadata) (map[string]bool, error) {
	grants := make(map[string]bool)
	if schema == nil || len(record) == 0 {
		return grants, nil
	}
	for key, value := range record {
		field := models.FindField(schema, key)
		if field == nil {
			continue
		}
		if !field.IsVirtual || field.VirtualKind != constants.VirtualFieldSystemPermission {
			continue
		}
		// nil is treated as false for consistency with physical boolean
		// fields: ValidateSchemaCompliance.validateType also accepts nil
		// on any type, and the junction default for a missing grant is
		// "not granted".
		if value == nil {
			grants[field.VirtualSourceAPIName] = false
			delete(record, key)
			continue
		}
		enabled, ok := value.(bool)
		if !ok {
			return grants, fmt.Errorf("virtual permission field %q requires a boolean value; got %T (did schemautil.NormalizeSObject run first?)", key, value)
		}
		grants[field.VirtualSourceAPIName] = enabled
		delete(record, key)
	}
	return grants, nil
}

// SetSystemPermission writes a permission grant to the SSoT
// (_System_PermissionSetPermission) junction. Previously mirrored to
// boolean columns on PermissionSet as a write-through cache; that path
// is gone now that the junction is the sole read source. Transaction-
// aware via getExecutor(ctx).
func (r *PermissionRepository) SetSystemPermission(ctx context.Context, permSetID, apiName string, enabled bool) error {
	const writerID = "nexuscrm-persistence/perm_system.go#PermissionRepository.SetSystemPermission"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetWithManagedPermissionSetIDPrelock(
					permissionStateDirectTargetFor(
						constants.TablePermissionSetPermission,
						permissionStateDirectEqual(constants.FieldSysPermissionSetPermission_PermissionSetID)+
							" AND "+permissionStateDirectEqual(constants.FieldSysPermissionSetPermission_PermissionAPIName),
						permSetID,
						apiName,
					),
				),
			},
			func(txCtx context.Context) error {
				return r.SetSystemPermission(txCtx, permSetID, apiName, enabled)
			},
		)
	}
	enabledInt := 0
	if enabled {
		enabledInt = 1
	}
	upsertSQL := fmt.Sprintf(
		"INSERT INTO `%s` (`%s`, `%s`, `%s`, `%s`) VALUES (?, ?, ?, ?) "+
			"ON DUPLICATE KEY UPDATE `%s` = VALUES(`%s`), `%s` = NOW()",
		constants.TablePermissionSetPermission,
		constants.FieldID,
		constants.FieldSysPermissionSetPermission_PermissionSetID,
		constants.FieldSysPermissionSetPermission_PermissionAPIName,
		constants.FieldSysPermissionSetPermission_IsEnabled,
		constants.FieldSysPermissionSetPermission_IsEnabled, constants.FieldSysPermissionSetPermission_IsEnabled,
		constants.FieldLastModifiedDate,
	)
	if _, err := r.getExecutor(ctx).ExecContext(ctx, upsertSQL, utils.GenerateID(), permSetID, apiName, enabledInt); err != nil {
		return apperrors.NewInternalError("failed to set system permission", err)
	}
	return nil
}

// LockProfileLifecycles returns exact transaction-current Profile lifecycle
// facts in deterministic identity order. It is shared by actor authorization
// and metadata selectors so both roles use one lifecycle authority.
func (r *PermissionRepository) LockProfileLifecycles(ctx context.Context, profileIDs []string) ([]*models.Profile, error) {
	tx, ok := r.getExecutor(ctx).(*sql.Tx)
	if !ok {
		return nil, apperrors.NewInternalError("Profile lifecycle locks require the owning transaction", nil)
	}
	ids := append([]string(nil), profileIDs...)
	sort.Strings(ids)
	profiles := make([]*models.Profile, 0, len(ids))
	previous := ""
	for _, profileID := range ids {
		if strings.TrimSpace(profileID) == "" || profileID == previous {
			continue
		}
		previous = profileID
		var profile models.Profile
		err := tx.QueryRowContext(ctx, fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, `%s` FROM `%s` WHERE `%s` = ? LIMIT 1 FOR UPDATE",
			constants.FieldID, constants.FieldProfile_Name, constants.FieldProfile_Description,
			constants.FieldProfile_IsSystem, constants.FieldProfile_IsActive, constants.FieldProfile_IsDeleted,
			constants.TableProfile, constants.FieldID,
		), profileID).Scan(
			&profile.ID, &profile.Name, &profile.Description, &profile.IsSystem, &profile.IsActive, &profile.IsDeleted,
		)
		if err == sql.ErrNoRows {
			continue
		}
		if err != nil {
			return nil, apperrors.NewInternalError("failed to lock Profile lifecycle", err)
		}
		profiles = append(profiles, &profile)
	}
	return profiles, nil
}

// LockProfileLifecycle is the actor-authorization single-row view of the same
// exact lifecycle authority.
func (r *PermissionRepository) LockProfileLifecycle(ctx context.Context, profileID string) (*models.Profile, error) {
	profiles, err := r.LockProfileLifecycles(ctx, []string{profileID})
	if err != nil || len(profiles) == 0 {
		return nil, err
	}
	return profiles[0], nil
}

// GetAllProfiles retrieves all system profiles
func (r *PermissionRepository) GetAllProfiles(ctx context.Context) ([]*models.Profile, error) {
	qr := query.From(constants.TableProfile).
		Select([]string{
			constants.FieldID, constants.FieldProfile_Name, constants.FieldProfile_Description,
			constants.FieldProfile_IsSystem, constants.FieldProfile_IsActive, constants.FieldProfile_IsDeleted,
		}).
		OrderBy(constants.FieldProfile_Name, "ASC").
		Build()

	rows, err := r.getExecutor(ctx).QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query profiles", err)
	}
	defer rows.Close()

	var profiles []*models.Profile
	for rows.Next() {
		var p models.Profile
		if err := rows.Scan(&p.ID, &p.Name, &p.Description, &p.IsSystem, &p.IsActive, &p.IsDeleted); err != nil {
			return nil, apperrors.NewInternalError("failed to scan profile", err)
		}
		profiles = append(profiles, &p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating profiles", err)
	}
	return profiles, nil
}

// --- Salesforce-Compatible System Permissions ---

// ListSystemPermissions returns every active row from _System_Permission
// ordered deterministically. This is the row-driven replacement for the
// legacy Go-function catalog: adding a permission is a
// pack edit, not a backend release.
func (r *PermissionRepository) ListSystemPermissions(ctx context.Context) ([]models.SystemPermission, error) {
	query := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s` "+
			"FROM `%s` "+
			"WHERE `%s` = 1 "+
			"ORDER BY `%s`, `%s`",
		constants.FieldID,
		constants.FieldSysPermission_APIName,
		constants.FieldSysPermission_Label,
		constants.FieldSysPermission_Category,
		constants.FieldSysPermission_Description,
		constants.FieldSysPermission_IsSystem,
		constants.FieldSysPermission_IsActive,
		constants.FieldSysPermission_RequiresPermission,
		constants.TablePermission,
		constants.FieldSysPermission_IsActive,
		constants.FieldSysPermission_Category,
		constants.FieldSysPermission_APIName,
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, query)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to list system permissions", err)
	}
	defer rows.Close()

	out := make([]models.SystemPermission, 0, 80)
	for rows.Next() {
		var p models.SystemPermission
		var desc sql.NullString
		var req sql.NullString
		var isSys, isActive int
		if err := rows.Scan(&p.ID, &p.APIName, &p.Label, &p.Category, &desc, &isSys, &isActive, &req); err != nil {
			return nil, apperrors.NewInternalError("failed to scan system permission row", err)
		}
		if desc.Valid {
			d := desc.String
			p.Description = &d
		}
		if req.Valid {
			r := req.String
			p.RequiresPermission = &r
		}
		p.IsSystem = isSys != 0
		p.IsActive = isActive != 0
		out = append(out, p)
	}
	return out, rows.Err()
}

// GetEffectiveSystemPermissions retains the legacy map surface while delegating
// to the one authoritative version-qualified aggregate read path.
func (r *PermissionRepository) GetEffectiveSystemPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	systemPermissions, _, err := r.loadCurrentEffectivePermissionMaps(
		ctx,
		userID,
		profileID,
	)
	if err != nil {
		return nil, apperrors.NewInternalError(
			"failed to get effective system permissions",
			err,
		)
	}
	return systemPermissions, nil
}

// LockPermissionSet acquires a row-level lock on the PermissionSet and verifies it exists.
// Must be called inside a transaction. Returns false if the PermissionSet does not exist.
func (r *PermissionRepository) LockPermissionSet(ctx context.Context, permSetID string) (bool, error) {
	sqlQuery := fmt.Sprintf("SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 FOR UPDATE",
		constants.FieldID, constants.TablePermissionSet, constants.FieldID, constants.FieldPermissionSet_IsDeleted)
	var id string
	err := r.getExecutor(ctx).QueryRowContext(ctx, sqlQuery, permSetID).Scan(&id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return false, nil
		}
		return false, apperrors.NewInternalError("failed to lock PermissionSet", err)
	}
	return true, nil
}

// LockPermissionSetAuthoring acquires the canonical aggregate row lock and
// returns the current optimistic authoring revision. The revision lives on the
// PermissionSet root so no parallel lifecycle state can drift from it.
func (r *PermissionRepository) LockPermissionSetAuthoring(ctx context.Context, permSetID string) (ports.LockedPermissionAuthoringAggregate, bool, error) {
	exec, err := r.permissionAuthoringTransactionExecutor(ctx)
	if err != nil {
		return ports.LockedPermissionAuthoringAggregate{}, false, err
	}
	sqlQuery := fmt.Sprintf("SELECT `%s`, `%s`, `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 FOR UPDATE",
		constants.FieldPermissionSet_AuthoringRevision,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		constants.TablePermissionSet,
		constants.FieldID,
		constants.FieldPermissionSet_IsDeleted)
	var revision uint64
	var ownedByProfile bool
	var profileID sql.NullString
	err = exec.QueryRowContext(ctx, sqlQuery, permSetID).Scan(&revision, &ownedByProfile, &profileID)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return ports.LockedPermissionAuthoringAggregate{}, false, nil
		}
		return ports.LockedPermissionAuthoringAggregate{}, false, apperrors.NewInternalError("failed to lock PermissionSet authoring state", err)
	}
	if revision == 0 {
		return ports.LockedPermissionAuthoringAggregate{}, false, apperrors.NewInternalError("PermissionSet authoring revision is not initialized", nil)
	}
	role := ports.PermissionAuthoringAdditivePermissionSet
	if ownedByProfile {
		if !profileID.Valid || strings.TrimSpace(profileID.String) == "" {
			return ports.LockedPermissionAuthoringAggregate{}, false, apperrors.NewInternalError("Profile-owned PermissionSet has no Profile identity", nil)
		}
		role = ports.PermissionAuthoringProfileBaseline
	} else if profileID.Valid && strings.TrimSpace(profileID.String) != "" {
		return ports.LockedPermissionAuthoringAggregate{}, false, apperrors.NewInternalError("additive PermissionSet carries a Profile identity", nil)
	}
	return ports.LockedPermissionAuthoringAggregate{
		CanonicalPermissionSetID: permSetID,
		Role:                     role,
		Revision:                 revision,
	}, true, nil
}

// ReadPermissionSetAuthoringRevision is the recovery worker's post-commit
// read-back. Unlike LockPermissionSetAuthoring it intentionally uses an
// ordinary current read: the aggregate transaction is already committed and
// later revisions are valid proof that the requested revision became visible.
func (r *PermissionRepository) ReadPermissionSetAuthoringRevision(
	ctx context.Context,
	permSetID string,
) (uint64, bool, error) {
	if r == nil || r.db == nil || strings.TrimSpace(permSetID) == "" {
		return 0, false, errors.New("permission authoring read-back is unavailable")
	}
	query := fmt.Sprintf("SELECT `%s` FROM `%s` WHERE `%s` = ?",
		constants.FieldPermissionSet_AuthoringRevision,
		constants.TablePermissionSet,
		constants.FieldID,
	)
	var revision uint64
	if err := r.db.QueryRowContext(ctx, query, permSetID).Scan(&revision); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return 0, false, nil
		}
		return 0, false, apperrors.NewInternalError("failed to read back PermissionSet authoring revision", err)
	}
	if revision == 0 {
		return 0, false, apperrors.NewInternalError("PermissionSet authoring revision is not initialized", nil)
	}
	return revision, true, nil
}

// LockPermissionAuthoringCustomPermissions establishes the catalog segment of
// the global authoring lock order: PermissionSet root -> CustomPermission
// catalog -> Permission State. The later catalog projection remains the one
// semantic read path; this method owns only physical serialization.
func (r *PermissionRepository) LockPermissionAuthoringCustomPermissions(
	ctx context.Context,
	customPermissionIDs []string,
) error {
	if len(customPermissionIDs) == 0 {
		return nil
	}
	exec, err := r.permissionAuthoringTransactionExecutor(ctx)
	if err != nil {
		return err
	}
	seen := make(map[string]struct{}, len(customPermissionIDs))
	ids := make([]string, 0, len(customPermissionIDs))
	for _, id := range customPermissionIDs {
		id = strings.TrimSpace(id)
		if id == "" {
			return apperrors.NewInternalError("permission authoring CustomPermission lock identity is empty", nil)
		}
		if _, exists := seen[id]; exists {
			continue
		}
		seen[id] = struct{}{}
		ids = append(ids, id)
	}
	sort.Strings(ids)
	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")
	args := make([]any, len(ids))
	for index, id := range ids {
		args[index] = id
	}
	rows, err := exec.QueryContext(ctx, fmt.Sprintf(
		"SELECT `%s` FROM `%s` WHERE `%s` IN (%s) ORDER BY `%s` FOR UPDATE",
		constants.FieldID,
		constants.TableCustomPermission,
		constants.FieldID,
		placeholders,
		constants.FieldID,
	), args...)
	if err != nil {
		return apperrors.NewInternalError("failed to lock permission authoring CustomPermission catalog", err)
	}
	defer rows.Close()
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return apperrors.NewInternalError("failed to scan permission authoring CustomPermission catalog lock", err)
		}
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError("failed to iterate permission authoring CustomPermission catalog locks", err)
	}
	return nil
}

// ResolvePermissionAuthoringTarget locks and resolves the caller-selected
// identity for a newly accepted command. A Profile is locked before its
// profile-owned PermissionSet mapping is read, so the command can durably bind
// one canonical aggregate without racing a delete or rebind. A direct
// PermissionSet target locks that root itself. Completed replay never calls
// this method.
func (r *PermissionRepository) ResolvePermissionAuthoringTarget(
	ctx context.Context,
	targetKind string,
	targetID string,
) (string, bool, error) {
	exec, err := r.permissionAuthoringTransactionExecutor(ctx)
	if err != nil {
		return "", false, err
	}
	targetID = strings.TrimSpace(targetID)
	if targetID == "" {
		return "", false, apperrors.NewInternalError("permission authoring target identity is empty", nil)
	}

	switch strings.ToUpper(strings.TrimSpace(targetKind)) {
	case "PERMISSION_SET":
		var permissionSetID string
		var ownedByProfile bool
		err := exec.QueryRowContext(ctx, fmt.Sprintf(
			"SELECT `%s`, `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 FOR UPDATE",
			constants.FieldID,
			constants.FieldPermissionSet_IsOwnedByProfile,
			constants.TablePermissionSet,
			constants.FieldID,
			constants.FieldPermissionSet_IsDeleted,
		), targetID).Scan(&permissionSetID, &ownedByProfile)
		if errors.Is(err, sql.ErrNoRows) {
			return "", false, nil
		}
		if err != nil {
			return "", false, apperrors.NewInternalError("failed to resolve PermissionSet authoring target", err)
		}
		if ownedByProfile {
			return "", false, nil
		}
		return permissionSetID, true, nil

	case "PROFILE":
		var profileID string
		err := exec.QueryRowContext(ctx, fmt.Sprintf(
			"SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 FOR UPDATE",
			constants.FieldID,
			constants.TableProfile,
			constants.FieldID,
			constants.FieldProfile_IsDeleted,
		), targetID).Scan(&profileID)
		if errors.Is(err, sql.ErrNoRows) {
			return "", false, nil
		}
		if err != nil {
			return "", false, apperrors.NewInternalError("failed to lock Profile authoring target", err)
		}

		rows, err := exec.QueryContext(ctx, fmt.Sprintf(
			"SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 1 AND `%s` = 0 ORDER BY `%s` FOR UPDATE",
			constants.FieldID,
			constants.TablePermissionSet,
			constants.FieldPermissionSet_ProfileID,
			constants.FieldPermissionSet_IsOwnedByProfile,
			constants.FieldPermissionSet_IsDeleted,
			constants.FieldID,
		), profileID)
		if err != nil {
			return "", false, apperrors.NewInternalError("failed to resolve Profile PermissionSet authoring target", err)
		}
		defer rows.Close()
		var resolved []string
		for rows.Next() {
			var permissionSetID string
			if err := rows.Scan(&permissionSetID); err != nil {
				return "", false, apperrors.NewInternalError("failed to scan Profile PermissionSet authoring target", err)
			}
			resolved = append(resolved, permissionSetID)
			if len(resolved) > 1 {
				return "", false, apperrors.NewConflictErrorWithMessage("Profile", "Profile has multiple active owned PermissionSets")
			}
		}
		if err := rows.Err(); err != nil {
			return "", false, apperrors.NewInternalError("failed to iterate Profile PermissionSet authoring target", err)
		}
		if len(resolved) == 0 {
			return "", false, nil
		}
		return resolved[0], true, nil

	default:
		return "", false, apperrors.NewInternalError("unsupported permission authoring target kind", nil)
	}
}

// AdvancePermissionSetAuthoringRevision performs the one aggregate revision
// advance after a changed final image. The caller must hold the PermissionSet
// row lock in the same transaction.
func (r *PermissionRepository) AdvancePermissionSetAuthoringRevision(ctx context.Context, permSetID string, expected uint64) (uint64, error) {
	const writerID = "nexuscrm-persistence/perm_system.go#PermissionRepository.AdvancePermissionSetAuthoringRevision"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		var advanced uint64
		err := r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectEqual(constants.FieldID),
					permSetID,
				),
			},
			func(txCtx context.Context) error {
				var err error
				advanced, err = r.AdvancePermissionSetAuthoringRevision(txCtx, permSetID, expected)
				return err
			},
		)
		return advanced, err
	}
	if expected == 0 || expected == ^uint64(0) {
		return 0, apperrors.NewInternalError("invalid PermissionSet authoring revision", nil)
	}
	exec, err := r.permissionAuthoringTransactionExecutor(ctx)
	if err != nil {
		return 0, err
	}
	query := fmt.Sprintf(`UPDATE %s SET %s = %s + 1, %s = CURRENT_TIMESTAMP(6)
WHERE %s = ? AND %s = ? AND %s = 0`,
		constants.TablePermissionSet,
		constants.FieldPermissionSet_AuthoringRevision,
		constants.FieldPermissionSet_AuthoringRevision,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldPermissionSet_AuthoringRevision,
		constants.FieldPermissionSet_IsDeleted,
	)
	result, err := exec.ExecContext(ctx, query, permSetID, expected)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to advance PermissionSet authoring revision", err)
	}
	if err := requireOneAffected(result, "advance PermissionSet authoring revision"); err != nil {
		return 0, apperrors.NewConflictErrorWithMessage("PermissionSet", "PermissionSet authoring state changed")
	}
	return expected + 1, nil
}

func (r *PermissionRepository) permissionAuthoringTransactionExecutor(ctx context.Context) (Executor, error) {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx, nil
	}
	return nil, apperrors.NewInternalError("PermissionSet authoring operation requires an active transaction", nil)
}

func (r *PermissionRepository) permissionSetExists(ctx context.Context, permSetID string) (bool, error) {
	sqlQuery := fmt.Sprintf("SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 0 LIMIT 1",
		constants.FieldID, constants.TablePermissionSet, constants.FieldID, constants.FieldPermissionSet_IsDeleted)
	var id string
	err := r.getExecutor(ctx).QueryRowContext(ctx, sqlQuery, permSetID).Scan(&id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return false, nil
		}
		return false, apperrors.NewInternalError("failed to verify PermissionSet existence", err)
	}
	return true, nil
}

// GetPermissionSetGrants returns all system permission grants for a single PermissionSet
// from the SSoT table (_System_PermissionSetPermission).
func (r *PermissionRepository) GetPermissionSetGrants(ctx context.Context, permSetID string) (map[string]bool, error) {
	sqlQuery := fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE `%s` = ?",
		constants.FieldSysPermissionSetPermission_PermissionAPIName,
		constants.FieldSysPermissionSetPermission_IsEnabled,
		constants.TablePermissionSetPermission,
		constants.FieldSysPermissionSetPermission_PermissionSetID,
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, permSetID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query permission set grants", err)
	}
	defer rows.Close()

	perms := make(map[string]bool)
	sawGrantRow := false
	for rows.Next() {
		sawGrantRow = true
		var apiName string
		var enabled bool
		if err := rows.Scan(&apiName, &enabled); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission set grant", err)
		}
		perms[apiName] = enabled
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating permission set grants", err)
	}
	if !sawGrantRow {
		exists, err := r.permissionSetExists(ctx, permSetID)
		if err != nil {
			return nil, err
		}
		if !exists {
			return nil, apperrors.NewNotFoundError("PermissionSet", permSetID)
		}
	}
	return perms, nil
}

// GetPermissionSetGrantsMany batches grant lookups for many PermissionSet IDs.
// Returns a map keyed by permSetID, each value a map of apiName→enabled.
// IDs with no grants map to an empty (non-nil) inner map. IDs that do not
// refer to existing PermissionSets are silently omitted — callers that care
// about existence validate separately (Retrieve already loads the base row
// before invoking projection).
func (r *PermissionRepository) GetPermissionSetGrantsMany(ctx context.Context, permSetIDs []string) (map[string]map[string]bool, error) {
	out := make(map[string]map[string]bool, len(permSetIDs))
	if len(permSetIDs) == 0 {
		return out, nil
	}
	for _, id := range permSetIDs {
		out[id] = map[string]bool{}
	}
	placeholders := make([]string, len(permSetIDs))
	args := make([]interface{}, len(permSetIDs))
	for i, id := range permSetIDs {
		placeholders[i] = "?"
		args[i] = id
	}
	sqlQuery := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s` FROM `%s` WHERE `%s` IN (%s)",
		constants.FieldSysPermissionSetPermission_PermissionSetID,
		constants.FieldSysPermissionSetPermission_PermissionAPIName,
		constants.FieldSysPermissionSetPermission_IsEnabled,
		constants.TablePermissionSetPermission,
		constants.FieldSysPermissionSetPermission_PermissionSetID,
		strings.Join(placeholders, ","),
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to batch-query permission set grants", err)
	}
	defer rows.Close()
	for rows.Next() {
		var permSetID, apiName string
		var enabled bool
		if err := rows.Scan(&permSetID, &apiName, &enabled); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission set grant row", err)
		}
		out[permSetID][apiName] = enabled
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating batch permission set grants", err)
	}
	return out, nil
}

// GetEffectiveCustomPermissions retains the legacy map surface while
// delegating to the one authoritative version-qualified aggregate read path.
func (r *PermissionRepository) GetEffectiveCustomPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	_, customPermissions, err := r.loadCurrentEffectivePermissionMaps(
		ctx,
		userID,
		profileID,
	)
	if err != nil {
		return nil, apperrors.NewInternalError(
			"failed to get effective custom permissions",
			err,
		)
	}
	return customPermissions, nil
}

// UpsertSystemPermission creates or updates a system permission definition using query builder
func (r *PermissionRepository) UpsertSystemPermission(ctx context.Context, apiName, label, category, description string) error {
	const writerID = "nexuscrm-persistence/perm_system.go#PermissionRepository.UpsertSystemPermission"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				permissionStateDirectTargetFor(
					constants.TablePermission,
					permissionStateDirectEqual(constants.FieldSysPermission_APIName),
					apiName,
				),
			},
			func(txCtx context.Context) error {
				return r.UpsertSystemPermission(txCtx, apiName, label, category, description)
			},
		)
	}
	qb := query.Insert(constants.TablePermission, map[string]interface{}{
		constants.FieldSysPermission_APIName:     apiName,
		constants.FieldSysPermission_Label:       label,
		constants.FieldSysPermission_Category:    category,
		constants.FieldSysPermission_Description: description,
		constants.FieldSysPermission_IsActive:    true,
	})
	result := qb.Build()

	// Append ON DUPLICATE KEY UPDATE clause
	sqlQuery := result.SQL + " ON DUPLICATE KEY UPDATE " +
		constants.FieldSysPermission_Label + " = VALUES(" + constants.FieldSysPermission_Label + "), " +
		constants.FieldSysPermission_Category + " = VALUES(" + constants.FieldSysPermission_Category + "), " +
		constants.FieldSysPermission_Description + " = VALUES(" + constants.FieldSysPermission_Description + ")"

	if _, err := r.getExecutor(ctx).ExecContext(ctx, sqlQuery, result.Params...); err != nil {
		return apperrors.NewInternalError("failed to upsert system permission", err)
	}
	return nil
}