package console

import (
	"context"
	"encoding/json"
	"net/http"
	"strconv"
	"strings"
	"testing"
	"time"

	"github.com/carlosframework/platform/internal/mail"
	"github.com/carlosframework/platform/internal/release"
)

func bounceTo(recipient, message string, at time.Time) mail.Event {
	return mail.Event{Type: mail.EventBounce, Domain: "lgc.li", MessageID: message, At: at,
		Outcomes: []mail.Outcome{{Recipient: recipient, Status: "5.1.1", Diagnostic: "550 5.1.1 no such user"}},
		Detail:   mail.EventDetail{BounceType: "Permanent", BounceSubType: "NoEmail"}}
}

// A bounce is kept as what it was: which message, to whom, and what the
// receiving server answered — and a notification SNS delivers twice is
// still one row.
func TestTheMailLogKeepsEachRecipientsOutcomeOnce(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Date(2026, 9, 15, 9, 0, 0, 0, time.UTC)
	ev := bounceTo("ann@example.com", "0102-ann", now.Add(-time.Minute))

	for i := 0; i < 2; i++ {
		if err := fx.st.RecordMailMessageEvent(ctx, "beb", "jb-book", "sns-1", ev, true, now); err != nil {
			t.Fatal(err)
		}
	}
	got, err := fx.st.MailEvents(ctx, "beb", "jb-book", MailEventQuery{}, now)
	if err != nil {
		t.Fatal(err)
	}
	if len(got) != 1 {
		t.Fatalf("rows = %d, want 1 — SNS redelivered one notification: %+v", len(got), got)
	}
	e := got[0]
	if e.MessageID != "0102-ann" || e.Type != mail.EventBounce || e.Recipient != "ann@example.com" ||
		e.Status != "5.1.1" || e.Detail != "Permanent/NoEmail" || !e.BySender {
		t.Errorf("row = %+v", e)
	}
	if !strings.Contains(e.Diagnostic, "no such user") {
		t.Errorf("the receiving server's answer was not kept: %q", e.Diagnostic)
	}
}

func TestTheMailLogFindsAMessageOrAnAddress(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Date(2026, 9, 15, 9, 0, 0, 0, time.UTC)
	for i, c := range []struct{ to, id string }{
		{"ann@example.com", "m-ann"}, {"bob@example.com", "m-bob"}, {"ann@example.com", "m-ann-2"},
	} {
		ev := bounceTo(c.to, c.id, now.Add(-time.Duration(3-i)*time.Hour))
		if err := fx.st.RecordMailMessageEvent(ctx, "beb", "jb-book", "sns-"+c.id, ev, true, now); err != nil {
			t.Fatal(err)
		}
	}
	for _, c := range []struct {
		name string
		q    MailEventQuery
		want []string
	}{
		{"everything, newest first", MailEventQuery{}, []string{"m-ann-2", "m-bob", "m-ann"}},
		{"one message", MailEventQuery{MessageID: "m-bob"}, []string{"m-bob"}},
		{"one address, however it is typed", MailEventQuery{Recipient: "ANN@Example.com"}, []string{"m-ann-2", "m-ann"}},
		{"since", MailEventQuery{Since: now.Add(-150 * time.Minute)}, []string{"m-ann-2", "m-bob"}},
		{"a limit", MailEventQuery{Limit: 1}, []string{"m-ann-2"}},
	} {
		t.Run(c.name, func(t *testing.T) {
			got, err := fx.st.MailEvents(ctx, "beb", "jb-book", c.q, now)
			if err != nil {
				t.Fatal(err)
			}
			var ids []string
			for _, e := range got {
				ids = append(ids, e.MessageID)
			}
			if strings.Join(ids, ",") != strings.Join(c.want, ",") {
				t.Errorf("got %v, want %v", ids, c.want)
			}
		})
	}
	if other, _ := fx.st.MailEvents(ctx, "beb", "another-app", MailEventQuery{}, now); len(other) != 0 {
		t.Errorf("another app read this app's log: %+v", other)
	}
}

