Skip to content

High performance, self-hosted, newsletter and mailing list manager with a modern dashboard. Single binary app.

37.5 KB
Raw Blame
package main

import (
	"encoding/csv"
	"encoding/json"
	"fmt"
	"net/http"
	"net/textproto"
	"net/url"
	"strconv"
	"strings"
	"time"

	"github.com/jmoiron/sqlx/types"
	"github.com/knadh/listmonk/internal/auth"
	"github.com/knadh/listmonk/internal/i18n"
	"github.com/knadh/listmonk/internal/notifs"
	"github.com/knadh/listmonk/internal/subimporter"
	"github.com/knadh/listmonk/models"
	"github.com/labstack/echo/v5"
	"github.com/lib/pq"
)

const (
	dummyUUID = "00000000-0000-0000-0000-000000000000"
)

// subscribersQueryDefaults is the allow list of filter query params.
var subscribersQueryDefaults = map[string]string{
	"page":                "",
	"per_page":            "",
	"search":              "",
	"query":               "",
	"order_by":            "",
	"order":               "",
	"subscription_status": "",
	"list_id":             "",
	"status":              "",
}

// subQueryReq is a "catch all" struct for reading various
// subscriber related requests.
type subQueryReq struct {
	Search             string `json:"search"`
	Query              string `json:"query"`
	ListIDs            []int  `json:"list_ids"`
	TargetListIDs      []int  `json:"target_list_ids"`
	SubscriberIDs      []int  `json:"ids"`
	Action             string `json:"action"`
	Status             string `json:"status"`
	SubscriptionStatus string `json:"subscription_status"`
	SubscriberStatus   string `json:"subscriber_status"`
	All                bool   `json:"all"`
}

// subOptin contains the data that's passed to the double opt-in e-mail template.
type subOptin struct {
	models.Subscriber

	OptinURL string
	UnsubURL string
	Lists    []models.List
}

var (
	dummySubscriber = models.Subscriber{
		Email:   "[email protected]",
		Name:    "Demo Subscriber",
		UUID:    dummyUUID,
		Attribs: models.JSON{"city": "Bengaluru"},
	}
)

// subscribersView is the admin page view for the subscribers list page.
type subscribersView struct {
	adminView

	Subscribers []models.Subscriber
	Page        models.PageProps
	AllLists    []models.List
	CurrentList *models.List
}

// subscriberView is the admin page view for a single subscriber.
type subscriberView struct {
	adminView

	IsNew         bool
	Tab           string
	Subscriber    models.Subscriber
	AllLists      []models.List
	Bounces       []models.Bounce
	CampaignViews []subCampaignView
	LinkClicks    []subLinkClick
	TotalViews    int
	Charts        types.JSONText
	TotalClicks   int
}

// subCampaignView and subLinkClick are the parsed rows of a subscriber's activity,
// unmarshalled from the JSON returned by GetSubscriberActivity.
type subCampaignView struct {
	ID           int       `json:"id"`
	Name         string    `json:"name"`
	Subject      string    `json:"subject"`
	ViewCount    int       `json:"view_count"`
	LastViewedAt time.Time `json:"last_viewed_at"`
}

type subLinkClick struct {
	URL             string    `json:"url"`
	CampaignID      int       `json:"campaign_id"`
	CampaignName    string    `json:"campaign_name"`
	CampaignSubject string    `json:"campaign_subject"`
	ClickCount      int       `json:"click_count"`
	LastClickedAt   time.Time `json:"last_clicked_at"`
}

// ViewSubscribers renders the HTML view for subscribers, optionally filtered by a list.
func (a *App) ViewSubscribers(c *echo.Context) error {
	listID, _ := strconv.Atoi(c.Param("id"))
	if listID > 0 {
		user := auth.GetUser(c)
		if err := user.HasListPerm(auth.PermTypeGet, listID); err != nil {
			return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", "lists"))
		}
	}

	subs, props, err := a.getSubscribers(c, listID)
	if err != nil {
		return err
	}

	// All lists the user can access, for the subscriber form's list selector.
	allLists, err := a.getViewableLists(c)
	if err != nil {
		return err
	}

	// Fetch the list being filtered by, if any, to show its name in the header.
	var curList *models.List
	if listID > 0 {
		if l, err := a.core.GetList(listID, ""); err == nil {
			curList = &l
		}
	}

	data := subscribersView{
		adminView:   newAdminView(c, a.i18n.T("globals.terms.subscribers"), "", "subscribers.all"),
		Subscribers: subs,
		Page:        props,
		AllLists:    allLists,
		CurrentList: curList,
	}

	return c.Render(http.StatusOK, "admin-subscribers", data)
}

