Utility Functions

def format_soql(query, *args, **kwargs):
    """
    Format SOQL query with safe parameter substitution and escaping.

    Parameters:
    - query: SOQL query string with {} placeholders for positional args or {name} for keyword args
    - *args: Positional arguments for substitution
    - **kwargs: Keyword arguments for substitution

    Returns:
    str: Safely formatted SOQL query with properly escaped values

    Examples:
    format_soql("SELECT Id FROM Account WHERE Name = {}", "O'Reilly Corp")
    format_soql("SELECT Id FROM Account WHERE Name = {name} AND Type = {type}", 
                name="Test Corp", type="Customer")
    """

def quote_soql_value(value):
    """
    Quote and escape individual value for safe SOQL usage.

    Parameters:
    - value: Value to quote and escape for SOQL

    Returns:
    str: Properly quoted and escaped value safe for SOQL queries

    Examples:
    quote_soql_value("O'Reilly") -> "'O\\'Reilly'"
    quote_soql_value("Test Corp") -> "'Test Corp'"
    """

def format_external_id(field, value):
    """
    Create external ID string for upsert operations.

    Parameters:
    - field: External ID field API name
    - value: External ID value

    Returns:
    str: Formatted external ID string for upsert operations

    Examples:
    format_external_id("External_ID__c", "EXT123") -> "External_ID__c/EXT123"
    """

class SoqlFormatter:
    def __init__(self):
        """
        Custom formatter for SOQL queries with special format specifications.
        
        Supports:
        - :literal - Insert value without quoting (for field names, functions)
        - :like - Escape value for LIKE expressions with % and _ wildcards
        """
        
    def format(self, format_string, *args, **kwargs):
        """
        Format string with SOQL-specific format specifications.

        Parameters:
        - format_string: Format string with placeholders and format specs
        - *args: Positional arguments
        - **kwargs: Keyword arguments

        Returns:
        str: Formatted string with SOQL-safe values
        """

from simple_salesforce import format_soql, quote_soql_value

# Simple parameter substitution
account_name = "O'Reilly Corporation"
query = format_soql("SELECT Id, Name FROM Account WHERE Name = {}", account_name)
print(query)
# Output: SELECT Id, Name FROM Account WHERE Name = 'O\'Reilly Corporation'

# Multiple parameters with keywords
query = format_soql(
    "SELECT Id, Name FROM Account WHERE Name = {name} AND Type = {account_type}",
    name="Test Corp",
    account_type="Customer"
)
print(query)
# Output: SELECT Id, Name FROM Account WHERE Name = 'Test Corp' AND Type = 'Customer'

# Numeric and boolean values
query = format_soql(
    "SELECT Id FROM Account WHERE NumberOfEmployees > {} AND IsPersonAccount = {}",
    100,
    False
)
print(query)
# Output: SELECT Id FROM Account WHERE NumberOfEmployees > 100 AND IsPersonAccount = false

# Date and datetime handling
from datetime import datetime, date

query = format_soql(
    "SELECT Id FROM Account WHERE CreatedDate >= {} AND LastModifiedDate = {}",
    datetime(2023, 1, 1),
    date.today()
)
print(query)
# Output includes properly formatted ISO dates

from simple_salesforce.format import SoqlFormatter

formatter = SoqlFormatter()

# Literal values (field names, functions) - no quoting
field_name = "Custom_Field__c"
query = formatter.format(
    "SELECT Id, {field:literal} FROM Account ORDER BY {field:literal}",
    field=field_name
)
print(query)
# Output: SELECT Id, Custom_Field__c FROM Account ORDER BY Custom_Field__c

# LIKE expressions with proper escaping
search_term = "Test% Corp_Name"
query = formatter.format(
    "SELECT Id FROM Account WHERE Name LIKE '{search:like}'",
    search=search_term
)
print(query)
# Output: SELECT Id FROM Account WHERE Name LIKE 'Test\\% Corp\\_Name'

