from flask import Flask, render_template, request, redirect, url_for, session, jsonify
from werkzeug.security import check_password_hash, generate_password_hash
from database import init_db, get_db
from ml_model import MLModel
from functools import wraps
from werkzeug.middleware.proxy_fix import ProxyFix
import os
import pandas as pd

app = Flask(__name__)
app.secret_key = os.environ.get("SECRET_KEY", "aims-secret-key")
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1)
app.config.update(
    PREFERRED_URL_SCHEME="https",
    SESSION_COOKIE_HTTPONLY=True,
    SESSION_COOKIE_SAMESITE="None" if os.environ.get("SPACE_ID") else "Lax",
    SESSION_COOKIE_SECURE=bool(os.environ.get("SPACE_ID")),
)

ml = MLModel()

TOPICS = [
    'Course', 'Internet', 'Food', 'Accommodation', 'Library',
    'Sports', 'Transportation', 'Administration', 'Health',
    'Security', 'Events', 'General', 'Other'
]

# INIT DB
init_db()

# ---------------- AUTH ----------------
def login_required(f):
    @wraps(f)
    def wrapper(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        return f(*args, **kwargs)
    return wrapper

# ---------------- HOME ----------------
@app.route("/")
def index():
    return render_template("index.html")

# ---------------- LOGIN ----------------
@app.route("/login", methods=["GET", "POST"])
def login():
    error = None

    if "user_id" in session:
        return redirect(url_for("dashboard"))

    if request.method == "POST":
        username = request.form.get("username", "").strip()
        password = request.form.get("password", "")

        db = get_db()
        user = db.execute(
            "SELECT * FROM users WHERE username=?",
            (username,)
        ).fetchone()
        db.close()

        if user and check_password_hash(user["password"], password):
            session["user_id"] = user["id"]
            session["username"] = user["username"]
            return redirect(url_for("dashboard"))

        error = "Wrong credentials"

    return render_template("login.html", error=error)

# ---------------- REGISTER ----------------
@app.route("/register", methods=["GET", "POST"])
def register():
    error = None

    if request.method == "POST":
        username = request.form["username"]
        email = request.form["email"]
        password = request.form["password"]
        confirm_password = request.form.get("confirm_password", "")

        # Validate password confirmation
        if password != confirm_password:
            return render_template("register.html", error="Passwords do not match")

        # Validate password length
        if len(password) < 6:
            return render_template("register.html", error="Password must be at least 6 characters")

        db = get_db()

        exists = db.execute(
            "SELECT * FROM users WHERE username=? OR email=?",
            (username, email)
        ).fetchone()

        if exists:
            return render_template("register.html", error="User already exists")

        db.execute(
            "INSERT INTO users (username,email,password) VALUES (?,?,?)",
            (username, email, generate_password_hash(password))
        )
        db.commit()
        db.close()

        return redirect(url_for("login"))

    return render_template("register.html", error=error)

# ---------------- LOGOUT ----------------
@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("index"))

# ---------------- DASHBOARD ----------------
@app.route("/dashboard")
@login_required
def dashboard():
    return render_template(
        "dashboard.html",
        topics=TOPICS,
        username=session.get("username")
    )

# ---------------- PREDICTION ----------------
@app.route("/prediction")
@login_required
def prediction():
    return render_template(
        "prediction.html",
        username=session.get("username")
    )

# ---------------- HISTORY ----------------
@app.route("/history")
@login_required
def history():
    db = get_db()
    feedbacks = db.execute(
        "SELECT * FROM feedbacks ORDER BY created_at DESC"
    ).fetchall()
    db.close()

    return render_template(
        "history.html",
        feedbacks=feedbacks,
        username=session.get("username")
    )

# Backward-compatible old profile URL
@app.route("/profile")
@login_required
def profile():
    return redirect(url_for("history"))

# ---------------- FEEDBACK ----------------
@app.route("/feedback", methods=["GET", "POST"])
def feedback():
    success = False

    if request.method == "POST":
        comment = request.form["comment"]
        submitted_topic = request.form.get("topic", "").strip()

        try:
            result = ml.predict(comment)
        except Exception as exc:
            return render_template("feedback.html", topics=TOPICS, success=False, error=str(exc))

        topic = submitted_topic if submitted_topic in TOPICS else result["topic"]

        db = get_db()
        db.execute("""
            INSERT INTO feedbacks
            (comment_text, topic, sentiment_label, sentiment_score)
            VALUES (?, ?, ?, ?)
        """, (
            comment,
            topic,
            result["sentiment_label"],
            result["sentiment_score"]
        ))
        db.commit()
        db.close()

        success = True

    return render_template("feedback.html", topics=TOPICS, success=success)

