package persistence

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

	"github.com/nexuscrm/db"
	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"
)

// --- Permission Set Extensions ---

// ListPermissionSetObjectPermissions retrieves all object permissions for a permission set
func (r *PermissionRepository) ListPermissionSetObjectPermissions(ctx context.Context, permissionSetID string) ([]models.ObjectPermissions, error) {
	cols := strings.Join([]string{
		constants.FieldObjectPermissions_ID, constants.FieldObjectPermissions_ParentID, constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_PermissionsRead, constants.FieldObjectPermissions_PermissionsCreate,
		constants.FieldObjectPermissions_PermissionsEdit, constants.FieldObjectPermissions_PermissionsDelete,
		constants.FieldObjectPermissions_PermissionsViewAllRecords, constants.FieldObjectPermissions_PermissionsModifyAllRecords,
	}, ", ")

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

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

	var perms []models.ObjectPermissions
	for rows.Next() {
		var p models.ObjectPermissions
		if err := rows.Scan(&p.ID, &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 permset object permission", err)
		}
		perms = append(perms, p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating permset object permissions", err)
	}
	return perms, nil
}

// ListPermissionSetFieldPermissions retrieves all field permissions for a permission set
func (r *PermissionRepository) ListPermissionSetFieldPermissions(ctx context.Context, permissionSetID string) ([]models.FieldPermissions, error) {
	cols := strings.Join([]string{
		constants.FieldFieldPermissions_ID, constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_Field, constants.FieldFieldPermissions_PermissionsRead,
		constants.FieldFieldPermissions_PermissionsEdit,
	}, ", ")

	query := fmt.Sprintf(`
		SELECT %s
		FROM %s
		WHERE %s = ? AND %s = 0
	`, cols, constants.TableFieldPermissions, constants.FieldFieldPermissions_ParentID, constants.FieldFieldPermissions_IsDeleted)

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

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

// ListPermissionAuthoringObjectGrants is the neutral authoring projection of
// the retained CRM physical permission table.
func (r *PermissionRepository) ListPermissionAuthoringObjectGrants(ctx context.Context, permissionSetID string) ([]ports.PermissionAuthoringObjectGrant, error) {
	query := fmt.Sprintf(`SELECT %s, %s, %s, %s, %s, %s, %s, %s, %s
FROM %s WHERE %s = ?`,
		constants.FieldObjectPermissions_ID,
		constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_IsDeleted,
		constants.FieldObjectPermissions_PermissionsRead,
		constants.FieldObjectPermissions_PermissionsCreate,
		constants.FieldObjectPermissions_PermissionsEdit,
		constants.FieldObjectPermissions_PermissionsDelete,
		constants.FieldObjectPermissions_PermissionsViewAllRecords,
		constants.FieldObjectPermissions_PermissionsModifyAllRecords,
		constants.TableObjectPermissions,
		constants.FieldObjectPermissions_ParentID,
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, query, permissionSetID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query permission authoring object grants", err)
	}
	defer rows.Close()
	var out []ports.PermissionAuthoringObjectGrant
	for rows.Next() {
		var row ports.PermissionAuthoringObjectGrant
		if err := rows.Scan(
			&row.ID, &row.ResourceType, &row.Deleted,
			&row.Read, &row.Create, &row.Edit, &row.Delete, &row.ViewAll, &row.ModifyAll,
		); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission authoring object grant", err)
		}
		out = append(out, row)
	}
	return out, rows.Err()
}

// LockPermissionAuthoringFieldGrants is the neutral authoring projection of
// the retained CRM physical permission table. The whole PermissionSet field
// grant set is locked so historical simple and canonical qualified aliases are
// planned and changed as one logical permission.
func (r *PermissionRepository) LockPermissionAuthoringFieldGrants(ctx context.Context, permissionSetID string) ([]ports.PermissionAuthoringFieldGrant, error) {
	query := fmt.Sprintf(`SELECT %s, %s, %s, %s, %s, %s
FROM %s WHERE %s = ? ORDER BY %s FOR UPDATE`,
		constants.FieldFieldPermissions_ID,
		constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_Field,
		constants.FieldFieldPermissions_IsDeleted,
		constants.FieldFieldPermissions_PermissionsRead,
		constants.FieldFieldPermissions_PermissionsEdit,
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldFieldPermissions_ID,
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, query, permissionSetID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query permission authoring field grants", err)
	}
	defer rows.Close()
	var out []ports.PermissionAuthoringFieldGrant
	for rows.Next() {
		var row ports.PermissionAuthoringFieldGrant
		if err := rows.Scan(
			&row.ID, &row.ResourceType, &row.FieldName, &row.Deleted, &row.Read, &row.Edit,
		); err != nil {
			return nil, apperrors.NewInternalError("failed to scan permission authoring field grant", err)
		}
		out = append(out, row)
	}
	return out, rows.Err()
}

// ListEffectiveObjectPermissionsForUser returns aggregated permissions for a user
func (r *PermissionRepository) ListEffectiveObjectPermissionsForUser(ctx context.Context, userID, profileID string) ([]models.ObjectPermissions, error) {
	query := fmt.Sprintf(`
		SELECT
			%s,
			MAX(%s), MAX(%s), MAX(%s), MAX(%s), MAX(%s), MAX(%s)
		FROM %s
		WHERE
			%s = 0
		AND
			%s
		GROUP BY %s
	`, constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_PermissionsRead, constants.FieldObjectPermissions_PermissionsCreate,
		constants.FieldObjectPermissions_PermissionsEdit, constants.FieldObjectPermissions_PermissionsDelete,
		constants.FieldObjectPermissions_PermissionsViewAllRecords, constants.FieldObjectPermissions_PermissionsModifyAllRecords,
		constants.TableObjectPermissions,
		constants.FieldObjectPermissions_IsDeleted,
		effectivePermissionParentPredicate(constants.FieldObjectPermissions_ParentID),
		constants.FieldObjectPermissions_SobjectType)

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

	var perms []models.ObjectPermissions
	for rows.Next() {
		var p models.ObjectPermissions
		var r, c, e, d, va, ma sql.NullBool
		if err := rows.Scan(&p.SobjectType, &r, &c, &e, &d, &va, &ma); err != nil {
			return nil, apperrors.NewInternalError("failed to scan effective object permission", err)
		}
		p.PermissionsRead = r.Valid && r.Bool
		p.PermissionsCreate = c.Valid && c.Bool
		p.PermissionsEdit = e.Valid && e.Bool
		p.PermissionsDelete = d.Valid && d.Bool
		p.PermissionsViewAllRecords = va.Valid && va.Bool
		p.PermissionsModifyAllRecords = ma.Valid && ma.Bool
		perms = append(perms, p)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating effective object permissions", err)
	}
	return perms, nil
}

// ListEffectiveFieldPermissionsForUser returns aggregated field permissions for a user.
// It intentionally remains the all-object administrative/warm projection.
func (r *PermissionRepository) ListEffectiveFieldPermissionsForUser(ctx context.Context, userID, profileID string) ([]models.FieldPermissions, error) {
	return r.listEffectiveFieldPermissionsForUser(ctx, userID, profileID, nil)
}

// ListEffectiveFieldPermissionsForUserObject returns the effective field
// permissions for one object. Request-time FLS checks use this bounded
// projection; callers needing the administrative all-object view must use the
// explicit ListEffectiveFieldPermissionsForUser method above.
func (r *PermissionRepository) ListEffectiveFieldPermissionsForUserObject(ctx context.Context, userID, profileID, objectAPIName string) ([]models.FieldPermissions, error) {
	objectAPIName = strings.ToLower(strings.TrimSpace(objectAPIName))
	if objectAPIName == "" {
		return nil, nil
	}
	return r.listEffectiveFieldPermissionsForUser(
		ctx, userID, profileID, []string{objectAPIName},
	)
}

// ListEffectiveFieldPermissionsForUserObjects keeps a selected multi-object
// authority read in one statement. Object identities remain data parameters;
// the SQL shape and permission aggregation semantics are identical to the
// complete and one-object paths.
func (r *PermissionRepository) ListEffectiveFieldPermissionsForUserObjects(
	ctx context.Context,
	userID string,
	profileID string,
	objectAPINames []string,
) ([]models.FieldPermissions, error) {
	normalized := make([]string, 0, len(objectAPINames))
	seen := make(map[string]struct{}, len(objectAPINames))
	for _, objectAPIName := range objectAPINames {
		objectAPIName = strings.ToLower(strings.TrimSpace(objectAPIName))
		if objectAPIName == "" {
			continue
		}
		if _, duplicate := seen[objectAPIName]; duplicate {
			continue
		}
		seen[objectAPIName] = struct{}{}
		normalized = append(normalized, objectAPIName)
	}
	if len(normalized) == 0 {
		return nil, nil
	}
	sort.Strings(normalized)
	return r.listEffectiveFieldPermissionsForUser(
		ctx, userID, profileID, normalized,
	)
}

func (r *PermissionRepository) listEffectiveFieldPermissionsForUser(
	ctx context.Context,
	userID string,
	profileID string,
	objectAPINames []string,
) ([]models.FieldPermissions, error) {
	fieldCol := fmt.Sprintf("fp.`%s`", constants.FieldFieldPermissions_Field)
	objectCol := fmt.Sprintf("fp.`%s`", constants.FieldFieldPermissions_SobjectType)
	logicalFieldExpr := fmt.Sprintf("SUBSTRING_INDEX(%s, '.', -1)", fieldCol)
	prefixGuard := fmt.Sprintf("(LOCATE('.', %s) = 0 OR SUBSTRING_INDEX(%s, '.', 1) = %s)", fieldCol, fieldCol, objectCol)
	objectPredicate := ""
	args := make([]interface{}, 0, len(objectAPINames)+3)
	if len(objectAPINames) != 0 {
		if len(objectAPINames) == 1 {
			objectPredicate = fmt.Sprintf(
				"AND\n\t\t\tfp.`%s` = ?",
				constants.FieldFieldPermissions_SobjectType,
			)
			args = append(args, objectAPINames[0])
		} else {
			placeholders := make([]string, len(objectAPINames))
			for index, objectAPIName := range objectAPINames {
				placeholders[index] = "?"
				args = append(args, objectAPIName)
			}
			objectPredicate = fmt.Sprintf(
				"AND\n\t\t\tfp.`%s` IN (%s)",
				constants.FieldFieldPermissions_SobjectType,
				strings.Join(placeholders, ", "),
			)
		}
	}
	query := fmt.Sprintf(`
		SELECT
			MIN(fp.%s), MIN(%s),
			MAX(fp.%s), MAX(fp.%s)
		FROM %s fp
		WHERE
			fp.%s = 0
		AND
			%s
		%s
		AND
			%s
		GROUP BY fp.%s, %s
	`, constants.FieldFieldPermissions_SobjectType, logicalFieldExpr,
		constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_IsDeleted,
		prefixGuard,
		objectPredicate,
		effectivePermissionParentPredicate(fmt.Sprintf("fp.%s", constants.FieldFieldPermissions_ParentID)),
		constants.FieldFieldPermissions_SobjectType, logicalFieldExpr)

	args = append(args, profileID, userID, userID)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, query, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query effective field permissions", err)
	}
	defer rows.Close()

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

// EnsureProfilePermissionSet creates or retrieves the PermissionSet that backs a Profile.
// Uses INSERT IGNORE + SELECT to handle concurrent creation safely.
func (r *PermissionRepository) EnsureProfilePermissionSet(ctx context.Context, profileID, profileName string) (string, error) {
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.EnsureProfilePermissionSet"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		var id string
		err := r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectEqual(constants.FieldPermissionSet_ProfileID)+
						" AND "+permissionStateDirectEqual(constants.FieldPermissionSet_IsOwnedByProfile),
					profileID,
					1,
				),
			},
			func(txCtx context.Context) error {
				var err error
				id, err = r.EnsureProfilePermissionSet(txCtx, profileID, profileName)
				return err
			},
		)
		return id, err
	}
	selectActiveSQL := fmt.Sprintf(
		`SELECT %s FROM %s WHERE %s = ? AND %s = 1 AND %s = 0 LIMIT 1`,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)

	var id string
	err := r.getExecutor(ctx).QueryRowContext(ctx, selectActiveSQL, profileID).Scan(&id)
	if err == nil {
		return id, nil
	}
	if err != sql.ErrNoRows {
		return "", apperrors.NewInternalError("failed to query profile permission set", err)
	}

	// Reuse existing profile-owned rows (including soft-deleted), instead of creating a new one.
	selectAnySQL := fmt.Sprintf(
		`SELECT %s, %s FROM %s WHERE %s = ? AND %s = 1 ORDER BY %s ASC, %s DESC, %s DESC LIMIT 1`,
		constants.FieldID, constants.FieldPermissionSet_IsDeleted, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted, constants.FieldLastModifiedDate, constants.FieldID,
	)
	var isDeleted int
	err = r.getExecutor(ctx).QueryRowContext(ctx, selectAnySQL, profileID).Scan(&id, &isDeleted)
	if err == nil {
		if isDeleted == 1 {
			now := time.Now()
			reactivateSQL := fmt.Sprintf(
				`UPDATE %s SET %s = 0, %s = 1, %s = ? WHERE %s = ?`,
				constants.TablePermissionSet,
				constants.FieldPermissionSet_IsDeleted,
				constants.FieldPermissionSet_IsActive,
				constants.FieldLastModifiedDate,
				constants.FieldID,
			)
			if _, updateErr := r.getExecutor(ctx).ExecContext(ctx, reactivateSQL, now, id); updateErr != nil {
				return "", apperrors.NewInternalError("failed to reactivate profile permission set", updateErr)
			}
		}
		return id, nil
	}
	if err != sql.ErrNoRows {
		return "", apperrors.NewInternalError("failed to query any profile permission set", err)
	}

	newID := utils.GenerateID()
	now := time.Now()
	insertSQL := fmt.Sprintf(
		`INSERT IGNORE INTO %s (%s, %s, %s, %s, %s, %s, %s, %s) VALUES (?, ?, ?, ?, 1, 1, 0, ?)`,
		constants.TablePermissionSet,
		constants.FieldID,
		constants.FieldPermissionSet_Name,
		constants.FieldPermissionSet_Label,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsActive,
		constants.FieldPermissionSet_IsCustom,
		constants.FieldCreatedDate,
	)
	_, err = r.getExecutor(ctx).ExecContext(ctx, insertSQL, newID, "CRM_"+profileID, profileName+" (Profile)", profileID, now)
	if err != nil {
		return "", apperrors.NewInternalError("failed to insert profile permission set", err)
	}

	err = r.getExecutor(ctx).QueryRowContext(ctx, selectActiveSQL, profileID).Scan(&id)
	if err != nil {
		return "", apperrors.NewInternalError("failed to retrieve profile permission set after insert", err)
	}
	return id, nil
}