# Combined usage
query = formatter.format(
    "SELECT {fields:literal} FROM {object:literal} WHERE Name LIKE '{pattern:like}' AND Type = '{type}'",
    fields="Id, Name, Type",
    object="Account",
    pattern="%Corp%",
    type="Customer"
)

from simple_salesforce import format_external_id

# Create external ID references for upsert
external_ref = format_external_id("External_ID__c", "EXT123")
print(external_ref)
# Output: External_ID__c/EXT123

# Usage in upsert operations
sf = Salesforce(username='user@example.com', password='pass', security_token='token')

# Upsert using external ID
result = sf.Account.upsert(
    external_ref,  # Uses External_ID__c/EXT123
    {
        'Name': 'Updated Account Name',
        'Type': 'Customer'
    }
)

# Bulk upsert with external IDs
upsert_data = []
for i, external_id in enumerate(['EXT001', 'EXT002', 'EXT003']):
    upsert_data.append({
        'External_ID__c': external_id,
        'Name': f'Account {i+1}',
        'Type': 'Customer'
    })

bulk_results = sf.bulk.Account.upsert(
    upsert_data,
    external_id_field='External_ID__c'
)

def build_dynamic_query(base_fields, conditions, order_by=None):
    """Build SOQL query dynamically with safe parameter handling."""
    
    # Start with base query
    query_parts = ["SELECT"]
    
    # Add fields safely
    if isinstance(base_fields, list):
        fields = ', '.join(base_fields)
    else:
        fields = base_fields
    
    query_parts.append(fields)
    query_parts.append("FROM Account")
    
    # Add WHERE conditions safely
    if conditions:
        where_clauses = []
        values = []
        
        for condition in conditions:
            if condition['operator'] == '=':
                where_clauses.append(f"{condition['field']} = {{}}")
                values.append(condition['value'])
            elif condition['operator'] == 'LIKE':
                where_clauses.append(f"{condition['field']} LIKE {{}}")
                values.append(f"%{condition['value']}%")
            elif condition['operator'] == 'IN':
                placeholders = ', '.join(['{}'] * len(condition['value']))
                where_clauses.append(f"{condition['field']} IN ({placeholders})")
                values.extend(condition['value'])
        
        if where_clauses:
            query_parts.append("WHERE")
            query_parts.append(" AND ".join(where_clauses))
    
    # Add ORDER BY
    if order_by:
        query_parts.append(f"ORDER BY {order_by}")
    
    # Format safely
    base_query = " ".join(query_parts)
    return format_soql(base_query, *values)

# Usage
conditions = [
    {'field': 'Name', 'operator': 'LIKE', 'value': 'Corp'},
    {'field': 'Type', 'operator': '=', 'value': 'Customer'},
    {'field': 'Industry', 'operator': 'IN', 'value': ['Technology', 'Healthcare']}
]

safe_query = build_dynamic_query(
    base_fields=['Id', 'Name', 'Type', 'Industry'],
    conditions=conditions,
    order_by='Name'
)

# Execute safely
results = sf.query(safe_query)

# Type aliases for better code documentation
Headers = dict  # Type alias for MutableMapping[str, str]
Proxies = dict  # Type alias for MutableMapping[str, str]
BulkDataAny = list  # Type alias for List[Mapping[str, Any]]
BulkDataStr = list  # Type alias for List[Mapping[str, str]]

# Named tuples for structured data
class Usage:
    """
    API usage information from Salesforce headers.
    
    Attributes:
    - used: Number of API calls used
    - total: Total API calls allowed
    """
    def __init__(self, used: int, total: int):
        self.used = used
        self.total = total

class PerAppUsage:
    """
    Per-application API usage information.
    
    Attributes:
    - used: API calls used by this app
    - total: Total API calls allowed for this app
    - name: Application name
    """
    def __init__(self, used: int, total: int, name: str):
        self.used = used
        self.total = total
        self.name = name

# Escaping constants
soql_escapes = str.maketrans({
    "'": "\\'",
    "\\": "\\\\",
    "\n": "\\n",
    "\r": "\\r",
    "\t": "\\t",
    "\b": "\\b",
    "\f": "\\f",
    '"': '\\"'
})

