package calls

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log/slog"
	"strings"
	"sync/atomic"
	"time"

	"gorm.io/gorm"
	"gorm.io/gorm/clause"

	"amadan.net/oficina/calls/internal/calls/seal"
)

// grantLife is ruling 4: a guest grant expires two hours after the
// meeting's start, or two hours after creation when there is no start.
const grantLife = 2 * time.Hour

// pendingLife matches the pending-join cookie: fifteen minutes.
const pendingLife = 15 * time.Minute

// clock is the store's time source. It is a value tests move (see
// TestHandle.SetClock) while request goroutines are reading it — a
// stream handler outlives the request that opened it — so the function
// lives behind an atomic rather than in a plain field.
type clock struct{ fn atomic.Value }

func newClock(f func() time.Time) *clock {
	c := &clock{}
	c.set(f)
	return c
}

func (c *clock) set(f func() time.Time) { c.fn.Store(f) }

func (c *clock) now() time.Time { return c.fn.Load().(func() time.Time)() }

// store is every database access in the app. Sealing happens here and
// nowhere else, so a handler never holds a plaintext title it did not
// get from the store.
type store struct {
	g      *gorm.DB
	sealer *seal.Sealer
	clk    *clock
	logger *slog.Logger
}

// now is the app's clock, the one a test can move.
func (s *store) now() time.Time { return s.clk.now() }

func actorFor(p Person) string { return fmt.Sprintf("person:%d", p.ID) }

// ensurePerson reads first and writes only when the read is empty:
// this runs on every signed-in request, and SQLite has one writer.
func (s *store) ensurePerson(ctx context.Context, address string) (Person, error) {
	email := normalizeAddress(address)
	if email == "" {
		return Person{}, errors.New("calls: cannot map an empty address to a person")
	}
	var found Person
	err := s.g.WithContext(ctx).Where("email = ?", email).Take(&found).Error
	switch {
	case err == nil:
		return found, nil
	case !errors.Is(err, gorm.ErrRecordNotFound):
		return Person{}, fmt.Errorf("calls: load person: %w", err)
	}
	name := email
	if at := strings.Index(email, "@"); at > 0 {
		name = email[:at]
	}
	created := Person{Email: email, Name: name, CreatedAt: s.now().UTC()}
	if res := s.g.WithContext(ctx).Clauses(clause.OnConflict{DoNothing: true}).Create(&created); res.Error != nil {
		return Person{}, fmt.Errorf("calls: create person: %w", res.Error)
	}
	if err := s.g.WithContext(ctx).Where("email = ?", email).Take(&found).Error; err != nil {
		return Person{}, fmt.Errorf("calls: load person: %w", err)
	}
	return found, nil
}

type meetingView struct {
	Meeting
	Title              string
	LinkPresent        bool
	CreatedByPrincipal string
	ByAgent            bool
}

func (s *store) title(m Meeting) string {
	t, err := s.sealer.OpenString(seal.PurposeMeetingTitle, m.TitleSealed)
	if err != nil {
		s.logger.Error("calls: open title", "meeting_id", m.ID, "err", err)
		return "(title unreadable)"
	}
	return t
}

// createMeeting creates the meeting and its first link in one
// transaction. The token is sealed for subsequent member sharing.
func (s *store) createMeeting(ctx context.Context, title string, startsAt *time.Time, actor string) (Meeting, string, error) {
	return s.insertMeeting(ctx, title, false, "", startsAt, actor)
}

// createCall is createMeeting for a meeting with no title of its own —
// the list's Start a call and the apps' Start meeting: the title is the
// one Calls wrote, and the meeting says so (Meeting.AutoTitle).
func (s *store) createCall(ctx context.Context, title autoTitle, actor string) (Meeting, string, error) {
	return s.insertMeeting(ctx, title.Text, true, title.Zone, nil, actor)
}

