package persistence

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

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

func normalizeFieldPermissionLookup(objectAPIName, fieldAPIName string) (string, string, bool) {
	objectAPIName = strings.TrimSpace(objectAPIName)
	fieldObjectAPIName, fieldComponent := fieldpermissions.SplitFieldReference(objectAPIName, fieldAPIName)
	fieldObjectAPIName = strings.TrimSpace(fieldObjectAPIName)
	fieldComponent = strings.TrimSpace(fieldComponent)
	if objectAPIName == "" || fieldComponent == "" {
		return "", "", false
	}
	if !strings.EqualFold(objectAPIName, fieldObjectAPIName) {
		return "", "", false
	}
	return strings.ToLower(objectAPIName), strings.ToLower(fieldComponent), true
}

func fieldPermissionFieldPredicate(fieldExpr string) string {
	return fmt.Sprintf("%s IN (?, ?)", fieldExpr)
}

func fieldPermissionFieldParams(objectAPINameLower, fieldAPINameLower string) []interface{} {
	return []interface{}{fieldAPINameLower, objectAPINameLower + "." + fieldAPINameLower}
}

func profileOwnedPermissionParentPredicate(parentField string) string {
	return fmt.Sprintf("%s IN (%s)", parentField, profileOwnedPermissionSetIDsSQL())
}

func effectivePermissionParentPredicate(parentField string) string {
	return fmt.Sprintf("%s IN (%s UNION %s)", parentField, profileOwnedPermissionSetIDsSQL(), additivePermissionSetIDsSQL())
}

// LoadObjectPermission queries the database for a specific object permission
func (r *PermissionRepository) LoadObjectPermission(ctx context.Context, profileID, objectAPIName string) (*models.ObjectPermissions, error) {
	qr := query.From(constants.TableObjectPermissions).
		Select([]string{
			constants.FieldObjectPermissions_ParentID, constants.FieldObjectPermissions_SobjectType,
			constants.FieldObjectPermissions_PermissionsRead, constants.FieldObjectPermissions_PermissionsCreate,
			constants.FieldObjectPermissions_PermissionsEdit, constants.FieldObjectPermissions_PermissionsDelete,
			constants.FieldObjectPermissions_PermissionsViewAllRecords, constants.FieldObjectPermissions_PermissionsModifyAllRecords,
		}).
		WhereRaw(profileOwnedPermissionParentPredicate(constants.FieldObjectPermissions_ParentID), []interface{}{profileID}).
		Where(constants.FieldObjectPermissions_SobjectType, strings.ToLower(objectAPIName)).
		Where(constants.FieldObjectPermissions_IsDeleted, false).
		Limit(1).
		SkipID().
		Build()

	var p models.ObjectPermissions
	err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(
		&p.ParentID, &p.SobjectType,
		&p.PermissionsRead, &p.PermissionsCreate, &p.PermissionsEdit, &p.PermissionsDelete,
		&p.PermissionsViewAllRecords, &p.PermissionsModifyAllRecords,
	)

	if err == sql.ErrNoRows {
		return nil, nil // No permission record = no access
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to load object permission", err)
	}

	return &p, nil
}

// LoadFieldPermission queries the database for a specific field permission
func (r *PermissionRepository) LoadFieldPermission(ctx context.Context, profileID, objectAPIName, fieldAPIName string) (*models.FieldPermissions, error) {
	objectName, fieldName, ok := normalizeFieldPermissionLookup(objectAPIName, fieldAPIName)
	if !ok {
		return nil, nil
	}

	fieldExpr := fmt.Sprintf("`%s`", constants.FieldFieldPermissions_Field)
	sqlQuery := fmt.Sprintf(`
		SELECT %s, %s, %s, %s, %s
		FROM %s
		WHERE %s IN (
			%s
		)
		AND %s = ?
		AND %s
		AND %s = 0
		LIMIT 1
	`,
		constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field,
		constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_ParentID,
		profileOwnedPermissionSetIDsSQL(),
		constants.FieldFieldPermissions_SobjectType,
		fieldPermissionFieldPredicate(fieldExpr),
		constants.FieldFieldPermissions_IsDeleted,
	)
	params := append([]interface{}{profileID, objectName}, fieldPermissionFieldParams(objectName, fieldName)...)

	var p models.FieldPermissions
	err := r.getExecutor(ctx).QueryRowContext(ctx, sqlQuery, params...).Scan(
		&p.ParentID, &p.SobjectType, &p.Field,
		&p.PermissionsRead, &p.PermissionsEdit,
	)

	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to load field permission", err)
	}

	return &p, nil
}