soql_like_escapes = str.maketrans({
    "%": "\\%",
    "_": "\\_",
    "'": "\\'",
    "\\": "\\\\"
})

def getUniqueElementValueFromXmlString(xmlString, elementName):
    """
    Extract single element value from XML string.

    Parameters:
    - xmlString: XML content as string
    - elementName: Element tag name to extract

    Returns:
    str: Element text content or None if not found

    Example:
    xml = "<response><sessionId>abc123</sessionId></response>"
    session_id = getUniqueElementValueFromXmlString(xml, "sessionId")
    """

def date_to_iso8601(date):
    """
    Convert date object to ISO8601 string with URL encoding.

    Parameters:
    - date: datetime.date or datetime.datetime object

    Returns:
    str: URL-encoded ISO8601 date string suitable for Salesforce APIs

    Example:
    from datetime import datetime
    iso_date = date_to_iso8601(datetime(2023, 12, 25, 14, 30))
    # Returns: "2023-12-25T14%3A30%3A00"
    """

def exception_handler(result, name=""):
    """
    Route HTTP errors to appropriate Salesforce exception types.

    Parameters:
    - result: requests.Response object
    - name: Optional operation name for error context

    Raises:
    - SalesforceError: Base exception for Salesforce API errors
    - Specific exceptions based on HTTP status codes

    Example:
    response = requests.get(url)
    exception_handler(response, "Account query")
    """

def call_salesforce(url, method, session, headers, **kwargs):
    """
    Utility function for making HTTP calls to Salesforce with proper error handling.

    Parameters:
    - url: Salesforce API endpoint URL
    - method: HTTP method (GET, POST, PUT, PATCH, DELETE)
    - session: requests.Session object
    - headers: HTTP headers dictionary
    - **kwargs: Additional request parameters (data, json, params, etc.)

    Returns:
    requests.Response: HTTP response object

    Raises:
    - Appropriate Salesforce exceptions for error responses
    """

def list_from_generator(generator_function):
    """
    Convert generator function result to list.

    Parameters:
    - generator_function: Function that returns a generator

    Returns:
    list: All items from the generator as a list

    Example:
    def query_generator():
        for i in range(1000):
            yield f"Record {i}"
    
    all_records = list_from_generator(query_generator)
    """

class SafeSOQLBuilder:
    """Builder for constructing SOQL queries safely."""
    
    def __init__(self, sobject):
        self.sobject = sobject
        self.fields = []
        self.conditions = []
        self.order_fields = []
        self.limit_value = None
        
    def select(self, *fields):
        """Add fields to SELECT clause."""
        self.fields.extend(fields)
        return self
    
    def where(self, field, operator, value):
        """Add WHERE condition."""
        self.conditions.append((field, operator, value))
        return self
    
    def order_by(self, field, direction='ASC'):
        """Add ORDER BY field."""
        self.order_fields.append(f"{field} {direction}")
        return self
    
    def limit(self, count):
        """Set LIMIT clause."""
        self.limit_value = count
        return self
    
    def build(self):
        """Build the final SOQL query."""
        if not self.fields:
            raise ValueError("No fields specified for SELECT")
        
        # Build query parts
        query_parts = []
        values = []
        
        # SELECT clause
        fields_str = ', '.join(self.fields)
        query_parts.append(f"SELECT {fields_str}")
        
        # FROM clause
        query_parts.append(f"FROM {self.sobject}")
        
        # WHERE clause
        if self.conditions:
            where_parts = []
            for field, operator, value in self.conditions:
                if operator.upper() == 'IN' and isinstance(value, (list, tuple)):
                    placeholders = ', '.join(['{}'] * len(value))
                    where_parts.append(f"{field} IN ({placeholders})")
                    values.extend(value)
                else:
                    where_parts.append(f"{field} {operator} {{}}")
                    values.append(value)
            
            query_parts.append(f"WHERE {' AND '.join(where_parts)}")
        
        # ORDER BY clause
        if self.order_fields:
            query_parts.append(f"ORDER BY {', '.join(self.order_fields)}")
        
        # LIMIT clause
        if self.limit_value:
            query_parts.append(f"LIMIT {self.limit_value}")
        
        # Format safely
        base_query = ' '.join(query_parts)
        return format_soql(base_query, *values)