// ViewNewSubscriber renders the new subscriber page.
func (a *App) ViewNewSubscriber(c *echo.Context) error {
	allLists, err := a.getViewableLists(c)
	if err != nil {
		return err
	}

	data := subscriberView{
		adminView: newAdminView(c, fmt.Sprintf("%s / %s", a.i18n.T("subscribers.newSubscriber"), a.i18n.T("globals.terms.subscribers")), "", "subscribers.all"),
		IsNew:     true,
		AllLists:  allLists,
	}

	return c.Render(http.StatusOK, "admin-subscriber", data)
}

// ViewSubscriber renders the profile and subscriptions tabs of a subscriber.
func (a *App) ViewSubscriber(c *echo.Context) error {
	out, err := a.getViewSubscriber(c)
	if err != nil {
		return err
	}

	allLists, err := a.getViewableLists(c)
	if err != nil {
		return err
	}

	tab := "edit"
	if strings.HasSuffix(c.Path(), "/lists") {
		tab = "lists"
	}

	data := subscriberView{
		adminView:  newAdminView(c, out.Email+" / "+a.i18n.T("globals.terms.subscribers"), "", "subscribers.all"),
		Tab:        tab,
		Subscriber: out,
		AllLists:   allLists,
	}

	return c.Render(http.StatusOK, "admin-subscriber", data)
}

// ViewSubscriberBounces renders the bounces tab of a subscriber.
func (a *App) ViewSubscriberBounces(c *echo.Context) error {
	user := auth.GetUser(c)
	allLists, bounceListIDs := user.GetPermittedLists(auth.PermTypeGet | auth.PermTypeManage)

	// Verify list perms.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeGet|auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	out, err := a.core.GetSubscriber(id, "", "")
	if err != nil {
		return err
	}
	maskRestrictedSubLists(user, &out)

	// Get bounces from the DB.
	bounces, _, err := a.core.QueryBounces(0, out.ID, "", "", "", "", 0, 1000, allLists, bounceListIDs)
	if err != nil {
		return err
	}

	data := subscriberView{
		adminView:  newAdminView(c, out.Email+" / "+a.i18n.T("globals.terms.subscribers"), "", "subscribers.all"),
		Tab:        "bounces",
		Subscriber: out,
		Bounces:    bounces,
	}

	return c.Render(http.StatusOK, "admin-subscriber", data)
}

// ViewSubscriberInfo renders the activity tab (campaign views and link clicks) of a subscriber.
func (a *App) ViewSubscriberInfo(c *echo.Context) error {
	out, err := a.getViewSubscriber(c)
	if err != nil {
		return err
	}

	user := auth.GetUser(c)
	allLists, listIDs := user.GetPermittedLists(auth.PermTypeGet | auth.PermTypeManage)
	act, err := a.core.GetSubscriberActivity(out.ID, allLists, listIDs)
	if err != nil {
		return err
	}

	data := subscriberView{
		adminView:  newAdminView(c, out.Email+" / "+a.i18n.T("globals.terms.subscribers"), "", "subscribers.all"),
		Tab:        "activity",
		Subscriber: out,
	}

	data.Charts, err = a.core.GetSubscriberCharts(out.ID, allLists, listIDs)
	if err != nil {
		return err
	}

	// Parse the JSON activity blobs.
	_ = json.Unmarshal(act.CampaignViews, &data.CampaignViews)
	_ = json.Unmarshal(act.LinkClicks, &data.LinkClicks)
	for _, v := range data.CampaignViews {
		data.TotalViews += v.ViewCount
	}
	for _, cl := range data.LinkClicks {
		data.TotalClicks += cl.ClickCount
	}

	return c.Render(http.StatusOK, "admin-subscriber", data)
}

// getViewSubscriber fetches the subscriber in the request path after validating permissions.
func (a *App) getViewSubscriber(c *echo.Context) (models.Subscriber, error) {
	user := auth.GetUser(c)

	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeGet|auth.PermTypeManage, []int{id}); err != nil {
		return models.Subscriber{}, err
	}

	out, err := a.core.GetSubscriber(id, "", "")
	if err != nil {
		return models.Subscriber{}, err
	}
	maskRestrictedSubLists(user, &out)

	return out, nil
}

// getViewableLists returns all active lists the user has access to.
func (a *App) getViewableLists(c *echo.Context) ([]models.List, error) {
	user := auth.GetUser(c)

	hasAllPerm, permittedIDs := user.GetPermittedLists(auth.PermTypeGet)
	return a.core.GetLists("", models.ListStatusActive, hasAllPerm, permittedIDs)
}

