import re
from collections import defaultdict

def parse_slow_log(log_path):
    """Parse MySQL slow query log"""
    queries = []
    current = {}
    
    with open(log_path, 'r') as f:
        for line in f:
            # Parse timestamp
            if line.startswith('# Time:'):
                current['timestamp'] = line.split(':')[1].strip()
            # Parse user info
            elif line.startswith('# User@Host:'):
                current['user'] = line.split('@')[0].replace('# User: ', '').strip()
            # Parse query stats
            elif line.startswith('# Query_time:'):
                match = re.search(r'Query_time:\s*([\d.]+).*Lock_time:\s*([\d.]+).*Rows_sent:\s*(\d+).*Rows_examined:\s*(\d+)', line)
                if match:
                    current['query_time'] = float(match.group(1))
                    current['lock_time'] = float(match.group(2))
                    current['rows_sent'] = int(match.group(3))
                    current['rows_examined'] = int(match.group(4))
            # Parse SQL statement
            elif line.startswith('SET timestamp='):
                continue
            elif line.strip().endswith(';') and 'SELECT' in line.upper():
                current['sql'] = line.strip().rstrip(';')
                queries.append(current.copy())
                current = {}
    
    return queries

def cluster_queries(queries, n_clusters=10):
    """Cluster by SQL pattern"""
    from sklearn.feature_extraction.text import TfidfVectorizer
    from sklearn.cluster import KMeans
    
    # Extract SQL patterns (replace numbers and strings with placeholders)
    patterns = []
    for q in queries:
        pattern = re.sub(r'\b\d+\b', '?', q['sql'])
        pattern = re.sub(r"'[^']*'", '?', pattern)
        pattern = re.sub(r'\s+', ' ', pattern).strip()
        patterns.append(pattern)
    
    # TF-IDF vectorization
    vectorizer = TfidfVectorizer(max_features=500)
    tfidf_matrix = vectorizer.fit_transform(patterns)
    
    # KMeans clustering
    kmeans = KMeans(n_clusters=n_clusters, random_state=42, n_init=10)
    clusters = kmeans.fit_predict(tfidf_matrix)
    
    # Aggregate statistics
    cluster_stats = defaultdict(lambda: {
        'count': 0,
        'total_time': 0,
        'max_time': 0,
        'avg_rows_examined': 0,
        'samples': []
    })
    
    for i, cluster_id in enumerate(clusters):
        stats = cluster_stats[cluster_id]
        stats['count'] += 1
        stats['total_time'] += queries[i]['query_time']
        stats['max_time'] = max(stats['max_time'], queries[i]['query_time'])
        stats['avg_rows_examined'] += queries[i]['rows_examined']
        if len(stats['samples']) < 3:
            stats['samples'].append(queries[i]['sql'])
    
    for stats in cluster_stats.values():
        stats['avg_time'] = stats['total_time'] / stats['count']
        stats['avg_rows_examined'] /= stats['count']
    
    return dict(cluster_stats)

import subprocess
import json

def analyze_query_explain(db_config, sql):
    """Analyze SQL execution plan and return structured results"""
    
    # Execute EXPLAIN
    cmd = [
        'mysql', '-u', db_config['user'],
        '-p' + db_config['password'],
        '-h', db_config['host'],
        '-e', f'EXPLAIN FORMAT=JSON {sql}'
    ]
    
    result = subprocess.run(cmd, capture_output=True, text=True)
    
    if result.returncode != 0:
        return {'error': result.stderr}
    
    # Parse JSON format execution plan
    explain_json = json.loads(result.stdout.strip())
    
    analysis = {
        'tables': [],
        'warnings': [],
        'optimization_suggestions': []
    }
    
    for query_plan in explain_json['EXPLAIN_STMT']['query_block']['select_id']:
        table_info = query_plan.get('table', {})
        
        # Check for full table scan
        if table_info.get('access_type') == 'ALL':
            analysis['warnings'].append(
                f"Full table scan: {table_info.get('table_name', 'unknown')} "
                f"(estimated scan {table_info.get('rows', 'N/A')} rows)"
            )
        
        # Check if index is used
        if not table_info.get('key'):
            analysis['optimization_suggestions'].append(
                f"Table {table_info.get('table_name', 'unknown')} not using index, recommend adding index for WHERE condition"
            )
        
        # Check for filesort
        extra = table_info.get('using', {})
        if extra.get('using_filesort'):
            analysis['warnings'].append(
                f"Using filesort, may cause performance degradation: {table_info.get('table_name', 'unknown')}"
            )
        if extra.get('using_temporary'):
            analysis['warnings'].append(
                f"Using temporary table, may affect performance: {table_info.get('table_name', 'unknown')}"
            )
        
        analysis['tables'].append({
            'name': table_info.get('table_name'),
            'access_type': table_info.get('access_type'),
            'key': table_info.get('key'),
            'rows_estimated': table_info.get('rows'),
            'extra': extra
        })
    
    return analysis

