from flask import request, jsonify
from db_config import get_db_connection

def create_user_stat():
    data = request.json
    user_id = data.get("user_id")
    name = data.get("name")
    description = data.get("description")
    symbol = data.get("symbol", "none")
    color = data.get("color", "#3b82f6")
    decimal_places = data.get("decimal_places", 1)

    if not user_id or not name:
        return jsonify({"error": "Missing user_id or name"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Check current stat count
        cursor.execute("SELECT COUNT(*) FROM user_stats WHERE user_id = %s", (user_id,))
        count = cursor.fetchone()[0]
        if count >= 5:
            return jsonify({"error": "Stat limit reached (max 5 per user)"}), 403

        cursor.execute("""
            INSERT INTO user_stats (user_id, name, description, symbol, color, decimal_places)
            VALUES (%s, %s, %s, %s, %s, %s)
        """, (user_id, name, description, symbol, color, decimal_places))

        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "User stat created successfully"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

def get_user_stats():
    user_id = request.args.get("user_id")
    if not user_id:
        return jsonify({"error": "Missing user_id"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM user_stats WHERE user_id = %s", (user_id,))
        stats = cursor.fetchall()
        cursor.close()
        conn.close()
        return jsonify(stats)
    except Exception as e:
        return jsonify({"error": str(e)}), 500


def update_user_stat():
    data = request.json
    stat_id = data.get("stat_id")
    field = data.get("field")
    value = data.get("value")

    if not stat_id or not field or value is None:
        return jsonify({"error": "Missing required fields"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(f"UPDATE user_stats SET {field} = %s WHERE id = %s", (value, stat_id))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "User stat updated"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500


def delete_user_stat():
    stat_id = request.json.get("stat_id")
    if not stat_id:
        return jsonify({"error": "Missing stat_id"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM user_stats WHERE id = %s", (stat_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "User stat deleted"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500



def get_stat_entries():
    user_id = request.args.get("user_id")
    stat_id = request.args.get("stat_id")

    if not user_id or not stat_id:
        return jsonify({"error": "Missing user_id or stat_id"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM user_stat_entries WHERE user_id = %s AND stat_id = %s ORDER BY date ASC", (user_id, stat_id))
        entries = cursor.fetchall()
        cursor.close()
        conn.close()
        return jsonify(entries)
    except Exception as e:
        return jsonify({"error": str(e)}), 500

def add_stat_entry():
    data = request.json
    user_id = data.get("user_id")
    stat_id = data.get("stat_id")
    value = data.get("value")
    date = data.get("date")  # optional

    if not user_id or not stat_id or value is None:
        return jsonify({"error": "Missing required fields"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # Check for existing entry today
        cursor.execute("""
            SELECT id FROM user_stat_entries 
            WHERE user_id = %s AND stat_id = %s AND DATE(date) = CURDATE()
        """, (user_id, stat_id))

        if cursor.fetchone():
            return jsonify({"error": "Only one entry per day is allowed for this stat"}), 409

        cursor.execute("""
            INSERT INTO user_stat_entries (user_id, stat_id, value, date)
            VALUES (%s, %s, %s, COALESCE(%s, NOW()))
        """, (user_id, stat_id, value, date))

        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "Stat entry added"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500

def update_stat_entry():
    data = request.json
    entry_id = data.get("entry_id")
    value = data.get("value")
    date = data.get("date")

    if not entry_id:
        return jsonify({"error": "Missing entry_id"}), 400

    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        if value is not None:
            cursor.execute("UPDATE user_stat_entries SET value = %s WHERE id = %s", (value, entry_id))
        if date:
            cursor.execute("UPDATE user_stat_entries SET date = %s WHERE id = %s", (date, entry_id))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "Entry updated"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500


def delete_stat_entry():
    entry_id = request.json.get("entry_id")
    if not entry_id:
        return jsonify({"error": "Missing entry_id"}), 400
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("DELETE FROM user_stat_entries WHERE id = %s", (entry_id,))
        conn.commit()
        cursor.close()
        conn.close()
        return jsonify({"message": "Entry deleted"})
    except Exception as e:
        return jsonify({"error": str(e)}), 500


def get_latest_stat_entry():
    user_id = request.args.get("user_id")
    stat_id = request.args.get("stat_id")
    if not user_id or not stat_id:
        return jsonify({"error": "Missing user_id or stat_id"}), 400
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.execute("""
            SELECT * FROM user_stat_entries
            WHERE user_id = %s AND stat_id = %s
            ORDER BY date DESC LIMIT 1
        """, (user_id, stat_id))
        entry = cursor.fetchone()
        cursor.close()
        conn.close()
        return jsonify(entry or {})
    except Exception as e:
        return jsonify({"error": str(e)}), 500