// getSubscribers queries subscribers from query params for the HTML view. If listID > 0,
// results are filtered by that list (the per-list subscribers view).
func (a *App) getSubscribers(c *echo.Context, listID int) ([]models.Subscriber, models.PageProps, error) {
	// GET and POST are both supported. `query` (SQL query) is sent by the frontend
	// as a POST form param.
	params, err := c.FormValues()
	if err != nil {
		return nil, models.PageProps{}, err
	}

	q := makeQuery(params, subscribersQueryDefaults)

	// Get the authenticated user.
	user := auth.GetUser(c)

	// Filter the list IDs by permission.
	var listIDs []int
	if listID > 0 {
		listIDs = user.GetPermittedListIDs(auth.PermTypeGet|auth.PermTypeManage, []int{listID})
	} else {
		ids, err := a.filterListQueryByPerm("list_id", params, user)
		if err != nil {
			return nil, models.PageProps{}, err
		}
		listIDs = ids
	}

	// Does the user have the subscribers:sql_query permission for advanced queries?
	query := formatSQLExp(q.Get("query"))
	if query != "" && !user.HasPerm(auth.PermSubscribersSqlQuery) {
		return nil, models.PageProps{}, echo.NewHTTPError(http.StatusForbidden,
			a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
	}

	// Run the DB query.
	pg := a.pg.NewFromURL(q)
	res, total, err := a.core.QuerySubscribers(
		q.Get("search"),
		query,
		listIDs,
		q.Get("subscription_status"),
		q.Get("status"),
		q.Get("order"),
		q.Get("order_by"),
		pg.Offset,
		pg.Limit)
	if err != nil {
		return nil, models.PageProps{}, err
	}

	for i := range res {
		maskRestrictedSubLists(user, &res[i])
	}

	return res, models.NewPageProps(q, total, pg.Page, pg.PerPage), nil
}

// GetSubscriber handles the retrieval of a single subscriber by ID.
func (a *App) GetSubscriber(c *echo.Context) error {
	user := auth.GetUser(c)

	// Check if the user has access to at least one of the lists on the subscriber.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeGet|auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	// Fetch the subscriber from the DB.
	out, err := a.core.GetSubscriber(id, "", "")
	if err != nil {
		return err
	}

	maskRestrictedSubLists(user, &out)

	return c.JSON(http.StatusOK, okResp{out})
}

// GetSubscriberActivity handles the retrieval of a subscriber's campaign views and link clicks.
func (a *App) GetSubscriberActivity(c *echo.Context) error {
	user := auth.GetUser(c)

	// Check if the user has access to at least one of the lists on the subscriber.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeGet|auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	// Fetch the subscriber activity from the DB.
	allLists, listIDs := user.GetPermittedLists(auth.PermTypeGet | auth.PermTypeManage)
	out, err := a.core.GetSubscriberActivity(id, allLists, listIDs)
	if err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{out})
}