# ---------------- PREDICTION API ----------------
@app.route("/api/predict", methods=["POST"])
@login_required
def api_predict():
    payload = request.get_json(silent=True) or {}
    text = payload.get("text", "").strip()

    if not text:
        return jsonify({"error": "Text is required"}), 400

    try:
        result = ml.predict(text)
    except Exception as exc:
        return jsonify({"error": str(exc)}), 500

    db = get_db()
    cursor = db.execute("""
        INSERT INTO feedbacks
        (comment_text, topic, sentiment_label, sentiment_score)
        VALUES (?, ?, ?, ?)
    """, (
        text,
        result["topic"],
        result["sentiment_label"],
        result["sentiment_score"]
    ))
    db.commit()
    prediction_id = cursor.lastrowid
    db.close()

    result["id"] = prediction_id
    return jsonify(result)

# ---------------- CSV UPLOAD API ----------------
@app.route("/api/upload-csv", methods=["POST"])
@login_required
def api_upload_csv():
    uploaded = request.files.get("file")

    if not uploaded:
        return jsonify({"error": "CSV file is required"}), 400

    if not uploaded.filename.lower().endswith(".csv"):
        return jsonify({"error": "Only CSV files are supported"}), 400

    try:
        df = pd.read_csv(uploaded)
    except Exception:
        return jsonify({"error": "Could not read the CSV file"}), 400

    required_columns = {"comment"}
    if not required_columns.issubset(df.columns):
        return jsonify({"error": "CSV must contain a comment column"}), 400

    rows = []
    for _, row in df.iterrows():
        comment = str(row.get("comment", "")).strip()

        if not comment:
            continue

        try:
            result = ml.predict(comment)
        except Exception as exc:
            return jsonify({"error": str(exc)}), 500

        rows.append((
            comment,
            result["topic"],
            result["sentiment_label"],
            result["sentiment_score"]
        ))

    if rows:
        db = get_db()
        db.executemany("""
            INSERT INTO feedbacks
            (comment_text, topic, sentiment_label, sentiment_score)
            VALUES (?, ?, ?, ?)
        """, rows)
        db.commit()
        db.close()

    return jsonify({"processed": len(rows)})

# ---------------- DELETE FEEDBACK API ----------------
@app.route("/api/delete/<int:feedback_id>", methods=["POST"])
@login_required
def api_delete_feedback(feedback_id):
    db = get_db()
    db.execute("DELETE FROM feedbacks WHERE id=?", (feedback_id,))
    db.commit()
    db.close()

    return jsonify({"success": True})

# ---------------- DASHBOARD API ----------------
@app.route("/api/dashboard-data")
@login_required
def api_dashboard_data():
    db = get_db()
    query = "SELECT * FROM feedbacks WHERE topic IS NOT NULL AND topic != ''"
    params = []

    start_date = request.args.get("start_date", "").strip()
    end_date = request.args.get("end_date", "").strip()
    topic = request.args.get("topic", "").strip()
    sentiment = request.args.get("sentiment", "").strip()

    if start_date:
        query += " AND date(created_at) >= date(?)"
        params.append(start_date)

    if end_date:
        query += " AND date(created_at) <= date(?)"
        params.append(end_date)

    if topic and topic != "all":
        query += " AND topic = ?"
        params.append(topic)

    if sentiment and sentiment != "all":
        query += " AND sentiment_label = ?"
        params.append(sentiment)

    query += " ORDER BY created_at DESC"

    data = db.execute(query, params).fetchall()
    db.close()

    data = [dict(d) for d in data]

    total = len(data)
    pos = sum(1 for d in data if d["sentiment_label"] == "Positive")
    neu = sum(1 for d in data if d["sentiment_label"] == "Neutral")
    neg = sum(1 for d in data if d["sentiment_label"] == "Negative")

    topic_counts = {}
    for d in data:
        topic_counts[d["topic"]] = topic_counts.get(d["topic"], 0) + 1

    return jsonify({
        "total": total,
        "satisfaction": round(pos / total * 100, 1) if total else 0,
        "sentiment": {"Positive": pos, "Neutral": neu, "Negative": neg},
        "topics": topic_counts,
        "recent": data[:20]
    })

# ---------------- RUN ----------------
if __name__ == "__main__":
    port = int(os.environ.get("PORT", 7860))
    app.run(host="0.0.0.0", port=port, debug=False)