// LoadEffectiveObjectPermission loads permissions considering Profile AND Permission Sets
func (r *PermissionRepository) LoadEffectiveObjectPermission(ctx context.Context, user *models.UserSession, objectAPIName string) (*models.ObjectPermissions, error) {
	aggCols := strings.Join([]string{
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsRead),
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsCreate),
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsEdit),
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsDelete),
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsViewAllRecords),
		fmt.Sprintf("MAX(%s)", constants.FieldObjectPermissions_PermissionsModifyAllRecords),
	}, ", ")

	query := fmt.Sprintf(`
		SELECT %s
		FROM %s
		WHERE %s = ?
		AND %s = 0
		AND %s
	`, aggCols, constants.TableObjectPermissions, constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_IsDeleted,
		effectivePermissionParentPredicate(constants.FieldObjectPermissions_ParentID))

	var p models.ObjectPermissions
	p.SobjectType = objectAPIName

	var rr, c, e, d, va, ma sql.NullBool

	lowered := strings.ToLower(objectAPIName)

	err := r.getExecutor(ctx).QueryRowContext(ctx, query, lowered, user.ProfileID, user.ID, user.ID).Scan(
		&rr, &c, &e, &d, &va, &ma,
	)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to load effective object permission", err)
	}

	if !rr.Valid {
		return nil, nil // No access
	}

	p.PermissionsRead = rr.Bool
	p.PermissionsCreate = c.Bool
	p.PermissionsEdit = e.Bool
	p.PermissionsDelete = d.Bool
	p.PermissionsViewAllRecords = va.Bool
	p.PermissionsModifyAllRecords = ma.Bool

	return &p, nil
}

// LoadEffectiveFieldPermission loads field permissions considering Profile AND Permission Sets
func (r *PermissionRepository) LoadEffectiveFieldPermission(ctx context.Context, user *models.UserSession, objectAPIName, fieldAPIName string) (*models.FieldPermissions, error) {
	objectName, fieldName, ok := normalizeFieldPermissionLookup(objectAPIName, fieldAPIName)
	if !ok {
		return nil, nil
	}

	aggCols := strings.Join([]string{
		fmt.Sprintf("MAX(%s)", constants.FieldFieldPermissions_PermissionsRead),
		fmt.Sprintf("MAX(%s)", constants.FieldFieldPermissions_PermissionsEdit),
	}, ", ")

	// FieldPermissions are additive across active permission sources: a false
	// source row does not mute a true source row.
	query := fmt.Sprintf(`
		SELECT %s
		FROM %s
		WHERE %s = ?
		AND %s = 0
		AND %s
		AND %s
	`, aggCols, constants.TableFieldPermissions,
		constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_IsDeleted,
		fieldPermissionFieldPredicate(fmt.Sprintf("`%s`", constants.FieldFieldPermissions_Field)),
		effectivePermissionParentPredicate(constants.FieldFieldPermissions_ParentID))

	var readable, editable sql.NullBool
	params := append([]interface{}{objectName}, fieldPermissionFieldParams(objectName, fieldName)...)
	params = append(params, user.ProfileID, user.ID, user.ID)
	err := r.getExecutor(ctx).QueryRowContext(ctx, query, params...).Scan(&readable, &editable)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to load effective field permission", err)
	}

	if !readable.Valid {
		return nil, nil
	}

	return &models.FieldPermissions{
		SobjectType:     objectAPIName,
		Field:           fieldpermissions.FieldComponent(objectAPIName, fieldAPIName),
		PermissionsRead: readable.Bool,
		PermissionsEdit: editable.Valid && editable.Bool,
	}, nil
}