// DeleteSubscriberActivity deletes a subscriber's campaign views or link clicks.
func (a *App) DeleteSubscriberActivity(c *echo.Context) error {
	id := getID(c)
	if err := a.hasSubPerm(auth.GetUser(c), auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	if err := a.core.DeleteSubscriberActivity(id, c.Param("type")); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// QuerySubscribers handles querying subscribers based on an arbitrary SQL expression.
func (a *App) QuerySubscribers(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	// Filter list IDs by permission.
	listIDs, err := a.filterListQueryByPerm("list_id", c.QueryParams(), user)
	if err != nil {
		return err
	}

	// Does the user have the subscribers:sql_query permission?
	query := formatSQLExp(c.FormValue("query"))
	if query != "" {
		if !user.HasPerm(auth.PermSubscribersSqlQuery) {
			return echo.NewHTTPError(http.StatusForbidden,
				a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
		}
	}

	var (
		searchStr        = strings.TrimSpace(c.FormValue("search"))
		subStatus        = c.FormValue("subscription_status")
		subscriberStatus = c.FormValue("status")
		order            = c.FormValue("order")
		orderBy          = c.FormValue("order_by")
		pg               = a.pg.NewFromURL(c.Request().URL.Query())
	)

	// Query subscribers from the DB.
	res, total, err := a.core.QuerySubscribers(searchStr, query, listIDs, subStatus, subscriberStatus, order, orderBy, pg.Offset, pg.Limit)
	if err != nil {
		return err
	}

	for i := range res {
		maskRestrictedSubLists(user, &res[i])
	}

	out := models.PageResults{
		Results: res,
		PageProps: models.PageProps{
			Query:   query,
			Search:  searchStr,
			Total:   total,
			Page:    pg.Page,
			PerPage: pg.PerPage,
		},
	}

	return c.JSON(http.StatusOK, okResp{out})
}

// ExportSubscribers handles querying subscribers based on an arbitrary SQL expression.
func (a *App) ExportSubscribers(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	// Filter list IDs by permission.
	listIDs, err := a.filterListQueryByPerm("list_id", c.QueryParams(), user)
	if err != nil {
		return err
	}

	// Export only specific subscriber IDs?
	subIDs, err := getQueryInts("id", c.QueryParams())
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidID"))
	}

	// Filter by subscription status
	subStatus := c.QueryParam("subscription_status")

	// Does the user have the subscribers:sql_query permission?
	var (
		searchStr = strings.TrimSpace(c.FormValue("search"))
		query     = formatSQLExp(c.FormValue("query"))
	)
	if query != "" {
		if !user.HasPerm(auth.PermSubscribersSqlQuery) {
			return echo.NewHTTPError(http.StatusForbidden,
				a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
		}
	}

	// Get the batched export iterator.
	exp, err := a.core.ExportSubscribers(searchStr, query, subIDs, listIDs, subStatus, c.QueryParam("status"), a.cfg.DBBatchSize)
	if err != nil {
		return err
	}

	var (
		hdr = c.Response().Header()
		wr  = csv.NewWriter(c.Response())
	)

	hdr.Set(echo.HeaderContentType, echo.MIMEOctetStream)
	hdr.Set("Content-type", "text/csv")
	hdr.Set(echo.HeaderContentDisposition, "attachment; filename="+"subscribers.csv")
	hdr.Set("Content-Transfer-Encoding", "binary")
	hdr.Set("Cache-Control", "no-cache")
	wr.Write([]string{"uuid", "email", "name", "attributes", "status", models.FieldCreatedAt, "updated_at"})

loop:
	// Iterate in batches until there are no more subscribers to export.
	for {
		out, err := exp()
		if err != nil {
			return err
		}
		if len(out) == 0 {
			break
		}

		for _, r := range out {
			if err = wr.Write([]string{r.UUID, r.Email, r.Name, r.Attribs, r.Status,
				r.CreatedAt.Time.String(), r.UpdatedAt.Time.String()}); err != nil {
				a.log.Printf("error streaming CSV export: %v", err)
				break loop
			}
		}

		// Flush CSV to stream after each batch.
		wr.Flush()
	}

	return nil
}

// CreateSubscriber handles the creation of a new subscriber.
func (a *App) CreateSubscriber(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	// Get and validate fields.
	var req subimporter.SubReq
	if err := c.Bind(&req); err != nil {
		return err
	}
	if err := a.validateSubscriberStatus(req.Status); err != nil {
		return err
	}

	// Validate fields.
	req, err := a.importer.ValidateFields(req)
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	}

	// Filter lists against the current user's permitted lists.
	listIDs := user.FilterListsByPerm(auth.PermTypeManage, req.Lists)

	// Not a single permitted list?
	if len(req.Lists) > 0 && len(listIDs) == 0 {
		return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", "lists"))
	}

	// Insert the subscriber into the DB.
	sub, _, err := a.core.InsertSubscriber(req.Subscriber, listIDs, nil, req.PreconfirmSubs, false)
	if err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{sub})
}

// UpdateSubscriber handles modification of a subscriber.
func (a *App) UpdateSubscriber(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	// Get and validate fields.
	req := struct {
		models.Subscriber
		Lists          []int `json:"lists"`
		PreconfirmSubs bool  `json:"preconfirm_subscriptions"`
	}{}
	if err := c.Bind(&req); err != nil {
		return err
	}
	if err := a.validateSubscriberStatus(req.Status); err != nil {
		return err
	}

	// Sanitize and validate the email field.
	if em, err := a.importer.SanitizeEmail(req.Email); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	} else {
		req.Email = em
	}

	if req.Name != "" && !strHasLen(req.Name, 1, stdInputMaxLen) {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidName"))
	}

	// Filter lists against the current user's permitted lists.
	listIDs := user.FilterListsByPerm(auth.PermTypeManage, req.Lists)

	// Not a single permitted list?
	if len(req.Lists) > 0 && len(listIDs) == 0 {
		return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", "lists"))
	}

	// Update the subscriber in the DB.
	id := getID(c)

	// Check if the user has access to at least one of the lists on the target subscriber.
	if err := a.hasSubPerm(user, auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	// Get the user's permitted lists to pass to the update query so that lists on the subscribers
	// to which they don't have permissions are preserved/left as-is when deleteLists=true.
	allPerm, permittedLists := user.GetPermittedLists(auth.PermTypeManage)
	if allPerm {
		permittedLists = []int{}
	}

	out, _, err := a.core.UpdateSubscriberWithLists(id, req.Subscriber, listIDs, nil, req.PreconfirmSubs, true, false, permittedLists, false)
	if err != nil {
		return err
	}

	maskRestrictedSubLists(user, &out)

	return c.JSON(http.StatusOK, okResp{out})
}

// PatchSubscriber handles partially modifying a subscriber.
// Only fields present in the request body are updated.
func (a *App) PatchSubscriber(c *echo.Context) error {
	user := auth.GetUser(c)
	id := getID(c)

	// Check if the user has access to at least one of the lists on the target subscriber
	// before fetching it. An empty PATCH body is otherwise a cross-scope PII read primitive.
	if err := a.hasSubPerm(user, auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	// Fetch the sub subscriber from the DB.
	sub, err := a.core.GetSubscriber(id, "", "")
	if err != nil {
		return err
	}

	// Prepopulate the incoming request struct with existing values.
	// Rather than tediously and conditionally checking each incoming field, we can simply
	// overwrite everything in the DB with the incoming fields+existing fields.
	req := struct {
		models.Subscriber
		Lists          *[]int `json:"lists"`
		PreconfirmSubs bool   `json:"preconfirm_subscriptions"`
	}{
		Subscriber: sub,
	}

	if err := c.Bind(&req); err != nil {
		return err
	}
	if err := a.validateSubscriberStatus(req.Status); err != nil {
		return err
	}

	if em, err := a.importer.SanitizeEmail(req.Email); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest, err.Error())
	} else {
		req.Email = em
	}

	if req.Name != "" && !strHasLen(req.Name, 1, stdInputMaxLen) {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidName"))
	}

	// If lists were explicitly sent, replace the existing subscriptions.
	overwriteSubs := false
	var listIDs []int
	if req.Lists != nil {
		overwriteSubs = true
		listIDs = user.FilterListsByPerm(auth.PermTypeManage, *req.Lists)
		if len(*req.Lists) > 0 && len(listIDs) == 0 {
			return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", "lists"))
		}
	}

	allPerm, permittedLists := user.GetPermittedLists(auth.PermTypeManage)
	if allPerm {
		permittedLists = []int{}
	}

	out, _, err := a.core.UpdateSubscriberWithLists(id, req.Subscriber, listIDs, nil, req.PreconfirmSubs, overwriteSubs, false, permittedLists, false)
	if err != nil {
		return err
	}

	maskRestrictedSubLists(user, &out)

	return c.JSON(http.StatusOK, okResp{out})
}

