import math

from flask import jsonify, render_template, request

from app.admin import admin_blueprint
from app.admin.auth import admin_login_required
from config.db_config import get_db_connection


def _get_db():
    return get_db_connection()


# ── Page ──────────────────────────────────────────────────────────────────────

@admin_blueprint.route("/consent")
@admin_login_required
def consent_page():
    limit  = request.args.get("limit",  10,   type=int)
    page   = request.args.get("page",   1,    type=int)
    search = (request.args.get("search") or "").strip()

    if limit not in [10, 25, 50]:
        limit = 10

    offset = (page - 1) * limit

    conn   = _get_db()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT COUNT(*) AS c FROM visitors_registration")
    total_visitors = (cursor.fetchone() or {}).get("c", 0)

    cursor.execute("SELECT COUNT(*) AS c FROM consent_logs WHERE agreed = 1")
    consented_users = (cursor.fetchone() or {}).get("c", 0)

    cursor.execute("SELECT COUNT(*) AS c FROM users")
    total_users = (cursor.fetchone() or {}).get("c", 0)

    # Latest active message per role
    cursor.execute("""
        SELECT role, consent_msg
        FROM consent_messages
        WHERE is_active = 1
        GROUP BY role
        ORDER BY MAX(updated_at) DESC
    """)
    msgs = {r["role"]: r["consent_msg"] for r in cursor.fetchall()}

    search_sql = ""
    params     = []
    if search:
        pattern    = f"%{search}%"
        search_sql = "WHERE (username LIKE %s OR user_email LIKE %s)"
        params     = [pattern, pattern]

    cursor.execute(f"SELECT COUNT(*) AS c FROM consent_logs {search_sql}", params)
    total_logs  = (cursor.fetchone() or {}).get("c", 0)
    total_pages = math.ceil(total_logs / limit) if total_logs > 0 else 1

    cursor.execute(f"""
        SELECT id, username, user_email, agreed, agreed_at
        FROM consent_logs
        {search_sql}
        ORDER BY agreed_at DESC
        LIMIT %s OFFSET %s
    """, params + [limit, offset])
    logs = cursor.fetchall()

    cursor.close()
    conn.close()

    return render_template(
        "consent.html",
        total_visitors  = total_visitors,
        consented_users = consented_users,
        total_users     = total_users,
        consent_msgs    = msgs,
        logs            = logs,
        total_logs      = total_logs,
        total_pages     = total_pages,
        page            = page,
        limit           = limit,
        search          = search,
    )


@admin_blueprint.route("/api/consent/message/<role>", methods=["GET"])
@admin_login_required
def api_get_consent(role):
    if role not in ("visitor", "admin", "photographer"):
        return jsonify({"error": "Invalid role"}), 400

    conn   = _get_db()
    cursor = conn.cursor(dictionary=True)
    cursor.execute("""
        SELECT consent_msg FROM consent_messages
        WHERE role = %s AND is_active = 1
        ORDER BY updated_at DESC LIMIT 1
    """, (role,))
    row = cursor.fetchone()
    cursor.close()
    conn.close()

    return jsonify({"role": role, "consent_msg": row["consent_msg"] if row else ""})


@admin_blueprint.route("/api/consent/message/<role>", methods=["POST"])
@admin_login_required
def api_save_consent(role):
    if role not in ("visitor", "admin", "photographer"):
        return jsonify({"error": "Invalid role"}), 400

    payload = request.get_json(silent=True) or {}
    msg     = (payload.get("consent_msg") or "").strip()

    if not msg:
        return jsonify({"error": "Consent message cannot be empty"}), 400

    conn   = _get_db()
    cursor = conn.cursor()

    # Get the ONE existing row for this role (most recent)
    cursor.execute(
        "SELECT id FROM consent_messages WHERE role = %s ORDER BY updated_at DESC LIMIT 1",
        (role,)
    )
    existing = cursor.fetchone()

    if existing:
        row_id = existing[0]
        # Update the existing row
        cursor.execute(
            "UPDATE consent_messages SET consent_msg = %s, is_active = 1, updated_at = CURRENT_TIMESTAMP WHERE id = %s",
            (msg, row_id)
        )
        # Clean up any duplicate rows for same role
        cursor.execute(
            "DELETE FROM consent_messages WHERE role = %s AND id != %s",
            (role, row_id)
        )
    else:
        # No row yet — insert once
        cursor.execute(
            "INSERT INTO consent_messages (role, consent_msg, is_active) VALUES (%s, %s, 1)",
            (role, msg)
        )

    conn.commit()
    cursor.close()
    conn.close()

    return jsonify({"success": True, "role": role, "message": f"Consent message for {role} updated."})