package ports

import (
	"context"
	"time"

	"github.com/nexuscrm/backend/internal/domain/rls"
	"github.com/nexuscrm/backend/internal/domain/schema"
	"github.com/nexuscrm/shared/pkg/models"
)

// PermissionSetRef is a reference to a permission set within a group
type PermissionSetRef struct {
	ID      string
	APIName string
	Label   string
}

// RelatedListConfig represents related list configuration without database types
type RelatedListConfig struct {
	LookupFieldAPI    string
	LookupFieldLabel  string
	ChildObjectAPI    string
	ChildPluralLabel  string
	RelatedListLabel  *string // nil instead of sql.NullString
	RelatedListFields *string // nil instead of sql.NullString
}

// RecordRepository defines the interface for generic record persistence
type RecordRepository interface {
	// Exists checks if a record exists by ID
	Exists(ctx context.Context, tableName string, id string) (bool, error)

	// GetLock retrieves a record by ID and locks it (SELECT ... FOR UPDATE)
	GetLock(ctx context.Context, tableName string, id string) (models.SObject, error)

	// Insert executes an INSERT statement
	Insert(ctx context.Context, tableName string, record models.SObject) error

	// BulkInsert executes a multi-row INSERT statement for batch insertions
	BulkInsert(ctx context.Context, tableName string, records []models.SObject, allColumns []string, options interface{}) error

	// BulkInsertWithLanding executes bulk insert and reports, per input
	// record, whether that record was actually inserted. This is required for
	// callers that run post-insert side effects under INSERT IGNORE, where a
	// duplicate row is a success but must not trigger side effects.
	BulkInsertWithLanding(ctx context.Context, tableName string, records []models.SObject, allColumns []string, options interface{}) ([]bool, error)

	// Update executes an UPDATE statement
	Update(ctx context.Context, tableName string, id string, updates models.SObject) error

	// Delete executes a DELETE statement
	Delete(ctx context.Context, tableName string, id string) error

	// GetChildren retrieves active child records for cascade delete
	GetChildren(ctx context.Context, childTable string, foreignKey string, parentID string) ([]models.SObject, error)

	// ExistsByField checks if any record exists matching a specific field value
	ExistsByField(ctx context.Context, tableName string, fieldName string, value interface{}) (bool, error)

	// FindOne returns a single record by ID, excluding deleted
	FindOne(ctx context.Context, tableName string, id string) (models.SObject, error)

	// FindMany returns multiple records by IDs, excluding deleted. Returns a map of ID -> record.
	FindMany(ctx context.Context, tableName string, ids []string) (map[string]models.SObject, error)

	// FindOneByField returns a single record matching a specific field value, excluding deleted
	FindOneByField(ctx context.Context, tableName string, fieldName string, value interface{}) (models.SObject, error)

	// FindAny returns a record by ID, INCLUDING deleted ones
	FindAny(ctx context.Context, tableName string, id string) (models.SObject, error)

	// PhysicalDelete permanently removes a record
	PhysicalDelete(ctx context.Context, tableName string, id string) error

	// DeleteByField deletes records matching a specific field value
	DeleteByField(ctx context.Context, tableName string, fieldName string, value interface{}) error

	// CheckUniqueness checks if a value exists for a field, excluding a specific ID
	CheckUniqueness(ctx context.Context, schema *models.ObjectMetadata, fieldName string, value interface{}, excludeID string) (bool, error)

	// FindRecycleBinItemsByUser retrieves recycle bin items deleted by a specific user
	FindRecycleBinItemsByUser(ctx context.Context, username string) ([]models.SObject, error)

	// FindAllRecycleBinItems retrieves all recycle bin items (Admin only typically)
	FindAllRecycleBinItems(ctx context.Context) ([]models.SObject, error)

	// FindRecycleBinEntry finds a recycle bin row by recycle-bin entry Id.
	FindRecycleBinEntry(ctx context.Context, recycleBinID string) (models.SObject, error)

	// CheckQueueSupport checks if a queue supports a specific object type
	CheckQueueSupport(ctx context.Context, queueID string, objectType string) (bool, error)

	// GetRecordType looks up the object type for a given record ID
	// This is used for operations like DELETE where the object type is unknown
	GetRecordType(ctx context.Context, id string) (string, error)

	// FindObjectTypesForIDs finds the object type (table name) for a list of IDs by checking multiple tables.
	// Returns a map of ID -> TableName.
	FindObjectTypesForIDs(ctx context.Context, ids []string, tableNames []string) (map[string]string, error)
}

