#!/usr/bin/env python3
"""
Script to transfer existing recorded actions from SQLite to MySQL 
for playlists that were saved before the action transfer fix.
"""

import pymysql
import sqlite3
import os
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

def get_mysql_connection():
    """Get connection to 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 get_sqlite_connection():
    """Get connection to SQLite database."""
    return sqlite3.connect('clicks.db')

def main():
    """Transfer existing actions from SQLite to MySQL."""
    try:
        print("🔗 Connecting to databases...")
        mysql_conn = get_mysql_connection()
        mysql_cursor = mysql_conn.cursor()
        
        sqlite_conn = get_sqlite_connection()
        sqlite_cursor = sqlite_conn.cursor()
        
        # Find playlists that exist in both MySQL and SQLite
        mysql_cursor.execute("SELECT id, name FROM playlists ORDER BY id")
        mysql_playlists = mysql_cursor.fetchall()
        
        for mysql_id, playlist_name in mysql_playlists:
            print(f"\n📋 Processing playlist: '{playlist_name}' (MySQL ID: {mysql_id})")
            
            # Check if SQLite has this playlist
            sqlite_cursor.execute("SELECT id FROM Playlists WHERE name = ?", (playlist_name,))
            sqlite_row = sqlite_cursor.fetchone()
            
            if not sqlite_row:
                print(f"   ⚠️  No SQLite playlist found for '{playlist_name}'")
                continue
                
            sqlite_playlist_id = sqlite_row[0]
            
            # Check if MySQL already has actions for this playlist
            mysql_cursor.execute("SELECT COUNT(*) FROM actions WHERE playlist_id = %s", (mysql_id,))
            existing_actions = mysql_cursor.fetchone()[0]
            
            if existing_actions > 0:
                print(f"   ℹ️  Already has {existing_actions} actions in MySQL - skipping")
                continue
            
            # Get clicks from SQLite
            sqlite_cursor.execute(
                "SELECT x, y, timestamp FROM Clicks WHERE playlist_id = ? ORDER BY timestamp ASC",
                (sqlite_playlist_id,)
            )
            clicks = sqlite_cursor.fetchall()
            
            # Get keyboard events from SQLite
            sqlite_cursor.execute(
                "SELECT key, event_type, timestamp FROM KeyboardEvents WHERE playlist_id = ? ORDER BY timestamp ASC",
                (sqlite_playlist_id,)
            )
            keys = sqlite_cursor.fetchall()
            
            print(f"   📊 Found {len(clicks)} clicks and {len(keys)} key events in SQLite")
            
            if not clicks and not keys:
                print(f"   ⚠️  No actions to transfer")
                continue
            
            # Transfer clicks to MySQL
            for x, y, timestamp in clicks:
                mysql_cursor.execute(
                    "INSERT INTO actions (playlist_id, action_type, x, y, timestamp, playlist_name) VALUES (%s, %s, %s, %s, %s, %s)",
                    (mysql_id, 'click', x, y, timestamp, playlist_name)
                )
            
            # Transfer keyboard events to MySQL
            for key, event_type, timestamp in keys:
                action_type = 'key_press' if event_type == 'press' else 'key_release'
                mysql_cursor.execute(
                    "INSERT INTO actions (playlist_id, action_type, key_name, timestamp, playlist_name) VALUES (%s, %s, %s, %s, %s)",
                    (mysql_id, action_type, key, timestamp, playlist_name)
                )
            
            total_transferred = len(clicks) + len(keys)
            print(f"   ✅ Transferred {total_transferred} actions to MySQL")
        
        # Verify final state
        print("\n📊 Final MySQL actions count by playlist:")
        mysql_cursor.execute("""
            SELECT p.name, COUNT(a.id) as action_count 
            FROM playlists p 
            LEFT JOIN actions a ON p.id = a.playlist_id 
            GROUP BY p.id, p.name 
            ORDER BY p.id
        """)
        
        for playlist_name, action_count in mysql_cursor.fetchall():
            print(f"   {playlist_name}: {action_count} actions")
        
        mysql_conn.close()
        sqlite_conn.close()
        print("\n✅ Action transfer complete! Play button should now work with MySQL data.")
        
    except Exception as e:
        print(f"❌ Error transferring actions: {e}")

if __name__ == "__main__":
    main()