main.py

from flask import Flask, render_template, request, redirect, session, make_response, jsonify, Response, abort, send_from_directory


from flask_session import Session
from flask_socketio import SocketIO, emit, join_room

import json
import bcrypt
from datetime import datetime, timedelta
import os
from PIL import Image, ImageDraw, ImageFont
import ast
import requests as requests

from api import get_connection, set_config, get_conf, log, register_socketio, sqlq, auth, background
from backend import back
from migrate import run_migrations


try:
    run_migrations()
except Exception as e:
    print(f"[WARN] Migration beim Start fehlgeschlagen: {e}")

import uuid
import time
from werkzeug.utils import secure_filename




domain = str(get_conf("domain"))
path = str(get_conf("path"))


session_dir = os.path.join(os.getcwd(), "flask_session")
os.makedirs(session_dir, exist_ok=True)


try:
    for fname in os.listdir(session_dir):
        fpath = os.path.join(session_dir, fname)
        if os.path.isfile(fpath) and os.path.getsize(fpath) == 0:
            os.remove(fpath)
except Exception:
    pass

app = Flask(__name__)
app.config["SESSION_TYPE"] = "filesystem"
app.config["SESSION_FILE_DIR"] = session_dir
app.config["SESSION_FILE_THRESHOLD"] = 1000
app.config["SECRET_KEY"] = "cdejgkhjkaltkhjdmhtrjklejdklrklgf665gd4f35g735g735g7d57df4ges!7357g74g54th ggfjsdfzjkgfkjgfwerhjukvrtht"
app.config["PERMANENT_SESSION_LIFETIME"] = timedelta(days=10)
Session(app)


app.register_blueprint(back, url_prefix="/api")

socketio = SocketIO(app, async_mode="threading", cors_allowed_origins="*", path='/socket.io')

app.secret_key = "UDCuJKjkHJKFHKHE$(O(OVE(OWKLJJHjkHVRFHJKHGVRZEGHzV SRGFIUIOVHJKRJKJKFJR"
register_socketio(socketio)


UPLOAD_FOLDER = os.path.join(app.root_path, "static", "uploads")
os.makedirs(UPLOAD_FOLDER, exist_ok=True)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024  

# ------------------------ Online-User Tracking ------------------------
online_users = set()


#-----------------------------------------------------------------------------------------------------------------------------------------------before request--------------------------------------------------------------------------------
@app.before_request
def protect():
    rpath = request.path

    if "static" in rpath:
        return

    if "socket.io" in rpath or "EIO" in request.args:
        return

    if "login" in rpath:
        return

    if "/api" in rpath:
        return

    if session.get("auth") != True:
        return redirect("/" + path +"/login")


@app.route("/")
def index():
    user = session["user"] if session.get("user") else ""
    user_row = sqlq("SELECT id, kontacts, bio, avatar_url FROM users WHERE username = %s", (user,), "one")
    kontacts = []
    groups = []
    unread_users = {}
    unread_groups = {}
    avatar_url = ""
    bio = ""

    if user_row:
        user_id, raw_k, bio, avatar_url = user_row[0], user_row[1], user_row[2] or "", user_row[3] or ""
        try:
            raw_kontacts = json.loads(raw_k or "[]")
        except Exception:
            raw_kontacts = []

        if raw_kontacts:
            format_strings = ','.join(['%s'] * len(raw_kontacts))
            rows = sqlq(f"SELECT id, username FROM users WHERE id IN ({format_strings})", tuple(raw_kontacts), "all")
            if rows:
                kontacts = [{"id": r[0], "username": r[1]} for r in rows if r[1]]

        try:
            like_pattern = f"%{user_id}%"
            group_rows = sqlq(
                "SELECT id, name FROM `groups` WHERE owner_id = %s OR owner = %s OR members LIKE %s",
                (user_id, user_id, like_pattern),
                "all"
            )
            if group_rows:
                groups = [{"id": r[0], "name": r[1]} for r in group_rows]
        except Exception:
            try:
                group_rows = sqlq("SELECT id, name FROM `groups` WHERE members LIKE %s", (like_pattern,), "all")
                if group_rows:
                    groups = [{"id": r[0], "name": r[1]} for r in group_rows]
            except Exception:
                groups = []

        # Calculate unread 1-on-1 messages
        rows_u = sqlq("SELECT id, COUNT(*) FROM msg WHERE receiver = %s AND status != 'read' GROUP BY id", (user_id,), "all")
        if rows_u:
            for sender_id, cnt in rows_u:
                unread_users[str(sender_id)] = cnt

        # Calculate unread group messages
        for g in groups:
            gid = g["id"]
            lr_row = sqlq("SELECT last_read_msg_id FROM group_read_status WHERE user_id = %s AND group_id = %s", (user_id, gid), "one")
            last_read_id = lr_row[0] if lr_row else 0
            cnt_row = sqlq("SELECT COUNT(*) FROM msg WHERE conv = %s AND msg_id > %s AND id != %s", (gid, last_read_id, user_id), "one")
            cnt = cnt_row[0] if cnt_row else 0
            if cnt > 0:
                unread_groups[str(gid)] = cnt

    return render_template(
        "index.html",
        kontacts=kontacts,
        groups=groups,
        unread_users=unread_users,
        unread_groups=unread_groups,
        user=user,
        avatar_url=avatar_url or f"/chat/profile/{user}.png",
        bio=bio
    )


@app.route('/users_list')
@app.route('/chat/users_list')
def users_list():
    user = session.get('user')
    if not user:
        return jsonify({"code":401, "users": []})

    rows = sqlq("SELECT id, username FROM users WHERE username != %s ORDER BY username LIMIT 500", (user,), "all")
    users = [{"id": r[0], "username": r[1]} for r in rows] if rows else []
    return jsonify({"code":200, "users": users})


