import json
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


@admin_blueprint.route("/matched-images")
@admin_login_required
def matched_images_page():
    """Display all users with their matched event images."""
    limit = request.args.get("limit", 10, type=int)
    page  = request.args.get("page",  1,  type=int)

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

    offset = (page - 1) * limit

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

    # Total users who have at least one match
    cursor.execute("""
        SELECT COUNT(DISTINCT user_id) AS total FROM images_array
    """)
    total = (cursor.fetchone() or {}).get("total", 0)
    total_pages = math.ceil(total / limit) if total > 0 else 1

    # Fetch users with their image arrays
    cursor.execute("""
        SELECT
            ia.user_id,
            ia.image_ids,
            u.name,
            u.email,
            u.s3_image_url AS user_photo
        FROM images_array ia
        JOIN users u ON u.id = ia.user_id
        ORDER BY ia.user_id ASC
        LIMIT %s OFFSET %s
    """, (limit, offset))
    rows = cursor.fetchall()

    # Parse image_ids JSON and fetch event image URLs
    result = []
    for row in rows:
        ids = row["image_ids"]
        if isinstance(ids, str):
            try:
                ids = json.loads(ids)
            except Exception:
                ids = []

        event_images = []
        if ids:
            placeholders = ",".join(["%s"] * len(ids))
            cursor.execute(f"""
                SELECT id, processed_url, original_url
                FROM event_images
                WHERE id IN ({placeholders})
                ORDER BY id ASC
            """, tuple(ids))
            event_images = cursor.fetchall()

        result.append({
            "user_id":    row["user_id"],
            "name":       row["name"],
            "email":      row["email"],
            "user_photo": row["user_photo"],
            "images":     event_images,
        })

    cursor.close()
    conn.close()

    return render_template(
        "matched_images.html",
        rows=result,
        page=page,
        limit=limit,
        total=total,
        total_pages=total_pages,
    )