// TransactionManager defines the interface for managing transactions
type TransactionManager interface {
	// WithTransaction executes a function within a database transaction
	WithTransaction(ctx context.Context, fn func(ctx context.Context) error) error

	// WithRetry executes a function within a transaction with automatic retry on deadlock
	WithRetry(ctx context.Context, fn func(ctx context.Context) error, maxRetries int) error

	// IsTransactionActive checks if a transaction is currently active in the context
	IsTransactionActive(ctx context.Context) bool

	// ExtractTx returns the underlying transaction object (as interface{}) from context
	ExtractTx(ctx context.Context) interface{}

	// DetachTx returns a context with no active transaction, so that callers
	// using the returned context will open a fresh, independent transaction
	// instead of reusing the current one. Used by automation steps that must
	// write to the same row the enclosing transaction holds a lock on.
	DetachTx(ctx context.Context) context.Context
}

// MetadataRepository defines the interface for metadata persistence
type MetadataRepository interface {
	// Schema Operations
	GetSchemaByAPIName(ctx context.Context, apiName string) (*models.ObjectMetadata, error)
	GetAllSchemas(ctx context.Context) ([]*models.ObjectMetadata, error)
	GetFieldsForObject(ctx context.Context, objectID string) ([]models.FieldMetadata, error)
	GetRecordTypes(ctx context.Context, objectAPIName string) ([]*models.RecordType, error)
	GetAutoNumbers(ctx context.Context, objectAPIName string) ([]*models.AutoNumber, error)
	GetAllAutoNumbers(ctx context.Context) ([]*models.AutoNumber, error)
	GetAllIdentifierRules(ctx context.Context) ([]*models.IdentifierRule, error)
	GetAllRelationships(ctx context.Context) ([]*models.Relationship, error)
	GetRelationships(ctx context.Context, childObjectAPIName string) ([]*models.Relationship, error)
	GetFieldDependencies(ctx context.Context, objectAPIName string) ([]*models.FieldDependency, error)
	GetChildRelationships(ctx context.Context, parentObjectAPIName string) ([]*models.ObjectMetadata, error)

	// ConfigureAutoNumberField writes the autonumber v2 format + scope +
	// start onto `_System_Field.auto_number_*` for (objectAPIName,
	// fieldAPIName) and seeds the corresponding `_System_AutoNumberCounter`
	// row (scope=global). Replaces the deleted UpsertAutoNumber which
	// wrote to the retired `_System_AutoNumber` table. Idempotent.
	ConfigureAutoNumberField(ctx context.Context, objectAPIName, fieldAPIName, format string, start int64) error

	// UI & Layouts
	UpsertUIComponent(ctx context.Context, component *models.UIComponent) error
	UpsertSetupPage(ctx context.Context, page *models.SetupPage) error
	GetSetupPages(ctx context.Context) ([]*models.SetupPage, error)
	UpsertLayout(ctx context.Context, layout *models.PageLayout) error
	SaveLayout(ctx context.Context, layout *models.PageLayout) error
	GetLayout(ctx context.Context, id string) (*models.PageLayout, error)
	GetLayouts(ctx context.Context, objectAPIName string) ([]*models.PageLayout, error)
	DeleteLayout(ctx context.Context, id string) error
	SetDefaultLayout(ctx context.Context, objectAPIName string, layoutID string, actorID *string) error
	MarkLayoutCustomized(ctx context.Context, layoutID string)
	AssignLayoutToProfile(ctx context.Context, profileID, objectAPIName, layoutID string) error
	GetLayoutIDForProfile(ctx context.Context, objectAPIName, profileID string) (string, error)
	GetRelatedListConfigs(ctx context.Context, layoutObjectAPIName string) ([]RelatedListConfig, error)

	// Approval Layouts
	GetApprovalLayouts(ctx context.Context, objectAPIName string) ([]*models.ApprovalPageLayout, error)
	GetApprovalLayout(ctx context.Context, id string) (*models.ApprovalPageLayout, error)
	SaveApprovalLayout(ctx context.Context, layout *models.ApprovalPageLayout) error
	DeleteApprovalLayout(ctx context.Context, id string) error
	SetDefaultApprovalLayout(ctx context.Context, objectAPIName string, layoutID string) error

	// Layout Assignment (new specificity-based routing)
	FindBestAssignment(ctx context.Context, objectAPIName string, roleAncestors []string, profileID string, recordTypeID string) (*models.LayoutAssignment, error)
	CreateAssignment(ctx context.Context, assignment *models.LayoutAssignment) error
	DeleteAssignment(ctx context.Context, id string) error
	GetAssignmentsForObject(ctx context.Context, objectAPIName string) ([]*models.LayoutAssignment, error)
	DeleteAssignmentsByLayoutID(ctx context.Context, layoutID string) error

	// Layout History
	WriteLayoutHistory(ctx context.Context, entry *models.PageLayoutHistoryEntry) error
	GetLayoutHistoryMaxVersion(ctx context.Context, layoutID string) (int, error)
	GetLayoutHistoryConfig(ctx context.Context, layoutID string, versionNumber int) (string, error)
	PruneLayoutHistory(ctx context.Context, layoutID string, keepAboveVersion int) error

	// Migration
	GetMigrationVersion(ctx context.Context, migrationID string) (int, error)
	SetMigrationVersion(ctx context.Context, migrationID string, version int, description string) error

	// Themes
	GetActiveTheme(ctx context.Context) (*models.Theme, error)
	GetThemeByName(ctx context.Context, name string) (*models.Theme, error)
	CreateTheme(ctx context.Context, theme *models.Theme) error
	UpdateTheme(ctx context.Context, theme *models.Theme) error
	ActivateTheme(ctx context.Context, themeID string) error

	// Apps
	GetAllApps(ctx context.Context) ([]*models.AppConfig, error)
	GetApp(ctx context.Context, id string) (*models.AppConfig, error)
	GetAppWithTx(ctx context.Context, tx interface{}, id string) (*models.AppConfig, error)
	CreateApp(ctx context.Context, app *models.AppConfig) error
	CreateAppMenuItem(ctx context.Context, app *models.AppConfig) error
	UpdateApp(ctx context.Context, id string, app *models.AppConfig) error
	UpdateAppWithTx(ctx context.Context, tx interface{}, id string, app *models.AppConfig) error
	DeleteApp(ctx context.Context, id string) error

	// Dashboards
	GetAllDashboards(ctx context.Context) ([]*models.DashboardConfig, error)
	GetDashboard(ctx context.Context, id string) (*models.DashboardConfig, error)
	CreateDashboard(ctx context.Context, dashboard *models.DashboardConfig) error
	UpdateDashboard(ctx context.Context, id string, dashboard *models.DashboardConfig) error
	DeleteDashboard(ctx context.Context, id string) error

	// List Views
	GetListViews(ctx context.Context, objectAPIName string) ([]*models.ListView, error)
	CreateListView(ctx context.Context, lv *models.ListView) error
	UpdateListView(ctx context.Context, id string, lv *models.ListView) error
	DeleteListView(ctx context.Context, id string) error
	CountListViews(ctx context.Context, objectAPIName string) (int, error)

	// Actions & Sharing
	GetActions(ctx context.Context, objectAPIName string) ([]*models.ActionMetadata, error)
	GetAction(ctx context.Context, id string) (*models.ActionMetadata, error)
	GetAllActions(ctx context.Context) ([]*models.ActionMetadata, error)
	CreateAction(ctx context.Context, action *models.ActionMetadata) error
	UpdateAction(ctx context.Context, actionID string, updates *models.ActionMetadata) error
	DeleteAction(ctx context.Context, actionID string) error
	CheckActionExists(ctx context.Context, objectAPIName, name string) (bool, error)
	GetSharingRules(ctx context.Context, objectAPIName string) ([]*models.SharingRule, error)

	// FlowDefinition CRUD (Salesforce-compatible)
	GetAllFlowDefinitions(ctx context.Context) ([]*models.FlowDefinition, error)
	GetFlowDefinition(ctx context.Context, id string) (*models.FlowDefinition, error)
	GetFlowDefinitionByName(ctx context.Context, developerName string) (*models.FlowDefinition, error)
	CreateFlowDefinition(ctx context.Context, def *models.FlowDefinition) error
	UpdateFlowDefinition(ctx context.Context, id string, def *models.FlowDefinition) error
	PatchFlowDefinition(ctx context.Context, id string, patch *models.FlowDefinitionPatch) error
	MarkFlowDefinitionCustomized(ctx context.Context, id string) error
	SetLatestVersionId(ctx context.Context, definitionId string, versionId string) error
	SetTriggerInfo(ctx context.Context, definitionId string, triggerType string, objectOrEvent string) error
	DeleteFlowDefinition(ctx context.Context, id string) error

	// FlowVersion CRUD (Salesforce-compatible)
	GetFlowVersions(ctx context.Context, definitionId string) ([]*models.FlowVersion, error)
	GetFlowVersion(ctx context.Context, id string) (*models.FlowVersion, error)
	GetFlowVersionsByIDs(ctx context.Context, ids []string) (map[string]*models.FlowVersion, error)
	GetActiveFlowVersion(ctx context.Context, definitionId string) (*models.FlowVersion, error)
	CreateFlowVersion(ctx context.Context, version *models.FlowVersion) error
	CreateSeededFlowVersion(ctx context.Context, version *models.FlowVersion) error
	CreateTenantFlowVersion(ctx context.Context, version *models.FlowVersion) error
	UpdateFlowVersion(ctx context.Context, id string, version *models.FlowVersion) error
	ActivateFlowVersion(ctx context.Context, id string) error
	DeactivateFlowVersion(ctx context.Context, id string) error
	DeleteFlowVersion(ctx context.Context, id string) error

	// FlowInterview CRUD (Salesforce-compatible)
	GetFlowInterviews(ctx context.Context) ([]*models.FlowInterview, error)
	GetFlowInterview(ctx context.Context, id string) (*models.FlowInterview, error)
	SaveFlowInterview(ctx context.Context, interview *models.FlowInterview) error
	DeleteFlowInterview(ctx context.Context, id string) error

	// Validation Rules
	GetAllValidationRules(ctx context.Context) ([]*models.ValidationRule, error)
	GetValidationRules(ctx context.Context, objectAPIName string) ([]*models.ValidationRule, error)
	GetValidationRule(ctx context.Context, id string) (*models.ValidationRule, error)
	CreateValidationRule(ctx context.Context, rule *models.ValidationRule) error
	UpdateValidationRule(ctx context.Context, id string, updates *models.ValidationRule) error
	DeleteValidationRule(ctx context.Context, id string) error
	CheckValidationRuleExists(ctx context.Context, objectAPIName, name string) (bool, error)
}

