from flask import Flask, request, jsonify, send_from_directory
from db import run_sql
from gpt_helpers import generate_sql, generate_insights

# Load schema
try:
    with open("schema.txt", "r") as f:
        SCHEMA = f.read()
except FileNotFoundError:
    print("FATAL ERROR: schema.txt not found.")
    SCHEMA = ""

app = Flask(__name__, static_folder='static')


# -----------------------------
# CHATBOT ENDPOINT
# -----------------------------
@app.route("/ask", methods=["POST"])
def ask():
    data = request.json
    question = data.get("question")

    if not question:
        return jsonify({"error": "Question is required."}), 400

    # Generate SQL and execute
    try:
        sql_query = generate_sql(question, SCHEMA)
        result = run_sql(sql_query)
    except Exception as e:
        result = "DB ERROR"
        insights = f"**Error:** {e}"
    else:
        insights = generate_insights(question, result)

    return jsonify({
        "question": question,
        "sql": sql_query if 'sql_query' in locals() else None,
        "result": result,
        "insights": insights
    })


# -----------------------------
# SERVE FRONTEND
# -----------------------------
@app.route("/")
def index():
    return send_from_directory('static', 'index.html')


if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8000, debug=True)
