126 lines
4.1 KiB
Go
126 lines
4.1 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
|
|
"ghb.freebede.com/nahakubuilder/mailgosend/internal/models"
|
|
)
|
|
|
|
// SaveDMARCReport inserts a DMARC aggregate report and its IP-level records atomically.
|
|
func (d *DB) SaveDMARCReport(ctx context.Context, report *models.DMARCReport) error {
|
|
return d.WithTx(ctx, func(tx *sql.Tx) error {
|
|
res, err := tx.ExecContext(ctx, `
|
|
INSERT INTO dmarc_reports
|
|
(domain_id, org_name, org_email, report_id, date_begin, date_end,
|
|
policy_domain, policy_adkim, policy_aspf, policy_p, policy_pct)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
report.DomainID, report.OrgName, report.OrgEmail, report.ReportID,
|
|
report.DateBegin, report.DateEnd, report.PolicyDomain,
|
|
report.PolicyADKIM, report.PolicyASPF, report.PolicyP, report.PolicyPct,
|
|
)
|
|
if err != nil {
|
|
return fmt.Errorf("insert dmarc_report: %w", err)
|
|
}
|
|
|
|
reportID, err := res.LastInsertId()
|
|
if err != nil {
|
|
return fmt.Errorf("dmarc report last insert id: %w", err)
|
|
}
|
|
|
|
for i := range report.Records {
|
|
rec := &report.Records[i]
|
|
_, err := tx.ExecContext(ctx, `
|
|
INSERT INTO dmarc_records
|
|
(report_id, source_ip, count, disposition, dkim_result, spf_result,
|
|
header_from, envelope_from, dkim_domain, dkim_selector, spf_domain)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
reportID, rec.SourceIP, rec.Count, rec.Disposition,
|
|
rec.DKIMResult, rec.SPFResult, rec.HeaderFrom, rec.EnvelopeFrom,
|
|
rec.DKIMDomain, rec.DKIMSelector, rec.SPFDomain,
|
|
)
|
|
if err != nil {
|
|
return fmt.Errorf("insert dmarc_record: %w", err)
|
|
}
|
|
}
|
|
return nil
|
|
})
|
|
}
|
|
|
|
// ListDMARCReports returns the most recent DMARC reports for a domain, newest first.
|
|
// limit caps the number of reports returned (0 = use default 100).
|
|
func (d *DB) ListDMARCReports(ctx context.Context, domainID int64, limit int) ([]*models.DMARCReport, error) {
|
|
if limit <= 0 {
|
|
limit = 100
|
|
}
|
|
rows, err := d.db.QueryContext(ctx, `
|
|
SELECT id, domain_id, org_name, org_email, report_id, date_begin, date_end,
|
|
policy_domain, policy_adkim, policy_aspf, policy_p, policy_pct, received_at
|
|
FROM dmarc_reports
|
|
WHERE domain_id = ?
|
|
ORDER BY received_at DESC
|
|
LIMIT ?`, domainID, limit)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("list dmarc_reports: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
var reports []*models.DMARCReport
|
|
for rows.Next() {
|
|
var r models.DMARCReport
|
|
if err := rows.Scan(
|
|
&r.ID, &r.DomainID, &r.OrgName, &r.OrgEmail, &r.ReportID,
|
|
&r.DateBegin, &r.DateEnd, &r.PolicyDomain, &r.PolicyADKIM,
|
|
&r.PolicyASPF, &r.PolicyP, &r.PolicyPct, &r.ReceivedAt,
|
|
); err != nil {
|
|
return nil, fmt.Errorf("scan dmarc_report: %w", err)
|
|
}
|
|
reports = append(reports, &r)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
// Load IP-level records for each report.
|
|
for _, rep := range reports {
|
|
if err := d.loadDMARCRecords(ctx, rep); err != nil {
|
|
return nil, err
|
|
}
|
|
}
|
|
return reports, nil
|
|
}
|
|
|
|
// loadDMARCRecords fetches and attaches all IP-level records for one report.
|
|
func (d *DB) loadDMARCRecords(ctx context.Context, report *models.DMARCReport) error {
|
|
rows, err := d.db.QueryContext(ctx, `
|
|
SELECT id, report_id, source_ip, count, disposition, dkim_result, spf_result,
|
|
header_from, envelope_from, dkim_domain, dkim_selector, spf_domain
|
|
FROM dmarc_records WHERE report_id = ? ORDER BY id`, report.ID)
|
|
if err != nil {
|
|
return fmt.Errorf("load dmarc_records: %w", err)
|
|
}
|
|
defer rows.Close()
|
|
|
|
for rows.Next() {
|
|
var rec models.DMARCRecord
|
|
if err := rows.Scan(
|
|
&rec.ID, &rec.ReportID, &rec.SourceIP, &rec.Count, &rec.Disposition,
|
|
&rec.DKIMResult, &rec.SPFResult, &rec.HeaderFrom, &rec.EnvelopeFrom,
|
|
&rec.DKIMDomain, &rec.DKIMSelector, &rec.SPFDomain,
|
|
); err != nil {
|
|
return fmt.Errorf("scan dmarc_record: %w", err)
|
|
}
|
|
report.Records = append(report.Records, rec)
|
|
}
|
|
return rows.Err()
|
|
}
|
|
|
|
// DMARCReportCount returns the total number of reports stored for a domain.
|
|
func (d *DB) DMARCReportCount(ctx context.Context, domainID int64) (int, error) {
|
|
var n int
|
|
err := d.db.QueryRowContext(ctx,
|
|
"SELECT COUNT(*) FROM dmarc_reports WHERE domain_id=?", domainID).Scan(&n)
|
|
return n, err
|
|
}
|