@app.route("/chat/<receiver>")
def chat(receiver):
    user = session["user"] if session.get("user") else ""

    id_u = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]
    id_r = sqlq("SELECT id FROM users WHERE username = %s", (receiver,), "one")[0]

    raw_msg = sqlq(
        """SELECT m.msg_id, m.content, m.time, COALESCE(m.edited,0), m.status, m.file_url, m.file_type, u.username, m.id, m.reply_to_id, m.deleted_for_users, m.forwarded_from
           FROM msg m
           LEFT JOIN users u ON u.id = m.id
           WHERE ((m.id = %s AND m.receiver = %s) OR (m.id = %s AND m.receiver = %s))
             AND (m.conv IS NULL OR m.conv = 0)
           ORDER BY m.msg_id DESC LIMIT 50""",
        (id_u, id_r, id_r, id_u), "all"
    )

    msg = []
    if raw_msg:
        for r in raw_msg:
            if r[1] and (r[1] == '🗑️ Nachricht gelöscht' or str(r[1]).startswith('🗑️')):
                continue
            del_users = []
            if r[10]:
                try:
                    del_users = json.loads(r[10])
                except Exception:
                    del_users = []
            if id_u not in del_users and str(id_u) not in del_users:
                # Include forwarded_from as 11th element (index 10 in array)
                msg.append((r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[11]))
        msg.reverse()

    sqlq("UPDATE msg SET status = 'read' WHERE id = %s AND receiver = %s AND status != 'read'", (id_r, id_u), "none")

    reactions_map = {}
    if msg:
        msg_ids = [m[0] for m in msg if m[0]]
        if msg_ids:
            try:
                format_ids = ','.join(['%s'] * len(msg_ids))
                r_rows = sqlq(f"SELECT message_id, emoji, user_id FROM message_reactions WHERE message_id IN ({format_ids})", tuple(msg_ids), "all")
                if r_rows:
                    for r_msg_id, emo, uid in r_rows:
                        if r_msg_id not in reactions_map:
                            reactions_map[r_msg_id] = {}
                        if emo not in reactions_map[r_msg_id]:
                            reactions_map[r_msg_id][emo] = []
                        reactions_map[r_msg_id][emo].append(uid)
            except Exception:
                pass

    return render_template("chat.html", user=id_u, receiver=id_r, receiver_name=receiver, msg=msg, reactions_map=reactions_map)


@app.route('/group/<int:conv_id>')
def group_chat(conv_id):
    user = session["user"] if session.get("user") else ""
    if not user:
        return redirect("/" + path + "/login")

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]

    grp = sqlq("SELECT id, name, members FROM `groups` WHERE id = %s", (conv_id,), "one")
    if not grp:
        return redirect("/" + path)
    group_name = grp[1]
    raw_members = grp[2]

    members = []
    try:
        members = json.loads(raw_members) if raw_members else []
    except Exception:
        try:
            members = ast.literal_eval(raw_members) if raw_members else []
        except Exception:
            try:
                members = [int(x.strip()) for x in str(raw_members).split(",") if x.strip()]
            except Exception:
                members = []

    participant_list = []
    if members:
        try:
            format_strings = ','.join(['%s'] * len(members))
            rows_users = sqlq(f"SELECT id, username FROM users WHERE id IN ({format_strings})", tuple(members), "all")
            if rows_users:
                participant_list = [{"id": r[0], "username": r[1]} for r in rows_users]
        except Exception:
            participant_list = []

    raw_rows = sqlq(
        """SELECT m.msg_id, m.content, m.time, COALESCE(m.edited,0), m.status, m.file_url, m.file_type, u.username, m.id, m.reply_to_id, m.deleted_for_users, m.forwarded_from
           FROM msg m
           LEFT JOIN users u ON u.id = m.id
           WHERE m.conv = %s
           ORDER BY m.msg_id DESC LIMIT 50""",
        (conv_id,), "all"
    )

    rows = []
    if raw_rows:
        max_m_id = max([r[0] for r in raw_rows if r[0]])
        if max_m_id > 0:
            sqlq("""INSERT INTO group_read_status (user_id, group_id, last_read_msg_id)
                    VALUES (%s, %s, %s)
                    ON DUPLICATE KEY UPDATE last_read_msg_id = %s""", (my_id, conv_id, max_m_id, max_m_id), "none")

        for r in raw_rows:
            if r[1] and (r[1] == '🗑️ Nachricht gelöscht' or str(r[1]).startswith('🗑️')):
                continue
            del_users = []
            if r[10]:
                try:
                    del_users = json.loads(r[10])
                except Exception:
                    del_users = []
            if my_id not in del_users and str(my_id) not in del_users:
                rows.append((r[0], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[11]))
        rows.reverse()

    reactions_map = {}
    if rows:
        msg_ids = [m[0] for m in rows if m[0]]
        if msg_ids:
            format_ids = ','.join(['%s'] * len(msg_ids))
            r_rows = sqlq(f"SELECT message_id, emoji, user_id FROM message_reactions WHERE message_id IN ({format_ids})", tuple(msg_ids), "all")
            if r_rows:
                for r_msg_id, emo, uid in r_rows:
                    if r_msg_id not in reactions_map:
                        reactions_map[r_msg_id] = {}
                    if emo not in reactions_map[r_msg_id]:
                        reactions_map[r_msg_id][emo] = []
                    reactions_map[r_msg_id][emo].append(uid)

    return render_template("chat.html", user=my_id, receiver=0, receiver_name=group_name, msg=rows, group_id=conv_id, participants=participant_list, reactions_map=reactions_map)



@app.route('/group/<int:gid>/add/<int:uid>')
def add_member(gid, uid):
    user = session.get("user")
    if not user:
        return redirect("/" + path + "/login")

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]


    row = sqlq("SELECT members FROM `groups` WHERE id = %s", (gid,), "one")
    if not row:
        return redirect(request.referrer or ("/" + path))

    try:
        members = json.loads(row[0]) if row[0] else []
    except Exception:
        try:
            members = ast.literal_eval(row[0]) if row[0] else []
        except Exception:
            members = []

    if uid not in members:
        members.append(uid)
        sqlq("UPDATE `groups` SET members = %s WHERE id = %s", (json.dumps(members), gid), "none")

    return redirect(request.referrer or ("/" + path + "/group/" + str(gid)))

@app.route("/kontakt/<art>/<id>")
@app.route("/kontakt")
def kontakt(art=None,id=None):
    user = session["user"] if session.get("user") else ""

    if art == "del":
        kontakte = ast.literal_eval(sqlq("SELECT kontacts FROM users WHERE id = %s", (sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0],), "one")[0] or "[]")
        kontakte.remove(int(id))
        sqlq("UPDATE users SET kontacts = %s WHERE username = %s", (str(kontakte), user), "none")
        return redirect("/" + path)


    id_u = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]

    if id == None:
        data = sqlq("SELECT id,username FROM users WHERE id != %s",(session.get("user"),),"all")
        return render_template("kontakt.html",daten=data)
    elif id != None:
        if art == "1":
            pass
        elif art == "2":
            id = sqlq("SELECT id FROM users WHERE username = %s",(id,),"one")[0]
        kontakte = ast.literal_eval( sqlq("SELECT kontacts FROM users WHERE id = %s",(id_u,),"one")[0] or "[]" )
        if not int(id) in kontakte:
            kontakte.append(int(id))
        sqlq("UPDATE users SET kontacts = %s WHERE username = %s", (str(kontakte), session.get("user")), "none")
        return redirect("/" + path + "/kontakt")
    return redirect("/" + path + "/kontakt")

