archivmail/internal/storage/tenant_routing_rules.go

package storage

import (
	"context"
	"fmt"
	"strings"
	"time"
)

// PROJ-43: Tenant routing rules — flexible pattern rules that assign an
// incoming mail to a tenant. This complements the simple 1:1 tenant_domains
// mapping (PROJ-21 Phase 5): routing rules are more explicit and therefore
// evaluated *before* the plain domain fallback. Higher priority wins on ties.
//
// NOTE: this is intentionally a separate table from PROJ-51's `archiving_rules`
// (retention categories) — same "rules engine" style, different purpose.

// Routing rule match types.
const (
	RouteMatchFromDomain = "from_domain" // domain of the From address
	RouteMatchToDomain   = "to_domain"   // domain of any To/Cc/envelope recipient
	RouteMatchFromAddr   = "from_addr"   // full From address (exact)
	RouteMatchToAddr     = "to_addr"     // full To/Cc/envelope recipient (exact)
)

// TenantRoutingRule assigns matching mails to TenantID.
type TenantRoutingRule struct {
	ID        int64     `json:"id"`
	TenantID  int64     `json:"tenant_id"` // target tenant (always set — a routing rule must resolve to a tenant)
	MatchType string    `json:"match_type"`
	Pattern   string    `json:"pattern"`
	Priority  int       `json:"priority"` // higher = evaluated first
	CreatedAt time.Time `json:"created_at"`
}

func (s *Store) initTenantRoutingRulesSchema(ctx context.Context) {
	if s.db == nil {
		return
	}
	_, _ = s.db.Exec(ctx, `
		CREATE TABLE IF NOT EXISTS tenant_routing_rules (
			id         BIGSERIAL PRIMARY KEY,
			tenant_id  BIGINT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
			match_type TEXT NOT NULL,
			pattern    TEXT NOT NULL,
			priority   INT  NOT NULL DEFAULT 0,
			created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
		)`)
	_, _ = s.db.Exec(ctx, `CREATE INDEX IF NOT EXISTS idx_tenant_routing_rules_priority ON tenant_routing_rules (priority DESC)`)
	_, _ = s.db.Exec(ctx, `CREATE INDEX IF NOT EXISTS idx_tenant_routing_rules_tenant ON tenant_routing_rules (tenant_id)`)
}

func validRouteMatchType(t string) bool {
	switch t {
	case RouteMatchFromDomain, RouteMatchToDomain, RouteMatchFromAddr, RouteMatchToAddr:
		return true
	}
	return false
}

// ── CRUD ──────────────────────────────────────────────────────────────────────

