26499644744

Committed 27 May 2026 08:20AM UTC coverage: 86.841% (-0.2%) from 87.055%

Build # 26499644744

Build Type

push

github

Committed by

web-flow

Commit Message

feat(messaging): inter user private messaging

Coverage Stats

3368 of 4026 branches covered (83.66%)

Branch coverage included in aggregate %.

207 of 248 new or added lines in 16 files covered. (83.47%)

5 existing lines in 2 files now uncovered.

6788 of 7669 relevant lines covered (88.51%)

55.12 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

95.12

/api/services/MessageService.js

/**
 * MessageService.js
 *
 * @description :: Message service
 */

module.exports = {
  /**
   * Find an existing private conversation between two cavers.
   * @param {number} caver1Id
   * @param {number} caver2Id
   * @returns {Promise<number|null>}
   */
  findExistingConversation: async (caver1Id, caver2Id) => {
    const query = `
      SELECT p1.id_conversation 
      FROM j_participant p1 
      JOIN j_participant p2 ON p1.id_conversation = p2.id_conversation 
      WHERE p1.id_caver = $1 AND p2.id_caver = $2 
      LIMIT 1
    `;
    const result = await CommonService.query(query, [caver1Id, caver2Id]);
    return result.rows.length > 0 ? result.rows[0].id_conversation : null;
  },

  /**
   * Create a new conversation between two cavers.
   * @param {number} caver1Id
   * @param {number} caver2Id
   * @returns {Promise<Object>}
   */
  createConversation: async (caver1Id, caver2Id) =>
    sails.getDatastore().transaction(async (db) => {
      const newConvo = await TConversation.create({
        dateInscription: new Date(),
      })
        .usingConnection(db)
        .fetch();

      await CommonService.query(
        'INSERT INTO j_participant (id_conversation, id_caver) VALUES ($1, $2), ($3, $4)',
        [newConvo.id, caver1Id, newConvo.id, caver2Id],
        db
      );

      return newConvo;
    }),

  /**
   * Check if a caver is an eligible recipient for private messaging.
   * @param {number} caverId
   * @throws {Error} with code E_NOT_FOUND or E_FORBIDDEN
   * @returns {Promise<Object>} The caver
   */
  getEligibleRecipient: async (caverId) => {
    const caver = await TCaver.findOne({ id: caverId });

    if (!caver) {
      const error = new Error('Recipient not found');
      error.code = 'E_NOT_FOUND';
      throw error;
    }

    // Eligible Recipient criteria:
    // - Not banned
    // - Has a password (not a Non_User_Caver)
    // - mail_is_valid OR NOT activated
    if (
      caver.banned ||
      !caver.password ||
      (caver.activated && !caver.mailIsValid)
    ) {
      const error = new Error(
        'Recipient is not eligible for private messaging'
      );
      error.code = 'E_FORBIDDEN';
      throw error;
    }

    return caver;
  },

  /**
   * List conversations for a caver.
   * @param {number} caverId
   * @param {'active'|'archived'} state
   * @param {number} skip
   * @param {number} limit
   * @returns {Promise<Array>}
   */
  listConversations: async (caverId, state, skip, limit) => {
    const isArchived = state === 'archived';
    const query = `
      SELECT 
        c.id, 
        c.date_inscription as "dateInscription",
        last_m.date_sent as "lastMessageDate",
        last_m.body as "lastMessageBody",
        COALESCE(u.unread_count, 0)::int as "unreadCount",
        last_m.id as "lastMessageId",
        other_p.id_caver as "otherParticipantId",
        other_c.nickname as "otherParticipantNickname",
        tca.archived_at as "archivedAt"
      FROM t_conversation c
      JOIN j_participant my_p ON c.id = my_p.id_conversation AND my_p.id_caver = $1
      LEFT JOIN t_conversation_archive tca ON tca.id_conversation = c.id AND tca.id_caver = $1
      JOIN j_participant other_p ON c.id = other_p.id_conversation AND other_p.id_caver != $1
      LEFT JOIN t_caver other_c ON other_p.id_caver = other_c.id
      LEFT JOIN LATERAL (
        SELECT id, date_sent, body 
        FROM t_message 
        WHERE id_conversation = c.id 
        ORDER BY date_sent DESC 
        LIMIT 1
      ) last_m ON TRUE
      LEFT JOIN (
        SELECT id_conversation, COUNT(*) as unread_count
        FROM t_message
        WHERE id_caver_sender != $1 AND date_read IS NULL
        GROUP BY id_conversation
      ) u ON c.id = u.id_conversation
      WHERE ${isArchived ? 'tca.id IS NOT NULL' : 'tca.id IS NULL'}
      ORDER BY ${isArchived ? 'tca.archived_at' : 'last_m.date_sent'} DESC NULLS LAST
      LIMIT $2 OFFSET $3
    `;
    const result = await CommonService.query(query, [caverId, limit, skip]);
    return result.rows.map((row) => ({
      id: row.id,
      dateInscription: row.dateInscription,
      lastMessage: row.lastMessageId
        ? {
            id: row.lastMessageId,
            body: row.lastMessageBody,
            dateSent: row.lastMessageDate,
          }
        : null,
      unreadCount: parseInt(row.unreadCount, 10),
      otherParticipant: module.exports.formatParticipant({
        id: row.otherParticipantId,
        nickname: row.otherParticipantNickname,
      }),
      archivedAt: row.archivedAt || null,
    }));
  },

  /**
   * Count conversations for a caver.
   * @param {number} caverId
   * @param {'active'|'archived'} state
   * @returns {Promise<number>}
   */
  countConversations: async (caverId, state) => {
    const isArchived = state === 'archived';
    const query = `
      SELECT COUNT(*)
      FROM j_participant p
      LEFT JOIN t_conversation_archive tca
        ON tca.id_conversation = p.id_conversation AND tca.id_caver = p.id_caver
      WHERE p.id_caver = $1
        AND ${isArchived ? 'tca.id IS NOT NULL' : 'tca.id IS NULL'}
    `;
    const result = await CommonService.query(query, [caverId]);
    return parseInt(result.rows[0].count, 10);
  },

  /**
   * Get messages in a conversation.
   * Note: This uses "chat-style" pagination. skip=0 fetches the most recent messages.
   * The returned array is reversed to maintain chronological order for the client.
   * @param {number} conversationId
   * @param {number} skip
   * @param {number} limit
   * @param {number} readerId - Required. Marks messages as read for this participant.
   * @returns {Promise<Array>}
   */
  getMessages: async (conversationId, skip, limit, readerId) => {
    if (!readerId) {
      throw new Error('readerId is required to fetch messages');
    }

    const messages = await TMessage.find({ conversation: conversationId })
      .skip(skip)
      .limit(limit)
      .sort('dateSent DESC')
      .populate('caverSender');

    try {
      await module.exports.markAsRead(conversationId, readerId);
    } catch (err) {
      sails.log.error(
        `Failed to mark messages as read for conversation ${conversationId}:`,
        err
      );
    }

    return messages.map((m) => module.exports.formatMessage(m)).reverse();
  },

  /**
   * Count messages in a conversation.
   * @param {number} conversationId
   * @returns {Promise<number>}
   */
  countMessages: async (conversationId) =>
    TMessage.count({ conversation: conversationId }),

  /**
   * Mark unread messages from other participants as read.
   * @param {number} conversationId
   * @param {number} readerId
   * @returns {Promise<void>}
   */
  markAsRead: async (conversationId, readerId) => {
    // Only mark messages from other participants as read.
    // Opening a conversation does NOT auto-unarchive it; that is
    // exclusively handled by the /unarchive endpoint.
    await TMessage.update({
      conversation: conversationId,
      caverSender: { '!=': readerId },
      dateRead: null,
    }).set({
      dateRead: new Date(),
    });
  },

  /**
   * Check if a caver is a participant in a conversation.
   * @param {number} conversationId
   * @param {number} caverId
   * @returns {Promise<boolean>}
   */
  isParticipant: async (conversationId, caverId) => {
    const result = await CommonService.query(
      'SELECT 1 FROM j_participant WHERE id_conversation = $1 AND id_caver = $2',
      [conversationId, caverId]
    );
    return result.rows.length > 0;
  },

  /**
   * Get the ID of the other participant in a 1-on-1 conversation.
   * @param {number} conversationId
   * @param {number} caverId - The ID of the participant to exclude.
   * @returns {Promise<number|null>}
   */
  getOtherParticipantId: async (conversationId, caverId) => {
    const result = await CommonService.query(
      'SELECT id_caver FROM j_participant WHERE id_conversation = $1 AND id_caver != $2',
      [conversationId, caverId]
    );
    return result.rows.length > 0 ? result.rows[0].id_caver : null;
  },

  /**
   * Get unread message counts for both active and archived conversations.
   * @param {number} caverId
   * @returns {Promise<Object>} { active, archived }
   */
  getUnreadCounts: async (caverId) => {
    const query = `
      SELECT 
        CASE WHEN tca.id IS NOT NULL THEN 'archived' ELSE 'active' END as state,
        COUNT(m.id)::int as count
      FROM j_participant p
      LEFT JOIN t_conversation_archive tca
        ON tca.id_conversation = p.id_conversation AND tca.id_caver = p.id_caver
      JOIN t_message m ON p.id_conversation = m.id_conversation 
      WHERE p.id_caver = $1
        AND m.id_caver_sender != $1 
        AND m.date_read IS NULL
      GROUP BY tca.id IS NOT NULL
    `;
    const result = await CommonService.query(query, [caverId]);
    const counts = { active: 0, archived: 0 };
    result.rows.forEach((row) => {
      counts[row.state] = row.count;
    });
    return counts;
  },

  /**
   * Format a participant for API response (strips PII).
   * @param {object|number} participant
   * @returns {object|number}
   */
  formatParticipant: (participant) => {
    if (!participant || typeof participant !== 'object') {
      return participant;
    }
    return {
      id: participant.id,
      nickname: participant.nickname || 'Deleted User',
    };
  },

  /**
   * Format a message for API response (strips PII).
   * @param {object} message
   * @returns {object}
   */
  formatMessage: (message) => {
    if (!message) return null;
    return {
      id: message.id,
      body: message.body,
      dateSent: message.dateSent,
      dateRead: message.dateRead,
      conversation: message.conversation,
      caverSender: module.exports.formatParticipant(message.caverSender),
    };
  },
};