// ListObjectPermissions retrieves all object permissions for a profile
func (r *PermissionRepository) ListObjectPermissions(ctx context.Context, profileID string) ([]models.ObjectPermissions, error) {
	qr := query.From(constants.TableObjectPermissions).
		Select([]string{
			constants.FieldObjectPermissions_ParentID, constants.FieldObjectPermissions_SobjectType,
			constants.FieldObjectPermissions_PermissionsRead, constants.FieldObjectPermissions_PermissionsCreate,
			constants.FieldObjectPermissions_PermissionsEdit, constants.FieldObjectPermissions_PermissionsDelete,
			constants.FieldObjectPermissions_PermissionsViewAllRecords, constants.FieldObjectPermissions_PermissionsModifyAllRecords,
		}).
		WhereRaw(profileOwnedPermissionParentPredicate(constants.FieldObjectPermissions_ParentID), []interface{}{profileID}).
		Where(constants.FieldObjectPermissions_IsDeleted, false).
		SkipID().
		Build()

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

	var perms []models.ObjectPermissions
	for rows.Next() {
		var p models.ObjectPermissions
		if err := rows.Scan(&p.ParentID, &p.SobjectType, &p.PermissionsRead, &p.PermissionsCreate, &p.PermissionsEdit, &p.PermissionsDelete, &p.PermissionsViewAllRecords, &p.PermissionsModifyAllRecords); err != nil {
			return nil, apperrors.NewInternalError("failed to scan object permission", err)
		}
		perms = append(perms, p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating object permissions", err)
	}

	return perms, nil
}

// ListFieldPermissions retrieves all field permissions for a profile
func (r *PermissionRepository) ListFieldPermissions(ctx context.Context, profileID string) ([]models.FieldPermissions, error) {
	qr := query.From(constants.TableFieldPermissions).
		Select([]string{
			constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field,
			constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		}).
		WhereRaw(profileOwnedPermissionParentPredicate(constants.FieldFieldPermissions_ParentID), []interface{}{profileID}).
		Where(constants.FieldFieldPermissions_IsDeleted, false).
		SkipID().
		Build()

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

	var perms []models.FieldPermissions
	for rows.Next() {
		var p models.FieldPermissions
		if err := rows.Scan(&p.ParentID, &p.SobjectType, &p.Field, &p.PermissionsRead, &p.PermissionsEdit); err != nil {
			return nil, apperrors.NewInternalError("failed to scan field permission", err)
		}
		perms = append(perms, p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating field permissions", err)
	}

	return perms, nil
}

// UpsertObjectPermission creates or updates an object permission
func (r *PermissionRepository) UpsertObjectPermission(ctx context.Context, perm models.ObjectPermissions) error {
	return r.upsertObjectPermission(ctx, r.db, perm)
}

// UpsertObjectPermissionTx creates or updates an object permission within a transaction
func (r *PermissionRepository) UpsertObjectPermissionTx(ctx context.Context, tx *sql.Tx, perm models.ObjectPermissions) error {
	if r.permissionStateDirectCoordinator != nil {
		active, ok := r.getExecutor(ctx).(*sql.Tx)
		if !ok || active != tx ||
			!r.permissionStateDirectCoordinator.transactions.ownsPessimisticTx(ctx) {
			return apperrors.NewInternalError(
				"object permission transaction must be owned by the Permission State direct writer coordinator",
				nil,
			)
		}
	}
	return r.upsertObjectPermission(ctx, tx, perm)
}

func (r *PermissionRepository) upsertObjectPermission(ctx context.Context, exec Executor, perm models.ObjectPermissions) error {
	const writerID = "nexuscrm-persistence/perm_object_field.go#PermissionRepository.upsertObjectPermission"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			objectPermissionStateTargets([]models.ObjectPermissions{perm}),
			func(txCtx context.Context) error {
				return r.upsertObjectPermission(txCtx, r.getExecutor(txCtx), perm)
			},
		)
	}
	id := utils.GenerateID()
	values := map[string]interface{}{
		constants.FieldID:                                            id,
		constants.FieldObjectPermissions_ParentID:                    perm.ParentID,
		constants.FieldObjectPermissions_SobjectType:                 perm.SobjectType,
		constants.FieldObjectPermissions_PermissionsRead:             perm.PermissionsRead,
		constants.FieldObjectPermissions_PermissionsCreate:           perm.PermissionsCreate,
		constants.FieldObjectPermissions_PermissionsEdit:             perm.PermissionsEdit,
		constants.FieldObjectPermissions_PermissionsDelete:           perm.PermissionsDelete,
		constants.FieldObjectPermissions_PermissionsViewAllRecords:   perm.PermissionsViewAllRecords,
		constants.FieldObjectPermissions_PermissionsModifyAllRecords: perm.PermissionsModifyAllRecords,
		constants.FieldObjectPermissions_IsDeleted:                   false,
		constants.FieldCreatedDate:                                   time.Now(),
		constants.FieldLastModifiedDate:                              time.Now(),
	}

	qr := query.Insert(constants.TableObjectPermissions, values).
		OnDuplicateKeyUpdate(map[string]interface{}{
			constants.FieldObjectPermissions_PermissionsRead:             perm.PermissionsRead,
			constants.FieldObjectPermissions_PermissionsCreate:           perm.PermissionsCreate,
			constants.FieldObjectPermissions_PermissionsEdit:             perm.PermissionsEdit,
			constants.FieldObjectPermissions_PermissionsDelete:           perm.PermissionsDelete,
			constants.FieldObjectPermissions_PermissionsViewAllRecords:   perm.PermissionsViewAllRecords,
			constants.FieldObjectPermissions_PermissionsModifyAllRecords: perm.PermissionsModifyAllRecords,
			constants.FieldObjectPermissions_IsDeleted:                   false,
			constants.FieldLastModifiedDate:                              time.Now(),
		}).
		Build()

	if _, err := exec.ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to upsert object permission", err)
	}
	return nil
}