// ListTenantRoutingRules returns rules ordered by priority (desc). If tenantID
// is non-nil, only rules for that tenant are returned (tenant-admin scope).
// A nil tenantID returns all rules (superadmin scope).
func (s *Store) ListTenantRoutingRules(ctx context.Context, tenantID *int64) ([]TenantRoutingRule, error) {
	if s.db == nil {
		return nil, fmt.Errorf("storage: no db")
	}
	query := `SELECT id, tenant_id, match_type, pattern, priority, created_at FROM tenant_routing_rules`
	var args []interface{}
	if tenantID != nil {
		query += ` WHERE tenant_id = $1`
		args = append(args, *tenantID)
	}
	query += ` ORDER BY priority DESC, id ASC`

	rows, err := s.db.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("storage: list routing rules: %w", err)
	}
	defer rows.Close()

	var out []TenantRoutingRule
	for rows.Next() {
		var r TenantRoutingRule
		if err := rows.Scan(&r.ID, &r.TenantID, &r.MatchType, &r.Pattern, &r.Priority, &r.CreatedAt); err != nil {
			return nil, fmt.Errorf("storage: scan routing rule: %w", err)
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// GetTenantRoutingRule loads a single rule (used for IDOR scope checks and
// dry-run by rule ID).
func (s *Store) GetTenantRoutingRule(ctx context.Context, id int64) (*TenantRoutingRule, error) {
	if s.db == nil {
		return nil, fmt.Errorf("storage: no db")
	}
	var r TenantRoutingRule
	err := s.db.QueryRow(ctx,
		`SELECT id, tenant_id, match_type, pattern, priority, created_at FROM tenant_routing_rules WHERE id=$1`, id,
	).Scan(&r.ID, &r.TenantID, &r.MatchType, &r.Pattern, &r.Priority, &r.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("storage: get routing rule: %w", err)
	}
	return &r, nil
}

func normalizeRoutePattern(matchType, pattern string) (string, error) {
	if !validRouteMatchType(matchType) {
		return "", fmt.Errorf("storage: invalid match_type %q", matchType)
	}
	p := strings.ToLower(strings.TrimSpace(pattern))
	if p == "" {
		return "", fmt.Errorf("storage: pattern must not be empty")
	}
	return p, nil
}

// CreateTenantRoutingRule inserts a new rule and returns its generated ID.
func (s *Store) CreateTenantRoutingRule(ctx context.Context, r TenantRoutingRule) (int64, error) {
	if s.db == nil {
		return 0, fmt.Errorf("storage: no db")
	}
	pat, err := normalizeRoutePattern(r.MatchType, r.Pattern)
	if err != nil {
		return 0, err
	}
	if r.TenantID <= 0 {
		return 0, fmt.Errorf("storage: tenant_id must be set")
	}
	var id int64
	err = s.db.QueryRow(ctx, `
		INSERT INTO tenant_routing_rules (tenant_id, match_type, pattern, priority)
		VALUES ($1, $2, $3, $4) RETURNING id`,
		r.TenantID, r.MatchType, pat, r.Priority,
	).Scan(&id)
	if err != nil {
		return 0, fmt.Errorf("storage: create routing rule: %w", err)
	}
	return id, nil
}

// UpdateTenantRoutingRule replaces the editable fields of an existing rule.
func (s *Store) UpdateTenantRoutingRule(ctx context.Context, r TenantRoutingRule) error {
	if s.db == nil {
		return fmt.Errorf("storage: no db")
	}
	pat, err := normalizeRoutePattern(r.MatchType, r.Pattern)
	if err != nil {
		return err
	}
	if r.TenantID <= 0 {
		return fmt.Errorf("storage: tenant_id must be set")
	}
	tag, err := s.db.Exec(ctx, `
		UPDATE tenant_routing_rules
		SET tenant_id=$1, match_type=$2, pattern=$3, priority=$4
		WHERE id=$5`,
		r.TenantID, r.MatchType, pat, r.Priority, r.ID,
	)
	if err != nil {
		return fmt.Errorf("storage: update routing rule: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("storage: routing rule %d not found", r.ID)
	}
	return nil
}

// DeleteTenantRoutingRule removes a rule. Already-archived mails keep their
// assigned tenant (no retroactive re-routing).
func (s *Store) DeleteTenantRoutingRule(ctx context.Context, id int64) error {
	if s.db == nil {
		return fmt.Errorf("storage: no db")
	}
	tag, err := s.db.Exec(ctx, `DELETE FROM tenant_routing_rules WHERE id=$1`, id)
	if err != nil {
		return fmt.Errorf("storage: delete routing rule: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return fmt.Errorf("storage: routing rule %d not found", id)
	}
	return nil
}

// ── Matching ────────────────────────────────────────────────────────────────

func routeDomainOf(addr string) string {
	addr = strings.ToLower(strings.TrimSpace(addr))
	// strip a possible "Name <addr>" wrapper
	if i := strings.LastIndex(addr, "<"); i >= 0 {
		if j := strings.LastIndex(addr, ">"); j > i {
			addr = addr[i+1 : j]
		}
	}
	if i := strings.LastIndex(addr, "@"); i >= 0 {
		return addr[i+1:]
	}
	return ""
}

func routeBareAddr(addr string) string {
	addr = strings.ToLower(strings.TrimSpace(addr))
	if i := strings.LastIndex(addr, "<"); i >= 0 {
		if j := strings.LastIndex(addr, ">"); j > i {
			addr = addr[i+1 : j]
		}
	}
	return strings.TrimSpace(addr)
}

// domainMatches supports exact match and a leading "*." wildcard that also
// matches sub-domains (e.g. "*.kunde.de" matches "mail.kunde.de" and "kunde.de").
func domainMatches(pattern, domain string) bool {
	if pattern == "" || domain == "" {
		return false
	}
	if strings.HasPrefix(pattern, "*.") {
		base := pattern[2:]
		return domain == base || strings.HasSuffix(domain, "."+base)
	}
	return domain == pattern
}

// routingRuleMatches reports whether a rule matches the given (already
// lowercased-on-store) from address and recipient list.
func routingRuleMatches(r TenantRoutingRule, from string, recipients []string) bool {
	pat := strings.ToLower(strings.TrimSpace(r.Pattern))
	if pat == "" {
		return false
	}
	switch r.MatchType {
	case RouteMatchFromAddr:
		return routeBareAddr(from) == pat
	case RouteMatchFromDomain:
		return domainMatches(pat, routeDomainOf(from))
	case RouteMatchToAddr:
		for _, t := range recipients {
			if routeBareAddr(t) == pat {
				return true
			}
		}
		return false
	case RouteMatchToDomain:
		for _, t := range recipients {
			if domainMatches(pat, routeDomainOf(t)) {
				return true
			}
		}
		return false
	}
	return false
}

// ResolveTenantByRoutingRules evaluates all routing rules against a mail's From
// address and recipient list. The highest-priority matching rule wins (lowest
// id on a tie). Returns nil when no rule matches. This is the PROJ-43 explicit
// stage that runs *before* the plain tenant_domains fallback.
func (s *Store) ResolveTenantByRoutingRules(ctx context.Context, from string, recipients []string) (*int64, error) {
	if s.db == nil {
		return nil, nil
	}
	rules, err := s.ListTenantRoutingRules(ctx, nil)
	if err != nil {
		return nil, err
	}
	for i := range rules {
		if routingRuleMatches(rules[i], from, recipients) {
			tid := rules[i].TenantID
			return &tid, nil
		}
	}
	return nil, nil
}

// ── Dry-run ─────────────────────────────────────────────────────────────────

// RoutingDryRunMatch is a single sample row for the dry-run preview.
type RoutingDryRunMatch struct {
	ID         string    `json:"id"`
	MailFrom   string    `json:"mail_from"`
	MailTo     string    `json:"mail_to"`
	Subject    string    `json:"subject"`
	ReceivedAt time.Time `json:"received_at"`
}

// RoutingDryRunResult reports how many already-archived mails would be matched
// by a (match_type, pattern) pair, plus a bounded sample.
type RoutingDryRunResult struct {
	MatchType   string               `json:"match_type"`
	Pattern     string               `json:"pattern"`
	MatchCount  int64                `json:"match_count"`
	SampleLimit int                  `json:"sample_limit"`
	Sample      []RoutingDryRunMatch `json:"sample"`
}

// dryRunCondition builds a SQL condition + argument approximating the rule
// against the emails table (mail_from / mail_to are display strings, so we use
// ILIKE containment — this is a preview estimate, not the exact live matcher).
func dryRunCondition(matchType, pattern string) (cond string, arg string, err error) {
	p := strings.ToLower(strings.TrimSpace(pattern))
	if p == "" {
		return "", "", fmt.Errorf("storage: pattern must not be empty")
	}
	// "*." wildcard: drop the leading star so ILIKE '%.base' / '%@base' catches
	// both the base domain and its sub-domains.
	wild := strings.HasPrefix(p, "*.")
	base := p
	if wild {
		base = p[2:]
	}
	switch matchType {
	case RouteMatchFromAddr:
		return "LOWER(mail_from) LIKE $1", "%" + p + "%", nil
	case RouteMatchToAddr:
		return "LOWER(mail_to) LIKE $1", "%" + p + "%", nil
	case RouteMatchFromDomain:
		return "LOWER(mail_from) LIKE $1", "%@%" + base + "%", nil
	case RouteMatchToDomain:
		return "LOWER(mail_to) LIKE $1", "%@%" + base + "%", nil
	}
	return "", "", fmt.Errorf("storage: invalid match_type %q", matchType)
}

// DryRunRoutingRule counts and samples archived mails that a rule would match.
// sampleLimit bounds the sample (and is applied to the query) to avoid full
// table scans / timeouts on large archives.
// tenantScope, when non-nil, restricts the dry-run to mails already assigned to
// that tenant (enforced for tenant/domain admins so they cannot preview other
// tenants' mails). A nil tenantScope (superadmin) counts across all tenants.
func (s *Store) DryRunRoutingRule(ctx context.Context, matchType, pattern string, sampleLimit int, tenantScope *int64) (*RoutingDryRunResult, error) {
	if s.db == nil {
		return nil, fmt.Errorf("storage: no db")
	}
	if !validRouteMatchType(matchType) {
		return nil, fmt.Errorf("storage: invalid match_type %q", matchType)
	}
	if sampleLimit <= 0 || sampleLimit > 100 {
		sampleLimit = 20
	}
	cond, arg, err := dryRunCondition(matchType, pattern)
	if err != nil {
		return nil, err
	}
	args := []interface{}{arg}
	if tenantScope != nil {
		cond += fmt.Sprintf(" AND tenant_id = $%d", len(args)+1)
		args = append(args, *tenantScope)
	}

	res := &RoutingDryRunResult{
		MatchType:   matchType,
		Pattern:     strings.ToLower(strings.TrimSpace(pattern)),
		SampleLimit: sampleLimit,
		Sample:      []RoutingDryRunMatch{},
	}

	if err := s.db.QueryRow(ctx,
		`SELECT COUNT(*) FROM emails WHERE `+cond, args...,
	).Scan(&res.MatchCount); err != nil {
		return nil, fmt.Errorf("storage: dry-run count: %w", err)
	}

	sampleArgs := append(append([]interface{}{}, args...), sampleLimit)
	rows, err := s.db.Query(ctx,
		`SELECT id, COALESCE(mail_from,''), COALESCE(mail_to,''), COALESCE(subject,''), received_at
		 FROM emails WHERE `+cond+fmt.Sprintf(` ORDER BY received_at DESC LIMIT $%d`, len(sampleArgs)), sampleArgs...)
	if err != nil {
		return nil, fmt.Errorf("storage: dry-run sample: %w", err)
	}
	defer rows.Close()
	for rows.Next() {
		var m RoutingDryRunMatch
		if err := rows.Scan(&m.ID, &m.MailFrom, &m.MailTo, &m.Subject, &m.ReceivedAt); err != nil {
			return nil, fmt.Errorf("storage: dry-run scan: %w", err)
		}
		res.Sample = append(res.Sample, m)
	}
	return res, rows.Err()
}