#!/usr/bin/env python3
"""
MySQL Connection Cleanup Script

Kills idle MySQL connections to free up space for new connections.
Use this when "Too many connections" error occurs.
"""

import mysql.connector
import sys
import os

# Add parent directory to path to import config
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import config


def kill_idle_connections(idle_seconds=60):
    """
    Kill MySQL connections that have been idle for more than X seconds.

    Args:
        idle_seconds: Kill connections idle for this many seconds (default 60)
    """
    try:
        # Connect with admin privileges
        conn = mysql.connector.connect(
            host=config.DB_HOST,
            port=config.DB_PORT,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            database='information_schema'  # Connect to info schema
        )

        cursor = conn.cursor(dictionary=True)

        # Find idle connections
        query = """
            SELECT
                ID,
                USER,
                HOST,
                DB,
                COMMAND,
                TIME,
                STATE,
                INFO
            FROM information_schema.PROCESSLIST
            WHERE
                COMMAND = 'Sleep'
                AND TIME > %s
                AND USER = %s
                AND ID != CONNECTION_ID()
            ORDER BY TIME DESC
        """

        cursor.execute(query, (idle_seconds, config.DB_USER))
        idle_connections = cursor.fetchall()

        if not idle_connections:
            print(f"No idle connections found (idle > {idle_seconds}s)")
            return 0

        print(f"Found {len(idle_connections)} idle connections:")
        print(f"{'ID':<10} {'TIME':<10} {'DB':<20} {'STATE':<20}")
        print("-" * 60)

        killed_count = 0
        for conn_info in idle_connections:
            print(f"{conn_info['ID']:<10} {conn_info['TIME']:<10} {str(conn_info['DB']):<20} {str(conn_info['STATE']):<20}")

            try:
                kill_cursor = conn.cursor()
                kill_cursor.execute(f"KILL {conn_info['ID']}")
                kill_cursor.close()
                killed_count += 1
            except Exception as e:
                print(f"  Failed to kill connection {conn_info['ID']}: {e}")

        cursor.close()
        conn.close()

        print(f"\nKilled {killed_count} idle connections")
        return killed_count

    except mysql.connector.Error as e:
        print(f"MySQL Error: {e}")
        return 0
    except Exception as e:
        print(f"Error: {e}")
        return 0


def show_connection_stats():
    """Show current MySQL connection statistics"""
    try:
        conn = mysql.connector.connect(
            host=config.DB_HOST,
            port=config.DB_PORT,
            user=config.DB_USER,
            password=config.DB_PASSWORD,
            database='information_schema'
        )

        cursor = conn.cursor(dictionary=True)

        # Get connection count by user
        cursor.execute("""
            SELECT
                USER,
                COUNT(*) as count,
                SUM(CASE WHEN COMMAND = 'Sleep' THEN 1 ELSE 0 END) as sleeping
            FROM information_schema.PROCESSLIST
            GROUP BY USER
            ORDER BY count DESC
        """)

        print("\nConnection Statistics:")
        print(f"{'USER':<20} {'TOTAL':<10} {'SLEEPING':<10}")
        print("-" * 40)

        total = 0
        for row in cursor.fetchall():
            print(f"{row['USER']:<20} {row['count']:<10} {row['sleeping']:<10}")
            total += row['count']

        print("-" * 40)
        print(f"{'TOTAL':<20} {total:<10}")

        # Get max connections setting
        cursor.execute("SHOW VARIABLES LIKE 'max_connections'")
        max_conn = cursor.fetchone()
        print(f"\nMax connections: {max_conn['Value']}")
        print(f"Current usage: {total}/{max_conn['Value']} ({100*total/int(max_conn['Value']):.1f}%)")

        cursor.close()
        conn.close()

    except Exception as e:
        print(f"Error showing stats: {e}")


if __name__ == "__main__":
    print("=" * 60)
    print("MySQL Connection Cleanup Script")
    print("=" * 60)

    # Show current state
    show_connection_stats()

    # Kill idle connections (older than 60 seconds)
    print("\n" + "=" * 60)
    killed = kill_idle_connections(idle_seconds=60)

    if killed > 0:
        print("\n" + "=" * 60)
        print("After cleanup:")
        show_connection_stats()

    print("\n" + "=" * 60)
    print("Done!")