mailgosend/internal/db/domains.go

package db

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

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

// nullStr converts sql.NullString to plain string (empty if NULL).
func nullStr(ns sql.NullString) string { return ns.String }

// domainCols is the shared column list for all domain SELECT queries.
const domainCols = `id, name, enabled, dkim_private_enc, dkim_public, dkim_selector,
	       dkim_algo, spf_policy, dmarc_policy, max_users, max_quota_bytes, created_at, dmarc_rua`

// scanDomain reads a domain row from any row scanner.
func scanDomain(r interface {
	Scan(dest ...any) error
}) (*models.Domain, error) {
	var dom models.Domain
	var privEnc []byte
	var dkimPublic, dkimSelector, dkimAlgo, spfPolicy, dmarcPolicy, dmarcRua sql.NullString
	err := r.Scan(
		&dom.ID, &dom.Name, &dom.Enabled,
		&privEnc, &dkimPublic, &dkimSelector,
		&dkimAlgo, &spfPolicy, &dmarcPolicy,
		&dom.MaxUsers, &dom.MaxQuotaBytes, &dom.CreatedAt,
		&dmarcRua,
	)
	if err != nil {
		return nil, err
	}
	dom.DKIMPrivateEnc = privEnc
	dom.DKIMPublic = nullStr(dkimPublic)
	dom.DKIMSelector = nullStr(dkimSelector)
	dom.DKIMAlgo = nullStr(dkimAlgo)
	dom.SPFPolicy = nullStr(spfPolicy)
	dom.DMARCPolicy = nullStr(dmarcPolicy)
	dom.DMARCRua = nullStr(dmarcRua)
	return &dom, nil
}

// GetDomain returns the domain row by name, or nil if not found.
func (d *DB) GetDomain(ctx context.Context, name string) (*models.Domain, error) {
	row := d.db.QueryRowContext(ctx,
		"SELECT "+domainCols+" FROM domains WHERE lower(name) = lower(?)", name)
	dom, err := scanDomain(row)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get domain: %w", err)
	}
	return dom, nil
}

// GetDomainByID returns the domain row by ID.
func (d *DB) GetDomainByID(ctx context.Context, id int64) (*models.Domain, error) {
	row := d.db.QueryRowContext(ctx,
		"SELECT "+domainCols+" FROM domains WHERE id = ?", id)
	dom, err := scanDomain(row)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get domain by id: %w", err)
	}
	return dom, nil
}

// GetDomainByDMARCRua returns the enabled domain whose dmarc_rua matches the given address.
// Returns nil if no domain is configured for this address.
func (d *DB) GetDomainByDMARCRua(ctx context.Context, rua string) (*models.Domain, error) {
	row := d.db.QueryRowContext(ctx,
		"SELECT "+domainCols+" FROM domains WHERE lower(dmarc_rua) = lower(?) AND enabled=1", rua)
	dom, err := scanDomain(row)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get domain by dmarc rua: %w", err)
	}
	return dom, nil
}

// SetDomainDMARCRua sets or clears the DMARC monitoring address for a domain.
// Pass an empty string to disable monitoring.
func (d *DB) SetDomainDMARCRua(ctx context.Context, domainID int64, rua string) error {
	var val any
	if rua != "" {
		val = rua
	} // else nil → NULL
	_, err := d.db.ExecContext(ctx,
		"UPDATE domains SET dmarc_rua=? WHERE id=?", val, domainID)
	return err
}

// IsLocalDomain returns true if name is a known enabled domain.
func (d *DB) IsLocalDomain(ctx context.Context, name string) (bool, error) {
	var count int
	err := d.db.QueryRowContext(ctx,
		"SELECT COUNT(*) FROM domains WHERE lower(name)=lower(?) AND enabled=1", name).
		Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// ListDomains returns all domains ordered by name.
func (d *DB) ListDomains(ctx context.Context) ([]*models.Domain, error) {
	rows, err := d.db.QueryContext(ctx,
		"SELECT "+domainCols+" FROM domains ORDER BY name")
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var doms []*models.Domain
	for rows.Next() {
		dom, err := scanDomain(rows)
		if err != nil {
			return nil, err
		}
		doms = append(doms, dom)
	}
	return doms, rows.Err()
}

// CreateDomain inserts a new domain. Returns the new ID.
func (d *DB) CreateDomain(ctx context.Context, name, selector, algo string) (int64, error) {
	res, err := d.db.ExecContext(ctx, `
		INSERT INTO domains (name, enabled, dkim_selector, dkim_algo)
		VALUES (?, 1, ?, ?)`, name, selector, algo)
	if err != nil {
		return 0, fmt.Errorf("create domain: %w", err)
	}
	return res.LastInsertId()
}

// SaveDKIMKeys stores encrypted DKIM private key + public key for a domain.
func (d *DB) SaveDKIMKeys(ctx context.Context, domainID int64, privEnc []byte, pubPEM string) error {
	_, err := d.db.ExecContext(ctx,
		"UPDATE domains SET dkim_private_enc=?, dkim_public=? WHERE id=?",
		privEnc, pubPEM, domainID)
	return err
}