# Usage
query = (SafeSOQLBuilder('Account')
         .select('Id', 'Name', 'Type', 'Industry')
         .where('Name', 'LIKE', '%Corp%')
         .where('Type', '=', 'Customer')
         .where('Industry', 'IN', ['Technology', 'Healthcare'])
         .order_by('Name')
         .limit(100)
         .build())

results = sf.query(query)

def prepare_bulk_data(records, field_mapping=None, default_values=None):
    """
    Prepare record data for bulk operations with validation and transformation.
    
    Parameters:
    - records: List of record dictionaries
    - field_mapping: Dictionary mapping source fields to Salesforce fields
    - default_values: Dictionary of default values for missing fields
    
    Returns:
    list: Cleaned and validated records ready for bulk operations
    """
    
    prepared_records = []
    field_mapping = field_mapping or {}
    default_values = default_values or {}
    
    for record in records:
        # Apply field mapping
        mapped_record = {}
        for source_field, value in record.items():
            target_field = field_mapping.get(source_field, source_field)
            mapped_record[target_field] = value
        
        # Apply default values
        for field, default_value in default_values.items():
            if field not in mapped_record or mapped_record[field] is None:
                mapped_record[field] = default_value
        
        # Clean null values
        cleaned_record = {
            k: v for k, v in mapped_record.items() 
            if v is not None and v != ''
        }
        
        # Validate required fields (example)
        if 'Name' not in cleaned_record:
            cleaned_record['Name'] = 'Unnamed Record'
        
        prepared_records.append(cleaned_record)
    
    return prepared_records

def validate_record_data(records, required_fields=None, field_types=None):
    """
    Validate record data before bulk operations.
    
    Parameters:
    - records: List of record dictionaries
    - required_fields: List of required field names
    - field_types: Dictionary mapping field names to expected types
    
    Returns:
    dict: Validation results with errors and warnings
    """
    
    required_fields = required_fields or []
    field_types = field_types or {}
    
    errors = []
    warnings = []
    
    for i, record in enumerate(records):
        # Check required fields
        for field in required_fields:
            if field not in record or not record[field]:
                errors.append(f"Record {i+1}: Missing required field '{field}'")
        
        # Check field types
        for field, expected_type in field_types.items():
            if field in record and record[field] is not None:
                if not isinstance(record[field], expected_type):
                    warnings.append(
                        f"Record {i+1}: Field '{field}' expected {expected_type.__name__}, "
                        f"got {type(record[field]).__name__}"
                    )
    
    return {
        'valid': len(errors) == 0,
        'errors': errors,
        'warnings': warnings,
        'total_records': len(records)
    }

# Usage
raw_data = [
    {'company_name': 'Test Corp', 'account_type': 'Customer'},
    {'company_name': 'Another Corp', 'account_type': 'Partner'},
    # ... more records
]

prepared_data = prepare_bulk_data(
    raw_data,
    field_mapping={'company_name': 'Name', 'account_type': 'Type'},
    default_values={'Industry': 'Other'}
)

validation = validate_record_data(
    prepared_data,
    required_fields=['Name'],
    field_types={'Name': str, 'Type': str}
)

if validation['valid']:
    results = sf.bulk.Account.insert(prepared_data)
else:
    print("Validation errors:")
    for error in validation['errors']:
        print(f"  {error}")

