234 lines
8.6 KiB
JavaScript
234 lines
8.6 KiB
JavaScript
|
import pg from 'pg';
|
||
|
|
import nodemailer from 'nodemailer';
|
||
|
|
import dotenv from 'dotenv';
|
||
|
|
|
||
|
|
dotenv.config();
|
||
|
|
|
||
|
|
const pool = new pg.Pool({
|
||
|
|
connectionString: process.env.DATABASE_URL || 'postgresql://tenderpilot:tenderpilot123@localhost:5432/tenderpilot'
|
||
|
|
});
|
||
|
|
|
||
|
|
// Email transporter
|
||
|
|
const transporter = nodemailer.createTransport({
|
||
|
|
host: process.env.SMTP_HOST || 'smtp.gmail.com',
|
||
|
|
port: parseInt(process.env.SMTP_PORT || '587'),
|
||
|
|
secure: process.env.SMTP_PORT === '465',
|
||
|
|
auth: {
|
||
|
|
user: process.env.SMTP_USER || 'alerts@tenderradar.co.uk',
|
||
|
|
pass: process.env.SMTP_PASS || 'placeholder'
|
||
|
|
}
|
||
|
|
});
|
||
|
|
|
||
|
|
// HTML email template
|
||
|
|
function generateEmailTemplate(userEmail, matchedTenders) {
|
||
|
|
const tenderRows = matchedTenders.map(tender => `
|
||
|
|
<tr>
|
||
|
|
<td style="padding: 12px; border-bottom: 1px solid #eee;">
|
||
|
|
<strong>${sanitizeHtml(tender.title)}</strong>
|
||
|
|
<br/>
|
||
|
|
<small style="color: #666;">
|
||
|
|
${tender.source} | Deadline: ${new Date(tender.deadline).toLocaleDateString()}
|
||
|
|
</small>
|
||
|
|
</td>
|
||
|
|
<td style="padding: 12px; border-bottom: 1px solid #eee; text-align: right;">
|
||
|
|
£${tender.value_high ? parseFloat(tender.value_high).toLocaleString('en-GB', {minimumFractionDigits: 0, maximumFractionDigits: 0}) : 'N/A'}
|
||
|
|
</td>
|
||
|
|
<td style="padding: 12px; border-bottom: 1px solid #eee; text-align: center;">
|
||
|
|
<a href="${sanitizeHtml(tender.notice_url || '#')}" style="color: #0066cc; text-decoration: none;">View</a>
|
||
|
|
</td>
|
||
|
|
</tr>
|
||
|
|
`).join('');
|
||
|
|
|
||
|
|
return `
|
||
|
|
<!DOCTYPE html>
|
||
|
|
<html>
|
||
|
|
<head>
|
||
|
|
<meta charset="UTF-8">
|
||
|
|
<style>
|
||
|
|
body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
|
||
|
|
.container { max-width: 600px; margin: 0 auto; padding: 20px; }
|
||
|
|
.header { background-color: #1e40af; color: white; padding: 20px; border-radius: 5px 5px 0 0; text-align: center; }
|
||
|
|
.content { background-color: #f9fafb; padding: 20px; }
|
||
|
|
.footer { background-color: #f3f4f6; padding: 15px; text-align: center; font-size: 12px; color: #666; border-radius: 0 0 5px 5px; }
|
||
|
|
table { width: 100%; border-collapse: collapse; margin-top: 15px; }
|
||
|
|
.cta-button { display: inline-block; background-color: #0066cc; color: white; padding: 10px 20px; text-decoration: none; border-radius: 3px; margin-top: 15px; }
|
||
|
|
</style>
|
||
|
|
</head>
|
||
|
|
<body>
|
||
|
|
<div class="container">
|
||
|
|
<div class="header">
|
||
|
|
<h1>TenderRadar Daily Digest</h1>
|
||
|
|
<p>Your matched tenders for today</p>
|
||
|
|
</div>
|
||
|
|
<div class="content">
|
||
|
|
<p>Hello,</p>
|
||
|
|
<p>We found <strong>${matchedTenders.length}</strong> tender(s) matching your preferences:</p>
|
||
|
|
|
||
|
|
<table>
|
||
|
|
<thead>
|
||
|
|
<tr style="background-color: #e5e7eb;">
|
||
|
|
<th style="padding: 12px; text-align: left; font-weight: bold;">Tender</th>
|
||
|
|
<th style="padding: 12px; text-align: right; font-weight: bold;">Value</th>
|
||
|
|
<th style="padding: 12px; text-align: center; font-weight: bold;">Action</th>
|
||
|
|
</tr>
|
||
|
|
</thead>
|
||
|
|
<tbody>
|
||
|
|
${tenderRows}
|
||
|
|
</tbody>
|
||
|
|
</table>
|
||
|
|
|
||
|
|
<center>
|
||
|
|
<a href="https://tenderradar.co.uk/dashboard" class="cta-button">View All Matches</a>
|
||
|
|
</center>
|
||
|
|
|
||
|
|
<p style="margin-top: 30px; font-size: 13px; color: #666;">
|
||
|
|
<strong>Manage your preferences:</strong><br/>
|
||
|
|
You can update your alert keywords and categories in your <a href="https://tenderradar.co.uk/settings/alerts" style="color: #0066cc;">account settings</a>.
|
||
|
|
</p>
|
||
|
|
</div>
|
||
|
|
<div class="footer">
|
||
|
|
<p>TenderRadar - UK Public Sector Tender Finder</p>
|
||
|
|
<p><a href="https://tenderradar.co.uk/unsubscribe" style="color: #0066cc; text-decoration: none;">Unsubscribe from digests</a></p>
|
||
|
|
</div>
|
||
|
|
</div>
|
||
|
|
</body>
|
||
|
|
</html>
|
||
|
|
`;
|
||
|
|
}
|
||
|
|
|
||
|
|
function sanitizeHtml(text) {
|
||
|
|
if (!text) return '';
|
||
|
|
return text
|
||
|
|
.replace(/&/g, '&')
|
||
|
|
.replace(/</g, '<')
|
||
|
|
.replace(/>/g, '>')
|
||
|
|
.replace(/"/g, '"')
|
||
|
|
.replace(/'/g, ''');
|
||
|
|
}
|
||
|
|
|
||
|
|
// Match tender against user preferences
|
||
|
|
function matchesTenderPreference(tender, preference) {
|
||
|
|
if (!preference) return false;
|
||
|
|
|
||
|
|
// Check value range
|
||
|
|
if (preference.min_value && tender.value_high && tender.value_high < preference.min_value) return false;
|
||
|
|
if (preference.max_value && tender.value_low && tender.value_low > preference.max_value) return false;
|
||
|
|
|
||
|
|
// Check locations
|
||
|
|
if (preference.locations && preference.locations.length > 0) {
|
||
|
|
if (!tender.location || !preference.locations.some(loc => tender.location.toLowerCase().includes(loc.toLowerCase()))) {
|
||
|
|
return false;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Check authority types
|
||
|
|
if (preference.authority_types && preference.authority_types.length > 0) {
|
||
|
|
if (!tender.authority_type || !preference.authority_types.some(type => tender.authority_type.toLowerCase().includes(type.toLowerCase()))) {
|
||
|
|
return false;
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Check keywords (match title or description)
|
||
|
|
if (preference.keywords && preference.keywords.length > 0) {
|
||
|
|
const searchText = `${tender.title} ${tender.description || ''}`.toLowerCase();
|
||
|
|
const keywordMatch = preference.keywords.some(keyword => searchText.includes(keyword.toLowerCase()));
|
||
|
|
if (!keywordMatch) return false;
|
||
|
|
}
|
||
|
|
|
||
|
|
// Check sectors (against CPV codes or category)
|
||
|
|
if (preference.sectors && preference.sectors.length > 0) {
|
||
|
|
const tenderCpv = (tender.cpv_codes || []).join(' ');
|
||
|
|
const sectorMatch = preference.sectors.some(sector => tenderCpv.includes(sector));
|
||
|
|
if (!sectorMatch && preference.sectors.length > 0) {
|
||
|
|
// If no sector match and sectors are specified, don't match
|
||
|
|
// (allow if sectors array is empty or not provided)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
return true;
|
||
|
|
}
|
||
|
|
|
||
|
|
async function sendDigest(dryRun = false) {
|
||
|
|
console.log(`[${new Date().toISOString()}] Starting email digest ${dryRun ? '(DRY RUN)' : ''}...`);
|
||
|
|
|
||
|
|
try {
|
||
|
|
// Get all users with profiles
|
||
|
|
const usersResult = await pool.query(`
|
||
|
|
SELECT u.id, u.email, p.keywords, p.sectors, p.min_value, p.max_value, p.locations, p.authority_types
|
||
|
|
FROM users u
|
||
|
|
INNER JOIN profiles p ON u.id = p.user_id
|
||
|
|
WHERE u.verified = true
|
||
|
|
`);
|
||
|
|
|
||
|
|
console.log(`Found ${usersResult.rows.length} users with preferences`);
|
||
|
|
|
||
|
|
// For each user, find matching new tenders
|
||
|
|
let emailCount = 0;
|
||
|
|
let totalMatches = 0;
|
||
|
|
|
||
|
|
for (const user of usersResult.rows) {
|
||
|
|
// Get tenders published in the last 24 hours that haven't been sent to this user
|
||
|
|
const tendersResult = await pool.query(`
|
||
|
|
SELECT t.* FROM tenders t
|
||
|
|
LEFT JOIN matches m ON t.id = m.tender_id AND m.user_id = $1
|
||
|
|
WHERE t.status = 'open'
|
||
|
|
AND t.published_date > NOW() - INTERVAL '24 hours'
|
||
|
|
AND m.id IS NULL
|
||
|
|
ORDER BY t.deadline ASC
|
||
|
|
`, [user.id]);
|
||
|
|
|
||
|
|
// Filter by user preferences
|
||
|
|
const matchedTenders = tendersResult.rows.filter(tender => matchesTenderPreference(tender, user));
|
||
|
|
|
||
|
|
if (matchedTenders.length > 0) {
|
||
|
|
console.log(` User ${user.email}: ${matchedTenders.length} matched tender(s)`);
|
||
|
|
totalMatches += matchedTenders.length;
|
||
|
|
|
||
|
|
if (!dryRun) {
|
||
|
|
try {
|
||
|
|
const htmlContent = generateEmailTemplate(user.email, matchedTenders);
|
||
|
|
|
||
|
|
await transporter.sendMail({
|
||
|
|
from: process.env.SMTP_FROM || 'TenderRadar Alerts <alerts@tenderradar.co.uk>',
|
||
|
|
to: user.email,
|
||
|
|
subject: `TenderRadar Daily Digest - ${matchedTenders.length} new tender(s)`,
|
||
|
|
html: htmlContent
|
||
|
|
});
|
||
|
|
|
||
|
|
console.log(` ✓ Email sent to ${user.email}`);
|
||
|
|
emailCount++;
|
||
|
|
|
||
|
|
// Mark tenders as sent
|
||
|
|
for (const tender of matchedTenders) {
|
||
|
|
await pool.query(`
|
||
|
|
INSERT INTO matches (user_id, tender_id, sent)
|
||
|
|
VALUES ($1, $2, true)
|
||
|
|
ON CONFLICT (user_id, tender_id) DO UPDATE SET sent = true
|
||
|
|
`, [user.id, tender.id]);
|
||
|
|
}
|
||
|
|
} catch (emailError) {
|
||
|
|
console.error(` ✗ Failed to send email to ${user.email}:`, emailError.message);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
console.log(`[${new Date().toISOString()}] Digest complete: ${emailCount} email(s) sent, ${totalMatches} total matches`);
|
||
|
|
|
||
|
|
if (dryRun) {
|
||
|
|
console.log('[DRY RUN] No emails actually sent. Run without --dry-run to send.');
|
||
|
|
}
|
||
|
|
|
||
|
|
await pool.end();
|
||
|
|
process.exit(0);
|
||
|
|
} catch (error) {
|
||
|
|
console.error('Error in digest process:', error);
|
||
|
|
await pool.end();
|
||
|
|
process.exit(1);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
|
||
|
|
// Check for --dry-run flag
|
||
|
|
const isDryRun = process.argv.includes('--dry-run');
|
||
|
|
sendDigest(isDryRun);
|