const { getDatabase } = require('../config/database');
const nodemailer = require('nodemailer');
require('dotenv').config();

const transporter = nodemailer.createTransport({
  host: process.env.SMTP_HOST,
  port: process.env.SMTP_PORT,
  secure: false, 
  auth: {
    user: process.env.SMTP_USER,
    pass: process.env.SMTP_PASS,
  },
});

async function runDailyAutomation() {
  const db = getDatabase();
  console.log("Starting Daily PES Automation...");

  // 1. Identify Overdue Tasks
  const overdueTasks = db.prepare(`
    SELECT t.title, t.due_date, u.email, u.first_name 
    FROM tasks t
    JOIN users u ON t.assignee_id = u.id
    WHERE t.status != 'completed' 
    AND t.due_date < date('now')
    AND t.reminder_sent = 0
  `).all();

  for (const task of overdueTasks) {
    try {
      await transporter.sendMail({
        from: '"SJH PES System" <no-reply@govinduvach.in>',
        to: task.email,
        subject: `ACTION REQUIRED: Task Overdue - ${task.title}`,
        text: `Hi ${task.first_name}, your task "${task.title}" was due on ${task.due_date}. Please update the status or upload proof of work at pesteam.govinduvach.in.`,
      });
      console.log(`Reminder sent to ${task.email}`);
    } catch (err) {
      console.error(`Mail failed for ${task.email}`);
    }
  }

  // 2. Mark reminders as sent so we don't spam
  db.prepare("UPDATE tasks SET reminder_sent = 1 WHERE due_date < date('now')").run();

  console.log("Automation Complete.");
}

runDailyAutomation();