"""Routes: admin email log / delivery history. Provides a single admin page (with a sidebar button) that lists every email that has been sent for registrations (leader + group members), including unsuccessful deliveries, with AJAX search-by-name/email and status filters (All / Sent / Unsuccessful). Counts of how many were sent vs. unsuccessful are shown up top. Opening an email shows its full reconstructed content. """ from flask import abort, jsonify, render_template, request from nexora.core import app from nexora.database.connection import query from nexora.security.permissions import admin_required, permission_required PAGE_SIZE = 50 def _search_terms(text): return (text or "").strip() def _build_list(search, status, email_type, limit, offset): """Return (rows, total) for the email log with filters applied. ``search`` matches the recipient email, subject or the registration leader/member name. ``status`` is one of ``all``, ``sent`` or ``unsuccessful``. ``email_type`` optionally narrows to a specific type. """ sql = """ SELECT el.id, el.registration_id, el.recipient_email, el.subject, el.description, el.status, el.error_message, el.sent_by, el.created_at, el.email_type, el.previous_status, el.new_status, el.pdf_attached, el.retry_count, r.full_name AS leader_name, r.email AS leader_email, r.team_name, r.entry_type FROM email_logs el LEFT JOIN registrations r ON r.id = el.registration_id WHERE 1=1 """ args = [] if status == "sent": sql += " AND el.status = 'sent'" elif status == "unsuccessful": sql += " AND el.status IN ('failed', 'permanent_failure')" if email_type: sql += " AND el.email_type = ?" args.append(email_type) if search: like = f"%{search}%" sql += """ AND ( el.recipient_email LIKE ? OR el.subject LIKE ? OR COALESCE(r.full_name,'') LIKE ? OR COALESCE(r.team_name,'') LIKE ? OR COALESCE(el.description,'') LIKE ? )""" args += [like, like, like, like, like] total = query( f"SELECT COUNT(*) AS c FROM ({sql}) t", args, one=True )["c"] sql += " ORDER BY el.id DESC LIMIT ? OFFSET ?" args += [limit, offset] rows = query(sql, args) result = [] for row in rows: result.append( { "id": row["id"], "registration_id": row["registration_id"], "recipient_email": row["recipient_email"], "subject": row["subject"], "description": row["description"], "status": row["status"], "error_message": row["error_message"], "sent_by": row["sent_by"], "created_at": row["created_at"], "email_type": row["email_type"], "previous_status": row["previous_status"], "new_status": row["new_status"], "pdf_attached": row["pdf_attached"], "retry_count": row["retry_count"], "leader_name": row["leader_name"], "leader_email": row["leader_email"], "team_name": row["team_name"], "entry_type": row["entry_type"], } ) return result, total def _counts(search, email_type): """Totals for the filter chips: how many sent / unsuccessful.""" sent_sql = "SELECT COUNT(*) AS c FROM email_logs WHERE status = 'sent'" uns_sql = "SELECT COUNT(*) AS c FROM email_logs WHERE status IN ('failed','permanent_failure')" args_s, args_u = [], [] if email_type: sent_sql += " AND email_type = ?" uns_sql += " AND email_type = ?" args_s.append(email_type) args_u.append(email_type) if search: like = f"%{search}%" sent_sql += """ AND (recipient_email LIKE ? OR subject LIKE ? OR COALESCE(description,'') LIKE ?)""" uns_sql += """ AND (recipient_email LIKE ? OR subject LIKE ? OR COALESCE(description,'') LIKE ?)""" args_s += [like, like, like] args_u += [like, like, like] sent = query(sent_sql, args_s, one=True)["c"] unsuccessful = query(uns_sql, args_u, one=True)["c"] return {"total": sent + unsuccessful, "sent": sent, "unsuccessful": unsuccessful} def _email_types(): rows = query( "SELECT DISTINCT email_type FROM email_logs WHERE email_type IS NOT NULL AND email_type != '' ORDER BY email_type" ) return [r["email_type"] for r in rows] @app.route("/admin/email-logs") @admin_required @permission_required("view_registrations") def admin_email_logs(): search = _search_terms(request.args.get("q")) status = request.args.get("status", "all") if status not in ("all", "sent", "unsuccessful"): status = "all" email_type = request.args.get("email_type", "").strip() page = request.args.get("page", 1, type=int) or 1 page = max(1, page) rows, total = _build_list(search, status, email_type, PAGE_SIZE, (page - 1) * PAGE_SIZE) counts = _counts(search, email_type) return render_template( "admin_email_logs.html", rows=rows, total=total, counts=counts, filters={"q": search, "status": status, "email_type": email_type}, email_types=_email_types(), page=page, page_size=PAGE_SIZE, total_pages=max(1, (total + PAGE_SIZE - 1) // PAGE_SIZE), ) @app.route("/admin/email-logs/api") @admin_required @permission_required("view_registrations") def admin_email_logs_api(): search = _search_terms(request.args.get("q")) status = request.args.get("status", "all") if status not in ("all", "sent", "unsuccessful"): status = "all" email_type = request.args.get("email_type", "").strip() page = request.args.get("page", 1, type=int) or 1 page = max(1, page) rows, total = _build_list(search, status, email_type, PAGE_SIZE, (page - 1) * PAGE_SIZE) counts = _counts(search, email_type) return jsonify( { "rows": rows, "total": total, "counts": counts, "page": page, "page_size": PAGE_SIZE, "total_pages": max(1, (total + PAGE_SIZE - 1) // PAGE_SIZE), } ) def _plain_text_of(html): import re text = html or "" text = re.sub(r"", "", text, flags=re.S | re.I) text = re.sub(r"<[^>]+>", "", text) text = re.sub(r" ", " ", text) text = re.sub(r"&", "&", text) text = re.sub(r"<", "<", text) text = re.sub(r">", ">", text) text = re.sub(r"\n\s*\n+", "\n", text) return text.strip() @app.route("/admin/email-logs/detail") @admin_required @permission_required("view_registrations") def admin_email_log_detail(): """Reconstruct the full email for a single log row so the admin can open it and see exactly who it was sent to and its full content.""" log_id = request.args.get("id", type=int) if not log_id: abort(400) row_row = query("SELECT * FROM email_logs WHERE id = ?", (log_id,), one=True) if not row_row: abort(404, "Email log not found.") row = dict(row_row) from nexora.services.registrations import registration_details from nexora.services.tickets import ticket_member_roster from nexora.services.email_service import ( build_status_email_html, ticket_email_html, email_config, ) cfg = email_config() details = registration_details(row["registration_id"]) if row["registration_id"] else None # Who was this email for? (leader vs. group member) recipient_name = row.get("recipient_name") or "" role = "Registration" is_group = bool(details and (details.get("entry_type") or "").lower() == "group") if is_group: roster = ticket_member_roster(details) if details and row["recipient_email"] and details.get("email") == row["recipient_email"]: role = "Team Leader" else: for m in roster: if m.get("email") == row["recipient_email"]: role = f"Team Member #{m.get('number', '')}" if not recipient_name: recipient_name = m.get("name") or "" break if role == "Team Leader": recipient_name = details.get("name") or (details.get("leader_name") or "") if not recipient_name and details: recipient_name = details.get("name") or (details.get("leader_name") or "") or "Participant" # Reconstruct body from stored metadata + registration data. settings_row = query("SELECT * FROM settings WHERE id = 1", one=True) settings_dict = dict(settings_row) if settings_row else {} event_title = settings_dict.get("event_title", "Nexora Esports Championship") new_status = (row.get("new_status") or "").strip() if row.get("email_type") in ("initial", "status_change") and details: status_for_body = new_status or row.get("status") or "under_verification" html_body = build_status_email_html( details, status_for_body, recipient_name=recipient_name or None, event_title=event_title, reason=(row.get("reason") or ""), ) elif details: game_name = (details.get("game") or "N/A") fee_label = cfg.get("fee_label", "Registration Fee") fee_amount = details.get("amount_paid") or details.get("total_payable") or "Rs. 0" html_body = ticket_email_html( recipient_name or (details.get("name") or "Participant"), event_title, details.get("ticket_number") or f"NX-{int(details.get('id', 0)):04d}", game_name, fee_label, fee_amount, fee_amount, ) else: html_body = f"

{row.get('subject') or ''}

" return jsonify( { "id": row["id"], "from_name": cfg.get("from_name", "Nexora Esports"), "from_email": cfg.get("from_email", ""), "to_email": row["recipient_email"], "recipient_name": recipient_name, "role": role, "subject": row["subject"], "sent_at": row["created_at"], "status": row["status"], "error_message": row["error_message"], "email_type": row["email_type"], "previous_status": row["previous_status"], "new_status": new_status, "pdf_attached": row["pdf_attached"], "sent_by": row["sent_by"], "html_body": html_body, "plain_text": _plain_text_of(html_body), "registration_id": row["registration_id"], } )