func (s *store) insertMeeting(ctx context.Context, title string, auto bool, zone string, startsAt *time.Time, actor string) (Meeting, string, error) {
	sealed, err := s.sealer.SealString(seal.PurposeMeetingTitle, title)
	if err != nil {
		return Meeting{}, "", err
	}
	token, hash, err := newLinkToken()
	if err != nil {
		return Meeting{}, "", err
	}
	linkSealed, err := s.sealer.SealString(seal.PurposeMeetingLink, token)
	if err != nil {
		return Meeting{}, "", err
	}
	defaults, err := s.meetingDefaults(ctx)
	if err != nil {
		return Meeting{}, "", err
	}
	var m Meeting
	err = s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		for attempt := 0; ; attempt++ {
			sqid, err := newSqid()
			if err != nil {
				return err
			}
			m = Meeting{RequireSignin: defaults.RequireSignin, WaitingRoom: defaults.WaitingRoom, AutoTitle: auto, TitleZone: zone, Sqid: sqid, TitleSealed: sealed, StartsAt: startsAt, CreatedBy: actor, CreatedAt: s.now().UTC()}
			res := tx.Clauses(clause.OnConflict{DoNothing: true}).Create(&m)
			if res.Error != nil {
				return res.Error
			}
			if res.RowsAffected == 1 {
				break
			}
			if attempt+1 >= maxSqidAttempts {
				return errors.New("calls: could not mint a unique sqid")
			}
		}
		return tx.Create(&MeetingLink{MeetingID: m.ID, TokenHash: hash, TokenSealed: linkSealed, CreatedBy: actor, CreatedAt: s.now().UTC()}).Error
	})
	if err != nil {
		return Meeting{}, "", err
	}
	return m, token, nil
}

func (s *store) listMeetings(ctx context.Context) ([]meetingView, error) {
	var ms []Meeting
	if err := s.g.WithContext(ctx).Order("ended_at IS NOT NULL, starts_at IS NULL, starts_at ASC, created_at DESC").Find(&ms).Error; err != nil {
		return nil, err
	}
	// labels is shared across the whole list so a run of meetings from
	// the same actor (one agent's usual shape) resolves creatorLabel
	// once rather than once per row — the N+1 Task 3's report flagged
	// for exactly this move.
	labels := make(map[string]string, len(ms))
	out := make([]meetingView, 0, len(ms))
	for _, m := range ms {
		v, err := s.meetingViewOfCached(ctx, m, labels)
		if err != nil {
			return nil, err
		}
		out = append(out, v)
	}
	return out, nil
}

// meetingViewOf is one row as every reader wants it: the meeting, its
// title opened, whether a live link exists, and who made it — an
// agent's principal (§8.1's robot badge and operator name "on every
// write") or a person's display name, resolved through creatorLabel.
// Factored out of listMeetings so the drive surface's single-meeting
// read (§8.2) answers link_present from the same query the list does,
// rather than growing a second opinion about what "present" means.
func (s *store) meetingViewOf(ctx context.Context, m Meeting) (meetingView, error) {
	return s.meetingViewOfCached(ctx, m, map[string]string{})
}

// meetingViewOfCached is meetingViewOf's body, taking a creator-label
// cache a caller can share across many rows. meetingViewOf itself
// passes a throwaway map, since a single row has nothing to share a
// cache with; listMeetings passes one map for the whole list.
func (s *store) meetingViewOfCached(ctx context.Context, m Meeting, labels map[string]string) (meetingView, error) {
	var n int64
	if err := s.g.WithContext(ctx).Model(&MeetingLink{}).
		Where("meeting_id = ? AND revoked_at IS NULL", m.ID).Count(&n).Error; err != nil {
		return meetingView{}, err
	}
	by, ok := labels[m.CreatedBy]
	if !ok {
		by = s.creatorLabel(ctx, m.CreatedBy)
		labels[m.CreatedBy] = by
	}
	return meetingView{
		Meeting:            m,
		Title:              s.title(m),
		LinkPresent:        n > 0,
		CreatedByPrincipal: by,
		ByAgent:            strings.HasPrefix(m.CreatedBy, "agent:"),
	}, nil
}

func (s *store) meetingBySqid(ctx context.Context, sqid string) (Meeting, bool, error) {
	var m Meeting
	err := s.g.WithContext(ctx).Where("sqid = ?", sqid).Take(&m).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return Meeting{}, false, nil
	}
	return m, err == nil, err
}