@app.route("/msg/<receiver>",methods=["POST"])
@app.route("/chat/msg/<receiver>",methods=["POST"])
@app.route("/msg/<receiver>/<msg>")
@app.route("/chat/msg/<receiver>/<msg>")
def msg(receiver,msg=None):
    user = session.get("user") if session.get("user") else ""

    group_id = request.form.get("group_id") or request.form.get("conv")
    reply_to_id = request.form.get("reply_to_id")
    try:
        reply_to_id = int(reply_to_id) if reply_to_id else None
    except (ValueError, TypeError):
        reply_to_id = None

    if msg is None:
        msg = str(request.form.get("msg") or "")

    my = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my:
        return redirect("/" + path + "/login")
    my_id = my[0]

    if group_id:
        try:
            gid = int(group_id)
        except Exception:
            return jsonify({"code":400, "error":"Ungültige group_id"}), 400

        msg_id = sqlq(
            "INSERT INTO msg (id, receiver, conv, content, status, reply_to_id) VALUES (%s, %s, %s, %s, 'sent', %s)",
            (my_id, 0, gid, msg, reply_to_id), "lastid"
        )

        socketio.emit("msg", {"content": msg, "sender": user, "sender_id": my_id, "status": "sent", "msg_id": msg_id, "reply_to_id": reply_to_id}, room=f"group_{gid}")
        if request.headers.get("X-Requested-With") == "XMLHttpRequest" or request.is_json or request.headers.get("Accept", "").find("application/json") != -1:
            return jsonify({"code": 200, "message": "Gesendet", "msg_id": msg_id})
        return redirect("/" + path + "/group/" + str(gid))
    try:
        recv_id = int(receiver)
        row = sqlq("SELECT username FROM users WHERE id = %s", (recv_id,), "one")
        if not row:
            return redirect("/" + path)
        receiver_name = row[0]
    except ValueError:
        row = sqlq("SELECT id FROM users WHERE username = %s", (receiver,), "one")
        if not row:
            return redirect("/" + path)
        recv_id = row[0]
        receiver_name = receiver

    ids = sorted([int(my_id), int(recv_id)])
    room = f"{ids[0]}_{ids[1]}"

    msg_id = sqlq("INSERT INTO msg (id, receiver, content, status, reply_to_id) VALUES (%s, %s, %s, 'sent', %s)", (my_id, recv_id, msg, reply_to_id), "lastid")

    msg_payload = {"content": msg, "sender": user, "sender_id": my_id, "status": "sent", "msg_id": msg_id, "reply_to_id": reply_to_id}
    print(f"[MSG-EMIT] room={room}, payload={msg_payload}")
    socketio.emit("msg", msg_payload, room=room)

    if request.headers.get("X-Requested-With") == "XMLHttpRequest" or request.is_json or request.headers.get("Accept", "").find("application/json") != -1:
        return jsonify({"code": 200, "message": "Gesendet", "msg_id": msg_id})

    return redirect("/" + path + "/chat/" + receiver_name + "")

# ------------------------ File-Upload ------------------------
@app.route("/upload", methods=["POST"])
def upload_file():
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    receiver_id = request.form.get("receiver_id")
    group_id = request.form.get("group_id")
    file = request.files.get("file")

    if not file or file.filename == '':
        return jsonify({"code": 400, "error": "Keine Datei ausgewählt"}), 400

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]
    filename = secure_filename(file.filename)
    ext = os.path.splitext(filename)[1].lower()

    if ext in ['.jpg', '.jpeg', '.png', '.gif', '.webp']:
        file_type = 'image'
    elif ext in ['.mp3', '.wav', '.ogg', '.m4a', '.webm', '.mp4', '.aac', '.opus']:
        file_type = 'audio'
    else:
        file_type = 'document'

    unique_name = f"{uuid.uuid4().hex}_{filename}"
    file_path = os.path.join(app.config['UPLOAD_FOLDER'], unique_name)
    file.save(file_path)

    file_url = f"/chat/static/uploads/{unique_name}"
    content = filename

    if group_id:
        group_id = int(group_id)
        msg_id = sqlq(
            "INSERT INTO msg (id, receiver, conv, content, file_url, file_type, status) VALUES (%s, 0, %s, %s, %s, %s, 'sent')",
            (my_id, group_id, content, file_url, file_type), "lastid"
        )
        room = f"group_{group_id}"
        socketio.emit("msg", {
            "content": content,
            "sender": user,
            "sender_id": my_id,
            "file_url": file_url,
            "file_type": file_type,
            "status": "sent",
            "msg_id": msg_id
        }, room=room)
    elif receiver_id:
        receiver_id = int(receiver_id)
        msg_id = sqlq(
            "INSERT INTO msg (id, receiver, content, file_url, file_type, status) VALUES (%s, %s, %s, %s, %s, 'sent')",
            (my_id, receiver_id, content, file_url, file_type), "lastid"
        )
        ids = sorted([int(my_id), receiver_id])
        room = f"{ids[0]}_{ids[1]}"
        socketio.emit("msg", {
            "content": content,
            "sender": user,
            "sender_id": my_id,
            "file_url": file_url,
            "file_type": file_type,
            "status": "sent",
            "msg_id": msg_id
        }, room=room)

    return jsonify({"code": 200, "file_url": file_url, "file_type": file_type})

