package persistence

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

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

// 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 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 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 {
	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
}

// 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,
		}).
		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); 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 calculates all system permissions for a user.
// SSoT: _System_PermissionSetPermission (normalized row table).
// Resolution: Layer 1 (Profile-owned PS + directly assigned PS) → Layer 2 (PSG with muting).
// All layers are additive (OR). Muting only subtracts within its PSG scope.
func (r *PermissionRepository) GetEffectiveSystemPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	effective := make(map[string]bool)

	// Layer 1: All permissions from user's PermissionSets (profile-owned + directly assigned).
	// Single query replaces the old 3-layer approach (ProfilePermission + boolean columns + PermSetPermission).
	directPerms, err := r.getAllPermissionSetPermissions(ctx, userID, profileID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to get permission set permissions", err)
	}
	for perm, enabled := range directPerms {
		if enabled {
			effective[perm] = true
		}
	}

	// Layer 2: Permission Set Group assignments from normalized rows (with per-PSG muting).
	// Muting is applied within each PSG before union — it cannot reach Profile or
	// direct PermSet grants. See getGroupSystemPermissions for details.
	groupPerms, _, err := r.getGroupSystemPermissions(ctx, userID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to get group permissions", err)
	}
	for perm, enabled := range groupPerms {
		if enabled {
			effective[perm] = true
		}
	}

	return effective, 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
}

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
}

// getAllPermissionSetPermissions returns system permissions from the SSoT table
// for all PermissionSets associated with the user (profile-owned + directly assigned).
func (r *PermissionRepository) getAllPermissionSetPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	// INNER JOIN _System_Permission with IsActive=1 so grants referencing
	// deactivated (tombstoned) catalog rows are invisible at runtime. The
	// catalog is the SSoT for what a permission even IS; a grant that
	// points at a deactivated catalog row must not flow to
	// CheckSystemPermission / /permissions/effective. Without this join,
	// Batch 1b's tombstone would hide the permission from describe but
	// still silently honor any existing grants — an inconsistency the
	// reviewer flagged as part of the "catalog as SSoT" contract.
	sqlQuery := `
		SELECT psp.` + constants.FieldSysPermissionSetPermission_PermissionAPIName + `, psp.` + constants.FieldSysPermissionSetPermission_IsEnabled + `
		FROM ` + constants.TablePermissionSetPermission + ` psp
		INNER JOIN ` + constants.TablePermission + ` perm
			ON perm.` + constants.FieldSysPermission_APIName + ` = psp.` + constants.FieldSysPermissionSetPermission_PermissionAPIName + `
			AND perm.` + constants.FieldSysPermission_IsActive + ` = 1
		WHERE psp.` + constants.FieldSysPermissionSetPermission_PermissionSetID + ` IN (
			SELECT ps.` + constants.FieldID + ` FROM ` + constants.TablePermissionSet + ` ps
			WHERE ps.` + constants.FieldPermissionSet_IsDeleted + ` = 0
			  AND (
				(ps.` + constants.FieldPermissionSet_ProfileID + ` = ? AND ps.` + constants.FieldPermissionSet_IsOwnedByProfile + ` = 1)
				OR ps.` + constants.FieldID + ` IN (
					SELECT psa.` + constants.FieldPermissionSetAssignment_PermissionSetID + `
					FROM ` + constants.TablePermissionSetAssignment + ` psa
					WHERE psa.` + constants.FieldPermissionSetAssignment_AssigneeID + ` = ?
					  AND psa.` + constants.FieldPermissionSetAssignment_IsDeleted + ` = 0
				)
			  )
		)
	`
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, profileID, userID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query all permission set permissions", err)
	}
	defer rows.Close()

	perms := make(map[string]bool)
	for rows.Next() {
		var apiName string
		var enabled bool
		if err := rows.Scan(&apiName, &enabled); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission set permission", err)
		}
		if enabled {
			perms[apiName] = true
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating permission set permissions", err)
	}
	return perms, nil
}

