app.py

import subprocess
import sys
import uuid
import os
import random
from flask import Flask, request, jsonify
from flask_cors import CORS

app = Flask(__name__)
CORS(app)

# --- DATASET GENERATORS ---

def get_small_dataset():
    return """
    -- SMALL DATASET: School Management
    CREATE TABLE Teachers (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        subject VARCHAR(30)
    );
    INSERT INTO Teachers VALUES (1, 'Mr. Smith', 'Math'), (2, 'Ms. Johnson', 'Science'), (3, 'Mrs. Davis', 'History');

    CREATE TABLE Students (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        grade INT,
        teacher_id INT
    );
    INSERT INTO Students VALUES (101, 'Alice', 5, 1), (102, 'Bob', 5, 1), (103, 'Charlie', 6, 2), (104, 'Diana', 6, 2), (105, 'Evan', 5, 3);
    """

def get_medium_dataset():
    return """
    -- MEDIUM DATASET: E-Commerce Store
    CREATE TABLE Products (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        price DECIMAL(10,2),
        stock INT
    );
    INSERT INTO Products VALUES (1, 'Laptop', 999.99, 10), (2, 'Mouse', 25.50, 50), (3, 'Keyboard', 45.00, 30), (4, 'Monitor', 150.00, 15), (5, 'Headphones', 80.00, 20);

    CREATE TABLE Customers (
        id INT PRIMARY KEY,
        name VARCHAR(50),
        city VARCHAR(50)
    );
    INSERT INTO Customers VALUES (1, 'John Doe', 'New York'), (2, 'Jane Smith', 'London'), (3, 'Mike Ross', 'New York'), (4, 'Rachel Zane', 'Toronto');

    CREATE TABLE Orders (
        id INT PRIMARY KEY,
        customer_id INT,
        product_id INT,
        quantity INT,
        date VARCHAR(20)
    );
    INSERT INTO Orders VALUES (1001, 1, 1, 1, '2023-01-01'), (1002, 2, 3, 2, '2023-01-02'), (1003, 1, 2, 1, '2023-01-03'), (1004, 3, 1, 1, '2023-01-04'), (1005, 4, 5, 1, '2023-01-05'), (1006, 2, 2, 5, '2023-01-05');
    """

def get_large_dataset():
    # Generates a synthetic log dataset
    sql = """
    -- LARGE DATASET: Web Server Traffic
    CREATE TABLE Users (
        user_id INT PRIMARY KEY,
        username VARCHAR(50),
        country VARCHAR(50)
    );
    INSERT INTO Users VALUES (1, 'admin', 'USA'), (2, 'guest', 'UK'), (3, 'power_user', 'Canada'), (4, 'bot_01', 'Russia'), (5, 'test_acc', 'India');
    
    CREATE TABLE ServerLogs (
        log_id INT PRIMARY KEY,
        user_id INT,
        endpoint VARCHAR(50),
        status_code INT,
        response_time_ms INT
    );
    """
    
    # Generate 100 rows of log data
    endpoints = ['/home', '/login', '/api/data', '/dashboard', '/logout', '/404']
    statuses = [200, 200, 200, 404, 500, 301]
    
    values = []
    for i in range(1, 101):
        uid = random.randint(1, 5)
        ep = random.choice(endpoints)
        sc = random.choice(statuses)
        rt = random.randint(20, 500)
        values.append(f"({i}, {uid}, '{ep}', {sc}, {rt})")
    
    sql += f"INSERT INTO ServerLogs VALUES {', '.join(values)};"
    return sql

# --- HELPERS ---

def detect_db_type(sql_code):
    """
    Simple heuristic to auto-detect dialect.
    Defaults to SQLite (most permissive for playgrounds).
    """
    code_upper = sql_code.upper()
    
    # MySQL Specifics
    if "AUTO_INCREMENT" in code_upper or "SHOW TABLES" in code_upper or "UNSIGNED" in code_upper:
        return "mysql"
    
    # PostgreSQL Specifics
    if "RETURNING" in code_upper or "SERIAL" in code_upper or "::" in code_upper or "ILIKE" in code_upper:
        return "postgres"
    
    # Default
    return "sqlite"

def run_command(command, input_text=None, timeout=15):
    try:
        run_process = subprocess.run(
            command,
            input=input_text,
            capture_output=True,
            text=True,
            timeout=timeout
        )
        output = run_process.stdout
        if run_process.stderr:
             # Filter out common CLI noise
             errs = [line for line in run_process.stderr.split('\n') if not line.startswith('Note:') and line.strip()]
             if errs:
                output += "\n--- System Messages ---\n" + "\n".join(errs)
        return output
    except subprocess.TimeoutExpired:
        return "Error: Execution timed out (15s limit)."
    except Exception as e:
        return f"System Error: {str(e)}"

# --- ROUTES ---

@app.route('/')
def home():
    return jsonify({"status": "ok", "message": "SQL Compiler API Ready"})

@app.route('/api/run-sql', methods=['POST'])
def run_sql():
    sql_code = request.form.get('code', '')
    dataset_size = request.form.get('dataset', 'small') # small, medium, large
    
    if not sql_code: 
        return jsonify({'output': 'No SQL code provided'}), 400

    # 1. Select Dataset
    init_sql = ""
    if dataset_size == 'small':
        init_sql = get_small_dataset()
    elif dataset_size == 'medium':
        init_sql = get_medium_dataset()
    elif dataset_size == 'large':
        init_sql = get_large_dataset()

    # 2. Auto-detect DB Type
    db_type = detect_db_type(sql_code)
    
    session_id = uuid.uuid4().hex[:8]
    output = ""

    try:
        # --- SQLITE ---
        if db_type == 'sqlite':
            cmd = ["sqlite3", ":memory:"]
            header = ".mode column\n.headers on\n" 
            full_script = header + init_sql + sql_code
            output = run_command(cmd, input_text=full_script)

        # --- POSTGRESQL ---
        elif db_type == 'postgres':
            schema_name = f"sess_{session_id}"
            run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground", "-c", f"CREATE SCHEMA {schema_name};"])
            try:
                full_script = f"SET search_path TO {schema_name};\n{init_sql}\n{sql_code}"
                output = run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground"], input_text=full_script)
            finally:
                run_command(["psql", "-h", "localhost", "-U", "playground", "-d", "playground", "-c", f"DROP SCHEMA {schema_name} CASCADE;"])

        # --- MYSQL ---
        elif db_type == 'mysql':
            db_name = f"sess_{session_id}"
            run_command(["mysql", "-h", "localhost", "-u", "playground", "-e", f"CREATE DATABASE {db_name};"])
            try:
                # MySQL doesn't support "INSERT INTO ... VALUES (...), (...)" syntax exactly same way if mixed with other logic sometimes, 
                # but standard SQL is fine. 
                # Note: We pipe specific settings to make output pretty
                full_script = f"USE {db_name};\n{init_sql}\n{sql_code}"
                output = run_command(["mysql", "-h", "localhost", "-u", "playground", "-t"], input_text=full_script)
            finally:
                run_command(["mysql", "-h", "localhost", "-u", "playground", "-e", f"DROP DATABASE {db_name};"])

    except Exception as e:
        output = f"Server Error: {str(e)}"

    return jsonify({'output': output, 'detected_engine': db_type})

if __name__ == '__main__':
    app.run(host="0.0.0.0", port=7860)