// personByID is the hub's half of standing: the tick and the entry
// checks hold a person id, not a request, so the row is read back here.
func (s *store) personByID(ctx context.Context, id int64) (Person, bool, error) {
	var p Person
	err := s.g.WithContext(ctx).Where("id = ?", id).Take(&p).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return Person{}, false, nil
	}
	return p, err == nil, err
}

// liveLink answers one false for a token that never existed, one that
// was revoked, and one whose meeting has ended (spec §3.2's one 404).
func (s *store) liveLink(ctx context.Context, token string) (MeetingLink, Meeting, bool, error) {
	if token == "" {
		return MeetingLink{}, Meeting{}, false, nil
	}
	return s.liveLinkByHash(ctx, hashLinkToken(token))
}

func (s *store) liveLinkByHash(ctx context.Context, hash string) (MeetingLink, Meeting, bool, error) {
	var l MeetingLink
	err := s.g.WithContext(ctx).Where("token_hash = ?", hash).Take(&l).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return MeetingLink{}, Meeting{}, false, nil
	}
	if err != nil {
		return MeetingLink{}, Meeting{}, false, err
	}
	return s.liveLinkRow(ctx, l)
}

func (s *store) liveLinkByID(ctx context.Context, id int64) (MeetingLink, Meeting, bool, error) {
	var l MeetingLink
	err := s.g.WithContext(ctx).Where("id = ?", id).Take(&l).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return MeetingLink{}, Meeting{}, false, nil
	}
	if err != nil {
		return MeetingLink{}, Meeting{}, false, err
	}
	return s.liveLinkRow(ctx, l)
}

func (s *store) liveLinkRow(ctx context.Context, l MeetingLink) (MeetingLink, Meeting, bool, error) {
	if l.RevokedAt != nil {
		return MeetingLink{}, Meeting{}, false, nil
	}
	var m Meeting
	if err := s.g.WithContext(ctx).Where("id = ?", l.MeetingID).Take(&m).Error; err != nil {
		return MeetingLink{}, Meeting{}, false, err
	}
	if m.EndedAt != nil {
		return MeetingLink{}, Meeting{}, false, nil
	}
	return l, m, true, nil
}

// errMeetingEnded is replaceLink's answer when the meeting ended before
// the transaction reached it (review S5-2). A caller's own earlier read
// of ended_at is a snapshot, not a guarantee: the only read that can be
// trusted against a concurrent end is the one inside the write's own
// transaction, and this is that read's refusal.
var errMeetingEnded = errors.New("calls: the meeting has ended")

// replaceLink revokes every live link for the meeting and mints one
// new one, in one transaction. The replacement is sealed for repeat sharing.
//
// The meeting's ended_at is re-read inside that transaction, matched in
// the same WHERE as the id: a meeting row is never deleted, so the one
// way the row fails to match is that it ended, and that is
// errMeetingEnded. Without it a replacement could read a running
// meeting, pause while another request ended it, and leave an
// unrevoked link on an ended meeting.
func (s *store) replaceLink(ctx context.Context, meetingID int64, actor string) (string, error) {
	token, hash, err := newLinkToken()
	if err != nil {
		return "", err
	}
	sealed, err := s.sealer.SealString(seal.PurposeMeetingLink, token)
	if err != nil {
		return "", err
	}
	now := s.now().UTC()
	err = s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var m Meeting
		if err := tx.Where("id = ? AND ended_at IS NULL", meetingID).Take(&m).Error; err != nil {
			if errors.Is(err, gorm.ErrRecordNotFound) {
				return errMeetingEnded
			}
			return err
		}
		if err := tx.Model(&MeetingLink{}).Where("meeting_id = ? AND revoked_at IS NULL", meetingID).Update("revoked_at", now).Error; err != nil {
			return err
		}
		return tx.Create(&MeetingLink{MeetingID: meetingID, TokenHash: hash, TokenSealed: sealed, CreatedBy: actor, CreatedAt: now}).Error
	})
	if err != nil {
		return "", err
	}
	return token, nil
}

