/**
 * Сервис уведомлений: создание записей только для затронутых пользователей (user_id).
 * Не рассылаем «всем по роли» — только конкретным userId из сущности.
 */

/**
 * Создать одно уведомление для пользователя.
 * @param {object} pool - pg Pool
 * @param {object} opts - { userId, type, title, body?, entityType?, entityId?, payload? }
 * @returns {Promise<{ id: number }>}
 */
async function createNotification(pool, opts) {
  const { userId, type, title, body = null, entityType = null, entityId = null, payload = null } = opts;
  if (!userId || !type || !title) {
    throw new Error('notificationService.createNotification: userId, type, title required');
  }
  const r = await pool.query(
    `INSERT INTO notifications (user_id, type, title, body, entity_type, entity_id, payload, read_at, created_at)
     VALUES ($1, $2, $3, $4, $5, $6, $7, NULL, NOW())
     RETURNING id`,
    [userId, type, title, body, entityType, entityId != null ? String(entityId) : null, payload ? JSON.stringify(payload) : null]
  );
  return { id: r.rows[0].id };
}

/**
 * Создать уведомления для нескольких пользователей (один и тот же текст).
 * @param {object} pool - pg Pool
 * @param {number[]} userIds - portal_users.id (без дубликатов)
 * @param {object} opts - { type, title, body?, entityType?, entityId?, payload? }
 * @returns {Promise<number>} количество созданных записей
 */
async function createNotificationForUserIds(pool, userIds, opts) {
  if (!userIds || userIds.length === 0) return 0;
  const unique = [...new Set(userIds)].filter(Boolean);
  let count = 0;
  for (const uid of unique) {
    await createNotification(pool, { ...opts, userId: uid });
    count++;
  }
  return count;
}

/**
 * Резолв employee_id(ы) в portal_users.id.
 * @param {object} pool - pg Pool
 * @param {string[]} employeeIds - employees.id
 * @returns {Promise<number[]>} portal_users.id
 */
async function resolveEmployeeIdsToUserIds(pool, employeeIds) {
  if (!employeeIds || employeeIds.length === 0) return [];
  const unique = [...new Set(employeeIds)].filter(Boolean);
  const r = await pool.query(
    `SELECT id FROM portal_users WHERE employee_id = ANY($1::text[])`,
    [unique]
  );
  return r.rows.map(row => Number(row.id));
}

/**
 * Резолв одного employee_id в portal_users.id (или null).
 * @param {object} pool - pg Pool
 * @param {string} employeeId - employees.id
 * @returns {Promise<number|null>}
 */
async function resolveEmployeeIdToUserId(pool, employeeId) {
  if (!employeeId) return null;
  const r = await pool.query(
    `SELECT id FROM portal_users WHERE employee_id = $1 LIMIT 1`,
    [employeeId]
  );
  return r.rows.length ? Number(r.rows[0].id) : null;
}

/**
 * Резолв имён сотрудников (performer_name, responsible_name, assigned_to) в portal_users.id.
 * @param {object} pool - pg Pool
 * @param {string[]} names - массив имён (например ['Иванов И.И.', 'Петров П.П.'])
 * @returns {Promise<number[]>} portal_users.id (без дубликатов)
 */
async function resolveEmployeeNamesToUserIds(pool, names) {
  if (!names || names.length === 0) return [];
  const trimmed = [...new Set(names.map(n => (n && String(n).trim())).filter(Boolean))];
  if (trimmed.length === 0) return [];
  const r = await pool.query(
    `SELECT DISTINCT pu.id FROM portal_users pu
     JOIN employees e ON e.id = pu.employee_id
     WHERE e.name = ANY($1::text[])`,
    [trimmed]
  );
  return r.rows.map(row => Number(row.id));
}

/**
 * Создать уведомления для сотрудников (по employee_id); только тем, у кого есть portal_users.
 * @param {object} pool - pg Pool
 * @param {string[]} employeeIds - employees.id
 * @param {object} opts - { type, title, body?, entityType?, entityId?, payload? }
 * @returns {Promise<number>}
 */