// SchemaRepository defines the interface for schema DDL and registry operations
type SchemaRepository interface {
	CreatePhysicalTable(ctx context.Context, def schema.TableDefinition) error
	CreateTableWithStrictMetadata(ctx context.Context, def schema.TableDefinition, objectMeta *models.ObjectMetadata) error
	BatchCreatePhysicalTables(ctx context.Context, defs []schema.TableDefinition) error
	DropTable(tableName string) error
	ValidateSchema(tableName string) error
	ValidateFieldDefinition(field schema.ColumnDefinition) error
	ValidateSchemaRegistry() (*schema.SchemaHealth, error)
	BatchRegisterTables(defs []schema.TableDefinition, tx interface{}) error
	AddColumn(tableName string, col schema.ColumnDefinition) error
	RegisterFieldOnly(tableName string, col schema.ColumnDefinition) error
	EnsureColumn(tableName string, col schema.ColumnDefinition) error
	DropColumn(tableName string, colName string) error
	ModifyColumn(tableName, colName string, col schema.ColumnDefinition) error
	IsSystemColumn(name string) bool
	GetStandardSystemColumns() []schema.ColumnDefinition
	GetStandardFieldMetadata() []models.FieldMetadata
	SaveObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error
	InsertObjectMetadata(ctx context.Context, obj *models.ObjectMetadata, tx interface{}) error
	BatchSaveObjectMetadata(ctx context.Context, objs []*models.ObjectMetadata, tx interface{}) error
	SaveFieldMetadataWithIDs(ctx context.Context, field *models.FieldMetadata, objectID string, objectAPIName string, fieldID string, tx interface{}) error
	BatchSaveFieldMetadata(ctx context.Context, fields []schema.FieldWithContext, tx interface{}) error
	PrepareFieldForBatch(tableName string, col schema.ColumnDefinition) schema.FieldWithContext
	GetTableRegistry(ctx context.Context) ([]*schema.TableRegistryItem, error)
	MapFieldTypeToSQL(fieldType string) string
	ValidateFormula(formulaStr string, env map[string]interface{}) error
	BatchAddColumns(tableName string, fields []models.FieldMetadata) error
}

