mailgosend/internal/db/admin.go

// Package db — admin-specific queries: queue, ip_bans, security_events,
// and update/delete operations for users and domains.
package db

import (
	"context"
	"database/sql"
	"fmt"
	"time"
)

// ---- Types ----

// QueueEntry is a row from the delivery queue.
type QueueEntry struct {
	ID          int64
	DomainID    sql.NullInt64
	FromAddr    string
	ToAddr      string
	MessageID   string
	Status      string // pending | failed | sent
	Attempts    int
	LastAttempt sql.NullTime
	NextAttempt time.Time
	ErrorLog    string
	CreatedAt   time.Time
	ExpiresAt   time.Time
}

// IPBan is a row from ip_bans.
type IPBan struct {
	ID          int64
	IP          string
	Reason      string
	BannedAt    time.Time
	ExpiresAt   sql.NullTime
	ReleasedBy  string
}

// SecurityEvent is a row from security_events.
type SecurityEvent struct {
	ID        int64
	Type      string
	IP        string
	UserID    sql.NullInt64
	Detail    string
	CreatedAt time.Time
}

// AdminStats aggregates summary counts for the dashboard.
type AdminStats struct {
	TotalDomains  int
	TotalUsers    int
	TotalMessages int
	QueuePending  int
	QueueFailed   int
	ActiveBans    int
	RecentEvents  int // last 24h
}

// ---- Queue ----

// ListQueueEntries returns all non-sent queue entries ordered by created_at desc.
func (d *DB) ListQueueEntries(ctx context.Context) ([]*QueueEntry, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT id, domain_id, from_addr, to_addr, message_id, status,
		       attempts, last_attempt, next_attempt, error_log, created_at, expires_at
		FROM queue
		WHERE status != 'sent'
		ORDER BY created_at DESC
		LIMIT 500`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*QueueEntry
	for rows.Next() {
		q := &QueueEntry{}
		err := rows.Scan(
			&q.ID, &q.DomainID, &q.FromAddr, &q.ToAddr, &q.MessageID, &q.Status,
			&q.Attempts, &q.LastAttempt, &q.NextAttempt, &q.ErrorLog, &q.CreatedAt, &q.ExpiresAt,
		)
		if err != nil {
			return nil, err
		}
		out = append(out, q)
	}
	return out, rows.Err()
}

// RetryQueueEntry resets a queue entry to pending with immediate next_attempt.
func (d *DB) RetryQueueEntry(ctx context.Context, id int64) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE queue SET status='pending', next_attempt=? WHERE id=?",
		time.Now().UTC(), id)
	return err
}

// DeleteQueueEntry removes a queue entry permanently.
func (d *DB) DeleteQueueEntry(ctx context.Context, id int64) error {
	_, err := d.db.ExecContext(ctx, "DELETE FROM queue WHERE id=?", id)
	return err
}

// ---- IP Bans ----

// ListIPBans returns all IP bans, active first.
func (d *DB) ListIPBans(ctx context.Context) ([]*IPBan, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT id, ip, reason, banned_at, expires_at, released_by
		FROM ip_bans
		ORDER BY banned_at DESC
		LIMIT 500`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*IPBan
	for rows.Next() {
		b := &IPBan{}
		err := rows.Scan(&b.ID, &b.IP, &b.Reason, &b.BannedAt, &b.ExpiresAt, &b.ReleasedBy)
		if err != nil {
			return nil, err
		}
		out = append(out, b)
	}
	return out, rows.Err()
}

// AddIPBan inserts a manual IP ban. hours=0 means permanent.
func (d *DB) AddIPBan(ctx context.Context, ip, reason string, hours int) error {
	var expiresAt *time.Time
	if hours > 0 {
		t := time.Now().UTC().Add(time.Duration(hours) * time.Hour)
		expiresAt = &t
	}
	_, err := d.db.ExecContext(ctx,
		"INSERT OR REPLACE INTO ip_bans (ip, reason, banned_at, expires_at) VALUES (?, ?, ?, ?)",
		ip, reason, time.Now().UTC(), expiresAt)
	return err
}

