package console

// The per-message email log: what happened to each message an app sent, to
// whom, and what the receiving server said. The counts in mailcounts.go are
// the abuse signal and stay exactly as they were; this sits beside them and
// is fed from the same verified SES notifications, one row per recipient
// per notification.

import (
	"context"
	"database/sql"
	"strings"
	"time"

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

// mailEventsRetention is how long a row is kept. The log carries recipient
// addresses — somebody's guests, customers, users — so it is a window, not
// an archive: long enough to answer "did that email last week arrive", no
// longer.
const mailEventsRetention = 30 * 24 * time.Hour

// mailEventsPruneEvery bounds how often the prune runs. It is one indexed
// DELETE, but it would otherwise run once per notification, and an hour's
// slack on a thirty-day window changes nothing anybody can see.
const mailEventsPruneEvery = time.Hour

// MailEvent is one row of the log.
type MailEvent struct {
	MessageID  string    `json:"message_id"`
	Type       string    `json:"type"`
	Recipient  string    `json:"recipient"`
	Status     string    `json:"status,omitempty"`
	Diagnostic string    `json:"diagnostic,omitempty"`
	Detail     string    `json:"detail,omitempty"`
	BySender   bool      `json:"by_sender"`
	At         time.Time `json:"at"`
}

// RecordMailMessageEvent keeps one notification for one app: a row per
// recipient it concerns. notice is SNS's MessageId for the notification,
// and a notification SNS delivers twice writes nothing the second time.
// bySender says whether SES itself named this app as the sender, rather
// than the app being one of several that share the sending domain.
func (s *Store) RecordMailMessageEvent(ctx context.Context, acct, app, notice string, ev mail.Event, bySender bool, now time.Time) error {
	at := ev.At
	if at.IsZero() {
		at = now
	}
	notice = mailNoticeKey(notice, ev, now)
	sender := 0
	if bySender {
		sender = 1
	}
	detail := ev.Detail.Summary()
	if recipients := ev.Recipients(); len(recipients) > 0 {
		// acct/app's rows only, under that app's permit (claim_mail_sql.go).
		err := s.withMailAppSQL(ctx, acct, app, "sql.RecordMailMessageEvent", func(v *Store) error {
			for _, o := range recipients {
				if _, err := v.db.ExecContext(ctx, `INSERT OR IGNORE INTO mail_events
  (account, app, notice, recipient, message_id, type, status, diagnostic, detail, by_sender, at, received_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
					acct, app, notice, o.Recipient, ev.MessageID, ev.Type, o.Status, o.Diagnostic, detail, sender,
					at.Unix(), now.Unix()); err != nil {
					return err
				}
			}
			return nil
		})
		if err != nil {
			return err
		}
	}
	// After the app's own write and outside it: the prune touches other
	// apps' rows, each under its own permit.
	return s.pruneMailEvents(ctx, now)
}

// mailNoticeKey is the key a notification is kept and queued under: SNS's
// own MessageId, or — for a notification with no id of its own, which is
// nothing SNS sends but the recording path is reachable directly — what it
// says, so a repeat still collapses and two different notifications do not.
// The log and the push queue share it, so the id an app is handed is the id
// the log has.
func mailNoticeKey(notice string, ev mail.Event, now time.Time) string {
	if notice != "" {
		return notice
	}
	at := ev.At
	if at.IsZero() {
		at = now
	}
	var to []string
	for _, o := range ev.Recipients() {
		to = append(to, o.Recipient)
	}
	// Two bounces for one message — one per recipient, or a delay reported
	// twice — are two notifications, so what they are about is in the key.
	return ev.MessageID + "/" + ev.Type + "/" + at.UTC().Format(time.RFC3339Nano) + "/" + strings.Join(to, ",")
}

// mailAppTables are the tables keyed by an app's (account sqid, name) that
// belong to that app alone: a name that is freed must not hand them on.
//
// mail_sender_counts joins the list (senders design §8), which also closes
// the gap where a rename left an app's counts behind under its old name.
// It cannot join blindly, though: every sweep here keys on a TIMESTAMP
// column and this table has a day string instead, so it gets the explicit
// case in mailAppTimeColumn and carryMailApp below. mail_counts is
// deliberately not here: nothing writes it any more and
// DropLegacyMailCounts removes it whole.
var mailAppTables = []string{"mail_events", "mail_event_hooks", "mail_event_deliveries", "mail_sender_counts"}

// execer is what carryMailApp and forgetMailApp need from a transaction.
type execer interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
}

// carryMailApp moves an app's email history with the app, when a rename or
// a move to another account changes the (sqid, name) it is keyed by. Rows
// already at the destination belong to a purged predecessor of that name
// and are dropped first, and only rows received since this app's own row was
// created travel — the same rule RenameAppIn applies to audit rows, for the
// same reason. Whatever is left under the old key is dropped.
//
// Across accounts, a row attributed only by its sending domain (by_sender =
// 0) does not travel. Such a row was given to every app on the domain because
// SES did not say which of them sent the message, so it may name another
// app's recipient — and that app stays behind in the source account. Within
// one account both kinds carry, since the same members could read both
// before and after.
func carryMailApp(ctx context.Context, tx execer, fromSqid, fromApp, toSqid, toApp string, createdAt int64) error {
	for _, table := range mailAppTables {
		if _, err := tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ? AND app = ?`, toSqid, toApp); err != nil {
			return err
		}
		if table == "mail_events" && fromSqid != toSqid {
			if _, err := tx.ExecContext(ctx, `DELETE FROM mail_events WHERE account = ? AND app = ? AND by_sender = 0`, fromSqid, fromApp); err != nil {
				return err
			}
		}
		if table == "mail_event_deliveries" {
			// Not carried: a queued body names the account and app it was
			// cut for, so after a move or a rename it names neither, and an
			// app checking its host is right to refuse it. The final DELETE
			// below drops them.
			if _, err := tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ? AND app = ?`, fromSqid, fromApp); err != nil {
				return err
			}
			continue
		}
		if _, err := tx.ExecContext(ctx, `UPDATE `+table+` SET account = ?, app = ? WHERE account = ? AND app = ? AND `+
			mailAppTimeColumn(table)+` >= ?`, toSqid, toApp, fromSqid, fromApp, mailAppTimeSince(table, createdAt)); err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ? AND app = ?`, fromSqid, fromApp); err != nil {
			return err
		}
	}
	return nil
}

// forgetMailAccount drops everything an account's apps left in these tables,
// when the account itself is reaped. Nothing can reach the rows afterwards —
// the sqid is tombstoned and never reused — but they are recipients'
// addresses, and a window is not an archive.
func forgetMailAccount(ctx context.Context, tx execer, sqid string) error {
	for _, table := range mailAppTables {
		if _, err := tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ?`, sqid); err != nil {
			return err
		}
	}
	return nil
}

// forgetMailApp drops everything a purged app left in these tables, so a
// later app of the same name starts with none of it.
func forgetMailApp(ctx context.Context, tx execer, sqid, app string) error {
	for _, table := range mailAppTables {
		if _, err := tx.ExecContext(ctx, `DELETE FROM `+table+` WHERE account = ? AND app = ?`, sqid, app); err != nil {
			return err
		}
	}
	return nil
}

// mailAppTimeColumn is the column that says when a row came to be. Most are
// unix seconds; mail_sender_counts keys on a UTC date string, which compares
// correctly against another ISO date and against nothing else.
func mailAppTimeColumn(table string) string {
	switch table {
	case "mail_events":
		return "received_at"
	case "mail_event_hooks":
		return "set_at"
	case "mail_sender_counts":
		return "day"
	}
	return "created_at"
}

// mailAppTimeSince renders createdAt in whatever mailAppTimeColumn(table)
// compares against: a unix stamp for a timestamp column, a UTC date string
// for the one day column. Comparing a unix integer against a 'YYYY-MM-DD'
// string in SQLite is not an error, it is simply always false, so getting
// this wrong would silently carry nothing.
func mailAppTimeSince(table string, createdAt int64) any {
	if mailAppTimeColumn(table) == "day" {
		return time.Unix(createdAt, 0).UTC().Format("2006-01-02")
	}
	return createdAt
}

// pruneMailEvents trims the log to its retention window, at most once per
// mailEventsPruneEvery. The stamp is taken before the DELETE runs, so two
// notifications arriving together do not both prune.
func (s *Store) pruneMailEvents(ctx context.Context, now time.Time) error {
	last := s.mailEventsPruned.Load()
	if last != 0 && now.Unix()-last < int64(mailEventsPruneEvery/time.Second) {
		return nil
	}
	if !s.mailEventsPruned.CompareAndSwap(last, now.Unix()) {
		return nil
	}
	if s.gate != nil && s.bound == nil {
		// Gated: one app at a time, each under its own permit. The stamp
		// stays on a partial failure: an app that refuses (an operation
		// holds it) is retried next window, not on every notification,
		// which would open an operation per app per event.
		return s.pruneMailAppRows(ctx, "mail_events", "received_at", now.Add(-mailEventsRetention).Unix(), "sql.PruneMailEvents")
	}
	if _, err := s.db.ExecContext(ctx, `DELETE FROM mail_events WHERE received_at < ?`, now.Add(-mailEventsRetention).Unix()); err != nil {
		// Not pruned, so not stamped: the next notification tries again
		// rather than waiting out the hour.
		s.mailEventsPruned.CompareAndSwap(now.Unix(), last)
		return err
	}
	return nil
}

// MailEventQuery narrows a read of the log. The zero value is the newest
// mailEventsDefaultLimit rows of the whole window.
type MailEventQuery struct {
	Since     time.Time // only rows SES timestamped at or after this
	MessageID string    // only this message
	Recipient string    // only this address, case-insensitively
	Limit     int
}

const (
	mailEventsDefaultLimit = 100
	mailEventsMaxLimit     = 1000
)

// MailEvents reads one app's log, newest first. A row older than the
// retention window is never returned, whether or not the prune has reached
// it yet — what a member is told is kept for thirty days has to be what
// they can see.
func (s *Store) MailEvents(ctx context.Context, acct, app string, q MailEventQuery, now time.Time) ([]MailEvent, error) {
	limit := q.Limit
	if limit <= 0 {
		limit = mailEventsDefaultLimit
	}
	if limit > mailEventsMaxLimit {
		limit = mailEventsMaxLimit
	}
	where := []string{"account = ?", "app = ?", "received_at >= ?"}
	args := []any{acct, app, now.Add(-mailEventsRetention).Unix()}
	if !q.Since.IsZero() {
		where = append(where, "at >= ?")
		args = append(args, q.Since.Unix())
	}
	if q.MessageID != "" {
		where = append(where, "message_id = ?")
		args = append(args, q.MessageID)
	}
	if q.Recipient != "" {
		where = append(where, "recipient = ? COLLATE NOCASE")
		args = append(args, q.Recipient)
	}
	args = append(args, limit)
	rows, err := s.db.QueryContext(ctx, `SELECT message_id, type, recipient, status, diagnostic, detail, by_sender, at
FROM mail_events WHERE `+strings.Join(where, " AND ")+`
ORDER BY at DESC, received_at DESC, recipient LIMIT ?`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []MailEvent
	for rows.Next() {
		var e MailEvent
		var at int64
		var sender int
		if err := rows.Scan(&e.MessageID, &e.Type, &e.Recipient, &e.Status, &e.Diagnostic, &e.Detail, &sender, &at); err != nil {
			return nil, err
		}
		e.BySender = sender == 1
		e.At = time.Unix(at, 0).UTC()
		out = append(out, e)
	}
	if err := rows.Err(); err != nil && err != sql.ErrNoRows {
		return nil, err
	}
	return out, nil
}