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

// --- 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_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 = ?
	`, cols, constants.TableObjectPermissions, constants.FieldObjectPermissions_ParentID)

	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.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.FieldObjectPermissions_ParentID, constants.FieldObjectPermissions_SobjectType,
		constants.FieldFieldPermissions_Field, constants.FieldFieldPermissions_PermissionsRead,
		constants.FieldFieldPermissions_PermissionsEdit,
	}, ", ")

	query := fmt.Sprintf(`
		SELECT %s
		FROM %s
		WHERE %s = ?
	`, cols, constants.TableFieldPermissions, constants.FieldObjectPermissions_ParentID)

	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.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
}

// 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 IN (
				SELECT %s FROM %s WHERE %s = ? AND %s = 1
				UNION
				SELECT %s FROM %s WHERE %s = ?
				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
			)
		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_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSetAssignment_PermissionSetID, constants.TablePermissionSetAssignment,
		constants.FieldPermissionSetAssignment_AssigneeID,
		// 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,
		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
func (r *PermissionRepository) ListEffectiveFieldPermissionsForUser(ctx context.Context, userID, profileID string) ([]models.FieldPermissions, error) {
	query := fmt.Sprintf(`
		SELECT
			%s, %s,
			MAX(%s), MAX(%s)
		FROM %s
		WHERE
			%s IN (
				SELECT %s FROM %s WHERE %s = ? AND %s = 1
				UNION
				SELECT %s FROM %s WHERE %s = ?
				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
			)
		GROUP BY %s, %s
	`, constants.FieldObjectPermissions_SobjectType, constants.FieldFieldPermissions_Field,
		constants.FieldFieldPermissions_PermissionsRead, constants.FieldFieldPermissions_PermissionsEdit,
		constants.TableFieldPermissions,
		constants.FieldFieldPermissions_ParentID,
		constants.FieldID, constants.TablePermissionSet,
		constants.FieldPermissionSet_ProfileID, constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSetAssignment_PermissionSetID, constants.TablePermissionSetAssignment,
		constants.FieldPermissionSetAssignment_AssigneeID,
		// 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,
		constants.FieldFieldPermissions_SobjectType, constants.FieldFieldPermissions_Field)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, query, profileID, userID, userID)
	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) {
	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
	}

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

	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 {
	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) {
	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 {
	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 {
	// First delete assignments
	qr1 := query.Delete(constants.TablePermissionSetAssignment).
		Where(constants.FieldObjectPermissions_ParentID, 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)
	}

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