// psgPermRow holds a single permission row associated with a PSG.
type psgPermRow struct {
	groupID string
	apiName string
	enabled bool
}

// getGroupSystemPermissions returns the effective permissions from all Permission Set Groups
// assigned to the user, with muting applied per-PSG (not globally).
//
// Salesforce muting semantics: a MutingPermissionSet is scoped to a single PSG.
// It can only mute permissions that were granted by that same PSG's component PermSets.
// It must NOT mute permissions granted by the Profile or direct PermSet assignments.
func (r *PermissionRepository) getGroupSystemPermissions(ctx context.Context, userID string) (map[string]bool, []string, error) {
	// Bug fix DA1-3: removed the spurious INNER JOIN PermissionSetAssignment.
	// The correct join path is:
	//   _System_PermissionSetPermission -> PermissionSetGroupComponent -> PermissionSetGroupAssignment
	// INNER JOIN _System_Permission with IsActive=1 matches
	// getAllPermissionSetPermissions — deactivated catalog rows cannot
	// flow to effective-permission resolution regardless of which path
	// the grant came through (direct PS assignment or PSG).
	groupQuery := `
		SELECT psga.` + constants.FieldPermissionSetGroupAssignment_GroupID + `, psp.` + constants.FieldSysPermissionSetPermission_PermissionAPIName + `, psp.` + constants.FieldSysPermissionSetPermission_IsEnabled + `
		FROM ` + constants.TablePermissionSetPermission + ` psp
		INNER JOIN ` + constants.TablePermission + ` perm
			ON perm.` + constants.FieldSysPermission_APIName + ` = psp.` + constants.FieldSysPermissionSetPermission_PermissionAPIName + `
			AND perm.` + constants.FieldSysPermission_IsActive + ` = 1
		INNER JOIN ` + constants.TablePermissionSetGroupComponent + ` psgc ON psp.` + constants.FieldSysPermissionSetPermission_PermissionSetID + ` = psgc.` + constants.FieldPermissionSetGroupComponent_PermissionSetID + `
		INNER JOIN ` + constants.TablePermissionSetGroupAssignment + ` psga ON psgc.` + constants.FieldPermissionSetGroupComponent_GroupID + ` = psga.` + constants.FieldPermissionSetGroupAssignment_GroupID + `
		WHERE psga.` + constants.FieldPermissionSetGroupAssignment_AssigneeID + ` = ? AND psga.` + constants.FieldPermissionSetGroupAssignment_IsDeleted + ` = 0
	`
	rows, err := r.getExecutor(ctx).QueryContext(ctx, groupQuery, userID)
	if err != nil {
		return nil, nil, apperrors.NewInternalError("failed to query group system permissions", err)
	}
	defer rows.Close()

	// Collect per-PSG permissions (Bug fix DA1-1: track by groupID for scoped muting)
	psgPerms := make(map[string]map[string]bool) // groupID -> permName -> enabled
	for rows.Next() {
		var row psgPermRow
		if err := rows.Scan(&row.groupID, &row.apiName, &row.enabled); err != nil {
			return nil, nil, apperrors.NewInternalError("failed to scan group permission", err)
		}
		if psgPerms[row.groupID] == nil {
			psgPerms[row.groupID] = make(map[string]bool)
		}
		psgPerms[row.groupID][row.apiName] = row.enabled
	}
	if err := rows.Err(); err != nil {
		return nil, nil, apperrors.NewInternalError("error iterating group permissions", err)
	}

	// Get muted permissions per PSG
	mutedQuery := `
		SELECT psga.` + constants.FieldPermissionSetGroupAssignment_GroupID + `, mpp.` + constants.FieldSysMutingPermissionSetPermission_PermissionAPIName + `
		FROM ` + constants.TableMutingPermissionSetPermission + ` mpp
		INNER JOIN ` + constants.TableMutingPermissionSet + ` mps ON mpp.` + constants.FieldSysMutingPermissionSetPermission_MutingPermissionSetID + ` = mps.Id
		INNER JOIN ` + constants.TablePermissionSetGroupAssignment + ` psga ON mps.` + constants.FieldMutingPermissionSet_GroupID + ` = psga.` + constants.FieldPermissionSetGroupAssignment_GroupID + `
		WHERE psga.` + constants.FieldPermissionSetGroupAssignment_AssigneeID + ` = ? AND psga.` + constants.FieldPermissionSetGroupAssignment_IsDeleted + ` = 0
	`
	mutedRows, err := r.getExecutor(ctx).QueryContext(ctx, mutedQuery, userID)
	if err != nil {
		return nil, nil, apperrors.NewInternalError("failed to query muted permissions", err)
	}
	defer mutedRows.Close()

	// Collect muted permissions per PSG
	psgMuted := make(map[string][]string) // groupID -> []permName
	for mutedRows.Next() {
		var groupID, apiName string
		if err := mutedRows.Scan(&groupID, &apiName); err != nil {
			return nil, nil, apperrors.NewInternalError("failed to scan muted permission", err)
		}
		psgMuted[groupID] = append(psgMuted[groupID], apiName)
	}
	if err := mutedRows.Err(); err != nil {
		return nil, nil, apperrors.NewInternalError("error iterating muted permissions", err)
	}

	// Bug fix DA1-1: apply muting per-PSG, then union into global effective map.
	// Muting only affects permissions granted by the same PSG — it cannot reach
	// Profile or direct PermSet grants.
	effective := make(map[string]bool)
	var allMuted []string
	for groupID, groupPermMap := range psgPerms {
		// Apply this PSG's muting to its own permissions only
		mutedSet := make(map[string]bool)
		for _, m := range psgMuted[groupID] {
			mutedSet[m] = true
		}
		for perm, enabled := range groupPermMap {
			if enabled && !mutedSet[perm] {
				effective[perm] = true
			}
		}
		allMuted = append(allMuted, psgMuted[groupID]...)
	}

	return effective, allMuted, nil
}