def recommend_indexes(queries, table_schema):
    """Recommend indexes based on query patterns"""
    
    recommendations = {}
    
    for q in queries:
        # Parse WHERE conditions
        where_match = re.search(r'WHERE\s+(.+?)(?:ORDER|GROUP|LIMIT|$)', q['sql'], re.IGNORECASE)
        if not where_match:
            continue
        
        where_clause = where_match.group(1).strip()
        
        # Extract condition columns
        conditions = re.findall(r'(\w+)\s*(?:=|>|<|>=|<=|LIKE|IN)\s*(?:\?|"[^"]*")', where_clause, re.IGNORECASE)
        
        if not conditions:
            continue
        
        # Find involved tables
        from_match = re.search(r'FROM\s+(\w+)', q['sql'], re.IGNORECASE)
        if not from_match:
            continue
        
        table = from_match.group(1)
        
        # Check if suitable index already exists
        if table not in recommendations:
            recommendations[table] = {
                'columns': set(),
                'query_count': 0,
                'avg_time': 0,
                'total_time': 0
            }
        
        recommendations[table]['columns'].update(conditions)
        recommendations[table]['query_count'] += 1
        recommendations[table]['total_time'] += q['query_time']
    
    # Generate index suggestions
    suggestions = []
    for table, info in recommendations.items():
        if info['query_count'] >= 2:  # Only recommend columns appearing multiple times
            avg_time = info['total_time'] / info['query_count']
            columns = sorted(info['columns'])
            
            suggestions.append({
                'table': table,
                'columns': columns,
                'recommendation': f"CREATE INDEX idx_{table}_{'_'.join(columns)} ON {table} ({', '.join(columns)});",
                'reason': f"Appears in {info['query_count']} slow queries, average execution time {avg_time:.2f}s"
            })
    
    # Sort by query frequency
    suggestions.sort(key=lambda x: x['query_count'], reverse=True)
    return suggestions

from openai import OpenAI

client = OpenAI(base_url="http://localhost:11434/v1", api_key="ollama")

def get_llm_index_advice(explain_plan, query, schema_info):
    """Use LLM to analyze execution plan and provide index optimization suggestions"""
    
    prompt = f"""You are a professional database performance expert. Please analyze the following SQL execution plan and provide index optimization suggestions.

SQL Statement:
{query}

Execution Plan:
{json.dumps(explain_plan, indent=2, ensure_ascii=False)}

Table Structure Information:
{schema_info}

Please output in the following format:
1. Problem Diagnosis: Identify performance issues in the execution plan
2. Index Recommendations: Specific index creation statements to recommend
3. Expected Results: Possible performance improvements after optimization
4. Risk Warnings: Write performance degradation that too many indexes may cause"""

    response = client.chat.completions.create(
        model="llama3",
        messages=[{"role": "user", "content": prompt}],
        temperature=0.3
    )
    
    return response.choices[0].message.content

import pymysql
import time
from contextlib import contextmanager

@contextmanager
def db_connection(config):
    """Database connection context manager"""
    conn = pymysql.connect(
        host=config['host'],
        user=config['user'],
        password=config['password'],
        database=config['database']
    )
    try:
        yield conn
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

def execute_optimization(config, suggestions, dry_run=True):
    """Safely execute index optimization"""
    
    results = {
        'success': [],
        'failed': [],
        'warnings': []
    }
    
    with db_connection(config) as conn:
        cursor = conn.cursor()
        
        for suggestion in suggestions:
            sql = suggestion['recommendation']
            table = suggestion['table']
            
            try:
                if dry_run:
                    # Dry-run mode: analyze only, don't execute
                    analyze_sql = f"SHOW INDEX FROM `{table}`"
                    cursor.execute(analyze_sql)
                    existing_indexes = cursor.fetchall()
                    
                    # Check if index already exists
                    index_columns = tuple(suggestion['columns'])
                    already_exists = any(
                        idx[4] == index_columns for idx in existing_indexes
                    )
                    
                    if already_exists:
                        results['warnings'].append(f"Index already exists: {table}({','.join(suggestion['columns'])})")
                    else:
                        results['success'].append({
                            'action': 'DRY_RUN',
                            'table': table,
                            'sql': sql,
                            'reason': suggestion['reason']
                        })
                else:
                    # Actual execution
                    start_time = time.time()
                    cursor.execute(sql)
                    execution_time = time.time() - start_time
                    
                    results['success'].append({
                        'action': 'EXECUTED',
                        'table': table,
                        'sql': sql,
                        'execution_time': execution_time,
                        'reason': suggestion['reason']
                    })
                    
            except Exception as e:
                results['failed'].append({
                    'table': table,
                    'sql': sql,
                    'error': str(e)
                })
    
    return results