// BulkEnsureProfilePermissionSets creates or retrieves PermissionSets for multiple profiles in bulk.
// Uses 1 SELECT + 1 INSERT IGNORE + 1 re-SELECT instead of 3N queries.
func (r *PermissionRepository) BulkEnsureProfilePermissionSets(ctx context.Context, profiles []struct{ ID, Name string }) (map[string]string, error) {
	if len(profiles) == 0 {
		return nil, nil
	}
	if len(profiles) > 200 {
		result := make(map[string]string, len(profiles))
		err := forEachPermissionStateDirectProjectionBatch(profiles, func(batch []struct{ ID, Name string }) error {
			batchResult, err := r.BulkEnsureProfilePermissionSets(ctx, batch)
			if err != nil {
				return err
			}
			for profileID, permissionSetID := range batchResult {
				result[profileID] = permissionSetID
			}
			return nil
		})
		if err != nil {
			return nil, err
		}
		return result, nil
	}
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.BulkEnsureProfilePermissionSets"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		profileIDs := make([]interface{}, len(profiles))
		for index, profile := range profiles {
			profileIDs[index] = profile.ID
		}
		var result map[string]string
		err := r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectIn(constants.FieldPermissionSet_ProfileID, len(profileIDs))+
						" AND "+permissionStateDirectEqual(constants.FieldPermissionSet_IsOwnedByProfile),
					append(profileIDs, 1)...,
				),
			},
			func(txCtx context.Context) error {
				var err error
				result, err = r.BulkEnsureProfilePermissionSets(txCtx, profiles)
				return err
			},
		)
		return result, err
	}

	// Query 1: SELECT all existing profile-owned permission sets (including soft-deleted).
	// We order by IsDeleted ASC so active rows are preferred when choosing one row per profile.
	placeholders := make([]string, len(profiles))
	args := make([]interface{}, len(profiles))
	for i, p := range profiles {
		placeholders[i] = "?"
		args[i] = p.ID
	}
	selectSQL := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s` FROM `%s` WHERE `%s` IN (%s) AND `%s` = 1 ORDER BY `%s` ASC, `%s` ASC, `%s` DESC, `%s` DESC",
		constants.FieldPermissionSet_ProfileID,
		constants.FieldID,
		constants.FieldPermissionSet_IsDeleted,
		constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(placeholders, ","),
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldPermissionSet_IsDeleted,
		constants.FieldLastModifiedDate,
		constants.FieldID,
	)

	result := make(map[string]string, len(profiles))
	needsReactivation := make([]string, 0)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, selectSQL, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("bulk select profile permission sets", err)
	}
	defer rows.Close()
	for rows.Next() {
		var profileID, psID string
		var isDeleted int
		if err := rows.Scan(&profileID, &psID, &isDeleted); err != nil {
			return nil, apperrors.NewInternalError("scan profile permission set", err)
		}
		if _, exists := result[profileID]; exists {
			continue
		}
		result[profileID] = psID
		if isDeleted == 1 {
			needsReactivation = append(needsReactivation, psID)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate profile permission sets", err)
	}

	// Find missing profiles
	var missing []struct{ ID, Name string }
	for _, p := range profiles {
		if _, ok := result[p.ID]; !ok {
			missing = append(missing, p)
		}
	}
	if len(missing) == 0 {
		if err := r.bulkReactivatePermissionSets(ctx, needsReactivation); err != nil {
			return nil, err
		}
		return result, nil // warm path
	}

	// Query 2: INSERT IGNORE for missing profiles
	now := time.Now()
	var valueParts []string
	var insertArgs []interface{}
	for _, p := range missing {
		valueParts = append(valueParts, "(?, ?, ?, ?, 1, 1, 0, ?)")
		insertArgs = append(insertArgs,
			utils.GenerateID(), "CRM_"+p.ID, p.Name+" (Profile)", p.ID, now,
		)
	}
	insertSQL := fmt.Sprintf(
		"INSERT IGNORE INTO `%s` (`%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`) VALUES %s",
		constants.TablePermissionSet,
		constants.FieldID,
		constants.FieldPermissionSet_Name,
		constants.FieldPermissionSet_Label,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsActive,
		constants.FieldPermissionSet_IsCustom,
		constants.FieldCreatedDate,
		strings.Join(valueParts, ", "),
	)
	if _, err := r.getExecutor(ctx).ExecContext(ctx, insertSQL, insertArgs...); err != nil {
		return nil, apperrors.NewInternalError("bulk insert profile permission sets", err)
	}

	// Reactivate any soft-deleted profile-owned rows we selected.
	if err := r.bulkReactivatePermissionSets(ctx, needsReactivation); err != nil {
		return nil, err
	}

	// Query 3: Re-SELECT ACTIVE rows to get final IDs for all profiles
	selectActiveSQL := fmt.Sprintf(
		"SELECT `%s`, `%s` FROM `%s` WHERE `%s` IN (%s) AND `%s` = 1 AND `%s` = 0",
		constants.FieldPermissionSet_ProfileID,
		constants.FieldID,
		constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(placeholders, ","),
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)
	rows2, err := r.getExecutor(ctx).QueryContext(ctx, selectActiveSQL, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("re-select after bulk insert", err)
	}
	defer rows2.Close()
	for rows2.Next() {
		var profileID, psID string
		if err := rows2.Scan(&profileID, &psID); err != nil {
			return nil, apperrors.NewInternalError("scan after bulk insert", err)
		}
		result[profileID] = psID
	}
	if err := rows2.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate re-select", err)
	}

	// Verify every requested profile has a permission set — fail explicitly if any are missing
	for _, p := range profiles {
		if _, ok := result[p.ID]; !ok {
			return nil, apperrors.NewInternalError(
				fmt.Sprintf("BulkEnsureProfilePermissionSets: profile %s has no PermissionSet after INSERT IGNORE + re-SELECT", p.ID), nil)
		}
	}
	return result, nil
}

func (r *PermissionRepository) bulkReactivatePermissionSets(ctx context.Context, ids []string) error {
	if len(ids) == 0 {
		return nil
	}
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.bulkReactivatePermissionSets"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		args := make([]interface{}, len(ids))
		for index, id := range ids {
			args[index] = id
		}
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectIn(constants.FieldID, len(args)),
					args...,
				),
			},
			func(txCtx context.Context) error {
				return r.bulkReactivatePermissionSets(txCtx, ids)
			},
		)
	}

	placeholders := make([]string, len(ids))
	params := make([]interface{}, 0, len(ids)+1)
	for i, id := range ids {
		placeholders[i] = "?"
		params = append(params, id)
	}
	params = append(params, time.Now())

	updateSQL := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 0, `%s` = 1, `%s` = ? WHERE `%s` IN (%s)",
		constants.TablePermissionSet,
		constants.FieldPermissionSet_IsDeleted,
		constants.FieldPermissionSet_IsActive,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		strings.Join(placeholders, ","),
	)

	// Put timestamp first placeholder for query args
	reordered := make([]interface{}, 0, len(params))
	reordered = append(reordered, params[len(params)-1])
	reordered = append(reordered, params[:len(params)-1]...)

	if _, err := r.getExecutor(ctx).ExecContext(ctx, updateSQL, reordered...); err != nil {
		return apperrors.NewInternalError("bulk reactivate profile permission sets", err)
	}
	return nil
}

// BulkCountObjectPermissionsByProfiles returns object permission counts for multiple profiles in one query.
func (r *PermissionRepository) BulkCountObjectPermissionsByProfiles(ctx context.Context, profileIDs []string) (map[string]int, error) {
	if len(profileIDs) == 0 {
		return nil, nil
	}

	placeholders := make([]string, len(profileIDs))
	args := make([]interface{}, len(profileIDs))
	for i, id := range profileIDs {
		placeholders[i] = "?"
		args[i] = id
	}

	q := fmt.Sprintf(
		"SELECT ps.`%s`, COUNT(*) FROM `%s` op JOIN `%s` ps ON op.`%s` = ps.`%s` AND ps.`%s` = 1 WHERE ps.`%s` IN (%s) AND op.`%s` = 0 GROUP BY ps.`%s`",
		constants.FieldPermissionSet_ProfileID,
		constants.TableObjectPermissions,
		constants.TablePermissionSet,
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(placeholders, ","),
		constants.FieldObjectPermissions_IsDeleted,
		constants.FieldPermissionSet_ProfileID,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, q, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("bulk count object permissions", err)
	}
	defer rows.Close()

	result := make(map[string]int, len(profileIDs))
	for rows.Next() {
		var pid string
		var count int
		if err := rows.Scan(&pid, &count); err != nil {
			return nil, apperrors.NewInternalError("scan bulk count", err)
		}
		result[pid] = count
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate bulk count", err)
	}
	return result, nil
}

// BulkCountFieldPermissionsByProfiles returns field permission counts for multiple profiles in one query.
// Used alongside BulkCountObjectPermissionsByProfiles to detect half-seeded profiles
// (ObjectPermissions > 0 but FieldPermissions = 0).
func (r *PermissionRepository) BulkCountFieldPermissionsByProfiles(ctx context.Context, profileIDs []string) (map[string]int, error) {
	if len(profileIDs) == 0 {
		return nil, nil
	}

	placeholders := make([]string, len(profileIDs))
	args := make([]interface{}, len(profileIDs))
	for i, id := range profileIDs {
		placeholders[i] = "?"
		args[i] = id
	}

	q := fmt.Sprintf(
		"SELECT ps.`%s`, COUNT(*) FROM `%s` fp JOIN `%s` ps ON fp.`%s` = ps.`%s` AND ps.`%s` = 1 WHERE ps.`%s` IN (%s) AND fp.`%s` = 0 GROUP BY ps.`%s`",
		constants.FieldPermissionSet_ProfileID,
		constants.TableFieldPermissions,
		constants.TablePermissionSet,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(placeholders, ","),
		constants.FieldFieldPermissions_IsDeleted,
		constants.FieldPermissionSet_ProfileID,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, q, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("bulk count field permissions", err)
	}
	defer rows.Close()

	result := make(map[string]int, len(profileIDs))
	for rows.Next() {
		var pid string
		var count int
		if err := rows.Scan(&pid, &count); err != nil {
			return nil, apperrors.NewInternalError("scan bulk field count", err)
		}
		result[pid] = count
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate bulk field count", err)
	}
	return result, nil
}

// BulkCountObjectPermissionsByProfilesAndTypes returns object permission counts for multiple
// profiles restricted to a specific set of SObject API names.
func (r *PermissionRepository) BulkCountObjectPermissionsByProfilesAndTypes(ctx context.Context, profileIDs []string, objectAPINames []string) (map[string]int, error) {
	if len(profileIDs) == 0 || len(objectAPINames) == 0 {
		return map[string]int{}, nil
	}

	profilePlaceholders := make([]string, len(profileIDs))
	args := make([]interface{}, 0, len(profileIDs)+len(objectAPINames))
	for i, id := range profileIDs {
		profilePlaceholders[i] = "?"
		args = append(args, id)
	}

	objectPlaceholders := make([]string, len(objectAPINames))
	for i, apiName := range objectAPINames {
		objectPlaceholders[i] = "?"
		args = append(args, apiName)
	}

	q := fmt.Sprintf(
		"SELECT ps.`%s`, COUNT(*) FROM `%s` op JOIN `%s` ps ON op.`%s` = ps.`%s` AND ps.`%s` = 1 WHERE ps.`%s` IN (%s) AND op.`%s` = 0 AND op.`%s` IN (%s) GROUP BY ps.`%s`",
		constants.FieldPermissionSet_ProfileID,
		constants.TableObjectPermissions,
		constants.TablePermissionSet,
		constants.FieldObjectPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(profilePlaceholders, ","),
		constants.FieldObjectPermissions_IsDeleted,
		constants.FieldObjectPermissions_SobjectType,
		strings.Join(objectPlaceholders, ","),
		constants.FieldPermissionSet_ProfileID,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, q, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("bulk count filtered object permissions", err)
	}
	defer rows.Close()

	result := make(map[string]int, len(profileIDs))
	for rows.Next() {
		var pid string
		var count int
		if err := rows.Scan(&pid, &count); err != nil {
			return nil, apperrors.NewInternalError("scan filtered object permission count", err)
		}
		result[pid] = count
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate filtered object permission count", err)
	}
	return result, nil
}

// BulkCountFieldPermissionsByProfilesAndTypes returns field permission counts for multiple
// profiles restricted to a specific set of SObject API names.
func (r *PermissionRepository) BulkCountFieldPermissionsByProfilesAndTypes(ctx context.Context, profileIDs []string, objectAPINames []string) (map[string]int, error) {
	if len(profileIDs) == 0 || len(objectAPINames) == 0 {
		return map[string]int{}, nil
	}

	profilePlaceholders := make([]string, len(profileIDs))
	args := make([]interface{}, 0, len(profileIDs)+len(objectAPINames))
	for i, id := range profileIDs {
		profilePlaceholders[i] = "?"
		args = append(args, id)
	}

	objectPlaceholders := make([]string, len(objectAPINames))
	for i, apiName := range objectAPINames {
		objectPlaceholders[i] = "?"
		args = append(args, apiName)
	}

	q := fmt.Sprintf(
		"SELECT ps.`%s`, COUNT(*) FROM `%s` fp JOIN `%s` ps ON fp.`%s` = ps.`%s` AND ps.`%s` = 1 WHERE ps.`%s` IN (%s) AND fp.`%s` = 0 AND fp.`%s` IN (%s) GROUP BY ps.`%s`",
		constants.FieldPermissionSet_ProfileID,
		constants.TableFieldPermissions,
		constants.TablePermissionSet,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID,
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_ProfileID,
		strings.Join(profilePlaceholders, ","),
		constants.FieldFieldPermissions_IsDeleted,
		constants.FieldFieldPermissions_SobjectType,
		strings.Join(objectPlaceholders, ","),
		constants.FieldPermissionSet_ProfileID,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, q, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("bulk count filtered field permissions", err)
	}
	defer rows.Close()

	result := make(map[string]int, len(profileIDs))
	for rows.Next() {
		var pid string
		var count int
		if err := rows.Scan(&pid, &count); err != nil {
			return nil, apperrors.NewInternalError("scan filtered field permission count", err)
		}
		result[pid] = count
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("iterate filtered field permission count", err)
	}
	return result, nil
}

// CleanupProfilePermissionSet soft-deletes the PermissionSet owned by a Profile.
func (r *PermissionRepository) CleanupProfilePermissionSet(ctx context.Context, profileID string) error {
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.CleanupProfilePermissionSet"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectEqual(constants.FieldPermissionSet_ProfileID)+
						" AND "+permissionStateDirectEqual(constants.FieldPermissionSet_IsOwnedByProfile),
					profileID,
					1,
				),
			},
			func(txCtx context.Context) error {
				return r.CleanupProfilePermissionSet(txCtx, profileID)
			},
		)
	}
	sql := fmt.Sprintf(
		`UPDATE %s SET %s = 1 WHERE %s = ? AND %s = 1`,
		constants.TablePermissionSet,
		constants.FieldPermissionSet_IsDeleted,
		constants.FieldPermissionSet_ProfileID,
		constants.FieldPermissionSet_IsOwnedByProfile,
	)
	_, err := r.getExecutor(ctx).ExecContext(ctx, sql, profileID)
	if err != nil {
		return apperrors.NewInternalError("failed to soft-delete profile permission set", err)
	}
	return nil
}

// CreatePermissionSet creates a new permission set
func (r *PermissionRepository) CreatePermissionSet(ctx context.Context, name, label, description string) (string, error) {
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.CreatePermissionSet"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		var id string
		err := r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectEqual(constants.FieldPermissionSet_Name),
					name,
				),
			},
			func(txCtx context.Context) error {
				var err error
				id, err = r.CreatePermissionSet(txCtx, name, label, description)
				return err
			},
		)
		return id, err
	}
	id := utils.GenerateID()
	values := map[string]interface{}{
		constants.FieldID:                        id,
		constants.FieldPermissionSet_Name:        name,
		constants.FieldPermissionSet_Label:       label,
		constants.FieldPermissionSet_Description: description,
		constants.FieldCreatedDate:               time.Now(),
	}

	qr := query.Insert(constants.TablePermissionSet, values).Build()

	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return "", apperrors.NewInternalError("failed to create permission set", err)
	}
	return id, nil
}

// UpdatePermissionSet updates a permission set
func (r *PermissionRepository) UpdatePermissionSet(ctx context.Context, id, name, label, description string) error {
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.UpdatePermissionSet"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			[]permissionStateDirectTarget{
				managedPermissionSetStateTarget(
					permissionStateDirectEqual(constants.FieldID),
					id,
				),
			},
			func(txCtx context.Context) error {
				return r.UpdatePermissionSet(txCtx, id, name, label, description)
			},
		)
	}
	updates := map[string]interface{}{
		constants.FieldPermissionSet_Name:        name,
		constants.FieldPermissionSet_Label:       label,
		constants.FieldPermissionSet_Description: description,
	}

	qr := query.Update(constants.TablePermissionSet).
		Set(updates).
		Where(constants.FieldID, id).
		Build()

	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to update permission set", err)
	}
	return nil
}

// DeletePermissionSet deletes a permission set
func (r *PermissionRepository) DeletePermissionSet(ctx context.Context, id string) error {
	const writerID = "nexuscrm-persistence/perm_sets.go#PermissionRepository.DeletePermissionSet"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		return r.runPermissionStateDirectWriter(
			ctx,
			writerID,
			permissionSetDeletePermissionStateTargets(id),
			func(txCtx context.Context) error {
				return r.DeletePermissionSet(txCtx, id)
			},
		)
	}
	// First delete assignments
	qr1 := query.Delete(constants.TablePermissionSetAssignment).
		Where(constants.FieldPermissionSetAssignment_PermissionSetID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr1.SQL, qr1.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete permission set assignments", err)
	}

	appQuery := query.From(constants.TableConnectedAppAccess).
		SkipID().
		Select([]string{constants.FieldSysConnectedAppAccess_ConnectedAppID}).
		Where(constants.FieldSysConnectedAppAccess_PermissionSetID, id).
		Distinct().
		Build()
	rows, err := r.getExecutor(ctx).QueryContext(ctx, appQuery.SQL, appQuery.Params...)
	if err != nil {
		return apperrors.NewInternalError("failed to query connected app access for permission set", err)
	}
	var appIDs []string
	for rows.Next() {
		var appID string
		if err := rows.Scan(&appID); err != nil {
			rows.Close()
			return apperrors.NewInternalError("failed to scan connected app access for permission set", err)
		}
		appIDs = append(appIDs, appID)
	}
	if err := rows.Close(); err != nil {
		return apperrors.NewInternalError("failed to close connected app access rows", err)
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError("failed to iterate connected app access for permission set", err)
	}
	sort.Strings(appIDs)

	for _, appID := range appIDs {
		if err := r.retirePermissionSetConnectedAppAccess(ctx, appID, func(txCtx context.Context) error {
			deleteAccessQuery := query.Delete(constants.TableConnectedAppAccess).
				Where(constants.FieldSysConnectedAppAccess_PermissionSetID, id).
				Where(constants.FieldSysConnectedAppAccess_ConnectedAppID, appID).
				Build()
			if _, err := r.getExecutor(txCtx).ExecContext(
				txCtx,
				deleteAccessQuery.SQL,
				deleteAccessQuery.Params...,
			); err != nil {
				return apperrors.NewInternalError(
					"failed to delete connected app access for permission set",
					err,
				)
			}
			return nil
		}); err != nil {
			return err
		}
	}

	// Delete permissions
	qr2 := query.Delete(constants.TableObjectPermissions).
		Where(constants.FieldObjectPermissions_ParentID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr2.SQL, qr2.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete object permissions for permission set", err)
	}

	qr3 := query.Delete(constants.TableFieldPermissions).
		Where(constants.FieldObjectPermissions_ParentID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr3.SQL, qr3.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete field permissions for permission set", err)
	}

	// Delete the set itself
	qr4 := query.Delete(constants.TablePermissionSet).
		Where(constants.FieldID, id).
		Build()
	if _, err := r.getExecutor(ctx).ExecContext(ctx, qr4.SQL, qr4.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete permission set", err)
	}
	return nil
}

// retirePermissionSetConnectedAppAccess closes every credential family and
// removes one app's PermissionSet-derived access rows in the same app-locked
// pessimistic transaction. Direct PermissionSet deletion bypasses records
// lifecycle hooks, so it must carry the same client -> grant retirement rule
// itself rather than leaving a live connection behind.
func (r *PermissionRepository) retirePermissionSetConnectedAppAccess(
	ctx context.Context,
	appID string,
	deleteAccess func(context.Context) error,
) error {
	systemRepository := NewSystemRepository(r.db)
	if !isPessimisticGrantTransaction(ctx) {
		if err := systemRepository.SetTransactionManager(NewTransactionManager(database.NewConnectionFromDB(r.db))); err != nil {
			return err
		}
	}
	return systemRepository.WithConnectedAppCredentialIssuance(ctx, appID, func(txCtx context.Context) error {
		sessionRepository := NewSessionRepository(r.db)
		oauthRepository := NewOAuthRepository(r.db)
		if _, err := sessionRepository.RevokeAllSessionsForConnectedApp(txCtx, appID); err != nil {
			return apperrors.NewInternalError("failed to revoke sessions after connected app access deletion", err)
		}
		if _, err := oauthRepository.RevokeRefreshTokensForConnectedApp(txCtx, appID); err != nil {
			return err
		}
		if _, err := oauthRepository.RevokeAuthorizationGrantsForClient(
			ports.WithAuthorizationClientLock(txCtx, appID),
			appID,
			"system:permission-set-deleted",
		); err != nil {
			return err
		}
		return deleteAccess(txCtx)
	})
}

func permissionSetDeletePermissionStateTargets(id string) []permissionStateDirectTarget {
	rootTarget := managedPermissionSetStateTarget(
		permissionStateDirectEqual(constants.FieldID),
		id,
	)
	cascade := func(target permissionStateDirectTarget) permissionStateDirectTarget {
		return permissionStateDirectTargetWithPermissionSetDefinitionRootCascade(target, rootTarget)
	}
	connectedAppAccessTarget := permissionStateDirectTargetWithConnectedAppPolicyParentPrelock(
		cascade(permissionStateDirectTargetFor(
			constants.TableConnectedAppAccess,
			permissionStateDirectEqual(constants.FieldSysConnectedAppAccess_PermissionSetID),
			id,
		)),
		constants.FieldSysConnectedAppAccess_ConnectedAppID,
	)
	return []permissionStateDirectTarget{
		rootTarget,
		cascade(permissionStateDirectTargetFor(
			constants.TableFieldPermissions,
			permissionStateDirectEqual(constants.FieldFieldPermissions_ParentID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TableObjectPermissions,
			permissionStateDirectEqual(constants.FieldObjectPermissions_ParentID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetAppVisibility,
			permissionStateDirectEqual(constants.FieldPermissionSetAppVisibility_PermissionSetID),
			id,
		)),
		permissionStateDirectTargetFor(
			constants.TablePermissionSetAssignment,
			permissionStateDirectEqual(constants.FieldPermissionSetAssignment_PermissionSetID),
			id,
		),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetCustomPermission,
			permissionStateDirectEqual(constants.FieldPermissionSetCustomPermission_PermissionSetID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetGroupComponent,
			permissionStateDirectEqual(constants.FieldPermissionSetGroupComponent_PermissionSetID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetPermission,
			permissionStateDirectEqual(constants.FieldSysPermissionSetPermission_PermissionSetID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetTabSetting,
			permissionStateDirectEqual(constants.FieldPermissionSetTabSetting_PermissionSetID),
			id,
		)),
		cascade(permissionStateDirectTargetFor(
			constants.TablePermissionSetRecordTypeVisibility,
			permissionStateDirectEqual(constants.FieldPermissionSetRecordTypeVisibility_PermissionSetID),
			id,
		)),
		connectedAppAccessTarget,
	}
}