import math
from flask import 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("/uploaded-images")
@admin_login_required
def uploaded_images_page():
    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)

    # Count distinct days
    cursor.execute("""
        SELECT COUNT(DISTINCT day) AS total
        FROM day_events
        WHERE event_name IS NOT NULL AND TRIM(event_name) != ''
    """)
    total_days  = (cursor.fetchone() or {}).get("total", 0)
    total_pages = math.ceil(total_days / limit) if total_days > 0 else 1

    # Get distinct days with pagination
    cursor.execute("""
        SELECT DISTINCT day
        FROM day_events
        WHERE event_name IS NOT NULL AND TRIM(event_name) != ''
        ORDER BY day ASC
        LIMIT %s OFFSET %s
    """, (limit, offset))
    days = [r["day"] for r in cursor.fetchall()]

    days_data = []
    for day_val in days:
        # Get all events for this day
        cursor.execute("""
            SELECT id, event_name
            FROM day_events
            WHERE day = %s AND event_name IS NOT NULL AND TRIM(event_name) != ''
            ORDER BY id ASC
        """, (day_val,))
        events = cursor.fetchall()

        events_data = []
        for event in events:
            # Get images linked to this day_event_id
            cursor.execute("""
                SELECT id, original_url, processed_url, uploaded_at
                FROM event_images
                WHERE day_event_id = %s
                ORDER BY uploaded_at ASC
            """, (event["id"],))
            raw_images = cursor.fetchall()

            images = []
            for img in raw_images:
                # Find ALL matched users for this image via images_array
                cursor.execute("""
                    SELECT
                        u.id           AS user_id,
                        u.s3_image_url AS user_photo
                    FROM images_array ia
                    JOIN users u ON u.id = ia.user_id
                    WHERE JSON_CONTAINS(ia.image_ids, CAST(%s AS JSON))
                """, (img["id"],))
                matched_users = cursor.fetchall()

                images.append({
                    "id":            img["id"],
                    "original_url":  img["original_url"],
                    "processed_url": img["processed_url"],
                    "uploaded_at":   img["uploaded_at"],
                    "matched_users": matched_users,  # list of all matched users
                })

            events_data.append({
                "id":     event["id"],
                "name":   event["event_name"],
                "images": images,
                "count":  len(images),
            })

        # Extract day number from "day1", "day2", etc.
        day_num = ''.join(filter(str.isdigit, str(day_val))) or day_val

        days_data.append({
            "day_val":     day_val,
            "day_num":     day_num,
            "events":      events_data,
            "total_images": sum(e["count"] for e in events_data),
        })

    cursor.close()
    conn.close()

    return render_template(
        "uploaded_images.html",
        days_data   = days_data,
        page        = page,
        limit       = limit,
        total_days  = total_days,
        total_pages = total_pages,
    )