"""
MCLogger – Panel (MC-Daten)
Zeigt die Minecraft-Logdaten der Gruppe an.
Die Datenbankverbindung kommt aus den verschlüsselten Gruppen-Credentials.
"""
from functools import wraps
from datetime import datetime
from flask import Blueprint, render_template, request, redirect, url_for, session, flash, jsonify, abort
import pymysql
import pymysql.cursors
import panel_db as pdb

panel = Blueprint("panel", __name__)


# ─────────────────────────────────────────────────────────────
# Hilfsfunktionen
# ─────────────────────────────────────────────────────────────

def login_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if not session.get("user_id"):
            return redirect(url_for("auth.login"))
        if session.get("is_site_admin") and not session.get("group_id"):
            return redirect(url_for("site_admin.dashboard"))
        if not session.get("group_id"):
            return redirect(url_for("auth.login"))
        return f(*args, **kwargs)
    return decorated


def perm_required(perm):
    def decorator(f):
        @wraps(f)
        def wrapped(*args, **kwargs):
            if session.get("is_site_admin") or session.get("role") == "admin":
                return f(*args, **kwargs)
            perms = session.get("permissions", {})
            if not perms.get(perm, False):
                flash("You do not have permission to view this page.", "danger")
                return redirect(url_for("panel.dashboard"))
            return f(*args, **kwargs)
        return wrapped
    return decorator


def get_mc_db():
    """Liefert eine Datenbankverbindung zur MC-Datenbank der aktuellen Gruppe."""
    group_id = session.get("group_id")
    if not group_id:
        abort(403)
    creds = pdb.get_group_db_creds(group_id)
    if not creds:
        abort(503)
    return pymysql.connect(
        host=creds["host"],
        port=creds["port"],
        user=creds["user"],
        password=creds["password"],
        database=creds["database"],
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
        connect_timeout=10,
    )


def query(sql, args=None, fetchone=False):
    conn = get_mc_db()
    try:
        with conn.cursor() as cur:
            cur.execute(sql, args or ())
            return cur.fetchone() if fetchone else cur.fetchall()
    finally:
        conn.close()


