package persistence

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

	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"
)

// LoadObjectPermission queries the database for a specific object permission
func (r *PermissionRepository) LoadObjectPermission(ctx context.Context, profileID, objectAPIName string) (*models.ObjectPermissions, error) {
	parentSubquery := fmt.Sprintf(
		"%s IN (SELECT %s FROM %s WHERE %s = ? AND %s = 1)",
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
	)
	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(parentSubquery, []interface{}{profileID}).
		Where(constants.FieldObjectPermissions_SobjectType, strings.ToLower(objectAPIName)).
		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) {
	parentSubquery := fmt.Sprintf(
		"%s IN (SELECT %s FROM %s WHERE %s = ? AND %s = 1)",
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
	)
	qr := query.From(constants.TableFieldPermissions).
		Select([]string{
			constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field,
			constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		}).
		WhereRaw(parentSubquery, []interface{}{profileID}).
		Where(constants.FieldObjectPermissions_SobjectType, strings.ToLower(objectAPIName)).
		Where(constants.FieldFieldPermissions_Field, strings.ToLower(fieldAPIName)).
		Limit(1).
		SkipID().
		Build()

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

	if err == sql.ErrNoRows {
		return nil, nil // No field permission = use object-level defaults
	}
	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 IN (
			SELECT %s FROM %s WHERE %s = ? AND %s = 1
			UNION
			SELECT %s FROM %s WHERE %s = ? AND %s = 0
			UNION
			SELECT op.%s FROM %s op
			JOIN %s psgc ON op.%s = psgc.%s
			JOIN %s psga ON psgc.%s = psga.%s
			WHERE psga.%s = ? AND psga.%s = 0
		)
	`, aggCols, constants.TableObjectPermissions, constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSetAssignment_PermissionSetID, constants.TablePermissionSetAssignment,
		constants.FieldPermissionSetAssignment_AssigneeID, constants.FieldPermissionSetAssignment_IsDeleted,
		// PSG component branch
		constants.FieldObjectPermissions_ParentID, constants.TableObjectPermissions,
		constants.TablePermissionSetGroupComponent, constants.FieldObjectPermissions_ParentID, constants.FieldPermissionSetGroupComponent_PermissionSetID,
		constants.TablePermissionSetGroupAssignment, constants.FieldPermissionSetGroupComponent_GroupID, constants.FieldPermissionSetGroupAssignment_GroupID,
		constants.FieldPermissionSetGroupAssignment_AssigneeID, constants.FieldPermissionSetGroupAssignment_IsDeleted)

	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) {
	aggCols := strings.Join([]string{
		fmt.Sprintf("MAX(%s)", constants.FieldFieldPermissions_PermissionsRead),
		fmt.Sprintf("MAX(%s)", constants.FieldFieldPermissions_PermissionsEdit),
	}, ", ")

	query := fmt.Sprintf(`
		SELECT %s
		FROM %s
		WHERE %s = ? AND %s = ?
		AND %s IN (
			SELECT %s FROM %s WHERE %s = ? AND %s = 1
			UNION
			SELECT %s FROM %s WHERE %s = ? AND %s = 0
			UNION
			SELECT fp.%s FROM %s fp
			JOIN %s psgc ON fp.%s = psgc.%s
			JOIN %s psga ON psgc.%s = psga.%s
			WHERE psga.%s = ? AND psga.%s = 0
		)
	`, aggCols, constants.TableFieldPermissions, constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSetAssignment_PermissionSetID, constants.TablePermissionSetAssignment,
		constants.FieldPermissionSetAssignment_AssigneeID, constants.FieldPermissionSetAssignment_IsDeleted,
		// PSG component branch
		constants.FieldFieldPermissions_ParentID, constants.TableFieldPermissions,
		constants.TablePermissionSetGroupComponent, constants.FieldFieldPermissions_ParentID, constants.FieldPermissionSetGroupComponent_PermissionSetID,
		constants.TablePermissionSetGroupAssignment, constants.FieldPermissionSetGroupComponent_GroupID, constants.FieldPermissionSetGroupAssignment_GroupID,
		constants.FieldPermissionSetGroupAssignment_AssigneeID, constants.FieldPermissionSetGroupAssignment_IsDeleted)

	var readable, editable sql.NullBool
	err := r.getExecutor(ctx).QueryRowContext(ctx, query, strings.ToLower(objectAPIName), strings.ToLower(fieldAPIName), user.ProfileID, user.ID, user.ID).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:           fieldAPIName,
		PermissionsRead: readable.Bool,
		PermissionsEdit: editable.Bool,
	}, nil
}

// ListObjectPermissions retrieves all object permissions for a profile
func (r *PermissionRepository) ListObjectPermissions(ctx context.Context, profileID string) ([]models.ObjectPermissions, error) {
	parentSubquery := fmt.Sprintf(
		"%s IN (SELECT %s FROM %s WHERE %s = ? AND %s = 1)",
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
	)
	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(parentSubquery, []interface{}{profileID}).
		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) {
	parentSubquery := fmt.Sprintf(
		"%s IN (SELECT %s FROM %s WHERE %s = ? AND %s = 1)",
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
	)
	qr := query.From(constants.TableFieldPermissions).
		Select([]string{
			constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field,
			constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		}).
		WhereRaw(parentSubquery, []interface{}{profileID}).
		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 {
	return r.upsertObjectPermission(ctx, tx, perm)
}

func (r *PermissionRepository) upsertObjectPermission(ctx context.Context, exec Executor, perm models.ObjectPermissions) error {
	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.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.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 {
	return r.upsertFieldPermission(ctx, tx, perm)
}

func (r *PermissionRepository) upsertFieldPermission(ctx context.Context, exec Executor, perm models.FieldPermissions) error {
	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.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.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` IN (SELECT `%s` FROM `%s` WHERE `%s` = ? AND `%s` = 1)",
			constants.TableObjectPermissions,
			constants.FieldObjectPermissions_ParentID,
			constants.FieldID, constants.TablePermissionSet,
			constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile),
		profileID,
	).Scan(&count)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to count object permissions", err)
	}
	return count, nil
}

