// Package admin implements the admin console — dashboard, user management,
// event CRUD, and the badge designer. Every route is mounted behind
// RequireAdmin so anonymous users get bounced to /signin and logged-in
// non-admins get a flat 403.
package admin

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"log/slog"
	"net/http"
	"net/url"
	"strconv"
	"strings"
	"time"

	"github.com/bluesky-social/indigo/atproto/syntax"
	"github.com/go-chi/chi/v5"

	"atmoquest/config"
	"atmoquest/features/admin/pages"
	"atmoquest/features/auth"
	"atmoquest/internal/admincrypto"
	"atmoquest/internal/badge"
	"atmoquest/internal/checkin"
	"atmoquest/internal/connection"
	"atmoquest/internal/event"
	"atmoquest/internal/profile"
	"atmoquest/internal/users"
)

// Handlers holds the dependencies the admin feature needs.
type Handlers struct {
	DB     *sql.DB
	Auth   *auth.Handlers
	Signer *admincrypto.Signer
}

// NewHandlers wires the admin feature.
func NewHandlers(conn *sql.DB, authH *auth.Handlers, signer *admincrypto.Signer) *Handlers {
	return &Handlers{DB: conn, Auth: authH, Signer: signer}
}

// RequireAdmin wraps a handler so it only runs when the session cookie
// identifies an active admin (users.is_admin = 1 AND is_banned = 0).
//
//   - No cookie → redirect to /signin?next=<path>.
//   - Cookie present, not admin → 403 with a small page.
//   - Cookie present, admin → pass through.
func (h *Handlers) RequireAdmin(next http.Handler) http.Handler {
	return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
		didStr, sid := h.Auth.Sessions.Get(r)
		if didStr == "" || sid == "" {
			http.Redirect(w, r, "/signin?next="+r.URL.Path, http.StatusSeeOther)
			return
		}
		did, err := syntax.ParseDID(didStr)
		if err != nil {
			http.Redirect(w, r, "/signin", http.StatusSeeOther)
			return
		}
		ok, err := users.IsAdmin(r.Context(), h.DB, did)
		if err != nil {
			slog.Error("RequireAdmin: IsAdmin", "did", did.String(), "err", err)
			http.Error(w, "internal error", http.StatusInternalServerError)
			return
		}
		if !ok {
			// Don't leak whether the page exists — render a generic
			// not-authorized page rather than 404 vs 403 distinction.
			w.WriteHeader(http.StatusForbidden)
			_, _ = w.Write([]byte("not authorized"))
			return
		}
		next.ServeHTTP(w, r)
	})
}

// AdminDashboard renders the top-level admin landing page: a small set of
// "is this app being used" stats plus quick links to the deeper admin
// sections.
func (h *Handlers) AdminDashboard(w http.ResponseWriter, r *http.Request) {
	stats, err := users.Counts(r.Context(), h.DB)
	if err != nil {
		slog.Error("admin dashboard: counts", "err", err)
	}

	// Event count is its own query — kept out of the users package so each
	// package stays focused.
	var eventCount, checkinCount int
	_ = h.DB.QueryRowContext(r.Context(), `SELECT COUNT(*) FROM events`).Scan(&eventCount)
	_ = h.DB.QueryRowContext(r.Context(), `SELECT COUNT(*) FROM checkins`).Scan(&checkinCount)

	view := pages.AdminDashboardView{
		TotalUsers:    stats.TotalUsers,
		AdminCount:    stats.AdminCount,
		BannedCount:   stats.BannedCount,
		ActiveLast7d:  stats.ActiveLast7d,
		EventCount:    eventCount,
		CheckinCount:  checkinCount,
		SignerKeyID:   signerKeyID(h.Signer),
		SignerPubKey:  signerPubKey(h.Signer),
		CurrentHandle: h.currentAdminHandle(r),
	}
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminDashboard(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin dashboard", "err", err)
	}
}