async function createNotificationForEmployeeIds(pool, employeeIds, opts) {
  const userIds = await resolveEmployeeIdsToUserIds(pool, employeeIds);
  return createNotificationForUserIds(pool, userIds, opts);
}

/**
 * Получить portal_users.id сотрудников, ответственных за зону (section + sub_id).
 * Используется для целевых уведомлений по зонам ответственности.
 * @param {object} pool - pg Pool
 * @param {string} section - раздел (hr, finance, requests, pr, legal, development, office, admin)
 * @param {string} subId - подраздел (employees, hiring, invoices, ...)
 * @returns {Promise<number[]>} portal_users.id
 */
async function getResponsibleUserIdsForZone(pool, section, subId) {
  if (!section || !subId) return [];
  const r = await pool.query(
    `SELECT DISTINCT pu.id FROM portal_users pu
     JOIN employee_responsibility er ON er.employee_id = pu.employee_id
     WHERE er.section = $1 AND er.sub_id = $2`,
    [section, subId]
  );
  return r.rows.map(row => Number(row.id));
}

/**
 * Создать уведомления для ответственных за зону (section + sub_id).
 * @param {object} pool - pg Pool
 * @param {string} section - раздел
 * @param {string} subId - подраздел
 * @param {object} opts - { type, title, body?, entityType?, entityId?, payload? }
 * @returns {Promise<number>} количество созданных уведомлений
 */
async function createNotificationForResponsibleZone(pool, section, subId, opts) {
  const userIds = await getResponsibleUserIdsForZone(pool, section, subId);
  return createNotificationForUserIds(pool, userIds, opts);
}

const SECTION_IDS = ['dashboard', 'objects', 'requests', 'pr', 'finance', 'legal', 'development', 'hr', 'office', 'admin'];
const ROLE_ACCESS = {
  DIRECTOR: ['all'],
  ENGINEER: ['dashboard', 'objects', 'requests', 'office', 'development'],
  MASTER: ['objects', 'requests'],
  LAWYER: ['dashboard', 'legal', 'objects', 'requests'],
  FINANCIER: ['dashboard', 'finance', 'office', 'objects'],
  HR_MANAGER: ['dashboard', 'hr', 'office'],
  PR_MANAGER: ['dashboard', 'pr', 'requests']
};

function allowedSectionsFromPermissions(permissions) {
  if (!permissions || !Array.isArray(permissions) || permissions.length === 0) return null;
  if (permissions.includes('all')) return SECTION_IDS;
  const set = new Set();
  for (const p of permissions) {
    if (SECTION_IDS.includes(p)) set.add(p);
    else if (typeof p === 'string' && p.includes('_')) {
      const section = p.split('_')[0];
      if (SECTION_IDS.includes(section)) set.add(section);
    }
  }
  return Array.from(set);
}

/**
 * Получить portal_users.id пользователей, у которых есть доступ к любому из указанных разделов (по permissions или роли).
 * @param {object} pool - pg Pool
 * @param {string[]} sections - разделы (dashboard, pr, finance, legal, development, hr, office, ...)
 * @returns {Promise<number[]>} portal_users.id
 */
async function getPortalUserIdsBySections(pool, sections) {
  if (!sections || sections.length === 0) return [];
  const r = await pool.query(
    `SELECT id, role, permissions FROM portal_users`
  );
  const wanted = new Set(sections);
  const userIds = [];
  for (const row of r.rows) {
    let allowed;
    if (row.permissions && Array.isArray(row.permissions) && row.permissions.length > 0) {
      allowed = allowedSectionsFromPermissions(row.permissions);
    } else {
      const roleSections = ROLE_ACCESS[row.role];
      allowed = roleSections && roleSections.includes('all') ? SECTION_IDS : (roleSections || []);
    }
    if (allowed && allowed.some(s => wanted.has(s))) {
      userIds.push(Number(row.id));
    }
  }
  return userIds;
}

module.exports = {
  createNotification,
  createNotificationForUserIds,
  createNotificationForEmployeeIds,
  createNotificationForResponsibleZone,
  resolveEmployeeIdsToUserIds,
  resolveEmployeeIdToUserId,
  resolveEmployeeNamesToUserIds,
  getResponsibleUserIdsForZone,
  getPortalUserIdsBySections,
};