// shareLink is a member action. Older hash-only invitations cannot be opened;
// mint one recoverable invitation alongside them, then reuse it. Replacement
// and ending still revoke every live invitation in their own transactions.
func (s *store) shareLink(ctx context.Context, meetingID int64, actor string) (string, error) {
	var token string
	err := s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		var m Meeting
		if err := tx.Where("id = ? AND ended_at IS NULL", meetingID).Take(&m).Error; err != nil {
			if errors.Is(err, gorm.ErrRecordNotFound) {
				return errMeetingEnded
			}
			return err
		}
		var link MeetingLink
		err := tx.Where("meeting_id = ? AND revoked_at IS NULL AND token_sealed IS NOT NULL", meetingID).Order("id DESC").Take(&link).Error
		if err == nil {
			token, err = s.sealer.OpenString(seal.PurposeMeetingLink, link.TokenSealed)
			if err != nil || token == "" || hashLinkToken(token) != link.TokenHash {
				return errors.New("calls: cannot open invitation")
			}
			return nil
		}
		if !errors.Is(err, gorm.ErrRecordNotFound) {
			return err
		}
		var hash string
		token, hash, err = newLinkToken()
		if err != nil {
			return err
		}
		sealed, err := s.sealer.SealString(seal.PurposeMeetingLink, token)
		if err != nil {
			return err
		}
		return tx.Create(&MeetingLink{MeetingID: meetingID, TokenHash: hash, TokenSealed: sealed, CreatedBy: actor, CreatedAt: s.now().UTC()}).Error
	})
	return token, err
}

// endMeeting sets ended_at and revokes every link, and reports whether
// this call is the one that ended the meeting. False means the
// conditional UPDATE matched no row — the meeting was already ended,
// by another request or an earlier one of this caller's — and nothing
// was written, so the links the winning end revoked stay revoked
// (review S5-2). Two racing ends therefore get one true and one false
// rather than two 200s.
func (s *store) endMeeting(ctx context.Context, meetingID int64, actor string) (bool, error) {
	now := s.now().UTC()
	ended := false
	err := s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		res := tx.Model(&Meeting{}).Where("id = ? AND ended_at IS NULL", meetingID).
			Select("ended_at", "ended_by").Updates(map[string]any{"ended_at": now, "ended_by": actor})
		if res.Error != nil {
			return res.Error
		}
		if res.RowsAffected == 0 {
			return nil
		}
		ended = true
		return tx.Model(&MeetingLink{}).Where("meeting_id = ? AND revoked_at IS NULL", meetingID).Update("revoked_at", now).Error
	})
	if err != nil {
		return false, err
	}
	return ended, nil
}

func (s *store) grantFor(ctx context.Context, meetingID, personID int64) (GuestGrant, bool, error) {
	var g GuestGrant
	err := s.g.WithContext(ctx).Where("meeting_id = ? AND person_id = ?", meetingID, personID).Take(&g).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return GuestGrant{}, false, nil
	}
	return g, err == nil, err
}

// mintGrant is idempotent: following the link twice touches nothing.
func (s *store) mintGrant(ctx context.Context, m Meeting, personID int64) (GuestGrant, error) {
	if g, ok, err := s.grantFor(ctx, m.ID, personID); err != nil || ok {
		return g, err
	}
	now := s.now().UTC()
	base := now
	if m.StartsAt != nil {
		base = m.StartsAt.UTC()
	}
	g := GuestGrant{MeetingID: m.ID, PersonID: personID, GrantedAt: now, ExpiresAt: base.Add(grantLife)}
	if res := s.g.WithContext(ctx).Clauses(clause.OnConflict{DoNothing: true}).Create(&g); res.Error != nil {
		return GuestGrant{}, res.Error
	}
	g, _, err := s.grantFor(ctx, m.ID, personID)
	return g, err
}

// mintGrantViaLink mints a grant for the link the browser actually
// followed, re-reading that link inside the grant's own transaction
// (review C1-3). followLink resolves the link and then mints; between
// those two steps a member can replace the link, and mintGrant on its
// own would never notice, because it is handed the meeting rather than
// the link. Standing checks the grant and not its origin, so such a
// grant would stay usable for its full two hours.
//
// It answers false — no error — when the link has been revoked or its
// meeting has ended, which the caller turns into the same 404 an unknown
// token gets (spec §3.2's one 404).
//
// SQLite's writer pool is one connection, so the transaction's read and
// its write cannot be interleaved with another transaction's.
func (s *store) mintGrantViaLink(ctx context.Context, linkID, personID int64) (GuestGrant, bool, error) {
	var g GuestGrant
	live := false
	err := s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		ts := &store{g: tx, sealer: s.sealer, clk: s.clk, logger: s.logger}
		_, m, ok, err := ts.liveLinkByID(ctx, linkID)
		if err != nil || !ok {
			return err
		}
		if g, err = ts.mintGrant(ctx, m, personID); err != nil {
			return err
		}
		live = true
		return nil
	})
	if err != nil {
		return GuestGrant{}, false, err
	}
	return g, live, nil
}