// GetEffectiveCustomPermissions returns all custom permissions for a user
func (r *PermissionRepository) GetEffectiveCustomPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error) {
	sqlQuery := `
		SELECT DISTINCT cp.` + constants.FieldSysCustomPermission_APIName + `
		FROM ` + constants.TableCustomPermission + ` cp
		LEFT JOIN ` + constants.TableProfileCustomPermission + ` pcp ON cp.Id = pcp.` + constants.FieldProfileCustomPermission_CustomPermissionID + ` AND pcp.` + constants.FieldProfileCustomPermission_ProfileID + ` = ?
		LEFT JOIN ` + constants.TablePermissionSetCustomPermission + ` pscp ON cp.Id = pscp.` + constants.FieldPermissionSetCustomPermission_CustomPermissionID + `
		LEFT JOIN ` + constants.TablePermissionSetAssignment + ` psa ON pscp.` + constants.FieldPermissionSetCustomPermission_PermissionSetID + ` = psa.` + constants.FieldPermissionSetAssignment_PermissionSetID + ` AND psa.` + constants.FieldPermissionSetAssignment_AssigneeID + ` = ? AND psa.` + constants.FieldPermissionSetAssignment_IsDeleted + ` = 0
		WHERE cp.` + constants.FieldSysCustomPermission_IsActive + ` = 1 AND cp.` + constants.FieldSysCustomPermission_IsDeleted + ` = 0 AND (pcp.Id IS NOT NULL OR psa.Id IS NOT NULL)
	`
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlQuery, profileID, userID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query effective custom permissions", err)
	}
	defer rows.Close()

	perms := make(map[string]bool)
	for rows.Next() {
		var apiName string
		if err := rows.Scan(&apiName); err != nil {
			return nil, apperrors.NewInternalError("failed to scan custom permission", err)
		}
		perms[apiName] = true
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating custom permissions", err)
	}
	return perms, 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 {
	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
}