from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import ollama

app = FastAPI()

class QueryRequest(BaseModel):
    natural_language: str
    schema_context: str
    dialect: str = "postgresql"

def generate_sql_prompt(nl_query: str, schema: str, dialect: str) -> str:
    return f"""Given this database schema:
{schema}

Write a {dialect} SQL query for: {nl_query}

Only return the SQL query, no explanations."""

@app.post("/generate-sql")
async def generate_sql(request: QueryRequest):
    try:
        response = ollama.generate(
            model="deepseek-coder:33b",
            prompt=generate_sql_prompt(
                request.natural_language,
                request.schema_context,
                request.dialect
            )
        )
        return {"sql": response["response"].strip()}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

def validate_and_explain(sql: str, db_connection):
    """Validate SQL and return EXPLAIN output"""
    try:
        cursor = db_connection.cursor()
        cursor.execute(f"EXPLAIN {sql}")
        return {"valid": True, "plan": cursor.fetchall()}
    except Exception as e:
        return {"valid": False, "error": str(e)}

class SchemaRegistry:
    def __init__(self, db_connection):
        self.schema = self._fetch_schema(db_connection)

    def _fetch_schema(self, conn):
        cursor = conn.cursor()
        cursor.execute("""
            SELECT table_name, column_name, data_type
            FROM information_schema.columns
            WHERE table_schema = 'public'
        """)
        return self._format_schema(cursor.fetchall())

def build_sql_generation_prompt(nl_query, schema, dialect="postgresql", constraints=[]):
    """Build a detailed prompt for SQL generation."""
    prompt = f"""You are an expert {dialect} database engineer.

SCHEMA CONTEXT:
{schema}

CONSTRAINTS:
{chr(10).join('- ' + c for c in constraints)}

REQUIREMENTS:
- Only return the SQL query, nothing else
- Format with proper indentation
- Use explicit JOINS instead of subqueries where possible
- Add comments for non-obvious logic
- Optimize for readability

USER REQUEST:
{nl_query}

RESPONSE:"""
    return prompt

# Example with constraints
constraints = [
    "Execution time must be under 500ms",
    "Only use columns from the schema provided",
    "Use parameterized queries for security",
    "Avoid expensive operations on large tables"
]

from typing import Optional
from sqlalchemy.orm import Session

class AIQueryGenerator:
    def __init__(self, model_name: str = "deepseek-coder:33b"):
        self.model = model_name
        self.ollama_url = "http://localhost:11434"

    def natural_to_sql(
        self,
        natural_query: str,
        schema: str,
        dialect: str = "postgresql"
    ) -> str:
        """Convert natural language to SQL using local model."""
        import requests

        prompt = f"""Write a {dialect} query for: {natural_query}

Schema:
{schema}

Return only the SQL, no explanation."""

        response = requests.post(
            f"{self.ollama_url}/api/generate",
            json={
                "model": self.model,
                "prompt": prompt,
                "stream": False
            },
            timeout=30
        )

        return response.json()["response"].strip()

    def generate_safe(
        self,
        session: Session,
        natural_query: str,
        schema: str
    ) -> Optional[list]:
        """Generate and safely execute query."""
        try:
            sql = self.natural_to_sql(natural_query, schema)
            # Validate basic SQL structure
            if not self._validate_sql(sql):
                return None
            # Execute with read-only permissions
            result = session.execute(sql)
            return result.fetchall()
        except Exception as e:
            print(f"Query execution failed: {e}")
            return None

    def _validate_sql(self, sql: str) -> bool:
        """Prevent dangerous operations."""
        dangerous_keywords = ['DROP', 'DELETE', 'TRUNCATE', 'ALTER', 'UPDATE']
        upper_sql = sql.upper()
        return not any(kw in upper_sql for kw in dangerous_keywords)

# Usage
generator = AIQueryGenerator()
results = generator.generate_safe(
    session=db_session,
    natural_query="Find top 10 customers by total spending",
    schema=schema_definition
)

from concurrent.futures import ThreadPoolExecutor
import time

