import os
from flask import Flask, request, jsonify
from psycopg2 import connect, sql
import psycopg2.extras
import gradio as gr
import threading

# Fetch environment variables
DB_NAME = os.getenv("DB_NAME")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT")
APP_PASSWORD = os.getenv("APP_PASSWORD")

# Database connection function
def get_db_connection():
    return connect(
        dbname=DB_NAME,
        user=DB_USER,
        password=DB_PASSWORD,
        host=DB_HOST,
        port=DB_PORT
    )

# Create Flask app
app = Flask(__name__)

# API endpoint for running SQL commands
@app.route("/run_sql", methods=["POST"])
def run_sql():
    # Get the password and command from the request
    password = request.json.get("password")
    command = request.json.get("command")

    # Check if the password is correct
    if password != APP_PASSWORD:
        return jsonify({"error": "Invalid password!"}), 401

    # Validate SQL command input
    if not command:
        return jsonify({"error": "No SQL command provided!"}), 400

    # Execute the SQL command
    conn = None
    result = None
    try:
        # Connect to the database
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

        # Execute the SQL command (this uses parameterized queries for security)
        cursor.execute(command)
        if command.strip().lower().startswith("select"):
            result = cursor.fetchall()  # If the command is a SELECT, fetch the results
        else:
            conn.commit()  # For non-SELECT commands, commit the changes
            result = {"message": "Command executed successfully!"}
    except Exception as e:
        result = {"error": f"Error executing command: {str(e)}"}
    finally:
        if conn:
            conn.close()

    return jsonify(result)

# Define Gradio interface
def gradio_interface():
    def greet(name):
        return f"Hello {name}!"

    interface = gr.Interface(fn=greet, inputs="text", outputs="text")
    interface.launch(share=True, inline=True)

# Starting Flask app in a separate thread
def run_flask():
    app.run(host="0.0.0.0", port=5050)

# Run Flask and Gradio in parallel using threading
if __name__ == "__main__":
    flask_thread = threading.Thread(target=run_flask)
    flask_thread.daemon = True  # Make sure it will close when the main thread exits
    flask_thread.start()

    # Run Gradio in the main thread
    gradio_interface()