# Solution: Snowflake Secrets Access Issue

## Problem Summary
The Spark TikTok Analytics application shows error: **"Failed to access Apify API token from secrets"** when trying to use `_snowflake.get_generic_secret_string('spark_apify_api_key')`.

## Root Cause Analysis
The issue occurs due to one or more of these problems:

1. **Secret contains placeholder token** - The secret was created with 'YOUR_APIFY_TOKEN_HERE' instead of actual token
2. **External access integration missing secret reference** - Integration doesn't include `SECRETS = ('spark_apify_api_key')`
3. **Application not recreated** - Streamlit app needs to be recreated after secret/integration changes
4. **Permission issues** - Missing grants on secret or integration
5. **Invalid token** - Token is too short, expired, or malformed

## Solution Components

### 1. Enhanced API Module (`api_module.py`)
- **Enhanced `_get_apify_token()` method** with detailed error diagnostics
- **New `diagnose_secrets_access()` method** for comprehensive troubleshooting
- **Better error logging** with specific recommendations for each error type

### 2. Enhanced Test Page (`main_app.py`)
- **Secrets diagnostics section** with visual status indicators
- **One-click diagnostic testing** to identify issues immediately
- **Actionable recommendations** displayed in the UI

### 3. Complete Deployment Fix (`SQL/deploy_secrets_fix.sql`)
- **Comprehensive diagnosis** of current state
- **Automated fixes** for all common issues
- **Post-deployment validation** to ensure success
- **Testing instructions** and troubleshooting guide

### 4. Troubleshooting Documentation (`DOCUMENTATION/troubleshooting_secrets_access.md`)
- **Step-by-step diagnosis** procedures
- **Common issues and solutions** reference
- **Validation checklist** for confirming fixes
- **Emergency fallback procedures**

## Implementation Steps

### Step 1: Update API Module
The enhanced `_get_apify_token()` method now includes:
```python
def _get_apify_token(self) -> Optional[str]:
    try:
        token = _snowflake.get_generic_secret_string('spark_apify_api_key')

        # Validation checks
        if token is None:
            logger.error("Secret returned None - secret may not exist")
        elif token in ['YOUR_APIFY_TOKEN_HERE', 'YOUR_ACTUAL_APIFY_TOKEN_HERE']:
            logger.error("Secret contains placeholder - needs actual token")
        elif len(token) < 20:
            logger.error(f"Token too short ({len(token)} chars) - likely invalid")
        else:
            logger.info("Successfully retrieved valid token")
            return token

    except Exception as e:
        # Enhanced error diagnostics
        logger.error(f"Failed to access secret: {e}")
        # Specific error analysis and recommendations
```

### Step 2: Add Diagnostic Capabilities
New diagnostic method provides comprehensive analysis:
```python
def diagnose_secrets_access(self) -> Dict[str, Any]:
    # Tests secret access, token validity, integration status
    # Returns detailed diagnosis with recommendations
```

### Step 3: Enhanced Test Page
The Test Apify API page now includes:
- **Diagnostic section** with visual status indicators
- **One-click testing** of secrets access
- **Actionable recommendations** for fixing issues

### Step 4: Run Complete Fix
Execute `SQL/deploy_secrets_fix.sql` which:
1. **Diagnoses current state** of secrets and integration
2. **Creates/updates secret** with actual Apify token
3. **Updates external access integration** to reference secret
4. **Uploads enhanced application files** with diagnostics
5. **Recreates Streamlit application** to pick up changes
6. **Validates deployment** with comprehensive tests

## Testing Procedure

### 1. SQL-Level Testing
```sql
-- Verify secret contains actual token (not placeholder)
SELECT
    CASE
        WHEN LENGTH(_snowflake.get_generic_secret_string('spark_apify_api_key')) >= 20
        THEN '✅ SECRET VALID'
        ELSE '❌ SECRET INVALID'
    END as status;
```

### 2. Application-Level Testing
1. Navigate to **Test Apify API** page
2. Click **"🔍 Run Diagnostics"**
3. Verify all status indicators are green:
   - 🟢 Secrets Access: `success`
   - 🟢 Token Validity: `appears_valid`
   - 🟢 Integration: `ready`
4. Click **"🚀 Test API Call"**
5. Verify success message with user information

### 3. Background Processing Testing
1. Go to **Background Processing** page
2. Add a song to refresh queue
3. Start processing - should work without token errors

## Success Indicators

✅ **Secrets Working:**
- Diagnostic shows all green status
- No "Failed to access Apify API token from secrets" errors

✅ **API Integration Working:**
- Test API call returns user information
- Monthly usage statistics displayed
- Background processing executes automatically

✅ **Production Ready:**
- No manual token configuration required
- Secure credential management via Snowflake secrets
- Enhanced error handling and diagnostics

## Files Updated

- `/PRODUCTION/api_module.py` - Enhanced with diagnostics and error handling
- `/PRODUCTION/main_app.py` - Enhanced test page with diagnostic UI
- `/SQL/deploy_secrets_fix.sql` - Complete deployment and fix script
- `/SQL/fix_secrets_access_issue.sql` - Diagnostic and troubleshooting queries
- `/DOCUMENTATION/troubleshooting_secrets_access.md` - Comprehensive troubleshooting guide

## Quick Resolution

1. **Replace token placeholder** in `deploy_secrets_fix.sql` with actual Apify token
2. **Execute the deployment script** in Snowflake SQL worksheet
3. **Test the application** using the enhanced diagnostic tools
4. **Verify success** with the provided validation checklist

The solution provides both immediate fixes and long-term diagnostic capabilities to prevent future issues with Snowflake secrets access.