func (s *store) fingerprint(address string) (string, error) {
	return s.sealer.Fingerprint(seal.PurposePendingAddress, normalizeAddress(address))
}

func (s *store) writePending(ctx context.Context, address string, link MeetingLink) error {
	fp, err := s.fingerprint(address)
	if err != nil {
		return err
	}
	now := s.now().UTC()
	return s.g.WithContext(ctx).Create(&PendingJoin{
		MeetingID: link.MeetingID, LinkID: link.ID, AddressFingerprint: fp,
		CreatedAt: now, ExpiresAt: now.Add(pendingLife),
	}).Error
}

// pendingLive is the Authorize half: does this address hold an
// unexpired pending row whose link is still live?
func (s *store) pendingLive(ctx context.Context, address string) (bool, error) {
	p, ok, err := s.latestPending(ctx, address)
	if err != nil || !ok {
		return false, err
	}
	_, _, live, err := s.liveLinkByID(ctx, p.LinkID)
	return live, err
}

func (s *store) latestPending(ctx context.Context, address string) (PendingJoin, bool, error) {
	fp, err := s.fingerprint(address)
	if err != nil {
		return PendingJoin{}, false, err
	}
	var p PendingJoin
	err = s.g.WithContext(ctx).Where("address_fingerprint = ? AND expires_at > ?", fp, s.now().UTC()).
		Order("created_at DESC, id DESC").Take(&p).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return PendingJoin{}, false, nil
	}
	return p, err == nil, err
}

func (s *store) deletePending(ctx context.Context, address string) error {
	fp, err := s.fingerprint(address)
	if err != nil {
		return err
	}
	return s.g.WithContext(ctx).Where("address_fingerprint = ?", fp).Delete(&PendingJoin{}).Error
}

// consumePending re-checks the pending row's link and meeting as
// GET /j/{token} would, mints the grant for a non-member, and deletes
// every pending row for the address, all in one transaction. It returns
// the holding-screen path, or "" when the re-check failed.
//
// The row is re-selected inside the transaction and the caller's value
// is only trusted when it is still the newest unexpired one (review
// C1-4). latestPending runs before the transaction opens, so by the time
// we get here the row may have been consumed by a concurrent /continue,
// or superseded by a newer request from another browser. A stale value
// must not mint — and must not delete the row that superseded it, so a
// mismatch deletes nothing at all and the caller falls through to / or
// /welcome. Expiry needs no separate check: latestPending already
// excludes expired rows, and the sweeper removes them.
func (s *store) consumePending(ctx context.Context, p Person, pend PendingJoin, member bool) (string, error) {
	dest := ""
	err := s.g.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		ts := &store{g: tx, sealer: s.sealer, clk: s.clk, logger: s.logger}
		current, ok, err := ts.latestPending(ctx, p.Email)
		if err != nil || !ok || current.ID != pend.ID {
			return err
		}
		if err := ts.deletePending(ctx, p.Email); err != nil {
			return err
		}
		_, m, live, err := ts.liveLinkByID(ctx, current.LinkID)
		if err != nil || !live {
			return err
		}
		if !member {
			if _, err := ts.mintGrant(ctx, m, p.ID); err != nil {
				return err
			}
		}
		dest = "/m/" + m.Sqid + "/join"
		return nil
	})
	return dest, err
}

// sweepLimit bounds one sweep call to a page of rows, so a large
// backlog cannot hold SQLite's one write connection for an unbounded
// delete (review C2-3). The caller loops while the count it gets back
// is exactly this many.
const sweepLimit = 1000

