mailgosend/internal/db/relay.go

// Package db — relay account and IP relay rule operations.
package db

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

	"ghb.freebede.com/nahakubuilder/mailgosend/internal/models"
)

// ---- Relay accounts ----

// GetRelayAccountByUsername returns the relay account with the given username (case-insensitive), or nil.
func (d *DB) GetRelayAccountByUsername(ctx context.Context, username string) (*models.RelayAccount, error) {
	row := d.db.QueryRowContext(ctx, `
		SELECT id, domain_id, username, password_hash, display_name, enabled, description, created_at
		FROM relay_accounts WHERE lower(username)=lower(?)`, username)
	return scanRelayAccount(row)
}

// GetRelayAccountByID returns the relay account with the given ID, or nil.
func (d *DB) GetRelayAccountByID(ctx context.Context, id int64) (*models.RelayAccount, error) {
	row := d.db.QueryRowContext(ctx, `
		SELECT id, domain_id, username, password_hash, display_name, enabled, description, created_at
		FROM relay_accounts WHERE id=?`, id)
	return scanRelayAccount(row)
}

// RelayAccountWithDomain pairs a RelayAccount with its domain name for cross-domain listings.
type RelayAccountWithDomain struct {
	*models.RelayAccount
	DomainName string
}

// ListAllRelayAccounts returns every relay account across all domains, joined with domain name.
func (d *DB) ListAllRelayAccounts(ctx context.Context) ([]*RelayAccountWithDomain, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT r.id, r.domain_id, r.username, r.password_hash, r.display_name,
		       r.enabled, r.description, r.created_at, d.name
		FROM relay_accounts r
		JOIN domains d ON d.id = r.domain_id
		ORDER BY d.name, r.username`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*RelayAccountWithDomain
	for rows.Next() {
		a := &models.RelayAccount{}
		var domainName string
		if err := rows.Scan(&a.ID, &a.DomainID, &a.Username, &a.PasswordHash,
			&a.DisplayName, &a.Enabled, &a.Description, &a.CreatedAt, &domainName); err != nil {
			return nil, fmt.Errorf("scan relay account with domain: %w", err)
		}
		out = append(out, &RelayAccountWithDomain{RelayAccount: a, DomainName: domainName})
	}
	return out, rows.Err()
}

// ListRelayAccounts returns all relay accounts for a domain, ordered by username.
func (d *DB) ListRelayAccounts(ctx context.Context, domainID int64) ([]*models.RelayAccount, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT id, domain_id, username, password_hash, display_name, enabled, description, created_at
		FROM relay_accounts WHERE domain_id=? ORDER BY username`, domainID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*models.RelayAccount
	for rows.Next() {
		a, err := scanRelayAccountRow(rows)
		if err != nil {
			return nil, err
		}
		out = append(out, a)
	}
	return out, rows.Err()
}

// CreateRelayAccount inserts a new relay account. Returns the new ID.
func (d *DB) CreateRelayAccount(ctx context.Context, domainID int64, username, passwordHash, displayName, description string) (int64, error) {
	res, err := d.db.ExecContext(ctx, `
		INSERT INTO relay_accounts (domain_id, username, password_hash, display_name, description, created_at)
		VALUES (?,?,?,?,?,?)`,
		domainID, username, passwordHash, displayName, description, time.Now().UTC())
	if err != nil {
		return 0, fmt.Errorf("create relay account: %w", err)
	}
	return res.LastInsertId()
}

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

// SetRelayAccountPassword updates the password hash for a relay account.
func (d *DB) SetRelayAccountPassword(ctx context.Context, id int64, hash string) error {
	_, err := d.db.ExecContext(ctx, "UPDATE relay_accounts SET password_hash=? WHERE id=?", hash, id)
	return err
}

// DeleteRelayAccount permanently removes a relay account and its send-as patterns.
func (d *DB) DeleteRelayAccount(ctx context.Context, id int64) error {
	_, err := d.db.ExecContext(ctx, "DELETE FROM relay_accounts WHERE id=?", id)
	return err
}

// ---- Relay send-as (per relay account) ----