// AdminUsers renders the user list with optional search + admin filter.
//
// Query params:
//
//	?q=...         substring search over handle/display_name/DID
//	?admins=1      show only admins
//	?page=N        1-indexed pagination (50 per page)
func (h *Handlers) AdminUsers(w http.ResponseWriter, r *http.Request) {
	const perPage = 50
	q := r.URL.Query().Get("q")
	adminsOnly := r.URL.Query().Get("admins") == "1"
	page, _ := strconv.Atoi(r.URL.Query().Get("page"))
	if page < 1 {
		page = 1
	}

	list, err := users.List(r.Context(), h.DB, users.ListOptions{
		Query:      q,
		AdminsOnly: adminsOnly,
		Limit:      perPage,
		Offset:     (page - 1) * perPage,
	})
	if err != nil {
		slog.Error("admin users: list", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}

	view := pages.AdminUsersView{
		Query:      q,
		AdminsOnly: adminsOnly,
		Page:       page,
		Users:      toAdminUsersRows(list),
		// HasNext is a cheap signal: if we got a full page, there might be
		// another. Exact count would require a second query — not worth it
		// for a small admin list.
		HasNext: len(list) == perPage,
	}
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminUsers(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin users", "err", err)
	}
}

// AdminUsersPromote handles POST /admin/users/{did}/promote — toggles the
// is_admin flag based on the `make_admin` form value ("1" → admin, anything
// else → demote).
//
// The current admin can demote themselves; this is intentional so a
// sole-admin reset path exists (re-promote via the seed script).
func (h *Handlers) AdminUsersPromote(w http.ResponseWriter, r *http.Request) {
	didStr := chi.URLParam(r, "did")
	did, err := syntax.ParseDID(didStr)
	if err != nil {
		http.Error(w, "invalid DID", http.StatusBadRequest)
		return
	}
	makeAdmin := r.FormValue("make_admin") == "1"
	if err := users.SetAdmin(r.Context(), h.DB, did, makeAdmin); err != nil {
		if errors.Is(err, users.ErrNotFound) {
			http.Error(w, "user not found (have they signed in?)", http.StatusNotFound)
			return
		}
		slog.Error("admin promote", "did", did.String(), "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	back := "/admin/users"
	if r.URL.RawQuery != "" {
		back += "?" + r.URL.RawQuery
	}
	http.Redirect(w, r, back, http.StatusSeeOther)
}

// AdminEvents renders the list of admin-managed events plus the "create
// new" CTA.
func (h *Handlers) AdminEvents(w http.ResponseWriter, r *http.Request) {
	list, err := event.ListAdmin(r.Context(), h.DB, 0)
	if err != nil {
		slog.Error("admin events: list", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	view := pages.AdminEventsView{
		PublicURL: config.Global.PublicURL,
		Events:    toAdminEventRows(list, config.Global.PublicURL),
	}
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminEvents(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin events", "err", err)
	}
}

// AdminEventNew renders the empty new-event form.
func (h *Handlers) AdminEventNew(w http.ResponseWriter, r *http.Request) {
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminEventNew(pages.AdminEventFormView{}).Render(r.Context(), w); err != nil {
		slog.Error("render admin event new", "err", err)
	}
}

// AdminEventCreate handles POST /admin/events — validates the form, writes a
// quest.atmo.event record to the admin's PDS, and caches the resulting event
// locally with its QR token.
func (h *Handlers) AdminEventCreate(w http.ResponseWriter, r *http.Request) {
	_, viewerSess, ok := h.Auth.RequireSession(w, r)
	if !ok {
		return
	}
	in, formView, perr := parseEventForm(r)
	if perr != "" {
		formView.Error = perr
		w.Header().Set("Content-Type", "text/html; charset=utf-8")
		w.WriteHeader(http.StatusBadRequest)
		_ = pages.AdminEventNew(formView).Render(r.Context(), w)
		return
	}

	_, qr, err := event.Put(r.Context(), viewerSess, h.DB, in)
	if err != nil {
		slog.Error("admin event create", "err", err)
		formView.Error = "failed to create event: " + err.Error()
		w.Header().Set("Content-Type", "text/html; charset=utf-8")
		w.WriteHeader(http.StatusBadGateway)
		_ = pages.AdminEventNew(formView).Render(r.Context(), w)
		return
	}
	// Land the admin on the badge designer next — most events want a badge
	// and this keeps the flow obvious.
	http.Redirect(w, r, "/admin/events/"+qr+"/badge", http.StatusSeeOther)
}

// AdminEventEditPage renders the edit form for an existing event.
func (h *Handlers) AdminEventEditPage(w http.ResponseWriter, r *http.Request) {
	token := chi.URLParam(r, "token")
	ev, err := event.LookupByQRToken(r.Context(), h.DB, token)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	view := pages.AdminEventFormView{
		Name:              ev.Name,
		Location:          ev.Location,
		StartTime:         ev.StartTime.Format("2006-01-02T15:04"),
		EndTime:           ev.EndTime.Format("2006-01-02T15:04"),
		ExpectedAttendees: ev.ExpectedAttendees,
		Links:             toEventLinks(ev.Links),
		QRToken:           token,
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminEventEdit(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin event edit", "err", err)
	}
}

// toEventLinks maps stored event links to the view type.
func toEventLinks(links []event.Link) []pages.EventLink {
	out := make([]pages.EventLink, 0, len(links))
	for _, l := range links {
		out = append(out, pages.EventLink{Label: l.Label, URL: l.URL})
	}
	return out
}

// AdminEventEditSave handles POST /admin/events/{token}/edit. It rewrites both
// the organizer's PDS record and the local cache so the two stay in sync.
func (h *Handlers) AdminEventEditSave(w http.ResponseWriter, r *http.Request) {
	_, viewerSess, ok := h.Auth.RequireSession(w, r)
	if !ok {
		return
	}

	token := chi.URLParam(r, "token")
	ev, err := event.LookupByQRToken(r.Context(), h.DB, token)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	in, formView, perr := parseEventForm(r)
	formView.QRToken = token
	if perr != "" {
		formView.Error = perr
		w.Header().Set("Content-Type", "text/html; charset=utf-8")
		w.WriteHeader(http.StatusBadRequest)
		_ = pages.AdminEventEdit(formView).Render(r.Context(), w)
		return
	}

	// Geofence isn't editable in the form, so carry the existing value through
	// to avoid dropping it when the PDS record is rewritten.
	if err := event.Update(r.Context(), viewerSess, h.DB, ev.URI, event.UpdateInput{
		Name:              in.Name,
		StartTime:         in.StartTime,
		EndTime:           in.EndTime,
		Location:          in.Location,
		ExpectedAttendees: in.ExpectedAttendees,
		Geofence:          ev.Geofence,
		Links:             in.Links,
	}); err != nil {
		slog.Error("admin event edit save", "err", err)
		formView.Error = "failed to save: " + err.Error()
		w.Header().Set("Content-Type", "text/html; charset=utf-8")
		w.WriteHeader(http.StatusInternalServerError)
		_ = pages.AdminEventEdit(formView).Render(r.Context(), w)
		return
	}

	http.Redirect(w, r, "/admin/events", http.StatusSeeOther)
}

// AdminEventBadge renders the badge designer for an event, pre-loaded with
// any existing design.
func (h *Handlers) AdminEventBadge(w http.ResponseWriter, r *http.Request) {
	token := chi.URLParam(r, "token")
	ev, err := event.LookupByQRToken(r.Context(), h.DB, token)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	view := pages.AdminEventBadgeView{
		EventURI:  ev.URI,
		EventName: ev.Name,
		Token:     token,
		Shapes:    badge.DefaultShapes,
		Primary:   badge.DefaultPrimaryColors,
		Accent:    badge.DefaultAccentColors,
		Ribbon:    badge.DefaultRibbonColors,
		Design: pages.AdminBadgeDesign{
			Shape:        "shield",
			PrimaryColor: badge.DefaultPrimaryColors[0],
			AccentColor:  badge.DefaultAccentColors[0],
			RibbonColor:  badge.DefaultRibbonColors[0],
			Label:        ev.Name,
		},
		QRPublicURL: strings.TrimRight(config.Global.PublicURL, "/") + "/e/" + token,
	}

	// Which badge type are we editing?
	badgeType := r.URL.Query().Get("type")
	if badgeType == "" {
		badgeType = "event-attendee"
	}
	view.SelectedBadgeType = badgeType

	if h.Signer != nil {
		if d, err := badge.Get(r.Context(), h.DB, h.Signer, ev.URI, badgeType); err == nil {
			view.Design = pages.AdminBadgeDesign{
				Shape:           d.Shape,
				PrimaryColor:    d.PrimaryColor,
				AccentColor:     d.AccentColor,
				RibbonColor:     d.RibbonColor,
				Label:           d.Label,
				MessageTemplate: d.MessageTemplate,
				Signature:       d.Signature,
				SigningKeyID:    d.SigningKeyID,
			}
			view.SVGPreview = badge.RenderSVG(d, 200)
		} else if !errors.Is(err, badge.ErrNotFound) {
			slog.Warn("admin badge: get", "event_uri", ev.URI, "type", badgeType, "err", err)
		}
	}

	// If no preview yet, render the default-design preview so the page
	// always shows something.
	if view.SVGPreview == "" {
		view.SVGPreview = badge.RenderSVG(badge.Design{
			Shape:        view.Design.Shape,
			PrimaryColor: view.Design.PrimaryColor,
			AccentColor:  view.Design.AccentColor,
			RibbonColor:  view.Design.RibbonColor,
			Label:        view.Design.Label,
		}, 200)
	}

	// Build event badge overview — show which badge types have designs.
	eventBadgeTypes := []struct {
		slug, name, desc string
	}{
		{"event-attendee", "Event check-in", "Awarded when someone checks into this event"},
		{"event-leaderboard-1st", "Leaderboard 1st place", "Awarded to the top connector at event end"},
		{"event-leaderboard-2nd", "Leaderboard 2nd place", "Awarded to the runner-up at event end"},
		{"event-leaderboard-3rd", "Leaderboard 3rd place", "Awarded to third place at event end"},
		{"event-exceeded", "Beyond expectations", "Awarded when check-ins exceed expected attendees"},
	}
	designMap, _ := badge.ListForEvent(r.Context(), h.DB, ev.URI)
	for _, bt := range eventBadgeTypes {
		eb := pages.EventBadgeOverview{
			Slug:        bt.slug,
			Name:        bt.name,
			Description: bt.desc,
		}
		if d, ok := designMap[bt.slug]; ok {
			eb.Designed = true
			eb.Label = d.Label
		}
		view.EventBadges = append(view.EventBadges, eb)
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminEventBadge(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin badge", "err", err)
	}
}

// BadgePreviewSVG renders a badge SVG from query parameters for the live
// designer preview. No signing or persistence — purely visual.
func (h *Handlers) BadgePreviewSVG(w http.ResponseWriter, r *http.Request) {
	d := badge.Design{
		Shape:        r.URL.Query().Get("shape"),
		PrimaryColor: r.URL.Query().Get("primary_color"),
		AccentColor:  r.URL.Query().Get("accent_color"),
		RibbonColor:  r.URL.Query().Get("ribbon_color"),
		Label:        r.URL.Query().Get("label"),
	}
	if d.Shape == "" {
		d.Shape = "shield"
	}
	if d.PrimaryColor == "" {
		d.PrimaryColor = "#fab387"
	}
	if d.AccentColor == "" {
		d.AccentColor = "#1e1e2e"
	}
	if d.RibbonColor == "" {
		d.RibbonColor = "#f38ba8"
	}
	if d.Label == "" {
		d.Label = "preview"
	}
	svg := badge.RenderSVG(d, 200)
	w.Header().Set("Content-Type", "image/svg+xml")
	w.Header().Set("Cache-Control", "no-cache")
	_, _ = w.Write([]byte(svg))
}

// AdminEventBadgeSave handles POST /admin/events/{token}/badge.
func (h *Handlers) AdminEventBadgeSave(w http.ResponseWriter, r *http.Request) {
	if h.Signer == nil {
		http.Error(w, "admin signing key not configured on the server", http.StatusServiceUnavailable)
		return
	}
	token := chi.URLParam(r, "token")
	ev, err := event.LookupByQRToken(r.Context(), h.DB, token)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	badgeType := r.FormValue("badge_type")
	if badgeType == "" {
		badgeType = "event-attendee"
	}

	d := badge.Design{
		EventURI:        ev.URI,
		BadgeType:       badgeType,
		Shape:           r.FormValue("shape"),
		PrimaryColor:    r.FormValue("primary_color"),
		AccentColor:     r.FormValue("accent_color"),
		RibbonColor:     r.FormValue("ribbon_color"),
		Label:           strings.TrimSpace(r.FormValue("label")),
		MessageTemplate: strings.TrimSpace(r.FormValue("message_template")),
	}
	if _, err := badge.Save(r.Context(), h.DB, h.Signer, d); err != nil {
		slog.Warn("admin badge save", "err", err)
		http.Error(w, err.Error(), http.StatusBadRequest)
		return
	}
	http.Redirect(w, r, "/admin/events/"+token+"/badge?type="+badgeType+"&saved=1", http.StatusSeeOther)
}

// parseEventForm pulls form values into a CreateInput, returning a
// pre-populated formView for re-rendering on error.
func parseEventForm(r *http.Request) (event.CreateInput, pages.AdminEventFormView, string) {
	in := event.CreateInput{
		Name:     strings.TrimSpace(r.FormValue("name")),
		Location: strings.TrimSpace(r.FormValue("location")),
	}
	if n, err := strconv.Atoi(r.FormValue("expected_attendees")); err == nil {
		in.ExpectedAttendees = n
	}
	// HTML datetime-local inputs come through as "2006-01-02T15:04" in the
	// browser's local TZ. Some browsers also send seconds
	// ("2006-01-02T15:04:05") or a space instead of "T". We try multiple
	// layouts so the form works across browsers.
	startStr := r.FormValue("start_time")
	endStr := r.FormValue("end_time")
	startT, errS := parseDatetimeLocal(startStr)
	endT, errE := parseDatetimeLocal(endStr)

	view := pages.AdminEventFormView{
		Name:              in.Name,
		Location:          in.Location,
		StartTime:         startStr,
		EndTime:           endStr,
		ExpectedAttendees: in.ExpectedAttendees,
	}

	// Parse up to MaxEventLinks {label,url} rows. A row needs a URL to be
	// kept (the label is optional and falls back to the host at render);
	// fully-empty rows are dropped. URLs must be http(s).
	for i := 0; i < event.MaxEventLinks; i++ {
		label := strings.TrimSpace(r.FormValue("link_label_" + strconv.Itoa(i)))
		raw := strings.TrimSpace(r.FormValue("link_url_" + strconv.Itoa(i)))
		if raw == "" {
			continue
		}
		u, perr := url.Parse(raw)
		if perr != nil || (u.Scheme != "http" && u.Scheme != "https") {
			return in, view, "links must be http or https URLs"
		}
		in.Links = append(in.Links, event.Link{Label: label, URL: raw})
		view.Links = append(view.Links, pages.EventLink{Label: label, URL: raw})
	}

	if in.Name == "" {
		return in, view, "event name is required"
	}
	if errS != nil || errE != nil {
		return in, view, "start and end times are required (use the date/time pickers)"
	}
	if endT.Before(startT) {
		return in, view, "end time must be after start time"
	}
	in.StartTime = startT
	in.EndTime = endT
	return in, view, ""
}

// AdminEventDelete handles POST /admin/events/delete — removes the event, its
// PDS record, and all associated local data (checkins, badges, etc.).
// The event URI is read from the "uri" form field so that events created before
// qr_token was introduced (which have an empty token) can still be deleted.
func (h *Handlers) AdminEventDelete(w http.ResponseWriter, r *http.Request) {
	_, sess, ok := h.Auth.RequireSession(w, r)
	if !ok {
		return
	}

	uri := r.FormValue("uri")
	if uri == "" {
		http.NotFound(w, r)
		return
	}

	if err := event.Delete(r.Context(), sess, h.DB, uri); err != nil {
		slog.Error("admin event delete", "uri", uri, "err", err)
		http.Error(w, "failed to delete event", http.StatusInternalServerError)
		return
	}

	slog.Info("admin event deleted", "uri", uri)
	http.Redirect(w, r, "/admin/events", http.StatusSeeOther)
}

// AdminEventFinalize awards end-of-event badges: leaderboard top 3 and
// "exceeded expectations" if check-ins surpassed expected attendees.
// This is a manual admin action triggered after the event ends.
func (h *Handlers) AdminEventFinalize(w http.ResponseWriter, r *http.Request) {
	_, _, ok := h.Auth.RequireSession(w, r)
	if !ok {
		return
	}

	token := chi.URLParam(r, "token")
	ev, err := event.LookupByQRToken(r.Context(), h.DB, token)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	ctx := r.Context()

	// Get all checked-in attendees.
	attendeeDIDs, _ := checkin.ListAttendeesForEvent(ctx, h.DB, ev.URI)

	// Build leaderboard (connection counts per attendee at this event).
	type connCount struct {
		did   string
		count int
	}
	var leaderboard []connCount
	for _, didStr := range attendeeDIDs {
		did, err := syntax.ParseDID(didStr)
		if err != nil {
			continue
		}
		// Skip users who opted out of leaderboard.
		if users.IsHiddenFromLeaderboard(ctx, h.DB, didStr) {
			continue
		}
		pds := lookupPDSForDIDAdmin(ctx, h.DB, did)
		conns, _ := connection.List(ctx, pds, did)
		eventConns := 0
		for _, c := range conns {
			if c.EventURI == ev.URI {
				eventConns++
			}
		}
		if eventConns > 0 {
			leaderboard = append(leaderboard, connCount{did: didStr, count: eventConns})
		}
	}

	// Sort by count desc.
	for i := 0; i < len(leaderboard); i++ {
		for j := i + 1; j < len(leaderboard); j++ {
			if leaderboard[j].count > leaderboard[i].count {
				leaderboard[i], leaderboard[j] = leaderboard[j], leaderboard[i]
			}
		}
	}

	// Load custom message templates for this event's badges.
	msgTemplates := make(map[string]string)
	if designs, err := badge.ListForEvent(ctx, h.DB, ev.URI); err == nil {
		for badgeType, d := range designs {
			msgTemplates[badgeType] = d.MessageTemplate
		}
	}

	// Award leaderboard badges (1st, 2nd, 3rd) using competition ranking.
	placeSlugs := []string{"event-leaderboard-1st", "event-leaderboard-2nd", "event-leaderboard-3rd"}
	placeIdx := 0
	prevCount := -1
	for i, entry := range leaderboard {
		if placeIdx >= len(placeSlugs) {
			break
		}
		if entry.count != prevCount {
			placeIdx = i
			prevCount = entry.count
		}
		if placeIdx < len(placeSlugs) {
			def, err := badge.GetDefinitionBySlug(ctx, h.DB, placeSlugs[placeIdx])
			if err == nil && !badge.HasEarned(ctx, h.DB, entry.did, def.ID) {
				msg := badgeMessage(msgTemplates, placeSlugs[placeIdx], ev.Name)
				_ = badge.RecordEarned(ctx, h.DB, entry.did, def.ID, "", msg)
				slog.Info("finalize: awarded leaderboard badge",
					"did", entry.did, "place", placeIdx+1, "connections", entry.count)
			}
		}
	}

	// Award "exceeded expectations" badge if check-ins > expected attendees.
	if ev.ExpectedAttendees > 0 && len(attendeeDIDs) > ev.ExpectedAttendees {
		def, err := badge.GetDefinitionBySlug(ctx, h.DB, "event-exceeded")
		if err == nil {
			msg := badgeMessage(msgTemplates, "event-exceeded", ev.Name)
			for _, didStr := range attendeeDIDs {
				if !badge.HasEarned(ctx, h.DB, didStr, def.ID) {
					_ = badge.RecordEarned(ctx, h.DB, didStr, def.ID, "", msg)
				}
			}
			slog.Info("finalize: awarded exceeded badge",
				"event", ev.Name, "checkins", len(attendeeDIDs), "expected", ev.ExpectedAttendees)
		}
	}

	// Award "event-host" badge if the event was successful (>1 check-in + >=1 connection).
	totalConns := 0
	for _, cc := range leaderboard {
		totalConns += cc.count
	}
	if len(attendeeDIDs) > 1 && totalConns > 0 && ev.OrganizerDID.String() != "" {
		def, err := badge.GetDefinitionBySlug(ctx, h.DB, "event-host")
		if err == nil && !badge.HasEarned(ctx, h.DB, ev.OrganizerDID.String(), def.ID) {
			msg := badgeMessage(msgTemplates, "event-host", ev.Name)
			if msg == "" {
				msg = "Successfully hosted " + ev.Name
			}
			_ = badge.RecordEarned(ctx, h.DB, ev.OrganizerDID.String(), def.ID, "", msg)
			slog.Info("finalize: awarded event-host badge",
				"did", ev.OrganizerDID.String(), "event", ev.Name)
		}
	}

	// Compute and cache event summary statistics.
	profileFetcher := func(ctx context.Context, pds string, did syntax.DID) []string {
		q, err := profile.FetchQuest(ctx, pds, did)
		if err != nil || q == nil {
			return nil
		}
		return q.Interests
	}
	pdsLookupFn := func(did syntax.DID) string { return lookupPDSForDIDAdmin(ctx, h.DB, did) }
	if err := event.ComputeAndCacheSummary(ctx, h.DB, ev.URI, attendeeDIDs, ev, pdsLookupFn, profileFetcher); err != nil {
		slog.Warn("finalize: compute summary", "err", err)
	}

	slog.Info("admin event finalized", "uri", ev.URI, "name", ev.Name,
		"attendees", len(attendeeDIDs), "leaderboard_entries", len(leaderboard))
	http.Redirect(w, r, "/admin/events", http.StatusSeeOther)
}

// badgeMessage looks up a custom message template for a badge type,
// falling back to a default message with the event name.
func badgeMessage(templates map[string]string, badgeType, eventName string) string {
	if msg, ok := templates[badgeType]; ok && msg != "" {
		return msg
	}
	return "Earned at " + eventName
}

// AdminBadges lists all app-wide badge definitions.
func (h *Handlers) AdminBadges(w http.ResponseWriter, r *http.Request) {
	defs, err := badge.ListDefinitions(r.Context(), h.DB)
	if err != nil {
		slog.Error("admin badges: list", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}

	var view pages.AdminBadgesView
	for _, d := range defs {
		svg := badge.RenderSVG(badge.Design{
			Shape:        d.Shape,
			PrimaryColor: d.PrimaryColor,
			AccentColor:  d.AccentColor,
			RibbonColor:  d.RibbonColor,
			Label:        d.Label,
		}, 80)
		view.Badges = append(view.Badges, pages.AdminBadgeRow{
			ID:           d.ID,
			Slug:         d.Slug,
			Name:         d.Name,
			Description:  d.Description,
			Shape:        d.Shape,
			PrimaryColor: d.PrimaryColor,
			AccentColor:  d.AccentColor,
			RibbonColor:  d.RibbonColor,
			Label:        d.Label,
			Trigger:      d.Trigger,
			SVGPreview:   svg,
		})
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminBadges(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin badges", "err", err)
	}
}

// AdminBadgeEditPage renders the badge edit form for a specific definition.
func (h *Handlers) AdminBadgeEditPage(w http.ResponseWriter, r *http.Request) {
	idStr := chi.URLParam(r, "id")
	id, err := strconv.Atoi(idStr)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	defs, _ := badge.ListDefinitions(r.Context(), h.DB)
	var def *badge.Definition
	for i := range defs {
		if defs[i].ID == id {
			def = &defs[i]
			break
		}
	}
	if def == nil {
		http.NotFound(w, r)
		return
	}

	svg := badge.RenderSVG(badge.Design{
		Shape:        def.Shape,
		PrimaryColor: def.PrimaryColor,
		AccentColor:  def.AccentColor,
		RibbonColor:  def.RibbonColor,
		Label:        def.Label,
	}, 200)

	view := pages.AdminBadgeEditView{
		Badge: pages.AdminBadgeRow{
			ID:           def.ID,
			Slug:         def.Slug,
			Name:         def.Name,
			Description:  def.Description,
			Shape:        def.Shape,
			PrimaryColor: def.PrimaryColor,
			AccentColor:  def.AccentColor,
			RibbonColor:  def.RibbonColor,
			Label:        def.Label,
			Trigger:      def.Trigger,
			SVGPreview:   svg,
		},
		Shapes:  badge.DefaultShapes,
		Primary: badge.DefaultPrimaryColors,
		Accent:  badge.DefaultAccentColors,
		Ribbon:  badge.DefaultRibbonColors,
		Saved:   r.URL.Query().Get("saved") == "1",
	}

	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminBadgeEdit(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin badge edit", "err", err)
	}
}

// AdminBadgeEditSave handles POST /admin/badges/{id}/edit — updates the
// badge definition's visual design.
func (h *Handlers) AdminBadgeEditSave(w http.ResponseWriter, r *http.Request) {
	idStr := chi.URLParam(r, "id")
	id, err := strconv.Atoi(idStr)
	if err != nil {
		http.NotFound(w, r)
		return
	}

	if err := r.ParseForm(); err != nil {
		http.Error(w, "bad request", http.StatusBadRequest)
		return
	}

	_, err = h.DB.ExecContext(r.Context(), `
		UPDATE badge_definitions SET
			name = ?,
			description = ?,
			shape = ?,
			primary_color = ?,
			accent_color = ?,
			ribbon_color = ?,
			label = ?
		WHERE id = ?
	`,
		strings.TrimSpace(r.FormValue("name")),
		strings.TrimSpace(r.FormValue("description")),
		r.FormValue("shape"),
		r.FormValue("primary_color"),
		r.FormValue("accent_color"),
		r.FormValue("ribbon_color"),
		strings.TrimSpace(r.FormValue("label")),
		id,
	)
	if err != nil {
		slog.Warn("admin badge edit save", "id", id, "err", err)
		http.Error(w, "failed to save", http.StatusInternalServerError)
		return
	}

	http.Redirect(w, r, fmt.Sprintf("/admin/badges/%d/edit?saved=1", id), http.StatusSeeOther)
}

// lookupPDSForDIDAdmin is a helper that looks up PDS host from oauth_sessions.
func lookupPDSForDIDAdmin(ctx context.Context, db *sql.DB, did syntax.DID) string {
	var host string
	err := db.QueryRowContext(ctx, `
		SELECT data ->> 'host_url' FROM oauth_sessions
		WHERE did = ? ORDER BY updated_at DESC LIMIT 1
	`, did.String()).Scan(&host)
	if err == nil && host != "" {
		return host
	}
	return "https://bsky.social"
}

// toAdminEventRows projects []event.AdminEvent into the view type. We
// pre-format times here so the template stays dumb.
func toAdminEventRows(list []event.AdminEvent, publicURL string) []pages.AdminEventRow {
	publicURL = strings.TrimRight(publicURL, "/")
	out := make([]pages.AdminEventRow, 0, len(list))
	for _, ev := range list {
		out = append(out, pages.AdminEventRow{
			URI:               ev.URI,
			Name:              ev.Name,
			Location:          ev.Location,
			StartTime:         ev.StartTime.Format("Mon Jan 2 · 3:04 PM"),
			EndTime:           ev.EndTime.Format("Mon Jan 2 · 3:04 PM"),
			ExpectedAttendees: ev.ExpectedAttendees,
			QRToken:           ev.QRToken,
			QRPublicURL:       publicURL + "/e/" + ev.QRToken,
		})
	}
	return out
}

// toAdminUsersRows projects users.User into the view-layer row type. We do
// this in the handler so the template stays decoupled from the storage
// struct (which has a syntax.DID, not a string).
func toAdminUsersRows(list []users.User) []pages.AdminUserRow {
	out := make([]pages.AdminUserRow, 0, len(list))
	for _, u := range list {
		out = append(out, pages.AdminUserRow{
			DID:         u.DID.String(),
			Handle:      u.Handle,
			DisplayName: u.DisplayName,
			AuthCount:   u.AuthCount,
			FirstSeen:   u.FirstSeenAt.Format("2006-01-02"),
			LastSeen:    u.LastSeenAt.Format("2006-01-02 15:04"),
			IsAdmin:     u.IsAdmin,
			IsBanned:    u.IsBanned,
		})
	}
	return out
}

// currentAdminHandle returns a short label for whichever admin is viewing
// the dashboard. Used purely for cosmetics ("hi, @you").
func (h *Handlers) currentAdminHandle(r *http.Request) string {
	didStr, _ := h.Auth.Sessions.Get(r)
	if didStr == "" {
		return ""
	}
	if did, err := syntax.ParseDID(didStr); err == nil {
		if u, err := users.Get(r.Context(), h.DB, did); err == nil {
			if u.Handle != "" {
				return u.Handle
			}
		}
	}
	return didStr
}

// signerKeyID / signerPubKey are small nil-safe helpers so callers can
// render the signer fingerprint without nil-checking everywhere.
func signerKeyID(s *admincrypto.Signer) string {
	if s == nil {
		return ""
	}
	return s.KeyID
}

func signerPubKey(s *admincrypto.Signer) string {
	if s == nil {
		return ""
	}
	return s.PublicKeyBase64()
}

// JSONResponse is a tiny helper for any future JSON admin endpoints.
func writeJSON(w http.ResponseWriter, v any) {
	w.Header().Set("Content-Type", "application/json")
	_ = json.NewEncoder(w).Encode(v)
}

var _ = writeJSON // keep available for upcoming admin JSON endpoints

// FollowUpRow is a single row returned by AdminFollowUpsCSV.
type FollowUpRow struct {
	DisplayName    string
	Email          string
	Events         string // comma-separated event names
	ConnectionNums int    // number of connections
}

// AdminFollowUps renders the follow-ups page — local users who opted in.
func (h *Handlers) AdminFollowUps(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.QueryContext(r.Context(), `
		SELECT did, display_name, email
		FROM users
		WHERE user_type = 'local' AND follow_up = 1 AND email != ''
		ORDER BY updated_at DESC
	`)
	if err != nil {
		slog.Error("admin follow-ups: list", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var entries []pages.AdminFollowUpEntry
	for rows.Next() {
		var e pages.AdminFollowUpEntry
		if err := rows.Scan(&e.DID, &e.DisplayName, &e.Email); err != nil {
			slog.Error("admin follow-ups: scan", "err", err)
			continue
		}
		entries = append(entries, e)
	}
	if err := rows.Err(); err != nil {
		slog.Error("admin follow-ups: rows", "err", err)
	}

	view := pages.AdminFollowUpsView{
		Entries: entries,
		Count:   len(entries),
	}
	w.Header().Set("Content-Type", "text/html; charset=utf-8")
	if err := pages.AdminFollowUps(view).Render(r.Context(), w); err != nil {
		slog.Error("render admin follow-ups", "err", err)
	}
}

// AdminFollowUpsCSV returns a CSV download of follow-up entries with their
// checked-in events and connection counts.
func (h *Handlers) AdminFollowUpsCSV(w http.ResponseWriter, r *http.Request) {
	rows, err := h.DB.QueryContext(r.Context(), `
		SELECT did, display_name, email
		FROM users
		WHERE user_type = 'local' AND follow_up = 1 AND email != ''
		ORDER BY updated_at DESC
	`)
	if err != nil {
		slog.Error("admin follow-ups csv: list", "err", err)
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()

	var results []FollowUpRow
	for rows.Next() {
		var did, displayName, email string
		if err := rows.Scan(&did, &displayName, &email); err != nil {
			slog.Error("admin follow-ups csv: scan", "err", err)
			continue
		}

		// Get events this user checked into
		eventRows, err := h.DB.QueryContext(r.Context(), `
			SELECT e.name FROM checkins c
			JOIN events e ON e.uri = c.event_uri
			WHERE c.did = ?
			ORDER BY c.checked_in_at ASC
		`, did)
		var eventNames []string
		if err == nil {
			for eventRows.Next() {
				var name string
				if err := eventRows.Scan(&name); err == nil {
					eventNames = append(eventNames, name)
				}
			}
			eventRows.Close()
		}

		// Get connection count — count rows in local_connections where
		// this user is the viewer (using viewer_local_id).
		var connCount int
		_ = h.DB.QueryRowContext(r.Context(), `
			SELECT COUNT(*) FROM local_connections
			WHERE viewer_local_id = ?
		`, did).Scan(&connCount)

		events := ""
		for _, n := range eventNames {
			if events != "" {
				events += " "
			}
			events += n
		}

		results = append(results, FollowUpRow{
			DisplayName:    displayName,
			Email:          email,
			Events:         events,
			ConnectionNums: connCount,
		})
	}
	rows.Close()

	// Write CSV
	w.Header().Set("Content-Type", "text/csv; charset=utf-8")
	w.Header().Set("Content-Disposition", "attachment; filename=atmoquest-follow-ups.csv")
	// Write BOM for Excel compatibility
	_, _ = w.Write([]byte{0xEF, 0xBB, 0xBF})
	_, _ = w.Write([]byte("display_name,email,events,connections\n"))
	for _, r := range results {
		line := csvEscape(r.DisplayName) + "," + csvEscape(r.Email) + "," + csvEscape(r.Events) + "," + strconv.Itoa(r.ConnectionNums) + "\n"
		_, _ = w.Write([]byte(line))
	}
}

// csvEscape wraps a value in double quotes if it contains special chars.
func csvEscape(v string) string {
	if strings.ContainsAny(v, ",\"\n\r") {
		v = strings.ReplaceAll(v, "\"", "\"\"")
		return "\"" + v + "\""
	}
	return v
}

// parseDatetimeLocal parses the value of an HTML <input type="datetime-local">.
// Browsers can send several formats:
//   - "2006-01-02T15:04"       (Chrome, Safari — most common)
//   - "2006-01-02T15:04:05"    (with seconds — some mobile browsers)
//   - "2006-01-02 15:04"       (space instead of T — older browsers)
//   - "2006-01-02 15:04:05"    (space + seconds)
//
// All are parsed as server-local time.
func parseDatetimeLocal(s string) (time.Time, error) {
	s = strings.TrimSpace(s)
	for _, layout := range []string{
		"2006-01-02T15:04",
		"2006-01-02T15:04:05",
		"2006-01-02 15:04",
		"2006-01-02 15:04:05",
	} {
		if t, err := time.ParseInLocation(layout, s, time.Local); err == nil {
			return t, nil
		}
	}
	return time.Time{}, fmt.Errorf("unrecognized datetime format: %q", s)
}