// RemoveIPBan deletes a ban by IP address.
func (d *DB) RemoveIPBan(ctx context.Context, ip string) error {
	_, err := d.db.ExecContext(ctx, "DELETE FROM ip_bans WHERE ip=?", ip)
	return err
}

// ---- Security Events ----

// ListSecurityEvents returns recent security events.
func (d *DB) ListSecurityEvents(ctx context.Context, limit int) ([]*SecurityEvent, error) {
	if limit <= 0 || limit > 1000 {
		limit = 200
	}
	rows, err := d.db.QueryContext(ctx, `
		SELECT id, type, ip, user_id, detail, created_at
		FROM security_events
		ORDER BY created_at DESC
		LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*SecurityEvent
	for rows.Next() {
		ev := &SecurityEvent{}
		err := rows.Scan(&ev.ID, &ev.Type, &ev.IP, &ev.UserID, &ev.Detail, &ev.CreatedAt)
		if err != nil {
			return nil, err
		}
		out = append(out, ev)
	}
	return out, rows.Err()
}

// LogSecurityEvent inserts a security event record.
func (d *DB) LogSecurityEvent(ctx context.Context, eventType, ip string, userID *int64, detail string) error {
	_, err := d.db.ExecContext(ctx,
		"INSERT INTO security_events (type, ip, user_id, detail, created_at) VALUES (?, ?, ?, ?, ?)",
		eventType, ip, userID, detail, time.Now().UTC())
	return err
}

// ---- User admin operations ----

// SetUserEnabled enables or disables a user account.
func (d *DB) SetUserEnabled(ctx context.Context, userID int64, enabled bool) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET enabled=? WHERE id=?", enabled, userID)
	return err
}

// SetUserAdmin sets admin / domain_admin flags.
func (d *DB) SetUserAdmin(ctx context.Context, userID int64, admin, domainAdmin bool) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET admin=?, domain_admin=? WHERE id=?", admin, domainAdmin, userID)
	return err
}

// SetUserQuota updates quota_bytes.
func (d *DB) SetUserQuota(ctx context.Context, userID int64, quotaBytes int64) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET quota_bytes=? WHERE id=?", quotaBytes, userID)
	return err
}

// SetUserPassword replaces the bcrypt hash for a user.
func (d *DB) SetUserPassword(ctx context.Context, userID int64, hash string) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET password_hash=? WHERE id=?", hash, userID)
	return err
}

// SetUserDisplayName updates display_name.
func (d *DB) SetUserDisplayName(ctx context.Context, userID int64, name string) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET display_name=? WHERE id=?", name, userID)
	return err
}

// DeleteUser permanently deletes a user and all associated data (cascade).
func (d *DB) DeleteUser(ctx context.Context, userID int64) error {
	_, err := d.db.ExecContext(ctx, "DELETE FROM users WHERE id=?", userID)
	return err
}

// ListAllUsers returns users across all domains, joined with domain name.
func (d *DB) ListAllUsers(ctx context.Context) ([]*UserWithDomain, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT u.id, u.domain_id, u.username, u.email, u.display_name,
		       u.quota_bytes, u.used_bytes, u.enabled, u.admin, u.domain_admin,
		       u.mfa_enabled, u.created_at, u.last_login,
		       d.name AS domain_name
		FROM users u
		LEFT JOIN domains d ON d.id = u.domain_id
		ORDER BY d.name, u.email`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*UserWithDomain
	for rows.Next() {
		u := &UserWithDomain{}
		var lastLogin sql.NullTime
		err := rows.Scan(
			&u.ID, &u.DomainID, &u.Username, &u.Email, &u.DisplayName,
			&u.QuotaBytes, &u.UsedBytes, &u.Enabled, &u.Admin, &u.DomainAdmin,
			&u.MFAEnabled, &u.CreatedAt, &lastLogin,
			&u.DomainName,
		)
		if err != nil {
			return nil, err
		}
		if lastLogin.Valid {
			u.LastLogin = lastLogin.Time
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

// UserWithDomain is a user row augmented with the domain name.
type UserWithDomain struct {
	ID          int64
	DomainID    int64
	Username    string
	Email       string
	DisplayName string
	DomainName  string
	QuotaBytes  int64
	UsedBytes   int64
	Enabled     bool
	Admin       bool
	DomainAdmin bool
	MFAEnabled  bool
	CreatedAt   time.Time
	LastLogin   time.Time
}

// ---- MFA operations ----

// SetMFASecret stores the encrypted TOTP secret. Passing nil clears the secret.
func (d *DB) SetMFASecret(ctx context.Context, userID int64, encSecret []byte) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET mfa_secret_enc=? WHERE id=?", encSecret, userID)
	return err
}

// SetMFAEnabled enables or disables TOTP for a user.
func (d *DB) SetMFAEnabled(ctx context.Context, userID int64, enabled bool) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET mfa_enabled=? WHERE id=?", enabled, userID)
	return err
}

// SetRecoveryCodes stores the encrypted recovery codes JSON.
func (d *DB) SetRecoveryCodes(ctx context.Context, userID int64, encCodes []byte) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET recovery_codes_enc=? WHERE id=?", encCodes, userID)
	return err
}

// ClearMFA disables MFA and removes the secret + recovery codes atomically.
func (d *DB) ClearMFA(ctx context.Context, userID int64) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE users SET mfa_enabled=0, mfa_secret_enc=NULL, recovery_codes_enc=NULL WHERE id=?",
		userID)
	return err
}

