import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';

export interface NotificationPayload {
  type: string;
  title: string;
  message: string;
  link?: string;
  relatedProjectId?: number;
}

async function insertForUsers(userIds: number[], payload: NotificationPayload) {
  const ids = [...new Set(userIds)];
  if (ids.length === 0) return;
  const values = ids.map((userId) => [
    userId,
    payload.type,
    payload.title,
    payload.message,
    payload.link ?? null,
    payload.relatedProjectId ?? null,
  ]);
  await pool.query(
    `INSERT INTO notifications (user_id, type, title, message, link, related_project_id) VALUES ?`,
    [values],
  );
}

export async function notifyUsers(userIds: number[], payload: NotificationPayload) {
  await insertForUsers(userIds, payload);
}

export async function notifyRole(roleName: string, payload: NotificationPayload, excludeUserId?: number) {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT u.id FROM users u JOIN roles r ON r.id = u.role_id WHERE r.name = ? AND u.id != ?`,
    [roleName, excludeUserId ?? 0],
  );
  await insertForUsers(rows.map((r) => r.id as number), payload);
}

/**
 * Legacy no-role accounts are treated as unrestricted everywhere else in the
 * app (see permissionService.unrestrictedPermissions), so they're included
 * here too — "everyone except X/Y" means every actual role-holder outside
 * the excluded set, plus anyone with no role assigned.
 */
export async function notifyAllExceptRoles(
  roleNames: string[],
  payload: NotificationPayload,
  excludeUserId?: number,
) {
  const [rows] = await pool.query<RowDataPacket[]>(
    `SELECT u.id FROM users u
     LEFT JOIN roles r ON r.id = u.role_id
     WHERE (r.name IS NULL OR r.name NOT IN (?)) AND u.id != ?`,
    [roleNames.length > 0 ? roleNames : [''], excludeUserId ?? 0],
  );
  await insertForUsers(rows.map((r) => r.id as number), payload);
}