// RollupRepository defines the interface for rollup calculations
type RollupRepository interface {
	CalculateRollup(ctx context.Context, calcType, summaryObject, summaryField, relationField, parentID string, filter *string) (interface{}, error)
	UpdateParentRollup(ctx context.Context, parentObjName, parentID, targetField string, value interface{}) error
	GetAllParentIDs(ctx context.Context, objectName string) ([]string, error)
}

// Helper for type assertion compilation check
// var _ RecordRepository = (*persistence.RecordRepository)(nil)

// SystemRepository defines the interface for system-level operations
type SystemRepository interface {
	GetLogs(ctx context.Context, limit int) ([]*models.SystemLog, error)
	GetRecentItems(ctx context.Context, userID string, limit int) ([]*models.SystemRecent, error)
	GetConfig(ctx context.Context, key string) (*string, error)
	GetAllConfigs(ctx context.Context) ([]*models.SystemConfig, error)
	GetConfigID(ctx context.Context, key string) (string, error)
	UpsertConfig(ctx context.Context, config *models.SystemConfig) error
	DeleteConfig(ctx context.Context, key string) error
	GetProfileIDByName(ctx context.Context, name string) (string, error)
	GetUserIDByEmail(ctx context.Context, email string) (string, error)
	BatchUpsertProfiles(ctx context.Context, profiles []models.Profile) error
	RenameDuplicateProfilesByName(ctx context.Context, bootstrapID, currentName, newName string) (int64, error)
	BatchUpsertUsers(ctx context.Context, users []models.User) error
	ForceUpdateUserPassword(ctx context.Context, email string, hashedPassword string) error
	GetConnectedAppByClientID(ctx context.Context, clientID string) (*models.SystemConnectedApp, error)
	IsUserPermittedForApp(ctx context.Context, appID string, userID string) (bool, error)
}