def query_paged(sql, count_sql, args=None, page=1, per_page=50):
    args = args or ()
    total_row = query(count_sql, args, fetchone=True)
    total = list(total_row.values())[0] if total_row else 0
    pages = max(1, (total + per_page - 1) // per_page)
    offset = (page - 1) * per_page
    rows = query(sql + f" LIMIT {per_page} OFFSET {offset}", args)
    return rows, total, pages


# ─────────────────────────────────────────────────────────────
# Fehler-Handler wenn DB nicht konfiguriert
# ─────────────────────────────────────────────────────────────

@panel.errorhandler(503)
def no_db(e):
    return render_template("panel/no_db.html"), 503


# ─────────────────────────────────────────────────────────────
# Dashboard
# ─────────────────────────────────────────────────────────────

@panel.route("/")
@login_required
@perm_required("view_dashboard")
def dashboard():
    group_id = session["group_id"]
    if not pdb.has_db_configured(group_id):
        return render_template("panel/no_db.html")
    try:
        stats = {
            "players_total":       query("SELECT COUNT(*) AS c FROM players",        fetchone=True)["c"],
            "sessions_today":      query("SELECT COUNT(*) AS c FROM player_sessions WHERE login_time >= CURDATE()", fetchone=True)["c"],
            "chat_today":          query("SELECT COUNT(*) AS c FROM player_chat     WHERE timestamp >= CURDATE()", fetchone=True)["c"],
            "commands_today":      query("SELECT COUNT(*) AS c FROM player_commands WHERE timestamp >= CURDATE()", fetchone=True)["c"],
            "blocks_today":        query("SELECT COUNT(*) AS c FROM block_events    WHERE timestamp >= CURDATE()", fetchone=True)["c"],
            "deaths_today":        query("SELECT COUNT(*) AS c FROM player_deaths   WHERE timestamp >= CURDATE()", fetchone=True)["c"],
            "entity_events_today": query("SELECT COUNT(*) AS c FROM entity_events   WHERE timestamp >= CURDATE()", fetchone=True)["c"],
            "proxy_events_today":  query("SELECT COUNT(*) AS c FROM proxy_events    WHERE timestamp >= CURDATE()", fetchone=True)["c"],
        }
        online = query("""
            SELECT p.uuid AS player_uuid, p.username AS player_name, ps.server_name, ps.login_time
            FROM player_sessions ps
            JOIN players p ON p.uuid = ps.player_uuid
            WHERE ps.logout_time IS NULL
            ORDER BY ps.login_time DESC
        """)
        top_players = query("""
            SELECT username, total_playtime_sec
            FROM players ORDER BY total_playtime_sec DESC LIMIT 10
        """)
        death_causes = query("""
            SELECT cause, COUNT(*) AS cnt FROM player_deaths
            WHERE timestamp >= NOW() - INTERVAL 7 DAY
            GROUP BY cause ORDER BY cnt DESC LIMIT 8
        """)
        server_events = query("""
            SELECT timestamp, event_type, server_name, message
            FROM server_events
            WHERE timestamp >= NOW() - INTERVAL 24 HOUR
            ORDER BY timestamp DESC LIMIT 20
        """)
        block_chart = query("""
            SELECT DATE(timestamp) AS day, COUNT(*) AS cnt
            FROM block_events
            WHERE timestamp >= NOW() - INTERVAL 7 DAY
            GROUP BY DATE(timestamp) ORDER BY day
        """)
        recent = query("""
            SELECT * FROM v_recent_activity LIMIT 50
        """)
    except Exception:
        panel.logger.exception("Database error while rendering dashboard")
        flash("Database query failed. Please contact an administrator.", "danger")
        return render_template("panel/no_db.html")

    return render_template("panel/dashboard.html",
        stats=stats, online=online, top_players=top_players,
        death_causes=death_causes, server_events=server_events,
        block_chart=block_chart, recent=recent)


# ─────────────────────────────────────────────────────────────
# Spieler
# ─────────────────────────────────────────────────────────────

@panel.route("/players")
@login_required
@perm_required("view_players")
def players():
    search = request.args.get("q", "")
    page   = max(1, request.args.get("page", 1, type=int))
    if search:
        base = "FROM players WHERE username LIKE %s"
        args = (f"%{search}%",)
    else:
        base = "FROM players WHERE 1"
        args = ()
    rows, total, pages = query_paged(
        f"SELECT * {base} ORDER BY last_seen DESC",
        f"SELECT COUNT(*) AS c {base}", args, page)
    return render_template("panel/players.html",
        players=rows, total=total, pages=pages, page=page, search=search)


@panel.route("/players/<uuid>")
@login_required
@perm_required("view_players")
def player_detail(uuid):
    player = query("SELECT * FROM players WHERE uuid = %s", (uuid,), fetchone=True)
    if not player:
        flash("Player not found.", "danger")
        return redirect(url_for("panel.players"))
    perms = session.get("permissions", {})
    is_admin = session.get("is_site_admin") or session.get("role") == "admin"
    return render_template("panel/player_detail.html",
        player=player,
        sessions  = query("SELECT * FROM player_sessions  WHERE player_uuid=%s ORDER BY login_time  DESC LIMIT 20", (uuid,)),
        chat      = query("SELECT * FROM player_chat      WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 50", (uuid,)) if (is_admin or perms.get("view_chat"))     else [],
        commands  = query("SELECT * FROM player_commands  WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 50", (uuid,)) if (is_admin or perms.get("view_commands"))  else [],
        deaths    = query("SELECT * FROM player_deaths    WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 20", (uuid,)) if (is_admin or perms.get("view_deaths"))    else [],
        teleports = query("SELECT * FROM player_teleports WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 20", (uuid,)),
        stats     = query("SELECT * FROM player_stats     WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 30", (uuid,)),
        proxy_events = query("SELECT * FROM proxy_events  WHERE player_uuid=%s ORDER BY timestamp   DESC LIMIT 30", (uuid,)) if (is_admin or perms.get("view_proxy"))    else [],
    )


# ─────────────────────────────────────────────────────────────
# Sessions
# ─────────────────────────────────────────────────────────────

@panel.route("/sessions")
@login_required
@perm_required("view_sessions")
def sessions():
    page   = max(1, request.args.get("page", 1, type=int))
    player = request.args.get("player", "")
    server = request.args.get("server", "")
    conditions, args = [], []
    if player: conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    if server: conditions.append("server_name = %s");    args.append(server)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM player_sessions {where} ORDER BY login_time DESC",
        f"SELECT COUNT(*) AS c FROM player_sessions {where}", tuple(args), page)
    servers = [r["server_name"] for r in query("SELECT DISTINCT server_name FROM player_sessions ORDER BY server_name")]
    return render_template("panel/sessions.html",
        rows=rows, total=total, pages=pages, page=page,
        player=player, server=server, servers=servers)


