from models.db import db_cursor


# ── Event Config ──────────────────────────────────────────────────────────────

def get_event_config():
    """Fetch the event configuration (only 1 row expected)."""
    query = "SELECT id, event_name, event_subtitle, venue, start_date, end_date FROM events LIMIT 1"
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchone()


def get_latest_event():
    """Fetch the latest event from the events table."""
    query = "SELECT id, event_name, event_subtitle, venue, start_date, end_date FROM events ORDER BY id DESC LIMIT 1"
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchone()


def get_events():
    """Fetch all events from the events table."""
    query = "SELECT id, event_name, event_subtitle, venue, start_date, end_date FROM events ORDER BY start_date ASC"
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()


def update_event_config(event_name, event_subtitle, venue, start_date, end_date):
    """Insert or update the event configuration."""
    existing = get_event_config()
    with db_cursor() as (_, cursor):
        if existing:
            cursor.execute("""
                UPDATE events
                SET event_name = %s, event_subtitle = %s, venue = %s, start_date = %s, end_date = %s
                WHERE id = %s
            """, (event_name, event_subtitle, venue, start_date, end_date, existing["id"]))
        else:
            cursor.execute("""
                INSERT INTO events (event_name, event_subtitle, venue, start_date, end_date)
                VALUES (%s, %s, %s, %s, %s)
            """, (event_name, event_subtitle, venue, start_date, end_date))


# ── Day Events ────────────────────────────────────────────────────────────────

def get_day_events_grouped():
    """Fetch all day events grouped by day, returning id and event_name."""
    query = """
        SELECT id, day, event_name
        FROM day_events
        WHERE day IS NOT NULL AND event_name != ''
        ORDER BY day, id
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        rows = cursor.fetchall()
        grouped = {}
        for row in rows:
            day = row["day"]
            if day not in grouped:
                grouped[day] = []
            grouped[day].append({"id": row["id"], "event_name": row["event_name"]})
        return grouped


def get_all_day_events():
    """Fetch all day events as a flat list for dropdown (excludes empty placeholders)."""
    query = """
        SELECT id, day, event_name
        FROM day_events
        WHERE event_name IS NOT NULL AND TRIM(event_name) != ''
        ORDER BY day, id
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()


def add_day(day):
    """Add a day. Days are created when their first event is added."""
    return True  # No empty placeholder rows


def add_day_event(day, event_name):
    """Insert a new day event into the day_events table."""
    query = "INSERT INTO day_events (day, event_name) VALUES (%s, %s)"
    with db_cursor() as (_, cursor):
        cursor.execute(query, (day, event_name))
        return cursor.lastrowid


def delete_day_event(event_id):
    """Delete a specific event row by ID."""
    with db_cursor() as (_, cursor):
        cursor.execute("DELETE FROM day_events WHERE id = %s", (event_id,))


def delete_day(day):
    """Delete all rows for a specific day."""
    with db_cursor() as (_, cursor):
        cursor.execute("DELETE FROM day_events WHERE day = %s", (day,))


# ── Event Images ─────────────────────────────────────────────────────────────

def create_event_image(original_s3_key, processed_s3_key, original_url, processed_url, day_event_id=None):
    query = """
        INSERT INTO event_images (original_s3_key, processed_s3_key, original_url, processed_url, day_event_id)
        VALUES (%s, %s, %s, %s, %s)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (original_s3_key, processed_s3_key, original_url, processed_url, day_event_id))
        return cursor.lastrowid


def get_all_event_images():
    query = """
        SELECT id, original_s3_key, processed_s3_key, original_url, processed_url, uploaded_at
        FROM event_images
        ORDER BY uploaded_at ASC
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query)
        return cursor.fetchall()


# ── Matches ───────────────────────────────────────────────────────────────────

def create_match(user_id, photo_id, confidence):
    query = """
        INSERT INTO matches (user_id, photo_id, confidence)
        VALUES (%s, %s, %s)
        ON DUPLICATE KEY UPDATE confidence = VALUES(confidence)
    """
    with db_cursor() as (_, cursor):
        cursor.execute(query, (user_id, photo_id, confidence))


# ── Images Array ──────────────────────────────────────────────────────────────

def append_image_to_user_array(user_id: int, event_image_id: int) -> None:
    """Append event_image_id into images_array for the given user."""
    query = """
        INSERT INTO images_array (user_id, image_ids)
        VALUES (%s, JSON_ARRAY(%s))
        ON DUPLICATE KEY UPDATE
            image_ids = IF(
                JSON_CONTAINS(image_ids, JSON_ARRAY(%s)),
                image_ids,
                JSON_ARRAY_APPEND(image_ids, '$', %s)
            )
    """
    with db_cursor() as (conn, cursor):
        cursor.execute(query, (user_id, event_image_id, event_image_id, event_image_id))
        conn.commit()


def get_user_image_array(user_id: int):
    """Return matched image IDs for a user with name + email via JOIN."""
    query = """
        SELECT u.id AS user_id, u.name, u.email, ia.image_ids
        FROM images_array ia
        JOIN users u ON u.id = ia.user_id
        WHERE ia.user_id = %s
    """
    with db_cursor(dictionary=True) as (_, cursor):
        cursor.execute(query, (user_id,))
        row = cursor.fetchone()
        if not row:
            return {"user_id": user_id, "name": None, "email": None, "image_ids": []}
        ids = row["image_ids"]
        if isinstance(ids, str):
            import json
            ids = json.loads(ids)
        return {"user_id": row["user_id"], "name": row["name"], "email": row["email"], "image_ids": ids or []}