from flask import Flask, request, jsonify, send_from_directory, render_template
import os
import sqlite3
import subprocess
import threading
import queue
from flask import Response
from flask_cors import CORS
import json
import re
from datetime import datetime
from flask_socketio import SocketIO, emit


event_queue = queue.Queue()

app = Flask(__name__, static_folder='../frontend/app/dist', static_url_path='/')

socketio = SocketIO(app, cors_allowed_origins="*")

__all__ = ["app", "socketio"]


from codeagent.src.codeagent.main import CodeAgentFlow

CORS(app)

SQL_FILES_DIR = 'sql_files'
os.makedirs(SQL_FILES_DIR, exist_ok=True)
DB_FILE = 'app.db'

@app.route('/')
def index():
    return send_from_directory(app.static_folder, 'index.html')

@app.route('/files', methods=['GET'])
def get_files():
    files = os.listdir(SQL_FILES_DIR)
    return jsonify([{'name': f} for f in files])

@app.route('/files', methods=['POST'])
def create_file():
    data = request.json
    filename = data['filename']
    content = data.get('content', '')
    filepath = os.path.join(SQL_FILES_DIR, filename)
    with open(filepath, 'w') as f:
        f.write(content)
    return jsonify({"status": "created", "file": filename})

@app.route('/files/<filename>', methods=['PUT'])
def update_file(filename):
    content = request.json.get('content', '')
    filepath = os.path.join(SQL_FILES_DIR, filename)
    if not os.path.exists(filepath):
        return jsonify({"error": "file not found"}), 404
    with open(filepath, 'w') as f:
        f.write(content)
    return jsonify({"status": "updated", "file": filename})

@app.route('/files/<filename>', methods=['GET'])
def get_file_content(filename):
    filepath = os.path.join(SQL_FILES_DIR, filename)
    if os.path.exists(filepath):
        with open(filepath, 'r') as f:
            content = f.read()
        return jsonify({"filename": filename, "content": content})
    return jsonify({"error": "file not found"}), 404

@app.route('/files/<filename>', methods=['DELETE'])
def delete_file(filename):
    filepath = os.path.join(SQL_FILES_DIR, filename)
    if os.path.exists(filepath):
        os.remove(filepath)
        return jsonify({"status": "deleted", "file": filename})
    return jsonify({"error": "file not found"}), 404


def get_db_connection():
    conn = sqlite3.connect(os.path.join(SQL_FILES_DIR,DB_FILE))
    conn.row_factory = sqlite3.Row
    return conn

@app.route('/tables', methods=['GET'])
def get_tables():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = [row['name'] for row in cursor.fetchall()]
    conn.close()
    return jsonify(tables)

recent_result = None
@app.route('/query', methods=['POST'])
def run_query():
    query = request.json.get('query', '')
    print(query)
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        query_lc = str(query).strip().lower()

        if ";" in query.strip() and not query_lc.startswith("select") and not query_lc.startswith("pragma"):
            # Multiple statements → executescript
            cursor.executescript(query)
            conn.commit()
            result = {"rows_affected": cursor.rowcount}
        else:
            cursor.execute(query)

            if query_lc.startswith("pragma"):
                rows = cursor.fetchall()
                result = [dict(row) for row in rows] if rows else []
            elif query_lc.startswith("select"):
                result = [dict(row) for row in cursor.fetchall()]
            else:
                conn.commit()
                result = {"rows_affected": cursor.rowcount}

        conn.close()
        global recent_result
        recent_result = {"query": query, "result": result}

        return jsonify(result)
    except Exception as e:
        conn.close()
        return jsonify({"error": str(e)}), 400

@app.route('/recent_query', methods=['GET'])
def recent_query():
    return jsonify(recent_result)

@app.route('/execute_file', methods=['POST'])
def execute_file():
    """
    Execute a .sql file located in SQL_FILES_DIR using sqlite3.executescript.
    Accepts: { "filename": "schema.sql" }
    Returns: { "status": "success", "file": "...", "started_at": "...", "finished_at": "..."}
             or { "error": "message" }
    """
    data = request.json or {}
    filename = data.get('filename')
    if not filename:
        return jsonify({"error": "filename is required"}), 400

    filepath = os.path.join(SQL_FILES_DIR, filename)
    if not os.path.exists(filepath):
        return jsonify({"error": f"file not found: {filename}"}), 404

    started_at = datetime.utcnow().isoformat()
    conn = get_db_connection()
    try:
        with open(filepath, 'r', encoding='utf-8') as f:
            sql_text = f.read()

        conn.executescript(sql_text)
        conn.commit()
        finished_at = datetime.utcnow().isoformat()
        return jsonify({
            "status": "success",
            "file": filename,
            "started_at": started_at,
            "finished_at": finished_at
        })
    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e), "file": filename}), 400
    finally:
        conn.close()

def run_crew_task(user_input):
    codeAgent = CodeAgentFlow(user_input=user_input)
    codeAgent.kickoff()

@app.route('/run_crew', methods=['POST'])
def run_crew():
    instructions = request.json.get('instructions', '')
    threading.Thread(target=run_crew_task, args=(instructions,)).start()
    return jsonify({"status": "Crew is Working..."})

def send_event(msg):
    socketio.emit("event", msg)

@app.route('/send')
def sendSSE():
    msg = {
        "filename": "Users_query.sql",
        "proposedCode": "SELECT * FROM Users;",
        "isNew": False,
        "newFileName": "Users_query.sql",
        "kind": "code_change"
    }
    send_event(msg)
    return "sent"

@socketio.on('connect')
def handle_connect():
    print("Client connected")
    emit("event", {"status": "connected"})

@socketio.on('disconnect')
def handle_disconnect():
    print("Client disconnected")

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