def validate_optimization(config, sql_before, sql_after, duration='1h'):
    """Validate optimization effects"""
    
    # Collect slow query metrics before and after optimization
    metrics = {
        'before': collect_slow_query_metrics(config, before=True),
        'after': collect_slow_query_metrics(config, before=False)
    }
    
    # Compare key metrics
    improvements = []
    for key in metrics['before']:
        before_val = metrics['before'][key]
        after_val = metrics['after'].get(key, before_val)
        
        if before_val > 0:
            change_pct = ((after_val - before_val) / before_val) * 100
            improvements.append({
                'metric': key,
                'before': before_val,
                'after': after_val,
                'change_pct': change_pct
            })
    
    return improvements

from prophet import Prophet
import pandas as pd

def learn_performance_baseline(history_data):
    """Learn performance baseline"""
    
    # Aggregate slow query count and average execution time by hour
    df = pd.DataFrame({
        'ds': history_data['timestamps'],
        'y': history_data['query_times']
    })
    
    # Train Prophet model
    model = Prophet(
        yearly_seasonality=True,
        weekly_seasonality=True,
        daily_seasonality=True
    )
    model.fit(df)
    
    # Generate future predictions
    future = model.make_future_dataframe(periods=24, freq='H')
    forecast = model.predict(future)
    
    return model, forecast

def detect_performance_anomaly(model, current_metrics):
    """Detect performance anomalies"""
    
    # Predict normal value range for current time
    prediction = model.predict(
        pd.DataFrame({'ds': [current_metrics['timestamp']]})
    )
    
    upper_bound = prediction['yhat_upper'].values[0]
    lower_bound = prediction['yhat_lower'].values[0]
    predicted = prediction['yhat'].values[0]
    
    current_value = current_metrics['query_time']
    
    # Anomaly detection: outside predicted range
    if current_value > upper_bound:
        severity = 'critical' if current_value > upper_bound * 2 else 'warning'
        return {
            'anomaly': True,
            'severity': severity,
            'current_value': current_value,
            'predicted_value': predicted,
            'upper_bound': upper_bound,
            'deviation_pct': ((current_value - predicted) / predicted) * 100
        }
    
    return {'anomaly': False}

# docker-compose.yml - AI Database Tuning System
services:
  # Slow query log collector
  log-collector:
    image: python:3.11-slim
    volumes:
      - ./queries:/app/queries
      - /var/lib/mysql:/var/lib/mysql:ro
    command: python /app/collector.py
    restart: unless-stopped

  # AI analysis engine
  ai-analyzer:
    image: python:3.11-slim
    volumes:
      - ./analysis:/app/analysis
    environment:
      - DB_HOST=mysql
      - DB_USER=admin
      - DB_PASSWORD=${DB_PASSWORD}
      - OLLAMA_URL=http://ollama:11434
    command: python /app/analyzer.py
    restart: unless-stopped
    depends_on:
      - mysql

  # Database
  mysql:
    image: mysql:8.0
    environment:
      - MYSQL_ROOT_PASSWORD=${DB_PASSWORD}
      - MYSQL_DATABASE=app
    volumes:
      - mysql-data:/var/lib/mysql
    ports:
      - "3306:3306"
    command: --slow-query-log=1 --long-query-time=1

  # Ollama (local LLM)
  ollama:
    image: ollama/ollama:latest
    ports:
      - "11434:11434"
    volumes:
      - ollama-data:/root/.ollama
    restart: unless-stopped

volumes:
  mysql-data:
  ollama-data:

#!/bin/bash
# deploy-ai-db-tuner.sh

set -e

echo "🚀 Deploying AI Database Tuning System..."

# Create config directories
mkdir -p config queries analysis logs

# Generate environment file
cat > .env << EOF
DB_PASSWORD=$(openssl rand -base64 32)
DB_HOST=mysql
OLLAMA_URL=http://ollama:11434
EOF

# Pull and start services
docker compose up -d

# Wait for MySQL to be ready
echo "⏳ Waiting for MySQL to start..."
until docker exec mysql mysqladmin ping -h localhost --silent; do
  sleep 2
done

# Initialize database
echo "📦 Initializing database..."
docker exec mysql mysql -u root -p${DB_PASSWORD} -e "
  CREATE DATABASE IF NOT EXISTS app;
  CREATE USER IF NOT EXISTS 'app_user'@'%' IDENTIFIED BY '${DB_PASSWORD}';
  GRANT ALL PRIVILEGES ON app.* TO 'app_user'@'%';
  FLUSH PRIVILEGES;
"

# Start Ollama model
echo "🤖 Starting Ollama model..."
docker exec ollama ollama pull llama3

echo "✅ Deployment complete!"
echo "   MySQL: localhost:3306"
echo "   Ollama: localhost:11434"
echo "   Log directory: ./logs/"