// SubscriberSendOptin sends an optin confirmation e-mail to a subscriber.
func (a *App) SubscriberSendOptin(c *echo.Context) error {
	user := auth.GetUser(c)

	// Fetch the subscriber.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	out, err := a.core.GetSubscriber(id, "", "")
	if err != nil {
		return err
	}

	// Trigger the opt-in confirmation e-mail hook.
	if _, err := a.fnOptinNotify(out, nil); err != nil {
		return echo.NewHTTPError(http.StatusInternalServerError, a.i18n.T("subscribers.errorSendingOptin"))
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// BlocklistSubscriber handles the blocklisting of a given subscriber.
func (a *App) BlocklistSubscriber(c *echo.Context) error {
	user := auth.GetUser(c)

	// Update the subscribers in the DB.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	if err := a.core.BlocklistSubscribers([]int{id}); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// BlocklistSubscribers handles the blocklisting of one or more subscribers.
func (a *App) BlocklistSubscribers(c *echo.Context) error {
	user := auth.GetUser(c)

	var req subQueryReq
	if err := c.Bind(&req); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.Ts("globals.messages.errorInvalidIDs", "error", err.Error()))
	}
	if len(req.SubscriberIDs) == 0 {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.Ts("globals.messages.errorInvalidIDs", "error", "ids"))
	}

	if err := a.hasSubPerm(user, auth.PermTypeManage, req.SubscriberIDs); err != nil {
		return err
	}

	// Update the subscribers in the DB.
	if err := a.core.BlocklistSubscribers(req.SubscriberIDs); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// ManageSubscriberLists handles bulk addition or removal of subscribers
// from or to one or more target lists.
// It takes either an ID in the URI, or a list of IDs in the request body.
func (a *App) ManageSubscriberLists(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	// Is it an /:id call?
	var (
		pID    = c.Param("id")
		subIDs []int
	)
	if pID != "" {
		id, _ := strconv.Atoi(pID)
		if id < 1 {
			return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidID"))
		}
		subIDs = append(subIDs, id)
	}

	var req subQueryReq
	if err := c.Bind(&req); err != nil {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.Ts("globals.messages.errorInvalidIDs", "error", err.Error()))
	}
	if len(req.SubscriberIDs) == 0 {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.noIDs"))
	}
	if len(subIDs) == 0 {
		subIDs = req.SubscriberIDs
	}
	if len(req.TargetListIDs) == 0 {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("subscribers.errorNoListsGiven"))
	}

	if err := a.hasSubPerm(user, auth.PermTypeManage, subIDs); err != nil {
		return err
	}

	// Filter lists against the current user's permitted lists.
	listIDs := user.FilterListsByPerm(auth.PermTypeManage, req.TargetListIDs)

	// User doesn't have the required list permissions.
	if len(listIDs) == 0 {
		return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", "lists"))
	}

	// Run the action in the DB.
	var err error
	switch req.Action {
	case models.SubscriptionActionAdd:
		if err := a.validateSubscriptionStatus(req.Status); err != nil {
			return err
		}
		err = a.core.AddSubscriptions(subIDs, listIDs, req.Status)
	case models.SubscriptionActionRemove:
		err = a.core.DeleteSubscriptions(subIDs, listIDs)
	case models.SubscriptionActionUnsubscribe:
		err = a.core.UnsubscribeLists(subIDs, listIDs, nil)
	default:
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidAction"))
	}

	if err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// DeleteSubscriber handles deletion of a single subscriber.