class BatchSQLGenerator:
    def __init__(self, model="deepseek-coder:33b", workers=2):
        self.model = model
        self.workers = workers
        self.ollama_url = "http://localhost:11434"

    def generate_batch(self, queries: list[dict]) -> list[dict]:
        """Process multiple queries in parallel."""
        results = []
        start = time.time()

        with ThreadPoolExecutor(max_workers=self.workers) as executor:
            futures = []
            for query_def in queries:
                future = executor.submit(
                    self._generate_one,
                    query_def['natural'],
                    query_def['schema']
                )
                futures.append((query_def['id'], future))

            for query_id, future in futures:
                try:
                    sql = future.result(timeout=30)
                    results.append({
                        'id': query_id,
                        'sql': sql,
                        'status': 'success'
                    })
                except Exception as e:
                    results.append({
                        'id': query_id,
                        'error': str(e),
                        'status': 'failed'
                    })

        elapsed = time.time() - start
        print(f"Generated {len(results)} queries in {elapsed:.1f}s")
        return results

    def _generate_one(self, natural: str, schema: str) -> str:
        import requests
        response = requests.post(
            f"{self.ollama_url}/api/generate",
            json={
                "model": self.model,
                "prompt": f"SQL for: {natural}\nSchema: {schema}"
            }
        )
        return response.json()["response"].strip()

from dataclasses import dataclass
import json
from datetime import datetime

@dataclass
class GenerationMetric:
    timestamp: datetime
    model: str
    query_complexity: str  # simple, medium, complex
    generation_time_ms: float
    execution_successful: bool
    execution_time_ms: float
    generated_vs_human_cost: float

class GenerationMonitor:
    def __init__(self, log_file="sql_generation.jsonl"):
        self.log_file = log_file

    def record_metric(self, metric: GenerationMetric):
        """Log generation metrics for analysis."""
        with open(self.log_file, 'a') as f:
            f.write(json.dumps({
                'timestamp': metric.timestamp.isoformat(),
                'model': metric.model,
                'complexity': metric.query_complexity,
                'gen_time_ms': metric.generation_time_ms,
                'exec_success': metric.execution_successful,
                'exec_time_ms': metric.execution_time_ms,
                'cost_ratio': metric.generated_vs_human_cost
            }) + '\n')

    def generate_report(self):
        """Analyze generation patterns."""
        metrics = []
        with open(self.log_file) as f:
            for line in f:
                metrics.append(json.loads(line))

        if not metrics:
            return None

        # Calculate success rate by complexity
        by_complexity = {}
        for m in metrics:
            c = m['complexity']
            if c not in by_complexity:
                by_complexity[c] = {'success': 0, 'total': 0}
            by_complexity[c]['total'] += 1
            if m['exec_success']:
                by_complexity[c]['success'] += 1

        return {
            'success_rates': {
                c: by_complexity[c]['success'] / by_complexity[c]['total']
                for c in by_complexity
            },
            'avg_gen_time_ms': sum(m['gen_time_ms'] for m in metrics) / len(metrics),
            'total_queries': len(metrics)
        }

class EnsembleGenerator:
    def __init__(self):
        self.models = [
            "deepseek-coder:33b",
            "codellama:34b",
            "qwen2.5-coder:32b"
        ]

    def generate_ensemble(self, nl_query: str, schema: str) -> dict:
        """Generate SQL from multiple models and pick best."""
        candidates = {}

        for model in self.models:
            sql = self._generate_with_model(nl_query, schema, model)
            is_valid = self._validate_syntax(sql)
            cost_score = self._estimate_cost(sql)
            candidates[model] = {
                'sql': sql,
                'valid': is_valid,
                'cost': cost_score
            }

        # Filter to valid queries
        valid = {m: c for m, c in candidates.items() if c['valid']}

        if not valid:
            # Return least invalid option
            return min(candidates.items(), key=lambda x: len(x[1]['sql']))[1]

        # Prefer lowest estimated cost among valid options
        return min(valid.items(), key=lambda x: x[1]['cost'])[1]

    def _estimate_cost(self, sql: str) -> float:
        """Rough estimate of query cost."""
        # Penalize sequential scans, rewards indexes
        score = 0
        if 'WHERE' in sql.upper():
            score -= 1  # Good - has filtering
        if 'INDEX' in sql.upper():
            score -= 2  # Better - uses indexes
        if 'SELECT *' in sql.upper():
            score += 1  # Worse - no column projection
        return score