// OutboxRepository defines the interface for outbox event storage
type OutboxRepository interface {
	Enqueue(ctx context.Context, eventType string, payload interface{}) (string, error)
	GetPendingEvents(ctx context.Context, limit int) ([]models.OutboxEvent, error)
	ClaimEvent(ctx context.Context, id string) (string, error)
	UpdateStatus(ctx context.Context, id string, status string, errMessage string) error
	IncrementRetry(ctx context.Context, id string, newCount int, errMessage string) error
	CleanupProcessed(ctx context.Context, cutoff time.Time) (int64, error)
}

// UserDeactivationHook is called when a user is deactivated or deleted.
// Implementations should revoke sessions and invalidate caches.
type UserDeactivationHook interface {
	OnUserDeactivated(userID string)
}

// SessionRepository defines the interface for user session management
type SessionRepository interface {
	InsertSession(ctx context.Context, session *models.SystemSession) error
	GetSession(ctx context.Context, sessionID string) (*models.SystemSession, error)
	RevokeSession(ctx context.Context, sessionID string) error
	RevokeAllSessionsForUser(ctx context.Context, userID string) error
	RevokeAllSessionsForConnectedApp(ctx context.Context, appID string) (int64, error)
	UpdateLastActivity(ctx context.Context, sessionID string) error
	CleanupExpiredSessions(ctx context.Context, olderThan time.Duration) (int64, error)
	IsConnectedAppActive(ctx context.Context, appID string) (bool, error)
}