// ---- Domain admin operations ----

// SetDomainEnabled enables or disables a domain.
func (d *DB) SetDomainEnabled(ctx context.Context, domainID int64, enabled bool) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE domains SET enabled=? WHERE id=?", enabled, domainID)
	return err
}

// SetDomainLimits updates max_users and max_quota_bytes.
func (d *DB) SetDomainLimits(ctx context.Context, domainID int64, maxUsers int, maxQuota int64) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE domains SET max_users=?, max_quota_bytes=? WHERE id=?",
		maxUsers, maxQuota, domainID)
	return err
}

// SetDomainDNS stores SPF and DMARC policy strings (informational, for display).
func (d *DB) SetDomainDNS(ctx context.Context, domainID int64, spf, dmarc string) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE domains SET spf_policy=?, dmarc_policy=? WHERE id=?", spf, dmarc, domainID)
	return err
}

// DeleteDomain permanently removes a domain (cascade deletes users + their data).
func (d *DB) DeleteDomain(ctx context.Context, domainID int64) error {
	_, err := d.db.ExecContext(ctx, "DELETE FROM domains WHERE id=?", domainID)
	return err
}

// ---- Stats ----

// GetAdminStats returns aggregate counts for the admin dashboard.
func (d *DB) GetAdminStats(ctx context.Context) (*AdminStats, error) {
	s := &AdminStats{}

	queries := []struct {
		dest *int
		sql  string
		args []any
	}{
		{&s.TotalDomains, "SELECT COUNT(*) FROM domains WHERE enabled=1", nil},
		{&s.TotalUsers, "SELECT COUNT(*) FROM users WHERE enabled=1", nil},
		{&s.TotalMessages, "SELECT COUNT(*) FROM messages WHERE deleted_at IS NULL", nil},
		{&s.QueuePending, "SELECT COUNT(*) FROM queue WHERE status='pending'", nil},
		{&s.QueueFailed, "SELECT COUNT(*) FROM queue WHERE status='failed'", nil},
		{&s.ActiveBans, "SELECT COUNT(*) FROM ip_bans WHERE (expires_at IS NULL OR expires_at > ?)", []any{time.Now().UTC()}},
		{&s.RecentEvents, "SELECT COUNT(*) FROM security_events WHERE created_at > ?", []any{time.Now().UTC().Add(-24 * time.Hour)}},
	}

	for _, q := range queries {
		err := d.db.QueryRowContext(ctx, q.sql, q.args...).Scan(q.dest)
		if err != nil {
			return nil, fmt.Errorf("admin stats: %w", err)
		}
	}
	return s, nil
}