1	/**
2	* MessageService.js
3	*
4	* @description :: Message service
5	*/
6
7	module.exports = {	2✔
8	/**
9	* Find an existing private conversation between two cavers.
10	* @param {number} caver1Id
11	* @param {number} caver2Id
12	* @returns {Promise<number\|null>}
13	*/
14	findExistingConversation: async (caver1Id, caver2Id) => {
15	const query = `	6✔
16	SELECT p1.id_conversation
17	FROM j_participant p1
18	JOIN j_participant p2 ON p1.id_conversation = p2.id_conversation
19	WHERE p1.id_caver = $1 AND p2.id_caver = $2
20	LIMIT 1
21	`;
22	const result = await CommonService.query(query, [caver1Id, caver2Id]);	6✔
23	return result.rows.length > 0 ? result.rows[0].id_conversation : null;	6✔
24	},
25
26	/**
27	* Create a new conversation between two cavers.
28	* @param {number} caver1Id
29	* @param {number} caver2Id
30	* @returns {Promise<Object>}
31	*/
32	createConversation: async (caver1Id, caver2Id) =>
33	sails.getDatastore().transaction(async (db) => {	5✔
34	const newConvo = await TConversation.create({	5✔
35	dateInscription: new Date(),
36	})
37	.usingConnection(db)
38	.fetch();
39
40	await CommonService.query(	5✔
41	'INSERT INTO j_participant (id_conversation, id_caver) VALUES ($1, $2), ($3, $4)',
42	[newConvo.id, caver1Id, newConvo.id, caver2Id],
43	db
44	);
45
46	return newConvo;	5✔
47	}),
48
49	/**
50	* Check if a caver is an eligible recipient for private messaging.
51	* @param {number} caverId
52	* @throws {Error} with code E_NOT_FOUND or E_FORBIDDEN
53	* @returns {Promise<Object>} The caver
54	*/
55	getEligibleRecipient: async (caverId) => {
56	const caver = await TCaver.findOne({ id: caverId });	12✔
57
58	if (!caver) {	12✔
59	const error = new Error('Recipient not found');	1✔
60	error.code = 'E_NOT_FOUND';	1✔
61	throw error;	1✔
62	}
63
64	// Eligible Recipient criteria:
65	// - Not banned
66	// - Has a password (not a Non_User_Caver)
67	// - mail_is_valid OR NOT activated
68	if (	11✔
69	caver.banned \|\|	35✔
70	!caver.password \|\|
71	(caver.activated && !caver.mailIsValid)
72	) {
73	const error = new Error(	4✔
74	'Recipient is not eligible for private messaging'
75	);
76	error.code = 'E_FORBIDDEN';	4✔
77	throw error;	4✔
78	}
79
80	return caver;	7✔
81	},
82
83	/**
84	* List conversations for a caver.
85	* @param {number} caverId
86	* @param {'active'\|'archived'} state
87	* @param {number} skip
88	* @param {number} limit
89	* @returns {Promise<Array>}
90	*/
91	listConversations: async (caverId, state, skip, limit) => {
92	const isArchived = state === 'archived';	6✔
93	const query = `	6✔
94	SELECT
95	c.id,
96	c.date_inscription as "dateInscription",
97	last_m.date_sent as "lastMessageDate",
98	last_m.body as "lastMessageBody",
99	COALESCE(u.unread_count, 0)::int as "unreadCount",
100	last_m.id as "lastMessageId",
101	other_p.id_caver as "otherParticipantId",
102	other_c.nickname as "otherParticipantNickname",
103	tca.archived_at as "archivedAt"
104	FROM t_conversation c
105	JOIN j_participant my_p ON c.id = my_p.id_conversation AND my_p.id_caver = $1
106	LEFT JOIN t_conversation_archive tca ON tca.id_conversation = c.id AND tca.id_caver = $1
107	JOIN j_participant other_p ON c.id = other_p.id_conversation AND other_p.id_caver != $1
108	LEFT JOIN t_caver other_c ON other_p.id_caver = other_c.id
109	LEFT JOIN LATERAL (
110	SELECT id, date_sent, body
111	FROM t_message
112	WHERE id_conversation = c.id
113	ORDER BY date_sent DESC
114	LIMIT 1
115	) last_m ON TRUE
116	LEFT JOIN (
117	SELECT id_conversation, COUNT(*) as unread_count
118	FROM t_message
119	WHERE id_caver_sender != $1 AND date_read IS NULL
120	GROUP BY id_conversation
121	) u ON c.id = u.id_conversation
122	WHERE ${isArchived ? 'tca.id IS NOT NULL' : 'tca.id IS NULL'}	6✔
123	ORDER BY ${isArchived ? 'tca.archived_at' : 'last_m.date_sent'} DESC NULLS LAST	6✔
124	LIMIT $2 OFFSET $3
125	`;
126	const result = await CommonService.query(query, [caverId, limit, skip]);	6✔
127	return result.rows.map((row) => ({	13✔
128	id: row.id,
129	dateInscription: row.dateInscription,
130	lastMessage: row.lastMessageId	13!
131	? {
132	id: row.lastMessageId,
133	body: row.lastMessageBody,
134	dateSent: row.lastMessageDate,
135	}
136	: null,
137	unreadCount: parseInt(row.unreadCount, 10),
138	otherParticipant: module.exports.formatParticipant({
139	id: row.otherParticipantId,
140	nickname: row.otherParticipantNickname,
141	}),
142	archivedAt: row.archivedAt \|\| null,	22✔
143	}));
144	},
145
146	/**
147	* Count conversations for a caver.
148	* @param {number} caverId
149	* @param {'active'\|'archived'} state
150	* @returns {Promise<number>}
151	*/
152	countConversations: async (caverId, state) => {
153	const isArchived = state === 'archived';	6✔
154	const query = `	6✔
155	SELECT COUNT(*)
156	FROM j_participant p
157	LEFT JOIN t_conversation_archive tca
158	ON tca.id_conversation = p.id_conversation AND tca.id_caver = p.id_caver
159	WHERE p.id_caver = $1
160	AND ${isArchived ? 'tca.id IS NOT NULL' : 'tca.id IS NULL'}	6✔
161	`;
162	const result = await CommonService.query(query, [caverId]);	6✔
163	return parseInt(result.rows[0].count, 10);	6✔
164	},
165
166	/**
167	* Get messages in a conversation.
168	* Note: This uses "chat-style" pagination. skip=0 fetches the most recent messages.
169	* The returned array is reversed to maintain chronological order for the client.
170	* @param {number} conversationId
171	* @param {number} skip
172	* @param {number} limit
173	* @param {number} readerId - Required. Marks messages as read for this participant.
174	* @returns {Promise<Array>}
175	*/
176	getMessages: async (conversationId, skip, limit, readerId) => {
177	if (!readerId) {	5✔
178	throw new Error('readerId is required to fetch messages');	1✔
179	}
180
181	const messages = await TMessage.find({ conversation: conversationId })	4✔
182	.skip(skip)
183	.limit(limit)
184	.sort('dateSent DESC')
185	.populate('caverSender');
186
187	try {	4✔
188	await module.exports.markAsRead(conversationId, readerId);	4✔
189	} catch (err) {
NEW 190	sails.log.error(	×
191	`Failed to mark messages as read for conversation ${conversationId}:`,
192	err
193	);
194	}
195
196	return messages.map((m) => module.exports.formatMessage(m)).reverse();	12✔
197	},
198
199	/**
200	* Count messages in a conversation.
201	* @param {number} conversationId
202	* @returns {Promise<number>}
203	*/
204	countMessages: async (conversationId) =>
205	TMessage.count({ conversation: conversationId }),	2✔
206
207	/**
208	* Mark unread messages from other participants as read.
209	* @param {number} conversationId
210	* @param {number} readerId
211	* @returns {Promise<void>}
212	*/
213	markAsRead: async (conversationId, readerId) => {
214	// Only mark messages from other participants as read.
215	// Opening a conversation does NOT auto-unarchive it; that is
216	// exclusively handled by the /unarchive endpoint.
217	await TMessage.update({	4✔
218	conversation: conversationId,
219	caverSender: { '!=': readerId },
220	dateRead: null,
221	}).set({
222	dateRead: new Date(),
223	});
224	},
225
226	/**
227	* Check if a caver is a participant in a conversation.
228	* @param {number} conversationId
229	* @param {number} caverId
230	* @returns {Promise<boolean>}
231	*/
232	isParticipant: async (conversationId, caverId) => {
233	const result = await CommonService.query(	6✔
234	'SELECT 1 FROM j_participant WHERE id_conversation = $1 AND id_caver = $2',
235	[conversationId, caverId]
236	);
237	return result.rows.length > 0;	6✔
238	},
239
240	/**
241	* Get the ID of the other participant in a 1-on-1 conversation.
242	* @param {number} conversationId
243	* @param {number} caverId - The ID of the participant to exclude.
244	* @returns {Promise<number\|null>}
245	*/
246	getOtherParticipantId: async (conversationId, caverId) => {
247	const result = await CommonService.query(	2✔
248	'SELECT id_caver FROM j_participant WHERE id_conversation = $1 AND id_caver != $2',
249	[conversationId, caverId]
250	);
251	return result.rows.length > 0 ? result.rows[0].id_caver : null;	2!
252	},
253
254	/**
255	* Get unread message counts for both active and archived conversations.
256	* @param {number} caverId
257	* @returns {Promise<Object>} { active, archived }
258	*/
259	getUnreadCounts: async (caverId) => {
260	const query = `	1✔
261	SELECT
262	CASE WHEN tca.id IS NOT NULL THEN 'archived' ELSE 'active' END as state,
263	COUNT(m.id)::int as count
264	FROM j_participant p
265	LEFT JOIN t_conversation_archive tca
266	ON tca.id_conversation = p.id_conversation AND tca.id_caver = p.id_caver
267	JOIN t_message m ON p.id_conversation = m.id_conversation
268	WHERE p.id_caver = $1
269	AND m.id_caver_sender != $1
270	AND m.date_read IS NULL
271	GROUP BY tca.id IS NOT NULL
272	`;
273	const result = await CommonService.query(query, [caverId]);	1✔
274	const counts = { active: 0, archived: 0 };	1✔
275	result.rows.forEach((row) => {	1✔
276	counts[row.state] = row.count;	1✔
277	});
278	return counts;	1✔
279	},
280
281	/**
282	* Format a participant for API response (strips PII).
283	* @param {object\|number} participant
284	* @returns {object\|number}
285	*/
286	formatParticipant: (participant) => {
287	if (!participant \|\| typeof participant !== 'object') {	32✔
288	return participant;	7✔
289	}
290	return {	25✔
291	id: participant.id,
292	nickname: participant.nickname \|\| 'Deleted User',	34✔
293	};
294	},
295
296	/**
297	* Format a message for API response (strips PII).
298	* @param {object} message
299	* @returns {object}
300	*/
301	formatMessage: (message) => {
302	if (!message) return null;	19!
303	return {	19✔
304	id: message.id,
305	body: message.body,
306	dateSent: message.dateSent,
307	dateRead: message.dateRead,
308	conversation: message.conversation,
309	caverSender: module.exports.formatParticipant(message.caverSender),
310	};
311	},
312	};

GrottoCenter / grottocenter-api / 26499644744

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous