import logging
import json
import pymysql
from pymysql import Error
from datetime import datetime
import os
try:
    # Import the config module itself so we always see the latest MYSQL_CONFIG
    # after credential refresh (no stale copy of the dict).
    from . import config as _mysql_config
except ImportError:
    import config as _mysql_config

# Set up logging to a file
logging.basicConfig(filename='mysql_debug.log', level=logging.INFO, format='%(asctime)s %(levelname)s: %(message)s')

# Log effective environment and connection target for debugging
try:
    logging.info(
        f"[MySQL] Effective ENV={os.getenv('ENV')} "
        f"target host={_mysql_config.MYSQL_CONFIG.get('host')} "
        f"db={_mysql_config.MYSQL_CONFIG.get('database')} "
        f"port={_mysql_config.MYSQL_CONFIG.get('port')}"
    )
except Exception:
    # Avoid logging problems impacting startup
    pass

# MySQL Configuration is imported from config.py

def get_mysql_connection():
    """Get a connection to the MySQL database.

    Uses the live `MYSQL_CONFIG` from `mysql.config` so that credential
    refreshes (e.g. password rotation via `refresh_mysql_config`) take
    effect without restarting the process.
    """
    try:
        connection = pymysql.connect(**_mysql_config.MYSQL_CONFIG)
        return connection
    except Error as e:
        logging.error(f"Error connecting to MySQL: {e}")
        raise



