const pool = require('../config/db');

/**
 * @param {object} filter - Optional { country_id, location_id, department_id } from master dashboard; filters by assigned user's location.
 */
async function getAllSchedules(filter = null) {
  let where = '';
  const params = [];
  if (filter && (filter.country_id != null || filter.location_id != null || filter.department_id != null)) {
    const cond = [];
    if (filter.country_id != null && filter.country_id !== '') {
      cond.push('loc.country_id = ?');
      params.push(Number(filter.country_id));
    }
    if (filter.location_id != null && filter.location_id !== '') {
      cond.push('u.location_id = ?');
      params.push(Number(filter.location_id));
    }
    if (filter.department_id != null && filter.department_id !== '') {
      cond.push('u.department_id = ?');
      params.push(Number(filter.department_id));
    }
    if (cond.length) where = ' LEFT JOIN locations loc ON u.location_id = loc.location_id WHERE ' + cond.join(' AND ');
  }
  const [rows] = await pool.query(
    `SELECT s.id,
            s.audit_name,
            s.assigned_to_user_id,
            DATE_FORMAT(s.scheduled_date, '%Y-%m-%d') AS scheduled_date,
            s.recurrence_type,
            s.recurrence_day_of_week,
            s.recurrence_day_of_month,
            s.reminder_days_before,
            s.assignment_mail_sent_at,
            s.created_by,
            s.created_at,
            s.updated_at,
            u.employee_name AS assigned_to_name,
            u.email_id AS assigned_to_email,
            c.employee_name AS created_by_name
    FROM sat_audit_schedules s
    LEFT JOIN users u ON s.assigned_to_user_id = u.user_id
    LEFT JOIN users c ON s.created_by = c.user_id
    ${where}
    ORDER BY s.scheduled_date ASC, s.created_at ASC`,
    params
  );
  return rows;
}

async function getScheduleById(id) {
  const [rows] = await pool.query(
    `SELECT s.id,
            s.audit_name,
            s.assigned_to_user_id,
            DATE_FORMAT(s.scheduled_date, '%Y-%m-%d') AS scheduled_date,
            s.recurrence_type,
            s.recurrence_day_of_week,
            s.recurrence_day_of_month,
            s.reminder_days_before,
            s.assignment_mail_sent_at,
            s.created_by,
            s.created_at,
            s.updated_at,
            u.employee_name AS assigned_to_name,
            u.email_id AS assigned_to_email,
            c.employee_name AS created_by_name
     FROM sat_audit_schedules s
     LEFT JOIN users u ON s.assigned_to_user_id = u.user_id
     LEFT JOIN users c ON s.created_by = c.user_id
     WHERE s.id = ?`,
    [id]
  );
  return rows[0] || null;
}

async function createSchedule(data, connection = null) {
  const db = connection || pool;
  const {
    audit_name,
    assigned_to_user_id,
    scheduled_date,
    recurrence_type,
    recurrence_day_of_week,
    recurrence_day_of_month,
    reminder_days_before,
    created_by
  } = data;
  const [result] = await db.query(
    `INSERT INTO sat_audit_schedules (
      audit_name, assigned_to_user_id, scheduled_date,
      recurrence_type, recurrence_day_of_week, recurrence_day_of_month,
      reminder_days_before, created_by, created_at, updated_at
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
    [
      audit_name,
      assigned_to_user_id,
      scheduled_date,
      recurrence_type || 'once',
      recurrence_day_of_week ?? null,
      recurrence_day_of_month ?? null,
      reminder_days_before ?? 0,
      created_by
    ]
  );
  return result.insertId;
}

async function updateSchedule(id, data, connection = null) {
  const db = connection || pool;
  const {
    audit_name,
    assigned_to_user_id,
    scheduled_date,
    recurrence_type,
    recurrence_day_of_week,
    recurrence_day_of_month,
    reminder_days_before
  } = data;
  const [result] = await db.query(
    `UPDATE sat_audit_schedules SET
      audit_name = ?, assigned_to_user_id = ?, scheduled_date = ?,
      recurrence_type = ?, recurrence_day_of_week = ?, recurrence_day_of_month = ?,
      reminder_days_before = ?, updated_at = NOW()
     WHERE id = ?`,
    [
      audit_name,
      assigned_to_user_id,
      scheduled_date,
      recurrence_type || 'once',
      recurrence_day_of_week ?? null,
      recurrence_day_of_month ?? null,
      reminder_days_before ?? 0,
      id
    ]
  );
  return result.affectedRows;
}

async function deleteSchedule(id, connection = null) {
  const db = connection || pool;
  const [result] = await db.query('DELETE FROM sat_audit_schedules WHERE id = ?', [id]);
  return result.affectedRows;
}

async function setAssignmentMailSent(id, connection = null) {
  const db = connection || pool;
  const [result] = await db.query(
    'UPDATE sat_audit_schedules SET assignment_mail_sent_at = NOW() WHERE id = ?',
    [id]
  );
  return result.affectedRows;
}

/** @deprecated Use auditScheduleReminderService with recurrence-aware logic. */
async function getSchedulesDueForReminder() {
  const [rows] = await pool.query(`
    SELECT s.*,
           u.employee_name AS assigned_to_name,
           u.email_id AS assigned_to_email
    FROM sat_audit_schedules s
    LEFT JOIN users u ON s.assigned_to_user_id = u.user_id
    WHERE s.recurrence_type = 'once'
      AND s.scheduled_date = DATE_ADD(CURDATE(), INTERVAL s.reminder_days_before DAY)
  `);
  return rows;
}

module.exports = {
  getAllSchedules,
  getScheduleById,
  createSchedule,
  updateSchedule,
  deleteSchedule,
  setAssignmentMailSent,
  getSchedulesDueForReminder
};