// UpsertFieldPermission creates or updates a field permission
func (r *PermissionRepository) UpsertFieldPermission(ctx context.Context, perm models.FieldPermissions) error {
	return r.upsertFieldPermission(ctx, r.db, perm)
}

// UpsertFieldPermissionTx creates or updates a field permission within a transaction
func (r *PermissionRepository) UpsertFieldPermissionTx(ctx context.Context, tx *sql.Tx, perm models.FieldPermissions) error {
	if r.permissionStateDirectCoordinator != nil {
		active, ok := r.getExecutor(ctx).(*sql.Tx)
		if !ok || active != tx ||
			!r.permissionStateDirectCoordinator.transactions.ownsPessimisticTx(ctx) {
			return apperrors.NewInternalError(
				"field permission transaction must be owned by the Permission State direct writer coordinator",
				nil,
			)
		}
	}
	return r.upsertFieldPermission(ctx, tx, perm)
}

func (r *PermissionRepository) upsertFieldPermission(ctx context.Context, exec Executor, perm models.FieldPermissions) error {
	if !fieldpermissions.AccessCoherent(perm.PermissionsRead, perm.PermissionsEdit) {
		return apperrors.NewValidationError(constants.FieldFieldPermissions_PermissionsEdit, "field edit permission requires field read permission")
	}
	const writerID = "nexuscrm-persistence/perm_object_field.go#PermissionRepository.upsertFieldPermission"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			fieldPermissionStateTargets([]models.FieldPermissions{perm}),
			func(txCtx context.Context) error {
				return r.upsertFieldPermission(txCtx, r.getExecutor(txCtx), perm)
			},
		)
	}
	id := utils.GenerateID()
	values := map[string]interface{}{
		constants.FieldID:                               id,
		constants.FieldObjectPermissions_ParentID:       perm.ParentID,
		constants.FieldObjectPermissions_SobjectType:    perm.SobjectType,
		constants.FieldFieldPermissions_Field:           perm.Field,
		constants.FieldFieldPermissions_PermissionsRead: perm.PermissionsRead,
		constants.FieldFieldPermissions_PermissionsEdit: perm.PermissionsEdit,
		constants.FieldFieldPermissions_IsDeleted:       false,
		constants.FieldCreatedDate:                      time.Now(),
		constants.FieldLastModifiedDate:                 time.Now(),
	}

	qr := query.Insert(constants.TableFieldPermissions, values).
		OnDuplicateKeyUpdate(map[string]interface{}{
			constants.FieldFieldPermissions_PermissionsRead: perm.PermissionsRead,
			constants.FieldFieldPermissions_PermissionsEdit: perm.PermissionsEdit,
			constants.FieldFieldPermissions_IsDeleted:       false,
			constants.FieldLastModifiedDate:                 time.Now(),
		}).
		Build()

	if _, err := exec.ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to upsert field permission", err)
	}
	return nil
}

