import { connect } from '@tursodatabase/database';
import nodemailer from 'nodemailer';
import { resolve } from 'node:path';

if (!process.env.SMTP_HOST || !process.env.SMTP_FROM)
  throw new Error('SMTP delivery is not configured.');
const database = await connect(
  resolve(process.env.TDR_DATABASE_PATH || './data/shd-build.db'),
);
const transport = nodemailer.createTransport({
  host: process.env.SMTP_HOST,
  port: Number(process.env.SMTP_PORT || 587),
  secure: process.env.SMTP_SECURE === 'true',
  auth: process.env.SMTP_USER
    ? { user: process.env.SMTP_USER, pass: process.env.SMTP_PASSWORD }
    : undefined,
  requireTLS: process.env.SMTP_SECURE !== 'true',
  disableFileAccess: true,
  disableUrlAccess: true,
});
const origin = (process.env.APP_ORIGIN || 'http://localhost:5173').replace(
  /\/+$/,
  '',
);
const recipients = await (
  await database.prepare(`SELECT DISTINCT n.user_id,e.email
	FROM notifications n
	JOIN notification_preferences p ON p.user_id=n.user_id AND p.category=n.category AND p.email_frequency='daily'
	JOIN user_emails e ON e.user_id=n.user_id
	WHERE n.emailed_at IS NULL AND n.created_at>=p.updated_at`)
).all();
let sent = 0;
for (const recipient of recipients) {
  const items = await (
    await database.prepare(`SELECT n.id,n.title,n.body,n.link FROM notifications n
		JOIN notification_preferences p ON p.user_id=n.user_id AND p.category=n.category AND p.email_frequency='daily'
		WHERE n.user_id=? AND n.emailed_at IS NULL AND n.created_at>=p.updated_at ORDER BY n.created_at LIMIT 100`)
  ).all(recipient.user_id);
  if (!items.length) continue;
  const escape = (value) =>
    String(value).replace(
      /[&<>"']/g,
      (character) =>
        ({
          '&': '&amp;',
          '<': '&lt;',
          '>': '&gt;',
          '"': '&quot;',
          "'": '&#39;',
        })[character],
    );
  const rows = items
    .map(
      (item) =>
        `<div style="border-top:1px solid #34393a;padding:16px 0"><strong>${escape(item.title)}</strong><p style="color:#a5aaab">${escape(item.body)}</p>${item.link ? `<a style="color:#f6a821" href="${origin}${item.link}">VIEW INTELLIGENCE</a>` : ''}</div>`,
    )
    .join('');
  await transport.sendMail({
    from: process.env.SMTP_FROM,
    to: recipient.email,
    subject: `SHD.build daily notification summary · ${items.length} update${items.length === 1 ? '' : 's'}`,
    text: items
      .map(
        (item) =>
          `${item.title}\n${item.body}\n${item.link ? `${origin}${item.link}` : ''}`,
      )
      .join('\n\n'),
    html: `<div style="background:#0a0c0d;color:#ecebe5;padding:28px;font-family:Arial,sans-serif"><p style="color:#f6a821;font-size:12px;letter-spacing:2px">SHD DAILY INTELLIGENCE</p><h1>${items.length} notification${items.length === 1 ? '' : 's'}</h1>${rows}</div>`,
  });
  const now = new Date().toISOString();
  for (const item of items)
    await (
      await database.prepare(
        'UPDATE notifications SET emailed_at=? WHERE id=? AND emailed_at IS NULL',
      )
    ).run(now, item.id);
  sent += 1;
}
console.log(`Sent ${sent} daily notification digest${sent === 1 ? '' : 's'}.`);