# ─────────────────────────────────────────────────────────────
# Chat
# ─────────────────────────────────────────────────────────────

@panel.route("/chat")
@login_required
@perm_required("view_chat")
def chat():
    page = max(1, request.args.get("page", 1, type=int))
    search = request.args.get("q", ""); server = request.args.get("server", "")
    date_from = request.args.get("from", ""); date_to = request.args.get("to", "")
    conditions, args = [], []
    if search:     conditions.append("message LIKE %s");    args.append(f"%{search}%")
    if server:     conditions.append("server_name = %s");   args.append(server)
    if date_from:  conditions.append("timestamp >= %s");    args.append(date_from)
    if date_to:    conditions.append("timestamp <= %s");    args.append(date_to + " 23:59:59")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM player_chat {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM player_chat {where}", tuple(args), page)
    servers = [r["server_name"] for r in query("SELECT DISTINCT server_name FROM player_chat ORDER BY server_name")]
    return render_template("panel/chat.html",
        rows=rows, total=total, pages=pages, page=page,
        search=search, server=server, servers=servers, date_from=date_from, date_to=date_to)


# ─────────────────────────────────────────────────────────────
# Commands
# ─────────────────────────────────────────────────────────────

@panel.route("/commands")
@login_required
@perm_required("view_commands")
def commands():
    page = max(1, request.args.get("page", 1, type=int))
    player = request.args.get("player", ""); search = request.args.get("q", ""); server = request.args.get("server", "")
    conditions, args = [], []
    if player: conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    if search: conditions.append("command LIKE %s");     args.append(f"%{search}%")
    if server: conditions.append("server_name = %s");    args.append(server)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM player_commands {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM player_commands {where}", tuple(args), page)
    servers = [r["server_name"] for r in query("SELECT DISTINCT server_name FROM player_commands ORDER BY server_name")]
    return render_template("panel/commands.html",
        rows=rows, total=total, pages=pages, page=page,
        player=player, search=search, server=server, servers=servers)


# ─────────────────────────────────────────────────────────────
# Tode
# ─────────────────────────────────────────────────────────────

@panel.route("/deaths")
@login_required
@perm_required("view_deaths")
def deaths():
    page = max(1, request.args.get("page", 1, type=int))
    player = request.args.get("player", ""); cause = request.args.get("cause", "")
    conditions, args = [], []
    if player: conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    if cause:  conditions.append("cause = %s");          args.append(cause)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM player_deaths {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM player_deaths {where}", tuple(args), page)
    causes = [r["cause"] for r in query("SELECT DISTINCT cause FROM player_deaths ORDER BY cause")]
    return render_template("panel/deaths.html",
        rows=rows, total=total, pages=pages, page=page, player=player, cause=cause, causes=causes)


# ─────────────────────────────────────────────────────────────
# Block-Events
# ─────────────────────────────────────────────────────────────

@panel.route("/blocks")
@login_required
@perm_required("view_blocks")
def blocks():
    page = max(1, request.args.get("page", 1, type=int))
    event_type = request.args.get("type", ""); player = request.args.get("player", "")
    world = request.args.get("world", ""); server = request.args.get("server", ""); block = request.args.get("block", "")
    conditions, args = [], []
    if event_type: conditions.append("event_type = %s");    args.append(event_type)
    if player:     conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    if world:      conditions.append("world = %s");          args.append(world)
    if server:     conditions.append("server_name = %s");    args.append(server)
    if block:      conditions.append("block_type LIKE %s");  args.append(f"%{block}%")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM block_events {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM block_events {where}", tuple(args), page)
    worlds  = [r["world"]       for r in query("SELECT DISTINCT world       FROM block_events ORDER BY world")]
    servers = [r["server_name"] for r in query("SELECT DISTINCT server_name FROM block_events ORDER BY server_name")]
    return render_template("panel/blocks.html",
        rows=rows, total=total, pages=pages, page=page,
        event_type=event_type, player=player, world=world, server=server, block=block,
        worlds=worlds, servers=servers)


# ─────────────────────────────────────────────────────────────
# Proxy-Events
# ─────────────────────────────────────────────────────────────

@panel.route("/proxy")
@login_required
@perm_required("view_proxy")
def proxy():
    page = max(1, request.args.get("page", 1, type=int))
    event_type = request.args.get("type", ""); player = request.args.get("player", "")
    conditions, args = [], []
    if event_type: conditions.append("event_type = %s");    args.append(event_type)
    if player:     conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM proxy_events {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM proxy_events {where}", tuple(args), page)
    return render_template("panel/proxy.html",
        rows=rows, total=total, pages=pages, page=page, event_type=event_type, player=player)


# ─────────────────────────────────────────────────────────────
# Server-Events
# ─────────────────────────────────────────────────────────────

@panel.route("/server-events")
@login_required
@perm_required("view_server_events")
def server_events():
    page = max(1, request.args.get("page", 1, type=int))
    server = request.args.get("server", ""); etype = request.args.get("type", "")
    conditions, args = [], []
    if server: conditions.append("server_name = %s"); args.append(server)
    if etype:  conditions.append("event_type = %s");  args.append(etype)
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM server_events {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM server_events {where}", tuple(args), page)
    servers = [r["server_name"] for r in query("SELECT DISTINCT server_name FROM server_events ORDER BY server_name")]
    etypes  = [r["event_type"]  for r in query("SELECT DISTINCT event_type  FROM server_events ORDER BY event_type")]
    return render_template("panel/server_events.html",
        rows=rows, total=total, pages=pages, page=page,
        server=server, etype=etype, servers=servers, etypes=etypes)


# ─────────────────────────────────────────────────────────────
# Berechtigungen (plugin_events)
# ─────────────────────────────────────────────────────────────

@panel.route("/perms")
@login_required
@perm_required("view_perms")
def perms():
    page = max(1, request.args.get("page", 1, type=int))
    player = request.args.get("player", ""); plugin_filter = request.args.get("plugin", ""); etype = request.args.get("type", "")
    conditions, args = [], []
    if player:        conditions.append("player_name LIKE %s"); args.append(f"%{player}%")
    if plugin_filter: conditions.append("plugin_name = %s");    args.append(plugin_filter)
    if etype:         conditions.append("event_type LIKE %s");  args.append(f"%{etype}%")
    where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
    rows, total, pages = query_paged(
        f"SELECT * FROM plugin_events {where} ORDER BY timestamp DESC",
        f"SELECT COUNT(*) AS c FROM plugin_events {where}", tuple(args), page)
    plugins = [r["plugin_name"] for r in query("SELECT DISTINCT plugin_name FROM plugin_events ORDER BY plugin_name")]
    return render_template("panel/perms.html",
        rows=rows, total=total, pages=pages, page=page,
        player=player, plugin_filter=plugin_filter, etype=etype, plugins=plugins)


# ─────────────────────────────────────────────────────────────
# API
# ─────────────────────────────────────────────────────────────

@panel.route("/api/online")
@login_required
def api_online():
    rows = query("""
        SELECT p.uuid AS player_uuid, p.username AS player_name, ps.server_name, ps.login_time
        FROM player_sessions ps
        JOIN players p ON p.uuid = ps.player_uuid
        WHERE ps.logout_time IS NULL ORDER BY ps.login_time DESC
    """)
    return jsonify([dict(r) for r in rows])


@panel.route("/api/stats")
@login_required
def api_stats():
    return jsonify({
        "players_online": query("SELECT COUNT(*) AS c FROM player_sessions WHERE logout_time IS NULL", fetchone=True)["c"],
    })
