import json
import threading

from flask import jsonify, render_template

from app.admin import admin_blueprint
from app.admin.auth import admin_login_required
from app.email_service import send_email_to_user
from config.db_config import get_db_connection
from models.user_model import get_site_assets


@admin_blueprint.route("/trigger", methods=["GET"])
@admin_login_required
def trigger_page():
    return render_template("trigger.html", site_assets=get_site_assets())


@admin_blueprint.route("/api/trigger/run", methods=["POST"])
@admin_login_required
def run_trigger_pipeline():
    """
    Trigger flow:
    1. Read images_array  → user_id + image_ids
    2. Join users         → email, name, face_image
    3. Check email_deliveries → skip already sent image IDs
    4. Send email for NEW images only
    5. INSERT new rows into email_deliveries
    """
    connection = None
    cursor = None

    try:
        connection = get_db_connection()
        cursor = connection.cursor()
        cursor.execute("INSERT INTO trigger_jobs (status) VALUES (%s)", ("running",))
        connection.commit()
        job_id = cursor.lastrowid
    except Exception as exc:
        return jsonify({"status": "error", "message": f"Failed to create job: {exc}"}), 500
    finally:
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()

    def execute_pipeline(job_id):
        conn = None
        cur = None
        emails_sent = 0
        emails_skipped = 0
        summary = []

        try:
            site_assets = get_site_assets()
            header_image_url = (
                site_assets["header"]["image_path"]
                if site_assets.get("header") else ""
            )

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

            # ── Step 1: Get all users from images_array with their matched images ──
            cur.execute("""
                SELECT
                    ia.user_id,
                    ia.image_ids,
                    u.name,
                    u.email,
                    u.s3_image_url
                FROM images_array ia
                JOIN users u ON u.id = ia.user_id
                WHERE u.email IS NOT NULL AND u.email != ''
            """)
            rows = cur.fetchall()

            if not rows:
                _update_job(job_id, "completed", {
                    "success": True,
                    "message": "No users found in images_array.",
                    "emails_sent": 0,
                    "emails_skipped": 0,
                })
                return

            for row in rows:
                user_id       = int(row["user_id"])
                user_email    = row["email"]
                user_name     = row["name"]
                user_face_img = row["s3_image_url"]

                # ── Step 2: Parse image_ids ────────────────────────────────
                raw_ids = row["image_ids"]
                if raw_ids is None:
                    image_ids = []
                elif isinstance(raw_ids, list):
                    image_ids = raw_ids
                elif isinstance(raw_ids, str):
                    try:
                        image_ids = json.loads(raw_ids)
                    except Exception:
                        image_ids = []
                else:
                    image_ids = []

                image_ids = [int(i) for i in image_ids if i is not None]

                if not image_ids:
                    print(f"[trigger] user_id={user_id}: no images, skip")
                    emails_skipped += 1
                    continue

                # ── Step 3: Check which images already sent ────────────────
                fmt = ",".join(["%s"] * len(image_ids))
                cur.execute(
                    f"""
                    SELECT event_image_id FROM email_deliveries
                    WHERE user_id = %s AND event_image_id IN ({fmt})
                    """,
                    [user_id] + image_ids
                )
                already_sent = {r["event_image_id"] for r in cur.fetchall()}
                new_ids = [i for i in image_ids if i not in already_sent]

                if not new_ids:
                    print(f"[trigger] {user_email}: all images already sent, skip")
                    emails_skipped += 1
                    continue

                # ── Step 4: Send email ─────────────────────────────────────
                try:
                    send_email_to_user(
                        user_email=user_email,
                        user_id=user_id,
                        user_face_image=user_face_img,
                        header_image_url=header_image_url,
                    )

                    # ── Step 5: Log delivery for new images only ───────────
                    for img_id in new_ids:
                        cur.execute(
                            "INSERT IGNORE INTO email_deliveries (user_id, event_image_id) VALUES (%s, %s)",
                            (user_id, img_id)
                        )
                    conn.commit()

                    emails_sent += 1
                    summary.append({
                        "user_id":        user_id,
                        "name":           user_name,
                        "email":          user_email,
                        "new_images_sent": len(new_ids),
                        "total_images":   len(image_ids),
                    })
                    print(f"[trigger] ✅ Email sent to {user_email} ({len(new_ids)} new images)")

                except Exception as exc:
                    print(f"[trigger] ❌ Email failed for {user_email}: {exc}")
                    summary.append({
                        "user_id": user_id,
                        "name":    user_name,
                        "email":   user_email,
                        "error":   str(exc),
                    })

            _update_job(job_id, "completed", {
                "success":        True,
                "message":        "Trigger completed.",
                "emails_sent":    emails_sent,
                "emails_skipped": emails_skipped,
                "summary":        summary,
            })
            print(f"[trigger] Done. Sent={emails_sent} Skipped={emails_skipped}")

        except Exception as exc:
            import traceback
            print(f"[trigger] Pipeline error: {exc}")
            print(traceback.format_exc())
            _update_job(job_id, "failed", {"error": str(exc)})

        finally:
            if cur:
                cur.close()
            if conn and conn.is_connected():
                conn.close()

    threading.Thread(target=execute_pipeline, args=(job_id,), daemon=True).start()
    return jsonify({"status": "started", "job_id": job_id}), 202


def _update_job(job_id, status, result_data):
    conn = None
    cur = None
    try:
        conn = get_db_connection()
        cur = conn.cursor()
        cur.execute(
            "UPDATE trigger_jobs SET status=%s, result=%s, completed_at=NOW() WHERE id=%s",
            (status, json.dumps(result_data), job_id)
        )
        conn.commit()
    except Exception as exc:
        print(f"[trigger] Failed to update job: {exc}")
    finally:
        if cur:
            cur.close()
        if conn and conn.is_connected():
            conn.close()


@admin_blueprint.route("/api/trigger/status", methods=["GET"])
@admin_login_required
def trigger_status():
    """Get the status of the latest trigger job."""
    connection = None
    cursor = None
    try:
        connection = get_db_connection()
        cursor = connection.cursor(dictionary=True)
        cursor.execute(
            "SELECT id, status, result, started_at, completed_at FROM trigger_jobs ORDER BY id DESC LIMIT 1"
        )
        row = cursor.fetchone()
        if not row:
            return jsonify({"status": "idle"})

        result_data = {}
        if row.get("result"):
            try:
                result_data = json.loads(row["result"])
            except Exception:
                result_data = {"error": str(row["result"])}

        return jsonify({
            "status":       row["status"],
            "result":       result_data,
            "started_at":   row.get("started_at"),
            "completed_at": row.get("completed_at"),
        })
    except Exception as exc:
        return jsonify({"status": "error", "message": str(exc)}), 500
    finally:
        if cursor:
            cursor.close()
        if connection and connection.is_connected():
            connection.close()