// sweep deletes pending rows whose fifteen minutes are up, at most
// sweepLimit per call via a subselect on ids (a bare DELETE has no
// portable LIMIT). Reads already exclude expired rows; this is about
// not keeping a record past its life (review C1-7). It returns how many
// rows went.
func (s *store) sweep(ctx context.Context, now time.Time) (int64, error) {
	res := s.g.WithContext(ctx).Where(
		"id IN (SELECT id FROM pending_joins WHERE expires_at <= ? LIMIT ?)",
		now.UTC(), sweepLimit,
	).Delete(&PendingJoin{})
	return res.RowsAffected, res.Error
}

// nextExpiry answers the earliest expires_at across pending_joins,
// rastrillo's own auth_links, and rastrillo's own sessions, or the zero
// time when all three are empty. It is the platform wake-up input for
// cmd/calls/main.go's Options.NextDue (review C2-2).
//
// auth_links matters here too: a plain member sign-in writes no pending
// row at all (that table exists for the outsider/pending-join path,
// spec §3.2), only rastrillo's own magic-link row. If nextExpiry read
// pending_joins alone, an app that hibernated with nothing outstanding
// but one unclicked member link would never be woken to sweep it — the
// platform sees a zero NextDue and assumes there is nothing to do
// (round 2 follow-up, caught on re-review of 9e067e4).
//
// sessions is here for the same reason, one level further out:
// auth.Sweep cleans auth_links AND sessions in one call
// (auth/store.go:117 delegates to sessions.Sweep), so a session sitting
// alone with nothing else outstanding needs the same wake-up guarantee
// an unclicked link does (Codex round 3, C3-1).
func (s *store) nextExpiry(ctx context.Context) (time.Time, error) {
	var p PendingJoin
	err := s.g.WithContext(ctx).Order("expires_at ASC").Take(&p).Error
	var pending time.Time
	switch {
	case errors.Is(err, gorm.ErrRecordNotFound):
		// pending stays zero.
	case err != nil:
		return time.Time{}, err
	default:
		pending = p.ExpiresAt
	}

	authLink, err := s.rastrilloTextExpiry(ctx, "auth_links")
	if err != nil {
		return time.Time{}, err
	}
	session, err := s.rastrilloTextExpiry(ctx, "sessions")
	if err != nil {
		return time.Time{}, err
	}

	next := time.Time{}
	for _, t := range []time.Time{pending, authLink, session} {
		if t.IsZero() {
			continue
		}
		if next.IsZero() || t.Before(next) {
			next = t
		}
	}
	return next, nil
}

// rastrilloTextExpiry reads the earliest expires_at from one of
// rastrillo's own tables — auth_links or sessions, the only two callers
// pass, never anything caller-supplied. Both are rastrillo's tables, not
// store models: rastrillo writes to them over database/sql rather than
// gorm, storing expires_at as RFC3339 text via
// now.UTC().Format(time.RFC3339) (auth/store.go:83,152,
// sessions/sessions.go:157; see auth/migrations/0001_init.sql and
// sessions/migrations/0001_init.sql for the column). MIN() over that
// text column sorts correctly because every row is formatted the same
// fixed-width, UTC way, so lexicographic and chronological order agree.
// The zero time means no row; an empty table's MIN() is a SQL NULL, not
// an empty string, hence sql.NullString rather than a bare string.
func (s *store) rastrilloTextExpiry(ctx context.Context, table string) (time.Time, error) {
	var raw sql.NullString
	if err := s.g.WithContext(ctx).Raw("SELECT MIN(expires_at) FROM " + table).Row().Scan(&raw); err != nil {
		return time.Time{}, err
	}
	if !raw.Valid || raw.String == "" {
		return time.Time{}, nil
	}
	t, err := time.Parse(time.RFC3339, raw.String)
	if err != nil {
		return time.Time{}, fmt.Errorf("calls: parse %s.expires_at: %w", table, err)
	}
	return t, nil
}

