const { getDb } = require('../database');
const { sendExpiryNotification } = require('./email');

async function checkExpiringCertificates() {
  try {
    const db = getDb();
    const today = new Date();
    const thirtyDaysLater = new Date(today);
    thirtyDaysLater.setDate(thirtyDaysLater.getDate() + 30);

    const todayStr = today.toISOString().split('T')[0];
    const futureStr = thirtyDaysLater.toISOString().split('T')[0];

    // Get certificates expiring within 30 days
    const expiring = db.prepare(`
      SELECT c.*, u.email as creator_email, u.full_name as creator_name
      FROM certificates c
      JOIN users u ON c.created_by = u.id
      WHERE c.next_due_date BETWEEN ? AND ?
      ORDER BY c.next_due_date ASC
    `).all(todayStr, futureStr);

    if (expiring.length === 0) return;

    // Create notifications for all active users
    const activeUsers = db.prepare("SELECT id, email, full_name FROM users WHERE status = 'active'").all();

    const insertNotif = db.prepare(`
      INSERT OR IGNORE INTO notifications (user_id, certificate_id, message, type)
      VALUES (?, ?, ?, 'expiry')
    `);

    for (const user of activeUsers) {
      for (const cert of expiring) {
        const msg = `Certificate ${cert.certificate_number} for ${cert.client} expires on ${cert.next_due_date}`;
        insertNotif.run(user.id, cert.id, msg);
      }
      // Send email notification
      await sendExpiryNotification(user.email, user.full_name, expiring);
    }

    console.log(`Expiry check: ${expiring.length} certificate(s) expiring soon. Notified ${activeUsers.length} user(s).`);
  } catch (err) {
    console.error('Error checking expiring certificates:', err.message);
  }
}

module.exports = { checkExpiringCertificates };