@app.route("/chat/media/<target_type>/<target_id>", methods=["GET"])
@app.route("/media/<target_type>/<target_id>", methods=["GET"])
@app.route("/api/chat/<target_type>/<target_id>/media", methods=["GET"])
@app.route("/chat/api/chat/<target_type>/<target_id>/media", methods=["GET"])
def get_chat_media(target_type, target_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id_row = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id_row:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id_row[0]

    target_user_id = target_id
    if target_type != "group":
        try:
            target_user_id = int(target_id)
        except ValueError:
            row = sqlq("SELECT id FROM users WHERE username = %s", (str(target_id),), "one")
            if row:
                target_user_id = row[0]

    if target_type == "group":
        rows = sqlq(
            """SELECT msg_id, content, file_url, file_type, time 
               FROM msg 
               WHERE conv = %s AND file_url IS NOT NULL AND file_url != '' 
               ORDER BY time DESC""",
            (int(target_id),), "all"
        )
    else:
        rows = sqlq(
            """SELECT msg_id, content, file_url, file_type, time 
               FROM msg 
               WHERE ((id = %s AND receiver = %s) OR (id = %s AND receiver = %s)) 
                 AND (conv IS NULL OR conv = 0)
                 AND file_url IS NOT NULL AND file_url != '' 
               ORDER BY time DESC""",
            (my_id, target_user_id, target_user_id, my_id), "all"
        )

    media = []
    if rows:
        for r in rows:
            media.append({
                "msg_id": r[0],
                "content": r[1],
                "file_url": r[2],
                "file_type": r[3],
                "time": str(r[4])
            })

    return jsonify({"code": 200, "media": media})


# ------------------------ Reactions API ------------------------
@app.route("/chat/reactions/<int:msg_id>", methods=["POST"])
@app.route("/reactions/<int:msg_id>", methods=["POST"])
@app.route("/api/messages/<int:msg_id>/reactions", methods=["POST"])
@app.route("/chat/api/messages/<int:msg_id>/reactions", methods=["POST"])
def toggle_message_reaction(msg_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401
    data = request.get_json(silent=True) or {}
    emoji = data.get("emoji")

    if not emoji or not user:
        return jsonify({"code": 400, "error": "Fehlende Parameter"}), 400

    my_id_row = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id_row:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id_row[0]


    msg_exists = sqlq("SELECT msg_id, id, receiver, conv FROM msg WHERE msg_id = %s", (msg_id,), "one")
    if not msg_exists:
        return jsonify({"code": 404, "error": "Nachricht nicht gefunden"}), 404

    existing = sqlq("SELECT id FROM message_reactions WHERE message_id = %s AND user_id = %s AND emoji = %s",
                    (msg_id, my_id, emoji), "one")

    if existing:
        sqlq("DELETE FROM message_reactions WHERE id = %s", (existing[0],), "none")
    else:
        sqlq("INSERT INTO message_reactions (message_id, user_id, emoji) VALUES (%s, %s, %s)",
             (msg_id, my_id, emoji), "none")

    all_reactions = sqlq("SELECT emoji, user_id FROM message_reactions WHERE message_id = %s", (msg_id,), "all")
    reactions_dict = {}
    if all_reactions:
        for emo, uid in all_reactions:
            if emo not in reactions_dict:
                reactions_dict[emo] = []
            reactions_dict[emo].append(uid)

    payload = {"message_id": msg_id, "reactions": reactions_dict}

    sender_id, recv_id, conv_id = msg_exists[1], msg_exists[2], msg_exists[3]
    if conv_id and conv_id != 0:
        socketio.emit("reaction_update", payload, room=f"group_{conv_id}")
    else:
        ids = sorted([int(sender_id), int(recv_id)])
        socketio.emit("reaction_update", payload, room=f"{ids[0]}_{ids[1]}")
        socketio.emit("reaction_update", payload, room=f"user_{sender_id}")
        socketio.emit("reaction_update", payload, room=f"user_{recv_id}")

    return jsonify({"code": 200, "reactions": reactions_dict})

# ------------------------ group Chat ------------------------
@app.route("/create_group", methods=["POST"])
@app.route('/chat/create_group', methods=['POST'])
def create_group():
    user = session.get("user")
    if not user:
        return redirect("/" + path + "/login")

    data = request.get_json(silent=True) or {}
    group_name = str(data.get("name", "")).strip()
    members = data.get("members", [])

    if not group_name:
        return jsonify({"code": 400, "error": "Gruppenname erforderlich"}), 400

    try:
        my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]
    except Exception:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    try:
        members = [int(m) for m in (members or [])]
    except Exception:
        members = []

    if my_id not in members:
        members.append(my_id)

    members_json = json.dumps(members)
    try:
        sqlq("INSERT INTO `groups` (name, owner_id, members) VALUES (%s, %s, %s)", (group_name, my_id, members_json), "none")
    except Exception as e:
        try:
            sqlq("INSERT INTO `groups` (name, owner, members) VALUES (%s, %s, %s)", (group_name, my_id, members_json), "none")
        except Exception as e2:
            raise

    gid_row = sqlq("SELECT LAST_INSERT_ID()", (), "one")
    gid = gid_row[0] if gid_row else None

    return jsonify({"code": 201, "group": {"id": gid, "name": group_name}}), 201


@app.route("/api/groups/<int:group_id>", methods=["GET"])
@app.route("/chat/api/groups/<int:group_id>", methods=["GET"])
def get_group_info(group_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    group_row = sqlq("SELECT id, name, owner_id, members, avatar_url FROM `groups` WHERE id = %s", (group_id,), "one")
    if not group_row:
        return jsonify({"code": 404, "error": "Gruppe nicht gefunden"}), 404

    gid, name, owner_id, members_json, avatar_url = group_row
    try:
        member_ids = json.loads(members_json) if members_json else []
    except Exception:
        member_ids = []

    members_info = []
    if member_ids:
        format_strings = ','.join(['%s'] * len(member_ids))
        rows = sqlq(f"SELECT id, username FROM users WHERE id IN ({format_strings})", tuple(member_ids), "all")
        if rows:
            for r in rows:
                members_info.append({"id": r[0], "username": r[1], "is_owner": r[0] == owner_id})

    return jsonify({
        "code": 200,
        "group": {
            "id": gid,
            "name": name,
            "owner_id": owner_id,
            "avatar_url": avatar_url or f"/chat/static/img/profil/default_group.png",
            "members": members_info
        }
    })


@app.route("/api/groups/<int:group_id>/members", methods=["POST"])
@app.route("/chat/api/groups/<int:group_id>/members", methods=["POST"])
def update_group_members(group_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id[0]

    group_row = sqlq("SELECT owner_id, members FROM `groups` WHERE id = %s", (group_id,), "one")
    if not group_row:
        return jsonify({"code": 404, "error": "Gruppe nicht gefunden"}), 404

    owner_id, members_json = group_row
    if owner_id != my_id:
        return jsonify({"code": 403, "error": "Nur der Gruppen-Admin kann Mitglieder verwalten"}), 403

    data = request.get_json(silent=True) or {}
    new_member_ids = data.get("members", [])
    try:
        new_member_ids = [int(m) for m in new_member_ids]
    except Exception:
        new_member_ids = []

    if owner_id not in new_member_ids:
        new_member_ids.append(owner_id)

    updated_json = json.dumps(new_member_ids)
    sqlq("UPDATE `groups` SET members = %s WHERE id = %s", (updated_json, group_id), "none")

    return jsonify({"code": 200, "message": "Mitglieder aktualisiert"})




@app.route("/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        name = str(request.form.get("username"))
        password = str(request.form.get("password"))
        email = str(request.form.get("email"))

        user = sqlq("SELECT id FROM users WHERE username = %s",(name,),"one")
        if not user:
            password_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")

            img = Image.new('RGB', (1000, 1000), (127, 127, 127))
            draw = ImageDraw.Draw(img)
            try:
                font = ImageFont.truetype("/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf", 300)
                draw.text((400, 450), name, fill=(255, 0, 0), font=font)
            except:
                draw.text((400, 450), name, fill=(255, 0, 0))
            img.save("static/img/profil/" + name + ".png")

            sqlq("INSERT INTO users (username, password,email) VALUES (%s, %s,%s)", (name, password_hash,email),"none")
            return redirect("/" + path +"/login")
        else:
            return render_template("register.html", daten="Benutzername existiert bereits")
    return render_template("register.html")


@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        user = request.form.get("username")
        passw = request.form.get("password")
        req = auth(user, passw)
        if req["data"] == 1:
            session.permanent = True
            session["auth"] = True
            session["user"] = user

            if not os.path.exists("static/img/profil/" + user + ".png"):
                img = Image.new('RGB', (1000, 1000), (127, 127, 127))
                draw = ImageDraw.Draw(img)
                try:
                    font = ImageFont.truetype("/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf", 100)
                    draw.text((400, 450), user, fill=(255, 0, 0), font=font)
                except:
                    draw.text((400, 450), user, fill=(255, 0, 0))
                img.save("static/img/profil/" + user + ".png")

            return redirect("/" + path)
# ------------------------ Profile & Forward Web Routes ------------------------
@app.route("/profile", methods=["GET", "POST"])
@app.route("/chat/profile_settings", methods=["GET", "POST"])
def user_profile_settings():
    user = session.get("user")
    if not user:
        return redirect("/" + path + "/login")

    my_row = sqlq("SELECT id, username, bio, avatar_url, email FROM users WHERE username = %s", (user,), "one")
    if not my_row:
        return redirect("/" + path + "/login")

    my_id, username, bio, avatar_url, email = my_row[0], my_row[1], my_row[2] or "", my_row[3] or "", my_row[4] or ""

    if request.method == "POST":
        new_bio = request.form.get("bio", "").strip()
        avatar_file = request.files.get("avatar")

        new_avatar_url = avatar_url

        if avatar_file and avatar_file.filename != '':
            try:
                img = Image.open(avatar_file.stream)
                img = img.convert("RGB")
                w, h = img.size
                min_dim = min(w, h)
                left = (w - min_dim) / 2
                top = (h - min_dim) / 2
                right = (w + min_dim) / 2
                bottom = (h + min_dim) / 2
                img = img.crop((left, top, right, bottom))
                img = img.resize((400, 400), Image.LANCZOS)

                profile_dir = os.path.join(app.root_path, "static", "img", "profil")
                os.makedirs(profile_dir, exist_ok=True)
                save_filename = f"{username}.png"
                save_path = os.path.join(profile_dir, save_filename)
                img.save(save_path, "PNG")
                new_avatar_url = f"/chat/static/profil/{save_filename}"
            except Exception as e:
                print(f"[ERROR] Profilbild Upload: {e}")

        sqlq("UPDATE users SET bio = %s, avatar_url = %s WHERE id = %s", (new_bio, new_avatar_url, my_id), "none")
        return redirect("/" + path + "/profile")

    return render_template(
        "profile.html",
        username=username,
        bio=bio,
        avatar_url=avatar_url or f"/chat/static/img/profil/{username}.png",
        email=email
    )


@app.route("/chat/forward_msg", methods=["POST"])
@app.route("/forward_msg", methods=["POST"])
def web_forward_msg():
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")[0]
    data = request.get_json(silent=True) or {}
    msg_id = data.get("msg_id")
    target_type = data.get("target_type")
    target_id = data.get("target_id")

    if not msg_id or not target_type or not target_id:
        return jsonify({"code": 400, "error": "Fehlende Parameter"}), 400

    row = sqlq("""SELECT m.content, m.file_url, m.file_type, u.username 
                  FROM msg m 
                  LEFT JOIN users u ON u.id = m.id 
                  WHERE m.msg_id = %s""", (msg_id,), "one")
    if not row:
        return jsonify({"code": 404, "error": "Nachricht nicht gefunden"}), 404

    content, file_url, file_type, orig_sender = row[0], row[1], row[2], row[3] or "Anonym"

    if target_type == "group":
        new_id = sqlq(
            "INSERT INTO msg (id, receiver, conv, content, file_url, file_type, forwarded_from, status) VALUES (%s, 0, %s, %s, %s, %s, %s, 'sent')",
            (my_id, target_id, content, file_url, file_type, orig_sender), "lastid"
        )
        socketio.emit("msg", {
            "content": content,
            "sender": user,
            "sender_id": my_id,
            "file_url": file_url,
            "file_type": file_type,
            "forwarded_from": orig_sender,
            "status": "sent",
            "msg_id": new_id
        }, room=f"group_{target_id}")
    else:
        new_id = sqlq(
            "INSERT INTO msg (id, receiver, content, file_url, file_type, forwarded_from, status) VALUES (%s, %s, %s, %s, %s, %s, 'sent')",
            (my_id, target_id, content, file_url, file_type, orig_sender), "lastid"
        )
        ids = sorted([my_id, int(target_id)])
        socketio.emit("msg", {
            "content": content,
            "sender": user,
            "sender_id": my_id,
            "file_url": file_url,
            "file_type": file_type,
            "forwarded_from": orig_sender,
            "status": "sent",
            "msg_id": new_id
        }, room=f"{ids[0]}_{ids[1]}")

    return jsonify({"code": 200, "message": "Nachricht weitergeleitet"})


@app.route('/chat/profile/<path:filename>')
def profile_image(filename):
    profile_dir = os.path.join(app.root_path, 'static', 'img', 'profil')
    os.makedirs(profile_dir, exist_ok=True)

    safe_name = secure_filename(filename)
    if not safe_name.lower().endswith('.png'):
        clean_username = safe_name
        safe_name = safe_name + '.png'
    else:
        clean_username = safe_name[:-4]

    file_path = os.path.join(profile_dir, safe_name)
    if not os.path.exists(file_path):
        img = Image.new('RGB', (200, 200), (99, 102, 241))
        draw = ImageDraw.Draw(img)
        initial = clean_username[0].upper() if clean_username else "?"
        try:
            font = ImageFont.truetype("/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf", 90)
            draw.text((70, 45), initial, fill=(255, 255, 255), font=font)
        except Exception:
            draw.text((90, 80), initial, fill=(255, 255, 255))
        img.save(file_path)

    response = make_response(send_from_directory(profile_dir, safe_name))
    response.headers['Cache-Control'] = 'public, max-age=86400, must-revalidate'
    return response



@app.route("/logout")
def logout():

    session["auth"] = False
    session["user"] = ""
    session.clear()
    session.pop("user", None)
    return redirect("/" + path + "/login")


# ------------------------ Search ------------------------
@app.route("/search")
def search():
    q = request.args.get("q", "").strip()
    if len(q) < 1:
        return jsonify({"results": []})

    user = session.get("user")
    if not user:
        return jsonify({"results": []})

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"results": []})
    my_id = my_id[0]

    like = f"%{q}%"
    rows = sqlq(
        """SELECT m.id, m.content, m.time, u.username
           FROM msg m
           JOIN users u ON u.id = m.id
           WHERE (m.id = %s OR m.receiver = %s)
             AND m.content LIKE %s
           ORDER BY m.time DESC
           LIMIT 50""",
        (my_id, my_id, like), "all"
    )

    results = []
    if rows:
        for r in rows:
            results.append({
                "msg_id": r[0],
                "content": r[1][:100],
                "time": str(r[2]),
                "other_user": r[3]
            })

    return jsonify({"results": results})


# ------------------------ Typing Event ------------------------
@socketio.on('typing')
def on_typing(data):
    my_id = data.get("user_id")
    target_id = data.get("receiver")
    if not my_id or not target_id:
        return
    ids = sorted([int(my_id), int(target_id)])
    room = f"{ids[0]}_{ids[1]}"
    emit("typing", {"user_id": my_id}, room=room, include_self=False)

@socketio.on('stop_typing')
def on_stop_typing(data):
    my_id = data.get("user_id")
    target_id = data.get("receiver")
    if not my_id or not target_id:
        return
    ids = sorted([int(my_id), int(target_id)])
    room = f"{ids[0]}_{ids[1]}"
    emit("stop_typing", {"user_id": my_id}, room=room, include_self=False)


# ------------------------ Read Receipts ------------------------
@socketio.on('mark_read')
def on_mark_read(data):
    my_id = data.get("user_id")
    sender_id = data.get("sender_id")
    if not my_id or not sender_id:
        return

    sqlq("UPDATE msg SET status = 'read' WHERE id = %s AND receiver = %s AND status != 'read'", (sender_id, my_id), "none")

    ids = sorted([int(my_id), int(sender_id)])
    room = f"{ids[0]}_{ids[1]}"
    payload = {"reader_id": my_id, "sender_id": sender_id}
    emit("messages_read", payload, room=room)
    emit("messages_read", payload, room=f"user_{sender_id}")


# ------------------------  message edit & del ------------------------
@app.route("/edit_msg", methods=["POST"])
def edit_msg():
    data = request.get_json(silent=True) or {}
    msg_id = data.get("msg_id")
    new_content = data.get("content", "").strip()
    user = session.get("user")

    if not msg_id or not new_content or not user:
        return jsonify({"code": 400, "error": "Fehlende Parameter"}), 400

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401
    my_id = my_id[0]

    row = sqlq("SELECT id, receiver, conv FROM msg WHERE id = %s AND msg_id = %s", (my_id, msg_id), "one")
    if not row:
        return jsonify({"code": 403, "error": "Nicht deine Nachricht"}), 403

    sqlq("UPDATE msg SET content = %s, edited = 1 WHERE msg_id = %s", (new_content, msg_id), "none")

    conv = row[2]
    if conv and conv > 0:
        room = f"group_{conv}"
    else:
        ids = sorted([int(row[0]), int(row[1])])
        room = f"{ids[0]}_{ids[1]}"

    socketio.emit("msg_edited", {"msg_id": msg_id, "content": new_content, "edited": 1}, room=room)

    return jsonify({"code": 200})


@app.route("/delete_msg", methods=["POST"])
def delete_msg():
    data = request.get_json(silent=True) or {}
    msg_id = data.get("msg_id")
    mode = data.get("mode", "all")
    user = session.get("user")

    if not msg_id or not user:
        return jsonify({"code": 400, "error": "Fehlende Parameter"}), 400

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401
    my_id = my_id[0]

    row = sqlq("SELECT id, receiver, conv, deleted_for_users FROM msg WHERE msg_id = %s", (msg_id,), "one")
    if not row:
        return jsonify({"code": 404, "error": "Nachricht nicht gefunden"}), 404

    sender_id, receiver_id, conv, raw_del = row[0], row[1], row[2], row[3]

    if mode == "me":
        try:
            del_users = json.loads(raw_del) if raw_del else []
        except Exception:
            del_users = []
        if my_id not in del_users and str(my_id) not in del_users:
            del_users.append(my_id)
        sqlq("UPDATE msg SET deleted_for_users = %s WHERE msg_id = %s", (json.dumps(del_users), msg_id), "none")
        return jsonify({"code": 200, "mode": "me"})
    else:
        if sender_id != my_id:
            return jsonify({"code": 403, "error": "Nicht deine Nachricht"}), 403

        deleted_text = "🗑️ Nachricht gelöscht"
        sqlq("UPDATE msg SET content = %s, file_url = NULL, file_type = NULL, edited = 0 WHERE msg_id = %s", (deleted_text, msg_id), "none")

        if conv and conv > 0:
            room = f"group_{conv}"
        else:
            ids = sorted([int(sender_id), int(receiver_id)])
            room = f"{ids[0]}_{ids[1]}"

        socketio.emit("msg_deleted", {"msg_id": msg_id, "content": deleted_text}, room=room)
        return jsonify({"code": 200, "mode": "all"})


# ------------------------ Pin Message Routes ------------------------
@app.route("/chat/pin/<int:msg_id>", methods=["POST"])
@app.route("/pin/<int:msg_id>", methods=["POST"])
def pin_message(msg_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id[0]

    msg_row = sqlq("SELECT m.msg_id, m.id, m.receiver, m.conv, m.content, u.username FROM msg m LEFT JOIN users u ON u.id = m.id WHERE m.msg_id = %s", (msg_id,), "one")
    if not msg_row:
        return jsonify({"code": 404, "error": "Nachricht nicht gefunden"}), 404

    sender_id, receiver_id, conv, content, sender_name = msg_row[1], msg_row[2], msg_row[3], msg_row[4], msg_row[5]

    if conv and conv > 0:
        room_type = "group"
        room_id = str(conv)
        room = f"group_{conv}"
    else:
        room_type = "user"
        ids = sorted([int(sender_id), int(receiver_id)])
        room_id = f"{ids[0]}_{ids[1]}"
        room = room_id

    sqlq("""
        INSERT INTO pinned_messages (room_type, room_id, message_id, pinned_by)
        VALUES (%s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE message_id = VALUES(message_id), pinned_by = VALUES(pinned_by), pinned_at = CURRENT_TIMESTAMP
    """, (room_type, room_id, msg_id, my_id), "none")

    payload = {"msg_id": msg_id, "content": content, "sender_name": sender_name or "Unbekannt"}
    socketio.emit("pin_update", payload, room=room)

    return jsonify({"code": 200, "pinned": payload})


@app.route("/chat/unpin/<target_type>/<target_id>", methods=["POST"])
@app.route("/unpin/<target_type>/<target_id>", methods=["POST"])
def unpin_message(target_type, target_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id[0]

    if target_type == "group":
        room_type = "group"
        room_id = str(target_id)
        room = f"group_{target_id}"
    else:
        room_type = "user"
        try:
            recv_id = int(target_id)
        except ValueError:
            recv_row = sqlq("SELECT id FROM users WHERE username = %s", (target_id,), "one")
            recv_id = recv_row[0] if recv_row else 0
        ids = sorted([int(my_id), int(recv_id)])
        room_id = f"{ids[0]}_{ids[1]}"
        room = room_id

    sqlq("DELETE FROM pinned_messages WHERE room_type = %s AND room_id = %s", (room_type, room_id), "none")
    socketio.emit("pin_update", {"msg_id": None}, room=room)

    return jsonify({"code": 200})


@app.route("/chat/pinned/<target_type>/<target_id>", methods=["GET"])
@app.route("/pinned/<target_type>/<target_id>", methods=["GET"])
def get_pinned_message(target_type, target_id):
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id[0]

    if target_type == "group":
        room_type = "group"
        room_id = str(target_id)
    else:
        room_type = "user"
        try:
            recv_id = int(target_id)
        except ValueError:
            recv_row = sqlq("SELECT id FROM users WHERE username = %s", (target_id,), "one")
            recv_id = recv_row[0] if recv_row else 0
        ids = sorted([int(my_id), int(recv_id)])
        room_id = f"{ids[0]}_{ids[1]}"

    row = sqlq("""
        SELECT pm.message_id, m.content, u.username
        FROM pinned_messages pm
        JOIN msg m ON m.msg_id = pm.message_id
        LEFT JOIN users u ON u.id = m.id
        WHERE pm.room_type = %s AND pm.room_id = %s
    """, (room_type, room_id), "one")

    if row:
        return jsonify({"code": 200, "pinned": {"msg_id": row[0], "content": row[1], "sender_name": row[2] or "Unbekannt"}})
    return jsonify({"code": 200, "pinned": None})


# ------------------------ Tenor GIF Proxy API ------------------------
@app.route("/api/gifs/trending", methods=["GET"])
@app.route("/chat/api/gifs/trending", methods=["GET"])
def get_trending_gifs():
    import urllib.request
    import urllib.parse

    api_key = get_conf("tenor_api_key", "LIVDSRZULEUB")
    limit = request.args.get("limit", 20, type=int)

    url = f"https://tenor.googleapis.com/v2/featured?key={api_key}&client_key=eddi_chat&limit={limit}&media_filter=gif,tinygif"
    try:
        req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
        with urllib.request.urlopen(req, timeout=5) as resp:
            data = json.loads(resp.read().decode("utf-8"))
            results = data.get("results", [])
            gifs = []
            for item in results:
                media = item.get("media_formats", {})
                gif_url = media.get("gif", {}).get("url") or media.get("tinygif", {}).get("url")
                preview_url = media.get("tinygif", {}).get("url") or gif_url
                if gif_url:
                    gifs.append({
                        "id": item.get("id"),
                        "title": item.get("content_description", "GIF"),
                        "url": gif_url,
                        "preview": preview_url
                    })
            return jsonify({"code": 200, "gifs": gifs})
    except Exception as e:
        print("Tenor API Error:", e)
        return jsonify({"code": 500, "error": "GIFs konnten nicht von Tenor geladen werden", "gifs": []})


@app.route("/api/gifs/search", methods=["GET"])
@app.route("/chat/api/gifs/search", methods=["GET"])
def search_gifs():
    import urllib.request
    import urllib.parse

    q = request.args.get("q", "").strip()
    if not q:
        return get_trending_gifs()

    api_key = get_conf("tenor_api_key", "LIVDSRZULEUB")
    limit = request.args.get("limit", 20, type=int)

    encoded_q = urllib.parse.quote(q)
    url = f"https://tenor.googleapis.com/v2/search?q={encoded_q}&key={api_key}&client_key=eddi_chat&limit={limit}&media_filter=gif,tinygif"
    try:
        req = urllib.request.Request(url, headers={"User-Agent": "Mozilla/5.0"})
        with urllib.request.urlopen(req, timeout=5) as resp:
            data = json.loads(resp.read().decode("utf-8"))
            results = data.get("results", [])
            gifs = []
            for item in results:
                media = item.get("media_formats", {})
                gif_url = media.get("gif", {}).get("url") or media.get("tinygif", {}).get("url")
                preview_url = media.get("tinygif", {}).get("url") or gif_url
                if gif_url:
                    gifs.append({
                        "id": item.get("id"),
                        "title": item.get("content_description", "GIF"),
                        "url": gif_url,
                        "preview": preview_url
                    })
            return jsonify({"code": 200, "gifs": gifs})
    except Exception as e:
        print("Tenor Search API Error:", e)
        return jsonify({"code": 500, "error": "GIFs konnten nicht durchsucht werden", "gifs": []})


# ------------------------ History Pagination Route ------------------------
@app.route("/chat/messages/history", methods=["GET"])
@app.route("/messages/history", methods=["GET"])
def get_message_history():
    user = session.get("user")
    if not user:
        return jsonify({"code": 401, "error": "Nicht eingeloggt"}), 401

    my_id = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
    if not my_id:
        return jsonify({"code": 401, "error": "Ungültiger Benutzer"}), 401
    my_id = my_id[0]

    target_type = request.args.get("target_type", "user")
    target_id = request.args.get("target_id")
    before_id = request.args.get("before_id", type=int)
    limit = request.args.get("limit", 50, type=int)

    if not target_id or before_id is None:
        return jsonify({"code": 400, "error": "Fehlende Parameter"}), 400

    if target_type == "group":
        raw_rows = sqlq("""
            SELECT m.msg_id, m.content, m.time, COALESCE(m.edited,0), m.status, m.file_url, m.file_type, u.username, m.id, m.reply_to_id, m.deleted_for_users
            FROM msg m
            LEFT JOIN users u ON u.id = m.id
            WHERE m.conv = %s AND m.msg_id < %s
            ORDER BY m.msg_id DESC LIMIT %s
        """, (int(target_id), before_id, limit), "all")
    else:
        try:
            recv_id = int(target_id)
        except ValueError:
            recv_row = sqlq("SELECT id FROM users WHERE username = %s", (target_id,), "one")
            recv_id = recv_row[0] if recv_row else 0

        raw_rows = sqlq("""
            SELECT m.msg_id, m.content, m.time, COALESCE(m.edited,0), m.status, m.file_url, m.file_type, u.username, m.id, m.reply_to_id, m.deleted_for_users
            FROM msg m
            LEFT JOIN users u ON u.id = m.id
            WHERE ((m.id = %s AND m.receiver = %s) OR (m.id = %s AND m.receiver = %s))
              AND (m.conv IS NULL OR m.conv = 0)
              AND m.msg_id < %s
            ORDER BY m.msg_id DESC LIMIT %s
        """, (my_id, recv_id, recv_id, my_id, before_id, limit), "all")

    messages = []
    has_more = False
    if raw_rows:
        if len(raw_rows) == limit:
            has_more = True
        for r in raw_rows:
            if r[1] and (r[1] == '🗑️ Nachricht gelöscht' or str(r[1]).startswith('🗑️')):
                continue
            del_users = []
            if r[10]:
                try:
                    del_users = json.loads(r[10])
                except Exception:
                    del_users = []
            if my_id not in del_users and str(my_id) not in del_users:
                messages.append({
                    "msg_id": r[0],
                    "content": r[1],
                    "time": str(r[2]),
                    "edited": r[3],
                    "status": r[4],
                    "file_url": r[5],
                    "file_type": r[6],
                    "username": r[7],
                    "sender_id": r[8],
                    "reply_to_id": r[9]
                })
        messages.reverse()

    return jsonify({"code": 200, "messages": messages, "has_more": has_more})


# ------------------------ File Upload Endpoint ------------------------
@app.route("/api/upload", methods=["POST"])
def handle_api_upload():
    if "user" not in session or not session.get("auth"):

        return jsonify({"code": 401, "error": "Nicht authentifiziert"}), 401

    if "file" not in request.files:
        return jsonify({"code": 400, "error": "Keine Datei gesendet"}), 400

    file = request.files["file"]
    if file.filename == "":
        return jsonify({"code": 400, "error": "Leerer Dateiname"}), 400

    filename = secure_filename(file.filename)
    if not filename:
        filename = f"upload_{int(time.time())}.dat"


    upload_dir = os.path.join(app.root_path, "static", "uploads")
    os.makedirs(upload_dir, exist_ok=True)

    unique_name = f"{uuid.uuid4().hex[:8]}_{filename}"
    file_path = os.path.join(upload_dir, unique_name)
    file.save(file_path)

    file_url = f"/chat/static/uploads/{unique_name}"


    ext = os.path.splitext(filename)[1].lower()
    if ext in [".png", ".jpg", ".jpeg", ".gif", ".webp"]:
        msg_type = "image"
    elif ext in [".mp3", ".ogg", ".wav", ".m4a", ".webm"]:
        msg_type = "audio"
    else:
        msg_type = "document"

    return jsonify({
        "code": 200,
        "file_url": file_url,
        "filename": filename,
        "msg_type": msg_type
    })


# ------------------------ Group Creation Endpoint ------------------------
@app.route("/api/groups/create", methods=["POST"])
def handle_api_create_group():
    if "user" not in session or not session.get("auth"):

        return jsonify({"code": 401, "error": "Nicht authentifiziert"}), 401

    data = request.get_json(silent=True) or {}
    group_name = data.get("name", "").strip()
    member_ids = data.get("members", [])

    if not group_name:
        return jsonify({"code": 400, "error": "Gruppenname erforderlich"}), 400

    user_name = session.get("user")
    owner_row = sqlq("SELECT id FROM users WHERE username = %s", (user_name,), "one")
    if not owner_row:
        return jsonify({"code": 401, "error": "User nicht gefunden"}), 401

    owner_id = owner_row[0]

    if owner_id not in member_ids:
        member_ids.append(owner_id)

    members_json = json.dumps(member_ids)

    sqlq("INSERT INTO `groups` (name, owner_id, members) VALUES (%s, %s, %s)",
         (group_name, owner_id, members_json), "none")

    group_row = sqlq("SELECT id FROM `groups` WHERE name = %s AND owner_id = %s ORDER BY id DESC LIMIT 1",
                     (group_name, owner_id), "one")

    group_id = group_row[0] if group_row else 0

    return jsonify({"code": 200, "group_id": group_id, "name": group_name})


# ------------------------ Socket.IO Events ------------------------

@socketio.on('join')
def on_join(data):
    print(f"[SOCKET-JOIN] data={data}, session_user={session.get('user')}")
    token = data.get("token")
    my_id = None
    try:
        if token:
            from backend import verify_jwt_token
            my_id, my_name = verify_jwt_token(token)
        else:
            my_name = session.get("user")
            if not my_name:
                print("[SOCKET-JOIN] ABBRUCH: kein session user")
                return
            row = sqlq("SELECT id FROM users WHERE username = %s", (my_name,), "one")
            if row:
                my_id = row[0]

        if my_id is None:
            print("[SOCKET-JOIN] ABBRUCH: my_id ist None")
            return

        group_id = data.get("group_id")
        if group_id:
            try:
                gid = int(group_id)
                join_room(f"group_{gid}")
                print(f"[SOCKET-JOIN] joined group_{gid}")
            except Exception:
                pass
            return

        target_id = data.get("receiver")
        if not target_id:
            print("[SOCKET-JOIN] ABBRUCH: kein receiver in data")
            return

        try:
            try:
                target_user_id = int(target_id)
            except ValueError:
                target_row = sqlq("SELECT id FROM users WHERE username = %s", (str(target_id),), "one")
                if target_row:
                    target_user_id = target_row[0]
                else:
                    print(f"[SOCKET-JOIN] ABBRUCH: receiver '{target_id}' nicht gefunden")
                    return

            ids = sorted([int(my_id), int(target_user_id)])
            room = f"{ids[0]}_{ids[1]}"
            join_room(room)
            print(f"[SOCKET-JOIN] user {my_id} joined room {room}")
        except ValueError:
            pass

    except Exception as e:
        print(f"[SOCKET-JOIN] FEHLER: {e}")


@socketio.on_error_default
def default_error_handler(e):
    pass

@socketio.on('connect')
def on_connect():
    user = session.get("user")
    print(f"[SOCKET-CONNECT] user={user}")
    if user:
        online_users.add(user)
        row = sqlq("SELECT id FROM users WHERE username = %s", (user,), "one")
        if row:
            join_room(f"user_{row[0]}")
            print(f"[SOCKET-CONNECT] joined user_{row[0]}")
    else:
        print("[SOCKET-CONNECT] WARNUNG: kein session user!")
    socketio.emit("online_update", {"users": list(online_users)})

@socketio.on('disconnect')
def on_disconnect():
    user = session.get("user")
    if user and user in online_users:
        online_users.discard(user)
        socketio.emit("online_update", {"users": list(online_users)})


if __name__ == "__main__":

    set_config()
    socketio.run(
        app,
        host="0.0.0.0",
        port=7000,
        debug=True,
        allow_unsafe_werkzeug=True
    )


#         ssl_context=("https/cert.pem", "https/key.pem"),