// Identity rows use AUTOINCREMENT and are never recycled for another person.
func (s *store) existingHomePerson(ctx context.Context, address string) (Person, error) {
	var p Person
	err := s.g.WithContext(ctx).Where("email = ?", normalizeAddress(address)).Take(&p).Error
	return p, err
}
func (s *store) homePersonByID(ctx context.Context, q interface {
	QueryRowContext(context.Context, string, ...any) *sql.Row
}, id int64) (Person, error) {
	var p Person
	err := q.QueryRowContext(ctx, "SELECT id, email, name FROM persons WHERE id = ?", id).Scan(&p.ID, &p.Email, &p.Name)
	return p, err
}

func (s *store) meetingDefaults(ctx context.Context) (MeetingDefaults, error) {
	var d MeetingDefaults
	err := s.g.WithContext(ctx).Where("id = ?", 1).Take(&d).Error
	if errors.Is(err, gorm.ErrRecordNotFound) {
		return MeetingDefaults{ID: 1}, nil
	}
	return d, err
}
func (s *store) saveMeetingDefaults(ctx context.Context, signin, waiting bool) error {
	return s.g.WithContext(ctx).Clauses(clause.OnConflict{Columns: []clause.Column{{Name: "id"}}, DoUpdates: clause.AssignmentColumns([]string{"require_signin", "waiting_room"})}).Create(&MeetingDefaults{ID: 1, RequireSignin: signin, WaitingRoom: waiting}).Error
}

// Meetings are team-owned, as are the other meeting mutations in this store.
func (s *store) saveMeetingSettings(ctx context.Context, id int64, signin, waiting bool) error {
	return s.g.WithContext(ctx).Model(&Meeting{}).Where("id = ? AND ended_at IS NULL", id).Select("RequireSignin", "WaitingRoom").Updates(map[string]any{"RequireSignin": signin, "WaitingRoom": waiting}).Error
}
func (s *store) createGuest(ctx context.Context, name string) (Person, error) {
	p := Person{Email: "guest:" + nativeRandom(), Name: name, Anonymous: true, CreatedAt: s.now().UTC()}
	err := s.g.WithContext(ctx).Create(&p).Error
	return p, err
}

// saveDeviceToken records where a person can be reached when the app is
// not running, and is an upsert on the token rather than on the person:
// Apple reissues a token to whoever installs the app next, so the same
// string arriving under a different person means the device changed
// hands and the old row is wrong. LastSeen moves on every registration,
// which is what lets a token that has gone quiet be swept later.
func (s *store) saveDeviceToken(ctx context.Context, personID int64, token, platform string) error {
	now := s.now().UTC()
	return s.g.WithContext(ctx).Clauses(clause.OnConflict{
		Columns:   []clause.Column{{Name: "token"}},
		DoUpdates: clause.AssignmentColumns([]string{"person_id", "platform", "last_seen"}),
	}).Create(&DeviceToken{
		PersonID: personID, Token: token, Platform: platform, CreatedAt: now, LastSeen: now,
	}).Error
}

// deviceTokensFor answers every device a set of people can be reached
// on. Plural because one person has a Mac and a phone, and a knock is
// worth saying on both — whichever they are near.
func (s *store) deviceTokensFor(ctx context.Context, personIDs []int64) ([]DeviceToken, error) {
	if len(personIDs) == 0 {
		return nil, nil
	}
	var tokens []DeviceToken
	err := s.g.WithContext(ctx).Where("person_id IN ?", personIDs).Find(&tokens).Error
	return tokens, err
}

// forgetDeviceToken deletes one token, which is the only correct answer
// to Apple reporting it unregistered. Deleting by token and not by
// person: the device is gone, the person is not.
func (s *store) forgetDeviceToken(ctx context.Context, token string) error {
	return s.g.WithContext(ctx).Where("token = ?", token).Delete(&DeviceToken{}).Error
}

// memberPersonIDs is everyone who may admit: the people whose address is
// on the team allowlist. A knock is theirs to answer, and nobody else's.
func (s *store) memberPersonIDs(ctx context.Context, allowed allowlist) ([]int64, error) {
	var persons []Person
	if err := s.g.WithContext(ctx).Where("email NOT LIKE ?", "guest:%").Find(&persons).Error; err != nil {
		return nil, err
	}
	ids := make([]int64, 0, len(persons))
	for _, p := range persons {
		if allowed.allows(p.Email) {
			ids = append(ids, p.ID)
		}
	}
	return ids, nil
}