#!/usr/bin/env python3
"""
Script to fix new playlist records that were incorrectly marked as processed=1
when they should be processed=0 for retry when files are actually uploaded.
"""

import pymysql
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

def get_db_connection():
    """Get connection to local MySQL database."""
    return pymysql.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        user=os.getenv('DB_USER', 'root'),
        password=os.getenv('DB_PASSWORD', ''),
        database=os.getenv('DB_NAME', 'BigPond'),
        port=int(os.getenv('DB_PORT', 3306)),
        autocommit=True
    )

def main():
    """Fix new playlist records that were incorrectly marked as processed."""
    try:
        print("🔗 Connecting to local MySQL database...")
        connection = get_db_connection()
        cursor = connection.cursor()
        # NOTE: We no longer modify the playlists table schema here.
        
        # Find new playlists that were marked processed=1 with S3 download errors
        check_query = """
            SELECT id, playlist_name, playlist_mode, error_message, processed
            FROM direct_integration_uploads 
            WHERE playlist_mode = 'new' 
            AND processed = 1 
            AND error_message LIKE '%Failed to download S3 file%'
        """
        cursor.execute(check_query)
        records = cursor.fetchall()
        
        if not records:
            print("✅ No incorrectly marked 'new' playlist records found.")
            return
            
        print(f"📋 Found {len(records)} incorrectly marked 'new' playlist records:")
        for record in records:
            print(f"   - ID {record[0]}: {record[1]} (mode: {record[2]})")
        
        # Fix them by setting processed=0 and updating error message
        fix_query = """
            UPDATE direct_integration_uploads 
            SET processed = 0,
                error_message = 'New playlist - waiting for file upload',
                step_details = JSON_OBJECT('reason', 'new_playlist_reset_for_retry'),
                updated_at = NOW()
            WHERE playlist_mode = 'new' 
            AND processed = 1 
            AND error_message LIKE '%Failed to download S3 file%'
        """
        cursor.execute(fix_query)
        rows_updated = cursor.rowcount
        
        print(f"✅ Fixed {rows_updated} records - set processed=0 for retry")
        
        # Show final status
        cursor.execute(check_query.replace("processed = 1", "processed = 0"))
        fixed_records = cursor.fetchall()
        
        print(f"📊 Now {len(fixed_records)} 'new' playlist records ready for retry:")
        for record in fixed_records:
            print(f"   - ID {record[0]}: {record[1]} (processed: {record[4]})")
        
        connection.close()
        print("✅ Fix complete! New playlists will now retry when files are uploaded.")
        
    except Exception as e:
        print(f"❌ Error fixing database: {e}")

if __name__ == "__main__":
    main()