// CountObjectPermissionsByProfile returns the number of object permission rows for a given profile
func (r *PermissionRepository) CountObjectPermissionsByProfile(ctx context.Context, profileID string) (int, error) {
	var count int
	err := r.getExecutor(ctx).QueryRowContext(ctx,
		fmt.Sprintf("SELECT COUNT(*) FROM `%s` WHERE %s AND `%s` = 0",
			constants.TableObjectPermissions,
			profileOwnedPermissionParentPredicate(fmt.Sprintf("`%s`", constants.FieldObjectPermissions_ParentID)),
			constants.FieldObjectPermissions_IsDeleted),
		profileID,
	).Scan(&count)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to count object permissions", err)
	}
	return count, nil
}

// InsertMissingObjectPermissions seeds ObjectPermissions rows for the platform's
// built-in default profiles (its only callers build rows via resolvePermRule,
// which yields nothing for import-/admin-owned profiles — see the source-limit
// in permission_seeder.go). It must NOT overwrite the six CRUD flags on a row
// that already exists, so a default-seed pass never clobbers or widens an
// admin-edited live grant.
//
// On a duplicate-key collision it sets `IsDeleted = 0`. For a LIVE row this is a
// no-op (IsDeleted is already 0; flags untouched → additive). For a SOFT-DELETED
// row (tombstone) it REVIVES the row, because built-in default coverage is a
// platform-guaranteed invariant enforced by VerifyProfilePermissionCoverage: a
// tombstoned built-in default would otherwise be an unhealable coverage gap that
// fatals startup. (This preserves main's prior seed-path self-heal, where the
// upsert revived rows via `IsDeleted = VALUES(IsDeleted)`.)
// Using ON DUPLICATE KEY UPDATE rather than INSERT IGNORE keeps genuine row
// errors (NOT NULL, FK, truncation) surfacing instead of being swallowed.
func (r *PermissionRepository) InsertMissingObjectPermissions(ctx context.Context, perms []models.ObjectPermissions) error {
	if len(perms) == 0 {
		return nil
	}
	const writerID = "nexuscrm-persistence/perm_object_field.go#PermissionRepository.InsertMissingObjectPermissions"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriterGroup(ctx, func(txCtx context.Context) error {
			return forEachPermissionStateDirectProjectionBatch(perms, func(batch []models.ObjectPermissions) error {
				return r.runPermissionStateDirectWriter(
					txCtx,
					writerID,
					objectPermissionStateTargets(batch),
					func(batchCtx context.Context) error {
						return r.InsertMissingObjectPermissions(batchCtx, batch)
					},
				)
			})
		})
	}

	const batchSize = 200
	for i := 0; i < len(perms); i += batchSize {
		end := i + batchSize
		if end > len(perms) {
			end = len(perms)
		}
		batch := perms[i:end]

		var records []map[string]interface{}
		for _, perm := range batch {
			records = append(records, map[string]interface{}{
				constants.FieldID:                                            utils.GenerateID(),
				constants.FieldObjectPermissions_ParentID:                    perm.ParentID,
				constants.FieldObjectPermissions_SobjectType:                 perm.SobjectType,
				constants.FieldObjectPermissions_PermissionsRead:             perm.PermissionsRead,
				constants.FieldObjectPermissions_PermissionsCreate:           perm.PermissionsCreate,
				constants.FieldObjectPermissions_PermissionsEdit:             perm.PermissionsEdit,
				constants.FieldObjectPermissions_PermissionsDelete:           perm.PermissionsDelete,
				constants.FieldObjectPermissions_PermissionsViewAllRecords:   perm.PermissionsViewAllRecords,
				constants.FieldObjectPermissions_PermissionsModifyAllRecords: perm.PermissionsModifyAllRecords,
				constants.FieldObjectPermissions_IsDeleted:                   false,
				constants.FieldCreatedDate:                                   time.Now(),
				constants.FieldLastModifiedDate:                              time.Now(),
			})
		}

		qr := query.Insert(constants.TableObjectPermissions, nil).
			ValuesBatch(records).
			OnDuplicateKeyUpdate(map[string]interface{}{
				// Revive on collision (IsDeleted -> 0); flags are NOT in the update
				// clause, so a live row is untouched (additive) and a tombstoned
				// built-in default is restored. See the method doc for why built-in
				// coverage must be guaranteed.
				constants.FieldObjectPermissions_IsDeleted: query.Raw("0"),
			}).
			Build()

		if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
			return apperrors.NewInternalError("failed to insert missing object permissions", err)
		}
	}
	return nil
}