// PermissionRepository defines the interface for permission management
type PermissionRepository interface {
	LoadObjectPermission(ctx context.Context, profileID, objectAPIName string) (*models.ObjectPermissions, error)
	LoadFieldPermission(ctx context.Context, profileID, objectAPIName, fieldAPIName string) (*models.FieldPermissions, error)
	LoadEffectiveObjectPermission(ctx context.Context, user *models.UserSession, objectAPIName string) (*models.ObjectPermissions, error)
	LoadEffectiveFieldPermission(ctx context.Context, user *models.UserSession, objectAPIName, fieldAPIName string) (*models.FieldPermissions, error)
	ListObjectPermissions(ctx context.Context, profileID string) ([]models.ObjectPermissions, error)
	ListFieldPermissions(ctx context.Context, profileID string) ([]models.FieldPermissions, error)
	UpsertObjectPermission(ctx context.Context, perm models.ObjectPermissions) error
	// BatchUpsertObjectPermissions creates or updates multiple object permissions via multi-value INSERT
	BatchUpsertObjectPermissions(ctx context.Context, perms []models.ObjectPermissions) error
	UpsertFieldPermission(ctx context.Context, perm models.FieldPermissions) error
	// BatchUpsertFieldPermissions creates or updates multiple field permissions via multi-value INSERT
	BatchUpsertFieldPermissions(ctx context.Context, perms []models.FieldPermissions) error
	DeleteFieldPermissionsForFields(ctx context.Context, objectAPIName string, fieldAPINames []string) error
	// CountObjectPermissionsByProfile returns the count of object permissions for a profile
	CountObjectPermissionsByProfile(ctx context.Context, profileID string) (int, error)
	// BulkEnsureProfilePermissionSets creates or retrieves PermissionSets for multiple profiles in bulk
	BulkEnsureProfilePermissionSets(ctx context.Context, profiles []struct{ ID, Name string }) (map[string]string, error)
	// BulkCountObjectPermissionsByProfiles returns object permission counts for multiple profiles
	BulkCountObjectPermissionsByProfiles(ctx context.Context, profileIDs []string) (map[string]int, error)
	// BulkCountFieldPermissionsByProfiles returns field permission counts for multiple profiles
	BulkCountFieldPermissionsByProfiles(ctx context.Context, profileIDs []string) (map[string]int, error)
	// BulkCountObjectPermissionsByProfilesAndTypes returns object permission counts for multiple profiles
	// restricted to specific SObject API names.
	BulkCountObjectPermissionsByProfilesAndTypes(ctx context.Context, profileIDs []string, objectAPINames []string) (map[string]int, error)
	// BulkCountFieldPermissionsByProfilesAndTypes returns field permission counts for multiple profiles
	// restricted to specific SObject API names.
	BulkCountFieldPermissionsByProfilesAndTypes(ctx context.Context, profileIDs []string, objectAPINames []string) (map[string]int, error)
	ListPermissionSetObjectPermissions(ctx context.Context, permissionSetID string) ([]models.ObjectPermissions, error)
	ListPermissionSetFieldPermissions(ctx context.Context, permissionSetID string) ([]models.FieldPermissions, error)
	ListEffectiveObjectPermissionsForUser(ctx context.Context, userID, profileID string) ([]models.ObjectPermissions, error)
	ListEffectiveFieldPermissionsForUser(ctx context.Context, userID, profileID string) ([]models.FieldPermissions, error)
	CreatePermissionSet(ctx context.Context, name, label, description string) (string, error)
	EnsureProfilePermissionSet(ctx context.Context, profileID, profileName string) (string, error)
	CleanupProfilePermissionSet(ctx context.Context, profileID string) error
	UpdatePermissionSet(ctx context.Context, id, name, label, description string) error
	DeletePermissionSet(ctx context.Context, id string) error
	GetUserProfileID(ctx context.Context, userID string) (string, error)
	GetAllRoles(ctx context.Context) ([]*models.UserRole, error)
	CreateRole(ctx context.Context, name, description string, parentRoleID *string) (string, error)
	GetRole(ctx context.Context, id string) (*models.UserRole, error)
	UpdateRole(ctx context.Context, id, name, description string, parentRoleID *string) error
	DeleteRole(ctx context.Context, id string) error
	IsUserInGroup(ctx context.Context, groupID, userID string) (bool, error)
	GetManualShareAccessLevels(ctx context.Context, objectAPIName, recordID, userID string) ([]string, error)
	GetTeamMemberAccessLevel(ctx context.Context, objectAPIName, recordID, userID string) (*string, error)
	GetRecordOwnerID(ctx context.Context, tableName, recordID string) (string, error)
	// GetRecordByID retrieves a full record as SObject for parent access checks
	// (e.g. ControlledByParent sharing needs full parent record for criteria-based sharing rules).
	GetRecordByID(ctx context.Context, tableName, recordID string) (models.SObject, error)
	GetPendingApprovalLockInfo(ctx context.Context, targetObjectID string) (processInstanceID string, lockType string, err error)
	HasPendingApprovalWorkitemForUser(ctx context.Context, processInstanceID, userID string) (bool, error)
	// SOQL RLS pre-resolution: resolve subqueries into ID lists
	GetUserGroupIDs(ctx context.Context, userID string) ([]string, error)
	GetManualShareRecordIDs(ctx context.Context, objectAPIName, userID string) ([]string, error)
	GetTeamMemberRecordIDs(ctx context.Context, objectAPIName, userID string) ([]string, error)
	GetAllProfiles(ctx context.Context) ([]*models.Profile, error)

	// System Permissions (Salesforce-compatible)
	ListSystemPermissions(ctx context.Context) ([]models.SystemPermission, error)
	GetEffectiveSystemPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error)
	GetEffectiveCustomPermissions(ctx context.Context, userID, profileID string) (map[string]bool, error)
	SetSystemPermission(ctx context.Context, permSetID, apiName string, enabled bool) error
	GetPermissionSetGrants(ctx context.Context, permSetID string) (map[string]bool, error)
	GetPermissionSetGrantsMany(ctx context.Context, permSetIDs []string) (map[string]map[string]bool, error)
	LockPermissionSet(ctx context.Context, permSetID string) (bool, error)

	// Custom Permission CRUD
	ListCustomPermissions(ctx context.Context) ([]models.SystemCustomPermission, error)
	GetCustomPermission(ctx context.Context, id string) (*models.SystemCustomPermission, error)
	CreateCustomPermission(ctx context.Context, perm *models.SystemCustomPermission) error
	UpdateCustomPermission(ctx context.Context, perm *models.SystemCustomPermission) error
	DeleteCustomPermission(ctx context.Context, id string) error

	// Permission Set Group CRUD
	ListPermissionSetGroups(ctx context.Context) ([]models.PermissionSetGroup, error)
	GetPermissionSetGroupByID(ctx context.Context, id string) (*models.PermissionSetGroup, error)
	GetPermissionSetGroupComponents(ctx context.Context, groupID string) ([]PermissionSetRef, error)
	CreatePermissionSetGroup(ctx context.Context, group *models.PermissionSetGroup, permSetIDs []string) error
	UpdatePermissionSetGroup(ctx context.Context, group *models.PermissionSetGroup, permSetIDs []string) error
	DeletePermissionSetGroup(ctx context.Context, id string) error

	// CleanupDirectProfilePermissions removes old bootstrap data that used ProfileId directly as ParentId
	CleanupDirectProfilePermissions(ctx context.Context, profileIDs []string) error
}