// BatchUpsertObjectPermissions creates or updates multiple object permissions using multi-value INSERT
func (r *PermissionRepository) BatchUpsertObjectPermissions(ctx context.Context, perms []models.ObjectPermissions) error {
	if len(perms) == 0 {
		return nil
	}

	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.FieldCreatedDate:                                   time.Now(),
				constants.FieldLastModifiedDate:                              time.Now(),
			})
		}

		qr := query.Insert(constants.TableObjectPermissions, nil).
			ValuesBatch(records).
			OnDuplicateKeyUpdate(map[string]interface{}{
				constants.FieldObjectPermissions_PermissionsRead:             query.Raw("VALUES(`PermissionsRead`)"),
				constants.FieldObjectPermissions_PermissionsCreate:           query.Raw("VALUES(`PermissionsCreate`)"),
				constants.FieldObjectPermissions_PermissionsEdit:             query.Raw("VALUES(`PermissionsEdit`)"),
				constants.FieldObjectPermissions_PermissionsDelete:           query.Raw("VALUES(`PermissionsDelete`)"),
				constants.FieldObjectPermissions_PermissionsViewAllRecords:   query.Raw("VALUES(`PermissionsViewAllRecords`)"),
				constants.FieldObjectPermissions_PermissionsModifyAllRecords: query.Raw("VALUES(`PermissionsModifyAllRecords`)"),
				constants.FieldLastModifiedDate:                              query.Raw("VALUES(`LastModifiedDate`)"),
			}).
			Build()

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

// BatchUpsertFieldPermissions creates or updates multiple field permissions using multi-value INSERT
func (r *PermissionRepository) BatchUpsertFieldPermissions(ctx context.Context, perms []models.FieldPermissions) error {
	if len(perms) == 0 {
		return nil
	}

	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.FieldCreatedDate:                      time.Now(),
				constants.FieldLastModifiedDate:                 time.Now(),
			})
		}

		qr := query.Insert(constants.TableFieldPermissions, nil).
			ValuesBatch(records).
			OnDuplicateKeyUpdate(map[string]interface{}{
				constants.FieldFieldPermissions_PermissionsRead: query.Raw("VALUES(`PermissionsRead`)"),
				constants.FieldFieldPermissions_PermissionsEdit: query.Raw("VALUES(`PermissionsEdit`)"),
				constants.FieldLastModifiedDate:                 query.Raw("VALUES(`LastModifiedDate`)"),
			}).
			Build()

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

func (r *PermissionRepository) DeleteFieldPermissionsForFields(ctx context.Context, objectAPIName string, fieldAPINames []string) error {
	if len(fieldAPINames) == 0 {
		return nil
	}

	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
}

// CleanupDirectProfilePermissions removes old bootstrap data that used ProfileId directly as ParentId
// instead of the PermissionSet Id.
func (r *PermissionRepository) CleanupDirectProfilePermissions(ctx context.Context, profileIDs []string) error {
	if len(profileIDs) == 0 {
		return nil
	}

	placeholders := make([]string, len(profileIDs))
	params := make([]interface{}, len(profileIDs))
	for i, id := range profileIDs {
		placeholders[i] = "?"
		params[i] = id
	}
	inClause := strings.Join(placeholders, ", ")

	objSQL := fmt.Sprintf("DELETE FROM `%s` WHERE `%s` IN (%s)",
		constants.TableObjectPermissions,
		constants.FieldObjectPermissions_ParentID,
		inClause,
	)
	if _, err := r.getExecutor(ctx).ExecContext(ctx, objSQL, params...); err != nil {
		return apperrors.NewInternalError("failed to cleanup direct profile object permissions", err)
	}

	fieldSQL := fmt.Sprintf("DELETE FROM `%s` WHERE `%s` IN (%s)",
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_ParentID,
		inClause,
	)
	if _, err := r.getExecutor(ctx).ExecContext(ctx, fieldSQL, params...); err != nil {
		return apperrors.NewInternalError("failed to cleanup direct profile field permissions", err)
	}

	return nil
}