#!/usr/bin/env python3
"""
Script to fix local database with proper sample data.
"""

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 the local database with proper test data."""
    try:
        print("🔗 Connecting to local MySQL database...")
        connection = get_db_connection()
        cursor = connection.cursor()
        
        # Check current data
        cursor.execute("SELECT COUNT(*) FROM playlists")
        playlist_count = cursor.fetchone()[0]
        cursor.execute("SELECT COUNT(*) FROM application") 
        app_count = cursor.fetchone()[0]
        
        print(f"📊 Current data: {playlist_count} playlists, {app_count} applications")
        
        # Read and execute the fix script
        with open('fix_local_data.sql', 'r') as file:
            sql_content = file.read()
        
        # Split by semicolon and execute each statement
        statements = sql_content.split(';')
        
        for statement in statements:
            statement = statement.strip()
            if statement and not statement.startswith('--'):
                try:
                    cursor.execute(statement)
                    print(f"✅ Executed: {statement[:50]}...")
                except Exception as e:
                    print(f"❌ Error: {e}")
                    print(f"   Statement: {statement[:100]}...")
        
        # Check final counts
        cursor.execute("SELECT COUNT(*) FROM playlists")
        new_playlist_count = cursor.fetchone()[0]
        cursor.execute("SELECT COUNT(*) FROM application")
        new_app_count = cursor.fetchone()[0]
        
        print(f"✅ Final data: {new_playlist_count} playlists, {new_app_count} applications")
        
        # Show sample data
        cursor.execute("SELECT name FROM playlists LIMIT 3")
        playlists = cursor.fetchall()
        print(f"📋 Sample playlists:")
        for playlist in playlists:
            print(f"   - {playlist[0]}")
            
        cursor.execute("SELECT name FROM application LIMIT 3")
        apps = cursor.fetchall()
        print(f"🎯 Sample applications:")
        for app in apps:
            print(f"   - {app[0]}")
        
        connection.close()
        print("✅ Database fix complete!")
        
    except Exception as e:
        print(f"❌ Error fixing database: {e}")

if __name__ == "__main__":
    main()