// UserWithPassword extends SystemUser to include password hash for auth checks
// Moved from persistence to allow interface decoupling
type UserWithPassword struct {
	*models.User
	PasswordHash string
}

// UserRepository defines the interface for user management
type UserRepository interface {
	CheckUserExistsByEmail(ctx context.Context, email string) (bool, error)
	CheckUserExistsByID(ctx context.Context, id string) (bool, error)
	CheckEmailConflict(ctx context.Context, email, excludeID string) (bool, error)
	UpdateUser(ctx context.Context, userID string, updates map[string]interface{}) error
	DeleteUser(ctx context.Context, userID string) error
	FindAll(ctx context.Context) ([]*models.User, error)
	GetUserRoleID(ctx context.Context, userID string) (*string, error)
	FindUserByEmailWithPassword(ctx context.Context, email string) (*UserWithPassword, error)
	FindUserByIDWithPassword(ctx context.Context, userID string) (*UserWithPassword, error)
	UpdatePassword(ctx context.Context, userID, passwordHash string) error
	GetUserByID(ctx context.Context, userID string) (*models.User, error)
	CreateUser(ctx context.Context, user *models.User) error
	GetFirstUserIDByProfile(ctx context.Context, profileID string) (string, error)
	GetRoleNameByID(ctx context.Context, roleID string) (string, error)
	GetProfileNameByID(ctx context.Context, profileID string) (string, error)
	GetUserIDsByRoleIDs(ctx context.Context, roleIDs []string) ([]string, error)

	// ResetFailedLoginAttempts resets the failed login counter and clears any lockout for a user.
	ResetFailedLoginAttempts(ctx context.Context, userID string) error

	// IncrementFailedAndLockIfNeeded atomically increments the failed login counter
	// and locks the account if the threshold is reached.
	IncrementFailedAndLockIfNeeded(ctx context.Context, userID string, maxAttempts int, lockDuration time.Duration) (locked bool, err error)
}