// The log holds recipients' addresses, so it is a window. A row past it is
// neither shown nor kept.
func TestTheMailLogForgetsAfterThirtyDays(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	then := time.Date(2026, 8, 1, 9, 0, 0, 0, time.UTC)
	if err := fx.st.RecordMailMessageEvent(ctx, "beb", "jb-book", "sns-old", bounceTo("ann@example.com", "m-old", then), true, then); err != nil {
		t.Fatal(err)
	}
	now := then.Add(mailEventsRetention + time.Hour)
	if got, _ := fx.st.MailEvents(ctx, "beb", "jb-book", MailEventQuery{}, now); len(got) != 0 {
		t.Errorf("a row past the window was shown: %+v", got)
	}
	// The next notification prunes it for good.
	if err := fx.st.RecordMailMessageEvent(ctx, "beb", "jb-book", "sns-new", bounceTo("bob@example.com", "m-new", now), true, now); err != nil {
		t.Fatal(err)
	}
	var n int
	if err := fx.st.db.QueryRow(`SELECT count(*) FROM mail_events WHERE message_id = 'm-old'`).Scan(&n); err != nil {
		t.Fatal(err)
	}
	if n != 0 {
		t.Errorf("the old row is still in the table")
	}
}

// Two apps can send from one domain. The log is narrower wherever SES names
// the sender, because a recipient's address is one app's business, not
// every app's that happens to share a From domain; the counts follow the
// same caller-named sender now (design §8) and fall back to charging every
// app on the domain only when SES names nobody CARLOS minted.
func TestTheMailLogGoesToTheAppSESSaysSentIt(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	for _, app := range []string{"jb-book", "jb-site"} {
		if _, err := fx.st.CreateAppIn(OpsAccountID, app, "bill@tito.io", now); err != nil {
			t.Fatal(err)
		}
	}
	rec := &mail.DomainRecord{
		Domain: "lgc.li", Kind: mail.KindCustom, Apps: []string{"jb-book", "jb-site"},
		Regions: map[string]*mail.RegionState{"eu-west-1": {
			IdentityARN: "arn:aws:ses:eu-west-1:1:identity/lgc.li", MailFrom: "mail-eu-west-1.lgc.li", Status: mail.StatusVerified,
		}},
		CreatedAt: now,
	}
	if _, err := mail.PutDomain(ctx, fx.rel, release.OpsAccount, rec, ""); err != nil {
		t.Fatal(err)
	}
	logOf := func(app string) []MailEvent {
		t.Helper()
		got, err := fx.st.MailEvents(ctx, release.OpsAccount, app, MailEventQuery{}, time.Now())
		if err != nil {
			t.Fatal(err)
		}
		return got
	}

	named := bounceTo("ann@example.com", "m-named", now)
	named.Caller = mail.UserName(release.OpsAccount, "jb-book")
	fx.srv.recordAndEnforceMail(ctx, named, "sns-named")

	if got := logOf("jb-book"); len(got) != 1 || !got[0].BySender {
		t.Errorf("the app SES named should have the row, marked as its own: %+v", got)
	}
	if got := logOf("jb-site"); len(got) != 0 {
		t.Errorf("the other app on the domain was shown another app's recipient: %+v", got)
	}
	// THE VISIBLE CHANGE, and it belongs in the roll note (design §8).
	// Before senders, every app on a shared domain was charged for every
	// event on that domain. Now only the app SES named is charged, so a
	// sibling's cap stops being consumed by another app's sends.
	day := now.UTC().Format("2006-01-02")
	if c, _ := fx.st.MailCounts(ctx, release.OpsAccount, "jb-book", "", day); c.Bounces != 1 {
		t.Errorf("jb-book bounces = %d, want the 1 SES attributed to it", c.Bounces)
	}
	if c, _ := fx.st.MailCounts(ctx, release.OpsAccount, "jb-site", "", day); c.Bounces != 0 {
		t.Errorf("jb-site bounces = %d, want 0 — a sibling on the same domain is no longer charged", c.Bounces)
	}

	// SES naming one of CARLOS's own identities that is not a live app gives
	// the row to nobody — the name is the sender, and it is not here to be
	// told. It does NOT fall back to the domain's other apps.
	stray := bounceTo("eve@example.com", "m-stray", now)
	stray.Caller = mail.UserName(release.OpsAccount, "not-an-app")
	fx.srv.recordAndEnforceMail(ctx, stray, "sns-stray")
	for _, app := range []string{"jb-book", "jb-site"} {
		for _, e := range logOf(app) {
			if e.MessageID == "m-stray" {
				t.Errorf("%s was given a row SES attributed to another CARLOS app: %+v", app, e)
			}
		}
	}

	// SES naming nobody CARLOS minted falls back to the domain, and says so
	// on the row.
	for i, caller := range []string{"", "someone-else"} {
		ev := bounceTo("bob@example.com", "m-unnamed", now)
		ev.Caller = caller
		fx.srv.recordAndEnforceMail(ctx, ev, "sns-unnamed-"+string(rune('a'+i)))
	}
	for _, app := range []string{"jb-book", "jb-site"} {
		var unnamed []MailEvent
		for _, e := range logOf(app) {
			if e.MessageID == "m-unnamed" {
				unnamed = append(unnamed, e)
			}
		}
		if len(unnamed) != 2 {
			t.Errorf("%s: %d domain-attributed rows, want 2", app, len(unnamed))
		}
		for _, e := range unnamed {
			if e.BySender {
				t.Errorf("%s: a row attributed by domain claims SES named the sender: %+v", app, e)
			}
		}
	}

	// An unknown caller keeps today's behaviour exactly: the domain is all
	// there is to go on, so every app on it is charged.
	for _, app := range []string{"jb-book", "jb-site"} {
		if c, _ := fx.st.MailCounts(ctx, release.OpsAccount, app, "", day); c.Bounces < 2 {
			t.Errorf("%s bounces = %d, want the two domain-attributed ones", app, c.Bounces)
		}
	}

	// The stray caller parses into a well-formed account and app, so it is
	// "named" — but "not-an-app" is not a live app, and a count row keyed on
	// a name no app answers to is a row nothing reads and nothing reaps.
	if c, _ := fx.st.MailCounts(ctx, release.OpsAccount, "not-an-app", "", day); c.Bounces != 0 {
		t.Errorf("a name that is not an app was given %d bounces", c.Bounces)
	}
}