func (a *App) DeleteSubscriber(c *echo.Context) error {
	user := auth.GetUser(c)

	// Delete the subscribers from the DB.
	id := getID(c)
	if err := a.hasSubPerm(user, auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	if err := a.core.DeleteSubscribers([]int{id}, nil); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// DeleteSubscribers handles bulk deletion of one or more subscribers.
func (a *App) DeleteSubscribers(c *echo.Context) error {
	user := auth.GetUser(c)

	// Multiple IDs.
	ids, err := parseStringIDs(c.Request().URL.Query()["id"])
	if err != nil {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.Ts("globals.messages.errorInvalidIDs", "error", err.Error()))
	}
	if len(ids) == 0 {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.Ts("globals.messages.errorInvalidIDs", "error", "ids"))
	}

	if err := a.hasSubPerm(user, auth.PermTypeManage, ids); err != nil {
		return err
	}

	// Delete the subscribers from the DB.
	if err := a.core.DeleteSubscribers(ids, nil); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// DeleteSubscribersByQuery bulk deletes based on an
// arbitrary SQL expression.
func (a *App) DeleteSubscribersByQuery(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	var req subQueryReq
	if err := c.Bind(&req); err != nil {
		return err
	}

	req.Search = strings.TrimSpace(req.Search)
	req.Query = formatSQLExp(req.Query)
	if !req.All && req.Search == "" && req.Query == "" {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.Ts("globals.messages.invalidFields", "name", "query"))
	}

	// Does the user have the subscribers:sql_query permission?
	if req.Query != "" {
		if !user.HasPerm(auth.PermSubscribersSqlQuery) {
			return echo.NewHTTPError(http.StatusForbidden,
				a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
		}
	}

	// Filter list IDs against the current user's permitted lists.
	listIDs := user.GetPermittedListIDs(auth.PermTypeManage, req.ListIDs)

	// Delete the subscribers from the DB.
	if err := a.core.DeleteSubscribersByQuery(req.Search, req.Query, listIDs, req.SubscriptionStatus, req.SubscriberStatus); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// BlocklistSubscribersByQuery bulk blocklists subscribers
// based on an arbitrary SQL expression.
func (a *App) BlocklistSubscribersByQuery(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	var req subQueryReq
	if err := c.Bind(&req); err != nil {
		return err
	}

	req.Search = strings.TrimSpace(req.Search)
	req.Query = formatSQLExp(req.Query)
	if !req.All && req.Search == "" && req.Query == "" {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.Ts("globals.messages.invalidFields", "name", "query"))
	}
	// Does the user have the subscribers:sql_query permission?
	if req.Query != "" {
		if !user.HasPerm(auth.PermSubscribersSqlQuery) {
			return echo.NewHTTPError(http.StatusForbidden,
				a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
		}
	}

	// Filter list IDs against the current user's permitted lists.
	listIDs := user.GetPermittedListIDs(auth.PermTypeManage, req.ListIDs)

	// Update the subscribers in the DB.
	if err := a.core.BlocklistSubscribersByQuery(req.Search, req.Query, listIDs, req.SubscriptionStatus, req.SubscriberStatus); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// ManageSubscriberListsByQuery bulk adds/removes/unsubscribes subscribers
// from one or more lists based on an arbitrary SQL expression.
func (a *App) ManageSubscriberListsByQuery(c *echo.Context) error {
	// Get the authenticated user.
	user := auth.GetUser(c)

	var req subQueryReq
	if err := c.Bind(&req); err != nil {
		return err
	}
	if len(req.TargetListIDs) == 0 {
		return echo.NewHTTPError(http.StatusBadRequest,
			a.i18n.T("subscribers.errorNoListsGiven"))
	}

	req.Search = strings.TrimSpace(req.Search)
	req.Query = formatSQLExp(req.Query)
	if !req.All && req.Search == "" && req.Query == "" {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.Ts("globals.messages.invalidFields", "name", "query"))
	}

	// Does the user have the subscribers:sql_query permission?
	if req.Query != "" {
		if !user.HasPerm(auth.PermSubscribersSqlQuery) {
			return echo.NewHTTPError(http.StatusForbidden,
				a.i18n.Ts("globals.messages.permissionDenied", "name", auth.PermSubscribersSqlQuery))
		}
	}

	// Filter lists against the current user's permitted lists.
	sourceListIDs := user.GetPermittedListIDs(auth.PermTypeManage, req.ListIDs)
	targetListIDs := user.FilterListsByPerm(auth.PermTypeManage, req.TargetListIDs)

	// Run the action in the DB.
	var err error
	switch req.Action {
	case models.SubscriptionActionAdd:
		if err := a.validateSubscriptionStatus(req.Status); err != nil {
			return err
		}
		err = a.core.AddSubscriptionsByQuery(req.Search, req.Query, sourceListIDs, targetListIDs, req.Status, req.SubscriptionStatus, req.SubscriberStatus)
	case models.SubscriptionActionRemove:
		err = a.core.DeleteSubscriptionsByQuery(req.Search, req.Query, sourceListIDs, targetListIDs, req.SubscriptionStatus, req.SubscriberStatus)
	case models.SubscriptionActionUnsubscribe:
		err = a.core.UnsubscribeListsByQuery(req.Search, req.Query, sourceListIDs, targetListIDs, req.SubscriptionStatus, req.SubscriberStatus)
	default:
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidAction"))
	}

	if err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// DeleteSubscriberBounces deletes all the bounces on a subscriber.
func (a *App) DeleteSubscriberBounces(c *echo.Context) error {
	id := getID(c)

	// Check if the user has access to at least one of the lists on the subscriber.
	if err := a.hasSubPerm(auth.GetUser(c), auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	// Delete the bounces from the DB.
	if err := a.core.DeleteSubscriberBounces(id, ""); err != nil {
		return err
	}

	return c.JSON(http.StatusOK, okResp{true})
}

// ExportSubscriberData pulls the subscriber's profile,
// list subscriptions, campaign views and clicks and produces
// a JSON report. This is a privacy feature and depends on the
// configuration in a.Constants.Privacy.
func (a *App) ExportSubscriberData(c *echo.Context) error {
	// Get the subscriber's data. A single query that gets the profile,
	// list subscriptions, campaign views, and link clicks. Names of
	// private lists are replaced with "Private list".
	id := getID(c)

	// Check if the user has access to at least one of the lists on the subscriber.
	if err := a.hasSubPerm(auth.GetUser(c), auth.PermTypeGet|auth.PermTypeManage, []int{id}); err != nil {
		return err
	}

	user := auth.GetUser(c)
	allLists, listIDs := user.GetPermittedLists(auth.PermTypeGet | auth.PermTypeManage)
	_, b, err := a.exportSubscriberData(id, "", a.cfg.Privacy.Exportable, allLists, listIDs)
	if err != nil {
		a.log.Printf("error exporting subscriber data: %s", err)
		return echo.NewHTTPError(http.StatusInternalServerError,
			a.i18n.Ts("globals.messages.errorFetching", "name", "{globals.terms.subscribers}", "error", err.Error()))
	}

	// Set headers to force the browser to prompt for download.
	c.Response().Header().Set("Cache-Control", "no-cache")
	c.Response().Header().Set("Content-Disposition", `attachment; filename="data.json"`)
	return c.Blob(http.StatusOK, "application/json", b)
}

// exportSubscriberData collates the data of a subscriber including profile,
// subscriptions, campaign_views, link_clicks (if they're enabled in the config)
// and returns a formatted, indented JSON payload. Either takes a numeric id
// and an empty subUUID or takes 0 and a string subUUID.
func (a *App) exportSubscriberData(id int, subUUID string, exportables map[string]bool, allLists bool, listIDs []int) (models.SubscriberExportProfile, []byte, error) {
	data, err := a.core.GetSubscriberProfileForExport(id, subUUID, allLists, listIDs)
	if err != nil {
		return data, nil, err
	}

	// Filter out the non-exportable items.
	if _, ok := exportables["profile"]; !ok {
		data.Profile = nil
	}
	if _, ok := exportables["subscriptions"]; !ok {
		data.Subscriptions = nil
	}
	if _, ok := exportables["campaign_views"]; !ok {
		data.CampaignViews = nil
	}
	if _, ok := exportables["link_clicks"]; !ok {
		data.LinkClicks = nil
	}

	// Marshal the data into an indented payload.
	b, err := json.MarshalIndent(data, "", "  ")
	if err != nil {
		a.log.Printf("error marshalling subscriber export data: %v", err)
		return data, nil, err
	}

	return data, b, nil
}

// maskRestrictedSubLists replaces list names with "*Unknown" for lists
// the user doesn't have read access to. This appears on the subscriber
// details UI and prevents users without access to certain lists from seeing their names.
func maskRestrictedSubLists(user auth.User, sub *models.Subscriber) {
	if user.HasPerm(auth.PermListManageAll) || user.HasPerm(auth.PermListGetAll) {
		return
	}

	// Hacky JSON manipulation (for now).
	var lists []map[string]interface{}
	if err := json.Unmarshal(sub.Lists, &lists); err != nil || len(lists) == 0 {
		return
	}

	for i, l := range lists {
		id, _ := l["id"].(float64)
		if user.HasListPerm(auth.PermTypeGet, int(id)) != nil &&
			user.HasListPerm(auth.PermTypeManage, int(id)) != nil {
			lists[i]["name"] = "*Unknown"
			lists[i]["restricted"] = true
			delete(lists[i], "description")
		}
	}

	if b, err := json.Marshal(lists); err == nil {
		sub.Lists = b
	}
}

// hasSubPerm checks whether the current user has permission to access the given list
// of subscriber IDs.
func (a *App) hasSubPerm(u auth.User, types auth.PermType, subIDs []int) error {
	allPerm, listIDs := u.GetPermittedLists(types)

	// User has the corresponding blanket list permission.
	if allPerm {
		return nil
	}

	// Check whether the subscribers have the list IDs permitted to the user.
	res, err := a.core.HasSubscriberLists(subIDs, listIDs)
	if err != nil {
		return err
	}

	for id, has := range res {
		if !has {
			return echo.NewHTTPError(http.StatusForbidden, a.i18n.Ts("globals.messages.permissionDenied", "name", fmt.Sprintf("subscriber: %d", id)))
		}
	}

	return nil
}

// filterListQueryByPerm filters the list IDs in the query params and returns the list IDs to which the user has access.
func (a *App) filterListQueryByPerm(param string, qp url.Values, user auth.User) ([]int, error) {
	var listIDs []int

	// If there are incoming list query params, filter them by permission.
	if qp.Has(param) {
		ids, err := getQueryInts(param, qp)
		if err != nil {
			return nil, echo.NewHTTPError(http.StatusBadRequest, a.i18n.T("globals.messages.invalidID"))
		}

		listIDs = ids
	}

	return user.GetPermittedListIDs(auth.PermTypeGet|auth.PermTypeManage, listIDs), nil
}

// formatSQLExp does basic sanitisation on arbitrary
// SQL query expressions coming from the frontend.
func formatSQLExp(q string) string {
	q = strings.TrimSpace(q)
	if len(q) == 0 {
		return ""
	}

	// Remove semicolon suffix.
	if q[len(q)-1] == ';' {
		q = q[:len(q)-1]
	}
	return q
}

// makeOptinNotifyHook returns an enclosed callback that sends optin confirmation e-mails.
// This is plugged into the 'core' package to send optin confirmations when a new subscriber is
// created via `core.CreateSubscriber()`.
func makeOptinNotifyHook(unsubHeader bool, u *UrlConfig, q *models.Queries, i *i18n.I18n) func(sub models.Subscriber, listIDs []int) (int, error) {
	return func(sub models.Subscriber, listIDs []int) (int, error) {
		// Fetch double opt-in lists from the given list IDs.
		// Get the list of subscription lists where the subscriber hasn't confirmed.
		var lists = []models.List{}
		if err := q.GetSubscriberLists.Select(&lists, sub.ID, nil, pq.Array(listIDs), nil, models.SubscriptionStatusUnconfirmed, models.ListOptinDouble); err != nil {
			lo.Printf("error fetching lists for opt-in: %s", err)
			return 0, err
		}

		// None.
		if len(lists) == 0 {
			return 0, nil
		}

		var (
			out      = subOptin{Subscriber: sub, Lists: lists}
			qListIDs = url.Values{}
		)

		// Construct the opt-in URL with list IDs.
		for _, l := range out.Lists {
			qListIDs.Add("l", l.UUID)
		}
		out.OptinURL = fmt.Sprintf(u.OptinURL, sub.UUID, qListIDs.Encode())
		out.UnsubURL = fmt.Sprintf(u.UnsubURL, dummyUUID, sub.UUID)

		// Unsub headers.
		hdr := textproto.MIMEHeader{}
		hdr.Set(models.EmailHeaderSubscriberUUID, sub.UUID)

		// Attach List-Unsubscribe headers?
		if unsubHeader {
			unsubURL := fmt.Sprintf(u.UnsubURL, dummyUUID, sub.UUID)
			hdr.Set("List-Unsubscribe-Post", "List-Unsubscribe=One-Click")
			hdr.Set("List-Unsubscribe", `<`+unsubURL+`>`)
		}

		// Send the e-mail.
		if err := notifs.Notify([]string{sub.Email}, i.T("globals.buttons.confirmSubscription"), notifs.TplSubscriberOptin, out, hdr); err != nil {
			lo.Printf("error sending opt-in e-mail for subscriber %d (%s): %s", sub.ID, sub.UUID, err)
			return 0, err
		}

		return len(lists), nil
	}
}

// validateSubscriberStatus sanity checks subscriber status.
func (a *App) validateSubscriberStatus(status string) error {
	if status != "" && status != models.SubscriberStatusEnabled && status != models.SubscriberStatusDisabled && status != models.SubscriberStatusBlockListed {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.Ts("globals.messages.invalidFields", "name", "status"))
	}
	return nil
}

// validateSubscriptionStatus sanity checks subscription status.
func (a *App) validateSubscriptionStatus(status string) error {
	if status != "" && status != models.SubscriptionStatusUnconfirmed && status != models.SubscriptionStatusConfirmed && status != models.SubscriptionStatusUnsubscribed {
		return echo.NewHTTPError(http.StatusBadRequest, a.i18n.Ts("globals.messages.invalidFields", "name", "status"))
	}
	return nil
}