def save_playlist_to_mysql(name, created_date=None, application_id=None, user_id=None, company_id=None, show: int | None = None):
    """Save a playlist to MySQL database.

    Optionally stores application_id, user_id and company_id with the playlist.
    """
    if created_date is None:
        created_date = datetime.utcnow()
    elif isinstance(created_date, str):
        # Convert ISO string to datetime if needed
        created_date = datetime.fromisoformat(created_date.replace('Z', '+00:00'))
    
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()

        # Build dynamic insert based on provided optional fields
        columns = ["name", "created_date"]
        placeholders = ["%s", "%s"]
        values = [name, created_date]

        # Keep a consistent column order when present
        if application_id is not None:
            columns.insert(1, "application_id")
            placeholders.insert(1, "%s")
            values.insert(1, application_id)
        if user_id is not None:
            # Place after application_id if present
            insert_index = 2 if application_id is not None else 1
            columns.insert(insert_index, "user_id")
            placeholders.insert(insert_index, "%s")
            values.insert(insert_index, user_id)
        if company_id is not None:
            # Place after user_id if present
            if application_id is not None and user_id is not None:
                insert_index = 3
            elif application_id is not None or user_id is not None:
                insert_index = 2
            else:
                insert_index = 1
            columns.insert(insert_index, "company_id")
            placeholders.insert(insert_index, "%s")
            values.insert(insert_index, company_id)
        # show flag (0/1). If provided, always append as last column to keep ordering predictable
        if show is not None:
            # Use backticks because SHOW is a reserved keyword in MySQL
            columns.append("`show`")
            placeholders.append("%s")
            # Coerce to int 0/1 for MySQL TINYINT
            try:
                values.append(1 if int(show) != 0 else 0)
            except Exception:
                values.append(0 if not show else 1)

        query = f"INSERT INTO playlists ({', '.join(columns)}) VALUES ({', '.join(placeholders)})"
        
        logging.info(f"Attempting to insert playlist: name={name}, created_date={created_date}")
        
        cursor.execute(query, values)
        playlist_id = cursor.lastrowid
        
        # CRITICAL: Commit the transaction
        connection.commit()
        
        # Return data in similar format to Supabase response
        result_data = {
            'data': [{
                'id': playlist_id,
                'name': name,
                'application_id': application_id,
                'user_id': user_id,
                'company_id': company_id,
                'show': (None if show is None else (1 if int(show) != 0 else 0)),
                'created_date': created_date.isoformat()
            }],
            'error': None
        }
        
        logging.info(f"MySQL insert successful: {result_data}")
        return result_data
        
    except Error as e:
        logging.error(f"Exception during MySQL playlist insert: {e}")
        return {'data': None, 'error': str(e)}
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def save_action_to_mysql(playlist_id, action_type, x, y, key_name, timestamp, playlist_name=None, variable_name=None):
    """Save an action (click, keyboard, or trigger) to MySQL database.

    Supports optional variable_name for variable-target clicks. Ensure the actions
    table has a `variable_name` column (run add_variable_name_column.sql once).
    """
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        # Insert with variable_name when provided; fall back to legacy insert otherwise
        if variable_name is not None:
            query = (
                "INSERT INTO actions (playlist_id, action_type, x, y, `key`, timestamp, playlist_name, variable_name) "
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
            )
            values = (playlist_id, action_type, x, y, key_name, timestamp, playlist_name, variable_name)
        else:
            query = (
                "INSERT INTO actions (playlist_id, action_type, x, y, `key`, timestamp, playlist_name) "
                "VALUES (%s, %s, %s, %s, %s, %s, %s)"
            )
            values = (playlist_id, action_type, x, y, key_name, timestamp, playlist_name)
        
        logging.info(f"Attempting to insert action: playlist_id={playlist_id}, action_type={action_type}")
        
        try:
            cursor.execute(query, values)
        except Exception as e:
            logging.error(f"[MySQL] Error inserting action with variable_name={variable_name}: {e}")
            raise
        action_id = cursor.lastrowid
        
        # CRITICAL: Commit the transaction
        connection.commit()
        
        # Return data in similar format to Supabase response
        result_data = {
            'data': [{
                'id': action_id,
                'playlist_id': playlist_id,
                'action_type': action_type,
                'x': x,
                'y': y,
                'key': key_name,
                'timestamp': timestamp,
                'playlist_name': playlist_name,
                'variable_name': variable_name
            }],
            'error': None
        }
        
        logging.info(f"MySQL action insert successful: {result_data}")
        return result_data
        
    except Error as e:
        logging.error(f"Exception during MySQL action insert: {e}")
        return {'data': None, 'error': str(e)}
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_playlist_names_from_mysql():
    """Fetch all playlist names from MySQL, ordered by created_date descending."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = "SELECT name FROM playlists ORDER BY created_date DESC"
        cursor.execute(query)
        
        rows = cursor.fetchall()
        playlist_names = [row[0] for row in rows]
        
        logging.info(f"Fetched {len(playlist_names)} playlist names from MySQL")
        return playlist_names
        
    except Error as e:
        logging.error(f"Exception during MySQL fetch playlists: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_playlist_names_with_app_details():
    """Fetch all playlists with their application details including start_point."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        query = """
            SELECT p.name, p.application_id, a.start_point, a.platform, a.name as app_name 
            FROM playlists p 
            LEFT JOIN application a ON p.application_id = a.id 
            ORDER BY p.created_date DESC
        """
        cursor.execute(query)
        
        rows = cursor.fetchall()
        logging.info(f"Fetched {len(rows)} playlists with app details from MySQL")
        return rows
        
    except Error as e:
        logging.error(f"Exception during MySQL fetch playlists with app details: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def playlist_name_exists_in_mysql(name):
    """Return True if a playlist with the given name exists in MySQL, else False."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = "SELECT id FROM playlists WHERE name = %s LIMIT 1"
        cursor.execute(query, (name,))
        
        result = cursor.fetchone()
        exists = result is not None
        
        logging.info(f"Playlist name '{name}' exists: {exists}")
        return exists
        
    except Error as e:
        logging.error(f"Exception during MySQL playlist name check: {e}")
        return False
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def save_screenshot_analysis_to_mysql(playlist_name, screenshot_path, openai_result):
    """Save screenshot analysis result to MySQL database."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = """
            INSERT INTO screenshot_analysis (playlist_name, screenshot_path, openai_result) 
            VALUES (%s, %s, %s)
        """
        values = (playlist_name, screenshot_path, openai_result)
        
        logging.info(f"Attempting to insert screenshot analysis for playlist: {playlist_name}")
        
        cursor.execute(query, values)
        analysis_id = cursor.lastrowid
        
        # Return data in similar format to Supabase response
        result_data = {
            'data': [{
                'id': analysis_id,
                'playlist_name': playlist_name,
                'screenshot_path': screenshot_path,
                'openai_result': openai_result
            }],
            'error': None
        }
        
        logging.info(f"MySQL screenshot analysis insert successful")
        return result_data
        
    except Error as e:
        logging.error(f"Exception during MySQL screenshot analysis insert: {e}")
        return {'data': None, 'error': str(e)}
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()

def get_next_auto_extractor_job():
    """Fetch the first unprocessed auto_extractor job from MySQL."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)  # Return results as dictionaries
        
        query = """
            SELECT * FROM auto_extractor 
            WHERE processed = 0 
            ORDER BY created_at ASC 
            LIMIT 1
        """
        cursor.execute(query)
        
        row = cursor.fetchone()
        
        if row:
            logging.info(f"Found unprocessed auto_extractor job: ID {row['id']}")
            return row
        else:
            # logging.info("No unprocessed auto_extractor jobs found")
            return None
        
    except Error as e:
        logging.error(f"Exception during MySQL auto_extractor fetch: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def mark_auto_extractor_job_processed(job_id):
    """Mark the given auto_extractor job as processed in MySQL."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = "UPDATE auto_extractor SET processed = 1 WHERE id = %s"
        cursor.execute(query, (job_id,))
        
        # CRITICAL: Commit the transaction
        connection.commit()
        
        rows_affected = cursor.rowcount
        
        if rows_affected > 0:
            logging.info(f"Marked auto_extractor job {job_id} as processed")
            result_data = {'data': [{'id': job_id, 'processed': 1}], 'error': None}
        else:
            logging.warning(f"No auto_extractor job found with ID {job_id}")
            result_data = {'data': None, 'error': f'Job {job_id} not found'}
        
        return result_data
        
    except Error as e:
        logging.error(f"Exception during MySQL auto_extractor update: {e}")
        return {'data': None, 'error': str(e)}
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_playlist_actions(playlist_id):
    """Get all actions for a specific playlist, ordered by timestamp."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        query = """
            SELECT * FROM actions 
            WHERE playlist_id = %s 
            ORDER BY timestamp ASC
        """
        cursor.execute(query, (playlist_id,))
        
        actions = cursor.fetchall()
        logging.info(f"Fetched {len(actions)} actions for playlist {playlist_id}")
        return actions
        
    except Error as e:
        logging.error(f"Exception during MySQL playlist actions fetch: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_playlist_id_by_name(name):
    """Get playlist ID by name."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = "SELECT id FROM playlists WHERE name = %s LIMIT 1"
        cursor.execute(query, (name,))
        
        result = cursor.fetchone()
        if result:
            return result[0]
        return None
        
    except Error as e:
        logging.error(f"Exception during MySQL playlist ID fetch: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_application_by_id(application_id: int):
    """Fetch a single application by id as a dict including instance_id for heartbeats."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        # Include instance_id so the app can send heartbeats to instance_usage
        query = "SELECT id, platform, name, start_point, instance_id FROM application WHERE id = %s LIMIT 1"
        logging.info(f"[MySQL] Fetching application with ID {application_id}")
        cursor.execute(query, (application_id,))
        result = cursor.fetchone()
        if result:
            logging.info(f"[MySQL] Found application: {result}")
        else:
            logging.warning(f"[MySQL] No application found with ID {application_id}")
        return result
    except Error as e:
        logging.error(f"[MySQL] Exception during application fetch for ID {application_id}: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_playlist_by_id(playlist_id: int):
    """Fetch a single playlist by id as a dict with id, application_id, name, created_date."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        cursor.execute(
            "SELECT id, application_id, name, created_date FROM playlists WHERE id = %s LIMIT 1",
            (playlist_id,),
        )
        return cursor.fetchone()
    except Error as e:
        logging.error(f"Exception during MySQL playlist fetch: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass
def get_unprocessed_direct_uploads(limit: int = 10):
    """Return unprocessed direct integration uploads from shared table as list of dicts."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        query = (
            "SELECT id, filename, original_filename, s3_bucket, s3_key, processed, "
            "application_id, playlist_id, playlist_mode, playlist_name, error_message, created_at, updated_at, user_id, is_admin, company_id "
            "FROM direct_integration_uploads "
            "WHERE processed = 0 "
            "ORDER BY id DESC "
            "LIMIT %s"
        )
        cursor.execute(query, (limit,))
        rows = cursor.fetchall()
        try:
            ids = [row.get("id") for row in rows] if rows else []
            logging.info(
                f"[DirectIntegration] Unprocessed rows fetched (count={len(rows)}): {ids}"
            )
        except Exception:
            # Avoid logging failures impacting flow
            pass
        return rows or []
    except Error as e:
        logging.error(f"Error fetching unprocessed uploads: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_latest_unprocessed_direct_upload():
    """Return the latest unprocessed direct integration upload (processed = 0).
    
    Simply gets the most recent record with processed = 0.
    """
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        query = (
            "SELECT di.* "
            "FROM direct_integration_uploads di "
            "WHERE di.processed = 0 "  # Just get unprocessed records
            "ORDER BY di.id DESC "  # Latest record by ID
            "LIMIT 1"
        )
        cursor.execute(query)
        row = cursor.fetchone()
        if row:
            has_s3 = row.get('s3_bucket') and row.get('s3_key')
            created_at = row.get('created_at')
            age = ""
            if created_at:
                try:
                    from datetime import datetime
                    now = datetime.utcnow()
                    delta = now - created_at
                    age = f" (waiting {delta.total_seconds():.0f}s)"
                except Exception:
                    pass
                    
            logging.info(
                f"[DirectIntegration] Processing upload id={row.get('id')} mode={row.get('playlist_mode')} "
                f"name={row.get('playlist_name')} s3={'yes' if has_s3 else 'local'}{age}"
            )
        # else:
            # logging.info("[DirectIntegration] No unprocessed uploads available")
        return row
    except Error as e:
        logging.error(f"Error fetching latest unprocessed upload: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_direct_upload_by_id(upload_id: int):
    """Fetch a direct integration upload row by id as a dict (includes processed flag)."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        cursor.execute(
            "SELECT id, processed, filename, original_filename, playlist_mode, playlist_name, application_id, playlist_id, error_message, created_at, updated_at, user_id, is_admin, company_id, s3_bucket, s3_key, extracted_data "
            "FROM direct_integration_uploads WHERE id = %s LIMIT 1",
            (upload_id,),
        )
        return cursor.fetchone()
    except Error as e:
        logging.error(f"Error fetching upload by id {upload_id}: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def set_direct_upload_status(upload_id, status, error_message=None):
    """Set the processing status for a direct integration upload.
    
    Status codes:
    - 0: Unprocessed
    - 1: Completed successfully
    - 2: Currently processing 
    - 3: Error
    - 4: Waiting for user interaction
    """
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = """
            UPDATE direct_integration_uploads 
            SET processed = %s, 
                error_message = %s,
                updated_at = NOW() 
            WHERE id = %s
        """
        cursor.execute(query, (status, error_message, upload_id))
        connection.commit()
        
        status_name = {0: 'unprocessed', 1: 'completed', 2: 'processing', 3: 'error'}.get(status, f'status_{status}')
        logging.info(f"[DirectIntegration] Updated upload id={upload_id} to status={status} ({status_name})")
        
        return {'success': True}
        
    except Error as e:
        logging.error(f"Failed to set status for upload {upload_id}: {e}")
        return {'success': False, 'error': str(e)}
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def mark_direct_upload_processed(
    upload_id: int,
    *,
    extracted_data: str | None = None,
    error_message: str | None = None,
    processed: int = 1,
):
    """Update a direct upload row with extracted data and mark processed."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        query = (
            "UPDATE direct_integration_uploads "
            "SET processed = %s, "
            "    extracted_data = %s, "
            "    error_message = %s, "
            "    updated_at = NOW() "
            "WHERE id = %s"
        )
        cursor.execute(query, (processed, extracted_data, error_message, upload_id))
        connection.commit()
        try:
            logging.info(
                f"[DirectIntegration] Updated upload id={upload_id} set processed={processed}, has_data={'yes' if bool(extracted_data) else 'no'}, error={'yes' if bool(error_message) else 'no'}"
            )
        except Exception:
            pass
        return True
    except Error as e:
        logging.error(f"Error updating upload {upload_id}: {e}")
        return False
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def mark_playlist_processed(playlist_id: int) -> bool:
    """Deprecated: No-op. We don't track processed status on playlists anymore.

    This remains to avoid breaking older code paths; it logs and returns True.
    """
    try:
        logging.info(
            f"[Deprecated] mark_playlist_processed({playlist_id}) called; skipping (processed is tracked on direct_integration_uploads only)"
        )
    except Exception:
        pass
    return True

def log_direct_integration_table_snapshot(limit: int = 10) -> None:
    """Log a snapshot of the latest rows in direct_integration_uploads for debugging.

    Includes both processed and unprocessed rows, ordered by most recent creation.
    """
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        snapshot_query = (
            "SELECT id, filename, original_filename, processed, application_id, "
            "playlist_id, playlist_mode, playlist_name, created_at, updated_at, user_id, is_admin, company_id "
            "FROM direct_integration_uploads "
            "ORDER BY created_at DESC "
            "LIMIT %s"
        )
        cursor.execute(snapshot_query, (limit,))
        rows = cursor.fetchall() or []
        logging.info(f"[DirectIntegration] Latest {len(rows)} rows in direct_integration_uploads (most recent first):")
        for row in rows:
            try:
                logging.info(
                    "[DirectIntegration] row id=%s processed=%s filename=%s original=%s app_id=%s playlist_id=%s mode=%s name=%s created_at=%s",
                    row.get("id"),
                    row.get("processed"),
                    row.get("filename"),
                    row.get("original_filename"),
                    row.get("application_id"),
                    row.get("playlist_id"),
                    row.get("playlist_mode"),
                    row.get("playlist_name"),
                    row.get("created_at"),
                )
            except Exception:
                # Ensure logging formatting problems do not break flow
                logging.info(f"[DirectIntegration] row: {row}")
    except Error as e:
        logging.error(f"Error logging table snapshot: {e}")
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def save_subplaylist_action(playlist_id, subplaylist_id, timestamp=None, playlist_name=None):
    """Save a subplaylist action to the database."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        if timestamp is None:
            import time
            timestamp = time.time()
        
        query = """
            INSERT INTO actions (playlist_id, action_type, subplaylist_id, timestamp, playlist_name)
            VALUES (%s, 'subplaylist', %s, %s, %s)
        """
        cursor.execute(query, (playlist_id, subplaylist_id, timestamp, playlist_name))
        connection.commit()
        
        action_id = cursor.lastrowid
        logging.info(f"Saved subplaylist action: playlist_id={playlist_id}, subplaylist_id={subplaylist_id}")
        return action_id
        
    except Error as e:
        logging.error(f"Exception during MySQL subplaylist action insert: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_subplaylist_actions(playlist_id):
    """Get all subplaylist actions for a specific playlist."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        query = """
            SELECT a.*, p.name as subplaylist_name 
            FROM actions a 
            LEFT JOIN playlists p ON a.subplaylist_id = p.id 
            WHERE a.playlist_id = %s AND a.action_type = 'subplaylist'
            ORDER BY a.timestamp ASC
        """
        cursor.execute(query, (playlist_id,))
        
        actions = cursor.fetchall()
        logging.info(f"Fetched {len(actions)} subplaylist actions for playlist {playlist_id}")
        return actions
        
    except Error as e:
        logging.error(f"Exception during MySQL subplaylist actions fetch: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def create_subplaylist_queue_entry(playlist_id, subplaylist_id, playlist_name, application_id=None, user_id=None, company_id=None):
    """Create a queue entry for subplaylist processing in direct_integration_uploads table."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = """
            INSERT INTO direct_integration_uploads 
            (playlist_id, subplaylist_id, playlist_name, playlist_mode, processed, application_id, user_id, company_id, created_at)
            VALUES (%s, %s, %s, 'subplaylist', 0, %s, %s, %s, NOW())
        """
        cursor.execute(query, (playlist_id, subplaylist_id, playlist_name, application_id, user_id, company_id))
        connection.commit()
        
        upload_id = cursor.lastrowid
        logging.info(f"Created subplaylist queue entry: upload_id={upload_id}, playlist_id={playlist_id}, subplaylist_id={subplaylist_id}")
        return upload_id
        
    except Error as e:
        logging.error(f"Exception during MySQL subplaylist queue entry creation: {e}")
        return None
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def get_subplaylist_queue_entries(limit=10):
    """Get unprocessed subplaylist queue entries."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor(pymysql.cursors.DictCursor)
        
        query = """
            SELECT diu.*, p.name as subplaylist_name
            FROM direct_integration_uploads diu
            LEFT JOIN playlists p ON diu.subplaylist_id = p.id
            WHERE diu.processed = 0 AND diu.playlist_mode = 'subplaylist'
            ORDER BY diu.created_at ASC
            LIMIT %s
        """
        cursor.execute(query, (limit,))
        
        entries = cursor.fetchall()
        # logging.info(f"Fetched {len(entries)} subplaylist queue entries")
        return entries
        
    except Error as e:
        logging.error(f"Exception during MySQL subplaylist queue fetch: {e}")
        return []
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

def mark_subplaylist_queue_processed(upload_id, processed=1, error_message=None):
    """Mark a subplaylist queue entry as processed."""
    connection = None
    try:
        connection = get_mysql_connection()
        cursor = connection.cursor()
        
        query = """
            UPDATE direct_integration_uploads 
            SET processed = %s, error_message = %s, updated_at = NOW()
            WHERE id = %s AND playlist_mode = 'subplaylist'
        """
        cursor.execute(query, (processed, error_message, upload_id))
        connection.commit()
        
        rows_affected = cursor.rowcount
        if rows_affected > 0:
            logging.info(f"Marked subplaylist queue entry {upload_id} as processed")
            return True
        else:
            logging.warning(f"No subplaylist queue entry found with ID {upload_id}")
            return False
        
    except Error as e:
        logging.error(f"Exception during MySQL subplaylist queue update: {e}")
        return False
    finally:
        if 'cursor' in locals() and cursor:
            try:
                cursor.close()
            except Exception:
                pass
        if connection:
            try:
                connection.close()
            except Exception:
                pass

# Initialize tables when module is imported
if __name__ == "__main__":    print("MySQL client initialized successfully")