// An event whose caller names a slugged sender is charged to that sender
// alone, resolved through the caller index rather than by parsing.
func TestAnIndexedCallerIsChargedToItsOwnSender(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Date(2026, 9, 15, 12, 0, 0, 0, time.UTC)
	if _, err := fx.st.CreateAppIn(OpsAccountID, "jb-book", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	names, err := mail.SenderNames(release.OpsAccount, "jb-book", "book.example.com", "")
	if err != nil {
		t.Fatal(err)
	}
	if err := mail.PutCallerIndex(ctx, fx.rel, names.IAMUser, release.OpsAccount, "jb-book", "book.example.com", now); err != nil {
		t.Fatal(err)
	}
	ev := bounceTo("ann@example.com", "m-instance", now)
	// bounceTo sets At but not SentAt, and the count buckets by SentAt
	// (falling back to time.Now() when it's zero) — set it explicitly so
	// this test's fixed date is the day the count actually lands in,
	// rather than depending on which real day the suite happens to run.
	ev.SentAt = now
	ev.Caller = names.IAMUser
	fx.srv.recordAndEnforceMail(ctx, ev, "sns-instance")

	day := now.UTC().Format("2006-01-02")
	one, _ := fx.st.MailCounts(ctx, release.OpsAccount, "jb-book", "book.example.com", day)
	if one.Bounces != 1 {
		t.Fatalf("the named sender's counts = %+v, want 1 bounce", one)
	}
	appSender, _ := fx.st.MailCounts(ctx, release.OpsAccount, "jb-book", mail.SenderAppID, day)
	if appSender.Bounces != 0 {
		t.Fatalf("the app sender was charged for an instance sender's bounce: %+v", appSender)
	}
}

// A trashed app is told nothing new, and a name that is freed — by a purge,
// a rename or a move to another account — hands none of its email history
// to whatever holds the name next. The history of an app that is renamed or
// moved goes with it.
func TestTheMailLogBelongsToTheAppNotItsName(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	logOf := func(acct, app string) int {
		t.Helper()
		got, err := fx.st.MailEvents(ctx, acct, app, MailEventQuery{}, time.Now())
		if err != nil {
			t.Fatal(err)
		}
		return len(got)
	}
	send := func(app, message string) {
		t.Helper()
		ev := bounceTo("ann@example.com", message, now)
		ev.Caller = mail.UserName(release.OpsAccount, app)
		fx.srv.recordAndEnforceMail(ctx, ev, "sns-"+message)
	}
	for _, app := range []string{"harbour", "renamed-from", "moving"} {
		if _, err := fx.st.CreateAppIn(OpsAccountID, app, "bill@tito.io", now.Add(-time.Hour)); err != nil {
			t.Fatal(err)
		}
	}

	// Trashed: nothing new is kept.
	send("harbour", "m-live")
	if err := fx.st.TrashAppRow(OpsAccountID, "harbour", now); err != nil {
		t.Fatal(err)
	}
	send("harbour", "m-trashed")
	if n := logOf(release.OpsAccount, "harbour"); n != 1 {
		t.Errorf("harbour has %d rows, want 1 — nothing arriving after the trash is kept", n)
	}

	// Purged: the name starts clean for the next app to claim it.
	if err := fx.st.PurgeAppRow(OpsAccountID, "harbour"); err != nil {
		t.Fatal(err)
	}
	if n := logOf(release.OpsAccount, "harbour"); n != 0 {
		t.Errorf("a purged app's history is still there for its name: %d rows", n)
	}

	// Renamed: the history goes with the app, and the old name holds none.
	send("renamed-from", "m-before-rename")
	if err := fx.st.RenameAppIn(OpsAccountID, "renamed-from", "renamed-to"); err != nil {
		t.Fatal(err)
	}
	if logOf(release.OpsAccount, "renamed-to") != 1 || logOf(release.OpsAccount, "renamed-from") != 0 {
		t.Errorf("after a rename: to=%d from=%d, want 1 and 0",
			logOf(release.OpsAccount, "renamed-to"), logOf(release.OpsAccount, "renamed-from"))
	}

	// Moved to another account: the same, keyed by the new account's sqid.
	send("moving", "m-before-move")
	if err := fx.st.CreateAccount(99, "dest", "Destination", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	if err := fx.st.MoveApp("moving", OpsAccountID, 99); err != nil {
		t.Fatal(err)
	}
	if logOf("dest", "moving") != 1 || logOf(release.OpsAccount, "moving") != 0 {
		t.Errorf("after a move: dest=%d ops=%d, want 1 and 0", logOf("dest", "moving"), logOf(release.OpsAccount, "moving"))
	}
}

// A bounce, a complaint or a delivery can arrive long after its message was
// sent, and the rate limiter divides a day's bounces by that same day's
// sends. So the counts file an event under the day it was SENT, whatever
// day it arrives — a late bounce landing on the new day would read as a
// bounce rate over a handful of sends and pause a healthy tenant.
func TestALateBounceCountsAgainstTheDayItWasSent(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	if _, err := fx.st.CreateAppIn(OpsAccountID, "harbour", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	rec := &mail.DomainRecord{
		Domain: "harbour.example", Kind: mail.KindCustom, Apps: []string{"harbour"},
		Regions: map[string]*mail.RegionState{"eu-west-1": {
			IdentityARN: "arn:aws:ses:eu-west-1:1:identity/harbour.example", Status: mail.StatusVerified,
		}},
		CreatedAt: now,
	}
	if _, err := mail.PutDomain(ctx, fx.rel, release.OpsAccount, rec, ""); err != nil {
		t.Fatal(err)
	}
	sent := time.Date(2026, 9, 14, 23, 50, 0, 0, time.UTC)
	ev := bounceTo("ann@example.com", "m-late", sent.Add(15*time.Minute)) // bounces at 00:05 the next day
	ev.Domain, ev.SentAt = "harbour.example", sent
	fx.srv.recordAndEnforceMail(ctx, ev, "sns-late")

	for day, want := range map[string]int{"2026-09-14": 1, "2026-09-15": 0} {
		c, err := fx.st.MailCounts(ctx, release.OpsAccount, "harbour", "", day)
		if err != nil {
			t.Fatal(err)
		}
		if c.Bounces != want {
			t.Errorf("bounces on %s = %d, want %d — counted against the day it arrived, not the day it was sent", day, c.Bounces, want)
		}
	}
}

// A row attributed only by a shared sending domain may name another app's
// recipient. It stays in the account where that other app lives: a move to
// another account carries only the rows SES said were the app's own. A
// rename within the account carries both, since the same members could read
// both before and after.
func TestAMoveLeavesDomainAttributedRowsBehind(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	for _, app := range []string{"moving", "renaming"} {
		if _, err := fx.st.CreateAppIn(OpsAccountID, app, "bill@tito.io", now.Add(-time.Hour)); err != nil {
			t.Fatal(err)
		}
		for i, bySender := range []bool{true, false} {
			ev := bounceTo("ann@example.com", app+"-"+strconv.FormatBool(bySender), now)
			if err := fx.st.RecordMailMessageEvent(ctx, release.OpsAccount, app, "sns-"+app+strconv.Itoa(i), ev, bySender, now); err != nil {
				t.Fatal(err)
			}
		}
	}
	if err := fx.st.CreateAccount(99, "dest", "Destination", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	if err := fx.st.MoveApp("moving", OpsAccountID, 99); err != nil {
		t.Fatal(err)
	}
	moved, _ := fx.st.MailEvents(ctx, "dest", "moving", MailEventQuery{}, time.Now())
	if len(moved) != 1 || !moved[0].BySender {
		t.Errorf("a move to another account carried %+v, want only the app's own row", moved)
	}
	if left, _ := fx.st.MailEvents(ctx, release.OpsAccount, "moving", MailEventQuery{}, time.Now()); len(left) != 0 {
		t.Errorf("rows were left under the old account's key: %+v", left)
	}

	if err := fx.st.RenameAppIn(OpsAccountID, "renaming", "renamed"); err != nil {
		t.Fatal(err)
	}
	if renamed, _ := fx.st.MailEvents(ctx, release.OpsAccount, "renamed", MailEventQuery{}, time.Now()); len(renamed) != 2 {
		t.Errorf("a rename within the account carried %d rows, want both", len(renamed))
	}
}

// A reaped account's email history goes with it.
func TestReapingAnAccountForgetsItsMailLog(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	if err := fx.st.CreateAccount(99, "reaped", "Going", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	if err := fx.st.RecordMailMessageEvent(ctx, "reaped", "gone", "sns-1", bounceTo("ann@example.com", "m", now), true, now); err != nil {
		t.Fatal(err)
	}
	if err := fx.st.ReapAccount(99, now); err != nil {
		t.Fatal(err)
	}
	var n int
	fx.st.db.QueryRow(`SELECT count(*) FROM mail_events WHERE account = 'reaped'`).Scan(&n)
	if n != 0 {
		t.Errorf("%d rows outlived the account", n)
	}
}

// A prune that fails is not stamped, so the next notification tries again
// rather than waiting out the hour.
func TestAFailedPruneIsTriedAgain(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx, cancel := context.WithCancel(context.Background())
	cancel()
	if err := fx.st.pruneMailEvents(ctx, time.Now()); err == nil {
		t.Fatal("a prune with a cancelled context should fail")
	}
	if got := fx.st.mailEventsPruned.Load(); got != 0 {
		t.Errorf("the stamp was left at %d after a failed prune", got)
	}
}

// The log's domain fallback gives recipients' addresses only to a domain its
// account has verified. The counts keep matching as they always did.
func TestTheDomainFallbackNeedsAVerifiedDomain(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Now()
	if _, err := fx.st.CreateAppIn(OpsAccountID, "squatter", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	rec := &mail.DomainRecord{
		Domain: "mail-eu-west-1.lgc.li", Kind: mail.KindCustom, Apps: []string{"squatter"},
		Regions: map[string]*mail.RegionState{"eu-west-1": {
			IdentityARN: "arn:aws:ses:eu-west-1:1:identity/mail-eu-west-1.lgc.li", Status: mail.StatusPending,
		}},
		CreatedAt: now,
	}
	if _, err := mail.PutDomain(ctx, fx.rel, release.OpsAccount, rec, ""); err != nil {
		t.Fatal(err)
	}
	ev := bounceTo("ann@example.com", "m-unverified", now)
	ev.Domain, ev.SentAt = "mail-eu-west-1.lgc.li", now
	fx.srv.recordAndEnforceMail(ctx, ev, "sns-unverified")
	if got, _ := fx.st.MailEvents(ctx, release.OpsAccount, "squatter", MailEventQuery{}, time.Now()); len(got) != 0 {
		t.Errorf("an unverified domain was given recipients' addresses: %+v", got)
	}
	if c, _ := fx.st.MailCounts(ctx, release.OpsAccount, "squatter", "", now.UTC().Format("2006-01-02")); c.Bounces != 1 {
		t.Errorf("bounces = %d — the counts' matching is not this change's to narrow", c.Bounces)
	}
}

// End to end through the SNS door: a signed notification lands in the log
// under SNS's own MessageId, with what the receiving server said, and a
// redelivery of the same notification is still one row.
func TestANotificationThroughTheDoorLandsInTheLog(t *testing.T) {
	fx := newConsoleFixture(t)
	fx.srv.cfg.MailDeployment.SNSTopicARN = "arn:aws:sns:eu-west-1:1:carlos-mail"
	fx.srv.snsVerify = func(_ snsEnvelope) error { return nil }
	ctx := context.Background()
	now := time.Now()
	if _, err := fx.st.CreateAppIn(OpsAccountID, "jb-book", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	rec := &mail.DomainRecord{
		Domain: "lgc.li", Kind: mail.KindCustom, Apps: []string{"jb-book"},
		Regions: map[string]*mail.RegionState{"eu-west-1": {
			IdentityARN: "arn:aws:ses:eu-west-1:1:identity/lgc.li", Status: mail.StatusVerified,
		}},
		CreatedAt: now,
	}
	if _, err := mail.PutDomain(ctx, fx.rel, release.OpsAccount, rec, ""); err != nil {
		t.Fatal(err)
	}
	message, _ := json.Marshal(map[string]any{
		"eventType": "Bounce",
		"mail": map[string]any{"source": "bookings@lgc.li", "messageId": "0102-door", "timestamp": now.UTC().Format(time.RFC3339),
			"tags": map[string][]string{"ses:caller-identity": {mail.UserName(release.OpsAccount, "jb-book")}}},
		"bounce": map[string]any{"bounceType": "Permanent", "bounceSubType": "NoEmail", "timestamp": now.UTC().Format("2006-01-02T15:04:05.000Z"),
			"bouncedRecipients": []map[string]string{{"emailAddress": "ann@example.com", "status": "5.1.1", "diagnosticCode": "550 no such user"}}},
	})
	envelope, _ := json.Marshal(map[string]string{
		"Type": "Notification", "MessageId": "sns-door-1", "TopicArn": "arn:aws:sns:eu-west-1:1:carlos-mail",
		"Message": string(message), "SigningCertURL": "https://sns.eu-west-1.amazonaws.com/x.pem", "Signature": "x",
	})
	for i := 0; i < 2; i++ {
		resp := sesPost(t, fx, string(envelope))
		resp.Body.Close()
		if resp.StatusCode != http.StatusOK {
			t.Fatalf("status = %d", resp.StatusCode)
		}
	}
	got, err := fx.st.MailEvents(ctx, release.OpsAccount, "jb-book", MailEventQuery{}, time.Now())
	if err != nil {
		t.Fatal(err)
	}
	if len(got) != 1 {
		t.Fatalf("rows = %d, want 1: %+v", len(got), got)
	}
	e := got[0]
	if e.MessageID != "0102-door" || e.Recipient != "ann@example.com" || e.Status != "5.1.1" || e.Detail != "Permanent/NoEmail" || !e.BySender {
		t.Errorf("row = %+v", e)
	}
	var notice string
	fx.st.db.QueryRow(`SELECT notice FROM mail_events WHERE message_id = '0102-door'`).Scan(&notice)
	if notice != "sns-door-1" {
		t.Errorf("notice = %q, want SNS's own MessageId", notice)
	}
}

// A sender's counts belong to the app, not to its name. A rename carries
// them; a purge drops them, so the next app of that name starts at zero;
// an account reap takes the lot. The comparison is on the day STRING,
// because this table has no timestamp column for mailAppTimeColumn to name.
func TestSenderCountsFollowTheAppAndDieWithIt(t *testing.T) {
	fx := newConsoleFixture(t)
	ctx := context.Background()
	now := time.Date(2026, 9, 15, 12, 0, 0, 0, time.UTC)
	yesterday := now.AddDate(0, 0, -1).UTC().Format("2006-01-02")
	today := now.UTC().Format("2006-01-02")

	seed := func(app, sender, day string) {
		t.Helper()
		if err := fx.st.RecordMailEvent(ctx, release.OpsAccount, app, sender, day, mail.EventSend); err != nil {
			t.Fatal(err)
		}
	}
	countRows := func(app string) int {
		t.Helper()
		var n int
		if err := fx.st.db.QueryRow(`SELECT count(*) FROM mail_sender_counts WHERE account = ? AND app = ?`,
			release.OpsAccount, app).Scan(&n); err != nil {
			t.Fatal(err)
		}
		return n
	}

	if _, err := fx.st.CreateAppIn(OpsAccountID, "jb-book", "bill@tito.io", now); err != nil {
		t.Fatal(err)
	}
	seed("jb-book", mail.SenderAppID, today)
	seed("jb-book", "book.example.com", today)
	// A row from BEFORE the app row was created belongs to a predecessor of
	// the name and must not travel, the rule RenameAppIn already applies to
	// audit rows.
	seed("jb-book", mail.SenderAppID, yesterday)

	tx, err := fx.st.db.Begin()
	if err != nil {
		t.Fatal(err)
	}
	if err := carryMailApp(ctx, tx, release.OpsAccount, "jb-book", release.OpsAccount, "jb-reader", now.Unix()); err != nil {
		t.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
		t.Fatal(err)
	}
	if got := countRows("jb-reader"); got != 2 {
		t.Fatalf("the rename carried %d rows, want the 2 from today", got)
	}
	if got := countRows("jb-book"); got != 0 {
		t.Fatalf("%d rows were left under the old name", got)
	}

	tx, err = fx.st.db.Begin()
	if err != nil {
		t.Fatal(err)
	}
	if err := forgetMailApp(ctx, tx, release.OpsAccount, "jb-reader"); err != nil {
		t.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
		t.Fatal(err)
	}
	if got := countRows("jb-reader"); got != 0 {
		t.Fatalf("a purge left %d rows for the next app of that name", got)
	}

	seed("jb-reader", mail.SenderAppID, today)
	tx, err = fx.st.db.Begin()
	if err != nil {
		t.Fatal(err)
	}
	if err := forgetMailAccount(ctx, tx, release.OpsAccount); err != nil {
		t.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
		t.Fatal(err)
	}
	if got := countRows("jb-reader"); got != 0 {
		t.Fatalf("the account reap left %d rows", got)
	}
}