// InsertMissingFieldPermissions seeds FieldPermissions rows for the platform's
// built-in default profiles, reviving a tombstoned built-in default on collision
// (IsDeleted -> 0) while never touching a live row's flags. Counterpart of
// InsertMissingObjectPermissions — see its doc for the full rationale.
func (r *PermissionRepository) InsertMissingFieldPermissions(ctx context.Context, perms []models.FieldPermissions) error {
	if len(perms) == 0 {
		return nil
	}
	for _, perm := range perms {
		if !fieldpermissions.AccessCoherent(perm.PermissionsRead, perm.PermissionsEdit) {
			return apperrors.NewValidationError(constants.FieldFieldPermissions_PermissionsEdit, "field edit permission requires field read permission")
		}
	}
	const writerID = "nexuscrm-persistence/perm_object_field.go#PermissionRepository.InsertMissingFieldPermissions"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriterGroup(ctx, func(txCtx context.Context) error {
			return forEachPermissionStateDirectProjectionBatch(perms, func(batch []models.FieldPermissions) error {
				return r.runPermissionStateDirectWriter(
					txCtx,
					writerID,
					fieldPermissionStateTargets(batch),
					func(batchCtx context.Context) error {
						return r.InsertMissingFieldPermissions(batchCtx, batch)
					},
				)
			})
		})
	}

	const batchSize = 200
	for i := 0; i < len(perms); i += batchSize {
		end := i + batchSize
		if end > len(perms) {
			end = len(perms)
		}
		batch := perms[i:end]

		var records []map[string]interface{}
		for _, perm := range batch {
			records = append(records, map[string]interface{}{
				constants.FieldID:                               utils.GenerateID(),
				constants.FieldObjectPermissions_ParentID:       perm.ParentID,
				constants.FieldObjectPermissions_SobjectType:    perm.SobjectType,
				constants.FieldFieldPermissions_Field:           perm.Field,
				constants.FieldFieldPermissions_PermissionsRead: perm.PermissionsRead,
				constants.FieldFieldPermissions_PermissionsEdit: perm.PermissionsEdit,
				constants.FieldFieldPermissions_IsDeleted:       false,
				constants.FieldCreatedDate:                      time.Now(),
				constants.FieldLastModifiedDate:                 time.Now(),
			})
		}

		qr := query.Insert(constants.TableFieldPermissions, nil).
			ValuesBatch(records).
			OnDuplicateKeyUpdate(map[string]interface{}{
				// Revive on collision (IsDeleted -> 0); flags untouched, so a live
				// row is additive-preserved and a tombstoned built-in default is
				// restored. See InsertMissingObjectPermissions.
				constants.FieldFieldPermissions_IsDeleted: query.Raw("0"),
			}).
			Build()

		if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
			return apperrors.NewInternalError("failed to insert missing field permissions", err)
		}
	}
	return nil
}

func (r *PermissionRepository) DeleteFieldPermissionsForFields(ctx context.Context, objectAPIName string, fieldAPINames []string) error {
	if len(fieldAPINames) == 0 {
		return nil
	}
	const writerID = "nexuscrm-persistence/perm_object_field.go#PermissionRepository.DeleteFieldPermissionsForFields"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			deleteFieldPermissionsPermissionStateTargets(objectAPIName, fieldAPINames),
			func(txCtx context.Context) error {
				return r.DeleteFieldPermissionsForFields(txCtx, objectAPIName, fieldAPINames)
			},
		)
	}

	placeholders := make([]string, len(fieldAPINames))
	params := make([]interface{}, 0, len(fieldAPINames)+1)
	params = append(params, strings.ToLower(objectAPIName))
	for i, fieldAPIName := range fieldAPINames {
		placeholders[i] = "?"
		params = append(params, strings.ToLower(fieldAPIName))
	}

	sqlQuery := fmt.Sprintf(
		"DELETE FROM `%s` WHERE `%s` = ? AND `%s` IN (%s)",
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_Field,
		strings.Join(placeholders, ","),
	)
	if _, err := r.getExecutor(ctx).ExecContext(ctx, sqlQuery, params...); err != nil {
		return apperrors.NewInternalError("failed to delete field permissions for fields", err)
	}
	return nil
}