def retry_failed_records(bulk_operation_func, records, max_retries=3):
    """
    Retry failed records from bulk operations with exponential backoff.
    
    Parameters:
    - bulk_operation_func: Function that performs the bulk operation
    - records: Initial record data
    - max_retries: Maximum number of retry attempts
    
    Returns:
    dict: Final results with success/failure counts
    """
    
    current_records = records.copy()
    all_results = []
    retry_count = 0
    
    while current_records and retry_count < max_retries:
        print(f"Attempt {retry_count + 1}: Processing {len(current_records)} records")
        
        try:
            # Perform bulk operation
            results = bulk_operation_func(current_records)
            all_results.extend(results)
            
            # Identify failed records for retry
            failed_records = []
            for i, result in enumerate(results):
                if not result.get('success', False):
                    failed_records.append(current_records[i])
                    print(f"Failed record: {result.get('error', 'Unknown error')}")
            
            current_records = failed_records
            retry_count += 1
            
            if failed_records:
                # Exponential backoff
                wait_time = 2 ** retry_count
                print(f"Waiting {wait_time} seconds before retry...")
                time.sleep(wait_time)
            
        except Exception as e:
            print(f"Bulk operation failed: {e}")
            retry_count += 1
            if retry_count < max_retries:
                wait_time = 2 ** retry_count
                time.sleep(wait_time)
            else:
                raise
    
    # Calculate final statistics
    successful = sum(1 for r in all_results if r.get('success', False))
    failed = len(all_results) - successful
    
    return {
        'total_processed': len(records),
        'successful': successful,
        'failed': failed,
        'success_rate': (successful / len(records)) * 100 if records else 0,
        'retry_attempts': retry_count,
        'final_results': all_results
    }

# Usage
def perform_bulk_insert(records):
    return sf.bulk.Account.insert(records, include_detailed_results=True)

final_results = retry_failed_records(
    perform_bulk_insert,
    prepared_account_data,
    max_retries=3
)

print(f"Final success rate: {final_results['success_rate']:.1f}%")

def monitor_api_usage(sf, operation_name="Operation"):
    """
    Monitor and report API usage after operations.
    
    Parameters:
    - sf: Salesforce client instance
    - operation_name: Name of operation for logging
    """
    
    if hasattr(sf, 'api_usage') and sf.api_usage:
        usage_info = sf.api_usage
        
        print(f"{operation_name} API Usage:")
        if 'api-usage' in usage_info:
            total_usage = usage_info['api-usage']
            print(f"  Total API calls: {total_usage.used}/{total_usage.total}")
            
        if 'per-app-api-usage' in usage_info:
            for app_usage in usage_info['per-app-api-usage']:
                print(f"  {app_usage.name}: {app_usage.used}/{app_usage.total}")

def benchmark_query_performance(sf, queries, iterations=1):
    """
    Benchmark query performance for optimization.
    
    Parameters:
    - sf: Salesforce client instance
    - queries: List of SOQL queries to benchmark
    - iterations: Number of times to run each query
    
    Returns:
    list: Performance results for each query
    """
    
    results = []
    
    for query in queries:
        times = []
        
        for i in range(iterations):
            start_time = time.time()
            
            try:
                result = sf.query(query)
                end_time = time.time()
                
                execution_time = end_time - start_time
                times.append(execution_time)
                
                print(f"Query {len(results)+1}, Run {i+1}: {execution_time:.3f}s "
                      f"({result['totalSize']} records)")
                
            except Exception as e:
                print(f"Query {len(results)+1}, Run {i+1}: Failed - {e}")
                times.append(None)
        
        # Calculate statistics
        valid_times = [t for t in times if t is not None]
        if valid_times:
            avg_time = sum(valid_times) / len(valid_times)
            min_time = min(valid_times)
            max_time = max(valid_times)
        else:
            avg_time = min_time = max_time = None
        
        results.append({
            'query': query,
            'iterations': iterations,
            'avg_time': avg_time,
            'min_time': min_time,
            'max_time': max_time,
            'success_rate': len(valid_times) / iterations * 100
        })
    
    return results

# Usage
queries_to_test = [
    "SELECT Id, Name FROM Account LIMIT 100",
    "SELECT Id, Name FROM Account WHERE CreatedDate = TODAY",
    "SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account LIMIT 50"
]

performance_results = benchmark_query_performance(sf, queries_to_test, iterations=3)

for result in performance_results:
    print(f"Query: {result['query'][:50]}...")
    print(f"  Average time: {result['avg_time']:.3f}s")
    print(f"  Success rate: {result['success_rate']:.1f}%")