ck_qr_nextjs_node_redis_postgres/api/utils/emailService.js

const nodemailer = require('nodemailer');
const { Pool } = require('pg');

const pgPool = new Pool({
    host: process.env.PGHOST || 'postgres',
    port: process.env.PGPORT || 5432,
    user: process.env.PGUSER || 'postgres',
    password: process.env.PGPASSWORD || 'postgres',
    database: process.env.PGDATABASE || 'ckqr',
});

// Helper to fetch SMTP settings from the database
async function getSmtpSettings() {
    const result = await pgPool.query("SELECT key, value FROM app_settings WHERE category = 'smtp'");
    const settings = {};
    result.rows.forEach(row => {
        settings[row.key] = row.value;
    });
    return settings;
}

// Helper to create and configure a transporter
async function getTransporter() {
    const settings = await getSmtpSettings();
    
    return nodemailer.createTransport({
        host: settings.smtp_host || 'smtp.office365.com',
        port: parseInt(settings.smtp_port) || 587,
        secure: settings.smtp_secure === 'true', // true for 465, false for other ports
        auth: {
            user: settings.smtp_user,
            pass: settings.smtp_pass,
        },
        tls: {
            ciphers: 'SSLv3', // sometimes required for Office365
            rejectUnauthorized: false
        }
    });
}

/**
 * Sends an email using dynamic SMTP settings from the DB.
 * 
 * @param {string} to - Recipient email address
 * @param {string} subject - Email subject
 * @param {string} html - HTML body of the email
 */
async function sendMail(to, subject, html) {
    const settings = await getSmtpSettings();
    const transporter = await getTransporter();

    const mailOptions = {
        from: `"CK-QR Support" <${settings.smtp_user}>`,
        to: to,
        subject: subject,
        html: html
    };

    try {
        const info = await transporter.sendMail(mailOptions);
        console.log('Message sent: %s', info.messageId);
        return { success: true, messageId: info.messageId };
    } catch (error) {
        console.error('Error sending email:', error);
        throw error;
    }
}

module.exports = { sendMail, getSmtpSettings };