func deleteFieldPermissionsPermissionStateTargets(
	objectAPIName string,
	fieldAPINames []string,
) []permissionStateDirectTarget {
	args := make([]interface{}, 0, len(fieldAPINames)+1)
	args = append(args, strings.ToLower(objectAPIName))
	for _, fieldAPIName := range fieldAPINames {
		args = append(args, strings.ToLower(fieldAPIName))
	}
	return []permissionStateDirectTarget{
		permissionStateDirectTargetWithManagedPermissionSetCurrentOwnerPrelock(
			permissionStateDirectTargetFor(
				constants.TableFieldPermissions,
				permissionStateDirectEqual(constants.FieldFieldPermissions_SobjectType)+
					" AND "+permissionStateDirectIn(constants.FieldFieldPermissions_Field, len(fieldAPINames)),
				args...,
			),
			constants.FieldFieldPermissions_ParentID,
		),
	}
}

func objectPermissionStateTargets(perms []models.ObjectPermissions) []permissionStateDirectTarget {
	clauses := make([]string, len(perms))
	args := make([]interface{}, 0, len(perms)*2)
	parentArgumentIndexes := make([]int, 0, len(perms))
	seenParents := map[string]struct{}{}
	for index, perm := range perms {
		clauses[index] = "(" +
			permissionStateDirectEqual(constants.FieldObjectPermissions_ParentID) +
			" AND " +
			permissionStateDirectEqual(constants.FieldObjectPermissions_SobjectType) +
			")"
		if _, seen := seenParents[perm.ParentID]; !seen {
			seenParents[perm.ParentID] = struct{}{}
			parentArgumentIndexes = append(parentArgumentIndexes, len(args))
		}
		args = append(args, perm.ParentID, perm.SobjectType)
	}
	return []permissionStateDirectTarget{
		permissionStateDirectTargetWithManagedPermissionSetArgumentPrelock(
			permissionStateDirectTargetFor(
				constants.TableObjectPermissions,
				strings.Join(clauses, " OR "),
				args...,
			),
			parentArgumentIndexes...,
		),
	}
}

func fieldPermissionStateTargets(perms []models.FieldPermissions) []permissionStateDirectTarget {
	clauses := make([]string, len(perms))
	args := make([]interface{}, 0, len(perms)*3)
	parentArgumentIndexes := make([]int, 0, len(perms))
	seenParents := map[string]struct{}{}
	for index, perm := range perms {
		clauses[index] = "(" +
			permissionStateDirectEqual(constants.FieldFieldPermissions_ParentID) +
			" AND " +
			permissionStateDirectEqual(constants.FieldFieldPermissions_SobjectType) +
			" AND " +
			permissionStateDirectEqual(constants.FieldFieldPermissions_Field) +
			")"
		if _, seen := seenParents[perm.ParentID]; !seen {
			seenParents[perm.ParentID] = struct{}{}
			parentArgumentIndexes = append(parentArgumentIndexes, len(args))
		}
		args = append(args, perm.ParentID, perm.SobjectType, perm.Field)
	}
	return []permissionStateDirectTarget{
		permissionStateDirectTargetWithManagedPermissionSetArgumentPrelock(
			permissionStateDirectTargetFor(
				constants.TableFieldPermissions,
				strings.Join(clauses, " OR "),
				args...,
			),
			parentArgumentIndexes...,
		),
	}
}

func forEachPermissionStateDirectProjectionBatch[T any](values []T, run func([]T) error) error {
	const batchSize = 200
	for start := 0; start < len(values); start += batchSize {
		end := min(start+batchSize, len(values))
		if err := run(values[start:end]); err != nil {
			return err
		}
	}
	return nil
}