// BulkRepository defines the interface for bulk job management
type BulkRepository interface {
	CreateJob(ctx context.Context, job *models.BulkJob) error
	GetJob(ctx context.Context, jobId string) (*models.BulkJob, error)
	UpdateJobState(ctx context.Context, jobId string, state models.BulkJobState) error
	UpdateJob(ctx context.Context, job *models.BulkJob) error
	CreateBatch(ctx context.Context, batch *models.BulkBatch) error
	GetBatches(ctx context.Context, jobId string) ([]*models.BulkBatch, error)
}

// QueryRepository defines the interface for complex read operations
type QueryRepository interface {
	Find(ctx context.Context, tableSchema *models.ObjectMetadata, req models.QueryRequest, rlsResult *rls.Result, visibleFields []string) (*models.QueryResult, error)
	Search(ctx context.Context, schema *models.ObjectMetadata, term string, searchFields []string, selectFields []string, limit int, offset int, orderBy string, orderDir string, filterExpr string, rlsResult *rls.Result) ([]models.SObject, error)
	// RunAnalytics executes an aggregation query
	RunAnalytics(ctx context.Context, tableSchema *models.ObjectMetadata, q models.AnalyticsQuery, rlsResult *rls.Result) (interface{}, error)
	ExecuteRawSQL(ctx context.Context, sql string, params []interface{}) ([]models.SObject, error)
	GetLookupNames(ctx context.Context, tableSchema *models.ObjectMetadata, ids []string, idField string, nameField string) ([]models.SObject, error)
	GetRecordsByIDs(ctx context.Context, tableSchema *models.ObjectMetadata, ids []string, fields []string) ([]models.SObject, error)
}

// OAuthRepository defines the interface for OAuth code persistence
type OAuthRepository interface {
	// StoreAuthCode saves an authorization code with expiration
	StoreAuthCode(ctx context.Context, code, clientID, redirectURI, scope, userID, nonce string, expiresAt time.Time) error

	// ConsumeAuthCode retrieves and marks a code as used (one-time use)
	// Returns nil if code not found, expired, or already used
	ConsumeAuthCode(ctx context.Context, code string) (*OAuthCodeData, error)

	// CleanupExpiredCodes removes expired codes (for background cleanup)
	CleanupExpiredCodes(ctx context.Context) (int64, error)
}

// OAuthCodeData represents an OAuth authorization code record
type OAuthCodeData struct {
	ClientID    string
	RedirectURI string
	Scope       string
	UserID      string
	Nonce       string
	ExpiresAt   time.Time
}