// GetRelaySendAs returns all allowed sender patterns for a relay account.
func (d *DB) GetRelaySendAs(ctx context.Context, relayAccountID int64) ([]*models.RelaySendAs, error) {
	rows, err := d.db.QueryContext(ctx,
		"SELECT id, relay_account_id, pattern, created_at FROM relay_send_as WHERE relay_account_id=? ORDER BY id",
		relayAccountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*models.RelaySendAs
	for rows.Next() {
		r := &models.RelaySendAs{}
		if err := rows.Scan(&r.ID, &r.RelayAccountID, &r.Pattern, &r.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// AddRelaySendAs adds a sender pattern for a relay account. Duplicate patterns are ignored.
func (d *DB) AddRelaySendAs(ctx context.Context, relayAccountID int64, pattern string) error {
	_, err := d.db.ExecContext(ctx,
		"INSERT OR IGNORE INTO relay_send_as (relay_account_id, pattern) VALUES (?,?)",
		relayAccountID, pattern)
	return err
}

// DeleteRelaySendAs removes a sender pattern. relayAccountID is verified.
func (d *DB) DeleteRelaySendAs(ctx context.Context, id, relayAccountID int64) error {
	_, err := d.db.ExecContext(ctx,
		"DELETE FROM relay_send_as WHERE id=? AND relay_account_id=?", id, relayAccountID)
	return err
}

// IsRelaySenderAllowed returns true when the relay account is permitted to send as fromEmail.
// Any pattern in relay_send_as matching the address is sufficient.
func (d *DB) IsRelaySenderAllowed(ctx context.Context, relayAccountID int64, fromEmail string) (bool, error) {
	rows, err := d.db.QueryContext(ctx,
		"SELECT pattern FROM relay_send_as WHERE relay_account_id=?", relayAccountID)
	if err != nil {
		return false, err
	}
	defer rows.Close()

	for rows.Next() {
		var pattern string
		if err := rows.Scan(&pattern); err != nil {
			return false, err
		}
		if matchSenderPattern(pattern, fromEmail) {
			return true, nil
		}
	}
	return false, rows.Err()
}

// ---- Relay IP rules (per domain) ----

// GetRelayIPRules returns all IP relay rules for a domain.
func (d *DB) GetRelayIPRules(ctx context.Context, domainID int64) ([]*models.RelayIPRule, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT id, domain_id, cidr, sender_pattern, description, created_at
		FROM relay_ip_rules WHERE domain_id=? ORDER BY id`, domainID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*models.RelayIPRule
	for rows.Next() {
		r := &models.RelayIPRule{}
		if err := rows.Scan(&r.ID, &r.DomainID, &r.CIDR, &r.SenderPattern, &r.Description, &r.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// AddRelayIPRule adds an IP relay rule for a domain.
func (d *DB) AddRelayIPRule(ctx context.Context, domainID int64, cidr, senderPattern, description string) error {
	_, err := d.db.ExecContext(ctx, `
		INSERT INTO relay_ip_rules (domain_id, cidr, sender_pattern, description)
		VALUES (?,?,?,?)`, domainID, cidr, senderPattern, description)
	return err
}

// DeleteRelayIPRule removes an IP relay rule. domainID is verified.
func (d *DB) DeleteRelayIPRule(ctx context.Context, id, domainID int64) error {
	_, err := d.db.ExecContext(ctx,
		"DELETE FROM relay_ip_rules WHERE id=? AND domain_id=?", id, domainID)
	return err
}

// CheckIPRelay returns true when clientIP is authorized to send as senderEmail
// via any active IP relay rule across all enabled domains.
func (d *DB) CheckIPRelay(ctx context.Context, clientIP, senderEmail string) (bool, error) {
	rows, err := d.db.QueryContext(ctx, `
		SELECT r.cidr, r.sender_pattern
		FROM relay_ip_rules r
		JOIN domains d ON d.id = r.domain_id
		WHERE d.enabled = 1`)
	if err != nil {
		return false, err
	}
	defer rows.Close()

	for rows.Next() {
		var cidr, pattern string
		if err := rows.Scan(&cidr, &pattern); err != nil {
			return false, err
		}
		if ipInCIDR(cidr, clientIP) && matchSenderPattern(pattern, senderEmail) {
			return true, nil
		}
	}
	return false, rows.Err()
}

// ---- Relay account IP whitelist ----

// GetRelayAccountIPs returns all IP whitelist entries for a relay account.
func (d *DB) GetRelayAccountIPs(ctx context.Context, relayAccountID int64) ([]*models.RelayAccountIP, error) {
	rows, err := d.db.QueryContext(ctx,
		"SELECT id, relay_account_id, cidr, description, created_at FROM relay_account_ips WHERE relay_account_id=? ORDER BY id",
		relayAccountID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*models.RelayAccountIP
	for rows.Next() {
		r := &models.RelayAccountIP{}
		if err := rows.Scan(&r.ID, &r.RelayAccountID, &r.CIDR, &r.Description, &r.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// AddRelayAccountIP adds an IP/CIDR whitelist entry for a relay account.
func (d *DB) AddRelayAccountIP(ctx context.Context, relayAccountID int64, cidr, description string) error {
	_, err := d.db.ExecContext(ctx,
		"INSERT INTO relay_account_ips (relay_account_id, cidr, description) VALUES (?,?,?)",
		relayAccountID, cidr, description)
	return err
}

// DeleteRelayAccountIP removes an IP whitelist entry. relayAccountID is verified.
func (d *DB) DeleteRelayAccountIP(ctx context.Context, id, relayAccountID int64) error {
	_, err := d.db.ExecContext(ctx,
		"DELETE FROM relay_account_ips WHERE id=? AND relay_account_id=?", id, relayAccountID)
	return err
}

// FindRelayAccountByIP returns the first enabled relay account whose IP whitelist
// contains clientIP and whose send-as patterns allow senderEmail.
// Returns nil (no error) if no match.
func (d *DB) FindRelayAccountByIP(ctx context.Context, clientIP, senderEmail string) (*models.RelayAccount, error) {
	// Load all enabled relay accounts that have at least one IP whitelist entry.
	rows, err := d.db.QueryContext(ctx, `
		SELECT DISTINCT r.id, r.domain_id, r.username, r.password_hash, r.display_name,
		       r.enabled, r.description, r.created_at
		FROM relay_accounts r
		JOIN relay_account_ips ip ON ip.relay_account_id = r.id
		WHERE r.enabled = 1`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	type candidate struct {
		acc  *models.RelayAccount
	}
	var candidates []candidate
	for rows.Next() {
		a := &models.RelayAccount{}
		if err := rows.Scan(&a.ID, &a.DomainID, &a.Username, &a.PasswordHash,
			&a.DisplayName, &a.Enabled, &a.Description, &a.CreatedAt); err != nil {
			return nil, fmt.Errorf("scan relay account: %w", err)
		}
		candidates = append(candidates, candidate{acc: a})
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}

	for _, c := range candidates {
		// Check IP whitelist.
		ipRows, err := d.db.QueryContext(ctx,
			"SELECT cidr FROM relay_account_ips WHERE relay_account_id=?", c.acc.ID)
		if err != nil {
			return nil, err
		}
		ipMatch := false
		for ipRows.Next() {
			var cidr string
			if err := ipRows.Scan(&cidr); err != nil {
				ipRows.Close()
				return nil, err
			}
			if ipInCIDR(cidr, clientIP) {
				ipMatch = true
				break
			}
		}
		ipRows.Close()
		if !ipMatch {
			continue
		}

		// IP matched — check send-as patterns.
		allowed, err := d.IsRelaySenderAllowed(ctx, c.acc.ID, senderEmail)
		if err != nil {
			return nil, err
		}
		if allowed {
			return c.acc, nil
		}
	}
	return nil, nil
}

// ---- Helpers ----

func scanRelayAccount(row *sql.Row) (*models.RelayAccount, error) {
	a := &models.RelayAccount{}
	err := row.Scan(&a.ID, &a.DomainID, &a.Username, &a.PasswordHash,
		&a.DisplayName, &a.Enabled, &a.Description, &a.CreatedAt)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan relay account: %w", err)
	}
	return a, nil
}

func scanRelayAccountRow(rows *sql.Rows) (*models.RelayAccount, error) {
	a := &models.RelayAccount{}
	err := rows.Scan(&a.ID, &a.DomainID, &a.Username, &a.PasswordHash,
		&a.DisplayName, &a.Enabled, &a.Description, &a.CreatedAt)
	if err != nil {
		return nil, fmt.Errorf("scan relay account row: %w", err)
	}
	return a, nil
}

// matchSenderPattern checks if email matches pattern.
// "*@domain.com" matches any address at that domain.
// Anything else is an exact case-insensitive match.
func matchSenderPattern(pattern, email string) bool {
	pattern = strings.ToLower(strings.TrimSpace(pattern))
	email = strings.ToLower(strings.TrimSpace(email))
	if pattern == email {
		return true
	}
	if strings.HasPrefix(pattern, "*@") {
		domain := pattern[2:]
		at := strings.LastIndex(email, "@")
		return at > 0 && email[at+1:] == domain
	}
	return false
}

// ipInCIDR returns true when ip falls within the cidr range.
// cidr may be a plain IP or CIDR notation.
func ipInCIDR(cidr, ip string) bool {
	parsedIP := net.ParseIP(ip)
	if parsedIP == nil {
		return false
	}
	if !strings.Contains(cidr, "/") {
		target := net.ParseIP(cidr)
		return target != nil && target.Equal(parsedIP)
	}
	_, network, err := net.ParseCIDR(cidr)
	if err != nil {
		return false
	}
	return network.Contains(parsedIP)
}